Rebuild control file

1 Description

The reconstruction control file can only be used in case of necessity. It is generally used in the following situations:

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
3 you need to change a hard limit database parameter in the control file
4 if you are moving your database to another server and files are located in a different location
5 Oracle customer support advice you to

2 method

2.1 method 1

– back up control files

SQL> alter database backup controlfile to trace;

– view TRACE file generation path

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 -- sort by time
total 46
-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 method 2 - simpler

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

Directly copy the creation statement in this file and modify it slightly according to the actual situation.

The general content is as follows:

Part of the document:

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 reconstruction control documents

SQL> shutdown immediate;
SQL> startup nomount;
SQL>@control.sql--Store the SQL Sentence

Be careful:

After creating the control file, add the temp file manually.
For example: alter tablespace temp_ts add tempfile 'У / oradata/V11/temp01.dbf' ™ reuse;
Once the data is opened by RESETLOGS, back up the database as soon as possible.

Posted by rsmarsha on Fri, 08 Nov 2019 11:23:15 -0800

Programmer Group