导航：首页 > 数据库 >

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

发表于：2024-09-24 作者：千家信息网编辑

千家信息网最后更新 2024年09月24日，这篇文章给大家介绍ORACLE服务器异常断电及控制文件故障的处理步骤是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。这里只是简单的记录操作步骤，以做备忘。一、版本SQL> s

千家信息网最后更新 2024年09月24日ORACLE服务器异常断电及控制文件故障的处理步骤是什么

这篇文章给大家介绍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
数据库装载完毕。
数据库已经打开。
至此恢复完成。