阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle 重建控制文件一例

192次阅读
没有评论

共计 3188 个字符,预计需要花费 8 分钟才能阅读完成。

环境:OEL 5.7 + Oracle 10.2.0.5
背景:在 Oracle 的运维过程中,时常会遇到一些场景是需要重建控制文件才可以解决的。本文的场景可以通过复制控制文件到新路径,运行一段时间后,再用老的控制文件启动数据库重现。

1. 当前故障现象
2. 分析故障原因
3. 重建控制文件

1. 当前故障现象

在使用旧的控制文件启动数据库时,报错 ORA-01122、ORA-01110、ORA-01207:
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size            218106944 bytes
Database Buffers          373293056 bytes
Redo Buffers                6287360 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/oradata/cxywdb/system01.dbf’
ORA-01207: file is more recent than control file – old control file

2. 分析故障原因

根据报错信息查找 MOS 文档:
•ORA-1122, ORA-1110, ORA-1207 while open the database after crash (文档 ID 283927.1)

延伸思考一下,为什么会这样?
 主要错误是 ORA-01207,利用 oerr 工具看到 Oralce 对这个错误的详细描述是:
01207, 00000, “file is more recent than control file – old control file”
// *Cause:  The control file change sequence number in the data file is
//        greater than the number in the control file. This implies that
//        the wrong control file is being used. Note that repeatedly causing
//        this error can make it stop happening without correcting the real
//        problem. Every attempt to open the database will advance the
//        control file change sequence number until it is great enough.
// *Action: Use the current control file or do backup control file recovery to
//        make the control file current. Be sure to follow all restrictions
//        on doing a backup control file recovery.

一般遇到这种情况,当前的控制文件肯定是找不到了。那么就得考虑重建控制文件来解决,MOS 给出的建议也是重建控制文件。

3. 重建控制文件

重建控制文件的核心步骤:
3.1 备份控制文件到 trace
startup mount
alter database backup controlfile to trace;
oradebug setmypid
oradebug tracefile_name

3.2 启动数据库到 nomount 状态
shutdown abort
startup nomount;

3.3 确认重建控制文件的语句
vi control.sql
CREATE CONTROLFILE REUSE DATABASE “CXYWDB” NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 5 ‘/oradata2/cxywdb/redo11.log’  SIZE 50M,
  GROUP 6 ‘/oradata2/cxywdb/redo12.log’  SIZE 50M,
  GROUP 7 ‘/oradata2/cxywdb/redo13.log’  SIZE 50M
— STANDBY LOGFILE
DATAFILE
  ‘/oradata/cxywdb/system01.dbf’,
  ‘/oradata/cxywdb/undotbs01.dbf’,
  ‘/oradata/cxywdb/sysaux01.dbf’,
  ‘/oradata/cxywdb/users01.dbf’,
  ‘/oradata/cxywdb/alfred01.dbf’,
  ‘/oradata/cxywdb/alfred02.dbf’,
  ‘/oradata/cxywdb/alfred03.dbf’,
  ‘/oradata/cxywdb/alfred04.dbf’,
  ‘/oradata/cxywdb/alfred05.dbf’,
  ‘/oradata/cxywdb/dbs_i_alfred01.dbf’
CHARACTER SET ZHS16GBK
;

3.4 恢复并打开数据库
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.

附:实际解决过程如下:
SQL> shutdown abort   
ORACLE instance shut down.
SQL> startup mount

ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size            218106944 bytes
Database Buffers          373293056 bytes
Redo Buffers                6287360 bytes
Database mounted.
SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/s01/oracle/admin/cxywdb/udump/cxywdb_ora_3983.trc
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size            218106944 bytes
Database Buffers          373293056 bytes
Redo Buffers                6287360 bytes
SQL> @control

Control file created.
SQL> select status from v$instance;

STATUS
————
MOUNTED

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

Database altered.

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计3188字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中