Oracle In Depth: Apply PSU patch in 11g Non RAC

For pSUjan'17: Chang permission as chmod 644 $ORACLE_HOME/lib/libsqlplus.so

uname -a Verify os version and flavour

$OH/OPatch/opatch -detail | grep rollback Check if PSU or CPU home

[1] Take crontab backup & Comment crontab.

crontab -l > crontab_bkup

crontab -e

:%s/^/##CHG000004513991##/g --- to comment

:%s/##CR##//g --- to uncomment

:%s/rm /rm -r /g --- to uncomment

Bug Patch location:

cd /orasoft/stage/linux_x86_64bit/patches_112043/25188350

@?/sqlpatch/25188350/postinstall.sql

[2] Disable monitoring. (/var/opt/oracle or oracle/local/etc) -----for prod Database only

[3] Know the OS info

Goto: cd /orasoft/local/contrib or /orasoft/local/bin

$ ./osinfo

cd $OH/bin then

getconf -a|grep -i kernel

Vendor Name Level Bits CPU Sockets Cores Threads

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

Oracle SunOS SunOS 5.10 (Solaris 10 1/13 Update 11) 64 sparc 4 32 256

[4] Make the Directory on the mountpoint that has sufficient free space.

[5] Know the previous patchset

$export PATH=$ORACLE_HOME/OPatch:$PATH - opatch rollback -id 15941858

$opatch lsinventory

opatch lsinv>opatch_BKP

$OH/OPatch/opatch lsinv |grep applied

[6] DB Precheckcd ..

alter session set NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS";

set lines 300;

set pages 300;

col DB_UNIQUE_NAME for a18;

col host_name for a10

select distinct instance_name,name,open_mode,log_mode,logins,host_name,startup_time,database_role from gv$database,gv$instance;

ON PDB

set lines 250 pages 250

col DEST_NAME for a20

col ERROR for a10

col GAP_STATUS for a10

col RECOVERY_MODE for a10

col DB_UNIQUE_NAME for a15

select DEST_ID,DEST_NAME,STATUS,TYPE,DATABASE_MODE,RECOVERY_MODE,ARCHIVED_SEQ#,APPLIED_SEQ#,ERROR,DB_UNIQUE_NAME,SRL,GAP_STATUS

from v$archive_dest_status where STATUS<>'INACTIVE';

SQL> spool precheck_PSUAPRL2018.log

set echo on

col host_name for a15

select name, open_mode,host_name,log_mode from v$database,v$instance;

select * from v$version;

show parameter fal;

show parameter archive_config;

col owner for a15

col object_name for a35

select count(*) from dba_objects where status='INVALID';

select count(*), status from dba_objects group by status;

select OWNER,OBJECT_NAME,OBJECT_TYPE,status from DBA_OBJECTS where status = 'INVALID';

select owner,count(1) from dba_objects where status='INVALID' group by owner;

select * from v$backup

set lines 2000

col COMMENTS for a20

col ACTION for a10

col ACTION_TIME for a30

col NAMESPACE for a10

col BUNDLE_SERIES for a20

col VERSION for a10

select * from registry$history;

--------------------------------------------- for 12c

select patch_id, action, action_time, description, status, version, bundle_series from registry$sqlpatch;

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

col COMP_NAME for a40

col VERSION for a20

col STATUS for a10

set lines 150

select comp_name,version,status from dba_registry;

select * from dba_tab_privs where table_name in ('UTL_FILE','UTL_TCP','UTL_SMTP','UTL_HTTP','DBMS_RANDOM') and grantee='PUBLIC';

spool off

note(6:27:35 AM): sudo-l|grep free - if you get any output then first freeze cluster before shutdown services and once patch applied ,please unfreeze cluster

note(6:27:42 AM): sudo-l|grep free -

note(6:27:45 AM): sudo-l|grep free

note(6:28:30 AM): sudo -l|grep free

sudo /opt/VRTSvcs/bin/hastatus -sum

sudo /opt/VRTSvcs/bin/hastatus -sum |grep p0csi2d2

sudo -l | grep free to check freezed and Unfreed command and SG name.

To Freeze Cluster use

sudo /opt/VRTSvcs/bin/hagrp -freeze <SG name>

note(6:28:49 AM): sudo -l|grep free

sudo /opt/VRTSvcs/bin/hastatus -sum

sudo /opt/VRTSvcs/bin/hastatus -sum |grep p0csi2d2

To unFreeze Cluster use

sudo /opt/VRTSvcs/bin/hagrp -unfreeze <SG name>

note(6:29:02 AM): p0csi2d2

note(6:29:35 AM): select owner, TRIGGER_NAME , status from dba_triggers where TRIGGER_NAME like ('%GGS%') order by 1;

note(6:29:53 AM): disable Trigger

set lines 200

select owner, TRIGGER_NAME , status from dba_triggers where TRIGGER_NAME like ('%GGS%') order by 1;- Output Trigger needs to disabled.Below only for example

alter trigger GGSUSER.GGS_LAGTIME_TR1 disable ;

alter trigger GGSUSER.GGS_LAGTIME_TR2 disable;

alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable;

note(6:30:30 AM): who

[7] Stop the GGS and Agent

ps -ef | grep ggs

select owner, TRIGGER_NAME , status from dba_triggers where TRIGGER_NAME like ('%GGS%') order by 1; -- Disable trigger If any

ps -ef | grep emagent

cd $GGS_HOME

ggsci> info all

ggsci> stop *

ggsci> stop mgr

emctl stop agent

lsnrlist

[8] Shutdown Database and stop listener

[8A] Check the full backup is successfull or not otherwise take full db backup /opt/app/hostname/oraflsh01/Precheck_PSUJAN2017

[9] Take the Binary Backup

tar -cvf - 11.2.0.4 | gzip -c > /pctms/oraflsh/fs01/PSUOCT2017/11.2.0.4.tar.gz

tar -cvf - 11.2.0.4 | gzip -c > /opt/app/hostname/oraflsh01/PSUOCT2017/11.2.0.4.tar.gz

tar -cvf - 11.2.0.4 | gzip -c > /opt/app/p1crt1d2/oraflsh01/PSUAPRL2018/11.2.0.4.tar.gz

tar -cvf - 11.2.0.4 | gzip -c > /opt/app/p1crt1d4/oraflsh01/PSUAPRL2018/11.2.0.4.tar.gz

tar -cvf - 11.2.0.4 | gzip -c > /opt/app/p1crt1d5/oraflsh01/PSUAPRL2018/11.2.0.4.tar.gz

[10] Copy the patch from orasoft to the local directory.

cd /orasoft/stage/linux_x86_64bit/psu_112040/24918033 ---- PSUJAN17

cd /orasoft/stage/linux_x86_64bit/psu_112040/24436313 ---- PSUOCT16

cd /orasoft/stage/solaris_sparc_64bit/psu_112040/24918033 ---- PSUJAN17 for solaris

cp -rf 20132580 /pac/oroarp1/data/orabkup01/CHG000003152288

[11] Go through Readme.html

cd 20132580

cat readme.html

copy and save as .html and follow the steps.

[12] Determine whether any currently installed one-off patches conflict with the PSU patch as follows

opatch prereq CheckConflictAgainstOHWithDetail -ph ./

[13] Apply patch (PSU AND OJVM AT BINARY LEVEL).

>opatch apply----Once For PSU(From PSU Patch Location)

>opatch apply----Once for OJVM(From OJVM Patch location)

[14] for OJVM patch(At DB Lavel)

cd $ORACLE_HOME/sqlpatch/26635834

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> Startup Upgrade

SQL> @postinstall.sql

SQL> shutdown

SQL> startup

[15] for PSU patch(At DB Lavel)

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> @catbundle.sql psu apply

SQL> Shut Immediate

SQL> STARTUP

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> @utlrp.sql - instead run compile script, dont run utlrp

[16] Verify if the patching is done successfully on binary and DB level

check dba_registery and registery$history

opatch lsinv

[17] Uncomment crontab and enable patrol.

crontab crontab_bkup

/orasoft/stage/solaris_sparc_64bit/OPatch/11.2.0.3.6/opatch - IF opatch version not working

COMBO PATCH LOCATION: PSUAPRL17

cd /orasoft/stage/linux_x86_64bit/psu_112040/25440428 -for linux

cd /orasoft/stage/aix_64bit/psu_112040/25440428 - For AIX 64and 32 bit

cd /orasoft/stage/solaris_sparc_64bit/psu_112040/25440428 -for SUn SOlaris-64bit

cd /orasoft/stage/hpux_itanium_64bit/psu_112040/25440428 -For HP-UX Itanium

11.2.0.4 - Recommended Option - PSU / OJVM Combo with JDBC COMBO PSU OJVM

Combo OJVM PSU 11.2.0.4.170418 and Database PSU 11.2.0.4.170418 25440428

Database PSU 11.2.0.4.170418 24732075

Oracle JavaVM (OJVM) Component Database PSU 11.2.0.4.170418 Patch 25434033 for UNIX 25434033

