Oracle RDS Monitoring beyond free space

0

We want to monitor Oracle RDS instances beyond allocated DB storage such as Tablespace utilization so that we can generate CW metric alarm.

2개 답변
0

Monitoring RDS instances, including Oracle, goes beyond just checking for free space. Amazon CloudWatch provides a wide range of metrics for monitoring your RDS instances. These metrics include CPU utilization, database connections, read and write operations, and many others.

For monitoring tablespace utilization specifically, you can enable Enhanced Monitoring for your RDS instances. Enhanced Monitoring provides metrics in real time for the operating system that your DB instance runs on. You can view key operational metrics for your DB instance load, including active sessions, I/O, and system metrics, which can help you analyze and troubleshoot your database performance.

However, please note that Enhanced Monitoring doesn't provide tablespace utilization out of the box. Oracle tablespaces are specific to Oracle databases and aren't directly exposed as a metric in RDS or CloudWatch.

For tablespace-related metrics, you will have to query the Oracle system views directly. You can create a procedure in Oracle to gather this information and then use RDS Events or a Lambda function to trigger a custom CloudWatch metric.

SELECT 
   df.tablespace_name "Tablespace",
   totalusedspace "Used MB",
   (df.totalspace - tu.totalusedspace) "Free MB",
   df.totalspace "Total MB",
   ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Percent Free"
FROM
   (SELECT 
      tablespace_name,
      ROUND(SUM(bytes) / 1048576) TotalSpace
    FROM 
      dba_data_files
    GROUP BY 
      tablespace_name) df,
   (SELECT 
      ROUND(SUM(bytes)/(1024*1024)) totalusedspace, tablespace_name
    FROM 
      dba_segments
    GROUP BY 
      tablespace_name) tu
WHERE
   df.tablespace_name = tu.tablespace_name;
profile picture
전문가
답변함 일 년 전
  • Thank you. Do you mean to trigger the DB stored procedure via Lambda function and trigger the Lambda using EventBridge? How do I create an alarm based on a specific metric?

0

In AWS RDS, where we create BIGFILE tablespaces with AUTOEXTEND, measuring space usage based on datafiles does not provide an accurate picture. Instead, what you want is a tablespace level picture.

Eg. CREATE TABLESPACE CPS_NDX1_TSP DATAFILE SIZE 25G AUTOEXTEND ON MAXSIZE 90G;

Using the query above, here is the output generated :

Tablespace Used MB Free MB Total MB Percent Free

CPS_TSP 53950 2850 56800 5

This output does not take into account that the datafile can extend upto 90G.

Instead, I found this query here more useful :

column USED_PERCENT format 999.99

column FREE_PERCENT format 999.99

select TABLESPACE_NAME,USED_PERCENT,100-USED_PERCENT "FREE_PERCENT" from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME USED_PERCENT FREE_PERCENT

CPS_TSP 56.54 43.46

Here, I see that %free for this tablespace is 43.46

답변함 한 달 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인