Query is to find the session that is generating more Archives.

if space is full then change the archive location

alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';





col program for a10
col username for a10
select to_char(sysdate,’hh24:mi’), username, program , a.sid, a.serial#, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where b.STATISTIC# =c.STATISTIC#
and c.sid=a.sid and b.name like ‘redo%’
order by value;


===================Archivelog generation on a daily basis===============
set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;



===================Archive log generation on an hourly basis====================
set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log

group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;


-- Show the Number of Redo Log Switches Per Hour
--

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

SELECT to_char(first_time, 'yyyy - mm - dd') aday,
           to_char(first_time, 'hh24') hour,
           count(*) total
FROM   v$log_history
WHERE  thread#=&EnterThreadId
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
/



############script to list the history of log switches per hour over the last week#######
SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
  ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <8;


================archivelog switches on an hourly basis that happened in the past one week===================
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
where to_date(first_time) > sysdate -30
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/


===list archived files not backed-up============
select NAME, ARCHIVED , deleted, status from v$archived_log where status='A';
####
RUN
{ 
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT  '/u02/export_dir/rman_arch/STATV10_archive%U';
ALLOCATE CHANNEL ch2 DEVICE TYPE DISK FORMAT '/u02/export_dir/rman_arch/STATV10_archive%U';
backup AS COMPRESSED BACKUPSET archivelog all delete input ;
}


###
####Recovery time for one archive log on standby###

set linesize 400
col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",to_char(item)||' = '||to_char(sofar)||' '
||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values" from v$recovery_progress where start_time=(select max(start_time)
from v$recovery_progress);


##List the sessions and the generated redo in MB if the session is still connected.
This was the query I used to use whenever I notice a huge archive log generation.


select s.username, s.osuser, s.status,s.sql_id,  sr.*  from 
  (select sid, round(value/1024/1024) as "RedoSize(MB)" 
          from v$statname sn, v$sesstat ss
          where sn.name = 'redo size'
                and ss.statistic# = sn.statistic#
          order by value desc) sr,
   v$session s
where sr.sid = s.sid
and   rownum <= 10;



###To Know which table / object has more number of changes during the problematic window###

1. 

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
        dhsso.object_name,
        sum(db_block_changes_delta) as maxchages
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND begin_interval_time BETWEEN to_date('2013_05_22 17','YYYY_MM_DD HH24')
                                           AND to_date('2013_05_22 21','YYYY_MM_DD HH24')
  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
           dhsso.object_name order by maxchages asc;



###Tables has more number of changes###
2. 

SNAP_TIME        OBJECT_NAME                     MAXCHAGES
---------------- ------------------------------ ----------
2017_03_03 14:00 SIGNAL_MASCHINE                     10144
2017_03_03 13:00 SIGNAL_MASCHINE                     10240
2017_03_03 12:00 SIGNAL_MASCHINE                     11728
2017_03_03 15:00 SIGNAL_MASCHINE                     12000
2017_03_03 12:00 ICOL$                               24000
2017_03_03 14:00 I_COL1                              24096
2017_03_03 14:00 ICOL$                               24208
2017_03_03 14:00 I_COL3                              24240
2017_03_03 14:00 I_COL2                              24336
2017_03_03 12:00 I_COL2                              24352
2017_03_03 12:00 I_COL3                              24432
2017_03_03 12:00 I_COL1                              24576
2017_03_03 13:00 I_COL3                              27456
2017_03_03 13:00 I_COL1                              27792
2017_03_03 13:00 I_COL2                              27824
2017_03_03 13:00 ICOL$                               27936
2017_03_03 15:00 ICOL$                               31760
2017_03_03 15:00 I_COL1                              32864
2017_03_03 15:00 I_COL2                              32896
2017_03_03 15:00 I_COL3                              33616
2017_03_03 15:00 TPM_SCHICHT                        101328
2017_03_03 12:00 ALIVETIMER                       22931616
2017_03_03 13:00 ALIVETIMER                       23646176
2017_03_03 14:00 ALIVETIMER                       23965088
2017_03_03 15:00 ALIVETIMER                       25387744


####Once you know the objects ,get the SQL information realted to those objects#####
3. 


SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
         dbms_lob.substr(sql_text,4000,1),
         dhss.instance_number,
         dhss.sql_id,executions_delta,rows_processed_delta
  FROM dba_hist_sqlstat dhss,
         dba_hist_snapshot dhs,
         dba_hist_sqltext dhst
  WHERE upper(dhst.sql_text) LIKE '%TPM_SCHICHT%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_Number=dhs.instance_number
 AND begin_interval_time BETWEEN to_date('2017_03_03 10','YYYY_MM_DD HH24')
                                           AND to_date('2017_03_03 16','YYYY_MM_DD HH24')
    AND dhss.sql_id = dhst.sql_id;


#####Once you know the SQL ID , 
Get the program and userid who is running it and intimate the user to take care of this query / Program.
4.

SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
         user_id,program  FROM dba_hist_active_sess_history WHERE sql_id in ('5xmfm787py0a3','bq28gpz5w5qqg','71t5d196acgr9','a8d5br4z9m251')
    AND snap_id BETWEEN 31856 AND 31860 ;


###########This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session#####

SELECT s.sid, s.serial#, s.username, s.program,i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid ORDER BY 5 desc, 1, 2, 3, 4;


#####Query V$TRANSACTION. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

SELECT s.sid, s.serial#, s.username, s.program, 
  t.used_ublk, t.used_urec
  FROM v$session s, v$transaction t
  WHERE s.taddr = t.addr
  ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


SELECT s.sid, s.serial#, s.username, s.program, 
  t.used_ublk, t.used_urec
  FROM v$session s, v$transaction t
  WHERE s.taddr = t.addr
  ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


select sql.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks,
 (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
 v$session s,
 v$sql sql
 where t.addr = s.taddr
 and s.sql_id = sql.sql_id
 and s.username ='&USERNAME';



###########Sessions generating more redo or SQL queries generate heavy archive logs########
set lines 2000
set pages 1000
col sid for 99999
col name for a09
col username for a14
col PROGRAM for a21
col MODULE for a25
select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id
from v$sesstat s join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by
redo_mb desc;

Oracle Cloud Infrastructure Foundations

GI and RDBMS - Patching Oracle 18c (18.3.2.0.0) to Oracle 18c (18.5.0.0.0)

GI and RDBMS - Patching Oracle 18c (18.3.2.0.0) to Oracle 18c (18.5.0.0.0) On Two-Node RAC in Multi-Tenant Environment

GI and RDBMS - Patching Oracle 18c (18.3.2.0.0)  to Oracle 18c (18.5.0.0.0) On Two-Node RAC in Multi-Tenant Environment 


Instance Type: Two Node RAC (orcl1 and orcl2)
Container Database: orcl
Pluggable Databases: pdb1 and pdb2

GRID Patch: p28828717_180000_Linux-x86-64.zip
RDBMS Patch: p28822489_180000_Linux-x86-64.zip

ORACLE DATABASE 12C – CREATE DATA GUARD STANDBY DATABASE USING DBCA

syntax : dbca -createDuplicateDB 

    -gdbName global_database_name 

    -primaryDBConnectionString easy_connect_string_to_primary

    -sid database_system_identifier

    [-createAsStandby 

        [-dbUniqueName db_unique_name_for_standby]]

--

primary : prid01

standby : prid02

We will run the command from the standby  server  prid02 as shown below.

dbca -silent -createDuplicateDB -gdbName psfp -primaryDBConnectionString prid01:1521/pfspsb -sid psfp -createAsStandby -dbUniqueName psfpsb

enter sys user password

once done connect standby  and check the role.

select database_role from v$database:;

now the sp file and orapwd file automatically created.

tnsnames.ora

add the standby and prod tnsnames files

Databroker configuation

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> quit

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

[oracle@prod01 archivelog]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Thu Mar 1914:47:27 2019

connect /

DGMGRL> create configuration 'psfp_dg'

> as primary database is 'psfp'

> connect identifier is 'psfpsb';

DGMGRL> add database 'psfpsb' as connect identifier is 'psfpsb';

DGMGRL> enable configuration;
Enabled.

Create the Standby Redo Log Files on the primary database

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oradata/psfp/redo03.log
/u01/app/oradata/psfp/redo02.log
/u01/app/oradata/psfp/redo01.log

SQL> select bytes/1048576 from v$log;

BYTES/1048576
-------------
     200
     200
     200


SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo1.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo2.log' size 200m;

Database altered.


SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo3.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo4.log' size 200m;

Database altered.


Create the Standby Redo Log Files on the standby database



DGMGRL> connect /
Connected to "psfp"
Connected as SYSDG.

DGMGRL> edit database 'psfp_sb' set state='APPLY-OFF';
Succeeded.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size          8619984 bytes
Variable Size           436209712 bytes
Database Buffers   721420288 bytes
Redo Buffers              8155136 bytes
Database mounted.

SQL>  alter database add standby logfile '/u01/app/oradata/psfp/standy_redo1.log' size 200m;

Database altered.


SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo2.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo3.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo4.log' size 200m;

Database altered.

SQL> alter database open;

Database altered.

SQL>


Verify the Data Guard Configuration



DGMGRL> edit database 'psfp_sb' set state='APPLY-ON';
Succeeded.


DGMGRL> show configuration;

Configuration - psfp_dg

 Protection Mode: MaxPerformance

 psfp    - Primary database
   psfp_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 8 seconds ago)


