Set Up Database Instance Monitoring

Oracle Log Analytics can extract database instance records based on the SQL query that you provide in the log source configuration.

For the types of databases supported, available Oracle-defined log sources, and instructions specific to those databases, see:

Note

For Oracle Real Application Clusters (RAC), where there are multiple database instances pointing to the same database storage, use the entity type Oracle Database instead of Oracle Database Instance. Install a remote agent that can access the database using the SCAN IP. Do not configure agent/agents to run SQL queries against individual database instances.

Prerequisites

Ensure that database connection works from the agent host to database. If the database and agent are on the same host, then use sqlplus, tnsping, or trcroute command to test connectivity to the database. See Testing Connections in Database Net Services Administrator's Guide.

If the database and agent are not on same host, then run the following steps on the agent host:

sqlplus (if installed on the agent)
Ping from your agent to the database. This should work unless firewalls block the ping. If the ping is blocked, then:
- telnet to your database server with the database port to check if the listener is accepting the connection; for example, telnet <database_server_hostname> 1521.

Overall Flow for Collecting Database Logs

The following are the high-level tasks for collecting log information stored in a database:

Install Management Agent on a host that can access the database. See Set Up Continuous Log Collection From Your Hosts.
Create the Database Entity
Create the Database Source
Associate the database entity with the source. See Configure New Source-Entity Association.
Provide the Database Entity Credentials
View Your Database Entity in Database Management Service

Oracle Database

Oracle Database includes

Pluggable Database (PDB), Multitenant Container Database (CDB), and Application Container
Oracle Database Instance
Oracle Real Application Clusters (RAC)
Oracle Autonomous Database
- Autonomous Data Warehouse (ADW)
- Autonomous Transaction Processing (ATP)
For an example of how to collect logs from tables or views in Oracle Autonomous Database, see Collect Logs from Tables or Views in Oracle Autonomous Database ( Tutorial ).

Oracle Log Analytics provides a large set of Oracle-defined log sources of the type Database for Oracle Database:

Log Source	Entity Type
AVDF Alert in Oracle Database	Autonomous JSON Database, Oracle Database Instance, Autonomous Database with the Oracle APEX Application Development, Autonomous Data Warehouse,Oracle Database, Autonomous Transaction Processing
AVDF Event in Oracle Database	Oracle Database Instance, Autonomous Data Warehouse, Oracle Database, Autonomous JSON Database, Autonomous Database with the Oracle APEX Application Development, Autonomous Transaction Processing
Identity and Access Management Audit Database	Oracle Database Instance, Autonomous Transaction Processing, Autonomous JSON Database, Autonomous Database with the Oracle APEX Application Development, Oracle Database, Autonomous Data Warehouse
Oracle DB Audit Log Source Stored in Database	Autonomous Transaction Processing, Oracle Database Instance, Autonomous Data Warehouse, Autonomous JSON Database, Autonomous Database with the Oracle APEX Application Development, Oracle Database
Oracle EBS Transaction Logs	Autonomous Database with the Oracle APEX Application Development, Oracle Pluggable Database,Autonomous Transaction Processing, Oracle Database Instance, Autonomous JSON Database, Oracle Database, Autonomous Data Warehouse
Symantec DLP System Events	Autonomous JSON Database, Oracle Database, Autonomous Transaction Processing, Autonomous Database with the Oracle APEX Application Development, Oracle Database Instance, Autonomous Data Warehouse
Oracle Unified DB Audit Log Source Stored in Database 12.1	Autonomous JSON Database,Oracle Database, Oracle Pluggable Database, Autonomous Data Warehouse, Oracle Database Instance, Autonomous Transaction Processing, Autonomous Database with the Oracle APEX Application Development
Oracle Unified DB Audit Log Source Stored in Database 12.2	Autonomous JSON Database, Oracle Database Instance, Autonomous Database with the Oracle APEX Application Development, Oracle Pluggable Database, Oracle Database, Autonomous Data Warehouse, Autonomous Transaction Processing
Database Alert Logs Stored in Database	Autonomous JSON Database, Oracle Pluggable Database, Autonomous Transaction Processing,Autonomous Data Warehouse, Autonomous Database with the Oracle APEX Application Development, Oracle Database Instance, Oracle Database
Database Trace Logs stored in Database	Oracle Database, Autonomous Database with the Oracle APEX Application Development, Autonomous Data Warehouse, Oracle Database Instance, Autonomous JSON Database, Oracle Pluggable Database, Autonomous Transaction Processing
Oracle Database Health Monitor Service Logs	Autonomous Transaction Processing, Oracle Database, Autonomous Database with the Oracle APEX Application Development, Oracle Database Instance, Autonomous JSON Database, Autonomous Data Warehouse
Oracle Database Dataguard Logs	Autonomous Transaction Processing, Autonomous JSON Database, Autonomous Data Warehouse, Oracle Database, Oracle Database Instance, Autonomous Database with the Oracle APEX Application Development

