Standby's For Primary Site Backups ??
In this post I will show how one can leverage the standby site for off-loading backup jobs from primary. Thus freeing up primary for other critical jobs and also improving the performance of the primary site.On Primary Site -
[oracle@rac1e ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 24 07:43:40 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DGTEST (DBID=3796352795)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TROY
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +REPORTING_DATA/troy/datafile/system.262.839828803
2 720 SYSAUX *** +REPORTING_DATA/troy/datafile/sysaux.276.839828805
3 35 UNDOTBS1 *** +REPORTING_DATA/troy/datafile/undotbs1.277.839828809
4 5 USERS *** +REPORTING_DATA/troy/datafile/users.278.839828815
5 25 UNDOTBS2 *** +REPORTING_DATA/troy/datafile/undotbs2.283.839829369
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 25 TEMP 32767 +REPORTING_DATA/troy/tempfile/temp.282.839829279
On Standby Site -
[oracle@stby2 HS]$ rman target /
connected to target database: DGTEST (DBID=3796352795, not open)
RMAN> report schema;
using target database control file instead of recovery catalog
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name HOFFMAN
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/hoffman/datafile/system.293.839922187
2 720 SYSAUX *** +DATA/hoffman/datafile/sysaux.290.839922187
3 35 UNDOTBS1 *** +DATA/hoffman/datafile/undotbs1.288.839922187
4 5 USERS *** +DATA/hoffman/datafile/users.286.839922187
5 25 UNDOTBS2 *** +DATA/hoffman/datafile/undotbs2.287.839922187
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 25 TEMP 32767 +DATA/hoffman/tempfile/temp.279.840090943
On standby -
RMAN> backup as copy datafile 1 format '/home/oracle/HS/%U.rman';
RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 80.00K DISK 00:00:00 24-FEB-14
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20140224T065328
Piece Name: /home/oracle/HS/0ap1dfgc_1_1.rman
SPFILE Included: Modification time: 24-FEB-14
SPFILE db_unique_name: HOFFMAN
On primary -
delete the system datafile
ASMCMD [+reporting_data/troy/datafile] > cp SYSTEM.262.839828803 SYSTEM.262.839828803.bak
copying +reporting_data/troy/datafile/SYSTEM.262.839828803 -> +reporting_data/troy/datafile/SYSTEM.262.839828803.bak
ASMCMD [+reporting_data/troy/datafile] > rm SYSTEM.262.839828803
#while starting up hit the following error -
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+REPORTING_DATA/troy/datafile/system.262.839828803'
#NOW COPY THE BACKUP FILE FROM STANDBY TO PRIMARY .....
#SINCE THE PRIMARY IS NOT AWARE OF THIS BACKUP ONE NEED TO CATALOG IT AS FOLLOWS
On Primary -
RMAN> catalog start with '/home/oracle/HS/';
searching for all files that match the pattern /home/oracle/HS/
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/HS/0ap1dfgc_1_1.rman
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/HS/0ap1dfgc_1_1.rman
RMAN> restore datafile 1;
Starting restore at 24-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=1 STAMP=840356556 file name=+REPORTING_DATA/troy/datafile/system.262.840356549
destination for restore of datafile 00001: +REPORTING_DATA/troy/datafile/system.262.839828803
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=+REPORTING_DATA/troy/datafile/system.288.840356741
RECID=0 STAMP=0
Finished restore at 24-FEB-14
# So recovery finishes successfully. Now one needs to recover the datafile using the archive logs already available on primary site.
RMAN> recover datafile 1 ;
Starting recover at 24-FEB-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-FEB-14
RMAN> alter database open ;
SQL> select database_role, open_mode, SWITCHOVER_STATUS from gv$database;
DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
---------------- -------------------- --------------------
PRIMARY READ WRITE TO STANDBY
SQL> select current_scn from gv$database;
CURRENT_SCN
-----------
1682907
CHECK SCN ON STANDBY -
SQL> select current_scn from gv$database /
CURRENT_SCN
-----------
1682437
SCENARIO II -
In this scenario, one can restore the whole database backup taken on standby site on primary site.
ON STANDBY -
# Take full database backup on standby site as follows
RMAN> backup database tag 'full_standby_copy' format '/home/oracle/HS/%U.rman';
Starting backup at 24-FEB-14
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=00002 name=+DATA/hoffman/datafile/sysaux.290.839922187
input datafile file number=00001 name=+DATA/hoffman/datafile/system.293.839922187
input datafile file number=00003 name=+DATA/hoffman/datafile/undotbs1.288.839922187
input datafile file number=00005 name=+DATA/hoffman/datafile/undotbs2.287.839922187
input datafile file number=00004 name=+DATA/hoffman/datafile/users.286.839922187
channel ORA_DISK_1: starting piece 1 at 24-FEB-14
channel ORA_DISK_1: finished piece 1 at 24-FEB-14
piece handle=/home/oracle/HS/0fp1diba_1_1.rman tag=FULL_STANDBY_COPY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-FEB-14
channel ORA_DISK_1: finished piece 1 at 24-FEB-14
piece handle=/home/oracle/HS/0gp1dibp_1_1.rman tag=FULL_STANDBY_COPY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-FEB-14
# and then catalog the backup files on primary.
On Primary -
# Copy the backup on primary server. Once the backup is copied to the primary site, one needs to catalog it with primary control file as primary control file may not have this backup information.
RMAN> catalog start with '/home/oracle/HS';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/HS
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/HS/0fp1diba_1_1.rman
File Name: /home/oracle/HS/0gp1dibp_1_1.rman
File Name: /home/oracle/HS/0ep1dial_1_1.rman
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/HS/0fp1diba_1_1.rman
File Name: /home/oracle/HS/0gp1dibp_1_1.rman
File Name: /home/oracle/HS/0ep1dial_1_1.rman
# NOW SIMULATE THE CRASH BY DELETING THE DATAFILES. REMEMBER WE ARE NOT TAKING CONTROLFILE BACKUP FROM STANDBY AS ITS STANDBY CONTROLFILE.
# IN THE EVENT WHERE CONTROLFILE IS ALSO LOST, ONE HAS TO RESTORE CONTROLFILE FROM THE BACKUP OF PRIMARY.
# HERE I'M NOT RESTORING CONTROLFILE AS WE ARE SIMULATING THE CRASH OF ONLY DATAFILES.
ASMCMD [+reporting_data/troy/datafile] > ls
SYSAUX.276.839828805
SYSTEM.262.839828803.bak
SYSTEM.262.840356549
SYSTEM.288.840356741
UNDOTBS1.277.839828809
UNDOTBS2.283.839829369
USERS.278.839828815
ASMCMD [+reporting_data/troy/datafile] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
# Once done, connect to RMAN on primary database and restore the database with newly catalogged backup sets.
RMAN> restore database ;
Starting restore at 24-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=234 instance=DGTEST1 device type=DISK
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=1 STAMP=840356556 file name=+REPORTING_DATA/troy/datafile/system.262.840356549
destination for restore of datafile 00001: +REPORTING_DATA/troy/datafile/system.288.840356741
ORA-19505: failed to identify file "+REPORTING_DATA/troy/datafile/system.262.840356549"
ORA-17503: ksfdopn:2 Failed to open file +REPORTING_DATA/troy/datafile/system.262.840356549
ORA-15012: ASM file '+REPORTING_DATA/troy/datafile/system.262.840356549' does not exist
ORA-19600: input file is datafile-copy 1 (+REPORTING_DATA/troy/datafile/system.262.840356549)
ORA-19601: output file is datafile 1 (+REPORTING_DATA
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +REPORTING_DATA/troy/datafile/sysaux.276.839828805
channel ORA_DISK_1: restoring datafile 00003 to +REPORTING_DATA/troy/datafile/undotbs1.277.839828809
channel ORA_DISK_1: restoring datafile 00004 to +REPORTING_DATA/troy/datafile/users.278.839828815
channel ORA_DISK_1: restoring datafile 00005 to +REPORTING_DATA/troy/datafile/undotbs2.283.839829369
channel ORA_DISK_1: reading from backup piece /home/oracle/HS/0fp1diba_1_1.rman
channel ORA_DISK_1: piece handle=/home/oracle/HS/0fp1diba_1_1.rman tag=FULL_STANDBY_COPY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
failover to previous backup
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +REPORTING_DATA/troy/datafile/system.288.840356741
channel ORA_DISK_1: reading from backup piece /home/oracle/HS/0fp1diba_1_1.rman
channel ORA_DISK_1: piece handle=/home/oracle/HS/0fp1diba_1_1.rman tag=FULL_STANDBY_COPY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 24-FEB-14
RMAN> recover database ;
Starting recover at 24-FEB-14
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 41 is already on disk as file +APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_41.12295.840359309
archived log for thread 1 with sequence 42 is already on disk as file +APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_42.10005.840359313
archived log for thread 1 with sequence 43 is already on disk as file +APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_43.12416.840359315
archived log for thread 1 with sequence 44 is already on disk as file +APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_44.10048.840359317
archived log for thread 1 with sequence 45 is already on disk as file +APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_45.14052.840359317
archived log for thread 1 with sequence 46 is already on disk as file +APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_46.10027.840359317
archived log for thread 1 with sequence 47 is already on disk as file +APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_47.9986.840359319
archived log file name=+APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_41.12295.840359309 thread=1 sequence=41
archived log file name=+APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_42.10005.840359313 thread=1 sequence=42
archived log file name=+APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_43.12416.840359315 thread=1 sequence=43
archived log file name=+APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_44.10048.840359317 thread=1 sequence=44
archived log file name=+APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_45.14052.840359317 thread=1 sequence=45
archived log file name=+APP_DATA/troy/archivelog/2014_02_24/thread_1_seq_46.10027.840359317 thread=1 sequence=46
media recovery complete, elapsed time: 00:00:02
Finished recover at 24-FEB-14
## NOTE THAT THE DATAFILES PATH ON STANDBY SITE IS TO +DATA DISK GROUP under +DATA/hoffman DIRECTORY.
Also, when restoring on primary the diskgroup name is +APP_DATA for archivelogs and +REPORTING_DATA for datafiles. RMAN automatically discovers the same (from primary controlfile) and restore them to original location regardless of different path on standby.
RMAN> alter database open;
database opened
SQL> select database_role, open_mode, SWITCHOVER_STATUS from gv$database;
DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
---------------- -------------------- --------------------
PRIMARY READ WRITE TO STANDBY
Hope this will help you to leverage the data guard set up more efficiently by freeing up your primary for more meaningful work.
No comments:
Post a Comment