create pfile時報ORA-27037問題的處理

       在客戶那接手一個資料庫問題,啟動資料庫時報以下錯誤

[root@p570a2:/crs/app/oracle/product/crs_1/bin]#su - oracle
[oracle@p570a2:/oracle/app/oracle]$sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 5 11:53:42 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENERS_FSYYK1'

檢查tnsnames.ora檔案發現少了LISTENERS_FSYYK1 配置,而這個配置因為由雙機改為單機不需要新增,必須從引數檔案中手工去掉LOCAL_LISTENER引數

由於這個用的asm資料庫,引數也放在asm儲存中,create pfile from spfile時,報以下錯誤

SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

由於這個時候資料庫無法開啟,不知道是用spfile啟動還是pfile啟動

所以只能試先從pfile建立成spfile,再從asm位置建立成pfile,然後進行修改

[oracle@p570a2:/oracle/app/oracle]$sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 5 11:57:38 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENERS_FSYYK1'

SQL> create pfile=/tmp/pfile20090105.ora from spfile=+DGDATA/fsyyk/spfilefsyyk.ora;
create pfile=/tmp/pfile20090105.ora from spfile
             *
ERROR at line 1:
ORA-02236: invalid file name

上面這個問題是pfile路徑要加單引號

SQL> create pfile='/tmp/pfile20090105.ora' from spfile='+DGDATA/fsyyk/spfilefsyyk.ora';

File created.

SQL>

然後vi /tmp/pfile20090105.ora 引數檔案,去掉local_listener引數

再建立asm儲存的spfile

SQL> create spfile='+DGDATA/fsyyk/spfilefsyyk.ora'  from  pfile='/tmp/pfile20090105.ora' ;

File created.

SQL> startup

啟動資料庫成功

總結:

如果看到資料庫啟動時報以下錯誤,則可以知道是spfile啟動的

SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENERS_FSYYK1'

如果看到資料庫啟動時報以下錯誤,則可以知道是pfile啟動的,即多了ORA-01078錯誤

SQL> startup
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENERS_FSYYK1'