Additionally, more oracle-defined log sources of the type File are available for Oracle Database such as Database Alert Logs, Database Audit Logs, Database Audit XML Logs, Database Incident Dump Files, Database Listener Alert Logs, Database Listener Trace Logs, Database Trace Logs, and Database XML Alert Logs.

Microsoft SQL Server Database Instance

Note

For successful log collection from Microsoft SQL Server Database source, ensure that Management Agent version is 210403.1350 or later.
Monitoring of Microsoft SQL Server Database Instance is supported only with the installation of standalone Management Agent. It is not supported with Management Agent plugin in Oracle Cloud Agent.

The following Oracle-defined log sources of the type Database are available for monitoring Microsoft SQL Server Database Instance:

McAfee Data Loss Prevention Endpoint
McAfee ePolicy Orchestrator

Additionally, more oracle-defined log sources of the type File are available for Microsoft SQL Server Database Instance such as Microsoft SQL Server Agent Error Log and Microsoft SQL Server Error Log Sources.

MySQL Database Instance

Note

For successful log collection from MySQL Database source, ensure that Management Agent version is 210205.0202 or later.
Monitoring of MySQL Database Instance is supported only with the installation of standalone Management Agent. It is not supported with Management Agent plugin in Oracle Cloud Agent.

The following Oracle-defined log sources of the type Database are available for monitoring MySQL Database Instance:

MySQL Error Logs Stored in Database
MySQL General Log Source Stored in Database
MySQL Slow Query Logs Stored in Database

Additionally, more oracle-defined log sources of the type File are available for MySQL Database Instance such as MySQL Database Audit XML Logs, MySQL Error Logs, MySQL General Query Logs, and MySQL Slow Query Logs.

To perform remote collection for a MySQL database instance, the following configuration must be done at the database instance:

To allow access from a specific host where the management agent is installed:
1. Create the new account authenticated by the specified password:
```
CREATE USER '<mysql_user>'@'<host_name>' IDENTIFIED BY '<password>';
```
2. Assign READ privileges for all the databases to the mysql_user user on host host_name:
```
GRANT SELECT ON *.* TO '<mysql_user>'@'<host_name>' WITH GRANT OPTION;
```
3. Save the updates to the user privileges by issuing the command:
```
FLUSH PRIVILEGES;
```
To allow access to a specific database from any host:
1. Grant READ privileges to mysql_user from any valid host:
```
GRANT SELECT ON <database_name>.* TO '<mysql_user>'@'%' WITH GRANT OPTION;
```
2. Save the updates to the user privileges by issuing the command:
```
FLUSH PRIVILEGES;
```

PostgreSQL Database

Note

For successful log collection from PostgreSQL Database source, ensure to install Management Agent version 250121.1929 or later on your database host.
Monitoring of PostgreSQL database is supported only with the installation of standalone Management Agent. It is not supported with Management Agent plugin in Oracle Cloud Agent.

The following Oracle-defined log sources of the type File are available for monitoring PostgreSQL Database:

OCI PostgreSQL Service Logs
PostgreSQL Logs

Create the Database Entity

Create the database entity to reference your database instance and to enable log collection from it. If you are using management agent to collect logs, then after you install the management agent, you must come back here to configure the agent monitoring for the entity.

Open the navigation menu and click Observability & Management. Under Log Analytics, click Administration. The Administration Overview page opens.
The administration resources are listed in the left hand navigation pane under Resources. Click Entities.
Ensure that your compartment selector on the left indicates that you are in the desired compartment for this new entity.

Click Create.
Select an Entity Type that suits your database instance, for example Oracle Database Instance.

Provide a Name for the entity.
Select Management Agent Compartment in which the agent is installed and select the Management Agent to associate with the database entity so that the logs can be collected.

Alternatively, you can create the entity first, edit it later and provide the management agent OCID after the agent is installed.
Note
- Monitoring of MySQL Database Instance, Microsoft SQL Server Database, and PostgreSQL Database is supported only with the installation of standalone Management Agent. It is not supported with Management Agent plugin in Oracle Cloud Agent.
- Use Management Agent version 210403.1350 or later to install on your database host to ensure Microsoft SQL Server Database support.
- For successful log collection from MySQL Database Instance source, ensure that Management Agent version is 210205.0202 or later.
- Use Management Agent version 250121.1929 or later to install on your database host to ensure PostgreSQL Database support.
If your database instance has a Cloud Resource ID, then provide that OCID. If the OCID is provided and if your database is enabled for Database Management, then you can view it in Database Management with the help of the option available in the Log Explorer. See View Your Database Entity in Database Management Service.
To ingest SQL, provide the following properties in case of Oracle Database (for RAC deployments), Oracle Database Instance, or Oracle Pluggable Database:
- port
- hostname
- sid or service_name
  If you provide both the values, then Log Analytics uses service_name to ingest SQL.
