Oracle控制文件的备份、恢复以及多路复用
文章转载于http://blog.itpub.net/29436907/viewspace-1109948/,
前言
今天Frank给大家带来有关Oracle控制文件的技术分享。不积跬步无以至千里,不积小流无以成江海。Oracle技术博大精深,以Frank一个人的力量,那无异于蜉蝣于天地,沧海之一粟。在此,笔者抛砖引玉,希望读者能够与Frank形成良好的互动,之间共同进步。闲言少叙,书归正传,马上开始今天的讨论话题。
一、控制文件概要描述
既然今天的主题是控制文件,那么首先Frank先带大家来简要回顾一下控制文件的相关知识点。控制文件在默认情况下,一般和数据文件以及日志文件等位于同一个目录(当然,不放在这里也是没有问题的……看个人喜好),具体查看的sql语句是:
SYS@ENMOEDU > select name from v$controlfile;
NAME
------------------------------
/u01/app/oracle/oradata/ENMOEDU/control01.ctl
/u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl
从这里就已经清晰的看到我控制文件的路径以及名称了,还有,细心的读者应该发现了,Frank是一个很小心谨慎的人,所以呢,我的控制文件是两份。这样做的好处是,万一我其中的一个控制文件被误删了或者损坏了,我还有另外一个控制文件保证我数据库的正常运行以及启动(不小心把多路复用技术给提前引入了……稍后会有更详细的配置以及解析)。
控制文件的作用我想就毋庸赘言了吧?上至九十老妪,下到五岁孩童,对于控制文件的重要性都能如数家珍般的娓娓道来。控制文件中有数据库以及在线重做日志的位置以及众多重要的信息,丢失或者损坏控制文件,数据库将无法正常启动和运行。既然控制文件这么重要,那么我们该怎么对它进行备份和恢复呢?
二、控制文件的备份与恢复
(1)基于用户管理的备份和恢复
首先进入Oracle的trace目录
[oracle@ENMOEDU trace]$ cd /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace
用tail命令来打开alert_ENMOEDU.log文件
[oracle@ENMOEDU trace]$ tail -100f alert_ENMOEDU.log
此时打开另一窗口,执行如下命令
SYS@ENMOEDU > alter database backup controlfile to trace;
Database altered.
我们可以在alert.ENMOEDU.log看到如下信息
Mon Mar 10 15:22:20 2014
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_18205.trc
Completed: alter database backup controlfile to trace
根据上述信息,我们去查看提示的文件
[oracle@ENMOEDU ~]$ cat /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_18205.trc
在浩如烟海的信息中,我们可以找到创建控制文件的命令
CREATE CONTROLFILE REUSE DATABASE "ENMOEDU" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ENMOEDU/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ENMOEDU/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ENMOEDU/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ENMOEDU/system01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/users01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/example01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/test01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/test02.dbf'
CHARACTER SET AL32UTF8;
稍后我们就可以用这些命令来创建控制文件了。下面来模拟控制文件丢失和损坏的实验环境:
[root@ENMOEDU ~]# rm -rf /u01/app/oracle/oradata/ENMOEDU/control01.ctl
[root@ENMOEDU ~]# rm -rf /u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl
笔者将两个控制文件都删除了,很彻底~然后尝试启动数据库。
[oracle@ENMOEDU ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 10 15:47:54 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
ORA-00205: error in identifying control file, check alert log for more info
可以看出,数据库已经无法正常启动了。那么我们就将数据库启动到nomount状态下进行恢复。
SYS@ENMOEDU > shutdown abort
ORACLE instance shut down.
SYS@ENMOEDU > startup nomount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
SYS@ENMOEDU > CREATE CONTROLFILE REUSE DATABASE "ENMOEDU" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/ENMOEDU/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/ENMOEDU/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/ENMOEDU/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/ENMOEDU/system01.dbf',
14 '/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf',
15 '/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf',
16 '/u01/app/oracle/oradata/ENMOEDU/users01.dbf',
17 '/u01/app/oracle/oradata/ENMOEDU/example01.dbf',
18 '/u01/app/oracle/oradata/ENMOEDU/test01.dbf',
19 '/u01/app/oracle/oradata/ENMOEDU/test02.dbf'
20 CHARACTER SET AL32UTF8;
Control file created.
尝试开启数据库
SYS@ENMOEDU > alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
提示错误。按照提示来开启数据库:
SYS@ENMOEDU > alter database open resetlogs;
Database altered.
数据库已经正常启动了。
上述情况属于非常乐观且极易恢复的模拟实验现场,但是在实际情况中,环境却险要恶劣的多。往往控制文件丢失后,用户还会进行大量的操作,比如创建表空间等等,这些使得控制文件的恢复要困难许多。路漫漫其修远兮,吾将上下而求索,Frank对待技术的渴望永无止境。下面我就给大家介绍一下如何在备份的控制文件过久的情况下恢复控制文件。
首先,我们先备份一下我们的控制文件。由于Frank采用了多路复用,所以看起来是不是应该把两个控制文件都备份一下呢?按照国际惯例,一般作者向读者发出类似这种疑问句的时候,答案往往是否定的。但是,Frank作为一个走位忽上忽下意识飘忽不定的技术人员,所以,答案,仍然是不需要备份两份。由于两份控制文件是一模一样的,所以备份一份即可,只需记得在恢复的时候注意文件名字。
备份控制文件。在本文中,将控制文件备份到Oracle的家目录下,给控制文件一种家的温暖。
[oracle@ENMOEDU ENMOEDU]$ cp control01.ctl ~
创建表空间和文件,从而使现在的控制文件和备份文件产生差异。
SYS@ENMOEDU > create tablespace frank datafile '/u01/app/oracle/oradata/ENMOEDU/frank01.dbf' size 100m;
Tablespace created.
查看一下当前在线日志的status,并切换日志,产生归档。
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 ACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 CURRENT
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 ACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
查看现在日志和数据文件的状态:
SYS@ENMOEDU > select GROUP#,SEQUENCE#,ARCHIVED,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# SEQUENCE# ARC FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- --- ------------- ------------
1 112 YES 1382686 1382699
2 113 YES 1382699 1382705
3 114 NO 1382705 2.8147E+14
SYS@ENMOEDU > select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1382686
2 1382686
3 1382686
4 1382686
5 1382686
6 1382686
7 1382686
8 1382686
8 rows selected.
删除控制文件:
rm -rf /u01/app/oracle/oradata/ENMOEDU/control01.dbf;
rm -rf /u01/app/oracle/fast_recovery_area/ENMOEDU/ ontrol02.dbf;
关机,重启,报错,就以nomount的形式开启数据库:
SYS@ENMOEDU > shutdown abort;
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@ENMOEDU > shutdown abort
ORACLE instance shut down.
SYS@ENMOEDU > startup nomount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
恢复控制文件:
[oracle@ENMOEDU ~]$ cp control01.ctl /u01/app/oracle/oradata/ENMOEDU/control01.ctl
[oracle@ENMOEDU ~]$ cp control01.ctl /u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl
将数据库启动到mount状态:
SYS@ENMOEDU > alter database mount;
Database altered.
查看一下当前文件的状态:
SYS@ENMOEDU > select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1371719
2 1371719
3 1371719
4 1371719
5 1371719
6 1371719
7 1371719
8 1382338
8 rows selected.
SYS@ENMOEDU > select status from v$datafile;
STATUS
-------
SYSTEM
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
RECOVER
8 rows selected.
明 显看出来datafile 8的scn号跟其他的不一样,因为它是在备份控制文件之后创建的。
利用archivelog来恢复控制文件:
SYS@ENMOEDU > recover database using backup controlfile;
ORA-00279: change 1382338 generated at 03/11/2014 20:23:02 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_106_9ky02vgf_.arc
ORA-00280: change 1382338 for thread 1 is in sequence #106
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_106_9ky02vgf_.arc
ORA-00279: change 1382640 generated at 03/11/2014 20:23:55 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_107_9ky03860_.arc
ORA-00280: change 1382640 for thread 1 is in sequence #107
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_106_9ky02vgf_.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_107_9ky03860_.arc
ORA-00279: change 1382655 generated at 03/11/2014 20:24:08 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_108_9ky03wyl_.arc
ORA-00280: change 1382655 for thread 1 is in sequence #108
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_107_9ky03860_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_108_9ky03wyl_.arc
ORA-00279: change 1382666 generated at 03/11/2014 20:24:28 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_109_9ky045yl_.arc
ORA-00280: change 1382666 for thread 1 is in sequence #109
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_108_9ky03wyl_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_109_9ky045yl_.arc
ORA-00279: change 1382673 generated at 03/11/2014 20:24:37 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_110_9ky04mo6_.arc
ORA-00280: change 1382673 for thread 1 is in sequence #110
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_109_9ky045yl_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_110_9ky04mo6_.arc
ORA-00279: change 1382680 generated at 03/11/2014 20:24:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_111_9ky04xrr_.arc
ORA-00280: change 1382680 for thread 1 is in sequence #111
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_110_9ky04mo6_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_111_9ky04xrr_.arc
ORA-00279: change 1382686 generated at 03/11/2014 20:25:01 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_112_9ky056n5_.arc
ORA-00280: change 1382686 for thread 1 is in sequence #112
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_111_9ky04xrr_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_112_9ky056n5_.arc
ORA-00279: change 1382699 generated at 03/11/2014 20:25:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_113_9ky05g7p_.arc
ORA-00280: change 1382699 for thread 1 is in sequence #113
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_112_9ky056n5_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_113_9ky05g7p_.arc
ORA-00279: change 1382705 generated at 03/11/2014 20:25:18 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_114_%u_.arc
ORA-00280: change 1382705 for thread 1 is in sequence #114
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_113_9ky05g7p_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_114_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_114_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
至此,归档文件已利用完毕。在开始阶段频繁切日志所酿下的一个不大不小的问题浮出水面,从而使我的恢复工作增加了一定量的复杂度。现在应该要使用到redo log日志了。因为提示说要用到的序列号为114,而在关机前所查看的日志状态中,redo03.log的序列号为114,所以指定redo03.log来继续恢复。
指定 日志恢复:
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ENMOEDU/redo03.log
Log applied.
Media recovery complete.
恢复成功,打开数据库:
SYS@ENMOEDU > alter database open resetlogs;
Database altered.
查看当前数据库数据文件状态:
S YS@ENMOEDU > select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1382865
2 1382865
3 1382865
4 1382865
5 1382865
6 1382865
7 1382865
8 1382865
8 rows selected.
至此,数据库控制文件恢复成功。
从上面可以看书,采用用户管理备份与恢复的方式来恢复我们的控制文件还是有一定的时间复杂度和空间复杂度的。那么到底有没有一个简单点得方法来解决这个问题吗?按照国际惯例,一般作者向读者发出类似这种疑问句的时候,答案往往是肯定的。但是,Frank作为一个走位忽上忽下意识飘忽不定的技术人员,所以,答案,仍然是肯定的。那就是RMAN。
(2)基于RMAN的备份和恢复
终于到了RMAN这一部分了。其实用RMAN恢复控制文件相对而言要简单的多。有如此利器而放置不用,实乃暴殄天物。下面就为大家带来用RMAN恢复控制文件的过程。
首先,用RMAN做一次全备份。
SYS@ENMOEDU > alter database begin backup;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup database plus archivelog;
备份的过程就不逐一显示在这里了。
SYS@ENMOEDU > alter database end backup;
至此,备份过程完成。
创建表空间和表文件,删除控制文件 。
SYS@ENMOEDU > create tablespace frank datafile '/u01/app/oracle/oradata/ENMOEDU/frank01' size 100m;
Tablespace created.
SYS@ENMOEDU > create table frank (x int,y int);
Table created.
SYS@ENMOEDU > insert into frank values(1,2);
1 row created.
SYS@ENMOEDU > insert into frank values(2,3);
1 row created.
SYS@ENMOEDU > select * from frank;
X Y
---------- ----------
1 2
2 3
SYS@ENMOEDU > commit;
[oracle@ENMOEDU ENMOEDU]$ rm -rf /u01/app/oracle/oradata/ENMOEDU/control01.dbf;
[oracle@ENMOEDU ENMOEDU]$ rm -rf /u01/app/oracle/fast_recovery_area/ENMOEDU/ ontrol02.dbf;
查看一下DBID,一会RMAN恢复将要使用这个参数。
SYS@ENMOEDU > select dbid from v$database;
DBID
----------
87396644
关闭数据库,以nomount的方式启动数据库。
SYS@ENMOEDU > shutdown abort
ORACLE instance shut down.
SYS@ENMOEDU > startup nomount
ORACLE instance started.
恢复控制文件 。
RMAN> set dbid=87396644;
executing command: SET DBID
RMAN> restore controlfile from autobackup;
restore自动提示的过程略去,为了节省空间和版面。
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> restore database;
RMAN> recover database;
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
至此,恢复过程结束。
检验一下恢复的效果如何。
SYS@ENMOEDU > select * from frank;
X Y
----------------
1 2
2 3
SYS@ENMOEDU > select name from v$datafile;
NAME
--------------------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/system01.dbf
/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf
/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf
/u01/app/oracle/oradata/ENMOEDU/users01.dbf
/u01/app/oracle/oradata/ENMOEDU/example01.dbf
/u01/app/oracle/oradata/ENMOEDU/test01.dbf
/u01/app/oracle/oradata/ENMOEDU/test02.dbf
/u01/app/oracle/oradata/ENMOEDU/frank01
由上我们可以看出恢复成功。
纵然用RMAN恢复控制文件比较简单粗暴,但是最好的防守就是进攻,与其坐以待毙等待控制文件出错被动恢复,倒不如采取主动出击多路复用控制文件。狡兔尚且知掘三窟,那么引入多路复用技术也就自然而然了。下面介绍多路复用技术。
三、控制文件的多路复用技术
(1)多路复用技术的配置
关于多路复用有两种配置方式,一种是在数据库nomount的方式下修改参数,另一种是直接修改参数文件。
将数据库启动到nomount,修改参数。
SYS@ENMOEDU > startup nomount
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
SYS@ENMOEDU > alter system set control_files=
'/u01/app/oracle/oradata/ENMOEDU/control01.ctl','/u01/app/oracle/oradata/control01.ctl' scope=spfile;
System altered.
将控制文件拷贝到另一个需要放置控制文件的路径。
[oracle@ENMOEDU dbs]$ cp /u01/app/oracle/oradata/ENMOEDU/control01.ctl /u01/app/oracle/oradata/control01.ctl
重启数据库,查看控制文件状态。
SYS@ENMOEDU > shutdown immediate
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.
SYS@ENMOEDU > select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/control01.ctl
/u01/app/oracle/oradata/control01.ctl
控制文件的多路复用成功。
第二种方法是直接修改init.ora文件。
首先创建pfile。
SYS@ENMOEDU > create pfile from spfile;
File created.
修改pfile。
[oracle@ENMOEDU dbs]$ vi initENMOEDU.ora
将控制文件那一项修改。
*.control_files='/u01/app/oracle/oradata/ENMOEDU/control01.ctl','/u01/app/oracle/oradata/control01.ctl'
创建spfile。
SYS@ENMOEDU > create pfile from spfile;
File created.
启动数据库,查看控制文件状态。
SYS@ENMOEDU > shutdown immediate
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.
SYS@ENMOEDU > select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/control01.ctl
/u01/app/oracle/oradata/control01.ctl
控制文件的多路复用成功 。
至此,控制文件的多路复用已经实现了。下面我们将做一个实验,来看看多路复用技术是怎么保障控制文件的正常使用的。
正常启动数据库。删除其中一份控制文件。
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.
[oracle@ENMOEDU oradata]$ rm -rf /u01/app/oracle/oradata/control01.ctl
正常关闭数据库,发现不能关闭,用shutdown abort 的方式关闭。
SYS@ENMOEDU > shutdown abort
ORACLE instance shut down.
以nomount的方式打开数据库。
SYS@ENMOEDU > startup nomount
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
修改参数,将已删除的控制文件从参数中删除。
SYS@ENMOEDU > alter system set control_files= '/u01/app/oracle/oradata/ENMOEDU/control01.ctl' scope=spfile;
System altered.
关闭数据库,重启。
SYS@ENMOEDU > shutdown immediate
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.
SYS@ENMOEDU > select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/control01.ctl
至此,数据库已经成功启动。
综上所述,本文总结了控制文件的用户管理备份和恢复、RMAN备份和恢复、多路复用的相关知识点。Frank才疏学浅,知识匮乏,只能尽绵薄之力总结自身所学,狂生之言,仅为笑谈。
一、控制文件概要描述
既然今天的主题是控制文件,那么首先Frank先带大家来简要回顾一下控制文件的相关知识点。控制文件在默认情况下,一般和数据文件以及日志文件等位于同一个目录(当然,不放在这里也是没有问题的……看个人喜好),具体查看的sql语句是:
SYS@ENMOEDU > select name from v$controlfile;
NAME
------------------------------
/u01/app/oracle/oradata/ENMOEDU/control01.ctl
/u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl
从这里就已经清晰的看到我控制文件的路径以及名称了,还有,细心的读者应该发现了,Frank是一个很小心谨慎的人,所以呢,我的控制文件是两份。这样做的好处是,万一我其中的一个控制文件被误删了或者损坏了,我还有另外一个控制文件保证我数据库的正常运行以及启动(不小心把多路复用技术给提前引入了……稍后会有更详细的配置以及解析)。
控制文件的作用我想就毋庸赘言了吧?上至九十老妪,下到五岁孩童,对于控制文件的重要性都能如数家珍般的娓娓道来。控制文件中有数据库以及在线重做日志的位置以及众多重要的信息,丢失或者损坏控制文件,数据库将无法正常启动和运行。既然控制文件这么重要,那么我们该怎么对它进行备份和恢复呢?
二、控制文件的备份与恢复
(1)基于用户管理的备份和恢复
首先进入Oracle的trace目录
[oracle@ENMOEDU trace]$ cd /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace
用tail命令来打开alert_ENMOEDU.log文件
[oracle@ENMOEDU trace]$ tail -100f alert_ENMOEDU.log
此时打开另一窗口,执行如下命令
SYS@ENMOEDU > alter database backup controlfile to trace;
Database altered.
我们可以在alert.ENMOEDU.log看到如下信息
Mon Mar 10 15:22:20 2014
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_18205.trc
Completed: alter database backup controlfile to trace
根据上述信息,我们去查看提示的文件
[oracle@ENMOEDU ~]$ cat /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_18205.trc
在浩如烟海的信息中,我们可以找到创建控制文件的命令
CREATE CONTROLFILE REUSE DATABASE "ENMOEDU" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ENMOEDU/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ENMOEDU/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ENMOEDU/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ENMOEDU/system01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/users01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/example01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/test01.dbf',
'/u01/app/oracle/oradata/ENMOEDU/test02.dbf'
CHARACTER SET AL32UTF8;
稍后我们就可以用这些命令来创建控制文件了。下面来模拟控制文件丢失和损坏的实验环境:
[root@ENMOEDU ~]# rm -rf /u01/app/oracle/oradata/ENMOEDU/control01.ctl
[root@ENMOEDU ~]# rm -rf /u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl
笔者将两个控制文件都删除了,很彻底~然后尝试启动数据库。
[oracle@ENMOEDU ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 10 15:47:54 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
ORA-00205: error in identifying control file, check alert log for more info
可以看出,数据库已经无法正常启动了。那么我们就将数据库启动到nomount状态下进行恢复。
SYS@ENMOEDU > shutdown abort
ORACLE instance shut down.
SYS@ENMOEDU > startup nomount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
SYS@ENMOEDU > CREATE CONTROLFILE REUSE DATABASE "ENMOEDU" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/ENMOEDU/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/ENMOEDU/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/ENMOEDU/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/ENMOEDU/system01.dbf',
14 '/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf',
15 '/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf',
16 '/u01/app/oracle/oradata/ENMOEDU/users01.dbf',
17 '/u01/app/oracle/oradata/ENMOEDU/example01.dbf',
18 '/u01/app/oracle/oradata/ENMOEDU/test01.dbf',
19 '/u01/app/oracle/oradata/ENMOEDU/test02.dbf'
20 CHARACTER SET AL32UTF8;
Control file created.
尝试开启数据库
SYS@ENMOEDU > alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
提示错误。按照提示来开启数据库:
SYS@ENMOEDU > alter database open resetlogs;
Database altered.
上述情况属于非常乐观且极易恢复的模拟实验现场,但是在实际情况中,环境却险要恶劣的多。往往控制文件丢失后,用户还会进行大量的操作,比如创建表空间等等,这些使得控制文件的恢复要困难许多。路漫漫其修远兮,吾将上下而求索,Frank对待技术的渴望永无止境。下面我就给大家介绍一下如何在备份的控制文件过久的情况下恢复控制文件。
首先,我们先备份一下我们的控制文件。由于Frank采用了多路复用,所以看起来是不是应该把两个控制文件都备份一下呢?按照国际惯例,一般作者向读者发出类似这种疑问句的时候,答案往往是否定的。但是,Frank作为一个走位忽上忽下意识飘忽不定的技术人员,所以,答案,仍然是不需要备份两份。由于两份控制文件是一模一样的,所以备份一份即可,只需记得在恢复的时候注意文件名字。
备份控制文件。在本文中,将控制文件备份到Oracle的家目录下,给控制文件一种家的温暖。
[oracle@ENMOEDU ENMOEDU]$ cp control01.ctl ~
创建表空间和文件,从而使现在的控制文件和备份文件产生差异。
SYS@ENMOEDU > create tablespace frank datafile '/u01/app/oracle/oradata/ENMOEDU/frank01.dbf' size 100m;
Tablespace created.
查看一下当前在线日志的status,并切换日志,产生归档。
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 ACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 CURRENT
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 ACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
SYS@ENMOEDU > alter system archive log current;
System altered.
SYS@ENMOEDU > select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
切日志的次数由于一不留神没有控制住,所以产生的归档日志的数量也很可观,导致一会恢复控制文件的时候造成了不小的困难。
查看现在日志和数据文件的状态:
SYS@ENMOEDU > select GROUP#,SEQUENCE#,ARCHIVED,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# SEQUENCE# ARC FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- --- ------------- ------------
1 112 YES 1382686 1382699
2 113 YES 1382699 1382705
3 114 NO 1382705 2.8147E+14
SYS@ENMOEDU > select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1382686
2 1382686
3 1382686
4 1382686
5 1382686
6 1382686
7 1382686
8 1382686
8 rows selected.
删除控制文件:
rm -rf /u01/app/oracle/oradata/ENMOEDU/control01.dbf;
rm -rf /u01/app/oracle/fast_recovery_area/ENMOEDU/ ontrol02.dbf;
关机,重启,报错,就以nomount的形式开启数据库:
SYS@ENMOEDU > shutdown abort;
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@ENMOEDU > shutdown abort
ORACLE instance shut down.
SYS@ENMOEDU > startup nomount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
恢复控制文件:
[oracle@ENMOEDU ~]$ cp control01.ctl /u01/app/oracle/oradata/ENMOEDU/control01.ctl
[oracle@ENMOEDU ~]$ cp control01.ctl /u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl
将数据库启动到mount状态:
SYS@ENMOEDU > alter database mount;
Database altered.
查看一下当前文件的状态:
SYS@ENMOEDU > select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1371719
2 1371719
3 1371719
4 1371719
5 1371719
6 1371719
7 1371719
8 1382338
8 rows selected.
SYS@ENMOEDU > select status from v$datafile;
STATUS
-------
SYSTEM
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
RECOVER
8 rows selected.
明 显看出来datafile 8的scn号跟其他的不一样,因为它是在备份控制文件之后创建的。
利用archivelog来恢复控制文件:
SYS@ENMOEDU > recover database using backup controlfile;
ORA-00279: change 1382338 generated at 03/11/2014 20:23:02 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_106_9ky02vgf_.arc
ORA-00280: change 1382338 for thread 1 is in sequence #106
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_106_9ky02vgf_.arc
ORA-00279: change 1382640 generated at 03/11/2014 20:23:55 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_107_9ky03860_.arc
ORA-00280: change 1382640 for thread 1 is in sequence #107
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_106_9ky02vgf_.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_107_9ky03860_.arc
ORA-00279: change 1382655 generated at 03/11/2014 20:24:08 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_108_9ky03wyl_.arc
ORA-00280: change 1382655 for thread 1 is in sequence #108
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_107_9ky03860_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_108_9ky03wyl_.arc
ORA-00279: change 1382666 generated at 03/11/2014 20:24:28 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_109_9ky045yl_.arc
ORA-00280: change 1382666 for thread 1 is in sequence #109
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_108_9ky03wyl_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_109_9ky045yl_.arc
ORA-00279: change 1382673 generated at 03/11/2014 20:24:37 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_110_9ky04mo6_.arc
ORA-00280: change 1382673 for thread 1 is in sequence #110
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_109_9ky045yl_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_110_9ky04mo6_.arc
ORA-00279: change 1382680 generated at 03/11/2014 20:24:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_111_9ky04xrr_.arc
ORA-00280: change 1382680 for thread 1 is in sequence #111
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_110_9ky04mo6_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_111_9ky04xrr_.arc
ORA-00279: change 1382686 generated at 03/11/2014 20:25:01 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_112_9ky056n5_.arc
ORA-00280: change 1382686 for thread 1 is in sequence #112
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_111_9ky04xrr_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_112_9ky056n5_.arc
ORA-00279: change 1382699 generated at 03/11/2014 20:25:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_113_9ky05g7p_.arc
ORA-00280: change 1382699 for thread 1 is in sequence #113
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_112_9ky056n5_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_113_9ky05g7p_.arc
ORA-00279: change 1382705 generated at 03/11/2014 20:25:18 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_114_%u_.arc
ORA-00280: change 1382705 for thread 1 is in sequence #114
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_113_9ky05g7p_.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_114_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_03_11/o1_mf_1_114_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
至此,归档文件已利用完毕。在开始阶段频繁切日志所酿下的一个不大不小的问题浮出水面,从而使我的恢复工作增加了一定量的复杂度。现在应该要使用到redo log日志了。因为提示说要用到的序列号为114,而在关机前所查看的日志状态中,redo03.log的序列号为114,所以指定redo03.log来继续恢复。
指定 日志恢复:
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ENMOEDU/redo03.log
Log applied.
Media recovery complete.
恢复成功,打开数据库:
SYS@ENMOEDU > alter database open resetlogs;
Database altered.
查看当前数据库数据文件状态:
S YS@ENMOEDU > select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1382865
2 1382865
3 1382865
4 1382865
5 1382865
6 1382865
7 1382865
8 1382865
8 rows selected.
至此,数据库控制文件恢复成功。
从上面可以看书,采用用户管理备份与恢复的方式来恢复我们的控制文件还是有一定的时间复杂度和空间复杂度的。那么到底有没有一个简单点得方法来解决这个问题吗?按照国际惯例,一般作者向读者发出类似这种疑问句的时候,答案往往是肯定的。但是,Frank作为一个走位忽上忽下意识飘忽不定的技术人员,所以,答案,仍然是肯定的。那就是RMAN。
(2)基于RMAN的备份和恢复
终于到了RMAN这一部分了。其实用RMAN恢复控制文件相对而言要简单的多。有如此利器而放置不用,实乃暴殄天物。下面就为大家带来用RMAN恢复控制文件的过程。
首先,用RMAN做一次全备份。
SYS@ENMOEDU > alter database begin backup;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup database plus archivelog;
备份的过程就不逐一显示在这里了。
SYS@ENMOEDU > alter database end backup;
至此,备份过程完成。
创建表空间和表文件,删除控制文件 。
SYS@ENMOEDU > create tablespace frank datafile '/u01/app/oracle/oradata/ENMOEDU/frank01' size 100m;
Tablespace created.
SYS@ENMOEDU > create table frank (x int,y int);
Table created.
SYS@ENMOEDU > insert into frank values(1,2);
1 row created.
SYS@ENMOEDU > insert into frank values(2,3);
1 row created.
SYS@ENMOEDU > select * from frank;
X Y
---------- ----------
1 2
2 3
SYS@ENMOEDU > commit;
[oracle@ENMOEDU ENMOEDU]$ rm -rf /u01/app/oracle/oradata/ENMOEDU/control01.dbf;
[oracle@ENMOEDU ENMOEDU]$ rm -rf /u01/app/oracle/fast_recovery_area/ENMOEDU/ ontrol02.dbf;
查看一下DBID,一会RMAN恢复将要使用这个参数。
SYS@ENMOEDU > select dbid from v$database;
DBID
----------
87396644
关闭数据库,以nomount的方式启动数据库。
SYS@ENMOEDU > shutdown abort
ORACLE instance shut down.
SYS@ENMOEDU > startup nomount
ORACLE instance started.
恢复控制文件 。
RMAN> set dbid=87396644;
executing command: SET DBID
RMAN> restore controlfile from autobackup;
restore自动提示的过程略去,为了节省空间和版面。
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> restore database;
RMAN> recover database;
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
至此,恢复过程结束。
检验一下恢复的效果如何。
SYS@ENMOEDU > select * from frank;
X Y
----------------
1 2
2 3
SYS@ENMOEDU > select name from v$datafile;
NAME
--------------------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/system01.dbf
/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf
/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf
/u01/app/oracle/oradata/ENMOEDU/users01.dbf
/u01/app/oracle/oradata/ENMOEDU/example01.dbf
/u01/app/oracle/oradata/ENMOEDU/test01.dbf
/u01/app/oracle/oradata/ENMOEDU/test02.dbf
/u01/app/oracle/oradata/ENMOEDU/frank01
由上我们可以看出恢复成功。
纵然用RMAN恢复控制文件比较简单粗暴,但是最好的防守就是进攻,与其坐以待毙等待控制文件出错被动恢复,倒不如采取主动出击多路复用控制文件。狡兔尚且知掘三窟,那么引入多路复用技术也就自然而然了。下面介绍多路复用技术。
三、控制文件的多路复用技术
(1)多路复用技术的配置
关于多路复用有两种配置方式,一种是在数据库nomount的方式下修改参数,另一种是直接修改参数文件。
将数据库启动到nomount,修改参数。
SYS@ENMOEDU > startup nomount
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
SYS@ENMOEDU > alter system set control_files=
'/u01/app/oracle/oradata/ENMOEDU/control01.ctl','/u01/app/oracle/oradata/control01.ctl' scope=spfile;
System altered.
将控制文件拷贝到另一个需要放置控制文件的路径。
[oracle@ENMOEDU dbs]$ cp /u01/app/oracle/oradata/ENMOEDU/control01.ctl /u01/app/oracle/oradata/control01.ctl
重启数据库,查看控制文件状态。
SYS@ENMOEDU > shutdown immediate
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.
SYS@ENMOEDU > select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/control01.ctl
/u01/app/oracle/oradata/control01.ctl
控制文件的多路复用成功。
第二种方法是直接修改init.ora文件。
首先创建pfile。
SYS@ENMOEDU > create pfile from spfile;
File created.
修改pfile。
[oracle@ENMOEDU dbs]$ vi initENMOEDU.ora
将控制文件那一项修改。
*.control_files='/u01/app/oracle/oradata/ENMOEDU/control01.ctl','/u01/app/oracle/oradata/control01.ctl'
创建spfile。
SYS@ENMOEDU > create pfile from spfile;
File created.
启动数据库,查看控制文件状态。
SYS@ENMOEDU > shutdown immediate
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.
SYS@ENMOEDU > select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/control01.ctl
/u01/app/oracle/oradata/control01.ctl
控制文件的多路复用成功 。
至此,控制文件的多路复用已经实现了。下面我们将做一个实验,来看看多路复用技术是怎么保障控制文件的正常使用的。
正常启动数据库。删除其中一份控制文件。
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.
[oracle@ENMOEDU oradata]$ rm -rf /u01/app/oracle/oradata/control01.ctl
正常关闭数据库,发现不能关闭,用shutdown abort 的方式关闭。
SYS@ENMOEDU > shutdown abort
ORACLE instance shut down.
以nomount的方式打开数据库。
SYS@ENMOEDU > startup nomount
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
修改参数,将已删除的控制文件从参数中删除。
SYS@ENMOEDU > alter system set control_files= '/u01/app/oracle/oradata/ENMOEDU/control01.ctl' scope=spfile;
System altered.
关闭数据库,重启。
SYS@ENMOEDU > shutdown immediate
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 292933632 bytes
Fixed Size 1344512 bytes
Variable Size 222301184 bytes
Database Buffers 62914560 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.
SYS@ENMOEDU > select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/control01.ctl
至此,数据库已经成功启动。
综上所述,本文总结了控制文件的用户管理备份和恢复、RMAN备份和恢复、多路复用的相关知识点。Frank才疏学浅,知识匮乏,只能尽绵薄之力总结自身所学,狂生之言,仅为笑谈。