SYED GAP RESOLUTION BY INCREMENTAL BACKUP

Roll Forward Physical Standby Database using RMAN incremental backup
There would be scenarios where the standby database lags far behind from the primary database leading to Archive Gap. It could be due to one of the following reasons

1. Might be due to the network outage between the primary and the standby database leading to the archive gaps. Data guard would be able to detect the archive gaps automatically and can fetch the missing logs as soon as the connection is re-established.

2. It could also be due to archive logs getting missed out on the primary database or the archives getting corrupted and there would be no valid backups.

In such cases where the standby lags far behind from the primary database, incremental backups can be used as one of the  methods to roll forward the physical standby database to have it in sync with the primary database.

Oracle Database version : 11.2.0.1.0 My Oracle Database is using ASM.

Primary database : primary              Standby database : standby

Primary Host : primary                         Standby Host : standby

The maximum archivelog sequence generated on the Primary Database is 203.


SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         primary             PRIMARY

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- ----------------
1       203
On the standby database, the maximum archivelog sequence that is applied is sequence 159.


SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED      standby             PHYSICAL STANDBY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- ----------------
1       159
The standby database is lagging behind the primary database by around 140 archives (203 – 159).


Step 1: Take a note of the Current SCN of the Physical Standby Database.

Standby Database:


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
2358948
Note down the CURRENT_SCN value of the standby database (2358948) to proceed further.

Step 2 : Cancel the Managed Recovery Process on the Standby database.

Standby Database:

1
SQL>alter database recover managed standby database cancel;
Step 3: On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database (2358948)

Connect to the primary database and take the incremental SCN backup.

Primary Database:


