Category Archives: 11g DataGuard

RECOVER STANDBY DATAFILE FROM PRIMARY

In this post, I will demonstrate how we can recover a lost/corrupted/inaccessible datafile on standby from primary.

Overview:

— Simulate loss of a datafile on standby database by renaming it.
— Restart standby database – Stops at mount stage as datafile is inaccessible.
— Check that redo apply to standby is stopped.
— Connect to primary database as target and standby as auxiliary.
— Take backup of the affected datafile from primary  so that backup file is created on standby.
— Recover standby database.
— Open standby database.
— check that redo apply has been restrated on standby and configuration is successful again.

– Implementation –

– Check that dataguard configuration is running properly

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

– Find out names of datafiles on standby

SBY>select name from v$datafile;

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

– To simulate loss of the datafile of example tablespace , rename it

SBY>ho mv /u01/app/oracle/oradata/dg02/example01.dbf /u01/app/oracle/oradata/dg02/example.dbf

– Restart standby database
— Stops at mount stage due to missing datafile

SBY>startup force;
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/dg02/system01.dbf'

-- Switch logs on primary and verify that redo apply has stopped on standby

PRI>alter system switch logfile;

DGMGRL>  show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database
 Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

– Using RMAN, connect to primary as target and standby as auxiliary

[oracle@node1 ~]$ . oraenv
ORACLE_SID = [dg01] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

[oracle@node1 ~]$ rman target / auxiliary sys/oracle@dg02

connected to target database: DG01 (DBID=434142737)
connected to auxiliary database: DG01 (DBID=434142737, not open)

RMAN>

– Try to take backupset type of backup of example tablespace on primary so that backup file is created on standby
— Fails as only image copies can be transported over network using RMAN

RMAN> backup tablespace example auxiliary format
2> '/u01/app/oracle/oradata/dg02/example01.dbf';

Starting backup at 26-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/26/2013 14:37:04
RMAN-06955: Network copies are only supported for image copies.

– Take image copy backup of example tablespace on primary so that backup file is created on standby

RMAN> backup as copy  tablespace example auxiliary format
2> '/u01/app/oracle/oradata/dg02/example01.dbf';

Starting backup at 26-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/dg01/example01.dbf
output file name=/u01/app/oracle/oradata/dg02/example01.dbf tag=TAG20131226T143727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 26-DEC-13

– check that image copy copy has been created on standby

SBY>ho ls /u01/app/oracle/oradata/dg02/example01.dbf
/u01/app/oracle/oradata/dg02/example01.dbf

– Recover standby database and open it

SBY>recover managed standby database disconnect;
alter database open;

– Check that redo apply is resumed again and configuration is successful

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Note:  In 12c datafiles can be restored over network using backup sets/ compressed backupsets as shown in this post.

—————————————————————————————————–

Related links:

Home
11g Dataguard Index

11g  DataGuard: Flashback Standby After Resetlogs On Primary11g  DataGuard Setup Using Active DataGuard on RHEL/OEL- 5 
Flashback Through Role Transition For Physical Standby
How to reinstate the old primary as a standby after failover
12c Dataguard: Restore Datafile From Service

—————

Using RMAN Incremental Backups to Refresh Standby Database

In this post , I will demonstrate synchronization of a standby DB from an incremental RMAN backup. It might be needed in following scenarios:

1-The standby database is considerably lagging behind the primary . The copying and applying of archive logs from primary will be time consuming as it will apply both the COMMITED and the NON COMMITED transactions then will ROLLBACK the non committed transactions. Incremental backup will recover the standby database much faster than applying the archives as it will apply only the COMMITED transactions on the standby database .

2-Some archivelogs on the primary which have not been applied to the standby have been lost. In such cases, you can create an incremental backup of the primary database containing changes since the standby database was last refreshed. This incremental backup can be applied to the standby database to synchronize it with a primary database.

Overview:

  • Stop redo transport on primary
  • Switch logs on primary – results in some archived logs on primary which have not been sent to standby
  • Rename newly generated archived logs on primary to simulate their loss
  • Restart redo transport – gives error as gap cannot be resolved due to missing logs
  • Create a control file for standby database on primary
  • Take incremental backup on primary starting from the SCN# of standby database
  • Copy the incremental backup to the standby host and catalog it with RMAN
  • Mount the standby database with newly created standby control file
  • Cancel managed recovery of standby database and apply incremental backup to the standby database
  • Start managed recovery of standby database

Implementation:

– Check current log sequence on primary

