UNDO表空间备份恢复（四）

   当undo表空间没有备份的时候，控制文件里有undo建立以来的信息

   先查看表里的信息

第一，先查看原来t1表里的信息和事物槽里的事物

SQL> select ename,sal,deptno from t1 where deptno=30;

ENAME             SAL           DEPTNO
----------             ----------         ----------
ALLEN             1600             30
WARD             1250              30
MARTIN          1250              30
BLAKE            2850              30
TURNER        1500              30
JAMES           950                30

6 rows selected.

SQL>

查看事物槽里的事物

select xidusn,xidslot,sidsqn from v$transaction;

no rows selected

 

SQL> show parameter undo

NAME                                        TYPE        VALUE
------------------------------------       -----------    ------------------------------
undo_management                      string      AUTO
undo_retention                             integer    900
undo_tablespace

创建新的undo表空间

SQL> create undo tablespace undotbs3 datafile '/u01/tiger/oradata/orcl2/undotbs03.dbf' size 20m;

Tablespace created.

修改undo表空间

SQL> alter system set undo_tablespace='UNDOTBS3';

System altered.

 

第二，产生一个新的会话让他产生事物

SQL> update t1 set sal=sal+1 where deptno=30;

6 rows updated.

SQL> select ename,sal,deptno from t1 where deptno=30;

ENAME             SAL       DEPTNO
----------            ----------    ----------
ALLEN            1601         30
WARD             1251        30
MARTIN          1251        30
BLAKE            2851        30
TURNER        1501        30
JAMES            951         30

6 rows selected.

查看交易产生的事物

SQL> select XIDUSN,XIDSLOT,XIDSQN,name from v$transaction;

    XIDUSN    XIDSLOT       XIDSQN     NAME
----------          ----------          ----------      ----------
        30          0                     2

这个查询能看到是30号回滚段，下面我们要找30号回滚段在那个文件里面

SQL> select segment_name,tablespace_name,file_id from dba_rollback_segs where segment_id=30;

SEGMENT_NAME                   TABLESPACE_NAME                        FILE_ID
------------------------------             ------------------------------                        ----------
_SYSSMU30$                         UNDOTBS3                                          6

这个查询看出是6号数据文件

我们再确认一下用的是哪个回滚表空间然后把它干掉

SQL> show parameter undo

NAME                                     TYPE          VALUE
------------------------------------     -----------   ------------------------------
undo_management                 string        AUTO
undo_retention                        integer      900
undo_tablespace                     string        UNDOTBS3
SQL> !rm -rf /u01/tiger/oradata/orcl2/undotbs03.dbf

然后强制关闭数据库

SQL> shut abort
ORACLE instance shut down.

启动数据库：

SQL> startup
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1219136 bytes
Variable Size             104859072 bytes
Database Buffers          201326592 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/tiger/oradata/orcl2/undotbs03.dbf'
这里发现会报错，因为我们已经没有了回滚表空间

SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#   REC      FUZ
---------- ------------------                            ---         ---
         1             489268                         NO        YES
         2             489268                         NO        YES
         3             489268                         NO        YES
         4             489268                         NO        YES
         5             489268                         NO        YES
         6                  0

6 rows selected.

SQL>  select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS          CHECKPOINT_CHANGE#   NAME
---------- ------- -                -----------------                          ----------------------------------------
         1  SYSTEM            489268                                  /u01/tiger/oradata/orcl2/system01.dbf
         2 ONLINE              489268                                  /u01/tiger/oradata/orcl2/undotbs01.dbf
         3 ONLINE              489268                                  /u01/tiger/oradata/orcl2/sysaux01.dbf
         4 ONLINE              489268                                  /u01/tiger/oradata/orcl2/users01.dbf
         5 ONLINE              489268                                  /u01/tiger/oradata/orcl2/undotbs02.dbf
         6 ONLINE              492667                                  /u01/tiger/oradata/orcl2/undotbs03.dbf

6 rows selected.

SQL> select status,CHECKPOINT_CHANGE#,name,creation_change# from v$datafile;

STATUS              CHECKPOINT_CHANGE#   NAME                                                           CREATION_CHANGE#
-------                   ------------------                         ----------------------------------------                        ----------------
SYSTEM             489268                                  /u01/tiger/oradata/orcl2/system01.dbf                   9
ONLINE              489268                                  /u01/tiger/oradata/orcl2/undotbs01.dbf             444079
ONLINE              489268                                  /u01/tiger/oradata/orcl2/sysaux01.dbf                6609
ONLINE              489268                                  /u01/tiger/oradata/orcl2/users01.dbf                10566
ONLINE              489268                                  /u01/tiger/oradata/orcl2/undotbs02.dbf             468291
ONLINE              492667                                  /u01/tiger/oradata/orcl2/undotbs03.dbf             492666

6 rows selected.

依据控制文件的信息重建这个文件

SQL> alter database create datafile '/u01/tiger/oradata/orcl2/undotbs03.dbf';

Database altered.

SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;

     FILE#         CHECKPOINT_CHANGE#    REC          FUZ
----------            ------------------                         ---               ---
         1             489268                                   NO             YES
         2             489268                                   NO             YES
         3             489268                                   NO             YES
         4             489268                                   NO             YES
         5             489268                                   NO             YES
         6             492666                                   YES            NO

6 rows selected.

查询要修复的数据文件

SQL> select * from v$recover_File; 

     FILE#  ONLINE   ONLINE_          ERROR                   CHANGE#    TIME
----------     -------        -------                --------------------        ----------          ---------
         6     ONLINE    ONLINE                                           492666           24-AUG-12

SQL>  select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS    SCN                SEQUENCE
----------                 ----------      ----------------     ----------
         1                  8196          489268                    3
         2                   4               489268                    3
         3                   4               489268                    3
         4                   4               489268                    3
         5                   4               489268                    3
         6                   0               492666                    3

       查询得出需要3号日志进行修复

SQL> recover datafile 6;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;

     FILE#       CHECKPOINT_CHANGE#   REC   FUZ
     ----------      ------------------                         ---      ---
         1             513352                                  NO  YES
         2             513352                                  NO  YES
         3             513352                                  NO  YES
         4             513352                                  NO  YES
         5             513352                                  NO  YES
         6             513352                                  NO  YES

6 rows selected

 

SQL> select ename,sal,deptno from t1 where deptno=30;

ENAME             SAL       DEPTNO
----------              ---------- ----------
ALLEN            1601         30
WARD            1251         30
MARTIN         1251         30
BLAKE           2851         30
TURNER       1501          30
JAMES           951            30

6 rows selected.

    因为shut abort 数据被回退所以之前有一笔交易没有被记录