UNDO恢复

回滚段数据文件丢失或损坏处理方法 步骤 1：将回滚段改成手动管理 2：创建新的回滚段，替换现有回滚段并删除 3：将回滚段改成自动管理 idle>select * from v$version; BANNER                                                                           -------------------------------------------------------------------------------- OracleDatabase11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production    PL/SQL Release 11.2.0.1.0 - Production                                           CORE 11.2.0.1.0 Production                                                       TNS for 64-bit Windows: Version 11.2.0.1.0 - Production                         NLSRTL Version 11.2.0.1.0 - Production                                           idle>select open_mode from v$database; OPEN_MODE                                                                        --------------------                                                             READ WRITE                                                                      idle>select ts#,name from v$tablespace;        TS# NAME                                                                  ---------- ------------------------------                                                 0 SYSTEM                                                                         1 SYSAUX                                                                         2 UNDOTBS1                                                                      4 USERS                                                                         3 TEMP                                                                           6 EXAMPLE                                                               已选择6行。 idle>col file# for 99; idle>col status for a15; idle>col name for a55; idle>set linesize 100; idle>select file#,status,name from v$datafile where ts#=2; FILE# STATUS          NAME                                                                           ----- --------------- -------------------------------------------------------                            3 ONLINE          D:\ORACLE11G\ORADATA\TEST\UNDOTBS01.DBF                                        idle>shutdown immediate; 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 idle>startup mount; ORACLE 例程已经启动。 Total System Global Area 1636814848 bytes                                                            Fixed Size                  2176248 bytes                                                            Variable Size            1073744648 bytes                                                            Database Buffers          553648128 bytes                                                            Redo Buffers                7245824 bytes                                                            数据库装载完毕。 idle>host del d:\oracle11g\oradata\test\undotbs01.dbf; idle>alter database open; alter database open * 第 1 行出现错误: ORA-01157: 无法标识/锁定数据文件 3 - 请参阅 DBWR 跟踪文件 ORA-01110: 数据文件 3: 'D:\ORACLE11G\ORADATA\TEST\UNDOTBS01.DBF' idle>show parameter undo; NAME                                 TYPE        VALUE                                              ------------------------------------ ----------- ------------------------------                      undo_management                      string      AUTO                                                undo_retention                       integer     900                                                 undo_tablespace                      string      UNDOTBS1                                           idle>alter system set undo_management=manual scope=spfile; 系统已更改。 idle>alter database datafile 3 offline drop; 数据库已更改。 idle>alter database open; 数据库已更改。 idle>create undo tablespace undotbs2 datafile 'd:\oracle11g\oradata\test\undotbs2.dbf' size 500M; 表空间已创建。                         idle>alter system set undo_tablespace=undotbs2 scope=spfile; 系统已更改。                               idle>shutdown immediate; 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 idle>startup mount; ORACLE 例程已经启动。 Total System Global Area 1636814848 bytes                                                            Fixed Size                  2176248 bytes                                                            Variable Size            1073744648 bytes                                                            Database Buffers          553648128 bytes                                                            Redo Buffers                7245824 bytes                                                            数据库装载完毕。 idle>alter system set undo_management = auto scope=spfile; 系统已更改。 idle>startup force; ORACLE 例程已经启动。 Total System Global Area 1636814848 bytes                                                            Fixed Size                  2176248 bytes                                                            Variable Size            1073744648 bytes                                                            Database Buffers          553648128 bytes                                                            Redo Buffers                7245824 bytes                                                            数据库装载完毕。 数据库已经打开。 idle>show parameter undo; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_management                      string      AUTO undo_retention                       integer     900 undo_tablespace                      string      UNDOTBS2 idle>drop tablespace undotbs1 including contents and datafiles; 表空间已删除。 idle>insert into user1.t1 values(1,'ab'); 已创建 1 行。 idle>commit; 提交完成。