PRI>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42

- check that all the archived logs prior to the current log have been sent to standby

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

MAX(SEQUENCE#)
--------------
41

– stop redo transport from primary (dg01)

DGMGRL> show database dg01

Database - dg01

Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
dg01

Database Status:
SUCCESS

DGMGRL> edit database dg01 set state='Transport-off';
Succeeded.

DGMGRL> show database dg01

Database - dg01

Role:            PRIMARY
Intended State:  TRANSPORT-OFF
Instance(s):
dg01

Database Status:
SUCCESS

– switch log on primary

PRI>alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     44
Next log sequence to archive   45
Current log sequence           46

– check that logs after sequence# 42 are not being transported to sby

SBY> select max(sequence#) from v$archived_log
MAX(SEQUENCE#)
--------------
42

– Find out names of archived logs generated on primary which have not been transported to standby

PRI>set line 500
col name for a40
select sequence#, name from v$archived_log where sequence# > 42;

SEQUENCE# NAME
---------- ----------------------------------------
43 /u01/app/oracle/flash_recovery_area/DG01
/archivelog/2013_11_02/o1_mf_1_43_9780s1
ch_.arc

44 /u01/app/oracle/flash_recovery_area/DG01
/archivelog/2013_11_02/o1_mf_1_44_9780tl
t0_.arc

45 /u01/app/oracle/flash_recovery_area/DG01
/archivelog/2013_11_02/o1_mf_1_45_9780tl
vo_.arc

– To simulate loss of archived logs on primary, rename them

PRI>ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_43_9780s1ch_.arc  /home/oracle/arch_43.arc

ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_44_9780tlt0_.arc /home/oracle/arch_44.arc

ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_45_9780tlvo_.arc /home/oracle/arch_45.arc

– Restart redo transport

DGMGRL>  edit database dg01 set state='Transport-on';

– check that gap in redo logs on standby cannot be resolved as some logs are missing on primary

DGMGRL> show database dg01

Database - dg01

Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
dg01

Database Error(s):
 ORA-16783: cannot resolve gap for database dg02

Database Status:
ERROR

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
Error: ORA-16724: cannot resolve gap for one or more standby databases

dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

– check current scn# of standby

SBY>select current_scn from v$database;

CURRENT_SCN
-----------
998647

– Create a standby control file:

PRI>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/standby_control.ctl';

– copy the standby control file to standby host

PRI> !scp /home/oracle/standby_control.ctl node2:/home/oracle/standby_control.ctl

– Take incremental backup on primary starting from scn# of standby database
RMAN uses the selected SCN as the basis for this incremental backup. For all files being backed up, RMAN includes all data blocks that were changed at SCNs greater than or equal to the FROM SCN in the incremental backup.
Note:
• RMAN does not consider the incremental backup as part of a backup strategy at the source database. The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database.

RMAN> Backup incremental from SCN 987005 database tag='FOR_STANDBY' format '/home/oracle/%d_%t_%s_%p';

Starting backup at 02-NOV-13

channel ORA_DISK_1: starting full datafile backup set
...
channel ORA_DISK_1: starting piece 1 at 02-NOV-13
channel ORA_DISK_1: finished piece 1 at 02-NOV-13
piece handle=/home/oracle/DG01_830395300_22_1 tag=FOR_STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

– Move the backup pieces to the standby host

PRI> !scp /home/oracle/DG01_830395300_22_1 node2:/home/oracle/DG01_830395300_22_1

– Catalog the Incremental Backup Files at the Standby Database

RMAN>  catalog backuppiece  '/home/oracle/DG01_830395300_22_1';

list backup tag FOR_STANDBY;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Incr    9.38M      DISK        00:00:00     02-NOV-13
BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: FOR_STANDBY
Piece Name: /home/oracle/DG01_830395300_22_1
Keep: NOLOGS             Until: 09-NOV-13

List of Datafiles in backup set 9

– Find out names of current control files

SQL>  col value for a50
col name for a15
select name, value   from v$parameter where upper(name)= 'CONTROL_FILES';

NAME            VALUE
--------------- --------------------------------------------------
control_files   /u01/app/oracle/oradata/dg02/control01.ctl, /u01/a
pp/oracle/flash_recovery_area/dg02/control02.ctl

- Shutdown the standby database and rename the original control file of the standby database:

SBY> Shu immediate;
!mv /u01/app/oracle/oradata/dg02/control01.ctl /u01/app/oracle/oradata/dg02/control01.bak
!mv /u01/app/oracle/flash_recovery_area/dg02/control02.ctl /u01/app/oracle/flash_recovery_area/dg02/control02.bak

– Restore the standby control file we just copied from the primary

SBY>    ! cp /home/oracle/standby_control.ctl /u01/app/oracle/oradata/dg02/control01.ctl
! cp /home/oracle/standby_control.ctl /u01/app/oracle/flash_recovery_area/dg02/control02.ctl

– Startup the Standby database with the new controlfile:

SBY> startup mount;

– Apply the Incremental Backup to the Standby Database
Use the RMAN RECOVER command with the NOREDO option to apply the incremental backup to the standby database. All changed blocks captured in the incremental backup are updated at the standby database, bringing it up to date with the primary database.

SBY> recover managed standby database cancel;
RMAN> RECOVER DATABASE from tag for_standby NOREDO;

Starting recover at 02-NOV-13
using channel ORA_DISK_1
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/oradata/dg02/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/dg02/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/dg02/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/dg02/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/dg02/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/DG01_830395300_22_1
channel ORA_DISK_1: piece handle=/home/oracle/DG01_830395300_22_1 tag=FOR_STANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 02-NOV-13

Now your standby database is refreshed from the incremental backup.
You can start the Managed Recovery process on the standby DB:

SBY>recover managed standby database disconnect;
Media recovery complete.

You can now resume managed recovery at the standby. Any redo logs required at the standby with changes since those contained in the incremental are automatically requested from the primary and applied.

— check that SCN# of the standby database has advanced.

SBY>select current_scn from v$database;

CURRENT_SCN
-----------
1005729

DGMGRL> edit database dg01 set state='transport-on';
DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Note:
This technique cannot be used to update a duplicate database.

References :

http://dba-tips.blogspot.in/2011/10/refresh-standby-database-from-rman.html

http://www.stanford.edu/dept/itss/docs/oracle/10gR2/backup.102/b14191/rcmdupdb008.htm

———————————————————————————————–

Related Links:

Home
11g Dataguard Index

 

Flashback through role transition for physical standby

In this post, I will demonstrate that database role does not change when a physical standby is flashed back through a switchover.

Overview:
– Note current SCN# (pre switchover)
– Check current configuration
pc00sby1 – Primary database
pc00prmy – Physical standby database
– Perform switchover
– check post switchover configuration
pc00prmy – Primary database
pc00sby1 – Physical standby database
– Flashback new primary (pc00prmy) to SCN# before switchover
– check that there is error in configuration and current standby(pc00sby1) has diverged from the primary as SCN# of standby is ahead of primary
– To bring back the standby database into configuration, flashback it to a scn# before switchover
— check that both the databases have retain their current roles after flashback
pc00prmy – Primary database
pc00sby1 – Physical standby database

– Implementation –

- Check current configuration

pc00sby1 – Primary database
pc00prmy – Physical standby database

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00sby1 - Primary database
pc00prmy - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

– Find out current SCN# (before switchover)

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1100354

– Perform switchover

DGMGRL> switchover to pc00prmy;
Performing switchover NOW, please wait...
New primary database "pc00prmy" is opening...
Operation requires shutdown of instance "pc00sby1" on database "pc00sby1"
Shutting down instance "pc00sby1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "pc00sby1" on database "pc00sby1"
Starting instance "pc00sby1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "pc00prmy"

– check that switchover has taken place successfully
pc00prmy – Primary database
pc00sby1 – Physical standby database

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00prmy - Primary database
pc00sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

PC00SBY1>select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

PC00PRMY>select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

– Flashback new primary (pc00prmy) to SCN# before switchover (1100354)

PC00PRMY>shu immediate;
         startup mount;
         flashback database to scn 1100354;
         alter database open resetlogs;

         alter system switch logfile;

– check that there is error in configuration and current standby(pc00sby1) has diverged from the primary

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00prmy - Primary database
pc00sby1 - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
 ERROR

DGMGRL> show database pc00sby1;

Database - pc00sby1

Enterprise Manager Name: pc00sby1.us.oracle.com
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
pc00sby1

Database Error(s):
 ORA-16700: the standby database has diverged from the primary database
 ORA-16766: Redo Apply is stopped

Database Status:
 ERROR

— The reason for diverging is that SCN# of standby( 1101143) is ahead of primary (1100582)

PC00PRMY>select current_scn from v$database;

CURRENT_SCN
-----------
1100582

PC00SBY1>select current_scn from v$database;

CURRENT_SCN
-----------
1101143

— To bring back the standby database into configuration, we need to flashback it to an scn# behind primary.

– Let’s flashback standby database  to a scn# before switchover (1100354) since we want to verify that databases maintain their current role after flashback.

PC00SBY1>shu immediate;
startup mount;
flashback database to scn 1100354;

alter database open;

PC00SBY1>select current_scn from v$database;

CURRENT_SCN
-----------
1100354

– switch log on primary –

PC00PRMY>alter system switch logfile;

System altered.

– check configuration –
— Both the databasese retain their current roles –
pc00prmy – Primary database
pc00sby1 – Physical standby database

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00prmy - Primary database
pc00sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

I hope this post was useful.

Enjoy!

———————————————————————————–

Related links:

————————————————————————–

ORA-01102: cannot mount database in EXCLUSIVE mode

I received this error message when I was trying to mount the primary database in my dataguard configuration.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01102: cannot mount database in EXCLUSIVE mode

Here is how I resolved it:

- Shutdown the primary database

SQL> shu immediate;
ORA-01507: database not mounted

– Find out all the processes belonging to the database still running

[oracle@EDP1R3P0-pc00sby1 dbs]$ ps -ef |grep ora_|grep pc00sb

– Kill all the processes

[oracle@EDP1R3P0-pc00sby1 dbs]$ kill -9 32231

– Check that there are no more processes running

[oracle@EDP1R3P0-pc00sby1 dbs]$ ps -ef |grep ora_|grep pc00sb

– startup the database – succeeeds

SQL> startup
ORACLE instance started.

Total System Global Area  439406592 bytes
Fixed Size                  1337072 bytes
Variable Size             348129552 bytes
Database Buffers           83886080 bytes
Redo Buffers                6053888 bytes
Database mounted.
Database opened.

—————————————————————-
Related links:

Home

11g R2 RAC Index

INS-08109 unexpected error occured while validating inputs at state ‘InstallOptions’
ORA-15040: diskgroup is incomplete
PROT-35: The configured Oracle Cluster Registry locations are not accessible
PRVF-5636 : The DNS response time for an unreachable node exceeded 15000 ms

 

ORA-12528: TNS:listener: all appropriate instances are blocking new connection

I  got this error when I was configuring 11g Dataguard using active dataguard.
 On my standby machine, I had configured listener on port 1521 in grid home and had registered the service for standby database (dg02) with this listener statically. In my primary tnsnames.ora , I had made an entry for dg02 on standby machine on port 1521.
While creating standby database,duplicate database command was being issued from database home on primary and it expected listener to be running in database home on standby machine and hence giving above error.
I created another listener on port 1522 in database home on standby machine and registered the service for standby database (dg02) with this listener statically. In my primary tnsnames.ora ,  I modified the entry for dg02 on standby machine on port 1522.
This resolved my problem.Hence, it can be concluded   that if we have two machines say node1 and node2.
On node1, I have a database db1  which I want to access from node2.
I have a listener on port 1521 in grid home.On node2 I create an entry in tnsnames.ora for db1 available on node1  on port 1521.Now If I try to connect to db1 from node2, I will get above error. Since tnsnames.ora entry is created in
database home,  while connecting to node1, it is expected that listener is also available in database home
on node1.Hence, if I move the listener on node1  from grid home  to database home, the problem will be resolved
or I can
–  create another listener on node1 in database home on port 1522(say) ,
– Make service for db1 available at port 1522
– Register this service statically with listener on port 1522
– create an entry in tnsnames.ora on node2 for db1 available on node1 at port 1522

References:
————————————————————————————————-

11g Dataguard Index

Warning: ORA-16792: configurable property value is inconsistent with database setting

I got this error on DGMGRL command line when I was configuring 11g Dataguard and I issued

DGMGRL> show configuration;

 

This error was coming as my standby database was started using pfile,

I shutdown the standby database and restarted it using spfile and the problem was resolved.

Another time I got this error was when I changed the IP address of the machine after dataguard had been configured. I deleted the tnsnames.ora and listener.ora entries and recreated them. Then it started working.

———————————————————————————————————-

Related links:
                                                                ————

11G DATAGUARD: FLASHBACK STANDBY AFTER RESETLOGS ON PRIMARY

   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
  Thanks for your time !!!
   Keep visiting the blog….
                                                   ———————————
Related links:

11g DataGuard : Automatic Client Failover

Flashback Through Role Transition For Physical Standby

                                                      ———————-