Thursday, January 15, 2026

Successfully managing storage in Amazon RDS for Oracle Databases


Environment friendly storage administration is essential for sustaining the efficiency, reliability, and cost-effectiveness of your Oracle databases operating on Amazon RDS. As your knowledge grows and your workloads evolve, it’s important to proactively monitor and optimize your storage utilization. On this submit, we discover numerous strategies and finest practices for successfully managing storage in RDS for Oracle Databases.

Amazon Relational Database Service (Amazon RDS) for Oracle offers a completely managed database service that makes it easy to arrange, function, and scale Oracle databases within the AWS Cloud. Amazon RDS for Oracle manages time-consuming database administration duties so you may focus in your functions. When you develop and enhance your core functions, RDS handles important backend operations: provisioning new databases, implementing patches, operating backups, managing restoration processes, monitoring for failures, and performing repairs. With this complete database administration, you may spend extra time constructing functions and fewer time on administrative duties. It presents a variety of occasion varieties and storage choices to fulfill your efficiency and value necessities and offers built-in excessive availability options resembling Multi-AZ(Availability Zone) deployments for enhanced availability.

Overview

On this submit, we talk about other ways to watch, optimize, and handle free storage in Amazon RDS.

1. Monitor the storage

Amazon CloudWatch empowers you to carefully monitor the well being of your RDS cases and observe modifications to the underlying infrastructure and database workloads. Utilizing CloudWatch you may monitor a variety of metrics over particular time intervals, resembling disk utilization, CPU utilization, reminiscence utilization, disk I/O, and community site visitors. You should utilize this complete visibility to rapidly determine and tackle points which may come up.

You can even additional create a customized SQL question just like one talked about in: Why does my Amazon RDS for Oracle DB occasion use extra storage than anticipated? and ship an e-mail to the required recipients utilizing engine options resembling UTL_MAIL as talked about in: How do I ship an e-mail from my Amazon RDS for Oracle DB occasion?

RDS for Oracle is absolutely managed and consists of an information volumes, binary(bin) quantity and root quantity. The basis and bin volumes comprise the OS and the Oracle Database binaries respectively. These volumes are monitored and maintained by the interior Amazon RDS automation system. As an Amazon RDS for Oracle buyer, you could monitor the information volumes. This is similar storage that’s provisioned in the course of the Amazon RDS occasion creation time. The info volumes for RDS for Oracle occasion hosts the information recordsdata, redo and archive log recordsdata, and hint recordsdata. That is the placement for the database listing and so it should host the dump file for knowledge pump export and import, RMAN backup items, and recordsdata referred to by exterior tables. Moreover, exterior tables could be saved in Amazon Easy Storage Service (Amazon S3), with S3 integration, offering extra flexibility for knowledge storage and entry choices.

The CloudWatch metric to watch free cupboard space per quantity is FreeStorageSpace. It offers the quantity of obtainable cupboard space per quantity and is measured in bytes, as proven within the following determine.


Moreover, you may arrange CloudWatch alarms to obtain notifications when a metric exceeds a predefined threshold. Utilizing this proactive method you may take well timed motion to resolve issues earlier than they escalate and influence your operations. CloudWatch metric alarms are highly effective monitoring instruments that monitor a single CloudWatch metric or the result of a mathematical expression derived from a number of CloudWatch metrics. It may set off numerous actions, resembling sending notifications to an Amazon Easy Notification Service (Amazon SNS) subject, primarily based on the metric or expression worth in relation to a specified threshold over an outlined time interval. This helps proactive monitoring and fast identification and backbone of points to take care of the reliability, availability, and optimum efficiency of your AWS surroundings.

To watch an Amazon RDS for Oracle occasion, use the next:

aws cloudwatch put-metric-alarm 
  --alarm-name FreeSpace 
  --alarm-description "Take a look at Alarm when Area Beneath 50 GB" 
  --metric-name FreeStorageSpace 
  --namespace AWS/RDS 
  --statistic Common 
  --period 60 
  --threshold 50 
  --comparison-operator LessThanThreshold 
  --dimensions "Identify=DBInstanceIdentifier,Worth=testrds" 
  --evaluation-periods 2 
  --alarm-actions  
  --region us-west-2

The previous AWS Command Line Interface (AWS CLI) command creates a CloudWatch alarm named “FreeSpace” to watch the free cupboard space of an RDS database occasion known as “testrds” within the US West (Oregon) AWS Area. The alarm is ready to set off when the common free cupboard space falls under 50 GB for 2 consecutive 1-minute intervals. When activated, it sends a notification by means of an Amazon SNS subject. This setup helps you proactively handle database storage by alerting them earlier than the database runs critically low on area. The command makes use of a shared AWS profile for authentication, permitting for standardized entry throughout a staff or group.

Monitor Further Storage Volumes (ASVs)

For monitoring extra storage volumes in CloudWatch, a brand new dimension “RDS -> DB Occasion Identifier, Quantity Identify (per-volume metrics)” is obtainable.

From the filtering, you possibly can additional drill all the way down to database identifier and quantity title. The amount title for added storage volumes would observe the naming conference rdsdbdata the place n could be 2, 3 or 4 (max 3 extra volumes). The first storage quantity has a quantity title of “rdsdbdata” (with none quantity).

Within the following screenshot, we take a look at the metrics of the extra storage quantity: rdsdbdata2

To view the metrics in Enhanced Monitoring, choose “Enhanced Monitoring” choice from the drop down beneath the Monitoring tab.

You may open the Handle Graphs choice and choose the required metrics:

When you select save, you may view the quantity metrics of all of the volumes together with any extra storage volumes. The Complete Filesystem(/rdsdbdata*) metric offers the combination sum of the first and extra storage volumes.

To view the metrics in Database Insights Superior, go to Database Telemetry -> Metrics -> Create Widget

Enter a widget title and seek for “rdsdbdata2” within the choose metrics to pick the related metrics for the ASV.

When you create the widget, you must have the ability to view the small print.

2. Storage autoscaling

Storage autoscaling is a handy characteristic that allows Amazon RDS to mechanically scale up your storage when it detects that you simply’re operating out of free cupboard space.

Be aware: Storage autoscaling helps forestall sudden storage getting full outdoors of labor hours or monitoring home windows. It’s really useful to scale the storage of RDS for Oracle occasion to the anticipated measurement wanted plus buffer area. That is to keep away from going into the storage optimization state, which might take 24 hours or extra to finish.

You may examine if in case you have storage autoscaling enabled in your RDS for Oracle occasion, through the use of the AWS Administration Console for Amazon RDS or utilizing the AWS CLI to explain the occasion and examine if the MaxAllocatedStorage parameter has a worth. It’s vital to know how Storage autoscaling works and its limitations.

Amazon RDS initiates a storage modification for an autoscaling-enabled DB occasion when the next situations are met:

  • Free accessible area is lower than or equal to 10% of the allotted storage.
  • The low-storage situation lasts for no less than 5 minutes.
  • At the least 6 hours have handed for the reason that final storage modification or storage optimization has accomplished, whichever is longer.

The extra storage is added in increments of whichever is larger:

  • 10 GiB
  • 10% of the presently allotted storage
  • Predicted storage development exceeding the present allotted storage measurement inside the subsequent 7 hours primarily based on the FreeStorageSpace metric from the previous hour.

Keep in mind, whereas storage autoscaling is a helpful characteristic, it shouldn’t be relied upon solely. Figuring out the constraints of this characteristic, implementing sturdy monitoring practices, and commonly reviewing storage necessities is essential for sustaining a wholesome and well-performing RDS for Oracle database.

Storage autoscaling is presently not accessible for added storage volumes. If an occasion has extra storage volumes, you may nonetheless use storage autoscaling for the first quantity. Storage autoscaling shouldn’t be supported just for “extra storage volumes”. Nevertheless, you may monitor the storage as described in Monitor the storage, setup alarms and manually enhance the storage or release area as mentioned in Eradicating undesirable recordsdata.

3. Eradicating undesirable recordsdata

Database operations resembling RMAN backups, knowledge pumps, archive logs, audit and hint recordsdata repeatedly generate recordsdata that may rapidly eat useful cupboard space. When left unmanaged, these recordsdata can result in efficiency degradation, failed operations, and pointless prices as RDS storage is charged primarily based on allotted area.

To delete a file in database listing like DATAPUMP_DIR, you need to use the UTL_FILE.FREMOVE package deal in RDS for Oracle. This package deal offers a handy approach to take away recordsdata from directories and launch area again to the working system. See the Performing miscellaneous duties for Oracle DB cases for extra data on find out how to handle database directories in an RDS for Oracle occasion. It is strongly recommended to have an audit coverage to trace actions carried out utilizing UTL_FILE package deal for safety objective.

For DATAPUMP_DIR:

-- Utilizing UTL_FILE.FREMOVE
BEGIN
  UTL_FILE.FREMOVE('DATAPUMP_DIR', 'filename.dmp');
END;
/

For hint recordsdata, audit recordsdata and listener logs, the default retention in Amazon RDS is 7 days. For alert logs, it’s 30 days. It’s vital to evaluation the rententon schedule and make needed adjustements in accordance with your necessities.

For extra details about sustaining log recordsdata, see Amazon RDS for Oracle database log recordsdata.

The administration of archive log recordsdata in RDS for Oracle follows particular retention patterns primarily based on the occasion configuration and replication setup. The default retention interval for archive logs in a standalone RDS for Oracle occasion is ready to 0 hours, which means these logs are mechanically purged from the host system instantly after being efficiently uploaded to Amazon S3 for backup functions.

In RDS for Oracle, the retention habits modifications considerably beneath completely different replication eventualities. When learn replicas are carried out, the retention interval defaults to 2 hours as a result of logs have to be stored till they’re utilized to all replicas, resulting in elevated storage necessities. For cross Area replicas, RDS for Oracle retains the transaction logs on the supply DB occasion till they’ve been transmitted and utilized to all cross-Area learn replicas. Therefore, it is very important monitor the duplicate lag metric.

For extra details about retention for archive log recordsdata, see Retaining archived redo logs.

Managing audit trails

It’s vital to correctly handle audit trails in your databases to take care of environment friendly efficiency and optimum use of disk area. As audit trails in your databases develop in quantity, querying an audit path with a big quantity of audit knowledge can influence efficiency and result in area scalability points. It’s finest to archive the previous information and purge them from the net audit path periodically.

For extra data, see Safety auditing in Amazon RDS for Oracle: Half 1.

4. Desk and tablespace reorganization

Desk reorganization in Oracle is the method of restructuring a desk to enhance its efficiency, reclaim unused area, or modify its construction. Right here’s an outline of desk reorganization and find out how to do it:

Reorganize tables

Desk reorganization in Oracle databases serves a number of vital functions that instantly influence database efficiency and effectivity. The first motivation for desk reorganization is to handle fragmentation points that naturally happen over time as knowledge is inserted, up to date, and deleted, resulting in scattered knowledge blocks and inefficient area utilization. Via reorganization, unused area could be reclaimed, decreasing the general storage footprint and bettering area effectivity. This course of considerably enhances question efficiency by optimizing the bodily storage of information, ensuring that associated knowledge is saved contiguously the place potential.

Moreover, reorganization permits for the reordering of information to higher align with frequent entry patterns, resembling sorting knowledge primarily based on ceaselessly used columns or index keys. From a structural perspective, desk reorganization offers a possibility to change desk buildings, resembling including or dropping columns, with out the necessity for separate operations which may influence efficiency. This complete method to desk upkeep not solely optimizes present efficiency but additionally prepares the database for future development and altering enterprise necessities.

Common desk reorganization ought to be a part of a database upkeep technique, notably for tables experiencing excessive volumes of DML operations or these vital to enterprise operations.

Verify for desk fragmentation

For details about checking for desk fragmentation, see Find out how to Discover Fragmentation for Tables and LOBs (Doc ID KB138882)

Run the next process with schema title and desk title:

SQL> SELECT SUM(bytes)/1024/1024 AS "Desk Measurement (MB)" FROM user_segments 
WHERE segment_name="COUNTRIES_1";

Desk Measurement (MB)
---------------
       1216

SQL> set serveroutput on
declare
v_unformatted_blocks quantity;
v_unformatted_bytes quantity;
v_fs1_blocks quantity;
v_fs1_bytes quantity;
v_fs2_blocks quantity;
v_fs2_bytes quantity;
v_fs3_blocks quantity;
v_fs3_bytes quantity;
v_fs4_blocks quantity;
v_fs4_bytes quantity;
v_full_blocks quantity;
v_full_bytes quantity;
start
dbms_space.space_usage ('ADMIN', 'COUNTRIES_1', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks="||v_unformatted_blocks);
dbms_output.put_line("FS1 Blocks="||v_fs1_blocks);
dbms_output.put_line("FS2 Blocks="||v_fs2_blocks);
dbms_output.put_line("FS3 Blocks="||v_fs3_blocks);
dbms_output.put_line("FS4 Blocks="||v_fs4_blocks);
dbms_output.put_line("Full Blocks="||v_full_blocks);
finish;
/
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25  Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 951
FS3 Blocks = 969
FS4 Blocks = 78521
Full Blocks = 72475

PL/SQL process efficiently accomplished.
Reference:
fs1_blocks : Variety of blocks having no less than 0 to 25% free area
fs2_blocks : Variety of blocks having no less than 25 to 50% free area
fs3_blocks : Variety of blocks having no less than 50 to 75% free area
fs4_blocks : Variety of blocks having no less than 75 to 100% free area
ful1_blocks : Complete variety of blocks full within the section

What choices can be found in Oracle to reorganize tables?

Be aware: The next ought to be examined in lower-priority environments first. Some strategies may require downtime and ought to be carried out throughout upkeep home windows.

Oracle offers a number of approaches for desk reorganization, every with distinct benefits and trade-offs: CTAS, ALTER TABLE MOVEDBMS_REDEFINITION, export/import, and ALTER TABLE SHRINK SPACE.

CTAS technique

Desk reorganization in Oracle could be achieved by means of a number of strategies, with CREATE TABLE AS SELECT (CTAS) being one of the vital used approaches. The CTAS technique includes a three-step course of:

  1. Creating a brand new desk with the specified construction and copying knowledge from the prevailing desk utilizing a SELECT assertion.
    CREATE TABLE new_table AS SELECT * FROM old_table;

  2. Dropping the unique desk.
  3. Renaming the brand new desk to match the unique desk title.
    ALTER TABLE new_table RENAME TO old_table;

Whereas this technique presents benefits resembling simplicity, straightforwardness, and the pliability to implement structural modifications rapidly, it comes with notable concerns and limitations.

The first disadvantage is the requirement for double the cupboard space in the course of the reorganization course of, as a result of each the unique and new tables exist concurrently. Moreover, the desk turns into unavailable for DML operations in the course of the reorganization, which might influence enterprise operations in manufacturing environments. One other important consideration is that dependent objects resembling indexes, triggers, and grants have to be recreated after the reorganization, requiring cautious planning and extra steps within the course of.

Regardless of these limitations, CTAS stays a well-liked selection for smaller tables or throughout upkeep home windows the place downtime is appropriate. Right here’s the essential syntax:

The ALTER TABLE MOVE command presents an easy technique for reorganizing tables and reclaiming area.

ALTER TABLE table_name MOVE TABLESPACE new_tablespace;

Through the use of this method, you may transfer tables to completely different tablespaces, compress knowledge, and reorganize the desk’s storage construction. The MOVE operation is advantageous for its directness in defragmenting area, potential to vary storage parameters, and choice to maneuver tables throughout tablespaces. Nevertheless, it requires downtime as a result of the desk is locked in the course of the operation, and all indexes have to be rebuilt afterward. The operation could be enhanced with the ONLINE key phrase in Enterprise Version to reduce disruption, although this may influence efficiency.

The next is a pattern ALTER TABLE MOVE implementation:

-- Fundamental transfer operation
ALTER TABLE table_name MOVE;

-- Transfer with particular parameters
ALTER TABLE table_name MOVE 
TABLESPACE new_tablespace
COMPRESS
NOLOGGING;

-- On-line transfer (Enterprise Version)
ALTER TABLE table_name MOVE ONLINE;

-- Rebuild indexes after transfer
ALTER INDEX index_name REBUILD;

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => "SCHEMA_NAME',
    orig_table => 'OLD_TABLE',
    int_table => 'INTERIM_TABLE');
END;
/

DBMS_REDEFINITION represents a extra subtle method, permitting for on-line desk reorganization with minimal downtime. This technique is especially useful in manufacturing environments the place steady availability is vital. Utilizing DBMS_REDEFINITION, you may carry out complicated structural modifications whereas sustaining dependent objects mechanically. The method begins with DBMS_REDEFINITION.START_REDEF_TABLE, specifying the unique and interim desk particulars. Whereas this technique presents important benefits for manufacturing techniques, it requires cautious implementation, extra cupboard space in the course of the course of, and is perhaps slower than offline alternate options.

The export/import technique offers essentially the most complete reorganization choice, enabling full desk restructuring and knowledge migration between completely different Oracle variations or techniques. Nevertheless, this method comes with appreciable drawbacks, together with important downtime necessities, extra storage wants for export recordsdata, and probably lengthy execution occasions for big tables.

Shrink

The ALTER TABLE SHRINK SPACE command presents an environment friendly on-line technique for reclaiming unused area in Oracle tables and their related indexes, making it a useful software for database area administration. This operation is especially efficient for tables experiencing important area fragmentation due to frequent DELETE or UPDATE operations, as a result of it really works by transferring rows to the start of the desk or section and releasing free area on the finish for return to the tablespace. Earlier than implementing a shrink operation, confirm the desk’s eligibility and allow row motion utilizing the next command.

ALTER TABLE table_name ENABLE ROW MOVEMENT';

The shrink course of usually follows a two-step method:

  1. Use the COMPACT choice to defragment the area.
  2. Use CASCADE to shrink each the desk and its dependent objects.

Whereas this technique presents a number of benefits, together with on-line operation with minimal disruption, no extra storage necessities, and automated index upkeep, there are vital concerns to remember. The operation requires row motion to be enabled, may quickly influence efficiency throughout execution, and isn’t appropriate for tables with long-running transactions or sure Giant Objects (LOB) segments. Tables with triggers or international key constraints may require particular consideration, and it’s really useful to carry out shrink operations throughout low-activity intervals. The standard implementation sequence appears like the next:

ALTER TABLE table_name ENABLE ROW MOVEMENT;
ALTER TABLE table_name SHRINK SPACE COMPACT;
ALTER TABLE table_name SHRINK SPACE CASCADE;

Weekly monitoring of area utilization and fragmentation ranges might help you establish the optimum timing for shrink operations, making it a necessary a part of proactive database upkeep methods.

Oracle Desk Reorganization Strategies: Execs and Cons

Technique Execs Cons
CTAS (Create Desk As Choose) • Easy and simple implementation

• Versatile for implementing structural modifications

• Good for smaller tables

• Full management over new desk construction
• Requires double cupboard space throughout course of

• Desk unavailable for DML operations throughout reorganization

• Dependent objects (indexes, triggers, grants) have to be recreated

• Causes downtime – finest for upkeep home windows
ALTER TABLE MOVE • Direct technique for defragmenting area

• Can change storage parameters

• Capacity to maneuver tables throughout tablespaces

• Less complicated syntax than different strategies
• Desk is locked throughout operation (downtime required)

• All indexes have to be rebuilt afterward

• Restricted structural modifications potential

• Efficiency influence throughout operation
DBMS_REDEFINITION • On-line reorganization with minimal downtime

• Maintains dependent objects mechanically

• Permits complicated structural modifications

• Supreme for manufacturing environments

• Steady availability throughout course of
• Requires cautious implementation

• Wants extra cupboard space throughout course of

• Extra complicated to implement than different strategies

• Doubtlessly slower than offline alternate options

• Requires Enterprise Version options
Export/Import • Most complete reorganization choice

• Full desk restructuring potential

• Helps knowledge migration between completely different Oracle variations
• Vital downtime necessities

• Further storage wanted for export recordsdata

• Lengthy execution occasions for big tables

• Most disruptive of all strategies
ALTER TABLE SHRINK SPACE • Environment friendly on-line technique with minimal disruption

• No extra storage necessities

• Computerized index upkeep

• Good for reclaiming area after mass deletes

• Can embrace CASCADE choice for dependent objects
• Requires row motion to be enabled

• Might quickly influence efficiency

• Not appropriate for tables with long-running transactions

• Restricted effectiveness for sure LOB segments

• Particular consideration wanted for tables with triggers or international keys

Keep in mind, one of the best technique to make use of relies on your particular wants, desk measurement, accessible downtime, and Oracle model. Take a look at in a non-production surroundings earlier than deploying to manufacturing.

Tablespace administration and reorganization in Oracle: A complete method

Tablespace reorganization in Oracle databases represents a vital facet of database upkeep geared toward optimizing storage utilization, bettering efficiency, and managing area effectively. Tablespaces, being logical storage models that group associated knowledge segments, require common upkeep to assist forestall fragmentation and keep optimum efficiency. Widespread indicators indicating the necessity for reorganization embrace degraded question efficiency, elevated area fragmentation, and inefficient area utilization. Oracle offers a number of approaches to tablespace administration, together with transferring datafiles, resizing tablespaces, including new datafiles, and implementing space-saving options like desk compression.

The coalesce operation represents one method inside this broader upkeep technique. Tablespace coalesce operations in Oracle present a mechanism for combining adjoining areas of free area inside a tablespace to create bigger contiguous blocks, essentially bettering area utilization and decreasing fragmentation. This operation is particularly designed for regionally managed tablespaces with automated section area administration, working by scanning bitmap blocks to determine and merge adjoining free extents. The coalesce operation presents a number of important benefits:

  • It may be carried out whereas the tablespace stays on-line and accessible
  • Requires no extra cupboard space for execution
  • It may be both mechanically triggered by Oracle or manually initiated utilizing the command
    ALTER TABLESPACE tablespace_name COALESCE;

Whereas the operation successfully improves area utilization and might improve efficiency by means of decreased fragmentation, it does include sure limitations. The method won’t be as efficient as a whole reorganization when coping with 80% or extra fragmented tablespaces, can eat important system sources notably for big tablespaces, and notably doesn’t compact or transfer present knowledge. For extra data, see Resizing tablespaces, knowledge recordsdata, and temp recordsdata.

Common monitoring and proactive upkeep of tablespaces, together with periodic reorganization and coalescing operations, are important parts of a complete database administration technique. You need to develop a scientific method to tablespace administration, contemplating components resembling peak utilization intervals, development patterns, and efficiency necessities whereas implementing these upkeep operations.

It’s vital to notice that whereas tablespace coalesce might help optimize area utilization, it’s not an alternative to correct sizing and administration of tablespaces. Common monitoring and proactive administration are nonetheless needed for optimum database efficiency.

In some circumstances when knowledge is faraway from an Oracle tablespace in an Amazon RDS database, the allotted area for that tablespace doesn’t mechanically shrink. The database marks the information blocks that have been beforehand occupied as free area, permitting them to be reused for brand spanking new knowledge insertions inside the identical tablespace. Nevertheless, the general allotted measurement of the tablespace stays unchanged. To reclaim the unused area and scale back the tablespace’s footprint, you could manually resize the tablespace or its related knowledge recordsdata by means of particular procedures.

5. Scale back the dimensions of the tablespace

To resize a tablespace, see How do I resize the tablespace for my Amazon RDS for Oracle DB occasion?

Remember that resizing operations can influence efficiency, so it’s really useful to carry out them throughout upkeep home windows or low exercise intervals. Take backups and check the procedures in a non-production surroundings earlier than making use of them to your manufacturing databases.

SYSAUX utilization

In Amazon RDS for Oracle, carefully monitoring the SYSAUX tablespace is vital, as a result of it will probably develop quickly resulting from numerous components such because the Computerized Workload Repository (AWR), optimizer statistics, or auditing. By default, the SYSAUX tablespace is ready as auto-extensible, making it much more vital to watch its area utilization.

To successfully handle the SYSAUX tablespace, collect detailed details about which parts are consuming area inside the tablespace, the retention interval of AWR experiences, and the section distribution. Oracle offers “Ideas if Your SYSAUX Tablespace Grows Quickly or Too Giant (Doc ID 1292724.1)” which presents useful steerage on addressing SYSAUX tablespace development points. Be aware that if the dimensions of the AUDIT tablespace is full, you won’t have the ability to log in to the database if audit_sys_operations is ready to true.

Advisable practices to keep away from or mitigate SYSAUX development issues:

  1. Make sure that no utility or consumer objects are current within the SYSAUX tablespace, as a result of it’s meant for Oracle-managed parts solely.
  2. If auditing is enabled, use a separate tablespace for auditing and measurement it appropriately. You should utilize the next command to set the audit path location:
    exec dbms_audit_mgmt.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,audit_trail_location_value=>'New tablespace');

  3. Due to Amazon RDS limitations, you can’t instantly reorganize objects inside the SYSAUX tablespace to launch free area. Nevertheless, you may think about the next choices:
    1. Flip off AUTOEXTEND for the SYSAUX tablespace, so the free area inside the tablespace might be reused for different segments.
    2. Use AWS Database Migration Service (AWS DMS) or Information Pump emigrate to a brand new occasion and monitor future development on SYSAUX.
  4. Monitor SYSAUX development commonly to determine the parts contributing to its development, and take applicable actions, resembling adjusting AWR retention intervals, rebuilding optimizer statistics, or resizing the tablespace as wanted.

By following these practices and utilizing Oracle’s documentation, you may proactively handle the SYSAUX tablespace, assist forestall sudden development points, and keep optimum efficiency and stability of your Oracle database surroundings.

Conclusion

Efficient storage administration is crucial for sustaining the efficiency, reliability, and cost-effectiveness of your Oracle databases operating on Amazon RDS. By implementing sturdy monitoring practices, utilizing options like storage autoscaling (for RDS for Oracle), eradicating undesirable recordsdata, and performing desk reorganizations, you may optimize your storage utilization and ensure your databases function at peak effectivity.

See the Working with Storage for Amazon RDS for Oracle Situations for the newest updates and finest practices.


In regards to the authors

Radhika Chakravarty

Radhika Chakravarty

Radhika is a Senior Options Architect specializing in Generative AI and Database applied sciences. She collaborates with NAMER Unbiased Software program Vendor (ISV) companions to architect enterprise-scale AI options. With in depth database experience spanning relational and NoSQL platforms, Radhika excels at designing AI options constructed on sturdy, scalable knowledge architectures that bridge conventional knowledge administration with trendy AI-driven functions.

Balaji Salem Balasundram

Balaji Salem Balasundram

Balaji is a Sr. Technical Account Supervisor primarily based in Salt Lake Metropolis, USA. As a Gold Jacket winner, Balaji works carefully with AWS clients to facilitate their migration journey to the AWS cloud. He focuses on serving to organizations obtain increased flexibility, scale, and resiliency with database companies within the AWS ecosystem.

Narendra Harsha Nimmagadda

Narendra Harsha Nimmagadda

Narendra is a Database Engineer at AWS, bringing 17+ years of transformational database engineering management to enterprise clients worldwide. As an AWS-certified skilled and subject material professional in RDS Oracle and PostgreSQL, Harsha focuses on architecting and executing complicated database migration methods that ship measurable enterprise worth and operational excellence.

Deepak Mani

Deepak Mani

Deepak is a Sr. Cloud Database Engineer at AWS. He’s a Topic Matter Skilled for Amazon RDS for Oracle and Amazon RDS. Deepak has 15 years of expertise working with relational databases.

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest Articles