What are control files?
The control file is a binary file that defines the current state of the physical database. The control file is one of the core files at the heart of the Oracle database. Together, this means that the Oracle instance itself is the only means by which the contents of the control file should ever be modified. The DBA should only modify it indirectly by means of altering the database. Loss or corruption of the control file requires a database recovery.
The control file is read at the mount stage of database startup and is required for the database to be functional. Each control file is linked to a single database. Before the database is opened, Oracle reads the control file to determine whether the database is in a valid state to use. The control file is updated almost continuously by the Oracle server when the database is in use and therefore must be available for write processing whenever the database is open.
Things to Consider Before Recreating the Controlfile
The control file is very important to the database. Some information is stored only in the control file rather than the data dictionary. The metadata includes flashback logs, block change tracking, RMAN backups, and datafile locations. Often there are workarounds or solutions available and the control file does not need to be recreated at all.
If you really must recreate the control file, or when instructed by Oracle, consider the following…
1. Inaccessible/Offlined Datafiles
If all of the datafiles are not on disk in the first place, you will not able able to recreate the controlfile. If the datafiles are on disk, ensure that there are no offlined datafiles:
select distinct(status) from v$datafile where status not in ('ONLINE','SYSTEM');
select name, ts#, online$, contents$ from ts$ where online$ =2;
Otherwise, once the control file is recreated with resetlogs all offlined datafiles cannot be added back to the database. You may encounter the following errors:
RMAN> sql 'alter database datafile 6 online'; sql statement: alter database datafile 6 online RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of sql command on default channel at 07/29/2012 11:07:07 RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 6 online ORA-01190: control file or data file 6 is from before the last RESETLOGS ORA-01110: data file 6: '/opt/app/oracle/oradata/ORA112/leng_ts.dbf'
2. Flashback database
When a controlfile is recreated, all flashback information – normal restore point, guaranteed restore point, and flashback database, will be turned OFF, even if using NORESETLOGS. All flashback logs on disk cannot be cataloged back into the controlfile. You will encounter this error if attempting to catalog flashback logs:
File Name: /opt/app/oracle/fra/ORA112/flashback/o1_mf_85zttw61_.flb RMAN-07529: Reason: catalog is not supported for this file type
You will have to re-enable flashback database. All older flashbacklogs will no longer be useful and should be manually removed from disk.
3. Flash Recovery Area Usage
Once the controlfile is recreated, the view v$flash_recovery_area_usage may not reflect the actual usage on disk. So you may need to run this command to catalog everything back into the controlfile.
RMAN> catalog recovery area;
4. Backup metadata
If you are not using an RMAN recovery catalog, you will need to catalog all backup pieces into the controlfile. Further, prior to 9i, we cannot catalog backup pieces anyway.
The control file is important to a backup and restore process. It is true that you can catalog all backup pieces back into the control file, but you must have a control file to use in the first place. If you’ve lost your entire system and only have backup pieces to use, you must have at least a controlfile to catalog these backup pieces. Further, you cannot recreate the controlfile without any datafile on disk.
Backing Up the Current controlfile
You should at least take a backup of the current controlfiles before recreating it. If in open/mounted mode:
RMAN> backup current controlfile;
SYS> alter database backup controlfile to '/tmp/control01.bk';
If the database is already shutdown, take an operating system backup first. For example:
SYS@ORA112.SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ -------------------- ------------------------------
control_files string /opt/app/oracle/oradata/ORA112
/control1.ctl, /opt/app/oracle
/oradata/ORA112/control2.ctl
$ cp /opt/app/oracle/oradata/ORA112/control1.ctl /opt/app/oracle/oradata/ORA112/control1.ctl.old $ cp /opt/app/oracle/oradata/ORA112/control2.ctl /opt/app/oracle/oradata/ORA112/control2.ctl.old
Note that a binary backup is different from an ASCII backup! This generates an ASCII command that will allow you to recreate the controlfile. It is actually NOT a backup of the current controlfile.
SQL> alter database backup controlfile to trace;
Recreating Control File
You should only recreate your control file under the following circumstances:
- All current copies of the control file have been lost or are corrupted.
- You are restoring a backup in which the control file is corrupted or missing.
- You need to change a hard limit database parameter in the controlfile.
- If you are moving your database to another server and files are located in a different location.
- Oracle Customer Support advises you to.
Creating a new Controlfile from an existing database that is mounted or open.
First you must generate an ascii dump of the controlfile. Whilst the database is mounted or open issue:
SQL> alter database backup controlfile to trace;
A trace file will be generated in the user_dump_destination directory.
SQL> show parameter user_dump_dest NAME TYPE VALUE -------------- ------ ------------------------------------------------ user_dump_dest string /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace
After navigating to the directory locate the latest trace file by date/time by issuing “ls -ltr” command.
$ cd /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace $ ls -ltr
Once you locate the file it will appear as an ordinary trace file: /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace/V11_ora_31225.trc
Modify the trace file and use it as a script to create the control.
CREATE CONTROLFILE REUSE DATABASE "V11" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oradata/V11/redo01.log' SIZE 50M, GROUP 2 '/oradata/V11/redo02.log' SIZE 50M, GROUP 3 '/oradata/V11/redo03.log' SIZE 50M DATAFILE '/oradata/V11/system01.dbf', '/oradata/V11/sysaux01.dbf', '/oradata/V11/undotbs01.dbf', '/oradata/V11/user01.dbf' CHARACTER SET WE8MSWIN1252 ;
It is important to delete everything above the “CREATE CONTROLFILE” and everything after the “CHARACTER SET”. Ensure you leave the semicolon “;”. In the above example, we are choosing the NORESETLOGS option and running the database in archivelog mode. After successfully saving the script you are now able to recreate the controlfile. When shutting down the database ensure that you shutdown with the immediate option.
SQL> shutdown immediate;
Start the database in nomount stage and run the script you just created.
SQL> startup nomount; SQL>@control.sql
Note: After recreating the controlfile ensure you add the existing TEMP files. For example:
sql> alter tablespace temp_ts add tempfile '/oradata/V11/temp01.dbf' reuse;
Once the controlfile is successfully created the database is automatically mounted. If you have opened the database with a resetlogs it is important to take a backup asap.
Creating a new controlfile from a database that is not able to mount
Under the rare occasion that you do not have a controlfile to either:
1. Restore control file from backup
2. or have a script from a previously generated “backup controlfile to trace script”, i.e., when your control file was good. In many cases you may not be able to generate the script now if you get any error and does not have any backup of control file, then the only option is to go for a manual listing of files method mentioned below.
3. or you must create a script from the beginning, by manually listing of all the files, which is risky if you miss out any files, so carefully list all the files.
SQL> CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS ARCHIVELOG;
Follow the format listing:
– Location of redo logs.
– Location of datafiles
– Specifying the characterset.
Once you have listed all files correctly you are ready to recreate your controlfile:
SQL> startup nomount; SQL>@control.sql