UNDO表空间备份恢复

UNDO表空间备份恢复

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

1 2 3 4 下一页