Database links of distributed oracle system

Database links of distributed oracle system
P893<>

一 concepts
1 Database links concept, advantages.
concept:
The database link is the central concept in distributed database syste. A database link is a connection between two physical database servers that allows a client to access them as one logical database.A database link is a pointer that defines a one-way communication path from an Oracle database server to another database server. The link pointer is actually defined as an entry in a data dictionary table.
(Database Link用于分布式数据库系统中，用来连接两个物理数据库服务器。Database link是单向连接)

advantages:
The great advantage of database links is that they allow users to access another user’s objects in a remote database so that they are bounded by the privilege set of the object’s owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database.

2 Privileges necessary for creating Database links;
Pirvilege   database required for
Create Database Link                 Local      Creation of a private database link
Create Public Database Link     Local      Creation of a public  database link
Create Session                            Remote  Creation of any type of database link.

3 about Global_name enforce,refer to document<>

二 Create database link
make sure configure sales tnsname in tnsnames.ora firstly.
sales =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.245.13)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sale)
    )
  )

Database Link types: private,public,global.
link users: connected user, fixed user, current user

default is  private connected user;

1 create private connected user;(只有创建者自己可以使用。且remote端必须有相应的用户)
conn / as sysdba
SQL> CREATE DATABASE LINK sale using 'sales';

Database link created.

SQL> select * from test@SALE.US.ORACLE.COM;
select * from test@SALE.US.ORACLE.COM
                   *
ERROR at line 1:
ORA-01005: null password given; logon denied
表明没有把用户密码传过给remote database.

conn mouse/mouse
SQL> CREATE DATABASE LINK sale using 'sales';
Database link created.
SQL> select count(*) from test@SALE.US.ORACLE.COM;

  COUNT(*)
----------
     28026

2 create private fixed user;(只有创建者自己可以使用,在remote端以connect to userid 的身份出现)

SQL> conn / as sysdba
Connected.
SQL> CREATE DATABASE LINK sale connect to test identified by test using 'sales';

Database link created.

SQL> select count(*) from test@SALE.US.ORACLE.COM;

  COUNT(*)
----------
     28026

SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> conn mouse/mouse
Connected.
SQL> select count(*) from test@SALE.US.ORACLE.COM;
select count(*) from test@SALE.US.ORACLE.COM
                          *
ERROR at line 1:
ORA-02019: connection description for remote database not found

3 create private current_user( 同样只有创建者自己可使用)
SQL> conn / as sysdba
Connected.
SQL> CREATE DATABASE LINK sale_current CONNECT TO CURRENT_USER USING 'sales';
Database link created.

SQL> select count(*) from test@sale_current;
select count(*) from test@sale_current
                          *
ERROR at line 1:
ORA-28030: Server encountered problems accessing LDAP directory service
(遇到LDAP Directory service 问题，不能访问,原因现在未知 待日后再check ???)

