19c Database Patching Steps

By -

1. Download pacthes

2. Patch Alasysis

3. Conflicts check and system space check 

4. Backups (GI Home, DB Home and Database)

5. Apply 

6. Post checks

Download the Patches:-

Search the below keyword in metalink to download the CPU Patches

Critical patch Update (CPU) Program JAN 2022

Critical patch Update (CPU) Program Apr 2022  

Critical patch Update (CPU) Program JUL 2022  

Critical patch Update (CPU) Program OCT 2022



I opened for the July patch, and here we need to click Patch availability for Oracle products -> 3.1 Oracle database.


Oracle database (3.1) Many Oracle products patches are listed here. I need only Oracle database patches. So, I'll go with the Oracle database 3.1.7



My DB version is 19c So I will choose 3.1.7.3 Oracle database 19c



Here I will download the below database patch and grid patch (GI)

One interesting fact is that if you download the GI Patch Database patch, it will also come under GI Patch. As a result, there is no need to download a database patch separately.

Note: If you only apply the patch in Oracle Home, you must download the database patch. Database Release Update 19.16.0.0.220719 Patch 34133642 

GI Release Update 19.16.0.0.220719 Patch 34130714,



Captured below screenshot for GI Patch (34130714) read me document. In this screenshot database patch also come under single GI patch



Download the Latest Opatch

Current OPatch version is 12.2.0.1.17

[oracle@oraclelab1 grid]$  OPatch/opatch version

OPatch Version: 12.2.0.1.17

OPatch succeeded.

[oracle@oraclelab1 grid]$

p6880880_122010_Linux-x86-64.zip


Using below commands to find the applied Patches:

$ORACLE_HOME/OPatch/opatch lspatches

$ORACLE_HOME/OPatch/opatch lsinventory :- It will show all applied patch list


Conflicts Check and System Space Check 

Conflicts check and system space check commands are same for both GI Home and Oracle Home

For conflict check if any patch is conflicts we need to work with Oracle and resolve the issue.

Go to ASM Home and Run Conflicts check and Space scheck

[oracle@oraclelab2 patches]$ . oraenv

ORACLE_SID = [TESTCDB] ? +ASM

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oraclelab2 patches]$ 

Sample Output for the Conflicts check:

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/34155589/34172231

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/34155589/34160444

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/34155589/34172227


Sample Output for Space Check

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/33509923/33575402
$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/33509923/33534448
$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/33509923/33515361



ASM Patch(Automated) Steps:

ASM Patch fully automated via OPatch auto command is automated everything. Once we executed the Patch via Opatch auto and it will shutdown the database listner and ASM and apply the patches in Grid Home+Database Home+Datapatch and start the database listner and ASM instance.

Note:- Opatch auto need to execute root user only

ASM Using Opatchauto High level patching steps:

Login the root user

Check the permissions for the Patch 

Export the envirnoment variable 

Sourece the ASM 

Apply the Patch

root@oraclelab2 ~]# oraenv

ORACLE SID = [root] ? +ASM

The Oracle base has been set to /u01/app/oracle


export ORACLE_HOME=<ORACLE_HOME>
export ORACLE_SID=+ASM
export PATH=$PATH:$ORACLE_HOME/OPatch

[root@oraclelab2 ~]# /u01/app/19.0.0.0/grid/0Patch/opatchauto apply /u01/patches/33509923


ASM Using Opatch High level patching steps:

Stop Database+Listner+GI Home 

Grant permission patch directory 
 
OPatch version and current lspatches checks

Conflict pre checks

System space pr-check

Apply Patch

Post verification
 
Start Database+Listner+GI Home 


DataBase Patch 

Stop Database+Listner

Grant permission patch directory 
 
OPatch version and current lspatches checks

Conflict pre checks

System space pr-check

Apply Patch

Post verification
 
Start Database+Listner+GI Home 


Opatch Apply steps:-

cd /u01/patches/35943109
$ORACLE_HOME/OPatch/opatch apply

cd /u01/patches/35648918
$ORACLE_HOME/OPatch/opatch apply

Check logs for issues.

6. Check 1sinventory for patch applied

$ORACLE_HOME/OPatch/opatch 1spatches | grep -i 35943109
$ORACLE_HOME/OPatch/opatch 1spatches grep i 35648918


Common steps for ASM & DB Patching 

After applied the Patch check the registery all are valid or not. If any one of the component invalid need to work with oracle.


Run the database post steps – datapatch

Verify the database post steps

cd $ORACLE_HOME/OPatch

./datapatch -verbose


DBA_REGISTRY_SQLPATCH table contains information about the SQL patches that have been installed in the database.

A SQL patch is a patch that contains SQL scripts which need to be run after OPatch completes. DBA_REGISTRY_SQLPATCH is updated by the datapatch utility. Each row contains information about an installation attempt (apply or roll back) for a given patch.

set lines 132
col DESCRIPTION for a50
col ACTION_TIME for a30
col ACTION for a10
col STATUS for a10
select PATCH_ID, PATCH_UID, ACTION, STATUS, ACTION_TIME, DESCRIPTION from dba_registry_sqlpatch;

Check the invalid objects


Frequently Using Opatch Commands

Find the applied Patches:

$ORACLE_HOME/Opatch/opatch lspatch

Find the Inventory Details

$ORACLE_HOME/OPatch/opatch lsinventory

Find the Optach Version

$ORACLE_HOME/OPatch/opatch version

Rollback the Patch

$ORACLE_HOME/OPatch/opatch rollback -id 12345678 

Opatch Frequently asked Questions:

What is the difference between opatch apply Vs opatchauto?

Opatch apply: Its manual method we need to shutdown ASM,DB&Listner and apply the patch and start the database

Opatch Auto: Its automated it will shutdown ASM,DB&Listner and apply the Grid home patch+Database Home Patch + Datapatch and start the database it self.

What is the difference Opatch and Opatch napply?

Opatch apply: Its used to apply the singal epatch

Opatch napplay:- Its used to apply multiple patches at same time

$ORACLE_HOME/OPatch/opatch napply -id 15941858,15955138

Multiple database running on single oracle home need to apply datapatch in all database?

Yes, need to apply all database for datapatch for binary level we applied onetime patch If we have 5 database running in single oracle home need to apply datapatch in eash database

Datapatch is online activity we can run anytime no need down time. 


Need to apply datapatch in Dataguared envirnment?

No need to apply datapatch in DG because standby database in munted mode not be in open mode

Can we patch standby DB first or primary DB first? Or patch both together? Which best practice?

We need to apply patch standby database first

What is central Inventory and Local Inventory?


How to find locations of Central Inventory and Local Inventory?

Local Inventry: The inventory directory inside the oracle home called local inventory
inventory_loc=/u01/app/oraInventory

Central Inventory:Global Inventory directory outside of the oracle_home called local inventory
Physical Location /var/opt/oraInst.loc, /etc/oraInst.loc (AIX,Linux)


Comments

Post a Comment

Popular posts from this blog

How to troubleshoot long running concurrent request in R12.2

By -
Normally we are getting concurrent program running longer and concurrent request not picking jobs, etc. those are very frequent issue we expected from development team. Collect below basis level of information to development team 1 Oracle seeded program/Custom program? 2 How much time it used earlier? 3 Is there any recent code change done in concurrent program? 4 Is this program fetching higher data compare to last run? 5 Does this job running any specific time/ It can be run any time? 6 Does this job fetching data using DB link? 7 Does the problem happen on both the test and production instance? Once asked above questions to development team in meanwhile we need to start basic sanity check in our environment. 1 Verify the status of the concurrent program select REQUEST_ID,phase_code,status_code,ORACLE_SESSION_ID from apps.fnd_concurrent_requests where request_id=’1234567’; 2 Find which concurrent manger ran a specific concurrent request select b.USER_CONCURR
Read more

How to run Gather Schema Statistics in R12.2

By -
Image
What is Gather Schema Statistics? Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions. The cost-based optimization (CBO)  uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan. Why Run the Gather Schema Statistics? When Data is update, insert, delete in table of user end Like (Technical User ,Finance User ,Ect) It become necessary to run the Gather Schema Statistics It’s recommended to Run GSS in Weekly Once or twice. How to run Gather Schema Statistics? Login application with sysadmin user Submit Request Window Navigate to: Concurrent > Requests Enter the parameters This can be run for specific schemas by specifying the schema name or enter ‘ALL’ to gather statistics for every schema in the database Submit the Gather Schema Statistics program Parameters: Schem
Read more

How to compile forms in R12.2

By -
STEP 1--> Login to app server STEP 2--> Go to  directory $AU_TOP/forms?US STEP 3--> Move to ".fmb" file STEP 4--> Execute the below command to generate ".fmx" frmcmp_batch userid=apps/<apps_pwd> module=<form_name>.fmb output_file=<form_name>.fmx module_type=form batch=no compile_all=special (EX):-   frmcmp_batch userid=apps/apps module=JAINTAXT.fmb output_file=$JA_TOP/forms/US/JAINTAXT.fmx module_type=form batch=no compile_all= special (O/p):   Created form file /oracle/CLONE/apps/fs2/EBSapps/appl/ja/12.0.0/forms/US/ JAINTAXT.fmx Refer: R12: How to Compile a Form in Release 12 (Doc ID 1085928.1)
Read more