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. You can define a parser for the database instance log records using Oracle Log Analytics.

Currently, the supported database types are Oracle Database Instance (omc_oracle_db_instance), Microsoft SQL Server Database Instance (omc_sqlserver_db_instance), and MySQL Database Instance (omc_mysql_db_instance).

Overall Flow for Collecting Database Logs

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

Creating your log source
Providing entity credentials
Associating an entity with the log source

Note:

By default, after you’ve installed the cloud agent, it collects the database instance records for 30 days. If you want to extract records that’re more than 30 days old, then update the property before the event collection from the database begins:

omcli setproperty agent -allow_new -name loganalytics.database_sql.max_oldDays -value <newValue_for_max_oldDays>

For an example of how to collect Database Audit Logs, see Collect Database Audit Logs to Analyze Using Oracle Log Analytics ( Tutorial).

For an example of how to collect logs from Oracle Autonomous Database user tables, see Collect Logs from Oracle Autonomous Database User Tables ( Tutorial).

Create the Database 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 .
Alternatively, in the Log Sources section, you can click the available number of log sources link and then in the Log Sources page, click Create.

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 the required entity type. For example, Oracle 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.
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.
See SQL Query Guidelines.

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 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, you need to provide the necessary credentials to be used 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.

Create the JSON File with Credentials Information

Create a JSON file that contains the credential information as the following:

[{
    "entity":"<Enter Entity Type>.<Enter Entity Name>",          
    "name":"LCAgentDBCreds",
    "type":"DBCredsNormal",
    "usage":"LOGANALYTICS",
    "globalName":"AgentUserCredential", 
    "description":"SSH Credential for fetching the data from db tables via sql",
    "properties":[{
        "name":"USERNAME",
        "value":"CLEAR[username]"
    },
    {
        "name":"PASSWORD",
        "value":"CLEAR[password]"
    },
    {
        "name":"ROLE",
        "value":"CLEAR[rolename]"
    }]
}]

For example, for a database named avdf_instance and user name, password, and role as sys, syspasswd, and SYSDBA respectively, the JSON file should contain:

[{
        "entity":"omc_oracle_db_instance.avdf_instance/orcl",
        "name":"LCAgentDBCreds",
        "type":"DBCredsNormal",
        "globalName":"AgentUserCredential",
        "usage":"LOGANALYTICS",
        "description":"DB Credentials",
        "properties":[{
                "name":"USERNAME",
                "value":"CLEAR[sys]"
        },
        {
                "name":"PASSWORD",
                "value":"CLEAR[syspasswd]"
        },
        {
                "name":"ROLE",
                "value":"CLEAR[SYSDBA]"
        }]
}]

The name, type and usage fields should be set to LCAgentDBCreds, DBCredsNormal and LOGANALYTICS respectively. The globalName field needs to be unique within the credential store managed by the local cloud agent. The ROLE property is optional.

You need to register the credential information with the cloud agent.

Go to the Oracle Management Cloud host computer.
To create a credential store if it was not created earlier,
1. Stop the cloud agent:
  
  omcli stop agent
2. Run the following command from the <AGENT_BASE_DIR>/agent_inst/bin location:
  
  omcli add_credential_store agent -no_password
  
  See omcli Command Options in Working with Oracle Management Cloud.
3. Start the cloud agent:
  
  omcli start agent
To register the credential information, run the following command from the <AGENT_BASE_DIR>/agent_inst/bin location:
omcli add_credentials agent -credential_file <PATH_TO_CRED_JSON_FILE>

See omcli Command Options in Working with Oracle Management Cloud.

Note:

omcli setproperty agent -allow_new -name loganalytics.database_sql.max_oldDays -value <newValue_for_max_oldDays>

Next: To associate the entity with your log source, see Working with Entity Associations.