For log collection from Microsoft SQL Server Database Instance, MySQL Database source, and PostgreSQL Database, provide the following properties:
- database_name
- host_name
- port
If you intend to use Oracle-defined log sources to collect logs from management agents, it is recommended that you provide any parameter values that may already be defined for the chosen entity type. If the parameter values are not provided, then when you try to associate the source to this entity, it will fail because of the missing parameter values.

Click Save.

Create the Database Source

Open the navigation menu and click Observability & Management. Under Log Analytics, click Administration. The Administration Overview page opens.
The administration resources are listed in the left hand navigation pane under Resources. Click Sources.
In the Sources page, click Create Source.

This displays the Create Source dialog box.
In the Source field, enter the name for the source.
From the Source Type list, select Database.
Click Entity Type and select the required entity type. For example, Oracle Database Instance, Oracle Pluggable Database, Microsoft SQL Server Database Instance, or MySQL Database Instance.
In the Database Queries tab, click Add to specify the details of the SQL query, based on which Oracle Log Analytics instance collects database instance logs.

See SQL Query Guidelines.
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. To create a new field for mapping, click the icon.

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. If you don't want the fields to determine the sequence of the records, then you can select SQL query collection time to use the collection time as the log entry time. In that case, all the log records are re-collected in every collection cycle.
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.
Repeat Step 6 through Step 8 for adding multiple SQL queries.
Select Enabled for each of the SQL queries and then click Save.

Provide the Database Entity Credentials

For each entity that’s used for collecting the data defined in the Database log source, provide the necessary credentials to the agent to connect to the entity and run the SQL query. These credentials need to be registered in a credential store that’s maintained locally by the cloud agent. The credentials are used by the cloud agent to collect the log data from the entity.

Log in to the host on which the management agent is installed.
Create the DBCreds type credentials JSON input file. For example agent_dbcreds.json:
```
cat agent_dbcreds.json
{
    "source": "lacollector.la_database_sql",
    "name": "LCAgentDBCreds.<entity_name>",
      "type": "DBCreds",
    "usage": "LOGANALYTICS",
    "disabled": "false",
    "properties": [
        {
            "name": "DBUserName",
            "value": "CLEAR[username]"
        },
        {
            "name": "DBPassword",
            "value": "CLEAR[password]"
        },
        {
            "name": "DBRole",
            "value": "CLEAR[normal]"
        }
    ]
}
```
The following properties must be provided in the input file as in the above example agent_dbcreds.json:
- source : "lacollector.la_database_sql"
- name : "LCAgentDBCreds.<entity_name>"
  entity_name is the value of the Name field that you entered while creating the entity.
- type : "DBCreds"
- usage : "LOGANALYTICS"
- properties : user name, password and role. Role is optional.
Use the credential_mgmt.sh script with the upsertCredentials operation to add the credentials to the agent's credential store:

Syntax:
```
$cat <input_file> | sudo -u mgmt_agent /opt/oracle/mgmt_agent/agent_inst/bin/credential_mgmt.sh -o upsertCredentials -s <service_name>
```
In the above command:
- Input file: The input JSON file with the credential parameters, for example, agent_dbcreds.json.
- Service name: Use logan as the name of the Oracle Log Analytics plug-in deployed on the agent.
By using the example values of the two parameters, the command would be:
```
$cat agent_dbcreds.json | sudo -u mgmt_agent /opt/oracle/mgmt_agent/agent_inst/bin/credential_mgmt.sh -o upsertCredentials -s logan
```
After the credentials are successfully added, you can delete the input JSON file.

For more information about managing credentials on the management agent credential store, see Management Agent Source Credentials in Management Agent Documentation.

View Your Database Entity in Database Management Service

If your database is enabled for Database Management and has a cloud resource OCID associated with it, then Log Analytics enables you to view it in Database Management with the help of the option available in the Log Explorer.

To enable Database Management for your database, see OCI Documentation: Enable Database Management.

Open the navigation menu and click Observability & Management. Under Log Analytics, click Log Explorer.
Optionally, narrow down the search for your logs by entity type which must be one of the database types. In the Fields panel, under Pinned section, click Entity Type. In the Entity Type dialog box, select the required entity types, for example, Oracle Database Instance, and click Apply.
From the Visualize panel, select one of the visualization options that display the records table, for example, Records with Histogram.

Then the logs are displayed in the Records with Histogram visualization. In the records table, under each log record, the information about the entity name, log source, and entity type are displayed.
Click the name of the entity. From the menu, click View in Database Management.

A new tab with the Database Management service console in the context of your database is displayed.

Oracle Cloud Infrastructure Documentation