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

客官°小女子只卖身不卖艺 2022-06-07 10:54 387阅读 0赞

控制文件对于数据库来说是非常重要，在进行数据恢复时通常是必不可少的；丢失控制文件并不是致命的，但是会使恢复变得困难很多。因此，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.