Things to Consider Before Recreating the Controlfile

Things to Consider Before Recreating the Controlfile 

Highlight the importance of the controlfile and issues you need to consider before recreating it.

TROUBLESHOOTING STEPS

Before Recreating The Controlfile...

The controlfile is very important to the database. Some information is stored only in the controlfile rather than the data dictionary. The metadata includes flashback logs, block change tracking, RMAN backups, and datafile locations. Often there are work-arounds or solutions available and the controlfile does not need to be recreated at all.

If you really must recreate the controlfile, 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 controlfile 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 backuppieces into the controlfile. Further, prior to 9i, we cannot catalog backuppieces anyway.

The controlfile is important to a backup and restore process. It is true that you can catalog all backuppieces back into the controlfile, but you must have a controlfile to use in the first place. If you've lost your entire system and only have backuppieces to use, you must have at least a controlfile to catalog these backuppieces. 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;

Recommendations

1) multiplex your controlfile in different storage

You should have at least 2 copies of a controlfile, on different storage. If media is damaged, you will at least have another copy to use.
So the database parameter file (pfile or spfile) should point to at least 2 locations:
control_files='location1/file1','location2/file2'

2) turn on autobackup
This will ensure that a controlfile backup is taken at the end of every backup:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Change the location if required:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default