oracle控制文件恢复失败,Oracle控制文件损坏恢复

一、使用trace文件恢复

SQL> alter database backup controlfile to trace; ——将控制文件备份到trace文件

Database altered.

SQL> @gettrace  --得到trace文件的路径

TRACE_FILE_NAME-

--------------------------------------------------------------------------------

/Oracle/app/admin/ora10g/udump/ora10g_ora_31270.trc

SQL> !

[Oracle@www.linuxidc.com ~]$ more /oracle/app/admin/ora10g/udump/ora10g_ora_31270.trc

/Oracle/app/admin/ora10g/udump/ora10g_ora_31270.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Oracle_HOME = /oracle/app/oracle/product/10.2.0/db_1

System name:    Linux

Node name:      www.linuxidc.com

Release:        2.6.18-164.el5

.

.

.

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oradata/ora10g/redo01.log'  SIZE 50M,

GROUP 2 '/oradata/ora10g/redo02.log'  SIZE 50M,

GROUP 3 '/oradata/ora10g/redo03.log'  SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/oradata/ora10g/system01.dbf',

'/oradata/ora10g/undotbs01.dbf',

.

.

.

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf'

SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- End of tempfile additions.

--

[Oracle@www.linuxidc.com udump]$  cd

[Oracle@www.linuxidc.com ~]$ vim recontro.sql ——拷贝trace文件中有用的语句重建控制文件。如果在之前没有备份的trace文件，我们可以从init{SID}.ora文件中得到数据文件、日志文件、数据库表空间等信息，把init{SID}.ora文件中的内容按以下格式创建重构控制文件脚本

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oradata/ora10g/redo01.log'  SIZE 50M,

GROUP 2 '/oradata/ora10g/redo02.log'  SIZE 50M,

GROUP 3 '/oradata/ora10g/redo03.log'  SIZE 50M

DATAFILE

'/oradata/ora10g/system01.dbf',

'/oradata/ora10g/undotbs01.dbf',

'/oradata/ora10g/sysaux01.dbf',

'/oradata/ora10g/users01.dbf'

CHARACTER SET ZHS16GBK

;

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oradata/ora10g/redo01.log'  SIZE 50M,

GROUP 2 '/oradata/ora10g/redo02.log'  SIZE 50M,

GROUP 3 '/oradata/ora10g/redo03.log'  SIZE 50M

DATAFILE

'/oradata/ora10g/system01.dbf',

'/oradata/ora10g/undotbs01.dbf',

'/oradata/ora10g/sysaux01.dbf',

'/oradata/ora10g/users01.dbf'

CHARACTER SET ZHS16GBK

;

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

"recontro.sql" 49L, 1641C 已写入

[Oracle@www.linuxidc.com ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 19 03:30:34 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate

Database closed.

Database dismounted.

Oracle instance shut down.

SQL> set echo on       ——打开显示输出

SQL> @recontro        ——使用刚建立的重构控制文件的脚本重建控制文件

SQL> STARTUP NOMOUNT

Oracle instance started.

Total System Global Area  209715200 bytes

Fixed Size                  1218556 bytes

Variable Size              75499524 bytes

Database Buffers          130023424 bytes

Redo Buffers                2973696 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS     ARCHIVELOG

2      MAXLOGFILES 16

3      MAXLOGMEMBERS 3

4      MAXDATAFILES 100

5      MAXINSTANCES 8

6      MAXLOGHISTORY 292

7  LOGFILE

8    GROUP 1 '/oradata/ora10g/redo01.log'     SIZE 50M,

9    GROUP 2 '/oradata/ora10g/redo02.log'     SIZE 50M,

10    GROUP 3 '/oradata/ora10g/redo03.log'     SIZE 50M

11  DATAFILE

12    '/oradata/ora10g/system01.dbf',

13    '/oradata/ora10g/undotbs01.dbf',

14    '/oradata/ora10g/sysaux01.dbf',

15    '/oradata/ora10g/users01.dbf'

16  CHARACTER SET ZHS16GBK

17  ;

Control file created.

SQL> VARIABLE RECNO NUMBER;

SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

PL/SQL procedure successfully completed.

SQL> RECOVER DATABASE

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf'

2       SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

SQL> STARTUP NOMOUNT

ORA-01081: cannot start already-running Oracle - shut it down first

SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG

2      MAXLOGFILES 16

3      MAXLOGMEMBERS 3

4      MAXDATAFILES 100

5      MAXINSTANCES 8

6      MAXLOGHISTORY 292

7  LOGFILE

8    GROUP 1 '/oradata/ora10g/redo01.log'     SIZE 50M,

9    GROUP 2 '/oradata/ora10g/redo02.log'     SIZE 50M,

10    GROUP 3 '/oradata/ora10g/redo03.log'     SIZE 50M

11  DATAFILE

12    '/oradata/ora10g/system01.dbf',

13    '/oradata/ora10g/undotbs01.dbf',

14    '/oradata/ora10g/sysaux01.dbf',

15    '/oradata/ora10g/users01.dbf'

16  CHARACTER SET ZHS16GBK

17  ;

CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG

SQL> VARIABLE RECNO NUMBER;

SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

PL/SQL procedure successfully completed.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE

SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf'

2       SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf'

Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

本文出自：亿恩科技【www.enkj.com】