数据泵+DBLINK迁移用户

最新推荐文章于 2024-08-08 10:57:18 发布

甲骨园

最新推荐文章于 2024-08-08 10:57:18 发布

阅读量929

点赞数

文章标签：数据库 oracle 迁移

本文链接：https://blog.csdn.net/zx_canway/article/details/72677756

版权

客户有一个需求，是将一个库中的某个用户迁移到一台新的oracle服务器上，因数据量较小，并且不涉及版本的升级，所以可以采用创建一个dblink，然后通过这个dblink直接从源库将用户数据导出并导入到新库中。

为了防止现场发生意外，因此先自己搭建一套环境进行测试，环境如下：

源库：192.168.56.100数据库：orcl 连接名：orcl1 导出用户：test

新库：192.168.56.40数据库：orcl 连接名：ogg

实施步骤：

配置源库和新库的tnsnames.ora文件，使其可以互连，这里源库和新库的tnsnames.ora文件内容相同：

[oracle@Abbott admin]$ vi tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

OGG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

2. 测试连接：

[oracle@Abbott admin]$ sqlplus sys/oracle@ogg as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 24 16:01:36 2017

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>

3. 新库创建dblink，这里用sys用户登录，创建了一个public的dblink：

SQL> create public database link sjdr connect to test identified by test using 'orcl1';

Database link created.

4. 在源库授予导出用户test执行数据泵的权限：

[oracle@Abbott admin]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 24 16:30:58 2017

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> grant EXP_FULL_DATABASE to test;

Grant succeeded.

注：如果未授予该权限，在导入导出的时候将报如下错误：

[oracle@ora-ogg admin]$ impdp system/oracle schemas=TEST network_link=sjdr

Import: Release 11.2.0.4.0 - Production on Wed May 24 16:24:25 2017

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
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

5. 在新库中创建test用户所对应的表空间及数据文件：

SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 20m autoextend on;

Tablespace created

6. 执行导入导出：

[oracle@ora-ogg admin]$ impdp system/oracle schemas=TEST network_link=sjdr

Import: Release 11.2.0.4.0 - Production on Wed May 24 16:33:16 2017

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 "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** schemas=TEST network_link=sjdr 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "TEST"."T1"                                      1 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed May 24 16:33:30 2017 elapsed 0 00:00:14

7. 检查结果：

[oracle@ora-ogg admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 24 16:33:40 2017

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> select * from test.t1;

        ID NAME
---------- --------------------
         1 zx

8. 只导用户表结构，不包含表数据，并且将test用户表结构及对象导入到abbott用户下，修改test用户表空间到abbott表空间：

[oracle@ora-ogg admin]$ impdp system/oracle remap_schema=test:abbott remap_tablespace=test:abbott network_link=impabbott content=metadata_only