Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, gs.

Asked: October 15, 2002 - 6:24 pm UTC

Last updated: May 10, 2010 - 7:04 pm UTC

Version: 8.1.7.4

Viewed 10K+ times! This question is

You Asked

Tom,

Question: 1
------------
I will have few instances running on different servers. One of the server (say, MSTR server) will have some tables which will be accessed (only select) by all other srvers once in a while.

The tables with same structure and name is there in all the other servers also. And all the other servers may have more than one user (schema) with the same tables.

I am thinking of creating a user on the MSTR server with a schemaname say RDONLY, who can only select from the tables. (the tables are owned by another schema)

Now, I will create public DB Link on all the servers with fixed user RDONLY pointing to the MSTR schema.

Is there any better way to do this? The current implementation has some limitation as anyone logged in to one of the N servers can select from RDONLY@MSTR.

Another somewhat related question: Is there anyway, I can pass the userid and/or password to the DB link?


Question: 2
-----------
Soon I will be having more servers and will have one instance per server. Currenly all my instances are called the same name.

Is there any advantage of having different name to different instances? What are all the utilities/products (such as OEM) mandates that the instance/dbname has to be different on each server?

Note: Each of my instances will always be on differnt servers.


Thanks.


and Tom said...

1) the RDONLY is good -- but you can use a private db link and views to restrict WHO can access it from the remote sites, consider:

b@ORA815DEV.FOO> @connect /

ops$tkyte@ORA815DEV.FOO> drop user a cascade;
User dropped.

ops$tkyte@ORA815DEV.FOO> drop user b cascade;
User dropped.

ops$tkyte@ORA815DEV.FOO> grant create session to a identified by a;
Grant succeeded.

ops$tkyte@ORA815DEV.FOO> grant create session to b identified by b;
Grant succeeded.

ops$tkyte@ORA815DEV.FOO> @connect scott/tiger

scott@ORA815DEV.FOO> drop database link ORA817DEV.US.ORACLE.COM;
Database link dropped.

scott@ORA815DEV.FOO> create database link ORA817DEV.US.ORACLE.COM
2 connect to scott
3 identified by tiger
4 using 'ORA817DEV.US.ORACLE.COM';

Database link created.

scott@ORA815DEV.FOO> select * from dual@ORA817DEV.US.ORACLE.COM;

D
-
X

scott@ORA815DEV.FOO> create view remote_table
2 as
3 select * from emp@ORA817DEV.US.ORACLE.COM
4 /
View Created.


scott@ORA815DEV.FOO> grant select on remote_table to a
2 /
Grant succeeded.

scott@ORA815DEV.FOO> @connect a/a

a@ORA815DEV.FOO> select ename, empno from scott.remote_table where rownum = 1;

ENAME EMPNO
---------- ----------
SMITH 7369

a@ORA815DEV.FOO> @connect b/b

b@ORA815DEV.FOO> select ename, empno from scott.remote_table where rownum = 1;
select ename, empno from scott.remote_table where rownum = 1
*
ERROR at line 1:
ORA-00942: table or view does not exist


so, using that technique, you can "reduce the exposure" to the RDONLY account easily


To "pass" a username/password to a dblink -- you use the technique above (i like views much better then synonyms to hide the dblink too -- imports the meta data locally, easier all around -- better security choices)

2) global_name should be unique (easy to alter database). they can have all of the same sid and oracle home. dbname is OK to be the same as well.



