14 August, 2021

Restoring Standby Database after Failover

 In the previous post, I demonstrated how the Database Incarnation changed after a Failover was issued for a Standby Database.

Let's assume that Read-Write testing of the Standby has been done and now I need to restore and revert STDBYDB to the state of being a Standby for ORCLCDB


The Primary is still at Database Incarnation#=3  and is now at Sequence#=409



SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCLCDB
SQL> select incarnation#
  2  from v$database_incarnation
  3  where status = 'CURRENT'
  4  /

INCARNATION#
------------
           3

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence     407
Next log sequence to archive   409
Current log sequence           409
SQL>


The former Standby STDBYDB has already diverged and is at Incarnation#=4 and its current Sequence#=5


SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      STDBYDB
SQL> select incarnation#
  2  from v$database_incarnation
  3  where status = 'CURRENT'
  4  /

INCARNATION#
------------
           4

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/archivelog/STDBYDB
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL>


I now delete all the datafiles STDBYDB and restore the the backup that I made before I executed the Failover (see the previous blog post)


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>cd
oracle19c>sh ./to_delete_STDBYDB.SH
oracle19c>
oracle19c>echo $ORACLE_SID
STDBYDB
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 14 17:49:50 2021
Version 19.12.0.0.0

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area    1207955544 bytes

Fixed Size                     9134168 bytes
Variable Size                486539264 bytes
Database Buffers             704643072 bytes
Redo Buffers                   7639040 bytes

RMAN> restore controlfile from '/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_08_13/o1_mf_s_1080515319_jkf2tzhp_.bkp';

Starting restore at 14-AUG-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/oracle/oradata/STDBYDB/control01.ctl
output file name=/opt/oracle/oradata/STDBYDB/control02.ctl
Finished restore at 14-AUG-21

RMAN> quit


Recovery Manager complete.
oracle19c>echo $ORACLE_SID
STDBYDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 14 17:52:34 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database mount;

Database altered.

SQL>  show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      STDBYDB
SQL> select database_role, open_mode
  2  from v$database
  3  /

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> select incarnation#, resetlogs_change#, to_char(resetlogs_time,'DD-MON-RR HH24:MI') resetlogs_time,
  2  status, resetlogs_id
  3  from v$database_incarnation
  4  order by 1
  5  /

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME           STATUS  RESETLOGS_ID
------------ ----------------- ------------------------ ------- ------------
           1                 1 17-APR-19 00:55          PARENT    1005785759
           2           1920977 04-MAY-19 23:21          PARENT    1007421686
           3           4797184 27-MAR-20 00:00          CURRENT   1036108814

SQL>


So, this restored Controlfile confirms that I have a Standby Database that is now back at Incarnation#=3.
I can now proceed to restore the database and I assume that I can reapply all the ArchiveLogs from the Primary which is also at Incarnation#=3


SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 14 17:56:06 2021
Version 19.12.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057, not open)

RMAN> restore database;

Starting restore at 14-AUG-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=129 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 00003 to /opt/oracle/oradata/STDBYDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_08_13/o1_mf_nnndf_TAG20210813T230720_jkf2ob5r_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /opt/oracle/oradata/STDBYDB/system01.dbf
channel ORA_DISK_2: restoring datafile 00004 to /opt/oracle/oradata/STDBYDB/undotbs01.dbf
channel ORA_DISK_2: reading from backup piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_08_13/o1_mf_nnndf_TAG20210813T230720_jkf2o9j4_.bkp
....
....
....

channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:36
Finished restore at 14-AUG-21

RMAN> quit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 14 18:01:50 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      STDBYDB
SQL> select database_role, open_mode
  2  from v$database
  3  /

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> select incarnation#, resetlogs_change#, to_char(resetlogs_time,'DD-MON-RR HH24:MI') resetlogs_time,
  2  status, resetlogs_id
  3  from v$database_incarnation
  4  order by 1
  5  /

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME           STATUS  RESETLOGS_ID
------------ ----------------- ------------------------ ------- ------------
           1                 1 17-APR-19 00:55          PARENT    1005785759
           2           1920977 04-MAY-19 23:21          PARENT    1007421686
           3           4797184 27-MAR-20 00:00          CURRENT   1036108814

