ORA-19912: cannot recover to target incarnation

RMAN> reset database to incarnation 3;



RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/23/2021 10:55:21
ORA-00283: recovery session canceled due to errors
ORA-19912: cannot recover to target incarnation 3



1.
RMAN>
restore controlfile from '/u01/app/oracle/RmanBackup/ORCL_20210123_9_1_CONTROL';
RMAN>

Starting restore at 23-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCL/controlfile/control.crtl
*.db_block_size=8192
*.db_create_file_dest=
output file name=/u01/app/oracle/oradata'
Finished restore at 23-JAN-21


2.
RMAN> alter database mount ;


check incarnation !!!!!!!!!!!

export NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';    !!!!!!!!!!!!!!!!!!

rman target /
list incarnation;

RMAN> list incarnation;


Recovery Manager complete.
[oracle@ORA-19C-DR controlfile]$ export NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';
[oracle@ORA-19C-DR controlfile]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Jan 23 17:27:14 2021
Version 18.12.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1588126891, not open)





RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1588126891       PARENT  1          26-01-2017 13:52:29
2       2       ORCL     1588126891       CURRENT 1408558    28-12-2020 12:35:58
5       5       ORCL     1588126891       ORPHAN  5571101    23-01-2021 11:53:40
4       4       ORCL     1588126891       ORPHAN  5571101    23-01-2021 11:06:43
3       3       ORCL     1588126891       ORPHAN  5571490    23-01-2021 10:43:24



No idea why incarnation messed up !!!!!!!!!!


I did restore database . 

RMAN> RESTORE DATABASE;

Starting restore at 23-01-2021 17:28:41
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK

skipping datafile 7; already restored to file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_hymn895b_.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymnby5w_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymnby55_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymnby62_.dbf
skipping datafile 9; already restored to file /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_system_hymnsy7h_.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_sysaux_hymnsy7z_.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_undotbs1_hymnsy80_.dbf
skipping datafile 12; already restored to file /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_users_hymnv6o1_.dbf
skipping datafile 13; already restored to file /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_system_hymz1hkc_.dbf
skipping datafile 14; already restored to file /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymz1hko_.dbf
skipping datafile 15; already restored to file /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymz1hko_.dbf
skipping datafile 25; already restored to file /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_users_hyoxo51g_.dbf
skipping datafile 16; already restored to file /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_system_hymzdm1q_.dbf
skipping datafile 17; already restored to file /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymzdm1r_.dbf
skipping datafile 18; already restored to file /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymzdm1r_.dbf
skipping datafile 27; already restored to file /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_users_hypg2fww_.dbf
skipping datafile 22; already restored to file /u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_system_hymnby5w_.dbf
skipping datafile 23; already restored to file /u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_sysaux_hymnby55_.dbf
skipping datafile 24; already restored to file /u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_undotbs1_hymnby62_.dbf
skipping datafile 26; already restored to file /u01/app/oracle/oradata/ORCL/VIHAAN2/vihaan2_users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymn44tg_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymn6t87_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymn87yy_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20210123_1_1_FULL
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_1_1_FULL tag=ORCLEE_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 23-01-2021 17:29:57



I have checked the status of all the datafiles i.e FHRBA_SEQ=73 ... so we are good ...


set linesize 300 pagesize 300
col FILE_NAME for a80
select fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name,substr(HXFNM,1,70) file_name from x$kcvfh;

FHDBN          FHDBI      HXFIL      FHSTA FHSCN                FHAFS                 FHRBA_SEQ TBS_NAME                       FILE_NAME
--------- ---------- ---------- ---------- -------------------- -------------------- ---------- ------------------------------ --------------------------------------------------------------------------------
ORCL      1588126891          1       8192 5571100              0                            73 SYSTEM                         /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymn44tg_.dbf
ORCL      1588126891          3          0 5571100              0                            73 SYSAUX                         /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymn6t87_.dbf
ORCL      1588126891          4          0 5571100              0                            73 UNDOTBS1                       /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymn87yy_.dbf
ORCL      1588126891          5       8192 5557030              0                            73 SYSTEM                         /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymnby5w_.dbf
ORCL      1588126891          6          0 5557030              0                            73 SYSAUX                         /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymnby55_.dbf
ORCL      1588126891          7          0 5571100              0                            73 USERS                          /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_hymn895b_.dbf
ORCL      1588126891          8          0 5557030              0                            73 UNDOTBS1                       /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymnby62_.dbf
ORCL      1588126891          9       8192 5568989              0                            73 SYSTEM                         /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile
ORCL      1588126891         10          0 5568989              0                            73 SYSAUX                         /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile
ORCL      1588126891         11          0 5568989              0                            73 UNDOTBS1                       /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile
ORCL      1588126891         12          0 5568989              0                            73 USERS                          /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile
ORCL      1588126891         13       8192 5568990              0                            73 SYSTEM                         /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile
ORCL      1588126891         14          0 5568990              0                            73 SYSAUX                         /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile
ORCL      1588126891         15          0 5568990              0                            73 UNDOTBS1                       /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile
ORCL      1588126891         16       8192 5569012              0                            73 SYSTEM                         /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile
ORCL      1588126891         17          0 5569012              0                            73 SYSAUX                         /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile
ORCL      1588126891         18          0 5569012              0                            73 UNDOTBS1                       /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile
ORCL      1588126891         22       8192 5569017              0                            73 SYSTEM                         /u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_system_hymnby5w_.d
ORCL      1588126891         23          0 5569017              0                            73 SYSAUX                         /u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_sysaux_hymnby55_.d
ORCL      1588126891         24          0 5569017              0                            73 UNDOTBS1                       /u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_undotbs1_hymnby62_
ORCL      1588126891         25          0 5568990              0                            73 USERS                          /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile
ORCL      1588126891         26          0 5569017              0                            73 VIHAAN2_USERS                  /u01/app/oracle/oradata/ORCL/VIHAAN2/vihaan2_users01.dbf
ORCL      1588126891         27          0 5569012              0                            73 USERS                          /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile

23 rows selected.


SQL>  alter database backup controlfile to trace;

Database altered.


SQL> shutdown immediate ;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


SQL> startup nomount ;  !!!!!!!!!!!!!!!!!!!!!!!!!!
ORACLE instance started.

Total System Global Area 2080372000 bytes
Fixed Size                  8897824 bytes
Variable Size            1191182336 bytes
Database Buffers          872415232 bytes
Redo Buffers                7876608 bytes




check the trace file control file statement 

Create control file !!!!!!

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_hymn9g57_.log',
    '/u01/app/oracle/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_1_hymn9h56_.log'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_hymn9g9j_.log',
    '/u01/app/oracle/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_2_hymn9hfx_.log'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_hymnb0fq_.log',
    '/u01/app/oracle/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_3_hymnb157_.log'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymn44tg_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymn6t87_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymn87yy_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymnby5w_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymnby55_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_hymn895b_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymnby62_.dbf',
  '/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_system_hymnsy7h_.dbf',
  '/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_sysaux_hymnsy7z_.dbf',
  '/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_undotbs1_hymnsy80_.dbf',
  '/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_users_hymnv6o1_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_system_hymz1hkc_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymz1hko_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymz1hko_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_system_hymzdm1q_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymzdm1r_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymzdm1r_.dbf',
  '/u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_system_hymnby5w_.dbf',
  '/u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_sysaux_hymnby55_.dbf',
  '/u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_undotbs1_hymnby62_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_users_hyoxo51g_.dbf',
  '/u01/app/oracle/oradata/ORCL/VIHAAN2/vihaan2_users01.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_users_hypg2fww_.dbf'
CHARACTER SET AL32UTF8
;


SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
  3    4    5      MAXINSTANCES 8
    MAXLOGHISTORY 292
  6    7  LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_hymn9g57_.log',
    '/u01/app/oracle/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_1_hymn9h56_.log'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_hymn9g9j_.log',
    '/u01/app/oracle/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_2_hymn9hfx_.log'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_hymnb0fq_.log',
    '/u01/app/oracle/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_3_hymnb157_.log'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymn44tg_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymn6t87_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymn87yy_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymnby5w_.dbf',
  8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26    '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymnby55_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_hymn895b_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymnby62_.dbf',
  '/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_system_hymnsy7h_.dbf',
  '/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_sysaux_hymnsy7z_.dbf',
  '/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_undotbs1_hymnsy80_.dbf',
  '/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_users_hymnv6o1_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_system_hymz1hkc_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymz1hko_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymz1hko_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_system_hymzdm1q_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymzdm1r_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymzdm1r_.dbf',
  '/u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_system_hymnby5w_.dbf',
  '/u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_sysaux_hymnby55_.dbf',
  '/u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_undotbs1_hymnby62_.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_users_hyoxo51g_.dbf',
  '/u01/app/oracle/oradata/ORCL/VIHAAN2/vihaan2_users01.dbf',
  '/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_users_hypg2fww_.dbf'
CHARACTER SET AL32UTF8
; 27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46

Control file created.


SQL> alter database open resetlogs ;

Database altered.




RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1588126891       PARENT  1408558    28-12-2020 12:35:58
2       2       ORCL     1588126891       CURRENT 5571101    23-01-2021 17:39:25

!!!!!!!!!!!!!!
You must perform a level 0 backup immediately, because older backups will not work with this new database incarnations.

!!!!!!!!!!!!!!!

TAke new backup !!!!!!!

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1       Full    18.23M     DISK        00:00:01     23-01-2021 17:40:02
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20210123T174001
        Piece Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_01_23/o1_mf_n_1062610801_j0rqvl2q_.bkp
  Control File Included: Ckp SCN: 5571573      Ckp time: 23-01-2021 17:40:01


delete backup tag = 'TAG20210123T174001';


RMAN> list backup;

specification does not match any backup in the repository




New backup !!!!!!!!!!


RMAN>

 RUN
 {
 configure controlfile autobackup on;
set command id to 'ORCLEEBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCLEE_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup tag ORCLEE_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
backup tag ORCLEE_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
release channel c1;
}


 RUN
 {
 configure controlfile autobackup on;
set command id to 'ORCLEEBackupFull';
RMAN> 2> 3> ALLOCATE CHANNEL c1 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCLEE_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup tag ORCLEE_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
backup tag ORCLEE_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
release channel c1;
}4> 5> 6> 7> 8> 9> 10> 11> 12>

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

executing command: SET COMMAND ID

released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=59 device type=DISK

Starting backup at 23-01-2021 17:49:45
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymn44tg_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymn87yy_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymn6t87_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_hymn895b_.dbf
channel c1: starting piece 1 at 23-01-2021 17:49:45
channel c1: finished piece 1 at 23-01-2021 17:50:50
piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_2_1_FULL tag=ORCLEE_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:01:05
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymzdm1r_.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_system_hymzdm1q_.dbf
input datafile file number=00018 name=/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymzdm1r_.dbf
input datafile file number=00027 name=/u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_users_hypg2fww_.dbf
channel c1: starting piece 1 at 23-01-2021 17:50:50
channel c1: finished piece 1 at 23-01-2021 17:51:35
piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_3_1_FULL tag=ORCLEE_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00014 name=/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymz1hko_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymz1hko_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_system_hymz1hkc_.dbf
input datafile file number=00025 name=/u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_users_hyoxo51g_.dbf
channel c1: starting piece 1 at 23-01-2021 17:51:35
channel c1: finished piece 1 at 23-01-2021 17:52:20
piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_4_1_FULL tag=ORCLEE_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00022 name=/u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_system_hymnby5w_.dbf
input datafile file number=00023 name=/u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_sysaux_hymnby55_.dbf
input datafile file number=00024 name=/u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_undotbs1_hymnby62_.dbf
input datafile file number=00026 name=/u01/app/oracle/oradata/ORCL/VIHAAN2/vihaan2_users01.dbf
channel c1: starting piece 1 at 23-01-2021 17:52:21
channel c1: finished piece 1 at 23-01-2021 17:53:06
piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_5_1_FULL tag=ORCLEE_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymnby55_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymnby5w_.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymnby62_.dbf
channel c1: starting piece 1 at 23-01-2021 17:53:06
channel c1: finished piece 1 at 23-01-2021 17:53:51
piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_6_1_FULL tag=ORCLEE_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_sysaux_hymnsy7z_.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_system_hymnsy7h_.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_undotbs1_hymnsy80_.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_users_hymnv6o1_.dbf
channel c1: starting piece 1 at 23-01-2021 17:53:51
channel c1: finished piece 1 at 23-01-2021 17:54:36
piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_7_1_FULL tag=ORCLEE_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
Finished backup at 23-01-2021 17:54:36

