ITPub博客

ORACLE服务器异常断电,控制文件故障的处理步骤

原创 作者:gaopengtttt 时间:2017-12-19 12:25:38 0 删除 编辑
这里只是简单的记录操作步骤,以做备忘。本步骤由同事邹启健(问心)完成


一、版本


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
数据库装载完毕。
数据库已经打开。

至此恢复完成

请登录后发表评论 登录
全部评论

注册时间:2008-10-13

  • 博文量
    616
  • 访问量
    2729299