运维首页 > 数据运维 > 不知密码的情况下如何重建db_link？

不知密码的情况下如何重建db_link？

2023年 12月 7日 43.9k 0

如题：不知密码的情况下如何重建db_link？

处理过程

1.导出dmp

[oracle@lncs dmp]$ expdp '/ as sysdba' directory=ORADMP cluster=n dumpfile=d.dmp logfile=d.log parallel=1 content=metadata_only include=db_link full=y

Export: Release 11.2.0.4.0 - Production on Tue Dec 5 15:39:33 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=ORADMP cluster=n dumpfile=d.dmp logfile=d.log parallel=1 content=metadata_only include=db_link full=y
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/oracle/dmp/d.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Tue Dec 5 15:40:10 2023 elapsed 0 00:00:36

检查导出的db_link脚本是否符合预期？

[oracle@lncs dmp]$ impdp '/ as sysdba' directory=ORADMP cluster=n dumpfile=d.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link sqlfile=d.sql full=y

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 15:40:27 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=ORADMP cluster=n dumpfile=d.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link sqlfile=d.sql full=y
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Dec 5 15:40:30 2023 elapsed 0 00:00:03

[oracle@lncs dmp]$ more d.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
-- CONNECT GISTAR
CREATE DATABASE LINK "LNDB57"
CONNECT TO "GISTAR" IDENTIFIED BY VALUES ':1'
USING 'lndb57';
-- CONNECT SYS
CREATE PUBLIC DATABASE LINK "SMSDB"
CONNECT TO "UOP_OCS" IDENTIFIED BY VALUES ':1'
USING 'lndb57';
CREATE PUBLIC DATABASE LINK "ZYGL"
CONNECT TO "ZYGL" IDENTIFIED BY VALUES ':1'
USING 'lndb57';
-- CONNECT QUEST
CREATE DATABASE LINK "QUEST_SOO_SCZY1"
USING 'sczy1';
CREATE DATABASE LINK "QUEST_SOO_SCZY2"
USING 'sczy2';

2.删除dblink

注意：不是自己的dblink是不能删除的，所以需要这样：

1)sys用户下创建删除的存储过程：


create or replace procedure drop_dblink(schemaName varchar2, dbLink varchar2 ) is
plsql varchar2(1000);
cur number;
uid number;
rc number;
begin
select u.user_id into uid from dba_users u
where u.username = schemaName;
plsql := 'drop database link "'||dbLink||'"';
cur := SYS.DBMS_SYS_SQL.open_cursor;
SYS.DBMS_SYS_SQL.parse_as_user(
c => cur,
statement => plsql,
language_flag => DBMS_SQL.native,
userID => uid
);
rc := SYS.DBMS_SYS_SQL.execute(cur);
SYS.DBMS_SYS_SQL.close_cursor(cur);
end;
/

2)执行删除操作：

SQL> exec drop_dblink('QUEST','QUEST_SOO_SCZY1');

PL/SQL procedure successfully completed.

3)删掉存储过程(临时用的过程记得安全还原)：

drop procedure drop_dblink;

3.导入dblink

[oracle@lncs dmp]$ impdp '/ as sysdba' directory=ORADMP cluster=n dumpfile=d.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link full=y

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 15:53:00 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=ORADMP cluster=n dumpfile=d.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link full=y
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
ORA-31684: Object type DB_LINK:"GISTAR"."LNDB57" already exists
ORA-31684: Object type DB_LINK:"PUBLIC"."SMSDB" already exists
ORA-31684: Object type DB_LINK:"PUBLIC"."ZYGL" already exists
Job "SYS"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Tue Dec 5 15:53:04 2023 elapsed 0 00:00:02

当然导入的时候不一定用sys用户，有权限的用户也是可以的，比如impdp jyc/jyc这样

检查ok:

提示：活学活用，不用sys用户导入导出也是可以的，测试如下：


[oracle@lncs dmp]$ expdp jyc/jyc directory=ORADMP cluster=n dumpfile=d2.dmp logfile=d.log parallel=1 content=metadata_only include=db_link full=y

Export: Release 11.2.0.4.0 - Production on Tue Dec 5 16:14:08 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JYC"."SYS_EXPORT_FULL_01": jyc/******** directory=ORADMP cluster=n dumpfile=d2.dmp logfile=d.log parallel=1 content=metadata_only include=db_link full=y
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "JYC"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_FULL_01 is:
/oracle/dmp/d2.dmp
Job "JYC"."SYS_EXPORT_FULL_01" successfully completed at Tue Dec 5 16:14:15 2023 elapsed 0 00:00:06

[oracle@lncs dmp]$ expdp jyc/jyc directory=ORADMP cluster=n dumpfile=d3.dmp logfile=d.log parallel=1 include=db_link full=y

Export: Release 11.2.0.4.0 - Production on Tue Dec 5 16:14:29 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JYC"."SYS_EXPORT_FULL_01": jyc/******** directory=ORADMP cluster=n dumpfile=d3.dmp logfile=d.log parallel=1 include=db_link full=y
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "JYC"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_FULL_01 is:
/oracle/dmp/d3.dmp
Job "JYC"."SYS_EXPORT_FULL_01" successfully completed at Tue Dec 5 16:14:37 2023 elapsed 0 00:00:08

[oracle@lncs dmp]$ ll d*.dmp
-rw-r----- 1 oracle oinstall 233472 Dec 5 16:14 d2.dmp
-rw-r----- 1 oracle oinstall 233472 Dec 5 16:14 d3.dmp
-rw-r----- 1 oracle oinstall 233472 Dec 5 15:40 d.dmp
[oracle@lncs dmp]$ impdp jyc/jyc directory=ORADMP cluster=n dumpfile=d2.dmp sqlfile=d2.sql logfile=d1.log parallel=1 content=metadata_only include=db_link full=y

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 16:15:19 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "JYC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "JYC"."SYS_SQL_FILE_FULL_01": jyc/******** directory=ORADMP cluster=n dumpfile=d2.dmp sqlfile=d2.sql logfile=d1.log parallel=1 content=metadata_only include=db_link full=y
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "JYC"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Dec 5 16:15:22 2023 elapsed 0 00:00:01

[oracle@lncs dmp]$ more d2.sql
-- CONNECT JYC
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
-- CONNECT GISTAR
CREATE DATABASE LINK "LNDB57"
CONNECT TO "GISTAR" IDENTIFIED BY VALUES ':1'
USING 'lndb57';
-- CONNECT JYC
CREATE PUBLIC DATABASE LINK "SMSDB"
CONNECT TO "UOP_OCS" IDENTIFIED BY VALUES ':1'
USING 'lndb57';
CREATE PUBLIC DATABASE LINK "ZYGL"
CONNECT TO "ZYGL" IDENTIFIED BY VALUES ':1'
USING 'lndb57';
-- CONNECT QUEST
CREATE DATABASE LINK "QUEST_SOO_SCZY1"
USING 'sczy1';
CREATE DATABASE LINK "QUEST_SOO_SCZY2"
USING 'sczy2';
[oracle@lncs dmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 5 16:15:48 2023

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop PUBLIC DATABASE LINK "SMSDB";

Database link dropped.

SQL> exec drop_dblink('QUEST','QUEST_SOO_SCZY1');

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lncs dmp]$ impdp jyc/jyc directory=ORADMP cluster=n dumpfile=d2.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link full=y

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 16:16:22 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "JYC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_FULL_01": jyc/******** directory=ORADMP cluster=n dumpfile=d2.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link full=y
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
ORA-31684: Object type DB_LINK:"GISTAR"."LNDB57" already exists
ORA-31684: Object type DB_LINK:"PUBLIC"."ZYGL" already exists
ORA-31684: Object type DB_LINK:"QUEST"."QUEST_SOO_SCZY2" already exists
Job "JYC"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Tue Dec 5 16:16:27 2023 elapsed 0 00:00:04

爱可生开源社区
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

爱可生开源社区

这个人很懒，什么都没有留下～

文章2708 评论0