################################################################
Acquired Skills:
################################################################
-> Oracle Home "clone" steps on some server
-> Oracle Home RU patching
################################################################
DOCUMENTS:
################################################################
Non-RAC : How to Perform out of Place Patching to Minimize DB Downtime (Doc ID 1389364.1)
Non-RAC: A Technique To Minimize Database Downtime When Patching (Doc ID 1390066.1)
RAC example ===> https://unknowndba.blogspot.com/2019/09/out-of-place-rolling-patching-aka.html
################################################################
ACTION PLAN:
################################################################
[+] Clone Current Oracle Home
[+] Patch New Oracle Home
[+] Stop services running from old home
[+] Update your env files AND "/etc/oratab"
[+] Start services from new home
[+] Perform database post-patching steps (if required according to patch readMe file)
[+] (OPTIONAL) Patch old home to same level as new one /// Or just remove old home
################################################################
WORK:
################################################################
Current Oracle Home ===> /u01/app/oracle/product/19.0.0/dbhome_1
New Oracle Home ===> /u01/app/oracle/product/19.0.0/dbhome_2
@@@@@@@@@@@@@@@@@@@@@@@@@@@
Clone Current Oracle Home:
@@@@@@@@@@@@@@@@@@@@@@@@@@@
>>> oracle@srv-example-db.oracle.com
$ cp -r /u01/app/oracle/product/19.0.0/dbhome_1 /u01/app/oracle/product/19.0.0/dbhome_2
$ cd /u01/app/oracle/product/19.0.0/dbhome_2
$ ./runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile /u01/app/oracle/product/19.0.0/dbhome_2/install/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en,en_GB \
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_2 \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
>>> root@srv-example-db
$ /u01/app/oracle/product/19.0.0/dbhome_2/root.sh
$ cat /u01/app/oraInventory/ContentsXML/inventory.xml
++++++++++
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2020, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>12.2.0.7.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDB19Home1" LOC="/u01/app/oracle/product/19.0.0/dbhome_1" TYPE="O" IDX="1"/>
<HOME NAME="OraDB19Home2" LOC="/u01/app/oracle/product/19.0.0/dbhome_2" TYPE="O" IDX="2"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
++++++++++
@@@@@@@@@@@@@@@@@@@@@@@@@@@
Patch New Oracle Home:
@@@@@@@@@@@@@@@@@@@@@@@@@@@
>>> oracle@srv-example-db
$ cat .bash_profile
++++++++++
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
home1() {
# User specific environment and startup programs
export ORACLE_SID=ORCL
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export ORACLE_HOSTNAME=srv-example-db.oracle.com
export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export PS1='\[\033[0;32m\]$ORACLE_SID> \[\033[0;33m\]\u@\h\[\033[00m\] [\t] \w]\$ '
##
## Tools
##
alias rman="rlwrap ${ORACLE_HOME}/bin/rman"
alias sp="rlwrap ${ORACLE_HOME}/bin/sqlplus / as sysdba"
}
home2() {
# User specific environment and startup programs
export ORACLE_SID=ORCL
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_2
export ORACLE_HOSTNAME=srv-example-db.oracle.com
export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export PS1='\[\033[0;32m\]$ORACLE_SID> \[\033[0;33m\]\u@\h\[\033[00m\] [\t] \w]\$ '
##
## Tools
##
alias rman="rlwrap ${ORACLE_HOME}/bin/rman"
alias sp="rlwrap ${ORACLE_HOME}/bin/sqlplus / as sysdba"
}
++++++++++
$ home2
$ opatch lspatches
+++++
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
OPatch succeeded.
+++++
//
// Upgrade OPatch
//
$ opatch version
+++++
OPatch Version: 12.2.0.1.17
OPatch succeeded.
+++++
$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch_bkp_`date +%F`
$ unzip /u01/soft/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME/
$ ls -ld $ORACLE_HOME/OPatch*
+++++
drwxr-x---. 14 oracle oinstall 4096 Dec 18 07:56 /u01/app/oracle/product/19.0.0/dbhome_2/OPatch
drwxr-x---. 14 oracle oinstall 4096 Mar 9 07:58 /u01/app/oracle/product/19.0.0/dbhome_2/OPatch_bkp_2020-03-09
+++++
$ opatch version
+++++
OPatch Version: 12.2.0.1.19
OPatch succeeded.
+++++
//
// Patch 30557433: DATABASE RELEASE UPDATE 19.6.0.0.0
//
$ {
cd /u01/soft
unzip p30557433_190000_Linux-x86-64.zip
cd 30557433
}
$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
+++++
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2020, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_2
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.0.0/dbhome_2/oraInst.loc
OPatch version : 12.2.0.1.19
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_2/cfgtoollogs/opatch/opatch2020-03-09_08-48-21AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
+++++
$ opatch apply
+++++
Patch 30557433 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [30557433].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_2/cfgtoollogs/opatch/opatch2020-03-09_08-48-45AM_1.log
OPatch succeeded.
+++++
$ egrep -i "error|fail|ora-|ac-|rc-" /u01/app/oracle/product/19.0.0/dbhome_2/cfgtoollogs/opatch/opatch2020-03-09_08-48-45AM_1.log
//
// Patch 30484981: OJVM RELEASE UPDATE 19.6.0.0.0
//
{
cd /u01/soft
unzip p30484981_190000_Linux-x86-64.zip
cd 30484981
}
$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
+++++
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2020, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_2
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.0.0/dbhome_2/oraInst.loc
OPatch version : 12.2.0.1.19
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_2/cfgtoollogs/opatch/opatch2020-03-09_09-13-07AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
+++++
$ opatch apply
+++++
Patch 30484981 successfully applied.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_2/cfgtoollogs/opatch/opatch2020-03-09_09-13-57AM_1.log
OPatch succeeded.
+++++
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Stop services running from old home:
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
>>> oracle@srv-example-db
$ home1
$ sqlplus / as sysdba
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Update your env files AND "/etc/oratab":
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
>>> oracle@srv-example-db
$ cat /etc/oratab | grep -i orcl
+++++
#ORCL:/u01/app/oracle/product/19.0.0/dbhome_1:N
ORCL:/u01/app/oracle/product/19.0.0/dbhome_2:N
+++++
$ vi ~/.bash_profile
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Start services from new home:
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
>>> oracle@srv-example-db
$ home2
$ sqlplus / as sysdba
SQL> startup;
ORACLE instance started.
Total System Global Area 1.2583E+10 bytes
Fixed Size 8910496 bytes
Variable Size 1744830464 bytes
Database Buffers 1.0805E+10 bytes
Redo Buffers 24641536 bytes
Database mounted.
Database opened.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Post-patching steps (if required according to patch readMe file):
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
>>> oracle@srv-example-db
$ sqlplus / as sysdba
SQL> set lines 222 pages 999;
col COMMENTS format a60;
col ACTION format a11;
col VERSION format a15;
col NAMESPACE format a10;
col BUNDLE_SERIES format a10;
col ACTION_TIME format a35;
select * from registry$history
order by ACTION_TIME;
++++++++++
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SER
----------------------------------- ----------- ---------- --------------- ---------- ------------------------------------------------------------ ----------
01-MAR-20 09.35.10.332371 AM RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0: Release_Update - 190410122720
BOOTSTRAP DATAPATCH 19 RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
++++++++++
$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose
++++++++++
SQL Patching tool version 19.6.0.0.0 Production on Mon Mar 9 09:29:05 2020
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_9179_2020_03_09_09_29_05/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 30484981 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981)):
Binary registry: Installed
SQL registry: Not installed
Current state of release update SQL patches:
Binary registry:
19.6.0.0.0 Release_Update 191217155004: Installed
SQL registry:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 01-MAR-20 09.35.20.489766 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
No interim patches need to be rolled back
Patch 30557433 (Database Release Update : 19.6.0.0.200114 (30557433)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.6.0.0.0 Release_Update 191217155004
The following interim patches will be applied:
30484981 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981))
Installing patches...
Patch installation complete. Total patches installed: 2
Validating logfiles...done
Patch 30557433 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30557433/23305305/30557433_apply_ORCL_2020Mar09_09_31_04.log (no errors)
Patch 30484981 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30484981/23248235/30484981_apply_ORCL_2020Mar09_09_29_51.log (no errors)
SQL Patching tool complete on Mon Mar 9 09:36:53 2020
++++++++++
$ sqlplus / as sysdba
SQL> set lines 222 pages 999;
col COMMENTS format a75;
col ACTION format a11;
col VERSION format a22;
col NAMESPACE format a10;
col BUNDLE_SERIES format a5;
col ACTION_TIME format a35;
select * from registry$history
order by ACTION_TIME;
++++++++++
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDL
----------------------------------- ----------- ---------- ---------------------- ---------- --------------------------------------------------------------------------- -----
01-MAR-20 09.35.10.332371 AM RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0: Release_Update - 190410122720
09-MAR-20 09.31.04.535922 AM jvmpsu.sql SERVER 19.6.0.0.200114OJVMRU 0 RAN jvmpsu.sql
09-MAR-20 09.31.04.578736 AM APPLY SERVER 19.6.0.0.200114OJVMRU 0 OJVM RU post-install
09-MAR-20 09.35.53.850255 AM RU_APPLY SERVER 19.0.0.0.0 Patch applied from 19.3.0.0.0 to 19.6.0.0.0: Release_Update - 191217155004
BOOTSTRAP DATAPATCH 19 RDBMS_19.6.0.0.0DBRU_LINUX.X64_191217
++++++++++
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[+] (OPTIONAL) Patch old home to same level as new one /// Or just remove old home:
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DONE.
################################################################
Useful commands for DB patching:
################################################################
//
// Get current list of paches
//
$ opatch lspatches
$ opatch lsinventory | grep -i <patch/bug number>
//
// Check patch for conflicts
//
cd <patch_directory>
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
//
// SQL query from DB registry to get list of installed patches
//
SQL> set lines 222 pages 999;
col COMMENTS format a60;
col ACTION format a11;
col VERSION format a15;
col NAMESPACE format a10;
col BUNDLE_SERIES format a10;
col ACTION_TIME format a35;
select * from registry$history
order by ACTION_TIME;
//
// Get errors/failers from patch log
//
$ egrep -i "error|fail|ora-|ac-|rc-" /u01/app/oracle/product/19.0.0/dbhome_2/cfgtoollogs/opatch/??????.log