Search This Blog

Total Pageviews

Saturday, 23 January 2021

ORA-19912: cannot recover to target incarnation


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>
Posted by at No comments:

Thursday, 21 January 2021

Oracle Tracing file info 

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';



set linesize 300
col NAME for a25
col VALUE for a80
select * from GV$DIAG_INFO ;





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








split alter logfile date wise 


sed -n -e '\#2024-07-31#,\#2024-08-01#w first.out' alert_rac1.log

sed -n -e '\#2024-08-01#,\#2024-08-01#w Alert01-08-2024.out' alert_rac1.log
cat Alert01-08-2024.out




Posted by at 2 comments:

Sunday, 3 January 2021

Oracle version info via oraversion 

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.
Posted by at 1 comment:

Friday, 1 January 2021

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

 

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)



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


create sql patch !!!!!!!!
https://anuj-singh.blogspot.com/search?q=dbms_sqldiag.create_sql_patch
Posted by at 4 comments:
Subscribe to: Posts (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive