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 ;
select * from
                   *
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 ;

  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 ;

  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 ;
select count(*) from
                          *
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 ;
select count(*) from
                          *
ERROR at line 1:
ORA-01005: null password given; logon denied

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

SQL> conn mouse/mouse as sysdba
Connected.
SQL> select count(*) from ;
select count(*) from
                          *
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/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 ;

  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 ;
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
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 ;
select * from
                   *
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 ;

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

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

6 其它細節問題查閱相關文件