4 create public connected user.(所有用户可用，在local端，谁使用，对端就必须有相应的用户，对端也以该用户的身份出现）

SQL> conn / as sysdba
Connected.
SQL> create public database link sale_public USING 'sales';(sys 创建者)

SQL> select count(*) from test@hq_sales_tru;
select count(*) from test@hq_sales_tru
                          *
ERROR at line 1:
ORA-01005: null password given; logon denied

SQL> conn sys/sys as sysdba
Connected.
SQL> select count(*) from test@hq_sales_tru;
select count(*) from test@hq_sales_tru
                          *
ERROR at line 1:
ORA-01005: null password given; logon denied

SQL> conn mouse/mouse as sysdba
Connected.
SQL> select count(*) from test@hq_sales_tru;
select count(*) from test@hq_sales_tru
                          *
ERROR at line 1:
ORA-01005: null password given; logon denied

(表明，当link user type为 connected user时，必须提供用户密码,且不能以as sysdba 方式登录,
如conn sys/sys as sysdba，conn mouse/mouse as sysdba )

SQL> conn test/test
Connected.
SQL>  select count(*) from test@sale_public; (test/test使用)

  COUNT(*)
----------
     28026

还有其它的各种情形，可作类似测试。

5 shared database links:
Look carefully at your application and shared server configuration to determine
whether to use shared links. A simple guideline is to use shared database links
when the number of users accessing a database link is expected to be much larger
than the number of server processes in the local database.
(主要用于，连接database link数量比较多的时候)
CREATE SHARED DATABASE LINK dblink_name
[CONNECT TO username IDENTIFIED BY password]|[CONNECT TO CURRENT_USER]
AUTHENTICATED BY schema_name IDENTIFIED BY password
[USING 'service_name'];

SQL> conn / as sysdba
Connected.
SQL> CREATE SHARED PUBLIC DATABASE LINK sales_public_shared AUTHENTICATED BY mouse IDENTIFIED BY mouse USING 'sales';

SQL> conn test/test
Connected.
SQL> select count(*) from test@sales_public_shared;

  COUNT(*)
----------
     28026
(两端都必须有test用户，且remote端必须有test table,且remote端的mouse user 必须可以访问test.test表)

6 Manageing database link: closing, drop database, view,limiting the number of active connection.

close database link:
ALTER SESSION CLOSE DATABASE LINK sales;

drop database links:
删除的时候分private,和public.且不能删除别的用户的private db_link,即使是sys用户不行。
select 'drop  public database link '|| db_link || ';' from dba_db_links;

view db_link information
#The following script. queries the DBA_DB_LINKS view to access link information:
COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS
/

#For example, you can create and execute the script. below to determine which links are open (sample output included):
COL DB_LINK FORMAT A25
COL OWNER_ID FORMAT 99999 HEADING "OWNID"
COL LOGGED_ON FORMAT A5 HEADING "LOGON"
COL HETEROGENEOUS FORMAT A5 HEADING "HETER"
COL PROTOCOL FORMAT A8
COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"
COL IN_TRANSACTION FORMAT A3 HEADING "TXN"
COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"
COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"
SELECT * FROM V$DBLINK
/
这是连接级的查询，只有当前连接中存在db_link会话时，才有记录。

OPEN_LINKS:This parameter controls the number of remote connections that a single user session can use concurrently in distributed transactions.

三 troubleshooting:
1 Note that when you issue a SELECT statement across a database
link, a transaction lock is placed on the rollback segments. To re-release the
segment, you must issue a COMMIT or ROLLBACK statement.(Select statement also will lock the the rollback)

select count(*) from test@fixed_dblink;
ALTER SESSION CLOSE DATABASE LINK sales;
ERROR:
ORA-02080: database link is in us
commit;
ALTER SESSION CLOSE DATABASE LINK sales;
Session altered

2 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.(到底是远端的Global_names 必须为false,还是local端，或者两端都要呢。从文档的理解上看是远端的。但实际中，必须本地端，与user type 无关.）

3在建立Database link时并不检查名字的合法性。只有在使用时才检查。
select * from dual@xxx
ERROR at line 1:
ORA-02085: database link SALES connects to SALE.US.ORACLE.COM

4 conn / as sysdba
SQL> CREATE DATABASE LINK sale using 'sales';

Database link created.

SQL> select * from test@SALE.US.ORACLE.COM;
select * from test@SALE.US.ORACLE.COM
                   *
ERROR at line 1:
ORA-01005: null password given; logon denied
表明没有把用户密码传过给remote database.

conn mouse/mouse
SQL> CREATE DATABASE LINK sale using 'sales';
Database link created.
SQL> select count(*) from test@SALE.US.ORACLE.COM;

  COUNT(*)
----------
     28026

5 Database link name由 schema.db_link组成，不同的schema中可以有相同的db_link名。

6 其它细节问题查阅相关文档

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10248702/viewspace-615660/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10248702/viewspace-615660/