--========================================

-- 又一例 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 ????? ? ?????????????

?

更多

SPFILE 錯誤導致數據庫無法啟動

?

Managing Archived Redo Logs

?

Oracle 用戶、對象權限、系統權限

?

Oracle 角色、配置文件

?

? Oracle 聯機重做日志文件(ONLINE LOG FILE)

?

? Oracle 控制文件(CONTROLFILE)

?

? Oracle 表空間與數據文件

?

Oracle 歸檔日 志

?

又一例SPFILE設置錯誤導致數據庫無法啟動