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

*

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;

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

 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

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

控制檔案已建立。

SQL> alter database mount;

alter database mount

ORA-01100: ??????

SQL> alter session set nls_language='american';--中文字元集顯示亂碼，需要更改session字元集

Session altered.

ERROR at line 1:

ORA-01100: database already mounted

SQL> alter database open resetlogs;

alter database open resetlogs

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

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'

G:\app\Administrator\oradata\orcl\REDO02.LOG 這裡指定是redolog中的一個

Log applied.

Media recovery complete.

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

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

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

資料庫已經打開。

至此恢複完成