Oracle 控制文件

Oracle 控制文件

分类： Oracle 基础知识 2009-12-09 21:41 2159人阅读 评论(10) 举报

 

 

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

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）

       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.

（2） MAXINSTANCES

       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.

（4）MAXLOGHISTORY

       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会重置日志序列号，而且会重置联机重做日志内容.

       这样做是为了防止不完全恢复后日志序列会发生冲突（因为现有日志和数据文件间有了时间差）。