[oracle@primary ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 29 11:49:26 2016

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

connected to target database: PRIMARY (DBID=1737398252)

RMAN>  backup incremental from scn 2358948 database format '/tmp/stnd_backp_%U.b                                       ak';

Starting backup at 29-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/primary/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/primary/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/primary/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/primary/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/primary/users01.dbf
channel ORA_DISK_1: starting piece 1 at 29-JAN-16
channel ORA_DISK_1: finished piece 1 at 29-JAN-16
piece handle=/tmp/stnd_backp_06qsj6e6_1_1.bak tag=TAG20160129T115550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
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
channel ORA_DISK_1: starting piece 1 at 29-JAN-16
channel ORA_DISK_1: finished piece 1 at 29-JAN-16
piece handle=/tmp/stnd_backp_07qsj6g9_1_1.bak tag=TAG20160129T115550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JAN-16

RMAN>  backup current controlfile for standby format '/tmp/stnd_%U.ctl';

Starting backup at 29-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JAN-16
channel ORA_DISK_1: finished piece 1 at 29-JAN-16
piece handle=/tmp/stnd_08qsj6k7_1_1.ctl tag=TAG20160129T115903 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JAN-16

RMAN> quit

Primary Database :


[oracle@dev bkp]$ pwd
/u02/bkp
[oracle@dev bkp]$ ls -lrt
total 13576
-rw-r----- 1 oracle oinstall   540672 Mar 25 15:47 stnd_backp_10n6p3nl_1_1.bak
-rw-r----- 1 oracle oinstall 13336576 Mar 25 15:47 stnd_backp_11n6p3p4_1_1.bak
[oracle@dev bkp]$ scp stnd* uat:/u02/bkp
oracle@uat's password:
stnd_backp_10n6p3nl_1_1.bak                   100%  528KB 528.0KB/s   00:00
stnd_backp_11n6p3p4_1_1.bak                   100%   13MB   6.4MB/s   00:02
[oracle@dev bkp]$ cd /u02
[oracle@dev u02]$ ls -lrt stnd*
-rw-r----- 1 oracle oinstall 13336576 Mar 25 15:48 stnd_12n6p3qt_1_1.ctl
[oracle@dev u02]$ scp stnd* uat:/u02
oracle@uat's password:
stnd_12n6p3qt_1_1.ctl                         100%   13MB  12.7MB/s   00:01
[oracle@dev u02]$
Step 6: On the standby server, connect the Standby Database through RMAN and catalog the copied incremental backups so that the Controlfile of the Standby Database would be aware of these incremental backups.

I had the incremental backuppieces copied to the location ‘/u02/bkp‘ on the standby server.

Standby Database:


[oracle@standby tmp]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 29 12:16:58 2016

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

connected to target database: PRIMARY (DBID=1737398252, not open)

RMAN> catalog start with '/u02/bkp';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u02/bkp

List of Files Unknown to the Database
=====================================
File Name: /u02/bkp/stnd_backp_07qsj6g9_1_1.bak
File Name: /u02/bkp/stnd_08qsj6k7_1_1.ctl
File Name: /u02/bkp/stnd_backp_06qsj6e6_1_1.bak

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

List of Cataloged Files
=======================
File Name: /u02/bkp/stnd_backp_07qsj6g9_1_1.bak
File Name: /u02/bkp/stnd_08qsj6k7_1_1.ctl
File Name: /u02/bkp/stnd_backp_06qsj6e6_1_1.bak

RMAN> recover database noredo;

Starting recover at 29-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 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: /u01/app/oracle/standby/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/standby/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/standby/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/standby/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/standby/example01.dbf
channel ORA_DISK_1: reading from backup piece /u02/bkp/stnd_backp_06qsj6e6_1_1.b                                                                  ak
channel ORA_DISK_1: piece handle=/u02/bkp/stnd_backp_06qsj6e6_1_1.bak tag=TAG201                                                                  60129T115550
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished recover at 29-JAN-16

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     726540288 bytes

Fixed Size                     2256792 bytes
Variable Size                465567848 bytes
Database Buffers             251658240 bytes
Redo Buffers                   7057408 bytes


RMAN> restore standby controlfile from '/u02/bkp/stnd_08qsj6k7_1_1.ctl';

Starting restore at 29-JAN-16
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/standby/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/standby/control02.ctl
Finished restore at 29-JAN-16

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup mount


Recovery Manager complete.
[oracle@standby tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 12:32:38 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0
SQL> alter system set compatible='11.2.0.4.0' scope=spfile;

System altered.

SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
ORA-01507: database not mounted


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

Total System Global Area  726540288 bytes
Fixed Size                  2256792 bytes
Variable Size             465567848 bytes
Database Buffers          251658240 bytes
Redo Buffers                7057408 bytes
Database mounted.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/standby/system01.dbf
/u01/app/oracle/standby/sysaux01.dbf
/u01/app/oracle/standby/undotbs01.dbf
/u01/app/oracle/standby/users01.dbf
/u01/app/oracle/standby/example01.dbf

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@standby tmp]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 29 12:35:37 2016

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

connected to target database: PRIMARY (DBID=1737398252, not open)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     726540288 bytes

Fixed Size                     2256792 bytes
Variable Size                465567848 bytes
Database Buffers             251658240 bytes
Redo Buffers                   7057408 bytes

RMAN> catalog start with '/u01/app/oracle/standby'
2> ;

Starting implicit crosscheck backup at 29-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 29-JAN-16

Starting implicit crosscheck copy at 29-JAN-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-JAN-16

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

searching for all files that match the pattern /u01/app/oracle/standby
no files found to be unknown to the database

RMAN> catalog start with '/u01/app/oracle/standby/'
2> ;

searching for all files that match the pattern /u01/app/oracle/standby/
no files found to be unknown to the database

RMAN> quit


Recovery Manager complete.
[oracle@standby tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 12:39:52 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database recover managed standby database disconnectfrom session;
alter database recover managed standby database disconnectfrom session
*
ERROR at line 1:
ORA-00274: illegal recovery option DISCONNECTFROM


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

Database altered.

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

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING             212
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                213
MRP0      WAIT_FOR_LOG        213

8 rows selected.

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            212

SQL>

Disable broker configguration

Time for action – disabling broker configuration
Broker management of the primary database and all of its standby databases can be disabled using the DISABLE CONFIGURATION command. After disabling the configuration, it won't be possible to fetch any information from the Data Guard broker by using DGMGRL.

Before disabling the configuration, let's check the status of the broker configuration, as follows:
DGMGRL> show configuration;
Configuration - PACKT
  Protection Mode: MaxPerformance
  Databases:
    turkey_un - Primary database
    INDIA_UN  - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
The current status of the configuration is SUCCESS

.

Disable the configuration as follows:
DGMGRL> disable configuration;
Disabled.
Check the current configuration status as follows:


DGMGRL> show configuration;
Configuration - PACKT
  Protection Mode: MaxPerformance
  Databases:
    turkey_un - Primary database
    INDIA_UN  - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED



You can disable the configuration either from the primary or the standby database. On disabling the broker, databases will not be monitored by the broker. This command won't remove the broker configuration from the configuration file. You're still able to perform changes on the database properties. However, the changes will only be applicable once you enable the configuration again.

Time for action – preliminary tests before performing switchover



In order to perform switchover, we have to prepare and verify both the primary and standby databases. Perform the following steps:

1 ) Check the standby redo logfile status on the primary database as follows:


SQL> select group#,member,type from v$logfile where type='STANDBY';

    GROUP# MEMBER                                            TYPE
---------- ---------------------------------------------- -------
        ..   ........
     14 /u01/app/oracle/oradata/orcl/standby_redo05.log    STANDBY
     16 /u01/app/oracle/oradata/orcl/standby_redo06.log    STANDBY
6 rows selected.
Standby redo logfiles should have been created on the primary database; this is so that after performing switchover, the new standby database can receive redo using standby redo logfiles. This will help us save time in the post-configuration steps.


2 Verify the log archive destination on the standby database, which will be active after the switchover and will be used to transfer redo to the new standby database as follows:
SQL> show parameter log_archive_dest_2
NAME                 TYPE    VALUE
-------------------- ------  ------------------------------
log_archive_dest_2   string   SERVICE=TURKEY LGWR ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=turkey_un'



Verify if the temporary files of the temporary tablespaces are created on the standby database. Compare the result of the following query from the primary and standby databases.
SQL>  select file_name,bytes/1024/1024 "SizeMB",maxbytes/1024/1024 "MaxSize MB",autoextensible fromdba_temp_files;

FILE_NAME                                Size MB MaxSize MB AUT
---------------------------------------- ------- ---------- ---
/u02/app/oracle/oradata/orcl/temp01.dbf       20 32767.9844 YES




3 If temporary files don't exist on the standby database or the number and size of temporary files don't match in the primary and standby databases, create or modify the temporary files on the standby database properly.

TIP
If you have created a standby database using the RMAN command DUPLICATE in Oracle 11gR2, the temporary files will be created by default.

4 Check if any offline datafiles exist on primary as well as standby. If they do exist, bring them online using the following code:
SQL> select name from v$datafile where status='OFFLINE';




5 Verify the status of the redo transport and apply services against any gap and synchronization issues as follows:
SQL> select db_unique_name, status, protection_mode, synchronization_status, synchronized from v$archive_dest_status where dest_id=2;

DB_UNIQUE_NAME STATUS  PROTECTION_MODE  SYNCHRONIZATION_STATUS SYN
-------------- ------- ----------------- --------------------- ---
INDIA_UN      VALID  MAXIMUM PERFORMANCE CHECK CONFIGURATION   YES




6   In the previous output, you can ignore the synchronization status CHECK CONFIGURATION if the database is in Maximum Performance mode. If the configuration is either Maximum Protection or Availability, the status OK will be returned when there are no synchronization issues. Check the maximum archived log sequences on the primary and standby databases.


From primary – to obtain the maximum number of archived log sequences for each instance, the following code can be used:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            335


From standby – to obtain the maximum number of archived log sequences for each instance, the following code can be used:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
  335



Now verify if the MRP process is running or not by running the following statement on the standby database:

SQL> select thread#,sequence#,process,status,client_process from v$managed_standby where thread#=1;

   THREAD#  SEQUENCE# PROCESS   STATUS       CLIENT_P
---------- ---------- --------- ------------ --------
         1        335 ARCH      CLOSING      ARCH
         1        333 ARCH      CLOSING      ARCH
         1        334 ARCH      CLOSING      ARCH
         1        336 MRP0      APPLYING_LOG N/A
         1        336 RFS       IDLE         LGWR

The current sequence 336 is being written into the standby redo logfiles and the MRP process is applying this sequence at the same time.



It's also possible to query the v$dataguard_stats view on the standby database to check the synchronization status:


SQL> select name,value,time_computed from v$dataguard_stats;

NAME                   VALUE         TIME_COMPUTED                
---------------------- ------------- --------------------
transport lag          +00 00:00:00  10/10/2012 15:07:51  
apply lag              +00 00:00:00  10/10/2012 15:07:51  
apply finish time      +00 00:00:00  10/10/2012 15:07:51
estimated startup time           16  10/10/2012 15:07:51

SQL> !date
Wed Oct 10 15:07:52 IST 2012


Ensure that no backup jobs are running. Disable the RMAN and EXP/EXPDP backup jobs from CRONTAB if they exist.

If the primary and standby databases are monitored with EM Cloud/Grid Control and you're performing switchover using SQL*Plus or Data Guard broker, black out the database until the task is completed.


Set the JOB_QUEUE_PROCESSES parameter value to 0 so that no more jobs will be started. After the completion of switchover, reset it with the previous value.




SQL> alter system set JOB_QUEUE_PROCESSES=0 scope=both sid='*';
If the primary database is RAC, ensure all the remaining primary instances except one are shut down. If Active Data Guard is in use, disable it and ensure that all standby instances are in the mount state.
It's advisable to take a full backup of the database either from primary or standby.

CONFIGURE DATA GUARD BROKER