Saturday, 22 April 2017

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

There are two most important files in the ORACLE_HOME/network/admin directory which serves the requests for every connection getting established within Oracle Database.
  • tnsnames.ora
  • listener.ora
tnsnames.ora contains the details for Database Name, HostName, Connection Port and the associated Listener Information as well. Listener.ora contains the information regarding the Listener and the ORACLE_HOME to which it belongs. If any of the information mentioned in these files gets changed or are not in Sync, you will face trouble in getting connected to Oracle Database.

Below is one of the same kind of issues.

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>



To resolve the same, make sure you keep a look at tnsnames.ora and listener.ora files first and see if the values or properly mentioned or not. Although there are more than 90% chances that this will fix your issue but if it doesn't then here's and alternative:

C:\Users\Hp>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:\app\Hp\product\11.2.0\dbhome_1\database\INITorcl.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>

No comments:

Post a comment