Set the property StaticConnectIdentifier to prevent errors during switchover operations



Edit database ‘psfp’ set property StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prid01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=psfp_DGMGRL)(INSTANCE_NAME=psfp)(SERVER=DEDICATED)))';
Edit database ‘psfp_sb’ set property StaticConnectIdentifier=StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prid02.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=psfp_sb_DGMGRL)(INSTANCE_NAME=psfp)(SERVER=DEDICATED)))';
Edit listener.ora on primary database host and add the lines shown below. Reload the listener.



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = psfp_DGMGRL)
      (SID_NAME = psfp)
        )
  )



Edit listener.ora on standby database host and add the lines shown below. Reload the listener.



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = psfp_sb_DGMGRL)
      (SID_NAME = psfp)
        )
  )



Enjoy

INMEMORY IN ORACLE 12C

IN Memory

inmemory_size must be set 100m or larger

sga_target=sga+in memory ---singe instance

sga_targer=sga+(inmemory*1)--Rac

pga_target= (max expected parallel servers * 2gb)


how does it work


SGA_TARGET must be large enough to accommodate in memory area.

Contain data in the new in memory columnar format.

inmemory contain two subpools

imcu  allocate 1mb extend in memory area(segements)

smu

object in memory area is composed of one or more

some useful for immemory.

V$inmemory_area current size of pools in the in memory area

v$IM_SEGMENTS  list of segments currently populated in memory column store.

to monitor the progress of population.

V$INMEMORY_AREA ---IS THERE ENOUGH MEMORY

V$IM_SEGMENTS
V$IM_SMU_HEAD




Inmemory column store is not a cache

Any objects with the in memory




oracle -in-memory advisor
===================

create response file and Applying PSU (Apr-17)Patch on Oracle 11gR2 Two Node RAC

Applying PSU (APR 2017) Patch on Oracle 11gR2 Two Node RAC

This document will explain how to apply PSU patch ( April-2017) on Oracle 11.2.0.4 RAC, All the steps are performed on 2 node RAC.

Consolidated steps:

1 : Take Backup of OPatch folder on Both GI and RDBMS homes and keep recommended OPatch folder as per document.
2 : Stop OEM, if running
3 : Download required PSU (25476126) and unzip it
4 : Create response file from any OPatch location
5 : Apply the PSU node by node
6 : Check opatch details and run @catbundle psu apply followed by @utlrp.sql

Step details:

1) Stop OEM if running

bash-4.2$ export ORACLE_UNQNAME=PROD
bash-4.2$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://eddb1.imcl.com:5500/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.


2) Exting patches both GI and RDMS homes:

a) RDBMS Home
bash-4.2$ ./opatch lspatches
20831122;OCW Patch Set Update : 11.2.0.4.7 (20831122)
20760982;Database Patch Set Update : 11.2.0.4.7 (20760982)

OPatch succeeded.
bash-4.2$ ./opatch version
OPatch Version: 11.2.0.3.14

OPatch succeeded.
bash-4.2$

b) GI Home

bash-4.2$ pwd
/oracle/app/11.2.0/grid/OPatch
bash-4.2$ ./opatch version
OPatch Version: 11.2.0.3.14

OPatch succeeded.
bash-4.2$ ./opatch lspatches
20831122;OCW Patch Set Update : 11.2.0.4.7 (20831122)
20760982;Database Patch Set Update : 11.2.0.4.7 (20760982)
20299019;ACFS Patch set update : 11.2.0.4.6 (20299019)

OPatch succeeded.
bash-4.2$


3) Download Latest PSU and Unzip