COMBO DIRECTORY - 25440428 opatch prereq CheckConflictAgainstOHWithDetail -ph ./

OJVM PATCH - 25434033

PSUOCT2016 - 24732075

/orasoft/stage/linux_x86_64bit/psu_112040/25440428/24732075

/orasoft/stage/linux_x86_64bit/psu_112040/25440428/25434033

*******************************

PSU july2017

cd /orasoft/stage/solaris_sparc_64bit/psu_112040/26031209 -Solaris sparc64bit

COMBO DIRECTORY - 26031209

OJVM PATCH - 26027154

PSU PATCH - 25869727

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++++ ++++

++++ IT HAS BEEN IDENTIFIED THAT PSUOCT2016 HAS A BUG THAT REMOVES READ PERMISSIONS FROM $OH/LIB/LIBSQLPLUS.so ++++

++++ ++++

++++ THIS BUG WAS REPEATED IN PSUJAN2017 (For 12.1.0.2) and in PSUAPR2017 (BOTH 11.2.0.4 and 12.1.0.2) ++++

++++ ++++

++++ SEE MOS NOTE: ++++

++++ Applying Oct2016 DBPSU 11.2.0.4.161018 or 12.1.0.2.161018 Changes Permission OF ++++

++++ $OH/LIB/LIBSQLPLUS.SO FROM 644 TO 640 (Doc ID 2201729.1) ++++

++++ ++++

++++ THESE PATCHING NOTES CONTAIN A FINAL STEP TO APPLY THE PATCH TO RESOLVE THIS PERMISSION ISSUE BUG ++++

++++ ++++

++++ NOTE: ALTHOUGH THE PATCH IS LISTED AS A PSUOCT2016 FIX, IT IS COMPATIBLE WITH PSUJAN2017 & PSUAPR2017 ++++

++++ ++++

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

12.1.0.2 Recommended Option - PSU / OJVM Combo with JDBC COMBO PSU OJVM

Combo OJVM PSU 12.1.0.2.170418 and Database PSU 12.1.0.2.170418 25433980

Database PSU 12.1.0.2.170418 25171037

Oracle JavaVM Component Database PSU 12.1.0.2.170117 25437695 for UNIX 25437695

COMBO PATCH 25433980

OJVM 25437695

PSU 25171037

cd /orasoft/stage/linux_x86_64bit/psu_121020/25433980

==================================================

PSU OCT2017 for 12C:

COMBO PATCH 26636270

OJVM 26635845

PSU 26713565

cd /orasoft/stage/linux_x86_64bit/psu_121020/26636270

==============================================

== ==

== 12.1.0.2 PSUAPR2017 / OJVM COMBO PATCH ==

== RedHat Linux 12.1.0.2.0 ==

== PSU VERSION 12.1.0.2.170418 ==

== ==

== THESE NOTES INCLUDE A MANUAL APPLY ==

== OF THE RMAN PERMISSION FIX PATCH ==

== ==

== 20713578 - VERSION 12.1.0.2.170418 ==

== ==

==============================================

Error::: Code -73

The Content of respective log file is as below.

[Oct 22, 2012 2:10:20 PM] Finish fuser command /sbin/fuser /u00/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1 at Fri Oct 22 14:10:20 CET 2012

[Oct 22, 2012 2:10:20 PM] Following executables are active :

/u00/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1

[Oct 22, 2012 2:10:20 PM] Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

Following executables are active :

/u00/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1

[Oct 22, 2012 2:10:20 PM] OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

[Oct 22, 2012 2:10:20 PM] Finishing UtilSession at Fri Oct 22 14:10:20 CET 2012

Solution Description

The problem is because of the library file (libclntsh.so.11.1) is not released by some process. Most probably it will EM console. Either you can stop emctl

using following command or kill the process which is holding the library file. You can find out the process which holding the lib file using /sbin/fuser

command.

Option 1: emctl stop

Option 2:

$ /sbin/fuser /u00/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1

/u00/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1: 4212m

$ kill -9 4212

Now you can proceed with patching.

================================================================================================================================

If XDB component get invalid:

1) first grant execute permission to xdb on sys.utl_file

sql>grant execute on sys.utl_file to xdb;

2)sql>@?/rdbms/admin/utlrp.sql

If it works then ok, otherwise need to remove and install again

==========================

If CATJAVA component get invalid:

CATJAVA Oracle Database Java Packages 11.2.0.4.0 INVALID

execute sys.dbms_registry.loaded('CATJAVA');
execute sys.dbms_registry.valid('CATJAVA');

=========================

$ sqlplus
ld.so.1: sqlplus: fatal: libclntsh.so.12.1: open failed: No such file or directory

oracle@node1:/opt/app/oracle/product/12.1.0.2/lib
$ ls -ltr libclntsh.so.12.1
libclntsh.so.12.1: No such file or directory

always copy libclntsh.so.12.1 cp $OH/lib and cp $OH/bin before patch and upgrade in SunOS.

solution : copy libclntsh.so.12.1 from other env .

=====================

$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Thu Nov 19 08:43:20 2020
Copyright (c) 2012, 2016, Oracle. All rights reserved.

Log file for this invocation: /opt/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18731_2020_11_19_08_43_20/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done

Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'
and/or check the invocation log
/opt/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18731_2020_11_19_08_43_20/sqlpatch_invocation.log
for the complete error.
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/opt/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18731_2020_11_19_08_43_20/sqlpatch_invocation.log
for information on how to resolve the above errors.

Solution :

unset LD_LIBRARY_PATH_64
export LD_LIBRARY_PATH=$OH/lib:$LD_LIBRARY_PATH

PATH=$PATH:$ORACLE_HOME/OPatch

=============================

getting error while applying patch on another home on the same host

$ /orasoft/stage/linux_x86_64bit/OPatch/11.2.0.3.21/opatch prereq CheckConflictAgainstOHWithDetail -invPtrLoc $ORACLE_HOME/oraInst.loc -phBaseDir ./30670774
Oracle Interim Patch Installer version 11.2.0.3.21
Copyright (c) 2021, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home       : /opt/app/prod_instance/oracle/product/prod_instance
Central Inventory : /var/opt/oracle/oraInventory
   from           : /opt/app/prod_instance/oracle/product/prod_instance/oraInst.loc
OPatch version    : 11.2.0.3.21
OUI version       : 11.2.0.4.0
Log file location : /opt/app/prod_instance/oracle/product/prod_instance/cfgtoollogs/opatch/opatch2021-01-05_05-17-32AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"
List of Homes on this system:

Home name= oraclehome2, Location= "/opt/app/node1/oracle/product/agent13c/GoldImage_v2/agent_13.2.0.0.0"
Home name= 11204node1, Location= "/opt/app/node1/oracle/product/11.2.0.4"
List of Homes on this system:

$ more /etc/oraInst.loc
inventory_loc=/var/opt/oracle/oraInventory
inst_group=oinstall

$ cd ContentsXML
prod_instance@PRD:node1:prod_instance:(17) /var/opt/oracle/oraInventory/ContentsXML
$ ls -ltr
total 12
-rw-rw---- 1 node1 oinstall 702 Mar 25 2020 inventory.xml
-rw-rw---- 1 node1 oinstall 292 Jan 5 04:38 libs.xml
-rw-rw---- 1 node1 oinstall 329 Jan 5 04:38 comps.xml
prod_instance@PRD:node1:prod_instance:(18) /var/opt/oracle/oraInventory/ContentsXML
$ more inventory.xml
<?xml version="1.0" standalone="yes" ?>


<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>13.9.1.0.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="oraclehome2" LOC="/opt/app/node1/oracle/product/agent13c/GoldImage_v2/agent_13.2.0.0.0" TYPE="O" IDX="3"/>
<HOME NAME="11204node1" LOC="/opt/app/node1/oracle/product/11.2.0.4" TYPE="O" IDX="2"/>
<HOME NAME="oraclehome1" LOC="/opt/app/node1/oracle/product/agent13c/agent_13.2.0.0.0" TYPE="O" IDX="1" REMOVED="T"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
prod_instance@PRD:node1:prod_instance:(19) /var/opt/oracle/oraInventory/ContentsXML

Solution : Attaching oracle home to central inventory

cd $ORACLE_HOME/oui/bin
./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc ORACLE_HOME="/opt/app/prod_instance/oracle/product/11.2.0.4" ORACLE_HOME_NAME="11204prod_instance"

$ pp/prod_instance/oracle/product/11.2.0.4" ORACLE_HOME_NAME="11204prod_instance" <
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 16383 MB Passed
The inventory pointer is located at /var/opt/oracle/oraInst.loc
The inventory is located at /var/opt/oracle/oraInventory
'AttachHome' was successful.
prod_instance@PRD:node1:prod_instance:(137) /opt/app/prod_instance/oracle/product/prod_instance/oui/bin

Oracle In Depth

Menu

Saturday, September 29, 2018

Apply PSU patch in 11g Non RAC

No comments:

Post a Comment