Oracle 12c 数据库链接导出与删除(Database Link)

电玩女神 2022-05-28 02:35 240阅读 0赞

前阵子在漏洞扫描后，有些暂时不再使用的数据库链接Database Link需要删除。出于万一后续需要再用的情况考虑，于是乎先备份这些Database Link。首先让我想到的是直接生成DDL就行。事实上这DDL并不包含链接用户的密码。此路不通，所以就只能考虑用expdp工具来进行备份了。其次由于有些数据库用户的密码未知，因此这些用户创建的数据库链接在sys账号下无法删除。下文则是这些个问题的描述与解决。

## 一、环境准备 ##

12c中包含了2个pdb数据库，分别是cdb1pdb1, cdb1pdb2
    其中在cdb1pdb1上有一个db link指向了cdb1pdb2
    
    --演示环境
    SQL> select * from v$version;
    
    BANNER                                                                              CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
    PL/SQL Release 12.2.0.1.0 - Production                                                    0
    
    --在cdb1pdb2上创建用户，用户配置从cdb1pdb1访问
    SQL> alter session set container=cdb1pdb2;
    
    SQL> create user robin identified by xxx;
    
    SQL> grant dba,connect,resource to robin;
    
    $ sqlplus robin/xxx@cdb1pdb2
    
    SQL> create table blog(ename varchar2(20),addr varchar2(60));
    
    SQL> insert into blog values('leshami','http://blog.csdn.net/leshami');
    
    SQL> commit;
    
    --连接到cdb1pdb1，此时我们使用了hr账号，密码已知。
    --真实的环境类似用户密码我们是未知的，此处演示。
    $ sqlplus hr/hr@cdb1pdb1
    SQL> show user;
    USER is "HR"
    
    --创建到cdb1pdb2的数据库链接 
    SQL> create database link to_cdb1pdb2 connect to robin identified by xxx using 'CDB1PDB2';
    
    SQL> select * from blog@to_cdb1pdb2;
    
    ENAME                ADDR
    -------------------- ------------------------------------------------------------
    leshami              http://blog.csdn.net/leshami

## 二、备份数据库链接(提取DDL，以及导出DB Link) ##

通过get_ddl方式导出DB Link
    $ sqlplus / as sysdba
    
    SQL> select db_link,username from cdb_db_links where owner='HR';
    
    DB_LINK                        USERNAME
    ------------------------------ ----------------------------------------
    TO_CDB1PDB2.YDQ05.COM          ROBIN
    
    SQL> alter session set container=cdb1pdb1;
    
    SQL> alter session set current_schema=hr;
    
    --通过get_ddl函数提取DDL，如下，我们看到密码部分为一个绑定变量
    --很显然，这个没有起到绝对备份的作用
    SQL> set long 5000
    SQL> select dbms_metadata.get_ddl('DB_LINK','TO_CDB1PDB2.YDQ05.COM','HR') FROM DUAL;
    
    DBMS_METADATA.GET_DDL('DB_LINK','TO_CDB1PDB2.YDQ05.COM','HR')
    --------------------------------------------------------------------------------
    
      CREATE DATABASE LINK "TO_CDB1PDB2.YDQ05.COM"
      CONNECT TO "ROBIN" IDENTIFIED BY VALUES ':1'
      USING 'CDB1PDB2'
    
    通过expdp方式导出DB Link
    $ vi prfile.par 
    directory=DATA_PUMP_DIR
    dumpfile=hrdblink.dmp
    logfile=exp_dblink.log
    schemas=hr
    INCLUDE=DB_LINK:"LIKE 'TO_CDB1PDB2.YDQ05.COM'" 
    
    $ expdp parfile=prfile.par                                                                              
    
    Export: Release 12.2.0.1.0 - Production on Tue Mar 27 17:38:11 2018
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Username: sys@cdb1pdb1 as sysdba 
    Password: 
    
    Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/********@cdb1pdb1 AS SYSDBA parfile=prfile.par 
    Processing object type SCHEMA_EXPORT/DB_LINK
    Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
      /app/oracle/ora12c/admin/cdb1/dpdump/50DDF77203BA2CCBE053F401A8C03639/hrdblink.dmp
    Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Mar 27 17:38:29 2018 elapsed 0 00:00:07

## 三、删除数据库链接(DB Link) ##

--此处模拟我们不知道创建数据库链接的用户名和密码，所以用sys登陆
    SQL> show user;
    USER is "SYS"
    
    SQL> show con_name;
    
    CON_NAME
    ------------------------------
    CDB1PDB1
    
    SQL> alter session set current_schema=hr;
    
    --以下操作均无法删除DB Link，即使带上owner也不行
    SQL> drop database link to_cdb1pdb2;
    drop database link to_cdb1pdb2
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    
    SQL> drop database link TO_CDB1PDB2.YDQ05.COM;
    drop database link TO_CDB1PDB2.YDQ05.COM
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    
    --下面通过一个专用的存储过程来搞定
    --关于这个使用sys删除其他用户下的dblink，感谢steve.tang支持提供了个参考链接
    SQL> exec DROP_DBLINK('HR','TO_CDB1PDB2.YDQ05.COM');
    
    PL/SQL procedure successfully completed.
    
    SQL> select db_link,username from cdb_db_links where owner='HR';
    
    no rows selected

## 四、恢复数据库链接(DB Link) ##

$ impdp directory=DATA_PUMP_DIR dumpfile=hrdblink.dmp full=y
    
    Import: Release 12.2.0.1.0 - Production on Wed Mar 28 09:15:11 2018
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Username: sys@cdb1pdb1 as sysdba
    Password: 
    
    Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "SYS"."SYS_IMPORT_FULL_01":  sys/********@cdb1pdb1 AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hrdblink.dmp full=y 
    Processing object type SCHEMA_EXPORT/DB_LINK
    Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed Mar 28 09:15:27 2018 elapsed 0 00:00:03
    
    SQL> conn hr/hr@cdb1pdb1  
    
    SQL> select * from blog@to_cdb1pdb2;
    
    ENAME                ADDR
    -------------------- ------------------------------------------------------------
    leshami              http://blog.csdn.net/leshami

## 五、参考链接 ##

[删除数据库链接][Link 1]  
[expdp impdp中 exclude/include 的使用][expdp impdp_ exclude_include]

[Link 1]: http://www.oracle-ckpt.com/drop-db_links-of-a-private-user-from-sys/
[expdp impdp_ exclude_include]: https://blog.csdn.net/leshami/article/details/7034839