opatchauto failed with error /u01/app/oraInventory/ContentsXML/oui-patch.xml (Permission denied)

Hi,

While applying Jan 2022 RU patches on Oracle GI & DB 19.13.0.0 database, "opatchauto" got failed with below errors. Pasting here entire log for study and reference purpose.

[root@node2 ~]# opatchauto apply /backupstore/Jan2022_CPU_Patches/33567274/33509923

OPatchauto session is initiated at Fri Apr 22 18:01:58 2022

System initialization log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchautodb/systemconfig2022-04-22_06-02-02PM.log.

Session log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/opatchauto2022-04-22_06-02-25PM.log

The id for this session is ND4W

Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.0.0/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.0.0/dbhome_1

Patch applicability verified successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Patch applicability verified successfully on home /u01/app/19.0.0/grid

Executing patch validation checks on home /u01/app/19.0.0/grid

Patch validation checks successfully completed on home /u01/app/19.0.0/grid

Executing patch validation checks on home /u01/app/oracle/product/19.0.0/dbhome_1

Patch validation checks successfully completed on home /u01/app/oracle/product/19.0.0/dbhome_1

Verifying SQL patch applicability on home /u01/app/oracle/product/19.0.0/dbhome_1

SQL patch applicability verified successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Preparing to bring down database service on home /u01/app/oracle/product/19.0.0/dbhome_1

Successfully prepared home /u01/app/oracle/product/19.0.0/dbhome_1 to bring down database service

Performing prepatch operations on CRS - bringing down CRS service on home /u01/app/19.0.0/grid

Prepatch operation log file location: /u01/app/grid/crsdata/node2/crsconfig/crs_prepatch_apply_inplace_node2_2022-04-22_06-04-09PM.log

CRS service brought down successfully on home /u01/app/19.0.0/grid

Performing prepatch operation on home /u01/app/oracle/product/19.0.0/dbhome_1

Perpatch operation completed successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Start applying binary patch on home /u01/app/oracle/product/19.0.0/dbhome_1

Binary patch applied successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Performing postpatch operation on home /u01/app/oracle/product/19.0.0/dbhome_1

Postpatch operation completed successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Start applying binary patch on home /u01/app/19.0.0/grid

Failed while applying binary patches on home /u01/app/19.0.0/grid

Execution of [OPatchAutoBinaryAction] patch action failed, check log for more details. Failures:

Patch Target : node2->/u01/app/19.0.0/grid Type[crs]

Details: [

---------------------------Patching Failed---------------------------------

Command execution failed during patching in home: /u01/app/19.0.0/grid, host: node2.

Command failed:  /u01/app/19.0.0/grid/OPatch/opatchauto  apply /backupstore/Jan2022_CPU_Patches/33567274/33509923 -oh /u01/app/19.0.0/grid -target_type cluster -binary -invPtrLoc /u01/app/19.0.0/grid/oraInst.loc -jre /u01/app/19.0.0/grid/OPatch/jre -persistresult /u01/app/19.0.0/grid/opatchautocfg/db/sessioninfo/sessionresult_node2_crs_1.ser -analyzedresult /u01/app/19.0.0/grid/opatchautocfg/db/sessioninfo/sessionresult_analyze_node2_crs_1.ser

Command failure output:

==Following patches FAILED in apply:

Patch: /backupstore/Jan2022_CPU_Patches/33567274/33509923/33515361

Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-22_18-14-08PM_1.log

Reason: Failed during Patching: oracle.opatch.opatchsdk.OPatchException: ApplySession failed in system modification phase... 'ApplySession::apply failed: java.io.IOException: oracle.sysman.oui.patch.PatchException: java.io.FileNotFoundException: /u01/app/oraInventory/ContentsXML/oui-patch.xml (Permission denied)'

After fixing the cause of failure Run opatchauto resume

]

OPATCHAUTO-68061: The orchestration engine failed.

OPATCHAUTO-68061: The orchestration engine failed with return code 1

OPATCHAUTO-68061: Check the log for more details.

OPatchAuto failed.

OPatchauto session completed at Fri Apr 22 18:18:58 2022

Time taken to complete the session 17 minutes, 1 second

 opatchauto failed with error code 42

 

 

[root@node2 ~]# ll /u01/app/oraInventory/ContentsXML/oui-patch.xml

-rw-r--r-- 1 oracle oinstall 174 Apr 22 18:13 /u01/app/oraInventory/ContentsXML/oui-patch.xml

[root@node2 ~]#

[root@node2 ~]#

Solution :  According to MOS Doc ID 2582139.1, During 19.x Grid Infrastructure (GI) installation, the file 'oui-patch.xml' will be created under the central inventory directory on the OUI node (node where gridSetup.sh was invoked) but NOT on the remote nodes.

Hence need to copy oui-patch.xml file from Node1 to Node2 [remote nodes] and resume the patching.

[root@node1 ~]# scp /u01/app/oraInventory/ContentsXML/oui-patch.xml grid@node2: /u01/app/oraInventory/ContentsXML

[root@node2 ~]# ll /u01/app/oraInventory/ContentsXML/oui-patch.xml

-rw-rw---- 1 grid oinstall 174 Apr 22 18:13 /u01/app/oraInventory/ContentsXML/oui-patch.xml

[root@node2 ~]# opatchauto resume

........

OPatchAuto successful.

Cheers !

Not able to retrieve system instance details :: PRCA-1084 : Failed to retrieve ASM Mode PRKH-1059 : Failed to get ASM mode PRKH-1056 : Unexpected CSS error(error code[0]) PRKH-1002 : Internal HASContext Error: JNI Native Call Failure

 Hi,

While applying Jan 2022 RU patches on Oracle GI & DB 19.13.0.0 database, "opatchauto" got failed with below errors. Pasting here entire log for study and reference purpose.

Not able to retrieve system instance details :: PRCA-1084 : Failed to retrieve ASM Mode

PRKH-1059 : Failed to get ASM mode

PRKH-1056 : Unexpected CSS error(error code[0])

PRKH-1002 : Internal HASContext Error: JNI Native Call Failure

Related MOS doc for reference. Doc ID 1992303.1

Error :

[root@node1 33509923]# opatchauto apply /backupstore/Jan2022_CPU_Patches/33567274/33509923

2022-04-22 15:48:12,542 FINE [1] com.oracle.glcm.patch.auto.db.product.driver.crs.CrsProductDriver - oracle.installer.library_loc set to /backupstore/Jan2022_CPU_Patches/33567274/33509923/33529556/files/lib

2022-04-22 15:48:12,818 SEVERE [1] com.oracle.glcm.patch.auto.db.integration.model.productsupport.topology.TopologyCreator - Stacktrace :: oracle.dbsysmodel.driver.sdk.productdriver.ProductDriverException: PRCA-1084 : Failed to retrieve ASM Mode

PRKH-1059 : Failed to get ASM mode

PRKH-1056 : Unexpected CSS error(error code[0])

PRKH-1002 : Internal HASContext Error: JNI Native Call Failure

com.oracle.glcm.patch.auto.db.product.driver.crs.CrsProductDriver.makeASM(CrsProductDriver.java:702)

com.oracle.glcm.patch.auto.db.product.driver.crs.CrsProductDriver.buildCRSSystemInstance(CrsProductDriver.java:511)

com.oracle.glcm.patch.auto.db.product.driver.crs.CrsProductDriver.buildSystemInstance(CrsProductDriver.java:562)

com.oracle.glcm.patch.auto.db.integration.model.productsupport.topology.TopologyCreator.createSystemInstance(TopologyCreator.java:261)

com.oracle.glcm.patch.auto.db.integration.model.productsupport.topology.TopologyCreator.process(TopologyCreator.java:177)

com.oracle.glcm.patch.auto.db.integration.model.productsupport.topology.TopologyCreator.main(TopologyCreator.java:121)

   

Solution :

By root user check the environment variable set :

[root@node1 trace]# env

GRID_HOME=/u01/app/19.0.0/grid

ORA_CRS_HOME=/u01/app/19.0.0/grid/bin

PATH=/u01/app/19.0.0/grid/bin:/u01/app/19.0.0/grid/OPatch:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

OLDPWD=/root

[root@node1 trace]#

So here you can observer grid home path pointed to home location & bin location too.

Hence, unset "ORA_CRS_HOME" variable and try again.

[root@node1 33509923]# opatchauto apply /backupstore/Jan2022_CPU_Patches/33567274/33509923

........

Trying to apply SQL patch on home /u01/app/oracle/product/19.0.0/dbhome_1

SQL patch applied successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

OPatchAuto successful.

Cheers ! 

opatchauto failed with error code 42 : Cannot copy file from ocrcheck.bin

Hi,

While applying Jan 2022 RU patches on Oracle restart 19.13.0.0 database, "opatchauto" got failed with below errors. Pasting here entire log for study and reference purpose.

Patch 33529556:

Copy Action: Source File "/u01/Software/Jan_2022CPU_patches/33567274/33509923/33529556/files/bin/ocrcheck.bin" does not exists or is not readable

'oracle.has.crs, 19.0.0.0.0': Cannot copy file from 'ocrcheck.bin' to '/u01/app/19.0.0/grid/bin/ocrcheck.bin'

Copy Action: Source File "/u01/Software/Jan_2022CPU_patches/33567274/33509923/33529556/files/bin/ocrconf ... 

Error :

[root@NODE1 ~]# opatchauto apply /u01/Software/Jan_2022CPU_patches/33567274/33509923

OPatchauto session is initiated at Fri Apr 22 11:08:58 2022

System initialization log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchautodb/systemconfig2022-04-22_11-09-02AM.log.

Session log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/opatchauto2022-04-22_11-09-07AM.log

The id for this session is 4JXB

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.0.0/dbhome_1

Patch applicability verified successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Executing patch validation checks on home /u01/app/oracle/product/19.0.0/dbhome_1

Patch validation checks successfully completed on home /u01/app/oracle/product/19.0.0/dbhome_1

Verifying SQL patch applicability on home /u01/app/oracle/product/19.0.0/dbhome_1

SQL patch applicability verified successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.0.0/grid

Patch applicability verified successfully on home /u01/app/19.0.0/grid

Executing patch validation checks on home /u01/app/19.0.0/grid

Patch validation checks successfully completed on home /u01/app/19.0.0/grid

Preparing to bring down database service on home /u01/app/oracle/product/19.0.0/dbhome_1

Successfully prepared home /u01/app/oracle/product/19.0.0/dbhome_1 to bring down database service

Bringing down database service on home /u01/app/oracle/product/19.0.0/dbhome_1

Following database has been stopped and will be restarted later during the session: testdb

Database service successfully brought down on home /u01/app/oracle/product/19.0.0/dbhome_1

Performing prepatch operations on CRS - bringing down CRS service on home /u01/app/19.0.0/grid

Prepatch operation log file location: /u01/app/grid/crsdata/node1/crsconfig/hapatch_2022-04-22_11-13-56AM.log

CRS service brought down successfully on home /u01/app/19.0.0/grid

Start applying binary patch on home /u01/app/oracle/product/19.0.0/dbhome_1

Binary patch applied successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Start applying binary patch on home /u01/app/19.0.0/grid

Failed while applying binary patches on home /u01/app/19.0.0/grid

Failed while applying binary patches on home /u01/app/19.0.0/grid

Execution of [OPatchAutoBinaryAction] patch action failed, check log for more details. Failures:

Patch Target : node1->/u01/app/19.0.0/grid Type[siha]

Details: [

---------------------------Patching Failed---------------------------------

Command execution failed during patching in home: /u01/app/19.0.0/grid, host: node1.

Command failed:  /u01/app/19.0.0/grid/OPatch/opatchauto  apply /u01/Software/Jan_2022CPU_patches/33567274/33509923 -oh /u01/app/19.0.0/grid -target_type has -binary -invPtrLoc /u01/app/19.0.0/grid/oraInst.loc -jre /u01/app/19.0.0/grid/OPatch/jre -persistresult /u01/app/19.0.0/grid/opatchautocfg/db/sessioninfo/sessionresult_node1_siha_1.ser -analyzedresult /u01/app/19.0.0/grid/opatchautocfg/db/sessioninfo/sessionresult_analyze_node1_siha_1.ser

Command failure output: 

==Following patches FAILED in apply:

Patch: /u01/Software/Jan_2022CPU_patches/33567274/33509923/33529556

Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-22_11-26-49AM_1.log

Reason: Failed during Analysis: CheckPatchApplicable Failed, [ Prerequisite Status: FAILED, Prerequisite output: 

The details are:

Patch 33529556:

Copy Action: Source File "/u01/Software/Jan_2022CPU_patches/33567274/33509923/33529556/files/bin/ocrcheck.bin" does not exists or is not readable

'oracle.has.crs, 19.0.0.0.0': Cannot copy file from 'ocrcheck.bin' to '/u01/app/19.0.0/grid/bin/ocrcheck.bin'

Copy Action: Source File "/u01/Software/Jan_2022CPU_patches/33567274/33509923/33529556/files/bin/ocrconf ... 

After fixing the cause of failure Run opatchauto resume

]

OPATCHAUTO-68061: The orchestration engine failed.

OPATCHAUTO-68061: The orchestration engine failed with return code 1

OPATCHAUTO-68061: Check the log for more details.

OPatchAuto failed.

OPatchauto session completed at Fri Apr 22 11:32:06 2022

Time taken to complete the session 23 minutes, 9 seconds

 opatchauto failed with error code 42

Solution :   Here need to change the owner of the patches from root to "GRID:OINSTALL"

[root@NODE1 ]# chown -R grid:oinstall /u01/Software/Jan_2022CPU_patches

[root@NODE1 ]# ls -ltr

total 4161128

drwxrwxrwx 4 grid oinstall         80 Jan 17 16:24 33567274

-rw-rw-r-- 1 grid oinstall    1483571 Jan 18 12:29 PatchSearch.xml

drwxr-xr-x 4 grid oinstall         75 Jan 19 20:00 33567270

-rw-r--r-- 1 grid oinstall 1498895522 Apr 21 13:12 p33567270_190000_Linux-x86-64.zip

-rw-r--r-- 1 grid oinstall 2760607369 Apr 21 14:10 p33567274_190000_Linux-x86-64.zip

[root@NODE1 33509923]# opatchauto resume

OPatchauto session is initiated at Fri Apr 22 12:32:20 2022

Session log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/opatchauto2022-04-22_12-32-20PM.log

Resuming existing session with id 4JXB

Start applying binary patch on home /u01/app/19.0.0/grid

Binary patch applied successfully on home /u01/app/19.0.0/grid

Performing postpatch operations on CRS - starting CRS service on home /u01/app/19.0.0/grid

Postpatch operation log file location: /u01/app/grid/crsdata/node1/crsconfig/hapatch_2022-04-22_12-37-35AM.log

CRS service started successfully on home /u01/app/19.0.0/grid

Preparing home /u01/app/oracle/product/19.0.0/dbhome_1 after database service restarted

No step execution required.........

 

Trying to apply SQL patch on home /u01/app/oracle/product/19.0.0/dbhome_1

SQL patch applied successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

OPatchAuto successful.

Cheers !

SEC_USER_UNAUTHORIZED_ACCESS_BANNER

SEC_USER_UNAUTHORIZED_ACCESS_BANNER

Purpose::

To specify a text file containing the banner contents that warn the user about possible user action auditing. The complete path of the text file must be specified in the sqlnet.ora file on the server. Oracle Call Interface (OCI) applications can make use of OCI features to retrieve this banner and display it to the user. The text file has a maximum limit of 512 bytes.

node1: /data2/1930/network/admin $ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /data2/1930/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME, EZCONNECT)

SEC_USER_UNAUTHORIZED_ACCESS_BANNER = /home/oracle/banner.txt

node1: /home/oracle $

node1: /home/oracle $ cat banner.txt

Welcome to RnD DB

node1: /home/oracle $

Post, need to restart the database to changes get effected

node1: /home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 2 22:56:47 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

node1: /home/oracle $

node1: /home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 2 22:57:53 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Welcome to RnD DB                <<<--- Welcome message 

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area xxxxx bytes

Fixed Size               xxxxx bytes

Variable Size            xxxxx bytes

Database Buffers         xxxxx bytes

Redo Buffers             xxxxx bytes

Database mounted.

Database opened.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

node1: /home/oracle $

Cheers !

The Oracle Management Server (OMS) host and port specified via OMS_HOST and EM_UPLOAD_PORT is not available

Error :  The Oracle Management Server (OMS) host and port specified via OMS_HOST and EM_UPLOAD_PORT is not available. Pass in a valid hostname and port number for the OMS to successfully deploy the agent.

Solution :

In order to resolve above error, you should add hostnames and ip addresses of OMS Server and Agent Server to the both server's /etc/hosts file.

OMS host # vi /etc/hosts

xx.xx.xx.xx agent_host

AGENT host # vi /etc/hosts

xx.xx.xx.xx oms_host

After that, test it with ping 

OMS host # ping agent_hostname

AGENT host # ping OMS_hostname

Cheers !

ORA-29278: SMTP transient error: 421 Service not available

 Getting below errors while sending mail from one of DB using UTL_SMTP.mail

ORA-29278: SMTP transient error: 421 Service not available

ORA-06512: at "SYS.UTL_SMTP", line 57

ORA-06512: at "SYS.UTL_SMTP", line 140

ORA-06512: at "SYS.UTL_SMTP", line 641

ORA-06512: at "SYS.TESTMAIL", line 30

ORA-29278: SMTP transient error: 421 Service not available

ORA-06512: at "SYS.UTL_SMTP", line 57

ORA-06512: at "SYS.UTL_SMTP", line 140

ORA-06512: at "SYS.UTL_SMTP", line 201

ORA-06512: at "SYS.TESTMAIL", line 6

ORA-06512: at line 5

solution :

1. install mailx & postfix and enable the services

rpm -qa | grep mailx

rpm -qa | grep postfix

yum install -y postfix

yum install -y mailx

systemctl status postfix

systemctl start postfix

systemctl enable postfix

systemctl status postfix

2. Try to telnet the email relay server, if you failed reach to network admin and open the relay server port

eg.

db ip - source - 192.xx.xx.35

mail relay ip - 192.xx.xx.25 

mail port - 587

$ telnet 192.xx.xx.35 587

3. Telnet output :

[root@node1 ~]# telnet 192.xx.xx.25 587

Trying 192.xx.xx.25...

Connected to 192.xx.xx.25.

Escape character is '^]'.

Connection closed by foreign host.

[root@node1 ~]#

** if Telnet output is "Connection closed by foreign host." then there is issue, you should reach to email admin and ask to allow source ip in email relay server 

after that, if you telnet then below output should receive

[root@node1 ~]# telnet 192.xx.xx.25 587

Trying 192.xx.xx.25...

Connected to 192.xx.xx.25.

Escape character is '^]'.

220 relay.mydomain Microsoft ESMTP MAIL Service, Version: 8.5.9600.16384 ready at  Thu, 6 Jan 2022 09:56:05 +0300

now, type

helo domain

250 relay.mydomain Hello [192.xx.xx.35]

4. 

REFER : https://stackoverflow.com/questions/41377078/ora-29278-smtp-transient-error-service-not-available-when-running-utl-mail

Create proper ACL :

Give grant to corresponding schema name for utl_tcp,utl_smtp and utl_http.

        grant execute on utl_tcp to myprod_schema;

        grant execute on utl_smtp to myprod_schema;

        grant execute on utl_http to myprod_schema;

   

CREATE_ACL using DBMS_NETWORK_ACL_ADMIN sys package:-

    BEGIN

       DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (

       acl => '/sys/acls/myorg.xml',

       description => 'Allowing SMTP Connection',

       principal => 'myprod_schema

       privilege => 'connect',

       start_date => SYSTIMESTAMP,

       end_date => NULL);

     COMMIT;

     END;

   /

ADD_PRIVILEGE to schema using DBMS_NETWORK_ACL_ADMIN package:-

    BEGIN

     DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(

     acl => '/sys/acls/myorg.xml',

     principal => 'myprod_schema',

     is_grant => true,

     privilege => 'resolve');

    COMMIT;

   END;

  /

ASSIGN_ACL to mail server using DBMS_NETWORK_ACL_ADMIN package:-

     BEGIN

       DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

        acl => '/sys/acls/myprod_schema.xml',

        host => '*');

       COMMIT;

      END;

     / 

5. CREATE TESTMAIL PROC AND TEST IT

REFER : Check SMTP Server Availability for ORA-29278 or ORA-29279 errors using UTL_SMTP to Send Email. (Doc ID 604763.1)

CREATE OR REPLACE NONEDITIONABLE PROCEDURE TESTMAIL(fromm VARCHAR2,too VARCHAR2,sub VARCHAR2,body VARCHAR2,port NUMBER)

IS

objConnection UTL_SMTP.CONNECTION;

vrData VARCHAR2(32000);

BEGIN

objConnection := UTL_SMTP.OPEN_CONNECTION('192.xx.xx.25',PORT);

UTL_SMTP.HELO(objConnection, '192.xx.xx.25');

UTL_SMTP.MAIL(objConnection, fromm);

UTL_SMTP.RCPT(objConnection, too);

UTL_SMTP.OPEN_DATA(objConnection);

UTL_SMTP.WRITE_DATA(objConnection, 'From: '||fromm || UTL_TCP.CRLF);

UTL_SMTP.WRITE_DATA(objConnection, 'To: '||too || UTL_TCP.CRLF);

UTL_SMTP.WRITE_DATA(objConnection, 'Subject: ' || sub || UTL_tcp.CRLF);

UTL_SMTP.WRITE_DATA(objConnection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF);

UTL_SMTP.WRITE_DATA(objConnection, 'Content-Type: ' || 'text/html;');

UTL_SMTP.WRITE_DATA(objConnection, 'Content-Transfer-Encoding: ' || '"8Bit"' || UTL_TCP.CRLF);

UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF);

UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'<HTML>');

UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'<BODY>');

UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'<FONT COLOR="red" FACE="Courier New">'||body||'</FONT>');

UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'</BODY>');

UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'</HTML>');

UTL_SMTP.CLOSE_DATA(objConnection);

UTL_SMTP.QUIT(objConnection);

EXCEPTION

WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN

UTL_SMTP.QUIT(objConnection);

DBMS_OUTPUT.PUT_LINE(SQLERRM);

WHEN OTHERS THEN

UTL_SMTP.QUIT(objconnection);

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END TESTMAIL;

/

Test by executing this ---

SQL> 

DECLARE

Vdate Varchar2(25);

BEGIN

Vdate := to_char(sysdate,'dd-mon-yyyy HH:MI:SS AM');

TESTMAIL('source@mail.com', 'destination@mail.com', 'TESTMAIL','This is a UTL_SMTP-generated email at '|| Vdate,587);

END;

/

--PL/SQL procedure successfully completed

Cheers !!!

Slow Login To Oracle 19c DB

Slow Login To Oracle 19c DB Server Using HPUX Itanium 19c Client (Doc ID 2673496.1)

Simulated ::

node1: /home/oracle $ cat > exit.sql

exit;

node1: /home/oracle $ time sqlplus / as sysdba @exit.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 8 09:05:08 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

real    0m1.543s   <<<--- slow

user    0m0.020s

sys     0m0.025s

node1: /home/oracle $

node1: /home/oracle $

node1: /home/oracle $

node1: /home/oracle $

node1: /home/oracle $

node1: /home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 8 09:05:37 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter client_statistics_level

NAME                                 TYPE        VALUE

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

client_statistics_level              string      TYPICAL

SQL> alter system set client_statistics_level=OFF scope=spfile;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

node1: /home/oracle $

node1: /home/oracle $

node1: /home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 8 09:06:36 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 6224344544 bytes

Fixed Size                  8910304 bytes

Variable Size            3858759680 bytes

Database Buffers         2348810240 bytes

Redo Buffers                7864320 bytes

Database mounted.

Database opened.

SQL>

SQL>

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

node1: /home/oracle $

node1: /home/oracle $

node1: /home/oracle $ time sqlplus / as sysdba @exit.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 8 09:07:26 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

real    0m0.116s    <<<---- improved

user    0m0.012s

sys     0m0.025s

node1: /home/oracle $

Cheers !

ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], [], [], [], [], []

Hi,

While fetching the data over dblink between Oracle & MS SQL database, got below error:

SQL> select * from "dbo"."ACC_TAB"@mssql;

select * from "dbo"."ACC_TAB"@mssql

ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], [], [], [], [], []

SQL>

Solution:

You can follow below MOS Doc to resolve this.

ORA-600 [HO define: Long fetch] Error Message When Selecting Data Via Oracle Database Gateways (Doc ID 1224783.1)

In my case, found that base table in MS SQL database "ACC_TAB" having columns datatype "varchar(max)", hence got the error.

In order to resolve this, I have modified the column and defined the column length and created index on the same column to fetch the data faster. Bingo! issue has been resolved. I can able to fetch data over dblink.

alter table [dbo].[ACC_TAB] ALTER COLUMN [Account] varchar(30);

create index acc on  [dbname].[d]bo.[ACC_TAB] ([Account]);

SQL> select * from "dbo"."ACC_TAB"@mssql;

--------

901212

Cheers !

OPatch failed with error code 1 -- Incompatible usage of java with OUI (32/64 bit).

OPatch failed with error code 1 --  Incompatible usage of java with OUI (32/64 bit).

 
$ ./opatch lsinventory
OPatch cannot continue because it would not be able to load OUI platform dependent library from the directory "/u02/app/oracle/product/19.3.0.0/db_1/oui/lib/linux". The directory does not exist in the Oracle home.
This could be due to the following reasons.
(1) Incompatible usage of java with OUI (32/64 bit).
(2) Wrong 32-bit Oracle Home installation in 64-bit environment (or) vice-versa.
Please contact Oracle support for more details.
OPatch failed with error code 1

Solution: Mistakenly downloaded 32 bit OPatch installer and post this error, we have downloded 64 bit and issue has been resolved.

CATPROC Oracle Database Packages and Types INVALID after upgrade from 12.1.0.2.0 to 19.3.0.0

CATPROC Oracle Database Packages and Types INVALID after upgrade from 12.1.0.2.0 to 19.3.0.0

Scenario:

We have upgraded our db from 12.1.0.2 to 19.3 and SYS objects got INVALID.

Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
CATPROC Oracle Database Packages and Types INVALID 19.0.0.0.0 12.1.0.2.0 12.1.0.2.0


Solution :

1) Below script will tell us which PLSQL code needs to be validated/compiled first for the CATPROC to get validated too. Please apply its recommendation and then repeat it until no more recommendations are there.

sqlplus / as sysdba
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name FROM obj$ WHERE status > 1 AND (ctime BETWEEN start_time AND end_time OR mtime BETWEEN start_time AND end_time OR stime BETWEEN start_time AND end_time) AND ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||' Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/


2) If the above query returns 'CATPROC can be validated now', then please execute the following command to validate the catproc registry component:
$ sqlplus / as sysdba
exec dbms_registry_sys.validate_catproc;


I have followed 1st step,


SQL> declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name FROM obj$ WHERE status > 1 AND 2 (ctime BETWEEN start_time AND end_time OR mtime BETWEEN start_time AND end_time OR stime BETWEEN start_time AND end_time) AND ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||' Object_id '||object_id );
EXCEPTION
WHEN NO_D 3 ATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/ 4 5 6 7 8 9 10 11 12 13 14
Please compile Invalid object DBMS_AUTO_INDEX_INTERNAL Object_id 904209
PL/SQL procedure successfully completed.




SQL> alter package DBMS_AUTO_INDEX_INTERNAL compile body;
Warning: Package Body altered with compilation errors.
SQL> show error
Errors for PACKAGE BODY DBMS_AUTO_INDEX_INTERNAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7256/5 PL/SQL: SQL Statement ignored
7319/14 PLS-00231: function 'DBMS_AUTO_INDEX_INTERNAL.AUTO_INDEX_ALLOW'
may not be used in SQL
7319/39 PL/SQL: ORA-00904: : invalid identifier
7403/9 PL/SQL: Statement ignored
7403/13 PLS-00364: loop index variable 'CUR' use is invalid
7407/9 PL/SQL: Statement ignored
7407/43 PLS-00364: loop index variable 'CUR' use is invalid
7410/9 PL/SQL: Statement ignored
7410/44 PLS-00364: loop index variable 'CUR' use is invalid

Still this package is INVALID, so followed below steps :

1. connect to the database as sysdba:
sqlplus "/ as sysdba"
2. shutdown immediate
3. startup upgrade
4. spool catalog.log
@$ORACLE_HOME/rdbms/admin/catalog.sql
spool off
5. spool catproc.log
@$ORACLE_HOME/rdbms/admin/catproc.sql
spool off
6. spool utlrp.log
@?/rdbms/admin/utlrp
spool off
7. shutdown immediate
8. startup
9. 
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
CATPROC Oracle Database Packages and Types VALID 19.0.0.0.0 12.1.0.2.0 12.1.0.2.0


Hope this will help you :)

joxcsys: release mismatch 12.1.0.2.0 1.6 in database (classes.bin) vs 12.1.0.2.191015 1.6 in executable

joxcsys: release mismatch 12.1.0.2.0 1.6 in database (classes.bin) vs 12.1.0.2.191015 1.6 in executable

Observed multiple errors in one of the development database.



Solution :

[oracle@devoracledb ~]$ sqlplus / as sysdba


SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 11:18:02 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS on 08-MAR-20 @ db_UAT2 > select dbms_java.get_jdk_version() from dual;

select dbms_java.get_jdk_version() from dual
                                        *
ERROR at line 1:
ORA-29548: Java system class reported: release of Java system classes in the
database (12.1.0.2.0 1.6) does not match that of the oracle executable
(12.1.0.2.191015 1.6)


SYS on 08-MAR-20 @ db_UAT2 > @?/javavm/install/update_javavm_db.sql
SYS on 08-MAR-20 @ db_UAT2 > SET FEEDBACK 1
SYS on 08-MAR-20 @ db_UAT2 > SET NUMWIDTH 10
SYS on 08-MAR-20 @ db_UAT2 > SET LINESIZE 80
SYS on 08-MAR-20 @ db_UAT2 > SET TRIMSPOOL ON
SYS on 08-MAR-20 @ db_UAT2 > SET TAB OFF
SYS on 08-MAR-20 @ db_UAT2 > SET PAGESIZE 100
SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > -- If Java is installed, do CJS.
SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > -- If CJS can deal with the SROs inconsistent with the new JDK,
SYS on 08-MAR-20 @ db_UAT2 > -- the drop_sros() call here can be removed.
SYS on 08-MAR-20 @ db_UAT2 > call initjvmaux.drop_sros();

Call completed.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > create or replace java system;
  2  /



Java created.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > update dependency$
  2    set p_timestamp=(select stime from obj$ where obj#=p_obj#)
  3    where (select stime from obj$ where obj#=p_obj#)!=p_timestamp and
  4          (select type# from obj$ where obj#=p_obj#)=29  and
  5          (select owner# from obj$ where obj#=p_obj#)=0;

0 rows updated.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > commit;

Commit complete.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > SYS on 08-MAR-20 @ db_UAT2 > SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > ---- VERIFIED 

SYS on 08-MAR-20 @ db_UAT2 > select dbms_java.get_jdk_version() from dual;

DBMS_JAVA.GET_JDK_VERSION()
-------------------------------------------------------------
1.6.0_211

1 row selected.

SYS on 08-MAR-20 @ db_UAT2 > 

ORA-12012: error on auto execute of job ORA-12008: error in materialized view refresh path ORA-01722: invalid number

Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_j000_31458.trc:
ORA-12012: error on auto execute of job 111816
ORA-12008: error in materialized view refresh path
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_IREFRESH", line 687
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1


Approach :

select job, schema_user, what from dba_jobs where job='111816';

  JOB SCHEMA_USER WHAT
111816 DB_PROD      dbms_refresh.refresh('"DB_PROD"."MV_CUST"');



Open the above mentioned materialized view "MV_CUST", in select query, one of the condition getting failed with "INVALID NUMBER". By digging down, found that, in one column having (its datatype VARCHAR) instead of number there was CHARACTER and this row getting compared with number. 

Offline installation of SQL Server 2017 on Linux

Offline installation of SQL Server 2017 on Linux::

1) Meet the pre-requisites 

2) Download the rpm package for the SQL Server database engine. You can download the required rpm packages from the release note (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes?view=sql-server-ver15) or from Microsoft Repositories.

3) Create a directory for SQLINSTALL & Copy the downloaded rpm to the Linux machine using WinSCP.

4) # systemctl status mssql-server

5) Go ahead with rpm installation

6) Complete installation by executing below command & selected Evaluation pack & enter admin password.

# sudo /opt/mssql/bin/mssql-conf setup

7) Check the services :

8) Now you can connect to DB through the SSMS tool.

MySQL to Oracle Characterset Problem -- Solved

In our environment, one of the Oracle production DB fetches MySQL DB data for reporting purposes. Recently application team reported that they are getting data WITH SPACE in between. 

DOC	REF	REFNO	REMARKS
R C	R A	1 0 1 2 0	R E C E I P T
R C	R A	1 0 1 2 0	D E P O S I T

Solution:

1. Check Character set of MySQL database.

mysql> show variables like "character_set_database";

+------------------------+--------+

| Variable_name          | Value  |

+------------------------+--------+

| character_set_database | latin1 |

+------------------------+--------+

1 row in set (0.00 sec)

2. Insert below line in odbc.ini file

set LD_LIBRARY_PATH=/usr/lib64/lib

Description     = MySQL Production database

Trace       = ON

TraceFile   = /tmp/stderr

SERVER      = 10.xx.XX.XX

USER        = root

PASSWORD    = XXXX

PORT        = 3306

DATABASE    = MYSQLPROD

Driver=/home/oracle/oracleTOmysql/lib/libmyodbc8w.so

CHARSET = latin1

3. Fetch the data again from oracle DB. Now getting proper data from MySQL.

DOC	REF	REFNO	REMARKS
RC	RA	10120	RECEIPT
RC	RA	10120	DEPOSIT

Hope this will help 😊