Home » Other » General » invalid specification for system parameter LOCAL_LISTENER (Oracle dB 12c, oracle linux 7)
invalid specification for system parameter LOCAL_LISTENER [message #656289] Sun, 02 October 2016 03:50 Go to next message
Janning
Messages: 43
Registered: April 2016
Member

Hi,

Let me start by saying, I'm not really a beginner at all this, but by no means an expert. I have Oracle 12c enterprise set up on a Oracle Enterprise Linux ver 7 box.
This has always worked fine, start Linux, from a terminal window
[oracle@oradev ~]$ sqlplus /nologSQL> conn SYS as SYSDBA
Enter password: xxxxx
startup
lsnrctl start

All was well. I have this running on VMware® Workstation 12 Pro, 12.5.0 build-4352439. I recently updated VMware...now when I get to
startup I get:
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=oradev.attlocal.net)(PORT=1522))'
SQL>

I believe my host name in oracle Linux (which is on VM ware, so maybe is in VM ware setting, this all started after updating VM ware)
is not the same, somehow is in my listener.ora ? It looks ok below to me, but as I said, I'm not expert level

I've taken many notes and screen shots, which can be seen at

http://www.aanning.com/ajissues/oracle_startup_faq/

Please advise?
James
Re: invalid specification for system parameter LOCAL_LISTENER [message #656290 is a reply to message #656289] Sun, 02 October 2016 05:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Create a pfile from the spfile, edit it to remove the local_listener parameter, re-create the spfile from the modified pfile, startup the instance. Take it from there.
Re: invalid specification for system parameter LOCAL_LISTENER [message #656291 is a reply to message #656290] Sun, 02 October 2016 06:03 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

Is this to address a possible corrupt SPRILE?
I don't know how to create a pfile from the spfile..then you use the to create the file it came from?
Keep in mind a few things. I'm new to this, I'm an oracle developer with little DBA type experience. Also, this dB was working for months, the VMWare update killed it...I'm guessing because
I made no changes to oracle
From what I googled Ill need the database running to "SQL> create pfile from spfile;" I can not start the dB, trying to start is when the error occurs.
will I loose any of my settings? I must keep: HOST at "oradev.attlocal.net") and the listening port at 1522.
Re: invalid specification for system parameter LOCAL_LISTENER [message #656292 is a reply to message #656291] Sun, 02 October 2016 06:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Is this to address a possible corrupt SPRILE?
No. It is to remove the parameter that you have set incorrectly.

Quote:
I don't know how to create a pfile from the spfile..then you use the to create the file it came from?
You need to look up the command CREATE SPFILE...from PFILE... It is in the docs. Or take a basic DBA course.

Quote:
I'm new to this
You said "I'm not really a beginner at all this" Smile

Quote:
From what I googled Ill need the database running
Where do you read that rubbish? If you don't believe me, you could just try it, you know.

--update: mis-read something, corrected

[Updated on: Sun, 02 October 2016 06:13]

Report message to a moderator

Re: invalid specification for system parameter LOCAL_LISTENER [message #656293 is a reply to message #656292] Sun, 02 October 2016 06:16 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

I'm beginner to the point I don't know the basics, that is true...I'm a developer, not a DBA, so you know how that is..I only know enough to be dangerous.
Its not something I need to do alot, so its not likely I'll increase my level rapidly.

I googled "Create PFILE from SPFILE....the commands are from the SQL prompt, using PL/SQL...the database has to be running for that....no?
Re: invalid specification for system parameter LOCAL_LISTENER [message #656294 is a reply to message #656293] Sun, 02 October 2016 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No... and you don't use PL/SQL for this.

[Updated on: Sun, 02 October 2016 06:18]

Report message to a moderator

Re: invalid specification for system parameter LOCAL_LISTENER [message #656295 is a reply to message #656294] Sun, 02 October 2016 06:20 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

The spfile is created using the CREATE SPFILE statement; this requires connecting as SYSDBA.

Connect system/manager as sysdba;

CREATE SPFILE FROM PFILE;

The three hits in google, I came up with...say the above, ill continue looking for an alternative method
Re: invalid specification for system parameter LOCAL_LISTENER [message #656296 is a reply to message #656295] Sun, 02 October 2016 06:31 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Janning wrote on Sun, 02 October 2016 12:20
The spfile is created using the CREATE SPFILE statement; this requires connecting as SYSDBA.

Connect system/manager as sysdba;

CREATE SPFILE FROM PFILE;

The three hits in google, I came up with...say the above, ill continue looking for an alternative method
So you've found the answer three times with Google, as well as being told by me and MC, and you still don't believe it? This is why DBAs hate some developers! Those developers who just won't believe what they are told.
Re: invalid specification for system parameter LOCAL_LISTENER [message #656298 is a reply to message #656296] Sun, 02 October 2016 06:57 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

I got, it...its 7 am int the morning..I've been awake since 6 am friday...give me a break. I was confusing connection to an idle instance with a started dB. I told you, I only do something like this twice a year or so I do not have it memorized.
Using
SQL> CREATE PFILE FROM SPFILE
2 /

File created.

however, i can not locate PFILE, is there anywhere special I should create it and.or look for it once created/

Re: invalid specification for system parameter LOCAL_LISTENER [message #656299 is a reply to message #656298] Sun, 02 October 2016 07:00 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
THe other sort of developer that DBAs hate are te ones who won;t read documentation. I did suggest that you do that. Here is the link,
http://docs.oracle.com/database/121/SQLRF/statements_6009.htm
Re: invalid specification for system parameter LOCAL_LISTENER [message #656300 is a reply to message #656299] Sun, 02 October 2016 07:06 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

The sort of DBA deveolpers hate are the ones that expect us to "become a DBA" in an effort to do one little thing. I didn't get on here to debate, or demean someone...but, if it makes you feel better, I'll pay that price.
I've seen that doc, Its not very clear, but, thinking about it (that should make you happy), I'm seeing PFILE is not a "file" it creates a file I assume the init.ora
Re: invalid specification for system parameter LOCAL_LISTENER [message #656301 is a reply to message #656300] Sun, 02 October 2016 07:13 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The idea of this bulletin board is to help people develop their skills, not to spoonfeed answers. However, I see from your topic on OTN that you have been stuck on this for a while, and we do have a guideline to provide complete answers if people are in a hurry on a production issue. So here you are:
C:\Users\john>
C:\Users\john>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 2 13:09:49 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orclz> create pfile='c:\tmp\initorclz.ora' from spfile='%ORACLE_HOME%\database\spfileorclz.ora';

File created.

orclz>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\Users\john>dir /tmp/init*
Parameter format not correct - "mp".

C:\Users\john>
C:\Users\john>dir \tmp\init*
 Volume in drive C is OS
 Volume Serial Number is 4454-E531

 Directory of C:\tmp

02/10/2016  13:10             1,173 initorclz.ora
               1 File(s)          1,173 bytes
               0 Dir(s)  142,348,374,016 bytes free

C:\Users\john>

[Updated on: Sun, 02 October 2016 07:16]

Report message to a moderator

Re: invalid specification for system parameter LOCAL_LISTENER [message #656303 is a reply to message #656301] Sun, 02 October 2016 07:56 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

Ok, I used:
Create PFILE = '/home/oracle/APPS/pfile/init.ora' from SPFILE = '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileorcl.ora'
I know have a init.ora file, in the above dir
Removing "*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=oradev.attlocal.net)(PORT=1522))'" from the new init.ora file, I assume I can now take this and copy
the contents into my spfileorcl.ora file located in /u01/app/oracle/product/12.1.0/dbhome_1/dbs ?
Then restart
Re: invalid specification for system parameter LOCAL_LISTENER [message #656304 is a reply to message #656303] Sun, 02 October 2016 08:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> STARTUP PFILE='/home/oracle/APPS/pfile/init.ora'
Re: invalid specification for system parameter LOCAL_LISTENER [message #656305 is a reply to message #656304] Sun, 02 October 2016 08:25 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

Ok, it did start up..I see a new file initorcl.ora, which does have:
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=oradev.attlocal.net)(PORT=1522))'

I shutdown, and tried using just "startup" as I had for months, I get the same old error, so I'm lost here
Re: invalid specification for system parameter LOCAL_LISTENER [message #656306 is a reply to message #656305] Sun, 02 October 2016 10:35 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Janning wrote on Sun, 02 October 2016 14:25
Ok, it did start up..I see a new file initorcl.ora, which does have:
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=oradev.attlocal.net)(PORT=1522))'

I shutdown, and tried using just "startup" as I had for months, I get the same old error, so I'm lost here
Quote:
Create a pfile from the spfile, edit it to remove the local_listener parameter, re-create the spfile from the modified pfile, startup the instance. Take it from there.
Re: invalid specification for system parameter LOCAL_LISTENER [message #656309 is a reply to message #656306] Sun, 02 October 2016 19:01 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

Ok, so are you saying I now need to recreat the spfile, from the modified pfile, int eh start up instance.

The should be?

Create SPFILE = '/home/oracle/APPS/spfile/spfileorcl.ora' from PFILE = '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initorcl.ora'
or should it be from here?
Create SPFILE = '/home/oracle/APPS/spfile/spfileorcl.ora' from PFILE = '/home/oracle/APPS/pfile/init.ora';

then take one of the above, and move spfileorcl.ora to /u01/app/oracle/product/12.1.0/dbhome_1/dbs
Re: invalid specification for system parameter LOCAL_LISTENER [message #656310 is a reply to message #656309] Sun, 02 October 2016 21:27 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

Update:
I used the:
Create SPFILE = '/home/oracle/APPS/spfile/spfileorcl.ora' from PFILE = '/home/oracle/APPS/pfile/init.ora';
rebooted linux and from a terminal:

sqlplus /nolog
conn SYS as SYSDBA
startup

It started up just like she used too! this is good!!

Next I started the listener, and get the error below. The current contents of all my .ora files and

[oracle@oradev ~]$ lsnrctl status
[oracle@oradev ~]$ lsnrctl service

are at:

http://www.aanning.com/ajissues/oracle_startup_faq/notes_Or_sp.txt



------------error below---------------------

lsnrctl start

[oracle@oradev ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-OCT-2016 19:19:51

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

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oradev/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradev.attlocal.net)(PORT=1522)))
TNS-12545: Connect failed because target host or object does not exist
TNS-12560: TNS:protocol adapter error
TNS-00515: Connect failed because target host or object does not exist


Listener failed to start. See the error message(s) above...

[oracle@oradev ~]$
Re: invalid specification for system parameter LOCAL_LISTENER [message #656314 is a reply to message #656310] Mon, 03 October 2016 02:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The next step....
Does oradev.attlocal.net resolve to an IP address running on your database server? Don't just say "yes", you have to prove it, because Uncle thinks "no".

Please use [code] tags when copy/pasting, described here How to use [code] tags and make your code easier to read
Re: invalid specification for system parameter LOCAL_LISTENER [message #656336 is a reply to message #656314] Mon, 03 October 2016 08:22 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

I can not ping it from a terminal window....no
Re: invalid specification for system parameter LOCAL_LISTENER [message #656337 is a reply to message #656336] Mon, 03 October 2016 09:50 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Janning wrote on Mon, 03 October 2016 13:22
I can not ping it from a terminal window....no
Did you can not ping from what to what?

As John suggestion, you might review your hosts file in /etc/hosts. If you have got a root account, you can edit this file, if you have not, you probably review it by typing "$ more /etc/hosts". This is very simply. Otherwise you have not any idea to go to fix it.

Write some tips on your notepad file.

1.Your Linux OS hostname? Check by typing "$ hostname". Does the VM server use IPv6? Does the VM server use loopback 127.0.0.1? Does the VM can resolve the hostname?
2.Your Virtualization Engine Application (VEA)? Oracle Virtual, VMWare, NetApp, ..etc? Check and confirm it. Does it bring Bridge Connection or use physical NIC (Network Interface Card). If one of VEA used physical NIC, did the System Admin configure or by pass MAC Address by VEA network utility? In general case, the System Admin use -Bridge Connection to configure for Developer deploying.
3.Your Linux OS configuration with SELinux? Check by typing "$ more /etc/selinux"
4.Your Linux OS configuration with firewall? Check by typing "$ service iptables status"
5.Your result when testing by "$ tnsping your_alias_tnsnames_of_Oracle_database". Verify the alias by "$ cat $ORACLE_HOME/network/admin/tnsnames.ora".
6.If you use jdbc*thin to connect directly to your Oracle Database, verify it by using SQL*Developer utility.
7.Your listener.ora is configured by "oradev.attlocal.net", the hostname "oradev.attlocal.net" can be resolved by checking "ping oradev.attlocal.net"?
Hope this help.
TaT




[Updated on: Mon, 03 October 2016 09:53]

Report message to a moderator

Re: invalid specification for system parameter LOCAL_LISTENER [message #656338 is a reply to message #656337] Mon, 03 October 2016 10:24 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

1.Your Linux OS hostname? Check by typing "$ hostname"

my host name is oradev


2.Your Virtualization Engine Application (VEA)? Oracle Virtual, VMWare, NetApp, ..etc? Check and confirm it.
Does it bring Bridge Connection or use physical NIC (Network Interface Card).
If one of VEA used physical NIC, did the System Admin configure or by pass MAC Address by
VEA network utility? In general case, the System Admin use -Bridge Connection
to configure for Developer deploying.

Im VMWare Workstation...All my issues began when I updated the VM workstation
I have a feeling my issues is right here, in the VM NIC connection or the Linux NIC
The below shows the VM workstation is a bridged connection:

http://www.aanning.com/ajissues/oracle_startup_faq/VMWare_network_readd.jpg

The literal VM where oracle is on oracle linux 7.2
The NIC is as shown in jpgs NIC1, NIC2, NIC3 and NIC4.jpg

http://www.aanning.com/ajissues/oracle_startup_faq/


3.Your Linux OS configuration with SELinux? Check by typing "$ more /etc/selinux"

[oracle@oradev ~]$ more /etc/selinux
*** /etc/selinux: directory ***


4.Your Linux OS configuration with firewall? Check by typing "$ service iptables status"

The firewall should be off
[oracle@oradev ~]$ service iptables status
Redirecting to /bin/systemctl status iptables.service
● iptables.service
Loaded: not-found (Reason: No such file or directory)
Active: inactive (dead)


5.Your result when testing by "$ tnsping your_alias_tnsnames_of_Oracle_database".
Verify the alias by "$ cat $ORACLE_HOME/network/admin/tnsnames.ora".

tnsping oradev.attlocal.net

Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

cat $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradev.attlocal.net)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.attlocal.net)
)
)



6.If you use jdbc*thin to connect directly to your Oracle Database, verify it by using SQL*Developer utility.
Dont have this installed

7.Your listener.ora is configured by "oradev.attlocal.net", the hostname "oradev.attlocal.net" can be resolved
by checking "ping oradev.attlocal.net"?

[oracle@oradev ~]$ ping oradev.attlocal.net
ping: unknown host oradev.attlocal.net
[oracle@oradev ~]$
Re: invalid specification for system parameter LOCAL_LISTENER [message #656339 is a reply to message #656338] Mon, 03 October 2016 10:30 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

and the hosts file:

[oracle@oradev ~]$ more /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
[oracle@oradev ~]$
Re: invalid specification for system parameter LOCAL_LISTENER [message #656345 is a reply to message #656338] Mon, 03 October 2016 13:20 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I wonder of this could be a problem -

Quote:
[oracle@oradev ~]$ ping oradev.attlocal.net
ping: unknown host oradev.attlocal.net
[oracle@oradev ~]$
Re: invalid specification for system parameter LOCAL_LISTENER [message #656346 is a reply to message #656345] Mon, 03 October 2016 13:24 Go to previous messageGo to next message
Janning
Messages: 43
Registered: April 2016
Member

I thought "unknown host oradev.attlocal.net" .....is the problem? and that is what I am trying to correct.
This is what I have used for 9 months w no issues.

What else is odd, is in my router, this box was seen as "oradev"....now, it only see it as its IP address (for port forwarding)
Re: invalid specification for system parameter LOCAL_LISTENER [message #656360 is a reply to message #656346] Mon, 03 October 2016 22:54 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Janning wrote on Mon, 03 October 2016 18:24
I thought "unknown host oradev.attlocal.net" .....is the problem? and that is what I am trying to correct.
This is what I have used for 9 months w no issues.

Yes, it's the problem, exactly. The hostname is simply, name of host, such as my name, your name. Our name are identified by ID card, and the name of the host is identified by /etc/hosts. You did not define the hostname, you did not call it from anywhere.

More simple, imaging, you have plan to have got a weekend in fishing camp, when you go to the camp manager, you must take your ID to the manager who has known about you 9 months later.

Add the line as below:

$ vi /etc/hosts
# Internet host table
#
::1             localhost
127.0.0.1       localhost loghost

# Primary IPs
xx.xx.xx.xx     oradev.attlocal.net

In which, replace xx.xx.xx.xx with real IP. How to define real IP? Type: "# ifconfig -a"

Quote:

What else is odd, is in my router, this box was seen as "oradev"....now, it only see it as its IP address (for port forwarding)
"The box was seen", well, if I am not wrong, the VM is named as oradev.attlocal.net, it's belonged to VM management.

[Updated on: Mon, 03 October 2016 23:15]

Report message to a moderator

Re: invalid specification for system parameter LOCAL_LISTENER [message #656914 is a reply to message #656360] Sun, 23 October 2016 09:22 Go to previous messageGo to next message
learndba.com
Messages: 1
Registered: October 2016
Junior Member
Hi All,

I am seeing this issue and resolved it by below steps.

SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE)(PORT=1521))'
SQL>


Previous :

[root@ORACLE ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

[root@ORACLE ~]#


Added an entry in hosts :

[root@ORACLE ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.108.128 ORACLE localhost.com
[root@ORACLE ~]#

Then I am able to start the database server.

SQL> startup
ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2217384 bytes
Variable Size 532679256 bytes
Database Buffers 234881024 bytes
Redo Buffers 6868992 bytes
Database mounted.
Database opened.
SQL>

Re: invalid specification for system parameter LOCAL_LISTENER [message #656915 is a reply to message #656914] Sun, 23 October 2016 12:42 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
(HOST=ORACLE)
I strongly advise to NOT use ORACLE as host name as well any other keyword.



Previous Topic: SQL Text
Next Topic: Oracle development copies on Azure?
Goto Forum:
  


Current Time: Thu Mar 28 17:45:18 CDT 2024