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

kngnng發表於2013-03-07
模式

說明

         資料庫開啟歸檔，有資料庫RMAN備份，之後控制檔案和資料檔案丟失，但是有控制檔案備份，這種情況可以先恢復控制檔案，再恢復資料檔案，在保證歸檔檔案都存在的情況下恢復完成後資料不會丟失。

 

資料恢復

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>select * from ttt;

 

 

6        備份控制檔案

SQL>conn / as sysdba;

SQL>alter database backup controlfile to ‘/home/oracle/control.ctl’;

                  說明：這一步也可以省略，在第8步恢復控制檔案時需要用之前備份的備份集   來恢復。

         執行：   

RMAN> restore controlfile to '/oracle/u01/app/oracle/oradata/orcl/control01.ctl' from '/oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_07/o1_mf_ncsnf_TAG20130307T145358_8mjgfnbg_.bkp';

 

7        模擬丟失控制檔案和資料檔案

SQL>conn / as sysdba;

SQL>shutdown abort;

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

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

SQL>startup

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狀態。

        

8        恢復控制檔案

         之前我們trace出了控制檔案的備份

         利用備份恢復控制檔案        

RMAN>set dbid=1324770912

RMAN> restore controlfile to '/oracle/u01/app/oracle/oradata/orcl/control01.ctl' from '/home/oracle/control.ctl';

 

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

 

channel ORA_DISK_1: copied control file copy

Finished restore at 07-MAR-13

         拷貝控制檔案        

[oracle@dev206 oracle]$ cp /oracle/u01/app/oracle/oradata/orcl/control01.ctl /oracle/u01/app/oracle/oradata/orcl/control02.ctl

[oracle@dev206 oracle]$ cp /oracle/u01/app/oracle/oradata/orcl/control01.ctl /oracle/u01/app/oracle/oradata/orcl/control03.ctl

         啟動資料庫到mount狀態   

SQL>alter database mount;

9        利用RMAN恢復資料庫

RMAN>recover database;

 

Starting recover at 07-MAR-13

Starting implicit crosscheck backup at 07-MAR-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=543 devtype=DISK

Crosschecked 2 objects

Finished implicit crosscheck backup at 07-MAR-13

 

Starting implicit crosscheck copy at 07-MAR-13

using channel ORA_DISK_1

Finished implicit crosscheck copy at 07-MAR-13

 

searching for all files in the recovery area

cataloging files...

no files cataloged

 

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 14:44:05

RMAN-06094: datafile 6 must be restored

         提示需要restore datafile 6; 

SQL>restore datafile 6;

 

Starting restore at 07-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_07/o1_mf_nnndf_TAG20130307T142051_8mjdg42h_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_07/o1_mf_nnndf_TAG20130307T142051_8mjdg42h_.bkp tag=TAG20130307T142051

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

Finished restore at 07-MAR-13

         啟動資料庫

RMAN> alter database open;

 

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

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

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

RMAN-03002: failure of alter db command at 03/07/2013 14:49:32

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

RMAN>alter database open noresetlogs;

 

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

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

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

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;"

RMAN-01008: the bad identifier was: noresetlogs

RMAN-01007: at line 1 column 21 file: standard input

 

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

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

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

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate"

RMAN-01007: at line 1 column 32 file: standard input

 

RMAN>alter database open resetlogs;

        

10   檢查資料

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

        ID

----------

         1

         2

         3

         4

         5

         6

         資料庫恢復完成.

 

說明：這個恢復過程在一定程度上就是“歸檔模式有備份丟失資料檔案後恢復——重建控制檔案”的恢復，只是沒有重建控制檔案利用了備份來恢復。

詳見：http://space.itpub.net/26252014/viewspace-755506

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

相關文章