首页 > 代码库 > oracle 使用db_link 导入导出小结

oracle 使用db_link 导入导出小结

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

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

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

新库:192.168.56.40  ora-ogg  数据库:orcl 连接名:ogg   创建用户:abbott

实施步骤:

  1. 配置源库和新库的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. 测试连接,从源库使用abbott用户连接到新库上,或者从新库用test用户连接到源库上:

    [oracle@Abbott admin]$ sqlplus abbott/abbott@ogg
      
    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=sjdr content=metadata_only   

    9. 新库中abbott用户默认表空间为abbott,如果未指定remap_tablespace,而只是指定remap_schema为abbott,那么导入后对象仍然在TEST表空间中:

    [oracle@Abbott ~]$ impdp system/oracle remap_schema=test:abbott network_link=impabbott
    
    Import: Release 11.2.0.4.0 - Production on Thu May 25 23:26:00 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/******** remap_schema=test:abbott network_link=impabbott 
    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
    ORA-31684: Object type USER:"ABBOTT" already exists
    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 "ABBOTT"."T1"                                    1 rows
    Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
    Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
    Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
    ORA-39082: Object type ALTER_FUNCTION:"ABBOTT"."VERIFY_FUNCTION_11G" created with compilation warnings
    Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
    ORA-39082: Object type ALTER_PROCEDURE:"ABBOTT"."MM" created with compilation warnings
    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at Thu May 25 23:26:12 2017 elapsed 0 00:00:11
    
    
    
    [oracle@Abbott ~]$ sqlplus / as sysdba
    
    SQL> conn abbott/abbott 
    SQL> select * from t1;
    
            ID NAME
    ---------- --------------------
             1 zx
     
    SQL> select segment_name,tablespace_name from dba_segments where owner=‘ABBOTT‘ and segment_name=‘T1‘;
    
    SEGMENT_NAME            TABLESPACE_NAME
    -----------------------------------------
    T1                        TEST
    
    SQL> select segment_name,tablespace_name from dba_segments where segment_name=‘T1‘; #一个test.T1 一个Abbott.T1
    
    SEGMENT_NAME            TABLESPACE_NAME
    -----------------------------------------------
    T1                        TEST
    T1                        TEST

    10. 使用sys无密码导入导出:

    [oracle@Abbott ~]$ impdp \/ as sysdba\ directory=impdir dumpfile=full.dmp logfile=testim2.log remap_schema=test:abbott

    11.用户密码包含特殊符号的导入导出

  2. [oracle@Abbott ~]$ impdp \sys/\"oracle@#123\" as sysdba\ directory=impdir dumpfile=full.dmp logfile=testim2.log

oracle 使用db_link 导入导出小结