控制文件(control file)是数据库重要的文件,一旦其丢失将导致数据库宕机。控制文件在数据库的MOUNT阶段被读取,它记录着数据库许多重要的信息。因此控制文件的日常检查以及一些常规故障恢复方法就是需要我们必须要掌握的。
一、控制文件的查询
-->通过参数查询
SYS@testdb>show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oracle/ora10g/oradata/control
01.ctl
SYS@testdb>col name format a30
SYS@testdb>col value format a50
SYS@testdb>select name,value from v$parameter where name='control_files';
NAME VALUE
------------------------------ --------------------------------------------------
control_files /oracle/ora10g/oradata/control01.ctl
-->通过v$controlfile视图查询
SYS@testdb>col name for a40
SYS@testdb>select name from v$controlfile;
NAME
----------------------------------------
/oracle/ora10g/oradata/control01.ctl
控制文件记录内容查询,v$controlfile_record_section视图displays information about the control file record sections。
SYS@testdb>select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 4 0 0 0 0
REDO THREAD 256 1 1 0 0 0
REDO LOG 72 5 5 0 0 9
DATAFILE 428 100 11 0 0 46
FILENAME 524 2275 15 0 0 0
TABLESPACE 68 100 11 0 0 17
TEMPORARY FILENAME 56 100 1 0 0 1
RMAN CONFIGURATION 1108 50 2 0 0 12
LOG HISTORY 56 292 30 1 30 30
OFFLINE RANGE 200 163 0 0 0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
ARCHIVED LOG 584 28 28 2 1 29
BACKUP SET 40 409 58 1 58 58
BACKUP PIECE 736 200 58 1 58 58
BACKUP DATAFILE 116 282 134 1 134 134
BACKUP REDOLOG 76 215 0 0 0 0
DATAFILE COPY 660 223 48 1 48 48
BACKUP CORRUPTION 44 371 0 0 0 0
COPY CORRUPTION 40 409 0 0 0 0
DELETED OBJECT 20 818 102 1 102 102
PROXY COPY 852 211 0 0 0 0
BACKUP SPFILE 36 454 41 1 41 41
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE INCARNATION 56 292 3 1 3 3
FLASHBACK LOG 84 2048 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0 0
RMAN STATUS 116 141 141 52 51 192
THREAD INSTANCE NAME MAPPING 80 1 1 0 0 0
MTTR 100 1 1 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0
STANDBY DATABASE MATRIX 400 10 10 0 0 0
GUARANTEED RESTORE POINT 212 2048 0 0 0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
RESTORE POINT 212 2083 0 0 0 0
34 rows selected.
二、控制文件的备份方式
1、对控制文件进行镜像。
由于控制文件的重要性,在数据库中一般都配置3个控制文件镜像,这些镜像放置在不同的物理磁盘上,减少介质损坏的风险。从上面的信息可以看到,我们的数据库只有一个控制文件,这就需要我们进行控制文件的镜像操作。
-->关闭数据库
SYS@testdb>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
-->使用主机cp 命令将控制文件复制到不同路径。我这里是简单的演示,所以都放到了相同路径下
tempapp@ora10g[#/home/ora10g]cp /oracle/ora10g/oradata/control01.ctl /oracle/ora10g/oradata/control02.ctl
-->修改control_files参数,将新控制文件路径加入
SYS@testdb>create pfile='/home/ora10g/pfile_20140327.ora' from spfile;
File created.
-->使用vi命令修改control_files参数
tempapp@ora10g[#/home/ora10g]vi pfile_20140327.ora
"pfile_20140327.ora" 22 lines, 731 characters
testdb.__db_cache_size=1644167168
testdb.__java_pool_size=16777216
testdb.__large_pool_size=16777216
testdb.__shared_pool_size=452984832
testdb.__streams_pool_size=0
*.control_files='/oracle/ora10g/oradata/control01.ctl','/oracle/ora10g/oradata/control02.ctl'
*.cursor_sharing='EXACT'
*.db_file_multiblock_read_count=8# SMALL
*.db_files=80# SMALL
SYS@testdb>startup nomount pfile='/home/ora10g/pfile_20140327.ora';
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 496887712 bytes
Database Buffers 1644167168 bytes
Redo Buffers 4259840 bytes
SYS@testdb>
SYS@testdb>show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oracle/ora10g/oradata/control
01.ctl, /oracle/ora10g/oradata
/control02.ctl
SYS@testdb>create spfile from pfile='/home/ora10g/pfile_20140327.ora';
File created.
SYS@testdb>shutdown abort
ORACLE instance shut down.
SYS@testdb>
SYS@testdb>startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 496887712 bytes
Database Buffers 1644167168 bytes
Redo Buffers 4259840 bytes
Database mounted.
SYS@testdb>
SYS@testdb>alter database open;
Database altered.
SYS@testdb>col name for a50
SYS@testdb>select name from v$controlfile;
NAME
--------------------------------------------------
/oracle/ora10g/oradata/control01.ctl
/oracle/ora10g/oradata/control02.ctl
2、备份控制文件到二进制文件
SYS@testdb>alter database backup controlfile to '/home/ora10g/controlfile_20140327.ora';
Database altered.
3、备份控制文件到trace文件。这种方式将获取重建控制文件的脚本
SYS@testdb>alter database backup controlfile to trace;
Database altered.
SYS@testdb>oradebug setmypid
Statement processed.
SYS@testdb>oradebug tracefile_name
/oracle/ora10g/admin/testdb/udump/testdb_ora_19847.trc
-->从trace文件中,我们可以获取重建controlfile脚本,例如
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/ora10g/oradata/redo01.log' SIZE 100M,
GROUP 2 '/oracle/ora10g/oradata/redo02.log' SIZE 100M,
GROUP 3 '/oracle/ora10g/oradata/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/oracle/ora10g/oradata/system01.dbf',
'/oracle/ora10g/oradata/undotbs01.dbf',
'/oracle/ora10g/oradata/sysaux01.dbf',
'/oracle/ora10g/oradata/users01.dbf',
'/oracle/ora10g/oradata/system02.dbf'
CHARACTER SET US7ASCII
;
4、使用rman备份控制文件
-->在rman中我们可以设置controlfile的自动备份
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ora_data/wangche/controlfile_%F';
-->在进行全备时,controlfile将一同备份。
RMAN> backup database format '/ora_data/backup/database_full_%U';
Starting backup at 28-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle/ora10g/oradata/users01.dbf
input datafile fno=00008 name=/oracle/ora10g/oradata/system02.dbf
input datafile fno=00001 name=/oracle/ora10g/oradata/system01.dbf
input datafile fno=00003 name=/oracle/ora10g/oradata/sysaux01.dbf
input datafile fno=00002 name=/oracle/ora10g/oradata/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 28-MAR-14
channel ORA_DISK_1: finished piece 1 at 28-MAR-14
piece handle=/ora_data/backup/database_full_30p49ua4_1_1 tag=TAG20140328T083100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 28-MAR-14
-->controlfile被自动备份
Starting Control File and SPFILE Autobackup at 28-MAR-14
piece handle=/ora_data/wangche/controlfile_c-2622586788-20140328-00 comment=NONE
Finished Control File and SPFILE Autobackup at 28-MAR-14
-->使用rman命令来备份controlfile
RMAN> backup current controlfile format '/ora_data/backup/controlfile_%U';
Starting backup at 28-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 28-MAR-14
channel ORA_DISK_1: finished piece 1 at 28-MAR-14
piece handle=/ora_data/backup/controlfile_33p49uh6_1_1 tag=TAG20140328T083446 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-MAR-14
-->这里由于没有关闭controlfile的自动备份,所以controlfile被备份了2次
Starting Control File and SPFILE Autobackup at 28-MAR-14
piece handle=/ora_data/wangche/controlfile_c-2622586788-20140328-01 comment=NONE
Finished Control File and SPFILE Autobackup at 28-MAR-14
三、不同情况下控制文件的恢复方式
实践证明,主机异常掉电导致控制文件损坏的概率极高,所以系统中至少要保存两份控制文件,以防止单点故障。由于控制文件中保存了太多信息,所以因控制文件问题而导致数据库不能启动的故障很多,DBA应该熟练掌握重建控制文件的流程。
1、控制文件镜像丢失处理
-->模拟数据库宕机
SYS@testdb>shutdown abort
ORACLE instance shut down.
-->手工删除controlfile文件,模拟控制文件丢失
tempapp@ora10g[#/home/ora10g]cd /oracle/ora10g/oradata
tempapp@ora10g[#/oracle/ora10g/oradata]ls
control01.ctl redo01.log redo03.log system01.dbf temp01.dbf users01.dbf
control02.ctl redo02.log sysaux01.dbf system02.dbf undotbs01.dbf
tempapp@ora10g[#/oracle/ora10g/oradata]rm -f control02.ctl
tempapp@ora10g[#/oracle/ora10g/oradata]ls
control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf system02.dbf temp01.dbf undotbs01.dbf users01.dbf
-->启动数据库,报控制文件丢失
SYS@testdb>startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 496887712 bytes
Database Buffers 1644167168 bytes
Redo Buffers 4259840 bytes
ORA-00205: error in identifying control file, check alert log for more info
-->从alert log中,我们能够获得更详细的信息。/oracle/ora10g/oradata/control02.ctl控制文件丢失。
Fri Mar 28 08:40:26 2014
ORA-00202: control file: '/oracle/ora10g/oradata/control02.ctl'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3
Fri Mar 28 08:40:26 2014
ORA-205 signalled during: ALTER DATABASE MOUNT...
-->使用镜像文件处理
-->关闭数据库
SYS@testdb>shutdown abort
ORACLE instance shut down.
-->拷贝一份控制文件
tempapp@ora10g[#/oracle/ora10g/oradata]cp control01.ctl control02.ctl
tempapp@ora10g[#/oracle/ora10g/oradata]ls
control01.ctl redo01.log redo03.log system01.dbf temp01.dbf users01.dbf
control02.ctl redo02.log sysaux01.dbf system02.dbf undotbs01.dbf
-->重新启动数据库
SYS@testdb>startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 496887712 bytes
Database Buffers 1644167168 bytes
Redo Buffers 4259840 bytes
Database mounted.
SYS@testdb>alter database open;
Database altered.
2、所有控制文件损坏但存在二进制文件备份
-->备份controlfile
SYS@testdb>alter database backup controlfile to '/home/ora10g/controlfile_20140328.ora';
Database altered.
-->关闭数据库
SYS@testdb>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
-->删除全部controlfile,模拟controlfile全部丢失情况
tempapp@ora10g[#/oracle/ora10g/oradata]mv control0*.ctl /home/ora10g/
-->启动数据库
SYS@testdb>startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 496887712 bytes
Database Buffers 1644167168 bytes
Redo Buffers 4259840 bytes
ORA-00205: error in identifying control file, check alert log for more info
-->从alert log 中,我们同样可以获得相关信息
Fri Mar 28 08:49:10 2014
ORA-00202: control file: '/oracle/ora10g/oradata/control01.ctl'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3
Fri Mar 28 08:49:10 2014
ORA-205 signalled during: ALTER DATABASE MOUNT...
-->关闭数据库
SYS@testdb>shutdown abort
ORACLE instance shut down.
-->使用
tempapp@ora10g[#/home/ora10g]cp controlfile_20140328.ora /oracle/ora10g/oradata/control01.ctl
tempapp@ora10g[#/home/ora10g]cp controlfile_20140328.ora /oracle/ora10g/oradata/control02.ctl
-->启动数据库
SYS@testdb>startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 496887712 bytes
Database Buffers 1644167168 bytes
Redo Buffers 4259840 bytes
Database mounted.
SYS@testdb>
SYS@testdb>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@testdb>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/ora10g/oradata/system01.dbf'
-->执行恢复
SYS@testdb>SELECT controlfile_type, controlfile_sequence#, controlfile_change#,controlfile_time,open_resetlogs FROM v$database;
CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFI OPEN_RESETL
------- --------------------- ------------------- --------- -----------
BACKUP 4267 2013290 28-MAR-14 ALLOWED
SYS@testdb>recover database until cancel using backup controlfile;
ORA-00279: change 2013290 generated at 03/28/2014 08:43:23 needed for thread 1
ORA-00289: suggestion : /oracle/ora10g/archlog/1_11_842202065.dbf
ORA-00280: change 2013290 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/oracle/ora10g/archlog/1_11_842202065.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/ora10g/oradata/system01.dbf'
SYS@testdb>recover database using backup controlfile;
ORA-00279: change 2013290 generated at 03/28/2014 08:43:23 needed for thread 1
ORA-00289: suggestion : /oracle/ora10g/archlog/1_11_842202065.dbf
ORA-00280: change 2013290 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/oracle/ora10g/archlog/1_11_842202065.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3
SYS@testdb>
SYS@testdb>recover database using backup controlfile;
ORA-00279: change 2013290 generated at 03/28/2014 08:43:23 needed for thread 1
ORA-00289: suggestion : /oracle/ora10g/archlog/1_11_842202065.dbf
ORA-00280: change 2013290 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/oracle/ora10g/archlog/1_11_842202065.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3
SYS@testdb>recover databae;
ORA-00905: missing keyword
===> 这块,我的操作有错误,在提示Specify log:
===> 我需要输入当前redo log file即可。
tempapp@ora10g[#/home/ora10g]rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Fri Mar 28 09:37:03 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: TESTDB (DBID=2622586788, not open) RMAN> recover database; Starting recover at 28-MAR-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=154 devtype=DISK starting media recovery archive log thread 1 sequence 11 is already on disk as file /oracle/ora10g/oradata/redo02.log archive log filename=/oracle/ora10g/oradata/redo02.log thread=1 sequence=11 media recovery complete, elapsed time: 00:00:03 Finished recover at 28-MAR-14 RMAN> exit Recovery Manager complete. SYS@testdb> SYS@testdb>alter database open resetlogs; Database altered. SYS@testdb>>
3、所有控制文件损坏但存在RMAN备份
-->关闭后删除全部控制文件 --> RMAN> startup mount Oracle instance started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 03/28/2014 09:51:49 ORA-00205: error in identifying control file, check alert log for more info --> RMAN> shutdown abort using target database control file instead of recovery catalog Oracle instance shut down RMAN> startup nomount connected to target database (not started) Oracle instance started Total System Global Area 2147483648 bytes Fixed Size 2168928 bytes Variable Size 496887712 bytes Database Buffers 1644167168 bytes Redo Buffers 4259840 bytes --> RMAN> restore controlfile from '/ora_data/backup/controlfile_33p49uh6_1_1'; Starting restore at 28-MAR-14 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output filename=/oracle/ora10g/oradata/control01.ctl output filename=/oracle/ora10g/oradata/control02.ctl Finished restore at 28-MAR-14 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 --> SYS@testdb>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 10 104857600 1 NO CURRENT 1960269 27-MAR-14 3 1 9 104857600 1 YES INACTIVE 1933272 27-MAR-14 2 1 8 104857600 1 YES INACTIVE 1895969 26-MAR-14 GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------- --- 1 ONLINE /oracle/ora10g/oradata/redo01.log NO 2 STALE ONLINE /oracle/ora10g/oradata/redo02.log NO 3 STALE ONLINE /oracle/ora10g/oradata/redo03.log NO SYS@testdb>select file#,name,status from v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /oracle/ora10g/oradata/system01.dbf SYSTEM 2 /oracle/ora10g/oradata/undotbs01.dbf ONLINE 3 /oracle/ora10g/oradata/sysaux01.dbf ONLINE 4 /oracle/ora10g/oradata/users01.dbf ONLINE 8 /oracle/ora10g/oradata/system02.dbf SYSTEM SYS@testdb>recover database until cancel using backup controlfile; ORA-00283: recovery session canceled due to errors ORA-19909: datafile 1 belongs to an orphan incarnation ORA-01110: data file 1: '/oracle/ora10g/oradata/system01.dbf' -->重启主机到nomount状态 SYS@testdb>startup nomount ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 2168928 bytes Variable Size 496887712 bytes Database Buffers 1644167168 bytes Redo Buffers 4259840 bytes -->重建控制文件 SYS@testdb>CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG 2 MAXLOGFILES 5 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/oracle/ora10g/oradata/redo01.log' SIZE 100M, 9 GROUP 2 '/oracle/ora10g/oradata/redo02.log' SIZE 100M, 10 GROUP 3 '/oracle/ora10g/oradata/redo03.log' SIZE 100M 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/oracle/ora10g/oradata/system01.dbf', 14 '/oracle/ora10g/oradata/undotbs01.dbf', 15 '/oracle/ora10g/oradata/sysaux01.dbf', 16 '/oracle/ora10g/oradata/users01.dbf', 17 '/oracle/ora10g/oradata/system02.dbf' 18 CHARACTER SET US7ASCII; Control file created. SYS@testdb>select file#,name ,status from v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /oracle/ora10g/oradata/system01.dbf SYSTEM 2 /oracle/ora10g/oradata/undotbs01.dbf ONLINE 3 /oracle/ora10g/oradata/sysaux01.dbf ONLINE 4 /oracle/ora10g/oradata/users01.dbf ONLINE 8 /oracle/ora10g/oradata/system02.dbf SYSTEM SYS@testdb>recover database until cancel using backup controlfile; ORA-00279: change 2014055 generated at 03/28/2014 12:20:59 needed for thread 1 ORA-00289: suggestion : /oracle/ora10g/archlog/1_1_843394633.dbf ORA-00280: change 2014055 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/ora10g/archlog/1_1_843394633.dbf ORA-00308: cannot open archived log '/oracle/ora10g/archlog/1_1_843394633.dbf' ORA-27037: unable to obtain file status HP-UX Error: 2: No such file or directory Additional information: 3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/ora10g/oradata/redo01.log Log applied. Media recovery complete. SYS@testdb> SYS@testdb>alter database open resetlogs; Database altered. SYS@testdb>