DBA survival BLOG

DBA stuff and Oracle Data Guard

Reinstate an Oracle Database after failover without Flashback logging enabled

Posted on December 16, 2025 by Ludovico

The key difference between a switchover and a failover in Data Guard is the synchronization between primary and standby to avoid data loss.

Switchover
A switchover is a planned event. The primary and standby databases synchronize to allow a smooth, lossless role reversal:

The primary database stops the activity.
It writes a “redo marker” to the redo stream.
It flushes all the remaining redo to the standby.
The standby applies all the redo and, upon reaching the marker, knows it’s up to date and can open as the new primary.
Finally, the old primary becomes a standby and starts recovering changes from the new primary.

Failover
A failover usually happens after the primary becomes unavailable. Unlike a switchover, the new primary may not have all the latest redo from the old primary, even in synchronous mode because in-progress changes can exist past the last transmitted redo. As a result, their timelines diverge. The former primary (and possibly other standbys) must be reinstated to rejoin the configuration.

Flashback and Reinstate
Oracle’s Flashback Database feature allows you to rewind a database to a specific point in time. This makes recovering a failed primary much faster and easier because it does not require a full restore.
With Flashback enabled, the Data Guard Broker can quickly reinstate the former primary after a failover with a single command:

REINSTATE DATABASE {db_unique_name};

1	REINSTATE DATABASE {db_unique_name};

A customer recently asked how to reinstate a former primary when Flashback Logging is disabled.
Although we strongly recommend enabling Flashback Logging, some environments cannot support it due to storage constraints.
In such cases, you can still reinstate the database manually. Here’s how.

Step-by-step: reinstate a database from a backup and re-enable it in the broker

We are in a Maximum Performance configuration:

DGMGRL> show config

Configuration - adgvec_site0_adgvec_d7q_lhr

  Protection Mode: MaxPerformance
  Members:
  adgvec_site0   - Primary database
    adgvec_d7q_lhr - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 43 seconds ago)

DGMGRL> show config

Configuration - adgvec_site0_adgvec_d7q_lhr

Protection Mode: MaxPerformance

Members:

adgvec_site0 - Primary database

adgvec_d7q_lhr - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 43 seconds ago)

Let’s fail over:

DGMGRL> failover to adgvec_d7q_lhr immediate;
2025-12-16T09:58:55.928+00:00
Performing failover NOW, please wait...

2025-12-16T09:59:08.548+00:00
Failover succeeded, new primary is "adgvec_d7q_lhr".

2025-12-16T09:59:08.548+00:00
Failover processing complete, broker ready.
DGMGRL> exit

DGMGRL> failover to adgvec_d7q_lhr immediate;

2025-12-16T09:58:55.928+00:00

Performing failover NOW, please wait...

2025-12-16T09:59:08.548+00:00

Failover succeeded, new primary is "adgvec_d7q_lhr".

2025-12-16T09:59:08.548+00:00

Failover processing complete, broker ready.

DGMGRL> exit

Then restart the former primary in MOUNT mode:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area   15544938664 bytes      
Fixed Size                     4939944 bytes      
Variable Size               2650800128 bytes      
Database Buffers           12750684160 bytes      
Redo Buffers                 138514432 bytes      
Database mounted.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 15544938664 bytes

Fixed Size 4939944 bytes

Variable Size 2650800128 bytes

Database Buffers 12750684160 bytes

Redo Buffers 138514432 bytes

Database mounted.

The broker now says the former primary requires a reinstate:

DGMGRL> show config

Configuration - adgvec_site0_adgvec_d7q_lhr

  Protection Mode: MaxPerformance
  Members:
  adgvec_d7q_lhr - Primary database
    adgvec_site0   - Physical standby database (disabled)
      ORA-16661: The standby database must be reinstated.

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 15 seconds ago)

DGMGRL> show config

Configuration - adgvec_site0_adgvec_d7q_lhr

Protection Mode: MaxPerformance

Members:

adgvec_d7q_lhr - Primary database

adgvec_site0 - Physical standby database (disabled)

ORA-16661: The standby database must be reinstated.

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 15 seconds ago)

As I said, we can’t proceed with a normal reinstate if there’s no flashback:

DGMGRL> reinstate database adgvec_site0;
2025-12-16T10:07:22.733+00:00
Reinstating database "adgvec_site0", please wait...

Error: ORA-16827: Flashback Database is disabled.

