Mallik034: 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

Wednesday, March 27, 2024

Automation Script To Find the ASM diskgroup directory & Subdirectory Usage

This is 2 node Cluster: Node1 & Node2

[oraprod@node1 ~]$ ps -ef|grep smon

root 13869 1 1 00:09 ? 00:00:56 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 14278 1 0 00:10 ? 00:00:00 asm_smon_+ASM1

oracle 15184 1 0 00:10 ? 00:00:00 ora_smon_ORA19C

oraprod 15276 1 0 00:10 ? 00:00:00 ora_smon_SINGLE

oracle 15351 1 0 00:10 ? 00:00:00 ora_smon_CDBDB1

oracle 15378 1 0 00:10 ? 00:00:00 ora_smon_RAC12C1

oracle 15678 1 0 00:10 ? 00:00:00 ora_smon_CDB1

oracle 15759 1 0 00:10 ? 00:00:00 ora_smon_PRIMDB1

oraprod 22165 1 0 00:26 ? 00:00:00 ora_smon_ORAPROD1

oraprod 30323 20060 0 01:05 pts/0 00:00:00 grep --color=auto smon

[oraprod@node1 ~]$

[oraprod@node2 ~]$ ps -ef|grep smon

root 4178 1 1 Jan01 ? 1-13:27:12 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 5663 1 0 Jan01 ? 00:02:48 asm_smon_+ASM2

oraprod 8630 28730 0 01:04 pts/0 00:00:00 grep --color=auto smon

oracle 22302 1 0 Feb29 ? 00:01:52 ora_smon_CDBDB2

oracle 22308 1 0 Feb29 ? 00:01:01 mdb_smon_-MGMTDB

oracle 22359 1 0 Feb29 ? 00:01:59 ora_smon_RAC12C2

oracle 22771 1 0 Feb29 ? 00:03:05 ora_smon_PRIMDB2

oracle 26469 1 0 Mar23 ? 00:00:16 ora_smon_CDB2

oracle 30889 1 0 Jan06 ? 00:03:16 ora_smon_RAC1N_2

oraprod 31546 1 0 00:26 ? 00:00:00 ora_smon_ORAPROD2

[oraprod@node2 ~]$

This 2 node RAC cluster has +DATA and +RECO diskgroup

[oraprod@node1 ~]$ . oraenv

ORACLE_SID = [+ASM1] ?

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

[oraprod@node1 ~]$

[oraprod@node1 ~]$ env |grep ORA

ORACLE_SID=+ASM1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/19.0.0.0/grid

[oraprod@node1 ~]$

[oraprod@node1 ~]$ asmcmd -p lsdg

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 512 4096 4194304 81904 8572 0 8572 0 Y DATA/

MOUNTED EXTERN N 512 512 4096 4194304 40952 18948 0 18948 0 N RECO/

[oraprod@node1 ~]$

These +DATA and +RECO diskgroups have multiple sub directories inside

[oraprod@node1 ~]$ asmcmd -p

ASMCMD [+] > cd DATA/

ASMCMD [+DATA] > ls -l

Type Redund Striped Time Sys Name

Y ASM/

N CDB/

N CDBDB/

Y ORA19C/

N ORAPROD/

N PRIMDB/

N RAC12C/

N RAC1N/

N SINGLE/

N _mgmtdb/

CONTROLFILE UNPROT FINE MAR 07 13:00:00 N cdbdbsnapcf_cdbdb1.f => +DATA/CDBDB/CONTROLFILE/Backup.256.1106324849

Y node-clu/

CONTROLFILE UNPROT FINE MAR 07 13:00:00 N ora19csnapcf_ora19c.f => +DATA/ORA19C/CONTROLFILE/Backup.257.1106324849

ASMCMD [+DATA] > cd +RECO/

ASMCMD [+RECO] > ls -l

Type Redund Striped Time Sys Name

N CDB/

N CDBDB/

Y ORAPROD/

Y PRIMDB/

Y RAC12C/

Y RAC1N/

Y SINGLE/

ASMCMD [+RECO] > exit

[oraprod@node1 ~]$

With helpf of this find_ASM_direcory_size.sh automation script we can figure out which directory or database consumes more space and take a necessary action accordngly

[oraprod@node1 ~]$ cd /mnt/script/

[oraprod@node1 script]$ ll find_ASM_direcory_size.sh

-rwxr-xr-x 1 oracle oinstall 1016 Jan 26 2023 find_ASM_direcory_size.sh

[oraprod@node1 script]$ sh find_ASM_direcory_size.sh

Please provide a directory !

[oraprod@node1 script]$

Below command will get all the directory usage under +DATA diskgroup

[oraprod@node1 script]$ sh find_ASM_direcory_size.sh DATA

DATA subdirectories size

Subdir Used MB Mirror MB

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

ASM/ 0 0

CDB/ 10008 10008

CDBDB/ 8456 8456

ORA19C/ 64 64

ORAPROD/ 5460 5460

PRIMDB/ 9684 9684

RAC12C/ 6560 6560

RAC1N/ 4012 4012

SINGLE/ 3696 3696

_mgmtdb/ 24976 24976

cdbdbsnapcf_cdbdb1.f 0 0

node-clu/ 208 208

ora19csnapcf_ora19c.f 0 0

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

Total 73124 73124

[oraprod@node1 script]$

Below command will get all the directory usage under +RECO diskgroup

[oraprod@node1 script]$ sh find_ASM_direcory_size.sh RECO

RECO subdirectories size

Subdir Used MB Mirror MB

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

CDB/ 1508 1508

CDBDB/ 2504 2504

ORAPROD/ 3020 3020

PRIMDB/ 5080 5080

RAC12C/ 5136 5136

RAC1N/ 2456 2456

SINGLE/ 2132 2132

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

Total 21836 21836

[oraprod@node1 script]$

Even we can further drilldown and check the each sudirectorywise utilisation

Below command will get all the sub-directory usage under +RECO/PRIMDB directory

[oraprod@node1 script]$ sh find_ASM_direcory_size.sh RECO/PRIMDB/

RECO/PRIMDB/ subdirectories size

