Before you Begin

This 20-minute tutorial shows you how to set up the environment in Logging Analytics to collect data from the tables or views of Oracle Autonomous Database. Logging Analytics collects the data from the tables or views by connecting to the database and running the SQL query that you provide.

Background

Perform the steps described in this tutorial for collecting data from the tables or views from your autonomous database entity such as creating the log source, creating autonomous database entity, downloading the wallet files, registering the credentials, registering the wallet information, and finally associating the log source with the autonomous database entity to start collecting the logs.

What Do You Need?

  • Set up service policies for Oracle Cloud Logging Analytics. See Enable Access to Logging Analytics and Its Resources and Prerequisite IAM Policies in Oracle Cloud Infrastructure Documentation.
  • Create the IAM policies for setting up continuous log collection. See Permission Required for Setting Up Continuous Log Collection in Oracle Cloud Infrastructure Documentation.
  • Use an existing log group or create at least one log group to store logs that you collect from your autonomous database. See Create Log Groups to Store Your Logs in Oracle Cloud Infrastructure Documentation.
  • Install management agents on any host that can have JDBC connectivity to the ATP-S or ADW-W. Ensure to also perform the prerequisites for deploying the management agents. See Management Agent: Introduction in Oracle Cloud Infrastructure Documentation.

    On Unix-based hosts, the user that installs management agent is mgmt_agent for the manually installed management agent, and oracle-cloud-agent when the management agent is a plugin enabled with Oracle Cloud Agent.

  • Familiarize with the SQL Query Guidelines to write the SQL queries for extracting log data. See SQL Query Guidelines in Oracle Cloud Infrastructure Documentation.
  • Ensure that you have access to the compartments in which your autonomous databases reside in Oracle Cloud Infrastructure.
  • Optionally, you can configure Management Dashboard to create and use dashboards, which are data visualization tools that provide a quick insight into the health and performance of your IT infrastructure and applications, and help identify outliers and take corrective action. See Configure Management Dashboard in Oracle Cloud Infrastructure Documentation.
  • For the management agent to collect the logs from the autonomous database entity, you must provide the credentials to access the autonomous database. To know more about the credentials that must be registered with the management agent, see Management Agent Source Credentials: DBTCPSCreds in Oracle Cloud Infrastructure Documentation.
  • You will need to provide the service name while creating the autonomous database entity. For information about the predefined service names for autonomous databases, see Predefined Database Service Names for Autonomous Database in Oracle Database Documentation.

Download the Wallet Files

The Management Agent requires Java Key Store (JKS) or Oracle wallets to connect to ATP or ADW. Management Agent connectivity to an Oracle Autonomous Database requires the use of Oracle wallets.

  1. Sign in to Oracle Cloud with your cloud credentials > Navigate to the home page of your autonomous database.

  2. Click DB Connection tab > Download the wallet zip file wallet_<dbname>.zip.

  3. Unzip the zip to a directory securely so that only authorized users have access to them. The contents of the zip file are described below:

    • tnsnames.ora and sqlnet.ora: Network configuration files storing connect descriptors and SQL*Net client side configuration.
    • cwallet.ora and ewallet.p12: Auto-open SSO wallet and PKCS12 file. PKCS12 file is protected by the wallet password provided in the UI.
    • keystore.jks and truststore.jks: JKS Truststore and Keystore that is protected by the wallet passport provided while downloading the wallet.
    • ojdbc.properties: Contains the wallet related connection property required for JDBC connection. This should be in the same path as tnsnames.ora.

Create the Autonomous Database Entity

Create the autonomous database entity to reference your database instance and to enable log collection from it.

  1. Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.

    The administration resources are listed in the left hand navigation pane under Resources. Click Entities.

    The Entities page opens. Ensure that your compartment selector on the left indicates that you are in the desired compartment for this new entity. Click Create.

  2. Under Entity Type menu, select Autonomous Transaction Processing or Autonomous Data Warehouse.
    Create autonomous database entity
    Description of the illustration Create_Entity.png

    Provide a Name for the entity and note it for future reference. You will need it to register the database details with the management agent.

  3. Select Management Agent Compartment in which the agent is installed.
  4. In the Management Agent menu, select the management agent installed on your host that has JDBC connectivity to the ATP-S or ADW-W.
  5. Optionally, in the section Cloud Resource ID, provide the OCID of your autonomous database instance.
  6. Under Properties, specify the value of the property service_name.

    Predefined service names are available in the tnsnames.ora file that you downloaded in the previous step. The property value is of the format dbName_predefinedServiceName.

  7. Click Create.

Register the Database Credentials and Wallet Details with Management Agent

