Apply Incremental Backup On Standby Database

Posted: March 8, 2013 in StandBy
Tags: , ,

Some times we have situations when we have standby by database that is lagged too much behind the production database or few archives have been deleted from production without being shipped to standby. In this situation, in order to sync standby database with production we need to apply incremental backup of production on standby.

Recently, i got the below message on alert.log of standby database, which says that there is GAP of archives and GAP can’t be resolved automatically as all the FAL servers have been tried. On checking the primary server, i can’t find the archivelogs from 2854-2865. So, only option i am left with is to apply incremental backup (off course you can create standby again, if you have time for that 😉 )

FAL[client]: Failed to request gap sequence
GAP – thread 1 sequence 2854-2865
DBID 165665636 branch 781470756
FAL[client]: All defined FAL servers have been attempted.
————————————————————-
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
————————————————————-

First thing to do it to notedown the current SCN from standby, so that we know from where we need to start the backup on primary. logon on to standby server and record the SCN number:

SQL> select current_scn from v$database;

CURRENT_SCN
———–
19688161

Now, take backup on primary from this SCN number as:

oraplq_s@myhost1 $ rman target / catalog RMANPRD1/hahaha@rmap1

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Mar 7 11:47:27 2013

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

connected to target database: PLMQDBS (DBID=165665636)
connected to recovery catalog database

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 19688161 DATABASE FORMAT ‘/oraclebackup/PLMQDBS/incr_for_stdby_%U’;

Starting backup at 07-MAR-13

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=244 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=278 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=346 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=379 device type=DISK
backup will be obsolete on date 14-MAR-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u04/oradata/PLMQDBS/system01.dbf
input datafile file number=00007 name=/u04/oradata/PLMQDBS/TVC_DATA01.DBF
input datafile file number=00005 name=/u04/oradata/PLMQDBS/PLM_DATA01.DBF
channel ORA_DISK_1: starting piece 1 at 07-MAR-13
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u04/oradata/PLMQDBS/sysaux01.dbf
input datafile file number=00008 name=/u04/oradata/PLMQDBS/TVC_INDEX01.DBF
input datafile file number=00006 name=/u04/oradata/PLMQDBS/PLM_INDEX01.DBF
channel ORA_DISK_2: starting piece 1 at 07-MAR-13
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00003 name=/u04/oradata/PLMQDBS/undots01.dbf
input datafile file number=00004 name=/u04/oradata/PLMQDBS/PLM01.DBF
channel ORA_DISK_3: starting piece 1 at 07-MAR-13
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00010 name=/u04/oradata/PLMQDBS/cad_ts01.dbf
input datafile file number=00009 name=/u04/oradata/PLMQDBS/userdata01.dbf
channel ORA_DISK_4: starting piece 1 at 07-MAR-13
channel ORA_DISK_1: finished piece 1 at 07-MAR-13
piece handle=/oraclebackup/PLMQDBS/incr_for_stdby_f4o3u54k_1_1 tag=TAG20130307T123322 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_4: finished piece 1 at 07-MAR-13
piece handle=/oraclebackup/PLMQDBS/incr_for_stdby_f7o3u54k_1_1 tag=TAG20130307T123322 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 1 at 07-MAR-13
piece handle=/oraclebackup/PLMQDBS/incr_for_stdby_f5o3u54k_1_1 tag=TAG20130307T123322 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_3: finished piece 1 at 07-MAR-13
piece handle=/oraclebackup/PLMQDBS/incr_for_stdby_f6o3u54k_1_1 tag=TAG20130307T123322 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:16

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
backup will be obsolete on date 14-MAR-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 07-MAR-13
channel ORA_DISK_1: finished piece 1 at 07-MAR-13
piece handle=/oraclebackup/PLMQDBS/incr_for_stdby_f8o3u554_1_1 tag=TAG20130307T123322 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAR-13
RMAN-08591: WARNING: invalid archived log deletion policy

RMAN>

Now, catalog the backup file generated from above step. I have moved these files to another folder (just to make management easier) and then register with RMAN.

oracore@myhost1 $ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Mar 7 12:35:57 2013

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

connected to target database: PLMQDBS (DBID=165665636, not open)

RMAN> catalog start with ‘/oraclebackup/PLMQDBS/inc/’;

using target database control file instead of recovery catalog
searching for all files that match the pattern /oraclebackup/PLMQDBS/inc/

List of Files Unknown to the Database
=====================================
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f4o3u54k_1_1
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f5o3u54k_1_1
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f6o3u54k_1_1
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f7o3u54k_1_1
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f8o3u554_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f4o3u54k_1_1
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f5o3u54k_1_1
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f6o3u54k_1_1
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f7o3u54k_1_1
File Name: /oraclebackup/PLMQDBS/inc/incr_for_stdby_f8o3u554_1_1

Now, incremental backup files are available to rman, use the command “recover database noredo” to apply incremental backup to standby. Logs of this activity are as:

RMAN> recover database noredo;

