Category Archives: alert / logs

12c, Administration, alert / logs, audit

Oracle 12c – Real-Time Database Operation Monitoring

Leave a comment

Oracle 12c provides the ability to monitor PL/SQL operations within the database in real-time.  Database operations are either simple or composite.   Simple operations are single SQL or PL/SQL procedure/function and Composite operations are activity between two points in time.  A session doing multiple transactions, a given session can only be involved in one composite operation at a time.  This article outlines the basics involved in real-time database operation monitoring.

 

 

  1. Real time SQL monitoring was introduced in 11g but only supported simple operations,12c supports composite operation monitoring, which allows you the ability to monitor a logical grouping of actions to support a particular business requirement.
  2. Real-time Monitoring starts automatically when SQL statements run in parallel or consume more than 5 seconds of CPU or I/O time.
  3. Real-Time monitoring can be viewed via Cloud Control on Monitor SQL Execution page which is the preferred method, allowing you to drill down. It can also be monitored via the data dictionary views, or DBMS_SQL_MONITOR package.
  4. Data Dictionary views:
    1. V$SQL_MONITOR
    2. V$SQL_MONITOR_SESSTAT
    3. V$SQL_PLAN_MONITOR

 

  1. DBMS_SQL_MONITOR package provides the following functions and procedure
    1. REPORT_SQL_MONITOR – gives detailed report.
    2. BEGIN_OPERATION – starts monitoring a session’s performance.
    3. END_OPERATION – ends monitoring a session’s performance

 

  1. SQL Monitoring is automatic when STATISTICS_LEVEL is set to TYPICAL or ALL and Oracle will begin monitoring long running queries automatically.
  2. The init parameter CONTROL_MANAGEMENT_PACK_ACCESS must be set to default of DIAGNOSTIC+TUNING to use this feature.
  3. Hints of MONITOR and NO_MONITOR can be used to force or stop monitoring.

 

SELECT /*+MONITOR*/ first_name from emp;

 

  1. Use the BEGIN_OPERATION and END_OPERATION function of DBMS_SQL_MONITOR package to monitor entire session.

 

 

Larry Catt

OCP

12c, alert / logs, Performance

Oracle 12c – Monitor database alerts

Leave a comment

Alert Logs and Trace Files:

All Oracle background process have their own trace files where information about errors are dumped to when detected.  Alert logs also contain information about database errors, with less detail and normally point towards specific trace file.  Message in alert log include:

  1. Init parameter with non-default values or other notations.
  2. all errors: internal ORA-600, block corruption ORA-1578 and deadlock ORA-60
  3. Admin DDL CREATE, ALTER, and DROP and startup and shutdown.

Alert logs are maintained in both XML and plain text.   The ADRCI utility is used to view XML alert logs.  Both Alert and trace files are stored under the Automatic Diagnostic Repository directory structure.   The init parameter MAX_DUMP_FILE_SIZE limits the size of trace files to a set number of OS blocks.   You cannot limit size of alert logs, but it is a good practice to periodically rename the alert file to reduce file size oracle is using.  The database instance will automatically create a new alert log with appropriate name and this renaming process can be done with the database online.

 

Statistics:

Statistics are generated by the Oracle 12c database to help increase level of performance and to all the optimizer to decide on the best execution plans of SQL statements.   If the SQL_TRACE init parameter is set to TRUE, performance statistics will be generated for SQL statements.  SQL tracing can be set at session level by ‘ALTER SESSION SET SQL_TRACE’ command.   Trace files are written to Automatic Diagnostic Repository directory structure.  The DBMS_SESSION and DBMS_MONITOR packages can be used to control SQL tracing for a session.

 

Adaptive Thresholds:

You can monitor database continuously with adaptive thresholds.  By setting warning and critical alerts for system metrics.  This can be done using moving window metrics.  Two types of adaptive thresholds:

Percentage of maximum – Defined as the max of data/resource in moving window.   like 80% of observed.

Significance level – Defined to a unusual value of threshold, set to one of the following values

  1. High (.95)
  2. Very High (.99)
  3. Severe (.999)
  4. Extreme (.9999)

 

 

Larry Catt

OCP

12c, Administration, alert / logs, audit

