UNDO表空间备份恢复（二）

UNDO表空间热备份有事物的时候块损坏的解决方案

我没来做一个实验

第一，先查看一下UNDO表空间对应的数据文件

SQL> select tablespace_name,file_namefrom 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

第二，热备份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> select XIDUSN,XIDSLOT,XIDSQN fromv$transaction;

 

no rows selected

查看到目前是没有事物存在的，那么现在我们另开一个终端给他起一个事物

 

SQL> update bbb set sal=sal+1;

 

14 rows updated.

这个时候查看一下事物

SQL> select XIDUSN,XIDSLOT,XIDSQN fromv$transaction;

 

   XIDUSN    XIDSLOT     XIDSQN

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

       18          4         10

第三，现在开始破坏

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

 

SQL> alter database datafile 5 offline;

alter database datafile 5 offline

*

ERROR at line 1:

ORA-00603: ORACLE server session terminatedby fatal error

查看表空间

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                                          OFFLINE     FILE NOT FOUND

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

查看得知5号数据文件需要恢复所以我们恢复数据文件5

SQL> recoverdatafile 5;

ORA-00603:ORACLE server session terminated by fatal error

 

 

SQL> conn /assysdba

Connected.

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> alterdatabase datafile 5 online;

 

Databasealtered.

 

SQL> selectXIDUSN,XIDSLOT,XIDSQN from v$transaction;

 

    XIDUSN   XIDSLOT     XIDSQN

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

        20          0          0

        15          0          0

        20          0          0

        11          0          0

        12          0          0

        17          0          0

        18          4         10

        16          0          0

        14          0          0

        13          0          0

        19          0          0

联机5号数据文件之后查询发现事物存在

另一个终端commit提交事物之后

 

SQL> selectXIDUSN,XIDSLOT,XIDSQN from v$transaction;

 

no rows selected