- This blog is intended for Oracle DBA’s administering Amazon RDS for Oracle.
Issue:
- Oracle DBAs may need to view alertlog for the database, for troubleshooting issues and looking out for any ORA- errors and WARNINGS reported in the alertlog. But Amazon RDS for Oracle is Platform-as-a-service (PaaS) offering from Amazon. We do not have access to the database servers to login to the server and view the alertlog.
This blog shows a way to access alertlog for Oracle Databases running on Amazon RDS.
Solution
- The text alert log is rotated daily with 30-day retention managed by Amazon RDS. The XML alert log is retained for at least seven days. You can access this log by using the ALERTLOG
- Connect to RDS instance from any Oracle Client (Oracle SQL Developer, sqlplus .. etc)
$sqlplus root/***********@AWSCLD
- Now query alertlog view.
- SQL > select * from alertlog;
We can filter the rows based on our need.
For example :
- If looking for ORA- reported in alertlog:
SQL> select ORIGINATING_TIMESTAMP,message_text from alertlog where message_text like ‘ORA-%’;
- If interested in alertlog for only the past 24 hours.
SQL> select ORIGINATING_TIMESTAMP,message_text from alertlog where ORIGINATING_TIMESTAMP > sysdate-1
References:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.Oracle.html
Recommended Posts
