客户的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
Goal |
Solution |
1- RMAN Block Recovery |
2 - Restore and Recover the Corrupted Datafile(s) |
3 - Recreate the Corrupted Object |
4 - Export |
References |
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 Note 472231.1. 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/23135684/viewspace-1063926/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23135684/viewspace-1063926/