重建控制檔案--

重建控制檔案
方法一：
全都壞了的話...
希望你之前有做過
1. alter database backup controlfile to trace or
2. alter database backup controlfile to xxxx(file name)

如果是方法
1.是textfile 用vi看一下內容,去掉一些像 startup nomout,recover database 之類的字眼,
  然後去執行script. file,再下alter database open
 
如果是方法 
2.直接送到init.ora所設定的control_file的位置去就好了

controlfile要好好儲存好,理論上是不能lose的,good luck

方法二：

參考下面的script,修改成自己的日誌檔案和資料檔案就可以了,注意紅色字型部分
SQL> startup nomount
ORACLE 例程已經啟動。

Total System Global Area 31229980 bytes
Fixed Size 75804 bytes
Variable Size 14299136 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
SQL> CREATE CONTROLFILE reuse DATABASE "billy" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 64
3 MAXLOGMEMBERS 2
4 MAXDATAFILES 254
5 MAXINSTANCES 32
6 MAXLOGHISTORY 113
7 LOGFILE
8 GROUP 1 'E:\ORACLE\ORADATA\BILLY\REDO01.LOG' SIZE 1048576 ,
9 GROUP 2 'E:\ORACLE\ORADATA\BILLY\REDO02.LOG' SIZE 1048576 ,
10 GROUP 3 'E:\ORACLE\ORADATA\BILLY\REDO03.LOG' SIZE 1048576
11 DATAFILE
12 'E:\ORACLE\ORADATA\BILLY\SYSTEM01.DBF',
13 'E:\ORACLE\ORADATA\BILLY\RBS01.DBF',
14 'E:\ORACLE\ORADATA\BILLY\USERS01.DBF',
15 'E:\ORACLE\ORADATA\BILLY\TEMP01.DBF',
16 'E:\ORACLE\ORADATA\BILLY\TOOLS01.DBF',
17 'E:\ORACLE\ORADATA\BILLY\INDX01.DBF',
18 'E:\ORACLE\ORADATA\BILLY\DR01.DBF',
19 'E:\ORACLE\ORADATA\BILLY\DB4K.DBF'
20 CHARACTER SET ZHS16GBK
21 ;

方法三：

如何獲得建立控制檔案的指令碼並重建控制檔案
控制檔案對於資料庫來說是非常重要的資料結構，在進行資料恢復時通常是必不可少的.
Oracle給予兩種方法備份控制檔案:
1.生成可以重建控制檔案的指令碼script（批處理檔案）
2.備份二進位制的控制檔案
我們看一下怎麼獲得可以重建控制檔案的指令碼script（批處理檔案）.
Oracle給予如下命令:
 

alter database backup controlfile to trace;
                      
實際操作:
     
[oracle@standby tools]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 16 08:56:13 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i EnterPageRankise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> alter database backup controlfile to trace;
Database altered.
SQL> @gettrcname
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/PageRankimary/udump/PageRankimary_ora_2135.trc
       
trace檔案內容:
     
 
[oracle@standby tools]$ more /opt/oracle/admin/PageRankimary/udump/PageRankimary_ora_2135.trc
/opt/oracle/admin/PageRankimary/udump/PageRankimary_ora_2135.trc
Oracle9i EnterPageRankise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
ORACLE資料庫_HOME = /opt/oracle/PageRankoduct/9.2.0
System name:    Linux
Node name:      standby
Release:        2.4.21-4.EL
Version:        #1 Fri Oct 3 18:13:58 EDT 2003
Machine:        i686
Instance name: PageRankimary
Redo thread mounted by this instance: 1
Oracle PageRankocess number: 12
Unix PageRankocess pid: 2135, image: oracle@standby (TNS V1-V3)
*** SESSION ID:(11.6) 2004-10-16 09:00:03.830
*** 2004-10-16 09:00:03.830
# 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.dbf
# REMOTE_ARCHIVE_ENABLE=TRUE
# LOG_ARCHIVE_START=TRUE
# LOG_ARCHIVE_MAX_PageRankOCESSES=2
# STANDBY_FILE_MANAGEMENT=MANUAL
# STANDBY_ARCHIVE_DEST=?/dbs/arch
# FAL_CLIENT=''
# FAL_SERVER=''
#
# LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/oradata/PageRankimary/archive'
# LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
# LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM SYNC'
# LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
# LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
# 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 apPageRankoPageRankiate 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 the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PageRankIMARY" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
     MAXLOGFILES 5
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 1
     MAXLOGHISTORY 226
LOGFILE
   GROUP 1 '/opt/oracle/oradata/PageRankimary/redo01.log'  SIZE 10M,
   GROUP 2 '/opt/oracle/oradata/PageRankimary/redo02.log'  SIZE 10M,
   GROUP 3 '/opt/oracle/oradata/PageRankimary/redo03.log'  SIZE 10M