Failed.
2025-12-16T10:07:22.942+00:00
Reinstatement of database "adgvec_site0" failed

2025-12-16T10:07:22.942+00:00
Reinstate processing complete, broker ready.

DGMGRL> reinstate database adgvec_site0;

2025-12-16T10:07:22.733+00:00

Reinstating database "adgvec_site0", please wait...

Error: ORA-16827: Flashback Database is disabled.

Failed.

2025-12-16T10:07:22.942+00:00

Reinstatement of database "adgvec_site0" failed

2025-12-16T10:07:22.942+00:00

Reinstate processing complete, broker ready.

Let’s restore! This is the only way to bring a database to a past point in time if there is no flashback logging.

First, we put the DB in nomount mode to restore the standby controlfile:

SQL> startup force nomount;
ORACLE instance started.

Total System Global Area 1.5545E+10 bytes
Fixed Size		    4939944 bytes
Variable Size		 2650800128 bytes
Database Buffers	 1.2751E+10 bytes
Redo Buffers		  138514432 bytes

SQL> startup force nomount;

ORACLE instance started.

Total System Global Area 1.5545E+10 bytes

Fixed Size 4939944 bytes

Variable Size 2650800128 bytes

Database Buffers 1.2751E+10 bytes

Redo Buffers 138514432 bytes

Then we wipe out the directory that contains the DB (careful with this command!)

$ rm -rf * /u02/app/oracle/oradata/adgvec_site0/ADGVEC_SITE0

1	$ rm -rf * /u02/app/oracle/oradata/adgvec_site0/ADGVEC_SITE0

We restore the standby controlfile and the database:

RMAN> restore standby controlfile from service adgvec_d7q_lhr;
restore standby controlfile from service adgvec_d7q_lhr;
Starting restore at 16-DEC-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=331 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/app/oracle/oradata/adgvec_site0/control01.ctl
output file name=/u03/app/oracle/fast_recovery_area/ADGVEC_SITE0/control02.ctl
Finished restore at 16-DEC-25

RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed

RMAN> restore database from service adgvec_d7q_lhr;
restore database from service adgvec_d7q_lhr;
Starting restore at 16-DEC-25
Starting implicit crosscheck backup at 16-DEC-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=486 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 16-DEC-25

Starting implicit crosscheck copy at 16-DEC-25
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-DEC-25

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/app/oracle/fast_recovery_area/ADGVEC_SITE0/autobackup/2025_07_25/o1_mf_s_1207415482_n87gotv5_.bkp
File Name: /u03/app/oracle/fast_recovery_area/ADGVEC_SITE0/archivelog/2025_10_31/o1_mf_1_172_njbddl2f_.arc
...

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/datafile/o1_mf_system_n87ggxdv_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/32F00060B1F21FF6E0633A875E64E7C7/datafile/o1_mf_system_n87ggxhw_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/datafile/o1_mf_sysaux_n87ggxn0_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/32F00060B1F21FF6E0633A875E64E7C7/datafile/o1_mf_sysaux_n87ggxqm_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/datafile/o1_mf_users_n87gh76c_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/32F00060B1F21FF6E0633A875E64E7C7/datafile/o1_mf_undotbs1_n87gh97l_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/datafile/o1_mf_undotbs1_n87ghb6x_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/330D3A56F99F26B6E0635C08F40AF437/datafile/o1_mf_system_n87ghdl5_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/330D3A56F99F26B6E0635C08F40AF437/datafile/o1_mf_sysaux_n87ghdny_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/330D3A56F99F26B6E0635C08F40AF437/datafile/o1_mf_undotbs1_n87ghdrp_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/330D3A56F99F26B6E0635C08F40AF437/datafile/o1_mf_users_n87ghhw8_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 16-DEC-25

RMAN>

100

101

102

RMAN> restore standby controlfile from service adgvec_d7q_lhr;

restore standby controlfile from service adgvec_d7q_lhr;

Starting restore at 16-DEC-25

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=331 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

channel ORA_DISK_1: restoring control file

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

output file name=/u02/app/oracle/oradata/adgvec_site0/control01.ctl

output file name=/u03/app/oracle/fast_recovery_area/ADGVEC_SITE0/control02.ctl

Finished restore at 16-DEC-25

RMAN> alter database mount;

released channel: ORA_DISK_1

Statement processed

RMAN> restore database from service adgvec_d7q_lhr;

