重建控制文件的几种考虑情况

已于 2025-05-21 14:58:44 修改
阅读量618 收藏 12
点赞数 15
CC 4.0 BY-SA版权
分类专栏： Oracle案例分享 oracle 文章标签： 数据库 oracle 学习 服务器
于 2025-05-13 09:31:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42145355/article/details/147916068

需要提前的考虑

重建控制文件前的考虑：

📎Things to Consider Before Recreating the Controlfile.pdf

记住如果有备份的话优先从备份中恢复控制文件，然后做恢复，重建控制文件只有发生在如下情况：

  • All current copies of the control file have been lost or are corrupted.
  • You are restoring a backup in which the control file is corrupted or missing.
  • You need to change a hard limit database parameter in the controlfile.
  • If you are moving your database to another server and files are located in a different location.
  • Oracle Customer Support advises you to.

关于重建控制文件，官方推荐使用如下操作方式：

📎recrate controlfile.pdf

以下是我做的一些测试，重建控制文件之前我们需要分几种情况：

1、数据库是否能够mount？

2、redo是否损坏？

数据库可以mount，redo也没有损坏

这种情况下是最简单的，直接在数据库mount状态下执行备份脚本

SQL> startup force mount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size		    2253824 bytes
Variable Size		  180358144 bytes
Database Buffers	  230686720 bytes
Redo Buffers		    4247552 bytes
Database mounted.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12981.trc
SQL>

找到这个trace文件如下：

--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_l1qj452c_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_l1qj455y_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_l1qj4598_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_l1qj2v5r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_l1qj2v6m_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_l1qj2v6r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_l1qj2v7f_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_l1qj46rk_.dbf',
  '/u01/app/oracle/oradata/test.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc03.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc02.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex02.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/u01/arch/1_1_824297850.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/1_1_1132181957.dbf';
-- 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 '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_l1qj465p_.tmp' REUSE;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_l1qj452c_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_l1qj455y_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_l1qj4598_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_l1qj2v5r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_l1qj2v6m_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_l1qj2v6r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_l1qj2v7f_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_l1qj46rk_.dbf',
  '/u01/app/oracle/oradata/test.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc03.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc02.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex02.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/u01/arch/1_1_824297850.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/1_1_1132181957.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_l1qj465p_.tmp' REUSE;
-- End of tempfile additions.

这种情况下我们直接使用noresetlogs方式重建控制文件

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_l1qj452c_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_l1qj455y_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_l1qj4598_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_l1qj2v5r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_l1qj2v6m_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_l1qj2v6r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_l1qj2v7f_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_l1qj46rk_.dbf',
  '/u01/app/oracle/oradata/test.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc03.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc02.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex02.dbf'
CHARACTER SET ZHS16GBK
;

执行如下：

SQL> startup froce nomount;
SP2-0714: invalid combination of STARTUP options
SQL> startup force nomount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size		    2253824 bytes
Variable Size		  180358144 bytes
Database Buffers	  230686720 bytes
Redo Buffers		    4247552 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_l1qj452c_.log'  SIZE 50M  2    3    4    5    6    7    8   BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_l1qj455y_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_l1qj4598_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracl  9   10   11   12   13  e/oradata/ORCL/datafile/o1_mf_system_l1qj2v5r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_l1qj2v6m_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_l1qj2v6r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_l1qj2v7f 14   15   16  _.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_l1qj46rk_.dbf',
  '/u01/app/oracle/oradata/test.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc03.dbf',
  '/u01/app/oracle/oradata/ORCL/d 17   18   19   20   21  atafile/nnc02.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex02.dbf'
CHARACTER SET ZHS16GBK
; 22   23   24   25  

Control file created.

SQL> 
SQL> RECOVER DATABASE;
Media recovery complete.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

数据库打开之后记得把temp文件加回去

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_l1qj465p_.tmp' REUSE;

数据库可以mount，但是redo也损坏了

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_l1qj455y_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_l1qj452c_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_l1qj4598_.log

