ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 重建控制文件--

重建控制文件--

原创 Linux操作系统 作者:tom_xieym 时间:2012-02-28 14:51:39 0 删除 编辑

重建控制文件
方法一:
全都壞了的話...
希望你之前有做過
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-05-20

  • 博文量
    77
  • 访问量
    92583