Oracle AWS RDS Trace files

#oracle #aws #rds #database

This Blog post will help you get a clean formatted output of AWS RDS Trace files using the tables rdsadmin.tracefile_listing , since this is AWS RDS once cannot ssh to the RDS system , it shoudl always be accessed from an Oracle client.

I always use my info.sql as a practice to ensure file for a clean sqlplus formatting
https://dev.to/abhilash8/the-info-sql-for-oracle-developer-s-dba-s-pn8

$ cat rds_get_trace.sql 
col filename for a60
col mtime for a60
exec rdsadmin.manage_tracefiles.refresh_tracefile_listing;
SELECT * FROM rdsadmin.tracefile_listing order by mtime;
def tracefile_name=&tracefile_name
spool &tracefile_name
exec rdsadmin.manage_tracefiles.set_tracefile_table_location('&tracefile_name');
select * from tracefile_table;
spool off
undef tracefile_name

The script will show the text output like this in a file

ORCL11GA> @rds_get_trace

FILENAME                                                    |TYPE        | FILESIZE|MTIME
------------------------------------------------------------|------------|---------|--------------------
ORCL11GA_mmon_21409.trc                                     |file        |        8|2020-09-17 15:35
ORCL11GA_mmon_21409.trm                                     |file        |        4|2020-09-17 15:35
ORCL11GA_j000_26542.trc                                     |file        |       48|2020-09-17 15:36
ORCL11GA_j000_26542.trm                                     |file        |        4|2020-09-17 15:36
ORCL11GA_dbrm_27953.trm                                     |file        |        4|2020-09-17 15:40
fips-parameters                                             |file        |        0|2020-09-17 15:40
sqlnet-parameters                                           |file        |        4|2020-09-17 15:40
ORCL11GA_vktm_21385.trc                                     |file        |        4|2020-09-17 15:40
ORCL11GA_vktm_27945.trc                                     |file        |        4|2020-09-17 15:40
ORCL11GA_dbrm_27953.trc                                     |file        |        4|2020-09-17 15:40
ORCL11GA_ora_27255.trc                                      |file        |       28|2020-09-17 15:40
ORCL11GA_vktm_27945.trm                                     |file        |        4|2020-09-17 15:40
ORCL11GA_ora_27931.trc                                      |file        |        4|2020-09-17 15:40
ORCL11GA_ora_28027.trc                                      |file        |        4|2020-09-17 15:40
ORCL11GA_ora_28027.trm                                      |file        |        4|2020-09-17 15:40
ORCL11GA_ora_27931.trm                                      |file        |        4|2020-09-17 15:40
ORCL11GA_ora_27255.trm                                      |file        |        4|2020-09-17 15:40
ORCL11GA_mman_27957.trm                                     |file        |        4|2020-09-17 15:40
ORCL11GA_mman_27957.trc                                     |file        |        4|2020-09-17 15:40
ORCL11GA_vktm_21385.trm                                     |file        |        4|2020-09-17 15:40
ORCL11GA_arc0_28029.trc                                     |file        |        4|2020-09-17 15:41
ORCL11GA_arc0_28029.trm                                     |file        |        4|2020-09-17 15:41
ORCL11GA_lgwr_27961.trc                                     |file        |        4|2020-09-17 15:42
ORCL11GA_lgwr_27961.trm                                     |file        |        4|2020-09-17 15:42
ORCL11GA_arc1_28031.trc                                     |file        |        4|2020-09-17 15:46
ORCL11GA_ora_29486.trm                                      |file        |        4|2020-09-17 15:46
ORCL11GA_ora_29486.trc                                      |file        |        4|2020-09-17 15:46
ORCL11GA_arc1_28031.trm                                     |file        |        4|2020-09-17 15:46
ORCL11GA_ora_29486_SYSTEMSTATE.trm                          |file        |     1900|2020-09-17 15:46
ORCL11GA_ora_29486_SYSTEMSTATE.trc                          |file        |     6860|2020-09-17 15:46
ORCL11GA_mmon_27969.trm                                     |file        |        4|2020-09-17 15:50
ORCL11GA_mmon_27969.trc                                     |file        |        4|2020-09-17 15:50
alert_ORCL11GA.log                                          |file        |      196|2020-09-17 15:51
ORCL11GA_arc3_28035.trc                                     |file        |        4|2020-09-17 15:51
ORCL11GA_arc3_28035.trm                                     |file        |        4|2020-09-17 15:51



