RMAN Scripts to Create Backup Pieces to Multiple Disk LOcations and delete obsolete

In order to take backup of datafiles and archivelogs to more than one locations below scripts can be used:

run {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/data01/oradata/RMAN_BACKUP/LOC1/%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/data02/oradata/RMAN_BACKUP/LOC2/%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/data02/oradata/RMAN_BACKUP/LOC2/%F';
backup incremental level 0 database;
release channel disk1;
release channel disk2;

sql 'alter system archive log current';
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/data01/oradata/RMAN_BACKUP/LOC1/LOG_%t_%s_%p_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/data02/oradata/RMAN_BACKUP/LOC2/LOG_%t_%s_%p_%U';
backup archivelog all DELETE INPUT;
release channel disk1;
release channel disk2;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
}


We have faced below error while "delete obsolete":

RMAN> delete obsolete
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

Problem was, I was trying to delete backups, which are on both disk and tape, so we need to allocate proper channel tape/disk:

Tape:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
crosscheck backupset;
delete obsolete;
}

run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
crosscheck backupset;
delete noprompt backup of archivelog until time 'sysdate-7';
}


Disk:

run {
allocate channel 'dev_0' type disk;
crosscheck backupset;
delete obsolete;
}

run {
allocate channel 'dev_0' type disk;
crosscheck backupset;
delete noprompt backup of archivelog until time 'sysdate-7';
}

Oracle Database Disaster Recovery using Disk Based RMAN Backup

Scenario:
- Database name is MYDB
- Production [source] machine is completely out of order and inaccessible
- Have scheduled RMAN Backup on remote storage disks
- RMAN Backups are taken with "CONTROLFILE AUTOBACKUP ON;" configuration of RMAN
[if "CONTROLFILE AUTOBACKUP OFF;" then we need to create a pfile manually]
- All the needed RMAN backup pieces have been identified
- Oracle SW on the target machine has been installed
- Below two mount points have been mounted on the target machine and on which RMAN backup pieces are kept:
/data01/oradata/RMAN_RESTORE/LOC1
/data02/oradata/RMAN_RESTORE/LOC2
- Latest AUTOBACKUPed CONTROLFILE has been identified [ c-[DBID]-[YYYYMMDD]-[2 digit HEX_SEQUENCE for the day],expl:c-2169732094-20110614-0d ] and kept on /data02/oradata/RMAN_RESTORE/LOC2
- Now have to restore and recover the MYDB database
- For all the steps below, please export ORACLE_HOME & ORACLE_SID properly and use oracle user

[If you are on windows create a oracle DB service for MYDB as below on new machine before proceeding:
Please run the "Command Prompt" right click and "run as administrator" if current user is not "administrator"
oradim -new -sid MYDB -startmode m
]

1. Restore the spfile:
$ export ORACLE_SID=MYDB
rman> startup nomount
rman> restore spfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';

- In above command "c-2169732094-20110614-0d" is the latest autobackuped controlfile.
- Spfile is now restored in the default location [$ORACLE_HOME/dbs/spfileMYDB].
- We may need to edit the spfile to create pfile file, because some some parameter may needed to be chaged an startup with pfile

rman> shutdown immediate
$ rman target /
rman> startup nomount [or startup nomount pfile='PFILE_LOCATION']


2. Restore controlfile and mount the database

- To avoid below error we need to create related directory locations or change control file location:
ORA-19504: failed to create file "/data01/oradata/MYDB/cntl/control02.ctl"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

rman> restore controlfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';

- On above, this is the same file from which spfile has been restored.

rman> startup mount [or force]

3. Check needed configuration

- Check latest scn as no redo log file available
SQL> select group#, first_change#, status, archived from v$log order by FIRST_CHANGE# desc;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 44958649 CURRENT NO ---<<<< this is the target SCN
3 44958631 ACTIVE YES
2 44958342 ACTIVE YES

- optionally plan for change the datafile location
- use below command to find current datafile location then plan for new location:
rman> REPORT SCHEMA;
-we can use below set of commands to crosscheck whether we have all the needed backup pieces:
RMAN> list backup;
RMAN> list backup of database completed after [date];
RMAN> list backup of database completed between [start date] and [end date];

4. Register the calalog location [to identify the backup pieces from the disk locations]
- for each location where RMAN backup piece is kept please run " catalog start with" as below:

rman> catalog start with '/data01/oradata/RMAN_RESTORE/LOC1';
rman> catalog start with '/data02/oradata/RMAN_RESTORE/LOC2';


5. restore the database
- For similar location of the datafiles & tempfiles use below command:
rman> restore database;

- For changed locations [step 3.] of the datafiles & tempfiles use below command canbe used:
rman> run {
SET NEWNAME FOR DATAFILE 1 TO '[new location for datafile 1]';
SET NEWNAME FOR DATAFILE 5 TO '[new location for datafile 5]';
SET NEWNAME FOR DATAFILE 15 TO '[new location for datafile 15]';
SET NEWNAME FOR TEMPFILE 1 TO '[new location for tempfile 1]';
allocate channel 'dev_0' type disk;
restore database;
SWITCH DATAFILE ALL;
release channel 'dev_0';
}

6. Recover and open the database to the SCN identified at step 3:
rman> recover database until scn 44958649;
rman> alter database open resetlogs;

7. create a password file and give an entry in oratab file
$ orapwd file=$ORACLE_HOME/dbs/orapwMYDB password=password entries=5
[ for windows:$ orapwd file=$ORACLE_HOME/database/orapwMYDB password=password entries=5 ]
$ vi /etc/oratab
MYDB:/oracle/orabase/product/11.2.0/dbhome_1:N


Relater links:
http://web.njit.edu/info/oracle/DOC/backup.102/b14191/rcmcatdb002.htm
http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ
http://repettas.wordpress.com/2008/11/01/rman-restore-and-recover-of-a-database-when-the-repository-and-spfileinitora-files-are-lost/