歸檔模式有備份丟失資料檔案後恢復

kngnng發表於2013-03-06
模式

說明

         資料庫已經開啟了歸檔，控制檔案記錄了RMAN備份的資訊，下面分兩種情況說明此種備份恢復情況：

a)       建立資料檔案後做資料庫備份，之後重做了控制檔案，即控制檔案比資料檔案新，然後丟了了資料檔案

b)       建立資料檔案後做資料庫備份，沒有重做控制檔案，即控制檔案比資料檔案舊，然後丟失資料檔案

重做控制檔案後的恢復

1        建立新的表空間、資料檔案、使用者

SQL>create tablespace ttt datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ size 10m;

SQL>grant connect,resource to ttt identified by ttt;

SQL>alter user ttt default tablespace ttt;

SQL>alter user ttt account unlock;

 

2        構造資料

SQL>conn ttt/ttt;

SQL>create table ttt (id int);

SQL> insert into ttt values(1);

1 row created.

SQL> insert into ttt values(2);

1 row created.

SQL> insert into ttt values(3);

1 row created.

SQL> commit;

Commit complete.

SQL>select * from ttt;

ID

----

1

2

3

         ttt使用者預設表空間為ttt,建立表ttt，並插入3資料。

3        切換日誌    

SQL>conn / as sysdba;

SQL>alter system switch logfile;

         切換日誌，保證資料都寫到DBF中。

4        備份資料庫

RMAN>backup database;

 

5        備份控制檔案

SQL>conn / as sysdba;

SQL>alter database backup controlfile to trace as ‘/home/oracle/control.sql’;

        

6        利用trace重做控制檔案

SQL>conn / as sysdba;

SQL>shutdown immediate;

[oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/control0*.ctl

SQL> startup

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area 1224736768 bytes

Fixed Size                  2020384 bytes

Variable Size             352324576 bytes

Database Buffers          855638016 bytes

Redo Buffers               14753792 bytes

ORA-00205: error in identifying control file, check alert log for more info

         此時在啟動資料庫時報錯找不到控制檔案而只能啟動到nomount狀態。

         開啟/home/oracle/control.sql備份的控制檔案指令碼，執行語句建立控制檔案

SQL>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 ‘/oracle/u01/app/oracle/oradata/orcl/redo01.log’  SIZE 50M,

  GROUP 2 ‘/oracle/u01/app/oracle/oradata/orcl/redo02.log’  SIZE 50M,

  GROUP 3 ‘/oracle/u01/app/oracle/oradata/orcl/redo03.log’  SIZE 50M

-- STANDBY LOGFILE

 

DATAFILE

  ‘/oracle/u01/app/oracle/oradata/orcl/system01.dbf’,

  ‘/oracle/u01/app/oracle/oradata/orcl/undotbs01.dbf’,

  ‘/oracle/u01/app/oracle/oradata/orcl/sysaux01.dbf’,

  ‘/oracle/u01/app/oracle/oradata/orcl/users01.dbf’,

  ‘/oracle/u01/app/oracle/oradata/orcl/admin01.dbf’,

  ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

CHARACTER SET AL32UTF8;

         執行完畢資料庫自動啟動到mount狀態。

SQL>alter database open;

         此時資料庫啟動完成，重做了控制檔案，此時控制檔案中不再包含之前做的rman備份。

7        模擬主機斷電丟失資料檔案

         新插入一些資料    

SQL>conn ttt/ttt

SQL> insert into ttt values(4);

1 row created.

SQL> insert into ttt values(5);

1 row created.

SQL> insert into ttt values(6);

1 row created.

SQL> commit;

Commit complete.

 

SQL>conn / as sysdba;

SQL>shutdown abort;

[oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/ttt.dbf

SQL>startup;

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area 1224736768 bytes

Fixed Size                  2020384 bytes

Variable Size             352324576 bytes

Database Buffers          855638016 bytes

Redo Buffers               14753792 bytes

Database mounted.

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

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

         此時找不到資料檔案6 ttt.dbf

SQL> recover datafile 6;

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

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

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

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

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

 

         通過recover不能恢復資料檔案。      

8        手動建立資料檔案   

SQL> alter database create datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ as ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’;

alter database create datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ as ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

*

ERROR at line 1:

ORA-01178: file 6 created before last CREATE CONTROLFILE, cannot recreate

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

         此時報錯，不能建立

         原因是：控制檔案比資料檔案新，oracle不允許在重建控制檔案後通過”alter database                              create datafile” 來建立控制檔案之前的檔案。

9        通過備份恢復資料庫

SQL>rman target /;

RMAN>list backup

                   此時沒有備份資訊，因為我們重做了控制檔案，備份資訊已經不存在，我們需要手         工註冊來獲取備份資訊。        

RMAN>catalog start with ‘/oracle/u01/app/oracle/flash_recover_area’;

RMAN> restore datafile 6;

 

Starting restore at 06-MAR-13

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00006 to /oracle/u01/app/oracle/oradata/orcl/ttt.dbf

channel ORA_DISK_1: reading from backup piece /oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_06/o1_mf_nnndf_TAG20130306T143952_8mfs5s6w_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_06/o1_mf_nnndf_TAG20130306T143952_8mfs5s6w_.bkp tag=TAG20130306T143952

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Finished restore at 06-MAR-13

 

RMAN> recover database;

 

Starting recover at 06-MAR-13

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:00

 

Finished recover at 06-MAR-13

         啟動資料庫    

RMAN>alter database open;

 

10  檢查資料

SQL> select count(*) from ttt.ttt;

        ID

----------

         1

         2

         3

         4

         5

         6

         資料庫恢復完成.

 

 

未重做控制檔案後的恢復

1        建立新的表空間、資料檔案、使用者

SQL>create tablespace ttt datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ size 10m;

SQL>grant connect,resource to ttt identified by ttt;

SQL>alter user ttt default tablespace ttt;

SQL>alter user ttt account unlock;

 

2        構造資料

SQL>conn ttt/ttt;

SQL>create table ttt (id int);

SQL> insert into ttt values(1);

1 row created.

SQL> insert into ttt values(2);

1 row created.

SQL> insert into ttt values(3);

1 row created.

SQL> commit;

Commit complete.

SQL>select * from ttt;

ID

----

1

2

3

         ttt使用者預設表空間為ttt,建立表ttt，並插入3資料。

3        切換日誌    

SQL>conn / as sysdba;

SQL>alter system switch logfile;

         切換日誌，保證資料都寫到DBF中。

4        備份資料庫

RMAN>backup database;

 

5        模擬主機斷電丟失資料檔案

         新插入一些資料    

SQL>conn ttt/ttt

SQL> insert into ttt values(4);

1 row created.

SQL> insert into ttt values(5);

1 row created.

SQL> insert into ttt values(6);

1 row created.

SQL> commit;

Commit complete.

 

SQL>conn / as sysdba;

SQL>shutdown abort;

[oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/ttt.dbf

SQL>startup;

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area 1224736768 bytes

Fixed Size                  2020384 bytes

Variable Size             352324576 bytes

Database Buffers          855638016 bytes

Redo Buffers               14753792 bytes

Database mounted.

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

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

         此時找不到資料檔案6 ttt.dbf

SQL> recover datafile 6;

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

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

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

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

ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

 

         通過recover不能恢復資料檔案。      

6        手工建立資料檔案或者RMAN提取資料檔案    

SQL> alter database create datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ as ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’;

         資料檔案重新建立完成

                   注意：這一步為什麼能執行成功呢，因為我們的資料檔案建立在控制檔案之後，  即資料檔案要新於控制檔案。

 

         或者通過RMAN備份來提取資料檔案   

RMAN>restore datafile 6;

 

7        恢復資料檔案

SQL>recover datafile 6;

或者

RMAN>recover datafile 6;

         啟動資料庫

SQL>alter database open;

或者
RMAN>alter database open;

 

8        檢查資料

SQL> select count(*) from ttt.ttt;

        ID

----------

         1

         2

         3

         4

         5

         6

         資料庫恢復完成.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26252014/viewspace-755506/，如需轉載，請註明出處，否則將追究法律責任。

相關文章