undo损坏的数据库非常规的恢复

报错日志如下:

 

 cat lion_smon_7022.trc | more

/u01/admin/lion/bdump/lion_smon_7022.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/oracle
System name:    Linux
Node name:      oracle10g
Release:        2.6.9-67.ELsmp
Version:        #1 SMP Wed Nov 7 13:58:04 EST 2007
Machine:        i686
Instance name: lion
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 7022, image: oracle@oracle10g (SMON)

*** SERVICE NAME:() 2011-05-08 17:52:03.443
*** SESSION ID:(161.1) 2011-05-08 17:52:03.443
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
*** 2011-05-08 17:52:03.464
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/oradata/lion/lion/undotbs01.dbf'
*** 2011-05-08 17:52:03.489
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/oradata/lion/lion/undotbs01.dbf'

 

 

很明显看到undo data file 的数据文件损坏,如何恢复呢?对于undo损害的情况,归档模式,可以用归档的方式恢复,非归档模式只能尝试一些非常规的手段恢复.

 

这个数据库处于非归档模式

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2024
Current log sequence           2026

 

非归档模式一般有两种方法进行恢复:

 

(1):使用system segment的方式恢复

 

(2):使用隐含参数 ._corrupted_rollback_segments屏蔽掉丢失的undo segments

 

我用第二种方式恢复

 

步骤如下:

 

 1: create pfile from spfile

 

 2: 根据前边的日志,我们看到从segment1到segmnet10到回滚段日志出现问题

在initlion.ora中添加如下隐含参数

*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$'

 

 3:用修改的pfile启动db

 

 4:创建新undo的表空间,并切换

 create undo tablespace undotbs2    datafile '/u01/oradata/undotbs2.dbf' size 1g;

 

 alter system set undo_tablespace=undotbs2 

 drop tablespace undotbs1

create spfile from pfile

 

 5:用spfile启动db

 

 

 

 

另外ora-600 4194也是常见的undo报错,也需要用上面的步骤进行恢复.

 

 

 

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8410760/viewspace-732342/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8410760/viewspace-732342/