DG主库控制文件丢失处理方法

背景：
出问题系统架构为DG模式，主库的contorl文件不小心，用备库的contorl文件给替换了，结果导致主库启动不起来；

操作系统版本：CentOS Linux release 7.4.1708 (Core)
数据库版本：Oracle 11.2.0.4.0

一、由于DG备库可以正常运行，可以通过命令备份control文件

[oracle@mesbk xttzj]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 12月 23 15:16:55 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到: 
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace; 
	数据库已更改。

通过语句查看备份的文件路径：

SQL> select d.value||b.bias||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from ( select p.spid from sys.v$mystat m,sys.v$session s,sys.v$process p where m.statistic# = 1 and s.sid = m.sid and p.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,(select DECODE(count(BANNER),0,'/','\') bias from v$version where upper(banner) like '%WINDOWS%') b;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/mesbk/mesbk/trace/mesbk_ora_31452.trc

通过vi命令查看查找到的文件

[oracle@mesbk xttzj]$ vi /u01/app/oracle/diag/rdbms/mesbk/mesbk/trace/mesbk_ora_31452.trc

移动到文件的最后，向上找倒数第一个STARTUP NOMOUNT，下面就是创建控制文件的语句

CREATE CONTROLFILE REUSE DATABASE XTTZJ RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/xttzj/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/xttzj/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/xttzj/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 '/u01/app/oracle/oradata/xttzj/redo05.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 5 '/u01/app/oracle/oradata/xttzj/redo04.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 6 '/u01/app/oracle/oradata/xttzj/redo06.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/xttzj/system01.dbf',
  '/u01/app/oracle/oradata/xttzj/sysaux01.dbf',
  '/u01/app/oracle/oradata/xttzj/undotbs01.dbf',
  '/u01/app/oracle/oradata/xttzj/users01.dbf',
  '/u01/app/oracle/oradata/xttzj/IMES_D.dbf',
  '/u01/app/oracle/oradata/xttzj/SKZJ_D.dbf'
CHARACTER SET AL32UTF8
;

二、在主库创建control文件
找到创建控制文件语句后，需要在主库上创建，首先将主库启动到nomount状态

[oracle@XTTZJ-DB xttzj]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 12月 23 15:20:06 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
已连接到空闲例程。

SQL> startup force nomount
ORACLE 例程已经启动。

Total System Global Area 6714322944 bytes
Fixed Size                  2265944 bytes
Variable Size            4211084456 bytes
Database Buffers         2483027968 bytes
Redo Buffers               17944576 bytes

然后通过找到的语句创建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE XTTZJ RESETLOGS FORCE LOGGING 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/xttzj/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/oradata/xttzj/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/oradata/xttzj/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  --   GROUP 4 '/u01/app/oracle/oradata/xttzj/redo05.log'  SIZE 50M BLOCKSIZE 512,
 13  --   GROUP 5 '/u01/app/oracle/oradata/xttzj/redo04.log'  SIZE 50M BLOCKSIZE 512,
 14  --   GROUP 6 '/u01/app/oracle/oradata/xttzj/redo06.log'  SIZE 50M BLOCKSIZE 512
 15  DATAFILE
 16    '/u01/app/oracle/oradata/xttzj/system01.dbf',
 17    '/u01/app/oracle/oradata/xttzj/sysaux01.dbf',
 18    '/u01/app/oracle/oradata/xttzj/undotbs01.dbf',
 19    '/u01/app/oracle/oradata/xttzj/users01.dbf',
 20    '/u01/app/oracle/oradata/xttzj/IMES_D.dbf',
 21    '/u01/app/oracle/oradata/xttzj/SKZJ_D.dbf'
 22  CHARACTER SET AL32UTF8
 23  ;

控制文件已创建。

查看控制文件的scn是否一致：

SQL>  select file#,checkpoint_change# from v$datafile;  

 FILE# CHECKPOINT_CHANGE#
---------- ------------------
     1          106572426
     2          106572426
     3          106572426
     4          106572426
     5          106572426
     6          106572426

已选择6行。

SQL> select file#,checkpoint_change# from v$datafile_header;   

 FILE# CHECKPOINT_CHANGE#
---------- ------------------
     1          106572426
     2          106572426
     3          106572426
     4          106572426
     5          106572426
     6          106572426

打开数据库，提示需要 RESETLOGS 或 NORESETLOGS

SQL> alter database open ;

alter database open
*
第 1 行出现错误:
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

由于需要介质恢复，需要通过redo日志文件恢复数据，操作如下：

SQL> recover database using backup controlfile;

ORA-00279: 更改 106572426 (在 12/23/2021 09:24:57 生成) 对于线程 1 是必需的 ORA-00289:
建议:
	/u01/app/oracle/fast_recovery_area/XTTZJ/archivelog/2021_12_23/o1_mf_1_4096_%u_.
	arc
	ORA-00280: 更改 106572426 (用于线程 1) 在序列 #4096 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}

输入redo日志文件路径

/u01/app/oracle/oradata/xttzj/redo01.log

已应用的日志。
完成介质恢复。

SQL> recover database using backup controlfile;

ORA-00279: 更改 106597445 (在 12/23/2021 14:17:46 生成) 对于线程 1 是必需的 ORA-00289:
建议:
/u01/app/oracle/fast_recovery_area/XTTZJ/archivelog/2021_12_23/o1_mf_1_4096_%u_.
arc
ORA-00280: 更改 106597445 (用于线程 1) 在序列 #4096 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/xttzj/redo02.log
ORA-00310: archived log contains sequence 4094; sequence 4096 required
ORA-00334: archived log: '/u01/app/oracle/oradata/xttzj/redo02.log'

SQL> recover database using backup controlfile;

ORA-00279: 更改 106597445 (在 12/23/2021 14:17:46 生成) 对于线程 1 是必需的 ORA-00289:
建议:
/u01/app/oracle/fast_recovery_area/XTTZJ/archivelog/2021_12_23/o1_mf_1_4096_%u_.
arc
ORA-00280: 更改 106597445 (用于线程 1) 在序列 #4096 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/xttzj/redo03.log
ORA-00310: archived log contains sequence 4095; sequence 4096 required
ORA-00334: archived log: '/u01/app/oracle/oradata/xttzj/redo03.log'

恢复完后，通过resetlogs打开数据库

SQL> alter database open RESETLOGS;

数据库已更改。

由于在创建表空间过程中没有创建控制文件，执行以下语句即可，该语句在上面备份的control文件内找到；
添加临时表空间：

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/xttzj/temp01.dbf' SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
SQL> ALTER TABLESPACE IMES_T ADD TEMPFILE '/u01/app/oracle/oradata/xttzj/IMES_T.dbf' SIZE 1048576000  REUSE AUTOEXTEND ON NEXT 10485760  MAXSIZE 32767M;
SQL> ALTER TABLESPACE SKZJ_T ADD TEMPFILE '/u01/app/oracle/oradata/xttzj/SKZJ_T.dbf' SIZE 524288000  REUSE AUTOEXTEND ON NEXT 5242880  MAXSIZE 32767M;

至此，数据库启动完毕，如果采用rman备份，建议做一次全备。