Oracle 無備份情況下undo檔案損壞處理

SQL> select ename,sal from scott.emp where deptno=10;

ENAME   SAL

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

CLARK 2450

KING 5000

MILLER 1300

SQL> show undo

SP2-0158: unknown SHOW option "undo"

SQL> show parameter undo

NAME      TYPE VALUE

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

undo_management      string AUTO

undo_retention      integer 900

undo_tablespace      string UNDOTBS2

SQL> create undo tablespace undotbs3 datafile '/u01/oracle/oradata/ora10g/undotbs03.dbf' size 20M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs3;

System altered.

開新會話 產生事務

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

    XIDUSN    XIDSLOT   XIDSQN NAME

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

9     0      300

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

SEGMENT_NAME   TABLESPACE_NAME    FILE_ID

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

_SYSSMU9$   UNDOTBS3    2

SQL> ! rm -rf /u01/oracle/oradata/ora10g/undotbs03.dbf

SQL> shut abort   也可以不停庫 使用alter database datafile 2 offline;來觸發資料庫認識到資料檔案損壞 

  再使用alter database create datafile 2;這樣不用停庫 事務最後能保持住.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size     1218992 bytes

Variable Size    83887696 bytes

Database Buffers   197132288 bytes

Redo Buffers     2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '/u01/oracle/oradata/ora10g/undotbs03.dbf'

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

     FILE# CHECKPOINT_CHANGE# REC FUZ

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

1        513395 NO  YES

2     0

3        513395 NO  YES

4        513395 NO  YES

5        513395 NO  YES

6        513395 NO  YES

6 rows selected.

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

     FILE# STATUS     CHECKPOINT_CHANGE# NAME

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

1 SYSTEM   513395 /u01/oracle/oradata/ora10g/system01.dbf

2 ONLINE   513572 /u01/oracle/oradata/ora10g/undotbs03.dbf

3 ONLINE   513395 /u01/oracle/oradata/ora10g/sysaux01.dbf

4 ONLINE   513395 /u01/oracle/oradata/ora10g/users01.dbf

5 ONLINE   513395 /u01/oracle/oradata/ora10g/mytest01.dbf

6 ONLINE   513395 /u01/oracle/oradata/ora10g/undotbs02.dbf

6 rows selected.

SQL> set line 110

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

 STATUS     CHECKPOINT_CHANGE# NAME CREATION_CHANGE#

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

SYSTEM   513395 /u01/oracle/oradata/ora10g/system01.dbf    9

ONLINE   513572 /u01/oracle/oradata/ora10g/undotbs03.dbf    513571

ONLINE   513395 /u01/oracle/oradata/ora10g/sysaux01.dbf    6609

ONLINE   513395 /u01/oracle/oradata/ora10g/users01.dbf    10566

ONLINE   513395 /u01/oracle/oradata/ora10g/mytest01.dbf    459576

ONLINE   513395 /u01/oracle/oradata/ora10g/undotbs02.dbf    492642

6 rows selected.

依據控制檔案的資訊 重建這個檔案

SQL> alter database create datafile '/u01/oracle/oradata/ora10g/undotbs03.dbf';

Database altered.

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

     FILE# CHECKPOINT_CHANGE# REC FUZ

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

1        513395 NO  YES

2        513571 YES NO

3        513395 NO  YES

4        513395 NO  YES

5        513395 NO  YES

6        513395 NO  YES

6 rows selected.

select * from v$recover_file;file;

     FILE# ONLINE  ONLINE_ ERROR    CHANGE# TIME

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

2 ONLINE  ONLINE     513571 25-FEB-11

SQL> 

需要 5號日誌進行恢復

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

FILENUMBER    STATUS SCN      SEQUENCE

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

1      8196 513395     5

2 0 513571     5

3 4 513395     5

4 4 513395     5

5 4 513395     5

6 4 513395     5

6 rows selected.

SQL> recover datafile 2;

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        533737 NO  YES

2        533737 NO  YES

3        533737 NO  YES

4        533737 NO  YES

5        533737 NO  YES

6        533737 NO  YES

6 rows selected.

SQL> 資料庫shut abort過 事務被回退

SQL> select ename,sal from scott.emp where deptno=10;

ENAME   SAL

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

CLARK 2450

KING 5000

MILLER 1300

SQL>