jan 2021 - ORACLEAGENT BLOG

How to access the Hana table in the oracle database?

How does it work?

SQL commands are fired from an oracle database (sqlplus), which will reach out to Oracle Gateway via DB LINK > tnsnames.ora

Oracle Gateway will have a parameter in its init<sid>.ora file and load unixODBC libraries from its LD_LIBRARY_PATH.

unixODBC will load HANA odbc drivers and goes through DSN setting to read data from the HANA database.

Meaning Oracle DB > DBLINK > tnsnames.ora > Oracle Gateway > unixODBC drivers > Data source DSN (odbc.ini) > HANA odbc drivers > HANA DB

If you notice above Oracle Gateway and HANA odbc do not talk to each other directly. Instead, talk through unixODBC drivers.

Step 1 –

The first step is to make unixODBC working

Installed unixODBC RPMs (both 32 and 64-bit) on the RHEL machine where you will run Oracle Gateway.

unixODBC-2.2.14-11.el6.x86_64

unixODBC-devel-2.2.14-11.el6.i686

unixODBC-devel-2.2.14-11.el6.x86_64

unixODBC-2.2.14-11.el6.i686

Step 2 –

Install SAP HANA client (64bit) on the RHEL machine where you will run Oracle Gateway. Please refer to the HANA client installation guide.

Step 3 –

Create /etc/odbc.ini contents look like below

[H1X]

Driver=/usr/sap/hdbclient/libodbcHDB.so

ServerNode=serverhana:30015

Step 4 –

Install Oracle Gateway software. You may please google to find step-by-step on how to install using Oracle Universal Installer.

Step 5 –

Set the environment variable of the user running the oracle gateway. In my case it is

LD_LIBRARY_PATH=/usr/lib64:/usr/sap/hdbclient:/oracle/BW1/112_64/lib

It is very important to have 64bit unixODBC libraries path (/usr/lib64/libodbc.so) in LD_LIBRARY_PATH because we run RHEL x64 and Oracle 64-bit software.

Step 6 –

Create init<sid>.ora. In my case, I will call this dg4odbc (initdg4odbc.ora). The content should be like below.

HS_DB_NAME = H1X

HS_FDS_CONNECT_INFO = H1X <===== This is the DSN name that comes from step 2 /etc/odbc.ini

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE=AMERICAN_AMERICA.UTF8

HS_NLS_NCHAR=UCS2

HS_FDS_TRANSACTION_MODEL=READ_ONLY

set ODBCINI=/etc/odbc.ini

Step 7 –

Create listener.ora

LISTENER =

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=oragwhost)(PORT=1551))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=dg4odbc)

(ORACLE_HOME=/oracle/BW1/112_64)

(PROGRAM=dg4odbc)

(ENV=”LD_LIBRARY_PATH=/usr/lib64:/oracle/BW1/112_64/lib:”)

)

)

Step 8 –

Start listener

lsnrctl start

Step 9 –

Let’s first test if unixODBC is working

Log in as the user which will run oracle gateway and check LD_LIBRARY_PATH (refer to step 4 above) and use the below commands.

isql -v <DSN name from step 2 /etc/odbc.ini> <hana user name> <password>

For example isql -v H1X SYSTEM password

+—————————————+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+—————————————+

SQL> select * from dummy

+——+

| DUMMY|

+——+

| X |

+——+

SQLRowCount returns 1

1 rows fetched

If you see these outputs you are halfway through. unixODBC is working.

Now it’s time to work on the oracle database from where data will be read with SELECT statements.

Step 10 –

Add entries in tnsnames.ora In my case it will look like the below.

dg4odbc =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=oragwhost)(PORT=1551))

(CONNECT_DATA=(SID=dg4odbc))

(HS=OK)

)

And test it with tnsping

Step 11 –

Create a DB link in the oracle database and run a SELECT command. Commands look like this.

CREATE PUBLIC DATABASE LINK H1X CONNECT TO

“SYSTEM” IDENTIFIED BY “password” USING ‘dg4odbc’;

SQL> select * from dummy@H1X;

DUMMY
——–
X

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

HOME / TROUBLESHOOTING / PREREQUISITE CHECK “CHECKACTIVEFILESANDEXECUTABLES” FAILED Prerequisite Check “CheckActiveFilesAndExecutables” Failed

CHECKACTIVEFILESANDEXECUTABLES” FAILED Prerequisite Check “CheckActiveFilesAndExecutables” Failed

[oracle@oracleagent:Database] opatch apply
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2022, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/12.1.0
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0/oraInst.loc
OPatch version : 12.2.0.1.27
OUI version : 12.1.0.2.0
Logfile location: /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/opatch2022-01-21_08-04-36AM_1.log

Verifying environment and performing prerequisite checks…
Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:

Following active executables are not used by opatch process :
/u01/app/oracle/product/12.1.0/lib/libclntsh.so.12.1

Following active executables are used by opatch process :

/u01/app/oracle/product/12.1.0/lib/libasmclntsh12.so
UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.
Logfile location: /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/opatch2022-01-21_08-04-36AM_1.log

OPatch failed with error code 73

Solution:

[oracle@oracleagent:Database] /sbin/fuser /u01/app/oracle/product/12.1.0/lib/libclntsh.so.12.1
/u01/app/oracle/product/12.1.0/lib/libclntsh.so.12.1: 22235m 24173m
[oracle@oracleagent:Database] kill -9 22235 24173
[oracle@oracleagent:Database] opatch apply
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2022, Oracle Corporation. All rights reserved.

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 32768233 33128590

Do you want to proceed? [y|n]
y
User Responded with Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/12.1.0’)

Is the local system ready for patching? [y|n]
y
User Responded with Y
Backing up files…
Applying sub-patch ‘32768233’ to OH ‘/u01/app/oracle/product/12.1.0’
ApplySession: Optional component(s) [ oracle.network.cman, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.

…..

….

…..

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

ORACLE RELEASE PATCHING IN ORACLE 19C -Opatch update

Description

In this article, we are going to see steps used to apply the latest Oracle 19c Database Release Update 19.9.0.0.201020 (Patch 31771877) The Environment is a single instance database.

Review readme file on Patch 31771877 – Database Release Update 19.9.0.0.201020

Download patch p31

771877_190000_Linux-x86-64.zip

Make sure the opatch version is minimum 12.2.0.1.19

Step:-1 Download the Patch from oracle support

p31771877_190000_Linux-x86-64.zip

p6880880_200000_Linux-x86-64.zip

Step:-2 Copied the patch to DB Server

[oratest@oracle ~]$ mkdir patch

[oratest@oracle ~]$ ls

[oratest@oracle ~]$ cd patch/

[oratest@oracle ~]$ ls -lrth
-rwxrwxr-x. 1 oratest oratest 116M Oct 21 10:55-x86-64.zip
-rwxrwxr-x. 1 oratest oratest 1.6G Oct 21 11:23 p6880880_210000_Linux-x86-64.zip

Step:-3 Upgrade Opatch Tool from 12.2.0.1.17 to 12.2.0.1.

[oratest@oracle ~]$ cd $ORACLE_HOME
[oratest@oracle ~]$ ls
[oratest@oracle ~]$ ./opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded

[oratest@oracle ~]$ cd /home/oratest/patch 

[oratest@oracle ~]$  ls

[oratest@oracle ~]$cp p6880880_200000_Linux-x86-64.zip
/u01/app/oracle/product/19.0.0/dbhome_1

[oratest@oracle ~]$ cd /app/oracle/product/19.0.0/dbhome_1

[oratest@oracle ~]$pwd
/u01/app/oracle/product/19.0.0/dbhome_1

[oratest@oracle ~]$ mv OPatch/ OPatch_BKP

[oratest@oracle ~]$ unzip p6880880_200000_Linux-x86-64.zip
Archive: p6880880_200000_Linux-x86-64.zip
creating: OPatch/
inflating: OPatch/emdpatch.pl
creating: OPatch/oracle_common/
creating: OPatch/oracle_common/modules/
inflating: OPatch/oracle_common/modules/com.oracle.glcm.common-logging_1.6.5.0.jar
inflating: OPatch/oracle_common/modules/common-logging-config.jar
creating: OPatch/oplan/

[oratest@oracle]$ cd OPatch
[oratest@oracle OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.27
OPatch succeeded.

Step:-4 Check Patches status before applying using the below query

SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN comments FORMAT A30
COLUMN description FORMAT A60
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10

SELECT TO_CHAR(action_time, ‘YYYY-MM-DD’) AS action_time,action,status,
description,patch_id FROM sys.dba_registry_sqlpatch ORDER by action_time;

Identifying Invalid Objects before patching

SQL> select owner,object_type,object_name,status from dba_objects 
where status='INVALID';

no row selected.

Step:-5 Apply RU patch on ORACLE_HOME 19c

[oracle@oraem01 soft]$ unzip p31771877_190000_Linux-x86-64.zip
[oracle@oraem01 soft]$ 31771877
[oratest@oracle 31771877]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

[oratest@oracle 31771877]$ 
export PATH=/u01/app/oracle/product/19.0.0/dbhome_1/OPatch:$PATH

Step – 6:- Apply opatch

[oratest@oracle 31771877]$  opatch apply
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.27
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/
opatch/opatch2021-10-21_12-48-42PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   31771877

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '31771877' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , 
[ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] ,
[ oracle.tfa, 19.0.0.0.0 ] , [ oracle.options.olap.awm, 19.0.0.0.0 ] , 
[ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , 
[ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , 
[ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.assistants.usm, 19.0.0.0.0 ] , 
[ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.sqlj, 19.0.0.0.0 ] , 
[oracle.jdk, 1.8.0.191.0] not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.buildtools.rsf, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.dbtoolslistener, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.usm.deconfig, 19.0.0.0.0...

Patching component oracle.rdbms.crs, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.marvel, 19.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.bali.ice, 11.1.1.7.0...

Patching component oracle.ctx.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.rdbms.drdaas, 19.0.0.0.0...

Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.rdbms.install.common, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.jdk, 1.8.0.201.0...
Patch 31771877 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set
 patch [31771877].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/
opatch2021-10-21_12-48-42PM_1.log

OPatch succeeded.

Step – 7 – Run the Below Commands

SQL> select owner from all_objects where status='INVALID';
no rows selected

SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2021-10-21 13:20:37
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2021-10-21 13:20:41
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
                  0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
                          0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.


SQL> select owner from all_objects where status='INVALID';
no rows selected

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Apply Patching On Oracle 19c Database Release Update- January 2021

DESCRIPTION

This article we are going to see steps to apply the latest Oracle 19c Database Release Update Patch 32218454

Sep 1 : DOWNLOAD THE PATCH FROM ORACLE SUPPORT

Sep 2 : CHECK THE CURRENT OPTACH VERSION IN THE DATABASE

[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/19.0.0/OPatch:$PATH
[oracle@localhost ~]$ opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.
[oracle@localhost ~]$

Sep 3 : MOVE THE PATCH TO ORACLE HOME LOCATION

[oracle@localhost ~]$ cd Desktop/
[oracle@localhost Desktop]$ ls
LINUX.X64_193000_db_home.zip p32218454_190000_Linux-x86-64.zip VMwareTools-10.0.6-3595377.tar.gz
[oracle@localhost Desktop]$ mv p32218454_190000_Linux-x86-64.zip /u01/app/oracle/product/19.0.0/

NOTE:We need to Upgrade Opatch from “12.2.0.1.17 to 12.2.0.1.23” for applying Patch “32218454”.

Sep 4 : UNZIP THE DOWNLOADED OPATCH

oracle@localhost Desktop]$ mv p6880880_190000_Linux-x86-64.zip /u01/app/oracle/product/19.0.0/
[oracle@localhost Desktop]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/product/19.0.0/
[oracle@localhost 19.0.0]$ unzip p6880880_190000_Linux-x86-64.zip

Archive: p6880880_190000_LINUX.zip

replace OPatch/emdpatch.pl? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
inflating: OPatch/emdpatch.pl
inflating: OPatch/oracle_common/modules/com.oracle.glcm.common-logging_1.6.5.0.jar
inflating: OPatch/oracle_common/modules/common-logging-config.jar
inflating: OPatch/oplan/oplan
inflating: OPatch/datapatch

Continues

inflating: OPatch/modules/thirdparty/jackson-databind-2.10.2.jar
inflating: OPatch/modules/thirdparty/jackson-annotations-2.10.2.jar

[oracle@localhost 19.0.0]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/
[oracle@localhost 19.0.0]$ export PATH=/u01/app/oracle/product/19.0.0/OPatch:$PATH
[oracle@localhost 19.0.0]$ opatch version
OPatch Version: 12.2.0.1.23

OPatch succeeded.
[oracle@localhost 19.0.0]$

Sep 5 : BEFORE APPLYING THE PATCH CHECK THE STATUS

SQL> col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;

Sep 6 : SHUTDOWN DATABASE AND LISTENER

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
[oracle@localhost scripts]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/product/19.0.0
[oracle@localhost 19.0.0]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 27-JAN-2021 18:14:07

Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@localhost 19.0.0]$
The command completed successfully

Sep 7 : APPLY RU PATCH ON ORACLE_HOME 19C

[oracle@localhost 19.0.0]$ cd 32218454/
[oracle@localhost 32218454]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/19.0.0
Central Inventory : /u01/app/oracle/product/19.0.0/oraInventory
from : /u01/app/oracle/product/19.0.0//oraInst.loc
OPatch version : 12.2.0.1.23
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-01-27_18-46-39PM_1.log

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 32218454
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/19.0.0’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Patching component oracle.network.client, 19.0.0.0.0…
Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0…
Patching component oracle.precomp.common, 19.0.0.0.0…
Patching component oracle.precomp.lang, 19.0.0.0.0…
Patching component oracle.jdk, 1.8.0.201.0…
Patch 32218454 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [32218454].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-01-27_18-46-39PM_1.log
OPatch succeeded.

Sep 8 : STARTUP THE DATABASE AND LISTENER

[oracle@localhost ~]$ cd scripts/
[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 27-JAN-2021 19:12:28
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 19.0.0.0.0 – Production
System parameter file is /u01/app/oracle/product/19.0.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 27-JAN-2021 19:12:30
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
The listener supports no services
The command completed successfully

SQL> startup
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 620756992 bytes
Database Buffers 2382364672 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL>

[oracle@localhost OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.10.0.0.0 Production on Wed Jan 27 19:47:50 2021
Copyright (c) 2012, 2020, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18910_2021_01_27_19_47_50/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:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.10.0.0.0 Release_Update 210108185017: Installed
SQL registry:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 27-JAN-21 05.00.33.668766 PM
Adding patches to installation queue and performing prereq checks…done
Installation queue:
No interim patches need to be rolled back
Patch 32218454 (Database Release Update : 19.10.0.0.210119 (32218454)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.10.0.0.0 Release_Update 210108185017
No interim patches need to be applied
Installing patches…

Sep 9 : AFTER APPLYING RU PATCH,CHECK THE DBA_REGISTRY_SQLPATCH

SQL>col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;

Sep 9 : USING BELOW QUERY, WE CAN VERIFY DESCRIPTION,STATUS OF APPLIED PATCH
COLUMN action_time FORMAT A10
COLUMN status FORMAT A30
COLUMN description FORMAT A30
SELECT action_time,action,status,description,patch_id from dba_registry_sqlpatch;

Sep 10 : CHECK OPATCH LSINVENTORY AND LIST OF PATCHES APPLIED.

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/
[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/19.0.0/OPatch:$PATH
[oracle@localhost ~]$ opatch lspatches
32218454;Database Release Update : 19.10.0.0.210119 (32218454)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
[oracle@localhost ~]$

Sep 11 : AFTER THE EXECUTION OF DATAPATCH SHOULD RUN UTLRP.SQL TO CLEAR INVALID OBJECT.

SQL> @?/rdbms/admin/utlrp.sql

CHECK THE ACTION & PATCHID IN DBA_REGISTRY_SQLPATCH

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle Critical Patch Update for January 2021

ORACLE DATABASE PATCHING – JANUARY 2021 UPDATE

The Critical Patch Update for January 2021 was released on January 19, 2021. Oracle strongly recommends applying the patches as soon as possible.

In this document we are going to see how to download and apply Jan 2021 patch.

ORACLE DATABASE PATCHING – JANUARY 2021 UPDATE

Search and download patch from oracle support site.

Go to Dashboard and then Patches and Updates.

Once window will open and it will ask for the product, release and the platform as shown below.

we need to choose 32228578 PATCH

Click on the download option to download the patch. As well as download the read me file. From there you will get an idea to moving forward.

MOVE THE DOWNLOADED PATCH TO ORACLE_HOME LOCATION

UNZIP THE PATCH – To get the updated opatch version

[oracle@trichy PATCH2021]$ unzip p32228578_122010_Linux-x86-64.zip

RUN THE PRE REQ BEFORE APPLYING THE PATCH

[oracle@trichy PATCH2021]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.19

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_10-21-26AM_1.log

Invoking prereq “checkconflictagainstohwithdetail”

Prereq “checkConflictAgainstOHWithDetail” passed.

OPatch succeeded.

[oracle@trichy 32228578]$ $ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./

Oracle Interim Patch Installer version 12.2.0.1.19

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_10-23-55AM_1.log

Invoking prereq “checkactivefilesandexecutables”

Prereq “checkActiveFilesAndExecutables” for patch 32228578 passed.

OPatch succeeded.

SHUTDOWN DATABASE AND THE LISTENER.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@trichy PATCH2021]$ lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 20-JAN-2021 10:53:22

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.22)(PORT=1537)))

The command completed successfully.

GO TO THE PATCH FOLDER AND APPLY THE PATCH.

BEFORE APPLYING PATCH EXPORT THE PATH OF OPATCH FOLDER.

[oracle@trichy ~]$ cd /u01/PATCH2021/32228578/

[oracle@trichy 32228578]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/OPatch:$PATH

[oracle@trichy 32228578]$ opatch version

OPatch Version: 12.2.0.1.19

OPatch succeeded.

[oracle@trichy 32228578]$ opatch apply

Oracle Interim Patch Installer version 12.2.0.1.19

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_11-45-30AM_1.log

Verifying environment and performing prerequisite checks…

Prerequisite check “CheckMinimumOPatchVersion” failed.

The details are:

The OPatch being used has version 12.2.0.1.19 while the following patch(es) require higher versions:

Patch 32228578 requires OPatch version 12.2.0.1.23.

Please download latest OPatch from My Oracle Support.

UtilSession failed: Prerequisite check “CheckMinimumOPatchVersion” failed.

Log file location: /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_11-45-30AM_1.log

OPatch failed with error code 73

CHECK THE OPATCH VERSION USING BELOW COMMAND – This patch requires a updated Opatch version.

DOWNLOAD LATEST VERSION OF OPATCH TO APPLY THE RECENT PATCH

[oracle@trichy OPATCH]$ ll

total 119552

drwxr-x—. 15 oracle oinstall 4096 Nov 6 01:20 OPatch

-rwxrw-rw-. 1 oracle oinstall 122415188 Jan 20 11:58 p6880880_200000_LINUX.zip

UNZIP THE OPATCH

[oracle@trichy OPATCH]$ unzip p6880880_200000_LINUX.zip

inflating: OPatch/auto/core/modules/legacyoui/com.oracle.cie.comdev_7.8.5.0.jar

inflating: OPatch/auto/core/modules/legacyoui/com.oracle.cie.gdr-schema_1.8.5.0.jar

inflating: OPatch/auto/core/modules/legacyoui/legacyoui.classpath.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.patch.opatchauto-actions_13.9.4.5.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.osyscore.automation_13.9.1.0.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.osyscore.validation_13.9.1.0.jar

creating: OPatch/auto/core/modules/features/

inflating: OPatch/auto/core/modules/features/oracle.glcm.opatchauto.core.binary.classpath.jar

inflating: OPatch/auto/core/modules/features/oracle.glcm.opatchauto.core.classpath.jar

inflating: OPatch/auto/core/modules/features/oracle.glcm.oplan.core.classpath.jar

inflating: OPatch/auto/core/modules/features/opatchauto-binary-classpath.jar

inflating: OPatch/auto/core/modules/features/oracle.glcm.osys.core.classpath.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.osyscore.utils_13.9.1.0.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.patch.opatchauto-binary_13.9.4.5.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.osyscore.model_13.9.1.0.jar

creating: OPatch/auto/dbsessioninfo/

inflating: OPatch/auto/dbsessioninfo/README.txt

creating: OPatch/private/

inflating: OPatch/private/commons-compress-1.19.jar

inflating: OPatch/opatch_env.sh

inflating: OPatch/opatchauto

inflating: OPatch/operr

inflating: OPatch/opatchauto.cmd