Collect Logs for Oracle
The Oracle app collects data from several Oracle log types, and uses a Sumo-provided Python script to performance metrics. This page has instructions for setting up log collection.
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:
Source |
Example Source Category |
Alert Logs |
DB/Oracle/Alert |
Listener Logs |
DB/Oracle/Listener |
Audit Logs |
DB/Oracle/Audit |
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
Alert Log Sample
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.
Listener Log Sample
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
Audit Log Sample
<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'
Sumo Python DB Script Log Sample
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"}