IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I got ” ORA-00119: invalid specification for system parameter LOCAL_LISTENER ” error in Oracle database.
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
Details of error are as follows.
ORA-00119: invalid specification for system parameter %s Cause: The syntax for the specified parameter is incorrect. Action: Refer to the Oracle Reference Manual for the correct syntax
invalid specification for system parameter
This RMAN-05001 error is related with the invalid specification for system parameter.
Refer to the Oracle Reference Manual for the correct syntax.
Can’t startup a database through remote connection
Tnsping works fine.
[oracle@dmb1dbadm01 ~]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 16-JAN-2014 11:11:47
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL_SERV) (INSTANCE_NAME = ORCL)))
[oracle@myhost ~]$ sqlplus sys/password@ORCL as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 16 11:11:33 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-00119: invalid specification for system parameter LISTENER_NETWORKS
ORA-00137: invalid LISTENER_NETWORKS specification with NAME=NET1
ORA-00138: all addresses specified for attribute LOCAL_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'LOCAL_1'
ORA-00132: syntax error or unresolved network name 'REMOTE_1'
ORA-00132: syntax error or unresolved network name 'LOCAL_2'
ORA-00132: syntax error or unresolved network name 'REMOTE_2'
ORA-00132: syntax error or unresolved network name 'LOCAL_3'
ORA-00132: syntax error or unresolved network name 'REMOTE_3'
ORA-00132: syntax error or unresolved network name 'LOCAL_4'
ORA-00132: syntax error or unresolved network name 'REMOTE_4'
SQL> exit
--> All TNS alias are resolved
SQL> show parameter listener_networks;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string ((NAME=NET1)(LOCAL_LISTENER=
LOCAL_1)(REMOTE_LISTENER=REMOTE_
1)), ((NAME=NET2)(LOCAL_LISTEN
ER=LOCAL_2)(REMOTE_LISTENER=RE
MOTE_2)), ((N)(NAME=NET3)(LOCAL
_LISTENER=LOCAL_3)(REMOTE_LIS
TENER=REMOTE_3)), ((NAME=NET4
)(LOCAL_LISTENER=LOCAL_4)(R
EMOTE_LISTENER=REMOTE_4))
[oracle@myhost ~]$ srvctl start database -d ORCL --> works fine
The TNSNAMES.ORA file containing the TNS connect strings referenced in LISTENER_NETWORKS must be placed in both GRID_HOME/network/admin and RDBMS_HOME/network/admin.
During an attempt to start the database, the following errors are generated and the startup operation fails:
ORA-00119: invalid specification for system parameter LOCAL_LISTENER ORA-00132: syntax error or unresolved network name '%s'
The same error might be thrown for the system parameter LISTENER_NETWORKS:
ORA-00132: syntax error or unresolved network name ‘%s’
See also: Note: 1615175.1 ORA-00119: Invalid Specification for System Parameter LISTENER_NETWORKS During Database Startup
If the sqlnet.ora file parameter NAMES.DEFAULT_DOMAIN has been changed recently, the change may have caused this error.
The value for LOCAL_LISTENER must be resolvable by PMON just like any other tnsnames alias.
If the NAMES.DEFAULT_DOMAIN parameter has been altered since the LOCAL_LISTENER parameter was set, the entry may no longer be resolvable.
PMON will not allow the database to startup if LOCAL_LISTENER points to a tnsnames entry that cannot be resolved.
For example. If LOCAL_LISTENER was set like this:
LOCAL_LISTENER = ‘listener10i’
This tns alias would be resolvable via the local tnsnames.ora file:
listener10i = (ADDRESS= (PROTOCOL=tcp) (HOST=<hostname>) (PORT=<port #>))
Running “tnsping listener10i” at the command line would yield an OK indicating this entry was resolvable.
Now if the following parameter was added to the sqlnet.ora file, the tns alias would no longer be resolvable:
NAMES.DEFAULT_DOMAIN=world
A tnsping listener10i at the command line would now yield a TNS-3505: Failed to resolve name.
PMON will not startup the database with an unresolvable entry for LOCAL_LISTENER
See Note 114085.1 for additional information on the NAMES.DEFAULT_DOMAIN parameter and its effect on the tnsnames.ora file entries.
Make sure that the Net Service Name (alias) used for the LOCAL_LISTENER or LISTENER_NETWORKS is resolving.
In the example above, you would add the value of NAMES.DEFAULT_DOMAIN to the alias in the tnsnames.ora.
In this example, the domain is set to “world” as in:
sqlnet.ora:
NAMES.DEFAULT_DOMAIN=world
The pfile parameter LOCAL_LISTENER still points to “listener10i” as in:
init.ora:
LOCAL_LISTENER=listener10i
So in order to resolve “listener10i” it is necessary to add the domain “world” to the tnsnames.ora file entry as in:
Other options include:
a) Removing the NAMES.DEFAULT_DOMAIN parameter from the sqlnet.ora file and leaving the tnsnames.ora file entry set to just “listener10i” OR
b) Setting the LOCAL_LISTENER to the full address which eliminates the need to resolve via the tnsnames.ora file, making it more stable and effective.
For example, log into the database with dba privileges and issue:
alter system set LOCAL_LISTENER=”(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<port #>))” scope=both;
* Option b is recommended as changes externally to TNS files, will not affect the Database Startup or functions that need the Database parameters such as LOCAL_LISTENER.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
