Soumya's Database Blog : January 2022

The user does not have the privileges to run commands as user "root" using the Privilege Delegation tool "/usr/bin/sudo"

While installing agent in target host for oracle enterprise manager 13c, following error is observed

"The user does not have the privileges to run commands as user "root" using the Privilege Delegation tool "/usr/bin/sudo"

Solution:-

1. Ignore this warning and proceed with installation. Execute <agent Oracle Home>/root.sh manually after logging in as root.

OR

Ensure the sudo privileges for <AGENT_INSTALL_USER> to execute the command set in sudoers file.

a. Enable Sudo user setup on target host without password.

As root user login to the target server:

# visudo
Locate the following line in the file mentioned
root ALL=(ALL) ALL

Add the following line below the line mentioned .Here r12devdb is the user that would be used to connect the server and continue with the installation.

r12devdb ALL=(ALL) ALL

2. Continue installation in console.

How to start/stop/ check status of OMS and Agent in OEM 13c

To start Oracle Management Server(OMS), login to oem server.

Navigate to oracle middleware home

export MW_HOME= /data2/app/oracle/middleware

cd $MW_HOME/bin

[oem@ccuine25 bin]$ ./emctl start oms

Oracle Enterprise Manager Cloud Control 13c Release 5

Starting Oracle Management Server...

WebTier Successfully Started

Oracle Management Server Successfully Started

Oracle Management Server is Up

JVMD Engine is Up

To Check status of OMS

cd $MW_HOME/bin

[oem@ccuine25 bin]$ ./emctl status oms -details

Oracle Enterprise Manager Cloud Control 13c Release 5

Enter Enterprise Manager Root (SYSMAN) Password :

Console Server Host : ccuine25.xyz.co.in

HTTP Console Port : 7788

HTTPS Console Port : 7803

HTTP Upload Port : 4889

HTTPS Upload Port : 4903

EM Instance Home : /data2/app/oracle/gc_inst/em/EMGC_OMS1

OMS Log Directory Location : /data2/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log

OMS is not configured with SLB or virtual hostname

Agent Upload is locked.

OMS Console is locked.

Active CA ID: 1

Console URL: https://ccuine25.xyz.co.in:7803/em

Upload URL: https://ccuine25.xyz.co.in:4903/empbs/upload

WLS Domain Information

Domain Name : GCDomain

Admin Server Host : ccuine25.xyz.co.in

Admin Server HTTPS Port: 7102

Admin Server is RUNNING

Oracle Management Server Information

Managed Server Instance Name: EMGC_OMS1

Oracle Management Server Instance Host: ccuine25.xyz.co.in

WebTier is Up

Oracle Management Server is Up

JVMD Engine is Up

To Shutdown OMS:-

cd $MW_HOME/bin

[oem@ccuine25 bin]$ ./emctl stop oms -all

Oracle Enterprise Manager Cloud Control 13c Release 5

Stopping Oracle Management Server...

WebTier Successfully Stopped

Oracle Management Server Successfully Stopped

AdminServer Successfully Stopped

Oracle Management Server is Down

JVMD Engine is Down

To Start OEM agent (In Target Server):-

export AGENT_HOME=/DEV/agent/emagent/agent_inst

cd $AGENT_HOME/bin

[r12devdb@ccuine14 bin]$ ./emctl start agent

Oracle Enterprise Manager Cloud Control 13c Release 5

Starting agent ........ started.

To Stop OEM agent (In Target Server):-

cd $AGENT_HOME/bin

[r12devdb@ccuine14 bin]$ ./emctl stop agent

Oracle Enterprise Manager Cloud Control 13c Release 5

Stopping agent ... stopped.

To check status of OEM agent(In Target Server):-

cd $AGENT_HOME/bin

[r12devdb@ccuine14 bin]$ ./emctl status agent

Oracle Enterprise Manager Cloud Control 13c Release 5

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

Agent Version : 13.5.0.0.0

OMS Version : 13.5.0.0.0

Protocol Version : 12.1.0.1.0

Agent Home : /DEV/agent/emagent/agent_inst

Agent Log Directory : /DEV/agent/emagent/agent_inst/sysman/log

Agent Binaries : /DEV/agent/emagent/agent_13.5.0.0.0

Core JAR Location : /DEV/agent/emagent/agent_13.5.0.0.0/jlib

Agent Process ID : 5265

Parent Process ID : 5115

Agent URL : https://ccuine14.xyz.co.in:3872/emd/main/

Local Agent URL in NAT : https://ccuine14.xyz.co.in:3872/emd/main/

Repository URL : https://ccuine25.xyz.co.in:4903/empbs/upload

Started at : 2021-12-22 10:48:33

Started by user : r12devdb

Operating System : Linux version 3.10.0-1062.el7.x86_64 (amd64)

Number of Targets : 5

Last Reload : (none)

Last successful upload : 2021-12-22 12:59:38

Last attempted upload : 2021-12-22 12:59:38

Total Megabytes of XML files uploaded so far : 5.58

Number of XML files pending upload : 0

Size of XML files pending upload(MB) : 0

Available disk space on upload filesystem : 24.53%

Collection Status : Collections enabled

Heartbeat Status : Ok

Last attempted heartbeat to OMS : 2021-12-22 13:00:24

Last successful heartbeat to OMS : 2021-12-22 13:00:24

Next scheduled heartbeat to OMS : 2021-12-22 13:01:24

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

Agent is Running and Ready

How to create windows service for Nodemanager/Weblogic/forms/reports in FMW 12.2.1.4

To start or stop weblogic, forms , reports in windows fusion middleware 12.2.1.4 we need to execute command manually through cmd.

It becomes hectic and time consuming to start and stop these services one by one. To ease the entire startup/shutdown process we can create services for Nodemanager, weblogic admin server , forms ,reports.

Create Nodemanager service:-

To create nodemanager service first, navigate to

D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\bin

Here Middleware home= D:\app\oracle\product\12.2.1.4

Right click on installNodeMgrSvc.cmd and run as as administrator

Open services.msc and start Oracle Weblogic prd Nodemanager Services. Keep the startup type manual.

Create Weblogic adminserver service:-

To create weblogic service first we need to create one batch file with following contents.

Installweblogicservice.bat

echo on

SETLOCAL

set DOMAIN_NAME=PRD

set USERDOMAIN_HOME=F:\app\oracle\product\12.2.1.4\user_projects\domains\prd

set SERVER_NAME=AdminServer

set WL_HOME=D:\app\oracle\product\12.2.1.4\wlserver

set PRODUCTION_MODE=true

set WLS_USER=weblogic

set WLS_PW=xyz123#

set MEM_ARGS=-Xms128m -Xmx512m

call "D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\bin\setDomainEnv.cmd"

call "D:\app\oracle\product\12.2.1.4\wlserver\server\bin\installSvc.cmd"

ENDLOCAL

Cmd /k

Run the above batch file using “Run as Administrator”, a Windows Service will be created.

Start service as shown below. Keep the startup type manual.

Create FORMS service:-

To create Forms service first we need to create one batch file with following contents.

Create one batch file with following contents.

Installformsserivce.bat

SETLOCAL

set MW_HOME=D:\app\oracle\product\12.2.1.4

set DOMAIN_NAME=prd

set USERDOMAIN_HOME=D:\app\oracle\product\12.2.1.4\user_projects\domains\prd

set SERVER_NAME=WLS_FORMS

set ADMIN_URL=http://CSAPP2:7001

set JAVA_OPTIONS=-Dweblogic.Stdout="D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\stdout.txt" -Dweblogic.Stderr="D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\stderr.txt"

set WL_HOME=D:\app\oracle\product\12.2.1.4\wlserver

set PRODUCTION_MODE=true

set MEM_ARGS=-Xms128m -Xmx512m

call "D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\bin\setDomainEnv.cmd"

call "D:\app\oracle\product\12.2.1.4\wlserver\server\bin\installSvc.cmd"

ENDLOCAL

Cmd /k

Run the above batch file using “Run as Administrator”, a Windows Service will be created.

Start service as shown below. Keep the startup type manual.

Create REPORTS service:-

To create Reports service first we need to create one batch file with following contents.

Create one batch file with following contents.

Installreportsserivce.bat

SETLOCAL

set MW_HOME=D:\app\oracle\product\12.2.1.4

set DOMAIN_NAME=prd

set USERDOMAIN_HOME=D:\app\oracle\product\12.2.1.4\user_projects\domains\prd

set SERVER_NAME=WLS_REPORTS

set ADMIN_URL=http://CSAPP2:7001

set WL_HOME=D:\app\oracle\product\12.2.1.4\wlserver

set PRODUCTION_MODE=true

set MEM_ARGS=-Xms128m -Xmx512m

call "D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\bin\setDomainEnv.cmd"

call "D:\app\oracle\product\12.2.1.4\wlserver\server\bin\installSvc.cmd"

ENDLOCAL

Cmd /k

Run the above batch file using “Run as Administrator”, a Windows Service will be created.

Start service as shown below. Keep the startup type manual.

Batch script to start ohs1 and Reports server:

If there are ohs and standalone reports services running , we can start them manually.

Create one batch file with following commands for startup ohs1 and reports server.

ohs_reports_startup.bat

D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\bin\startComponent.cmd ohs1 && D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\bin\startComponent.cmd rep_server1_prod

Batch script to shutdown ohs1 and Reports server:

Likewise startup script, create one batch file with following commands for shutting down ohs1 and reports server.

ohs_reports_shutdown.bat

D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\bin\stopComponent.cmd ohs1 && D:\app\oracle\product\12.2.1.4\user_projects\domains\prd\bin\stopComponent.cmd rep_server1_prod

Autoupgrade fails with error unable to open file LOGIN.SQL

Recently I faced one error while upgrading oracle database 11.2.0.4 to 19.3.0 using auto upgrade utility.

I checked the status of the job and found it was errored out.

So I quickly gone through the log files as mentioned in above and found few error messages like

2021-12-09 20:27:53.940 ERROR Database Upgrade Error in File [D:\oracle\autoupg19c\upg_logs\ORCL\orcl\101\dbupgrade\catupgrd20211209193550orcl6.log] on Database [D:\oracle\autoupg19c\upg_logs\ORCL\orcl\101\dbupgrade\catupgrd20211209193550orcl6.log] - CatctlThread.catctl

2021-12-09 20:27:54.202 ERROR

DATABASE NAME: orcl

CAUSE: ERROR at Line 58498 in [D:\oracle\autoupg19c\upg_logs\ORCL\orcl\101\dbupgrade\catupgrd20211209193550orcl7.log]

REASON: SP2-0310: unable to open file "LOGIN.SQL"

ACTION: [MANUAL]

DETAILS: - CheckForErrors.checkForErrors

Solution:-

Perform the below solution:

1. Set SQLPATH (and ORACLE_PATH) variable to desired directory path.

2. Move login.sql to the directory defined in SQLPATH (and ORACLE_PATH)

NOTE: If you do not have a login.sql file, then perform any ONE of the following :

a. if you have a backup of the original installation, check there and see if you can retrieve the login.sql if it existed.

-or-

b. try obtaining it from another similar/like installation and modify if needed.

-or-

c. create one:

Reference:- SP2-0310: Unable To Open File "LOGIN.SQL"(Doc ID 2277751.1)

How to determine if any certificates used by the OMS in OEM 13c

How to determine if any certificates used by the OMS

1.Run the command below to check the status of the OMS and gather its details for hostnames and ports and SLB/virtual hostname details

Login to OMS server

$export ORACLE_HOME=<%MIDDLEWARE_HOME%>
Example:
$export ORACLE_HOME= /data2/app/oracle/middleware

[oem@ccuine25 middleware]$ cd $ORACLE_HOME/bin

[oem@ccuine25 bin]$ ./emctl status oms -details

Oracle Enterprise Manager Cloud Control 13c Release 5

Enter Enterprise Manager Root (SYSMAN) Password :

Console Server Host : ccuine25.xyz.co.in

HTTP Console Port : 7788

HTTPS Console Port : 7803

HTTP Upload Port : 4889

HTTPS Upload Port : 4903

EM Instance Home : /data2/app/oracle/gc_inst/em/EMGC_OMS1

OMS Log Directory Location : /data2/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log

OMS is not configured with SLB or virtual hostname

Agent Upload is locked.

OMS Console is locked.

Active CA ID: 1

Console URL: https://ccuine25.xyz.co.in:7803/em

Upload URL: https://ccuine25.xyz.co.in:4903/empbs/upload

WLS Domain Information

Domain Name : GCDomain

Admin Server Host : ccuine25.xyz.co.in

Admin Server HTTPS Port: 7102

Admin Server is RUNNING

Oracle Management Server Information

Managed Server Instance Name: EMGC_OMS1

Oracle Management Server Instance Host: ccuine25.xyz.co.in

WebTier is Up

Oracle Management Server is Up

JVMD Engine is Up

2. If the OMS is running, then the certificates used by the OMS can be checked using any of the below procedures.

2a.   Verifying with "emctl secdiag" command.

       In Issuer field, if it is showing the OMS hostname and the certificate validity is 10 years, then it is Domo certificate.
To verify,

[oem@ccuine25 bin]$ ./emctl secdiag openurl -url https://ccuine25.xyz.co.in:4903/empbs/upload

Oracle Enterprise Manager Cloud Control 13c Release 5

Log file: /tmp/OpenPage_2021_12_06_13_23_497868447601063284563.log

Opening page: https://ccuine25.xyz.co.in:4903/empbs/upload

Using non-validating trust manager; all certificates will be blindly accepted.

Proxy server is not set

Using protocol: TLSv1

Negotiated protocol: TLSv1

Getting the certificate chain

Details of cert# 1 in chain:

Subject: CN=ccuine25.xyz.co.in

Issuer: CN=ccuine25.xyz.co.in, C=US, ST=CA, L=EnterpriseManager on ccuine25.xyz.co.in, OU=EnterpriseManager on ccuine25.xyz.co.in, O=EnterpriseManager on ccuine25.xyz.co.in

Valid from: Fri Nov 26 12:23:32 IST 2021

Valid till: Tue Nov 25 12:23:32 IST 2031

Serial#: -1562215274281161665

Public key: Sun RSA public key, 1024 bits

params: null

modulus: 115776305526868774367344434524839482517685951325472324400167322766776075414308990760886761862848426323857328397387316124782891769368223780434083793913289701480931965560434252568637538653810794338946757805777495146784750664597087203172476664248076937258722119323874400428320431663792757454380232885358450306227

public exponent: 65537

Signature algorithm: SHA512withRSA

Details of cert# 2 in chain:

Subject: CN=ccuine25.xyz.co.in, C=US, ST=CA, L=EnterpriseManager on ccuine25.xyz.co.in, OU=EnterpriseManager on ccuine25.xyz.co.in, O=EnterpriseManager on ccuine25.xyz.co.in

Issuer: CN=ccuine25.xyz.co.in, C=US, ST=CA, L=EnterpriseManager on ccuine25.xyz.co.in, OU=EnterpriseManager on ccuine25.xyz.co.in, O=EnterpriseManager on ccuine25.xyz.co.in

Valid from: Fri Nov 26 12:23:32 IST 2021

Valid till: Tue Nov 25 12:23:32 IST 2031

Serial#: 28927599574933379200

Public key: Sun RSA public key, 1024 bits

params: null

modulus: 137261211539843504670622418187766808702599679284971506435071556027263289359546098536039368340742298948567040033910861434050027205919002583115809760212060733257289863786523160874210003867026089884047769780727525741437911092772616647738894839923266126650810544284334240152637157515395133646710028090924769802959

public exponent: 65537

Signature algorithm: SHA512withRSA

Following headers are present in the response:

Date : Mon, 06 Dec 2021 07:53:50 GMT

X-ORACLE-DMS-RID : 0:1:1

Content-Length : 305

X-Content-Type-Options : nosniff

X-XSS-Protection : 1; mode=block

X-ORACLE-DMS-ECID : 6e4738a3-125a-4155-99a6-cf6ce1401fe0-00000002

X-ORCL-EMOA : true

Content-Type : text/html

Response saved at :

/tmp/ccuine25.xyz.co.in_4903_2021_12_06_13_23_507464916505254990497.html

In the above output we can see the hostname of OMS server and certificates’validy is of 10 years. This confirms OMS server uses a demo certificate.

2b. We can also verify using “openssl ” command

In Issuer field with i:, if it is showing the OMS hostname and the certificate then it is Domo certificate.

[oem@ccuine25 bin]$ openssl s_client -connect ccuine25.xyz.co.in:4903

CONNECTED(00000003)

depth=1 O = EnterpriseManager on ccuine25.xyz.co.in, OU = EnterpriseManager on ccuine25.xyz.co.in, L = EnterpriseManager on ccuine25.xyz.co.in, ST = CA, C = US, CN = ccuine25.xyz.co.in

verify error:num=19:self signed certificate in certificate chain

---

Certificate chain

0 s:/CN=ccuine25.xyz.co.in

i:/O=EnterpriseManager on ccuine25.xyz.co.in/OU=EnterpriseManager on ccuine25.xyz.co.in/L=EnterpriseManager on ccuine25.xyz.co.in/ST=CA/C=US/CN=ccuine25.xyz.co.in

1 s:/O=EnterpriseManager on ccuine25.xyz.co.in/OU=EnterpriseManager on ccuine25.xyz.co.in/L=EnterpriseManager on ccuine25.xyz.co.in/ST=CA/C=US/CN=ccuine25.xyz.co.in

i:/O=EnterpriseManager on ccuine25.xyz.co.in/OU=EnterpriseManager on ccuine25.xyz.co.in/L=EnterpriseManager on ccuine25.xyz.co.in/ST=CA/C=US/CN=ccuine25.xyz.co.in

---

Server certificate

-----BEGIN CERTIFICATE-----

MIICwTCCAiqgAwIBAgII6lHlfCnILD8wDQYJKoZIhvcNAQENBQAwgeExNTAzBgNV

BAoTLEVudGVycHJpc2VNYW5hZ2VyIG9uIGNjdWluZTI1LmV2ZXJlYWR5LmNvLmlu

MTUwMwYDVQQLEyxFbnRlcnByaXNlTWFuYWdlciBvbiBjY3VpbmUyNS5ldmVyZWFk

eS5jby5pbjE1MDMGA1UEBxMsRW50ZXJwcmlzZU1hbmFnZXIgb24gY2N1aW5lMjUu

ZXZlcmVhZHkuY28uaW4xCzAJBgNVBAgTAkNBMQswCQYDVQQGEwJVUzEgMB4GA1UE

AxMXY2N1aW5lMjUuZXZlcmVhZHkuY28uaW4wHhcNMjExMTI2MDY1MzMyWhcNMzEx

MTI1MDY1MzMyWjAiMSAwHgYDVQQDFBdjY3VpbmUyNS5ldmVyZWFkeS5jby5pbjCB

nzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEApN71Yh+N2IJXKw2LUApvpzVqG979

G1xKxqmZui/F6Acwz+cfdmBjzqfR5OwRoEHjnpEvnqVQLczAWL3Wb7jcFmDA9X0i

Rw83zh7nyN0O830NSjEIJPwQ5NRRX6o7+QgZhNZzXUPz4/1hViISS3rw2Ct2Zp1J

