修復受損的SYSAUX表空間
客戶的SYSAUX表空間和對應的資料檔案都完全離線,且歸檔已經不存在,沒辦法執行物理級別的恢復。使用TTS遷移也行不通,因為exp和expdp匯出後設資料會報錯;使用expdp、exp按照tablespace、schema匯出也行不通,同樣會報錯;最後的辦法是使用exp按照tables的傳統方式匯出是可以的,但這需要手動處理使用者的檢視、儲存過程、函式、序列、同義詞等使用者物件,稍微麻煩了一點,不過最終還是OK了。下面貼出一篇指導性的metalink文章,供大家參考:
Fixing a Corrupted SYSAUX Tablespace (文件 ID 950128.1)
修改時間:2013-6-5型別:HOWTO
In this Document
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review. |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and laterInformation in this document applies to any platform.
***Checked for relevance on 17-May-2013***
GOAL
How to fix/work-around a corrupted SYSAUX tablespace
SOLUTION
SYSAUX was introduced in 10g to store all auxiliary database metadata related to Oracle options and features. This is a mandatory tablespace and cannot be dropped. Therefore it is important to have all objects in this tablespace accessible at all times.
To find out what is stored in this tablespace, look at V$SYSAUX_OCCUPANTS:
Throughout this document, we are assuming that the datafile 3, belonging to the SYSAUX tablespace, is corrupted.
1- RMAN Block Recovery
RMAN block recovery is only possible if you have a backup and all archivelogs from prior to the time of the corruption. Further, block recovery is only available for Oracle Enterprise Edition.
a) check for corruption
RMAN> backup validate check logical tablespace SYSAUX;
or
b) Once the above RMAN validate is completed, all corruptions found will be written to this view:
c) If V$DATABASE_BLOCK_CORRUPTION returns more than one corrupted block, recover them
2 - Restore and Recover the Corrupted Datafile(s)
If you have a backup of the corrupted file(s), then restore and recover the datafile(s) from backup.
eg:
RMAN> recover datafile 3;
SQL> alter database datafile 3 online;
3 - Recreate the Corrupted Object
To identify the corrupted objects, please see . Most indexes can be recreated but only certain tables in the SYSAUX tablespace can be recreated. Please contact Oracle Support if you wish to explore this path.
4 - Export
If all of the above options are exhausted, the last resort is to export the database, schema(s) or table(s), create a new database and import.
As long as the export views are accessible you should be able to perform either a database or schema level export.
If a tablespace level export works you might also consider using Transportable Tablespaces for recreating the database as documented in
Note:733824.1 HowTo Recreate a database using TTS (TransportableTableSpace)
Note: If all datafiles belonging to the SYSAUX are inaccessible then you can only perform export at the table level. Full and schema level export will not work as the export views in SYSAUX are no longer available. You will need to use traditional exp rather than expdp as expdp relies on objects in the SYSAUX tablespace.
eg:
Once exported, you can import this dump into a new database.
REFERENCES
NOTE:184327.1 - ORA-1157 TroubleshootingNOTE:243246.1 - SYSAUX New Mandatory Tablespace in Oracle 10g and higher
NOTE:472231.1 - How to identify all the Corrupted Objects in the Database with RMAN
NOTE:733824.1 - How To Recreate A Database Using TTS (Transportable TableSpace)
--end--
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27661381/viewspace-1064130/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 記一次sysaux表空間壞塊修復UX
- SYSAUX表空間管理及恢復UX
- sysaux表空間檔案損壞的處理(zt)UX
- ORACLE的SYSAUX 表空間OracleUX
- 32、SYSAUX表空間UX
- Oracle清理SYSAUX表空間OracleUX
- 某個表空間的資料檔案損壞的修復思路
- 2.5.4.1 關於SYSAUX表空間UX
- 認識 SYSAUX 表空間(zt)UX
- UNDO 表空間檔案損壞的恢復
- SYSAUX表空間清理之SM/OPTSTATUX
- AWR佔用sysaux表空間太大UX
- oracle之 SYSAUX表空間維護OracleUX
- 4.2.1.7 規劃 SYSTEM 和 SYSAUX 表空間UX
- sysaux 表空間爆滿處理方法UX
- sysaux 表空間不足問題處理UX
- system表空間檔案損壞----完全恢復
- 10G 新特性系列: SYSAUX 表空間UX
- 從system/sysaux空間轉移TABLE&Index到其它表空間UXIndex
- INDEX表空間檔案丟失或者損壞的恢復Index
- 非系統表空間損壞,rman備份恢復
- oracle sysaux表空間滿了處理辦法OracleUX
- 計算sysaux中各主件對sysaux空間的使用!UX
- UNDO表空間損壞的處理
- TEMP表空間的檔案丟失或損壞後的恢復
- 10g ORACLE_HOME空間滿導致SYSAUX表空間離線OracleUX
- AWR不自動刪除導致SYSAUX表空間滿UX
- SYSAUX表空間使用率高問題處理UX
- 電腦硬碟分割槽表損壞怎麼修復?電腦硬碟分割槽表損壞的修復方法硬碟
- Oracle10g以上sysaux表空間的維護和清理OracleUX
- 2.5.4 為 SYSAUX 表空間指定資料檔案屬性UX
- SYSAUX 表空間歷史統計資料過大purgeUX
- Oracle SYSAUX表空間使用率超過警戒閥值OracleUX
- 【AWR】該怎樣清理SYSAUX表空間相關資料UX
- 恢復Oracle表空間的方法Oracle
- Oracle SYSAUX 表空間使用率100% 導致的DB 故障OracleUX
- win10怎麼修復sd卡_win10系統sd卡受損的修復步驟Win10SD卡
- win10系統檔案受損如何修復 win10系統檔案修復的方法Win10