SQL> !rm -rf /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_l1qj455y_.log /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_l1qj452c_.log /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_l1qj4598_.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_l1qj42w6_.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_l1qj42w6_.ctl

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> startup force nomount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size		    2253824 bytes
Variable Size		  180358144 bytes
Database Buffers	  230686720 bytes
Redo Buffers		    4247552 bytes

这种情况下我们尝试使用noresetlog方式重建数据文件试试

SQL> startup force nomount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size		    2253824 bytes
Variable Size		  180358144 bytes
Database Buffers	  230686720 bytes
Redo Buffers		    4247552 bytes
SQL> alter database backup controlfile to trace;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_l1qj452c_.log'  SIZE 50M  2    3    4    5    6    7    8   BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_l1qj455y_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_l1qj4598_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracl  9   10   11   12   13  e/oradata/ORCL/datafile/o1_mf_system_l1qj2v5r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_l1qj2v6m_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_l1qj2v6r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_l1qj2v7f 14   15   16  _.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_l1qj46rk_.dbf',
  '/u01/app/oracle/oradata/test.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc03.dbf',
  '/u01/app/oracle/oradata/ORCL/d 17   18   19   20   21  atafile/nnc02.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex02.dbf'
CHARACTER SET ZHS16GBK
; 22   23   24   25  
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_l1qj452c_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

发现直接报错，报错是因为redo都损坏了，使用noresetlog方式自然无法正常创建成功，还是只能使用resetlog方式创建控制文件，执行如下：

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_l1qj452c_.log'  SIZE 50M B  2    3    4    5    6    7    8  LOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_l1qj455y_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_l1qj4598_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/  9   10   11   12   13  oradata/ORCL/datafile/o1_mf_system_l1qj2v5r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_l1qj2v6m_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_l1qj2v6r_.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_l1qj2v7f_. 14   15   16  dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_l1qj46rk_.dbf',
  '/u01/app/oracle/oradata/test.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nnc03.dbf',
  '/u01/app/oracle/oradata/ORCL/dat 17   18   19   20   21  afile/nnc02.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex01.dbf',
  '/u01/app/oracle/oradata/ORCL/datafile/nncindex02.dbf'
CHARACTER SET ZHS16GBK
; 22   23   24   25  

Control file created.

接下来应该使用recover database using backup controlfile until cancel;来做不完全恢复，执行如下：

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2267624 generated at 04/09/2024 18:30:38 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_69_1132181957.dbf
ORA-00280: change 2267624 for thread 1 is in sequence #69


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_l1qj2v5r_.dbf'


ORA-01112: media recovery not started

执行alter database open resetlogs发现有如下报错：

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_l1qj2v5r_.dbf'

因为control和redo同时坏，而且是数据库还处于打开的状态下redo发生损坏，损坏之后未做相关措施，直接shutdown abort，这种情况下只能使用隐藏参数（特殊情况下还需要推进scn）打开数据库，然后做逻辑备份了

其实这种情况都可以参考current状态redo损坏的处理方法了

create pfile='/home/oracle/pfile.ora' from spfile;
添加：
*._allow_resetlogs_corruption=TRUE

关闭数据库实例，再次用这个修改后的pfile重启
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='/home/oracle/pfile.ora';
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size		    2253824 bytes
Variable Size		  180358144 bytes
Database Buffers	  230686720 bytes
Redo Buffers		    4247552 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

查看告警日志，这里已经明确提示了数据库跳过了一致性检查，这个数据库需要尽快被重建

99%的人不知道，Oracle resetlogs强制开库需要推进SCN？-CSDN博客

把先前的temp文件添加回来，然后开始导出数据库

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_l1qj465p_.tmp' REUSE;

[oracle@centos7 d1]$ expdp "'/ as sysdba'" directory=d1 dumpfile=orcl_full_20240410_%U.dmp logfile=orcl_full.log parallel=8 cluster=N full=y compression=all

数据库不可以mount

如果没有之前的关于控制文件的任何备份，这种情况是比较棘手的，只能手工罗列数据库相关文件，但是这样做风险也比较大，详见第三条：

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值