Starting recover at 07-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=354 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=3 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=53 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=104 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 00001: /u04/oradata/CORE/PLMQDBS/system01.dbf
destination for restore of datafile 00005: /u04/oradata/CORE/PLMQDBS/PLM_DATA01.DBF
destination for restore of datafile 00007: /u04/oradata/CORE/PLMQDBS/TVC_DATA01.DBF
channel ORA_DISK_1: reading from backup piece /oraclebackup/PLMQDBS/inc/incr_for_stdby_f4o3u54k_1_1
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /u04/oradata/CORE/PLMQDBS/sysaux01.dbf
destination for restore of datafile 00006: /u04/oradata/CORE/PLMQDBS/PLM_INDEX01.DBF
destination for restore of datafile 00008: /u04/oradata/CORE/PLMQDBS/TVC_INDEX01.DBF
channel ORA_DISK_2: reading from backup piece /oraclebackup/PLMQDBS/inc/incr_for_stdby_f5o3u54k_1_1
channel ORA_DISK_3: starting incremental datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u04/oradata/CORE/PLMQDBS/undots01.dbf
destination for restore of datafile 00004: /u04/oradata/CORE/PLMQDBS/PLM01.DBF
channel ORA_DISK_3: reading from backup piece /oraclebackup/PLMQDBS/inc/incr_for_stdby_f6o3u54k_1_1
channel ORA_DISK_4: starting incremental datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: /u04/oradata/CORE/PLMQDBS/userdata01.dbf
destination for restore of datafile 00010: /u04/oradata/CORE/PLMQDBS/cad_ts01.dbf
channel ORA_DISK_4: reading from backup piece /oraclebackup/PLMQDBS/inc/incr_for_stdby_f7o3u54k_1_1
channel ORA_DISK_1: piece handle=/oraclebackup/PLMQDBS/inc/incr_for_stdby_f4o3u54k_1_1 tag=TAG20130307T123322
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_2: piece handle=/oraclebackup/PLMQDBS/inc/incr_for_stdby_f5o3u54k_1_1 tag=TAG20130307T123322
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:15
channel ORA_DISK_4: piece handle=/oraclebackup/PLMQDBS/inc/incr_for_stdby_f7o3u54k_1_1 tag=TAG20130307T123322
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:15
channel ORA_DISK_3: piece handle=/oraclebackup/PLMQDBS/inc/incr_for_stdby_f6o3u54k_1_1 tag=TAG20130307T123322
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:35

Finished recover at 07-MAR-13

RMAN>

Now, create a standby controlfile from primary database and transfer this file to respective locations of controlfile of standby.

oraplq_s@myhost $ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 7 12:47:33 2013

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

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select name from v$database;

NAME
———
PLMQDBS

SQL> alter database create standby controlfile as ‘/oraclebackup/PLMQDBS/control_std01.ctl’;

Database altered.

Now, shutdown the standby database and make necessary changes in init.ora file (if required) and start the standby database in mount state again with new standby control file.

oracore@myhost $ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 7 12:49:50 2013

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

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 4175568896 bytes
Fixed Size 2213384 bytes
Variable Size 805308920 bytes
Database Buffers 3355443200 bytes
Redo Buffers 12603392 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session noparallel;

Database altered.

Standby database is again in managed mode and archives should ship and apply automatically. Now, enable the log_archive_dest_state_2 parameter on primary again and check that archives getting shipped and applied or not. On primary database:

SQL> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;

System altered.

SQL> alter system switch logfile;

System altered.

Standby by alert.log file should now be saying that archives are getting applied. A small section of alert.log from standby database:

Attempt to start background Managed Standby Recovery process (PLMQDBS)
Thu Mar 07 12:56:31 2013
MRP0 started with pid=22, OS id=18350344
MRP0: Background Managed Standby Recovery process started (PLMQDBS)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived…
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 3031
Completed: alter database recover managed standby database disconnect from session noparallel
Thu Mar 07 12:58:02 2013
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
RFS[1]: Assigned to RFS process 22544650
RFS[1]: Identified database type as ‘physical standby’: Client is ARCH pid 60817500
Thu Mar 07 12:58:04 2013
RFS[2]: Assigned to RFS process 23593074
RFS[2]: Identified database type as ‘physical standby’: Client is LGWR ASYNC pid 49283190
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Opened log for thread 1 sequence 3031 dbid 165665636 branch 781470756
Archived Log entry 1 added for thread 1 sequence 3031 rlc 781470756 ID 0x9e06a64 dest 2:
RFS[2]: Opened log for thread 1 sequence 3032 dbid 165665636 branch 781470756
Thu Mar 07 12:58:07 2013
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_07/o1_mf_1_3031_8mk06df6_.arc
Media Recovery Waiting for thread 1 sequence 3032 (in transit)
Archived Log entry 2 added for thread 1 sequence 3032 rlc 781470756 ID 0x9e06a64 dest 2:
RFS[2]: Opened log for thread 1 sequence 3033 dbid 165665636 branch 781470756
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_07/o1_mf_1_3032_8mk06dv4_.arc
Media Recovery Waiting for thread 1 sequence 3033 (in transit)
Thu Mar 07 12:58:28 2013
RFS[3]: Assigned to RFS process 24641630
RFS[3]: Identified database type as ‘physical standby’: Client is ARCH pid 60817500
Thu Mar 07 12:59:26 2013
Archived Log entry 3 added for thread 1 sequence 3033 rlc 781470756 ID 0x9e06a64 dest 2:
RFS[2]: Opened log for thread 1 sequence 3034 dbid 165665636 branch 781470756
Thu Mar 07 12:59:27 2013
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_07/o1_mf_1_3033_8mk06lhs_.arc
Media Recovery Waiting for thread 1 sequence 3034 (in transit)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s