SNAPSHOT CONTROLFILE有什么用途

126次阅读

共计 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 小编将为大家输出更多高质量的实用文章！

正文完