Accesing Multiple Oracle Instances
Moderators: chulett, rschirm, roy
Accesing Multiple Oracle Instances
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
Thanks
Ramesh
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....
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....
Assume everything I say or do is positive
Re: Accesing Multiple Oracle Instances
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.rameshrr3 wrote: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.
And no, the SID *is* a connection option in the Oracle Enterprise stage.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Accesing Multiple Oracle Instances
SID for Oracle database is equal to Remote Server option in DataStage.rameshrr3 wrote: The 'SID' is NOT a connection option in the Oracle Enterprise stage also.
Hope it helps...
Assume everything I say or do is positive
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
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:
Thanks
Ramesh
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)
)
)
'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=*******}
Ramesh
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
and
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?
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
.
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.
My next question is whether Oracle 10g is parallel by default?
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.
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.....
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)
)
)
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.....
Assume everything I say or do is positive