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

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

實驗2 ：全備<老>--備份控制檔案<次新>--新建表空間andy--日誌檔案<新>

分析說明：整個恢復過程中datafile結構有了變化，變化發生在備份控制檔案之後，新增了表空間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> alter database backup controlfile to '/home/oracle/coldbak/ctl01.bak';

Database altered.

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

Tablespace created.

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

Table created.

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from andy.andy;

ID
----------
100

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 1 52428800 512 1 NO CURRENT
2 1 0 52428800 512 1 YES UNUSED
3 1 0 52428800 512 1 YES UNUSED

2）模擬新建資料檔案損壞

[oracle@11g orcl]$ rm -rf andy01.dbf

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from andy.andy;
select * from andy.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;

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/

5）啟動資料庫

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> select file#,checkpoint_change#,name from v$datafile;

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

6 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

6 rows selected.

6）使用備份控制檔案恢復資料庫

SQL> recover database using backup controlfile;
ORA-00279: change 2036793 generated at 12/12/2014 05:56:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_1_866095003.dbf
ORA-00280: change 2036793 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oradata/orcl/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 7: '/home/oracle/app/oradata/orcl/andy01.dbf'

ORA-01112: media recovery not started

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

FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ ------------------------------------------------------------
1 2036796 /home/oracle/app/oradata/orcl/system01.dbf
2 2036796 /home/oracle/app/oradata/orcl/sysaux01.dbf
3 2036796 /home/oracle/app/oradata/orcl/undotbs01.dbf
4 2036796 /home/oracle/app/oradata/orcl/users01.dbf
5 2036796 /home/oracle/app/oradata/orcl/tbtb01.dbf
6 2036796 /home/oracle/app/oradata/orcl/ogg01.dbf
7 2039190 /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED0
0007 <-老控制檔案不知道之後的andy01.dbf

7 rows selected.

說明：老控制檔案不知道之後的andy01.dbf

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

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

7 rows selected.

7）重新命名資料檔案

SQL> alter database create datafile '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007' as '/home/oracle/app/oradata/orcl/andy01.dbf';

Database altered.

說明：上面自動完成了兩個動作1）加了一個資料檔案andy01.dbf，2）重新命名控制檔案UNNAMED00007為andy01.dbf

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

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

7 rows selected.

SQL> recover database using backup controlfile;
ORA-00279: change 2039190 generated at 12/12/2014 06:24:49 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_1_866095003.dbf
ORA-00280: change 2039190 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oradata/orcl/redo01.log
Log applied.
Media recovery complete.

8）resetlogs開啟資料庫

SQL> alter database open resetlogs;

Database altered.

9)驗證

SQL> select * from andy.andy;

ID
----------
100

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

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

相關文章