一、Oracle 控制文件
为二进制文件，初始化大小由CREATE DATABASE指定,可以使用RMAN备份
记录了当前数据库的结构信息,同时也包含数据文件及日志文件的信息以及相关的状态,归档信息等等
在参数文件中描述其位置，个数等等。通常采用分散放开，多路复用的原则。在mount阶段被读取，open阶段一直被使用
维护数据库一致性(数据库启动时会比较控制文件与联机日志文件中的ckpt,即起始scn号，如相等则正常启动，否则需要介质恢复)
一个控制文件只能属于一个数据库
控制文件的任意修改将写入到初始化参数中指定的所有控制文件中，读取时则仅读取第一个控制文件
控制文件只能连接一个数据库，控制文件的大小一般不要超过MB,最多为个，最少一个，互为镜像
控制文件中包含的内容
数据库的名字、ID、创建的时间戳
表空间的名字
联机日志文件、数据文件的位置、个数、名字
联机日志的Sequence号码
检查点的信息
撤销段的开始或结束
归档信息
备份信息
二、查看控制文件的相关信息
1.使用相关视图来查看
V$CONTROLFILE --列出实例中所有控制文件的名字及状态信息
SYS@userdata>select * from v$controlfile; STATUS NAME IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS --------------------- -------------------------------------------------- --------- ---------- -------------- /u01/app/oracle/oradata/userdata/control01.ctl NO 16384 508 /u01/app/oracle/oradata/userdata/control02.ctl NO 16384 508 /u01/app/oracle/oradata/userdata/control03.ctl NO 16384 508
V$PARAMETER --列出所有参数的位置及状态信息
SYS@userdata>select name,value from v$parameter where name='control_files'; NAME -------------------------------------------------- VALUE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- control_files /u01/app/oracle/oradata/userdata/control01.ctl, /u01/app/oracle/oradata/userdata/control02.ctl, /u01/app/oracle/oradata/userdata/control03.ctl
V$CONTROLFILE_RECORD_SECTION --列出控制文件中记录的部分信息
SHOW PARAMETER CONTROL_FILES --列出控制文件的名字、状态、位置等
SYS@userdata>show parameter control_files; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u01/app/oracle/oradata/userda ta/control01.ctl, /u01/app/ora cle/oradata/userdata/control02 .ctl, /u01/app/oracle/oradata/ userdata/control03.ctl
2.使用STRINGS命令来查看控制文件中的具体内容
$ strings /u01/app/oracle/oradata/userdata/control01.ctl
3.备份控制文件到平面文件(然后查看控制文件中的具体内容)
SYS@userdata>alter database backup controlfile to trace as '/home/oracle/ctl.txt'; Database altered.
4.转储控制文件内容(查看控制文件中的具体内容)
SYS@userdata>alter system set events 'immediate trace name controlf level 10'; System altered. SYS@userdata>show parameter user_dump; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ user_dump_dest string /u01/app/oracle/admin/userdata /udump SYS@userdata>! ls -ltr /u01/app/oracle/admin/userdata/udump total 1344 -.............. -rw-r----- 1 oracle oinstall 136039 Sep 13 16:49 userdata_ora_9476.trc
或者
SYS@userdata>oradebug setmypid Statement processed. SYS@userdata>select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1)); SPID ------------------------------------ 9476 SYS@userdata>oradebug dump controlf 12 Statement processed. SYS@userdata>show parameter user_dump; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ user_dump_dest string /u01/app/oracle/admin/userdata /udump SYS@userdata>! ls -lh /u01/app/oracle/admin/userdata/udump/*9476* -rw-r----- 1 oracle oinstall 182K Sep 13 17:04 /u01/app/oracle/admin/userdata/udump/userdata_ora_9476.trc
三、控制文件个数与位置管理
spfile或pfile都可以实现对控制文件的个数及位置管理
spfile步骤
修改spfile参数中的control_files -- alter system ... scope = spfile | both |memory
一致性关闭数据库
增加或减少控制文件(cp or mv)
启动数据库使用spfile
验证结果
pfile步骤
一致性关闭数据库
修改pfile参数(vi或vim) 修改*.control_files＝......这一段
增加或减少控制文件(cp or mv)
启动数据库使用pfile
验证结果
1. 将控制文件减少到一个
SYS@userdata>show parameter control_files; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u01/app/oracle/oradata/userda ta/control01.ctl, /u01/app/ora cle/oradata/userdata/control02 .ctl, /u01/app/oracle/oradata/ userdata/control03.ctl SYS@userdata>alter system set control_files='/u01/app/oracle/oradata/userdata/control01.ctl' scope=spfile; System altered. SYS@userdata>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@userdata>startup mount; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. SYS@userdata>show parameter control_files; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u01/app/oracle/oradata/userda ta/control01.ctl
2. 将控制文件恢复到多路复用
SYS@userdata>show parameter control_files; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u01/app/oracle/oradata/userda ta/control01.ctl SYS@userdata>alter system set control_files='/u01/app/oracle/oradata/userdata/control01.ctl','/u01/app/oracle/oradata/userdata/control02.ctl','/u01/app/oracle/oradata/userdata/control03.ctl' scope=spfile; System altered. SYS@userdata>shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SYS@userdata>startup mount; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes ORA-00214: control file '/u01/app/oracle/oradata/userdata/control01.ctl' version 740 inconsistent with file '/u01/app/oracle/oradata/userdata/control02.ctl' version 738 SYS@userdata>! cp /u01/app/oracle/oradata/userdata/control01.ctl /u01/app/oracle/oradata/userdata/control02.ctl SYS@userdata>! cp /u01/app/oracle/oradata/userdata/control01.ctl /u01/app/oracle/oradata/userdata/control03.ctl SYS@userdata>alter database mount; Database altered. SYS@userdata>alter database open; Database altered.
四、控制文件的备份
SYS@userdata>alter database backup controlfile to '/home/oracle/ctl_20170913.bak'; #归档模式下备份 Database altered. SYS@userdata>alter database backup controlfile to trace as '/home/oracle/ctl_20170913.txt'; #归档非非归档均可 Database altered. RMAN> backup current controlfile format '/home/oracle/ctl_rman.bak'; #采用rman方式备份 Starting backup at 2017-09-13 17:28:21 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 2017-09-13 17:28:22 channel ORA_DISK_1: finished piece 1 at 2017-09-13 17:28:25 piece handle=/home/oracle/ctl_rman.bak tag=TAG20170913T172821 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 Finished backup at 2017-09-13 17:28:25 RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 1 Full 7.98M DISK 00:00:02 2017-09-13 17:28:23 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20170913T172821 Piece Name: /home/oracle/ctl_rman.bak Control File Included: Ckp SCN: 1133796 Ckp time: 2017-09-13 17:28:21
五、控制文件的恢复
1. 非归档模式下控制文件恢复
SYS@userdata>archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /arch Oldest online log sequence 17 Current log sequence 19 SYS@userdata>show parameter control_files; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u01/app/oracle/oradata/userda ta/control01.ctl SYS@userdata>alter database backup controlfile to trace as '/home/oracle/ctl_20170913.txt'; Database altered. SYS@userdata>create table temp_tb(id int,name varchar2(32)); Table created. SYS@userdata>insert into temp_tb values(1,'bill');
SYS@userdata>insert into temp)tb values(2,'sames'); 1 row created. SYS@userdata>commit; Commit complete. SYS@userdata>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@userdata>host rm -rf /u01/app/oracle/oradata/userdata/control01.ctl SYS@userdata>startup; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes ORA-00205: error in identifying control file, check alert log for more info SYS@userdata>host tail -n 10 /u01/app/oracle/admin/userdata/bdump/alert_userdata.log starting up 1 shared server(s) ... Wed Sep 13 19:17:55 2017 ALTER DATABASE MOUNT Wed Sep 13 19:17:55 2017 ORA-00202: control file: '/u01/app/oracle/oradata/userdata/control01.ctl' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Wed Sep 13 19:17:58 2017 ORA-205 signalled during: ALTER DATABASE MOUNT...
可以看到唯一的控制文件丢失后,数据库启动到mount状态下报错,此时只能通过手动新建控制文件的方法来解决,完整的创建控制文件的语句如下所示
--重建控制文件主要有三个需要考虑的是
--搞清各个日志文件的大小及位置
--搞清各个数据文件的位置
--设置正确的字符集
CREATE CONTROLFILE REUSE DATABASE "USERDATA" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 200 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/userdata/redo01.log' SIZE 100M, GROUP 2 '/u01/app/oracle/oradata/userdata/redo02.log' SIZE 100M, GROUP 3 '/u01/app/oracle/oradata/userdata/redo03.log' SIZE 100M DATAFILE '/u01/app/oracle/oradata/userdata/system01.dbf', '/u01/app/oracle/oradata/userdata/undotbs01.dbf', '/u01/app/oracle/oradata/userdata/sysaux01.dbf', '/u01/app/oracle/oradata/userdata/users01.dbf', '/u01/app/oracle/oradata/userdata/example01.dbf' CHARACTER SET AL32UTF8 ;
SYS@userdata> CREATE CONTROLFILE REUSE DATABASE "USERDATA" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 200 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/userdata/redo01.log' SIZE 100M, GROUP 2 '/u01/app/oracle/oradata/userdata/redo02.log' SIZE 100M, GROUP 3 '/u01/app/oracle/oradata/userdata/redo03.log' SIZE 100M DATAFILE '/u01/app/oracle/oradata/userdata/system01.dbf', '/u01/app/oracle/oradata/userdata/undotbs01.dbf', '/u01/app/oracle/oradata/userdata/sysaux01.dbf', '/u01/app/oracle/oradata/userdata/users01.dbf', '/u01/app/oracle/oradata/userdata/example01.dbf' 17 CHARACTER SET AL32UTF8; Control file created. SYS@userdata>alter database open; Database altered. SYS@userdata>select * from temp_tb; ID NAME ---------- -------------------------------------------------- 1 bill 2 sames
2. 归档模式下控制文件恢复
SYS@userdata>archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /arch Oldest online log sequence 19 Next log sequence to archive 21 Current log sequence 21 SYS@userdata>show parameter control_files; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u01/app/oracle/oradata/userda ta/control01.ctl SYS@userdata>insert into temp_tb values(3,'jerry'); 1 row created. YS@userdata>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@userdata>host rm -rf /u01/app/oracle/oradata/userdata/control01.ctl SYS@userdata>startup ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes ORA-00205: error in identifying control file, check alert log for more info SYS@userdata>host tail -n 10 /u01/app/oracle/admin/userdata/bdump/alert_userdata.log starting up 1 shared server(s) ... Wed Sep 13 19:59:08 2017 ALTER DATABASE MOUNT Wed Sep 13 19:59:08 2017 ORA-00202: control file: '/u01/app/oracle/oradata/userdata/control01.ctl' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Wed Sep 13 19:59:11 2017 ORA-205 signalled during: ALTER DATABASE MOUNT... SYS@userdata>host cp /home/oracle/archctl.ctl /u01/app/oracle/oradata/userdata/control01.ctl SYS@userdata>alter database mount; Database altered. SYS@userdata>alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SYS@userdata>shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SYS@userdata>startup mount; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. SYS@userdata>alter database open resetlogs; Database altered. YS@userdata>select * from temp_tb; ID NAME ---------- -------------------------------------------------- 1 bill 2 sames 3 jerry SYS@userdata>archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /arch Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2 SYS@userdata>select * from temp_tb; ID NAME ---------- -------------------------------------------------- 1 bill 2 sames 3 jerry