<menu id="u2y6m"></menu>
<tt id="u2y6m"><rt id="u2y6m"></rt></tt>
  • <input id="u2y6m"></input><tt id="u2y6m"><blockquote id="u2y6m"></blockquote></tt>
    <bdo id="u2y6m"><small id="u2y6m"></small></bdo>
  • <blockquote id="u2y6m"><optgroup id="u2y6m"></optgroup></blockquote>
  • 溫馨提示×

    數據庫遭遇ORA-01200錯誤的原因有哪些

    發布時間:2021-10-16 13:16:00 來源:億速云 閱讀:94 作者:小新 欄目:云計算

    這篇文章給大家分享的是有關數據庫遭遇ORA-01200錯誤的原因有哪些的內容。小編覺得挺實用的,因此分享給大家做個參考,一起跟隨小編過來看看吧。

    1、案例現象
    在數據庫startup時,報錯:
    [oracle@localhost ~]$ sqlplus "/as sysdba"
    SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 19 19:31:05 2011
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    Connected to an idle instance.
    SQL> startup
    ORACLE instance started.
    Total System Global Area  202445884 bytes
    Fixed Size                   451644 bytes
    Variable Size             167772160 bytes
    Database Buffers           33554432 bytes
    Redo Buffers                 667648 bytes
    Database mounted.
    ORA-01122: database file 1 failed verification check
    ORA-01110: data file 1: '/u01/u01d/sysaux01.dbf'
    ORA-01200: actual file size of 38400 is smaller than correct size of 51200 blocks

    2、問題分析

    SQL> select status from v$instance;
    STATUS
    ------------
    MOUNTED
    SQL>
    報錯說,數據文件的實際大小與正確的大小較小。
    SQL> col name for a50
    SQL> select file#,status,bytes/1024/1024 mb,name from v$datafile;
         FILE# STATUS          MB NAME
    ---------- ------- ---------- --------------------------------------------------
             1 SYSTEM         300 /u01/u01d/system01.dbf
             2 ONLINE         100 /u01/u01d/undotbs01.dbf
      3 SYSTEM   400 /u01/u01d/sysaux01.dbf
             4 ONLINE          25 /u01/u01d/users01.dbf
             5 ONLINE          25 /u01/u01d/indx01.dbf
             6 ONLINE         100 /u01/u01d/perfstat.dbf
             7 ONLINE          10 /u01/u01d/timi01.dbf
    6 rows selected.
    SQL>
    可以看出控制文件記錄的是400M,而du -sh system01.dbf結果是301M。接著,查閱在線幫助文檔:
    [oracle@localhost ~]$oerr ora 01200
    01200, 00000, "actual file size of %s is smaller than correct size of %s blocks"
    // *Cause:  The size of the file as returned by the operating system is smaller
    //         than the size of the file as indicated in the file header and the
    //         controlfile. Somehow the file has been truncated. Maybe it is the
    //         result of a half completed copy.
    // *Action: Restore a good copy of the data file and do recovery as needed.
    [oracle@localhost ~]$
    可以看出,是數據文件的實際大小與控制文件和該數據文件的頭部所記錄的大小不同而引起的。一般是數據庫異常導致的,在數據庫的運行過程中,重新啟動時,文件resize之后出現異常狀況時都可能會遇到。下面看看怎么恢復,以打開數據庫:

    3、確定解決方案

    首先轉儲數據文件頭部看看:
    SQL> alter session set events 'immediate trace name FILE_HDRS level 10';
    Session altered.
    SQL> @/u01/admin/mytools/myscripts/gettrcname.sql
    TRACE_FILE_NAME
    --------------------------------------------------------------------------------------------------------------
    /u01/admin/denver/udump/denver_ora_4669.trc
    SQL>
    [oracle@localhost ~]$more /u01/admin/denver/udump/denver_ora_5349.trc
    .....
    FILE HEADER:
            Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000
            Db ID=4004057640=0xeea91228, Db Name='DENVER'
            Activation ID=0=0x0
           Control Seq=1908=0x774, File size=51200=0xc800  //可見此處的大小是51200, 而ORA-01200報告說實際大小是38400
            File Number=1, Blksiz=8192, File Type=3 DATA
    Tablespace #0 - SYSTEM  rel_fn:1
    .....

    解決方案的確定:
    1.如果有備份可以采用備份來恢復.
    2.最壞的情況是將文件上的數據提取出來,重新加載到數據庫。
    3.最好的情況是通過一些特殊的手段,將該文件恢復,重新ONLINE運行。
    4.通過Metalink及各種途徑尋找已知的解決方案。

    此時,發現根本沒有任何的備份。沒辦法,只有使用bbed工具來非常規恢復了。

    4、用BBED工具恢復

    bbed就是英文block browse block edit的縮寫,用來直接查看和修改數據文件數據的一個工具。在windows上沒有提供這個工具,在linux上面有,需要編譯:
    make -f ins_rdbms.mk BBED=$ORACLE_HOME/rdbms/lib/bbed $ORACLE_HOME/bin/bbed
    然后把$ORACLE_HOME/rdbms/lib加到環境變量的PATH里面,就可以直接在命令中bbed了。不過有個默認的口令blockedit。在運行bbed之前先要自己寫幾個配置文件:

    這里假定有2個文件,分別是file.txt、par.bbd

    [oracle@localhost ~]$ ls
    file.txt bbed.par
    [oracle@localhost ~]$ more bbed.par
    blocksize=8192
    listfile=/home/oracle/file
    mode=edit
    [oracle@localhost ~]$ more file.txt
    3 /free/oracle/oradata/orcl/sysaux01.dbf 1614807040

    5、恢復過程

    接下來就可以使用BBED工具了:
    [oracle@localhost ~]$ bbed
    Password:

    BBED: Release 2.0.0.0.0 - Limited Production on Tue Sep 20 18:19:33 2011

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    ************* !!! For Oracle Internal Use only !!! ***************

    BBED>


    BBED> modify /x 00960000 offset 44  ---用find /x c8 curr 找出51200所在之處, 因為38400的十六進制為9600,在根據倒位法則就是0096
    File: /u01/u01d/system01.dbf (1)
    Block: 1                Offsets:   44 to  555           Dba:0x00400001
    ------------------------------------------------------------------------
    00960000 00200000 01000300 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 a1014000 07000000 00000000
    4bbaa02a 9aabd02a 0e3a0f00 00000000 00000000 00000000 00000000 00000400
    7a061000 00000000 86a7dd2a 01005162 01000000 8d660000 1000ffbf 02000000
    00000000 6c010000 54abd02a 6b010000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 06005359 5354454d 00000000 00000000 00000000
    00000000 00000000 00000000 01000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 0a000a00 9cefc52a
    5c0a0d00 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    <32 bytes per line>
    BBED> sum apply
    Check value for File 1, Block 1:
    current = 0x1459, required = 0x1459
    BBED> quit

    試著打開數據庫:
    SQL> alter database open;
    Database altered.
    SQL> col name for a50
    SQL> select file#,status,bytes/1024/1024 mb,name from v$datafile;
         FILE# STATUS          MB NAME
    ---------- ------- ---------- --------------------------------------------------
             1 SYSTEM         300 /u01/u01d/system01.dbf
             2 ONLINE         100 /u01/u01d/undotbs01.dbf
      3 SYSTEM   300 /u01/u01d/sysaux01.dbf  ---控制文件里記錄是300M,而且數據庫已經成功打開
             4 ONLINE          25 /u01/u01d/users01.dbf
             5 ONLINE          25 /u01/u01d/indx01.dbf
             6 ONLINE         100 /u01/u01d/perfstat.dbf
             7 ONLINE          10 /u01/u01d/timi01.dbf
    6 rows selected.
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    Total System Global Area  202445884 bytes
    Fixed Size                   451644 bytes
    Variable Size             167772160 bytes
    Database Buffers           33554432 bytes
    Redo Buffers                 667648 bytes
    Database mounted.
    Database opened.
    ok, 成功打開數據庫。

    感謝各位的閱讀!關于“數據庫遭遇ORA-01200錯誤的原因有哪些”這篇文章就分享到這里了,希望以上內容可以對大家有一定的幫助,讓大家可以學到更多知識,如果覺得文章不錯,可以把它分享出去讓更多的人看到吧!

    免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

    免費撥打  400 100 2938 免費撥打 400 100 2938
    24小時售后技術支持 24小時售后技術支持
    返回頂部 返回頂部
    <menu id="u2y6m"></menu>
    <tt id="u2y6m"><rt id="u2y6m"></rt></tt>
  • <input id="u2y6m"></input><tt id="u2y6m"><blockquote id="u2y6m"></blockquote></tt>
    <bdo id="u2y6m"><small id="u2y6m"></small></bdo>
  • <blockquote id="u2y6m"><optgroup id="u2y6m"></optgroup></blockquote>
  • 一本久道久久综合丁香五月_免费视频禁止18以下禁止观看_日本japanese熟睡人妻_成熟闷骚女邻居引诱2