如何删除 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 支持部门得到的关于我上面 1/2 的问题的回复.
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.
从 V$ARCHIVED_LOG 中选择 nvl(sum(BLOCKS * BLOCK_SIZE),0) 个字节
其中 DEST_ID=1;
字节
select nvl(sum(BLOCKS * BLOCK_SIZE),0) bytes from V$ARCHIVED_LOG
where DEST_ID=1;
BYTES
3.3700E+10
您看到存档日志大小差异的原因运行上述查询是因为此查询返回所有存档日志即使它已经从磁盘中删除.原因是，甲骨文除非进行交叉检查，否则将这些信息保存在控制文件中发起.遗憾的是，该不支持 RDSrdsadmin.rdsadmin_rman_util.crosscheck_archivelog在 RDS Oracle 版本oracle-se1:11.2.0.4.v1"上.
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".
还有:
回到您的存档日志查询.我可以很高兴地确认归档日志每 5 分钟从磁盘中清除一次周期设置为 0.
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.
因此，由于控制文件，31GB的返回是不准确的不更新.Oracle 更新控制文件时交叉检查"语句运行.为了让您管理通过 crosscheck 命令归档日志，我建议考虑迁移到更高版本的 RDS Oracle.为了您的方便访问我在下面列出了支持的版本:
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 或更高 11.2 版本
-12.1.0.2.v15 或更高版本 12.1 版本
-12.2.0.1.ru-2019-01.rur-2019-01.r1 或更高 12.2 版本
-所有 18.0.0.0 版本
-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
因此，在我的情况下，似乎存档日志实际上已被清除并且对磁盘使用没有影响.报告中的这种差异是因为我的 Oracle 版本是 11.2.0.4.v1.
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.
相关文章