問題描述
我們有一個 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.
這篇關于如何刪除 AWS RDS Oracle 實例上的存檔日志文件的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！