mysql undo丢失_RAC下丢失undo表空间的恢复

测试环境：系统：LINUX-64数据库：10.2.0.1二节点RAC：RACDB1，RACDB2 存储使用的ASM

测试环境：

系统：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未提交的书屋被当做提交处理了。

本文原创发布php中文网，转载请注明出处，感谢您的尊重！