Subdir Used MB Mirror MB

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

ARCHIVELOG/ 3180 3180

CONTROLFILE/ 64 64

ONLINELOG/ 1836 1836

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

Total 5080 5080

[oraprod@node1 script]$

Script:

[root@oracledb script]# cat find_ASM_direcory_size.sh

#!/bin/bash

# du of each subdirectory in a directory for ASM

# Purpose: This script will help in finding the size of the all the direcotirs and subdirectories inside ASM DiskGroup

# AUTHOR: Mallikarjun Ramadurg

# SCRIPT Name: find_ASM_direcory_size.sh

# Usage: Run this script at OS level after setting enviromental variable to ASM with input parameters as shown below

# Example: sh find_ASM_direcory_size.sh DATA

# Example: sh find_ASM_direcory_size.sh DATA/ORADB

D=$1

if [[ -z $D ]]

then

echo "Please provide a directory !"

exit 1

(for DIR in `asmcmd ls ${D}`

echo ${DIR} `asmcmd du ${D}/${DIR} | tail -1`

done) | awk -v D="$D" ' BEGIN { printf("\n\t\t%40s\n\n", D " subdirectories size") ;

printf("%25s%16s%16s\n", "Subdir", "Used MB", "Mirror MB") ;

printf("%25s%16s%16s\n", "------", "-------", "---------") ;}

{

printf("%25s%16s%16s\n", $1, $2, $3) ;

use += $2 ;

mir += $3 ;

}

END { printf("\n\n%25s%16s%16s\n", "------", "-------", "---------");

printf("%25s%16s%16s\n\n", "Total", use, mir) ;} '

[root@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

SPFILE & PASSWORD file missing from ASM diskgroup in RAC Database

1. spfile missing from ASM DiskGroup for RAC database

2. passwordfile missing from ASM DiskGroup for RAC database

[oraprod@node2 ~]$ srvctl status database -d ORAPROD

Instance ORAPROD1 is running on node node1

Instance ORAPROD2 is not running on node node2

[oraprod@node2 ~]$

[oraprod@node2 ~]$ srvctl start instance -i ORAPROD2 -d ORAPROD

PRCR-1013 : Failed to start resource ora.oraprod.db

PRCR-1064 : Failed to start resource ora.oraprod.db on node node2

CRS-5017: The resource action "ora.oraprod.db start" encountered the following error:

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oraprod/product/12.2.0.1/dbhome_1/dbs/initORAPROD2.ora'

. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/node2/crs/trace/crsd_oraagent_oraprod.trc".

CRS-2674: Start of 'ora.oraprod.db' on 'node2' failed

[oraprod@node2 ~]$

[oraprod@node2 ~]$ srvctl config database -d ORAPROD

Database unique name: ORAPROD

Database name:

Oracle home: /u01/app/oraprod/product/12.2.0.1/dbhome_1

Oracle user: oraprod

Spfile:

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: ORAPROD1,ORAPROD2

Configured nodes: node1,node2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oraprod@node2 ~]$

[oraprod@node1 ~]$ . oraenv

ORACLE_SID = [ORAPROD1] ?

The Oracle base has been changed from /home/oracle to /u01/app/oraprod

[oraprod@node1 ~]$

[oraprod@node1 ~]$ srvctl config database -d ORAPROD

Database unique name: ORAPROD

Database name:

Oracle home: /u01/app/oraprod/product/12.2.0.1/dbhome_1

Oracle user: oraprod

Spfile:

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: ORAPROD1,ORAPROD2

Configured nodes: node1,node2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oraprod@node1 ~]$

[oraprod@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 27 00:17:20 2024

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string

SQL>

SQL> create spfile='+DATA/ORAPROD/PARAMETERFILE/spfileORAPROD.ora' from pfile;

File created.

SQL>

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oraprod@node1 ~]$ srvctl modify database -d ORAPROD -spfile +DATA/ORAPROD/PARAMETERFILE/spfileORAPROD.ora

[oraprod@node1 ~]$

[oraprod@node1 ~]$ srvctl config database -d ORAPROD

Database unique name: ORAPROD

Database name:

Oracle home: /u01/app/oraprod/product/12.2.0.1/dbhome_1

Oracle user: oraprod

Spfile: +DATA/ORAPROD/PARAMETERFILE/spfileORAPROD.ora

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: ORAPROD1,ORAPROD2

Configured nodes: node1,node2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oraprod@node1 ~]$

Option 1: use orapwd utility to create password file

[oraprod@node1 admin]$ orapwd file='+DATA/CDBDB/PASSWORD/pwdORAPROD' ENTRIES=5 DBUNIQUENAME='ORAPROD'

Enter password for SYS:

[oraprod@node1 admin]$

[oraprod@node1 admin]$ sqlplus sys/Mallik123#@ORAPROD as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 27 00:24:55 2024

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oraprod@node1 admin]$

[oraprod@node1 admin]$ srvctl config database -d ORAPROD

Database unique name: ORAPROD

Database name:

Oracle home: /u01/app/oraprod/product/12.2.0.1/dbhome_1

Oracle user: oraprod

Spfile: +DATA/ORAPROD/PARAMETERFILE/spfileORAPROD.ora

Password file: +DATA/CDBDB/PASSWORD/pwdoraprod

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: ORAPROD1,ORAPROD2

Configured nodes: node1,node2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oraprod@node1 admin]$

[oraprod@node1 admin]$ srvctl stop database -d ORAPROD

[oraprod@node1 admin]$ srvctl start database -d ORAPROD

[oraprod@node1 admin]$ srvctl status database -d ORAPROD

Instance ORAPROD1 is running on node node1

Instance ORAPROD2 is running on node node2

[oraprod@node1 admin]$

[oraprod@node1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 27 00:37:03 2024

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string +DATA/ORAPROD/PARAMETERFILE/sp

fileoraprod.ora

SQL>

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oraprod@node1 admin]$

Option 2: use pwcreate utility to create password file

[oraprod@node1 admin]$ . oraenv

ORACLE_SID = [ORAPROD1] ? +ASM1

