Tag: alter database create standby controlfile as

Stop the managed recovery process (MRP)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On standby database, get the lowest SCN

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
7.8880E+12

SQL> set numwidth 16
SQL> /

CURRENT_SCN
----------------
7888036454134

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)
----------------
7883734410408

SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY' ; 

MIN(F.FHSCN)
----------------
7883734410408

SQL> select CHECKPOINT_CHANGE# from v$datafile_header;

MIN(CHECKPOINT_CHANGE#)
-----------------------
7883734410408

On primary database, RMAN backup from SCN of previous step

Take a SCN backup, and copy backup onto standby server.

RMAN> BACKUP INCREMENTAL FROM SCN 7883734410408 DATABASE 
      FORMAT '/tmp/testdb_styby_%U' tag 'INCFORSTANDBY';

Prepare Standby ControlFile and New Datafiles

a) Create a standby control file from primary, and then copy onto standby database server. Replace the standby control file by using this newly created one.

-- From primary
--
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/TESTDBSTY.ctl';

-- From Standby
--
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT; 
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/TESTDBSTY.ctl';

b) For new datafiles which are in Primary but not in standby database, copy them from primary to standby database server. Then catalog current all standby database datafiles, since the control file has been replaced by primary one.

RMAN> CATALOG START WITH '+DG1/tetsdbsty/datafile/'; 

c) Switch database to copy from RMAN.

RMAN> SWITCH DATABASE TO COPY;

Recover Standby Database

Catalog incremental backup, then recover standby database by using RMAN incremental backup of primary database.

RMAN> CATALOG START WITH '/tmp/testdb_styby';

RMAN> RECOVER DATABASE NOREDO;

Start the MRP process of standby database.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
     DISCONNECT FROM SESSION;

Check DataGuard Health

There is no gap between primary database and standby database now.

DGMGRL> show configuration;
Configuration - dg_oemrep
Protection Mode: MaxPerformance
Databases:
oemrep -    Primary database
stboemrep - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS

DGMGRL> show database "stboemrep";

Database - stboemrep

Enterprise Manager Name: OEMREP

Role:                    PHYSICAL STANDBY
Intended State:          APPLY-ON
Transport Lag:           0 seconds (computed 1 second ago)
Apply Lag:               0 seconds (computed 1 second ago)
Apply Rate:              4.93 MByte/s
Real Time Query:         OFF
Instance(s):
STBOEMREP

Database Status:
SUCCESS

DGMGRL>