• Oracle重建控制文件的實例教程

     更新時間:2018年05月28日 08:39:50   作者:AlfredZhao  
    前些天在做Oracle數據庫恢復測試時,因為一些異常操作導致控制文件出了問題,數據庫無法正常使用,下面這篇文章就來給大家介紹了關于Oracle重建控制文件的相關資料,需要的朋友可以參考借鑒,下面來一起看看吧

    前言

    控制文件(control file)是一個相當小的文件(最多能增長到64M左右),其中包含Oracle需要的其他文件的一個目錄。參數文件告知實例控制文件的位置,控制文件則告知示例數據庫和在線重做日志文件的位置??刂莆募€告知了Oracle其他一些事情,如已發生檢查點的有關信息、數據庫名(必須和db_name參數匹配)、創建數據庫的時間戳、歸檔重做日志的歷史(有時這會讓控制文件變大)、RMAN信息等。

    環境:OEL 5.7 + Oracle 10.2.0.5

    背景:在Oracle的運維過程中,時常會遇到一些場景是需要重建控制文件才可以解決的。本文的場景可以通過復制控制文件到新路徑,運行一段時間后,再用老的控制文件啟動數據庫重現。

    1.當前故障現象

    在使用舊的控制文件啟動數據庫時,報錯ORA-01122、ORA-01110、ORA-01207:

    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 599785472 bytes
    Fixed Size   2098112 bytes
    Variable Size  218106944 bytes
    Database Buffers  373293056 bytes
    Redo Buffers  6287360 bytes
    Database mounted.
    ORA-01122: database file 1 failed verification check
    ORA-01110: data file 1: '/oradata/cxywdb/system01.dbf'
    ORA-01207: file is more recent than control file - old control file

    2.分析故障原因

    根據報錯信息查找MOS文檔:

    ORA-1122, ORA-1110, ORA-1207 while open the database after crash (文檔 ID 283927.1)

    延伸思考一下,為什么會這樣?

    主要錯誤是ORA-01207,利用oerr工具看到Oralce對這個錯誤的詳細描述是:

    01207, 00000, "file is more recent than control file - old control file"
    // *Cause:  The control file change sequence number in the data file is
    //         greater than the number in the control file. This implies that
    //         the wrong control file is being used. Note that repeatedly causing
    //         this error can make it stop happening without correcting the real
    //         problem. Every attempt to open the database will advance the
    //         control file change sequence number until it is great enough.
    // *Action: Use the current control file or do backup control file recovery to
    //         make the control file current. Be sure to follow all restrictions
    //         on doing a backup control file recovery.

    一般遇到這種情況,當前的控制文件肯定是找不到了。那么就得考慮重建控制文件來解決,MOS給出的建議也是重建控制文件。

    3.重建控制文件

    重建控制文件的核心步驟:

    3.1 備份控制文件到trace

    startup mount
    alter database backup controlfile to trace;
    oradebug setmypid
    oradebug tracefile_name

    3.2 啟動數據庫到nomount狀態

    shutdown abort
    startup nomount;

    3.3 確認重建控制文件的語句

    vi control.sql
    CREATE CONTROLFILE REUSE DATABASE "CXYWDB" NORESETLOGS FORCE LOGGING ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 292
    LOGFILE
     GROUP 5 '/oradata2/cxywdb/redo11.log' SIZE 50M,
     GROUP 6 '/oradata2/cxywdb/redo12.log' SIZE 50M,
     GROUP 7 '/oradata2/cxywdb/redo13.log' SIZE 50M
    -- STANDBY LOGFILE
    DATAFILE
     '/oradata/cxywdb/system01.dbf',
     '/oradata/cxywdb/undotbs01.dbf',
     '/oradata/cxywdb/sysaux01.dbf',
     '/oradata/cxywdb/users01.dbf',
     '/oradata/cxywdb/alfred01.dbf',
     '/oradata/cxywdb/alfred02.dbf',
     '/oradata/cxywdb/alfred03.dbf',
     '/oradata/cxywdb/alfred04.dbf',
     '/oradata/cxywdb/alfred05.dbf',
     '/oradata/cxywdb/dbs_i_alfred01.dbf'
    CHARACTER SET ZHS16GBK
    ;

    3.4 恢復并打開數據庫

    SQL> recover database;
    Media recovery complete.
    SQL> alter database open;
    Database altered.

    附:實際解決過程如下:

    SQL> shutdown abort  
    ORACLE instance shut down.
    SQL> startup mount
    
    ORACLE instance started.
    
    Total System Global Area 599785472 bytes
    Fixed Size     2098112 bytes
    Variable Size    218106944 bytes
    Database Buffers   373293056 bytes
    Redo Buffers    6287360 bytes
    Database mounted.
    SQL> alter database backup controlfile to trace;
    
    Database altered.
    
    SQL> oradebug setmypid
    Statement processed.
    SQL> oradebug tracefile_name
    /s01/oracle/admin/cxywdb/udump/cxywdb_ora_3983.trc
    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> startup nomount;
    ORACLE instance started.
    
    Total System Global Area 599785472 bytes
    Fixed Size     2098112 bytes
    Variable Size    218106944 bytes
    Database Buffers   373293056 bytes
    Redo Buffers    6287360 bytes
    SQL> @control
    
    Control file created.
    SQL> select status from v$instance;
    
    STATUS
    ------------
    MOUNTED
    
    SQL> recover database;
    Media recovery complete.
    SQL> alter database open;
    
    Database altered.

    總結

    以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。

    相關文章

    最新評論

    美丽人妻被按摩中出中文字幕