startup資料庫報錯ORA-16038

xz43發表於2011-11-18
資料庫
使用startup啟動資料庫，報錯如下：
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
ORA-16038: log 3 sequence# 21 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/var/oracledata/orcl/redo03.log'
奇怪！很久沒啟動這資料庫了，怎麼上來就報錯。
從 limit exceeded for recovery files 可以看出，recovery files超出了限制。
SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
資料庫現在是mount狀態，沒有open；
SQL> show parameter db_recovery_file
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string                            /oracle/flash_recovery_area
db_recovery_file_dest_size           big integer                       2G
由上面的引數 db_recovery_file_dest_size 可以看到，這裡設定的閃回區只有2G，那看看已使用的有多少。
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                        98.78                         0              25
BACKUPPIECE                                         .34                       .34               1
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                          0                         0               0
6 rows selected.
由上面的查詢結果可以看出，閃回區的99％以上的空間被被佔用了，看來是這個閃回區大小的問題了，如是，先加大它。
SQL> alter system set db_recovery_file_dest_size=3G scope=both;          
System altered.
更改為3G後，再open試試
SQL> alter database open;
Database altered.
SQL>
資料庫終於正常啟動了。
然後，我把剛才設定為3G的引數又改為2G，看看會不會報什麼錯，結果很意外。
SQL> alter system set db_recovery_file_dest_size=2G scope=both;          
System altered.
SQL> show parameter recovery
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer
2G
recovery_parallelism                 integer
0
SQL>
奇怪，居然沒影響。下班了，關機走人。
 
第二天，帶著疑問，繼續看看這個問題。
SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------------------------------------------
NO
SQL>
懷疑會不會是oracle自動把flashback功能關閉了，昨天剛啟動時明明報那錯誤的。既然現在停了flashback功能，那就手動啟動試試看。
SQL> alter database flashback on;
alter database flashback on;
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.
 
靠，啟動這都報錯，可能是因為我這資料庫是從其他機器遷移過來的緣故，按照遇到相同問題人士的解決辦法。
首先，使Oracle無論什麼操作都進行redo的寫入。
SQL> alter database force logging;
alter database force logging
*
ERROR at line 1:
ORA-12920: database is already in force logging mode
再關閉flashbakc功能。
SQL> alter database flashback off;
Database altered.
SQL> alter database open;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
mount模式重啟資料庫。
SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
SQL> alter database flashback on ;
Database altered.
SQL> alter system set db_recovery_file_dest_size=2G scope=both;   
System altered.
SQL>select * from v$flash_recovery_area_usage;
FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                        134.2                         0              31
BACKUPPIECE                                           0                         0               0
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                        .74                         0               1
6 rows selected.
奇怪，怎麼閃回區的使用率超過了100％。會不會因為上面的那句把原來的3G改為現在的2G引起的，重啟試試，看是否會恢復或報錯。
SQL> alter database open;
Database altered.
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------------------------------------------
YES
可以確定，現在已經開啟了flashback功能。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                        134.2                         0              31
BACKUPPIECE                                           0                         0               0
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                        .74                         0               1
6 rows selected.
SQL> show parameter db_recovery
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string                            /oracle//flash_recovery_area
db_recovery_file_dest_size           big integer                       2G
SQL>
重啟後，閃回區還是2G，而它的使用率超過了100％，資料庫還能正常使用，不明白怎麼回事了，繼續觀察一段時間再說吧。
 
建立個測試表，寫段程式碼，迴圈100萬次，往表裡面插入資料，執行到後來資料庫hold住了，強行停止並重啟機器，再檢查那個引數是否真的沒影響了。
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             352324608 bytes
Database Buffers          838860800 bytes
Redo Buffers               14753792 bytes
Database mounted.
ORA-16038: log 3 sequence# 27 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/var/oracledata/kms/redo03.log'
 
問題終於還是出來了，出現最開始的不能開啟問題，只不過這次在意料之中。

SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------------------------------------------
YES
SQL> set linesize 200                                                              
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                        134.2                         0              31
BACKUPPIECE                                           0                         0               0
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                        .74                         0               1
6 rows selected.
SQL> show parameter db_recovery
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string                            /oracle/flash_recovery_area
db_recovery_file_dest_size           big integer                       2G
透過以上查詢結果，查明資料庫當前的flashback功能啟動了，而閃回區的使用已經超過了100％。
嘗試關閉flashback功能，在開啟資料庫試試，看是否還受閃回區不足的限制。
SQL> alter database flashback off;
Database altered.
SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 27 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/var/oracledata/kms/redo03.log'

SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------------------------------------------
NO
 
