UNDO表空间备份恢复

[img]http://dl2.iteye.com/upload/attachment/0094/0720/764718cb-4381-3ecd-b430-77ad6de7fd52.jpg[/img]
UNDO表空间在有热备没有事物交易的时候有数据块损坏的恢复

第一，先看一下确保undo表空间里没有交易的事物

SQL>select XID USN,XIDSLOT,XIDSQN fromv$transaction;

no rows selected

第二，确认当前所用的回退表空间

SQL>show parameter undo



NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

第三查看表空间对应的数据文件

SQL>select tablespace_name,file_name from dba_data_Files

TABLESPACE_NAME FILE_NAME

------------------------------ ----------------------------------------

USERS /u01/tiger/oradata/orcl2/users01.dbf

SYSAUX /u01/tiger/oradata/orcl2/sysaux01.dbf

SYSTEM /u01/tiger/oradata/orcl2/system01.dbf

UNDOTBS2 /u01/tiger/oradata/orcl2/undotbs02.dbf

TIGER /u01/tiger/oradata/orcl2/tiger.dbf

第四查看数据文件的状态

SQL> selectfile#,name,status,recover,error from v$datafile_header;



FILE# NAME STATUS REC ERROR

---------- ---------------------------------------- ------- --- --------------------

1 /u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5 /u01/tiger/oradata/orcl2/undotbs02.dbf ONLINE NO

7 /u01/tiger/oradata/orcl2/tiger.dbf ONLINE NO

第五，热备份undo表空间

SQL> alter tablespace undotbs2 beginbackup;



Tablespace altered.



SQL> !cp/u01/tiger/oradata/orcl2/undotbs02.dbf /bk



SQL> alter tablespace undotbs2 endbackup;



Tablespace altered.

如果在执行这条命令

SQL> alter tablespace undotbs2 beginbackup;

报错：

ERROR at line 1:

ORA-01123: cannot start online backup; media recovery notenabled

这说明了你的数据库不是归档模式，非归档的时候是不能进行热备的，只要把数据库改成归档模式即可。

第六，破坏数据块UNDO

QL> !cp /etc/passwd/u01/tiger/oradata/orcl2/undotbs02.dbf



SQL> selectfile#,name,status,recover,error from v$datafile_header;



FILE# NAME STATUS REC ERROR

---------- ---------------------------------------- ------- --- --------------------

1 /u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5 /u01/tiger/oradata/orcl2/undotbs02.dbf ONLINE CANNOTREAD HEADER

7 /u01/tiger/oradata/orcl2/tiger.dbf ONLINE NO

第七，使UNDO文件离线会触发写文件操作就会报错系统将自动UNDOoffline

SQL> alter database datafile 5 offline;

alter database datafile 5 offline

*

ERROR at line 1:

ORA-00603: Oracle server session terminatedby fatal error

SQL> /



FILE# NAME STATUS REC ERROR

-------------------------------------------------- ------- --- --------------------

1 /u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5 /u01/tiger/oradata/orcl2/undotbs02.dbf OFFLINE WRONG FILE TYPE

7 /u01/tiger/oradata/orcl2/tiger.dbf ONLINE NO

第八，恢复

SQL> !cp /bk/undotbs02.dbf/u01/tiger/oradata/orcl2/



SQL> selectfile#,name,status,recover,error from v$datafile_header;



FILE# NAME STATUS REC ERROR

-------------------------------------------------- ------- --- --------------------

1 /u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5 /u01/tiger/oradata/orcl2/undotbs02.dbf OFFLINEYES

7 /u01/tiger/oradata/orcl2/tiger.dbf ONLINE NO

正确识别了文件头但是需要恢复

SQL> recover datafile 5;

ORA-00603: ORACLE server session terminatedby fatal error





SQL> conn /as sysdba

Connected.

SQL> col error for a20

SQL> selectfile#,name,status,recover,error from v$datafile_header;



FILE# NAME STATUS REC ERROR

-------------------------------------------------- ------- --- --------------------

1 /u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5 /u01/tiger/oradata/orcl2/undotbs02.dbf OFFLINE NO

7 /u01/tiger/oradata/orcl2/tiger.dbf ONLINE NO

使得5号数据文件上线

SQL> alter database datafile 5 online;



Database altered.



SQL> selectfile#,name,status,recover,error from v$datafile_header;



FILE# NAME STATUS REC ERROR

-------------------------------------------------- ------- --- --------------------

1 /u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5 /u01/tiger/oradata/orcl2/undotbs02.dbf ONLINE NO

7 /u01/tiger/oradata/orcl2/tiger.dbf ONLINE NO