-- STANDBY LOGFILE
DATAFILE
   '/opt/oracle/oradata/PageRankimary/system01.dbf',
   '/opt/oracle/oradata/PageRankimary/undotbs01.dbf',
   '/opt/oracle/oradata/PageRankimary/users01.dbf'
CHARACTER SET ZHS16GBK
;
# 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 '/opt/oracle/oradata/PageRankimary/temp01.dbf'
      SIZE 41943040  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.
# The contents of online logs will be lost and all backups will
# be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PageRankIMARY" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
     MAXLOGFILES 5
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 1
     MAXLOGHISTORY 226
LOGFILE
   GROUP 1 '/opt/oracle/oradata/PageRankimary/redo01.log'  SIZE 10M,
   GROUP 2 '/opt/oracle/oradata/PageRankimary/redo02.log'  SIZE 10M,
   GROUP 3 '/opt/oracle/oradata/PageRankimary/redo03.log'  SIZE 10M
-- STANDBY LOGFILE
DATAFILE
   '/opt/oracle/oradata/PageRankimary/system01.dbf',
   '/opt/oracle/oradata/PageRankimary/undotbs01.dbf',
   '/opt/oracle/oradata/PageRankimary/users01.dbf'
CHARACTER SET ZHS16GBK
;
# 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 '/opt/oracle/oradata/PageRankimary/temp01.dbf'
      SIZE 41943040  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
# End of tempfile additions.
#
      
編輯這個trace檔案,我們就可以獲得建立控制檔案的指令碼script（批處理檔案）.
根據資料庫不相同狀況，你可以選擇是使用RESETLOGS/NORESETLOGS來重建控制檔案.
我們獲得以下指令碼script（批處理檔案）:

[oracle@standby tools]$ cat createctlf.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PageRankIMARY" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
     MAXLOGFILES 5
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 1
     MAXLOGHISTORY 226
LOGFILE
   GROUP 1 '/opt/oracle/oradata/PageRankimary/redo01.log'  SIZE 10M,
   GROUP 2 '/opt/oracle/oradata/PageRankimary/redo02.log'  SIZE 10M,
   GROUP 3 '/opt/oracle/oradata/PageRankimary/redo03.log'  SIZE 10M
-- STANDBY LOGFILE
DATAFILE
   '/opt/oracle/oradata/PageRankimary/system01.dbf',
   '/opt/oracle/oradata/PageRankimary/undotbs01.dbf',
   '/opt/oracle/oradata/PageRankimary/users01.dbf'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/PageRankimary/temp01.dbf'
      SIZE 41943040  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;     
                      
執行這個指令碼script（批處理檔案）即可重建控制檔案:

[oracle@standby tools]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 16 09:20:24 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
SQL> set echo on
SQL> @createctlf
SQL> STARTUP NOMOUNT
ORACLE資料庫 instance started.
Total System Global Area  135337420 bytes
Fixed Size                   452044 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "PageRankIMARY" NORESETLOGS  ARCHIVELOG
   2  --  SET STANDBY TO MAXIMIZE PERFORMANCE
   3      MAXLOGFILES 5
   4      MAXLOGMEMBERS 3
   5      MAXDATAFILES 100
   6      MAXINSTANCES 1
   7      MAXLOGHISTORY 226
   8  LOGFILE
   9    GROUP 1 '/opt/oracle/oradata/PageRankimary/redo01.log'  SIZE 10M,
  10    GROUP 2 '/opt/oracle/oradata/PageRankimary/redo02.log'  SIZE 10M,
  11    GROUP 3 '/opt/oracle/oradata/PageRankimary/redo03.log'  SIZE 10M
  12  -- STANDBY LOGFILE
  13  DATAFILE
  14    '/opt/oracle/oradata/PageRankimary/system01.dbf',
  15    '/opt/oracle/oradata/PageRankimary/undotbs01.dbf',
  16    '/opt/oracle/oradata/PageRankimary/users01.dbf'
  17  CHARACTER SET ZHS16GBK
  18  ;
Control file created.
SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/PageRankimary/temp01.dbf'
   2       SIZE 41943040  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
Tablespace altered.
SQL>      
                      
以上給出生成建立控制檔案指令碼script（批處理檔案）並重建控制檔案的方法，但是具體恢復中遇到的問題可能需要具體對待.
這種方法通常是在沒有控制檔案(二進位制檔案)備份的情況下所採用的，如果存在備份應該使用備份的控制檔案嘗試恢復

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25850100/viewspace-717284/，如需轉載，請註明出處，否則將追究法律責任。