手工清除歸檔處理歸檔空間滿
文章版權所有Jusin Hao（luckyfriends），支援原創，轉載請註明。
1.1. 資料庫歸檔空間滿的維護操作
伺服器資訊
[root@ora-83 ~]# ifconfig -a
eth0 Link encap:Ethernet HWaddr 84:2B:2B:73:8D:21
inet addr:10.168.4.83 Bcast:10.168.4.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:1877375280 errors:1 dropped:0 overruns:0 frame:1
TX packets:2350154974 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:296632242621 (276.2 GiB) TX bytes:559544325176 (521.1 GiB)
Interrupt:82 Memory:da000000-da012800
eth1 Link encap:Ethernet HWaddr 84:2B:2B:73:8D:22
inet addr:10.168.4.80 Bcast:10.168.4.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:1166599051 errors:576 dropped:0 overruns:0 frame:576
TX packets:504077 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:251487510476 (234.2 GiB) TX bytes:32268178 (30.7 MiB)
Interrupt:90 Memory:dc000000-dc012800
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:368818074 errors:0 dropped:0 overruns:0 frame:0
TX packets:368818074 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:53158698831 (49.5 GiB) TX bytes:53158698831 (49.5 GiB)
[root@ora-83 ~]#
SQL> SELECT * FROM V$DATABASE;
DATABASE_NAME
--------------------------------------------------------------------------------
LSDB
檢查歸檔路徑位置
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 26154
Next log sequence to archive 26154
Current log sequence 26156
檢查閃回區的大小
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u1/app/oracle/flash_recovery_
area
db_recovery_file_dest_size big integer 100G
recovery_parallelism integer 0
SQL>
SQL> show parameter arch
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
remote_archive_enable string true
standby_archive_dest string ?/dbs/arch
SQL>
SQL> set linesize 1500
SQL> set pagesize 5000
SQL> col member format a50
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 26155 52428800 1 YES INACTIVE 5.9722E+12 21-OCT-13
2 1 26156 52428800 1 YES ACTIVE 5.9722E+12 21-OCT-13
3 1 26157 52428800 1 NO CURRENT 5.9722E+12 21-OCT-13
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u1/app/oracle/oradata/lsdb/redo03.log NO
2 ONLINE /u1/app/oracle/oradata/lsdb/redo02.log NO
1 ONLINE /u1/app/oracle/oradata/lsdb/redo01.log NO
連線資料庫時報歸檔空間滿
SQL> conn system/oracle@lsdb
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
檢視閃回區的空間使用狀態發現已經達到99.9%
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 99.97 0 2463
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
但是到OS作業系統的檔案系統目錄下檢視實際檔案佔用為48G，這說明之前物理刪除過歸檔檔案，但是沒有透過rman將這些已註冊擔過期的歸檔日誌檔案刪除。
[oracle@ora-83 archivelog]$ du -sm *
[oracle@ora-83 archivelog]$ du -sm
48691 .
[oracle@ora-83 archivelog]$ ls
2013_09_01 2013_09_06 2013_09_11 2013_09_16 2013_09_21 2013_09_26 2013_10_01
…………
………….
登入Rman先執行歸檔日誌檔案的校驗檢查
[oracle@ora-83 archivelog]$
[oracle@ora-83 archivelog]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Oct 21 10:16:04 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: LSDB (DBID=834252102)
RMAN> crosscheck archivelog all;
……………
archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26149_9681yyqw_.arc recid=26148 stamp=829356127
validation succeeded for archived log
archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26150_9682cxdo_.arc recid=26149 stamp=829356541
validation succeeded for archived log
archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26151_9682sb64_.arc recid=26150 stamp=829356970
validation succeeded for archived log
archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26152_968338ov_.arc recid=26151 stamp=829357289
validation succeeded for archived log
archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_10_21/o1_mf_1_26153_9683c4pn_.arc recid=26152 stamp=829357541
Crosschecked 2463 objects
確認刪除過期的歸檔日誌檔案
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=215 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
23690 1 23691 X 08-AUG-13 /u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_08/o1_mf_1_23691_9065fo53_.arc
24977 1 24978 X 31-AUG-13 /u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24978_9221zz6c_.arc
24978 1 24979 X 31-AUG-13 /u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24979_9232bnxf_.arc
24979 1 24980 X 31-AUG-13 /u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24980_9240jkko_.arc
Do you really want to delete the above objects (enter YES or NO)?
………………………
archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24979_9232bnxf_.arc recid=24978 stamp=824911957
deleted archive log
archive log filename=/u1/app/oracle/flash_recovery_area/LSDB/archivelog/2013_08_31/o1_mf_1_24980_9240jkko_.arc recid=24979 stamp=824942865
Deleted 1290 EXPIRED objects
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 47.49 0 1173
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SELECT substr(name, 1, 30) name,
space_limit AS quota,
space_used AS used,
space_reclaimable AS reclaimable,
number_of_files AS files
FROM v$recovery_file_dest;
NAME QUOTA USED RECLAIMABLE FILES
------------------------------------------------------------ ---------- ------
/u02/flash_recovery_area 1073741824 3576443392 0 82
修改FLASH_RECOVERY_AREA的空間修改為指定大小
SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=6g;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-1083435/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [zt] 手工處理Standby 歸檔間隔(gap)的問題
- oracle資料庫歸檔日誌空間滿引起的錯誤處理Oracle資料庫
- 處理歸檔滿了fast_recovery_area無剩餘空間的案例AST
- RAC資料庫大量載入資料造成歸檔日誌空間滿處理資料庫
- rac使用預設閃回區歸檔空間滿
- ORA-00257歸檔日誌空間已滿
- oracle 10g rac+asm 歸檔路徑磁碟組空間滿問題處理Oracle 10gASM
- 單例項歸檔空間佔滿故障模擬實驗單例
- 檢視歸檔日誌空間
- 週末又一次歸檔空間不足問題處理
- ORACLE 歸檔空間滿導致的enq: TX - row lock contentionOracleENQ
- Oracle RMAN 清除歸檔日誌Oracle
- Oracle RMAN清除歸檔日誌Oracle
- 資料庫夯住！ORA-19815！歸檔空間滿資料庫
- oracle歸檔切換以及歸檔日誌滿報錯問題Oracle
- 歸檔日誌滿導致的資料庫掛起故障處理資料庫
- oracle 歸檔/非歸檔Oracle
- standby缺失primary歸檔，手工同步恢復
- 歸檔目錄空間不足造成的問題
- 歸檔空間不足導致例項死鎖
- Oracle 歸檔和非歸檔模式之間的切換Oracle模式
- Oracle資料庫的歸檔日誌寫滿磁碟空間解決辦法Oracle資料庫
- 手工rm刪除歸檔日誌對備份歸檔日誌的影響
- oracle 10g 歸檔日誌清除Oracle 10g
- 當ORACLE歸檔日誌滿後如何正確刪除歸檔日誌Oracle
- ORACLE DATAGUARD災備歸檔空間滿導致的ORA-00600 [2619]Oracle
- 一次oracle資料庫down事件（歸檔量較大，造成ASM空間滿）Oracle資料庫事件ASM
- Oracle GoldenGate同步服務歸檔空間維護OracleGo
- 計算歸檔日誌所需要的磁碟空間
- DATAGUARD手工註冊歸檔日誌（轉載）
- DG歸檔日誌斷檔時間過長如何處理（DG全庫恢復）
- 歸檔日誌滿導致的資料庫掛起故障處理【轉載】資料庫
- OGG整合抽取模式丟失歸檔處理模式
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- Oracle_dg歸檔丟失問題處理Oracle
- Linux/Unix shell 指令碼清除歸檔日誌檔案Linux指令碼
- rman清除歸檔日誌經典資料
- 清除閃回資料歸檔區資料