The Oracle base has been changed from /u01/app/oraprod to /u01/app/oracle

[oraprod@node1 admin]$

[oraprod@node1 admin]$ asmcmd -p

ASMCMD [+] > pwcreate --dbuniquename ORAPROD +DATA/CDBDB/PASSWORD/pwdoraprod oraprod

ASMCMD-9465: WARNING: passing password on command line is deprecated

OPW-00010: Could not create the password file. A password file with this name is present.

ASMCMD-9454: could not create new password file

ASMCMD [+] > rm +DATA/CDBDB/PASSWORD/pwdoraprod

ASMCMD [+] > pwcreate --dbuniquename ORAPROD +DATA/CDBDB/PASSWORD/pwdoraprod Mallik123#

ASMCMD-9465: WARNING: passing password on command line is deprecated

OPW-00010: Could not create the password file. This resource has a Password File.

ASMCMD-9454: could not create new password file

ASMCMD [+] >

ASMCMD [+] > ls +DATA/CDBDB/PASSWORD/pwdoraprod

ASMCMD-8002: entry 'pwdoraprod' does not exist in directory '+DATA/CDBDB/PASSWORD/'

ASMCMD [+] > cd +DATA/CDBDB/PASSWORD

ASMCMD [+DATA/CDBDB/PASSWORD] > ls -l

Type Redund Striped Time Sys Name

PASSWORD UNPROT COARSE MAR 19 2021 Y pwdcdbdb.289.1067560793

ASMCMD [+DATA/CDBDB/PASSWORD] > exit

[oraprod@node1 admin]$

The reason why pwcreate utility is unable to create was in srvctl configuration already passwordfile is configured, We have to remove that and retry pwcreate command as shown below

[oraprod@node2 admin]$ srvctl config database -d ORAPROD |grep Password

Password file: +DATA/CDBDB/PASSWORD/pwdoraprod

[oraprod@node2 admin]$ srvctl modify database -d ORAPROD -pwfile

[oraprod@node2 admin]$

[oraprod@node2 admin]$ srvctl config database -d ORAPROD |grep Password

Password file:

[oraprod@node2 admin]$

[oraprod@node1 admin]$ asmcmd -p

ASMCMD [+] > pwcreate --dbuniquename ORAPROD +DATA/ORAPROD/PASSWORD/pwdoraprod Mallik123#

ASMCMD-9465: WARNING: passing password on command line is deprecated

ASMCMD [+] > exit

[oraprod@node1 admin]$

[oraprod@node2 admin]$ srvctl config database -d ORAPROD

Database unique name: ORAPROD

Database name:

Oracle home: /u01/app/oraprod/product/12.2.0.1/dbhome_1

Oracle user: oraprod

Spfile: +DATA/ORAPROD/PARAMETERFILE/spfileORAPROD.ora

Password file: +DATA/ORAPROD/PASSWORD/pwdoraprod

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: ORAPROD1,ORAPROD2

Configured nodes: node1,node2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oraprod@node2 admin]$

[oraprod@node2 admin]$ sqlplus sys/Mallik123#@ORAPROD as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 27 00:46:45 2024

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oraprod@node2 admin]$

Upcoming Batch Schedule link:

https://mallik034.blogspot.com/p/upcoming-batch-schedule.html

#mallik034 #vismotechnologies #spfile #passworfile #oracledba #oraclecarrers #oracleworld #oracle #database #training #onlinetraining #liveclasses

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

Tuesday, March 26, 2024

ADRCI In Oracle

ADRCI

ADRCI is the command line interface for diagnostic utility used for viewing diagnostics data like listener log , alert log ,incident and cor dump etc and creating incident packages. Below are the the list of useful commands.

1. Get current base location:(Its known as ADR_BASE)

adrci> show base

ADR base is "/u01/app/oracle/"

2. Set new ORACLE_BASE(ADR_BASE)

adrci> set base /u01/app/grid

3. List current ORACLE_HOME

adrci> show home

4. Set new ORACLE_HOME

adrci> set homepath /u02/app/oracle

5. View alert log

adrci> show alert

adrci> show alert -tail 100

6. Purge alerts and trace files

-- This will purge data older than 600 minutes.

adrci> purge -age 600 -type ALERT

adrci> purge -age 600 -type TRACE

adrci> purge -age 600 -type incident

adrci> purge -age 10080 -type cdump

7. Set control policy for auto purge of files

There are two types of policies,

LONGP_POLICY is used to purge below data . Default value is 365 days.

• ALERT

• INCIDENT

• SWEEP

• STAGE

• HM

SHORTP_POLICY is used to purge for below data Default value is 30 days.

• TRACE

• CDUMP

• UTSCDMP

• IPS

— Get existing control policy

adrci> show control

Change default value of control policy details.

-- Set in hours.

adrci> set control (SHORTP_POLICY = 240)

adrci> set control (LONGP_POLICY = 600)

8 . Create incident package:

adrci> show incident

adrci>IPS CREATE PACKAGE INCIDENT

(or)

adrci> ips pack incident in /tmp

Generated package 9 in file /tmp/ORA1578_20090602113045_COM_1.zip, mode complete

We can create empty package and add required incident or problem or alert log files.

-- Create empty package

adrci>IPS CREATE PACKAGE

-- add the necessary incident files. ( package_number will be displayed in the above command)

adrci> IPS ADD INCIDENT incident_number PACKAGE 2

adrci> IPS ADD FILE /u01/app/oracle/alert_db.log PACKAGE 2

-- Now generate the package file.

adrci>IPS GENERATE PACKAGE 2 IN /home/oracle/housekeeping

9. Unpack a ips file

adrci> ips unpack file ORA_98928.zip into /tmp/housekeeping

10. Pack all incident files within a particular time frame

--Generates the package with the incidents occurred between the times '2023-03-01 12:00:00.00' and '2023-03-02 23:00:00.00'

ips pack time '2023-03-01 12:00:00.00' to '2023-03-02 23:00:00.00'

11. View package information

adrci> ips show package

adrci> ips show package 12 detail

12. Remove/delete package information:

-- Delete the complete package:

adrci> ips delete package 2

-- Remove incidents from the packages

adrci > ips remove incident 2 package 7

-- Remove the problem keys from packages

adrci > ips remove problem 4 package 8

Refer more details from oracle site

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-adr-command-interpreter-adrci.html#GUID-DC5744C7-FAC0-436B-99D5-DBD45B66930B

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-adr-command-interpreter-adrci.html#GUID-DC5744C7-FAC0-436B-99D5-DBD45B66930B

Logs:

[oracle@oraclelab1 diag]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Mar 25 23:25:54 2024

ADR base = "/u01/app/oracle"

adrci> show homepath

ADR Homes:

diag/rdbms/devdb/DEVDB

diag/rdbms/dev/DEV

diag/rdbms/test/TEST

diag/rdbms/uat/UAT

diag/rdbms/devcdb/DEVCDB

diag/rdbms/testcdb/TESTCDB

diag/rdbms/orclsba/ORCLSBA

diag/clients/user_oracle/host_1462227985_110

diag/tnslsnr/oraclelab1/listener_devdb

diag/tnslsnr/oraclelab1/listener

diag/tnslsnr/oraclelab1/listener_orclsba

adrci> set homepath diag/rdbms/devdb/DEVDB

adrci> show homepath

ADR Homes:

diag/rdbms/devdb/DEVDB

adrci> exit

[oracle@oraclelab1 diag]$

[oracle@oraclelab1 diag]$ cd

[oracle@oraclelab1 ~]$ clear

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

oracle 16568 1 0 09:02 ? 00:00:00 ora_smon_DEVDB

oracle 24829 20401 0 23:27 pts/0 00:00:00 grep --color=auto smon

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ env |grep ORA

ORACLE_SID=DEVDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Mar 25 23:28:32 2024

ADR base = "/u01/app/oracle"

adrci> show base

ADR base is "/u01/app/oracle"

adrci>

adrci> set base /u02/app/oracle

DIA-48447: The input path [/u02/app/oracle] does not contain any ADR homes

adrci>

adrci> show hopepath

DIA-48415: Syntax error found in string [show hopepath] at column [13]

adrci> show homepath

ADR Homes:

diag/rdbms/devdb/DEVDB

diag/rdbms/dev/DEV

diag/rdbms/test/TEST

diag/rdbms/uat/UAT

diag/rdbms/devcdb/DEVCDB

diag/rdbms/testcdb/TESTCDB

diag/rdbms/orclsba/ORCLSBA

diag/clients/user_oracle/host_1462227985_110

diag/tnslsnr/oraclelab1/listener_devdb

diag/tnslsnr/oraclelab1/listener

diag/tnslsnr/oraclelab1/listener_orclsba

adrci>

adrci> set homepath diag/rdbms/devdb/DEVDB

adrci>

adrci> show homepath

ADR Homes:

diag/rdbms/devdb/DEVDB

adrci>

adrci> show alert

ADR Home = /u01/app/oracle/diag/rdbms/devdb/DEVDB:

*************************************************************************

Output the results to file: /tmp/alert_24890_1396_DEVDB_1.ado

adrci>

adrci> show alert -tail 10

2024-03-25 22:00:48.429000 +05:30

Private strand flush not complete

Current log# 1 seq# 355 mem# 0: /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_1_lcx8xwt5_.log

Current log# 1 seq# 355 mem# 1: /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_1_lcx8xx9r_.log

2024-03-25 22:00:51.445000 +05:30

Thread 1 advanced to log sequence 356 (LGWR switch), current SCN: 19705879

Current log# 2 seq# 356 mem# 0: /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_2_lcx8xwv2_.log

Current log# 2 seq# 356 mem# 1: /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_2_lcx8xxcn_.log

2024-03-25 22:00:52.498000 +05:30

ARC0 (PID:16626): Archived Log entry 351 added for T-1.S-355 ID 0x3e743412 LAD:1

2024-03-25 22:01:48.114000 +05:30

