辽宁海城东四杀人案: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.trcOracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1System name:    LinuxNode name:      qs-dmm-rh2Release:        2.6.18-194.el5Version:        #1 SMP Tue Mar 16 21:52:43 EDT 2010Machine:        i686Instance name: daveRedo thread mounted by this instance: 0 Oracle process number: 15Unix 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.401ORA-01160: file is not a data fileORA-01110: data file : '/u01/app/oracle/oradata/dave/temp01.dbf'*** 2011-03-17 22:08:25.791Control file created with size 370 blocks*** 2011-03-17 22:10:21.444tkcrrsarc: (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: 24kwqmnich: instance no 0 check_only flag 1 kwqmnich: initialized job cache structure krvscm(+): Validating controlfile with logical metadatakrvscm(+): Initial controlfile statekrvscm(+):   kccdiflg [400001] kccdifl2 [1000]krvscm(+):   kccdi2ldscn [0x0000.00000000]krvscm(+):   kccdi2lrscn [0x0000.00000000]krvscm(+): Inspecting logical metadatakrvscm(+): Metadata statekrvscm(+):   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 metadatakrvscm(+): Updating controlfile with new statekrvscm(+): New controlfile statekrvscm(+):   kccdiflg [400001] kccdifl2 [1000]krvscm(+):   kccdi2ldscn [0x0000.00000000]krvscm(+):   kccdi2lrscn [0x0000.00000000]krvscm(+): Updating SGA associated with controlfile statekrvscm(+): 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 PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "DAVE" NORESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 2    MAXDATAFILES 30    MAXINSTANCES 1    MAXLOGHISTORY 292LOGFILE  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 LOGFILEDATAFILE  '/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 PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "DAVE" RESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 2    MAXDATAFILES 30    MAXINSTANCES 1    MAXLOGHISTORY 292LOGFILE  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 LOGFILEDATAFILE  '/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）db 启动到mount状态            SQL> startup nomount （2）创建控制文件create controlfile reuse database dave noresetlogs archivelogLOGFILEGROUP 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' sieze 100M; Tablespace altered. 注意：            如果使用resetlogs 打开的数据库，就需要对DB做一次备份。            resetlogs命令表示一个数据库逻辑生存期的结束和另一个数据库逻辑生存期的开始，每次使用resetlogs命令的时候，SCN不会被重置，不过oracle会重置日志序列号，而且会重置联机重做日志内容.            这样做是为了防止不完全恢复后日志序列会发生冲突（因为现有日志和数据文件间有了时间差）。  本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/tianlesoftware/archive/2009/12/09/4974440.aspx