Rating

  (35 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I thought about it. But the issues are

gs, October 16, 2002 - 7:45 pm UTC

I have my application do "set current_schema=".

Say, I have application user as appl_user1, appl_user2 etc
The corresponding db schema are db_user1, db_user2 etc

db_userX has the database objects and appl_userX has the select, insert etc privileges.

So, if appl_user1 set the current_schema to db_user1, it couldn't see the DB_LINK owned by the db_user1. The other was is to create the db_link probably under appl_userX. So, in this case what could be the preferred way?

The issue with views are, I have the same table on both side with the same name.

Regarding Question 2, I was thinking of having different global names for each instances. Then I was not sure whether the instance/db names also need to be unique. But your answer clarifies that very well. I didn't have two instances installed to test that.


Tom Kyte
October 16, 2002 - 7:50 pm UTC

so, name the view something DIFFERENT then. Use the view.






again

gs, October 16, 2002 - 8:55 pm UTC

BTW, is there anyway to access the objects thru the db_link defined on another schema (not public)? I guess this is not possible! (not by creating a procedure in the other schema and acessing)

Thanks!

Tom Kyte
October 17, 2002 - 7:03 am UTC

sure you can -- just like you would on a single database. You "grant select" on the object and you have access to it.

A reader, October 17, 2002 - 9:49 am UTC

Hi Tom,

I have two test databases on two servers.One is 9.0.1. and the other database is 8.1.7. The name of both the database is test and the domain name is oradom.com.
I want to create a link from 9i to 8i. but when i try to create link it gives me this error..

system@TEST.ORADOM.COM> create database link test.oradom.COM
2 connect to scott
3 identified by tiger
4 using 'test.oradom.COM';
connect to scott

ERROR at line 2:
ORA-02082: a loopback database link must have a connection qualifier


Tom Kyte
October 17, 2002 - 1:53 pm UTC

if they both have the same name.... you are creating a loopback link -- this database connects back to itself.

Two pieces of advice

a) first and foremost, DON'T USE SYSTEM, thats our account, don't use it, don't use sys, not even for testing.

b) use different names for them. test8i, test9i perhaps. change their global names (alter database command)

A reader, October 17, 2002 - 11:41 am UTC

Hi,

That is because you are using the DB_LINK name and the connect string which are same.

re-write it as

create database link test8i connect to scott
identified by tiger
using 'test.oradom.COM';

Am I correct Tom?

Regards,
Sagi.

Sagi

Sagi, October 17, 2002 - 12:30 pm UTC

Hi !

I forgot one more thing i.e. regarding global_names.

From the docs

"The name that you give to a link on the local database depends on whether the remote database that you want to access enforces global naming. If the remote database enforces global naming, then you must use the remote database's global database name as the name of the link. For example, if you are connected to the local hq server and want to create a link to the remote mfg database, and mfg enforces global naming, then you must use mfg's global database name as the link name."

Also please be aware that you can change the global_name using:

ALTER DATABASE RENAME old_global_name TO new_global_name ;


Tom please comment if i am wrong.

Regards,
Sagi



A reader, October 17, 2002 - 1:25 pm UTC

thanks sagi for your answer...was using the wrong service name..got it now...

A reader, October 17, 2002 - 1:32 pm UTC

Tom,

If the database enforces global names and both databases on different servers have the same name, then how will one create the db link...

Rick.

Tom Kyte
October 17, 2002 - 2:04 pm UTC

then you CANNOT BY DEFINITION be using global names!!!


global names is a method whereby we enforce uniqueness of database global names in a collection of distributed databases. they cannot have the same name by its very purpose.

Grant on remote objects.

Js, June 28, 2004 - 4:56 am UTC

hi.

I was trying to do the same [ Main question ] without view. 

I created a user "test" in two database on the same machine.

In database A  : test has a table "tab_a"

In database B  : test has a synonym "tab_a"  pointing to 
test.tab_a in database A using db_link.


       
      db_a                            db_b

  User :  A                      User : B
  Table :  tab_a    <--------    synonym tab_A on db_a's 
                                 table using db_link.

                                  
I am able to select,update,delete from db_B ;

But when I tried to grant select on public synonym tab_a to public in database B;

I got this error ...

                     
SQL> grant select on tab_a to public;
grant select on tab_a to public
                *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

 DDL ? ..   I am granting Select ...

 Sir,  This is working if I create a view first and then 
 public synonym and grant them to other users.

 Could you pls. explain Why it is not working with direct synonym.

Thanks,
Js
 
              

 

Tom Kyte
June 28, 2004 - 8:25 am UTC

