Applying Incremental Backups on Standby Database.

Standby Database is not Sync with Primary and gap is more than 40 days.. We don't have archivelog backups older than 30 days to apply on Standby Database.

 Instead of Rebuilding entire Standby Database we used Incremental Backup recover Standy database.

 Steps Followed briefly:

 1. Take current SCN From Standby Database
2. Take Primary DB Backup using FROM SCN.
3.Create standby controlfile
4. Copy Backups to Standby Server
5. shutdown standby ,startup nomount, Restore Standby control file & startup mount
6.   REcovery database noredo
7. alter database recover managed standby database disconnect from session;
8. Verify Standby Gap.

 Standby:
========

Current SCN From Standby Database:
----------------------------------

SQL> col current_scn for 999,999,999,999,999,9999
SQL>  select current_scn from v$database;

              CURRENT_SCN
-------------------------
         917,102,315,7647

Cancel Recovery of Standby Database:
------------------------------------

alter database recover managed standby database cancel;

Primary:
=======

Incremental backup of Primary Database using Standby SCN:
---------------------------------------------------------
alter system set log_archive_dest_state_2=’ENABLE’ scope=both;

connect to rman and run
rman target /

backup incremental from scn 9171023157647 database format '/backup/exp-orapl01/tmp_bkup/inc_img_backup_%U';

Primary standby controlfile:
----------------------------

alter database create standby controlfile as '/backup/emb-orapl01/tmp_bkup/standby_control.ctl';


Copy Backups to Standby Server

 Standby :
=========

shutdown Standby Database and replace controlfile with Standby controlfile.

shut immediate;

cp -ip /backup/emb-orapl01/tmp_bkup/standby_control.ctl control01.ctl
cp -ip /backup/emb-orapl01/tmp_bkup/standby_control.ctl control02.ctl
cp -ip /backup/emb-orapl01/tmp_bkup/standby_control.ctl control03.ctl


Mount the standby database and catalog the incremental backup to its repository:

startup mount;

RMAN TARGET /

sql "alter session set NLS_COMP=BINARY";
sql "alter session set NLS_SORT=BINARY";
sql "alter session set optimizer_mode=RULE";
sql "alter session set nls_date_format=''DD-MON-YYYY HH24:MI:SS''";

catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jhrgaqmi_1_1';
catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jirgar29_1_1';
catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jjrgar66_1_1';
catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jkrgar9p_1_1';
catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jlrgarc4_1_1';
catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jmrgaref_1_1';
catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jnrgargr_1_1';
catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jorgarj6_1_1';
catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jprgarkt_1_1';
catalog backuppiece '/backup/emb-orapl01/tmp_bkup/inc_img_backup_jqrgarlc_1_1';


RECOVER DATABASE NOREDO;

RMAN> recover database noredo;

Starting recover at 21-SEP-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=919 device type=DISK
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 00002: /u05/oracle/oradata/IMGNPROD_STBY/sysaux01.dbf
destination for restore of datafile 00009: /u05/oracle/oradata/IMGNPROD_STBY/indx01.dbf
destination for restore of datafile 00010: /u05/oracle/oradata/IMGNPROD_STBY/indx02.dbf
destination for restore of datafile 00018: /u02/oracle/oradata/IMGNPROD_STBY/undotbs201.dbf
channel ORA_DISK_1: reading from backup piece /backup/emb-orapl01/tmp_bkup/inc_img_backup_jhrgaqmi_1_1
channel ORA_DISK_1: piece handle=/backup/emb-orapl01/tmp_bkup/inc_img_backup_jhrgaqmi_1_1 tag=TAG20160921T011145
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
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 00011: /u05/oracle/oradata/IMGNPROD_STBY/indx03.dbf
channel ORA_DISK_1: reading from backup piece /backup/emb-orapl01/tmp_bkup/inc_img_backup_jirgar29_1_1
channel ORA_DISK_1: piece handle=/backup/emb-orapl01/tmp_bkup/inc_img_backup_jirgar29_1_1 tag=TAG20160921T011145
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00012: /u05/oracle/oradata/IMGNPROD_STBY/indx04.dbf
channel ORA_DISK_1: reading from backup piece /backup/emb-orapl01/tmp_bkup/inc_img_backup_jjrgar66_1_1
channel ORA_DISK_1: piece handle=/backup/emb-orapl01/tmp_bkup/inc_img_backup_jjrgar66_1_1 tag=TAG20160921T011145
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00005: /u05/oracle/oradata/IMGNPROD_STBY/data01.dbf
channel ORA_DISK_1: reading from backup piece /backup/emb-orapl01/tmp_bkup/inc_img_backup_jkrgar9p_1_1
channel ORA_DISK_1: piece handle=/backup/emb-orapl01/tmp_bkup/inc_img_backup_jkrgar9p_1_1 tag=TAG20160921T011145
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00006: /u05/oracle/oradata/IMGNPROD_STBY/data02.dbf
channel ORA_DISK_1: reading from backup piece /backup/emb-orapl01/tmp_bkup/inc_img_backup_jlrgarc4_1_1
channel ORA_DISK_1: piece handle=/backup/emb-orapl01/tmp_bkup/inc_img_backup_jlrgarc4_1_1 tag=TAG20160921T011145
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00007: /u05/oracle/oradata/IMGNPROD_STBY/data03.dbf
channel ORA_DISK_1: reading from backup piece /backup/emb-orapl01/tmp_bkup/inc_img_backup_jmrgaref_1_1
channel ORA_DISK_1: piece handle=/backup/emb-orapl01/tmp_bkup/inc_img_backup_jmrgaref_1_1 tag=TAG20160921T011145
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
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 00008: /u05/oracle/oradata/IMGNPROD_STBY/data04.dbf
channel ORA_DISK_1: reading from backup piece /backup/emb-orapl01/tmp_bkup/inc_img_backup_jnrgargr_1_1
channel ORA_DISK_1: piece handle=/backup/emb-orapl01/tmp_bkup/inc_img_backup_jnrgargr_1_1 tag=TAG20160921T011145
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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: /u05/oracle/oradata/IMGNPROD_STBY/system01.dbf
destination for restore of datafile 00004: /u05/oracle/oradata/IMGNPROD_STBY/users01.dbf
destination for restore of datafile 00013: /u05/oracle/oradata/IMGNPROD_STBY/indx201.dbf
destination for restore of datafile 00014: /u05/oracle/oradata/IMGNPROD_STBY/indx202.dbf
destination for restore of datafile 00015: /u05/oracle/oradata/IMGNPROD_STBY/indx203.dbf
channel ORA_DISK_1: reading from backup piece /backup/emb-orapl01/tmp_bkup/inc_img_backup_jorgarj6_1_1
channel ORA_DISK_1: piece handle=/backup/emb-orapl01/tmp_bkup/inc_img_backup_jorgarj6_1_1 tag=TAG20160921T011145
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00016: /u05/oracle/oradata/IMGNPROD_STBY/indx204.dbf
channel ORA_DISK_1: reading from backup piece /backup/emb-orapl01/tmp_bkup/inc_img_backup_jprgarkt_1_1
channel ORA_DISK_1: piece handle=/backup/emb-orapl01/tmp_bkup/inc_img_backup_jprgarkt_1_1 tag=TAG20160921T011145
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished recover at 21-SEP-16

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




