1  说明

重建控制文件，只能在迫不得已的情况下使用。一般满足以下情况：

1  All current copies of the control file have been lost or are corrupted.--所有控制文件都丢失或损坏

2  You are restoring a backup in which the control file is corrupted or missing.

http://www.cndba.cn/Expect-le/article/2195

3  You need to change a hard limit database parameter in the controlfile.

4  If you are moving your database to another server and files are located in a different location.http://www.cndba.cn/Expect-le/article/2195

5  Oracle Customer Support advises you to.

2  方法

2.1   方法1

--备份控制文件

SQL> alter database backup controlfile to trace;

--查看TRACE文件生成路径

SQL> show parameter user_dump_dest
NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest      string  /u01/app/oracle/product/12.2.0/db_1/rdbms/log


[root@12cdg-p 12.2.0]# cd /u01/app/oracle/product/12.2.0/db_1/rdbms/log/

[root@12cdg-p log]# ls  -ltr --按时间排序

total 44

-rw-r----- 1 oracle oinstall  927 Aug 23 16:12 cndba_ora_3254.trc

-rw-r----- 1 oracle oinstall  927 Aug 23 16:13 cndba_ora_3450.trc

-rw-r----- 1 oracle oinstall  927 Aug 23 16:14 cndba_ora_3545.trc

-rw-r----- 1 oracle oinstall  120 Aug 23 16:15 qopatch.log

-rw-r----- 1 oracle oinstall  927 Aug 23 16:17 cndba_ora_4506.trc

-rw-r----- 1 oracle oinstall  927 Aug 23 16:18 cndba_ora_4938.trc

-rw-r----- 1 oracle oinstall  927 Aug 23 16:24 cndba_ora_5808.trc

-rw-r----- 1 oracle oinstall  927 Aug 30 18:15 cndba_ora_2471.trc

-rw-r----- 1 oracle oinstall  927 Aug 30 20:54 cndba_ora_3084.trc

-rw-r----- 1 oracle oinstall 5688 Aug 30 22:01 qopatch_log.log

2.2   方法2-更简单

http://www.cndba.cn/Expect-le/article/2195

SQL> alter database backup controlfile to trace;
Database altered.

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/cndba_p/cndba/trace/cndba_ora_3168.trc

直接把这个文件里的创建语句复制出来根据实际情况稍加修改即可。http://www.cndba.cn/Expect-le/article/2195

大概内容如下：http://www.cndba.cn/Expect-le/article/2195

文件的部分内容:

http://www.cndba.cn/Expect-le/article/2195

CREATE CONTROLFILE REUSE DATABASE "CNDBA" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/CNDBA/onlinelog/o1_mf_1_dstgbrgr_.log',
    '/u01/app/oracle/fast_recovery_area/cndba/CNDBA/onlinelog/o1_mf_1_dstgc3ky_.log'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/CNDBA/onlinelog/o1_mf_2_dstgbrhj_.log',
    '/u01/app/oracle/fast_recovery_area/cndba/CNDBA/onlinelog/o1_mf_2_dstgc3k9_.log'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/CNDBA/onlinelog/o1_mf_3_dstgcgfh_.log',
    '/u01/app/oracle/fast_recovery_area/cndba/CNDBA/onlinelog/o1_mf_3_dstgcm91_.log'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/CNDBA/datafile/o1_mf_system_dstg7dv8_.dbf',
  '/u01/app/oracle/oradata/CNDBA/datafile/o1_mf_sysaux_dstg8tr7_.dbf',
  '/u01/app/oracle/oradata/CNDBA/datafile/o1_mf_undotbs1_dstg9mx9_.dbf',
  '/u01/app/oracle/oradata/CNDBA/datafile/o1_mf_users_dstg9q15_.dbf'
CHARACTER SET AL32UTF8;

2.3   重建控制文件

SQL> shutdown immediate;
SQL> startup nomount;
SQL>@control.sql--存放创建控制文件的SQL语句

http://www.cndba.cn/Expect-le/article/2195http://www.cndba.cn/Expect-le/article/2195

注意：

l  创建控制文件之后，手动添加temp file。http://www.cndba.cn/Expect-le/article/2195

如：alter tablespace temp_ts add tempfile '˜/oradata/V11/temp01.dbf'™ reuse;

2  一旦通过RESETLOGS方式打开数据，那么尽快备份数据库。

版权声明：本文为博主原创文章，未经博主允许不得转载。

重建控制文件