Oracle 11g重建控制文件——如何获取创建控制文件脚本

控制文件对于数据库来说是非常重要，在进行数据恢复时通常是必不可少的；丢失控制文件并不是致命的，但是会使恢复变得困难很多。因此，dba除了多路保存控制文件外，还需要备份控制文件。
     备份控制文件也有多种办法，例如cp拷贝、rman自动备份等，今天给大家介绍如何获取创建控制文件的脚本，这样数据库一旦出现意外，即使找不到可用的备份控制文件，也能通过重建控制文件来启动数据库。
     Oracle提供了如下命令来实现这个功能：
      alter database backup controlfile to trace;
 

1.环境准备

我们在Oracle11g中进行测试。
 

 SQL> 
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>

2.生成trace文件

打开数据库，执行‘alter database backup controlfile to trace;’语句来生成trace文件。

    [oracle@hoegh ~]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Sat May 30 12:31:02 2015

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> 
    SQL> startup
    ORACLE instance started.

    Total System Global Area 941600768 bytes
    Fixed Size 1348860 bytes
    Variable Size 515902212 bytes
    Database Buffers 419430400 bytes
    Redo Buffers 4919296 bytes
    Database mounted.
    Database opened.
    SQL> 
    SQL> 
    SQL> alter database backup controlfile to trace;

    Database altered.

    SQL>

3.查找trace文件路径

trace文件的路径可以通过一个多表查询来获得，其中涉及v$processv、$sessionv和$mystat等三个动态视图。
 

    SQL> 
    SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

    TRACEFILE
    --------------------------------------------------------------------------------
    /u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/HOEGH_ora_4831.trc

    SQL>

 

4.查看trace文件

通过cat命令查看trace文件，内容如下：
 

点击(此处)折叠或打开

 Trace file /u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/HOEGH_ora_4831.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: hoegh
Release: 2.6.18-164.el5PAE
Version: #1 SMP Thu Sep 3 02:28:20 EDT 2009
Machine: i686
VM name: VMWare Version: 6
Instance name: HOEGH
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 4831, image: oracle@hoegh (TNS V1-V3)


*** 2015-05-30 12:31:12.588
*** SESSION ID:(125.5) 2015-05-30 12:31:12.588
*** CLIENT ID:() 2015-05-30 12:31:12.588
*** SERVICE NAME:() 2015-05-30 12:31:12.588
*** MODULE NAME:(sqlplus@hoegh (TNS V1-V3)) 2015-05-30 12:31:12.588
*** ACTION NAME:() 2015-05-30 12:31:12.588
 
Successfully allocated 2 recovery slaves
Using 66 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 7, block 2, scn 898139
  cache-low rba: logseq 7, block 118
    on-disk rba: logseq 7, block 143, scn 898639
  start recovery at logseq 7, block 118, scn 0

*** 2015-05-30 12:31:12.690
Started writing zeroblks thread 1 seq 7 blocks 143-150

*** 2015-05-30 12:31:12.690
Completed writing zeroblks thread 1 seq 7
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 12Kb in 0.08s => 0.15 Mb/sec
Longest record: 1Kb, moves: 0/51 (0%)
Change moves: 2/35 (5%), moved: 0Mb
Longest LWN: 4Kb, moves: 0/9 (0%), moved: 0Mb
Last redo scn: 0x0000.000db64e (898638)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 262144
Longest hash chain = 1
Average hash chain = 9/9 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 29/75 = 0.4
----------------------------------------------

*** 2015-05-30 12:31:12.692
KCRA: start recovery claims for 9 data blocks

*** 2015-05-30 12:31:12.703
KCRA: blocks processed = 9/9, claimed = 9, eliminated = 0

*** 2015-05-30 12:31:12.704
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0

*** 2015-05-30 12:31:12.704
Completed redo application of 0.00MB

*** 2015-05-30 12:31:12.709
Completed recovery checkpoint
----- Recovery Hash Table Statistics ---------
Hash table buckets = 262144
Longest hash chain = 1
Average hash chain = 9/9 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 22/38 = 0.6
----------------------------------------------
Recovery sets nab of thread 1 seq 7 to 143 with 8 zeroblks

