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)
That looks great.
Are you aware of this way as well?
I didn't use for a while, but should work:
That works too , my script can be used to check contents of all kinds of trace files.
Didn't know about that one, thanks for sharing!
You’re welcome !!!!