As shown in my previous blog post, you can create a Standby Database that lags the Primary by not applying Redo immediately but "waiting" for a specified interval. It continues to receive and ArchiveLogs but simply applies each only after the "wait interval".
oracle19c>echo $ORACLE_SID ORCLCDB oracle19c>sqlplus '/ as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:03:12 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> select systimestamp, database_role, current_scn from v$database; SYSTIMESTAMP DATABASE_ROLE CURRENT_SCN -------------------------------------------------------------- ---------------- ----------- 24-JUL-21 06.03.32.106863 PM +08:00 PRIMARY 13258062 SQL> alter session set container=ORCLPDB1; Session altered. SQL> select * from hemant.job_tracking_tbl order by 1; JOB_END_TIME JOB_ID DB_SCN -------------------------------------------------------------- ---------- ---------- 24-JUL-21 04.57.21.676949 PM 1 13239134 24-JUL-21 05.04.29.870877 PM 2 13241261 24-JUL-21 05.30.17.962275 PM 3 13246616 24-JUL-21 05.39.10.912969 PM 4 13247859 24-JUL-21 05.40.20.865467 PM 5 13248159 24-JUL-21 05.50.23.930352 PM 6 13252182 24-JUL-21 06.00.27.037797 PM 7 13257658 7 rows selected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/archivelog/ORCLCDB Oldest online log sequence 310 Next log sequence to archive 312 Current log sequence 312 SQL>
Now, the status at STDB2 (the Standby that is lagging with an enforced 60minutes delay
From the alert.log : 2021-07-24T17:51:15.716720+08:00 PR00 (PID:2924): Media Recovery Log /opt/oracle/archivelog/STDB21_303_1036108814.dbf PR00 (PID:2924): Media Recovery Delayed for 59 minute(s) T-1.S-304 2021-07-24T17:57:26.299295+08:00 PR00 (PID:2924): Media Recovery Log /opt/oracle/archivelog/STDB21_304_1036108814.dbf 2021-07-24T17:57:44.580258+08:00 PR00 (PID:2924): Media Recovery Delayed for 60 minute(s) T-1.S-305 2021-07-24T18:00:32.550708+08:00 rfs (PID:3452): Archived Log entry 52 added for B-1036108814.T-1.S-311 ID 0xa7521ccd LAD:3 2021-07-24T18:00:33.444329+08:00 rfs (PID:3452): Selected LNO:4 for T-1.S-312 dbid 2778483057 branch 1036108814 oracle19c>sqlplus '/ as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:05:53 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 open read only; Database altered. SQL> select systimestamp, database_role, open_mode, current_scn from v$database; SYSTIMESTAMP DATABASE_ROLE OPEN_MODE CURRENT_SCN --------------------------------------------------- ---------------- ------------ ----------- 24-JUL-21 06.06.51.313616 PM +08:00 PHYSICAL STANDBY READ ONLY 13239390 SQL> SQL> alter pluggable database orclpdb1 open read only; Pluggable database altered. SQL> alter session set container=ORCLPDB1; Session altered. SQL> select * from hemant.job_tracking_tbl order by 1; CURRENT_TIMESTAMP JOB_ID CURRENT_SCN -------------------------------------------------------------- ---------- ----------- 24-JUL-21 04.57.21.676949 PM 1 13239134 SQL>
The Primary database is at Log Sequence#312. This Standby has applied only Sequence#304. Let me resume Recovery for some more time and then check the Standby again.
SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 oracle19c>echo $ORACLE_SID STDB2 oracle19c>sqlplus '/ as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:11:12 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> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1207955552 bytes Fixed Size 9134176 bytes Variable Size 318767104 bytes Database Buffers 872415232 bytes Redo Buffers 7639040 bytes Database mounted. SQL> alter database recover managed standby database using archived logfile disconnect from session; Database altered. SQL> SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 oracle19c> Later ... from the STDB2 alert log : 2021-07-24T18:40:22.531574+08:00 PR00 (PID:29990): Media Recovery Log /opt/oracle/archivelog/STDB21_309_1036108814.dbf PR00 (PID:29990): Media Recovery Delayed for 59 minute(s) T-1.S-310 2021-07-24T18:40:43.574486+08:00 rfs (PID:531): No SRLs available for T-1 2021-07-24T18:40:43.743506+08:00 rfs (PID:531): Opened log for T-1.S-317 dbid 2778483057 branch 1036108814 2021-07-24T18:40:43.762715+08:00 ARC3 (PID:29836): Archived Log entry 57 added for T-1.S-316 ID 0xa7521ccd LAD:1 2021-07-24T18:40:43.762785+08:00 ARC3 (PID:29836): Archive log for T-1.S-316 available in 60 minute(s) 2021-07-24T18:49:27.636427+08:00 PR00 (PID:29990): Media Recovery Log /opt/oracle/archivelog/STDB21_310_1036108814.dbf PR00 (PID:29990): Media Recovery Delayed for 60 minute(s) T-1.S-311 2021-07-24T18:50:45.257290+08:00 rfs (PID:531): Archived Log entry 58 added for B-1036108814.T-1.S-317 ID 0xa7521ccd LAD:3 2021-07-24T18:50:46.045279+08:00 rfs (PID:531): Selected LNO:4 for T-1.S-318 dbid 2778483057 branch 1036108814 oracle19c>echo $ORACLE_SID STDB2 oracle19c>sqlplus '/ as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:51:27 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 open read only; Database altered. SQL> alter pluggable database orclpdb1 open read only; Pluggable database altered. SQL> alter session set container=orclpdb1; Session altered. SQL> SQL> select * from hemant.job_tracking_tbl order by 1; JOB_END_TIME JOB_ID DB_SCN -------------------------------------------------------------- ---------- ---------- 24-JUL-21 04.57.21.676949 PM 1 13239134 24-JUL-21 05.04.29.870877 PM 2 13241261 24-JUL-21 05.30.17.962275 PM 3 13246616 24-JUL-21 05.39.10.912969 PM 4 13247859 24-JUL-21 05.40.20.865467 PM 5 13248159 24-JUL-21 05.50.23.930352 PM 6 13252182 6 rows selected. SQL> SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 24-JUL-21 06.53.31.159094 PM +08:00 SQL>
So, now at 18:47, STDB2 has applied Sequence#310 and the database now shows data that came through that ArchiveLog. Upto JOB_ID=6, JOB_END_TIME=05:50:23pm
The Primary has already progressed further.
SQL> l 1* select * from hemant.job_tracking_tbl order by 1 SQL> / JOB_END_TIME JOB_ID DB_SCN -------------------------------------------------------------- ---------- ---------- 24-JUL-21 04.57.21.676949 PM 1 13239134 24-JUL-21 05.04.29.870877 PM 2 13241261 24-JUL-21 05.30.17.962275 PM 3 13246616 24-JUL-21 05.39.10.912969 PM 4 13247859 24-JUL-21 05.40.20.865467 PM 5 13248159 24-JUL-21 05.50.23.930352 PM 6 13252182 24-JUL-21 06.00.27.037797 PM 7 13257658 24-JUL-21 06.10.33.163203 PM 8 13259223 24-JUL-21 06.20.36.839944 PM 9 13261275 24-JUL-21 06.22.46.972310 PM 10 13261560 24-JUL-21 06.30.39.787880 PM 11 13262799 24-JUL-21 06.37.18.623659 PM 12 13263658 24-JUL-21 06.40.41.713016 PM 13 13264263 24-JUL-21 06.50.43.755835 PM 14 13265798 14 rows selected. SQL>
So, the operative methods at the Standby are :
For Recovery :
1. alter database recover managed standby database USING ARCHIVED LOGFILE disconnect from session
To Open and Query :
1. alter database recover managed standby database CANCEL
2. alter database OPEN READ ONLY
3. alter pluggable database <pdbname> OPEN READ ONLY
To resume Recovery :
1. shutdown immediate
2. startup mount
3. alter database recover managed standby database USING ARCHIVED LOGFILE disconnect from session
While the Primary must specify a DELAY value in the log_archive_dest_n parameter for this destination Standby
No comments:
Post a Comment