oracle 19c容器数据库data dump数据泵传输数据（3）---完全传输

目录

查看pdb1

创建pdb2

从pdb1 中导出元数据

在pdb2中导入元数据（dmp文件）

---

Full Transportable Export/Import: Example

只传输除了system，sysaux，temp，undo以外的用户表空间，這種方式傳輸的是用戶自定義的表空間，這種方式適用於资料量大的数据库，比前面我所讲的方式快。

数据文件+元数据

查看pdb1

SQL> show pdbs;

? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 4 PDB2 ?? ??? ??? ? ?READ WRITE NO
SQL> alter session set container=pdb1;

Session altered.
TABLESPACE_NAME ?? ? ? ? ? FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM?? ??? ??? ? ? ? ? /u01/app/oracle/oradata/CDB/pdb1/system01.dbf
SYSAUX?? ??? ??? ? ? ? ? /u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
UNDOTBS1?? ??? ? ? ? ? /u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf
USERS?? ??? ??? ? ? ? ? /u01/app/oracle/oradata/CDB/pdb1/users01.dbf

SQL> alter tablespace users read only;

Tablespace altered.
SQL> select username from all_users;------NICE用户是pdb1中的用户

USERNAME
--------------------------------------------------------------------------------
SYS
AUDSYS
SYSTEM
SYSBACKUP
SYSDG
SYSKM
SYSRAC
OUTLN
DBSFWUSER
XS$NULL
GSMADMIN_INTERNAL

USERNAME
--------------------------------------------------------------------------------
GSMUSER
DIP
REMOTE_SCHEDULER_AGENT
DBSNMP
ORACLE_OCM
SYS$UMF
APPQOSSYS
GSMCATUSER
GGSYS
XDB
ANONYMOUS

USERNAME
--------------------------------------------------------------------------------
WMSYS
OJVMSYS
CTXSYS
ORDSYS
ORDDATA
ORDPLUGINS
SI_INFORMTN_SCHEMA
MDSYS
OLAPSYS
MDDATA
DVSYS

USERNAME
--------------------------------------------------------------------------------
LBACSYS
DVF
PDBADMIN
HR
NICE

38 rows selected.

创建pdb2

SQL> show pdbs;

? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 2 PDB$SEED?? ??? ??? ? ?READ ONLY ?NO
?? ? 3 PDB1 ?? ??? ??? ? ?MOUNTED
?? ? 4 PDB2 ?? ??? ??? ? ?MOUNTED
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/system01.dbf
/u01/app/oracle/oradata/CDB/sysaux01.dbf
/u01/app/oracle/oradata/CDB/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/CDB/users01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/system01.dbf
/u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/users01.dbf

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf

14 rows selected.

SQL> alter session set container=pdb2;

Session altered.

SQL> startup;
Pluggable Database opened.
SQL> SELECT file_name, tablespace_name FROM dba_data_files;
SQL> col file_name for a50
SQL> set linesize 200
SQL> /

FILE_NAME?? ??? ??? ??? ??? ? ? TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf?? ? ? SYSTEM
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf?? ? ? SYSAUX
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf?? ? ? UNDOTBS1

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf

SQL> select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME ?? ? ? ? ? FILE_NAME
------------------------------ --------------------------------------------------
TEMP?? ??? ??? ? ? ? ? /u01/app/oracle/oradata/CDB/pdb2/temp012024-01-10_
?? ??? ??? ? ? ? ? 16-24-28-422-PM.dbf


SQL> show parameter create

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size 	     integer	 8388608
create_stored_outlines		     string
db_create_file_dest		     string
db_create_online_log_dest_1	     string
db_create_online_log_dest_2	     string
db_create_online_log_dest_3	     string
db_create_online_log_dest_4	     string
db_create_online_log_dest_5	     string



SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/CDB/pdb2/';

System altered.

SQL> create tablespace users;                             

Tablespace created.

SQL> select TABLESPACE_NAME,file_name from dba_data_files;

TABLESPACE_NAME 	       FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM			       /u01/app/oracle/oradata/CDB/pdb2/system01.dbf
SYSAUX			       /u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf
UNDOTBS1		       /u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf
USERS			       /u01/app/oracle/oradata/CDB/pdb2/CDB/0EB5BD04C2C06
			       932E06302F4A8C08F19/datafile/o1_mf_users_lt4wf756_
			       .dbf

从pdb1 中导出元数据

[oracle@19c scripts]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@19c scripts]$ expdp system/oracle@pdb1 \
> full=y \
> dumpfile=11g.dmp \
> directory=dump_dir \
> transportable=always \#表示导出的数据可以在不同的数据库之间进行传输和导入
> logfile=dump.log


#以上这个命令执行完后最后回提示我们需要传输两个数据文件到目标数据库，因为我们是在pdb之间传输，所以我们可以直接永cp命令，本来如果是在两台主机上传输的话还需要传输11.dmp文件，但是我们这里就不需要了

******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /home/oracle/scripts/11g.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS:
  /u01/app/oracle/oradata/CDB/pdb1/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sat Jan 13 19:00:45 2024 elapsed 0 00:06:32

[oracle@19c scripts]$ ll -ls
total 218528
  3920 -rw-r-----. 1 oracle oinstall   4014080 Jan 13 19:00 11g.dmp
  3920 -rw-r-----. 1 oracle oinstall   4014080 Jan 10 17:52 cai.dmp
    12 -rw-r--r--. 1 oracle oinstall     11240 Jan 13 19:00 dump.log
     4 -rw-r-----. 1 oracle oinstall       434 Jan 10 18:08 export.log
   872 -rw-r-----. 1 oracle oinstall    892620 Jan 11 23:13 import.log
209800 -rw-r-----. 1 oracle oinstall 214835200 Jan 11 17:07 jie.dmp

[oracle@19c pdb1]$ cp /u01/app/oracle/oradata/CDB/pdb1/users01.dbf /u01/app/oracle/oradata/CDB/pdb2/CDB
[oracle@19c pdb1]$ cd /u01/app/oracle/oradata/CDB/pdb2/CDB
[oracle@19c CDB]$ ll -ls
total 5128
? ?0 drwxr-x---. 3 oracle oinstall ? ? ?22 Jan 13 19:19 0EB5BD04C2C06932E06302F4A8C08F19
5128 -rw-r-----. 1 oracle oinstall 5251072 Jan 13 19:35 users01.dbf

在pdb2中导入元数据（dmp文件）

执行这个命令就可以导入，在导入前，如果目标数据库和被导出的数据库的表空间名字相同，可以提前rename表空间的明在再执行导入。一下可以看出这样的导入方式就很高效率。

[oracle@19c CDB]$ impdp system/oracle@pdb2 \
> full=y \
> dumpfile=11g.dmp \
> directory=dump_dir \
> transport_datafiles='/u01/app/oracle/oradata/CDB/pdb2/CDB/users01.dbf' \
> logfile=imp.log

Import: Release 19.0.0.0.0 - Production on Sat Jan 13 19:48:37 2024
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": ?system/********@pdb2 full=y dumpfile=11g.dmp directory=dump_dir transport_datafiles=/u01/app/oracle/oradata/CDB/pdb2/CDB/users01.dbf logfile=imp.log?
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace 'USERS' already exists

Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at Sat Jan 13 19:48:52 2024 elapsed 0 00:00:14