How to reinstate the old Primary as a Standby after Failover in #Oracle

reinstate

You have done a failover to your Standby database so it becomes the new Primary. It may be possible to convert the old Primary into a Standby database now instead of having to do a time consuming duplicate again. The old Primary must have been running in flashback mode before the failover. The playground:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Members:
  prima - Primary database
    physt - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 36 seconds ago)

SYS@prima > host echo kaputt > /home/oracle/prima/users01.dbf

SYS@prima > select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 4: '/home/oracle/prima/users01.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 130

I did just cause a damage on my Primary database in order to have a reason to failover. I took backups on the Primary previously and could do restore and recovery instead of the failover. But failover ist way faster. After all, that is what we have the Standby for 🙂

[oracle@uhesse scripts]$ dgmgrl sys/oracle@physt
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> failover to physt;
Performing failover NOW, please wait...
Failover succeeded, new primary is "physt"

Took less than 10 seconds. Now I want a Standby for my new Primary. If the old Primary has had a power outage only, the Reinstate could be done immediately. But here, one datafile is damaged. Reinstate needs intact datafiles. Therefore first a restore, then reinstate:

[oracle@uhesse scripts]$ rman target sys/oracle@prima

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Sep 15 16:58:24 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     838860800 bytes

Fixed Size                     2929936 bytes
Variable Size                490736368 bytes
Database Buffers             339738624 bytes
Redo Buffers                   5455872 bytes

RMAN> restore datafile 4;

Starting restore at 2016-09-15 16:59:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=176 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/prima/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fra/PRIMA/backupset/2016_09_15/o1_mf_nnndf_TAG20160915T163533_cxomgq0q_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/fra/PRIMA/backupset/2016_09_15/o1_mf_nnndf_TAG20160915T163533_cxomgq0q_.bkp tag=TAG20160915T163533
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2016-09-15 16:59:05

RMAN> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

RMAN> exit


Recovery Manager complete.
[oracle@uhesse scripts]$ dgmgrl sys/oracle@physt
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Members:
  physt - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    prima - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 17 seconds ago)

DGMGRL> reinstate database prima;
Reinstating database "prima", please wait...
Reinstatement of database "prima" succeeded
DGMGRL> show database prima;

Database - prima

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

Database Status:
SUCCESS

Optionally, I may switch back now:

DGMGRL> switchover to prima;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prima" on database "prima"
Connecting to instance "prima"...
Connected as SYSDBA.
New primary database "prima" is opening...
Operation requires start up of instance "physt" on database "physt"
Starting instance "physt"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prima"
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Members:
  prima - Primary database
    physt - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 40 seconds ago)

This is one reason why we recommend that you turn on flashback for the databases that are part of a Data Guard configuration. As always: Don’t believe it, test it 🙂

  1. #1 by alexandruneda on September 21, 2016 - 15:38

    This is a great article, thank you Uwe!
    I was just testing a 12c failover with the broker and was wondering how will I convert the old primary to standby 🙂

  2. #2 by rajat sharma on September 24, 2016 - 02:37

    Hi Sir,

    Could you please explain the requirement of no of days/hrs flash back needed to perform below activity

    Thanks

  3. #3 by Uwe Hesse on September 27, 2016 - 16:52

    You wouldn’t need that much flashback logs unless the old primary is not up for a longer period of time after the failover. Suppose you did the failover one hour ago and the old primary is still up. Then db_flashback_retention_target should be at least 60 for the reinstate to work.

  1. RECOVER STANDBY DATAFILE FROM PRIMARY - ORACLE IN ACTION

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: