Oracle Management Cloud - Log Analytics: Collect Logs from Oracle Autonomous Database User Tables

Before You Begin

This 20-minute tutorial shows you how to enable continuous collection of log data from the user tables in an Oracle Autonomous Database using a Cloud Agent.

Unlike the typical scenario, for autonomous database, you can install the cloud agent on any host that has connectivity to the Oracle Cloud, and not on the host that has the autonomous database installed.

Perform the steps described in this tutorial for collecting logs from the user tables from your autonomous database entity on Oracle Management Cloud such as creating the log source, discovering the autonomous database entity, installing the cloud agent, registering the credentials, downloading the wallet files, registering the wallet information, designating the cloud agent for log collection, and finally associating the log source with the autonomous database entity to start collecting the logs.

What Do You Need?

Review and understand the deployment topology of Oracle Management Cloud agents. See Understand the Architecture of Oracle Management Cloud in Installing and Managing Oracle Management Cloud Agents.
An understanding of how to access Oracle Management Cloud. See Access Oracle Management Cloud in Getting Started with Oracle Management Cloud
Familiarize with the SQL Query Guidelines to write the SQL queries for extracting log data. See SQL Query Guidelines in Using Oracle Log Analytics.
Download the cloud agent software version 1.49.1 or higher that contains the script required to install the Oracle Management Cloud agents. See Download the Oracle Management Cloud Agent Software in Installing and Managing Oracle Management Cloud Agents.
Install the cloud agent on any host that has connectivity to Oracle Cloud. See Install Cloud Agents in Installing and Managing Oracle Management Cloud Agents.
Verify the agent deployment. See Verify the Cloud Agent Installation in Installing and Managing Oracle Management Cloud Agents.
Before you create a Cloud Discovery Profile, you must ensure that your autonomous databases are created and available in the same region as your Oracle Management Cloud instances. Cross-region monitoring of autonomous databases is currently not supported by Oracle Management Cloud. See the video Provisioning an Autonomous Transaction Processing.
Ensure that you have access to the compartments in which your autonomous databases reside in Oracle Cloud Infrastructure. See Perform Prerequisite Tasks in Using Oracle Database Management for Autonomous Databases.
Obtain the following credentials from the Oracle Cloud Infrastructure Console as Oracle Management Cloud connects to your Oracle Cloud Infrastructure tenancy using APIs - Tenancy's OCID and User's OCID, Private Key and Public Key in PEM format, and Fingerprint of the Public Key. See Discover Autonomous Databases in Using Oracle Database Management for Autonomous Databases.
Use the internal entity name and not the display name of the entity wherever this tutorial references entity names. You can obtain the internal entity name from the Data Explorer. See View an Entity Card in Using Oracle Log Analytics.

Create a Log Source

From Oracle Log Analytics, click the OMC Navigation icon on the top left corner of the interface. In the OMC Navigation bar, click Administration Home.
In the Log Sources section, click Create source.
This displays the Create Log Source dialog box.
In the Source field, enter the name for the log source.
From the Source Type list, select Database.
Click Entity Type and select Autonomous Transaction Processing or Autonomous Data Warehouse.
In the Database Queries tab, click Add to specify the details of the SQL query based on which Oracle Log Analytics instance collects autonomous database logs.
Click Configure to display the Configure Column Mapping dialog box.
In the Configure Column Mapping dialog box, map the SQL fields with the field names that would be displayed in the actual log records.
Specify a Sequence Column.

Note that the first mapped field with a data type of Timestamp is used as the time stamp of the log entry. If no such field is present, then the collection time is used as the time of the log entry.

Click Done.
Repeat steps 6 through 8 for adding multiple SQL queries.
Select Enabled for each of the SQL queries and then click Save.

Discover the Autonomous Database Entity

Access Oracle Management Cloud. Click the OMC Navigation icon on the top left corner of the interface > Navigate to Administration > Entity Configuration > Licensing. The Licensing page is displayed. Ensure that the Log Collection toggle button is ENABLED.
Click the OMC Navigation icon on the top left corner of the interface > Navigate to Administration > Discovery > Cloud Discovery Profiles.
On the Cloud Discovery Profiles page, click Add Profile.

The Add Discovery Profile page is displayed.
On the Add Discovery Profile page, enter a profile name and select Oracle Cloud Infrastructure in the Cloud Service Provider drop-down list.
In the Regions and Services section, select your region and select the autonomous database options Autonomous Data Warehouse and Autonomous Transaction Processing in the Services drop-down list.
In the Credentials section, select the New Credentials option and enter the following credentials:
- A name for the credentials account, which acts as a unique identifier for these credentials in Oracle Management Cloud.
- Oracle Cloud Infrastructure tenancy-related information: User OCID, Tenancy OCID, Private Key, and Fingerprint. If you've encrypted a passphrase when generating the private key, then enter it in the Passphrase field.
Click Start Discovery.

The Cloud Discovery Profile is saved and the status of the discovery is displayed on the Cloud Discovery Profiles page.

After the discovery process is complete, Oracle Management Cloud associates that autonomous database entity automatically with the log source Oracle Unified Audit Trail Stored in Cloud Database. Also, the Standard Edition license is auto-assigned to the autonomous database entity during discovery.

Download the Wallet Files

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

Sign in to Oracle Cloud with your cloud credentials > Navigate to the home page of your autonomous database.
Click DB Connection tab > Download the wallet zip file wallet_<dbname>.zip.
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.

Note: The JDBC drivers are included with Cloud Agent version 1.49.1 and higher. So, you don't have to download the JDBC drivers.

Register the Autonomous Database Credentials with Oracle Management Cloud Agent

For the cloud agent to collect the logs from the autonomous database entity, you must provide the credentials to access the autonomous database. If the credentials are not already registered on the agent credential store, then register.

Create a JSON file with the information of your database. In the following example, orcldb is the autonomous database entity name, and john is the user whose credentials are added.

Enter the parameter value in the corresponding field and press Enter to see the value in the following JSON example. You could then copy the example and create a JSON file to use in your environment.

Table of parameters and corresponding field boxes.
Parameter	Enter the value in the box
Entity Type
Entity Name
globalName
Description
User Name
Password
Role

[{
	"entity":".",
	"name":"LCAgentDBCreds",
	"type":"DBCredsNormal",
	"usage":"LOGANALYTICS",
	"globalName":"",
	"description":"",
	"properties":[{
		"name":"USERNAME",
		"value":"CLEAR[]"
	},
	{
		"name":"PASSWORD",
		"value":"CLEAR[]"
	},
	{
		"name":"ROLE",
		"value":"CLEAR[]"
	}]
}]

Customize the above example JSON and ensure the following:

Replace omc_oracle_cloud_atp with the entity type of your choice. For Autonomous Transaction Processing, specify omc_oracle_cloud_atp and for Autonomous Data Warehouse, specify omc_oracle_cloud_adw.
Replace orcldb with the entity name of your autonomous database.
Replace john, john_password, and john_role with the user name, password, and the role. The role property is optional.
The globalName parameter must be unique within the credential store managed by the local cloud agent.

Save the changes and store the JSON file on the host where the cloud agent is installed.

Stop the cloud agent.

$ <OMCAGENT_HOME>/agent_inst/bin/omcli stop agent

Create the cloud agent credential store, if it does not exist already.

$ <OMCAGENT_HOME>/agent_inst/bin/omcli add_credential_store agent -no_password

Restart the cloud agent.

$ <OMCAGENT_HOME>/agent_inst/bin/omcli start agent

Add the credentials to the agent credential store using the JSON file that you created earlier.

$ <OMCAGENT_HOME>/agent_inst/bin/omcli add_credentials agent -credential_file <PATH_TO_YOUR_CREDENTIAL_JSON_FILE>

Register the Wallet Details

Collect the following information before you execute the steps. Enter the parameter value in the corresponding box and press ENTER to see the value in the ensuing code examples. You could then copy the example code and run it in your environment.

Entity Name: The autonomous database entity name.
Wallet Directory Location: The absolute path of the wallet directory. The wallet locations are different for the entity types Autonomous Transaction Processing and Autonomous Data Warehouse.
Service Name: The name of the service used for connecting wallet with the cloud agent.
Host: The host on which the cloud agent is installed.
Proxy Host: To connect using a proxy server, specify the proxy host name.
TNS Alias: Collect the TNS alias value from the file tnsnames.ora that you downloaded earlier.

Specify the wallet location.

omcli setproperty agent -allow_new -name "oracle_wallet_location." -value "(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=)))"

Specify the connection URL. Substitute the entity name, TNS alias, and wallet directory in the command. The following example command is for Autonomous Transaction Processing entity type. For Autonomous Data Warehouse entity type, replace atp with adw.
```
omcli setproperty agent -allow_new -name "oracle_cloud_atp_url." -value "jdbc:oracle:thin:@?TNS_ADMIN="
```
Verify that the properties are set correctly. Substitute the entity name below:
```
omcli getproperty agent -name oracle_wallet_location.
```
The following example command is for Autonomous Transaction Processing entity type. For Autonomous Data Warehouse entity type, replace ATP with ADW.
```
omcli getproperty agent -name oracle_cloud_atp_url.
```
To connect using a proxy server, modify the downloaded tnsnames.ora file and add the proxy server host and port information in the address section of the connection definition. Also, update the service name and the host name of the cloud agent. For example:
```
 = (description=(address=(https_proxy=)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=)...
```

Obtain the Cloud Agent Details

For Autonomous Databases, the cloud agent is not co-located with the entity. To designate the cloud agent for the log collection, you must first collect all the required information about the cloud agent.

Run the following REST API command to collect the details about the cloud agent:
```
curl -s -X GET -u 'user_name:password' 'https://omc_url/serviceapi/tm-data/mes?entityName=entity_name'
```
In the above command,
- user_name: Your user name to access Oracle Management Cloud
- password: Your Oracle Management Cloud password
- omc_url: The portion of the URL up to and including oraclecloud.com that you see when you visit the Oracle Log Analytics home page
- entity_name: The cloud agent entity name
In response to the above command, the following information about the cloud agent is available:

lama_id_cloud_agent: meID of the Cloud Agent

lama_name_cloud_agent: Entity name of the Cloud Agent - usually of the form host:port

lama_name_cloud_agent_host: Entity name of the host where Cloud Agent is installed
Similarly, run the above REST API command to collect the details about the autonomous database entity by entering the user name, password, and omc_url for Oracle Management Cloud, and the entity name of the autonomous database.
In response to the above command, the following information about the autonomous database is available:

target_guid_auto_db: meID of the Oracle Autonomous Database entity

target_type_auto_db: Entity type of Oracle Autonomous Database entity, omc_oracle_cloud_atp or omc_oracle_cloud_adw

target_name_auto_db: Entity name of Oracle Autonomous Database entity

Create a json file, for example alternate_lama.json, in the same directory where you are running the curl command. Use the information about the cloud agent and autonomous database to create the json file as follows:

adds=[
  {
    "tlmTargetGuid":"target_guid_auto_db",
    "tlmTargetType":"target_type_auto_db",
    "tlmTargetName":"target_name_auto_db",
    "tlmLamaId":"lama_id_cloud_agent",
    "tlmLamaName":"lama_name_cloud_agent",
    "tlmOnhost":"lama_name_cloud_agent_host",
    "tlmAgentType":"Local"
  }
]

Designate the Cloud Agent for Log Collection

After substituting the correct values in the json file, run the following REST API command to designate the cloud agent as the collector of logs for the Oracle Autonomous Database entity:
```
  curl -s -X POST  -u 'user_name:password' -H 'Content-Type:application/json' --data-binary '@alternate_lama.json'  'https://omc_url/serviceapi/logan.targets-with-alternate-lama'
```
In the above command, user_name, password, and omc_url are the user name, password, and Oracle Management Cloud URL as described in the previous section. replace alternate_lama.json with the file name of your json file.

Associate the Log Source with Autonomous Database Entity

To begin collecting logs from the autonomous database, associate the autonomous database entity with the log source that you created in the earlier section. After the association, the logs collected from the autonomous database entity are processed by applying the parameters defined in the log source.

From Oracle Log Analytics, click the OMC Navigation icon on the top left corner of the interface. In the OMC Navigation bar, click Log Admin, and click Entities.
In the Entities section, click New Association.
From Entity Type menu, select Autonomous Data Warehouse or Autonomous Transaction Processing.
Click Add Entities and select the autonomous database that you added through the discovery UI. Click Continue.
Select the log source that you created in the previous section to associate with the entity. Click Continue.
Click Associate Entities.

Now Oracle Log Analytics is ready to collect logs from your autonomous database.

To disable the log collection from your autonomous database entity, delete the association of the entity with the log source. To enable the log collection, you can create the association again.

Want to Learn More?

Visualize Data Using Charts and Controls in Using Oracle Log Analytics
Typical Workflow for Developing Operational Insights in Using Oracle Log Analytics
Typical Use Cases in Using Oracle Log Analytics