Month: September 2024

How to Connect to an Oracle Autonomous Database

Connecting to an Autonomous Database using SQL Developer

Firstly download a wallet for an Autonomous Database.
Overview>Autonomous Database>Autonomous Database details>Database connection>Wallet type ( Instance wallet )>Download by giving password.

Then open SQL Developer on your local computer. In the Connections panel, right-click Connections and select New Connection.

Connecting to an Autonomous Database using tnsnames.ora

Copy and unzip the wallet file under TNS_ADMIN:

$ cat sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/oracle/ADWDB_WALLET")))
SSL_SERVER_DN_MATCH=no
$ sqlplus admin@adwdb_high
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 1 13:13:45 2024
Version 19.23.0.0.0

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

Enter password:
Last Successful login time: Tue Oct 01 2024 13:08:39 +10:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.1.0

SQL>

Connecting to an Autonomous Database with an Easy Connect String

$ sqlplus admin@tcps://adwdb.adb.ap-melbourne-1.oraclecloud.com:1522/g12345f7da5af88_adwdb_high.adb.oraclecloud.com?ssl_server_dn_match=no

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 1 13:21:15 2024
Version 19.23.0.0.0

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

Enter password:
Last Successful login time: Tue Oct 01 2024 13:13:52 +10:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.1.0

SQL>

Without “ssl_server_dn_match=no”, you might get “ORA-29002: SSL transport detected invalid or obsolete server certificate.”

$ sqlplus admin@tcps://adwdb.adb.ap-melbourne-1.oraclecloud.com:1522/g12345f7da5af88_adwdb_high.adb.oraclecloud.com

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 1 13:31:45 2024
Version 19.23.0.0.0

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

Enter password:
ERROR:
ORA-29002: SSL transport detected invalid or obsolete server certificate.


Enter user-name:

You can put both “ssl_server_dn_match’ and “wallet_location” in command line together:

$  sqlplus admin@tcps://adwdb.adb.ap-melbourne-1.oraclecloud.com:1522/g12345f7da5af88_adwdb_high.adb.oraclecloud.com?ssl_server_dn_match=no?wallet_location=/home/oracle/ADWDB_WALLET

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 1 13:39:31 2024
Version 19.23.0.0.0

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

Enter password:
Last Successful login time: Tue Oct 01 2024 13:38:54 +10:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.1.0

SQL>

Installing a 13.5 Management Agent Using the agentDeploy Script

We can install a Management Agent using the agentDeploy.sh script in the following ways:

Using EM CLI from the remote Destination Host

Acquiring the Management Agent Software and Downloading it onto the destination Host Using EM CLI.

Download and Set up EM CLI on the destination host.

The rest steps will be same as the followings, so we don’t repeat again.

Using EM CLI from the OMS Host

Acquiring the Management Agent Software and Downloading it onto the OMS Host Using EM CLI.

On OMS server host:

$ emcli login -username=sysman
Enter password :

Login successful

$ emcli sync
Synchronized successfully

$ emcli get_supported_platforms
-----------------------------------------------
Version = 13.5.0.0.0
 Platform = Linux x86-64
-----------------------------------------------
Platforms list displayed successfully.

Download the Management Agent software from the Software Library to a temporary directory on the OMS host:

$ emcli get_agentimage -destination=/tmp/agentimage  -platform="Linux x86-64" -version=13.5.0.0.0
 === Partition Detail ===
Space free : 7 GB
Space required : 1 GB
Check the logs at /u01/app/oracle/product/13.5.0/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/get_agentimage_2024-09-30_20-25-31-PM.log
Downloading /tmp/agentimage/13.5.0.0.0_AgentCore_226.zip
File saved as /tmp/agentimage/13.5.0.0.0_AgentCore_226.zip
Downloading /tmp/agentimage/13.5.0.0.0_Plugins_226.zip
File saved as /tmp/agentimage/13.5.0.0.0_Plugins_226.zip
Downloading /tmp/agentimage/unzip
File saved as /tmp/agentimage/unzip
Executing command: /tmp/agentimage/unzip /tmp/agentimage/13.5.0.0.0_Plugins_226.zip -d /tmp/agentimage
Archieving agentImage and plugins.
Exit status is:0
Agent Image Download completed successfully.

$ ls -ltr  /tmp/agentimage/
total 774044
-rw-r--r--. 1 oracle oinstall 792618975 Sep 30 20:25 13.5.0.0.0_AgentCore_226.zip

Transferring the Management Agent Software to the Destination Host /tmp directory.

Unzip the agent image into a temp directory:

$ unzip /tmp/13.5.0.0.0_AgentCore_226.zip -d agentimage/

Archive:  13.5.0.0.0_AgentCore_226.zip
  inflating: agentimage/agentcore.bin
  inflating: agentimage/agent.rsp
  inflating: agentimage/agentDeploy.sh
  inflating: agentimage/agentimage.properties
  inflating: agentimage/unzip
  inflating: agentimage/zip
   creating: agentimage/plugins/
  inflating: agentimage/plugins/oracle.sysman.emas.discovery.plugin-13.5.1.0.0.farb
  inflating: agentimage/plugins/oracle.sysman.si.discovery.plugin-13.5.1.0.0.farb
  inflating: agentimage/plugins/oracle.fmw.gg.discovery.plugin-13.5.2.0.0.farb
  inflating: agentimage/plugins/oracle.sysman.db.discovery.plugin-13.5.1.0.0.farb
  inflating: agentimage/plugins/oracle.sysman.xa.discovery.plugin-13.5.1.0.0.farb
  inflating: agentimage/plugins/oracle.sysman.oh.discovery.plugin-13.5.0.0.0.farb
  inflating: agentimage/plugins/oracle.sysman.oh.agent.plugin-13.5.0.0.0.farb
  inflating: agentimage/plugins.txt
   creating: agentimage/oneoffs/
  inflating: agentimage/oneoffs/agentDeploy.sh
  inflating: agentimage/oneoffs/agentConfig.jar
  inflating: agentimage/oneoffs/agentInstaller.jar
  inflating: agentimage/oneoffs/applypatchesonapplicablehome.pl
  inflating: agentimage/oneoffs/opatch_common.pl