Starting Control File Autobackup at 23-01-2021 17:54:36
piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_01_23/o1_mf_n_1062611676_j0rrpwys_.bkp comment=NONE
Finished Control File Autobackup at 23-01-2021 17:54:37

sql statement: alter system archive log current

sql statement: alter system archive log current

Starting backup at 23-01-2021 17:54:38
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=1062611678
input archived log thread=1 sequence=2 RECID=2 STAMP=1062611678
input archived log thread=1 sequence=3 RECID=3 STAMP=1062611680
channel c1: starting piece 1 at 23-01-2021 17:54:40
channel c1: finished piece 1 at 23-01-2021 17:54:41
piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_9_1_ARCHIVE tag=ORCLEE_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/Archive/Log1_1_1062610765.Arc RECID=1 STAMP=1062611678
archived log file name=/u01/app/oracle/Archive/Log2_1_1062610765.Arc RECID=2 STAMP=1062611678
archived log file name=/u01/app/oracle/Archive/Log3_1_1062610765.Arc RECID=3 STAMP=1062611680
Finished backup at 23-01-2021 17:54:41

Starting backup at 23-01-2021 17:54:41
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 23-01-2021 17:54:43
channel c1: finished piece 1 at 23-01-2021 17:54:44
piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_10_1_CONTROL tag=ORCLEE_CONTROL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-01-2021 17:54:44

Starting Control File Autobackup at 23-01-2021 17:54:44
piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_01_23/o1_mf_n_1062611684_j0rrq4h7_.bkp comment=NONE
Finished Control File Autobackup at 23-01-2021 17:54:45

released channel: c1



RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1588126891       PARENT  1408558    28-12-2020 12:35:58
2       2       ORCL     1588126891       CURRENT 5571101    23-01-2021 17:39:25



delete backup tag ORCLEE_FULL
2> ;




RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
2       B  F  A DISK        23-01-2021 17:50:44 1       1       YES        ORCLEE_FULL
3       B  F  A DISK        23-01-2021 17:51:29 1       1       YES        ORCLEE_FULL
4       B  F  A DISK        23-01-2021 17:52:15 1       1       YES        ORCLEE_FULL
5       B  F  A DISK        23-01-2021 17:53:05 1       1       YES        ORCLEE_FULL
6       B  F  A DISK        23-01-2021 17:53:47 1       1       YES        ORCLEE_FULL
7       B  F  A DISK        23-01-2021 17:54:29 1       1       YES        ORCLEE_FULL
8       B  F  A DISK        23-01-2021 17:54:37 1       1       NO         TAG20210123T175436
9       B  A  A DISK        23-01-2021 17:54:40 1       1       NO         ORCLEE_ARCHIVE
10      B  F  A DISK        23-01-2021 17:54:43 1       1       NO         ORCLEE_CONTROL
11      B  F  A DISK        23-01-2021 17:54:44 1       1       NO         TAG20210123T175444





RMAN> delete backup tag ORCLEE_FULL
2> ;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
2       2       1   1   AVAILABLE   DISK        /u01/app/oracle/RmanBackup/ORCL_20210123_2_1_FULL
3       3       1   1   AVAILABLE   DISK        /u01/app/oracle/RmanBackup/ORCL_20210123_3_1_FULL
4       4       1   1   AVAILABLE   DISK        /u01/app/oracle/RmanBackup/ORCL_20210123_4_1_FULL
5       5       1   1   AVAILABLE   DISK        /u01/app/oracle/RmanBackup/ORCL_20210123_5_1_FULL
6       6       1   1   AVAILABLE   DISK        /u01/app/oracle/RmanBackup/ORCL_20210123_6_1_FULL
7       7       1   1   AVAILABLE   DISK        /u01/app/oracle/RmanBackup/ORCL_20210123_7_1_FULL

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_2_1_FULL RECID=2 STAMP=1062611385
deleted backup piece
backup piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_3_1_FULL RECID=3 STAMP=1062611450
deleted backup piece
backup piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_4_1_FULL RECID=4 STAMP=1062611495
deleted backup piece
backup piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_5_1_FULL RECID=5 STAMP=1062611541
deleted backup piece
backup piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_6_1_FULL RECID=6 STAMP=1062611586
deleted backup piece
backup piece handle=/u01/app/oracle/RmanBackup/ORCL_20210123_7_1_FULL RECID=7 STAMP=1062611631
Deleted 6 objects


RMAN> list backup summary;



List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
8       B  F  A DISK        23-01-2021 17:54:37 1       1       NO         TAG20210123T175436
9       B  A  A DISK        23-01-2021 17:54:40 1       1       NO         ORCLEE_ARCHIVE
10      B  F  A DISK        23-01-2021 17:54:43 1       1       NO         ORCLEE_CONTROL
11      B  F  A DISK        23-01-2021 17:54:44 1       1       NO         TAG20210123T175444






RMAN> REPORT SCHEMA ;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1140     SYSTEM               YES     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymn44tg_.dbf
3    640      SYSAUX               NO      /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymn6t87_.dbf
4    880      UNDOTBS1             YES     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymn87yy_.dbf
5    400      PDB$SEED:SYSTEM      NO      /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hymnby5w_.dbf
6    420      PDB$SEED:SYSAUX      NO      /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hymnby55_.dbf
7    5        USERS                NO      /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_hymn895b_.dbf
8    350      PDB$SEED:UNDOTBS1    NO      /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_hymnby62_.dbf
9    410      ANUJ:SYSTEM          NO      /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_system_hymnsy7h_.dbf
10   430      ANUJ:SYSAUX          NO      /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_sysaux_hymnsy7z_.dbf
11   325      ANUJ:UNDOTBS1        NO      /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_undotbs1_hymnsy80_.dbf
12   5        ANUJ:USERS           NO      /u01/app/oracle/oradata/ORCL/B786A3874A265B38E0538101A8C01F55/datafile/o1_mf_users_hymnv6o1_.dbf
13   410      PDB2:SYSTEM          NO      /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_system_hymz1hkc_.dbf
14   440      PDB2:SYSAUX          NO      /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymz1hko_.dbf
15   440      PDB2:UNDOTBS1        NO      /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymz1hko_.dbf
16   410      VIHAAN:SYSTEM        NO      /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_system_hymzdm1q_.dbf
17   430      VIHAAN:SYSAUX        NO      /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_sysaux_hymzdm1r_.dbf
18   365      VIHAAN:UNDOTBS1      NO      /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_undotbs1_hymzdm1r_.dbf
22   450      VIHAAN2:SYSTEM       NO      /u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_system_hymnby5w_.dbf
23   390      VIHAAN2:SYSAUX       NO      /u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_sysaux_hymnby55_.dbf
24   380      VIHAAN2:UNDOTBS1     NO      /u01/app/oracle/oradata/ORCL/VIHAAN2/datafile/o1_mf_undotbs1_hymnby62_.dbf
25   1        PDB2:USERS           NO      /u01/app/oracle/oradata/ORCL/B78913A38EB71376E0538101A8C0C4D4/datafile/o1_mf_users_hyoxo51g_.dbf
26   50       VIHAAN2:VIHAAN2_USERS NO      /u01/app/oracle/oradata/ORCL/VIHAAN2/vihaan2_users01.dbf
27   100      VIHAAN:USERS         NO      /u01/app/oracle/oradata/ORCL/B78913A38EB81376E0538101A8C0C4D4/datafile/o1_mf_users_hypg2fww_.dbf



SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ANUJ                           MOUNTED
         4 PDB2                           MOUNTED
         5 VIHAAN                         MOUNTED
         6 VIHAAN2                        MOUNTED
SQL>

Oracle Tracing file info .... 

v$diag_info

-- alert log info 
set linesize 400
col ALERT_LOG for a100
col PATH for a100
select di.value  ||'/'|| 'alert_' || i.instance_name || '.log' ALERT_LOG from v$diag_info di, v$instance i where di.name = 'Diag Trace';






define Days=1
set pages 999 lines 100
SELECT to_char(originating_timestamp,'DD-MM-RR HH24:MI:SS') "Time", message_text
FROM X$DBGALERTEXT
WHERE originating_timestamp > systimestamp - &Days
AND
regexp_like(message_text, '(TNS-|ORA-|error)');





--Last Hour
SELECT inst_id, originating_timestamp, message_text
FROM TABLE(gv$(cursor(select inst_id, originating_timestamp, message_text
from v$diag_alert_ext
WHERE originating_timestamp > (sysdate - 1/24)  --- 1hr
AND message_text LIKE '%ORA-%')))
ORDER BY inst_id, originating_timestamp;
	

 All the trace files go into the Automatic Diagnostic Repository (ADR) by default

set linesize 300 pagesize 300
col name    form a25
col value   form a80 wrap
select * from v$diag_info
order by name
/

--- alert log file
set linesize 300 pagesize 300
col name    form a25
col value   form a80 wrap
select * from v$diag_info
where 1=1
and NAME='Diag Trace'
order by name
/

-- trace file info 
col adr_home for a80
col trace_filename for a50
select adr_home,trace_filename,change_time  from v$diag_trace_file  order by change_time desc
fetch first 5 rows only ;



--- 
set linesize 300 pagesize 300 
col filename for a100
col component_id for a30
select  distinct component_id,filename
from v$diag_alert_ext
order by 1,2;


-- Ora- error info 

set linesize 300 pagesize 300
col message_text for a100 
col sdate for a30
select
to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS') sdate  ,message_text from v$diag_alert_ext
where message_text like '%ORA-%'
and originating_timestamp > sysdate- 2
order by originating_timestamp;

set linesize 300 pagesize 300 
col message_text for a80 wrap 
col detailed_location  for a30 
col problem_key for a20
col originating_timestamp for a45
select originating_timestamp,detailed_location,message_level,message_text,problem_key from v$diag_alert_ext 
where message_level=1 
--AND MESSAGE_TEXT LIKE '%ORA-00600%' 
ORDER BY ORIGINATING_TIMESTAMP DESC;

set pagesize 200  linesize 300
column adr_home format a40
column message_text format a80
col originating_timestamp for a15
select call_monitor,
       adr_home,
       inst_id,
       to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS') originating_timestamp,
       message_text
 from (select adr_home,
              inst_id,
              ORIGINATING_TIMESTAMP,
              message_text,
              dense_rank() over (PARTITION BY adr_home order by ORIGINATING_TIMESTAMP DESC NULLS LAST) as call_monitor
         from v$diag_alert_ext)
where ORIGINATING_TIMESTAMP > systimestamp - INTERVAL '1' hour
order by ORIGINATING_TIMESTAMP;

 set linesize 500

 define beg=111

define end=222

 

 col originating_timestamp for a37

col message_text          for a50 wrap

col PROBLEM_KEY for a20 

col DETAILED_LOCATION for a20

col COMPONENT_ID for a20

col CLIENT_ID     for a10                                                       

col MODULE_ID  for a10 

select

   to_char(originating_timestamp,'yyyy-mm-dd hh24:mi:ssxff TZR') originating_timestamp

  ,message_text

  ,inst_id

  ,component_id

 -- ,host_id

 -- ,host_address

  ,message_type

  ,message_level

  ,message_group

  ,client_id

  ,module_id

  ,process_id

  ,user_id

  ,detailed_location

  ,problem_key

from --sys.x$dbgalertext

     v$diag_alert_ext

where 1=1

   -- and originating_timestamp between &beg and &end

  -- and originating_timestamp > sysdate -1

    and  originating_timestamp  >sysdate - interval '2400' minute

and ( problem_key is not null

      or message_text like '%ORA-%'

      or message_text like '%WARNING%'

      or message_text like '%FATAL%'

    )

/

select to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),

       MESSAGE_TEXT

  from V$DIAG_ALERT_EXT

 WHERE ORIGINATING_TIMESTAMP between sysdate - interval '10' hour and sysdate

   and trim(COMPONENT_ID)='tnslsnr';

