Tuesday, January 27, 2015

Recovering Physical Standby using Incremental backup of Primary

Recovering Physical Standby using Incremental backup of Primary
===============================================================

Scenario :- Physical standby went out of sync from the primary as some of the archive logs are missing,
even those archivelog is deleted in the primary as well and no backup is available. To overcome this
we can take the incremental backup from primary and apply to the standby to keep it is in sync.


Primary DB

SQL> select current_scn from V$database;

CURRENT_SCN
-----------
    2923116


SQL> select max(sequence#) from V$archived_log where applied = 'YES' and dest_id = 2;

MAX(SEQUENCE#)
--------------
            91


Standby DB
----------

SQL> select current_scn from V$database;

CURRENT_SCN
-----------
    2921713


SQL> select process, STATUS, sequence# from V$managed_standby where process like '%MRP%';

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      WAIT_FOR_GAP         92


SQL> SELECT * FROM v$ARCHIVE_GAP;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1            92            113


--


As per the above we could see that there is a gap between 93 and 113 sequence which is missing on the standby site,
the current scn in standby site is 2921713, around 20 archive logs are missing also it is not availble in backup
in the primary site. Lets take the incremental backup from the primary site.


RMAN> run
{
allocate channel a1 device type disk format '/oracle/scn_incre_bkp_%U_%T.bkp';
backup incremental from scn 2921713 database;
}

using target database control file instead of recovery catalog
allocated channel: a1
channel a1: SID=34 device type=DISK

Starting backup at 28-JAN-15

backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/source/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/source/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/source/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/source/test_tbls_01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/source/users01.dbf
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:02:05

backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
including current control file in backup set
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-15
released channel: a1


-- Take the current controlfile trace for standby.


SQL> alter database create standby controlfile as '/oracle/for_standby_ctl.ctl';

Database altered.


--


scp the controlfile and the backup piece to the standby location.

[oracle@rhel11gr2rac1 oracle]$ scp *.bkp oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
scn_incre_bkp_13ptrh7n_1_1_20150128.bkp                                                                                               100% 2552KB   2.5MB/s   00:00
scn_incre_bkp_14ptrhbl_1_1_20150128.bkp                                                                                               100% 9792KB   9.6MB/s   00:01

[oracle@rhel11gr2rac1 oracle]$ scp for_standby_ctl.ctl oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
for_standby_ctl.ctl                                                                                                                   100% 9744KB   9.5MB/s   00:01
[oracle@rhel11gr2rac1 oracle]$


On standby, stop the MRP process , shut down db and start with the controlfile which been copied from the primary site.


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------------------------------------------------------------
control_files                        string      /u01/app/oracle/oradata/source/control01.ctl, /u01/app/oracle/fast_recovery_area/source/control02.ctl

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


-- Overwrite the old controlfiles with the new one.



[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/oradata/source/control01.ctl
[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/fast_recovery_area/source/control02.ctl


[oracle@standalone2 bkp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 28 03:04:45 2015

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     313159680 bytes

Fixed Size                     2227944 bytes
Variable Size                226492696 bytes
Database Buffers              79691776 bytes
Redo Buffers                   4747264 bytes


-- catalog the backup piece


RMAN> catalog start with '/u01/app/oracle/bkp/scn';

Starting implicit crosscheck backup at 28-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
Crosschecked 17 objects
Finished implicit crosscheck backup at 28-JAN-15

Starting implicit crosscheck copy at 28-JAN-15
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 28-JAN-15

searching for all files in the recovery area
cataloging files... files cataloged

searching for all files that match the pattern /u01/app/oracle/bkp/scn

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp

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/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp

-- Start the recover.

RMAN> recover database;

Starting recover at 28-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/source/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/source/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/source/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/source/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/source/test_tbls_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

starting media recovery

archived log for thread 1 with sequence 116 is already on disk as file /u01/app/oracle/archive/1_116_868359284.dbf
archived log file name=/u01/app/oracle/archive/1_116_868359284.dbf thread=1 sequence=116
unable to find archived log
archived log thread=1 sequence=117
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2015 03:07:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 117 and starting SCN of 2928165


-- Now the restore is completed.

-- Lets check the scn now.

SQL> select current_scn from V$database;

CURRENT_SCN
-----------
    2928164

-- Now start the MRP process, both primary and standby are in sync now.






No comments:

Post a Comment