DEV Community

Abhilash Kumar Bhattaram for Nabhaas Cloud Consulting

Posted on • Edited on

3 2

Oracle AWS RDS Trace files

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

Enter fullscreen mode Exit fullscreen mode

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:

Enter fullscreen mode Exit fullscreen mode

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

exec rdsadmin.manage_tracefiles.refresh_tracefile_listing;
Enter fullscreen mode Exit fullscreen mode

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (4)

Collapse
 
project42 profile image
Project-42

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;
Enter fullscreen mode Exit fullscreen mode
Collapse
 
abhilash8 profile image
Abhilash Kumar Bhattaram

That works too , my script can be used to check contents of all kinds of trace files.

Collapse
 
project42 profile image
Project-42

Didn't know about that one, thanks for sharing!

Thread Thread
 
abhilash8 profile image
Abhilash Kumar Bhattaram

You’re welcome !!!!