1. Using Oracle Infrastructure Monitoring
  2. Discovery
  3. Add Oracle Databases

Add Oracle Databases

You can add Oracle Database entities using the cloud agent command line interface (omcli) with the appropriate JSON files. Alternatively, these entities can be added as part of a Database System.

Note:

Oracle Management Cloud allows you to add all database components (DB, ASM, listener, etc.) as a single composite entity via the Oracle database system entity type. See Add Oracle Database Systems.

Step 1: Prepare Oracle Database for monitoring.

Prerequisites

Setting Up Monitoring Credentials for Oracle Database

Before you can begin monitoring DB systems, you must have the necessary privileges. A SQL script (grantPrivileges.sql) is available to automate granting these privileges. This script must be run as the Oracle DB SYS user. In addition to granting privileges, the grantPrivileges.sql script can also be used to create new or update existing monitoring users with the necessary privileges. For information about this SQL script, location and usage instructions, see Creating the Oracle Database monitoring credentials for Oracle Management Cloud (Doc ID 2401597.1).

Enabling TCPS Connections

Database Side (Single Instance)
  1. Create the wallets.
    mkdir -p /scratch/aime/wallets/rwallets
mkdir -p /scratch/aime/wallets/swallets
mkdir -p /scratch/aime/wallets/cwallets
  2. To run the orapki commands go to the Oracle Home and run the following commands:
    cd $ORACLE_HOME/bin

echo "**************** Create Root wallet *******************"

./orapki wallet create -wallet /scratch/aime/wallets/rwallets -auto_login -pwd oracle123

./orapki wallet add -wallet /scratch/aime/wallets/rwallets -dn "C=US,O=Oracle Corporation,CN=RootCA" -keysize 2048 -self_signed -validity 365 -pwd oracle123 -addext_ski -sign_alg sha256

./orapki wallet export -wallet /scratch/aime/wallets/rwallets -dn "C=US,O=Oracle Corporation,CN=RootCA" -cert /scratch/aime/wallets/rwallets/cert.pem

./orapki wallet display -wallet /scratch/aime/wallets/rwallets

openssl x509 -noout -text -in /scratch/aime/wallets/rwallets/cert.pem

echo "**************** Create server wallet *******************"

./orapki wallet create -wallet /scratch/aime/wallets/swallets -auto_login -pwd oracle123

./orapki wallet add -wallet /scratch/aime/wallets/swallets -trusted_cert -cert /scratch/aime/wallets/rwallets/cert.pem -pwd oracle123

./orapki wallet add -wallet /scratch/aime/wallets/swallets -dn "C=US,O=Oracle Corporation,CN=DBServer" -keysize 2048 -pwd oracle123 -addext_ski -sign_alg sha256

./orapki wallet export -wallet /scratch/aime/wallets/swallets -dn "C=US,O=Oracle Corporation,CN=DBServer" -request /scratch/aime/wallets/swallets/csr.pem

./orapki cert create -wallet /scratch/aime/wallets/rwallets -request /scratch/aime/wallets/swallets/csr.pem -cert /scratch/aime/wallets/swallets/cert.pem -validity 365 -sign_alg sha256 -serial_num $(date +%s%3N)

./orapki wallet add -wallet /scratch/aime/wallets/swallets -user_cert -cert /scratch/aime/wallets/swallets/cert.pem -pwd oracle123

openssl x509 -noout -text -in /scratch/aime/wallets/swallets/cert.pem

./orapki wallet display -wallet /scratch/aime/wallets/swallets
 
echo "**************** Create client wallet *******************"
 
./orapki wallet create -wallet /scratch/aime/wallets/cwallets -auto_login -pwd oracle123

./orapki wallet add -wallet /scratch/aime/wallets/cwallets -trusted_cert -cert /scratch/aime/wallets/rwallets/cert.pem -pwd oracle123

./orapki wallet add -wallet /scratch/aime/wallets/cwallets -dn "C=US,O=Oracle Corporation,CN=DBClient" -keysize 2048 -pwd oracle123 -addext_ski  -sign_alg sha256

./orapki wallet export -wallet /scratch/aime/wallets/cwallets -dn "C=US,O=Oracle Corporation,CN=DBClient" -request /scratch/aime/wallets/cwallets/csr.pem

./orapki cert create -wallet /scratch/aime/wallets/rwallets -request /scratch/aime/wallets/cwallets/csr.pem -cert /scratch/aime/wallets/cwallets/cert.pem -validity 365  -sign_alg sha256 -serial_num $(date +%s%3N)

./orapki wallet add -wallet /scratch/aime/wallets/cwallets -user_cert -cert /scratch/aime/wallets/cwallets/cert.pem -pwd oracle123