a synonym is just another name for something.

that was the same as:

grant select on tab_a@a to public;


that would be DDL over a dblink. You'll want to use a view.




Grant Using DBLink

Soni, March 30, 2005 - 1:20 pm UTC

I have a table in Database1, for which I have to give SELECT access to another user(Java User) through Database2.
When I use dblink in my GRANT statement I am getting following error:
DDL operations are not allowed on a remote db

Tom Kyte
March 30, 2005 - 1:56 pm UTC

<quote>
I have a table in Database1, for which I have to give SELECT access to another
user(Java User) through Database2.
</quote>

doesn't make sense to me. in database1, USER1 granted select on T to JAVA_USER

Now, when java_user connects to database1 (via a dblink, directly, whatever), they have access to it.

Database"2" doesn't come into play? database2, database3, whatever can have a dblink to database1, if java_user uses it, they are authenticated to database1 and whatever grants are in place are in place.

A reader, March 30, 2005 - 4:15 pm UTC

Sorry for not explaining this correctly, here is the problem:

Database A Database B

User : A User : B
Table : tab_a <-------- synonym tab_a on db_a's
table using db_link.

We want User C to have only SELECT privilege.
When I say:

GRANT SELECT ON tab_A TO C;

I get this error:
ORA-02021: DDL operations are not allowed on a remote database.

However, if I create View first and then create synonym and then grant 'select' privilege, it works. But my problem is, I don't want to create a view(because User C needs access to several tables in Database A and we don't want to create view for each table in Database A). Is there an alternative way to solve this?

What are the alternatives for DBLinks?


Tom Kyte
March 30, 2005 - 5:22 pm UTC

create view tab_a as select * from tab_a@remote;
grant select on tab_a to c;




A reader, March 31, 2005 - 7:56 am UTC

Thanks a lot for your response.

1.
I have executed the following commands:

-> create view tab_a as select * from tab_a@remote;
-> grant select on tab_a to c;

2.Then logged in as user C and tried the following select command:

-> SELECT * FFROM TAB_A;

3. I am getting the following error:
Table TAB_A does not exist.

4.From Database B, can't why can't I directly execute following command:

GRANT SELECT ON TAB_A@MYLINK TO USER_C;

Is it a must to create a view/synonym?

Thanks in advance!






Tom Kyte
March 31, 2005 - 8:22 am UTC

select * from B.tab_a;

or have C create a synonym tab_a for b.tab_a

otherwise, select * from tab_a executed as C is of course "select * from C.tab_a"


you cannot grant on that object, that is why, it doesn't "exist" locally.

How to find out the real user id using common database link

Ray Cheng, April 11, 2005 - 5:07 pm UTC

Now I've got a question on the user ID. Say I have some remote tables are accessed by different users through a common database link. I have triggers on those remote tables to record who make changes to them. However, since all the user are sharing one database link i.e. one user ID to access remote table, "user" variable in the remote session will be a single value as defined in database link instead of the original local log-on IDs. Is there a way to carry over the real user ID from the calling site?

Thanks.

Tom Kyte
April 11, 2005 - 8:18 pm UTC

that'll only be true with a "proxy" database link and in that case they are all really "the proxy user", you've given up their identity.

You would have to let the real user really connect.

To find the real user behind the db link

William Wong, May 11, 2005 - 3:03 pm UTC

After some investigations and testing, I have developed a package that will tell me the original logon oracle user. Of course, provided you know from which database the original user is connected from, given the user who owns the public link. I find it very useful particularly when attached to audit table trigger. Instead of using USER, I will use my package variable HB_ORAUSER_SQL.ORAUSER.

