RMAN TIP: Avoiding ORA-27211

I'm using RMAN to backup only to disk. Usualy when I have to delete obsolete backup I run command:

delete obsolete device type disk;

It is anoying to type device type disk every time, but

delete obsolete;

causes:

RMAN-03002: failure of delete command at 09/12/2005 08:44:27
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

The reason is that some time ago I was testing backups to tape. There is simple solution:

Configure CHANNEL device type 'SBT_TAPE' clear;

It will remove information about tape device configuration, so all commands will use default device: disk.

Cheers, Paweł

Restore CONTROLFILE and/or SPFILE from autobackup

As I mentioned earlier in post about setting up autobackup, its time to describe how to restore CONTROLFILE and SPFILE from autobackups. If your database is still running and what You want is just get historical version of SPFILE or CONTROLFILE then it is easy task. Just start RMAN:

RMAN TARGET=/

And enter command to restore CONTROLFILE:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 01-FEB-07

The until time clause sets time of validity for CONTROLFILE. By default RAM looks for autobackups for seven days into past starting from that time. It can happen that for several days there were no changes, and autobackup was not invoked. In that case add MAXDAYS clause:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup maxdays 100;

It will force RMAN to look for copies for more days into past. Similarly You can restore SPFILE:

RMAN> restore until time 'sysdate-3'
2> SPFILE to 'c:\temp\SPFILE' from autobackup maxdays 100;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 01-FEB-07

After restoring SPFILE You can convert it to PFILE, so You can easily read settings:

RMAN> sql "create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''";
sql statement: create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''

Now more complex scenario: You have lost all database files including CONTROLFILE and SPFILE. You only have backups created by RMAN. In that case You have to eventually install Database Software. Then setup environment variables and start recovery. On Windows platform You have to create Service to be able to startup oracle. You probably remember instance name and SID. If You don't it can be usually found in some TNSNAMES files on client machines. But You probably do not know DBID. You can easily find DBID – it'is part of the name of autobackup file. Details in my previous post. So if autobackup file name is 'c-669001291-20070129-03' then DBID is 669001291. Now we can start recovery:

RMAN target=/

At the beginning set Database ID:

RMAN> SET DBID 669001291;

Then startup instance:

RMAN> startup force nomount;

and run recovery of SPFILE

RMAN> RUN {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE SPFILE FROM AUTOBACKUP; }

I suggest also creating PFILE from SPFILE. Then You should check if all file locations are correct, all directories exists and Oracle have rights to write in them.

RMAN> shutdown;
RMAN> startup force nomount;
RMAN> RUN { 
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE CONTROLFILE FROM AUTOBACKUP; }

Now You can continue recovery of datafiles.

Paweł

Autobackup of SPFILE and CONTROLFILE

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

RMAN connection to Catalog - updated

I must say that I was completely wrong in my post RMAN connection to Catalog. In fact when connection to Catalog fails, RMAN quits and rest of script is ignored. So I've changed this. RMAN connect only to Target Database during backup process, and after that RMAN is run second time. This time it connects to Target Database and Catalog Database, then run command RESYNC CATALOG to force synchronization of control file with Catalog. It should work now.

Cheers, Paweł

RMAN connection to Catalog

Today my nightly backups were not taken. I had an OS script that started backup using this command:

rman target=sysop/password@targetdb catalog=rman/password@catdb 
      log=rman.log append @rman.txt

The reason was that network was down and there was no connection to catdb. It leaded to error and RMAN did not start at all. So I’ve changed my command line to:

rman target=sysop/password@targetdb 
      log=rman.log append @rman.txt

And added a line in rman.txt

connect catalog rman/password@catdb;

Next time there will be no connection to catdb backup will be done anyway. It will be not registered in catalog only. Catalog will be synchronized on next backup procedure or next time you connect to target and catalog an issue any command that operate on catalog.

Cheers, Paweł