Fixing standby GAP issue with Primary using SCN backup

 Step 1:  The Current SCN of the Physical Standby Database.

Standby Database:


SQL> select name,open_mode from v$database;


NAME      OPEN_MODE

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

XYZDB     MOUNTED


SQL> select name,open_mode,database_role from v$database;


NAME      OPEN_MODE  DATABASE_ROLE

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

XYZDB     MOUNTED    PHYSICAL STANDBY

 

SQL> select db_unique_name, to_char(current_scn, '999999999999999999') as Standby_Controlfile_SCN from v$database;

 

DB_UNIQUE_NAME                 STANDBY_CONTROLFILE

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

XYZDB                            10861224114929


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

 

Last Sequence received:


SQL> select thread#, max(sequence#) "Last Standby Seq Received"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

 

THREAD#    Last Standby Seq Received

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

1             266428


Last sequence applied :


SQL> select thread#, max(sequence#) "Last Standby Seq Applied"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

and applied='YES'

group by thread# order by 1; 

 

THREAD#    Last Standby Seq Applied

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

1             255271


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


Standby Database:


SQL> select PROCESS,STATUS,CLIENT_PROCESS from v$managed_standby;

PROCESS   STATUS       CLIENT_P

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

ARCH      CONNECTED    ARCH

ARCH      CONNECTED    ARCH

SQL> alter database recover managed standby database cancel;

alter database recover managed standby database cancel

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active


Step 3:


Primary database  :


Taken  the incremental SCN backup from the SCN that is currently recorded on the standby database.


SQL> select name,open_mode,database_role from v$database;


NAME      OPEN_MODE  DATABASE_ROLE

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

XYZDB     READ WRITE PRIMARY


SQL> select db_unique_name, to_char(current_scn, '999999999999999999') as Primary_Controlfile_SCN from v$database;

 

DB_UNIQUE_NAME                 PRIMARY_CONTROLFILE

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

XYZDB                            10878875895876


RMAN Backup script:


vi rman_standby_10NOV2012.sh


export ORACLE_SID=XYZDB

rman target sys/******* <<! >rman_incremental_10NOV2012.log

run

{

ALLOCATE CHANNEL C1 DEVICE TYPE DISK ;

ALLOCATE CHANNEL C2 DEVICE TYPE DISK ;

ALLOCATE CHANNEL C3 DEVICE TYPE DISK ;

backup incremental from scn 10861224114929 database format ‘/u01/apps/oracle/admin/XYZDB/bkup/RmanIncremental_%U_%d_%T’;

backup current controlfile for standby format '/u01/apps/oracle/admin/XYZDB/bkup/control_file_%T';

RELEASE CHANNEL C1;

RELEASE CHANNEL C2;

RELEASE CHANNEL C3;

}

exit

!


$ nohup sh rman_standby_10NOV2012.sh &


Step 4: Transfer the backups from the Primary  to the Standby .


Primary Database


scp RmanIncremental_06npu8a1_1_1_XYZDB_20121110 oracle@usa..com:/global/CEXOE/XYZDB/RMAN_CENXCT_BK

Password:xxxxx

scp RmanIncremental_05npu8a1_1_1_XYZDB_20121110 oracle@usa..com:/global/CEXOE/XYZDB/RMAN_CENXCT_BK

Password:xxxxx

scp RmanIncremental_04npu8a0_1_1_XYZDB_20121110 oracle@usa..com:/global/CEXOE/XYZDB/RMAN_CENXCT_BK

Password:xxxxx

scp control_file_20121110 oracle@usa..com:/global/CEXOE/XYZDB/RMAN_CENXCT_BK

Password:xxxxx

 

Step 5:


 Shutdown the physical standby database, start it in nomount stage and restore the standby control file and RMAN Incremental backup that we had taken from the primary database.


Standby Database:


$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Nov 10 07:28:38 2012

 

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    3154116608 bytes

Fixed Size                     2142240 bytes

Variable Size               1201623008 bytes

Database Buffers            1811939328 bytes

Redo Buffers                 138412032 bytes

 

RMAN> restore standby controlfile from '/global/CEXOE/XYZDB/RMAN_CENXCT_BK/control_file_20121110';

Starting restore at 10-NOV-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=702 devtype=DISK

channel ORA_DISK_1: restoring control file

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

output filename=/global/CEXOE/XYZDB/oradata/group1/XYZDB_control01.ctl

output filename=/global/CEXOE/XYZDB/oradata/group2/XYZDB_control02.ctl

output filename=/global/CEXOE/XYZDB/oradata/group3/XYZDB_control03.ctl

Finished restore at 10-NOV-12


Backup restoration


RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> catalog start with '/global/CEXOE/XYZDB/RMAN_CENXCT_BK';

searching for all files that match the pattern /global/CEXOE/XYZDB/RMAN_CENXCT_BK

List of Files Unknown to the Database

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

File Name: /global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_06npu8a1_1_1_XYZDB_20121110

File Name: /global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_05npu8a1_1_1_XYZDB_20121110

File Name: /global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_04npu8a0_1_1_XYZDB_20121110

File Name: /global/CEXOE/XYZDB/RMAN_CENXCT_BK/control_file_20121110

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

cataloging files...

cataloging done

Step 6:

 Recover the standby database with the cataloged incremental backup pieces.

RMAN> recover database noredo;

Starting recover at 10-NOV-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=701 devtype=DISK

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00004: /global/CEXOE/XYZDB/oradata/group1/XYZDB_tools01a.dbs

channel ORA_DISK_1: restored backup piece 1

piece handle=/global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_06npu8a1_1_1_XYZDB_20121110 tag=TAG20121110T044444

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

channel ORA_DISK_1: starting incremental datafile backupset restore

piece handle=/global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_05npu8a1_1_1_XYZDB_20121110 tag=TAG20121110T044444

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

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

piece handle=/global/CEXOE/XYZDB/RMAN_CENXCT_BK/RmanIncremental_04npu8a0_1_1_XYZDB_20121110 tag=TAG20121110T044444

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

Finished recover at 10-NOV-12


Step 7:

 

Shutdown and startup mount standby database.


Standby Database:


RMAN> shutdown immediate

Oracle instance shut down


RMAN> startup mount


Note: List the files that have had nologging changes applied on the standby database


SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;

no rows selected


SQL> !date

Saturday, November 10, 2012  7:41:28 AM EST


Step 8:


Standby Database:


SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

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

268176


SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/apps/oracle/admin/XYZDB/arch

Oldest online log sequence     268173

Next log sequence to archive   268177

Current log sequence           268177


SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options



Enable the  Manager Recover process


SQL> RECOVER AUTOMATIC STANDBY DATABASE;


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


Step9:


 Primary Database:


SQL>  select max(sequence#) from v$log_history;


MAX(SEQUENCE#)

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

268207


Sql> alter system set log_archive_dest_2_state=enable scope=both;

 

Standby side


SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

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

 268204

-
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Add new mountpoint on your linux server

  Below are the steps to follow for adding any new mount on you linux machine. [root@oem ~]# fdisk -l Disk /dev/sdb: 53.7 GB, 53687091200 by...

  • Add new mountpoint on your linux server
      Below are the steps to follow for adding any new mount on you linux machine. [root@oem ~]# fdisk -l Disk /dev/sdb: 53.7 GB, 53687091200 by...
  • MySQL Background Process
     MySQL Background Process   In MySQL, background processes are responsible for various tasks that support the functioning and performance of...
  • Oracle RAC SCAN
     Oracle RAC SCAN   Oracle RAC SCAN, which stands for Single Client Access Name, is a feature in Oracle Real Application Clusters (RAC) that ...