ORACLE服務(wù)器異常斷電及控制文件故障的處理步驟是什么

138次閱讀

共計(jì) 5752 個(gè)字符，預(yù)計(jì)需要花費(fèi) 15 分鐘才能閱讀完成。

這篇文章給大家介紹 ORACLE 服務(wù)器異常斷電及控制文件故障的處理步驟是什么，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

這里只是簡單的記錄操作步驟，以做備忘。

一、版本

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 例程已經(jīng)啟動(dòng)。

Total System Global Area 1046224896 bytes
Fixed Size 1379212 bytes
Variable Size 708838516 bytes
Database Buffers 331350016 bytes
Redo Buffers 4657152 bytes
數(shù)據(jù)庫裝載完畢。
SQL alter dabase open;
alter dabase open
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-00940: 無效的 ALTER 命令

SQL alter database open;
alter database open
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01122: 數(shù)據(jù)庫文件 1 驗(yàn)證失敗
ORA-01110: 數(shù)據(jù)文件 1: G:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
ORA-01207: 文件比控制文件更新 – 舊的控制文件

三、處理步驟

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

數(shù)據(jù)庫已更改。

SQL shutdown immediate;
ORA-01109: 數(shù)據(jù)庫未打開

已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關(guān)閉。
SQL startup nomount;
ORACLE 例程已經(jīng)啟動(dòng)。

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 行出現(xiàn)錯(cuò)誤:
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 進(jìn)行重建避開和 redo logs 的校驗(yàn)

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;
控制文件已創(chuàng)建。

SQL alter database mount;
alter database mount
*
第 1 行出現(xiàn)錯(cuò)誤:
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

進(jìn)行 recover 數(shù)據(jù)庫，需要使用 resetlogs 和手動(dòng)指定歸檔日志這里其實(shí)就是 redolog 中的一個(gè)

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 中的一個(gè)
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
數(shù)據(jù)庫裝載完畢。
數(shù)據(jù)庫已經(jīng)打開。
至此恢復(fù)完成。

關(guān)于 ORACLE 服務(wù)器異常斷電及控制文件故障的處理步驟是什么就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。

正文完