DB link的迁移(dblink中的密码未知),dbdblink
不知DBA有时候是否会遇到这样的尬尴局面,做某些Schema的迁移的时候,由于用到Public的db link,然而由于不知道db link中目标端账号的密码,因此无法在新环境重新创建DB link。本次实验的思路是将视图dba_db_links的基表link$迁移到出来并替换到新环境。因为用户的password密文保存在link$基表中。
----首先交代两边环境,都是Linux上11.2.0.3的版本,在10.2.0.4上也测试过。 一边数据库seven,一边数据库ginna。假设在不知道scott的密码情况下将seven端的名为test的db link迁移至ginna端。
源端操作: 首先还在源端创建一个public 的db link 名为test。
seven >create public database link test connect to scott identified by tiger using 'ginna'; Database link created. seven >select * from global_name@test; GLOBAL_NAME --------------------- GINNA seven >select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------- PUBLIC TEST SCOTT ginna 06-MAR-15
创建中间表trans1,CTAS基表sys.link$。
seven >create table trans1 as select * from sys.link$; Table created. seven >select count(*) from trans1; COUNT(*) ---------- 1
然后在seven用户下创建第二个中间表trans2,(seven 有dba权限)。
seven >conn seven/oracle Connected. seven >create table trans2 as select * from sys.link$; -----当然不能直接CTAS基表的。 create table trans2 as select * from sys.link$ * ERROR at line 1: ORA-01031: insufficient privileges seven >create table trans2 as select * from sys.trans1; Table created. seven >select count(*) from trans2; COUNT(*) ---------- 1 seven >
OK,中间表trans2创建成功。
目标端操作: 在目标端创建名为test2的Public db link。
ginna >show user USER is "SYS" ginna >select * from dba_db_links; no rows selected ginna >create public database link test2 connect to seven identified by oracle using 'seven'; Database link created. ginna >select * from global_name@test2; GLOBAL_NAME ---------------------- SEVEN ginna >set line 200 ginna >col db_link for a30 ginna >col host for a30 ginna >select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------- PUBLIC TEST2 SEVEN seven 06-MAR-15
通过test2的db link将seven端的trans2表CTAS到表trans3。
ginna >create table trans3 as select * from trans2@test2; Table created. ginna >select count(*) from trans3; COUNT(*) ---------- 1
truncate掉基表sys.link$,为了安全起见也可以先备份下基表,并将表trans3插入到基表link$中。
ginna >truncate table sys.link$; Table truncated. ginna >insert into sys.link$ select * from trans3; 1 row created. ginna >commit; Commit complete.
现在再查一下dba_db_links就可以发现名为test的db link已经成功迁移在ginna端。
ginna >select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------- PUBLIC TEST SCOTT ginna 06-MAR-15
测试db link 的有效性:
ginna >select * from global_name@test; GLOBAL_NAME ------------------ GINNA
OK,现在还有一个问题,之前名为test2的db link现在还是否存在?是否能重建? 显然是不能的:
ginna >create public database link test2 connect to seven identified by oracle using 'seven'; create public database link test2 connect to seven identified by oracle using 'seven' * ERROR at line 1: ORA-02011: duplicate database link name
前提需要刷新下shared pool。之后再重建就可以了。
ginna >alter system flush shared_pool; System altered. ginna >create public database link test2 connect to seven identified by oracle using 'seven'; Database link created. ginna >select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------- PUBLIC TEST SCOTT ginna 06-MAR-15 PUBLIC TEST2 SEVEN seven 06-MAR-15 ginna >
当然如果不flush shared pool的话直接删除db link则会遭遇ora-600内部报错:
SQL> drop public database link test2; drop public database link test2 * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [16500], [kqdDBLinkUpdate], [3], [0], [TEST2], [], [], [], [], [], [], [] Process ID: 11165 Session ID: 152 Serial number: 359 SQL> SQL> create public database link test2 connect to seven identified by oracle using 'seven'; ERROR: ORA-03114: not connected to ORACLE SQL> conn / as sysdba Connected. SQL> create public database link test2 connect to seven identified by oracle using 'seven'; Database link created. SQL> drop public database link test2; Database link dropped. SQL>
当然我们也可以不创建test2这个db link,主要就是将trans1这张中间表迁移至ginna库就可以了,这有很多方法,可以直接使用exp/imp或者数据泵都可以。条条大道通罗马,方法有很多种,主要就是将seven端的sys.link$基表迁移至ginna端并替换掉ginna端的sys.link$。
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。