Pythian Blog: Technical Track

How to Read Oracle Traces from SQL*Plus

Let's say you need to read trace files from Amazon Relational Database Service (RDS). How do you do it? Here is an example of how to list and read those files using rdsadmin:
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.
 
 
 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.
 
 
 
 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.
 
 
 
 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
We have, from 12.2 on, a different method for any platform, using Oracle views. This is the topic for our next post. I hope it helps you today!

No Comments Yet

Let us know what you think

Subscribe by email