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

最新推荐文章于 2024-10-24 10:16:03 发布

罗曼蒂克复兴史

最新推荐文章于 2024-10-24 10:16:03 发布

阅读量382

点赞数

文章标签： oracle断电故障

本文记录了Oracle 11g数据库因数据文件验证失败导致无法正常启动的问题及解决步骤。通过备份控制文件、创建新的控制文件并使用resetlogs选项，最终成功恢复数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这里只是简单的记录操作步骤，以做备忘。本步骤由同事邹启健(问心)完成

一、版本

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

数据库装载完毕。

数据库已经打开。

至此恢复完成

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-2148864/，如需转载，请注明出处，否则将追究法律责任。