Oracle：关于控制文件
（本文为读书时的笔记，仅为个人记录）
对于Oracle数据库来说，有三种文件是必不可少的：数据文件、控制文件，联机重做日志文件。
其中，控制文件是一种比较小的二进制文件。
（控制文件的创建）
控制文件在数据库创建时创建。
*** 相关内容：“create controlfile …”：http://d-prototype.com/archives/1677
（控制文件的使用）
控制文件在数据库从nomount启动到mount时使用到，数据库实例通过spfile或pfile中的CONTROL_FILE找到控制文件。
控制文件中主要存储了以下信息：
数据库名称
数据文件的名称与位置
联机重做日志文件的名称与位置
当前联机重做日志的序列号
检查点信息
RMAN备份文件的名称与位置
控制文件的解读需要通过数据库实例的数据字典视图：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select table_name from dict where table_name like '%CONTROLFILE%'; TABLE_NAME -------------------------------------------------------------------------------- GV$CONTROLFILE GV$CONTROLFILE_RECORD_SECTION V$BACKUP_CONTROLFILE_DETAILS V$BACKUP_CONTROLFILE_SUMMARY V$CONTROLFILE V$CONTROLFILE_RECORD_SECTION 6 rows selected. SQL> |
获取控制文件的内容：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 |
[oracle@ora12c ~]$ ps -ef | grep ora_ oracle 39379 39249 0 13:01 pts/1 00:00:00 grep ora_ [oracle@ora12c ~]$ env | grep SID ORACLE_SID=atlas [oracle@ora12c ~]$ [oracle@ora12c ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 9 13:01:25 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1140850688 bytes Fixed Size 2923584 bytes Variable Size 771752896 bytes Database Buffers 352321536 bytes Redo Buffers 13852672 bytes Database mounted. SQL> SQL> desc v$controlfile; Name Null? Type ----------------------------------------- -------- ---------------------------- STATUS VARCHAR2(7) NAME VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3) BLOCK_SIZE NUMBER FILE_SIZE_BLKS NUMBER CON_ID NUMBER SQL> col name for a65 SQL> set linesize 400 SQL> select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID ------- ----------------------------------------------------------------- --- ---------- -------------- ---------- /oradata/ATLAS/controlfile/o1_mf_b5cyjq6b_.ctl NO 16384 1096 0 /oradata/fast_recovery_area/ATLAS/controlfile/o1_mf_b5cyjqjb_.ctl YES 16384 1096 0 SQL> SQL> desc v$controlfile_record_section; Name Null? Type ----------------------------------------- -------- ---------------------------- TYPE VARCHAR2(28) RECORD_SIZE NUMBER RECORDS_TOTAL NUMBER RECORDS_USED NUMBER FIRST_INDEX NUMBER LAST_INDEX NUMBER LAST_RECID NUMBER CON_ID NUMBER SQL> select count(*) from v$controlfile_record_section; COUNT(*) ---------- 41 SQL> SQL> select * from v$controlfile_record_section; TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ---------- DATABASE 316 1 1 0 0 0 0 CKPT PROGRESS 8180 11 0 0 0 0 0 REDO THREAD 256 8 1 0 0 0 0 REDO LOG 72 16 3 0 0 3 0 DATAFILE 520 1024 12 0 0 261 0 FILENAME 524 4146 21 0 0 0 0 TABLESPACE 68 1024 14 0 0 20 0 TEMPORARY FILENAME 56 1024 4 0 0 95 0 RMAN CONFIGURATION 1108 50 0 0 0 0 0 LOG HISTORY 56 292 159 1 159 159 0 OFFLINE RANGE 200 1063 8 1 8 8 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ---------- ARCHIVED LOG 584 28 3 1 3 3 0 BACKUP SET 96 1022 1 1 1 1 0 BACKUP PIECE 780 1006 1 1 1 1 0 BACKUP DATAFILE 200 1063 1 1 1 1 0 BACKUP REDOLOG 76 215 0 0 0 0 0 DATAFILE COPY 736 1000 0 0 0 0 0 BACKUP CORRUPTION 44 1115 0 0 0 0 0 COPY CORRUPTION 40 1227 0 0 0 0 0 DELETED OBJECT 20 818 0 0 0 0 0 PROXY COPY 928 1004 0 0 0 0 0 BACKUP SPFILE 124 131 1 1 1 1 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ---------- DATABASE INCARNATION 56 292 1 1 1 1 0 FLASHBACK LOG 84 2048 0 0 0 0 0 RECOVERY DESTINATION 180 1 1 0 0 0 0 INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0 REMOVABLE RECOVERY FILES 32 1000 0 0 0 0 0 RMAN STATUS 116 141 0 0 0 0 0 THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0 MTTR 100 8 1 0 0 0 0 DATAFILE HISTORY 568 57 0 0 0 0 0 STANDBY DATABASE MATRIX 400 31 31 0 0 0 0 GUARANTEED RESTORE POINT 212 2048 0 0 0 0 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ---------- RESTORE POINT 212 2083 0 0 0 0 0 DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0 ACM OPERATION 104 64 9 0 0 0 0 FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0 PDB RECORD 684 10 4 0 0 10 0 AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0 MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0 PDBINC RECORD 144 113 0 0 0 0 0 41 rows selected. SQL> SQL> desc gv$controlfile; Name Null? Type ----------------------------------------- -------- ---------------------------- INST_ID NUMBER STATUS VARCHAR2(7) NAME VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3) BLOCK_SIZE NUMBER FILE_SIZE_BLKS NUMBER CON_ID NUMBER SQL> SQL> select * from gv$controlfile; INST_ID STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID ---------- ------- ----------------------------------------------------------------- --- ---------- -------------- ---------- 1 /oradata/ATLAS/controlfile/o1_mf_b5cyjq6b_.ctl NO 16384 1096 0 1 /oradata/fast_recovery_area/ATLAS/controlfile/o1_mf_b5cyjqjb_.ctl YES 16384 1096 0 SQL> SQL> desc gv$controlfile_record_section; Name Null? Type ----------------------------------------- -------- ---------------------------- INST_ID NUMBER TYPE VARCHAR2(28) RECORD_SIZE NUMBER RECORDS_TOTAL NUMBER RECORDS_USED NUMBER FIRST_INDEX NUMBER LAST_INDEX NUMBER LAST_RECID NUMBER CON_ID NUMBER SQL> SQL> select * from gv$controlfile_record_section; INST_ID TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ---------- 1 DATABASE 316 1 1 0 0 0 0 1 CKPT PROGRESS 8180 11 0 0 0 0 0 1 REDO THREAD 256 8 1 0 0 0 0 1 REDO LOG 72 16 3 0 0 3 0 1 DATAFILE 520 1024 12 0 0 261 0 1 FILENAME 524 4146 21 0 0 0 0 1 TABLESPACE 68 1024 14 0 0 20 0 1 TEMPORARY FILENAME 56 1024 4 0 0 95 0 1 RMAN CONFIGURATION 1108 50 0 0 0 0 0 1 LOG HISTORY 56 292 159 1 159 159 0 1 OFFLINE RANGE 200 1063 8 1 8 8 0 INST_ID TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ---------- 1 ARCHIVED LOG 584 28 3 1 3 3 0 1 BACKUP SET 96 1022 1 1 1 1 0 1 BACKUP PIECE 780 1006 1 1 1 1 0 1 BACKUP DATAFILE 200 1063 1 1 1 1 0 1 BACKUP REDOLOG 76 215 0 0 0 0 0 1 DATAFILE COPY 736 1000 0 0 0 0 0 1 BACKUP CORRUPTION 44 1115 0 0 0 0 0 1 COPY CORRUPTION 40 1227 0 0 0 0 0 1 DELETED OBJECT 20 818 0 0 0 0 0 1 PROXY COPY 928 1004 0 0 0 0 0 1 BACKUP SPFILE 124 131 1 1 1 1 0 INST_ID TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ---------- 1 DATABASE INCARNATION 56 292 1 1 1 1 0 1 FLASHBACK LOG 84 2048 0 0 0 0 0 1 RECOVERY DESTINATION 180 1 1 0 0 0 0 1 INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0 1 REMOVABLE RECOVERY FILES 32 1000 0 0 0 0 0 1 RMAN STATUS 116 141 0 0 0 0 0 1 THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0 1 MTTR 100 8 1 0 0 0 0 1 DATAFILE HISTORY 568 57 0 0 0 0 0 1 STANDBY DATABASE MATRIX 400 31 31 0 0 0 0 1 GUARANTEED RESTORE POINT 212 2048 0 0 0 0 0 INST_ID TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ---------- 1 RESTORE POINT 212 2083 0 0 0 0 0 1 DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0 1 ACM OPERATION 104 64 9 0 0 0 0 1 FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0 1 PDB RECORD 684 10 4 0 0 10 0 1 AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0 1 MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0 1 PDBINC RECORD 144 113 0 0 0 0 0 41 rows selected. SQL> |
其中“gv$”开头的数据字典试图为Oracle RAC集群的全局视图。
通过v$database视图可以查看存储在控制文件中的与数据文件相关的信息：
|
1 2 3 4 5 6 7 |
SQL> select name,open_mode,to_char(created,'yyyy-mm-dd hh24:mi:ss') "Created Time",current_scn from v$database; NAME OPEN_MODE Created Time CURRENT_SCN --------- -------------------- ------------------- ----------- ATLAS MOUNTED 2014-11-02 17:32:08 0 SQL> |
关于查看数据文件的名称，除了可以通过上文的“select * from v$controlfile;”之外，还可以通过参数查看：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
[oracle@ora12c ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 9 13:41:12 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select status,instance_name from v$instance; STATUS INSTANCE_NAME ------------ ---------------- MOUNTED atlas SQL> alter database open; Database altered. SQL> show parameter control_files; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /oradata/ATLAS/controlfile/o1_ mf_b5cyjq6b_.ctl, /oradata/fas t_recovery_area/ATLAS/controlf ile/o1_mf_b5cyjqjb_.ctl SQL> |
或者，查看参数文件（spfile、pfile）：
SPFILE：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> show parameter pfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12/db_ 1/dbs/spfileatlas.ora SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ora12c ~]$ [oracle@ora12c ~]$ strings /u01/app/oracle/product/12/db_1/dbs/spfileatlas.ora | grep -i control_files *.control_files='/ [oracle@ora12c ~]$ [oracle@ora12c ~]$ strings /u01/app/oracle/product/12/db_1/dbs/spfileatlas.ora | grep -i ctl oradata/ATLAS/controlfile/o1_mf_b5cyjq6b_.ctl','/oradata/fast_recovery_area/ATLAS/controlfile/o1_mf_b5cyjqjb_.ctl' [oracle@ora12c ~]$ |
PFILE：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
[oracle@ora12c dbs]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 9 13:46:01 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options SQL> create pfile from spfile; File created. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options [oracle@ora12c dbs]$ pwd /u01/app/oracle/product/12/db_1/dbs [oracle@ora12c dbs]$ ls -ltr total 17580 -rw-r--r-- 1 oracle oinstall 2992 Feb 3 2012 init.ora -rw-r----- 1 oracle oinstall 24 Nov 2 17:32 lkATLAS -rw-r----- 1 oracle oinstall 7680 Nov 2 19:06 orapwatlas -rw-r----- 1 oracle oinstall 17973248 Nov 6 21:49 snapcf_atlas.f -rw-rw---- 1 oracle oinstall 1544 Nov 9 13:41 hc_atlas.dat -rw-r----- 1 oracle oinstall 3584 Nov 9 13:42 spfileatlas.ora -rw-r--r-- 1 oracle oinstall 1024 Nov 9 13:46 initatlas.ora [oracle@ora12c dbs]$ cat initatlas.ora | grep --color control_files *.control_files='/oradata/ATLAS/controlfile/o1_mf_b5cyjq6b_.ctl','/oradata/fast_recovery_area/ATLAS/controlfile/o1_mf_b5cyjqjb_.ctl' [oracle@ora12c dbs]$ |
增加控制文件。
复制当前的控制文件，并且将新的路径添加到CONTROL_FILES参数中即可。
该操作必须在关闭数据库的状态下才能完成。
具体细节查看：http://d-prototype.com/archives/1679
——————————————————
Ending。