set pagesize 400 linesize 200  long 99999

column ADR_HOME format a40

column "mylog.xml" format a180

select

   xmlelement(noentityescaping "msg",

           xmlattributes( alt.originating_timestamp as "time",

                          alt.organization_id       as "org_id",

                          alt.component_id          as "comp_id",

                          alt.message_id            as "msg_id",

                          alt.message_type          as "type",

                          alt.message_group         as "group",

                          alt.message_level         as "level",

                          alt.host_id               as "host_id",

                          alt.host_address          as "host_addr",

                          alt.process_id            as "pid_id",

                          alt.version               as "version"

                        ),

                xmlelement("txt", message_text)                       

            ) as "mylog.xml"

from 

   x$dbgalertext alt;

where

   rownum < = 30;

====

col TRACE_FILE for a70

col RAC_SID for a15

select

  i.instance_number inst_id

, s.sid

, s.serial#

, p.spid pid

, s.sid||','||s.serial#||',@'||i.instance_number rac_sid

, (select value from v$diag_info where name='Default Trace File') trace_file

from 

  v$session s

, v$instance i

, v$process p

where

  s.sid=(select sid from v$mystat where rownum=1)

  and s.paddr=p.addr

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

set linesize 100 pagesize 300

column trace_file format a80

SELECT s.sid,

       s.serial#,

       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    

       '_ora_' || p.spid || '.trc' AS trace_file

FROM   v$session s,

       v$process p,

       v$parameter pa

WHERE  pa.name = 'user_dump_dest'

AND    s.paddr = p.addr

AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

set linesize 300 pagesize 300

col MESSAGE_TEXT for a100

select substr(MESSAGE_TEXT, 1, 150) message_text,to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD') err_timestamp,  count(*) cnt

from X$DBGALERTEXT

where (upper(MESSAGE_TEXT) like '%ORA-%' 

or upper(MESSAGE_TEXT) like '%ERROR%') 

and cast(ORIGINATING_TIMESTAMP as DATE) > sysdate - 7                     

group by substr(MESSAGE_TEXT, 1, 150), to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD')

order by to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD');

set linesize 700 pagesize 500

define beg=111

define end=222

col originating_timestamp for a37

col message_text          for a50 wrap

col PROBLEM_KEY for a20 

col DETAILED_LOCATION for a20

col COMPONENT_ID for a20

select

   to_char(originating_timestamp,'yyyy-mm-dd hh24:mi:ssxff TZR') originating_timestamp

  ,message_text

  ,inst_id

  ,component_id

  ,host_id

  ,host_address

  ,message_type

  ,message_level

  ,message_group

  ,client_id

  ,module_id

  ,process_id

  ,user_id

  ,detailed_location

  ,problem_key

from --sys.x$dbgalertext

     v$diag_alert_ext

where 1=1

   -- and originating_timestamp between &beg and &end

   and originating_timestamp > sysdate -1

and ( problem_key is not null

      or message_text like '%ORA-%'

      or message_text like '%WARNING%'

      or message_text like '%FATAL%'

    )

/

==========

 select TRACE_FILENAME

   from   V$DIAG_TRACE_FILE

where 1=1

and CHANGE_TIME >sysdate -1/24

   order by 1;

select PAYLOAD

   from   V$DIAG_TRACE_FILE_CONTENTS

  where  TRACE_FILENAME = 'rdc_ora_98948.trc'

  order by LINE_NUMBER;

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

from 

https://www.pro-oracle.com/2021/09/oracle-database-19c-modify-adr-purging.html

 Modify the ADR Purging Policies

set variable !!!

 export ORACLE_HOME=/u01/app/19.0.0/grid

 export ORACLE_SID=+ASM2

# Set desired values for the ADR Purging Policies in days

SHORTP_POLICY_DAYS=30

LONGP_POLICY_DAYS=30

SHORTP_POLICY_HOURS=$(($SHORTP_POLICY_DAYS * 24))

LONGP_POLICY_HOURS=$(($LONGP_POLICY_DAYS * 24))

for i in `adrci exec="show homes;" | tail -n +2`; do

   if adrci exec="set home $i; show control;" | grep -q "1 row fetched"; then

      adrci exec="set home $i; set control \(SHORTP_POLICY = $SHORTP_POLICY_HOURS, LONGP_POLICY = $LONGP_POLICY_HOURS\);"

      adrci exec="set home $i; show control;" |  awk '

         FNR==2 {print $0}

         FNR==3 {print $0}

         FNR==4 {printf "%20s %20s\n", $2, $3}

         FNR==5 {printf "%20s %20s\n", $2, $3}

         FNR==6 {printf "%20s %20s\n", $2, $3}

         END    {print "\n"}'

   fi

done

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

[grid@rac02 ~]$ vi diagpolicy.sh

[grid@rac02 ~]$ chmod 777 diagpolicy.sh

[grid@ibrac02 ~]$ ./diagpolicy.sh

ADR Home = /u01/app/grid/diag/rdbms/_mgmtdb/-MGMTDB:

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

       SHORTP_POLICY         LONGP_POLICY

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

                 720                  720

ADR Home = /u01/app/grid/diag/asm/+asm/+ASM2:

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

       SHORTP_POLICY         LONGP_POLICY

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

                 720                  720

ADR Home = /u01/app/grid/diag/crs/ibrac02/crs:

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

       SHORTP_POLICY         LONGP_POLICY

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

                 720                  720

ADR Home = /u01/app/grid/diag/asmtool/user_grid/host_2670455502_107:

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

       SHORTP_POLICY         LONGP_POLICY

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

                 720                  720

ADR Home = /u01/app/grid/diag/asmtool/user_root/host_2670455502_107:

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

       SHORTP_POLICY         LONGP_POLICY

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

                 720                  720

ADR Home = /u01/app/grid/diag/asmtool/user_root/host_2670455502_110:

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

       SHORTP_POLICY         LONGP_POLICY

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

                 720                  720

ADR Home = /u01/app/grid/diag/apx/+apx/+APX2:

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

       SHORTP_POLICY         LONGP_POLICY

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

                 720                  720

