AWS Database Blog

Set Oracle diagnostics system events on Amazon RDS for Oracle

With Amazon Relational Database Service (Amazon RDS) for Oracle, you can now set Oracle diagnostic events parameters requested by Oracle Support at the system level to enable Oracle to dump additional diagnostics information from the database instance on the Oracle database Ora-errors or process context of a malfunctioning process. You can set these events parameters using the system event procedures in the rdsadmin.rdsadmin_util package. As of Q1 2022, these procedures currently support the error stack dump event type, the context dump event type, and a setlist of pre-approved events parameters. For events not in this list, you can also set those parameters at the session level (either directly or using schema logon triggers). For more details about the system event procedure, refer to Setting and unsetting diagnostics events.

Amazon RDS for Oracle is a fully managed commercial database that makes it easy to set up, operate, and scale Oracle databases in the cloud. Amazon RDS frees you to focus on innovation and application development by managing time-consuming database administration tasks, including provisioning, backups, software patching, monitoring, and hardware scaling.

Before this feature in Amazon RDS for Oracle, you could only set system state, hanganalyze, and other events parameters at the session level to trace a session. For more information refer to the Oracle database log files.

In this post, I show you how to use the rdsadmin.rdsadmin_util.set_system_event procedure to set the 10046 SQL trace event parameter on Amazon RDS for Oracle. Keep in mind that setting this event at the system level affects all database sessions and should be set based on a request from Oracle Support. In addition, ensure your system can tolerate the implications (space consumption, CPU overhead, performance degradation, and so on) of setting the events at the system level before setting it on the database.

Prerequisites

For this post, I use an Amazon RDS for Oracle instance. Before you get started, make sure you complete the following prerequisites:

  1. Create or have access to an AWS account.
  2. Create or have a Site-to-Site VPN connection between your on-premise network and your AWS VPC. For more information, refer to How AWS Site-to-Site VPN works.
  3. Install or have Oracle SQL developer already running on-premise or you can download Oracle SQL developer.
  4. You can also use a bastion host with Oracle client installed and SQL*Plus to connect to the database. For more information, refer to Connecting to your DB instance using SQL*Plus.
  5. Create or have an Oracle database release 19.0.0.0 instance running on Amazon RDS for Oracle instance in a private subnet.

Solution overview

The following diagram illustrates the solution architecture:

I walk you through the following high-level steps:

  1. Connect to Oracle SQL Developer.
  2. Connect to the Oracle database 19.0.0.0.ru-2021-10.rur-2021-10.r1 on Amazon RDS for Oracle.
  3. Set the 10046 system events.
  4. Run the Oracle Data Pump export.
  5. Validate the event was able to dump the process context.
  6. Identify the files to send to Oracle Support.

Set the 10046 SQL trace event parameter

Imagine your Oracle Data Pump export job is running slow on Amazon RDS for Oracle, and Oracle Support requests that you set the diagnostic event 10046 level 12 at the system level to get the context of the Data Pump processes. To do so, complete the following steps:

  1. Connect to your Oracle Database on Amazon RDS for Oracle using SQL Developer.
  2. Test the connection by choosing Test.
  3. Run the Amazon RDS procedure rdsadmin.rdsadmin_util.set_system_event to set the 10046 events and verify that they have been established: 
    SET SERVEROUTPUT ON
EXEC rdsadmin.rdsadmin_util.set_system_event(10046,12) ;

The script output shows that the event is automatically set as a context event type to trace the process context without you needing to specify the event type.

  1. Run the Data Pump export job: 
    DECLARE
        hdnl NUMBER;
        BEGIN
        hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null);
        DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'test_user.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
        DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'test_user.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
        DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''TEST_USER'')');
        DBMS_DATAPUMP.START_JOB(hdnl);
        END;
        /

  2. Query the Data Pump export log file to get information about the job: 
    select text from table (rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR', 'test_user.log'));

  3. Query the V$DIAG_TRACE_FILE view and look for the Data Pump process trace files (dw00_<process id>.trc and dm00_<process id>.trc) in the output: 
    select TRACE_FILENAME, MODIFY_TIME FROM V$DIAG_TRACE_FILE order by 1;

    You have multiple Data Pump worker files (dw01_<process id>.trc, dw02_<process id>.trc, dw03_<process id>.trc, and so on) if the export job was started with parallel > 1.

  1. Query the worker process file to validate the event dumped the Oracle Data Pump process context: 
    select PAYLOAD from V$DIAG_TRACE_FILE_CONTENTS where TRACE_FILENAME='ORCL_dw00_27002.trc' order by LINE_NUMBER;

  2. Review the query results as in the preceding screenshot to validate tracing.

You can also use the rdsadmin.manage_tracefiles.set_tracefile_table_location procedure to set the tracefile_table view to point to the intended trace file, and you can query the trace file to review its contents by querying the tracefile_table:

exec rdsadmin.manage_tracefiles.set_tracefile_table_location('ORCL_dw00_27007.trc');

select * from tracefile_table;


If the tracing is part of an incident, we support the Oracle ADRCI IPS pack, which you can use to package the files and send them to Oracle support. For more information, refer to Creating incident packages.

  1. Lastly, unset the 10046 system event using the rdsadmin.rdsadmin_util.unset_system_event procedure.
SET SERVEROUTPUT ON
EXEC rdsadmin.rdsadmin_util.unset_system_event(10046);

Summary

As an Amazon RDS for Oracle customer, you can manage your database parameters using parameter groups, but this doesn’t support the special case of the events parameter. The events parameter is generally used to enable system-wide diagnostic tracing of a particular Ora-error or a malfunctioning process. The request to set this parameter is usually from Oracle Support.

In this post, I showed you how to use the system events procedures to set 10046 events parameters for a Data Pump export scenario to get additional diagnostic data for Oracle Support.

To learn more about the system events that you can set on Amazon RDS for Oracle, refer to the following resources:

If you have feedback or questions, leave a comment on the comments section.

About the Author

Lesley Ajanoh is a Senior Database Specialist Solutions Architect with Amazon Web Services. He works with AWS Partner Database Service Acceleration team, focusing on commercial database engines like Oracle. He enjoys working with consulting partners (G/SI) to help build their database practices, database service offerings, and accelerate their Oracle opportunities on AWS.