restore database from service adgvec_d7q_lhr;

Starting restore at 16-DEC-25

Starting implicit crosscheck backup at 16-DEC-25

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=486 device type=DISK

Crosschecked 6 objects

Finished implicit crosscheck backup at 16-DEC-25

Starting implicit crosscheck copy at 16-DEC-25

using channel ORA_DISK_1

Finished implicit crosscheck copy at 16-DEC-25

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u03/app/oracle/fast_recovery_area/ADGVEC_SITE0/autobackup/2025_07_25/o1_mf_s_1207415482_n87gotv5_.bkp

File Name: /u03/app/oracle/fast_recovery_area/ADGVEC_SITE0/archivelog/2025_10_31/o1_mf_1_172_njbddl2f_.arc

...

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00001 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/datafile/o1_mf_system_n87ggxdv_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/32F00060B1F21FF6E0633A875E64E7C7/datafile/o1_mf_system_n87ggxhw_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/datafile/o1_mf_sysaux_n87ggxn0_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/32F00060B1F21FF6E0633A875E64E7C7/datafile/o1_mf_sysaux_n87ggxqm_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00007 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/datafile/o1_mf_users_n87gh76c_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00009 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/32F00060B1F21FF6E0633A875E64E7C7/datafile/o1_mf_undotbs1_n87gh97l_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00011 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/datafile/o1_mf_undotbs1_n87ghb6x_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00012 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/330D3A56F99F26B6E0635C08F40AF437/datafile/o1_mf_system_n87ghdl5_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00013 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/330D3A56F99F26B6E0635C08F40AF437/datafile/o1_mf_sysaux_n87ghdny_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00014 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/330D3A56F99F26B6E0635C08F40AF437/datafile/o1_mf_undotbs1_n87ghdrp_.dbf

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

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service adgvec_d7q_lhr

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

channel ORA_DISK_1: restoring datafile 00015 to /u02/app/oracle/oradata/adgvec_d7q_lhr/ADGVEC_D7Q_LHR/330D3A56F99F26B6E0635C08F40AF437/datafile/o1_mf_users_n87ghhw8_.dbf

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

Finished restore at 16-DEC-25

RMAN>

Now let’s enable the freshly reinstated standby database:

DGMGRL> enable database adgvec_site0;
Enabled.

DGMGRL> show configuration

Configuration - adgvec_site0_adgvec_d7q_lhr

  Protection Mode: MaxPerformance
  Members:
  adgvec_d7q_lhr - Primary database
    adgvec_site0   - Physical standby database 
      Warning: ORA-16809: Multiple warnings detected for the member.

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 8 seconds ago)

DGMGRL> enable database adgvec_site0;

Enabled.

DGMGRL> show configuration

Configuration - adgvec_site0_adgvec_d7q_lhr

Protection Mode: MaxPerformance

Members:

adgvec_d7q_lhr - Primary database

adgvec_site0 - Physical standby database

Warning: ORA-16809: Multiple warnings detected for the member.

Fast-Start Failover: Disabled

Configuration Status:

WARNING (status updated 8 seconds ago)

DGMGRL> show database verbose adgvec_site0 

Database - adgvec_site0

  Role:                PHYSICAL STANDBY
  Intended State:      APPLY-ON
  Transport Lag:       (unknown)
  Apply Lag:           6 minutes 41 seconds (computed 18 seconds ago)
  Average Apply Rate:  (unknown)
  Active Apply Rate:   (unknown)
  Maximum Apply Rate:  (unknown)
  Real Time Query:     OFF
  Instance(s):
    adgvec

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold
    ORA-16856: transport lag could not be determined
    ORA-16826: apply service state is inconsistent with the DelayMins property

...
Database Status:
WARNING

DGMGRL> show database verbose adgvec_site0

Database - adgvec_site0

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: (unknown)

Apply Lag: 6 minutes 41 seconds (computed 18 seconds ago)

Average Apply Rate: (unknown)

Active Apply Rate: (unknown)

Maximum Apply Rate: (unknown)

Real Time Query: OFF

Instance(s):

adgvec

Database Warning(s):

ORA-16853: apply lag has exceeded specified threshold

ORA-16856: transport lag could not be determined

ORA-16826: apply service state is inconsistent with the DelayMins property

...

Database Status:

WARNING

Oups, I made a mistake. The Real-Time Apply isn’t working. Of course! I also deleted the standby logs, so I need to clear them.

DGMGRL> edit database adgvec_site0 set state='APPLY-OFF';
Succeeded.

1 2	DGMGRL> edit database adgvec_site0 set state='APPLY-OFF'; Succeeded.

SQL> alter database clear logfile group 4, group 5, group 6, group 7;

Database altered.

SQL> alter database clear logfile group 4, group 5, group 6, group 7;

Database altered.

DGMGRL> edit database adgvec_site0 set state='APPLY-ON';
Succeeded.

1 2	DGMGRL> edit database adgvec_site0 set state='APPLY-ON'; Succeeded.

DGMGRL> sql 'alter system switch logfile';
Succeeded.
DGMGRL> show configuration verbose;

Configuration - adgvec_site0_adgvec_d7q_lhr

  Protection Mode: MaxPerformance
  Members:
  adgvec_d7q_lhr - Primary database
    adgvec_site0   - Physical standby database 

  Properties:
    BystandersFollowRoleChange      = 'ALL'
    CommunicationTimeout            = '180'
    ConfigurationSimpleName         = 'adgvec_site0_adgvec_d7q_lhr'
    ConfigurationWideServiceName    = 'adgvec_CFG'
    DrainTimeout                    = '0'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverLagGraceTime   = '0'
    FastStartFailoverLagLimit       = '30'
    FastStartFailoverLagType        = 'APPLY'
    FastStartFailoverPmyShutdown    = 'TRUE'
    FastStartFailoverThreshold      = '30'
    ObserverOverride                = 'FALSE'
    ObserverPingInterval            = '0'
    ObserverPingRetry               = '0'
    ObserverReconnect               = '0'
    OperationTimeout                = '30'
    PrimaryDatabaseCandidates       = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    TraceLevel                      = 'USER'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL> sql 'alter system switch logfile';

Succeeded.

DGMGRL> show configuration verbose;

Configuration - adgvec_site0_adgvec_d7q_lhr

Protection Mode: MaxPerformance

Members:

adgvec_d7q_lhr - Primary database

adgvec_site0 - Physical standby database

Properties:

BystandersFollowRoleChange = 'ALL'

CommunicationTimeout = '180'

ConfigurationSimpleName = 'adgvec_site0_adgvec_d7q_lhr'

ConfigurationWideServiceName = 'adgvec_CFG'

DrainTimeout = '0'

ExternalDestination1 = ''

ExternalDestination2 = ''

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverLagGraceTime = '0'

FastStartFailoverLagLimit = '30'

FastStartFailoverLagType = 'APPLY'

FastStartFailoverPmyShutdown = 'TRUE'

FastStartFailoverThreshold = '30'

ObserverOverride = 'FALSE'

ObserverPingInterval = '0'

ObserverPingRetry = '0'

ObserverReconnect = '0'

OperationTimeout = '30'

PrimaryDatabaseCandidates = ''

PrimaryLostWriteAction = 'CONTINUE'

TraceLevel = 'USER'

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS

Everything looks good now.

HTH
Ludovico

Script that duplicates a database using a physical standby RAC as source

Posted on March 18, 2012 by Ludovico

It’s possibile to duplicate a database for testing purposes (it’s an example) using a standby database as source. This allows you to off-load the production environment.

This is a simple script that makes use of ASM and classic duplicate, although I guess it’s possible to use the standby DB for a duplicate from active database.
You can launch it everyday to align your test env at a point in time.

#!/bin/bash
if [ $USER != 'oracle' ] ; then
        echo "need to be oracle"
        exit 0
fi

. $HOME/set11
export ORACLE_SID=test1

srvctl stop database -d test -o immediate

## this is supposed to be a script that erase your ASM  from your old test dbfiles:
## it's as simple as running with the CORRECT ENV:
## asmcmd rm -rf \
##   +DATA/TEST/ONLINELOG
##   +DATA/TEST/DATAFILE
##   +DATA/TEST/CONTROLFILE
##   +DATA/TEST/TEMPFILE
##   +FRA/TEST/ONLINELOG
##   +FRA/TEST/CONTROLFILE

ssh grid@testsrv /shared/refresh_test/remove_test_files.sh

sqlplus / as sysdba < <EOF
        set echo on
        startup nomount
        alter system set cluster_database=false scope=spfile;
        shutdown immediate
        startup nomount
EOF