ADR Home = /u01/app/grid/diag/kfod/ibrac02/kfod:

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

       SHORTP_POLICY         LONGP_POLICY

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

                 720                  720

adrci> set home diag/crs/rac02/crs

adrci> show control

ADR Home = /u01/app/grid/diag/crs/ibrac02/crs:

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

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

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

3635384382           720                  720                  2022-11-18 05:42:26.304673 -05:00        2022-11-17 07:38:41.279001 -05:00        2022-11-18 05:34:43.772602 -05:00        1                    2                    110                  1                    2018-02-02 16:45:19.253492 -05:00        18446744073709551615 0                    0                    95

1 row fetched

Oracle version info via oraversion

oraversion - Oracle Version tool 

Oracle version info via oraversion

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0


[oracle@ORA-18C]$ oraversion -help

This program prints release version information.
These are its possible arguments:
-compositeVersion: Print the full version number: a.b.c.d.e.
-baseVersion: Print the base version number: a.0.0.0.0.
-majorVersion: Print the major version number: a.
-buildStamp: Print the date/time associated with the build.
-buildDescription: Print a description of the build.
-help: Print this message.



cd $ORACLE_HOME/bin/


oraversion -compositeVersion
oraversion -baseVersion
oraversion -majorVersion
oraversion -buildStamp
oraversion -buildDescription


[oracle@ORA-18C]$ oraversion -compositeVersion
18.3.0.0.0

[oracle@ORA-18C]$ oraversion -baseVersion
18.0.0.0.0

[oracle@ORA-18C]$ oraversion -majorVersion
18

[oracle@ORA-18C]$ oraversion -buildStamp
180628094320

[oracle@ORA-18C]$ oraversion -buildDescription
Release_Update


Run this command on unix prompt ..

cd $ORACLE_HOME/bin/

echo "CompositeVersion:- `oraversion -compositeVersion`" ; echo "BaseVersion:- `oraversion -baseVersion`" ;echo "MajorVersion:- `oraversion -majorVersion`" ;echo "BuildStamp :- `oraversion -buildStamp`";echo "BuildDescription :- `oraversion -buildDescription`"

output !!

CompositeVersion:- 18.12.0.0.0
BaseVersion:- 18.0.0.0.0
MajorVersion:- 18
BuildStamp :- 201003042812
BuildDescription :- Release_Update

from Grid

 id
uid=54322(grid) gid=54321(oinstall) groups=54321(oinstall),599(ssh_access),54322(dba),54323(asmdba),54324(asmadmin),54325(asmoper)

echo "CompositeVersion:- `oraversion -compositeVersion`" ; echo "BaseVersion:- `oraversion -baseVersion`" ;echo "MajorVersion:- `oraversion -majorVersion`" ;echo "BuildStamp :- `oraversion -buildStamp`";echo "BuildDescription :- `oraversion -buildDescription`"

Output !!

CompositeVersion:- 18.6.0.0.0
BaseVersion:- 18.0.0.0.0
MajorVersion:- 18
BuildStamp :- 190319010234
BuildDescription :- Release_Update

In 12.1  we have two different types of patches – Bundle patch /PSU patches and interim patches (non bundles) :

  The versioning scheme up until 12.2.0.1 splits the patch metadata into 2 places :

The XML patch descriptor found under $ORACLE_HOME/sqlpatch/bug id/bug uid/bug id.xml  
bundledata_.xml found under $ORACLE_HOME/rdbms/admin.

check xml file 
cd /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin


ls -ltr *xml
-rw-r--r-- 1 oracle oinstall 52007 Oct  5 12:01 sqlfiles.xml
-rw-r--r-- 1 oracle oinstall  4130 Oct  5 12:01 bundlefcp_DBBP.xml


[oracle@ORA-18C-DR admin]$ cat bundlefcp_DBBP.xml|more
 


Bundle patches are marked with ‘B’ in the flags column of dba_registry_sqlpatch.

 

Query the last patch applied on database 11g, 12cR1, 12cR2 ,18c and 19c....

set lines 132  verify  off head off  feedback on  long 1000

select distinct

'======================================================' ||chr(10)||

'INSTALL ID.................: '||INSTALL_ID ||Chr(10)||

'PATCH_ID...................: '||PATCH_ID ||Chr(10)||

'PATCH UID..................: '||PATCH_UID    ||Chr(10)||

'PATCH TYPE.................: '||PATCH_TYPE  ||Chr(10)||

'ACTION.....................: '||ACTION    ||Chr(10)||

'STATUS.....................: '||STATUS    ||Chr(10)||

'ACTION TIME................: '||ACTION_TIME    ||Chr(10)||

'DESCRIPTION................: '||DESCRIPTION    ||Chr(10)||

'FLAGS......................: '||FLAGS    ||Chr(10)||

'SOURCE VERSION.............: '||SOURCE_VERSION ||Chr(10)||

'SOURCE BUILD_DESCRIPTION...: '||SOURCE_BUILD_DESCRIPTION    ||Chr(10)||

'SOURCE BUILD_TIMESTAMP.....: '||SOURCE_BUILD_TIMESTAMP  ||Chr(10)||

'TARGET VERSION.............: '||TARGET_VERSION    ||Chr(10)||

'TARGET BUILD DESCRIPTION...: '||TARGET_BUILD_DESCRIPTION    ||Chr(10)||

'TARGET BUILD TIMESTAMP.....: '||TARGET_BUILD_TIMESTAMP         ||Chr(10)

--'LOGFILE....................: '||LOGFILE  ||Chr(10)||

--'RU LOGFILE.................: '||RU_LOGFILE    ||Chr(10)

from dba_registry_sqlpatch  

order by 1

And ..... 

for CDB

set lines 132  verify  off head off  feedback on  long 1000

select distinct

'======================================================' ||chr(10)||

'INSTALL ID.................: '||INSTALL_ID ||Chr(10)||

'PATCH_ID...................: '||PATCH_ID ||Chr(10)||

'PATCH UID..................: '||PATCH_UID    ||Chr(10)||

'PATCH TYPE.................: '||PATCH_TYPE  ||Chr(10)||

'ACTION.....................: '||ACTION    ||Chr(10)||

'STATUS.....................: '||STATUS    ||Chr(10)||

'ACTION TIME................: '||ACTION_TIME    ||Chr(10)||

