oracle 11.2.0.4之oracle database db link之测试明细之一

测试结论
1,创建数据库db link有2种方式,一则为通过配置tnsnames.ora访问远端数据
  二则为不配置tnsnames.ora访问远端数据库

2,通过配置tnsnames.ora访问远端数据库的创建DB LINK的语法如下

create database link target_user_zxy connect to user_zxy identified by system  using ‘tns_esbdb';

(注:tns_esbdb为netmgr创建的net service name)

3,为不配置tnsnames.ora访问远端数据库 创建DB LINK的语法如下
SQL> create database link target_user_zxy connect to user_zxy identified by system 
  2  using ‘(DESCRIPTION = 
  3         (ADDRESS_LIST = 
  4         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521)) 
  5         ) 
  6         (CONNECT_DATA = 
  7          (SERVICE_NAME = esbdb) 
  8         )';

Database link created.

(注:一定要注意格式,否则即使创建成功,DB LINK也使用不了)

4,db link有2种类型,一则为private,二则为public

5,private db link只能创建数据库用户使用这个db link

6,public db link可以所有数据库用户使用这个db link

7,删除db link的语法
drop database link target_user_zxy;

8,删除public db link语法

drop public database link target_user_zxy;

9,private db link即使通过授权其它数据库用户强大的权力或者通过同义词,其它的数据库用户仍旧不能使用访问private db link

10,通过tnsnames.ora配置创建db link,如下字典对应的host为net service name
SQL> select owner,db_link,username,host,created from dba_db_links;

OWNER                          DB_LINK                        USERNAME                                 HOST                                               CREATED
—————————— —————————— —————————————- ————————————————– ———
SYSTEM                         TARGET_USER_ZXY                USER_ZXY                                 tns_esbdb                                          12-JUN-17

11,不通过tnsnames.ora配置创建db link,如下字典对应的host为如下

SQL> set linesize 300
SQL> col username for a40
SQL> col db_link for a30
sql> col host for a50
SQL> select owner,db_link,username,host,created from dba_db_links;

OWNER                          DB_LINK                        USERNAME                                 HOST                                               CREATED
—————————— —————————— —————————————- ————————————————– ——————-
SYSTEM                         TARGET_USER_ZXY                USER_ZXY                                 (DESCRIPTION =                                     2017-06-12 18:16:10
                                                                                                              (ADDRESS_LIST =
                                                                                                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3
                                                                                                       9)(PORT = 1521))
                                                                                                              )
                                                                                                              (CONNECT_DATA =
                                                                                                               (SERVICE_NAME = esbdb)
                                                                                                              )

12,不通通过db link对远端数据库进行DDL操作,否则报错
SQL> grant select on syn_t_test to user_zxy;
grant select on syn_t_test to user_zxy
                *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

[oracle@mygirl admin]$ oerr ora 2021
02021, 00000, “DDL operations are not allowed on a remote database”
// *Cause: An attempt was made to use a DDL operation on a remote database.
//         For example, “CREATE TABLE tablename@remotedbname …”.
// *Action: To alter the remote database structure, you must connect to the
//          remote database with the appropriate privileges.

测试明细
1,db link使用方之数据库版本
SQL> select * from v$version where rownum=1;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

2,db link使用方之数据库实例名称以及IP地址
SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      mygirl

[root@mygirl ~]# ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 08:00:27:E6:9A:3B  
          inet addr:10.0.0.5  Bcast:10.255.255.255  Mask:255.0.0.0
          inet6 addr: fe80::a00:27ff:fee6:9a3b/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:532 errors:0 dropped:0 overruns:0 frame:0
          TX packets:287 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:48493 (47.3 KiB)  TX bytes:38727 (37.8 KiB)

3,db link提供方之数据库版本以及数据库实例名称以及IP地址和数据库用户名称

SQL> select * from v$version where rownum=1;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      esbdb

SQL> select username from dba_users where username=’USER_ZXY';

USERNAME
——————————
USER_ZXY

SQL> select tname from tab;

no rows selected

SQL> create table t_test(a int);

Table created.

SQL> insert into t_test values(1);