Enter value for tracefile_name: alert_ORCL11GA.log 

TEXT
---------------------------------------------------------------------------------------------------------------
Thread 1 cannot allocate new log, sequence 97922
Checkpoint not complete
  Current log# 1 seq# 97921 mem# 0: /rdsdbdata/db/ORCL11GA/onlinelog/o1_mf_1_gr78wd60_.log
Thread 1 cannot allocate new log, sequence 97922
Private strand flush not complete
  Current log# 1 seq# 97921 mem# 0: /rdsdbdata/db/ORCL11GA/onlinelog/o1_mf_1_gr78wd60_.log
Thread 1 advanced to log sequence 97922 (LGWR switch)
  Current log# 2 seq# 97922 mem# 0: /rdsdbdata/db/ORCL11GA/onlinelog/o1_mf_2_gr78wdrf_.log
Sat Sep 19 17:09:00 2020
Archived Log entry 97920 added for thread 1 sequence 97921 ID 0x7b52cfc7 dest 1:
Sat Sep 19 17:13:58 2020
Thread 1 cannot allocate new log, sequence 97923
Checkpoint not complete
  Current log# 2 seq# 97922 mem# 0: /rdsdbdata/db/ORCL11GA/onlinelog/o1_mf_2_gr78wdrf_.log
Thread 1 cannot allocate new log, sequence 97923
Private strand flush not complete
  Current log# 2 seq# 97922 mem# 0: /rdsdbdata/db/ORCL11GA/onlinelog/o1_mf_2_gr78wdrf_.log
Thread 1 advanced to log sequence 97923 (LGWR switch)
  Current log# 3 seq# 97923 mem# 0: /rdsdbdata/db/ORCL11GA/onlinelog/o1_mf_3_gr78wfb7_.log
Sat Sep 19 17:14:04 2020
Archived Log entry 97921 added for thread 1 sequence 97922 ID 0x7b52cfc7 dest 1:
Sat Sep 19 17:19:04 2020
Thread 1 cannot allocate new log, sequence 97924
Checkpoint not complete
  Current log# 3 seq# 97923 mem# 0: /rdsdbdata/db/ORCL11GA/onlinelog/o1_mf_3_gr78wfb7_.log
Thread 1 advanced to log sequence 97924 (LGWR switch)
  Current log# 4 seq# 97924 mem# 0: /rdsdbdata/db/ORCL11GA/onlinelog/o1_mf_4_gr78wg72_.log
Sat Sep 19 17:19:08 2020
Archived Log entry 97922 added for thread 1 sequence 97923 ID 0x7b52cfc7 dest 1:

In case the desired files are not seen we can refresh the tracefile listing as below

exec rdsadmin.manage_tracefiles.refresh_tracefile_listing;

Top comments (4)

Project-42 • Sep 20 '20

That looks great.

Are you aware of this way as well?

I didn't use for a while, but should work:

set lines 500
set pages 50
col originating_timestamp for a40
col rownum for 999999
col Error for a150
-- SET PAUSE ON
-- SET PAUSE 'Press Return to Continue'
select inst_id, rownum "Line",TO_CHAR(originating_timestamp,'DD-MON-YYYY HH24:MI:SS'), message_text "Error"
from TABLE(gv$(cursor(select inst_id, originating_timestamp, message_text
from v$diag_alert_ext
where originating_timestamp >= (sysdate - 1)
AND regexp_like(message_text, '(ORA-|error)'))))
order by originating_timestamp asc;