How to delete archive log file on Oracle RDS instance

0

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:

  1. 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.
  2. Also how to make sure the log files do not continue to be generated or are cleaned up with an appropriate retention period.
ptha
asked 4 years ago5628 views
1 Answer
1

This is a furphy. Archive logs are only kept online until AWS backs them up. Because they are handled in the background, the Oracle archive log table is out of date.
If you are on 11.2.0.4v19 or above, you can run this to clean up your archive log table. Basically an rman crosscheck cleanup equivalent.

set serveroutput on
BEGIN
rdsadmin.rdsadmin_rman_util.crosscheck_archivelog(
p_delete_expired => FALSE,
p_rman_to_dbms_output => FALSE);
END;
/

Check the size of your disk in the AWS console and then do a sum on the size of your data files, temp files and redo logs and see the difference.
Mine turned out to be massive data growth.
Unless your alert log is showing archive log stuck messages.

Sparkle
answered 4 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions