ORA-00119:无效规范ORA-00132:语法错误



以下是我的SQLPLUS启动、LSNRCTRL SERVICES、LSNRCTRLSTATUS和TNSPING的输出。还包括我的LISTENER。ORA文件,TNSNAMES。ORA和我的PFILE。

从SQLPLUS启动输出中可以看到,数据库将不会启动,因为init.ora文件local_listener设置为listener,并且它表示无法解析该名称。然而,LSNCTRL状态显示LISTENER的名称为LISTENER,所以我不确定LSNCTRL实用程序是如何看到LISTENER和SQLPLUS不能看到的。我的猜测是,我错过了一些东西,我确信它是显而易见的。任何帮助都将不胜感激!

ORACLE_HOME=C:appproduct12.1.0dbhome_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:appproduct12.1.0dbhome_1networkadminlistener.ora
Listener Log File         C:appdiagtnslsnrMWKS137477listeneralertlog.xml
Listener Trace File       C:appdiagtnslsnrMWKS137477listenertraceora_9096_8848.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
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:appproduct12.1.0dbhome_1networkadminsqlnet.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:appproduct12.1.0dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:appproduct12.1.0dbhome_1binoraclr12.dll")
  )    
     (SID_DESC =
       (GLOBAL_DBNAME = EDMP1)
       (ORACLE_HOME = C:apporacleproduct11.2.0dbhome_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:appadminEDMP1adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='C:apporadataEDMP1control01.ctl','C:apporadataEDMP1control02.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'
================================================================================

尝试以下过程。它对我有效。

C:UsersHp>sqlplus SYSTEM as SYSDBA   
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 3 12:28:32 2016    
Copyright (c) 1982, 2010, Oracle.  All rights reserved.    
Enter password:    
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_ORCL'    
SQL> create pfile from spfile;    
File created.    
**************************    
Now open the PFILE (INIT%ORACLE_SID%.ORA) from %ORACLE_HOME%database
Search for string local_listener='LISTENER_ORCL' and remove it and save the file.     
**************************    
SQL> startup nomount pfile='C:appHpproduct11.2.0dbhome_1databaseINITorcl.ORA'    
ORACLE instance started.    
Total System Global Area 1686925312 bytes
Fixed Size                  2176368 bytes
Variable Size            1291848336 bytes
Database Buffers          385875968 bytes
Redo Buffers                7024640 bytes    
SQL> create spfile from pfile;   
File created.    
SQL> startup    
ORA-01081: cannot start already-running ORACLE - shut it down first    
SQL> shutdown    
ORA-01507: database not mounted    
ORACLE instance shut down.    
SQL> startup    
ORACLE instance started.    
Total System Global Area 1686925312 bytes
Fixed Size                  2176368 bytes
Variable Size            1291848336 bytes
Database Buffers          385875968 bytes
Redo Buffers                7024640 bytes
Database mounted.
Database opened.    
SQL>

您的初始化参数正在查找别名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))'
 (SID_DESC =
   (GLOBAL_DBNAME = EDMP1)
   (ORACLE_HOME = C:apporacleproduct11.2.0dbhome_1)
   (SID_NAME = EDMP1
C:appproduct12.1.0dbhome_1networkadminsqlnet.ora
C:appproduct12.1.0dbhome_1networkadminlistener.ora
  • ORACLE_HOME-您的ORACLE_HHOME环境变量设置不正确。您有多个ORACLE_HOME,一个指向11.2.0,另一个指向12.1.0

  • LOCAL LISTENER-更重要的是,正确设置LOCAL LISTENER。

  • STATIC注册-还有一件事,为什么要使用STATIC侦听器注册?移除SID_LIST_LISTENER。它使用静态注册,这就是您将STATUS视为UNKNOWN服务的原因。让它动态注册。

  • PLUGABLE DATABASE-如果您在12c上,并且已将其创建为PLUGABLe DATABASE,则必须在tnsnames.ora文件中包含PDB详细信息。否则,在执行SQLPLUS / AS SYSDBA时,默认情况下会连接到CDB

请阅读Oracle 12c安装后强制步骤

也有类似的问题。在我的案例中,它是通过更改主机名来解决的。

发现local_listener变量是根据随机启动的主机名计算的,所以值不正确。

将其更改为/etc/sysconfig/network,并将其设置为hostname myshost.mydomain。没有编辑/etc/hosts,因为我已经设置了dns,但否则可能是必要的。

相关内容

  • 没有找到相关文章

最新更新