Container and Pluggable database Patching Process

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 lsinventor

30. Listener status checked blocked or open

lsnrctl status

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.