SNAPSHOT CONTROLFILE有什么用途

159次閱讀

共計 8877 個字符，預計需要花費 23 分鐘才能閱讀完成。

本篇內容介紹了“SNAPSHOT CONTROLFILE 有什么用途”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

根據我的理解：
snapshot control file 只有在同步 catalog 的時候用到或者在控制文件備份的時候用到
（在備份控制文件之前會備份一個 SNAPSHOT CONTROLFILE）
———————————————————-
SNAPSHOT CONTROLFILE 的用途，顧名思義，應該是用來恢復控制文件的，下面是我的測試結果：
結論是：
1.SNAPSHOT CONTROLFILE 不能直接 copy 成 control file，如果數據庫已經 shutdown
2. 可以通過 rman，將 SNAPSHOT CONTROLFILE 恢復成控制文件
3. 如果數據庫沒有 shutdown，可以直接 copy SNAPSHOT CONTROLFILE 成控制文件，但仍需要恢復（因為他是舊的）

下面是我的測試步驟：
—————————————
查看 snapshot controlfile 的備份設置:
RMAN SHOW SNAPSHOT CONTROLFILE NAME;

RMAN configuration parameters are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO /u01/ora9a/product/9.2/dbs/snapcf_tt9a.f # default

RMAN

下面模擬控制文件丟失的恢復
$ ls
control01.ctl cwmlite01.dbf indx01.dbf redo02.log temp01.dbf users01.dbf
control02.ctl drsys01.dbf odm01.dbf redo03.log tools01.dbf xdb01.dbf
control03.ctl example01.dbf redo01.log system01.dbf undotbs01.dbf
$ rm cont* – 刪除控制文件
$ ls
cwmlite01.dbf example01.dbf odm01.dbf redo02.log system01.dbf tools01.dbf users01.dbf
drsys01.dbf indx01.dbf redo01.log redo03.log temp01.dbf undotbs01.dbf xdb01.dbf
$
在控制文件被刪除后，數據庫還能正常操作：
SQL insert into test(no) values(4);

1 row created.

SQL commit;

Commit complete.

SQL alter system switch logfile;

System altered.

SQL alter system checkpoint;

System altered.

SQL /

System altered.

SQL
SQL select no,to_char(vdate, yyyy-mm-dd hh34:mi:ss) from test;

NO TO_CHAR(VDATE, YYYY-MM-DDHH24:MI:SS)
———- ———————————————————
1 2009-02-24 10:16:09
2 2009-02-24 10:25:30
3 2009-02-24 10:26:56
4 2009-02-24 10:30:59

SQL
重新啟動數據庫：
SQL connect /as sysdba
Connected.
SQL shutdown immediate
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: /u01/ora9a/oradata/tt9a/control01.ctl
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
沒有了控制文件了，shutdown immediate 出錯
SQL
SQL shutdown abort;
ORACLE instance shut down.
SQL startup
ORACLE instance started.

Total System Global Area 320569400 bytes
Fixed Size 741432 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

SQL
沒有控制文件，數據庫不能 mount，現在把 SNAPSHOT CONTROLFILE copy 到對應目錄
$ cp /u01/ora9a/product/9.2/dbs/snapcf_tt9a.f /u01/ora9a/oradata/tt9a/control01.ctl
$ cp /u01/ora9a/oradata/tt9a/control01.ctl /u01/ora9a/oradata/tt9a/control0t.ctl
$ cp /u01/ora9a/oradata/tt9a/control01.ctl /u01/ora9a/oradata/tt9a/control03.ctl
$ ls -lrt /u01/ora9a/oradata/tt9a/contr*
-rw-r—– 1 ora9a dba 1531904 Feb 24 10:36 /u01/ora9a/oradata/tt9a/control01.ctl
-rw-r—– 1 ora9a dba 1531904 Feb 24 10:36 /u01/ora9a/oradata/tt9a/control0t.ctl
-rw-r—– 1 ora9a dba 1531904 Feb 24 10:36 /u01/ora9a/oradata/tt9a/control03.ctl
$
SQL startup
ORACLE instance started.

Total System Global Area 320569400 bytes
Fixed Size 741432 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
照樣不能 mount 數據庫
—————————————-
嘗試用 rman 恢復控制文件:
$ rman target /

Recovery Manager: Release 9.2.0.1.0 – 64bit Production

connected to target database (not started)

RMAN startup nomount;

Oracle instance started

Total System Global Area 320569400 bytes

Fixed Size 741432 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes

RMAN restore controlfile from /u01/ora9a/product/9.2/dbs/snapcf_tt9a.f

Starting restore at 24-FEB-09

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: copied controlfile copy
replicating controlfile
input filename=/u01/ora9a/oradata/tt9a/control01.ctl
output filename=/u01/ora9a/oradata/tt9a/control02.ctl
output filename=/u01/ora9a/oradata/tt9a/control03.ctl
Finished restore at 24-FEB-09

RMAN alter database mount;

database mounted

RMAN
ok, 用 rman 可以進行恢復，讓數據 mount 起來
恢復數據庫
RMAN recover database;

