控制檔案恢復—從快照中恢復

kngnng發表於2013-03-09

說明

         資料庫在進行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> CONFIGURE CONTROLFILE AUTOBACKUP ON;

 

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

 

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/u01/app/oracle/backup/%F';

 

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/u01/app/oracle/backup/%F';

new RMAN configuration parameters are successfully stored

 

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT

 '/oracle/u01/app/oracle/backup/%U';

 

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oracle/u01/app/oracle/backup/%U';

new RMAN configuration parameters are successfully stored

released channel: ORA_DISK_1

 

5        備份資料庫

RMAN>backup database;

 

Starting backup at 07-MAR-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=540 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/oracle/u01/app/oracle/oradata/orcl/system01.dbf

input datafile fno=00003 name=/oracle/u01/app/oracle/oradata/orcl/sysaux01.dbf

input datafile fno=00005 name=/oracle/u01/app/oracle/oradata/orcl/admin01.dbf

input datafile fno=00002 name=/oracle/u01/app/oracle/oradata/orcl/undotbs01.dbf

input datafile fno=00004 name=/oracle/u01/app/oracle/oradata/orcl/users01.dbf

input datafile fno=00006 name=/oracle/u01/app/oracle/oradata/orcl/ttt.dbf

channel ORA_DISK_1: starting piece 1 at 07-MAR-13

channel ORA_DISK_1: finished piece 1 at 07-MAR-13

piece handle=/oracle/u01/app/oracle/backup/0lo3uo4l_1_1

tag=TAG20130307T175741 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

Finished backup at 07-MAR-13

 

Starting Control File and SPFILE Autobackup at 07-MAR-13

piece handle=/oracle/u01/app/oracle/backup/c-1324770912-20130307-08 comment=NONE

Finished Control File and SPFILE Autobackup at 07-MAR-13

 

         資料庫備份集於/oracle/u01/app/oracle/backup/下

         控制檔案自動備份於/oracle/u01/app/oracle/backup/c-1324770912-20130307-08

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

 

 

7        模擬丟失控制檔案

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

        

8        恢復控制檔案

         在這裡我們利用控制檔案快照來恢復。    

RMAN>set dbid=1324770912

RMAN> restore controlfile from

 ‘/oracle/u01/app/oracle/product/10.2.0/dbs/snapcf_orcl.f’;

 

Starting restore at 08-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

output filename=/oracle/u01/app/oracle/oradata/orcl/control01.ctl

output filename=/oracle/u01/app/oracle/oradata/orcl/control02.ctl

output filename=/oracle/u01/app/oracle/oradata/orcl/control03.ctl

Finished restore at 08-MAR-13

         控制檔案提取完成，啟動資料庫到mount狀態。

RMAN>alter database mount;

 

         利用RMAN恢復資料庫

RMAN>recover database;

 

Starting recover at 08-MAR-13

Starting implicit crosscheck backup at 08-MAR-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=540 devtype=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 08-MAR-13

 

Starting implicit crosscheck copy at 08-MAR-13

using channel ORA_DISK_1

Finished implicit crosscheck copy at 08-MAR-13

 

searching for all files in the recovery area

cataloging files...

no files cataloged

 

using channel ORA_DISK_1

 

starting media recovery

 

archive log thread 1 sequence 1 is already on disk as file

/oracle/u01/app/oracle/oradata/orcl/redo01.log

archive log

filename=/oracle/u01/app/oracle/oradata/orcl/redo01.log thread=1 sequence=1

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

Finished recover at 08-MAR-13

         啟動資料庫

RMAN>alter database open resetlogs;

        

9         檢查資料

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

        ID

----------

         1

         2

         3

         4

         5

         6

         資料庫恢復完成.

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

相關文章