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)

2 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