Oracle 12c – Unified Audit Data Trail features

Leave a comment

In 12c the Unified Audit Trail consolidates audit information from multiple sources and makes this information available in a standard format in the UNIFIED_AUDIT_TRAIL dictionary view.  This article describes this new feature and gives a brief example of enabling it.

 

Unified Audit Data Trail features.

 

  1. The Unified Audit Trail is stored in a read-only table in the AUDSYS schema in SYSAUX tablespace
  2. Data is available to SYS and users with AUDIT_ADMIN and AUDIT_VIEWER roles.
  3. AUDIT_ADMIN can view data and create audit policies.
  4. AUDIT_VIEWER can only view data.
  5. The Unified Audit Data Trail combines the audit data from:
  • Fine-grained audit records from DBMS_FGA
  • Oracle Database Real Application Security
  • Oracle Recovery Manager
  • Oracle Database Vault
  • Oracle Label Security
  • Oracle Data Mining
  • Oracle Data Pump
  • Oracle SQL* Loader Direct Load
  1. Once configured the unified auditing is always enabled and does not depend on initialization parameters.
  2. If the database is open in READ-ONLY mode, audit records are written to new OS files in $ORACLE_BASE/audit/$ORACLE_SID directory.
  3. The V$OPTION view can be queried to determine if DB is using unified auditing.

Select value from v$option where parameter=’Unified Auditing’;

Parameter                          Value

—————–                    —————–

Unified Auditing              TRUE

 

  1. New 12c database mixed mode auditing is enabled by default by predefined policy
  2. Mixed-mode auditing enables both Traditional pre-12c auditing and unified auditing.
  3. Traditional auditing is controlled by AUDIT_TRAIL init parameters.
  4. When AUDIT_TRAIL init parameter is parameter other than NONE, the traditional audit trail will be populated as well as the Unified Audit trail.
  5. Auditing can be set to individual PDBs or CDB as a whole.
  6. In Multi-tenant DB each PDB and root CDB has its own audit trail.
  7. When upgrading to 12c, you have to manually migrate to Unified Auditing to enable.
  8. Once unified auditing is enabled, traditional is disabled.
  9. To start using Unified, at least one unified policy must be enabled.
  10. To stop using Unified, disable all unified policies.
  11. Predefined policy ORA_SECURECONFIG is initially enabled on all new 12c databases.

 

 

Larry Catt

OCP

12c, Administration, alert / logs, listener, tnsnames

Oracle 12c – Troubleshoot network issues

Leave a comment

Troubleshooting of Oracle networking issues can be a bit more complicated than a general database issue because it involves the client configuration, network, and server configuration.   On the client side we have determine the method used for connection and that all the parameter used to resolve the server side connection are correct.   Ensuring the network connection from the client machine to the server is also critical and will normally include network administrative support.   Finally, on the server side, the listener process must be properly configured to accept client connection requests and pass them to the appropriate server process.    The entire process of determining the solution to an Oracle networking issue can be very convoluted.   It helps to eliminate first what is not the problem.   This article outlines steps to address networking issues in a systematic process.    It addresses the three areas of network issues separately and if all work should make for a fluid network connection between oracle client and oracle database server.    It is assumed that the network connection has worked in the past and all appropriate files have been configured.

 

Server side network troubleshooting:

  1. Logon to your oracle database server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Thu Jan  5 07:25:33 EST 2015 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Verify that the listener process is running with the command lsnrctl status.

 

[oracle@linux2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:46:48

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.102)(PORT=1521)))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Linux Error: 111: Connection refused

[oracle@linux2 ~]$

 

  1. The listener process is not running start the process with lsnrctl start.

 

[oracle@linux2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:47:01

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

 

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.102)(PORT=1521)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.102)(PORT=1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date                05-JAN-2015 10:47:01

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File         /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.102)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

  Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 ~]$

 

  1. Once listener has been started attempt to use the listener with tnsping utility.

 

