Monday, 4 October 2021

Dataguard: physical standby database restoration using full rman backup


Scenario Preview: Resolving gap between primary and physical standby database  resolved  by taking incremental backup  and perform recovery using rman incremental backup.

  • Query to check current SCN status of standby database. Run on standby DB.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;


CURRENT_SCN

-----------

  339341431


SQL>  select min(fhscn) from x$kcvfh;


MIN(FHSCN)

----------------

339341432


SQL>  select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != 'READ ONLY';


MIN(F.FHSCN)

----------------

339341432

  • SCN  based  rman incremental backup  command below for reference.


run

{

allocate channel d0 type disk FORMAT 'backupscn_based%d_%s_%t.bak';

allocate channel d1 type disk FORMAT 'backupscn_based%d_%s_%t.bak';

allocate channel d2 type disk FORMAT 'backupscn_based%d_%s_%t.bak';

allocate channel d3 type disk FORMAT 'backupscn_based%d_%s_%t.bak';

backup as compressed backupset incremental from scn 339341432 database;

copy current controlfile to 'backupscn_basedcontrol01full_%d_%t_%U.bak';

backup current controlfile for standby format 'backupscn_basedstandbycontrol_%d_%T_%U.ctl';

RELEASE CHANNEL d0;

RELEASE CHANNEL d1;

RELEASE CHANNEL d2;

RELEASE CHANNEL d3;

}


  • Open database in nomount stage and restore control file 

[oracle@srv4 backup]$ . oraenv

ORACLE_SID = [ZABBIXDR] ?

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@srv4 backup]$ sqlplus / as sysdba


SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 27 18:37:36 2021


Copyright (c) 1982, 2014, Oracle.  All rights reserved.



Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> shut immediate;

ORA-01109: database not open



Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.


Total System Global Area 1795162112 bytes

Fixed Size                  2925456 bytes

Variable Size             620760176 bytes

Database Buffers         1157627904 bytes

Redo Buffers               13848576 bytes

SQL> exit


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@srv4 backup]$ rman target /


Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 27 18:40:44 2021


Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


connected to target database: ZABBIX (not mounted)


RMAN> restore standby controlfile from '/u01/backup/standbycontrol_ZABBIX_1d051c76_1_1.ctl';

Starting restore at 27-JUL-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/ZABBIXDR/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/ZABBIXDR/control02.ctl

Finished restore at 27-JUL-21

  • Open database in mount stage and restore  standby database 

RMAN> sql 'alter database mount standby database';


sql statement: alter database mount standby database

released channel: ORA_DISK_1


RMAN> catalog start with '/u01/backup';


Starting implicit crosscheck backup at 27-JUL-21

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=24 device type=DISK

Crosschecked 13 objects

Finished implicit crosscheck backup at 27-JUL-21


Starting implicit crosscheck copy at 27-JUL-21

using channel ORA_DISK_1

Crosschecked 6 objects

Finished implicit crosscheck copy at 27-JUL-21


searching for all files in the recovery area

cataloging files...

cataloging done


List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/autobackup/2021_07_25/o1_mf_s_1078857835_jhts676p_.bkp

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/autobackup/2021_07_23/o1_mf_s_1078691869_jhopt5fk_.bkp

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/autobackup/2021_07_19/o1_mf_s_1078341966_jhc13pjr_.bkp

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_17/o1_mf_1_51_jh5txgn3_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_17/o1_mf_1_52_jh5w956q_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_33_jhz922g1_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_34_jhz923jb_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_32_jhz87wwc_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_35_jhzcszvk_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_31_jhz87yd4_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_29_jhxdgqjh_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_28_jhx8b6c8_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_26_jhx752c2_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_27_jhx754fs_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_25_jhx750kr_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_24_jhwmbsk6_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_30_jhxfy2os_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_21_jhtrwp8q_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_10_jhtr7176_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_11_jhtr72bt_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_12_jhtr73o7_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_23_jhts9kwh_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_9_jhtqv96b_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_15_jhtrmltg_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_17_jhtrnorq_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_19_jhtrwhvo_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_8_jhtqv8w7_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_16_jhtrmmh5_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_18_jhtrpgbr_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_13_jhtrmj20_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_20_jhtrwmnk_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_6_jhtpbq66_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_14_jhtrmlrt_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_22_jhts9jpq_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_7_jhtqjvr0_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_08/o1_mf_1_49_jgg446oc_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_23/o1_mf_1_2_jhokqtln_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_23/o1_mf_1_3_jhop0w97_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_23/o1_mf_1_4_jhop0wt0_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_23/o1_mf_1_5_jhopsvv9_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_54_jhbqjq1l_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_64_jhc0njy1_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_58_jhbw0yf3_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_61_jhbzy9yf_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_57_jhbw02oc_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_63_jhbzyc4y_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_53_jhbqjqf9_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_56_jhbvzxjs_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_60_jhbzy6vz_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_55_jhbr7mg0_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_62_jhbzy9xr_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_59_jhbw495f_.arc

File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_1_jhc0tnhq_.arc


searching for all files that match the pattern /u01/backup


List of Files Unknown to the Database

=====================================

File Name: /u01/backup/standbycontrol_ZABBIX_1d051c76_1_1.ctl


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/backup/standbycontrol_ZABBIX_1d051c76_1_1.ctl


RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 20 BACKUP TYPE TO BACKUPSET;


new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 20 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

released channel: ORA_DISK_1


RMAN>  RECOVER DATABASE NOREDO;


RMAN> select process,status,sequence# from v$managed_standby;


PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

  • Apply Media recovery process on standby database 

RMAN> alter database recover managed standby database disconnect from session;


Statement processed


RMAN> select process,status,sequence# from v$managed_standby;


PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

MRP0      WAIT_FOR_LOG          7




Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)