ORACLE服务器异常断电及控制文件故障的处理步骤是什么

共计 5752 个字符，预计需要花费 15 分钟才能阅读完成。

这篇文章给大家介绍 ORACLE 服务器异常断电及控制文件故障的处理步骤是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

这里只是简单的记录操作步骤，以做备忘。

一、版本

SQL select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1 .0 – Production
PL/SQL Release 11.2.0.1 .0 – Production CORE    11.2.0.1 

二、故障
SQL startup mount;
ORACLE 例程已经启动。

Total System Global Area 1046224896 bytes
Fixed Size                  1379212 bytes
Variable Size             708838516 bytes
Database Buffers          331350016 bytes
Redo Buffers                4657152 bytes
数据库装载完毕。
SQL alter dabase open;
alter dabase open
      *
第 1 行出现错误:
ORA-00940: 无效的 ALTER 命令

SQL alter database open;
alter database open
*
第 1 行出现错误:
ORA-01122: 数据库文件 1 验证失败
ORA-01110: 数据文件 1: G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
ORA-01207: 文件比控制文件更新 – 旧的控制文件

三、处理步骤

SQL alter database backup controlfile to trace as G:\ctl1.txt

数据库已更改。

SQL shutdown immediate;
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL startup nomount;
ORACLE 例程已经启动。

Total System Global Area 1046224896 bytes
Fixed Size                  1379212 bytes
Variable Size             708838516 bytes
Database Buffers          331350016 bytes
Redo Buffers                4657152 bytes
SQL CREATE CONTROLFILE REUSE DATABASE ORCL NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 G:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG  SIZE 50M BLOCKSIZ
E 512,
  9    GROUP 2 G:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG  SIZE 50M BLOCKSIZ
E 512,
 10    GROUP 3 G:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG  SIZE 50M BLOCKSIZ
E 512
 11  DATAFILE
 12     G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF ,
 13     G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF ,
 14     G:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF ,
 15     G:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF ,
 16     G:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
 17  CHARACTER SET ZHS16GBK;
CREATE CONTROLFILE REUSE DATABASE ORCL NORESETLOGS  NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-01229: data file 2 is inconsistent with logs
ORA-01110: data file 2: G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
我们使用 resetlogs 进行重建避开和 redo logs 的校验

SQL CREATE CONTROLFILE REUSE DATABASE ORCL RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 G:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG  SIZE 50M BLOCKSIZ
E 512,
  9    GROUP 2 G:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG  SIZE 50M BLOCKSIZ
E 512,
 10    GROUP 3 G:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG  SIZE 50M BLOCKSIZ
E 512
 11  DATAFILE
 12     G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF ,
 13     G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF ,
 14     G:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF ,
 15     G:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF ,
 16     G:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
 17  CHARACTER SET ZHS16GBK;
控制文件已创建。

SQL alter database mount;
alter database mount
*
第 1 行出现错误:
ORA-01100: ??????

SQL alter session set nls_language= american – 中文字符集显示乱码，需要更改 session 字符集
Session altered.
SQL alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

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: G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF

进行 recover 数据库，需要使用 resetlogs 和手动指定归档日志这里其实就是 redolog 中的一个

SQL recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL recover database using backup controlfile;
ORA-00279: change 28088746 generated at 12/16/2017 20:32:45 needed for thread 1
ORA-00289: suggestion :
G:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_12_19\O1_MF_1_572_%U_.ARC
ORA-00280: change 28088746 for thread 1 is in sequence #572

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
G:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_12_19\O1_MF_1_572_%U_.ARC
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

SQL archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Current log sequence           0
SQL recover database using backup controlfile;
ORA-00279: change 28088746 generated at 12/16/2017 20:32:45 needed for thread 1
ORA-00289: suggestion :
G:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_12_19\O1_MF_1_572_%U_.ARC
ORA-00280: change 28088746 for thread 1 is in sequence #572

Specify log: {=suggested | filename | AUTO | CANCEL}
G:\app\Administrator\oradata\orcl\REDO01.LOG
ORA-00310: archived log contains sequence 571; sequence 572 required
ORA-00334: archived log: G:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG

SQL recover database using backup controlfile;
ORA-00279: change 28088746 generated at 12/16/2017 20:32:45 needed for thread 1
ORA-00289: suggestion :
G:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_12_19\O1_MF_1_572_%U_.ARC
ORA-00280: change 28088746 for thread 1 is in sequence #572

Specify log: {=suggested | filename | AUTO | CANCEL}
G:\app\Administrator\oradata\orcl\REDO02.LOG 这里指定是 redolog 中的一个
Log applied.
Media recovery complete.
SQL alter database open;
alter database open
*
ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL alter database open resetlogs;
Database altered.

SQL select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
G:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
G:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
G:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF

SQL shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup;
ORACLE instance started.

Total System Global Area 1046224896 bytes
Fixed Size                  1379212 bytes
Variable Size             708838516 bytes
Database Buffers          331350016 bytes
Redo Buffers                4657152 bytes
数据库装载完毕。
数据库已经打开。
至此恢复完成。

关于 ORACLE 服务器异常断电及控制文件故障的处理步骤是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。