--========================================
-- 又一例 SPFILE 設置錯誤導致數據庫無法啟動
--========================================
?
??? SPFILE 參數錯誤，容易導致數據庫無法啟動。關于 SPFILE 設置錯誤處理辦法的總結，請參照： SPFILE 錯誤導致數據庫無法啟動
???
??? 此次的情況與上次的稍有不同，故列出該次的恢復過程
?
故障 ???
??? SQL > startup
??? ORA - 16032 : parameter LOG_ARCHIVE_DEST destination string cannot be translated
??? ORA - 07286 : sksagdi : cannot obtain device information .
??? Linux Error : 2 : No such file or directory
???
分析
??? SQL > ho cat / u01 / app / oracle / admin / orcl / bdump / alert_orcl . log ?? -- 查看告警日志
??? Tue Aug ? 3 10 : 38 : 25 2010
??? ALTER SYSTEM SET log_archive_dest = 'u01/app/oracle/archivelog1' SCOPE = SPFILE ; -- 此處路徑開始處少了 "/"
??? Tue Aug ? 3 10 : 39 : 59 2010
??? ALTER SYSTEM SET log_archive_duplex_dest = '/u01/app/oracle/archivelog2' SCOPE = SPFILE ;
??? Tue Aug ? 3 10 : 40 : 25 2010
??? Incremental changes to log_archive_dest_1 not allowed with SPFILE
??? Tue Aug ? 3 10 : 40 : 43 2010
??? ALTER SYSTEM SET log_archive_dest_1 = 'location=/u01/app/oracle/archivelog3' SCOPE = SPFILE ;
??? Tue Aug ? 3 10 : 40 : 57 2010
??? ALTER SYSTEM SET log_archive_dest_2 = 'location=/u01/app/oracle/archivelog4' SCOPE = SPFILE ;
??? Tue Aug ? 3 10 : 41 : 02 2010
??? Starting background process EMN0
??? EMN0 started with pid = 21 , OS id = 3944
??? Tue Aug ? 3 10 : 41 : 02 2010
??? Shutting down instance : further logons disabled ??? -- 實例開始關閉
??? Tue Aug ? 3 10 : 41 : 03 2010
??? Stopping background process QMNC
??? Tue Aug ? 3 10 : 41 : 04 2010
??? Stopping background process CJQ0
??? Tue Aug ? 3 10 : 41 : 05 2010
??? Stopping background process MMNL
??? Tue Aug ? 3 10 : 41 : 06 2010
??? Stopping background process MMON
??? Tue Aug ? 3 10 : 41 : 07 2010
??? Shutting down instance ( immediate )
??? License high water mark = 7
??? Tue Aug ? 3 10 : 41 : 07 2010
??? Stopping Job queue slave processes
??? Tue Aug ? 3 10 : 41 : 12 2010
??? Process OS id : 3942 alive after kill
??? Errors in file / u01 / app / oracle / admin / orcl / udump / orcl_ora_3859 . trc
??? Tue Aug ? 3 10 : 41 : 12 2010
??? Job queue slave processes stopped
??? All dispatchers and shared servers shutdown
??? Tue Aug ? 3 10 : 41 : 14 2010
??? ALTER DATABASE CLOSE NORMAL
??? Tue Aug ? 3 10 : 41 : 15 2010
??? SMON : disabling tx recovery
??? SMON : disabling cache recovery
??? Tue Aug ? 3 10 : 41 : 15 2010
??? Shutting down archive processes
??? Archiving is disabled
??? Tue Aug ? 3 10 : 41 : 20 2010
??? ARCH shutting down
??? ARC1 : Archival stopped
??? Tue Aug ? 3 10 : 41 : 25 2010
??? ARCH shutting down
??? ARC0 : Archival stopped
??? Tue Aug ? 3 10 : 41 : 26 2010
??? Thread 1 closed at log sequence 46
??? Successful close of redo thread 1
??? Tue Aug ? 3 10 : 41 : 26 2010
??? Completed : ALTER DATABASE CLOSE NORMAL
??? Tue Aug ? 3 10 : 41 : 26 2010
??? ALTER DATABASE DISMOUNT
??? Completed : ALTER DATABASE DISMOUNT
??? ARCH : Archival disabled due to shutdown : 1089
??? Shutting down archive processes
??? Archiving is disabled
??? Archive process shutdown avoided : 0 active
??? ARCH : Archival disabled due to shutdown : 1089
??? Shutting down archive processes
??? Archiving is disabled
??? Archive process shutdown avoided : 0 active
??? Tue Aug ? 3 10 : 41 : 49 2010 ??????????????????????? -- 至此所有的進程都被關閉
??? Starting ORACLE instance ( normal ) ?????????????? -- 啟動后僅出現了行提示
??? LICENSE_MAX_SESSION = 0
??? LICENSE_SESSIONS_WARNING = 0
??? Picked latch - free SCN scheme 2
?
??? SQL > ho ps - ef | grep oracle ??? ? -- 后臺進程被關閉
??? root ????? 3796 ? 3764 ? 0 10 : 28 pts / 0 ??? 00 : 00 : 00 su - oracle
??? oracle ??? 3797 ? 3796 ? 0 10 : 29 pts / 0 ??? 00 : 00 : 00 - bash
??? oracle ??? 3829 ? 3797 ? 0 10 : 29 pts / 0 ??? 00 : 00 : 00 / usr / bin / perl - w / usr / bin / uniread sqlplus / as sysdba
??? oracle ??? 3830 ? 3829 ? 0 10 : 29 pts / 1 ??? 00 : 00 : 00 sqlplus ?? as sysdba
??? oracle ??? 3947 ? 3830 ? 1 10 : 41 ? ??????? 00 : 00 : 02 oracleorcl ( DESCRIPTION =( LOCAL = YES )( ADDRESS =( PROTOCOL = beq )))
??? oracle ??? 3953 ? 3830 ? 0 10 : 43 pts / 1 ??? 00 : 00 : 00 / bin / bash - c ps - ef | grep oracle
??? oracle ??? 3954 ? 3953 ? 0 10 : 43 pts / 1 ??? 00 : 00 : 00 ps - ef
?
??? SQL > ? ho strings / u01 / app / oracle / 10g / dbs / spfileorcl . ora ? -- 查看 spfileorcl 的信息
?
??? orcl . __db_cache_size = 167772160
??? orcl . __java_pool_size = 4194304
??? orcl . __large_pool_size = 4194304
??? orcl . __shared_pool_size = 71303168
??? orcl . __streams_pool_size = 0
??? *. audit_file_dest = '/u01/app/oracle/admin/orcl/adump'
??? *. background_dump_dest = '/u01/app/oracle/admin/orcl/bdump'
??? *. compatible = '10.2.0.1.0'
??? *. control_files = '/u01/app/oracle/oradata/orcl/control01.ctl' , '/u01/app/oracle/oradata/orcl/control02.ctl'
??? *. core_dump_dest = '/u01/app/oracle/admin/orcl/cdump'
??? *. db_block_size = 8192
??? *. db_domain = ''
??? *. db_file_multiblock_read_count = 16
??? *. db_name = 'orcl'
??? *. db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
??? *. db_recovery_file_dest_size = 2147483648
??? *. dispatchers = '(PROTOCOL=TCP) (SERVICE=orclXDB)'
??? *. fast_start_mttr_target = 30
??? *. job_queue_processes = 10
??? *. log_archive_dest_1 = 'location=/u01/app/oracle/archivelog3'
??? *. log_archive_dest_2 = 'location=/u01/app/oracle/archivelog4'
??? *. log_archive_dest_state_2 = 'ENABLE'
??? *. log_archive_dest = 'u01/app/oracle/archivelog1' ?? -- 同告警日志提示的一樣，路徑開始處少了 "/"
??? *. log_archive_duplex_dest = '/u01/app/oracle/archivelog2'
??? *. log_archive_format = 'arc_%t_%s_%r.arc'
??? *. open_cursors = 300
??? *. pga_aggregate_target = 83886080
??? *. processes = 150
??? *. remote_login_passwordfile = 'EXCLUSIVE'
??? *. sga_target = 251658240
??? *. undo_management = 'AUTO'
??? *. undo_tablespace = 'UNDOTBS1'
??? *. user_dump_dest = '/u01/app/oracle/admin/orcl/udump'
?
解決
??? SQL > vim / u01 / app / oracle / 10g / dbs / initorcl . ora ??? -- 由于沒有備份的參數文件，在此新建一個 pfile.
??????????????????????????????????????????????????? ? -- 如果有內容可以先清空其內容然后再添加如下內容并保存
??? spfile = '/u01/app/oracle/10g/dbs/spfileorcl.ora'
??? log_archive_dest = '/u01/app/oracle/archivelog1/'
?
??? SQL > startup pfile = '$ORACLE_HOME/dbs/initorcl.ora' ; ? -- 再次提示錯誤，原來是歸檔方式不兼容導致
??? ORA - 16019 : cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST
?
??? -- 根據上面的錯誤提示在使用 LOG_ARCHIVE_DEST_1 不能同時指定 LOG_ARCHIVE_DEST 和 LOG_ARCHIVE_DUPLEX_DEST 參數
??? -- 再次修改 initorcl.ora ，添加以下內容，添加后的結果如下
??? -- 注意此處是將 log_archive_dest_1 和 log_archive_dest_2 清空
??? -- 也可以將 log_archive_dest 和 log_archive_duplex_dest 置空，因為這是兩種不同的歸檔方式，互不兼容
?
??? SQL > ho cat / u01 / app / oracle / 10g / dbs / initorcl . ora ??? -- 查看修改后的初始化參數
??? spfile = '/u01/app/oracle/10g/dbs/spfileorcl.ora'
??? log_archive_dest = '/u01/app/oracle/archivelog1/'
??? log_archive_dest_1 = ''
??? log_archive_dest_2 = ''
?
??? SQL > startup pfile = '/u01/app/oracle/10g/dbs/initorcl.ora' ;
??? ORACLE instance started .
?
??? Total System Global Area ? 251658240 bytes
??? Fixed Size ????????????????? 1218796 bytes
??? Variable Size ????????????? 79693588 bytes
??? Database Buffers ????????? 167772160 bytes
??? Redo Buffers ??????????????? 2973696 bytes
??? Database mounted .
??? Database opened .
?
??? SQL > show parameter pfile ;
?
??? NAME ???????????????????????????????? TYPE ??????? VALUE
??? ------------------------------------ ----------- ------------------------------
??? spfile ?????????????????????????????? string ????? / u01 / app / oracle / 10g / dbs / spfile
??????????????????????????????????????????????????? ? orcl . ora
??? SQL > show parameter spfile ;
?
??? NAME ???????????????????????????????? TYPE ??????? VALUE
??? ------------------------------------ ----------- ------------------------------
??? spfile ?????????????????????????????? string ????? / u01 / app / oracle / 10g / dbs / spfile
??????????????????????????????????????????????????? ? orcl . ora
??????????????????????????????????????????????????? ?
??? SQL > desc v$spparameter
??? ? Name ????????????????????????????????????? Null ? ??? Type
??? ? ----------------------------------------- -------- ----------------------------
??? ? SID ??????????????????????????????????????????????? VARCHAR2 ( 80 )
??? ? NAME ?????????????????????????????????????????????? VARCHAR2 ( 80 )
??? ? VALUE ????????????????????????????????????????????? VARCHAR2 ( 255 )
??? ? DISPLAY_VALUE ????????????????????????????????????? VARCHAR2 ( 255 )
??? ? ISSPECIFIED ??????????????????????????????????????? VARCHAR2 ( 6 )
??? ? ORDINAL ??????????????????????????????????????????? NUMBER
??? ? UPDATE_COMMENT ???????????????????????????????????? VARCHAR2 ( 255 )
?
??? SQL > select distinct isspecified from v$spparameter ;
?
??? ISSPEC
??? ------
??? TRUE ?????? -- 第一行為 true 可以知道數據庫使用 spfile 參數啟動
??? FALSE
?
??? SQL > show parameter log_archive ?? -- 查看 log_archive 相關參數
?
??? NAME ???????????????????????????????? TYPE ??????? VALUE
??? ------------------------------------ ----------- ------------------------------
??? log_archive_config ?????????????????? string
??? log_archive_dest ???????????????????? string ????? / u01 / app / oracle / archivelog1 / ? -- 該參數已顯示正確路徑
??? log_archive_dest_1 ?????????????????? string ????? -- 該參數已經被初始化的 pfile 置空
??? log_archive_dest_10 ????????????????? string
??? log_archive_dest_2 ?????????????????? string ????? -- 該參數已經被初始化的 pfile 置空
??? log_archive_dest_3 ?????????????????? string
??? log_archive_dest_4 ?????????????????? string
??? log_archive_dest_5 ?????????????????? string
??? log_archive_dest_6 ?????????????????? string
??? log_archive_dest_7 ?????????????????? string
??? log_archive_dest_8 ?????????????????? string
?
??? NAME ???????????????????????????????? TYPE ??????? VALUE
??? ------------------------------------ ----------- ------------------------------
??? log_archive_dest_9 ?????????????????? string
??? log_archive_dest_state_1 ???????????? string ??? ?? enable
??? log_archive_dest_state_10 ??????????? string ????? enable
??? log_archive_dest_state_2 ???????????? string ????? ENABLE
??? log_archive_dest_state_3 ???????????? string ????? enable
??? log_archive_dest_state_4 ???????????? string ????? enable
??? log_archive_dest_state_5 ???????????? string ????? enable
??? log_archive_dest_state_6 ???????????? string ????? enable
??? log_archive_dest_state_7 ???????????? string ????? enable
??? log_archive_dest_state_8 ???????????? string ????? enable
??? log_archive_dest_state_9 ??? ????????? string ????? enable
?
??? NAME ???????????????????????????????? TYPE ??????? VALUE
??? ------------------------------------ ----------- ------------------------------
??? log_archive_duplex_dest ????????????? string ????? / u01 / app / oracle / archivelog2
??? log_archive_format ?????????????????? string ????? arc_ % t_ % s_ % r . arc
??? log_archive_local_first ????????????? boolean ???? TRUE
??? log_archive_max_processes ??????????? integer ???? 2
??? log_archive_min_succeed_dest ???????? integer ???? 1
??? log_archive_start ??????????????????? boolean ???? FALSE
??? log_archive_trace ??????????????????? integer ???? 0
?
??? -- 將出現錯誤的幾個參數永久化修改到 spfile 參數
??? SQL > alter system set log_archive_dest = '/u01/app/oracle/archivelog1' scope = spfile ;
?
??? System altered .
?
??? SQL > alter system set log_archive_dest_1 = '' scope = spfile ;
?
??? System altered .
?
??? SQL > alter system set log_archive_dest_2 = '' scope = spfile ;
?
??? System altered .
?
??? SQL > startup ??????????? -- 實例正常從 spfile 啟動
??? ORACLE instance started .
?
??? Total System Global Area ? 251658240 bytes
??? Fixed Size ????????????????? 1218796 bytes
??? Variable Size ????????????? 79693588 bytes
??? Database Buffers ????????? 167772160 bytes
??? Redo Buffers ??????????????? 2973696 bytes
??? Database mounted .
??? Database opened . ???
?
??? SQL > create pfile = '$ORACLE_HOME/dbs/spfileorcl.ora.bak' from spfile ;
?
??? File created . ?????? ? -- 備份 spfile ????? ? ?????????????
?
更多
?
?
?
?
? Oracle 聯機重做日志文件(ONLINE LOG FILE)
?
?
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
