為什麼會使用備份的控制檔案? 實際工作中主要有兩種情況：

第一種：當前控制檔案全部損壞，而資料檔案備份，控制檔案備份及當前日誌處於不同SCN版本，它們之間又增加過表空間（資料檔案）。
第二種：當前控制檔案沒有損壞，但想要恢復被刪除的表空間。

實驗1：有所有資料檔案備份（老）------(新建表空間andy)-----備份控制檔案（次新）------日誌檔案（新）

環境：當前控制檔案損壞，資料檔案損壞，有全備但之後增加了表空間，並備份了配套的控制檔案，利用備份控制檔案備份。

分析：新建表空間資料檔案損壞, 全備裡沒有該資料檔案的備份及控制檔案描述，當前控制檔案又丟失，只能用備份的控制檔案恢復。

1）準備環境

--生成要備份的資料檔案的命令
SQL> select 'ho cp ' || name || ' /home/oracle/coldbak' from v$datafile;

'HOCP'||NAME||'/HOME/ORACLE/COLDBAK'
-------------------------------------------------------------------------
ho cp /home/oracle/app/oradata/orcl/system01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/sysaux01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/undotbs01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/users01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/tbtb01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/ogg01.dbf /home/oracle/coldbak

6 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> ho cp /home/oracle/app/oradata/orcl/control01.ctl /home/oracle/coldbak
ho cp /home/oracle/app/flash_recovery_area/orcl/control02.ctl /home/oracle/coldbak
SQL> ho cp /home/oracle/app/oradata/orcl/system01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/sysaux01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/undotbs01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/users01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/tbtb01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/ogg01.dbf /home/oracle/coldbak

SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database Buffers 331350016 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 LZY YES NO YES
7 GOLDGATE YES NO YES

7 rows selected.

SQL> create tablespace andy datafile '/home/oracle/app/oradata/orcl/andy01.dbf' size 1m;

Tablespace created.

SQL> select * from andy.andy;
select * from andy.andy
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table andy.andy(id int) tablespace andy;

Table created.

SQL> insert into andy.andy values (1) ;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from andy.andy;

ID
----------
1
SQL> alter system switch logfile;

-- 備份控制檔案

SQL> alter database backup controlfile to '/home/oracle/coldbak/ctl01.bak';

Database altered.

2）模擬andy01.dbf損壞

SQL> ho rm -rf /home/oracle/app/oradata/orcl/andy01.dbf

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from andy;
select * from andy
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/home/oracle/app/oradata/orcl/andy01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

3)關閉資料庫

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

4)恢復所有資料檔案備份，準備做不完全恢復
[oracle@11g orcl]$ rm -rf *.dbf
[oracle@11g orcl]rm -rf /home/oracle/app/oradata/orcl/control01.ctl
[oracle@11g orcl]$ rm -rf /home/oracle/app/flash_recovery_area/orcl/control02.ctl
[oracle@11g coldbak]$ cp ctl01.bak /home/oracle/app/oradata/orcl/control01.ctl
[oracle@11g coldbak]$ cp ctl01.bak /home/oracle/app/flash_recovery_area/orcl/control02.ctl
[oracle@11g coldbak]$ cp *.dbf /home/oracle/app/oradata/orcl/

//SQL> alter database create datafile '/home/oracle/app/oradata/orcl/andy01.dbf';

Database altered.

SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database Buffers 331350016 bytes
Redo Buffers 5554176 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> col name for a50;
SQL> select file#,checkpoint_change#,name from v$datafile;

FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
1 2036303 /home/oracle/app/oradata/orcl/system01.dbf
2 2036303 /home/oracle/app/oradata/orcl/sysaux01.dbf
3 2036303 /home/oracle/app/oradata/orcl/undotbs01.dbf
4 2036303 /home/oracle/app/oradata/orcl/users01.dbf
5 2036303 /home/oracle/app/oradata/orcl/tbtb01.dbf
6 2036303 /home/oracle/app/oradata/orcl/ogg01.dbf
7 2036424 /home/oracle/app/oradata/orcl/andy01.dbf

7 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2035583
2 2035583
3 2035583
4 2035583
5 2035583
6 2035583
7 0

7 rows selected.
說明：
a）file7 在控制檔案裡記錄是andy01.dbf,而與之對應的資料檔案3是不存在的
b）備份的資料備份的scn比控制檔案scn的還老。

5)使用備份控制檔案恢復

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 7: '/home/oracle/app/oradata/orcl/andy01.dbf'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/oracle/app/oradata/orcl/andy01.dbf'

說明：
此錯是因為老備份裡沒有andy表空間，但只要控制檔案裡記錄了andy就好辦，方法是建一個datafile的空檔案，而其中內容可由日誌檔案recover(前滾)時填補出來。

SQL> alter database create datafile '/home/oracle/app/oradata/orcl/andy01.dbf';

Database altered.

--再次使用備份控制檔案恢復
SQL> recover database using backup controlfile;
ORA-00279: change 2035583 generated at 12/12/2014 04:53:45 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_1_866090437.dbf
ORA-00280: change 2035583 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2036542 generated at 12/12/2014 05:11:01 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_2_866090437.dbf
ORA-00280: change 2036542 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/archivelog/1_1_866090437.dbf' no longer
needed for this recovery

ORA-00308: cannot open archived log '/home/oracle/archivelog/1_2_866090437.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

說明：
//archive日誌前滾結束了，但當前日誌裡還有資訊需要恢復
//注意: 對於這個例子來說，一定要看清提示：如果提示的不是歸檔的日誌（是當前日誌），則要直接要輸入filename 不能輸入auto，否則open時會失敗。

--再次做恢復
SQL> recover database using backup controlfile;
ORA-00279: change 2036542 generated at 12/12/2014 05:11:01 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_2_866090437.dbf
ORA-00280: change 2036542 for thread 1 is in sequence #2

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oradata/orcl/redo02.log ## 把current日誌給它
Log applied.
Media recovery complete.

6）resetlogs開啟資料庫

SQL> alter database open resetlogs;

Database altered.

7）驗證

SQL> select * from andy.andy;

ID
----------
1

OK，結束。轉載請標明出處。

不完全恢復（資料檔案備份--新建表空間--控制檔案備份--日誌歸檔檔案）

相關文章