如何删除 AWS RDS Oracle 实例上的存档日志文件-数据库问题

How to delete archive log files on AWS RDS Oracle instance(如何删除 AWS RDS Oracle 实例上的存档日志文件)

本文介绍了如何删除 AWS RDS Oracle 实例上的存档日志文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我们有一个 AWS Oracle RDS 实例，最近用 ORA-01653 和 ORA-01654 耗尽了空间(无法在表空间 X 中将索引 X 扩展 8...) 错误.

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';

这显示了输出:

        GB
----------
26.3267608

这让我相信我们有 26 GB 的存档日志.

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';

我们有近 5000 行结果.一些示例行:

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

这个存档文件的位置是:/rdsdbdata/db/DRGNFLY_A/arch/redolog-56849-1-1002024260.arc其他行也在此目录中:/rdsdbdata/db/DRGNFLY_A/arch

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

来自 AWS Oracle 常见 DBA 任务文件，我们已经运行了存储过程:

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;

此命令成功执行，但再次运行上述查询时，仍显示 26 GB 的存档日志文件.

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;

这会产生输出:

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.

这会让我相信，由于归档日志保留时间的值为 0，这些文件应该被自动删除吗?

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

我的问题:

我的上述分析/查询是否有误?这些存档日志实际上并未导致我的表空间错误?
如何删除这些存档日志文件并回收空间，以便将其用于普通数据库存储等.据我所知，AWS-RDS 不提供删除这些文件的直接访问权限，这就是我尝试使用的原因rdsadmin.rdsadmin_master_util.drop_archivelog_dir 存储过程.
还有如何确保日志文件不会在适当的保留期内继续生成或清理.

如何删除 AWS RDS Oracle 实例上的存档日志文件

问题描述

推荐答案

基础教程推荐