rman <<EOF
connect target sys/mystrongpassword@stdby
connect auxiliary /

duplicate database to 'test' until time "trunc(sysdate)+6/24";

EOF


sqlplus / as sysdba <<EOF
        set echo on
        alter system set cluster_database=true scope=spfile;
        shutdown immediate
        startup mount
        alter database noarchivelog;
        shutdown immediate
EOF


srvctl start database -d test

#!/bin/bash

if [ $USER != 'oracle' ] ; then

echo "need to be oracle"

exit 0

. $HOME/set11

export ORACLE_SID=test1

srvctl stop database -d test -o immediate

## this is supposed to be a script that erase your ASM from your old test dbfiles:

## it's as simple as running with the CORRECT ENV:

## asmcmd rm -rf \

## +DATA/TEST/ONLINELOG

## +DATA/TEST/DATAFILE

## +DATA/TEST/CONTROLFILE

## +DATA/TEST/TEMPFILE

## +FRA/TEST/ONLINELOG

## +FRA/TEST/CONTROLFILE

ssh grid@testsrv /shared/refresh_test/remove_test_files.sh

sqlplus / as sysdba < <EOF

set echo on

startup nomount

alter system set cluster_database=false scope=spfile;

shutdown immediate

startup nomount

EOF

rman <<EOF

connect target sys/mystrongpassword@stdby

connect auxiliary /

duplicate database to 'test' until time "trunc(sysdate)+6/24";

EOF

sqlplus / as sysdba <<EOF

set echo on

alter system set cluster_database=true scope=spfile;

shutdown immediate

startup mount

alter database noarchivelog;

shutdown immediate

EOF

srvctl start database -d test

Dataguard check script for Real Application Clusters (MAA)

Posted on December 31, 2010 by Ludovico

Two years after my posts:
Quick Oracle Dataguard check script and More about Dataguard and how to check it I faced a whole new Dataguard between two Oracle Real Application Clusters, aka Oracle Maximum Availability Architecture (MAA).

This enviromnent is relying on Windows OS. Don’t know how this could be called “availability” but here we are. I revisited my scripts in a quick and very dirty way. Please consider that I did copy and paste to check the alignment once per thread, but it should be improved with some kind of iteration to check each thread in a more structured fashion.

#!D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe -w
use DBI;
use DBD::Oracle qw(:ora_session_modes);
# DB connection #
my $prod  = "prod";
my $stby = "stby";
my $prodh;
unless ($prodh = DBI-&gt;connect('dbi:Oracle:'.$prod, 
    'sys', 'strongpwd', 
    {PrintError=&gt;0, AutoCommit =&gt; 0,
    ora_session_mode =&gt; ORA_SYSDBA}))  {
print "Error connecting to DB: $DBI::errstr\n";
exit(1);
}
$prodh-&gt;{RaiseError}=1;

my $stbyh;
unless ($stbyh = DBI-&gt;connect('dbi:Oracle:'.$stby,
    'sys', 'strongpwd',
    {PrintError=&gt;0, AutoCommit =&gt; 0,
    ora_session_mode =&gt; ORA_SYSDBA}))  {
print "Error connecting to DB: $DBI::errstr\n";
$prodh-&gt;disconnect;
exit(1);
}
$stbyh-&gt;{RaiseError}=1;

my $sth;
### query stdby MRP0
$sth = $stbyh-&gt;prepare( &lt;&lt;EOSQL );
select thread#, SEQUENCE#, BLOCK#
    from gv\$managed_standby 
    where process='MRP0'
EOSQL
$sth-&gt;execute();
my ($mrpthread, $mrpsequence, $mrpblock) = $sth-&gt;fetchrow_array();
$sth-&gt;finish();

### query stdby RFS
$sth = $stbyh-&gt;prepare( &lt;&lt;EOSQL );
select thread#, SEQUENCE#, BLOCK#
    from gv\$managed_standby 
    where process='RFS' and client_process='LGWR' order by thread#
EOSQL
$sth-&gt;execute();
my ($rfsthread1, $rfssequence1, $rfsblock1) = $sth-&gt;fetchrow_array();
my ($rfsthread2, $rfssequence2, $rfsblock2) = $sth-&gt;fetchrow_array();
$sth-&gt;finish();

### query prod
$sth = $prodh-&gt;prepare( &lt;&lt;EOSQL );
select thread#, SEQUENCE#, BLOCK#
    from gv\$managed_standby
    where process='LNS' order by thread#
EOSQL
$sth-&gt;execute();
my ($pthread1, $psequence1, $pblock1) = $sth-&gt;fetchrow_array();
my ($pthread2, $psequence2, $pblock2) = $sth-&gt;fetchrow_array();
$sth-&gt;finish();


printf ("ENVIRONM  Thread Sequence   Block\n");
printf ("--------- ------ ---------- ----------\n");
printf ("PROD     LNS1  1 %10d %10d\n", $psequence1, $pblock1);
printf ("STANDBY  RFS1  1 %10d %10d\n", $rfssequence1, $rfsblock1);
printf ("PROD     LSN2  2 %10d %10d\n", $psequence2, $pblock2);
printf ("STANDBY  RFS2  2 %10d %10d\n", $rfssequence2, $rfsblock2);
printf ("STANDBY  MRP0  %d %10d %10d\n", $mrpthread, $mrpsequence, $mrpblock);

my $psequence;
my $pblock;
if ( $mrpthread == 1 ) {
$psequence=$psequence1;
$pblock=$pblock1;
} else {
$psequence=$psequence2;
$pblock=$pblock2;
}

$sth = $stbyh-&gt;prepare( &lt;&lt;EOSQL );
select nvl(sum(blocks),0)
+ $pblock - $mrpblock as BLOCK_GAP
from gv\$archived_log
where thread#=$mrpthread and sequence#
between $mrpsequence and $psequence
EOSQL
$sth-&gt;execute();
my ($mrpblockgap) = $sth-&gt;fetchrow_array();
$sth-&gt;finish();

$sth = $stbyh-&gt;prepare( &lt;&lt;EOSQL );
select nvl(sum(blocks),0)
+ $pblock1 - $rfsblock1 as BLOCK_GAP
from gv\$archived_log
where thread#=1 and sequence#
between $rfssequence1 and $psequence1
EOSQL
$sth-&gt;execute();
my ($rfsblockgap1) = $sth-&gt;fetchrow_array();
$sth-&gt;finish();

$sth = $stbyh-&gt;prepare( &lt;&lt;EOSQL );
select nvl(sum(blocks),0)
+ $pblock2 - $rfsblock2 as BLOCK_GAP
from gv\$archived_log
where thread#=2 and sequence#
between $rfssequence2 and $psequence2
EOSQL
$sth-&gt;execute();
my ($rfsblockgap2) = $sth-&gt;fetchrow_array();
$sth-&gt;finish();
printf ("\n\n%-10d blocks gap in TRANSMISSION\n", $rfsblockgap1+$rfsblockgap2);
printf ("%-10d blocks gap in APPLY (MRP0)\n", $mrpblockgap);

$stbyh-&gt;disconnect;
$prodh-&gt;disconnect;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

#!D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe -w

use DBI;

use DBD::Oracle qw(:ora_session_modes);

# DB connection #

my $prod = "prod";

my $stby = "stby";

my $prodh;

unless ($prodh = DBI->connect('dbi:Oracle:'.$prod,

'sys', 'strongpwd',

{PrintError=>0, AutoCommit => 0,

ora_session_mode => ORA_SYSDBA})) {

print "Error connecting to DB: $DBI::errstr\n";

exit(1);

}

$prodh->{RaiseError}=1;

my $stbyh;

unless ($stbyh = DBI->connect('dbi:Oracle:'.$stby,

'sys', 'strongpwd',

{PrintError=>0, AutoCommit => 0,

ora_session_mode => ORA_SYSDBA})) {

print "Error connecting to DB: $DBI::errstr\n";

$prodh->disconnect;

exit(1);

}

$stbyh->{RaiseError}=1;

my $sth;

### query stdby MRP0

$sth = $stbyh->prepare( <<EOSQL );

select thread#, SEQUENCE#, BLOCK#

from gv\$managed_standby

where process='MRP0'

EOSQL

$sth->execute();

my ($mrpthread, $mrpsequence, $mrpblock) = $sth->fetchrow_array();

$sth->finish();

### query stdby RFS

$sth = $stbyh->prepare( <<EOSQL );

select thread#, SEQUENCE#, BLOCK#

from gv\$managed_standby

where process='RFS' and client_process='LGWR' order by thread#

EOSQL

$sth->execute();