SQL>


Let my now try to Recover this Database as a Standby using ArchiveLogs from the PrimaryR (after " alter system set log_archive_dest_state_2='enable';" at the Primary)


SQL> select l.group#, l.bytes/1048576, l.status, f.member
  2  from v$standby_log l, v$logfile f
  3  where l.group#=f.group#
  4  order by 1
  5  /

    GROUP# L.BYTES/1048576 STATUS
---------- --------------- ----------
MEMBER
------------------------------------------------------------------------------------------------------------------------------------
         4             200 ACTIVE
/opt/oracle/oradata/STDBYDB/stdbredo01.log

         5             200 ACTIVE
/opt/oracle/oradata/STDBYDB/stdbredo02.dbf


SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 5;

Database altered.

SQL>
SQL>  select l.group#, l.bytes/1048576, l.status, f.member
  2  from v$standby_log l, v$logfile f
  3  where l.group#=f.group#
  4  order by 1
  5  /

    GROUP# L.BYTES/1048576 STATUS
---------- --------------- ----------
MEMBER
------------------------------------------------------------------------------------------------------------------------------------
         4             200 UNASSIGNED
/opt/oracle/oradata/STDBYDB/stdbredo01.log

         5             200 UNASSIGNED
/opt/oracle/oradata/STDBYDB/stdbredo02.dbf


SQL>
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>


STDBYDB takes a few minutes to get all the ArchiveLogs from Sequence#402 onwards and apply them


2021-08-14T18:16:46.242533+08:00
 rfs (PID:24643): Selected LNO:5 for T-1.S-416 dbid 2778483057 branch 1036108814
2021-08-14T18:16:46.279518+08:00
ARC1 (PID:15987): Archived Log entry 19 added for T-1.S-415 ID 0xa7521ccd LAD:1
2021-08-14T18:16:46.408474+08:00
PR00 (PID:23234): Media Recovery Waiting for T-1.S-416 (in transit)
2021-08-14T18:16:46.420169+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 416 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo02.dbf


The Primary also now shows :


SQL> l
  1  select dest_id, db_unique_name, status, type, archived_seq#, applied_seq#, synchronized, gap_status
  2  from v$archive_dest_status
  3  where dest_id in (1,2,3)
  4* order by 1
SQL> /

   DEST_ID DB_UNIQUE_NAME                 STATUS    TYPE             ARCHIVED_SEQ# APPLIED_SEQ# SYN GAP_STATUS
---------- ------------------------------ --------- ---------------- ------------- ------------ --- ------------------------
         1 NONE                           VALID     LOCAL                      415            0 NO
         2 STDBYDB                        VALID     PHYSICAL                   415          414 YES NO GAP
         3 STDB2                          VALID     PHYSICAL                   415          405 NO  NO GAP

SQL>

I can also see the ArchiveLogs that were generated from STDBYDB :