8FuWrVrYMcUB7LMCAwEAAaNAMD4wDAYDVR0TAQH/BAIwADAPBgNVHQ8BAf8EBQMD

B+gAMB0GA1UdDgQWBBTIGix4h0S0W22LzqNX21Vvh4j0ajANBgkqhkiG9w0BAQ0F

AAOBgQBqCQbGKb+so6mXmtyoWvJ9w4gr8r5HKTShW03DGMokspZ2wFXKf/7/0XKs

dom19rMwhzTjYFyLVxPpF2JaeQw2je9o0RwmNHI5YdHaCB66hdcwb4xK8e5NrmcU

f6TH/V5wAHQqvZMiJseyKa6w/LNDegAysBAX2FJ13FSyki8BZA==

-----END CERTIFICATE-----

subject=/CN=ccuine25.xyz.co.in

issuer=/O=EnterpriseManager on ccuine25.xyz.co.in/OU=EnterpriseManager on ccuine25.xyz.co.in/L=EnterpriseManager on ccuine25.xyz.co.in/ST=CA/C=US/CN=ccuine25.xyz.co.in

---

No client certificate CA names sent

Peer signing digest: SHA384

Server Temp Key: ECDH, P-256, 256 bits

---

SSL handshake has read 1957 bytes and written 415 bytes

---

New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES256-GCM-SHA384

Server public key is 1024 bit

Secure Renegotiation IS supported

Compression: NONE

Expansion: NONE

No ALPN negotiated

SSL-Session:

Protocol : TLSv1.2

Cipher : ECDHE-RSA-AES256-GCM-SHA384

Session-ID: CD135D17211057A2A2C8D090D6A539295AD8A463A48048ABF4102D16032F7C3D

Session-ID-ctx:

Master-Key: 77B489903B3E69A8ED19AC202FDB96F77905B85FB22EE76386CE9A6C46EC925427E453F2FED2B07889B833A294EBECF6

Key-Arg : None

Krb5 Principal: None

PSK identity: None

PSK identity hint: None

Start Time: 1638777708

Timeout : 300 (sec)

Verify return code: 19 (self signed certificate in certificate chain)

---

closed

2c.   Verifying the existing certificates are using SAN or not.

       If there is no SAN certificate below command will not return any results.

[oem@ccuine25 bin]$ openssl s_client -connect ccuine25.xyz.co.in:4903 </dev/null 2>/dev/null | openssl x509 -noout -text | grep DNS

If SAN certificates in use below command will return results like.

$ openssl s_client -connect <OMS_HOSTNAME>.<DOMAIN_NAME>:4903 </dev/null 2>/dev/null | openssl x509 -noout -text | grep DNS

DNS:<OMS_HOSTNAME>.<DOMAIN_NAME>, DNS:<OMS_HOSTNAME2>.<DOMAIN_NAME>, DNS:<SLBOMS_HOSTNAME>.<DOMAIN_NAME>

3. If the OMS is not running, you can check the certificates used by the OMS by reading the contents of the wallet used by the OMS.

If the Trusted certificate Subject is shows OMS hostname then it is Domo certificate.

export OMS_INST= /data2/app/oracle/gc_inst

cd $ORACLE_HOME/oracle_common/bin

[oem@ccuine25 bin]$./orapki wallet display -wallet $OMS_INST/user_projects/domains/GCDomain/config/fmwconfig/components/OHS/ohs1/keystores/upload/ -summary

Oracle PKI Tool : Version 12.2.1.4.0

Requested Certificates:

User Certificates:

Subject: CN=ccuine25.xyz.co.in

Trusted Certificates:

Subject: CN=ccuine25.xyz.co.in,C=US,ST=CA,L=EnterpriseManager on ccuine25.xyz.co.in,OU=EnterpriseManager on ccuine25.xyz.co.in,O=EnterpriseManager on ccuine25.xyz.co.in