Setup for this case:
- Two Gen2 Exadata Cloud At Customer X8 quarter rack (2 dbnodes + 3 cells):
- One ExaCC in NYC being the primary
- PRODDB is the primary RAC database running in 2 nodes in the NYC cluster.
- One ExaCC in SLC being the standby
- PRODDBB is the standby RAC database running in 2 nodes in the SLC cluster.
- One ExaCC in NYC being the primary
Something happened between the primary and the standby databases and the standby database was lagging behind:
[oracle@slc-exaccdb01 ~]$ . proddb.env
[oracle@slc-exaccdb01 ~]$ dgmgrl / "show configuration lag"
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Apr 26 13:41:31 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "proddbb"
Connected as SYSDG.
Configuration - proddb_dr
Protection Mode: MaxPerformance
Members:
proddb - Primary database
Error: ORA-16724: cannot resolve gap for one or more members
proddbb - Physical standby database
Warning: ORA-16809: multiple warnings detected for the member
Transport Lag: 1 day(s) 4 hours 11 minutes 28 seconds (computed 2 seconds ago)
Apply Lag: 1 day(s) 4 hours 11 minutes 40 seconds (computed 1 second ago)
dcazdlra - Recovery appliance
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 90 seconds ago)
When checking the alert.log I discovered that the standby couldn’t reach the primary:
2023-04-25T09:15:09.479341-03:00 PR00 (PID:114059): Media Recovery Waiting for T-2.S-52015 (in transit) 2023-04-25T09:15:09.487700-03:00 Recovery of Online Redo Log: Thread 2 Group 22 Seq 52015 Reading mem 0 Mem# 0: +DATAC1/PRODDBB/ONLINELOG/group_22.1360.1132096365 2023-04-25T09:25:04.992477-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_378423.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T09:30:36.957990-03:00 rfs (PID:72071): Opened log for T-1.S-53572 dbid 2621748695 branch 1082730331 2023-04-25T09:30:36.997585-03:00 rfs (PID:72071): Archived Log entry 11030 added for B-1082730331.T-1.S-53572 ID 0x9c44fcd3 LAD:2 2023-04-25T09:30:40.253246-03:00 rfs (PID:9499): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:68208) 2023-04-25T09:30:40.379497-03:00 rfs (PID:9499): Opened log for T-2.S-52016 dbid 2621748695 branch 1082730331 2023-04-25T09:30:40.420406-03:00 rfs (PID:9499): Archived Log entry 11031 added for B-1082730331.T-2.S-52016 ID 0x9c44fcd3 LAD:2 2023-04-25T09:30:45.244331-03:00 rfs (PID:66553): Possible network disconnect with primary database 2023-04-25T09:35:42.171322-03:00 rfs (PID:31177): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:68200) Deleted Oracle managed file +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_0_seq_0.25545.1135071343 2023-04-25T09:35:43.802413-03:00 rfs (PID:33032): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:68222) Network Resource Management enabled for Process (pid 33032) for Exadata I/O rfs (PID:33032): Primary database is in MAXIMUM PERFORMANCE mode 2023-04-25T09:35:43.905677-03:00 rfs (PID:33032): Selected LNO:11 for T-1.S-53573 dbid 2621748695 branch 1082730331 2023-04-25T09:40:04.589905-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_50999.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T09:45:08.566319-03:00 rfs (PID:33032): Selected LNO:13 for T-1.S-53574 dbid 2621748695 branch 1082730331 2023-04-25T09:45:08.622410-03:00 ARC1 (PID:103314): Archived Log entry 11033 added for T-1.S-53573 ID 0x9c44fcd3 LAD:1 2023-04-25T09:55:04.286806-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_188219.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T10:00:09.184211-03:00 rfs (PID:33032): Selected LNO:11 for T-1.S-53575 dbid 2621748695 branch 1082730331 2023-04-25T10:00:09.246714-03:00 ARC1 (PID:103314): Archived Log entry 11035 added for T-1.S-53574 ID 0x9c44fcd3 LAD:1 2023-04-25T10:10:03.723729-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_250373.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T10:15:09.772651-03:00 rfs (PID:33032): Selected LNO:13 for T-1.S-53576 dbid 2621748695 branch 1082730331 2023-04-25T10:15:09.866430-03:00 ARC3 (PID:103369): Archived Log entry 11037 added for T-1.S-53575 ID 0x9c44fcd3 LAD:1 2023-04-25T10:25:04.390143-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_5844.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T10:30:06.973533-03:00 rfs (PID:33032): Selected LNO:11 for T-1.S-53577 dbid 2621748695 branch 1082730331 2023-04-25T10:30:07.005172-03:00 ARC2 (PID:103328): Archived Log entry 11038 added for T-1.S-53576 ID 0x9c44fcd3 LAD:1 2023-04-25T10:40:03.987801-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_69271.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T10:45:04.891163-03:00 rfs (PID:33032): Selected LNO:13 for T-1.S-53578 dbid 2621748695 branch 1082730331 2023-04-25T10:45:04.921564-03:00 ARC0 (PID:103275): Archived Log entry 11040 added for T-1.S-53577 ID 0x9c44fcd3 LAD:1
At some point in time later the MRP process was cancelled/timed out and restarted itself:
2023-04-25T14:00:03.332358-03:00 rfs (PID:33032): Selected LNO:11 for T-1.S-53593 dbid 2621748695 branch 1082730331 2023-04-25T14:00:03.413266-03:00 ARC1 (PID:103314): Archived Log entry 11069 added for T-1.S-53592 ID 0x9c44fcd3 LAD:1 2023-04-25T14:10:04.307434-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_60492.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T14:13:11.610129-03:00 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL 2023-04-25T14:13:11.612017-03:00 PR00 (PID:114059): MRP0: Background Media Recovery cancelled with status 16037 2023-04-25T14:13:11.612319-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_pr00_114059.trc: ORA-16037: user requested cancel of managed recovery operation 2023-04-25T14:13:11.618543-03:00 .... (PID:99994): Managed Standby Recovery not using Real Time Apply 2023-04-25T14:13:11.990373-03:00 Recovery interrupted! Recovered data files to a consistent state at change 442738124 2023-04-25T14:13:12.542287-03:00 Increasing priority of 2 RS Reconfiguration started (old inc 10, new inc 12) List of instances (total 2) : 1 2 My inst 1 Global Resource Directory frozen Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out 2023-04-25T14:13:12.674395-03:00 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0 2023-04-25T14:13:12.674429-03:00 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0 Set master node info Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Reconfiguration complete (total time 0.3 secs) Decreasing priority of 2 RS 2023-04-25T14:13:13.073405-03:00 Stopping change tracking 2023-04-25T14:13:13.077654-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_pr00_114059.trc: ORA-16037: user requested cancel of managed recovery operation 2023-04-25T14:13:13.083017-03:00 Background Media Recovery process shutdown (proddb1) 2023-04-25T14:13:13.613288-03:00 Managed Standby Recovery Canceled (proddb1) Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL 2023-04-25T14:14:02.704458-03:00 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT INSTANCES 2 NODELAY 2023-04-25T14:14:02.710618-03:00 Attempt to start background Managed Standby Recovery process (proddb1) Starting background process MRP0 2023-04-25T14:14:02.753353-03:00 MRP0 started with pid=79, OS id=83730 2023-04-25T14:14:02.754560-03:00 Background Managed Standby Recovery process started (proddb1) 2023-04-25T14:14:07.903869-03:00 Started logmerger process on instance id 1 Started logmerger process on instance id 2 Starting Multi Instance Redo Apply (MIRA) on 2 instances IMC support for MIRA is disabled, invalidate the IMC 2023-04-25T14:14:07.930494-03:00 IM on ADG: Start of Empty Journal IM on ADG: End of Empty Journal 2023-04-25T14:14:07.933616-03:00 Starting Multi Instance Redo Apply (MIRA) 2023-04-25T14:14:08.055008-03:00 .... (PID:99994): Managed Standby Recovery starting Real Time Apply 2023-04-25T14:14:08.109461-03:00 max_pdb is 4 2023-04-25T14:14:08.501793-03:00 Increasing priority of 2 RS Reconfiguration started (old inc 12, new inc 14) List of instances (total 2) : 1 2 My inst 1 Global Resource Directory frozen Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out 2023-04-25T14:14:08.578832-03:00 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0 2023-04-25T14:14:08.578967-03:00 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0 Set master node info Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Reconfiguration complete (total time 0.2 secs) Decreasing priority of 2 RS 2023-04-25T14:14:09.399370-03:00 Started 8 apply slaves on instance id 1 2023-04-25T14:14:09.586750-03:00 Started 8 apply slaves on instance id 2 2023-04-25T14:14:10.886458-03:00 ALTER SYSTEM SET remote_listener='slc-exacc-scan.loredata.com.br:1521' SCOPE=MEMORY SID='proddb1'; 2023-04-25T14:14:10.890206-03:00 ALTER SYSTEM SET listener_networks='(( NAME=net2)(LOCAL_LISTENER=(ADDRESS=(PROTOCOL=TCP)(HOST=10.4.30.71)(PORT=1530))))' SCOPE=MEMORY SID='proddb1'; 2023-04-25T14:14:11.459669-03:00 Recovery of Online Redo Log: Thread 1 Group 12 Seq 53571 Reading mem 0 Mem# 0: +DATAC1/PRODDBB/ONLINELOG/group_12.1355.1132094527 2023-04-25T14:14:11.769081-03:00 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT INSTANCES 2 NODELAY 2023-04-25T14:15:04.066883-03:00 rfs (PID:33032): Selected LNO:13 for T-1.S-53594 dbid 2621748695 branch 1082730331 2023-04-25T14:15:04.132341-03:00 ARC3 (PID:103369): Archived Log entry 11071 added for T-1.S-53593 ID 0x9c44fcd3 LAD:1 2023-04-25T14:25:05.195347-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_223439.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T14:30:04.853895-03:00 rfs (PID:33032): Selected LNO:11 for T-1.S-53595 dbid 2621748695 branch 1082730331 2023-04-25T14:30:04.899184-03:00 ARC0 (PID:103275): Archived Log entry 11073 added for T-1.S-53594 ID 0x9c44fcd3 LAD:1 2023-04-25T14:30:10.529146-03:00 rfs (PID:33032): Selected LNO:13 for T-1.S-53596 dbid 2621748695 branch 1082730331 2023-04-25T14:30:10.556830-03:00 ARC3 (PID:103369): Archived Log entry 11075 added for T-1.S-53595 ID 0x9c44fcd3 LAD:1 2023-04-25T14:40:04.629445-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_287739.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T14:45:08.191958-03:00 rfs (PID:33032): Selected LNO:11 for T-1.S-53597 dbid 2621748695 branch 1082730331 2023-04-25T14:45:08.238757-03:00 ARC2 (PID:103328): Archived Log entry 11077 added for T-1.S-53596 ID 0x9c44fcd3 LAD:1 2023-04-25T14:55:04.524637-03:00 Errors in file /u02/app/oracle/diag/rdbms/proddbb/proddb1/trace/proddb1_ora_25747.trc: ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server 2023-04-25T15:00:09.204782-03:00
But as you can see it was not able to recover itself due to an unresolvable gap:
[oracle@slc-exaccdb01 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Apr 26 13:55:39 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "proddbb"
Connected as SYSDG.
DGMGRL> show database proddb
Database - proddb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
proddb1
proddb2
Database Error(s):
ORA-16783: cannot resolve gap for member proddbb
Database Status:
ERROR
DGMGRL> show database proddbb
Database - proddbb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 1 day(s) 4 hours 27 minutes 54 seconds (computed 1 second ago)
Apply Lag: 1 day(s) 4 hours 28 minutes 6 seconds (computed 0 seconds ago)
Average Apply Rate: 11.00 KByte/s
Real Time Query: ON
Instance(s):
proddb1 (apply instance)
proddb2
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
ORA-16855: transport lag has exceeded specified threshold
Database Status:
WARNING
Without going into the weeds of this problem and quickly looking around I found the issue explained in this MOS document to be similar to what happened here:
Data Guard Physical Standby managed recovery is not progressing after a network outage (Doc ID 1592356.1)
The goal here is just to quickly recover this standby since I can troubleshoot the problem later.
From the standby we can query v$archive_gap to see where we are:
[oracle@slc-exaccdb01 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 14:14:29 2023 Version 19.12.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.12.0.0.0 SQL> select * from v$archive_gap; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# CON_ID ---------- ------------- -------------- ---------- 1 53571 53571 1 2 52015 52015 1
While from the primary we can query v$log to see the current online redo logs:
[oracle@nyc-exaccdb01 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 14:18:29 2023 Version 19.12.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.12.0.0.0 SQL> select max(sequence#), thread# from v$log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 53709 1 52144 2
In the standby we can check the status of LGWR and MRP processes. MRP is running from instance 1 only since that is our apply instance:
SQL> select inst_id, client_process, process, thread#, sequence#, status from gv$managed_standby where client_process='LGWR' or process='MRP0'; INST_ID CLIENT_P PROCESS THREAD# SEQUENCE# STATUS ---------- -------- --------- ---------- ---------- ------------ 1 N/A MRP0 1 53571 APPLYING_LOG 1 LGWR RFS 1 53709 RECEIVING 2 LGWR RFS 2 52144 RECEIVING
MRP seems to be applying log sequence 53571 since ever:
SQL> SELECT INST_ID, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS, 2 decode(nvl(BLOCKS,0), 0, 0, round((BLOCK#/BLOCKS)*100,2)) PERC 3 FROM GV$MANAGED_STANDBY 4* WHERE PROCESS = 'MRP0'; INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS PERC ---------- --------- ------------ ---------- ---------- ---------- ---------- ---------- 1 MRP0 APPLYING_LOG 1 53571 2053 8192000 .03
We can query the list of archive logs present in the standby to see which one is missing and we can see immediately that sequences 53571 and 53572 are missing for thread 1:
SQL> SELECT name FROM v$archived_log WHERE thread# = 1 AND dest_id = 1 AND sequence# BETWEEN 53571 and 53709 and name is not null; NAME ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53573.12053.1135071909 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53574.23366.1135072809 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53575.16241.1135073709 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53576.26983.1135074607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53577.25598.1135075505 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53578.27020.1135076403 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53579.23123.1135077303 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53580.25291.1135078203 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53581.19697.1135078209 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53582.12419.1135079109 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53583.25910.1135080011 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53584.25917.1135080909 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53585.17118.1135081807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53586.12480.1135082703 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53587.25971.1135083603 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53588.25486.1135084505 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53589.11825.1135085403 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53590.11267.1135085407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53591.11768.1135086303 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53592.21753.1135087203 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53593.14001.1135088105 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53594.11785.1135089005 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53595.11063.1135089011 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53596.16511.1135089909 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53597.12464.1135090809 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53598.13064.1135091709 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53599.17931.1135092607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53600.25924.1135093505 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53601.11935.1135094403 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53602.26863.1135095303 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53603.19239.1135096203 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53604.19111.1135096213 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53605.17327.1135097111 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53606.25099.1135098011 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53607.16674.1135098909 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53608.11419.1135099807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53609.25784.1135100703 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53610.16534.1135101605 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53611.12412.1135102503 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53612.26784.1135103403 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53613.26827.1135103407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53614.15641.1135104303 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53615.18775.1135105205 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53616.12301.1135106105 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53617.14740.1135107003 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53618.23354.1135107009 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53619.16478.1135107909 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53620.26887.1135108809 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53621.25613.1135109709 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53622.24501.1135110607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53623.12212.1135111505 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53624.25506.1135112403 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53625.25704.1135113303 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53626.26016.1135114203 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53627.11035.1135114211 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53628.25367.1135115109 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53629.27130.1135116009 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53630.16074.1135116035 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53631.26987.1135116935 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53632.16868.1135117807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53633.26833.1135118703 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53634.10867.1135119603 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53635.25838.1135120505 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53636.21221.1135121405 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53637.25600.1135121407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_1_seq_53638.11494.1135122305 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53639.26737.1135123203 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53640.10893.1135124103 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53641.21234.1135125005 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53642.17688.1135125007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53643.23206.1135125905 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53644.25729.1135126805 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53645.25628.1135127703 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53646.25466.1135128603 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53647.26876.1135128607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53648.21153.1135129503 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53649.16027.1135130405 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53650.26901.1135131305 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53651.12647.1135132203 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53652.23293.1135132209 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53653.12161.1135133109 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53654.26688.1135134009 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53655.23301.1135134909 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53656.12434.1135135805 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53657.11560.1135136705 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53658.18857.1135137603 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53659.25749.1135138503 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53660.17967.1135139403 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53661.23132.1135139407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53662.12057.1135140303 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53663.12103.1135141205 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53664.12127.1135142103 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53665.18924.1135143003 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53666.16247.1135143005 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53667.12319.1135143903 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53668.12106.1135144803 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53669.24643.1135145705 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53670.26938.1135146603 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53671.25246.1135146605 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53672.20547.1135147503 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53673.11597.1135148403 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53674.16731.1135149303 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53675.11601.1135150205 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53676.15699.1135150209 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53677.11526.1135151111 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53678.12303.1135152009 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53679.23115.1135152909 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53680.11323.1135153807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53681.10883.1135154703 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53682.26930.1135155605 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53683.11123.1135156503 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53684.12489.1135157403 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53685.12005.1135157407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53686.22811.1135158303 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53687.25545.1135159205 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53688.14117.1135160105 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53689.25118.1135161003 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53690.24517.1135161005 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53691.10887.1135161903 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53692.12182.1135162803 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53693.19559.1135163705 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53694.12411.1135164603 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53695.12262.1135164607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53696.25869.1135165503 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53697.25953.1135166403 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53698.24824.1135167303 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53699.25851.1135168205 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53700.22644.1135168207 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53701.11308.1135169105 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53702.11251.1135170003 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53703.25735.1135170903 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53704.12453.1135171803 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53705.12231.1135171811 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53706.16061.1135172711 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53707.25967.1135173611 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53708.25804.1135174509 136 rows selected.
While for thread 2 we are missing sequences 52015 and 52016:
SQL> SELECT name FROM v$archived_log WHERE thread# = 2 AND dest_id = 1 AND sequence# BETWEEN 52015 and 52144 and name is not null; NAME ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52017.25974.1135071907 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52018.7885.1135072807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52019.11604.1135073709 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52020.10989.1135074609 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52021.23161.1135075507 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52022.11968.1135076407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52023.12040.1135077307 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52024.11259.1135078209 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52025.12350.1135078211 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52026.27539.1135079109 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52027.16828.1135080007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52028.24473.1135080907 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52029.12167.1135081807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52030.6922.1135082707 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52031.16628.1135083609 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52032.15723.1135084507 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52033.26991.1135085407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52034.11547.1135086307 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52035.11369.1135087207 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52036.14311.1135088109 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52037.12130.1135089007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52038.26736.1135089011 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52039.16093.1135089913 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52040.12462.1135090813 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52041.13320.1135091713 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52042.20584.1135092609 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52043.17594.1135093505 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52044.25233.1135094407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52045.12075.1135095307 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52046.11639.1135096207 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52047.12423.1135096213 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52048.11254.1135097115 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52049.17013.1135098013 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52050.25766.1135098913 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52051.25290.1135099807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52052.24489.1135100705 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52053.23219.1135101603 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52054.12913.1135102503 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52055.20566.1135103401 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52056.25854.1135103409 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52057.16178.1135104307 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52058.15006.1135105209 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52059.18516.1135106109 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52060.21207.1135107007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52061.21885.1135107009 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52062.25174.1135107907 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52063.12012.1135108807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52064.25631.1135109707 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52065.23294.1135110609 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52066.9945.1135111507 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52067.20672.1135112407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52068.26825.1135113307 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52069.27125.1135114209 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52070.26803.1135114211 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52071.25517.1135115109 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52072.27595.1135116007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52073.25822.1135116009 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52074.10891.1135116035 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52075.16836.1135116937 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52076.27073.1135117807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52077.11352.1135118705 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52078.25825.1135119603 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52079.27047.1135120503 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52080.26791.1135121401 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52081.25575.1135121409 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_25/thread_2_seq_52082.25632.1135122307 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52083.26900.1135123207 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52084.25798.1135124107 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52085.11212.1135125009 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52086.27559.1135125909 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52087.25399.1135126807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52088.11736.1135127707 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52089.19001.1135128607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52090.17840.1135129509 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52091.12299.1135130409 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52092.11344.1135131307 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52093.26760.1135132207 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52094.16590.1135132209 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52095.26640.1135133107 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52096.24657.1135134007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52097.17383.1135134909 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52098.23179.1135135807 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52099.12386.1135136707 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52100.2956.1135137607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52101.12475.1135138507 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52102.25662.1135139409 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52103.26783.1135140309 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52104.11113.1135141207 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52105.24560.1135142107 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52106.23235.1135143007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52107.25956.1135143907 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52108.25760.1135144809 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52109.17736.1135145707 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52110.11571.1135146607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52111.25525.1135147507 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52112.12270.1135148407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52113.17062.1135149309 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52114.22163.1135150207 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52115.11628.1135150209 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52116.11938.1135151107 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52117.19638.1135152007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52118.25904.1135152907 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52119.15910.1135153809 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52120.16726.1135154709 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52121.25582.1135155607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52122.11297.1135156507 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52123.25944.1135157407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52124.25068.1135158309 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52125.25782.1135159205 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52126.24409.1135160107 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52127.25934.1135161007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52128.11668.1135161907 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52129.11024.1135162809 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52130.12381.1135163709 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52131.12241.1135164607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52132.12097.1135165507 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52133.25651.1135166407 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52134.25287.1135167309 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52135.25923.1135168209 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52136.24958.1135169107 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52137.24380.1135170007 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52138.22121.1135170907 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52139.12324.1135171809 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52140.25566.1135171811 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52141.12133.1135172709 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52142.15494.1135173607 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52143.15555.1135174507 +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_2_seq_52144.15368.1135175407 128 rows selected.
To summarize the missing sequences:
#Thread 1 | #Thread 2 53571 | 52015 53572 | 52016
We are missing 2 archive logs on each thread and that might be due to a reason a backup job was executed in the primary while there was a network outage and the archive logs were removed and never shipped to the standby.
We can recover those archive logs from backup but if the list is not only 2 archive logs per thread then we might be into a problem of recoverying a whole bunch of files, which is doable, however nowadays we have more efficient ways of synchronizing a standby database with its primary.
From 19c onward we can use the “recover from service” feature roll the standby forward. Let’s explore further to see how it works.
Since this is a RAC standby database let’s first stop the standby database and start it up with only one instance running in mount mode:
[oracle@slc-exaccdb01 ~]$ srvctl stop database -db proddbb [oracle@slc-exaccdb01 ~]$ srvctl start instance -db proddbb -i proddb1 -startoption mount [oracle@slc-exaccdb01 ~]$ srvctl status database -db proddbb Instance proddb1 is running on node slc-exaccdb01 Instance proddb2 is not running on node slc-exaccdb02
We must now stop the MRP process as well:
[oracle@slc-exaccdb01 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Apr 26 15:09:04 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "proddbb"
Connected as SYSDG.
DGMGRL> show database proddbb;
Database - proddbb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 1 day(s) 5 hours 39 minutes 16 seconds (computed 1 second ago)
Apply Lag: 1 day(s) 5 hours 39 minutes 27 seconds (computed 1 second ago)
Average Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
proddb1 (apply instance)
proddb2
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
ORA-16855: transport lag has exceeded specified threshold
Database Status:
WARNING
DGMGRL> edit database proddbb set state=apply-off;
Succeeded.
DGMGRL> show database proddbb;
Database - proddbb
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 1 day(s) 5 hours 40 minutes 12 seconds (computed 0 seconds ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
proddb1 (apply instance)
proddb2
Database Warning(s):
ORA-16855: transport lag has exceeded specified threshold
Database Status:
WARNING
Then we can connect to the standby database as our RMAN target and execute the recovery to roll the standby database forward:
[oracle@slc-exaccdb01 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 26 15:10:54 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODDB (DBID=2621748695, not open)
RMAN> recover standby database from service proddb_dg;
Starting recover at 26-APR-23
using target database control file instead of recovery catalog
Oracle instance started
Total System Global Area 7969176000 bytes
Fixed Size 9157056 bytes
Variable Size 1962934272 bytes
Database Buffers 4328521728 bytes
Redo Buffers 74727424 bytes
In-Memory Area 1593835520 bytes
contents of Memory Script:
{
restore standby controlfile from service 'proddb_dg';
alter database mount standby database;
}
executing Memory Script
Starting restore at 26-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=783 instance=proddb1 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service proddb_dg
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output file name=+DATAC1/proddbb/CONTROLFILE/control.ctl
Finished restore at 26-APR-23
....
....
"+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tsiaralong.1200.1115907499",
"+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tsptfdat.1201.1115907501",
"+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tsptfidx.1202.1115907503",
"+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tsptflong.1203.1115907503",
"+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tstoolsdat.1204.1115907505",
"+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tstoolsidx.1205.1115907505",
"+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tstoolslong.1206.1115907507";
switch datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting implicit crosscheck backup at 26-APR-23
allocated channel: ORA_DISK_1
allocated channel: ORA_DISK_2
allocated channel: ORA_DISK_3
allocated channel: ORA_DISK_4
allocated channel: ORA_DISK_5
allocated channel: ORA_DISK_6
allocated channel: ORA_DISK_7
allocated channel: ORA_DISK_8
allocated channel: ORA_DISK_9
allocated channel: ORA_DISK_10
allocated channel: ORA_DISK_11
allocated channel: ORA_DISK_12
Crosschecked 1 objects
Finished implicit crosscheck backup at 26-APR-23
Starting implicit crosscheck copy at 26-APR-23
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
using channel ORA_DISK_11
using channel ORA_DISK_12
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Finished implicit crosscheck copy at 26-APR-23
searching for all files in the recovery area
cataloging files...
.....
.....
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/CB802DAAC0ADD636E053020E040A2D0E/DATAFILE/undotbs1.958.1098577209 RECID=17 STAMP=1135177969
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/sysaux.962.1098577237 RECID=18 STAMP=1135177969
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/DATAFILE/system.953.1098577139 RECID=26 STAMP=1135177970
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/CB802DAAC0ADD636E053020E040A2D0E/DATAFILE/system.954.1098577153 RECID=28 STAMP=1135177970
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/DATAFILE/sysaux.955.1098577161 RECID=25 STAMP=1135177970
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/CB802DAAC0ADD636E053020E040A2D0E/DATAFILE/sysaux.956.1098577197 RECID=29 STAMP=1135177970
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/DATAFILE/undotbs1.957.1098577207 RECID=23 STAMP=1135177970
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/CB916D184ABCFEE0E053020E040A91E6/DATAFILE/system.966.1098577265 RECID=30 STAMP=1135177975
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/DATAFILE/users.959.1098577217 RECID=22 STAMP=1135177970
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/DATAFILE/undotbs2.960.1098577223 RECID=21 STAMP=1135177970
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/system.961.1098577229 RECID=19 STAMP=1135177969
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/undotbs1.963.1098577253 RECID=24 STAMP=1135177970
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/undo_2.964.1098577257 RECID=27 STAMP=1135177970
cataloged datafile copy
datafile copy file name=+DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/users.965.1098577261 RECID=20 STAMP=1135177969
.....
......
destination for restore of datafile 00040: +DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tsptfidx.1202.1115907503
channel ORA_SBT_TAPE_6: restore complete, elapsed time: 00:00:28
channel ORA_SBT_TAPE_6: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_6: using network backup set from service proddb_dg
destination for restore of datafile 00041: +DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tsptflong.1203.1115907503
channel ORA_SBT_TAPE_7: restore complete, elapsed time: 00:00:28
channel ORA_SBT_TAPE_7: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_7: using network backup set from service proddb_dg
destination for restore of datafile 00042: +DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tstoolsdat.1204.1115907505
channel ORA_DISK_1: restore complete, elapsed time: 00:00:28
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service proddb_dg
destination for restore of datafile 00043: +DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tstoolsidx.1205.1115907505
channel ORA_DISK_2: restore complete, elapsed time: 00:00:28
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: using network backup set from service proddb_dg
destination for restore of datafile 00044: +DATAC1/PRODDBB/CB8048740F96098EE053040E040A5BE1/DATAFILE/tstoolslong.1206.1115907507
channel ORA_DISK_3: restore complete, elapsed time: 00:00:29
channel ORA_DISK_4: restore complete, elapsed time: 00:00:27
channel ORA_DISK_5: restore complete, elapsed time: 00:00:26
channel ORA_DISK_6: restore complete, elapsed time: 00:00:25
channel ORA_DISK_7: restore complete, elapsed time: 00:00:24
channel ORA_DISK_8: restore complete, elapsed time: 00:00:22
channel ORA_DISK_9: restore complete, elapsed time: 00:00:21
channel ORA_DISK_10: restore complete, elapsed time: 00:00:19
channel ORA_DISK_11: restore complete, elapsed time: 00:00:18
channel ORA_DISK_12: restore complete, elapsed time: 00:00:16
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:14
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:00:13
channel ORA_SBT_TAPE_3: restore complete, elapsed time: 00:00:12
channel ORA_SBT_TAPE_4: restore complete, elapsed time: 00:00:10
channel ORA_SBT_TAPE_5: restore complete, elapsed time: 00:00:09
channel ORA_SBT_TAPE_6: restore complete, elapsed time: 00:00:07
channel ORA_SBT_TAPE_8: restore complete, elapsed time: 00:00:33
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_SBT_TAPE_7: restore complete, elapsed time: 00:00:07
channel ORA_DISK_2: restore complete, elapsed time: 00:00:04
starting media recovery
archived log for thread 1 with sequence 53712 is already on disk as file +RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53712.17669.1135178105
archived log file name=+RECOC1/PRODDBB/ARCHIVELOG/2023_04_26/thread_1_seq_53712.17669.1135178105 thread=1 sequence=53712
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-APR-23
Executing: alter system set standby_file_management=auto
Finished recover at 26-APR-23
RMAN> exit
Recovery Manager complete.
Once the restore and recovery operations are done within RMAN we can enable again the redo apply:
[oracle@slc-exaccdb01 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Apr 26 15:25:43 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "proddbb"
Connected as SYSDG.
DGMGRL> show database proddbb
Database - proddbb
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
proddb1 (apply instance)
proddb2
Database Warning(s):
ORA-16789: standby redo logs configured incorrectly
Database Status:
WARNING
DGMGRL> edit database proddbb set state=apply-on;
Succeeded.
DGMGRL> show database proddbb
Database - proddbb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
proddb1 (apply instance)
proddb2
Database Warning(s):
ORA-16854: apply lag could not be determined
ORA-16826: apply service state is inconsistent with the DelayMins property
ORA-16789: standby redo logs configured incorrectly
Database Status:
WARNING
If you see that error message: ORA-16789: standby redo logs configured incorrectly
That is due to the incomplete recovery we have just executed, so the standby redo logs do not match to the current sequence they should be at.
We must clear the standby logs in the standby database, remember to stop the MRP redo apply process again:
DGMGRL> edit database proddbb set state=apply-off;
Succeeded.
DGMGRL> exit
[oracle@slc-exaccdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 15:30:22 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME CON_ID
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ --------- ----------
11 2621748695 1 53715 4194304000 512 162304 YES ACTIVE 444025537 26-APR-23 444027163 26-APR-23 0
12 UNASSIGNED 1 0 4194304000 512 0 NO UNASSIGNED 0
13 UNASSIGNED 1 0 4194304000 512 0 NO UNASSIGNED 0
14 UNASSIGNED 1 0 4194304000 512 0 NO UNASSIGNED 0
15 UNASSIGNED 1 0 4194304000 512 0 NO UNASSIGNED 0
21 UNASSIGNED 2 0 4194304000 512 0 YES UNASSIGNED 0
22 UNASSIGNED 2 0 4194304000 512 0 YES UNASSIGNED 0
23 UNASSIGNED 2 0 4194304000 512 0 YES UNASSIGNED 0
24 UNASSIGNED 2 0 4194304000 512 0 YES UNASSIGNED 0
25 UNASSIGNED 2 0 4194304000 512 0 YES UNASSIGNED 0
10 rows selected.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 11;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 12;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 13;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 14;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 15;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 21;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 22;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 23;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 24;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 25;
Database altered.
Let’s start the redo apply again and see if it works now:
[oracle@slc-exaccdb01 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Apr 26 15:34:22 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "proddbb"
Connected as SYSDG.
DGMGRL> edit database proddbb set state=apply-on;
Succeeded.
DGMGRL> show database proddbb
Database - proddbb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 7 seconds (computed 150 seconds ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
proddb1 (apply instance)
proddb2
Database Warning(s):
ORA-16854: apply lag could not be determined
ORA-16857: member disconnected from redo source for longer than specified threshold
Database Status:
WARNING
It takes a few seconds for the MRP to takeover and start applying the logs.
Meanwhile we can start instance 2 in mount mode as well until the standby is in sync:
[oracle@slc-exaccdb01 ~]$ srvctl start instance -db proddbb -i proddb2 -o mount
We can check the transport lag is now zero and the apply lag is ~8 mins:
[oracle@slc-exaccdb01 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Apr 26 15:37:49 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "proddbb"
Connected as SYSDG.
DGMGRL> show database proddbb
Database - proddbb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 2 seconds ago)
Apply Lag: 7 minutes 56 seconds (computed 0 seconds ago)
Average Apply Rate: 1.50 MByte/s
Real Time Query: OFF
Instance(s):
proddb1 (apply instance)
proddb2
Database Warning(s):
ORA-16854: apply lag could not be determined
ORA-16857: member disconnected from redo source for longer than specified threshold
Database Status:
WARNING
A few seconds later primary and standby are in sync:
DGMGRL> show database proddbb
Database - proddbb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 second (computed 0 seconds ago)
Average Apply Rate: 66.00 KByte/s
Real Time Query: OFF
Instance(s):
proddb1 (apply instance)
proddb2
Database Status:
SUCCESS
We can now validate and confirm both primary and standby look good:
DGMGRL> validate database verbose proddb;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
proddb: On
Capacity Information:
Database Instances Threads
proddb 2 2
Managed by Clusterware:
proddb: YES
Temporary Tablespace File Information:
proddb TEMP Files: 4
Data file Online Move in Progress:
proddb: No
Transport-Related Information:
Transport On: Yes
Log Files Cleared:
proddb Standby Redo Log Files: Cleared
DGMGRL> validate database verbose proddbb;
Database Role: Physical standby database
Primary Database: proddb
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
proddb : On
proddbb: Off
Capacity Information:
Database Instances Threads
proddb 2 2
proddbb 2 2
Managed by Clusterware:
proddb : YES
proddbb: YES
Temporary Tablespace File Information:
proddb TEMP Files: 4
proddbb TEMP Files: 4
Data file Online Move in Progress:
proddb: No
proddbb: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 6 seconds (computed 1 second ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 1 second ago)
Transport Status: Success
Log Files Cleared:
proddb Standby Redo Log Files: Cleared
proddbb Online Redo Log Files: Cleared
proddbb Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(proddb) (proddbb)
1 4 5 Sufficient SRLs
2 4 5 Sufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(proddbb) (proddb)
1 4 5 Sufficient SRLs
2 4 5 Sufficient SRLs
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(proddb) (proddbb)
1 4000 MBytes 4000 MBytes
2 4000 MBytes 4000 MBytes
Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(proddbb) (proddb)
1 4000 MBytes 4000 MBytes
2 4000 MBytes 4000 MBytes
Apply-Related Property Settings:
Property proddb Value proddbb Value
DelayMins 0 0
ApplyParallel AUTO auto
ApplyInstances 0 2
Transport-Related Property Settings:
Property proddb Value proddbb Value
LogShipping ON ON
LogXptMode ASYNC ASYNC
Dependency <empty> <empty>
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression enable enable
Now that both databases are in sync we can open the standby in read only mode and all its PDBs across all instances:
[oracle@slc-exaccdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 15:58:05 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> select inst_id, db_unique_name, open_mode, database_role from gv$database;
INST_ID DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
---------- ------------------------------ -------------------- ----------------
1 proddbb MOUNTED PHYSICAL STANDBY
2 proddbb MOUNTED PHYSICAL STANDBY
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 PDBAPP1 MOUNTED
4 PDBAPP2 MOUNTED
SQL> alter database open read only;
Database altered.
SQL> select inst_id, db_unique_name, open_mode, database_role from gv$database;
INST_ID DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
---------- ------------------------------ -------------------- ----------------
1 proddbb READ ONLY WITH APPLY PHYSICAL STANDBY
2 proddbb MOUNTED PHYSICAL STANDBY
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBAPP1 MOUNTED
4 PDBAPP2 MOUNTED
SQL>
SQL> exit
Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@slc-exaccdb01 ~]$ ssh slc-exaccdb02
Last login: Wed Apr 26 16:01:09 -03 2023
Last login: Wed Apr 26 16:01:43 2023 from slc-exaccdb01.loredata.com.br
[oracle@slc-exaccdb02 ~]$ . proddb.env
[oracle@slc-exaccdb02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 16:01:51 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> alter database open read only;
Database altered.
SQL> Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@slc-exaccdb02 ~]$ logout
Connection to slc-exaccdb02 closed.
[oracle@slc-exaccdb01 ~]$
[oracle@slc-exaccdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 16:02:27 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> alter pluggable database all open read only instances=all;
Pluggable database altered.
SQL> select inst_id, name, open_mode, restricted from gv$pdbs;
INST_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
1 PDB$SEED READ ONLY NO
1 PDBAPP1 READ ONLY NO
1 PDBAPP2 READ ONLY NO
2 PDB$SEED READ ONLY NO
2 PDBAPP1 READ ONLY NO
2 PDBAPP2 READ ONLY NO
6 rows selected.
That was all for today folks.
See you in the next one.
Franky