揭秘Oracle 12c数据库新特性之三:创建PDB的方法(六)拔出本地pdb,插入到远程另一个cdb中
本文讲解如何将本地的pdb插入到远程另一个cdb中,实现pdb级别的数据迁移。
下面开始实验操作步骤:
从本地cdb1211拔出PRODPDB
$ export ORACLE_SID=cdb1211
$ sqlplus / as sysdba
SYS@cdb1211> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PRODPDB MOUNTED
SYS@cdb1211>
拔出 prodpdb
SYS@cdb1211> alter pluggable database prodpdb unplug into ‘/home/oracle/prodpdb.xml’;
SYS@cdb1211> col pdb_name for a10
SYS@cdb1211> select pdb_name,status from cdb_pdbs where pdb_name=‘PRODPDB’;
PDB_NAME STATUS
PRODPDB UNPLUGGED
SYS@cdb1211> drop pluggable database prodpdb keep datafiles;
远程创建要插入pdb的数据文件的目录:
mkdir /u01/app/oracle/oradata/cdb1211/prodpdb
将元数据文件/home/oracle/prodpdb.xml拷贝到远程
将数据文件拷贝到远程相同的目录,最好跟xml文件一致,这样不用修改xml文件
scp /u01/app/oracle/oradata/cdb1211/prodpdb/* 10.18.3.178:/u01/app/oracle/oradata/cdb1211/prodpdb
scp /home/oracle/prodpdb.xml 10.18.3.178:/home/oracle/prodpdb.xml
检查兼容性
使用 DBMS_PDB.CHECK_PLUG_COMPATIBILITY 函数检查拔出的 prodpdb 与 cdb1 是否兼容
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => ‘/home/oracle/prodpdb.xml’,
pdb_name => ‘PRODPDB’)
WHEN TRUE THEN ‘YES’
ELSE ‘NO’
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
返回 YES 满足兼容性要求
插入 cdb1
创建新的pdb5目录:
mkdir /u01/app/oracle/oradata/cdb1/pdb5
create pluggable database pdb5 using ‘/home/oracle/prodpdb.xml’ file_name_convert=(’/u01/app/oracle/oradata/cdb1211/prodpdb’,’/u01/app/oracle/oradata/cdb1/pdb5’);
或者使用源端的原始路径:
SYS@cdb1> create pluggable database pdb5 using ‘/home/oracle/prodpdb.xml’ nocopy;
nocopy : 使用原来路径下数据文件
open 打开插入的 pdb5
SYS@cdb1> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
5 PDB5 MOUNTED
SYS@cdb1>
SYS@cdb1>alter pluggable database pdb5 open;
SYS@cdb1> col file_name for a60
SYS@cdb1> select file_name from cdb_data_files where con_id=5;
FILE_NAME
/u01/app/oracle/oradata/cdb1/pdb5/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb5/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb5/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb5/users01.dbf
SYS@cdb1>