【管理篇備份恢復】rman恢復測試（二）控制檔案恢復（一）

測試控制檔案丟失的恢復

先來看看控制檔案中記錄的內容和控制檔案的作用：

From 9i Administrator Guide--&gtManaging Control Files：

控制檔案是用於描述物理資料庫當前狀態的二進位制檔案。

丟失控制檔案則資料庫無法使用，必須加以修復。(從下面的第一個例子可以看出，丟失一個控制檔案並不會立即crash down，後面也將測試全部丟失的情況)

資料庫載入階段需要根據控制檔案中的資訊判斷資料庫的狀態。

(如果控制檔案丟失中有任何一個丟失或者全部丟失則無法載入資料庫，後面示例)

控制檔案很重要，所以應該設定多份拷貝（通常為3份，具體可以參見本例中v$controlfile檢視的內容）。

控制檔案的內容：

1，資料庫名字和識別符號(database name,id)

2，資料庫建立時間戳(database create timestamp)

(資料庫名稱，標識和建立時間在資料庫建立時寫入)

3，表空間名字（新增或刪除表空間時記錄）

4，資料檔案和redo日誌檔案的名字和位置

5，當前redo日誌檔案序列號（做log switch時記錄）

6，檢查點資訊（做checkpoint時記錄）

7，undo段的起始點和結束點

8，redo日誌歸檔資訊（日誌歸檔時記錄）

9，備份資訊（由RMAN負責維護）

資料檔案和重做日誌名稱和位置在增加，重新命名或者刪除的時候更新；

表空間資訊在增加或者刪除表空間的時候進行更新。

在測試之前，先看看是否有控制檔案的備份情況

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

45 Full 6.95M DISK 00:00:00 05-JUN-10

BP Key: 45 Status: AVAILABLE Compressed: NO Tag: TAG20100605T054843

Piece Name: /tpdata/database/flash_recovery_area/TEST1/autobackup/2010_06_05/o1_mf_s_720856123_60lx5vhq_.bkp

Control File Included: Ckp SCN: 6410727855245 Ckp time: 05-JUN-10

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

46 Full 6.95M DISK 00:00:00 06-JUN-10

BP Key: 46 Status: AVAILABLE Compressed: NO Tag: TAG20100606T014320

Piece Name: /tpdata/database/flash_recovery_area/TEST1/autobackup/2010_06_06/o1_mf_s_720927800_60o35rf6_.bkp

Control File Included: Ckp SCN: 6410727908582 Ckp time: 06-JUN-10

可以使用下列語句來備份控制檔案到trace檔案或者指定目錄下

SQL> alter database backup controlfile to trace;

Database altered.

SQL> alter database backup controlfile to '/tpdata/database/oradata/control.bak';

Database altered.

再看看controlfile相關的檢視：

SQL> select a.name from v$controlfile a;

NAME

--------------------------------------------------------------------------------

/tpdata/database/oradata/test1/control01.ctl

/tpdata/database/oradata/test1/control02.ctl

/tpdata/database/oradata/test1/control03.ctl

1，丟失一個當前控制檔案的情況

[oracle@limmTest test1]$ mv control03.ctl control03.bak

從控制檔案的內容和作用可以看出，丟失了一個控制檔案以後，資料庫並不會立即報錯，或者崩潰，可以看到示例仍然open，常用的查詢也是可以的，但是當需要讀或者寫控制檔案的時候資料庫會報錯，具體看看：

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> select * from dual;

D

-

X

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

COUNT(*)

----------

1100006

SQL> desc test.test

Name Null? Type

----------------------------------------- -------- ----------------------------

ID VARCHAR2(20)

MC VARCHAR2(60)

SQL> update test.test a set a.id = 1 where a.id = 1;

2 rows updated.

SQL> commit;

Commit complete.

使用控制檔案連rman的時候會報錯：

