Re: After Creating New Dtabase...

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 18 Jun 2004 14:48:43 +1000
Message-ID: <40d27414$0$6200$afc38c87@news.optusnet.com.au>

"Arijit Chatterjee" <arijitchatterjee123_at_yahoo.co.in> wrote in message news:ea01504d.0406172016.34ae77b9_at_posting.google.com...
> Thanks Howard
> Thanks Once Again.But the problem still there. When I am typing
> system/password_at_seconddb
> getting
>
> ORA-12541: TNS:no listener

OK. Follow it through. The alias "seconddb" resolves to port 1523. Your listener.ora says that LISTENER1 should be running on port 1523. So that's a 'good' path, and should work. The only reason why it can't work is that the listener on that port hasn't actually been started yet. Why not? We'll come back to that one, but it would require the command "lsnrctl start listener1" not just "lsnrctl start" (which only normally starts a listener with a default name of LISTENER).

>
> And
>
> system/password_at_FirstDb
> getting
>
> ORA-01034: ORACLE not available
> ORA-27101: shared memory realm does not exist

That error message almost always means you haven't actually started the service that provides the memory needed by the instance.

So now, let's just step back a bit and ask the real question: why on Earth are you creating two listeners on the one machine? It's not a good idea, untless you are trying to load balance on a multi-CPU box, and even then it's a bit over the top. A listener running on a non-default port means that instances won't be registered automatically and dynamically unless you go through further configuration issues with local_listener in your tnsnames. Which isn't hard to do if you know what you're doing, but it's an unnecessary complication. It's also the case that managing the listener in this configuration will always involve having to specify the listener name, because only one of them can use the default name of 'LISTENER'. It's just a pain. So don't do it.

Your listener.ora should therefore read:

LISTENER =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = <HostName>)(PORT = 1521))     )

And once that's edited and saved, type:

lsnrctl stop
lsnrctl start

Your tnsnames.ora would therefore read:

 FirstDb =
   (DESCRIPTION =

     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = <HostName>)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = FirstDb)
     )

   )

 SecondDb =
   (DESCRIPTION =

     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = <HostName>)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = SecondDb)
     )

   )

And with that saved, you need to make sure both services are started before going any further. Do that in the Control Panel - Services applett, or at the command line:

net stop OracleServiceFirstDB
net start OracleServiceFirstDB
net stop OracleServiceSecondDB
net start OracleServiceSecondDB

(Or whatever the exact names of the services are).

After that, you can (or should be able to) then do:

C:\> set ORACLE_SID=FirstDB
C:\> sqlplus "/ as sysdba"
SQL> select name from v$database; (you'll see FIRSTDB returned)
SQL> exit
C:\> sqlplus "/@SecondDB as sysdba"
SQL> select name from v$database;

ORACLE_SID provides the default so that non-explicit connections go to it, and the '@XXX' connection syntax is used to specify the non-default instance. The alternative would be:

C:\> set ORACLE_SID=SecondDB
C:\> sqlplus "/ as sysdba"
SQL> select name from v$database; (this time you'll get SECONDDB)
SQL> exit
C:\> sqlplus "/@FirstDB as sysdba"
SQL> select name from v$database;

And if you want to eliminate any possible source of confusion, you can be explicit on both occasions;

C:\> sqlplus "/@FirstDB as sysdba"
SQL> select name from v$database;
SQL> exit
C:\> sqlplus "/@SecondDB as sysdba"
SQL> select name from v$database;

I would however simply refer you again back to the networking documentation at http://tahiti.oracle.com, since this sort of configuration is pretty basic and ought to be straightforward.

When you've got it sorted out, you'll see that adding a third database to your system would consist of creating a new service (which the DBCA will do for you, of course) to handle the new instance; and editing the tnsnames.ora so that a new resolvable alias is created. The listener.ora doesn't get touched at all. A single listener can do listening duties for many, many databases/instances.

Regards
HJR Received on Thu Jun 17 2004 - 23:48:43 CDT