Two databases in one PHD

[Fry, Mark]

Hi Robert,

What happens if you get to a unix shell and enter

sqlplus $VORA1/$VORA2@DB1

or

sqlplus $VORA1/$VORA2@DB2

Does sqlplus connect OK, or do you get a similar message from TNS?

The syntax you have used in your dictionary looks fine to me.

Is your Oracle TNS listener daemon started?

Are all your environment variables set correctly? (below is an example of
c-shell syntax I use for Oracle 8 - the same variables are used for Oracle 7
too)

setenv LD_LIBRARY_PATH /isv/oracle/8.0.3/lib
setenv ORACLE_HOME /isv/oracle/8.0.3
setenv ORACLE_SID ORASOL80
setenv SHLIB_PATH $ORACLE_HOME/lib:$SHLIB_PATH
setenv PATH $ORACLE_HOME/bin:$PATH 

What about your $VORA1 and $VORA2 environment variables?  Have you checked
that they contain your username and password?

Here are the entries from tnsnames.ora that I use [hostnames have been
changed to protect the innocent :)]:

ORASOL80 =
  (DESCRIPTION =
    (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = <hostname of a Cognos ORACLE server>)
          (PORT = 1521)
    )
    (CONNECT_DATA = (SID = ORASOL80)(GLOBAL_NAME=ORASOL80)
    )
  )

ORASOL80.world =
  (DESCRIPTION =
    (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = <same hostname as above>)
          (PORT = 1521)
    )
    (CONNECT_DATA = (SID = ORASOL80)(GLOBAL_NAME=ORASOL80.world)
    )
  )

The only real difference I can see is that I have a GLOBAL_NAME set and you
do not.  Might be worth setting it to see if it makes a difference.

Lots of questions, but no definite solutions (so far).

Regards,

Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com

 -----Original Message-----
From: 	Robert J.M. Edis [mailto:robert.edis@creatcomp.com] 
Sent:	23 February 2000 16:49
To:	'powerh-l@list.swau.edu'
Subject:	Two databases in one PHD

G'day all

Environment: HP/UX 10.20, PH 7.33.D3, Oracle 7.3.4

I am trying to put two database contections in the same PHD.  Both RDBMS's
have the same structure, including table and columns names but a for
different organisations.

My environment variables ORACLE_SID, ORACLE_TERM, ORACLE_HOME and TNS_ADMIN
are set correctly.  The PATH points to .../oracle.7.3.4/bin etc.

My PHD declarations are:

Database DB1 type ORACLE open ORACLE@$VORA1@DB1/$VORA2 owner xxx
Database DB2 type ORACLE open ORACLE@$VORA1@DB2/$VORA2 owner xxx

The tnsnames.ora entries are:

DB1.world =
  (DESCRIPTION =
   (ADDRESS_LIST =
      (ADDRESS =
        (COMMUNITY = tcp.world)
        (PROTOCOL = TCP)
        (Host = 4.56.211.59)
        (Port = 1521)
      )
      (ADDRESS =
        (COMMUNITY = tcp.world)
        (PROTOCOL = TCP)
        (Host = 4.56.211.59)
        (Port = 1526)
      )
   )
     (CONNECT_DATA = (SID = DB1)
  ))
DB2.world =
  (DESCRIPTION =
   (ADDRESS_LIST =
      (ADDRESS =
        (COMMUNITY = tcp.world)
        (PROTOCOL = TCP)
        (Host = 4.56.211.59)
        (Port = 1521)
      )
      (ADDRESS =
        (COMMUNITY = tcp.world)
        (PROTOCOL = TCP)
        (Host = 4.56.211.59)
        (Port = 1526)
      )
   )
     (CONNECT_DATA = (SID = DB2)
  ))

Problem:

> acc table1 in db1
*W* SQL START DBE error (ORACLE@$VORA1@DB1/$VORA2)
DMS-E-GENERAL, A general exception has occurred during operation 'attach 
database'.
ORA-12154: TNS:could not resolve service name
*E* The file TABLE1 IN DB1 wasn't found in the data dictionary.
> acc table1 in db2
*W* SQL START DBE error (ORACLE@$VORA1@DB2/$VORA2)
DMS-E-GENERAL, A general exception has occurred during operation 'attach 
database'.
ORA-12154: TNS:could not resolve service name
*E* The file TABLE1 IN DB2 wasn't found in the data dictionary.
>

I looked at the knowledgebase at the Cognos support site and that is how I
derived the logic above (document: KB57519CS).  The document has some
typograhpical errors but I worked around those.

Anyone have an idea as to why this is not working?

Blue
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.