Edit agent.rsp

$ cat agentimage/agent.rsp | grep -v "#"

OMS_HOST=omsserver
EM_UPLOAD_PORT=4903
AGENT_BASE_DIR=/u01/app/oracle/product/13.5.0/agent_1
AGENT_PORT=3872
EM_INSTALL_TYPE="AGENT"

Install agent now.

$ /tmp/agentimage/agentDeploy.sh RESPONSE_FILE=agentimage/agent.rsp
...
..
Waiting for agent targets to get promoted...
Successfully Promoted agent and its related targets to Management Agent

Installing Oracle Management Agent in Silent Mode by Using the AgentPull Script

This is to demonstrate how to install 13.5 Oracle Management Agent in Silent Mode by Using the AgentPull Script .

Note: To install a Management Agent using the AgentPull script, you DO NOT need to download the Management Agent software onto the destination host. The AgentPull script performs this action automatically.

Get AgentPull script from OMS by running the following command, here we are going to install 13.5 agent on Linux platform.

On web to download AgentPull.sh script bu the following URL:
https:/OMS_HOST:OMS_PORT/em/install/getAgentImage

OR

$ cd /tmp
$ curl "https://OMS_HOST:OMS_PORT/em/install/getAgentImage" --insecure -o AgentPull.sh
$ chmod +x /tmp/AgentPull.sh

Identify the Platform by :

$ ./AgentPull.sh  -showPlatforms

Platforms       Version
Linux x86-64    13.5.0.0.0

Create a response file for installing the new agent:

$ cat /tmp/agent.properties
LOGIN_USER=sysman
LOGIN_PASSWORD=sysmanpasswd
PLATFORM="Linux x86-64"
AGENT_REGISTRATION_PASSWORD=abcd1234
AGENT_BASE_DIR=/u01/app/oracle/product/13.5.0/agent_1

Install OEM agent by running the following command now:

$ /tmp/AgentPull.sh RSPFILE_LOC=/tmp/agent.properties

The new agent should be installed and registered on OMS successfully.

But I got agent evaluation errors on OMS for newly installed agent. it is resolved through:

  • Disabled firewalld
  • EM 13c: Agent Shows Availability Evaluation Error with Message: Target data is currently being uploaded to the repository (Doc ID 2379296.1)

ORA-15099: disk is larger than maximum size of 2097152 MBs

The following errors occur when creating a new ASM diskgroup:

SQL> CREATE DISKGROUP DATA1 EXTERNAL REDUNDANCY disk 'AFD:DATA1_DISK1' NAME DATA1_DISK1;
CREATE DISKGROUP DATA1 EXTERNAL REDUNDANCY disk 'AFD:DATA1_DISK1' NAME DATA1_DISK1;
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15099: disk 'AFD:DATA1_DISK1' is larger than maximum size of 2097152 MBs

SOLUTION

SQL> CREATE DISKGROUP DATA1 EXTERNAL REDUNDANCY disk 'AFD:DATA1_DISK1' NAME DATA1_DISK1 ATTRIBUTE 'compatible.rdbms' = '19.0', 'compatible.asm' = '19.0';

Diskgroup created.

M 13c: Enterprise Manager 13c Cloud Control Agent Control Commands Respond with Message: Invalid EMSTATE valueEMD

The “Invalid EMSTATE valueEMD” error occurs when reload/upload/pingOMS agent:

oracle:/u01/app/oracle/product/13.5.0/agent_1/agent_inst/bin$ ./emctl reload  agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Invalid EMSTATE valueEMD reload completed successfully

Check what value is for EMSTATE :

$ cat emctl
#!/bin/sh -f
#++
#
#  16-dec-02.15:38:56 vnukal
#
# Copyright (c) 2002, 2019, Oracle and/or its affiliates. All rights reserved.
#
#  emctl - control script for state-only agent installs.
#
#    MODIFIED   (MM/DD/YY)
#    mkiran       11/27/11 - 13417393: Pass args via "$@" instead of $*
#    vnukal       12/16/02 - Creation
#--
REMOTE_EMDROOT=/u01/app/oracle/product/13.5.0/agent_1/agent_13.5.0.0.0
export REMOTE_EMDROOT
REMOTE_EMDROOT_AGHOME=/u01/app/oracle/product/13.5.0/agent_1/agent_13.5.0.0.0
export REMOTE_EMDROOT_AGHOME
EMSTATE=/u01/app/oracle/product/13.5.0/agent_1/agent_inst
export EMSTATE
/u01/app/oracle/product/13.5.0/agent_1/agent_13.5.0.0.0/bin/emctl "$@"

SOLUTION

The issue is that the group has write privilege for the directory agent_inst. This is not allowed, group should only have read and execute privilege .

Run the chmod utility to change the the access permissions as follows:

chmod g-w agent_inst

$ ./emctl upload  agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully
$ ./emctl pingOMS
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD pingOMS completed successfully
$ ./emctl reload  agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD reload completed successfully