If primary database is opened with resetlogs option following an incomplete recovery, redo apply service might halt on standby database when it encounters open resetlogs command in redo. But if physical standby database’s SCN is far enough behind the primary database’s SCN, then redo apply service can interpret open resetlogs command without stopping.

Hence , after primary has been opened with resetlogs option, if we flashback standby database to an SCN which is earlier than reset logs SCN# of primary, we won’t have to recreate the standby database.

Let’s implement :

OVERVIEW:

— Backup primary database

— Create restore point before_update

— Perform update

— Restore and recover primary database to restore point before_update

— Check configuration – standby database has diverged from primary

— Flashback standby database to SCN = primary (resetlogs_scn) – 1

— check that primary is back into configuration

IMPLEMENTATION:

–backup database

RMAN>backup database;

– Create restore point before_update

pri> create restore point before_update;

-- check initial salary –

PRI>select salary from hr.employees where employee_id=100;

SALARY

———-

2000

– update salary –

PRI> update hr.employees set salary = 1000 where employee_id = 100;

commit;

alter system switch logfile;

/

-- On primary Restore from backup and recover till before update

PRI>shutdown immediate;

startup mount;

$rman target sys/sys@orcl catalog rcowner/rcowner@rcat

RMAN> restore database;

recover database until restore point before_update;

alter database open resetlogs;

— check that recovery was successful i.e.

salary on primary database is the same as before update

PRI>select salary from hr.employees where employee_id=100;

SALARY

———-

2000

– check configuration –

Note that there is error as standby database has diverged from primary

DGMGRL>show configuration;

orcl – Primary database

sby – Physical standby database

Error: ORA-16810: multiple errors or warnings detected for the database

DGMGRL> show database sby;

Database – sby

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: (unknown)

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

sby

Database Error(s):

ORA-16700: the standby database has diverged from the primary database

ORA-16766: Redo Apply is stopped

– check resetlogs scn from primary database

PRI>select RESETLOGS_CHANGE# from v$database;

RESETLOGS_CHANGE#

—————–

1414101

– check current scn# from standby database

– note that standby database is ahead of reset logs change# and hence needs

to be flashed back

SBY>select current_scn from v$database;

CURRENT_SCN

———–

1414993

–Flashback standby database to 2 SCN earlier than resetlogs_scn–

i.e. 1414101 -2 = 1414099

SBY>shu immediate;

startup mount;

flashback database to scn 1414099 ;

alter database open;

– check that standby database is ready to receive and apply logs from primary;

DGMGRL> show configuration;

Configuration – dgconfig

Protection Mode: MaxPerformance

Databases:

orcl – Primary database

sby – Physical standby database

DGMGRL> show database sby;

Database – sby

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: ON

Instance(s):

sby

Database Status:

SUCCESS

– check that salary on standby database is the same as before update

SBY>select salary from hr.employees where employee_id=100;

SALARY

———-

2000

ITPub博客

11G DATAGUARD: FLASHBACK STANDBY AFTER RESETLOGS ON PRIMARY

最新文章