bash-4.2$ cd /u02/Patch_dir
bash-4.2$ pwd
/u02/Patch_dir
bash-4.2$ ls
25476126                       PatchSearch.xml                p25476126_112040_AIX64-5L.zip


4) Create a response file

su - oracle
$cd $ORACLE_HOME/OPatch/ocm/bin/
bash-4.2$ pwd
/oracle/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin
$./emocmrsp -no_banner -output /u02/Patch_dir/ocm.rsp

output:

bash-4.2$ ./emocmrsp -no_banner -output /u02/Patch_dir/ocm.rsp
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
The OCM configuration response file (/u02/Patch_dir/ocm.rsp) was successfully created.
bash-4.2$


5) Now run the patch installation as root user, to ensure that the installation is done directly to BOTH oracle and grid homes

# ./opatch auto /u02/Patch_dir/25476126 -ocmrf /u02/Patch_dir/ocm.rsp

output:

bash-4.2# pwd
/oracle/app/oracle/product/11.2.0/dbhome_1/OPatch
bash-4.2# ./opatch auto /u02/Patch_dir/25476126 -ocmrf /u02/Patch_dir/ocm.rsp
Executing /oracle/app/11.2.0/grid/perl/bin/perl ./crs/patch11203.pl -patchdir /u02/Patch_dir -patchn 25476126 -ocmrf /u02/Patch_dir/ocm.rsp -paramfile /oracle/app/11.2.0/grid/crs/install/crsconfig_params

This is the main log file: /oracle/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatchauto2017-06-17_16-12-55.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/oracle/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatchauto2017-06-17_16-12-55.report.log

2017-06-17 16:12:55: Starting Clusterware Patch Setup
Using configuration parameter file: /oracle/app/11.2.0/grid/crs/install/crsconfig_params

Stopping RAC /oracle/app/oracle/product/11.2.0/dbhome_1 ...
Stopped RAC /oracle/app/oracle/product/11.2.0/dbhome_1 successfully

patch /u02/Patch_dir/25476126/24732075  apply successful for home  /oracle/app/oracle/product/11.2.0/dbhome_1
patch /u02/Patch_dir/25476126/23054319/custom/server/23054319  apply successful for home  /oracle/app/oracle/product/11.2.0/dbhome_1

Stopping CRS...
Stopped CRS successfully

patch /u02/Patch_dir/25476126/24732075  apply successful for home  /oracle/app/11.2.0/grid
patch /u02/Patch_dir/25476126/23054319  apply successful for home  /oracle/app/11.2.0/grid
patch /u02/Patch_dir/25476126/22502505  apply successful for home  /oracle/app/11.2.0/grid

Starting CRS...
Installing Trace File Analyzer
CRS-4123: Oracle High Availability Services has been started.

Starting RAC /oracle/app/oracle/product/11.2.0/dbhome_1 ...
Started RAC /oracle/app/oracle/product/11.2.0/dbhome_1 successfully

opatch auto succeeded.
bash-4.2#

6) Verify the Opatch applied or not?

su - oracle
bash-4.2$ pwd
/u02/Patch_dir
bash-4.2$ cd $ORACLE_HOME/OPatch
bash-4.2$ ./opatch lspatches
23054319;OCW Patch Set Update : 11.2.0.4.160719 (23054319)
24732075;Database Patch Set Update : 11.2.0.4.170418 (24732075)

OPatch succeeded.
bash-4.2$

#su - grid

bash-4.2$ ./opatch lspatches
22502505;ACFS Patch Set Update : 11.2.0.4.160419 (22502505)
23054319;OCW Patch Set Update : 11.2.0.4.160719 (23054319)
24732075;Database Patch Set Update : 11.2.0.4.170418 (24732075)

OPatch succeeded.
bash-4.2$ whoami
grid
bash-4.2$


Now you can proceed same steps in other nodes

Note: Application downtime is not required.

7) For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql and utlrp.sql script as follows:

# su - oracle
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> @utlrp.sql
SQL>

Automatic Database Diagnostic Monitor (ADDM) Support for PDB Database

[oracle@rac1 bin]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 12 21:18:35 2020

Version 19.4.1.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.4.1.0.0

SQL> alter session set container=pdb01;

Session altered.

SQL>

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         4 PDB01                          READ WRITE NO

SQL> show parameter AWR_PDB_AUTOFLUSH_ENABLED;

NAME                                 TYPE        VALUE

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

awr_pdb_autoflush_enabled            boolean     FALSE