Create Controlfile using SQLPLUS oracle

Question

I need help with creating controlfile using sqlplus. I have used next steps, I altered spfile, shutdown database, startup nomount, used next command to create controlfile but it failed:

create controlfile
set database orcl
logfile controlnew1   
('\oracle\oradata\orcl\controlnew1.log’,
'\oracle\oradata\orcl\controlnew1.log’)
resetlogs;

My specifications is to create 2 new controlfiles named contronew1 and controlnew2 in location CONTROL01. I have found related article in oracle documentation but aberrantly I don't understand. I would be thankful for any help to resolve this.

Answer 1

This should probably go in the DBA stack exchange but I'm not sure how to make that happen.

I'd recommend reading Oracle's 12c document for Creating Control Files

If you're creating controlfiles I assume this means you don't have an existing controlfile to copy. Copying a control file is much simpler than creating a new control file and preferred. You should only be creating a new control file if all existing copies of the controlfile are gone or you're modifying controlfile config or database name.

If you do have an existing controlfile to copy it you can :

• Shut down the database.
• Copy an existing control file to a new location, using operating system commands.
• Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control file name, or to change the existing control file name.
• Restart the database.

If you have an existing controlfile you can write it out to a trace file from sqlplus (should go to diagnostic_dest). This isn't necessary for creating or copying a controlfile but can be helpful.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

If you are unable to copy an existing controlfile and need to create a new one here are a few notes about your CREATE CONTROLFILE command

• The logfile clause is used to specify REDO logs, not new control files.
• You have the same filename in logfile controlnew1
• You don't have any Data Files

Below is oracle's example for CREATE CONTROLFILE. The first step in creating a new controlfile is to make a list of all datafiles and redo log files of the database. The redo log files will go in the LOGFILE section and the data files will go in the DATAFILE section.

CREATE CONTROLFILE
SET DATABASE orcl
LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log', 
                '/u01/oracle/prod/redo01_02.log'),
       GROUP 2 ('/u01/oracle/prod/redo02_01.log', 
                '/u01/oracle/prod/redo02_02.log')
RESETLOGS
DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
        '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
        '/u01/oracle/prod/users01.dbs' SIZE 5M,
        '/u01/oracle/prod/temp01.dbs' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;

After the file is created you'll need to edit the CONTROL_FILES init parameter and open the database. Depending on the state of your database you may need to do additional recovery before opening it and may need to use

ALTER DATABASE OPEN RESETLOGS;

if your controlfile used RESETLOGS.