Mallik034: April 2024

Tuesday, April 2, 2024

Automation Script | Archivelog Generation Hourly Monitoring

1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month.

[oracle@oracledb script]$ ps -ef|grep smon

oracle 2524 1 0 12:53 ? 00:00:00 ora_smon_GGSOURCE

oracle 3122 1 0 12:54 ? 00:00:00 ora_smon_ORA12C

oradev 4029 1 0 12:56 ? 00:00:00 ora_smon_ORACDB

oracle 13118 1 0 Feb22 ? 00:00:56 ora_smon_ORCL

oracle 26246 18177 0 17:33 pts/0 00:00:00 grep --color=auto smon

[oracle@oracledb script]$

2. Set the environment to a database where you are generating the archivelog generation for last 30 days

[oracle@oracledb script]$ . oraenv

ORACLE_SID = [ORCL] ? ORCL

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oracledb script]$

3. Hours archivelog generation monitoring script

[oracle@oracledb script]$ ll *hour*

-rw-r--r--. 1 oracle oinstall 1916 Oct 26 2022 archivelog_generation_hourly.sql

[oracle@oracledb script]$ more archivelog_generation_hourly.sql

set verify off

set feed off

set timing off

PROMPT

PROMPT number of hourly redo switches for the last 31 days

set pages 1000 lines 1000

SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",

COUNT (1) "Total",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",

ROUND (COUNT (1) / 24, 2) "Avg"

FROM gv$log_history

WHERE thread# = inst_id

AND first_time > sysdate-31

GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')

ORDER BY 1,2;

[oracle@oracledb script]$

4. Run the hour archivelog monitoring script against ORCL database

[oracle@oracledb script]$ sqlplus / as sysdba @archivelog_generation_hourly.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 1 17:34:09 2024

Version 19.11.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

number of hourly redo switches for the last 31 days

Date INST_ID Day Total h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13

--------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------

01-MAR-24 1 Fri 25 0 0 0 0 0 0 0 0 0 0 0 0 0 0

02-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

03-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

04-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

05-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

06-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

07-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

08-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

09-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

10-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

11-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

12-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

13-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

14-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

15-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

16-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

17-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

18-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

19-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

20-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

21-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

22-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

23-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

24-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

25-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

26-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

27-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

28-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

29-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

30-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

31-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

01-APR-24 1 Mon 93 4 4 4 4 4 4 4 4 4 4 4 4 4 23

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

