oracle cdb远程克隆多pdb从理论到实战

一、oracle cdb远程克隆pdb理论

1、技术背景介绍

在12.2版本以前，如果要克隆pdb或non-CDB，需要先将源pdb或者non-CDB关闭或者置于read-only模式，不过12.2开始及不需要了，可以在不影响源端使用的情况下进行在线clone。

当源pdb是属于一个远端cdb时，需要通过数据链来克隆到本地。数据链必须建在本地CDB中，当在本地CDB中执行CREATE PLUGGABLE DATABASE语句时，必须要指定指向远端CDB的数据链。这个数据链要么连接到远端的CDB，要么指向远端的PDB。

2、图例

下面这张图展示了如何从远端pdb克隆到本地

二、oracle cdb远程克隆多pdb实战部分

1、在源端192.168.53.85 创建公共用户

create user c##suq identified by lygle2016 container=all;
grant  CREATE PLUGGABLE DATABASE to c##suq container=all;
grant create session,connect,resource,cdb_dba,sysoper to c##suq container=all;

2、在目标端192.168.53.116 创建连接到源端的dblink

create database link UATDB_LINK connect to c##suq identified by lygle2016 using 'UATDB_LINK';
添加下面 内容到目标端192.168.53.116 tnsname中
echo "UATDB_LINK =
  ( DESCRIPTION =
    ( ADDRESS = ( PROTOCOL = TCP )( HOST =192.168.53.85)( PORT = 1521 ))
    ( CONNECT_DATA =
      ( SERVER = DEDICATED )
      ( SERVICE_NAME = lygleuat)
    )
  ) " >> $ORACLE_HOME/network/admin/tnsnames.ora

3、在目标端创建多个pdb并开始克隆

在目标端192.168.53.116 使用create pluggable database ... from 的命令远程创建PDB，下面两个脚本同时执行
 

list="
META
LOG
"
for one in $list
do
sqlplus -S / as sysdba <<EOF
alter pluggable database ${one} close;  
drop pluggable database ${one} including datafiles;
create pluggable database ${one}  from  ${one}@UATDB_LINK FILE_NAME_CONVERT = ( '/u01/oradata/lygleuat/${one}/' , '/u01/oradata/JTFITS/${one}/' );          
alter pluggable database ${one} open;  
EOF
done


list="
META
LOG
"
for one in $list
do
sqlplus -S / as sysdba <<EOF
alter pluggable database ${one} close;  
drop pluggable database ${one} including datafiles;
create pluggable database ${one}  from  ${one}@UATDB_LINK FILE_NAME_CONVERT = ( '/u01/oradata/lygleuat/${one}/' , '/u01/oradata/JTFITS/${one}/' );          
alter pluggable database ${one} open;  
EOF
done

4、克隆完成后的“善后”工作

#修改密码密码过期时间为不限制（默认180天）

list="
META
LOG
"
for one in $list
do
sqlplus  pdbadmin/ly#admin@192.168.53.116:1521/$one<<EOF
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
exit
EOF
done

#修改超时时间和sga和processer

ALTER PROFILE DEFAULT LIMIT IDLE_TIME 10;
select resource_name,resource_type,limit from dba_profiles where profile='DEFAULT' ;
alter system set sga_target=11G scope=spfile;
alter system set processes=3500 scope=spfile;

#随数据库自启动pdb

sqlplus -S / as sysdba <<EOF
CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/
EOF