客戶的SYSAUX表空間和對應(yīng)的數(shù)據(jù)文件都完全脫機，且歸檔已經(jīng)不存在，沒辦法執(zhí)行物理級別的恢復(fù)。使用TTS遷移也行不通，因為exp和expdp導(dǎo)出元數(shù)據(jù)會報錯；使用expdp、exp按照tablespace、schema導(dǎo)出也行不通，同樣會報錯；最后的辦法是使用exp按照tables的傳統(tǒng)方式導(dǎo)出是可以的，但這需要手動處理用戶的視圖、存儲過程、函數(shù)、序列、同義詞等用戶對象，稍微麻煩了一點，不過最終還是OK了。下面貼出一篇指導(dǎo)性的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 later

Information 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:

SQL> select occupant_name, space_usage_kbytes from 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

RMAN> backup validate check logical datafile 3;

b) Once the above RMAN validate is completed, all corruptions found will be written to this view:

SQL> select * from v$database_block_corruption;

c) If? V$DATABASE_BLOCK_CORRUPTION returns more than one corrupted block, recover them

RMAN> blockrecover corruption list;

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> restore datafile 3;

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:

% exp scott/tiger file=xscott.dmp log=xscott.log tables=emp,dept

Once exported, you can import this dump into a new database.

REFERENCES

NOTE:184327.1- ORA-1157 Troubleshooting

NOTE:243246.1- SYSAUX New Mandatory Tablespace in Oracle 10g and higher

NOTE:472 oracle修復(fù)231.1- How to identify all the Corrupted Objectsoracle修復(fù) in the Database with RMAN

NOTE:733824.1- How To oracle修復(fù) Recreate A Database Using TTS (Transportable TableSpace)

--end--