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 !