As I noted in my previous blog post, with multiple Standby databases, you can have one or more of them, lagging the Primary. This allows the organisation a database that can be quickly opened for data recovery in case someone makes a mistake and deletes data or drops tables/objects from the Primary and the delete/drop has already been replicated to the first Standby.
Here is a quick demo.
At the Primary I have :
SQL> alter system archive log current; System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/archivelog/ORCLCDB Oldest online log sequence 280 Next log sequence to archive 282 Current log sequence 282 SQL>
At the first Standby "STDBYDB", I have :
2021-07-20T17:50:05.870763+08:00 PR00 (PID:2912): Media Recovery Waiting for T-1.S-282 (in transit) 2021-07-20T17:50:06.354006+08:00 ARC3 (PID:2736): Archived Log entry 35 added for T-1.S-281 ID 0xa7521ccd LAD:1 2021-07-20T17:50:06.396543+08:00 rfs (PID:3263): Archival of T-1.S-281 complete 2021-07-20T17:50:06.527483+08:00 rfs (PID:3263): Selected LNO:4 for T-1.S-282 dbid 2778483057 branch 1036108814 2021-07-20T17:50:07.008298+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 282 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
At the second Standby "STDB2", I have :
2021-07-20T17:50:09.484608+08:00 PR00 (PID:2975): Media Recovery Waiting for T-1.S-282 (in transit) 2021-07-20T17:50:09.500278+08:00 rfs (PID:3216): Opened log for T-1.S-282 dbid 2778483057 branch 1036108814 2021-07-20T17:50:09.527462+08:00 ARC3 (PID:2867): Archived Log entry 22 added for T-1.S-281 ID 0xa7521ccd LAD:1
To introduce a delay in applying ArchiveLogs at STDBY, I specify the DELAY parameter at the Primary database :
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string SERVICE=STDB2 ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STDB2
log_archive_dest_30 string
log_archive_dest_31 string
SQL> alter system set log_archive_dest_3='SERVICE=STDB2 DELAY=60 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDB2';
System altered.
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string SERVICE=STDB2 DELAY=60 ASYNC V
ALID_FOR=(ONLINE_LOGFILES,PRIM
ARY_ROLE) DB_UNIQUE_NAME=STDB2
log_archive_dest_30 string
log_archive_dest_31 string
SQL>
So, I have introduced a lag of 60minutes for STDB2. Over the next 60minutes, the Primary will continue generating Redo and ArchiveLogs and both Standbys will be receiving them. But STDB2 will apply them only after 60minutes.
However, to enforce this, I must also cause the Standby to *not* use Real Time Apply, so I must change the RECOVER command at the Standby Note, however, that this must be done on the Standby first ! The Standby must start it's recovery with "USING ARCHIVED LOGFILE" *before* the Primary sets a DELAY value for the target log_archive_dest_n
If I do not change the RECOVER command at the Standby to explicitly specify "USING ARCHIVED LOGFILE", it still defaults to Real Time Apply and ignores the DELAY specified by the Primary. I get the message in STDB2 alert log :
I review the alert log files for all 3 databases about an hour later.
oracle19c>echo $ORACLE_SID STDB2 oracle19c>sqlplus '/ as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 20 18:14:26 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database recover managed standby database using archived logfile disconnect from session; Database altered. SQL>
If I do not change the RECOVER command at the Standby to explicitly specify "USING ARCHIVED LOGFILE", it still defaults to Real Time Apply and ignores the DELAY specified by the Primary. I get the message in STDB2 alert log :
rfs (PID:13712): WARN: Managed Standby Recovery started with REAL TIME APPLY rfs (PID:13712): WARN: DELAY 60 minutes specified at primary ignored
I review the alert log files for all 3 databases about an hour later.
2021-07-20T19:10:57.161885+08:00 PR00 (PID:2912): Media Recovery Waiting for T-1.S-297 (in transit) 2021-07-20T19:10:57.324337+08:00 rfs (PID:3263): Selected LNO:4 for T-1.S-297 dbid 2778483057 branch 1036108814 2021-07-20T19:10:58.401720+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 297 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log 2021-07-20T19:14:39.910894+08:00 ARC3 (PID:2736): Archived Log entry 51 added for T-1.S-297 ID 0xa7521ccd LAD:1 2021-07-20T19:14:39.943728+08:00 rfs (PID:3263): Standby controlfile consistent with primary 2021-07-20T19:14:40.136187+08:00 rfs (PID:3263): Selected LNO:4 for T-1.S-298 dbid 2778483057 branch 1036108814 2021-07-20T19:14:40.136811+08:00 PR00 (PID:2912): Media Recovery Waiting for T-1.S-298 (in transit) 2021-07-20T19:14:41.180355+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 298 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string STDBYDB SQL> select current_scn from v$database; CURRENT_SCN ----------- 13123569 SQL>
And this is the Second Standby (STDB2) :
2021-07-20T19:10:58.180405+08:00 rfs (PID:21331): No SRLs available for T-1 2021-07-20T19:10:58.319036+08:00 ARC0 (PID:2857): Archived Log entry 37 added for T-1.S-296 ID 0xa7521ccd LAD:1 2021-07-20T19:10:58.319358+08:00 ARC0 (PID:2857): Archive log for T-1.S-296 available in 60 minute(s) 2021-07-20T19:10:58.320321+08:00 rfs (PID:21331): Opened log for T-1.S-297 dbid 2778483057 branch 1036108814 2021-07-20T19:14:40.363888+08:00 rfs (PID:21331): Archived Log entry 38 added for B-1036108814.T-1.S-297 ID 0xa7521ccd LAD:3 2021-07-20T19:14:40.782081+08:00 rfs (PID:21331): Selected LNO:4 for T-1.S-298 dbid 2778483057 branch 1036108814 2021-07-20T19:15:12.430015+08:00 PR00 (PID:26793): Media Recovery Log /opt/oracle/archivelog/STDB21_286_1036108814.dbf PR00 (PID:26793): Media Recovery Delayed for 60 minute(s) T-1.S-287 SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string STDB2 SQL> select current_scn from v$database; CURRENT_SCN ----------- 13108691 SQL>
So, while the first Standby (STDBYDB) has already applied and archived Sequence#297 and is currently applying Sequence#298 from the Standby logfile, the second Standby (STDB2) has archived Sequence#297 and received Sequence#298 but notifies that Sequence#286 is currently being applied and the apply Sequence#297 is delayed by 60minutes.
I can also run an SCN_TO_TIMESTAMP query in the Primary (this cannot be executed on a Standby that is not OPEN) :
SQL> select scn_to_timestamp(13123569) At_STDBYDB from dual; AT_STDBYDB --------------------------------------------------------------------------- 20-JUL-21 07.13.39.000000000 PM SQL> select scn_to_timestamp(13108691) At_STDB2 from dual; AT_STDB2 --------------------------------------------------------------------------- 20-JUL-21 06.15.07.000000000 PM SQL>
This shows that STDB2 is lagging by about 60minutes
So, if any "bad action" (deletion of data or dropping of objects) is detected at the Primary (and a Flashback option is not available), the Standby can be OPENed Read Only to view the data as it was 1hour ago.
I'll show that option in my next blog post.
No comments:
Post a Comment