How to rebuild the Controlfile ?

1.备份controlfile

  sql>alter database backup controlfile to trace

 

2.用户日志udump  (db_ora_29292.trc)

 

3.scripts

 

# 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 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 "WENHUA01" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 3 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO3A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO3B.LOG'
  ) SIZE 1M,
  GROUP 4 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4B.LOG'
  ) SIZE 1M,
  GROUP 5 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5B.LOG'
  ) SIZE 1M
-- STANDBY LOGFILE
DATAFILE
  'D:\ORACLE9I\ORADATA\WENHUA01\SYSTEM01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\UNDOTBS01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\EXAMPLE01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\INDX01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\CATALOG01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\OEM01.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 'D:\ORACLE9I\ORADATA\WENHUA01\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 "WENHUA01" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 3 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO3A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO3B.LOG'
  ) SIZE 1M,
  GROUP 4 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO4B.LOG'
  ) SIZE 1M,
  GROUP 5 (
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5A.LOG',
    'D:\ORACLE9I\ORADATA\WENHUA01\REDO5B.LOG'
  ) SIZE 1M
-- STANDBY LOGFILE
DATAFILE
  'D:\ORACLE9I\ORADATA\WENHUA01\SYSTEM01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\UNDOTBS01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\EXAMPLE01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\INDX01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\CATALOG01.DBF',
  'D:\ORACLE9I\ORADATA\WENHUA01\OEM01.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 'D:\ORACLE9I\ORADATA\WENHUA01\TEMP01.DBF'
     SIZE 41943040  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
# End of tempfile additions.