oracle 19c容器数据库data dump数据泵传输数据（2）---11g导19c

目录

1.在11gnon-cdb数据库中创建测试用户

2.在19cCDB容器数据库中新建pdb2

3.执行命令导出

4.执行命令导入

我們要記住一点：如果是全库导出导入的话，目标数据库没有的表空间我们要事先创建：不然就会导入失败

1.在11gnon-cdb数据库创建测试用户

SQL> SELECT file_name, tablespace_name FROM dba_data_files;------这里可以看出11g数据库比pdb多了两个表空间

SQL> set linesize 200
SQL> /

FILE_NAME					   TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/cai/users01.dbf 	   USERS
/u01/app/oracle/oradata/cai/undotbs01.dbf	   UNDOTBS1
/u01/app/oracle/oradata/cai/sysaux01.dbf	   SYSAUX
/u01/app/oracle/oradata/cai/system01.dbf	   SYSTEM
/u01/app/oracle/oradata/cai/example01.dbf	   EXAMPLE

SQL> ?grant dba,resource,connect to CC;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee='CC';

GRANTEE ?? ??? ? ? ? ? PRIVILEGE?? ??? ??? ??? ?ADM
------------------------------ ---------------------------------------- ---
CC?? ??? ??? ? ? ? ? UNLIMITED TABLESPACE?? ??? ??? ?NO


SQL> create directory dump_dir as '/home/oracle/scripts';
Directory created.

SQL> grant read,write,execute on directory dump_dir to system;

Grant succeeded.

2.在19cCDB容器数据库新建pdb2

SQL> show pdbs;

? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 2 PDB$SEED?? ??? ??? ? ?READ ONLY ?NO
?? ? 3 PDB1 ?? ??? ??? ? ?MOUNTED
SQL> create pluggable database pdb2 admin user admin1 identified by oracle
? 2 ?FILE_NAME_CONVERT=('pdbseed','pdb2');

Pluggable database created.

SQL> show pdbs;

? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 2 PDB$SEED?? ??? ??? ? ?READ ONLY ?NO
?? ? 3 PDB1 ?? ??? ??? ? ?MOUNTED
?? ? 4 PDB2 ?? ??? ??? ? ?MOUNTED
SQL> alter session set container=pdb2;

Session altered.

SQL> startup
Pluggable Database opened.
SQL> show pdbs;

? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 4 PDB2 ?? ??? ??? ? ?READ WRITE NO
SQL> select tablespace_name,block_size,status,logging,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME ?? ? ? ? ? BLOCK_SIZE STATUS ? ?LOGGING ? EXTENT_MAN SEGMEN
------------------------------ ---------- --------- --------- ---------- ------
SYSTEM?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? MANUAL
SYSAUX?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? AUTO
UNDOTBS1?? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? MANUAL
TEMP?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?NOLOGGING LOCAL?? ? MANUAL

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> create directory dump_dir as '/home/oracle/scripts';

Directory created.

SQL> grant read,write,execute on directory dump_dir to system;

Grant succeeded.


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> create tablespace example;

Tablespace created.

3.执行命令导出

[oracle@11g scripts]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@11g scripts]$ expdp system/oracle full=y dumpfile=jie.dmp directory=dump_dir
[oracle@11g scripts]$ scp jie.dmp 19c:/home/oracle/scripts
[oracle@11g scripts]$ ll -ls
total 209916
   116 -rw-r--r-- 1 oracle oinstall    116052 Jan 12 14:12 export.log
209800 -rw-r----- 1 oracle oinstall 214835200 Jan 12 14:12 jie.dmp

4.执行命令导入

[oracle@19c ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@19c ~]$ impdp system/oracle@pdb2 full=y dumpfile=jie.dmp directory=dump_dir

导入执行成功后查看pdb2是否有CC用户，如下显示导入成功

SQL> show pdbs;

? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 4 PDB2 ?? ??? ??? ? ?READ WRITE NO
SQL>?
SQL>?
SQL>?
SQL>?
SQL> SELECT file_name, tablespace_name FROM dba_data_files;

FILE_NAME?? ??? ??? ??? ??? ? ? TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf?? ? ? UNDOTBS1
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf?? ? ? SYSAUX
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf?? ? ? SYSTEM
/u01/app/oracle/oradata/CDB/pdb2/CDB/0EA407C05EF40 USERS
9A6E06302F4A8C0589B/datafile/o1_mf_users_lszzp7rj_
.dbf

/u01/app/oracle/oradata/CDB/pdb2/CDB/0EA407C05EF40 EXAMPLE
9A6E06302F4A8C0589B/datafile/o1_mf_example_lt00g0o
4_.dbf


SQL> select username from all_users;

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
ADMIN1
APEX_PUBLIC_USER
SYSMAN
MGMT_VIEW
FLOWS_FILES
APEX_030200
OWBSYS
OWBSYS_AUDIT
SPATIAL_WFS_ADMIN_USR

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
SPATIAL_CSW_ADMIN_USR
SCOTT
HR
OE
IX
SH
PM
BI
CC

53 rows selected.

?