一、文档说明
根据客户要求,需要将版本为12.2.0.1的ORACLCE数据库下面的PDB从一个集群迁移到另外一个集群,具体操作如下。
二、准备工作
1.检查存储
需查看源端和目标端存储使用情况(注:目标端的可使用存储必须大于源端已使用的存储)
查询磁盘组使用率:
Select name,total_mb,free_mb from v$asm_diskgroup;
2.查看源端失效对象
检查源端的对象个数,检查系统对象是否存在invaild的,如果有,迁移前先重新编译无效对象。 正式迁移前,清空pdb下的回收站,按照owner,object_name记录对象个数。
查询失效对象:
Select OWNER,SUBOBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’ INVALID’;
3.清空回收站
purge dba_recyclebin;
4.检查源端、目标端pdb状态
Source Database: ( CDB1 ):
SQL> select CON_ID, dbid, NAME, OPEN_MODE,open_time,create_scn from V$PDBS ORDER BY name;
CON_ID DBID NAME OPEN_MODE OPEN_TIME CREATE_SCN
---------- ---------- ------------------------------ ---------- ------------------------------------------------
2 3998669976 PDB$SEED READ ONLY 08-AUG-17 06.02.49.899 PM +05:30 227
3 2537019739 PDB1 MOUNTED 08-AUG-17 06.09.57.759 PM +05:30 1965762
4 2868907633 PDB2 MOUNTED 08-AUG-17 06.11.14.177 PM +05:30 2018955
Destination Database ( CDB2) :
SQL> select CON_ID, dbid, NAME, OPEN_MODE,open_time,create_scn from V$PDBS ORDER BY name;
CON_ID DBID NAME OPEN_MODE OPEN_TIME CREATE_SCN
---------- ---------- --------------- ---------- ---------------------------------------------------------------
2 3798668876 PDB$SEED READ ONLY 08-AUG-17 05.55.05.936 PM +05:30 227
4 2568907633 PDB2 MOUNTED 08-AUG-17 05.55.26.840 PM +05:30 3013955
三、正式迁移
这里示例环境为源端,目标端的CDB分别为CBD1,CBD2.需要将CDB1的PDB1迁移到CBD2;
1.源端pdb下创建迁移用户
在源端想要迁移的那个pdb下创建用户和授权;
SQL> ALTER SESSION SET CONTAINER=pdb1;
SQL> ALTER PLUGGABLE DATABASE pdb1 open;
SQL> CREATE USER remote_user_for_clone identified by remote_user_for_clone;
SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_user_for_clone;
##正式迁移前,需要将源端想要迁移的那个pdb置位readonly,如果不是正式迁移,只是前期环境梳理准备,这步骤暂时不做
SQL> ALTER PLUGGABLE DATABASE pdb1 close;
SQL> ALTER PLUGGABLE DATABASE pdb1 open read only
2.目标端创建tns
tnsnames.ora
~~~~~~~~~~
getpdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host = <Source_Database_ CDB1_host > )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)
~~~~~~~~~~
3.目标端cdb$root下创建dblink
SQL> CREATE DATABASE LINK getpdb_link CONNECT TO remote_user_for_clone identified by remote_user_for_clone using 'getpdb1';
4.目标端正式执行clone
正式迁移:
SQL> create pluggable database pdb1new from pdb1@getpdb_link file_name_convert=('D:\ORADATA\dbcdb\DATA\pdb1\','D:\ORADATA\dbcdbaux\DATA\pdb1\');
参数说明:pdb1new为新建pdb的名称,这地方可与源库pdb名字保持相同
如果文件存储路径相同,可不用添加file_name_convert
注:建议在后台运行,防止堡垒机等登录的窗口有限时;
具体脚本如下:
Clone_pdb.sh脚本
----------------------------------------------------------------------
sqlplus / as sysdba <<EOF
create pluggable database pdb1new from pdb1@getpdb_link;
quit;
EOF
----------------------------------------------------------------------
Oracle用户执行命令:
nohup ksh Clone_pdb.sh >Clone_pdb.sh.log 2>&1 &
5.打开迁移后的PDB
SQL> alter pluggable database pdb1new open;