-- This is the package to share with all real programmers
-- HB_ORAUSER_SQL_REMOTE is a synonym pointing
-- to this package in the user original logon database
CREATE or REPLACE PACKAGE HB_ORAUSER_SQL AS
ORAUSER VARCHAR2(30);
FUNCTION GET_ORAUSER(I_process IN VARCHAR2,
O_orauser OUT VARCHAR2)
return BOOLEAN;
END;
--
CREATE or REPLACE PACKAGE BODY HB_ORAUSER_SQL AS
FUNCTION GET_ORAUSER(I_process IN VARCHAR2,
O_orauser OUT VARCHAR2)
return BOOLEAN is
L_username VARCHAR2(30);
cursor c_orauser is
select s.username
from v$session s, v$process p
where p.addr = s.paddr
and p.spid = I_process
;
BEGIN
open c_orauser;
fetch c_orauser into L_username;
close c_orauser;
O_orauser := L_username;
return TRUE;
EXCEPTION
when others then return FALSE;
END GET_ORAUSER;
--
PROCEDURE SET_ORAUSER is
L_audsid NUMBER;
L_process VARCHAR2(12);
L_orauser VARCHAR2(30);
L_user VARCHAR2(30);
cursor c_process is
select process
from v$session
where audsid = L_audsid;
BEGIN
L_user := user;
ORAUSER := L_user;
--
if L_user = 'LINKOWNER' then
L_audsid := USER;
open c_process;
fetch c_process into L_process;
close c_process;
if HB_ORAUSER_SQL_REMOTE.GET_ORAUSER(L_process,L_orauser) then
ORAUSER := L_orauser;
end if;
end if;
EXCEPTION
when others then NULL;
END SET_ORAUSER;
--
BEGIN
SET_ORAUSER;
END HB_ORAUSER_SQL;
/
By the way, Ray is my buddy and we solved our problem.


reader

A reader, May 17, 2005 - 4:55 pm UTC

"
scott@ORA815DEV.FOO> create view remote_table
2 as
3 select * from emp@ORA817DEV.US.ORACLE.COM
4 /
View Created.


scott@ORA815DEV.FOO> grant select on remote_table to a
2 /
Grant succeeded.
"

In this situation, creating a public synonym to the database
link instead of creating a view, and granting access to
other users to acces the database objects via a public synonym ( viathe database link). Is this a viable solution


Tom Kyte
May 17, 2005 - 6:22 pm UTC

maybe -- depends on how the database link is created (proxy or not), whether the person using the synonym has the ability to see the remote object (here they might only need to see the view if the view was built on a private proxy database link that used a user account that had access to the table)

it is different, is it viable -- could be under the right circumstances

but I favor the view if possible, as it imports the metadata over (column names, types and so on). A local stored procedure that references 10 views using 10 dblinks will open NONE of them to recompile. A local stored procedure that references 10 synonyms that point to 10 objects that each pointed to a separate database link would open 10 dblinks (or refuse to compile).

Reader

A reader, May 17, 2005 - 8:52 pm UTC

For instance there is a non-proxy database link that has
access to the remote table(s). There are many remote
tables owned by database link owner. Creating a
view for each and every remote table seems a bit
cumbersome. I do not have access to a database servers
right now to test it. Is it not possible for owner of the link
to grant access to link to other local users. Other users,
before they use the link, set current_schema=<the owner of link>;
Then could they just use the link to aceess remote
tables. About the local stored procedure, view might be
the best solution

Tom Kyte
May 18, 2005 - 8:43 am UTC

and creating a synonym is somehow easier?

reader

A reader, May 18, 2005 - 12:59 pm UTC

Since link$ table has passowrd in clear text in it, only
the SYS user can have access to it.

What are system privilege or init.ora setting,
by revoking which, the regular user can not have visibilty
to link$ table. In other words, what system privilege or
init.ora setting, make a normal user see link$ table

Tom Kyte
May 18, 2005 - 1:16 pm UTC

they would need select any table OR select on that table

AND

O7_DICTIONARY_ACCESSIBILITY would have to be set to true (and it defaults to false in 9i and up)

reader

A reader, May 18, 2005 - 1:57 pm UTC

Thanks

Your answer probably relates upto 8i

I think 9i and up only select any dictionay will grant
visibilty regardless of 07_ setting , right?



Tom Kyte
May 18, 2005 - 3:35 pm UTC

false.  you control access to SYS owned objects via that parameter.  it overrides the ANY TABLE/ ANY DICTIONARY


ops$tkyte@ORA10G> show parameter o7
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
ops$tkyte@ORA10G> grant select any dictionary to ops$tkyte;
 
Grant succeeded.
 
ops$tkyte@ORA10G> select * from sys.link$;
select * from sys.link$
                  *
ERROR at line 1:
ORA-01031: insufficient privileges
 
 

reader

A reader, May 18, 2005 - 2:53 pm UTC

I am a bit confused.
In 8.1.7.4, the role 'SNMPAGENT'
does not have any role granted to it
does not have any system privilege assigned to it
dba_tab_privs have some V_$ views and some dba_ views

However when 07_dictionary_accessibilty is set to TRUE,
I can view sys.link$

reader

A reader, May 18, 2005 - 4:03 pm UTC

Please disregard the posting above. I can describe sys.link$
and not select on sys.link$

reader

A reader, May 18, 2005 - 4:30 pm UTC

It seems sys.link$ is a special table. I can not access
sys.link$ but I can access sys.tab$

SQL> connect / as sysdba
Connected.
SQL> grant snmpagent to i1;
 
Grant succeeded.
 
SQL> grant select any dictionary to i1;
 
Grant succeeded.
 
SQL> grant select any table to i1;
 
Grant succeeded.
 
SQL> connect i1/i1
Connected.
SQL> select * from sys.link$ where rownum = 1;
select * from sys.link$ where rownum = 1
                  *
ERROR at line 1:
ORA-01031: insufficient privileges
 
 
SQL> sho parameter 7
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE
log_archive_dest_7                   string
log_archive_dest_state_7             string      enable
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> select count(*) from sys.link$;
select count(*) from sys.link$
                         *
ERROR at line 1:
ORA-01031: insufficient privileges
 
 
SQL>  select count(*) from sys.tab$;
 
  COUNT(*)
----------
      3024
 

reader

A reader, May 18, 2005 - 9:51 pm UTC

I have tested the effect of 07_dictionary_accessibility
parameter on the sys owned objects on 8.1.7.4 as well as
9.2.0.5 databases. I found it to work just exactly the way
you have outlined in the posting

In 8.1.7.4, even the link$ table can be viewed when
the parameter is set to true and select any table is
granted to the user

Perhaps, there had been an enhancement in 9.2.0.5 to
protect link$ table because it contains
clear text password

Drop Database Link that has no Domain name

JZ, May 27, 2005 - 11:35 am UTC

Hi, Tom,

I created a database link before without a domain name. At that time my global_name was simply 'ADB' without domain name either. The resulted db_link name in all_db_links is 'REMOTEDB' without domain name and it can be used.

Later I changed my global_name to 'ADB.domain' with a domain name. Since then that old db link is no longer usable and I cannot drop it either. Oracle complains that "no such db link" even though it still shows up in user_db_links. I tried to change back to the old global_name 'ADB' but it didn't help. If I create a new db link with the same name 'REMOTEDB' a new entry is found in user_db_links with all same information except it has the domain name and this new link works. My question is, how can I get rid of the old db link?

Thanks

Tom Kyte
May 27, 2005 - 12:45 pm UTC

I'll ask you to work this one via support. (tell them to peek at bug 3675157)

Thanks for the direction

JZ, June 08, 2005 - 3:40 pm UTC

Yah, I contacted Oracle support with that bug number. They were able to help and I dropped those DB links without domain name. Thanks!

A reader, December 07, 2006 - 9:19 am UTC

In 9.2.0.7
set O7_DICTIONARY_ACCESSIBILITY = TRUE
and grant SELECT ANY TABLE to a user.
the user can not select sys.link$, even is able to desc it.
that is different from 8i.

Tom Kyte
December 07, 2006 - 1:06 pm UTC

very nice isn't it.



DB Link in same database

Ramchandra Joshi, September 15, 2008 - 4:57 am UTC

Hi,

The question below may sound foolish but I have to work this out somehow.