1 row created.

SQL> commit;

Commit complete.

suse11:~ # ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 08:00:27:81:B6:5A  
          inet addr:10.0.0.39  Bcast:10.0.0.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe81:b65a/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:774 errors:0 dropped:0 overruns:0 frame:0
          TX packets:449 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:69213 (67.5 Kb)  TX bytes:59159 (57.7 Kb)

4,db link提供方之数据库监听运行状态
oracle@suse11:~> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 12-JUN-2017 18:01:52

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 – Production
Start Date                12-JUN-2017 18:01:40
Uptime                    0 days 0 hr. 0 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /oracle/diag/tnslsnr/suse11/listener/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse11)(PORT=1521)))
Services Summary…
Service “esbdb” has 1 instance(s).
  Instance “esbdb”, status READY, has 1 handler(s) for this service…
Service “esbdbXDB” has 1 instance(s).
  Instance “esbdb”, status READY, has 1 handler(s) for this service…
The command completed successfully     

5,在db link使用方直接通过不配置数据库TNSNAMES.ORA访问远端数据库

SQL> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select owner,db_link,username,host,created from dba_db_links;  

no rows selected

SQL> show user
USER is “SYSTEM”

SQL> create database link target_user_zxy connect to user_zxy identified by system 
  2  using ‘(DESCRIPTION = 
  3         (ADDRESS_LIST = 
  4         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521)) 
  5         ) 
  6         (CONNECT_DATA = 
  7          (SERVICE_NAME = esbdb) 
  8         )';

Database link created.

SQL> conn /as sysdba
Connected.

SQL> set linesize 300
SQL> col username for a40
SQL> col db_link for a30
sql> col host for a50
SQL> select owner,db_link,username,host,created from dba_db_links;

OWNER                          DB_LINK                        USERNAME                                 HOST                                               CREATED
—————————— —————————— —————————————- ————————————————– ——————-
SYSTEM                         TARGET_USER_ZXY                USER_ZXY                                 (DESCRIPTION =                                     2017-06-12 18:16:10
                                                                                                              (ADDRESS_LIST =
                                                                                                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3
                                                                                                       9)(PORT = 1521))
                                                                                                              )
                                                                                                              (CONNECT_DATA =
                                                                                                               (SERVICE_NAME = esbdb)
                                                                                                              )

SQL> 

6,在DB LINK使用方验证DB LINK是否正常
SQL> select count(*) from t_test@target_user_zxy;
select count(*) from t_test@target_user_zxy
                            *
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> select count(*) from user_zxy.t_test@target_user_zxy;
select count(*) from user_zxy.t_test@target_user_zxy
                                     *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified

为了诊断上述的错误,尝试采用反推方式即通过图形化netgmr
[oracle@mygirl admin]$ more tnsnames.ora 
# tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TNS_ESBDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = esbdb)
    )
  )

[oracle@mygirl admin]$ sqlplus user_zxy/system@tns_esbdb

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 12 18:31:02 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> drop database link target_user_zxy;

Database link dropped.

SQL> create database link target_user_zxy connect to user_zxy identified by system 
  2  using ‘(DESCRIPTION =
  3      (ADDRESS_LIST =
  4        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521))
  5      )
  6      (CONNECT_DATA =
  7        (SERVICE_NAME = esbdb)
  8      )
  9    )';

Database link created.

SQL> select * from t_test@target_user_zxy;

         A
———-
         1

7,在db link使用方直接通过配置数据库TNSNAMES.ORA访问远端数据库

SQL> show user
USER is “SYSTEM”

SQL> drop database link target_user_zxy;

Database link dropped.

SQL> create database link target_user_zxy connect to user_zxy identified by system  using ‘tns_esbdb';

Database link created.

SQL> select * from t_test@target_user_zxy;

         A
———-
         1

SQL> set linesize 300
SQL> col username for a40
SQL> col db_link for a30
SQL> col host for a50
SQL> select owner,db_link,username,host,created from dba_db_links;

