Oracle12c資料庫連結匯出與刪除(DatabaseLink)

前陣子在漏洞掃描後，有些暫時不再使用的資料庫連結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

五、參考連結

刪除資料庫連結 http://www.oracle-ckpt.com/drop-db_links-of-a-private-user-from-sys/
expdp impdp中 exclude/include 的使用