Oracle 控制文件
一. Oracle 控制文件主要包含如下条目
DATABASE ENTRY
CHECKPOINT PROGRESS RECORDS
REDO THREAD RECORDS
LOG FILE RECORDS
DATA FILE RECORDS
TEMP FILE RECORDS
TABLESPACE RECORDS
LOG FILE HISTORY RECORDS
OFFLINE RANGE RECORDS
ARCHIVED LOG RECORDS
BACKUP SET RECORDS
BACKUP PIECE RECORDS
BACKUP DATAFILE RECORDS
BACKUP LOG RECORDS
DATAFILE COPY RECORDS
BACKUP DATAFILE CORRUPTION RECORDS
DATAFILE COPY CORRUPTION RECORDS
DELETION RECORDS
PROXY COPY RECORDS
INCARNATION RECORDS
二. 可以通过dump看到 控制文件内
2.1 直接dump controlfile
alter system set events 'immediate trace name controlf level 10'
2.2. 使用alter database backup controlfile to filename
以上两种方法生成的dump文件是不可读的即乱码。 只有生成trace后，才是可读的。
2.2. 使用alter database backup controlfile to trace
生成的trace 文件在udump 目录下，可以通过日期来判断。
SQL>show parameter user_dump_dest
也可以使用如下SQL 查询对应的trace 文件：
SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc'
trace_file
FROM (SELECT VALUE
FROM v$parameter
WHERE name = 'user_dump_dest') a,
(SELECT SUBSTR (VALUE, -6, 1) symbol
FROM v$parameter
WHERE name = 'user_dump_dest') b,
(SELECT instance_name FROM v$instance) c,
(SELECT spid
FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND s.sid = m.sid AND m.statistic# = 0) d
TRACE_FILE
--------------------------------------------------------------------------------
/u01/app/oracle/admin/dave/udump/dave_ora_7215.trc
整个Trace 的内容如下：
[oracle@qs-dmm-rh2 udump]$ cat dave_ora_7215.trc
/u01/app/oracle/admin/dave/udump/dave_ora_7215.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: qs-dmm-rh2
Release: 2.6.18-194.el5
Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010
Machine: i686
Instance name: dave
Redo thread mounted by this instance: 0 <none>
Oracle process number: 15
Unix process pid: 7215, image: oracle@qs-dmm-rh2 (TNS V1-V3)
*** ACTION NAME:() 2011-03-17 22:05:46.401
*** MODULE NAME:(sqlplus@qs-dmm-rh2 (TNS V1-V3)) 2011-03-17 22:05:46.401
*** SERVICE NAME:() 2011-03-17 22:05:46.401
*** SESSION ID:(159.1) 2011-03-17 22:05:46.401
ORA-01160: file is not a data file
ORA-01110: data file : '/u01/app/oracle/oradata/dave/temp01.dbf'
*** 2011-03-17 22:08:25.791
Control file created with size 370 blocks
*** 2011-03-17 22:10:21.444
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
kwqmnich: current time:: 14: 10: 24
kwqmnich: instance no 0 check_only flag 1
kwqmnich: initialized job cache structure
krvscm(+): Validating controlfile with logical metadata
krvscm(+): Initial controlfile state
krvscm(+): kccdiflg [400001] kccdifl2 [1000]
krvscm(+): kccdi2ldscn [0x0000.00000000]
krvscm(+): kccdi2lrscn [0x0000.00000000]
krvscm(+): Inspecting logical metadata
krvscm(+): Metadata state
krvscm(+): hasPrepSwitchSta [0]
krvscm(+): hasPrepSwitchPri [0]
krvscm(+): hasReceivedDict [0]
krvscm(+): hasDumpedDict [0]
krvscm(+): hasCommittedBor [0]
krvscm(+): hasSwitchedFromPri [0]
krvscm(+): hasStartedTa [0]
krvscm(+): hasValidSess [0]
krvscm(+): hasTxnConsistency [0]
krvscm(+): hasCleanlyShutdown [0]
krvscm(+): Generating new controlfile state from metadata
krvscm(+): Updating controlfile with new state
krvscm(+): New controlfile state
krvscm(+): kccdiflg [400001] kccdifl2 [1000]
krvscm(+): kccdi2ldscn [0x0000.00000000]
krvscm(+): kccdi2lrscn [0x0000.00000000]
krvscm(+): Updating SGA associated with controlfile state
krvscm(+): Validation complete
*** 2011-03-17 22:13:21.115
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="dave_st"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
-- LOG_ARCHIVE_CONFIG='DG_CONFIG=("dave_pd")'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=AUTO
-- STANDBY_ARCHIVE_DEST=/u01/archivelog
-- FAL_CLIENT=dave_st
-- FAL_SERVER=dave_pd
--
-- LOG_ARCHIVE_DEST_2='SERVICE=dave_pd'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=120 NODELAY'
-- LOG_ARCHIVE_DEST_2='LGWR NOAFFIRM NOEXPEDITE NOVERIFY ASYNC=61440'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_2='DB_UNIQUE_NAME=dave_pd'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_1='DB_UNIQUE_NAME=dave_st'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--对使用noresetlogs 的说明
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DAVE" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/dave/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/dave/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/dave/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/dave/system01.dbf',
'/u01/app/oracle/oradata/dave/undotbs01.dbf',
'/u01/app/oracle/oradata/dave/sysaux01.dbf',
'/u01/app/oracle/oradata/dave/users01.dbf'
CHARACTER SET ZHS16GBK
;
--以上是创建控制文件的语法
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/archivelog/1_1_746031707.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dave/temp01.dbf'
SIZE 32505856 REUSE AUTOEXTEND OFF;
--这里是要注意的地方，重建控制文件的时候，不能写上临时表空间，等控制文件创建完毕之后，在手工的执行SQL加上临时表空间。
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--第二种情况，使用resetlogs 的说明
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DAVE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/dave/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/dave/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/dave/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/dave/system01.dbf',
'/u01/app/oracle/oradata/dave/undotbs01.dbf',
'/u01/app/oracle/oradata/dave/sysaux01.dbf',
'/u01/app/oracle/oradata/dave/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/archivelog/1_1_746031707.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dave/temp01.dbf'
SIZE 32505856 REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
注意里面的几个参数：
（1） MAXDATAFILES
The MAXDATAFILES option of CREATE DATABASE determines the number of data files a database can have. With Oracle Real Application Clusters, databases tend to have more data files and log files than an exclusive mounted database.
The MAXINSTANCES option of CREATE DATABASE limits the number of instances that can access a database concurrently. The default value for this option under z/OS is 15. Set MAXINSTANCES to a value greater than the maximum number of instances you expect to run concurrently.
（3）MAXLOGFILE and MAXLOGMEMBERS
The MAXLOGFILES option of CREATE DATABASE specifies the maximum number of redo log groups that can be created for the database. The MAXLOGMEMBERS option specifies the maximum number of members or number of copies per group. Set MAXLOGFILES to the maximum number of instances you plan to run concurrently multiplied by the maximum anticipated number of groups per thread.
The MAXLOGHISTORY option of CREATE DATABASE specifies the maximum number of redo log files that can be recorded in the log history of the control file. The log history is used for automatic media recovery of Oracle Real Application Clusters.
For Oracle Real Application Clusters, set MAXLOGHISTORY to a large value, such as 100. The control file can then store information about this number of redo log files. When the log history exceeds this limit, the Oracle server overwrites the oldest entries in the log history. The default for MAXLOGHISTORY is 0 (zero), which disables log history.
这4个参数中，我们在创建DB 时需要注意的是MAXDATAFILES 和MAXLOGHISTORY。 因为默认值较小。 在创建DB 时就需要把这2个参数设置成较大值。
比如MAXDATAFILES 设置成8k，MAXLOGHISTORY 设置成1k。
与这几个参数相关的错误：
ORA-01164: MAXLOGFILES may not exceed string
Cause: MAXLOGFILES specified on the command line too large.
Action: Resubmit the command with a smaller MAXLOGFILES
ORA-01165: MAXDATAFILES may not exceed string
Cause: MAXDATAFILES specified on the command line too large.
Action: Resubmit the command with a smaller MAXDATAFILES
ORA-01166: file number string is larger than string (string)
Cause: File mentioned in CREATE CONTROLFILE has a file number which is larger than that specified for MAXDATAFILES or MAXLOGFILES.
Action: Increase the maximum specified on the command line.
三. 控制文件的重建
不到最后时刻，如三个控制文件都已损坏，又没有控制文件的备份。还是不要重建控制文件，处理不好就会有数据丢失。
（1）db 启动到mount状态
SQL> startup nomount
（2）创建控制文件
create controlfile reuse database dave noresetlogs archivelog
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/dave/redo01.log',
GROUP 2 '/u01/app/oracle/oradata/dave/redo02.log',
GROUP 3 '/u01/app/oracle/oradata/dave/redo03.log'
DATAFILE
'/u01/app/oracle/oradata/dave/sysaux01.dbf',
'/u01/app/oracle/oradata/dave/system01.dbf',
'/u01/app/oracle/oradata/dave/undotbs01.dbf',
'/u01/app/oracle/oradata/dave/users01.dbf'
CHARACTER SET ZHS16GBK;
我这里使用的是noresetlogs，所以直接open数据库就可以了：
SQL>alter database open;
如果是resetlogs 创建的控制文件，那么我们就需要使用：
SQL>alter database open resetlogs;
来打开DB.
（3）添加TEMP 表空间
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dave/temp01.dbf' size 100M;
Tablespace altered.
注意：
如果使用resetlogs 打开的数据库，就需要对DB做一次备份。
resetlogs命令表示一个数据库逻辑生存期的结束和另一个数据库逻辑生存期的开始，每次使用resetlogs命令的时候，SCN不会被重置，不过oracle会重置日志序列号，而且会重置联机重做日志内容.
这样做是为了防止不完全恢复后日志序列会发生冲突（因为现有日志和数据文件间有了时间差）。
---------------------------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823
聊天 群：40132017
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
