1、如果UNDO当中有事务，UNDO文件被删除如何恢复
i.在scott用户下更改emp表，不提交
SQL> update empset sal = sal +1;
14 rows updated.
Ii.删除undo数据文件
SQL> select namefrom v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/wyzc10g/system01.dbf
/u01/oracle/oradata/wyzc10g/undotbs01.dbf
/u01/oracle/oradata/wyzc10g/sysaux01.dbf
/u01/oracle/oradata/wyzc10g/users01.dbf
/u01/oracle/oradata/wyzc10g/example01.dbf
SQL> ho rm/u01/oracle/oradata/wyzc10g/undotbs01.dbf
Iii.abort关闭并重新启动数据库
SQL> shutdownabort
ORACLE instance shutdown.
SQL> startup
ORACLE instancestarted.
Total System GlobalArea 524288000 bytes
Fixed Size 2097592 bytes
Variable Size 293604936 bytes
DatabaseBuffers 222298112 bytes
Redo Buffers 6287360 bytes
Database mounted.
ORA-01157: cannotidentify/lock data file 2 - see DBWR trace file
ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'
Oracle在启动时需要为保证事务一致性需要做实例恢复，然后此时找不到UNDO没办法恢复,所以没办法打开数据库
Iv.解决方法:
(1)修改undo_management参数为手工，并重新启动数据库
SQL> alter systemset undo_management=manual scope=spfile;
System altered.
SQL> shutdown
ORA-01109: databasenot open
Database dismounted.
ORACLE instance shutdown.
SQL> startup
ORACLE instancestarted.
Total System GlobalArea 524288000 bytes
Fixed Size 2097592 bytes
Variable Size 293604936 bytes
DatabaseBuffers 222298112 bytes
Redo Buffers 6287360 bytes
Database mounted.
ORA-01157: cannotidentify/lock data file 2 - see DBWR trace file
ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'
(2)尝试->将数据库脱机并打开
SQL> alterdatabase datafile 2 offline drop;
Database altered.
SQL> alterdatabase open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLEinstance terminated. Disconnection forced
如上，数据库依然没办法打开，因为事务不存在，再次启动数据库
SQL> conn / assysdba
Connected to an idleinstance.
SQL> startup
ORACLE instancestarted.
Total System GlobalArea 524288000 bytes
Fixed Size 2097592 bytes
Variable Size 293604936 bytes
DatabaseBuffers 222298112 bytes
Redo Buffers 6287360 bytes
Database mounted.
ORA-01092: ORACLEinstance terminated. Disconnection forced
如上，此时怎么办呢?
(3)当数据库打开失败时，查看告警日志;
在/u01/oracle/admin/wyzc10g/bdump路径下的告警日志:alert_wyzc10g.log
查看到文件末尾最后的日志如下:
Errors in file/u01/oracle/admin/wyzc10g/udump/wyzc10g_ora_13564.trc:
ORA-00604: erroroccurred at recursive SQL level 1
ORA-00376: file 2cannot be read at this time
ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'
Error 604 happenedduring db open, shutting down database
USER: terminatinginstance due to error 604
Instance terminatedby USER, pid = 13564
ORA-1092 signalledduring: ALTER DATABASE OPEN...
查看trace文件，在trace文件当中同样可以看到UNDO有问题:
----- Recovery HashTable Statistics ---------
Hash table buckets =32768
Longest hash chain =1
Average hash chain =4/4 = 1.0
Max compares perlookup = 1
Avg compares perlookup = 80/80 = 1.0
----------------------------------------------
ORA-00604: erroroccurred at recursive SQL level 1
ORA-00376: file 2cannot be read at this time
ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'
若要打开数据库,需要用到隐藏参数,如下在11g中查看到的隐藏参数;
SQL> selectksppinm from x$ksppi where ksppinm like '%roll%';
KSPPINM
--------------------------------------------------------------------------------
_ksxp_disable_rolling_migration
transactions_per_rollback_segment
rollback_segments
_rollback_segment_initial
_rollback_segment_count
_offline_rollback_segments
_corrupted_rollback_segments
_cleanup_rollback_entries
_rollback_stopat
fast_start_parallel_rollback
_max_cr_rollbacks
KSPPINM
--------------------------------------------------------------------------------
_mv_rolling_inv
_optimizer_nested_rollup_for_gset
13 rows selected.
(4)创建参数文件， 关闭数据库后修改上述参数
并重新启动
SQL> createpfile='/tmp/a.ora' from spfile;
File created.
Vi /tmp/a.ora,在文件末尾添加
_offline_rollback_segments=true，表示允许强制脱机
若还是无法启动，需要在参数文件中再添加如下:
_corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2','_SYSSMU3','_SYSSMU4','_SYSSMU5','_SYSSMU6','_SYSSMU7','_SYSSMU8','_SYSSMU9','_SYSSMU10'
查询需要segment状态:
SQL> selectsegment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU2$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU3$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU4$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU5$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU6$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU7$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU8$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU9$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU10$ UNDOTBS1 NEEDS RECOVERY
10 rows selected.
或者:select segment_name from dba_segmentswhere tablespace_name = 'UNDOTBS1';
(5)再次重新启动，
startuppfile='/tmp/a.ora';
此时可以重新启动成功，说明参数文件有效
(6)按之前的步骤，重新配置UNDO表空间
(6)(1)关闭数据库 shutdown;
(6)(2)启动到mount状态
(6)(3)脱机: alter database datafile 2 offlinedrop;
(6)(4)修改数据库为打开状态 alter database open;
(6)(5)重新建立新的undo表空间
SQL> droptablespace UNDOTBS1;
Tablespace dropped.
SQL> create undotablespace undotbs1 datafile '/u01/oracle/oradata/wyzc10g/undotbs01.dbf' size10m autoextend on;
Tablespace created.
2、设置UNDO的相关参数
i.查看UNDO保留时间
SQL> showparameter undo_re
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_retention integer 900
如上，设的是900S，保留15min,值设置的越大占用的UNDO表空间就越大(若设置的是自动增长)
Ii.查看UNDO数据文件是否自动增长
SQL> selectfile_name,autoextensible from dba_data_files;
FILE_NAME AUT
-----------------------------------------------------
/u01/oracle/oradata/wyzc10g/users01.dbf YES
/u01/oracle/oradata/wyzc10g/sysaux01.dbf YES
/u01/oracle/oradata/wyzc10g/undotbs01.dbf YES
/u01/oracle/oradata/wyzc10g/system01.dbf YES
/u01/oracle/oradata/wyzc10g/example01.dbf YES
UNDO的管理
假定UNDO表空间的大小500M,RETENTION(保留历史数据)设置的是900S
秒 M
1 100
2 100
3 100
4 200
如上4S之后完成第一个100M做了提交，那么第5S,50M会覆盖第1S的历史数据
所以，如果数据文件设置的是固定大小，不管RETENSION设置的多大，都会被覆盖
若 100 100 30050,均没有提交，则第4秒钟就会报错，说UNDO表空间的大小不足，空间不足。若要避免这个错误，需设置为自动增长
Iii.设置UNDO表空间"确保",即不允许覆盖历史数据
SQL> selecttablespace_name,retention from dba_tablespaces;
TABLESPACE_NAME RETENTION
-----------------------------------------
SYSTEM NOT APPLY
UNDOTBS1 NOGUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
EXAMPLE NOT APPLY
6 rows selected.
SQL> altertablespace undotbs1 retention guarantee;
Tablespace altered.
修改为确保UNDO表空间,这样UNDO表空间的历史数据不允许覆盖
