控制檔案的恢復方法（三）

控制檔案的通常恢復方法有：
1、從控制檔案自動或二進位制備份中恢復

2、從控制檔案快照備份中恢復

3、控制檔案trace檔案備份中恢復

4、Oracle自動生成的控制檔案指令碼恢復

如下對相關的步驟及語法進行介紹：

3、控制檔案trace檔案備份中恢復

對資料庫進行一些改動並備份控制檔案的trace備份：

SQL> insert into t select * from dba_users;

8 rows created.

SQL> alter database backup controlfile to trace as '/opt/oracle/backups/control.txt';

Database altered.

SQL> insert into t select * from dba_users;

8 rows created.

SQL> alter system switch logfile;

System altered.

SQL>

[@more@]

在另外一個視窗中將控制檔案透過OS的mv 或rm命令進行移動或刪除；

[oracle@dbserv test]$ mv control01.ctl control02.ctl control03.ctl /opt/oracle/backups/

[oracle@dbserv test]$ ls

redo01.log redo02.log redo03.log sqlnet.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

關閉並重新啟動資料庫

SQL> shutdown immediate;

ORA-01097: cannot shutdown while in a transaction - commit or rollback first

SQL> commit;

Commit complete.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 1220432 bytes

Variable Size 486539440 bytes

Database Buffers 1644167168 bytes

Redo Buffers 15556608 bytes

ORA-00205: ?????????, ??????, ???????

將生成的控制檔案trace文字備份中獲取相關的恢復語句：

[oracle@xjtvpay ~]$ cd /opt/oracle/backups/

[oracle@xjtvpay backups]$ ls

control01.ctl control02.ctl control03.ctl control.txt

[oracle@xjtvpay backups]$ more control.txt

省略注視內容...

-- Set #1. NORESETLOGS case

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/opt/oracle/oradata/test/redo01.log' SIZE 50M,

GROUP 2 '/opt/oracle/oradata/test/redo02.log' SIZE 50M,

GROUP 3 '/opt/oracle/oradata/test/redo03.log' SIZE 50M

DATAFILE

'/opt/oracle/oradata/test/system01.dbf',

'/opt/oracle/oradata/test/undotbs01.dbf',

'/opt/oracle/oradata/test/sysaux01.dbf',

'/opt/oracle/oradata/test/users01.dbf'

CHARACTER SET ZHS16GBK

;

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/test/temp01.dbf'

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- Set #2. RESETLOGS case

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/opt/oracle/oradata/test/redo01.log' SIZE 50M,

GROUP 2 '/opt/oracle/oradata/test/redo02.log' SIZE 50M,

GROUP 3 '/opt/oracle/oradata/test/redo03.log' SIZE 50M

DATAFILE

'/opt/oracle/oradata/test/system01.dbf',

'/opt/oracle/oradata/test/undotbs01.dbf',

'/opt/oracle/oradata/test/sysaux01.dbf',

'/opt/oracle/oradata/test/users01.dbf'

CHARACTER SET ZHS16GBK

;

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/test/temp01.dbf'

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

根據以上內容介紹，將其中的部分複製到文字檔案後，在SQL PLUS行呼叫並執行即可。因我們的日誌檔案都健全，因此執行第一個NORESETLOGS case對應的內容：

[oracle@xjtvpay backups]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 15 21:38:23 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @creatconf.sql;

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 1220432 bytes

Variable Size 486539440 bytes

Database Buffers 1644167168 bytes

Redo Buffers 15556608 bytes

Control file created.

PL/SQL procedure successfully completed.

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

System altered.

Database altered.

Tablespace altered.

SQL> select count(*) from t;

COUNT(*)

----------

32

SQL>