TABLE SYS.WRP$_REPORTS: ADDED INTERVAL PARTITION SYS_P2225 (5198) VALUES LESS THAN (TO_DATE(' 2024-03-26 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLE SYS.WRP$_REPORTS_DETAILS: ADDED INTERVAL PARTITION SYS_P2226 (5198) VALUES LESS THAN (TO_DATE(' 2024-03-26 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLE SYS.WRP$_REPORTS_TIME_BANDS: ADDED INTERVAL PARTITION SYS_P2229 (5197) VALUES LESS THAN (TO_DATE(' 2024-03-25 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

adrci> :1

DIA-48415: Syntax error found in string [:1] at column [2]

adrci>

adrci> exit

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ locate alert_DEVDB.log

/u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log

/u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/log/alert_DEVDB.log

[oracle@oraclelab1 ~]$ tail -10f /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log

2024-03-25T22:00:51.445813+05:30

Thread 1 advanced to log sequence 356 (LGWR switch), current SCN: 19705879

Current log# 2 seq# 356 mem# 0: /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_2_lcx8xwv2_.log

Current log# 2 seq# 356 mem# 1: /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_2_lcx8xxcn_.log

2024-03-25T22:00:52.498205+05:30

ARC0 (PID:16626): Archived Log entry 351 added for T-1.S-355 ID 0x3e743412 LAD:1

2024-03-25T22:01:48.114691+05:30

TABLE SYS.WRP$_REPORTS: ADDED INTERVAL PARTITION SYS_P2225 (5198) VALUES LESS THAN (TO_DATE(' 2024-03-26 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLE SYS.WRP$_REPORTS_DETAILS: ADDED INTERVAL PARTITION SYS_P2226 (5198) VALUES LESS THAN (TO_DATE(' 2024-03-26 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLE SYS.WRP$_REPORTS_TIME_BANDS: ADDED INTERVAL PARTITION SYS_P2229 (5197) VALUES LESS THAN (TO_DATE(' 2024-03-25 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Mar 25 23:31:36 2024

ADR base = "/u01/app/oracle"

adrci> show base

ADR base is "/u01/app/oracle"

adrci> show homepath

ADR Homes:

diag/rdbms/devdb/DEVDB

diag/rdbms/dev/DEV

diag/rdbms/test/TEST

diag/rdbms/uat/UAT

diag/rdbms/devcdb/DEVCDB

diag/rdbms/testcdb/TESTCDB

diag/rdbms/orclsba/ORCLSBA

diag/clients/user_oracle/host_1462227985_110

diag/tnslsnr/oraclelab1/listener_devdb

diag/tnslsnr/oraclelab1/listener

diag/tnslsnr/oraclelab1/listener_orclsba

adrci> set homepath diag/rdbms/devdb/DEVDB

adrci> show homepath

ADR Homes:

diag/rdbms/devdb/DEVDB

adrci>

adrci> purge -age 600 -typr ALERT

DIA-48415: Syntax error found in string [purge -age 600 -typr] at column [20]

adrci> purge -age 600 -type ALERT

adrci> purge -age 600 -type TRACE

adrci> purge -age 600 -type incident

adrci> purge -age 10080 -type cdump

adrci>

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/devdb/DEVDB:

*************************************************************************

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME SIZEP_POLICY PURGE_PERIOD FLAGS PURGE_THRESHOLD

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

458116003 720 8760 2023-07-24 21:43:56.256053 +05:30 2024-03-25 09:01:41.364048 +05:30 2024-03-25 23:32:44.478668 +05:30 1 2 110 1 2023-07-24 21:43:56.256053 +05:30 18446744073709551615 0 0 95

1 row fetched

adrci> set control (SHORTP_POLICY = 240)

adrci> set control (LONGP_POLICY = 600)

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/devdb/DEVDB:

*************************************************************************

458116003 240 600 2024-03-25 23:36:48.915111 +05:30 2024-03-25 09:01:41.364048 +05:30 2024-03-25 23:32:44.478668 +05:30 1 2 110 1 2023-07-24 21:43:56.256053 +05:30 18446744073709551615 0 0 95

1 row fetched

adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/devdb/DEVDB:

*************************************************************************

0 rows fetched

adrci> IPS CREATE PACKAGE INCIDENT

DIA-48415: Syntax error found in string [IPS CREATE PACKAGE INCIDENT] at column [27]

adrci> ips pack incident in /tmp

DIA-48415: Syntax error found in string [ips pack incident in] at column [20]

DIA-48438: [in] is not a valid number

adrci> show homepath

ADR Homes:

diag/rdbms/devdb/DEVDB

adrci> set homepath diag/rdbms/uat/UAT

adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/uat/UAT:

*************************************************************************

INCIDENT_ID PROBLEM_KEY CREATE_TIME

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

84289 ORA 603 2024-02-21 10:19:58.259000 +05:30

86689 ORA 603 2024-02-21 10:20:24.149000 +05:30

89089 ORA 603 2024-02-21 10:21:29.428000 +05:30

91489 ORA 700 [kdmfsIsSetup-bad-schema-version] 2024-02-21 10:23:04.538000 +05:30

91457 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-21 10:23:23.219000 +05:30

91458 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-21 10:23:31.910000 +05:30

91577 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-21 10:24:32.189000 +05:30

91593 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-21 10:26:32.676000 +05:30

91594 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-21 10:30:33.372000 +05:30

91698 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-22 15:26:49.727000 +05:30

91417 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-22 15:53:07.903000 +05:30

91433 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-22 15:56:59.838000 +05:30

91418 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-23 20:26:54.019000 +05:30

91699 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-23 20:53:13.024000 +05:30

91419 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-23 20:57:04.956000 +05:30

91490 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-24 22:11:16.577000 +05:30

91420 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-25 01:26:58.048000 +05:30

91700 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-25 01:53:14.697000 +05:30

91421 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-25 01:57:06.617000 +05:30

91491 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-25 08:17:33.231000 +05:30

91492 ORA 600 [kewrpwrc_2: Invalid SWRF version] 2024-02-25 12:47:44.664000 +05:30

21 rows fetched

adrci> IPS CREATE PACKAGE INCIDENT

DIA-48415: Syntax error found in string [IPS CREATE PACKAGE INCIDENT] at column [27]

adrci> ips pack incident in /tmp

DIA-48415: Syntax error found in string [ips pack incident in] at column [20]

DIA-48438: [in] is not a valid number

adrci>

adrci> set homepath diag/rdbms/devdb/DEVDB

adrci> IPS CREATE PACKAGE

Created package 1 without any contents, correlation level typical

adrci> IPS ADD INCIDENT 84289 PACKAGE 1

DIA-49431: No such incident [84289]

adrci>

adrci> exit

[oracle@oraclelab1 ~]$ ls -ltrh

total 3.4M

drwxr-xr-x. 2 oracle oinstall 6 Jul 11 2022 Videos

drwxr-xr-x. 2 oracle oinstall 6 Jul 11 2022 Templates

drwxr-xr-x. 2 oracle oinstall 6 Jul 11 2022 Public

drwxr-xr-x. 2 oracle oinstall 6 Jul 11 2022 Pictures

drwxr-xr-x. 2 oracle oinstall 6 Jul 11 2022 Music

drwxr-xr-x. 2 oracle oinstall 6 Jul 11 2022 Documents

drwxr-xr-x. 2 oracle oinstall 6 Jul 11 2022 Desktop

-rw-r--r--. 1 oracle oinstall 20K Jul 11 2022 db_19.3.0.0.rsp

-rw-r-----. 1 oracle oinstall 26K Jul 11 2022 dbca_DEVDB.rsp

-rw-r-----. 1 oracle oinstall 26K Jul 26 2022 dbca_DEVCDB.rsp

-rw-r-----. 1 oracle oinstall 26K Jul 26 2022 dbca_TESTCDB.rsp

-rwxrwxrwx. 1 oracle oinstall 737 Jul 28 2022 level0.sh

-rw-r-----. 1 oracle oinstall 26K Aug 8 2022 dbca_DEV_12.2.0.1.rsp

-rwxrwxrwx. 1 oracle oinstall 657 Aug 9 2022 level.sh

-rw-r--r--. 1 oracle oinstall 26K Aug 16 2022 mallik_ash.hrml

-rw-r--r--. 1 oracle oinstall 644K Aug 16 2022 mallik_CDB.html

-rw-r--r--. 1 oracle oinstall 23K Aug 23 2022 db_12.2.0.1.rsp

-rw-r--r--. 1 oracle oinstall 26 Oct 4 2022 mallik.sh

-rwxrwxrwx. 1 oracle oinstall 737 Oct 19 2022 level1.sh

-rw-r--r--. 1 oracle oinstall 582K Nov 8 2022 mallik_08_Nov_2022.html

-rw-r--r--. 1 oracle oinstall 105 Dec 8 2022 bash.sh

-rw-r--r--. 1 oracle oinstall 81 Dec 8 2022 query.sql

drwxr-xr-x. 2 oracle oinstall 6 Dec 22 2022 Downloads

drwxr-xr-x. 3 oracle oinstall 4.0K Jan 11 2023 Daily

-rwxr-xr-x. 1 oracle oinstall 751 Feb 16 2023 level_0.sh

-rwxr-xr-x. 1 oracle oinstall 751 Feb 16 2023 level_1.sh

-rw-r--r--. 1 oracle oinstall 353 Feb 18 2023 '

-rwxr-xr-x. 1 oracle oinstall 869 Mar 7 2023 rman_level_0.sh

-rwxr-xr-x. 1 oracle oinstall 870 Mar 7 2023 rman_level_1.sh

-rw-r--r--. 1 oracle oinstall 642K Mar 23 2023 mallik.html

-rw-r--r--. 1 oracle oinstall 32K Mar 27 2023 mallik_ash.html

-rw-r--r--. 1 oracle oinstall 94 Apr 27 2023 mallik1.sh

-rwxrwxrwx. 1 oracle oinstall 734 May 11 2023 rman_DEVDB_level_0.sh

-rwxrwxrwx. 1 oracle oinstall 752 May 11 2023 rman_DEVDB_level_1.sh

-rwxrwxrwx. 1 oracle oinstall 266 Jun 7 2023 DEV.env

-rwxrwxrwx. 1 oracle oinstall 2.7K Jun 7 2023 instance_select_on_logon.sh

-rw-r--r--. 1 oracle oinstall 47 Jul 23 2023 mallik.txt

-rw-r--r--. 1 oracle oinstall 52 Jul 28 2023 script.sql

-rwxrwxrwx. 1 oracle oinstall 147 Jul 28 2023 batch_sqlplus.sh

-rw-------. 1 oracle oinstall 2.3K Jul 28 2023 nohup.out

-rwxrwxr-x. 1 oracle oinstall 801 Aug 11 2023 RMAN_L0_DEVDB_Sun.sh

-rwxrwxr-x. 1 oracle oinstall 801 Aug 11 2023 RMAN_L1_DEVDB_Mon_Sat.sh

-rw-r--r--. 1 oracle oinstall 72 Aug 12 2023 afiedt.buf

-rw-r--r--. 1 oracle oinstall 614K Sep 1 2023 mallik_awr.html

-rw-r--r--. 1 oracle oinstall 891 Nov 13 08:26 DEVDB_Sun_Level_0.sh

-rw-r--r--. 1 oracle oinstall 890 Nov 13 08:26 DEVDB_Mon_Sat_Level_1.sh

drwxrwxrwx. 3 root root 4.0K Feb 26 08:52 script

-rw-r--r--. 1 oracle oinstall 643K Mar 5 08:20 awr_DEVDB_05032024.html

[oracle@oraclelab1 ~]$ pwd

/home/oracle

[oracle@oraclelab1 ~]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Mar 25 23:41:53 2024

ADR base = "/u01/app/oracle"

adrci> set homepath diag/rdbms/devdb/DEVDB

adrci> sow homepath

DIA-48415: Syntax error found in string [sow homepath] at column [12]

adrci> show homepath

ADR Homes:

diag/rdbms/devdb/DEVDB

adrci> IPS ADD FILE /home/oracle/script.sql PACKAGE 1

DIA-49424: Directory outside ADR not allowed

adrci>

adrci> exit

[oracle@oraclelab1 ~]$ cd //u01/app/oracle/diag//rdbms/devdb/DEVDB

[oracle@oraclelab1 DEVDB]$ ll

total 28

drwxr-x---. 2 oracle oinstall 21 Mar 25 23:32 alert

drwxr-x---. 2 oracle oinstall 6 Jul 24 2023 cdump

drwxr-x---. 2 oracle oinstall 6 Jul 24 2023 hm

drwxr-x---. 2 oracle oinstall 6 Mar 25 23:32 incident

drwxr-x---. 3 oracle oinstall 19 Mar 25 23:39 incpkg

drwxr-x---. 2 oracle oinstall 34 Sep 30 08:38 ir

drwxr-x---. 2 oracle oinstall 4096 Oct 26 08:37 lck

drwxr-x---. 8 oracle oinstall 4096 Oct 26 07:49 log

drwxr-x---. 2 oracle oinstall 4096 Jul 24 2023 metadata

drwxr-x---. 2 oracle oinstall 6 Jul 24 2023 metadata_dgif

drwxr-x---. 2 oracle oinstall 6 Jul 24 2023 metadata_pv

drwxr-x---. 2 oracle oinstall 87 Mar 25 23:32 stage

drwxr-x---. 2 oracle oinstall 6 Oct 26 08:37 sweep

drwxr-x---. 3 oracle oinstall 581632 Mar 25 23:32 trace

[oracle@oraclelab1 DEVDB]$ cd alert

[oracle@oraclelab1 alert]$ ll

total 40

-rw-r-----. 1 oracle oinstall 38207 Mar 25 22:01 log.xml

[oracle@oraclelab1 alert]$ ls -ltrh

total 40K

-rw-r-----. 1 oracle oinstall 38K Mar 25 22:01 log.xml

[oracle@oraclelab1 alert]$

[oracle@oraclelab1 alert]$ pwd

//u01/app/oracle/diag/rdbms/devdb/DEVDB/alert

[oracle@oraclelab1 alert]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Mar 25 23:43:45 2024

ADR base = "/u01/app/oracle"

adrci> set homepath diag/rdbms/devdb/DEVDB

adrci> show homepath

ADR Homes:

diag/rdbms/devdb/DEVDB

adrci> IPS ADD FILE /u01/app/oracle/diag/rdbms/devdb/DEVDB/alert/log.xml PACKAGE 1

Added file /u01/app/oracle/diag/rdbms/devdb/DEVDB/alert/log.xml to package 1

adrci> IPS GENERATE PACKAGE 1 IN /home/oracle/

Generated package 1 in file /home/oracle/IPSPKG_20240325233912_COM_1.zip, mode complete

adrci> adrci>

adrci>

adrci> IPS unpack file /home/oracle/IPSPKG_20240325233912_COM_1.zip /tmp

Unpacking file /home/oracle/IPSPKG_20240325233912_COM_1.zip into target /tmp

adrci> exit

[oracle@oraclelab1 alert]$ cd /tmp

[oracle@oraclelab1 tmp]$ ls -ltrh

total 368K

drwx------. 2 root root 41 Apr 14 2023 mozilla_root0

drwx------. 3 root root 17 Dec 1 10:33 systemd-private-4309e12c255c45eebbe06424143737d8-rtkit-daemon.service-KgloC1

drwx------. 3 root root 17 Dec 1 10:33 systemd-private-4309e12c255c45eebbe06424143737d8-cups.service-emjPFx

drwx------. 3 root root 17 Dec 1 10:33 systemd-private-4309e12c255c45eebbe06424143737d8-bolt.service-iUBex2

drwx------. 3 root root 17 Dec 1 10:33 systemd-private-4309e12c255c45eebbe06424143737d8-colord.service-8chJlP

drwx------. 2 root root 25 Feb 2 15:11 ssh-levLy1ZfyOAl

drwx------. 3 root root 17 Feb 2 15:11 systemd-private-4309e12c255c45eebbe06424143737d8-fwupd.service-Wm3PL9

drwx------. 2 oracle oinstall 25 Feb 8 07:14 ssh-h8xcYGEJ7JnH

-rw-------. 1 root root 365K Mar 25 09:14 yum_save_tx.2024-03-25.09-14.BqYSVj.yumtx

drwxr-xr-x. 2 oracle oinstall 19 Mar 25 23:45 hsperfdata_oracle

drwxrwxr-x. 3 oracle oinstall 19 Mar 25 23:45 diag

[oracle@oraclelab1 tmp]$ cd diag

[oracle@oraclelab1 diag]$ ll

total 0

drwxr-xr-x. 3 oracle oinstall 19 Mar 25 23:45 rdbms

[oracle@oraclelab1 diag]$ cd rdbms/

[oracle@oraclelab1 rdbms]$ ll

total 0

drwxr-xr-x. 3 oracle oinstall 19 Mar 25 23:45 devdb

[oracle@oraclelab1 rdbms]$ cd devdb/

[oracle@oraclelab1 devdb]$ ll

total 4

drwxr-xr-x. 16 oracle oinstall 4096 Mar 25 23:45 DEVDB

[oracle@oraclelab1 devdb]$ cd DEVDB/

[oracle@oraclelab1 DEVDB]$ ll

total 8

drwxr-xr-x. 2 oracle oinstall 21 Mar 25 23:45 alert

drwxr-xr-x. 2 oracle oinstall 6 Mar 25 23:45 cdump

drwxr-xr-x. 2 oracle oinstall 6 Mar 25 23:45 hm

drwxr-xr-x. 2 oracle oinstall 6 Mar 25 23:45 incident

drwxr-xr-x. 3 oracle oinstall 19 Mar 25 23:45 incpkg

drwxr-xr-x. 2 oracle oinstall 6 Mar 25 23:45 ir

drwxr-xr-x. 2 oracle oinstall 4096 Mar 25 23:45 lck

drwxr-xr-x. 5 oracle oinstall 48 Mar 25 23:45 log

drwxr-xr-x. 2 oracle oinstall 4096 Mar 25 23:45 metadata

drwxr-xr-x. 2 oracle oinstall 6 Mar 25 23:45 metadata_dgif

drwxr-xr-x. 2 oracle oinstall 6 Mar 25 23:45 metadata_pv

drwxr-xr-x. 2 oracle oinstall 6 Mar 25 23:45 stage

drwxr-xr-x. 2 oracle oinstall 6 Mar 25 23:45 sweep

drwxr-xr-x. 2 oracle oinstall 29 Mar 25 23:45 trace

[oracle@oraclelab1 DEVDB]$ pwd

/tmp/diag/rdbms/devdb/DEVDB

[oracle@oraclelab1 DEVDB]$ date

Mon Mar 25 23:46:20 IST 2024

[oracle@oraclelab1 DEVDB]$

[oracle@oraclelab1 DEVDB]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Mar 25 23:46:27 2024

ADR base = "/tmp"

adrci> set homepath diag/rdbms/devdb/DEVDB

adrci>

adrci> ips pack time '2024-03-25 12:00:00' to '2024-03-25 13:00:00'

Generated package 2 in file /tmp/IPSPKG_20240325234807_COM_1.zip, mode complete

adrci> ips show paches

DIA-49406: Undefined configuration parameter specified [paches]

adrci> ips show package

PACKAGE_ID 1

PACKAGE_NAME IPSPKG_20240325233912

PACKAGE_DESCRIPTION

DRIVING_PROBLEM N/A

DRIVING_PROBLEM_KEY N/A

DRIVING_INCIDENT N/A

DRIVING_INCIDENT_TIME N/A

STATUS New (0)

CORRELATION_LEVEL Typical (2)

PROBLEMS 0 main problems, 0 correlated problems

INCIDENTS 0 main incidents, 0 correlated incidents

INCLUDED_FILES 24

PACKAGE_ID 2

PACKAGE_NAME IPSPKG_20240325234807

PACKAGE_DESCRIPTION

DRIVING_PROBLEM N/A

DRIVING_PROBLEM_KEY N/A

DRIVING_INCIDENT N/A

DRIVING_INCIDENT_TIME N/A

STATUS Generated (4)

CORRELATION_LEVEL Typical (2)

PROBLEMS 0 main problems, 0 correlated problems

INCIDENTS 0 main incidents, 0 correlated incidents

INCLUDED_FILES 28

adrci> ips show package 1 details

DIA-48415: Syntax error found in string [ips show package 1 details] at column [26]

adrci> ips show package 1 detail

DETAILS FOR PACKAGE 1:

PACKAGE_ID 1

PACKAGE_NAME IPSPKG_20240325233912

PACKAGE_DESCRIPTION

DRIVING_PROBLEM N/A

DRIVING_PROBLEM_KEY N/A

DRIVING_INCIDENT N/A

DRIVING_INCIDENT_TIME N/A

STATUS New (0)

CORRELATION_LEVEL Typical (2)

PROBLEMS 0 main problems, 0 correlated problems

INCIDENTS 0 main incidents, 0 correlated incidents

INCLUDED_FILES 24

SEQUENCES Last 0, last full 0, last base 0

UNPACKED TRUE

CREATE_TIME 2024-03-25 23:39:12.913644 +05:30

UPDATE_TIME N/A

BEGIN_TIME N/A

END_TIME N/A

FLAGS 0

HISTORY FOR PACKAGE 1:

SEQUENCE 1

BASE_SEQUENCE 1

MODE Complete (0)

STATUS Finalizing (1)

FILENAME

ARCHIVE_TIME 2024-03-25 23:45:00.626797 +05:30

UPLOAD_TIME N/A

UNPACK_TIME 2024-03-25 23:45:57.408539 +05:30

FORCE FALSE

GENERATE_FLAGS 0

UNPACK_FLAGS 0

MAIN INCIDENTS FOR PACKAGE 1:

CORRELATED INCIDENTS FOR PACKAGE 1:

FILES FOR PACKAGE 1:

FILE_ID 1

FILE_LOCATION <ADR_HOME>/alert

FILE_NAME log.xml

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 2

FILE_LOCATION <ADR_HOME>/trace

FILE_NAME alert_DEVDB.log

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 3

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME IPS_CONFIGURATION.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 4

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME IPS_PACKAGE.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 5

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME IPS_PACKAGE_INCIDENT.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 6

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME IPS_PACKAGE_FILE.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 7

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME IPS_PACKAGE_HISTORY.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 8

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME IPS_FILE_METADATA.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 9

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME IPS_FILE_COPY_LOG.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 10

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME DDE_USER_ACTION_DEF.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 11

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME DDE_USER_ACTION_PARAMETER_DEF.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 12

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME DDE_USER_ACTION.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 13

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME DDE_USER_ACTION_PARAMETER.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 14

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME DDE_USER_INCIDENT_TYPE.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 15

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME DDE_USER_INCIDENT_ACTION_MAP.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 16

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME INCIDENT.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 17

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME INCCKEY.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 18

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME INCIDENT_FILE.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 19

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME PROBLEM.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 20

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME HM_RUN.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 21

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export

FILE_NAME EM_USER_ACTIVITY.dmp

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 22

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1

FILE_NAME config.xml

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 23

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/opatch

FILE_NAME opatch.log

LAST_SEQUENCE 0

EXCLUDE Included

FILE_ID 24

FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/opatch

FILE_NAME opatch.xml

LAST_SEQUENCE 0

EXCLUDE Included

adrci> ips delete package 1

Deleted package 1

adrci> ips show package

PACKAGE_ID 2

PACKAGE_NAME IPSPKG_20240325234807

PACKAGE_DESCRIPTION

DRIVING_PROBLEM N/A

DRIVING_PROBLEM_KEY N/A

DRIVING_INCIDENT N/A

DRIVING_INCIDENT_TIME N/A

STATUS Generated (4)

CORRELATION_LEVEL Typical (2)

PROBLEMS 0 main problems, 0 correlated problems

INCIDENTS 0 main incidents, 0 correlated incidents

INCLUDED_FILES 28

adrci> ips show package 2 detail;s

DETAILS FOR PACKAGE 2:

PACKAGE_ID 2

PACKAGE_NAME IPSPKG_20240325234807

PACKAGE_DESCRIPTION

DRIVING_PROBLEM N/A

DRIVING_PROBLEM_KEY N/A

DRIVING_INCIDENT N/A

DRIVING_INCIDENT_TIME N/A

STATUS Generated (4)

CORRELATION_LEVEL Typical (2)

PROBLEMS 0 main problems, 0 correlated problems

INCIDENTS 0 main incidents, 0 correlated incidents

INCLUDED_FILES 28

SEQUENCES Last 1, last full 1, last base 0

UNPACKED FALSE

CREATE_TIME 2024-03-25 23:48:07.755342 +05:30

UPDATE_TIME N/A

BEGIN_TIME 2024-03-25 12:00:00.000000 +05:30

END_TIME 2024-03-25 13:00:00.000000 +05:30

FLAGS 0

HISTORY FOR PACKAGE 2:

SEQUENCE 1

BASE_SEQUENCE 1

MODE Complete (0)

STATUS Generated (4)

FILENAME /tmp/IPSPKG_20240325234807_COM_1.zip

ARCHIVE_TIME 2024-03-25 23:48:15.265451 +05:30

UPLOAD_TIME N/A

UNPACK_TIME N/A

FORCE FALSE

GENERATE_FLAGS 0

UNPACK_FLAGS 0

MAIN INCIDENTS FOR PACKAGE 2:

CORRELATED INCIDENTS FOR PACKAGE 2:

FILES FOR PACKAGE 2:

FILE_ID 1

FILE_LOCATION <ADR_HOME>/alert

FILE_NAME log.xml

LAST_SEQUENCE 1

EXCLUDE Included

FILE_ID 2

FILE_LOCATION <ADR_HOME>/trace

FILE_NAME alert_DEVDB.log

LAST_SEQUENCE 1

EXCLUDE Included

FILE_ID 25