Starting recover at 24-FEB-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /u01/ora9a/oradata/tt9a/redo01.log
archive log thread 1 sequence 2 is already on disk as file /u01/ora9a/oradata/tt9a/redo02.log
archive log thread 1 sequence 3 is already on disk as file /u01/ora9a/oradata/tt9a/redo03.log
archive log filename=/u01/ora9a/oradata/tt9a/redo01.log thread=1 sequence=0
archive log filename=/u01/ora9a/oradata/tt9a/redo02.log thread=1 sequence=2
archive log filename=/u01/ora9a/oradata/tt9a/redo03.log thread=1 sequence=3
media recovery complete
Finished recover at 24-FEB-09

RMAN alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/24/2009 10:47:36
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN alter database open resetlogs;

database opened

RMAN
查看先前 insert 的數據：
SQL select no,to_char(vdate, yyyy-mm-dd hh34:mi:ss) from test;

NO TO_CHAR(VDATE, YYYY-MM-DDHH24:MI:SS)
———- ———————————————————
1 2009-02-24 10:16:09
2 2009-02-24 10:25:30
3 2009-02-24 10:26:56
4 2009-02-24 10:30:59

SQL
數據仍在，恢復成功!

=============================
下面仍是在數據庫正常運行情況下，將控制文件刪除，仍測試直接 copy SNAPSHOT CONTROLFILE 的恢復方式：
SQL insert into test(no) values(5);

1 row created.

SQL commit;

Commit complete.

SQL
SQL !
$ pwd
/u01/ora9a/oradata/tt9a
$ ls
control01.ctl control0t.ctl example01.dbf redo01.log system01.dbf undotbs01.dbf
control02.ctl cwmlite01.dbf indx01.dbf redo02.log temp01.dbf users01.dbf
control03.ctl drsys01.dbf odm01.dbf redo03.log tools01.dbf xdb01.dbf
$ rm contr*
$ ls
cwmlite01.dbf example01.dbf odm01.dbf redo02.log system01.dbf tools01.dbf users01.dbf
drsys01.dbf indx01.dbf redo01.log redo03.log temp01.dbf undotbs01.dbf xdb01.dbf
$
$ exit

SQL select * from v$controlfile;

STATUS
———————
NAME
——————————————————————————–

/u01/ora9a/oradata/tt9a/control01.ctl

/u01/ora9a/oradata/tt9a/control02.ctl

/u01/ora9a/oradata/tt9a/control03.ctl

SQL select dbid from v$database;
select dbid from v$database
*
ERROR at line 1:
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: /u01/ora9a/oradata/tt9a/control01.ctl
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

SQL
沒有控制文件，查詢失敗
現在將 SNAPSHOT CONTROLFILE copy 到對應目錄，嘗試直接恢復成控制文件:
SQL !
$ pwd
/u01/ora9a/oradata/tt9a
$ cp /u01/ora9a/product/9.2/dbs/snapcf_tt9a.f control01.ctl
$ cp control01.ctl control02.ctl
$ cp control01.ctl control03.ctl
$ ls -lrt cont*
-rw-r—– 1 ora9a dba 1531904 Feb 24 10:55 control01.ctl
-rw-r—– 1 ora9a dba 1531904 Feb 24 10:55 control02.ctl
-rw-r—– 1 ora9a dba 1531904 Feb 24 10:55 control03.ctl
$
注意，此時不要起停數據庫，否則這樣恢復的控制文件仍不可用
嘗試查詢 control file 信息
SQL select dbid from v$database;

DBID
———-
3459515476

SQL
ok, 成功
查詢數據文件頭信息
SQL select CHECKPOINT_CHANGE# from v$datafile_header;
select CHECKPOINT_CHANGE# from v$datafile_header
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2170], [], [], [], [], [], [], []

SQL
失敗
現在重新啟動數據庫，看看是否可以到 mount 狀態：
SQL shutdown immediate
ORA-00600: internal error code, arguments: [kccchb_1], [356], [353], [0], [353], [0], [], []
SQL shutdown abort;
ORACLE instance shut down.
SQL
SQL startup
ORACLE instance started.

Total System Global Area 320569400 bytes
Fixed Size 741432 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL
ok，此時數據庫可以 mount 起來了

SQL
SQL alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: /u01/ora9a/oradata/tt9a/system01.dbf
提示需要恢復
SQL recover database using backup controlfile;
ORA-00279: change 349227 generated at 02/24/2009 10:49:58 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_1.dbf
ORA-00280: change 349227 for thread 1 is in sequence #1

Specify log: {RET =suggested | filename | AUTO | CANCEL}
AUTO
ORA-00328: archived log ends at change 347581, need later change 349227
ORA-00334: archived log: /u01/arch/1_1.dbf

SQL recover database using backup controlfile;
ORA-00279: change 349227 generated at 02/24/2009 10:49:58 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_1.dbf
ORA-00280: change 349227 for thread 1 is in sequence #1

Specify log: {RET =suggested | filename | AUTO | CANCEL}
/u01/ora9a/oradata/tt9a/redo01.log
Log applied.
Media recovery complete.

SQL alter database open resetlogs;

Database altered.

SQL
恢復成功
SQL select no,to_char(vdate, yyyy-mm-dd hh34:mi:ss) from test order by vdate;

NO TO_CHAR(VDATE, YYYY-MM-DDHH24:MI:SS)
———- ———————————————————
1 2009-02-24 10:16:09
2 2009-02-24 10:25:30
3 2009-02-24 10:26:56
4 2009-02-24 10:30:59
5 2009-02-24 10:51:01

SQL
ok, 新插入的數據也在。

“SNAPSHOT CONTROLFILE 有什么用途”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！

正文完