模拟现场:2个控制文件d:r2control_01.ctl, d:r2control_02.ctl,其中d:r2control_02.ctl损坏。
$sqlplus /nolog
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 34675092 bytes
Fixed Size 453012 bytes
Variable Size 25165824 bytes
Database Buffers 8388608 bytes
Redo Buffers 667648 bytes
SQL> alter system set control_files=' d:r2control_01.ctl' scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
SQL> alter database backup controlfile to trace;
Database altered.
SQL> select d.value '/' lower(rtrim(i.instance,chr(0))) '_ora_' p.spid '.trc' trace from
(select r.spid from sys.v$mystat m,sys.v$session s,sys.v$process r where m.statistic#=1 and s.sid=m.sid and r.addr=s.paddr) p,
(select t.instance from sys.v$thread t,sys.v$parameter v where v.name='thread' and (v.value=0 or t.thread#=to_number(v.value))) i,
(select value from sys.v$parameter where name='user_dump_dest') d;
SQL> alter system set control_files='d:r2control_01.ctl', 'd:r2control_01.ctl' scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
整理trc文件,总而言之一句话,将两个蓝色字体中间的内容copy出来,去掉带#的语句,然后执行整理后的文件。
SQL>conn / as sysdba;
SQL>@ldd.trc
Trc文件范例
Dump file d:r2udumpr2_ora_3136.trc
Sun Dec 10 00:07:51 2006
ORACLE V9.2.0.7.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Instance name: r2
Redo thread mounted by this instance: 1
Oracle process number: 10
Windows thread id: 3136, image: ORACLE.EXE
*** SESSION ID:(9.7) 2006-12-10 00:07:51.106
*** 2006-12-10 00:07:51.106
# 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=ARC%S.%T
# REMOTE_ARCHIVE_ENABLE=TRUE
# LOG_ARCHIVE_MAX_PROCESSES=2
# STANDBY_FILE_MANAGEMENT=MANUAL
# STANDBY_ARCHIVE_DEST=%ORACLE_HOME%RDBMS
# FAL_CLIENT=''
# FAL_SERVER=''
#
# LOG_ARCHIVE_DEST_1='LOCATION=d:ora9r2RDBMS'
# LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
# LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM SYNC'
# LOG_ARCHIVE_DEST_1='NOREGISTER 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 "R2" NORESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 56
LOGFILE
GROUP 1 'D:R2LOG_1A.DBF' SIZE 10M,
GROUP 2 'D:R2LOG_2A.DBF' SIZE 10M,
GROUP 3 'D:R2LOG_3A.DBF' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'D:R2SYSTEM.DBF',
'D:R2UNDO.DBF',
'D:R2EXAMPLE.DBF',
'D:R2TEST.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
# 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:R2TEMP.DBF'
SIZE 16777216 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 8191M;
# 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 "R2" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 56
LOGFILE
GROUP 1 'D:R2LOG_1A.DBF' SIZE 10M,
GROUP 2 'D:R2LOG_2A.DBF' SIZE 10M,
GROUP 3 'D:R2LOG_3A.DBF' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'D:R2SYSTEM.DBF',
'D:R2UNDO.DBF',
'D:R2EXAMPLE.DBF',
'D:R2TEST.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:R2TEMP.DBF'
SIZE 16777216 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 8191M;
# End of tempfile additions.
#