Collect Logs for Oracle

Step 1. Enable Oracle logging

If logging is not currently enabled for the following logs, enable it

• Alert log
• Listener Log
• Audit Log—For information about collecting Oracle Audit Logs, see https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50000.

Step 2. Configure local file sources for Oracle logs

In this step, you configure three Local File sources on an installed collector, one for each of the following Oracle logs: Alert, Listener, and Audit.

Follow the instructions in Local File Source.

When you configure the sources, plan your source categories for to ease the querying process.  A hierarchical approach allows you to make use of wildcards. For example:

Step 3. Set up Oracle performance metrics script

The instructions for setting up the Oracle performance metrics script vary by operating system:

• For Linux, see Set Up Oracle Performance Metrics Script on Linux

• For Windows, see Set Up Oracle Performance Metrics Script on Windows

Sample log message  

Wed Mar 21 10:56:08 2018
ORA-1688: unable to extend table ERP.EMP_DETAILS partition ERP_EMP_201803 by 1024 in tablespace ERP_03_DATA
Wed Mar 21 10:59:38 2018

ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. 
More info in file /q01/oracle/admin/diag/rdbms/sysp/sysp/trace/sysp_ora_12345678.trc.

21-Mar-2018 10:56:
08 * (CONNECT_DATA=(CID=(PROGRAM=null)(HOST=__jdbc__)(USER=null))(SERVICE_NAME=sumo.myservice1.org)) 
* (ADDRESS=(PROTOCOL=tcp)(HOST=182.80.119.50)(PORT=56454)) * establish * sumo.myservice1.org * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

Wed Mar 21 11:05:38 2018
21-Mar-2018 11:05:38 * (CONNECT_DATA=(SERVICE_NAME=sumo.crmdb01.com)(CID=(PROGRAM=siebsvc.exe)(HOST=proddb02)
(USER=root))) * (ADDRESS=(PROTOCOL=TCP)(HOST=10.11.12.14)(PORT=56486)) * establish * sumo.crmdb01.com * 0

<AuditRecord><Audit_Type>1</Audit_Type><Session_Id>18207304</Session_Id><StatementId>1</StatementId><EntryId>1
</EntryId><Extended_Timestamp>2018-03-21T10:59:08.000220Z</Extended_Timestamp><DB_User>proddb01</DB_User>
<OS_User>sumouser</OS_User><Userhost>prodApp01</Userhost><OS_Process>19938</OS_Process><Terminal>unknown</Terminal>
<Instance_Number>0</Instance_Number><Action>100</Action><TransactionId>0000000000000000</TransactionId><Returncode>
0</Returncode><Comment_Text>Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=TCP)(HOST=10.11.12.16)(PORT=59029))
</Comment_Text><Priv_Used>5</Priv_Used><DBID>1234541574</DBID></AuditRecord>


Mar 21 11:04:08 stgdb01 Oracle Audit[66686]: LENGTH : '154' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' 
PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'duc' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[10] '1234091467'

Mar 21 11:05:38 proddb01 Oracle Audit[53042]: LENGTH : '155' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' 
PRIVILEGE :[4] 'NONE' CLIENT USER:[6] 'hpal' CLIENT TERMINAL:[0] '' STATUS:[4] '1017' DBID:[10] '1234091467'

2018-03-21 11:05:00.275494 queryoutput={"Tablespace Name": "EXAMPLE", "USED_SPACE": 78.4375, 
"MONITOR_TYPE": "tablespace status", "TOTAL_SPACE": 100, "FREE_SPACE": 21.5625}


2018-03-21 11:05:00.275494 queryoutput={"FREE_SPACE_MB": 0.9375, 
"FILE_NAME": "/u01/oradata/NITINSID/users01.dbf", "ALLOCATED_MB": 5, "USED_MB": 4.0625, "MONITOR_TYPE": "datafile status"}

2018-03-21 11:05:00.275494 queryoutput={"Cache Hit Ratio": 99.89, "MONITOR_TYPE": "buffer cache hit ratio"}