Oracle控制文件

一、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