RUN THE RECOVER STANDBY DATABASE COMMAND:

alter database recover managed standby database disconnect from session;

PRIMARY:
=======
alter system set log_archive_dest_state_2=’ENABLE’ scope=both;


Verify archive shipping :

Media Recovery Waiting for thread 1 sequence 24233 (in transit)
Wed Sep 21 03:42:24 2016
Archived Log entry 4 added for thread 1 sequence 24233 ID 0xc4349580 dest 1:
Wed Sep 21 03:42:24 2016
RFS[2]: Selected log 5 for thread 1 sequence 24234 dbid -1003203456 branch 847456000
Wed Sep 21 03:42:27 2016
Media Recovery Log /u05/tmpArch/1_24233_847456000.dbf
Media Recovery Waiting for thread 1 sequence 24234 (in transit)



Comments

Post a Comment

Popular posts from this blog

Useful OEM Queries to get Target details from OEM Repository

List Targets with TNS Listener ports configured : SELECT mgmt$target.host_name , mgmt$target.target_name , mgmt$target.target_type , mgmt$target_properties.property_name , mgmt$target_properties.property_value FROM mgmt$target , mgmt$target_properties WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name ) AND ( mgmt$target.target_type = mgmt$target_properties.target_type ) and ( mgmt$target.target_type = 'oracle_listener' ) and ( mgmt$target_properties.property_name = 'Port' ); Devora02       LISTENER_ora02                       oracle_listener Port 1529 Devora01       LISTENER_ora01                       oracle_listener Port 1529 Devora04       LISTENER_ora04                       oracle_listener Port 1529 List Machine_Names, CPU Count & Database Verion for Licensing SELECT mgmt$target.host_name , mgmt$target_properties.property_name , mgmt$target_properties.property_value FROM mgmt$target , mgmt$target_properties WHERE ( mgmt$
Read more

WARNING: Subscription for node down event still pending' in Listener Log

Image
These messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). In a non-RAC environment it is recommended to disable this subscription.   This feature was introduced in Oracle 10g. SOLUTION Set the following parameter in the listener.ora: SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF Where <listener_name> should be replaced with the actual listener name configured in the LISTENER.ORA file. SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> parameter is to be placed by itself on an empty line. It will be necessary to restart or reload the listener following the addition of this parameter. This will prevent the messages from being written to the log file and may also prevent the TNS Listener from hanging periodically.  See (10g only) NOTE 340091.1 Intermittent TNS Listener Hang, New Child Listener Process Forked Please Note: Setting SUBSCRIBE_FOR_NODE_DOWN_<listener_n
Read more

ORA-65139: Mismatch between XML metadata file and data file

Error: CDB$ROOT@EBTUPRDC> CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse; CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse * ERROR at line 1: ORA-65139: Mismatch between XML metadata file and data file /u05/odb/ORADATA_1/EBTUSPRDC/datafile/system.1531.807800993 for value of fcpsb (2230502002 in the plug XML file, 2230505492 in the data file) Fix: Don't open Non-CDB database until we complete pdb creation other wise we will get above error. Shutdown immediate; startup mount; alter database open read only; create xml file. shutdown immediate; Connect to CDB and Create Pluggable database .
Read more