my ($rfsthread1, $rfssequence1, $rfsblock1) = $sth->fetchrow_array();

my ($rfsthread2, $rfssequence2, $rfsblock2) = $sth->fetchrow_array();

$sth->finish();

### query prod

$sth = $prodh->prepare( <<EOSQL );

select thread#, SEQUENCE#, BLOCK#

from gv\$managed_standby

where process='LNS' order by thread#

EOSQL

$sth->execute();

my ($pthread1, $psequence1, $pblock1) = $sth->fetchrow_array();

my ($pthread2, $psequence2, $pblock2) = $sth->fetchrow_array();

$sth->finish();

printf ("ENVIRONM Thread Sequence Block\n");

printf ("--------- ------ ---------- ----------\n");

printf ("PROD LNS1 1 %10d %10d\n", $psequence1, $pblock1);

printf ("STANDBY RFS1 1 %10d %10d\n", $rfssequence1, $rfsblock1);

printf ("PROD LSN2 2 %10d %10d\n", $psequence2, $pblock2);

printf ("STANDBY RFS2 2 %10d %10d\n", $rfssequence2, $rfsblock2);

printf ("STANDBY MRP0 %d %10d %10d\n", $mrpthread, $mrpsequence, $mrpblock);

my $psequence;

my $pblock;

if ( $mrpthread == 1 ) {

$psequence=$psequence1;

$pblock=$pblock1;

} else {

$psequence=$psequence2;

$pblock=$pblock2;

}

$sth = $stbyh->prepare( <<EOSQL );

select nvl(sum(blocks),0)

+ $pblock - $mrpblock as BLOCK_GAP

from gv\$archived_log

where thread#=$mrpthread and sequence#

between $mrpsequence and $psequence

EOSQL

$sth->execute();

my ($mrpblockgap) = $sth->fetchrow_array();

$sth->finish();

$sth = $stbyh->prepare( <<EOSQL );

select nvl(sum(blocks),0)

+ $pblock1 - $rfsblock1 as BLOCK_GAP

from gv\$archived_log

where thread#=1 and sequence#

between $rfssequence1 and $psequence1

EOSQL

$sth->execute();

my ($rfsblockgap1) = $sth->fetchrow_array();

$sth->finish();

$sth = $stbyh->prepare( <<EOSQL );

select nvl(sum(blocks),0)

+ $pblock2 - $rfsblock2 as BLOCK_GAP

from gv\$archived_log

where thread#=2 and sequence#

between $rfssequence2 and $psequence2

EOSQL

$sth->execute();

my ($rfsblockgap2) = $sth->fetchrow_array();

$sth->finish();

printf ("\n\n%-10d blocks gap in TRANSMISSION\n", $rfsblockgap1+$rfsblockgap2);

printf ("%-10d blocks gap in APPLY (MRP0)\n", $mrpblockgap);

$stbyh->disconnect;

$prodh->disconnect;

Please foreward me every improvement you implement over my code: it would be nice to post it here.

More about Dataguard and how to check it

Posted on February 6, 2009 by Ludovico

After my post Quick Oracle Dataguard check script I have some considerations to add:
to check the gap of applied log stream by MRP0 process it’s sufficient to replace this query in the perl script I posted:

 select SEQUENCE#, BLOCK# from v\$managed_standby
        where process='RFS' and client_process='LGWR'

1 2	select SEQUENCE#, BLOCK# from v\$managed_standby where process='RFS' and client_process='LGWR'

with this new one:

 select SEQUENCE#, BLOCK# from v\$managed_standby
        where process='MRP0'

1 2	select SEQUENCE#, BLOCK# from v\$managed_standby where process='MRP0'

To check this you have to meet the following condition: You should have real-time apply enabled (and possibly NODELAY clause specified in your recover statement). Check it with this query:

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

1	SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

It should be “MANAGED REAL TIME APPLY”.
If not using realtime apply your MRP0 process will wait until you have a new archive, so even if you have redo transport mode set to LGWR you’ll wait for standby log completion. Your gap of applied redo stream will be at least one sequence#.

With transport mode set to LGWR and real-time apply the output of the perl script is similar to this one:

# ./checkDataGuard.sh
PROD   :       1230      20631
STANDBY:       1230      20613
18         blocks gap

# ./checkDataGuard.sh

PROD : 1230 20631

STANDBY: 1230 20613

18 blocks gap

The whole gap between your primary and standby database should be LOW.