Oracle 控制文件 说明

一. Oracle 控制文件内容 我们可以通过v$controlfile_record_section 视图查看控制文件里包含的内容。 SQLgt; select type

一. Oracle 控制文件内容

我们可以通过v$controlfile_record_section 视图查看控制文件里包含的内容。

SQL> select type from v$controlfile_record_section;

TYPE

--------------------------------------------------------

DATABASE

CKPT PROGRESS

REDO THREAD

REDO LOG

DATAFILE

FILENAME

TABLESPACE

TEMPORARY FILENAME

RMAN CONFIGURATION

LOG HISTORY

OFFLINE RANGE

ARCHIVED LOG

BACKUP SET

BACKUP PIECE

BACKUP DATAFILE

BACKUP REDOLOG

DATAFILE COPY

BACKUP CORRUPTION

COPY CORRUPTION

DELETED OBJECT

PROXY COPY

BACKUP SPFILE

DATABASE INCARNATION

FLASHBACK LOG

RECOVERY DESTINATION

INSTANCE SPACE RESERVATION

REMOVABLE RECOVERY FILES

RMAN STATUS

THREAD INSTANCE NAME MAPPING

MTTR

DATAFILE HISTORY

STANDBY DATABASE MATRIX

GUARANTEED RESTORE POINT

RESTORE POINT

DATABASE BLOCK CORRUPTION

ACM OPERATION

FOREIGN ARCHIVED LOG

37 rows selected.

二. 可以通过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 parameteruser_dump_dest

也可以使用如下SQL 查询对应的trace 文件：

SELECT a.VALUE ||b.symbol|| c.instance_name|| '_ora_' ||d.spid|| '.trc'

trace_file

FROMv$parameter

symbol

FROMv$parameter

b,

(SELECTinstance_name FROMv$instance) c,

(SELECTspid

FROMv$session s,v$process p,v$mystat m

statistic#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 ApplicationTesting 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 Mar16 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-1722: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 initializationfile.

--

-- 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 NOVERIFYASYNC=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 setopens

-- the database with the NORESETLOGS option and should be used onlyif

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should beused

-- if online logs are unavailable.

-- The appropriate set of statements can be copiedfrom the trace into

-- a script file, edited as necessary, andexecuted 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 STANDBYDATABASE 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 restoredbackups,

-- or if the last shutdown was not normal orimmediate.

RECOVER DATABASE

-- All logs need archiving and a log switch isneeded.

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 STANDBYDATABASE 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 datafilesare 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