Tuesday, September 18, 2007

Configure Controlfile Autobackup’s

Hi,

You can configure automatic backup of your control file and server parameter file (SPFILE) using RMAN. RMAN backs up control file and SPFILE (if used to start the database) in one of two situations:

1) when a successful backup must be recorded in the RMAN repository, and 2) when a structural change to the database affects the contents of the control file which therefore must be backed up. Structural changes to database include: adding a new tablespace, altering the state of a tablespace or datafile, renaming a file, and so on.

The server process is responsible for performing control file autobackup when there is any structural change.

By default, control file autobackups are turned off. Control file autobackups can be turned on using the following command in RMAN:

C:\> rman target /

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

We may set the directory and format of backup files using the following RMAN command:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\db10g\cfbackup\db10g_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\db10g\cfbackup\db10g_%F';
new RMAN configuration parameters are successfully stored

All autobackup formats must include %F variable. %F expands to “C-XXXXXXXXX-YYYYMMDD-NN”, where:

XXXXXXXXX – database id

YYYYMMDD – day, when backed up

NN – change number during day, starts with 00, and represented in hexadecimal

At this point, my “C:\db10g\cfbackup”directory is empty:

C:\db10g\cfbackup>dir
Volume in drive C is SYSTEM
Volume Serial Number is AC21-0462

Directory of C:\db10g\cfbackup

18/09/2007 11:02 AM .
18/09/2007 11:02 AM ..
0 File(s) 0 bytes
2 Dir(s) 4,451,540,992 bytes free

To test the control file autobackup, I will add a new tablespace:

SQL> create tablespace dropme datafile 'C:\db10g\cfbackup\dropme.dbf' size 10m;

Tablespace created.

The above command should trigger an autobackup of controlfile, let me see if any file is created in “C:\db10g\cfbackup” folder:

C:\db10g\cfbackup>dir
Volume in drive C is SYSTEM
Volume Serial Number is AC21-0462

Directory of C:\db10g\cfbackup

18/09/2007 12:19 PM.
18/09/2007 12:19 PM..
18/09/2007 12:19 PM 7,340,032 DB10G_C-15597741-20070918-02
18/09/2007 12:19 PM 10,493,952 DROPME.DBF
2 File(s) 17,833,984 bytes
2 Dir(s) 4,433,707,008 bytes free

As we may see, a file "DB10G_C-15597741-20070918-02" is created. This is the autobackup of control file and SPFILE performed by the server process. At the same time, the database writes a message containing the complete path of the backup piece and device type to the alert log.

Tue Sep 18 12:19:37 2007
create tablespace dropme datafile 'C:\db10g\cfbackup\dropme.dbf' size 10m
Tue Sep 18 12:19:37 2007
Starting control autobackup
Control autobackup written to DISK device
handle 'C:\DB10G\CFBACKUP\DB10G_C-15597741-20070918-02'
Completed: create tablespace dropme datafile 'C:\db10g\cfbackup\dropme.dbf' size 10m

Control file autobackups can be turned off anytime using “CONFIGURE CONTROLFILE AUTOBACKUP OFF” command in RMAN.

Conclusion: With a control file autobackup, RMAN can recover database even if the current control file, recovery catalog, and server parameter file are inaccessible.

Regards

2 comments:

Tushar said...

Could you please tell how a server process takes backup of control file? As per my understanding a server process is created only when a session is established.

Asif Momen said...

Your understanding is correct, a server process is started when a session is established.

If you make any structural changes (eg add a new tablespace) in this session, then the same established server process will take a backup of your control file and SPFILE.