1. Connect PROD and check the oldest archive log and perform couple of logswitches
[oracle@oracledb ~]$ env |grep ORA
ORACLE_SID=ORCL
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oracledb ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 1 16:10:03 2024
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/backup
Oldest online log sequence 11276
Next log sequence to archive 11278
Current log sequence 11278
SQL> alter system switch logfile;
System altered.
SQL>
2. Execute the DG gap script to monitore or check the DG sync status between PROD and DR
[oracle@oraclesb ~]$ . oraenv
ORACLE_SID = [oracle] ? ORCLSB
The Oracle base has been set to /u01/app/oracle
[oracle@oraclesb ~]$
[oracle@oraclesb ~]$ env |grep ORA
ORACLE_SID=ORCLSB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclesb ~]$ cd /mnt/script/
[oracle@oraclesb script]$ ll *gap*
-rw-r--r--. 1 oracle oinstall 2279 Nov 11 2022 dg_gap_on_standby .sql
[oracle@oraclesb script]$ cd /mnt/script/
[oracle@oraclesb script]$ ll *gap*
-rw-r--r--. 1 oracle oinstall 2279 Nov 11 2022 dg_gap_on_standby.sql
[oracle@oraclesb script]$ sqlplus / as sysdba @dg_gap_on_standby.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 1 14:11:22 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
Recovery lag time from Source...
TIME LAG
--------------------------------------------------------------------------------
This DR env is 0 Hrs and -30 Mins behind last archive log from PROD...
Recovery details per thread
'--------------------------'
Last logs applied
'----------------'
SEQUENCE# THREAD# ARCHIVED APPLIED COMPLETED
---------- ---------- -------- ------- -------------------
11272 1 YES YES 2024/04/01 13:56:42
11271 1 YES YES 2024/04/01 13:41:43
11270 1 YES YES 2024/04/01 13:26:43
11269 1 YES YES 2024/04/01 13:11:43
11268 1 YES YES 2024/04/01 12:56:43
11267 1 YES YES 2024/04/01 12:41:44
11266 1 YES YES 2024/04/01 12:26:41
11265 1 YES YES 2024/04/01 12:11:44
11264 1 YES YES 2024/04/01 11:56:41
11263 1 YES YES 2024/04/01 11:41:42
The following query should show varying block# for error thread#
'---------------------------------------------------------------'
PROCESS STATUS SEQUENCE# BLOCKS BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH CLOSING 11266 1264 1
ARCH CLOSING 11271 1328 1
ARCH CLOSING 11259 7 1
ARCH CLOSING 11272 1005 1
RFS IDLE 11273 1 8489
MRP0 APPLYING_LOG 11273 409600 8489
Number of files to catch up; registered by NOT applied...
'--------------------------------------------------------'
THREAD# COUNT(1)
---------- ----------
----------
sum
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@oraclesb script]$
3. DG gap monitor script
[oracle@oraclesb script]$ more dg_gap_on_standby.sql
REM
REM Purpose: This script will help in cheking archive log gap or sync status between PROD & Standby Database
REM AUTHOR: Mallikarjun Ramadurg
REM SCRIPT Name: dg_gap_on_standby.sql
REM Usage: Connect to Standby Database on sql command prompt and run as below
REM Example: SQL>@dg_gap_on_standby.sql
REM
set verify off
set feed off
set timing off
PROMPT
PROMPT Recovery lag time from Source...
select 'This DR env is '||trunc(sysdate-max(first_time))||' Hrs and '||
trunc(((sysdate-max(first_time))*24-trunc((sysdate-max(first_time))*24))*60)||' Mins'||
' behind last archive log from PROD...' "TIME LAG"
from v$archived_log
where applied = 'YES';
PROMPT
PROMPT Recovery details per thread
PROMPT '--------------------------'
select al.thread# , min(al.sequence#) "MIN SEQ#", max(al.sequence#) "MAX SEQ#" ,
min(to_char(FIRST_TIME,'dd-mon-yy hh24:mi:ss')) "MIN FIRST TIME", round(sum(blocks * block_size) /1024/1024,2) "MB"
from v$archived_log al,
(select thread#, nvl(max(sequence#),0) max_seq from v$archived_log
where applied = 'YES' and registrar = 'RFS' and standby_dest = 'NO'
group by thread#) maxa
where applied= 'NO'
and registrar = 'RFS'
and standby_dest = 'NO'
and (al.thread# = maxa.thread# and al.sequence# > maxa.max_seq)
and exists (select 'x' from v$instance where status != 'OPEN')
group by al.thread#
having min(al.sequence#) > 0
;
PROMPT
PROMPT Last logs applied
PROMPT '----------------'
col archived for a8
col applied for a7
SELECT * FROM (
SELECT sequence#, thread#, archived, applied,
TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI:SS') AS completed
FROM sys.v$archived_log
WHERE applied='YES'
ORDER BY completed DESC)
WHERE ROWNUM <= 10;
PROMPT
PROMPT The following query should show varying block# for error thread#
PROMPT '---------------------------------------------------------------'
select process, status, sequence#, blocks, block#
from v$managed_standby
where sequence# <> 0 ;
PROMPT
PROMPT Number of files to catch up; registered by NOT applied...
PROMPT '--------------------------------------------------------'
break on report
compute sum of count(1) on report
SELECT thread#, count(1) FROM V$ARCHIVED_LOG where applied <> 'YES' group by thread# order by thread#;
set timing on
[oracle@oraclesb script]$
Regards,
Mallikarjun / Vismo Technologies
WhatsApp: +91 9880616848 / +91 9036478079
Cell: +91 9880616848 / +91 9036478079
Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com / info@vismotechnologies.com
No comments:
Post a Comment