Disater recovery implemented to validate the backups or to recover all datafiles, control file, redolog file, spfile if they are lost
Source Database name: jatway
Clone Database name: jatway (using the source database backup with same directory structure)
Step 1: Taking backup from the source database "jatway"
[oracle@sfo ~]$ . oraenv
ORACLE_SID = [11gR2_NEW] ? jatway
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@sfo ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 16:04:10 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1235959808 bytes
Fixed Size 2212896 bytes
Variable Size 1056967648 bytes
Database Buffers 167772160 bytes
Redo Buffers 9007104 bytes
Database mounted.
Database opened.
SQL> select name from v$database;
NAME
---------
JATWAY
Step 2: Make a note of the directory stucture from the source database. This same directory structure needs to be built in the new
server or clone location for this example.
(Note: If there is no chance to create the directory structure in the new server, we can also use "link method" to maintain the same
structure of the clone database)
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 3882M
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jetway/jatway/redo03.log
/u01/app/oracle/oradata/jetway/jatway/redo02.log
/u01/app/oracle/oradata/jetway/jatway/redo01.log
/u01/app/oracle/oradata/jetway/jatway/redo04.log
/u01/app/oracle/oradata/jetway/jatway/redo05.log
/u01/app/oracle/oradata/jetway/jatway/redo06.log
/u01/app/oracle/oradata/jetway/jatway/redo07.log
7 rows selected.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jetway/jatway/control01.ctl
/u01/app/oracle/flash_recovery_area/jatway/control02.ctl
find the location of tracefile
SQL>show parameter dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/jat
way/jatway/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/jat
way/jatway/cdump
user_dump_dest string /u01/app/oracle/diag/rdbms/jat
way/jatway/trace
SQL> show parameter dump_destaudit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/jatway/a
dump
SQL> show parameter diagnostic
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle
Step 3: Log into the user Tom and create table name "c" and insert value into it
SQL>conn tom/tom
connected
SQL> create table c(id number);
Table created.
SQL> insert into c values(4);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
..
SQL> select count(1) from c;
COUNT(1)
----------
5
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data
Mining and Real Application Testing options
Step 4: Backup the source database using RMAN
[oracle@sfo ~]$ . oraenv
ORACLE_SID = [jatway] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@sfo ~]$ rman target=/
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 25 17:01:47 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: JATWAY (DBID=3196377116)
a) Configure the backup location where the backup needs to be stored
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/oradata/jetway/jatway/backup/%U';
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/oradata/jetway/jatway/backup/%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/oradata/jetway/jatway/backup/%U';
new RMAN configuration parameters are successfully stored
b) Enable the control file autobackup so that it will backup the control file as well spfile in the backup piece
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
c) Verify the configuration settings of the RMAN
RMAN> show all;
RMAN configuration parameters for database with db_unique_name JATWAY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/oradata/jetway/jatway/backup/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_jatway.f'; # default
d) Backup the whole database with archivelog
RMAN> backup database plus archivelog;
Starting backup at 25-SEP-12
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=29 STAMP=794580386
input archived log thread=1 sequence=31 RECID=30 STAMP=794580651
input archived log thread=1 sequence=32 RECID=31 STAMP=794581003
input archived log thread=1 sequence=33 RECID=32 STAMP=794581473
input archived log thread=1 sequence=34 RECID=33 STAMP=794593512
input archived log thread=1 sequence=35 RECID=34 STAMP=794593515
input archived log thread=1 sequence=36 RECID=35 STAMP=794595461
input archived log thread=1 sequence=37 RECID=36 STAMP=794595464
input archived log thread=1 sequence=38 RECID=37 STAMP=794620809
input archived log thread=1 sequence=39 RECID=38 STAMP=794649631
input archived log thread=1 sequence=40 RECID=39 STAMP=794671245
input archived log thread=1 sequence=41 RECID=40 STAMP=794689121
input archived log thread=1 sequence=42 RECID=41 STAMP=794912453
input archived log thread=1 sequence=43 RECID=42 STAMP=794921073
input archived log thread=1 sequence=44 RECID=43 STAMP=794933728
input archived log thread=1 sequence=45 RECID=44 STAMP=794933729
input archived log thread=1 sequence=46 RECID=45 STAMP=794937869
input archived log thread=1 sequence=47 RECID=46 STAMP=794937872
input archived log thread=1 sequence=48 RECID=47 STAMP=794941697
channel ORA_DISK_1: starting piece 1 at 25-SEP-12
channel ORA_DISK_1: finished piece 1 at 25-SEP-12
piece handle=/u01/app/oracle/oradata/jetway/jatway/backup/11nm3m83_1_1 tag=TAG20120925T170818 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 25-SEP-12
Starting backup at 25-SEP-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/jetway/jatway/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/jetway/jatway/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-SEP-12
channel ORA_DISK_1: finished piece 1 at 25-SEP-12
piece handle=/u01/app/oracle/oradata/jetway/jatway/backup/12nm3m96_1_1 tag=TAG20120925T170854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
Finished backup at 25-SEP-12
Starting backup at 25-SEP-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=49 RECID=48 STAMP=794941810
channel ORA_DISK_1: starting piece 1 at 25-SEP-12
channel ORA_DISK_1: finished piece 1 at 25-SEP-12
piece handle=/u01/app/oracle/oradata/jetway/jatway/backup/13nm3mbj_1_1 tag=TAG20120925T171011 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-SEP-12
Starting Control File and SPFILE Autobackup at 25-SEP-12
piece handle=/u01/app/oracle/flash_recovery_area/JATWAY/autobackup/2012_09_25/o1_mf_s_794941814_864kz6qs_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-SEP-12
Step 5: Now the backups are stored in "/u01/app/oracle/oradata/jetway/jatway/backup/". Zip and TAR the backup folder and move across
the new server (Imagine that these backups now reside in the storage medium. To perform disaster recovery, the backup needs to be
brought from storage to the new server)
[oracle@sfo jatway]$ tar -cvzf backup.tar.gz backup
backup/
backup/o1_mf_s_794941814_864kz6qs_.bkp
backup/11nm3m83_1_1
backup/13nm3mbj_1_1
backup/12nm3m96_1_1
[oracle@sfo jatway]$ scp backup.tar.gz oracle@192.168.168.1:/u01/app/oracle/oradata/
oracle@192.168.168.1's password:
backup.tar.gz
100% 271MB 22.6MB/s 00:12
[oracle@sfo jatway]$
Step 6: This is the new server (Imagine that the RMAN backup has been downloaded from the storage medium to the new server). Extract
the zipped file.
[oracle@nyc oradata]$ mkdir jatway
[oracle@nyc jatway]$ tar -xvzf backup.tar.gz
backup/
backup/o1_mf_s_794941814_864kz6qs_.bkp
backup/11nm3m83_1_1
backup/13nm3mbj_1_1
backup/12nm3m96_1_1
All the steps below need to be performed in the new server (clone location)
Step 7: Create the required directory structure based on the new server location. Because in this example we are following the same
directory structure as the source database environment
(Based on the information collected from step 1, we have creating the directory structure in the new server)
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/oradata/jetway/jatway/
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/diag/rdbms/jatway/jatway/trace
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/diag/rdbms/jatway/jatway/cdump
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/diag/rdbms/jatway/jatway/trace
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/admin/jatway/adump
[oracle@nyc backup]$ mkdir -p /u01/app/oracle/flash_recovery_area/jatway/
Step 8: Restore and recover the database from the RMAN backup.
Now we will perform disaster recovery with same name of the database and directory structure of source database.
In the new server, adding the database name entry in /etc/oratab
Add database entry in the /etc/oratab
[oracle@nyc jatway]$ vi /etc/oratab
jatway:/u01/app/oracle/product/11.2.0/db_1:N
[oracle@nyc jatway]$ export ORACLE_SID=jatway
[oracle@nyc jatway]$ rman target=/
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 25 23:17:30 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
Step 9: Login to the RMAN prompt and perform "fake nomount"
The below command performs fake nomount of the database
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initjatway.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2211448 bytes
Variable Size 88080776 bytes
Database Buffers 62914560 bytes
Redo Buffers 5455872 bytes
Step 10: The below command creates the spfile from the backup taken using rman.
(same backup piece "o1_mf_s_794941814_864kz6qs_.bkp" will be used to restore spfile as well as control file)
RMAN> restore spfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
Starting restore at 25-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 25-SEP-12
RMAN> shutdown immediate;
(Note: Once the spfile restore successfully completed, we can get the copy of the spfile in $ORACLE_HOME/dbs)
[oracle@nyc backup]$ cd $ORACLE_HOME/dbs
[oracle@nyc dbs]$ ls
spfilejatway.ora
Step 11: Bring the database to actual nomount stage
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1235959808 bytes
Fixed Size 2212896 bytes
Variable Size 1056967648 bytes
Database Buffers 167772160 bytes
Redo Buffers 9007104 bytes
Step 12: Create the control file using the same backup piece which was used to recover spfile, since this backup has control file as
well as spfile information .
RMAN> restore controlfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
Starting restore at 26-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/jetway/jatway/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/jatway/control02.ctl
Finished restore at 26-SEP-12
Step 13: Mount the database with force option.
RMAN> startup force mount;
Oracle instance started
database mounted
Total System Global Area 1235959808 bytes
Fixed Size 2212896 bytes
Variable Size 1056967648 bytes
Database Buffers 167772160 bytes
Redo Buffers 9007104 bytes
Step 14: When we try to "restore" the database it will "throw an error" because the backup piece information is not updated in RMAN
catalog.
RMAN> restore database;
Starting restore at 26-SEP-12
Starting implicit crosscheck backup at 26-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 26-SEP-12
Starting implicit crosscheck copy at 26-SEP-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-SEP-12
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/26/2012 00:50:27
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
restore command fails("no files are catalog") because rman not able to find backup file using rman repository
Step 15: The below command helps the RMAN to understand the backup piece information in the specified location and the backup piece
information will be cataloged. We need to confirm ("YES") inorder to allow the files to be cataloged.
RMAN> catalog start with '/u01/app/oracle/oradata/jatway/backup';
searching for all files that match the pattern /u01/app/oracle/oradata/jatway/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/oradata/jatway/backup.tar.gz
File Name: /u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp
File Name: /u01/app/oracle/oradata/jatway/backup/11nm3m83_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/13nm3mbj_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/12nm3m96_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/exit
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp
File Name: /u01/app/oracle/oradata/jatway/backup/11nm3m83_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/13nm3mbj_1_1
File Name: /u01/app/oracle/oradata/jatway/backup/12nm3m96_1_1
Step 16: Now rman backup pieces are already cataloged and so the "restore database" command will work without error.
and recover database.
RMAN>recover database;
RMAN>recover database;
(Note: Sometimes, after applying all archivelog files, the command looks for archivelogs that are not yet generated and so it will
throw an error. This error can be ignored)
Step 17: Open the database with resetlog and check the values in the database
RMAN>alter database open resetlog;
database opened
check the values in the database.
SQL>conn tom/tom
connected
SQL> select count(1) from c;
COUNT(1)
----------
5
OR
We can use diaster recovery script instead of manually performing "Step 9 to Step 17"
(Disaser recovery script)
run
{
startup nomount;
restore spfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
startup force nomount;
restore controlfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
startup force mount;
catalog start with 'catalog start with '/u01/app/oracle/oradata/jatway/backup';
restore database;
recover database;
}
just copy and paste the line in rman prompt: it will perform disaster recovery by itself.
RMAN>startup nomount;
restore spfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
startup force nomount;
restore controlfile from '/u01/app/oracle/oradata/jatway/backup/o1_mf_s_794941814_864kz6qs_.bkp';
startup force mount;
catalog start with 'catalog start with '/u01/app/oracle/oradata/jatway/backup';
restore database;
recover database;