*** 2015-05-30 12:31:13.044
kwqmnich: current time:: 4: 31: 12: 0
kwqmnich: instance no 0 repartition flag 1 
kwqmnich: initialized job cache structure 
kwqinfy: Call kwqrNondurSubInstTsk 

*** 2015-05-30 12:31:30.897
-- 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=\"HOEGH\"
--
-- LOG_ARCHIVE_CONFIG=\'SEND, RECEIVE, NODG_CONFIG\'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=\'\'
-- FAL_SERVER=\'\'
--
-- LOG_ARCHIVE_DEST_1=\'LOCATION=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch\'
-- LOG_ARCHIVE_DEST_1=\'MANDATORY NOREOPEN NODELAY\'
-- LOG_ARCHIVE_DEST_1=\'ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC\'
-- LOG_ARCHIVE_DEST_1=\'NOREGISTER NOALTERNATE NODEPENDENCY\'
-- LOG_ARCHIVE_DEST_1=\'NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME\'
-- 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
--
-- 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 \"HOEGH\" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 \'/u01/app/oracle/oradata/HOEGH/redo01.log\' SIZE 50M BLOCKSIZE 512,
  GROUP 2 \'/u01/app/oracle/oradata/HOEGH/redo02.log\' SIZE 50M BLOCKSIZE 512,
  GROUP 3 \'/u01/app/oracle/oradata/HOEGH/redo03.log\' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  \'/u01/app/oracle/oradata/HOEGH/system01.dbf\',
  \'/u01/app/oracle/oradata/HOEGH/sysaux01.dbf\',
  \'/u01/app/oracle/oradata/HOEGH/undotbs01.dbf\',
  \'/u01/app/oracle/oradata/HOEGH/users01.dbf\',
  \'/u01/app/oracle/oradata/HOEGH/example01.dbf\'
CHARACTER SET AL32UTF8
;
-- 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/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_762197622.dbf\';
-- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_860888149.dbf\';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- 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/HOEGH/temp01.dbf\'
     SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- 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 \"HOEGH\" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 \'/u01/app/oracle/oradata/HOEGH/redo01.log\' SIZE 50M BLOCKSIZE 512,
  GROUP 2 \'/u01/app/oracle/oradata/HOEGH/redo02.log\' SIZE 50M BLOCKSIZE 512,
  GROUP 3 \'/u01/app/oracle/oradata/HOEGH/redo03.log\' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  \'/u01/app/oracle/oradata/HOEGH/system01.dbf\',
  \'/u01/app/oracle/oradata/HOEGH/sysaux01.dbf\',
  \'/u01/app/oracle/oradata/HOEGH/undotbs01.dbf\',
  \'/u01/app/oracle/oradata/HOEGH/users01.dbf\',
  \'/u01/app/oracle/oradata/HOEGH/example01.dbf\'
CHARACTER SET AL32UTF8
;
-- 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/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_762197622.dbf\';
-- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_860888149.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/HOEGH/temp01.dbf\'
     SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.

 

5.NORESETLOGS VS RESETLOGS

从trace文件可以看出，提供了两个创建控制文件的脚本，分别针对两种不同的应用场景：NORESETLOGS适用于当前redo log可用，而RESETLOGS适用于当前redo log不可用。
 

NORESETLOGS重建步骤

1.执行脚本，重建控制文件（CREATE CONTROLFILE REUSE DATABASE "HOEGH" NORESETLOGS NOARCHIVELOG......）
2.执行介质恢复RECOVER DATABASE
3.打开数据库ALTER DATABASE OPEN;
4.添加临时表空间
 

RESETLOGS重建步骤

1.执行脚本，重建控制文件（CREATE CONTROLFILE REUSE DATABASE "HOEGH" RESETLOGS NOARCHIVELOG......）
2.执行介质恢复RECOVER DATABASE USING BACKUP CONTROLFILE
3.打开数据库ALTER DATABASE OPEN RESETLOGS;

4.添加临时表空间