重建控制文件

重建控制文件：

oracle@dg1:~> echo $ORACLE_SID
wjldg


SQL> alter database backup controlfile to trace;

Database altered.

oracle@dg1:~/admin/wjldg/udump> cat wjldg_ora_8233.trc
/opt/oracle/admin/wjldg/udump/wjldg_ora_8233.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9iR2
System name:    Linux
Node name:      dg1
Release:        2.6.5-7.308-default
Version:        #1 Mon Dec 10 11:36:40 UTC 2007
Machine:        x86_64
Instance name: wjldg
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 8233, image: oracle@dg1 (TNS V1-V3)

*** SESSION ID:(9.7) 2011-04-25 13:00:19.384
*** 2011-04-25 13:00:19.384
# 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_PROCESSES=2
# STANDBY_FILE_MANAGEMENT=MANUAL
# STANDBY_ARCHIVE_DEST=?/dbs/arch
# FAL_CLIENT=''
# FAL_SERVER=''
#
# LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/oradata/wjldg/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 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 "WJLDG" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/opt/oracle/oradata/wjldg/redo01.log'  SIZE 100M,
  GROUP 2 '/opt/oracle/oradata/wjldg/redo02.log'  SIZE 100M,
  GROUP 3 '/opt/oracle/oradata/wjldg/redo03.log'  SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/opt/oracle/oradata/wjldg/system01.dbf',
  '/opt/oracle/oradata/wjldg/undotbs01.dbf',
  '/opt/oracle/oradata/wjldg/indx01.dbf',
  '/opt/oracle/oradata/wjldg/tools01.dbf',
  '/opt/oracle/oradata/wjldg/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/wjldg/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 "WJLDG" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/opt/oracle/oradata/wjldg/redo01.log'  SIZE 100M,
  GROUP 2 '/opt/oracle/oradata/wjldg/redo02.log'  SIZE 100M,
  GROUP 3 '/opt/oracle/oradata/wjldg/redo03.log'  SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/opt/oracle/oradata/wjldg/system01.dbf',
  '/opt/oracle/oradata/wjldg/undotbs01.dbf',
  '/opt/oracle/oradata/wjldg/indx01.dbf',
  '/opt/oracle/oradata/wjldg/tools01.dbf',
  '/opt/oracle/oradata/wjldg/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/wjldg/temp01.dbf'
     SIZE 41943040  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
# End of tempfile additions.
#
oracle@dg1:~/admin/wjldg/udump>








SQL> alter database force logging;

Database altered.

SQL> alter database backup controlfile to trace;

Database altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  202865936 bytes
Fixed Size                   740624 bytes
Variable Size             167772160 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "WJLDG" NORESETLOGS  ARCHIVELOG
  2  MAXLOGFILES 50
  3  MAXLOGMEMBERS 3
  4  MAXDATAFILES 100
  5  MAXINSTANCES 1
  6  MAXLOGHISTORY 226
LOGFILE
 GROUP 1 '/opt/oracle/oradata/wjldg/redo01.log'  SIZE 100M,
 GROUP 2 '/opt/oracle/oradata/wjldg/redo02.log'  SIZE 100M,
 GROUP 3 '/opt/oracle/oradata/wjldg/redo03.log'  SIZE 100M
DATAFILE
 '/opt/oracle/oradata/wjldg/system01.dbf',
 '/opt/oracle/oradata/wjldg/undotbs01.dbf',
  7    8    9   10   11   12   13   14   '/opt/oracle/oradata/wjldg/indx01.dbf',
 15   '/opt/oracle/oradata/wjldg/tools01.dbf',
 16   '/opt/oracle/oradata/wjldg/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/wjldg/temp01.dbf'
  2       SIZE 41943040  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

SQL>


select * from v$controlfile_record_section where type='REDO LOG';