We have an Oracle RDS instance that recently ran out of space with ORA-01653 and ORA-01654 (Unable to extend index ... by 8 in tablespace...) errors.
On investigation by running query:
select nvl(sum(BLOCKS * BLOCK_SIZE),0)/1024/1024/1024 GB from V$ARCHIVED_LOG where DEST_ID=1 and ARCHIVED='YES' and DELETED='NO';
This shows the output:
GB
----------
26.3267608
So we have 26 GB of Archive logs.
Running the following query to get some info on the archive files:
select * from V$ARCHIVED_LOG where DEST_ID=1 and ARCHIVED='YES' and DELETED='NO';
We have nearly 5000 rows of results.
Some example rows:
RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS RESETLOGS_ID FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTR STA ARC APPLIED DEL S COMPLETIO DIC DIC END BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_ COM FAL END_OF_RED BAC
------ ---------- ------------------------------------------------------------ --------- ---------- ---------- ----------------- --------- ------------ ------------- --------- ------------ --------- ---------- ---------- ------- ------- --- --- --------- --- - --------- --- --- --- ------------ ---------------- ----------- --- --- --- ---------- ---
56851 1021006860 /rdsdbdata/db/DRGNFLY_A/arch/redolog-56852-1-1002024260.arc 1 1 56852 222206 04-MAR-19 1002024260 108023802 07-OCT-19 108025497 07-OCT-19 1721 512 ARCH ARCH NO YES NO NO A 07-OCT-19 NO NO NO 0 1 1073401855 NO NO NO NO
56852 1021007166 /rdsdbdata/db/DRGNFLY_A/arch/redolog-56853-1-1002024260.arc 1 1 56853 222206 04-MAR-19 1002024260 108025497 07-OCT-19 108026500 07-OCT-19 626 512 ARCH ARCH NO YES NO NO A 07-OCT-19 NO NO NO 0 1 1073401855 NO NO NO NO
The location of this archive file is: /rdsdbdata/db/DRGNFLY_A/arch/redolog-56849-1-1002024260.arc
The other rows are also in this directory: /rdsdbdata/db/DRGNFLY_A/arch
From: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Log.html
We have ran the stored proc:
exec rdsadmin.rdsadmin_master_util.drop_archivelog_dir;
exec rdsadmin.rdsadmin_master_util.drop_onlinelog_dir;
This command executed but when running the query it still shows 23 GB of archive log files.
To see the archive log retention config I ran:
set serveroutput on
exec rdsadmin.rdsadmin_util.show_configuration;
This produces the output:
NAME:archivelog retention hours
VALUE:0
DESCRIPTION:ArchiveLog expiration specifies the duration in hours before archive/redo log files are automatically deleted.
NAME:tracefile retention
VALUE:1440
DESCRIPTION:tracefile expiration specifies the duration in minutes before tracefiles in bdump are automatically deleted.
My questions:
- Can you let us know how to remove this archive log files and reclaim the space so it can be used for normal DB storage etc.
- Also how to make sure the log files do not continue to be generated or are cleaned up with an appropriate retention period.