oracle控制文件恢复trc,trace文件备份控制文件并执行恢复

trace中的控制文件备份执行恢复，这种通过查看trace文件的记录，查看数据库打开过程中

执行控制文件的情况。

---以sysdba身份登录sqlplus，将控制文件转储到trace文件中:

sys@PROD>alter database backup controlfile to trace;

Database altered.

--找到转储trace文件:

[oracle@enmo trace]$ ls -lrt

... ...

-rw-r----- 1 oracle oinstall   8293 Nov  7 15:33 PROD_ora_15666.trc

-rw-r----- 1 oracle oinstall    109 Nov  7 15:53 PROD_mmon_15614.trm

-rw-r----- 1 oracle oinstall   1425 Nov  7 15:53 PROD_mmon_15614.trc

-rw-r----- 1 oracle oinstall    119 Nov  7 15:53 PROD_ora_15629.trm

-rw-r----- 1 oracle oinstall   8167 Nov  7 15:53 PROD_ora_15629.trc

-rw-r----- 1 oracle oinstall 176417 Nov  7 15:53 alert_PROD.log

[oracle@enmo trace]$

#按时间排序，找到位置最后的trace文件。

---查看最新生成的trace文件，可以清晰的看到创建控制文件的脚本:

[oracle@enmo trace]$ more PROD_ora_15629.trc

Trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_15629.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

... ...

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log'  SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log'  SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/PROD/system01.dbf',

'/u01/app/oracle/oradata/PROD/sysaux01.dbf',

'/u01/app/oracle/oradata/PROD/undotbs01.dbf',

'/u01/app/oracle/oradata/PROD/users01.dbf',

'/u01/app/oracle/oradata/PROD/example01.dbf',

'/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf',

'/u01/app/oracle/oradata/PROD/ts_ctl01.dbf'

CHARACTER SET AL32UTF8

;

#这部分就是打开数据库过程中启用控制文件的过程。

---删除控制文件并关闭数据库：

sys@PROD>!rm /u01/app/oracle/oradata/PROD/control01.ctl

sys@PROD>!rm /u01/app/oracle/fast_recovery_area/PROD/control02.ctl

sys@PROD>!ls /u01/app/oracle/oradata/PROD/control01.ctl

ls: /u01/app/oracle/oradata/PROD/control01.ctl: No such file or directory

sys@PROD>!ls /u01/app/oracle/fast_recovery_area/PROD/control02.ctl

ls: /u01/app/oracle/fast_recovery_area/PROD/control02.ctl: No such file or directory

---尝试关闭数据库：

sys@PROD>shutdown immediate;

Database closed.

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/PROD/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

#由于没有控制文件，正常关库关不了。

#需要强行关库。

---强制性关库:

sys@PROD>shutdown abort;

ORACLE instance shut down.

sys@PROD>

---按照转储trace文件中的步骤，恢复控制文件，第一步：启动到nomount状态:

sys@PROD>startup nomount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             507513936 bytes

Database Buffers          322961408 bytes

Redo Buffers                2371584 bytes

sys@PROD>

---创建控制文件：

sys@PROD>CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG

2   MAXLOGFILES 16

3  MAXLOGMEMBERS 3

4   MAXDATAFILES 100

5  MAXINSTANCES 8

6  MAXLOGHISTORY 292

7  LOGFILE

8  GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log'  SIZE 50M BLOCKSIZE 512,

9  GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log'  SIZE 50M BLOCKSIZE 512,

10  GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log'  SIZE 50M BLOCKSIZE 512

11  -- STANDBY LOGFILE

12  DATAFILE

13  '/u01/app/oracle/oradata/PROD/system01.dbf',

14  '/u01/app/oracle/oradata/PROD/sysaux01.dbf',

15  '/u01/app/oracle/oradata/PROD/undotbs01.dbf',

16  '/u01/app/oracle/oradata/PROD/users01.dbf',

17  '/u01/app/oracle/oradata/PROD/example01.dbf',

18  '/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf',

19  '/u01/app/oracle/oradata/PROD/ts_ctl01.dbf'

20  CHARACTER SET AL32UTF8

21  ;

Control file created.

sys@PROD>

#控制文件已经创建成功。

---查看数据库实例的状态：

sys@PROD>select status from v$instance;

STATUS

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

MOUNTED

#创建控制文件之后，自动变换到mount状态。

--系统查看控制文件的路径：

sys@PROD>!ls /u01/app/oracle/oradata/PROD/control01.ctl

/u01/app/oracle/oradata/PROD/control01.ctl

sys@PROD>!ls /u01/app/oracle/fast_recovery_area/PROD/control02.ctl

/u01/app/oracle/fast_recovery_area/PROD/control02.ctl

#已经生产新的控制文件。

---尝试恢复数据库并打开数据库：

sys@PROD>RECOVER DATABASE;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

sys@PROD>ALTER SYSTEM ARCHIVE LOG ALL;

ALTER SYSTEM ARCHIVE LOG ALL

*

ERROR at line 1:

ORA-01649: operation not allowed with a backup control file

sys@PROD>ALTER DATABASE OPEN;

ALTER DATABASE OPEN

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

sys@PROD>ALTER DATABASE OPEN RESETLOGS;

Database altered.

#数据库是以RESETLOGS方式打开。

--添加临时数据文件：

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf' SIZE 100M  REUSE AUTOEXTEND ON NEXT 10  MAXSIZE 200M;

sys@PROD>ALTER TABLESPACE TEMP ADD TEMPFILE

2  '/u01/app/oracle/oradata/PROD/temp01.dbf'

3  SIZE 100M  REUSE AUTOEXTEND ON NEXT 10  MAXSIZE 200M;

Tablespace altered.

sys@PROD>

sys@PROD>select FILE_NAME from dba_temp_files;

FILE_NAME

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

/u01/app/oracle/oradata/PROD/temp01.dbf

sys@PROD>

#控制文件恢复完成。