Oracle 12c 多租户 CDB 与 PDB 级别 expdb 与 impdb(表、用户、全库)

时间:2022-06-06 21:31:00
Oracle 数据库 12 c 多租户下,如何在容器数据库 (CDB) 和可插拔数据库 (PDB) 中使用 expdb 与 impdp (数据泵) 呢? 我们一起探讨下
PDB 下进行表级,用户级别,全库(PDB)导出 与 CDB 下进行表级,用户级别,全库(CDB)导出。导入类比导出即可。

实验环境:

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
CORE    12.2.0.1.0      Production
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


一、 PDB级别数据泵

1.PDB表导出
[oracle@12c01 ~]$ sqlplus sys/oracle@10.100.25.16:1521/pdb01 as sysdba
SQL> create table c##andy.pdb_expdb as select object_name,object_id from dba_objects order by object_id fetch first 1 percent rows only;
Table created.
SQL> create directory datapump as '/home/oracle/datadump/';
Directory created.
SQL> grant dba to c##andy;
Grant succeeded.
SQL> commit;
Commit complete.
[oracle@12c01 ~]$ mkdir -p /home/oracle/datadump/
[oracle@12c01 ~]$$ expdp c##andy/andy@10.100.25.16:1521/pdb01 directory=datapump tables=pdb_expdb dumpfile=pdb_expdb.dump logfile=export.log

Starting "C##ANDY"."SYS_EXPORT_TABLE_01":  c##andy/********@10.100.25.16:1521/pdb01 directory=datapump tables=pdb_expdb dumpfile=pdb_expdb.dump logfile=export.log 
.......
Job "C##ANDY"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 10 04:27:47 2017 elapsed 0 00:00:37

2.PDB用户导出
[oracle@12c01 ~]$ expdp c##andy/andy@10.100.25.16:1521/pdb01 directory=datapump schemas=c##andy dumpfile=pdb01schemal.dump logfile=export.log

Starting "C##ANDY"."SYS_EXPORT_SCHEMA_01":  c##andy/********@10.100.25.16:1521/pdb01 directory=datapump schemas=c##andy dumpfile=pdb01schemal.dump logfile=export.log
....
Job "C##ANDY"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 10 04:46:47 2017 elapsed 0 00:00:53

3.PDB全库导出
[oracle@12c01 ~]$ expdp c##andy/andy@10.100.25.16:1521/pdb01 directory=datapump full=y dumpfile=pdb01full.dump logfile=export.log

Starting "C##ANDY"."SYS_EXPORT_FULL_01":  c##andy/********@10.100.25.16:1521/pdb01 directory=datapump full=y dumpfile=pdb01full.dump logfile=export.log 
.....
Job "C##ANDY"."SYS_EXPORT_FULL_01" successfully completed at Wed May 10 04:31:53 2017 elapsed 0 00:02:32

二、 CDB级别数据泵

1.CDB表导出
[oracle@12c01 datadump]$ sqlplus / as sysdba
SQL>create table c##andy.cdb_expdb as select object_name,object_id from dba_objects order by object_id fetch first 1 percent rows only;
SQL> grant dba to c##andy;
Grant succeeded.
创建directory
SQL> create directory datapump as '/home/oracle/datadump/';
Directory created.
[oracle@12c01 ~]$expdp c##andy/andy@10.100.25.16:1521/andycdb directory=datapump tables=cdb_expdb dumpfile=cdb_expdb.dump logfile=export.log

Starting "C##ANDY"."SYS_EXPORT_TABLE_01":  c##andy/********@10.100.25.16:1521/andycdb directory=datapump tables=cdb_expdb dumpfile=cdb_expdb.dump logfile=export.log
....
Job "C##ANDY"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 10 04:54:39 2017 elapsed 0 00:00:25

2.CDB用户导出
[oracle@12c01 ~]$ expdp c##andy/andy@10.100.25.16:1521/andycdb directory=datapump schemas=c##andy dumpfile=andycdbschemal.dump logfile=export.log

Starting "C##ANDY"."SYS_EXPORT_SCHEMA_01":  c##andy/********@10.100.25.16:1521/andycdb directory=datapump schemas=c##andy dumpfile=andycdbschemal.dump logfile=export.log 
....
Job "C##ANDY"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 10 04:59:55 2017 elapsed 0 00:01:10

3.导出整个CDB     
[oracle@dg2 ~]$ expdp c##andy/andy@10.100.25.16:1521/andycdb directory=datapump full=y dumpfile=andycdbfull.dump logfile=export.log

Starting "C##ANDY"."SYS_EXPORT_FULL_01":  c##andy/********@10.100.25.16:1521/andycdb directory=datapump full=y dumpfile=andycdbfull.dump logfile=export.log 
....
Job "C##ANDY"."SYS_EXPORT_FULL_01" successfully completed at Wed May 10 05:06:17 2017 elapsed 0 00:03:01

总结:
 CDB 与 PDB 级别的导入或导出,只要在连接时 指定 CDB 或 PDB 的服务名即可。