Container and Pluggable database Patching steps in Oracle 12c
Note: Disable the backup job of RMAN Scheduler or rename the script.
1. Set ORACLE_HOME, PATH and ORACLE_SID
SET ORACLE_HOME=C:\oracle\12.1.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\opatch;%PATH%
SET ORACLE_SID=CDB
set PATH=%ORACLE_HOME%\perl\bin;%PATH%
2. Check the compatibility.
Go to patch location
Patch dircectory>opatch prereq CheckConflictAgainstOHWithDetail -ph .
F:\27162953>opatch prereq CheckConflictAgainstOHWithDetail -ph .
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2018, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : C:\oracle\12.1.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
from : n/a
OPatch version : 12.1.0.1.3
OUI version : 12.1.0.2.0
Log file location : C:\oracle\12.1.0\dbhome_1\cfgtoollogs\opatch\opatch2018-02-1
5_00-52-40AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
3. Check the lsinventory
opatch lsinventory
There are no Interim patches installed in this Oracle Home.
4. Check the OPatch utility version 12.1.0.1.2 or later to apply this patch.
opatch version
F:\27162953>opatch version
OPatch Version: 12.1.0.1.3
OPatch succeeded.
5. Take a snapshot of services.msc of Oracle Services in Windows.
Note: it help to remember which services is started or stopped
6. Status of the listener
lsnrctl status
F:\27162953>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 15-FEB-2018 00:55
:05
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ
ction
Start Date 15-FEB-2018 00:54:34
Uptime 0 days 0 hr. 0 min. 31 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File C:\oracle\diag\tnslsnr\WIN-R21P7VEDV43\listener\alert\
log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=WIN-R21P7VEDV43)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=WIN-R21P7VEDV43)(PORT=5500))(Securi
ty=(my_wallet_directory=C:\ORACLE\admin\CDB\xdb_wallet))(Presentation=HTTP)(Sess
ion=RAW))
Services Summary...
Service "CDB" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "CDBXDB" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "pdb3" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
The command completed successfully
7. Take the backup of IC Schema(optional)
run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
backup AS COMPRESSED BACKUPSET format 'C:\patchbackup\rman\full_db_%t_%sp%p' filesperset 5 database;
release channel ch00;
release channel ch01;
release channel ch02;
allocate channel ch00 type disk;
backup format 'C:\patchbackup\rman\cntrl_%s_%p_%t' CURRENT CONTROLFILE;
backup format 'C:\patchbackup\rman\spfile_%s_%p_%t' spfile;
release channel ch00;
}
8. Create PFILE from SPFILE
create pfile='C:\patchbackup\initpfile.ora' from spfile;
9. Check the status of all pluggable database which is open and closed
column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;
CON_ID NAME OPEN_MODE RESTRICTED STATUS
---------- --------------- ---------- ---------- --------
2 PDB$SEED READ ONLY NO NORMAL
3 PDB1 READ WRITE NO NORMAL
4 PDB3 MOUNTED n/a NORMAL
10. Open all the pluggable database.
alter pluggable database all open;
column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;
CON_ID NAME OPEN_MODE RESTRICTED STATUS
---------- --------------- ---------- ---------- --------
2 PDB$SEED READ ONLY NO NORMAL
3 PDB1 READ WRITE NO NORMAL
4 PDB3 READ WRITE NO NORMAL
11. Check the invalid objects present in PDBs & CDB database
Note: CDB view only return value for open pdbs, so all pdb must be open.
select count(*) from cdb_objects where status='INVALID';
Select con_id, count(*) from cdb_objects where status='INVALID' group by con_id;
12. Check the SQL Patch view for patch history
Note: CDB view only return value for open pdbs, so all pdb must be open.
col action_time for a28
col action for a8
col version for a8
col status for a8
col comments for a30
set line 999 pages 999
select con_id, patch_id, version, status, Action, Action_time from cdb_registry_sqlpatch order by action_time;
13. Check the dba_registry components
col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select con_id,comp_id,comp_name,version,status from cdb_registry;
14. Check the database archive mode
archive log list;
15. Shutdown the CDB database and PDBs database
alter pluggable database all close immediate;
column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;
shutdown immediate;
16. Take the services down from services.msc
17. Take the backup of Oracle home directory and Inventory
18. Apply the patch
Go to directory where patch is download from Oracle Support.
Unzip the patch and fire command to apply the patch
Patch directory>opatch apply
If you want to apply OJVM patch then apply the ojvm after window bundle patch:
Patch directory>opatch apply
19. Start the Oracle Service
-- Steps only for apply window bundle patch
startup
alter pluggable database all open;
OR
-- Steps apply for Window Pluse OJVM patch.
startup upgrade
alter pluggable database all open upgrade;
-- Check all pdb is in upgrade state
column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;
CON_ID NAME OPEN_MODE RESTRICTED STATUS
---------- --------------- ---------- ---------- ---------
2 PDB$SEED MIGRATE YES NORMAL
3 PDB1 MIGRATE YES NORMAL
4 PDB3 MIGRATE YES NORMAL
20. Execute the datapatch verbose commands
cd %ORACLE_HOME%/OPatch
datapatch -verbose
---When you execute the command you got the following message: In our case all Pdb is in open state
Connecting to database...OK
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)
Bootstrapping registry and package to current versions...done
Determining current state...done
-- It will install patches on all cdb or pdb databases.
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB1 PDB3
Nothing to roll back
The following patches will be applied:
27162998 (WINDOWS ORACLE JAVAVM COMPONENT BUNDLE PATCH 12.1.0.2.180116 (64
bit): 27162998)
27162953 (WINDOWS DB BUNDLE PATCH 12.1.0.2.180116(64bit):27162953)
---Finally completed
Installing patches...
Patch installation complete. Total patches installed: 6
Validating logfiles...
Patch 27162998 apply (pdb CDB$ROOT): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162998\21845419/27162998_apply_CDB_C
DBROOT_2018Feb16_01_01_14.log (no errors)
Patch 27162953 apply (pdb CDB$ROOT): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162953\21845495/27162953_apply_CDB_C
DBROOT_2018Feb16_01_01_27.log (no errors)
Patch 27162998 apply (pdb PDB$SEED): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162998\21845419/27162998_apply_CDB_P
DBSEED_2018Feb16_01_04_55.log (no errors)
Patch 27162953 apply (pdb PDB$SEED): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162953\21845495/27162953_apply_CDB_P
DBSEED_2018Feb16_01_05_08.log (no errors)
Patch 27162998 apply (pdb PDB1): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162998\21845419/27162998_apply_CDB_P
DB1_2018Feb16_01_05_12.log (no errors)
Patch 27162953 apply (pdb PDB1): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162953\21845495/27162953_apply_CDB_P
DB1_2018Feb16_01_05_35.log (no errors)
SQL Patching tool complete on Fri Feb 16 01:16:22 2018
21. Check the log files for catbundle at following locations
%ORACLE_HOME%\cfgtoollogs\catbundle or %ORACLE_BASE%\cfgtoollogs\catbundle for any errors:
22. Open the CDB & PDB’s database in normal mode:
--CDB
Shutdown immediate
startup
--PDB
alter pluggable database all open;
23. If no warning in opening PDBS then go to next Step
Note: Warning may occurred during opening of PDB which may be closed at time of database verbose command
SQL> alter pluggable database all open;
Warning: PDB altered with errors.
column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED"
, d.status from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;
CON_ID NAME OPEN_MODE RESTRICTED STATUS
---------- --------------- ---------- ---------- ---------
2 PDB$SEED READ ONLY NO NORMAL
3 PDB1 READ WRITE NO NORMAL
4 PDB3 READ WRITE YES NORMAL
Note: PDB3 is open in restricted mode
We can check with following view about error or action need to take:
select message,action from pdb_plug_in_violations;
Message Action
--------------------------------------------------------------------------------
PSU bundle patch 180116 (WINDOWS DB BUNDLE PATCH 12.1.0.2.180116(64bit):27162953
): Installed in the CDB but not in the PDB.
Call datapatch to install in the PDB or the CDB
SQL patch ID/UID 27162998/21845419 (WINDOWS ORACLE JAVAVM COMPONENT BUNDLE PATCH
12.1.0.2.180116 (64bit): 27162998): Installed in the CDB but not in the PDB.
Call datapatch to install in the PDB or the CDB
Note: Action output is in bold statement asking to rerun the datapatch verbose command for PDB3 database.
Action Taken for fixed pluggable database issue
1. Open the PDB3 in upgrade mode
2. Rerun the datapatch -verbose commands
3. open the PDB3 in normal mode.
24. Check the invalid objects in all PDBs & CDB and compile invalid objects
Note: CDB view only return value for open PDBs, so all PDBs must be open.
cd %ORACLE_HOME%\rdbms\admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql
select count(*) from cdb_objects where status='INVALID';
Select con_id, count(*) from cdb_objects where status='INVALID' group by con_id;
Note: From CDB execution it will fixed all CDB invalid objects, For pdb run it from any one pdb it will fixed all invalid objects of PDBs.
25. Check the database registry view
Note: CDB view only return value for open PDBs, so all PDBs must be open.
col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select con_id,comp_id,comp_name,version,status from cdb_registry;
26. Check the registry$history view
Note: CDB view only return value for open PDBs, so all PDBs must be open.
col action_time for a28
col action for a8
col version for a8
col status for a8
col comments for a30
set line 999 pages 999
select con_id, patch_id, version, status, Action, Action_time from cdb_registry_sqlpatch order by action_time;
select patch_id, patch_uid, version, status, description from cdb_registry_sqlpatch;
select patch_id, version, status, Action, Action_time from cdb_registry_sqlpatch order by action_time;
27. Check the services of oracle and windows is started.
28. Check the listener connectivity
sqlplus sys@cdb as sysdba
sqlplus sys@pdb1 as sysdba
sqlplus sys@pdb2 as sysdba
29. Check the Opatch lsinventory
30. Listener status checked blocked or open
lsnrctl status
Smart way of Technology 
This was a very helpful article! Thanks!
LikeLike
Very nice you really solved my issue thanks very much
LikeLiked by 1 person
Thanks
LikeLike