Oracle中控制文件损坏的处理方法

模拟现场：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.

ez8UG)A-p {0Total 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;

ITPUB个人空间,o X{8[s,g%TE&]
System altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount

Q"]n#V V_5?L0SQL> alter database backup controlfile to trace;

ITPUB个人空间W7YO3m#z
Database altered.

ITPUB个人空间5Y8A6C%wY W.Ng
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;

0\pcX#C+Z!X;~0SQL> alter system set control_files='d:r2control_01.ctl', 'd:r2control_01.ctl' scope=spfile;

ITPUB个人空间^ U1wr/t0R
System altered.

b\\"\
d`4M0SQL> shutdown immediate

ORA-01109: database not open

ITPUB个人空间 O0n7R,L)v9`5l.L#s
Database dismounted.

ORACLE instance shut down.

E7M1isEs0X0整理trc文件，总而言之一句话，将两个蓝色字体中间的内容copy出来，去掉带＃的语句，然后执行整理后的文件。

yF|u8~%[w"f8Q0SQL>conn / as sysdba;

SQL>@ldd.trc

5X/e7Oq*HBX0Trc文件范例

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

ITPUB个人空间6z/]@@^6b#}@_&s
Redo thread mounted by this instance: 1

Q;sNycv%d\0Oracle process number: 10

e%O6dPqH?0Windows 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.

#