For the management agent to collect the logs from the autonomous database entity, you must provide the credentials to access the autonomous database. Use the following steps to add the credentials or update the existing credentials. Also register wallet details with the agent.

  1. Collect the following information about the database:
    • Database_Entity_Name: The entity name that you specified earlier while creating the ATP / ADW entity
    • Database_User_Name: Database user name
    • Database_Password: Database password
    • Database user's role (optional)
  2. Collect the following information about the wallet files:
    • SSL_Truststore_Location: SSL Truststore location
    • SSL_Truststore_Password: SSL Truststore password
    • SSL_Keystore_Location: SSL Keystore location
    • SSL_Keystore_Password: SSL Keystore password
  3. Create a JSON file with the information of your database: 
    {
   "source": "lacollector.la_database_sql",
   "name": "LCAgentDBCreds.<Database_Entity_Name>",
   "type": "DBTCPSCreds",
   "usage": "LOGANALYTICS",
   "disabled": "false",
   "properties":[
         {"name":"DBUserName","value":"<Database_User_Name>"},
         {"name":"DBPassword","value":"<Database_Password>"},
         {"name":"ssl_trustStoreType","value":"JKS"},
         {"name":"ssl_trustStoreLocation","value":"<SSL_Truststore_Location>"},
         {"name":"ssl_trustStorePassword","value":"<SSL_Truststore_Password>"},
         {"name":"ssl_keyStoreType","value":"JKS"},
         {"name":"ssl_keyStoreLocation","value":"<SSL_Keystore_Location>"},
         {"name":"ssl_keyStorePassword","value":"<SSL_Keystore_Password>"},
         {"name":"ssl_server_cert_dn","value":"yes"}]
}

    For Windows paths, use back slash \ and for UNIX-like paths use forward slash / inside the JSON.

  4. To register the database credentials and wallet details with management agent, log in to the management agent host and run the following command by specifying the path to the JSON file that you created in the previous step:

    • On UNIX-like host:

      cat <path_to_JSON_file> | sh /opt/oracle/mgmt_agent/agent_inst/bin/credential_mgmt.sh -o upsertCredentials -s logan

      For example:

      cat /net/example.com/scratch/imp/my_atp/Wallet_ATP/my_logan_creds.json | sh /opt/oracle/mgmt_agent/agent_inst/bin/credential_mgmt.sh -o upsertCredentials -s logan

      Note:

      For Management Agents running on compute instances using the Oracle Cloud Agent plugin, the credential_mgmt.sh script location is under /var/lib/oracle-cloud-agent/plugins/oci-managementagent/polaris/agent_inst/bin.

    • On Windows host:

      type <path_to_JSON_file> | <path_to_credential_mgmt.bat> -o upsertCredentials -s logan

      For example:

      type C:\Oracle\my_logan_creds.json | C:\Oracle\mgmt_agent\agent_inst\bin\credential_mgmt.bat -o upsertCredentials -s logan

Provide Privileges to the Management Agent User to Read Wallet Files

Ensure that the management agent user has the correct privileges to read the wallet files. Provide READ and EXECUTE access to the wallet directory and READ access to the wallet files. For example commands that you can use, see Grant READ Access of the Logs to the Agent User on Your Host in Logging Analytics Documentation.

Create a Source for the Autonomous Database Entity Type

  1. Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.

    The administration resources are listed in the left hand navigation pane under Resources. Click Sources.

    The Sources page opens. Click Create Source.

  2. In the Name field, enter the name of the source.

    Optionally, add a description.

  3. From the Source Type list, select Database.
  4. Under Entity Type menu, select Autonomous Transaction Processing or Autonomous Data Warehouse.
    Create autonomous database source
    Description of the illustration Create_Source.png
  5. Click the Database Queries tab and specify the SQL statements based on which Oracle Cloud Logging Analytics collects autonomous database logs:
    • Enter the SQL statement in the SQL Query field.

      You may want to run the SQL queries outside of Logging Analytics to verify the results first.

    • Click Configure and map the SQL table columns to the field names that would be displayed in the actual log records.
    • Specify a Sequence Column. The value of this field must determine the sequence of the records inserted into the table. It must have unique incremental value.

    Note:

    • The first mapped field with a data type of Timestamp is used as the time stamp of the log record. If no such field is present, then the collection time is used as the time of the log record.
    • When the logs are collected for the first time after you created the log source (historic log collection):

      If any field in the SQL query is mapped to the Time field , then the value of that field is used as reference to upload the log records from previous 30 days.

      If none of the fields in the SQL query are mapped to the Time field, then a maximum of 10,000,000 records are uploaded.

    Click Done.

    To add multiple SQL queries, click Add and repeat this step.

  6. Select Enabled for each of the SQL queries and then click Save.

The sources listing page opens and shows all Oracle-defined and custom defined sources, including the one that you just created.

Associate the Log Source with Autonomous Database Entity

  1. In the sources listing page identify the source that you created in the previous step. Click the source name. The source details page opens.
  2. On the left of the Source details page, under Resource Scope, select the compartment. This will limit the entities visible to you based on the compartment that you selected.
  3. Under Resources, click Unassociated Entities to view the entities that are eligible for association with this source. Select the entity that you created earlier to associate with this source and click Add Association.
    Assocaite autonomous database entity and source
    Description of the illustration Associate_Source_Entity.png

    The Add Association dialog box opens.

  4. Select the compartment that contains the log group in which the log data should be located after it is collected and indexed.
  5. Select the log group that you created earlier during the setup.
  6. Click Submit.

The selected entity is now moved to the Associated Entities section where the status of the association is displayed. If the source-entity association task failed, then click Retry Association.

Collection instructions will be sent down to the agent that is monitoring this entity as defined earlier and the log collection will start. Go to Agent Collection Warnings section to view the warnings for log collection. Note that the list of warnings correspond to all the association entities for this source.

If you have any issues with the management agent collecting the data, then ensure that the user name that you provided in the credential store has READ access to the tables or views being queried. Wait for a few minutes to view the logs in the Log Explorer. After the agent has collected the historic logs, you will start seeing only newer logs being collected.

Learn More

The following sections in the Oracle Cloud Infrastructure Documentation: