How to find Oracle patch history ?

  1. Go to $ORACLE_HOME/.patch_storage directory
[oracle@oel65_11g ~]$ cd $ORACLE_HOME/.patch_storage
  1. You can find all information about patchs and sub-patchs
[oracle@oel65_11g .patch_storage]$ ls -ltrh
total 76K
drwxr-xr-x 3 oracle oinstall 4.0K Apr 26 15:55 NRollback
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:00 NApply
-rw-r--r-- 1 oracle oinstall   52 Apr 26 16:00 record_inventory.txt
-rw-r--r-- 1 oracle oinstall 4.5K Apr 26 16:00 interim_inventory.txt
-rw-r--r-- 1 oracle oinstall   93 Apr 26 16:03 LatestOPatchSession.properties
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 19769489_Dec_28_2014_21_22_44
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 24732075_Feb_22_2017_21_40_49
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 19121551_Oct_6_2014_10_07_57
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 20760982_Jun_4_2015_00_23_20
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 18031668_Feb_20_2014_05_15_58
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 18522509_Jun_30_2014_08_14_42
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 20299013_Mar_4_2015_02_27_44
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 21948347_Dec_14_2015_03_31_48
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 23054359_Jun_15_2016_16_08_35
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 17478514_Dec_6_2013_04_22_19
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 21352635_Sep_1_2015_07_49_44
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 24006111_Aug_26_2016_05_54_48
drwxr-xr-x 4 oracle oinstall 4.0K Apr 26 16:06 22502456_Mar_21_2016_11_49_22
  1. All apply histories are in NApply directory.
[oracle@oel65_11g .patch_storage]$ cd NApply/
[oracle@oel65_11g NApply]$ ls -ltrh
total 8.0K
drwxr-xr-x 2 oracle oinstall 4.0K Oct 31 08:25 2016-10-31_08-25-22AM
drwxr-xr-x 2 oracle oinstall 4.0K Apr 26 16:06 2017-04-26_15-57-54PM

[oracle@oel65_11g NApply]$ cd 2016-10-31_08-25-22AM
[oracle@oel65_11g 2016-10-31_08-25-22AM]$ ls -l
total 4
-rw-r--r-- 1 oracle oinstall 222 Oct 31 08:25 patchlist.txt
[oracle@oel65_11g 2016-10-31_08-25-22AM]$ cat 2016-10-31_08-25-22AM
cat: 2016-10-31_08-25-22AM: No such file or directory
[oracle@oel65_11g 2016-10-31_08-25-22AM]$ cat patchlist.txt
User invokes OPatch via "NApply" to process the following patches:

Patch ID: 23543737
Patch Location: "/u01/23543737"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/23543737_Jul_22_2016_09_18_21"
[oracle@oel65_11g 2016-10-31_08-25-22AM]$ cd ..
[oracle@oel65_11g NApply]$ cd 2017-04-26_15-57-54PM/
[oracle@oel65_11g 2017-04-26_15-57-54PM]$ cat patchlist.txt
User invokes OPatch via "NApply" to process the following patches:

Patch ID: 17478514
Patch Location: "/u01/24732075/17478514"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/17478514_Dec_6_2013_04_22_19"

Patch ID: 18031668
Patch Location: "/u01/24732075/18031668"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/18031668_Feb_20_2014_05_15_58"

Patch ID: 18522509
Patch Location: "/u01/24732075/18522509"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/18522509_Jun_30_2014_08_14_42"

Patch ID: 19121551
Patch Location: "/u01/24732075/19121551"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/19121551_Oct_6_2014_10_07_57"

Patch ID: 19769489
Patch Location: "/u01/24732075/19769489"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/19769489_Dec_28_2014_21_22_44"

Patch ID: 20299013
Patch Location: "/u01/24732075/20299013"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/20299013_Mar_4_2015_02_27_44"

Patch ID: 20760982
Patch Location: "/u01/24732075/20760982"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/20760982_Jun_4_2015_00_23_20"

Patch ID: 21352635
Patch Location: "/u01/24732075/21352635"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/21352635_Sep_1_2015_07_49_44"

Patch ID: 21948347
Patch Location: "/u01/24732075/21948347"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/21948347_Dec_14_2015_03_31_48"

