How to delete archive log files on AWS RDS Oracle instance

user_1860867 0
Viewed 5030

We have an AWS Oracle RDS instance that recently ran out of space with ORA-01653 and ORA-01654 (Unable to extend index X by 8 in tablespace X...) 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

This leads me to believe 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 AWS Oracle Common DBA Tasks document, 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 successfully, but when running the query above again it still shows 26 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.

This would lead me to believe that as archivelog retention hours has a value of 0, these files should be automatically deleted?

My questions:

  1. Is any of my above analysis/queries incorrect and these archive logs are not actually contributing to my tablespace space errors?
  2. How to remove these archive log files and reclaim the space so it can be used for normal DB storage etc. As far as I know AWS-RDS does not give direct access to remove these files, which is why I tried to use the rdsadmin.rdsadmin_master_util.drop_archivelog_dir stored proc.
  3. Also how to make sure the log files do not continue to be generated or are cleaned up with an appropriate retention period.
amazon-rds amazon-web-services oracle
3 Answers

I don't have any experience with AWS, but it's still an oracle database, so you should be taking regular rman backups, including backing up the archive logs with the 'delete' option. Simplest form is 

rman> backups database plus archivelog delete all input.

In addition to creating a backup of the database and archivelogs, it will delete the archivelogs that were included with the backup.

user_57325370

If you can run PL/SQL not only SQL then you can:

BEGIN
  FOR cur IN (
  SELECT L.* FROM v$archived_log L, v$database D WHERE L.NEXT_TIME<trunc(sysdate)-30 and L.DELETED = 'NO' and L.STANDBY_DEST='NO' 
  and L.APPLIED=DECODE(D.DATABASE_ROLE,'PRIMARY','NO','YES')  order by L.NEXT_TIME ASC,L.NAME
  ) LOOP
    sys.DBMS_BACKUP_RESTORE.deletearchivedlog(cur.recid,cur.stamp,cur.name,
                                          cur.thread#,cur.sequence#,
                                          cur.resetlogs_change#,cur.first_change#,
                                          cur.block_size,1);
  END LOOP;
END;
/

modify NEXT_TIME in the WHERE as yours.

from AWS RDS online doc:

set serveroutput on
exec rdsadmin.rdsadmin_util.show_configuration; 

begin
    rdsadmin.rdsadmin_util.set_configuration(
        name  => 'archivelog retention hours',
        value => '240');
end;
/
commit;

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Log.html#Appendix.Oracle.CommonDBATasks.RetainRedoLogs
user_9635850

I just want to post some of the response I got from AWS support regarding my questions 1/2 above.

I will proceed to address your archive log concern.

select nvl(sum(BLOCKS * BLOCK_SIZE),0) bytes from V$ARCHIVED_LOG
where DEST_ID=1;
BYTES

3.3700E+10

The reason you are seeing a disparity in size of archive logs from running the above query is because this query returns all archive logs even if it has been deleted from disk already. Reason being, Oracle holds this information in the control file unless a crosscheck is initiated. Regretfully, the RDSrdsadmin.rdsadmin_rman_util.crosscheck_archivelog is not supported on RDS Oracle version 'oracle-se1:11.2.0.4.v1".

Also:

Coming back to your archive log query. I can gladly confirm that archivelogs are purged from disk every 5 minutes when your retention period is set to 0.

Therefore, the return of 31GB's is inaccurate due to the control file not being updated. Oracle updates the control file when the "crosscheck" statement is run. In order for you to manage the archivelogs via the crosscheck command, I would kindly suggest considering moving to a higher version of RDS Oracle. For your ease of access I have listed the supported versions below:

-11.2.0.4.v19 or higher 11.2 versions
-12.1.0.2.v15 or higher 12.1 versions
-12.2.0.1.ru-2019-01.rur-2019-01.r1 or higher 12.2 versions
-All 18.0.0.0 versions

So in my case it seems that the archive logs had actually been purged and were not contributing to the disk usage. This disparity in the reporting was because of my version of Oracle which was 11.2.0.4.v1.

user_18608670
Related