又一例SPFILE設定錯誤導致資料庫無法啟動

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

--又一例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設定錯誤導致資料庫無法啟動

相關文章