OWNER                          DB_LINK                        USERNAME                                 HOST                                               CREATED
—————————— —————————— —————————————- ————————————————– ———
SYSTEM                         TARGET_USER_ZXY                USER_ZXY                                 tns_esbdb                                          12-JUN-17

SQL> 

8,可见在某个数据库用户创建的db link,其它数据库用户则不能使用它
SQL> conn /as sysdba 
Connected.
SQL> select * from t_test@target_user_zxy;
select * from t_test@target_user_zxy
                     *
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> conn user_zxy/system
Connected.
SQL> select * from t_test@target_user_zxy;
select * from t_test@target_user_zxy
                     *
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> select username from dba_users where account_status=’OPEN';

USERNAME
—————————————-
SYSTEM
SYS
USER_MOTHER
USER_ZXY
USER_FATHER
USER_OBJ

6 rows selected.

9,可见创建public database db link,创建db link的数据库用户与非数据库用户皆可访问使用db link

SQL> conn system/system
Connected.
SQL> drop database link target_user_zxy;

Database link dropped.

SQL> create public database link target_user_zxy connect to user_zxy identified by system  using ‘tns_esbdb';

Database link created.

SQL> show  user
USER is “SYSTEM”
SQL> select * from t_test@target_user_zxy;

         A
———-
         1

SQL> 
SQL> 
SQL> conn /as sysdba 
Connected.
SQL> select * from t_test@target_user_zxy;

         A
———-
         1

SQL> 
SQL> conn user_zxy/system
Connected.
SQL> select * from t_test@target_user_zxy;

         A
———-
         1

10,可见非public database db link只能创建自己的数据库用户访问,即使授权了其它数据库用户更强大的权利,还是不成功;
    通过同义词同上,亦不成功
SQL> conn system/system
Connected.
SQL> drop database link target_user_zxy;
drop database link target_user_zxy
                   *
ERROR at line 1:
ORA-02024: database link not found

SQL> drop public database link target_user_zxy;

Database link dropped.

SQL> create  database link target_user_zxy connect to user_zxy identified by system  using ‘tns_esbdb';

Database link created.

SQL> select * from t_test@target_user_zxy;

         A
———-
         1

SQL> conn user_zxy/system
Connected.
SQL> select * from t_test@target_user_zxy;
select * from t_test@target_user_zxy
                     *
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> conn /as sysdba
Connected.

SQL>  select distinct privilege from dba_sys_privs where lower(privilege) like ‘%link%';

PRIVILEGE
—————————————-
DROP PUBLIC DATABASE LINK
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK

SQL> select distinct privilege from dba_tab_privs where lower(privilege) like ‘%link%';

no rows selected

SQL> conn system/system
Connected.

SQL> create synonym syn_t_test for t_test@target_user_zxy;

Synonym created.

SQL> select * from syn_t_test;

         A
———-
         1

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

[oracle@mygirl admin]$ oerr ora 2021
02021, 00000, “DDL operations are not allowed on a remote database”
// *Cause: An attempt was made to use a DDL operation on a remote database.
//         For example, “CREATE TABLE tablename@remotedbname …”.
// *Action: To alter the remote database structure, you must connect to the
//          remote database with the appropriate privileges.

SQL> select distinct privilege from dba_sys_privs where lower(privilege) like ‘%syno%';

PRIVILEGE
—————————————-
CREATE SYNONYM
DROP ANY SYNONYM
CREATE ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM

SQL> select distinct privilege from dba_tab_privs where lower(privilege) like ‘%syno%';

no rows selected

SQL> conn /as sysdba
Connected.

SQL> grant select any table to user_zxy;

Grant succeeded.

SQL> conn user_zxy/system
Connected.

SQL> select * from system.syn_t_test;
select * from system.syn_t_test
                     *
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> conn /as sysdba
Connected.
SQL> grant dba to user_zxy;

Grant succeeded.

SQL> conn user_zxy/system
Connected.
SQL> select * from system.syn_t_test;
select * from system.syn_t_test
                     *
ERROR at line 1:
ORA-02019: connection description for remote database not found

PS:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处:Harries Blog™ » oracle 11.2.0.4之oracle database db link之测试明细之一

赞 (0)

分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址