Recovery Needed from SCN for Physical Standby

 Recovery Needed from SCN for Physical Standby:

Last week I fall in a scenario that my physical standby db is in unresolvable gap. I felt recovery is needed. Then I decide to recover the physical standby db from current SCN(System Change Number). I want to share with you the following steps to solve your problem.

#in primary:

----------

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

NAME      DATABASE_ROLE    SWITCHOVER_STATUS

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

db_sid     PRIMARY          UNRESOLVABLE GAP

#in standby:

----------

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

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

PHYSICAL STANDBY RECOVERY NEEDED

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

  239220059

#in primary:

-----------

RUN

{

ALLOCATE CHANNEL CH2 TYPE DISK FORMAT 'path/incr_DF2_%d_%T_%s_%U.bkp';

SQL 'ALTER SYSTEM SWITCH LOGFILE';

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL FROM SCN 239220059 DATABASE TAG BKP_INCR;

BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT 'path/stby0309_%d_%T_%s_%U.ctl';

}

scp incr_DF2.bkp stby.ctl oracle@standby_db_ip:path

#in primary:

----------

SQL> show parameter control_files;

NAME                                 TYPE

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

VALUE

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

control_files                        string

path01/control01.ctl, path02/control02.ctl

[oracle@TEST_DB dbs]$ cd path

[oracle@TEST_DB DRICBS]$ ls

control01.ctl

[oracle@TEST_DB DRICBS]$ mv control01.ctl control01.ctl.back

[oracle@TEST_DB ~]$ cd FRA

[oracle@TEST_DB recovery_area]$ ls

 control02.ctl 

[oracle@TEST_DB recovery_area]$ mv control02.ctl control02.ctl.back

#in standby:

-----------

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1287651328 bytes

Fixed Size                  8631192 bytes

Variable Size             713034856 bytes

Database Buffers          557842432 bytes

Redo Buffers                8142848 bytes

SQL>

RMAN> restore controlfile from path/stby.ctl' 

RMAN> alter database mount;

RMAN> recover database;

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

from primary:

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

SQL> set lines 300 pages 200;

SQL> select GAP_STATUS,TYPE,STATUS,DATABASE_MODE,RECOVERY_MODE,ERROR,SYNCHRONIZED from V$ARCHIVE_DEST_STATUS where DEST_ID=2;

GAP_STATUS               TYPE             STATUS    DATABASE_MODE   RECOVERY_MODE           ERROR                                                   SYN

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

NO GAP                   PHYSICAL         VALID     MOUNTED-STANDBY MANAGED REAL TIME APPLY                                                         NO