--========================================
--又一例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 Aug3 10:38:25 2010
ALTER SYSTEM SET log_archive_dest='u01/app/oracle/archivelog1' SCOPE=SPFILE; --此处路径开始处少了"/"
Tue Aug3 10:39:59 2010
ALTER SYSTEM SET log_archive_duplex_dest='/u01/app/oracle/archivelog2' SCOPE=SPFILE;
Tue Aug3 10:40:25 2010
Incremental changes to log_archive_dest_1 not allowed with SPFILE
Tue Aug3 10:40:43 2010
ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/oracle/archivelog3' SCOPE=SPFILE;
Tue Aug3 10:40:57 2010
ALTER SYSTEM SET log_archive_dest_2='location=/u01/app/oracle/archivelog4' SCOPE=SPFILE;
Tue Aug3 10:41:02 2010
Starting background process EMN0
EMN0 started with pid=21, OS id=3944
Tue Aug3 10:41:02 2010
Shutting down instance: further logons disabled--实例开始关闭
Tue Aug3 10:41:03 2010
Stopping background process QMNC
Tue Aug3 10:41:04 2010
Stopping background process CJQ0
Tue Aug3 10:41:05 2010
Stopping background process MMNL
Tue Aug3 10:41:06 2010
Stopping background process MMON
Tue Aug3 10:41:07 2010
Shutting down instance (immediate)
License high water mark = 7
Tue Aug3 10:41:07 2010
Stopping Job queue slave processes
Tue Aug3 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 Aug3 10:41:12 2010
Job queue slave processes stopped
All dispatchers and shared servers shutdown
Tue Aug3 10:41:14 2010
ALTER DATABASE CLOSE NORMAL
Tue Aug3 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 Aug3 10:41:20 2010
ARCH shutting down
ARC1: Archival stopped
Tue Aug3 10:41:25 2010
ARCH shutting down
ARC0: Archival stopped
Tue Aug3 10:41:26 2010
Thread 1 closed at log sequence 46
Successful close of redo thread 1
Tue Aug3 10:41:26 2010
Completed: ALTER DATABASE CLOSE NORMAL
Tue Aug3 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 Aug3 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--后台进程被关闭
root379637640 10:28 pts/000:00:00 su - oracle
oracle379737960 10:29 pts/000:00:00 -bash
oracle382937970 10:29 pts/000:00:00 /usr/bin/perl -w /usr/bin/uniread sqlplus / as sysdba
oracle383038290 10:29 pts/100:00:00 sqlplusas sysdba
oracle394738301 10:41 ?00:00:02 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle395338300 10:43 pts/100:00:00 /bin/bash -c ps -ef | grep oracle
oracle395439530 10:43 pts/100: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 Area251658240 bytes
Fixed Size1218796 bytes
Variable Size79693588 bytes
Database Buffers167772160 bytes
Redo Buffers2973696 bytes
Database mounted.
Database opened.
SQL> show parameter pfile;
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
spfilestring/u01/app/oracle/10g/dbs/spfile
orcl.ora
SQL> show parameter spfile;
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
spfilestring/u01/app/oracle/10g/dbs/spfile
orcl.ora
SQL> desc v$spparameter
NameNull?Type
----------------------------------------- -------- ----------------------------
SIDVARCHAR2(80)
NAMEVARCHAR2(80)
VALUEVARCHAR2(255)
DISPLAY_VALUEVARCHAR2(255)
ISSPECIFIEDVARCHAR2(6)
ORDINALNUMBER
UPDATE_COMMENTVARCHAR2(255)
SQL> select distinct isspecified from v$spparameter;
ISSPEC
------
TRUE--第一行为true 可以知道数据库使用spfile参数启动
FALSE
SQL> show parameter log_archive--查看log_archive相关参数
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
log_archive_configstring
log_archive_deststring/u01/app/oracle/archivelog1/--该参数已显示正确路径
log_archive_dest_1string--该参数已经被初始化的pfile置空
log_archive_dest_10string
log_archive_dest_2string--该参数已经被初始化的pfile置空
log_archive_dest_3string
log_archive_dest_4string
log_archive_dest_5string
log_archive_dest_6string
log_archive_dest_7string
log_archive_dest_8 string
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9string
log_archive_dest_state_1stringenable
log_archive_dest_state_10stringenable
log_archive_dest_state_2stringENABLE
log_archive_dest_state_3stringenable
log_archive_dest_state_4stringenable
log_archive_dest_state_5stringenable
log_archive_dest_state_6stringenable
log_archive_dest_state_7stringenable
log_archive_dest_state_8stringenable
log_archive_dest_state_9stringenable
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_deststring/u01/app/oracle/archivelog2
log_archive_formatstringarc_%t_%s_%r.arc
log_archive_local_firstbooleanTRUE
log_archive_max_processesinteger2
log_archive_min_succeed_destinteger1
log_archive_startbooleanFALSE
log_archive_traceinteger0
--将出现错误的几个参数永久化修改到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 Area251658240 bytes
Fixed Size1218796 bytes
Variable Size79693588 bytes
Database Buffers167772160 bytes
Redo Buffers2973696 bytes
Database mounted.
Database opened.
SQL> create pfile = '$ORACLE_HOME/dbs/spfileorcl.ora.bak' from spfile;
File created.--备份spfile