以上證明，即使關閉了flashback，資料庫還是打不開。嘗試重啟資料庫再試，還是一樣的報錯，原來是這樣的。
透過
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     27
Next log sequence to archive   27
Current log sequence           29
和
SQL> show parameter archive
發現 log_archive_dest_n都沒設定，歸檔日誌在db_recovery_file_dest 路徑下。
SQL> host tree /oracle//flash_recovery_area
/oracle//flash_recovery_area
`-- ORCL
    |-- archivelog
    |   |-- 2011_03_11
    |   |-- 2011_03_14
    |   |   |-- o1_mf_1_1_6qvk8fc9_.arc
    |   |   |-- o1_mf_1_1_6qvt1jd9_.arc
    |   |   |-- o1_mf_1_1_6qvt78l7_.arc
    |   |   |-- o1_mf_1_2_6qvkhbhk_.arc
    |   |   |-- o1_mf_1_2_6qvt1j9z_.arc
    |   |   |-- o1_mf_1_2_6qvt8fqo_.arc
    |   |   |-- o1_mf_1_3_6qvt1jcb_.arc
    |   |   `-- o1_mf_1_3_6qvtb51m_.arc
    |   |-- 2011_03_22
    |   |   `-- o1_mf_1_4_6rhpz7km_.arc
    |   |-- 2011_03_23
    |   |   |-- o1_mf_1_5_6rkkw2rr_.arc
    |   |   `-- o1_mf_1_6_6rmzjlmd_.arc
    |   |-- 2011_03_24
    |   |   `-- o1_mf_1_7_6rpmmk7f_.arc
    |   |-- 2011_03_25
    |   |   `-- o1_mf_1_8_6rrhcz8s_.arc
    |   |-- 2011_03_26
    |   |   `-- o1_mf_1_9_6rt8jjmf_.arc
    |   |-- 2011_03_27
    |   |   `-- o1_mf_1_10_6rwfy45k_.arc
    |   |-- 2011_03_28
    |   |   `-- o1_mf_1_11_6rz9cyg5_.arc
    |   |-- 2011_03_29
    |   |   |-- o1_mf_1_12_6s1d42vn_.arc
    |   |   `-- o1_mf_1_13_6s3shj86_.arc
    |   |-- 2011_03_30
    |   |   `-- o1_mf_1_14_6s6fv9f5_.arc
    |   |-- 2011_03_31
    |   |   `-- o1_mf_1_15_6s9274sz_.arc
    |   |-- 2011_04_01
    |   |   `-- o1_mf_1_16_6sc1k1bf_.arc
    |   |-- 2011_04_02
    |   |   `-- o1_mf_1_17_6sdq6l0g_.arc
    |   |-- 2011_04_03
    |   |   `-- o1_mf_1_18_6shmvv52_.arc
    |   |-- 2011_04_04
    |   |   `-- o1_mf_1_19_6smmq5p5_.arc
    |   |-- 2011_04_05
    |   |   `-- o1_mf_1_20_6soplgym_.arc
    |   |-- 2011_04_06
    |   |-- 2011_04_07
    |   |-- 2011_04_08
    |   |-- 2011_04_09
    |   |-- 2011_04_10
    |   |-- 2011_04_11
    |   |-- 2011_11_18
    |   |   |-- o1_mf_1_21_7dbm3nfr_.arc
    |   |   |-- o1_mf_1_22_7dbm3rfo_.arc
    |   |   |-- o1_mf_1_23_7dbm3s3f_.arc
    |   |   |-- o1_mf_1_24_7ddrynsr_.arc
    |   |   |-- o1_mf_1_25_7ddrynt9_.arc
    |   |   `-- o1_mf_1_26_7ddrytld_.arc
    |   `-- 2011_11_19
    |       |-- o1_mf_1_27_7df18xbp_.arc
    |       `-- o1_mf_1_28_7df1919h_.arc
    |-- flashback
    `-- onlinelog
29 directories, 33 files
SQL>
這裡，我透過rman來刪除歸檔日誌試試，因為這裡只是測試環境，歸檔日誌沒什麼用。
[oracle@oraclesvr ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on 星期六 11月 19 00:32:51 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1986166942)
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvt1jd9_.arc recid=23 stamp=745783472
validation succeeded for archived log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvk8fc9_.arc recid=15 stamp=745783368
validation succeeded for archived log
。。。
Crosschecked 33 objects

RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
specification does not match any archive log in the recovery catalog
RMAN> delete archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
List of Archived Log Copies
Key     Thrd Seq     S Low Time   Name
------- ---- ------- - ---------- ----
23      1    1       A 14-3月 -11 /oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvt1jd9_.arc
15      1    1       A 14-3月 -11 /oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvk8fc9_.arc
。。。

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvt1jd9_.arc recid=23 stamp=745783472
deleted archive log
archive log filename=/oracle/flash_recovery_area/ORCL/archivelog/2011_03_14/o1_mf_1_1_6qvk8fc9_.arc recid=15 stamp=745783368
。。。
 
Deleted 33 objects

RMAN> exit
SQL> conn /as sysdba
Connected.
SQL> set linesize 200
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------ ------------------ ------------------------- ---------------
CONTROLFILE                                           0                         0               0
ONLINELOG                                             0                         0               0
ARCHIVELOG                                         3.87                         0               1
BACKUPPIECE                                           0                         0               0
IMAGECOPY                                             0                         0               0
FLASHBACKLOG                                          0                         0               0
6 rows selected.
SQL>
閃回區變小了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9399028/viewspace-711577/，如需轉載，請註明出處，否則將追究法律責任。

相關文章