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

UNDO数据文件丢失，有备份，无活动事务。直接使用备份覆盖即可

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/shujukuai

Tablespace created.

SQL> set linesize 180;
SQL> alter system set undo_tablespace=UNDOTBS1 scope=both;

System altered.

SQL> show parameter undo;

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

Tablespace altered.

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

SQL> alter tablespace UNDOTBS1 end backup;

Tablespace altered.

SQL> host vi /u01/app/oracle/oradata/shujukuai/undotbs01.dbf
破坏UNDOTBS01.DBF

输入 :%!xxd 回车

输入 /20000 修改00a1为任意值

SQL> 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

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

SQL> conn / as sysdba
Connected.
SQL> 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 RECOVER             763796 /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.

SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;

     FILE# CHECKPOINT_CHANGE# REC FUZ
---------- ------------------ --- ---
         1             756427 NO  YES
         2                  0
         3             756427 NO  YES
         4             756427 NO  YES
         5             756427 NO  YES
         6             756427 NO  YES
         7             756427 NO  YES
        11             756427 NO  YES

8 rows selected.

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

SQL> alter database datafile 2 online;
alter database datafile 2 online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/u01/app/oracle/oradata/shujukuai/undotbs01.dbf'

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

SQL> conn / as sysdba
Connected.
SQL> alter database datafile 2 online;

Database altered.

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

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