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/