RAC下丢失undo表空间的恢复

                            RAC下丢失undo表空间的恢复

测试环境：

系统：LINUX-64

数据库：10.2.0.1

二节点RAC：RACDB1，RACDB2    存储使用的ASM

 

（1）插入数据，不提交

RACDB1>insert into xuhm.test3 values (4,'aa');

 

有一个活动的事务。

RACDB1>select usn,xacts from v$rollstat;

 

       USN      XACTS

---------- ----------

         0          0

         1          0

         2          0

         3          0

         4          1

         5          0

         6          0

         7          0

         8          0

         9          0

        10          0

 

（2）关闭数据库，删除RACDB1的UNDO表空间

RACDB1>shutdown abort;

RACDB2>shutdown abort;

 

ASMCMD> rm UNDOTBS1.260.794232647

 

（3）开启数据库

RACDB1>startup

ORACLE instance started.

 

Total System Global Area  184549376 bytes

Fixed Size                  2019448 bytes

Variable Size             121638792 bytes

Database Buffers           58720256 bytes

Redo Buffers                2170880 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '+RAC_DISK/racdb/datafile/undotbs1.260.794232647'

 

RACDB2>startup

ORACLE instance started.

 

Total System Global Area  184549376 bytes

Fixed Size                  2019448 bytes

Variable Size             155193224 bytes

Database Buffers           25165824 bytes

Redo Buffers                2170880 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '+RAC_DISK/racdb/datafile/undotbs1.260.794232647'

 

RACDB2>shutdown immediate

 

（4）因为这个文件丢失，所以只好把这个文件offline处理

RACDB1>alter database datafile '+RAC_DISK/racdb/datafile/undotbs1.260.794232647' offline drop;

 

 

（5）打开数据库

RACDB1>alter database open;

无法打开数据库，查看alert日志报错如下

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '+RAC_DISK/racdb/datafile/undotbs1.260.794232647'

Error 604 happened during db open, shutting down database

USER: terminating instance due to error 604

Fri Sep 28 20:32:29 2012

Errors in file /u01/app/oracle/admin/RACDB/bdump/racdb1_lms0_9732.trc:

ORA-00604: error occurred at recursive SQL level

Fri Sep 28 20:32:29 2012

Errors in file /u01/app/oracle/admin/RACDB/bdump/racdb1_lmon_9728.trc:

 

需要修改如下参数：注意，这里一定要使用_corrupted_rollback_segments，不能使用_offline_rollback_segments，要不然还是无法打开数据库。

修改在pfile文件中。

RACDB1.undo_management='MANUAL'

RACDB1.undo_tablespace='UNDO2'

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

 

RACDB1>startup  pfile='/u01/pfile';

ORACLE instance started.

 

Total System Global Area  184549376 bytes

Fixed Size                  2019448 bytes

Variable Size             121638792 bytes

Database Buffers           58720256 bytes

Redo Buffers                2170880 bytes

Database mounted.

Database opened.

 

（6）删除回滚段

RACDB1>SELECT segment_name,status FROM DBA_ROLLBACK_SEGS WHERE STATUS<>'OFFLINE';

 

SEGMENT_NAME                   STATUS

------------------------------ ----------------

SYSTEM                         ONLINE

_SYSSMU1$                      NEEDS RECOVERY

_SYSSMU2$                      NEEDS RECOVERY

_SYSSMU3$                      NEEDS RECOVERY

_SYSSMU4$                      NEEDS RECOVERY

_SYSSMU5$                      NEEDS RECOVERY

_SYSSMU6$                      NEEDS RECOVERY

_SYSSMU7$                      NEEDS RECOVERY

_SYSSMU8$                      NEEDS RECOVERY

_SYSSMU9$                      NEEDS RECOVERY

_SYSSMU10$                     NEEDS RECOVERY

 

11 rows selected.

 

RACDB1>drop rollback segment "_SYSSMU1$";

 

Rollback segment dropped.

 

RACDB1>drop rollback segment "_SYSSMU2$";

 

Rollback segment dropped.

 

RACDB1>drop rollback segment "_SYSSMU3$";

 

Rollback segment dropped.

 

RACDB1>drop rollback segment "_SYSSMU4$";

 

Rollback segment dropped.

 

RACDB1>drop rollback segment "_SYSSMU5$";

 

Rollback segment dropped.

 

RACDB1>drop rollback segment "_SYSSMU6$";

 

Rollback segment dropped.

 

RACDB1>drop rollback segment "_SYSSMU7$";

 

Rollback segment dropped.

 

RACDB1>drop rollback segment "_SYSSMU8$";

 

Rollback segment dropped.

 

RACDB1>drop rollback segment "_SYSSMU9$";

 

Rollback segment dropped.

 

RACDB1>drop rollback segment "_SYSSMU10$";

 

Rollback segment dropped.

 

（7）删除旧的undo表空间，创建新undo表空间

RACDB1>drop tablespace undotbs1 including contents and datafiles;

 

Tablespace dropped.

 

RACDB1>create undo tablespace undo2 ;

 

Tablespace created.

 

（8）修改spfile参数

RACDB1>shutdown immediate

RACDB1>startup mount;

RACDB1>alter system set undo_management=auto scope=spfile sid='RACDB1';

RACDB1>alter system set undo_tablespace=UNDO2 scope=spfile sid='RACDB1';

RACDB1>shutdown immediate

RACDB1>startup

RACDB1>show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDO2

 

 

（9）查看最后恢复的结果

RACDB1>select * from xuhm.test3;

 

        ID NA

---------- --

         4 aa

         2 xu

         3 li

--4，aa未提交的书屋被当做提交处理了。

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

转载于:http://blog.itpub.net/26655292/viewspace-745419/