手工清除歸檔處理歸檔空間滿
文章版權所有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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- rac使用預設閃回區歸檔空間滿
- ORACLE DATAGUARD災備歸檔空間滿導致的ORA-00600 [2619]Oracle
- 手工rm刪除歸檔日誌對備份歸檔日誌的影響
- Oracle GoldenGate同步服務歸檔空間維護OracleGo
- OGG整合抽取模式丟失歸檔處理模式
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- MySQL 磁碟空間滿導致表空間相關資料檔案損壞故障處理MySql
- 清除Oracle控制檔案中的歸檔資訊v$archived_logOracleHive
- oracle歸檔Oracle
- iOS 複雜物件的歸檔與反歸檔iOS物件
- ubunt下boot檔案空間滿boot
- 如何輕鬆歸檔檔案？2種方法輕鬆建立歸檔檔案！
- sysaux 表空間爆滿處理方法UX
- oracle adg備庫歸檔滿了無法同步Oracle
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- 刪除歸檔
- SharePlex跳過歸檔或從指定歸檔位置開始
- 怎麼將大量的電腦檔案進行歸類處理？
- PostgreSQL 歸檔日誌SQL
- 歸檔日誌挖掘
- oracle歸檔日誌Oracle
- oracle 11g開啟歸檔模式及修改歸檔目錄Oracle模式
- 使用RMAN增量備份處理Dataguard因歸檔丟失造成的gap
- oracle sysaux表空間滿了處理辦法OracleUX
- SQL資料庫怎麼進行資料歸檔和歸檔管理？SQL資料庫
- 咦？Oracle歸檔檔案存哪了？Oracle
- 11.使用make更新歸檔檔案
- Oracle歸檔日誌清理Oracle
- Oracle：歸檔量統計Oracle
- 歸檔oracle alert日誌Oracle
- SQLServer進行表歸檔SQLServer
- ADG歸檔不傳輸
- 11 – 分類與歸檔
- 14. 日誌歸檔
- RMAN刪除歸檔日誌出現RMAN-0813錯誤的處理
- LightBD/PostgreSQL資料庫設定歸檔保留時間SQL資料庫
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫
- ORACLE NBU調取oracle rman指令碼備份歸檔不自動刪除歸檔Oracle指令碼
- 關於丟失表空間資料檔案的處理方式