openssl x509 -noout -text -in /scratch/aime/wallets/cwallets/cert.pem

./orapki wallet display -wallet /scratch/aime/wallets/cwallets
  3. Change the mode of ewallet.p12.
    chmod 666 /scratch/aime/wallets/swallets/ewallet.p12
chmod 666 /scratch/aime/wallets/cwallets/ewallet.p12

Listener Changes

Running SI on TCPS (Single Instance)

  1. Create the Oracle Home.

  2. Create a listener using TCP protocol (such as LIST).

  3. Create a DB in the Oracle Home using the Listener created in Step 2. The Database and Listener might already be present.

  4. Shut down the database instance.

  5. Stop the Listener. 
    ./lsnrctl stop LIST
  6. Perform the following procedure.
     Set the environment variables

export WALLET_LOCATION=/net/slc05puy/scratch/dbwallets

The wallet is already created and stored here. Make sure the wallet location is accessible from the current host. 

export ORACLE_HOME=scratch/aimedb/12.1.0/12.1.0.2/dbhome_1
export ORACLE_SID=solsi

Back up the listener.ora, sqlnet.ora and tnsnames.ora files.
 
cp $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora.bckp
cp $ORACLE_HOME/network/admin/sqlnet.ora $ORACLE_HOME/network/admin/sqlnet.ora.bckp
cp $ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora.bckp

If sqlnet.ora is not present, create it. 
touch $ORACLE_HOME/network/admin/sqlnet.ora
  7. Modifying the ora files.
    Listener.ora

Replace all 'TCP' with 'TCPS'

sed -i 's/TCP/TCPS/'  $ORACLE_HOME/network/admin/listener.ora

Replace all '43434' with '2484' [43434 being the old listener port number]

sed -i 's/34343/2484/' $ORACLE_HOME/network/admin/listener.ora

Before executing the above shell commands, make sure you don’t have any string other than the protocol which contains “TCP”. This also applies to the for Listener port.
 
echo "SSL_CLIENT_AUTHENTICATION = TRUE" >> $ORACLE_HOME/network/admin/listener.ora;

echo "WALLET_LOCATION =(SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = $WALLET_LOCATION/swallets)))" >> $ORACLE_HOME/network/admin/listener.ora;

echo "SSL_VERSION = 1.2" >> $ORACLE_HOME/network/admin/listener.ora;  ** Only if TLS version has to be 1.2
 
[SSL_VERSION = 1.2 or 1.1 or 1.0]

Sqlnet.ora

echo "SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)" >> $ORACLE_HOME/network/admin/sqlnet.ora;
echo "SSL_CLIENT_AUTHENTICATION = TRUE" >> $ORACLE_HOME/network/admin/sqlnet.ora;
echo "WALLET_LOCATION =(SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = $WALLET_LOCATION/swallets)))" >> $ORACLE_HOME/network/admin/sqlnet.ora;
echo "SSL_VERSION = 1.2" >> $ORACLE_HOME/network/admin/sqlnet.ora;  
** Only if TLS version has to be 1.2

  8. Start the listener (./lsnrctl start LIST)

  9. Start the database instance.

  10. Run ./lsnrctl status LIST and check if the listener is running on TCPS with 2484 as the port and is associated with the database.
    ./lsnrctl status LTLS
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-APR-2016 13:03:54
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=myhost.myco.com)(PORT=2484)))

STATUS of the LISTENER
------------------------
Alias                     LTLS
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                06-APR-2016 10:41:33
Uptime                    0 days 2 hr. 22 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF

Listener Parameter File
/scratch/12102tls12/product/dbhome_1/network/admin/listener.ora

Listener Log File      
/scratch/12102tls12/diag/tnslsnr/myhost/ltls/alert/log.xml

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=myhost.myco.com)(PORT=2484)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2484)))

Services Summary...
Service "sitls" has 1 instance(s).
  Instance "sitls", status READY, has 1 handler(s) for this service...
Service "sitlsXDB" has 1 instance(s).
  Instance "sitls", status READY, has 1 handler(s) for this service...
The command completed successfully.
    You can see in the example that the database is now associated with the listener. If it is not, check whether the database local_listener parameter is set to the listener’s connect descriptor.

    alter system set local_listener='<CONNECT DESCRIPTOR FOR NEW LISTENER PORT>';

    Example: alter system set local_listener=' (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=strka31.myco.com)(PORT=2484)))’;

    Once done, bounce the database instance. Even after doing this, if the database is not getting associated with the listener, and the listener is up and running without any issue, go to the ORACLE HOME and create a brand new database out of it using DBCA. It will prompt you to use the listener you just secured, and which is up and running on TCPS protocol.

