Accesing Multiple Oracle Instances

[rameshrr3]

How can i access multiple remote oracle database instances(all of the same version 10g) using Oracle Enterprise stage plug in ? To my knowledge , only one ORACLE_SID is accesible in Oracle Enterprise stage. The 'SID' is NOT a connection option in the Oracle Enterprise stage also.

Thanks
Ramesh

[csrazdan]

I'm not clear about your requirement. If you want to access multiple Oracle instances:

1. You can have multiple Oracle Enterprise stage one for each DB instance.

2. You can have one Oracle Enterprise stage connecting to one database and have dblinks to other databases. In the SQL you can refer to remote database table as TABLE_NAME@DBLINK_NAME (This is exactly the same way you would do in PL/SQL)

3. You can ask your DBA to create synonyms for the tables in the database you want to connect and access these synonymns and any other objects in Oracle Enterprise stage.

Well there are few more possibilities...

Hope it helps....

[chulett]

How can i access multiple remote oracle database instances(all of the same version 10g) using Oracle Enterprise stage plug in ? To my knowledge , only one ORACLE_SID is accesible in Oracle Enterprise stage. The 'SID' is NOT a connection option in the Oracle Enterprise stage also.

One SID per stage, use multiple stages to connect to multiple instances. This is only an issue when discussing a single job. DBLinks would only need to come into play if you were joining tables across instances.

And no, the SID *is* a connection option in the Oracle Enterprise stage.

[csrazdan]

The 'SID' is NOT a connection option in the Oracle Enterprise stage also.

SID for Oracle database is equal to Remote Server option in DataStage.

Hope it helps...

[rameshrr3]

Im using a Solaris SMP system.

I want one Ora Enterprise stage to connect to Only one remote Oracle SID as defined in TNSNAMES.ORA.
On the same note i will need to access other SID's using additional OraEnt stages.

my TNS Names contains the following entry

Code: Select all

DNYSOPH3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = nysophdev02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DNYSOPH3)
    )
  )

What do i give for "Remote server" option in Oracle Enterprise stage ?
'DNYSOPH3' or 'nysophdev02'? The oracle instance is on the eponymous remote server/HOST .

My DB Options string looks like this:

Code: Select all

{user=blah,password=*******}

Thanks
Ramesh

[rameshrr3]

Wonder of wonders

I set Remote Server to 'DNYSOPH3' and was able to view data .

While running the job , im getting the 2 following 'fatal' errors

Code: Select all

Oracle_Enterprise_0: GenericQuery:esqlErrorHandler
Prepare failed for: GenericStmt_5
query is: select count(*) from       sys.gv_$instance
sqlcode is: -942
esql complaint: ORA-00942: table or view does not exist
.

and

Code: Select all

Oracle_Enterprise_0: APT_OraInstanceSet::opsInstalled(): prepare failed for query `select count(*) from       sys.gv_$instance
';
please have the DBA run the following command: grant select on sys.gv_$instance to public.

I guess the above messages are self explanatory and give a fair idea of what to do next.

My next question is whether Oracle 10g is parallel by default?

[csrazdan]

I do not see any wonder.....

Based on following tnsnames entry even if you are connecting to Oracle from SQL prompt you will use:

UserName/Password@DNYSOPH3

This is exactly same way you would connect to Oracle from DataStage.

Code: Select all

DNYSOPH3 = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = nysophdev02)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = DNYSOPH3) 
    ) 
  ) 

No Oracle 10G does not run in parallel by default. According to documentation you require SELECT on sys.gv_$instance only when you have Oracle instance running tin parallel. May be your instance is running in parallel. (Though looking at tnsnames entry it does not seam so.)

Even if you instance is not running in parallel, it is a good idea to ask you DBA's to give SELECT access to sys.gv_$instance

Hope it helps.....

[rameshrr3]

Thanks. The term "Remote 'Server'" was puzzling me. Im marking this thread as resolved.