转载至http://blog.csdn.net/oradh/article/details/25118719
这次主题继续介绍undo相关的内容,主要讲述undo数据文件丢失的处理示例(1),这次示例的处理方法简单,下一篇介绍一个更加复杂的处理示例。
模拟环境
os: linux x86-64
db: oracle 11gr2
mode: archivelog
模拟过程
1、用户发起事务(insert),未提交
2、shutdown abort 数据库
3、模拟undo数据文件丢失
4、数据库mount状态下offline drop掉undo数据文件
5、alter database open 数据库
6、完成后续处理
预备知识
"_offline_rollback_segments"/
"_corrupted_rollback_segments"两个参数的解释:
if the rollback segments corrupt,we should recover it from bacup.sometimes there's no backup or it's not necessary to recover,we maybe consider using undocument parameter to avoid access these rollback segments,Oracle provides "_offline_rollback_segments" and "_corrupt_rollback_segments",before use them,we should be clear the difference between them.when opening a database,any rollback segments listed in "
_offline_rollback_segments
" or "_corrupted_rollback_segments" parameter:
- are not scanned,and any active transactions are neither marked as dead nor rolled back;
- appear offline in undo$
- can't be acquered by instance for new transaction;
- note that although the rollback segments listed in "_offline_rollback_segments",oracle actually reads the segemnts to find transaction status and gather undo records to perform rollback,if a corrupt block is found,the oracle will still fail.if an open ITL is found to associated with "_corrupted_rollback_segments",the segment is not read to find the transaction status
- it is as though the rollback segment had been dropped,the transaction is assumed to be commited and delayed block cleanout is performed;
- so the transaction was not commited,logical corruption will occur
以下描述转载于askmaclean博客:
_OFFLINE_ROLLBACK_SEGMENTS(offline undo segment list)隐藏参数(hidden parameter)的独有作用:
- 在实例startup启动并open database的阶段仍将读取_OFFLINE_ROLLBACK_SEGMENTS所列出的Undo segments(撤销段/回滚段),若访问这些undo segments出现了问题则将在alert.log和其他TRACE中体现出来,但不影响实际的startup进程
- 若查询数据块发现活跃的事务,并ITL指向对应的undo segments则:
- 若读取undo segments的transaction table事务表发现事务已提交则做数据块的清除
- 若读取发现事务仍活动未commit,则生成一个CR块拷贝
- 若读取该undo segments存在问题(可能是corrupted讹误,可能是missed丢失)则产生一个错误并写出到alert.log,查询将异常终止
- 若DML更新相关的数据块会导致服务进程为了恢复活跃事务而进入死循环消耗大量CPU,解决方法是通过可以进行的查询工作重建相关表
_CORRUPTED_ROLLBACK_SEGMENTS(corrupted undo segment list)隐藏参数所独有的功能:
- 在实例启动startup并open database的阶段_CORRUPTED_ROLLBACK_SEGMENTS所列出的undo segments(撤销段/回滚段)将不会被访问读取
- 所有指向这些被_CORRUPTED_ROLLBACK_SEGMENTS列出的undo segments的事务都被认为已经提交了commit,和这个undo segments已经被drop时类似
- 这将导致严重的逻辑讹误(数据不一致)
- 如果数据字典上有活跃事务那么将更糟糕,数据字典逻辑讹误会造成数据库管理问题
- 如果bootstrap自举核心对象有活跃事务,那么将无法忽略错误ORA-00704: bootstrap process failure错误,导致无法强制打开数据库
- 衷心地建议用_CORRUPTED_ROLLBACK_SEGMENTS这个参数打开数据库后导出数据并重建数据库,这个参数使用的后遗症可能很顽固
- Oracle公司内部有叫做TXChecker的工具可以检查问题事务
示例
(1)模拟undo数据文件丢失
[oracle@netdbhost dbs]$ sqlplus dbmon
/dbmon_123
SQL *Plus: Release 11. 2. 0. 1. 0 Production on Mon May 5 10: 23: 55 2014
Copyright ( c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11. 2. 0. 1. 0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL > create table test as select * from dba_objects;
Table created.
SQL > select count( *) from test;
COUNT( *)
- - - - - - - - - -
71896
SQL > insert into test select * from test;
71896 rows created.
SQL > alter system flush buffer_cache;
System altered.
SQL > quit
SQL > shutdown abort;
ORACLE instance shut down.
SQL > host mv /u01 /test /test /undotbs1.dbf /u01 /test /test /undotbs1.dbfbak
SQL *Plus: Release 11. 2. 0. 1. 0 Production on Mon May 5 10: 23: 55 2014
Copyright ( c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11. 2. 0. 1. 0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL > create table test as select * from dba_objects;
Table created.
SQL > select count( *) from test;
COUNT( *)
- - - - - - - - - -
71896
SQL > insert into test select * from test;
71896 rows created.
SQL > alter system flush buffer_cache;
System altered.
SQL > quit
SQL > shutdown abort;
ORACLE instance shut down.
SQL > host mv /u01 /test /test /undotbs1.dbf /u01 /test /test /undotbs1.dbfbak
(2)启动数据库报错,提示undo文件不存在
SQL
> startup
ORACLE instance started.
Total System Global Area 1369579520 bytes
Fixed Size 2213216 bytes
Variable Size 905972384 bytes
Database Buffers 452984832 bytes
Redo Buffers 8409088 bytes
Database mounted.
ORA - 01157: cannot identify / lock data file 3 - see DBWR trace file
ORA - 01110: data file 3: '/u01/test/test/undotbs01.dbf'
ORACLE instance started.
Total System Global Area 1369579520 bytes
Fixed Size 2213216 bytes
Variable Size 905972384 bytes
Database Buffers 452984832 bytes
Redo Buffers 8409088 bytes
Database mounted.
ORA - 01157: cannot identify / lock data file 3 - see DBWR trace file
ORA - 01110: data file 3: '/u01/test/test/undotbs01.dbf'
(3)查询undo文件的文件名,并offline drop 掉(这种方式处理后,数据库可以open成功!!)
SQL
>
select
name
from v$datafile
where file#
=
3;
NAME
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
/u01 /test /test /undotbs01.dbf
SQL > alter database datafile '/u01/test/test/undotbs01.dbf' offline drop; ----直接offline drop
Database altered.
NAME
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
/u01 /test /test /undotbs01.dbf
SQL > alter database datafile '/u01/test/test/undotbs01.dbf' offline drop; ----直接offline drop
Database altered.
(4)open数据库,这时数据库可以open成功!查看数据库内undo段的状态
SQL
>
alter
database
open;
---这种情况下,数据库可以open成功!
Database altered.
SQL > select status, count( *) from dba_rollback_segs group by status;
STATUS COUNT( *)
- - - - - - - - - - - - - - - - - - - - - - - - - -
NEEDS RECOVERY 10
ONLINE 1
SQL > select usn,status from v$rollstat;
USN STATUS
- - - - - - - - - - - - - - - - - - - - - - - - -
0 ONLINE
SQL > col segment_name format a30
SQL > set linesize 200
SQL > select segment_name,status from dba_rollback_segs where status = 'NEEDS RECOVERY';
SEGMENT_NAME STATUS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
_SYSSMU1_3780397527$ NEEDS RECOVERY
_SYSSMU2_2232571081$ NEEDS RECOVERY
_SYSSMU3_2097677531$ NEEDS RECOVERY
_SYSSMU4_1152005954$ NEEDS RECOVERY
_SYSSMU5_1527469038$ NEEDS RECOVERY
_SYSSMU6_2443381498$ NEEDS RECOVERY
_SYSSMU7_3286610060$ NEEDS RECOVERY
_SYSSMU8_2012382730$ NEEDS RECOVERY
_SYSSMU9_1424341975$ NEEDS RECOVERY
_SYSSMU10_3550978943$ NEEDS RECOVERY
10 rows selected.
Database altered.
SQL > select status, count( *) from dba_rollback_segs group by status;
STATUS COUNT( *)
- - - - - - - - - - - - - - - - - - - - - - - - - -
NEEDS RECOVERY 10
ONLINE 1
SQL > select usn,status from v$rollstat;
USN STATUS
- - - - - - - - - - - - - - - - - - - - - - - - -
0 ONLINE
SQL > col segment_name format a30
SQL > set linesize 200
SQL > select segment_name,status from dba_rollback_segs where status = 'NEEDS RECOVERY';
SEGMENT_NAME STATUS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
_SYSSMU1_3780397527$ NEEDS RECOVERY
_SYSSMU2_2232571081$ NEEDS RECOVERY
_SYSSMU3_2097677531$ NEEDS RECOVERY
_SYSSMU4_1152005954$ NEEDS RECOVERY
_SYSSMU5_1527469038$ NEEDS RECOVERY
_SYSSMU6_2443381498$ NEEDS RECOVERY
_SYSSMU7_3286610060$ NEEDS RECOVERY
_SYSSMU8_2012382730$ NEEDS RECOVERY
_SYSSMU9_1424341975$ NEEDS RECOVERY
_SYSSMU10_3550978943$ NEEDS RECOVERY
10 rows selected.
(5)重建新的undo表空间(这一步也可以延后执行,影响不大)
SQL
>
create undo tablespace undo2 datafile
'/u01/test/test/undo2.dbf'
size
50m;
Tablespace created.
SQL > select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDO2
6 rows selected.
SQL > show parameter undo
NAME TYPE VALUE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS1
SQL > alter system set undo_tablespace = 'undo2';
System altered.
SQL > alter system set undo_management =manual scope =spfile; - - -调整为手工管理,执行后续的扫尾工作( drop rollbacksegment 的执行)
System altered.
Tablespace created.
SQL > select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDO2
6 rows selected.
SQL > show parameter undo
NAME TYPE VALUE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS1
SQL > alter system set undo_tablespace = 'undo2';
System altered.
SQL > alter system set undo_management =manual scope =spfile; - - -调整为手工管理,执行后续的扫尾工作( drop rollbacksegment 的执行)
System altered.
(6)重新启动数据库,通过pfile来启动,同时将丢失undo文件中的undo段都通过隐含参数(_offline_rollback_segments)屏蔽掉,告知oracle这写回滚段已经丢次,如果数据库内有数据块需要用到这些回滚段,则默认数据块内的数据已经提交
SQL
>
create pfile
=
'/home/oracle/dhtest.ora'
from spfile;
File created.
SQL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
*.undo_management =manual
*._offline_rollback_segments =( '_SYSSMU1_3780397527$', '_SYSSMU2_2232571081$', '_SYSSMU3_2097677531$', '_SYSSMU4_1152005954$', '_SYSSMU5_1527469038$', '_SYSSMU6_2443381498$', '_SYSSMU7_3286610060$', '_SYSSMU8_2012382730$', '_SYSSMU9_1424341975$', '_SYSSMU10_3550978943$')
SQL > startup pfile = /home /oracle /dhtest.ora
ORACLE instance started.
Total System Global Area 1369579520 bytes
Fixed Size 2213216 bytes
Variable Size 905972384 bytes
Database Buffers 452984832 bytes
Redo Buffers 8409088 bytes
Database mounted.
Database opened.
SQL > select count( *) from dh.test; - - -检查数据发现,为提交的数据默认为提交了。
COUNT( *)
- - - - - - - - - -
143792
SQL > select status, count( *) from dba_rollback_segs group by status;
STATUS COUNT( *)
- - - - - - - - - - - - - - - - - - - - - - - - - -
NEEDS RECOVERY 10
OFFLINE 10
ONLINE 1
File created.
SQL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
*.undo_management =manual
*._offline_rollback_segments =( '_SYSSMU1_3780397527$', '_SYSSMU2_2232571081$', '_SYSSMU3_2097677531$', '_SYSSMU4_1152005954$', '_SYSSMU5_1527469038$', '_SYSSMU6_2443381498$', '_SYSSMU7_3286610060$', '_SYSSMU8_2012382730$', '_SYSSMU9_1424341975$', '_SYSSMU10_3550978943$')
SQL > startup pfile = /home /oracle /dhtest.ora
ORACLE instance started.
Total System Global Area 1369579520 bytes
Fixed Size 2213216 bytes
Variable Size 905972384 bytes
Database Buffers 452984832 bytes
Redo Buffers 8409088 bytes
Database mounted.
Database opened.
SQL > select count( *) from dh.test; - - -检查数据发现,为提交的数据默认为提交了。
COUNT( *)
- - - - - - - - - -
143792
SQL > select status, count( *) from dba_rollback_segs group by status;
STATUS COUNT( *)
- - - - - - - - - - - - - - - - - - - - - - - - - -
NEEDS RECOVERY 10
OFFLINE 10
ONLINE 1
(7)手工删除丢失undo数据文件中的undo段
SQL
>
drop
rollback segment
"_SYSSMU1_3780397527$";
drop rollback segment "_SYSSMU2_2232571081$";
drop rollback segment "_SYSSMU3_2097677531$";
drop rollback segment "_SYSSMU4_1152005954$";
drop rollback segment "_SYSSMU5_1527469038$";
drop rollback segment "_SYSSMU6_2443381498$";
drop rollback segment "_SYSSMU7_3286610060$";
drop rollback segment "_SYSSMU8_2012382730$";
drop rollback segment "_SYSSMU9_1424341975$";
drop rollback segment "_SYSSMU10_3550978943$";
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
SQL > select status, count( *) from dba_rollback_segs group by status;
STATUS COUNT( *)
- - - - - - - - - - - - - - - - - - - - - - - - - -
OFFLINE 10
ONLINE 1
drop rollback segment "_SYSSMU2_2232571081$";
drop rollback segment "_SYSSMU3_2097677531$";
drop rollback segment "_SYSSMU4_1152005954$";
drop rollback segment "_SYSSMU5_1527469038$";
drop rollback segment "_SYSSMU6_2443381498$";
drop rollback segment "_SYSSMU7_3286610060$";
drop rollback segment "_SYSSMU8_2012382730$";
drop rollback segment "_SYSSMU9_1424341975$";
drop rollback segment "_SYSSMU10_3550978943$";
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
Rollback segment dropped.
SQL >
SQL > select status, count( *) from dba_rollback_segs group by status;
STATUS COUNT( *)
- - - - - - - - - - - - - - - - - - - - - - - - - -
OFFLINE 10
ONLINE 1
(8)删除原先的undo表空间,重新启动,将undo_management改回自动,同时去掉隐含参数,至此数据库恢复完毕
SQL
>
drop tablespace undotbs1 including contents
and datafiles;
Tablespace dropped.
SQL >
SQL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL > startup
ORACLE instance started.
Total System Global Area 1369579520 bytes
Fixed Size 2213216 bytes
Variable Size 905972384 bytes
Database Buffers 452984832 bytes
Redo Buffers 8409088 bytes
Database mounted.
Database opened.
SQL >
SQL > show parameter undo
NAME TYPE VALUE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
_undo_autotune boolean FALSE
undo_management string MANUAL
undo_retention integer 1800
undo_tablespace string undo2
SQL > alter system set undo_management =auto scope =spfile;
System altered.
SQL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL > startup
ORACLE instance started.
Total System Global Area 1369579520 bytes
Fixed Size 2213216 bytes
Variable Size 905972384 bytes
Database Buffers 452984832 bytes
Redo Buffers 8409088 bytes
Database mounted.
Database opened.
Tablespace dropped.
SQL >
SQL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL > startup
ORACLE instance started.
Total System Global Area 1369579520 bytes
Fixed Size 2213216 bytes
Variable Size 905972384 bytes
Database Buffers 452984832 bytes
Redo Buffers 8409088 bytes
Database mounted.
Database opened.
SQL >
SQL > show parameter undo
NAME TYPE VALUE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
_undo_autotune boolean FALSE
undo_management string MANUAL
undo_retention integer 1800
undo_tablespace string undo2
SQL > alter system set undo_management =auto scope =spfile;
System altered.
SQL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL > startup
ORACLE instance started.
Total System Global Area 1369579520 bytes
Fixed Size 2213216 bytes
Variable Size 905972384 bytes
Database Buffers 452984832 bytes
Redo Buffers 8409088 bytes
Database mounted.
Database opened.