[解決済み] ORA-00119： 無効な仕様 ORA-00132： 構文エラー

質問

以下は、SQLPLUS のスタートアップ、LSNRCTRL SERVICES、LSNRCTRL STATUS、TNSPING からの出力です。また、私の LISTENER.ORA ファイル、TNSNAMES.ORA と私の PFILE も含まれています。

SQLPLUSの起動出力からわかるように、init.oraファイルのlocal_listenerがLISTENERとして設定されており、その名前を解決できないというので、データベースは起動しません。しかし、LSNCTRLのステータスでは、LISTENERの名前はLISTENERになっているので、LSNCTRLユーティリティがLISTENERを見ていて、SQLPLUSが見れないというのはよくわからない。私の推測では、私は何かを見逃していると思いますが、それは明白で単純なことだと思います。どんな助けでも感謝します!

ORACLE_HOME=C:\app\product\12.1.0\dbhome_1

SQLPLUS STARTUP
================================================================================
sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 27 06:37:51 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

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

================================================================================

LSNRCTL SERVICES

================================================================================

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 27-FEB-2015 06:36:45

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "EDMP1" has 1 instance(s).
  Instance "EDMP1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:6 refused:0
         LOCAL SERVER
The command completed successfully

================================================================================

LSNRCTL STATUS

================================================================================

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 27-FEB-2015 06:36:59

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                27-FEB-2015 06:07:14
Uptime                    0 days 0 hr. 29 min. 49 sec
Trace Level               support
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\diag\tnslsnr\MWKS137477\listener\alert\log.xml
Listener Trace File       C:\app\diag\tnslsnr\MWKS137477\listener\trace\ora_9096_8848.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "EDMP1" has 1 instance(s).
  Instance "EDMP1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

================================================================================

TNSPING 

================================================================================

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 27-FEB-2015 06:37:31

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\product\12.1.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = EDMP1)))
OK (10 msec)

================================================================================

LISTENER.ORA

================================================================================
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\product\12.1.0\dbhome_1\bin\oraclr12.dll")
  )    
     (SID_DESC =
       (GLOBAL_DBNAME = EDMP1)
       (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
       (SID_NAME = EDMP1)
    )
  )
================================================================================

TNSNAMES.ORA

================================================================================
EDMP1.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = EDMP1)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
================================================================================

PFILE.ORA

================================================================================
edmp1.__data_transfer_cache_size=0
edmp1.__db_cache_size=1795162112
edmp1.__java_pool_size=16777216
edmp1.__large_pool_size=33554432
edmp1.__oracle_base='C:\app'#ORACLE_BASE set from environment
edmp1.__pga_aggregate_target=855638016
edmp1.__sga_target=2533359616
edmp1.__shared_io_pool_size=117440512
edmp1.__shared_pool_size=553648128
edmp1.__streams_pool_size=0
*.audit_file_dest='C:\app\admin\EDMP1\adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='C:\app\oradata\EDMP1\control01.ctl','C:\app\oradata\EDMP1\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='EDMP1'
*.diagnostic_dest='C:\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=EDMP1XDB)'
*.local_listener='LISTENER'
*.open_cursors=300
*.pga_aggregate_target=804m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2413m
*.undo_tablespace='UNDOTBS1'

================================================================================

解決方法は？

初期化パラメータはエイリアスを探しています。 LISTENER の中に存在することを示しました。 listener.ora しかし、データベースはそのファイルを見ず、読むことさえできないかもしれません。

エイリアスを指定する場合、そのエイリアスは tnsnames.ora ファイルに、一致する同じエントリーを追加してください。

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

あるいは、初期化パラメータにエイリアスではなく接続文字列を設定することで、接続文字列を参照する必要がなくなります。 tnsnames.ora :

*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))'