Patch ID: 22502456
Patch Location: "/u01/24732075/22502456"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/22502456_Mar_21_2016_11_49_22"

Patch ID: 23054359
Patch Location: "/u01/24732075/23054359"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/23054359_Jun_15_2016_16_08_35"

Patch ID: 24006111
Patch Location: "/u01/24732075/24006111"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/24006111_Aug_26_2016_05_54_48"

Patch ID: 24732075
Patch Location: "/u01/24732075/24732075"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/24732075_Feb_22_2017_21_40_49"
  1. All rollback histories are in NRollback directory.
[oracle@oel65_11g NApply]$ cd ..
[oracle@oel65_11g .patch_storage]$ cd NRollback/
[oracle@oel65_11g NRollback]$ ls -ltrh
total 4.0K
drwxr-xr-x 2 oracle oinstall 4.0K Apr 26 15:56 2017-04-26_15-55-07PM
[oracle@oel65_11g NRollback]$ cd 2017-04-26_15-55-07PM/
[oracle@oel65_11g 2017-04-26_15-55-07PM]$ ls -ltrh
total 4.0K
-rw-r--r-- 1 oracle oinstall 274 Apr 26 15:55 patchlist.txt
[oracle@oel65_11g 2017-04-26_15-55-07PM]$ cat patchlist.txt
User invokes OPatch via "NRollback" to process the following patches:

Patch ID: 23543737
Patch Location: "/u01/app/oracle/product/11.2.0/db_1/inventory/oneoffs/23543737"
Patch storage area: "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/23543737_Jul_22_2016_09_18_21"
In my environment, I understand that patch number 23543737 applied on 2016-10-31 and rollbacked on 2017-04-26.
  1. You can find last opatch operation information in LatestOPatchSession.properties file.
[oracle@oel65_11g .patch_storage]$ cat LatestOPatchSession.properties
SessionName=apply
SessionID=24732075_Feb_22_2017_21_40_49
PatchID=24732075
TriggeredPatchID=

last ddls on database

set linesize 200;
set pagesize 100;
set heading on;
COLUMN OWNER FORMAT A15
COLUMN OBJECT_NAME FORMAT A40
COLUMN OBJECT_TYPE FORMAT A30
  SELECT OWNER,
         OBJECT_NAME,
         OBJECT_TYPE,
         CREATED,
         LAST_DDL_TIME,
         TIMESTAMP,
         STATUS
    FROM DBA_OBJECTS
   WHERE     (CREATED >= SYSDATE 7 OR LAST_DDL_TIME >= SYSDATE 7)
         AND OWNER NOT IN (‘SYS’,‘SYSTEM’, ‘SYSMAN’, ‘DBSNMP’)
ORDER BY LAST_DDL_TIME DESC;

asm get all file paths

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN full_path              FORMAT a75                  HEAD ‘ASM File Name / Volume Name / Device Name’
COLUMN system_created         FORMAT a8                   HEAD ‘System|Created?’
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD ‘Bytes’
COLUMN space                  FORMAT 9,999,999,999,999    HEAD ‘Space’
COLUMN type                   FORMAT a18                  HEAD ‘File Type’
COLUMN redundancy             FORMAT a12                  HEAD ‘Redundancy’
COLUMN striped                FORMAT a8                   HEAD ‘Striped’
COLUMN creation_date          FORMAT a20                  HEAD ‘Creation Date’
COLUMN disk_group_name        noprint
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL “”              OF bytes space ON disk_group_name
COMPUTE sum LABEL “Grand Total: “ OF bytes space ON report
select a.*
from (
SELECT
    CONCAT(‘+’ || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, ‘/’)) full_path
  , db_files.bytes
  , db_files.space
  , NVL(LPAD(db_files.type, 18), ‘<DIRECTORY>’)  type
  , db_files.creation_date
  , db_files.disk_group_name
  , LPAD(db_files.system_created, 4) system_created
FROM
    ( SELECT
          g.name               disk_group_name
        , a.parent_index       pindex
        , a.name               alias_name
        , a.reference_index    rindex
        , a.system_created     system_created
        , f.bytes              bytes
        , f.space              space
        , f.type               type
        , TO_CHAR(f.modification_date , ‘DD-MON-YYYY HH24:MI:SS’)  creation_date
      FROM
          v$asm_file f RIGHT OUTER JOIN v$asm_alias     a USING (group_number, file_number)
                                   JOIN v$asm_diskgroup g USING (group_number)
    ) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
    CONNECT BY PRIOR db_files.rindex = db_files.pindex
