Monitoring a Database

This topic explains how to set up an Enterprise Manager Database Control console to monitor a version 11.2.0.4 database. The console is a web-based database management tool inside the Oracle database. You can use the console to perform basic administrative tasks such as managing user security, memory, and storage, and view performance information.

Required IAM Policy

Some of the procedures below require permission to create or update security lists. For more information about security list policies, see Security Lists.

Monitoring a Database with Enterprise Manager Express

On 1- and 2-node RAC DB Systems, by default, the EM Express console is not enabled on version 18.1.0.0, 12.2.0.1, and 12.1.0.2 databases. You can enable it for an existing database as described below, or you can enable it when you create a database by using the Database Commands command with the -co parameter.

You must also update the security list and iptables for the DB system as described later in this topic.

When you enable the console, you'll set the port for the console. The procedure below uses port 5500, but each additional console enabled on the same DB system will have a different port.

To enable the EM Express console and determine its port number
  1. SSH to the DB system, log in as opc, sudo to the oracle user, and log in to the database as SYS.

    sudo su - oracle
    . oraenv
     <provide the database SID at the prompt>
    sqlplus / as sysdba
    
  2. Do one of the following:

    • To enable the console and set its port, use the following command.

      exec DBMS_XDB_CONFIG.SETHTTPSPORT(<port>);

      For example:

      SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);
      
      PL/SQL procedure successfully completed.
      
    • To determine the port for a previously enabled console, use the following command.

      select dbms_xdb_config.getHttpsPort() from dual;

      For example:

      SQL> select dbms_xdb_config.getHttpsPort() from dual;
      
      DBMS_XDB_CONFIG.GETHTTPSPORT()
      ------------------------------
                                5500
      
  3. Return to the operating system by typing exit and then confirm that the listener is listening on the port:

    
    lsnrctl status | grep HTTP
    
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xxx.us.oracle.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/prod/xdb_wallet))(Presentation=HTTP)(Session=RAW))
    
  4. If you're using a 2-node RAC DB system, see To set the required permissions on a 2-node RAC DB system.
  5. Open the console's port as described in Opening Ports on the DB System.
  6. Update the security list for the console's port as described in Updating the Security List for the DB System.
To set the required permissions on a 2-node RAC DB system

