--alert 提示db_recovery_file_dest_size 空間不足，無法正常歸檔
Sat Apr 22 00:32:14 2017
Errors in file /u01/app/oracle/diag/rdbms/sdmddb/SDMDDB1/trace/SDMDDB1_m000_82504.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1471152128000 bytes is 97.18% used, and has 41450209280 remaining bytes available.
************************************************************************
You have following choices to free up space from 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> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 82272
Next log sequence to archive 82274
Current log sequence 82274
SQL>
SQL>
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +ARCH
db_recovery_file_dest_size big integer 1403000M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
可以看出歸檔使用的是+ARCH磁盤組，配額了大概1.4T
查看V$FLASH_RECOVERY_AREA_USAGE;，發現使用率都加一塊很低啊，空間剩余很多
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE 0 0
1
REDO LOG .33 0
9
ARCHIVED LOG .26 0
18
BACKUP PIECE 36.79 0
51
IMAGE COPY 0 0
0
FLASHBACK LOG .18 .11
5
FOREIGN ARCHIVED LOG 0 0
0
查看v$recovery_file_dest;發現SPACE_USED使用情況完全和V$FLASH_RECOVERY_AREA_USAGE;不一樣，總檔案數也不一樣
SQL> select substr(name,1,30),SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,SPACE_RECLAIMABLE/1024/1024/1024,NUMBER_OF_FILES
2 from v$recovery_file_dest;
SUBSTR(NAME,1,30)
------------------------------------------------------------
SPACE_LIMIT/1024/1024/1024 SPACE_USED/1024/1024/1024
-------------------------- -------------------------
SPACE_RECLAIMABLE/1024/1024/1024 NUMBER_OF_FILES
-------------------------------- ---------------
+ARCH
1370.11719 1137.01855
1.50292969 3210
查看asm磁盤組使用情況
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 1433597 863617 0 863617 0 N ARCH/
MOUNTED NORMAL N 512 4096 1048576 3069 2017 1023 497 0 Y CRS/
MOUNTED EXTERN N 512 4096 1048576 1126393 209072 0 209072 0 N DATA/
ASMCMD> cd arch
ASMCMD>
SDMDDB/
ASMCMD> du
Used_MB Mirror_used_MB
569803 569803
uj5u.com熱心網友回復：
upupupupupup轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/97866.html
標籤：高級技術
上一篇：關于查詢陳述句排序的問題
下一篇：求助SQL問題，著急，謝謝大拿。