Very Computer

HI all,

We are running Oracle 7.3.4 on Sun 5.6.

I have
- created a new database,
- and ran the scripts
catalog
catproc
pupbld.sql

I modified the TNSNAMES.ORA and LISTENER.ORA files
to include the new ORACLE_SID.

I stopped and restarted the database.
The batch script includes starting the listener.

ltfd1           - new database
ntst1           - old database

Now, on the Unix side, if I do:

export ORACLE_SID=the_sid

and

sqlplus
        - enter username and password

things works fine.

However, if I try to run everything as one string
with either SID:

I get the error ORA-12505:

SQL*Plus: Release 3.3.4.0.1 - Production on Tue Mar 28 18:54:33 2000
Copyright (c) Oracle Corporation 1979, 1996.  All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect
descriptor

The really crazy thing is when I tried to access via
SQLPLUS for Windows NT.

I used EASYCONFIG to point to the new database.  The time stamp
for the TNSNAMES.ORA file is 1995, even though the new info is
in the file!

And then, after calling sqlplus for windows,
and entering the data in all 3 slots,
I could only connect to the new database!!!
Even though I used to connect to the old database,
I could no longer connect!

The Unix LISTENER.ORA file shows:

# Installation Generated Net V2 Configuration
# Version Date: Sep-16-97
# Filename: Listener.ora
#
        #(ADDRESS= (PROTOCOL= IPC)(KEY= ndev1))
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= TCP)(Host= dev1)(Port= 1521))
        (ADDRESS= (PROTOCOL= IPC)(KEY= PNPKEY))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ntst1)
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ltfd1)
    )
  )

STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF

Listener info shows:

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=dev1)(Port=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 2.3.4.0.0 -
Production
Start Date                28-MAR-00 18:15:43
Uptime                    0 days 0 hr. 37 min. 30 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File
/apps/oracle/product/734/network/admin/listener.ora
Listener Log File
/apps/oracle/product/734/network/log/listener.log
Services Summary...
  ltfd1         has 1 service handler(s)
The command completed successfully

LSNRCTL> services
Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=dev1)(Port=1521))
Services Summary...
  ltfd1         has 1 service handler(s)
    DEDICATED SERVER established:2 refused:0
The command completed successfully

It's interesting that there is only 1 service handler,
even though when I did this, on the Unix side,
I logged onto both databases.

Does anyone have any suggestions?

Thanks,

Argosy

Sent via Deja.com http://www.deja.com/
Before you buy.

Ok, I got it to work.  It was the nesting of the
brackets in the LISTENER.ORA file.  No meaningful
error messages.  Just didn't work.

So now, I the database is started.
From Windows NT, I can connect to either database.
Either by entering the username, password, ORACLE_SID
names one by one, or all at once:

On the Unix side, I can log into either database with:

change ORACLE_SID

sqlplus
        - username
        - password

This will work for the original database

but will not work for the new one.  ???

I did changed the TNSNAMES.ORA on file on Unix.
I copied from both the original unix, and also
from NT, but, no dice.

Also tnsping works for the old database,
but not the new one.

Another > 12 hour day for me.   Any suggestions?

Thanks,

Argosy

It should be
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ntst1)
    )
    (SID_DESC =
      (SID_NAME = ltfd1)
    )
  )

And you are lacking the line ORACLE_HOME = <directory name>
in both cases.
Exporting the ORACLE_SID on Unix bypasses sqlnet, so that's why that method
work.

I'm getting a bit sad about a day of > 12 hr. Did you ever consult
documentation and/or use the network manager (instead of net easy config).
Your problems most likely would have been resolved long ago. It is not THAT
difficult. Don't want to be harsh on you, don't get me wrong. In the past I
usually started with the 'trial and error' method instead of reading
documentation, and my employer usually didn't want to pay $$ for courses.
However, most likely they will pay themselves back instanteanously.

Regards,

Sybrand Bakker, Oracle DBA

Thanks for responding.
I did manage to find the syntax error last night after all.

Actually, I am pretty thorough.  It sometimes drives
others nuts. I read as much as I can, talk to people
who have experience, and read and post on the newsgroups.

Then, I make a big tasklist for my plan of attack.
I think through it to see if I missed anything.
When I do the task, I cut and paste the commands.
And paste the results back.  I can tell you EXACTLY
what I did and what the result was.

Yet, I still get surprises.   The CREATE DATABASE
documentation refers you to only to other commands, and
the STARTUP, and SHUTDOWN command.

Regards,

Argosy

Quote:

> I don't know how your listener.ora looks like now, but from your
previous
> post it is incorrect

> It should be
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = ntst1)
> )
> (SID_DESC =
> (SID_NAME = ltfd1)
> )
> )

> And you are lacking the line ORACLE_HOME = <directory name>
> in both cases.
> Exporting the ORACLE_SID on Unix bypasses sqlnet, so that's why that
method
> work.

> I'm getting a bit sad about a day of > 12 hr. Did you ever consult
> documentation and/or use the network manager (instead of net easy
config).
> Your problems most likely would have been resolved long ago. It is
not THAT
> difficult. Don't want to be harsh on you, don't get me wrong. In the
past I
> usually started with the 'trial and error' method instead of reading
> documentation, and my employer usually didn't want to pay $$ for
courses.
> However, most likely they will pay themselves back instanteanously.

> Regards,

> Sybrand Bakker, Oracle DBA