--========================================
-- 又一例 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)
