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:
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 !
Another great blog , thanks sir for sharing your expertise
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.
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.
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
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.
Oracle's standard recommendation is to make use of the FLASHBACK DATABASE feature.
Post a Comment