閃回區大小出現警告解決
ORA-19815: 警告: db_recovery_file_dest_size 位元組 (共 2147483648 位元組) 已使用 99.88%, 尚有 2554880 位元組可用。
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
檢視閃回區使用情況
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 99.88 0 13
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
已選擇7行。
解決辦法:
1)增大閃回區大小
SQL> alter system set db_recovery_file_dest_size=3g;
系統已更改。
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 66.59 0 13
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
已選擇7行。
2)刪除歷史歸檔日誌
RMAN> crosscheck archivelog all;
RMAN> delete expired archielog all;
補充說明
1.使用os命令刪除fast_recovery_area內容後,需要使用crosscheck檢測(如:archivelog all,backup等)。
2.然後使用 DELETE EXPIRED命令刪除(archivelog all,backup等)
3.fast_recovery_area設定合適大小+合適的策略
4.在閃回恢復區中的空間使用超過 85% 的時候,資料庫將會向 alert 檔案中寫入告警資訊。而當超過97%的時候將會寫入嚴重告警資訊。
當閃回恢復區空間不夠的時候,Oracle將報告如下類似的錯誤:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 1258291200 limit
5.可以檢視dba_outstanding_alerts告警日誌
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
檢視閃回區使用情況
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 99.88 0 13
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
已選擇7行。
解決辦法:
1)增大閃回區大小
SQL> alter system set db_recovery_file_dest_size=3g;
系統已更改。
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 66.59 0 13
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
已選擇7行。
2)刪除歷史歸檔日誌
RMAN> crosscheck archivelog all;
RMAN> delete expired archielog all;
補充說明
1.使用os命令刪除fast_recovery_area內容後,需要使用crosscheck檢測(如:archivelog all,backup等)。
2.然後使用 DELETE EXPIRED命令刪除(archivelog all,backup等)
3.fast_recovery_area設定合適大小+合適的策略
4.在閃回恢復區中的空間使用超過 85% 的時候,資料庫將會向 alert 檔案中寫入告警資訊。而當超過97%的時候將會寫入嚴重告警資訊。
當閃回恢復區空間不夠的時候,Oracle將報告如下類似的錯誤:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 1258291200 limit
5.可以檢視dba_outstanding_alerts告警日誌
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26937943/viewspace-1133467/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 表閃回操作及解決閃回表時出現的ORA-08189故障
- 啟用Flashback Database閃回資料庫功能(閃回區滿解決辦法 )Database資料庫
- Oracle10g閃回恢復區詳解--開啟,設定閃回區Oracle
- 清空 oracle 閃回區Oracle
- Oracle -- 閃回恢復區---實踐1---閃回庫Oracle
- Oracle閃回恢復區Oracle
- (f)--閃回恢復區---實踐2---閃回表(閃回DML部分資料會用到閃回查詢)
- Oracle10g閃回恢復區詳解Oracle
- Oracle閃回詳解Oracle
- Oracle 閃回區(Oracle Flash recovery area)Oracle
- 閃回區滿的處理 - flashback
- 閃回表、閃回查詢
- 閃回(關於閃回查詢)
- 閃回刪除、閃回查詢
- 閃回技術二:閃回表
- Win10系統開機出現警告事件ID 360怎麼解決Win10事件
- 閃回技術全瞭解
- Oracle閃回查詢,閃回版本查詢與閃回事務查詢的使用區別總結Oracle
- Java swing JFrame用repaint出現閃爍的問題解決JavaAI
- 【閃回特性之閃回查詢】使用閃回查詢(select as of)
- 高德地圖警告解決地圖
- 閃回(關於閃回資料庫)資料庫
- 基本閃回查詢和閃回表
- 閃回技術一:閃回查詢
- Win10系統全屏玩遊戲出現閃屏現象怎麼解決Win10遊戲
- 一次快速閃回區滿導致資料庫不能啟動的解決過程資料庫
- 詳解oracle資料庫閃回Oracle資料庫
- Oracle資料庫閃回區空間不足Oracle資料庫
- Oracle資料庫的閃回恢復區Oracle資料庫
- 清除閃回資料歸檔區資料
- (f)--閃回恢復區---實踐3---閃回查詢(基於AUM (auto undo managemet))
- DM7閃回與閃回查詢
- 閃回查詢之閃回版本查詢
- 閃回查詢之閃回表查詢
- Mac新手教程:Mac電腦如何設定出現警告聲音時閃爍螢幕?Mac
- oracle閃回Oracle
- oracle 閃回Oracle
- 解決pycharm警告InsecureRequestWarning:PyCharm