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

kngnng發表於2013-03-07
模式

說明

         資料庫已經開啟了歸檔，但是沒有資料庫全備這種恢復是有條件的：

a)       System表空間在沒有藉助備份的情況下是不能恢復的；

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        備份控制檔案

SQL>conn / as sysdba;

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

        

5        利用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備份。

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

         新插入一些資料    

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不能恢復資料檔案。      

7        手動建立資料檔案   

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” 來建立控制檔案之前的檔案。

8        嘗試通過RMAN恢復資料庫

RMAN> restore datafile 6;

 

Starting restore at 07-MAR-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=540 devtype=DISK

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 03/07/2013 11:36:10

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 6 found to restore

         控制檔案重新建立過，RMAN找不到資料檔案建立過程。

         我們試一試手工註冊RMAN資訊        

RMAN> catalog start with '/oracle/u01/app/oracle/flash_recovery_area';

 

searching for all files that match the pattern /oracle/u01/app/oracle/flash_recovery_area

no files found to be unknown to the database

         並沒有找到備份資訊，因為沒有備份，所以不能恢復。

                 

9        開啟資料庫

                   此時資料庫因為ttt.dbf檔案的丟失不能恢復，從而只能啟動到mount狀態。

         要想開啟資料庫，我們只能將丟失的資料檔案offline，這樣在開啟資料庫，但是資料已 經丟失，並且不能恢復。    

SQL> alter database datafile '/oracle/u01/app/oracle/oradata/orcl/ttt.dbf' offline;

         啟動資料庫    

SQL>alter database open;

         嘗試恢復表空間ttt,資料檔案ttt.dbf  

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'

失敗

RMAN> recover database;

 

Starting recover at 07-MAR-13

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 03/07/2013 13:41:35

RMAN-06094: datafile 6 must be restored

失敗

         我們只能刪除表空間ttt重建

SQL> drop tablespace ttt including contents and datafiles;

 

未重做控制檔案後的恢復

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        模擬主機斷電丟失資料檔案

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

5        建立資料檔案

         因為此時沒有備份，指定手工建立資料檔案

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;

                  注意：為什麼能用RMAN來提取資料檔案呢，是因為資料檔案是在控制檔案之後   建立的，控制檔案中包含資料檔案的建立過程。

6        恢復資料檔案 

SQL>recover datafile 6;

或者

RMAN>recover datafile 6;

         啟動資料庫

SQL>alter database open;

或者
RMAN>alter database open;

7        檢查資料

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

        ID

----------

         1

         2

         3

         資料庫恢復完成.

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

相關文章