Managing your Oracle database on AWS RDS has some differences comparing it with the management of your Oracle database On-Prem.
One of these differences is the daily retention of 30 days for the alert log in text and 7 days for the XML type. Therefore, how can I find my alert log?
You can use the Amazon CloudWatch Logs to export the logs of your database but if you aren’t using this service you can query alertlog view or a table-like format as below:
When you query the alertlog view, the query will bring all the content of the alert log:
SQL> select message_text from alertlog;
Output:
Current log# 2 seq# 12290 mem# 0: /rdsdbdata/db/TECHDATABASKET_A/onlinelog/o1_mf_2_gvfsmgg8_.log Archived Log entry 12288 added for thread 1 sequence 12289 ID 0x2ef34fad dest 1: Thread 1 cannot allocate new log, sequence 12291 Checkpoint not complete Current log# 2 seq# 12290 mem# 0: /rdsdbdata/db/TECHDATABASKET_A/onlinelog/o1_mf_2_gvfsmgg8_.log MESSAGE_TEXT Thread 1 advanced to log sequence 12291 (LGWR switch) Current log# 3 seq# 12291 mem# 0: /rdsdbdata/db/TECHDATABASKET_A/onlinelog/o1_mf_3_gvfsmgsv_.log Archived Log entry 12289 added for thread 1 sequence 12290 ID 0x2ef34fad dest 1: 59719 rows selected.
When you use the table-like format, you have to replace the value for the name of your file as in the select below.
In this case my instance is called TECHDATABASKET hence the file is named alert_TECHDATABASKET.log.
SQL> SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','alert_TECHDATABASKET.log'));
Output:
Mon Nov 11 11:46:57 2019 Thread 1 cannot allocate new log, sequence 12293 Checkpoint not complete Current log# 4 seq# 12292 mem# 0: /rdsdbdata/db/TECHDATABASKET_A/onlinelog/o1_mf_4_gvfsmh9m_.log Mon Nov 11 11:47:00 2019 Thread 1 advanced to log sequence 12293 (LGWR switch) Current log# 1 seq# 12293 mem# 0: /rdsdbdata/db/TECHDATABASKET_A/onlinelog/o1_mf_1_gvfsmg0x_.log Mon Nov 11 11:47:00 2019 Archived Log entry 12291 added for thread 1 sequence 12292 ID 0x2ef34fad dest 1: 1255 rows selected.
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)