备份前确认数据放在哪个 pdb 下

SQL> show pdbs

     CON_ID CON_NAME      OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

  2 PDB$SEED      READ ONLY  NO

  3 PDB1      READ WRITE NO

切换到对应 pdb 上

alter session set container=pdb1;

create directory backup as '/data/21c/';

grant read,write on directory backup to public;

增加一些测试数据

create user test identified by oracle ；

grant dba to test ；

conn test/oracle

create table TAB1

(

   T_ID   NUMBER,

   T_NAME VARCHAR2(1000)

);

insert into tab1 values(1,'test1');

insert into tab1 values(2,'test2');

commit;

全备份

expdp \'sys/oracle@192.168.210.10/pdb1 as sysdba\' directory=backup dumpfile=full.dmp logfile=full.log full=y

恢复到 pdb2 上

create pluggable database pdb2 admin user test identified by oracle;

alter session set container=pdb2;

startup

create directory backup as '/data/21c';

grant read,write on directory backup to public;

impdp \'sys/oracle@192.168.210.10/pdb2 as sysdba\' directory=backup dumpfile=full.dmp logfile=fullimp.log full=y

在 pdb2 下查看数据：

select *from test.tab1;

检查权限是否过来

select * from dba_role_privs where grantee='TEST';

备份 schema 方式

expdp \'sys/oracle@192.168.210.10/pdb1 as sysdba\' directory=backup dumpfile=test.dmp logfile=test.log schemas=test

备份表方式

expdp \'sys/oracle@192.168.210.10/pdb1 as sysdba\' directory=backup dumpfile=tab.dmp logfile=tab.log tables=test.tab1

备份表空间方式

expdp \'sys/oracle@192.168.210.10/pdb1 as sysdba\' directory=backup dumpfile=ts.dmp logfile=ts.log tablespaces=users

备份元数据方式

alter tablespace users read only;

expdp \'sys/oracle@192.168.210.10/pdb1 as sysdba\' directory=backup dumpfile=full.dmp logfile=full.log full=y TRANSPORTABLE=ALWAYS

备份 TRANSPORT_TABLESPACES 方式

alter tablespace users read only;

expdp \'sys/oracle@192.168.210.10/pdb1 as sysdba\' directory=backup dumpfile=ts.dmp logfile=ts.log TRANSPORT_TABLESPACES=users;

需要拷贝数据文件

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:

   /data/21c/ts.dmp

******************************************************************************

Datafiles required for transportable tablespace USERS:

   /data/21c/oradata/ORA21C/F0564D828AC16531E0530AD2A8C085F9/datafile/o1_mf_users_kt5xcqr4_.dbf

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Dec 22 19:51:38 2022 elapsed 0 00:00:25

需要拷贝元数据和数据文件到目标库上

cp /data/21c/oradata/ORA21C/F0564D828AC16531E0530AD2A8C085F9/datafile/o1_mf_users_kt5xcqr4_.dbf /data/21c/users.dbf

恢复元数据及数据文件

impdp \'sys/oracle@192.168.210.10/pdb2 as sysdba\' directory=backup dumpfile=ts.dmp transport_datafiles=/data/21c/users.dbf