UNDO--UNDO数据文件丢失，有备份，有活动事务

SQL> set sqlp 'SESSION A> '
SESSION A> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SESSION A> alter tablespace UNDOTBS1 begin backup;

Tablespace altered.

SESSION A> host cp //u01/app/oracle/oradata/shujukuai/undotbs01.dbf /u01/app/oracle/oradata/shujukuai/undotbs01.dbf.bak

SESSION A> host cp /u01/app/oracle/oradata/shujukuai/undotbs01.dbf /u01/app/oracle/oradata/shujukuai/undotbs01.dbf.bak

SESSION A> alter tablespace UNDOTBS1 end backup;

Tablespace altered.

SESSION A> drop table t1;

Table dropped.

SESSION A> create table t1(x int);

Table created.

SESSION A> insert into t1 values(1);

1 row created.

重新打开一个会话

[oracle@RHEL5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 2 07:18:36 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set sqlp 'SESSION B> '
SESSION B> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         2         46        371

SESSION B> select segment_name,tablespace_name,file_id from dba_rollback_segs where segment_id=2;

SEGMENT_NAME                   TABLESPACE_NAME                   FILE_ID
------------------------------ ------------------------------ ----------
_SYSSMU2$                      UNDOTBS1                                2

再开一个连接

[oracle@RHEL5 ~]$ vi /u01/app/oracle/oradata/shujukuai/undotbs01.dbf

输入 :%!xxd

回车

输入 /20000

修改00a2 为1111

保存退出

SESSION B> col name for a40
SESSION B> set linesize 180
SESSION B> col error for a20
SESSION B> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

     FILE# STATUS  ERROR                REC TABLESPACE_NAME                NAME
---------- ------- -------------------- --- ------------------------------ ----------------------------------------
         2 ONLINE  CORRUPT HEADER                                          /u01/app/oracle/oradata/shujukuai/undotbs01.dbf

SESSION B> alter database datafile 2 offline;
alter database datafile 2 offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

SESSION B> conn / as sysdba
Connected.
SESSION B> set linesize 180
SESSION B> col name for a40
SESSION B> col error for a20
SESSION B> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

     FILE# STATUS  ERROR                REC TABLESPACE_NAME                NAME
---------- ------- -------------------- --- ------------------------------ ----------------------------------------
         2 OFFLINE FILE NOT FOUND

SESSION B> host cp /u01/app/oracle/oradata/shujukuai/undotbs01.bak /u01/app/oracle/oradata/shujukuai/undotbs01.dbf

SESSION B> recover datafile 2;
ORA-00603: ORACLE server session terminated by fatal error

SESSION B> conn / as sysdba
Connected.
SESSION B> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

no rows selected

SESSION B> col name for a50
SESSION B> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ --------------------------------------------------
         1 SYSTEM              756427 /u01/app/oracle/oradata/shujukuai/system01.dbf
         2 OFFLINE             767661 /u01/app/oracle/oradata/shujukuai/undotbs01.dbf
         3 ONLINE              756427 /u01/app/oracle/oradata/shujukuai/sysaux01.dbf
         4 ONLINE              756427 /u01/app/oracle/oradata/shujukuai/users01.dbf
         5 ONLINE              756427 /u01/app/oracle/oradata/shujukuai/tbs01.dbf
         6 ONLINE              756427 /u01/app/oracle/oradata/shujukuai/test2.dbf
         7 ONLINE              756427 /u01/app/oracle/oradata/shujukuai/tbs1.dbf
        11 ONLINE              756427 /u01/app/oracle/oradata/shujukuai/shujukuai_undo01
                                      .dbf

8 rows selected.

SESSION B> alter database datafile 2 online;

Database altered.

SESSION B> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         2         46        371

咦，为什么这个事务还在呢？？？

哦，因为恢复是使用了UNDO的REDO

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-667202/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-667202/