Creating DBlink

[sachin_salvi]

Hi ,

Can some one let me know abou creating a DBlink.
I have installed a standalone database, i want to create a DB link
from my Production database to this Local DB.

All suggestions are welcomed.. Would help me.

Thanks!

[ahmadbilal]

How To Create Private or Public Database Link
Doc ID: Note:1071984.6 Type: BULLETIN
Last Revision Date: 25-OCT-2005 Status: PUBLISHED

Syntax:
CREATE {PUBLIC} DATABASE LINK <database link name>
{CONNECT TO <oracle user id> IDENTIFIED BY
<remote oracle user's password>} USING '<dbstring>';

Notes:
- the default is for the Database Link to be created as a Private
- use the CONNECT TO option if you want to access the remote database
with a different Oracle USERID (i.e. not the one currently logged in).
- <database link name> must correspond to the name of the database to which
the database link refers if parameter GLOBAL_NAMES=TRUE
- <dbstring> is a valid SQL*Net connect string found in tnsnames.ora



EXAMPLE: You would like to be able to connect from database
"mps07db1" to database "mps07db3".

This is based on the service name (alias) of "db3" and "db_name" of "mps07db3":

TNSNAMES.ORA
============
db3=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=mps07)
(PORT=1543)
)
)
(CONNECT_DATA=
(SID=mps07db3)
)
)



CREATE DATABASE LINK SYNTAX:
============================

Note: omitting the KEYWORD PUBLIC will automatically create
PRIVATE database links.


CREATE [PUBLIC] DATABASE LINK mps07db3
CONNECT TO TC IDENTIFIED BY TIMEPRD
USING 'db3';

or

CREATE [PUBLIC] DATABASE LINK mps07db3
CONNECT TO TC IDENTIFIED BY TIMEPRD
USING 'db3.world';


TEST EXAMPLE OF CREATING A DATABASE LINK:
=========================================
Database Global Name: V734
tnsnames.ora alias: wally_V734.world

DBLINK TEST: Using V804 database as local database and V734 database as
remote database. First you need to create user sadavid and grant privileges to
sadavid.

SVRMGR> create user sadavid identified by sadavid;
Statement processed.
SVRMGR> grant dba to sadavid;
Statement processed.
SVRMGR> grant unlimited tablespace to sadavid;
Statement processed.
SVRMGR> alter user sadavid identified by sadavid default tablespace userdata;
Statement processed.
SVRMGR> alter user sadavid quota unlimited on USERDATA;
Statement processed.
SVRMGR> connect sadavid/sadavid
Connected.

********************************************************************

1) Connected to V734 database and do select from "global_name" to find out
"global_name". Do show parameter global to find out if "global_names" is
set to TRUE.

SVRMGR> connect sadavid
Password:
Connected.
SVRMGR> select * from global_name;
GLOBAL_NAME

------------------------------------------------------------------------
V734.WORLD
1 row selected.

SVRMGR> show parameter global
NAME TYPE VALUE
----------------------------------- ------- ----------------------------

global_names boolean TRUE

************************************************************************

2) Find "TNSNAMES.ORA" file which will show service name (alias) for databases.
For V734 you can see that the alias is "wally_V734.world". "TNSNANES.ORA"
for tiger 5 is located in the following directory:

/u01/app/oracle/propduct/8.0.4/network/admin

Look at "tnsnames.ora" file:

wally_V734.world =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= 138.1.102.165)(Port= 1521))
(CONNECT_DATA = (SID = V734))

***********************************************************************

3) If you create a database link named SHIRLEY and using alias from
"TNSNAMES.ORA". You will get ORA-2085 error.

Oracle Server Manager Release 3.0.4.0.0 - Production

(c) Copyright 1997, Oracle Corporation. All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.4.3.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.3.0 - Production

SVRMGR> connect sadavid/sadavid
Connected.
SVRMGR> CREATE [PUBLIC] DATABASE LINK SHIRLEY
2> CONNECT TO sadavid identified by sadavid
3> USING 'wally_V734.world';
Statement processed.

SVRMGR> SELECT * FROM GLOBAL_NAMES@SHIRLEY;
SELECT * FROM GLOBAL_NAME@SHIRLEY
*
ORA-02085: database link SHIRLEY.WORLD connects to V734.WORLD

Cause: The database link attempted to connect to a database with a different
name. The name of the database link must be the same as the name of the
database.

Action: Create a database link with the same name as the database to which it
connects.


See Note 19367.1 OERR: ORA 2085 "database link %s connects to %s" for
further explanation of the error and workaround involving init<SID>.ora
parameter GLOBAL_NAMES.


***********************************************************************

4) Create another dblink naming the link the same as database name(V734) and
make sure that you are using correct alias per "tnsnames.ora"
('wally_V734.world').

SVRMGR> CREATE [PUBLIC] DATABASE LINK V734
2> CONNECT TO sadavid identified by sadavid
3> USING 'wally_V734.world';
Statement processed.

***********************************************************************

5) Select across DBLINK to V734 database is successful.


SVRMGR> select * from global_name@V734;

GLOBAL_NAME

------------------------------------------------------------------------
V734.WORLD
1 row selected.


Solution Explanation:
=====================

Once you have service name (alias) and "db_name" from "tnsnames.ora", you will
be able to successfully create your database link and do a select across the
database link.


Oracle 8 :
==========
Note that Net8 also allows you to create a working database
link when <dbstring> is not an alias defined in the tnsnames.ora file
but a fully qualified connect string as in the following example:

create database link V805.NL.ORACLE.COM
connect to repadmin identified by repadmin using
'(description=(address=(protocol=tcp)(host=nldock0592.nl.oracle.com)
(Port = 1521) ) (connect_data= (sid=v805)))';

This can be very useful when the server side tnsnames.ora file does not
have the same connect strings for database services as your local tnsnames.ora,
for example if you
connect scott/tiger@alias
but a database link using 'alias' does not work, you can use the complete
string as in the example.





[quote]Hi ,

Can some one let me know abou creating a DBlink.
I have installed a standalone database, i want to create a DB link
from my Production database to this Local DB.

All suggestions are welcomed.. Would help me.

Thanks!



<i><div align="right">Originally posted by sachin_salvi