'DESCRIPTION................: '||DESCRIPTION    ||Chr(10)||

'FLAGS......................: '||FLAGS    ||Chr(10)||

'SOURCE VERSION.............: '||SOURCE_VERSION ||Chr(10)||

'SOURCE BUILD_DESCRIPTION...: '||SOURCE_BUILD_DESCRIPTION    ||Chr(10)||

'SOURCE BUILD_TIMESTAMP.....: '||SOURCE_BUILD_TIMESTAMP  ||Chr(10)||

'TARGET VERSION.............: '||TARGET_VERSION    ||Chr(10)||

'TARGET BUILD DESCRIPTION...: '||TARGET_BUILD_DESCRIPTION    ||Chr(10)||

'TARGET BUILD TIMESTAMP.....: '||TARGET_BUILD_TIMESTAMP         ||Chr(10)||

'CON_ID.....................: '||CON_ID

--'LOGFILE....................: '||LOGFILE  ||Chr(10)||

--'RU LOGFILE.................: '||RU_LOGFILE    ||Chr(10)

from cdb_registry_sqlpatch

 order by 1

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

INSTALL ID.................: 1

PATCH_ID...................: 28090523

PATCH UID..................: 22329768

PATCH TYPE.................: RU

ACTION.....................: APPLY

STATUS.....................: SUCCESS

ACTION TIME................: 29-DEC-20 02.44.51.571596 PM

DESCRIPTION................: Database Release Update : 18.3.0.0.180717 (28090523)

FLAGS......................: N

SOURCE VERSION.............: 18.1.0.0.0

SOURCE BUILD_DESCRIPTION...: Feature Release

SOURCE BUILD_TIMESTAMP.....:

TARGET VERSION.............: 18.3.0.0.0

TARGET BUILD DESCRIPTION...: Release_Update

TARGET BUILD TIMESTAMP.....: 28-JUN-18 09.43.00.000000 AM

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

INSTALL ID.................: 2

PATCH_ID...................: 27923415

PATCH UID..................: 22239273

PATCH TYPE.................: INTERIM

ACTION.....................: APPLY

STATUS.....................: SUCCESS

ACTION TIME................: 29-DEC-20 02.48.29.679234 PM

DESCRIPTION................: OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)

FLAGS......................: NJ

SOURCE VERSION.............: 18.3.0.0.0

SOURCE BUILD_DESCRIPTION...: Release_Update

SOURCE BUILD_TIMESTAMP.....: 28-JUN-18 09.43.00.000000 AM

TARGET VERSION.............: 18.3.0.0.0

TARGET BUILD DESCRIPTION...: Release_Update

TARGET BUILD TIMESTAMP.....: 28-JUN-18 09.43.00.000000 AM

2 rows selected.

From cdb_registry_sqlpatch

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

INSTALL ID.................: 1

PATCH_ID...................: 29517242

PATCH UID..................: 22862832

PATCH TYPE.................: RU

ACTION.....................: APPLY

STATUS.....................: SUCCESS

ACTION TIME................: 18-MAR-23 02.58.38.960124 PM

DESCRIPTION................: Database Release Update : 19.3.0.0.190416 (29517242)

FLAGS......................: N

SOURCE VERSION.............: 19.1.0.0.0

SOURCE BUILD_DESCRIPTION...: Feature Release

SOURCE BUILD_TIMESTAMP.....:

TARGET VERSION.............: 19.3.0.0.0

TARGET BUILD DESCRIPTION...: Release_Update

TARGET BUILD TIMESTAMP.....: 10-APR-19 12.27.20.000000 PM

CON_ID.....................: 1

===

SET LINESIZE 500  PAGESIZE 1000  SERVEROUT ON  LONG 2000000

COLUMN action_time FORMAT A12

COLUMN action FORMAT A10

COLUMN bundle_series FORMAT A4

COLUMN comments FORMAT A30

COLUMN description FORMAT A70

COLUMN namespace FORMAT A20

COLUMN status FORMAT A10

COLUMN version FORMAT A10

SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,

 action,

 status,

 description,

 version,

 patch_id,

 bundle_series

 FROM   sys.dba_registry_sqlpatch

 ORDER by action_time;

===

The XML patch descriptor found under $ORACLE_HOME/sqlpatch/bug id/bug uid/bug id.xml  

bundledata_<series>.xml found under $ORACLE_HOME/rdbms/admin.

Bundle patches are marked with 'B' in the flags column of dba_registry_sqlpatch.

FLAGS!!!! 

U: Patch requires upgrade mode

J: Patch is a JVM patch

F: Patch was installed using -force

B: Patch is a bundle patch

PATCH_TYPE

Type of the patch. Possible values:

• INTERIM: Interim patch

• RU: Release Update

• RUI: Release Update Increment

• RUR: Release Update Revision

• CU: Cumulative Update

set lines 132  verify  off head off  feedback on  long 1000

select distinct

'======================================================' ||chr(10)||

'PATCH_ID...................: '||PATCH_ID ||Chr(10)||

'PATCH UID..................: '||PATCH_UID    ||Chr(10)||

'ACTION.....................: '||ACTION    ||Chr(10)||

'STATUS.....................: '||STATUS    ||Chr(10)||

'ACTION TIME................: '||ACTION_TIME    ||Chr(10)||

'DESCRIPTION................: '||DESCRIPTION    ||Chr(10)||

'FLAGS......................: '||FLAGS    ||Chr(10)||

'LOGFILE....................: '||LOGFILE  ||Chr(10)

from dba_registry_sqlpatch  

order by 1

What to do if the status of a datapatch action was not SUCCESS due to finding non-ignorable errors (Doc ID 1635056.1)

use this !!!

 $ORACLE_HOME/OPatch/datapatch -verbose

 

 

 $ORACLE_HOME/OPatch/datapatch -verbose

SQL Patching tool version 18.0.0.0.0 Production on Fri Jan  1 14:05:52 2021

Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_65502_2021_01_01_14_05_52/sqlpatch_invocation.log

Connecting to database...OK

Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs

       that are in an open state, no patches will be applied to closed PDBs.

       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

       (Doc ID 1585822.1)

Warning: PDB ANUJ is in mode MOUNTED and will be skipped.

Warning: PDB PDB2 is in mode MOUNTED and will be skipped.