[oracle@linux2 ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:48:42

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (10 msec)

[oracle@linux2 ~]$

 

  1. This shows that the listener is accepting connection, but there may be issues in the database such as dispatcher or server processes that are beyond this instruction set.

 

 

Network troubleshooting between client and server:

 

  1. From the client machine open a command prompt, ensure that you can successfully ping the client machine.

 

C:\Users\MYWINDOWS>ping 192.168.0.102

 

Pinging 192.168.0.102 with 32 bytes of data:

Reply from 192.168.0.102: bytes=32 time<1ms TTL=64

Reply from 192.168.0.102: bytes=32 time<1ms TTL=64

Reply from 192.168.0.102: bytes=32 time<1ms TTL=64

 

Ping statistics for 192.168.0.102:

    Packets: Sent = 3, Received = 3, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

    Minimum = 0ms, Maximum = 0ms, Average = 0ms

Control-C

^C

C:\Users\MYWINDOWS>

 

  1. Success full connection to server machine. If this connection fails, contact your network administrator.

 

 

Client side:

 

Client side network troubleshooting:

  1. Logon to your oracle client machine with normal user account.
  2. Open a command prompt and attempt a tnsping to your alias machine

 

C:\Users\MYWINDOWS> tnsping orcl

 

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:48:42

 

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

 

Used parameter files:

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (10 msec)

C:\Users\MYWINDOWS>

 

  1. If this fails verify that the values for POTOCAL, HOST, PORT, SERVICE_NAME are correct for the server and database service you are seeking to connect to.

 

 

 

NOTE:   You can trace oracle net communications at the server level and client level this gives very detail logs of the communications occurring and very helpful in troubleshooting network connectivity issues.

 

 

 

Larry Catt

OCP

12c, Administration, alert / logs, testing, troubleshooting

Oracle 12c – Troubleshoot database issues

Leave a comment

Oracle uses the ADR (Automatic Diagnostic Repository) directory structure to store all log files concerning the health of the database.   This directory structure is stored under the $ORACLE_BASE/diag directory.    Each background process of the Oracle RDBMS maintains its own logs in addition to recording information to the central alert log.   Two versions of oracle logs are maintained by the ADR:  XML and plain text.  Background processes may place more detailed information into trace files referenced in the log file messages.  General steps for troubleshooting database issues.

 

 

  1. Logon to SQLPLUS with sysdba privileges.
  2. Determine the location of each log and trace file type by quering the view V$DIAG_INFO as shown below:

 

SQL> set linesize 200

SQL> column name format a30

SQL> column value format a150

SQL>  select name, value from v$diag_info;

 

NAME                           VALUE

—————————— ——————————————————————————————————————————————————

Diag Enabled                   TRUE

ADR Base                       /opt/app/oracle/diag

ADR Home                       /opt/app/oracle/diag/diag/rdbms/orcl/ORCL

Diag Trace                     /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/trace

Diag Alert                     /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/alert

Diag Incident                  /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/incident

Diag Cdump                     /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/cdump

Health Monitor                 /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/hm

Default Trace File             /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/trace/ORCL_ora_15788.trc

 

  1. Examine the log files starting with the alert log in directory: $ORACLE_BASE/diag/rdbms/<DB_NAME>/<SID>/trace directory.

 

This alert log will contain the following:

  • Internal errors (ORA-00600)
  • Block Corruption errors (ORA-01578)
  • Deadlock errors (ORA-00060)
  • All DDL statements: CREATE, ALTER, and DROP.
  • All SHUTDOWN, STARTUP, and ARCHIVELOG statements.
  • Errors with shared processes, server process, and dispatcher processes.
  • Errors from automatic refresh operations in materialized views.
  • Values of non-default initialization parameters.
  • Background and Archiving processes.

 

 

  1. Diagnostics is based on a problem or incident:

 

PROBLEM – critical error in the database, internal error such ORA- error.

INCIDENT – is a single occurrence of a problem and is tracked by a specific number.

 

Each INCIDENT records the following:

  • Entry is made to alert log.
  • Incident alert is sent to EM.
  • Diagnostic info is sent to dump file in the ADR subdirectory for that incident type.

 

  1. Take corrective action based on information received from INCIDENT referenced in logs and trace files.
  2. Re-execute process causing original issue and repeat troubleshooting if necessary.
  3. Additional steps of SQL TRACING and getting assistance from ORACLE SUPPORT may be required in some situations.

 

Larry Catt

OCP