TCPS Credentials

In order to establish secure communication with the Oracle Database, you must add TCPS Database Credential Properties to the credential JSON file in order to add the Oracle Database entity.

  • connectionTrustStoreLocation: Your server/trust Key Store Location. This property is used to specify the location of the trust store. A trust store is a key store that is used when making decisions about which clients and servers can be trusted. The property takes a String value that specifies a valid trust store location.

  • connectionTrustStoreType: Your server/trust Key Store Type. This property denotes the type of the trust store. It takes a String value. Any valid trust store type supported by SSL can be assigned to this property.

  • connectionTrustStorePassword: Your server/trust Key Store Password. This property is used to set the password for the trust store. The trust store password is used to check the integrity of the data in the trust store before accessing it. The property takes a String value.

  • connectionKeyStoreLocation: Your client Key Store Location. This property is used to specify the location of the key store. A key store is a database of key material that are used for various purposes, including authentication and data integrity. This property takes a String value.

  • connectionKeyStoreType: Your client Key Store Type. This property denotes the type of the key store. It takes a String value. Any valid key store type supported by SSL can be assigned to this property.

  • connectionKeyStorePassword: Your client Key Store Password. This property specifies the password of the key store. This password value is used to check the integrity of the data in the key store before accessing it. This property takes a String value.

Agent Properties
Client authority

./omcli setproperty agent -name connectionKeyStoreLocation -value /scratch/aime/wallets/cwallets/ewallet.p12
./omcli setproperty agent -name connectionKeyStoreType -value sha256
./omcli setproperty agent -name connectionKeyStorePassword -value oracle123

Server authority

./omcli setproperty agent -name connectionTrustStoreLocation -value /scratch/aime/wallets/swallets/ewallet.p12
./omcli setproperty agent -name connectionTrustStorePassword -value oracle123
./omcli setproperty agent -name connectionTrustStoreType -value sha256
Once set, bounce the Agent. 
./omcli stop agent
./omcli start agent

Note:

Make sure that the above wallet is accessible at the agent location.

Step 2: Add the Oracle Database using omcli and the Appropriate JSON Files

  1. Download and extract the required JSON file(s) from the master JSON zip file. See the table below for the specific JSON files you'll need.
  2. Edit the file(s) and specify the requisite properties shown below.
    Oracle Database System (single instance) JSON Properties and Files

    Definition File: omc_oracle_db_system_SI.json

    • name: Your Oracle Database Entity Name. Will also be used for the Database System name
    • displayName: Your Oracle Database Entity Display Name. Will also be used for the Database System display name.
    • timezoneRegion: Your timezone
    • omc_dbsys_config: Configuration for DB System. Here, it is SI.
    • omc_dbsys_name_qualifier: Name that will be used to de-duplicate, if needed, the auto-generated names for the Listener and Cluster (SCAN) Listener. Generated name will be hostname-of-listener_Listener Alias
    • host_name:Name of the listener host that will be used to create the connect string to the database (host:port:SID or host:port:ServiceName)
    • omc_dbsys_port: Listener port number used for connection requests
    • omc_dbsys_connect_type: Specify type of connection: SID or Service Name
    • omc_dbsys_connect_value: The value of the SID or Service Name
    • omc_dbsys_lsnr_alias: Value of Listener Alias
    • omc_dbsys_home: Oracle Home directory of the Listener
    • capability: monitoring

    Credential File: omc_oracle_db_system_creds_SI_local.json

    • DBUserName : Your Database User Name
    • DBPassword : Your Database Password
    • DBRole : Your Database User Role. Default : Normal
    • If Remote:

    Credential File: omc_oracle_db_system_creds_SI_with_SSH.json

    • SSHUserName: Your SSH user used to remotely logon to the listener host
    • SSHUserPassword : Your SSH host Password
    • SSH_PVT_KEY: Path of your private key file. This private key is optional if the keys are generated at default location <user home>/.ssh
    • sshdPort: SSH port
  3. Add the entity using omcli.
    omcli add_entity agent DEFINITION_FILE [-credential_file CREDENTIAL_FILE [-encryption_method_gpg]]
  4. Verify the status of the newly added entity. 
    omcli status_entity agent DEFINITION_FILE

See step 4. Adding Entities to Your Service of Add Entities Using JSON Files for more information.

Step 3: (Optional but recommended) Set up alerts.

To enable lights-out monitoring, you can set up alert rules to generate alerts and send notifications if your entities have performance issues.

See Set Up Alert Rules and Set Up Alert Thresholds and Notifications.

Troubleshooting

If you run into any issues regarding discovery or monitoring of Oracle Database, see the following: