Software Engineer Thoughts

Even if you do not use RMAN to take backups of your database, you might find it useful to take snapshots of your SPFILE and CONTROLFILE automatically. You can configure Recovery Manager (RMAN) to automatically create copies of CONTROLFILE and SPFILE when you make changes to database such as:

• adding/dropping a new tablespace or datafile
• altering the state of a tablespace or datafile (online/offline/read only)
• adding a new online redo log,
• renaming a file
• adding a new redo thread
• and many more...

To setup autobackup just start RMAN and connect to database

RMAN target=/

or

RMAN target=sys/password@db

set directory and format of backup files:

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

In name of autbackup file you have to use %F. It will be expanded to C-XXXXXXXXX-YYYYMMDD-NN, where:

• XXXXXXXXX – database id
• YYYYMMDD – day, when backuped
• NN – change number during day, starts with 00, and represented in hexadecimal

Activate autobackups:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Now autobackups are active, and every time you change database structure controlfile will be automatically backed up.
If you use RMAN for backups it will create copy of SPFILE or CONTROLFILE also after each backup operation.
I've tested it on Oracle 10g (10.2) but it should work in Oracle since version 8i. Next time i'll write how to restore SPFILE or CONTROLFILE from these backup.

Cheers, Paweł

Update: How to restore CONTROLFILE and SPFILE from RMAN autobackups

Anonymous said...

you said: "it will create copy of SPFILE"
but where is it?

19 June, 2011 18:02

Paweł Barut said...

In the directory you have configured using CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ...

/Pawel

20 June, 2011 10:22

Anonymous said...

My RMAN configure:
configure controlfile autobackup format for device type disk to 'c:\backup\controlfile\%F'
but in this directory after i run backup database statement, there is only controlfile.

20 June, 2011 16:17

Paweł Barut said...

The file that was created contain backup of control file and spfile.

Check this using RMAN:
restore spfile to 'C:\newlocation\spfile_copy.ora' from autobackup;

/Pawel

20 June, 2011 21:49