[oracle@limmTest ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 10 02:48:21 2010

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

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

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

RMAN-00554: initialization of internal recovery manager package failed

RMAN-06003: ORACLE error from target database:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/tpdata/database/oradata/test1/control03.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

手動歸檔時也會報錯：

SQL> alter system archive log all;

alter system archive log all

*

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/tpdata/database/oradata/test1/control03.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

手動checkpoint動作，並不報錯：

SQL> alter system checkpoint;

System altered.

而查詢當前的checkpoint_change#報錯：

SQL> select CHECKPOINT_CHANGE# from v$database;

select CHECKPOINT_CHANGE# from v$database

*

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/tpdata/database/oradata/test1/control03.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

有點疑惑，簡單看看checkpoint的原理和作用：

checkpoint發生在DBWR程式將SGA的buffer緩衝內的已修改的buffer寫入資料庫資料檔案。資料檔案頭部同時更新最近的checkpoint scn，

無論資料檔案中是否有修改的blocks。

checkpoint發生在每次切換redolog之後（並不是切換過程中）。

可以設定引數 LOG_CHECKPOINTS_TO_ALERT=TRUE，從資料庫的ALERT log中觀察checkpoint開始和結束的時間

checkpoint 可以使用alter system checkpoint 手動強制執行。

CKPT程式（oracle checkpoint process）在checkopoint發生的時候標記資料檔案和控制檔案的時間。

v$sesstat或者v$mystat中DBWR checkpoints對應的value就是等待完成的checkpoint數量，如以下查詢：

SQL> select a.SID, a.STATISTIC#, a.VALUE, b.NAME, b.CLASS

2 from v$mystat a, v$statname b

3 where a.STATISTIC# = b.STATISTIC#

4 and b.NAME like '%checkpoint%';

SID STATISTIC# VALUE NAME CLASS

---------- ---------- ---------- ------------------------------------------------ ----------

150 67 0 physical writes non checkpoint 8

150 69 0 DBWR checkpoint buffers written 8

150 70 0 DBWR thread checkpoint buffers written 8

150 71 0 DBWR tablespace checkpoint buffers written 8

150 72 0 DBWR parallel query checkpoint buffers written 8

150 79 0 DBWR checkpoints 8

150 166 0 background checkpoints started 8

150 167 0 background checkpoints completed 8

8 rows selected

較為詳細的介紹：

A checkpoint performs the following three operations:

Every dirty block in the buffer cache is written to the data files.

That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.

It's the DBWR that writes all modified databaseblocks back to the datafiles.

The latest SCN is written (updated) into the datafile header.

The latest SCN is also written to the controlfiles.

The update of the datafile headers and the control files is done by the LGWR(CKPT if CKPT is enabled).

As of version 8.0, CKPT is enabled by default.

Events that trigger a checkpoint

The following events trigger a checkpoint.

Redo log switch

LOG_CHECKPOINT_TIMEOUT has expired

LOG_CHECKPOINT_INTERVAL has been reached

DBA requires so (alter system checkpoint)

Additionally, if a tablespace is hot backuped, a checkpoint for the tablespace in question is taking place.

While redo log switches cause a checkpoint, checkpoints don't cause a log switch.

Time and SCN of last checkpoint

The date and time of the last checkpoint can be retrieved through checkpoint_time

in v$datafile_header The SCN of the last checkpoint can be found in v$database.checkpoint_change#.

Size of redo log

If the size of the redo log is to small, the performance of the checkpoint will not be optimal.

This is the case if the alert.log contains messages like Thread .. cannot allocate new log....

alter system checkpoint;

將觸發oracle的ckpt程式進行checkpoint動作，將data buffer中的髒塊（已經寫在redo裡面，但是沒有寫到datafile裡的）的內容

寫到datafile裡面，並釋放佔用的空間（dbwn程式完成），並修改datafile的scn。

檢視資料檔案頭的scn和checkpoint也會報錯：

SQL> select * from V$DATAFILE_HEADER a where a.FILE# = 6;

select * from V$DATAFILE_HEADER a where a.FILE# = 6

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/tpdata/database/oradata/test1/control03.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

切換redo log是可以的：

SQL> alter system switch logfile;

System altered.

建立資料檔案和表空間的語句，要寫控制檔案，會報錯：

SQL> create tablespace readwrite datafile '/tpdata/database/oradata/test1/readwrite' size 1M ;

create tablespace readwrite datafile '/tpdata/database/oradata/test1/readwrite' size 1M

*

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/tpdata/database/oradata/test1/control03.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL>

SQL> alter database create datafile '/tpdata/database/oradata/test1/readwrite';

alter database create datafile '/tpdata/database/oradata/test1/readwrite'

*

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/tpdata/database/oradata/test1/control03.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL>

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

【管理篇備份恢復】rman恢復測試（二） 控制檔案恢復（一）

相關文章

【管理篇備份恢復】rman恢復測試（二）控制檔案恢復（一）