AWS RDS: Read Oracle Traces from SQL*Plus

Today’s post is another dealing with AWS services. Let’s say you need to read trace files from RDS — how would you do it?

Here’s an example of how to list and read those files based on the directories from the database setting. To see the existent directories:

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------- ------------------------- ------------------------------------------------------------ -------------
SYS BDUMP /rdsdbdata/log/trace 0
SYS ADUMP /rdsdbdata/log/audit 0
SYS OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch 0
SYS OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch 0
SYS DATA_PUMP_DIR /rdsdbdata/datapump 0
SYS OPATCH_INST_DIR /rdsdbbin/oracle/OPatch 0
SYS TMP /rdsdbdata/userdirs/01 0

7 rows selected.

Once you’ve identified the directory you need, you can list the file as per:

SQL> select * from table (rdsadmin.rds_file_util.listdir( p_directory => 'BDUMP')) order by mtime;

FILENAME TYPE FILESIZE MTIME
---------------------------------------- ---------- ---------- ---------
CPROD1_ora_48800.trm file 73 24-DEC-18
CPROD1_ora_48800.trc file 998 24-DEC-18
CPROD1_ora_86597.trc file 998 24-DEC-18
CPROD1_ora_86597.trm file 73 24-DEC-18
CPROD1_ora_7999.trc file 881 24-DEC-18
CPROD1_ora_7999.trm file 71 24-DEC-18
CPROD1_ora_7997.trm file 71 24-DEC-18
CPROD1_ora_7997.trc file 881 24-DEC-18
CPROD1_ora_8240.trm file 71 24-DEC-18
CPROD1_ora_8240.trc file 881 24-DEC-18
CPROD1_ora_8381.trm file 72 24-DEC-18
CPROD1_ora_8381.trc file 995 24-DEC-18
CPROD1_ora_8540.trc file 881 24-DEC-18
CPROD1_ora_8540.trm file 71 24-DEC-18
CPROD1_ora_9876.trc file 881 24-DEC-18
CPROD1_ora_9876.trm file 71 24-DEC-18
CPROD1_ora_11142.trm file 72 24-DEC-18
CPROD1_ora_11142.trc file 883 24-DEC-18
CPROD1_ora_11182.trc file 883 24-DEC-18
CPROD1_ora_11182.trm file 72 24-DEC-18
CPROD1_ora_55077.trm file 73 24-DEC-18
CPROD1_ora_55077.trc file 997 24-DEC-18
CPROD1_ora_92260.trm file 73 24-DEC-18
CPROD1_ora_92260.trc file 997 24-DEC-18
CPROD1_ora_123869.trc file 1000 24-DEC-18
CPROD1_ora_123869.trm file 74 24-DEC-18
CPROD1_ora_41305.trc file 998 24-DEC-18
CPROD1_ora_41305.trm file 73 24-DEC-18
CPROD1_j002_3293.trc file 114049 24-DEC-18
CPROD1_j002_3293.trm file 370 24-DEC-18
CPROD1_mmon_71739.trc file 7511332 24-DEC-18
CPROD1_mmon_71739.trm file 738330 24-DEC-18
CPROD1_ora_92888.trc file 997 24-DEC-18
CPROD1_ora_92888.trm file 73 24-DEC-18
trace/ directory 323584 24-DEC-18
alert_CPROD1.log file 204808 24-DEC-18
CPROD1_ora_70145.trc file 1470 24-DEC-18
CPROD1_ora_70145.trm file 109 24-DEC-18

3845 rows selected.

With the trace file name confirmed, you can see the content with the following:

SQL> select * from table (rdsadmin.rds_file_util.read_text_file( p_directory => 'BDUMP', p_filename => 'trace/CPROD1_s003_81573.trc'));

From the trace file:
=========================
..................................................................................
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x000e289e.51da.47
Dump kdilk : itl=98, kdxlkflg=0x1 sdc=0 indexid=0x1c53db block=0x0019bdc0
(kdxlpu): purge leaf row
key :(24):
07 78 76 0c 17 17 09 32 08 c7 07 1a 02 28 15 01 18 06 00 1a 3c 99 00 1e

File 3 is not mirrored.

End dump previous blocks for kdsgrp
* kdsgrp1-2: ***********************************************
kdsDumpState: RID context dump

45511581 rows selected.

Also, as a side note, something that may help you out is the listing with the LIKE clause:

SQL> select * from table (rdsadmin.rds_file_util.listdir( p_directory => 'BDUMP')) where filename like '%CPROD1_s003_81573.trc%';

FILENAME TYPE FILESIZE MTIME
---------------------------------------- ---------- ---------- ---------
CPROD1_s003_81573.trc file 1948134047 23-DEC-18

I hope this information helps! If you have any questions or thoughts please leave them in the comments.