Well I have a daily DB and a Archive DB where tables need to be archived in archive DB.

Te archive DB is not yet setup and there are a number of packages that I need to test for the archival process which uses the DB_LINK name hardcoded in it and I cannot change those pacakges.

Hence I thought of creating the archive tables in the same database tempororily and create a DB Link pointing to same DB so that I can at least test my packages.

Unfortunately while doing so I came to know that I cannot create DB link pointing to my own DB.

Is there any way or workaround to resolve this issue?

For your reference I'm using oracle database 10g.

Thanks in advance,
Ramchandra Joshi
Tom Kyte
September 16, 2008 - 10:01 pm UTC

... Unfortunately while doing so I came to know that I cannot create DB link
pointing to my own DB.
...

sure you can, I do it all of the time?

You might have an issue with GLOBAL NAMES - which would restrict the name of course - but you can create a database link.

This is why a view would usually be preferred over a dblink in code (I like views over synonyms for dblinks - especially for compiling code - the remote database doesn't need to be available even to compile the code - the view creation imported the metadata for you into the local database)


You might consider a quick change from t@db to v (v for view...) before you roll this code out.

Problem with materialized views

Alejandra, February 12, 2009 - 10:03 am UTC

I like the idea of encapsulating the db link by using views. However, although it works just fine for online queries, I've found a problem when users try to query the view in order to create a materialized view:
SQL> conn remote/xxx
SQL> create database link EBSPTTP.WORLD connect to
remote identified by xxx using 'ebspttp';
SQL> create view AC_AC_AGENTE_RETENCION  as select *
from ac.AC_AGENTE_RETENCION@ebspttp with readonly;
SQL> grant select on AC_AC_AGENTE_RETENCION to A;

SQL> conn A/A
SQL> select * from remote.AC_AC_AGENTE_RETENCION;
This works OK.

SQL> create materialized view my_mv
  2  as select * from remote.AC_AC_AGENTE_RETENCION;
as select * from remote.AC_AC_AGENTE_RETENCION
                        *
ERROR at line 2:
ORA-04052: error occurred when looking up remote object
AC.AC_AGENTE_RETENCION@EBSPTTP.WORLD
ORA-00604: error occurred at recursive SQL level 1
ORA-02019: connection description for remote database not found
I guess this is expected behavior, but is there any solution to have a single, private dblink (as I said, I like the view method) that'll allow users who are granted read access to create snapshots as if they were using their own private ilnks or a public one?
Tom Kyte
February 12, 2009 - 4:09 pm UTC

lets back up a bit - this seems like a bad idea - to have a plethora of materialized views just created here and there and everywhere. Would this be something the DBA does in a very controlled environment/manner?

Materialized views part 2

Alejandra, February 13, 2009 - 12:40 pm UTC

We wanted to allow developers to choose whether to use remote queries (which works) or to cache the results locally with mviews (which doesn't). The benefit of your view approach is that it centralizes dblink management. But we'd like to let developers create their own mviews, not to do it ourselves (DBAs). Are you saying this is not a good idea? What would you do then?
Oh, and thanks for your help!
Tom Kyte
February 16, 2009 - 11:44 am UTC

seriously bad idea, you would hate yourself in the morning. You really want people to willy nilly create hard dependencies between databases in an unmanaged, uncontrolled fashion?

really?

Mviews part 3

Alejandra, February 16, 2009 - 12:55 pm UTC

I'll take your word for it, although I don't see such a big difference between an online query and a materialized view in this respect; they both represent dependencies between databases.
Anyway, I've still need to solve the problem where a developer needs to access a remote table and deems the best way is to have the data cached. What would be a better way to do it?
Tom Kyte
February 16, 2009 - 1:04 pm UTC

no, one is a hard dependency - it lives after the session goes away, it is data to be managed, it could ask itself to be refreshed every 30 seconds. You are allowing developers to schedule tasks that take place against a size and set of data that is entirely unknown to you on a period that is unknown to you. If I were the owner of the remote system, I would be locking your account out right about now in fear.

You'll need to use public database links if you want to do it the way you are - or have the developers have the ability to log into the remote database using their own credentials (so you would use a current user database link).

A reader, February 17, 2009 - 8:37 am UTC


Public DB link or not?

Hariharan Sairam, October 07, 2009 - 3:33 am UTC

Hi Tom,

Good Day.

I have some issues and need your expert guidance on this.

We have two servers called TN and FP. In order to transfer the data (on daily basis) from one server to another, a public db link was created. When we were testing for DISASTER RECOVERY, we changed one of the database to passive. (i.e.) TN was changed to Passive and FP remains active and found that DB link is not working.

Is it possible to access the passive database from active database through DB Link? If not, can you provide me with some other ways to access the passive database remotely?

Thanks

Hari
Tom Kyte
October 08, 2009 - 7:28 am UTC

tell me what active and passive means to you - you must be doing something all on your own there - as this doesn't appear to be dataguard?

and describe not working, otherwise I will be compelled to answer you with

my car won't start, why?

Public DB link or not?

Hariharan Sairam, October 08, 2009 - 7:36 am UTC

Hi Tom,

Sorry about the previous email.

Active database is where the users connect and access the database.

Passive is the replication of the active database. Here, replication is done on the OS level using Shareplex.

Not working in the sense I am not able to establish the link. When checked in the sites, they said that DB link cannot be established in this type of scenario. I thought it would always be better to get the information from you.

You may ask me why should I connect from active to passive, but it is one part of Disaster Recovery testing which needs to be satisfied.

Hope I am making it clear

Sorry again for the previous email

Hari

Public DB link or not?

Hariharan Sairam, October 09, 2009 - 4:42 am UTC

Hi Tom,

Good Day.

We are trying to implement DB Link to transfer data from one server to another server. My previous post on ACTIVE-PASSIVE exactly does this. However, I got the following statements on database link from my DBA. Can you please let me know if these are valid statements?

DBA does not endorse using DBLINK to fetch large amount of data on production between servers for the following reasons.

1.  During individual server failure,  only one set of server(s) will failover to its DR center.
2.  Performance over production servers locally is not as good as sqlldr.
3.  You may also affecting network performance.

Can you please answer my previous post and this post as well?

Thanks for your help

Hari

Public DB Link or Not?

Ali, May 09, 2010 - 9:23 pm UTC

Tom,
I am having two database instance lets say A & B in Two different Servers. Both is having different tables and all.

I want to fetch the data from the Instance A from B. I dont want to use DB_LINK due to the security & performance issue.

Is there any alternative available to fetch the data from the instance A from B?

I Know the java or other progamming language as a alternative, but i want to know any alternative in Oracle.
Tom Kyte
May 10, 2010 - 7:04 pm UTC

what security issue do you PERCEIVE will happen???? Back it up, be thorough - because I'm going to rip apart pretty much any 'security' FUD

As for performance - ditto, put up some numbers that show "dblinks are X% slower than java doing it over two connections"

because - my answer is:

java is insecure in this particular application because you have to figure out a way to securely get it two user/passwords to connect with and java will almost certainly be slower because java coders love to write procedural, slow by slow code.

Want it fast and secure? PLSQL + dblinks = your best change.

Want it slow and probably vulnerable to leaking passwords? Write it yourself.

Alexander, May 11, 2010 - 9:25 am UTC

Our auditors (or it could be external auditors, can't remember which) frown upon db links also because they think you don't need a password to access data in a remote database.

I don't think the fact that you need one to create it initially helps, but the fact that you need to be granted access to it just like anything else should.

Imports the meta data locally

reader, May 11, 2010 - 3:53 pm UTC

Tom:

With reference to your note :
To "pass" a username/password to a dblink -- you use the technique above (i like views
much better then synonyms to hide the dblink too -- imports the meta data locally, easier
all around -- better security choices).

When you say "imports the meta data locally" does it also include the optimizer statistics for the remote object or just the columns, data types and grants ?

Can you please elaborate?