AboutPeter Choi Expertise I am a senior Oracle DBA, PeopleSoft Administrator and Project Manager with
10+ years experience. I have been working with PeopleSoft (HRMS 5, 7, 7.x and
8.9), Oracle RDBMS (7.3 - 11gR1) on various Unix and Windows platforms, and some Oracle Application Server (9i/10gR2). I also have experience with the configuration and administration of BEA`s Tuxedo and WebLogic for PeopleSoft 8.x.
Question QUESTION: Hi.
I have AIX Server 5.3 and installed Oracle 10g (10.2).
One DataBase (DB1) already working under oracle user. I
have no problem with it.
I created second user oracle2 and installed second copy of
oracle 10g (10.2) RDBMS for test purpose and already
created a second Database DB2. I can start DB2.
Now I have a little problem.
I do not why but when I start LISTENER (lsnrctl) under
oracle user (to start DB1) DB2 is registered in this
LISTENER:
Listener Parameter File
/u01/oracle/product/10.1.0/network/admin/listener.ora
Listener Log File
/u01/oracle/product/10.1.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=15
21)))
Services Summary...
Service "DB1" has 1 instance(s).
Instance "DB1", status READY, has 1 handler(s) for this
service...
Service "DB1XDB" has 1 instance(s).
Instance "DB1", status READY, has 1 handler(s) for this
service...
Service "DB2" has 1 instance(s).
Instance "DB2", status READY, has 1 handler(s) for this
service...
Service "DB2XDB" has 1 instance(s).
Instance "DB2", status READY, has 1 handler(s) for this
service...
The command completed successfully
I need two separated LISTENERs and ports for two databases.
The suggestion was to execute this command under oracle2
(DB2) user.
SQL> ALTER SYSTEM SET
LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST =
hostname)(PORT =1522))" SCOPE=SPFILE;
But what about DB2 registartion in the oracle LISTENER ?
Thanks in advance.
ANSWER: Hi Magerram,
1. You need to make sure that you have defined 2 separate Oracle Homes. If user oracle1's $ORACLE_HOME (Unix environment) variable is defined as /u01/oracle/product/10.1.0/, then your oracle2's $ORACLE_HOME (Unix environment) variable should be defined as /u01/oracle2/product/10.1.0/.
In Unix, to see waht the variable is set to, type:
prompt> set
You will see a list of unix environment variables. Look for ORACLE_HOME. If you don't see one, then it has not been set.
2. I suspect that you've installed the Oracle software (or binaries) in two physical location. The two oracle users, oracle and oracle2 are in the same group. Thus when you use the same ORACLE_HOME to configure your network services, they would default to the first installation under the oracle user.
3. To have separate listeners, you need to disassociate the DB2 (second database instance) from your network configuration in
ALTER SYSTEM SET
LOCAL_LISTENER=”(ADDRESS=(PROTOCOL=TCP)(HOST=ip_address)(PO
RT=1523))” SCOPE=SPFILE - helped me.
Then I just configured listener.ora and tnsnames.ora files
with appropriate parameters: different port number,
different SID, same host ip address, and also different
EXTPROC. Actually I do not know what is it for (EXTPROC)
but I made them different, this parameter is vary in both
listenerss configuration files (EXTPROC0 and EXTPROC1) - is
this correct?
After that the second instance started successfully with no
listener error. Thanks.
But ...
First Oracle RDBMS - /u01/app/oracle/product/10.2.0/db_1
Second Oracle RDBMS - /u02/app/oracle/product/10.2.0/db_2
Is that an error ?
I mean, I installed two RDBMS on different drives for
performance purpose.
The names are different to avoid a mistake in configuration
files.
But anyway I do not how can I unregister second database
instance from first listener.
You send me very useful links, but I am not an expert in
Oracle, that's why I run into doubts.
Is it possible to use this command for first Oracle RDBMS:
ALTER SYSTEM RESET LOCAL_LISTENER SCOPE=SPFILE SID='*';
And after just register:
ALTER SYSTEM SET
LOCAL_LISTENER=”(ADDRESS=(PROTOCOL=TCP)(HOST=ip_address)(PO
RT=1521))” SCOPE=SPFILE
Can this solution help me ?
Thanks for your help.
Answer Hi Magerram,
By using the ALTER SYSTEM SET LOCAL_LISTENER, you are forcing that database to use the specified listener. If that specified listener is not available or running, it would default to the original listener.
It is a work around and yes it can and if done correctly it will work.
There is an Oracle tool called Oracle Net Manager (On UNIX, run netmgr from $ORACLE_HOME/bin) that you can use to configure your listener.
A question that I have, where are your two databases installed?
You had mentioned you had installed 2 oracle homes (installing the binaries twice) for performance gain. That's not quite correct. You need to install the software once. However, the databases needs to be created on different mount points so as to minimize I/O between your databases.
Which would provide a good reference for administering your database.
So in the short term, yes the solution can work. However your recovery solution may not be so straight forward (as your configuration is customized and not based on standards).