If you're using a 2-node RAC DB system, you'll need to add read permissions for the asmadmin group on the wallet directory on both nodes in the system.

  1. SSH to one of the nodes in the DB system, log in as opc, sudo to the grid user.

    [opc@dbsysHost1 ~]$ sudo su - grid
    [grid@dbsysHost1 ~]$ . oraenv
    ORACLE_SID = [+ASM1] ?
    The Oracle base has been set to /u01/app/grid
    
  2. Get the location of the wallet directory, shown in red below in the command output.

    [grid@dbsysHost1 ~]$ lsnrctl status | grep xdb_wallet
    
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=dbsysHost1.sub04061528182.dbsysapril6.oraclevcn.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/dbsys12_phx3wm/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  3. Return to the opc user, switch to the oracle user, and change to the wallet directory.

    [opc@dbsysHost1 ~]$ sudo su - oracle
    [oracle@dbsysHost1 ~]$ cd /u01/app/oracle/admin/dbsys12_phx3wm/xdb_wallet
  4. List the directory contents and note the permissions.

    
    [oracle@dbsysHost1 xdb_wallet]$ ls -ltr
    total 8
    -rw------- 1 oracle asmadmin 3881 Apr  6 16:32 ewallet.p12
    -rw------- 1 oracle asmadmin 3926 Apr  6 16:32 cwallet.sso
    
  5. Change the permissions:

    
    [oracle@dbsysHost1 xdb_wallet]$ chmod 640 /u01/app/oracle/admin/dbsys12_phx3wm/xdb_wallet/*
  6. Verify that read permissions were added.

    [oracle@dbsysHost1 xdb_wallet]$ ls -ltr
    total 8
    -rw-r----- 1 oracle asmadmin 3881 Apr  6 16:32 ewallet.p12
    -rw-r----- 1 oracle asmadmin 3926 Apr  6 16:32 cwallet.sso
    
  7. Important! Repeat the steps above on the other node in the cluster.
To connect to the EM Express console

After you've enabled the console and opened its port in the security list and iptables, you can connect as follows:

  1. From a web browser, connect to the console using the following URL format:

    https://<ip_address>:<port>/em

    For example, https://129.145.0.164:5500/em

    Use the DB system's private or public IP address depending on your network configuration.

    Use the private IP address to connect to the system from your on-premises network, or from within the virtual cloud network (VCN). This includes connecting from a host located on-premises connecting through a VPN or FastConnect to your VCN, or from another host in the same VCN. Use the Exadata system's public IP address to connect to the system from outside the cloud (with no VPN). You can find the IP addresses in the Oracle Cloud Infrastructure Console as follows:

    • Cloud VM clusters (new resource model): On the Exadata VM Cluster Details page, click Virtual Machines in the Resources list.
    • DB systems: On the DB System Details page, click Nodes in the Resources list.

    The values are displayed in the Public IP Address and Private IP Address & DNS Name columns of the table displaying the Virtual Machines or Nodes of the Exadata Cloud Service instance.

  2. A login page is displayed and you can log in with any valid database credentials.

    This screen shot shows the Login page for EM Express.

    The Database Home page is displayed.

    This screen shot shows the Database Home page in EM Express.

To learn more about EM Express, see Introduction to Oracle Enterprise Manager Database Express.

Note

If you're using a 1-node DB system, and you are unable to connect to the EM Express console, see Database Known Issues.

Monitoring a Database with Enterprise Manager Database Control

By default, the Enterprise Manager Database Control console is not enabled on version 11.2.0.4 databases. You can enable the console:

  • when you create a container database by using the Database Commands with the -co parameter
  • for an existing container database as described here.

Port 1158 is the default port used for the first console enabled on the DB system, but each additional console enabled on the DB system will have a different port.

Note

For a version 11.2.0.4 database on a 2-node RAC DB system, see To enable the console for a version 11.2.0.4 database on a multi-node DB system.

To determine the port for the Enterprise Manager Database Control console
  1. SSH to the DB system, log in as opc, and sudo to the oracle user.

    sudo su - oracle
    . oraenv
     <provide the database SID at the prompt>
  2. Use the following command to get the port number.

    emctl status dbconsole

    The port is in the URL, as shown in the following example:

    [oracle@dbsys ~]$ emctl status dbconsole
    Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
    Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
    https://dbprod:1158/em/console/aboutApplication
    Oracle Enterprise Manager 11g is running.
    ------------------------------------------------------------------
    Logs are generated in directory /u01/app/oracle/product/11.2.0.4/dbhome_2/dbprod_db11/sysman/log
    
    
  3. Open the console's port as described in Opening Ports on the DB System.
  4. Update the security list for the console's port as described in Updating the Security List for the DB System.
To connect to the Enterprise Manager Database Control console

After you've enabled the console and opened its port in the security list and iptables, you can connect as follows:

  1. From a web browser, connect to the console using the following URL format:

    https://<ip_address>:<port>/em

    For example, https://129.145.0.164:1158/em

    Use the DB system's private or public IP address depending on your network configuration.

    Use the private IP address to connect to the system from your on-premises network, or from within the virtual cloud network (VCN). This includes connecting from a host located on-premises connecting through a VPN or FastConnect to your VCN, or from another host in the same VCN. Use the Exadata system's public IP address to connect to the system from outside the cloud (with no VPN). You can find the IP addresses in the Oracle Cloud Infrastructure Console as follows:

    • Cloud VM clusters (new resource model): On the Exadata VM Cluster Details page, click Virtual Machines in the Resources list.
    • DB systems: On the DB System Details page, click Nodes in the Resources list.

    The values are displayed in the Public IP Address and Private IP Address & DNS Name columns of the table displaying the Virtual Machines or Nodes of the Exadata Cloud Service instance.

  2. A login page will be displayed and you can log in with any valid database credentials.

To learn more about Enterprise Manager Database Control, see Introduction to Oracle Enterprise Manager Database Control.

To enable the console for a version 11.2.0.4 database on a multi-node DB system

A few extra steps are required to enable the console for a version 11.2.0.4 database on a multi-node DB system.

Configure SSH Equivalency Between the Two Nodes

You'll create SSH keys on each node and copy the key to the other node, so that each node has the keys for both nodes. The following procedure uses the sample names node1 and node2.

  1. SSH to node1, log in as opc, and sudo to the oracle user.

    sudo su - oracle
    
  2. Create a directory called .ssh, set its permissions, create an RSA key, and add the public key to the authorized_keys file.

    mkdir .ssh
    chmod 755 .ssh
    ssh-keygen -t rsa
    cat id_rsa.pub > authorized_keys 
  3. Repeat the previous steps on the other node in the cluster.
  4. On each node, add the id_rsa.pub key for the other node to the authorized_keys file.

    When you're done, you should see both keys in authorized_keys on each node.

  5. On node1, create the known_hosts file by doing the following:

    • SSH to node1 and reply yes to the authentication prompt.
    • SSH to node2 and reply yes to the authentication prompt.
  6. On node2, create the known_hosts file by doing the following:

    • SSH to node2 and reply yes to the authentication prompt.
    • SSH to node1 and reply yes to the authentication prompt.
  7. On node1, verify that SSH equivalency is now configured by using the following Cluster Verification Utility (CVU) command.

    cluvfy stage -pre crsinst -n all -verbose

Configure the Console

  1. On node1, create a file called emca.rsp with the following entries.

    DB_UNIQUE_NAME=<pdb_unique_name>
    SERVICE_NAME=<db_unique_name>.<db_domain>
    PORT=<scan listener port>
    LISTENER_OH=$GI_HOME
    SYS_PWD=<admin password>
    DBSNMP_PWD=<admin password>
    SYSMAN_PWD=<admin password>
    CLUSTER_NAME=<cluster name>     <=== to get the cluster name, run: $GI_HOME/bin/cemutlo -n 
    ASM_OH=$GI_HOME
    ASM_SID=+ASM1
    ASM_PORT=<asm listener port>
    ASM_USER_NAME=ASMSNMP
    ASM_USER_PWD=<admin password>
  2. On node1, run Enterprise Manager Configuration Assistant (EMCA) using the emca.rsp file as input.

    $ORACLE_HOME/bin/emca -config dbcontrol db -repos create -cluster -silent -respFile  <location of response file above>
  3. On node2, configure the console so the agent in node1 reports to the console in node1, and the agent in node2 reports to the console in node2.

    $ORACLE_HOME/bin/emca -reconfig dbcontrol -silent -cluster -EM_NODE <node2 host> -EM_NODE_LIST <node2 host> -DB_UNIQUE_NAME <db_unique_name>
     -SERVICE_NAME <db_unique_name>.<db_domain>  
  4. On each node, verify that console is working properly.

    $ export ORACLE_UNQNAME=<db_unique_name>
    
    $ emctl status agent
    Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
    Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
    ---------------------------------------------------------------
    Agent Version     : 10.2.0.4.5
    OMS Version       : 10.2.0.4.5
    Protocol Version  : 10.2.0.4.5
    Agent Home        : /u01/app/oracle/product/11.2.0.4/dbhome_x/<host>_<db_unique_name>
    Agent binaries    : /u01/app/oracle/product/11.2.0.4/dbhome_x
    Agent Process ID  : 26194
    Parent Process ID : 25835
    Agent URL         : https://<node host>:1831/emd/main
    Repository URL    : https://<node host>:5501/em/upload/
    Started at        : 2017-03-15 20:20:34
    Started by user   : oracle
    Last Reload       : 2017-03-15 20:27:00
    Last successful upload                       : 2017-03-15 21:06:36
    Total Megabytes of XML files uploaded so far :    22.25
    Number of XML files pending upload           :        0                       <=== should be zero
    Size of XML files pending upload(MB)         :     0.00
    Available disk space on upload filesystem    :    42.75%
    Data channel upload directory                : /u01/app/oracle/product/11.2.0.4/dbhome_x/<host>_<db_unique_name>/sysman/recv
    Last successful heartbeat to OMS             : 2017-03-15 21:08:45
    ---------------------------------------------------------------   

Update iptables and Security List

  1. On each node, edit iptables to open the console's port as described in Opening Ports on the DB System.
  2. Update the security list for the console's port as described in Updating the Security List for the DB System.

Opening Ports on the DB System

Open the following ports as needed on the DB system:

  • 6200 - For Oracle Notification Service (ONS).
  • 1158 - For Enterprise Manager Database Control. 1158 is the default port, but each additional console enabled on the DB system will have a different port. If you're not sure which port to open for a particular console, see Monitoring a Database with Enterprise Manager Database Control.

For important information about critical firewall rules, see Essential Firewall Rules.

To open ports on the DB system
  1. SSH to the DB System.

    ssh -i <private_key_path> opc@<db_system_ip_address> 
  2. Log in as opc and then sudo to the root user.

    
    login as: opc
    
    [opc@dbsys ~]$ sudo su -
    
  3. Save a copy of iptables as a backup.

    [root@dbsys ~]# iptables-save > /tmp/iptables.orig

    (If necessary, you can restore the original file by using the command iptables-restore < /tmp/iptables.orig.)

  4. Dynamically add a rule to iptables to allow inbound traffic on the console port, as shown in the following sample. Change the port number  and comment as needed.

    [root@dbsys ~]# iptables -I INPUT 8 -p tcp -m state --state NEW -m tcp --dport 1158 -j ACCEPT -m comment --comment "Required for Enterprise Manager Database Control.”
  5. Make sure the rule was added.

    [root@dbsys ~]# service iptables status
  6. Save the updated file to /etc/sysconfig/iptables.

    [root@dbsys ~]# /sbin/service iptables save

    The change takes effect immediately and will remain in effect when the node is rebooted.

  7. Update the DB system's security list as described in Updating the Security List for the DB System.

Updating the Security List for the DB System

Review the list of ports in Opening Ports on the DB System and for every port you open in iptables, update the security list used for the DB system, or create a new security list.

Note that port 1521 for the Oracle default listener is included in iptables, but should also be added to the security list.

To update an existing security list
  1. Open the navigation menu. Click Oracle Database, then click Bare Metal, VM, and Exadata.
  2. Choose your Compartment.

    A list of DB systems is displayed.

  3. Locate the DB system in the list.
  4. Note the DB system's Subnet name and click its Virtual Cloud Network.
  5. Locate the subnet in the list, and then click its security list under Security Lists.
  6. Click Edit All Rules and add an ingress rule with source type = CIDR, source CIDR=<source CIDR>, protocol=TCP, and port=<port number or port range>.

    The source CIDR should be the CIDR block that includes the ports you open for the client connection.

For detailed information about creating or updating a security list, see Security Lists.