Control Files - Management

Managing Control Files

Obtaining Control File information

select value from v$parameter where upper(name)= 'CONTROL_FILES'
select * from v$controlfile;
select * from v$controlfile_record_section

Creating Additional Copies, Renaming, and Relocating Control Files

1. Shut down the database.
2. Copy an existing control file to a new location, using operating system commands.
3. Edit the CONTROL_FILES parameter CONTROL_FILES="D:\ORACLE\ORADATA\ORA11G2\CONTROL01.CTL","D:\ORACLE\ORADATA\ORA1 1G2\CONTROL02.CTL";
4. Restart the database.

Note: if you are using SPFILE, STARTUP NOMOUNT then use ALTER SYSTEM SET .. SCOPE=SPFILE command.

Creating New Control Files

1. Make a list of all datafiles and redo log files of the database.
2. Shut down the database.
3. Back up all datafiles and redo log files of the database.
4. STARTUP NOMOUNT
5. Use the CREATE CONTROLFILE statement:

CREATE CONTROLFILE SET DATABASE prod 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'), GROUP 3 ('/u01/oracle/prod/redo03_01.log', '/u01/oracle/prod/redo03_02.log') RESETLOGS | NORESETLOGS 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;

Specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.

 Caution: The CREATE CONTROLFILE statement can potentially damage specified datafiles and redo log files. Omitting a filename can cause loss of the data in that file, or loss of access to the entire database.

6. Store a backup of the new control file on an offline storage device.
7. Edit the CONTROL_FILES initialization parameter
8. If you are renaming the database, edit the DB_NAME parameter in your instance parameter file.
9. Recover the database if necessary.
     • If the new control file was created using the NORESETLOGS clause, you can recover the database with complete, closed database recovery.
     • If the new control file was created using the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE in your RECOVER command.

10. If you did not perform recovery, open the database normally.
ALTER DATABASE OPEN;
If you specified RESETLOGS when creating the control file: ALTER DATABASE OPEN RESETLOGS;

Handling Errors During CREATE CONTROLFILE

If Oracle Database sends you an error (usually error ORA-01173, ORA-01176, ORA-01177, ORA-01215, or ORA-01216) when you attempt to mount and open the database after creating a new control file, the most likely cause is that you omitted a file from the CREATE CONTROLFILE statement or included one that should not have been listed.

Checking for Missing Files after Creating Control Files

Check the alert log to see if the database has detected inconsistencies between the data dictionary and the control file.

• If a datafile exists in the data dictionary but not in the new control file, the database creates a placeholder entry in the control file under the name MISSINGnnnn, where nnnn is the file number in decimal. MISSINGnnnn is flagged in the control file as being offline and requiring media recovery.

+ If the actual datafile corresponding to MISSINGnnnn is read-only or offline normal, then you can make the datafile accessible by renaming MISSINGnnnn to the name of the actual datafile.

+ If MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, you must drop the tablespace containing the datafile.

Backing Up Control Files

 -- copy of the control file:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

-- commands to re-create the control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Show parameter user_dump_dest

Manage the Size of Control Files 

It is affected by MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES parameters in the CREATE DATABASE statement. Also it is affected by CONTROL_FILE_RECORD_KEEP_TIME

Multiplexing the Control File

1. Alter the SPFILE:
ALTER SYSTEM SET control_files ='$HOME/ORADATA/u01/ctrl01.ctl','$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;
2. Shut down the database
3. Create additional control files:
cp $HOME/ORADATA/u01/ctrl01.ctl $HOME/ORADATA/u02/ctrl02.ctl
4. Start the database:
startup