RMAN Recovery Catalog

This article gives the steps to create the recovery catalog.

1. Identify the database where catalog needs to be created.

Here I am using rcvcat as the catalog database

2. Create the tablespace to store the catalog information.

SQL> CREATE TABLESPACE CATALOG_DATA
DATAFILE '/u01/oradata/rvcvat/catalog_data_01.dbf'
SIZE 500M;

3. Create the user that is going to be the catalog owner

SQL> CREATE USER RMAN
IDENTIFIED BY RMAN
DEFAULT TABLESPACE CATALOG_DATA
TEMPORARY TABLESPACE TEMP;

4. Grant the roles and the privileges

SQL> GRANT RESOURCE TO RMAN;
SQL> GRANT CONNECT TO RMAN;

SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;

SQL> ALTER USER RMAN DEFAULT ROLE ALL;
SQL> ALTER USER RMAN QUOTA UNLIMITED ON RMAN_DATA;

5. Create the catalog

[oracle@naikh]$ rman catalog rman/rman@rvccat
RMAN> create catalog;

6. Register the target database

[oracle@naikh]$ export ORACLE_SID=orcl

[oracle@naikh]$ rman target / catalog rman/rman@rvccat

RMAN> register database;

Restore Spfile and Controlfile

Different ways of restoring spfile and controlfile

1. Restore SPFILE

restore spfile to '/bkp/spfile.ora';

restore spfile to '/bkp/spfile.ora' from autobackup;

restore spfile to '/bkp/spfile.ora' from tag=man_bkp;

restore spfile to '/bkp/spfile.ora' from /home/oracle/bkp/c-543432-24342-01 ;

restore spfile to '/bkp/spfile.ora' until time 'sysdate-31;

2. Restore Controlfile

restore controlfile to '/bkp/controlfile.ctl';

restore controlfile to '/bkp/controlfile.ctl' from autobackup;

restore controlfile to '/bkp/controlfile.ctl' from tag=man_bkp;

restore controlfile to '/bkp/controlfile.ctl' from /bkp/c-543432-24342-01 ;

restore controlfile to '/bkp/controlfile.ctl' until time 'sysdate-31;

RMAN Backup Restoration Drill

This post explains the procedure to test the RMAN backups on a test box. Here I am doing only partial restoration (not full database) due to space constraints.

1. Copy the pfile from prod box to test box and edit it.
$ scp init.ora oracle@TestBox:/restore_path

2. Create password file
$ orapwd file=orapw password=oracle

3. Create necessary folders in /restore_path
$ cd /restore_path
$ mkdir adump bdump cdump udump redo data archive

4. Restore the control file
$ export ORACLE_SID=
$ sqlplus "/ as sysdba"
sql> startup nomount pfile=/restore_path/init.ora
sql> exit
$ rman target /
rman> run {
allocate channel c1 type disk;
restore controlfile from '/restore_path/rman_controlfile_bkp.ctl';
}

5. Catalog all the backup files
rman> alter database mount;
rman> catalog backuppiece '/restore_path/ORADB_rman_full_bkp_s5_p1;
rman> catalog backuppiece '/restore_path/ORADB_rman_full_bkp_s4_p2;
rman> catalog backuppiece '/restore_path/ORADB_rman_full_bkp_s3_p3;
...

6. Restore selected tablespace
rman> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
set newname for datafile 1 to '/restore_path/data/system01.dbf';
set newname for datafile 3 to '/restore_path/data/sysaux01.dbf';
set newname for datafile 42 to '/restore_path/data/audit_data01.dbf';
set newname for datafile 2 to '/restore_path/data/audit_data.dbf';
set newname for datafile 10 to '/restore_path/data/undotbs01.dbf';
restore tablespace system;
restore tablespace syasaux;
restore tablespace undotbs01;
restore tablespace audit_data;
switch datafile all;
}

7. Drop all the remaining datafiles which are not included in restore drill
alter database datafile 5 offline off;
alter database datafile 15 offline off;
alter database datafile 25 offline off;
alter database datafile 35 offline off;
.....

8. Recover database
$ export ORACLE_SID=
$ sqlplus "/ as sysdba"
sql> recover database using backup contrlfile until cancel;

9. Restore archive logs
$ rman target /
rman> run {
set archivelog destination to '/restore_path/archive/';
allocate channel c1 type disk;
allocate channel c2 type disk;
restore archivelog from sequence 2503 until sequence 2150;
}

9.1 For RAC
restore archivelog from sequence 2503 until sequence 2150 thread=1;

restore archivelog from sequence 2503 until sequence 2150 thread=2;

10. Rename Redo log files
sql> alter database rename file '/prod_path/redo/reado01.log' to
'/restore_path/redo/reado01.log';

11. Cleare all redo log groups
sql> alter database cleare logfile group 1;
sql> alter database cleare logfile group 2;
sql> alter database cleare logfile group 3;
.....

12. Open the database with openrestlog option
sql> alter database open resetlogs;

13. Query the database
sql> select name,open_mode,log_mode,database_role from v$database;

14. Drop the database
sql> shutdown abort;
sql> startup nomount;
sql> alter database mount exclusive;
sql> alter system enable restricted session;
sql> drop database;

15. Drop all the directories created for restoration
$ rmdir adump bdump cdump udump redo data archive

RMAN Resoter and Recover

1. The whole database
RMAN> startup force mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

2. Tablespace
RMAN> sql 'alter tablespace tbs1 offline';
RMAN> restore tablespace tbs1;
RMAN> recover tablespace tbs1;
RMAN> sql 'alter tablespace tbs1 online';

3. Datafile
RMAN> sql 'alter database datafile 5 offline';
RMAN> restore datafile 5;
RMAN> recover datafile 5;
RMAN> sql 'alter database datafile 5 online';

4. Block of a Datafile
RMAN> blockrecover datafile 5 block 126;