Oracle 重建控制文件（在丢失控制文件的情况下，如何恢复数据库）

    控制文件对于数据库来说是非常重要，在进行数据恢复时通常是必不可少的；丢失控制文件并不是致命的，但是会使恢复变得困难很多。因此，dba除了多路保存控制文件外，还需要备份控制文件。
备份控制文件也有多种办法，例如cp拷贝、rman自动备份等，今天给大家介绍如何获取创建控制文件的脚本，这样数据库一旦出现意外，即使找不到可用的备份控制文件，也能通过重建控制文件来启动数据库。
     Oracle提供了如下命令来实现这个功能：
     alter database backup controlfile to trace;

下面将介绍在丢失控制文件的情况下如何恢复数据库。
测试环境：Linux6.5+Oracle 11g

1.环境准备
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0    Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> show parameter control_files
 打开数据库，执行‘alter database backup controlfile to trace;’语句来生成trace文件。
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_files                 string     /data/CEBPM/controlfile/o1_mf_
                         dm1flhj3_.ctl
2.生成trace文件
SQL> alter database backup controlfile to trace;

Database altered.

3.查找trace文件路径
trace文件的路径可以通过一个多表查询来获得，其中涉及v$processv、$sessionv和$mystat等三个动态视图。

SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cebpm/cebpm/trace/cebpm_ora_2225.trc

SQL>

4.查看trace文件
通过cat命令查看trace文件，内容如下：
......

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CEBPM" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 11 '/data/CEBPM/onlinelog/redo11_stb01.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 12 '/data/CEBPM/onlinelog/redo12_stb01.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 13 '/data/CEBPM/onlinelog/redo13_stb01.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 14 '/data/CEBPM/onlinelog/redo14_stb01.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf',
  '/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf',
  '/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf',
  '/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf'
CHARACTER SET AL32UTF8
;

......

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CEBPM" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf',
  '/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf',
  '/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf',
  '/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf'
CHARACTER SET AL32UTF8

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/data/CEBPM/archivelog/1_1_945593423.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp'
     SIZE 61865984  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions

.....

 5.NORESETLOGS VS RESETLOGS
从trace文件可以看出，提供了两个创建控制文件的脚本，分别针对两种不同的应用场景：NORESETLOGS适用于当前redo log可用，而RESETLOGS适用于当前redo log不可用。
 NORESETLOGS重建步骤
(1).执行脚本，重建控制文件（CREATE CONTROLFILE REUSE DATABASE "CEBPM NORESETLOGS NOARCHIVELOG......）
(2).执行介质恢复RECOVER DATABASE
(3).打开数据库ALTER DATABASE OPEN;
(4).添加临时表空间
 RESETLOGS重建步骤
(1).执行脚本，重建控制文件（CREATE CONTROLFILE REUSE DATABASE "CEBPM" RESETLOGS NOARCHIVELOG......）
(2).执行介质恢复RECOVER DATABASE USING BACKUP CONTROLFILE
(3).打开数据库ALTER DATABASE OPEN RESETLOGS;
(4).添加临时表空间

6.删除控制文件
根据上面查到的控制文件的路径利用rm将其删除
cebpm:/data/CEBPM/controlfile@myoracle>rm -rf o1_mf_dm1flhj3_.ctl
此时，强制关闭数据库，然后重启数据库，报ORA-00205错误。需要注意的是，此时执行shutdown immediate命令，数据库无法正常关闭，只能关闭到mounted状态；需要使用shutdown abort命令强制关闭数据库。
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/CEBPM/controlfile/o1_mf_dm1flhj3_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
 

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  688959488 bytes
Fixed Size            2256432 bytes
Variable Size          566231504 bytes
Database Buffers      117440512 bytes
Redo Buffers            3031040 bytes
ORA-00205: error in identifying control file, check alert log for more info

启动到nomount状态

SQL> startup nomount
ORACLE instance started.

Total System Global Area  688959488 bytes
Fixed Size            2256432 bytes
Variable Size          566231504 bytes
Database Buffers      117440512 bytes
Redo Buffers            3031040 bytes

利用刚才产生的trace里的控制文件创建脚本（如果事先没有产生创建控制文件trace文件，那么这一步则需要自己手动构造控制文件脚本）

cebpm:/u01@myoracle>vi createcontrofile.sql
 STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "CEBPM" NORESETLOGS FORCE LOGGING ARCHIVELOG
        MAXLOGFILES 16
        MAXLOGMEMBERS 3
        MAXDATAFILES 100
        MAXINSTANCES 8
        MAXLOGHISTORY 292
    LOGFILE
      GROUP 1 '/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log' SIZE 50M,
      GROUP 2 '/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log' SIZE 50M,
      GROUP 3 '/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log' SIZE 50M
    DATAFILE
    '/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf',
    '/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf',
    '/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf',
    '/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf'
    CHARACTER SET AL32UTF8
    ;
cebpm:/u01@myoracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 19 08:02:56 2017

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

Connected to an idle instance.

SQL> @/u01/createcontrofile.sql
ORACLE instance started.

Total System Global Area  688959488 bytes
Fixed Size            2256432 bytes
Variable Size          566231504 bytes
Database Buffers      117440512 bytes
Redo Buffers            3031040 bytes

Control file created.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf'


SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0    Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf
/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf
/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf
/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log
/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log
/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/data/CEBPM/controlfile/o1_mf_dm1flhj3_.ctl

SQL> select name from v$tempfile;

no rows selected

添加临时表空间

SQL> alter tablespace temp add tempfile '/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp';

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> alter system archive log current;

System altered.