UNION
SELECT
    ‘+’ || volume_files.disk_group_name ||  ‘ [‘ || volume_files.volume_name || ‘] ‘ ||  volume_files.volume_device full_path
  , volume_files.bytes
  , volume_files.space
  , NVL(LPAD(volume_files.type, 18), ‘<DIRECTORY>’)  type
  , volume_files.creation_date
  , volume_files.disk_group_name
  , null
FROM
    ( SELECT
          g.name               disk_group_name
        , v.volume_name        volume_name
        , v.volume_device       volume_device
        , f.bytes              bytes
        , f.space              space
        , f.type               type
        , TO_CHAR(f.modification_date , ‘DD-MON-YYYY HH24:MI:SS’)  creation_date
      FROM
          v$asm_file f RIGHT OUTER JOIN v$asm_volume    v USING (group_number, file_number)
                                   JOIN v$asm_diskgroup g USING (group_number)
    ) volume_files
WHERE volume_files.type IS NOT NULL) a
where UPPER(a.full_path) like ‘%SNAP%’
–where type like ‘%CONTROL%’
/

data file list script

col file_name format a50
col ts_name format a15
col AUTOEXTENSIBLE format a7
set lines 500
set pages 500
SELECT tablespace_name TS_NAME,
     a.file_name,
     ROUND ( (BYTES) / 1024 / 1024, 2) size_mb,
     b.free_mb,
     CASE
        WHEN a.maxbytes = 0 THEN ROUND ( (BYTES) / 1024 / 1024, 2)
        WHEN a.maxbytes > 0 THEN ROUND ( (maxbytes) / 1024 / 1024, 2)
     END
        max_size_mb,
     a.AUTOEXTENSIBLE
FROM dba_data_files a,
     (  SELECT file_id, ROUND (SUM (bytes) / 1024 / 1024, 2) free_mb
          FROM dba_free_space
      GROUP BY file_id) b
WHERE a.file_id = b.file_id
ORDER BY TS_NAME, AUTOEXTENSIBLE;

redo sizes by days script

select DAY,sum(total)*(select distinct round(bytes/1024/1024/1024,2) from v$log) TOTAL_GB,
sum(total)*(select distinct round(bytes/1024/1024,2) from v$log) TOTAL_MB
from
(SELECT TO_CHAR(FIRST_TIME,‘YYYY-MM-DD’) DAY,
TO_CHAR(FIRST_TIME,‘HH24’) HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
WHERE TO_CHAR(FIRST_TIME,‘YYYY-MM-DD’) >= TO_CHAR(SYSDATE-10,‘YYYY-MM-DD’)
GROUP BY TO_CHAR(FIRST_TIME,‘YYYY-MM-DD’),TO_CHAR(FIRST_TIME,‘HH24’)
ORDER BY TO_CHAR(FIRST_TIME,‘YYYY-MM-DD’),TO_CHAR(FIRST_TIME,‘HH24’)
ASC)
group by DAY
order by DAY;

Redo sizes by hours

COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,‘YYYY-MM-DD’) DAY,
TO_CHAR(FIRST_TIME,‘HH24’) HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
WHERE TO_CHAR(FIRST_TIME,‘YYYY-MM-DD’) >= TO_CHAR(SYSDATE-10,‘YYYY-MM-DD’)
GROUP BY TO_CHAR(FIRST_TIME,‘YYYY-MM-DD’),TO_CHAR(FIRST_TIME,‘HH24’)
ORDER BY TO_CHAR(FIRST_TIME,‘YYYY-MM-DD’),TO_CHAR(FIRST_TIME,‘HH24’)
ASC;

db_recovery_file_dest usage script

set lines 300
col name format a50
SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used  / 1024 / 1024) USED_M
, ceil( space_reclaimable  / 1024 / 1024) RECLAIMABLE_M
, decode( nvl( space_used, 0),
  0, 0
  , ceil ( ( ( space_used space_reclaimable ) / space_limit) * 100) ) PCT_USED
  FROM v$recovery_file_dest
ORDER BY name;