RAC下丢失undo表空间的恢复

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了RAC下丢失undo表空间的恢复，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5183字，纯文字阅读大概需要8分钟。

内容图文

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

内容总结

以上是互联网集市为您收集整理的RAC下丢失undo表空间的恢复全部内容，希望文章能够帮你解决RAC下丢失undo表空间的恢复所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端