[oracle@oracledb 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

Automation Script | Archive Log Deletion

1. List all the database running on the server

[oracle@oracledb ~]$ ps -ef|grep smon

oracle 2524 1 0 12:53 ? 00:00:00 ora_smon_GGSOURCE

oracle 3122 1 0 12:54 ? 00:00:00 ora_smon_ORA12C

oradev 4029 1 0 12:56 ? 00:00:00 ora_smon_ORACDB

oracle 13118 1 0 Feb22 ? 00:00:56 ora_smon_ORCL

oracle 19878 18177 0 17:14 pts/0 00:00:00 grep --color=auto smon

[oracle@oracledb ~]$

2. Check the archivelog deletion script scheduled via crontab job which is scheduled to run every Sunday

[oracle@oracledb ~]$ crontab -l

0 0 * * SUN /home/oracle/RMAN/delete_archivelog.sh

[oracle@oracledb ~]$

3. Archive log deletion script

[oracle@oracledb ~]$ more /home/oracle/RMAN/delete_archivelog.sh

unset PATH

export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

export ORACLE_SID=ORCL

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export PATH=$PATH:$ORACLE_HOME/bin

rman target / <<EOF

crosscheck archivelog all;

delete noprompt archivelog all completed before 'sysdate-2';

quit;

EOF

unset PATH

export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

export ORACLE_SID=ORA12C

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export PATH=$PATH:$ORACLE_HOME/bin

rman target / <<EOF

crosscheck archivelog all;

delete noprompt archivelog all completed before 'sysdate-2';

quit;

EOF

unset PATH

export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

export ORACLE_SID=GGSOURCE

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export PATH=$PATH:$ORACLE_HOME/bin

rman target / <<EOF

crosscheck archivelog all;

delete noprompt archivelog all completed before 'sysdate-2';

quit;

EOF

[oracle@oracledb ~]$

4. On-demand execution of the archive log deletion script

[oracle@oracledb ~]$ sh /home/oracle/RMAN/delete_archivelog.sh

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 1 17:16:25 2024

Version 19.11.0.0.0

connected to target database: ORCL (DBID=1607315920)

RMAN>

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=148 device type=DISK

validation succeeded for archived log

archived log file name=/u01/backup/1_11052_1153385377.dbf RECID=20840 STAMP=1164979956

validation succeeded for archived log

archived log file name=/u01/backup/1_11053_1153385377.dbf RECID=20843 STAMP=1164980856

validation succeeded for archived log

archived log file name=/u01/backup/1_11054_1153385377.dbf RECID=20844 STAMP=1164981756

Crosschecked 3 objects

RMAN>

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=148 device type=DISK

List of Archived Log Copies for database with db_unique_name ORCL

=====================================================================

Key Thrd Seq S Low Time

------- ---- ------- - ---------

20840 1 11052 A 30-MAR-24

Name: /u01/backup/1_11052_1153385377.dbf

20843 1 11053 A 30-MAR-24

Name: /u01/backup/1_11053_1153385377.dbf

20844 1 11054 A 30-MAR-24

Name: /u01/backup/1_11054_1153385377.dbf

deleted archived log

archived log file name=/u01/backup/1_11052_1153385377.dbf RECID=20840 STAMP=1164979956

deleted archived log

archived log file name=/u01/backup/1_11053_1153385377.dbf RECID=20843 STAMP=1164980856

deleted archived log

archived log file name=/u01/backup/1_11054_1153385377.dbf RECID=20844 STAMP=1164981756

Deleted 3 objects

RMAN>

Recovery Manager complete.

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Apr 1 17:16:28 2024

connected to target database: ORA12C (DBID=535155273)

RMAN>

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=392 device type=DISK

specification does not match any archived log in the repository

RMAN>

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=392 device type=DISK

specification does not match any archived log in the repository

RMAN>

Recovery Manager complete.

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 1 17:16:30 2024

Version 19.11.0.0.0

connected to target database: GGSOURCE (DBID=3191778824)

RMAN>

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=148 device type=DISK

specification does not match any archived log in the repository

RMAN>

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=148 device type=DISK

specification does not match any archived log in the repository

RMAN>

Recovery Manager complete.

[oracle@oracledb ~]$

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

Automation Script | Oracle House Keeping Audit & Trace Files

1. My current server running with multiple database owned by oracle and oradev user

[root@oracledb ~]# ps -ef|grep smon

oracle 2524 1 0 12:53 ? 00:00:00 ora_smon_GGSOURCE

oracle 3122 1 0 12:54 ? 00:00:00 ora_smon_ORA12C

oradev 4029 1 0 12:56 ? 00:00:00 ora_smon_ORACDB

root 8711 30209 0 16:36 pts/0 00:00:00 grep --color=auto smon

oracle 13118 1 0 Feb22 ? 00:00:56 ora_smon_ORCL

[root@oracledb ~]#

2. schedule a cleanup script to run on every Sunday

I am scheduling it with root use so that I need to schedule it multiple times for each user like in this example oracle user and oradev user.

[root@oracledb ~]# crontab -l

0 0 * * SUN /root/delete_aud.sh

[root@oracledb ~]#

3. On-demand manual execution of script

[root@oracledb ~]# sh /root/delete_aud.sh

[root@oracledb ~]#

4. Cleanup script

[root@oracledb ~]# more /root/delete_aud.sh

#!/bin/bash

find /u01/app/oracle/admin/GGSOURCE/adump -type f -mtime +7 -name '*.aud' -exec rm -f {} \;

find /u01/app/oracle/admin/ORA12C/adump -type f -mtime +7 -name '*.aud' -exec rm -f {} \;

find /u01/app/oracle/admin/ORCL/adump -type f -mtime +7 -name '*.aud' -exec rm -f {} \;

find /u01/app/oradev/admin/ORACDB/adump -type f -mtime +7 -name '*.aud' -exec rm -f {} \;

#diag or trace or xml

find /u01/app/oracle/diag/rdbms/ggsource/GGSOURCE/trace -type f -mtime +7 -name '*.trc' -exec rm -f {} \;

find /u01/app/oracle/diag/rdbms/ggsource/GGSOURCE/trace -type f -mtime +7 -name '*.trm' -exec rm -f {} \;

find /u01/app/oracle/diag/rdbms/ggsource/GGSOURCE/alert -type f -mtime +7 -name '*.xml' -exec rm -f {} \;

find /u01/app/oracle/diag/rdbms/ggsource/GGSOURCE/incident -type d -mtime +7 -name 'incdir_*' -exec rm -rf {} \;

#diag or trace or xml

find /u01/app/oracle/diag/rdbms/ora12c/ORA12C/trace -type f -mtime +7 -name '*.trc' -exec rm -f {} \;

find /u01/app/oracle/diag/rdbms/ora12c/ORA12C/trace -type f -mtime +7 -name '*.trm' -exec rm -f {} \;

find /u01/app/oracle/diag/rdbms/ora12c/ORA12C/alert -type f -mtime +7 -name '*.xml' -exec rm -f {} \;

find /u01/app/oracle/diag/rdbms/ora12c/ORA12C/incident -type d -mtime +7 -name 'incdir_*' -exec rm -rf {} \;

#diag or trace or xml

find /u01/app/oracle/diag/rdbms/orcl/ORCL/trace -type f -mtime +7 -name '*.trc' -exec rm -f {} \;

find /u01/app/oracle/diag/rdbms/orcl/ORCL/trace -type f -mtime +7 -name '*.trm' -exec rm -f {} \;

find /u01/app/oracle/diag/rdbms/orcl/ORCL/alert -type f -mtime +7 -name '*.xml' -exec rm -f {} \;

find /u01/app/oracle/diag/rdbms/orcl/ORCL/incident -type d -mtime +7 -name 'incdir_*' -exec rm -rf {} \;

#diag or trace or xml

find /u01/app/oradev/diag/rdbms/oracdb/ORACDB/trace -type f -mtime +7 -name '*.trc' -exec rm -f {} \;

find /u01/app/oradev/diag/rdbms/oracdb/ORACDB/trace -type f -mtime +7 -name '*.trm' -exec rm -f {} \;

find /u01/app/oradev/diag/rdbms/oracdb/ORACDB/alert -type f -mtime +7 -name '*.xml' -exec rm -f {} \;

find /u01/app/oradev/diag/rdbms/oracdb/ORACDB/incident -type d -mtime +7 -name 'incdir_*' -exec rm -rf {} \;

[root@oracledb ~]#

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

DataGuard sync status monitor automation script

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

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

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

Mallik034

Tuesday, April 2, 2024

Automation Script | Archivelog Generation Hourly Monitoring

1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month.

2. Set the environment to a database where you are generating the archivelog generation for last 30 days

3. Hours archivelog generation monitoring script

4. Run the hour archivelog monitoring script against ORCL database

Automation Script | Archive Log Deletion

1. List all the database running on the server

2. Check the archivelog deletion script scheduled via crontab job which is scheduled to run every Sunday

3. Archive log deletion script

4. On-demand execution of the archive log deletion script

Automation Script | Oracle House Keeping Audit & Trace Files

1. My current server running with multiple database owned by oracle and oradev user

2. schedule a cleanup script to run on every Sunday

3. On-demand manual execution of script

4. Cleanup script

DataGuard sync status monitor automation script

1. Connect PROD and check the oldest archive log and perform couple of logswitches

2. Execute the DG gap script to monitore or check the DG sync status between PROD and DR

3. DG gap monitor script

Automation Script | Archivelog Generation Hourly Monitoring

Social Plugin

Blog Archive