Unsetting Parameters in the spfile (was spfile/listener question)
- From: Feighery Raymond <Raymond.Feighery@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 29 Jun 2004 12:40:45 +0100
Thanks for the correction.
Interestingly, I tested this using an spfile. When I came to unset the
service_names in the spfile (that is to return it to its default value), the
documented way did not seem the right way to go about it.
Oracle9i Database Administrator's Guide Release 2 (9.2) states:
"Deleting Initialization Parameter Values
For initialization parameters whose values are string values you can restore
a parameter to its default value (effectively deleting it), by using the
following syntax:
ALTER SYSTEM SET parameter = '';
For numeric and boolean value parameters, you must specifically set the
parameter back to its original default value."
This seems like a nasty workaround to me. You have to hardcode values to
their default? What if the default value is dynamically based on other
parameters which subsequently change? It also shows up as a non-default
system parameter.
Using a pfile, it's trivial; you can just delete the parameter.
One clunky workaround would be to go spfile -> pfile -> delete parameter ->
spfile.
The solution seems to be to use the reset parameter of alter system i.e.
alter system reset service_names scope=spfile sid='*';
I think you have to use the sid= option as this command is designed for RAC.
Ray
-----Original Message-----
From: Graeme Farmer [mailto:graeme.farmer@xxxxxxxxxx]
Sent: Tuesday, June 29, 2004 11:40 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: spfile/listener question
It is not essential to have a listener.ora, provided you want to have the
listener run on the default port (1521) it will start up without a config
file.
HOWEVER, that would NOT be a good idea as changing from the default port is
mandatory from a security perspective, as is setting a password, admin
restrictions, etc, etc...
When using a non-default port, then automatic listener registration is only
possible via the LOCAL_LISTENER parameter in (s)pfile. This can be set to a
TNS alias that resolves to the correct host/port combination or you can
enter a connect descriptor; and, as Ray pointed out, the listener needs to
be started prior to any registration or indeed listening taking place!
Cheers,
gf
-----Original Message-----
From: Feighery Raymond [mailto:Raymond.Feighery@xxxxxxxxxxxxx]
Sent: Tuesday, 29 June 2004 6:14 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: spfile/listener question
You haven't started the listener.
Dynamic registration via the init.ora file means you don't have to modify
the listener.ora for each new database entry (e.g. you don't have to add an
extra SID_LIST entry). However, you still have to define the LISTENER in the
listener.ora for the listener itself and start it.
Ray
-----Original Message-----
From: Scott.Shafer@xxxxxxxxxxxxxxxxxx
[mailto:Scott.Shafer@xxxxxxxxxxxxxxxxxx]
Sent: Monday, June 28, 2004 6:31 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: spfile/listener question
Odd question time:
RTFM'd until we're blue, but we can't get the listener services to register
when specified in the init.ora vs a listener.ora file. HR Apps 11.5.7 on
Oracle 9.2.0.5, HPUX 11.11.
We're trying to set the listener in the init.ora/spfile using the
parameters:
LOCAL_LISTENER=db1
SERVICE_NAMES=db1.us.acme.com
INSTANCE_NAME=db1
tnsnames.ora entry looks like:
db1= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1421)))
As SYSDBA we've done the:
ALTER SYSTEM SET LOCAL_LISTENER='db1'
When we check the listener via lsnrctl we can not detect the services.
$TNS_ADMIN is set correctly.
LSNRCTL> set current_listener db1
LSNRCTL> services
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1421))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused
Anyone out there gone through this exercise successfully?
Thanks,
Scott
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
___________________________________________________________________________
This email and any attached to it are confidential and intended only for the
individual or entity to which it is addressed. If you are not the intended
recipient, please let us know by telephoning or emailing the sender. You
should also delete the email and any attachment from your systems and should
not copy the email or any attachment or disclose their content to any other
person or entity. The views expressed here are not necessarily those of
Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you.
Churchill Insurance Group plc. Company Registration Number - 2280426.
England.
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1
1DP.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
--
This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please notify
the sender and delete the transmission. The contents of this e-mail are the
opinion of the writer only and are not endorsed by the Mincom Group of
companies unless expressly stated otherwise.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
___________________________________________________________________________
This email and any attached to it are confidential and intended only for the
individual or entity to which it is addressed. If you are not the intended
recipient, please let us know by telephoning or emailing the sender. You
should also delete the email and any attachment from your systems and should
not copy the email or any attachment or disclose their content to any other
person or entity. The views expressed here are not necessarily those of
Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you.
Churchill Insurance Group plc. Company Registration Number - 2280426.
England.
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1
1DP.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » Unsetting Parameters in the spfile (was spfile/listener question)
