UNDO表空间备份恢复

UNDO表空间在有热备没有事物交易的时候有数据块损坏的恢复第一，先看一下确保undo表空间里没有交易的事物SQL>select XID USN,XIDSLOT,XIDSQN fromv$transaction;no rows selected第二，确认当前所用的回退表空间SQL>show parameter undo NAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1第三查看表空间对应的数据文件SQL>select tablespace_name,file_name from dba_data_FilesTABLESPACE_NAME FILE_NAME------------------------------ ----------------------------------------USERS /u01/tiger/oradata/orcl2/users01.dbfSYSAUX /u01/tiger/oradata/orcl2/sysaux01.dbfSYSTEM /u01/tiger/oradata/orcl2/system01.dbfUNDOTBS2 /u01/tiger/oradata/orcl2/undotbs02.dbfTIGER /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?这说明了你的数据库不是归档模式，非归档的时候是不能进行热备的，只要把数据库改成归档模式即可。第六，破坏数据块UNDOQL> ！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 NO3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO5 /u01/tiger/oradata/orcl2/undotbs02.dbf ONLINE CANNOTREAD HEADER7 /u01/tiger/oradata/orcl2/tiger.dbf ONLINE NO第七，使UNDO文件离线会触发写文件操作就会报错系统将自动UNDOofflineSQL> alter database datafile 5 offline;alter database datafile 5 offline*ERROR at line 1:ORA-00603: Oracle server session terminatedby fatal errorSQL> / FILE# NAME STATUS REC ERROR-------------------------------------------------- ------- --- --------------------1 /u01/tiger/oradata/orcl2/system01.dbf ONLINE NO3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO5 /u01/tiger/oradata/orcl2/undotbs02.dbf OFFLINE WRONG FILE TYPE7 /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 NO3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO5 /u01/tiger/oradata/orcl2/undotbs02.dbf OFFLINEYES7 /u01/tiger/oradata/orcl2/tiger.dbf ONLINE NO正确识别了文件头但是需要恢复SQL> recover datafile 5;ORA-00603: ORACLE server session terminatedby fatal error SQL> conn /as sysdbaConnected.SQL> col error for a20SQL> selectfile#,name,status,recover,error from v$datafile_header; FILE# NAME STATUS REC ERROR-------------------------------------------------- ------- --- --------------------1 /u01/tiger/oradata/orcl2/system01.dbf ONLINE NO3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO5 /u01/tiger/oradata/orcl2/undotbs02.dbf OFFLINE NO7 /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 NO3 /u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO4 /u01/tiger/oradata/orcl2/users01.dbf ONLINE NO5 /u01/tiger/oradata/orcl2/undotbs02.dbf ONLINE NO7 /u01/tiger/oradata/orcl2/tiger.dbf ONLINE NO

rac修改归档日志出现的错误解决Oracle控制文件备份恢复 noresetlogs 恢复相关资讯 undo

Oracle 11g undo_retention 以及（05月28日）
undo表空间使用率（07/23/2015 16:29:56）
undo表空间概述（02/24/2015 20:32:43）

Oracle中利用undo进行数据的恢复操（11/27/2015 09:31:30）
undo表空间修复小结（07/08/2015 08:43:13）
Oracle 11gR2 Database UNDO表空间（01/29/2015 11:30:59）

本文评论查看全部评论（0）