控制檔案恢復—從trace檔案中恢復

kngnng發表於2013-03-10

說明

         對於控制檔案我們可以手trace出備份，利用trace檔案指令碼重新建立控制檔案從而恢復資料庫。

 

從trace檔案中恢復

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

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

或者
SQL>alter database backup controlfile to '/home/oracle/control.ctl';

 

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        恢復控制檔案

         編輯剛剛備份產生的control.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

         這裡取noresetlogs段，因為日誌檔案並沒有損壞，不必重做日誌檔案。

         執行完成後資料庫自動啟動到mount狀態，啟動資料庫到open狀態

SQL>alter database open;

如果我們之前沒有一致性停庫，這裡我們需要恢復資料庫

SQL>recover database;

恢復完成在啟動到open狀態。

 

         如果，在備份資料庫控制檔案時使用的備份的二進位制，則這裡需要利用RMAN來恢復

RMAN>set dbid=1324770912

RMAN>restore controlfile from ‘/home/oracle/control.ctl’;

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

RMAN>alter database mount;

RMAN> alter database open;

 

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

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

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

RMAN-03002: failure of alter db command at 03/08/2013 17:05:46

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

 

RMAN> alter database open resetlogs;

 

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

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

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

RMAN-03002: failure of alter db command at 03/08/2013 17:05:50

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/oracle/u01/app/oracle/oradata/orcl/system01.dbf'

 

RMAN> restore database;

 

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

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

 

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

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

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

RMAN-03002: failure of restore command at 03/08/2013 17:06:08

RMAN-06026: some targets not found - aborting restore

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

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

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

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

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

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

 

RMAN> recover database;

 

Starting recover at 08-MAR-13

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;

 

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

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

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

RMAN-03002: failure of alter db command at 03/08/2013 17:06:25

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

 

RMAN> alter database open resetlogs;

 

database opened

                          

9         檢查資料

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

        ID

----------

         1

         2

         3

         4

         5

         6

         資料庫恢復完成.

 

注意：這樣利用trace檔案指令碼恢復完成控制檔案後需要手動新增temp表空間的資料檔案。

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

相關文章