Sunday, November 17, 2013

Disater recovery using rman with same directory structure

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;

No comments:

Post a Comment