12c pdb使用数据泵导入导出

原创风声 2020-11-17

2401

1.查看当前的SID，查看pdb并切换到容器数据库,这里的pluggable数据库是orcl

[oracle@orcl ~]$ echo $ORACLE_SID
zzorcl
[oracle@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 3 16:40:56 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
SQL> 
复制

2.切换到插件库

SQL> alter session set container = orcl;

Session altered.
复制

3.可以自己创建数据泵目录，也可以用oracle默认目录，这里以目录dp_dir，路径为oracle家目录

SQL> create or replace directory dp_dir as  '/home/oracle';

Directory created.

SQL> exit
复制

4.授予dp用户在数据泵路径有读写权限

SQL> grant read,write on directory dp_dir to dp;
Grant succeeded.
复制

5.设置tnsnames.ora，增加pdborcl。SERVICE_NAME为orcl,下面是我在TNSNAMES.ora 里加的

pdbORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zzorcl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
  ZZORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zzorcl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zzorcl)
    )
  )

LISTENER_ZSQ =
  (ADDRESS = (PROTOCOL = TCP)(HOST =zzorcl)(PORT = 1521))
复制

6.数据泵导出、导入(scott为例子)

expdp dp/dp@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl.log schemas=scott


impdp dp/dp@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl_imp.log schemas=scott
复制

oracle