Warning: PDB VIHAAN is in mode MOUNTED and will be skipped.

Warning: PDB VIHAAN2 is in mode MOUNTED and will be skipped.

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of interim SQL patches:

Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)):

  Binary registry: Installed

  PDB CDB$ROOT: Applied successfully on 29-DEC-20 02.48.29.679234 PM

  PDB PDB$SEED: Applied successfully on 29-DEC-20 03.44.41.663023 PM

Current state of release update SQL patches:

  Binary registry:

    18.3.0.0.0 Release_Update 1806280943: Installed

  PDB CDB$ROOT:

    Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 29-DEC-20 02.44.51.571596 PM

  PDB PDB$SEED:

    Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 29-DEC-20 03.42.14.995595 PM

Adding patches to installation queue and performing prereq checks...done

Installation queue:

  For the following PDBs: CDB$ROOT PDB$SEED

    No interim patches need to be rolled back

    No release update patches need to be installed

    No interim patches need to be applied

SQL Patching tool complete on Fri Jan  1 14:06:05 2021

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

set lines 132  verify  off head off  feedback on  long 1000

select distinct

'======================================================'        ||chr(10)||

'PATCH_ID...................: '||PATCH_ID                       ||Chr(10)||

'PATCH UID..................: '||PATCH_UID                      ||Chr(10)||

'ACTION.....................: '||ACTION                         ||Chr(10)||

'STATUS.....................: '||STATUS                         ||Chr(10)||

'ACTION TIME................: '||ACTION_TIME                    ||Chr(10)||

'DESCRIPTION................: '||DESCRIPTION                    ||Chr(10)||

'FLAGS......................: '||FLAGS                          ||Chr(10)||

'LOGFILE....................: '||LOGFILE                        ||Chr(10)

from dba_registry_sqlpatch

order by 1

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

PATCH_ID...................: 29517242

PATCH UID..................: 22862832

ACTION.....................: APPLY

STATUS.....................: SUCCESS

ACTION TIME................: 18-MAR-23 02.58.38.960124 PM

DESCRIPTION................: Database Release Update : 19.3.0.0.190416 (29517242)

FLAGS......................: N

LOGFILE....................: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_ORCLDP_CDBROOT_2023Mar18_14_56_39

.log

set lines 132  verify  off head off  feedback on  long 1000

select distinct

'======================================================'||chr(10)||

'id...........................: '||id    ||Chr(10)||

'ACTION.......................: '||ACTION ||Chr(10)||

'ACTION_TIME..................: '||ACTION_TIME ||Chr(10)||

'NAMESPACE....................: '||NAMESPACE    ||Chr(10)||

'VERSION......................: '||VERSION    ||Chr(10)||

'BUNDLE_SERIES................: '||BUNDLE_SERIES||Chr(10)||

'COMMENTS.....................: '||COMMENTS    

from registry$history  ---<<<<---DBA_REGISTRY_HISTORY does not get updated when he applies a Bundle Patch

order by 1

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

id...........................:

ACTION.......................: BOOTSTRAP

ACTION_TIME..................:

NAMESPACE....................: DATAPATCH

VERSION......................: 12.2.0.1

BUNDLE_SERIES................:

COMMENTS.....................: RDBMS_12.2.0.1.0_LINUX.X64_170125

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

id...........................:

ACTION.......................: BOOTSTRAP

ACTION_TIME..................:

NAMESPACE....................: DATAPATCH

VERSION......................: 18

BUNDLE_SERIES................:

COMMENTS.....................: RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627

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

Or !!

set serverout on

exec dbms_qopatch.get_sqlpatch_status;

SQL>  set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 28090523

        Action : APPLY

        Action Time : 29-DEC-2020 14:44:51

        Description : Database Release Update : 18.3.0.0.180717 (28090523)

        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/28090523/22329768/28090523_apply_ORCL_CDBROOT_2020Dec29_14_44_14.log

        Status : SUCCESS

Patch Id : 27923415

        Action : APPLY

        Action Time : 29-DEC-2020 14:48:29

        Description : OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)

        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/27923415/22239273/27923415_apply_ORCL_CDBROOT_2020Dec29_14_48_26.log

        Status : SUCCESS

PL/SQL procedure successfully completed.

set linesize 300

select patch_id,action,action_time,status,con_id from cdb_registry_sqlpatch;

  PATCH_ID ACTION          ACTION_TIME                                                                 STATUS                        CON_ID

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

  29249637 APPLY           12-APR-20 09.18.11.794215 AM                                                SUCCESS                            1

  29314339 APPLY           12-APR-20 09.18.14.935601 AM                                                SUCCESS                            1

  29249637 APPLY           12-APR-20 04.01.26.501126 AM                                                SUCCESS                            3

  29314339 APPLY           12-APR-20 04.01.29.657573 AM                                                SUCCESS                            3

  29249637 APPLY           12-APR-20 04.01.26.501126 AM                                                SUCCESS                            4

  29314339 APPLY           12-APR-20 04.01.29.657573 AM                                                SUCCESS                            4

6 rows selected.

===========

with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)

     select x.*

       from a,

     xmltable('InventoryInstance/patches/*'

     passing a.patch_output

     columns

        patch_id number path 'patchID',

        patch_uid number path 'uniquePatchID',

        description varchar2(80) path 'patchDescription',

     constituent number path 'constituent',

     patch_type varchar2(20) path 'patchType',

     rollbackable varchar2(20) path 'rollbackable',

     sql_patch varchar2(8) path 'sqlPatch',

     DBStartMode varchar2(10) path 'sqlPatchDatabaseStartupMode'

   ) x

 

 PATCH_ID  PATCH_UID DESCRIPTION                                        CONSTITUENT PATCH_TYPE           ROLLBACKABLE         SQL_PATC DBSTARTMOD

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

  29249637   22802302 OJVM RELEASE UPDATE: 12.2.0.1.190416 (29249637)                singleton            true                 true     normal

  29314339   22821655 Database Apr 2019 Release Update : 12.2.0.1.190416             singleton            true                 true     normal

                       (29314339)

  28163190   22274810 OCW JUL 2018 RELEASE UPDATE 12.2.0.1.180717 (28163             bundle_member        true                 false

                      190)