oracle19c>pwd
/opt/oracle/archivelog/STDBYDB
oracle19c>ls -latr
total 196820
drwxr-xr-x. 6 oracle oinstall       63 Aug 11 14:25 ..
-rw-r-----. 1 oracle oinstall   269824 Aug 13 22:47 1_394_1036108814.dbf
-rw-r-----. 1 oracle oinstall  4147200 Aug 13 23:02 1_399_1036108814.dbf
-rw-r-----. 1 oracle oinstall     3584 Aug 13 23:02 1_400_1036108814.dbf
-rw-r-----. 1 oracle oinstall    24064 Aug 13 23:02 1_401_1036108814.dbf
-rw-r-----. 1 oracle oinstall     3072 Aug 13 23:03 1_402_1036108814.dbf
-rw-r-----. 1 oracle oinstall     1024 Aug 13 23:13 1_1_1080515513.dbf
-rw-r-----. 1 oracle oinstall 21302784 Aug 14 17:13 1_2_1080515513.dbf
-rw-r-----. 1 oracle oinstall 96683520 Aug 14 17:41 1_3_1080515513.dbf
-rw-r-----. 1 oracle oinstall    14848 Aug 14 17:42 1_4_1080515513.dbf
-rw-r-----. 1 oracle oinstall    45568 Aug 14 18:04 1_405_1036108814.dbf
-rw-r-----. 1 oracle oinstall   703488 Aug 14 18:04 1_404_1036108814.dbf
-rw-r-----. 1 oracle oinstall   486400 Aug 14 18:04 1_403_1036108814.dbf
-rw-r-----. 1 oracle oinstall 20830208 Aug 14 18:04 1_406_1036108814.dbf
-rw-r-----. 1 oracle oinstall  5378048 Aug 14 18:04 1_407_1036108814.dbf
-rw-r-----. 1 oracle oinstall   538112 Aug 14 18:04 1_408_1036108814.dbf
-rw-r-----. 1 oracle oinstall  4228096 Aug 14 18:04 1_409_1036108814.dbf
-rw-r-----. 1 oracle oinstall     4096 Aug 14 18:04 1_410_1036108814.dbf
-rw-r-----. 1 oracle oinstall   197120 Aug 14 18:07 1_411_1036108814.dbf
-rw-r-----. 1 oracle oinstall   176128 Aug 14 18:16 1_414_1036108814.dbf
-rw-r-----. 1 oracle oinstall   489984 Aug 14 18:16 1_412_1036108814.dbf
-rw-r-----. 1 oracle oinstall    91136 Aug 14 18:16 1_413_1036108814.dbf
-rw-r-----. 1 oracle oinstall     4096 Aug 14 18:16 1_415_1036108814.dbf
drwxr-xr-x. 2 oracle oinstall     4096 Aug 14 18:25 .
-rw-r-----. 1 oracle oinstall 45876224 Aug 14 18:25 1_416_1036108814.dbf
oracle19c>

Note how Sequence#1 to Sequence#4 were generated in the new Incarnation with Resetlogs ID 1080515513.  This was the Resetlogs ID assigned when STDBYDB was opened with Failover, thus a New Incarnation and effectively a Resetlogs. Sequence#403 onwards are from the Primary with Resetlogs ID 1036108814
(You can check the Resetlogs IDs shown in the previous blog post)

7 comments:

Hemant K Chitale said...

The "to_delete_STDBYDB.SH" script is generated using
select 'rm ' || name from v$datafile
union
select 'rm ' || name from v$controlfile
union
select 'rm ' || member from v$logfile
order by 1


I prefer this over a DROP DATABASE command. Note : The DROP DATABASE command also removes the spfile !

Unknown said...

Another great blog , thanks sir for sharing your expertise

Arindam said...

Hi , Thanks for the wonderful explanation.
However in the demonstrated case , the old PRIMARY ORCLCDB was not reinstated( if we use FSFO) as it seems to me.
So if we have FSFO and old primary is reinstated , can we still use the old backup to build the STDBY after we do a failback to ORCLCDB.
Its like below after failover as part of FSFO
ORCLCDB - New STDBY
STDBYDB - New Primary.
Do a manual failback.
ORCLCDB - Primary again
Can I still use the old backup to build the STDBY.

Arindam said...

Hi , thanks for the wonderful explanation.
However it seems the old primary ORCLCDB is not reinstated.
So if I have FSFO enabled and failover happens the ORCLCDB is reinstated and becomes new standby and STDBYDB becomes new primary.
Now will the same backup be still valid if I do a failback and try to build STDBYDB with the old backup.
Its like below, Failover happens
ORCLCDB - New Standby ( reinstate happens)
STDBYDB- New Primary
Now I do a failback, so
ORCLCDB is again PRIMARY now, so can I use the same backup (taken before failover) to build STDBYDB.
Also what will happen to STDB2 after failback, will it start syncing from new logseq number.

Hemant K Chitale said...

I wouldn't be doing a Failback. And I am not using FSFO.

The Standby is to be discarded -- all changes made in the Standby are to destroyed.

The Standby has to be rebuilt from either an older Standby Backup or from the Primary

Arindam said...

Thank you. But in my case , we have Primary , HA(FSFO enabled database) and DR(lagging physical standby). So if we similar situation as I stated, so what can be the best solution to build the DR , if a failover happens, considering the fact that reinstate happens after failover.

Hemant K Chitale said...

Oracle's standard recommendation is to make use of the FLASHBACK DATABASE feature.