第一部分PDB 迁移
1.1 拔插PDB
该方案是适应于相同服务器上不同CDB 之间的PDB 迁移。
方案流程
1
：
1
、关闭源环境
PDB
2
、
UNPLUG PDB
3
、检查兼容性
4
、plugging a PDB
5
、打开数据库PDB
GDS 重建服务 ( 非必选项 , 无特殊规划请跳过 )
重建灾备
登陆源库，关闭
PDB
# su - oracle
$ sqlplus / as sysdba;
SQL> alter pluggable database sjwpdb close immediate instances=all;
登陆源库，
UNPLUG PDB
SQL> ALTER PLUGGABLE DATABASE sjwpdb UNPLUG INTO '/tmp/sjwpdb.xml';
登陆目标库，检查兼容性，查看视图
PDB_PLUG_IN_VIOLATIONS
# su - oracle
$ sqlplus / as sysdba;
SQL> set serveroutput on
SQL> declare
compatible constant varchar2(3) :=
case dbms_pdb.check_plug_compatibility(
pdb_descr_file => '/tmp/sjwpdb.xml',
pdb_name => 'sjwpdb')
when true then 'yes'
else 'no'
end;
begin
dbms_output.put_line(compatible);
end;
/
Yes --ok
登陆目标库，
plugging a PDB
SQL> create pluggable database sjwpdb using '/tmp/sjwpdb.xml'
nocopy
tempfile reuse standbys=none;
登陆目标库，打开数据库
SQL> alter pluggable database sjwpdb open instances=all;
SQL> alter pluggable database sjwpdb save state instances=all;
GDS
重建服务
(
非必选项
,
无特殊规划请跳过
)
删除服务:
GDSCTL> remove service -gdspool sjwdbpool -service sjwpdb_s1,sjwpdb_s2
添加服务：
GDSCTL> add service -gdspool sjwtestpool -service sjwpdb_s1 -pdbname sjwpdb -preferred sjwtest,sjwtestdg -role primary
修改服务：
GDSCTL> modify service -gdspool sjwtestpool -service sjwpdb_s1 -database sjwtest -add_instances -preferred sjwtest1,sjwtest2
GDSCTL> modify service -gdspool sjwtestpool -service sjwpdb_s1 -database sjwtestdg -add_instances -preferred sjwtestdg1,sjwtestdg2
启动服务：
GDSCTL> start service -gdspool sjwtestpool -service sjwpdb_s1
登陆源库，备份
PDB
登陆
源库，删除PDB
，
该操作有风险，建议延迟若干天再执行
SQL> drop pluggable database sjwpdb including datafiles;
登陆
目标
库，恢复灾备环境
# su - oracle
$ dgmgrl /
DGMGRL> edit database sjwtestdg set state='apply-on';
DGMGRL> show database sjwtestdg;
# 如果没有延迟，再执行以下步骤
DGMGRL> edit database sjwtestdg set state='apply-off';
恢复PDB 数据文件
# su - oracle
$ rman target=sys@sjwtest
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
# su - oracle
$ rman target=sys@sjwtestdg
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> run{
set newname for pluggable database sjwpdb to new;
restore pluggable database sjwpdb from service sjwdb;
switch datafile all;
}
关闭灾备库，并启动PDB 恢复
# su - oracle
$ export ORACLE_SID=sjwtestdg1
$ srvctl stop database -d sjwtestdg -o immediate;
$ sqlplus / as sysdba;
SQL> startup mount;
SQL> alter session set container=sjwpdb;
SQL> alter pluggable database enable recovery;
$ srvctl start database -d sjwtestdg;
Broker 恢复日志应用
# su - oracle
$ dgmgrl /
DGMGRL> edit database sjwtestdg set state='apply-on';
$ rman target=sys@sjwtest
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY to applied on all standby backed up 1 times to tape;
参考：
How to relocate a PDB from one CDB to another with minimal down time -12.2 Release (Doc ID 2396518.1)
Data Guard Impact on Oracle Multitenant Environments (Doc ID 2049127.1)
1.2 Relocate PDB
该方案是适应于不同服务器上不同CDB 之间的PDB 迁移。
方案流程 ：该方案业务影响时间接近0 秒，可以实现分钟迁移！
源CDB 创建用户 -> 目标环境创建DBLINK -> Relocate PDB -> 备份PDB -> 目标CDB 打开PDB ->GDS 重建服务 -> 重建灾备
备注：
该方案不能做到分钟回退, 可以通过克隆PDB ，提前进行性能测试，降低回退的可能性！
检查是否具备relocate 条件
源库和目标库必须采用local undo 模式
源库必须启动归档模式
源库和目标库字节顺序必须相同
源库和目标库字符集必须相同
SQL> select property_name,property_value
from database_properties
where property_name in ('NLS_CHARACTERSET','DICTIONARY_ENDIAN_TYPE','LOCAL_UNDO_ENABLED');
SQL> select log_mode from v$database;
登陆源库，创建DBLINK 用户
# su - oracle
$ sqlplus / as sysdba;
SQL> create user c##user1 identified by szsjw123p container=all;
SQL> grant create session, sysoper, create pluggable database to c##user1 container=all;
登陆目标库，创建DBLINK
# su - oracle
$ sqlplus / as sysdba;
SQL> create PUBLIC database link sjwdb_link connect to c##user1 identified by szsjw123p using 'sjwdb';
SQL> select * from global_name@sjwdb_link;
登陆目标库，Relocate PDB
SQL> CREATE PLUGGABLE DATABASE cmdpdb FROM cmdpdb@sjwdb_link file_name_convert=('+datadg','+datadg') standbys=none RELOCATE AVAILABILITY MAX;
登陆目标库，打开PDB
SQL> show pdbs;
SQL> alter pluggable database sjwpdb open instances=all;
SQL> alter pluggable database sjwpdb save state instances=all;
目标库开始应用日志
源库开始关闭： 9:53:31
SJWPDB(5):JIT: pid 8847814 requesting stop
SJWPDB(5):KILL SESSION for sid=(1467, 24492):
SJWPDB(5): Reason = PDB close immediate
SJWPDB(5): Mode = KILL HARD FORCE -/-/-
SJWPDB(5): Requestor = PPA7 (orapid = 42, ospid = 8847814, inst = 1)
SJWPDB(5): Owner = Process: USER (orapid = 188, ospid = 11993686)
SJWPDB(5): Result = ORA-0
2018-11-20T09:53:31.874969+08:00
Pluggable database SJWPDB closed
目标库开始打开：目标库恢复到 835514754
SJWPDB(4):Incomplete Recovery applied until change 835514754 time 11/20/2018 10:15:19
2018-11-20T09:53:57.651963+08:00
SJWPDB(4):Media Recovery Complete (gsm8dg1)
SJWPDB(4):This instance was first to open pluggable database SJWPDB (container=4)
SJWPDB(4):attach called for domid 4 (domuid: 0x901cb9ad, options: 0x0, pid: 3670462)
SJWPDB(4):queued attach broadcast request 700010019faf840
SCN 分析 Relcoate 没有数据丢失：
日志挖掘最大scn
select max(scn) from a0909 where data_objd#=73241 --835514144 < 业务数据最大SCN> select max(scn) from a0909 where src_con_uid='2476485332'--835514731 <pdb 数据最大SCN> select start_timestamp,commit_timestamp from a0909 where scn = 835514731 <scn& 时间>
2018/11/20 9:53:34 目标表中最大数据 2018-11-20 09:53:30 835514141 2018-11-20 09:53:30 835514144 与第一条查询一致 select count(*) from a0909 where scn > 835514754 and src_con_uid='2476485332'
源库归档日志中大于该scn 没有该PDB 的数据！
GDS 重建服务 ( 非必选项 , 无特殊规划请跳过 )
删除服务:
GDSCTL> remove service -gdspool sjwdbpool -service sjwpdb_s1,sjwpdb_s2
添加服务：
GDSCTL> add service -gdspool sjwtestpool -service sjwpdb_s1 -pdbname sjwpdb -preferred sjwtest,sjwtestdg -role primary
修改服务：
GDSCTL> modify service -gdspool sjwtestpool -service sjwpdb_s1 -database sjwtest -add_instances -preferred sjwtest1,sjwtest2
GDSCTL> modify service -gdspool sjwtestpool -service sjwpdb_s1 -database sjwtestdg -add_instances -preferred sjwtestdg1,sjwtestdg2
启动服务：
GDSCTL> start service -gdspool sjwtestpool -service sjwpdb_s1
登陆源库，备份PDB
登陆源库，删除PDB ， 该操作有风险，建议延迟若干天再执行
SQL> drop pluggable database sjwpdb including datafiles;
登陆目标库，恢复灾备环境
# su - oracle
$ dgmgrl /
DGMGRL> edit database sjwtestdg set state='apply-on';
DGMGRL> show database sjwtestdg;
# 如果没有延迟，再执行以下步骤
DGMGRL> edit database sjwtestdg set state='apply-off';
恢复PDB 数据文件
# su - oracle
$ rman target=sys@sjwtest
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
# su - oracle
$ rman target=sys@sjwtestdg
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> run{
set newname for pluggable database sjwpdb to new;
restore pluggable database sjwpdb from service sjwdb;
switch datafile all;
}
关闭灾备库，并启动PDB 恢复
# su - oracle
$ export ORACLE_SID=sjwtestdg1
$ srvctl stop database -d sjwtestdg -o immediate;
$ sqlplus / as sysdba;
SQL> startup mount;
SQL> alter session set container=sjwpdb;
SQL> alter pluggable database enable recovery;
$ srvctl start database -d sjwtestdg;
Broker 恢复日志应用
# su - oracle
$ dgmgrl /
DGMGRL> edit database sjwtestdg set state='apply-on';
$ rman target=sys@sjwtest
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY to applied on all standby backed up 1 times to tape;
参考：
How to relocate a PDB from one CDB to another with minimal down time -12.2 Release (Doc ID 2396518.1)
Data Guard Impact on Oracle Multitenant Environments (Doc ID 2049127.1)
1.3 PDB 连接串
非GDS 环境下连接串格式与参数
GOLD=
(DESCRIPTION_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)
(DESCRIPTION=(ENABLE=BROKEN)(CONNECT_TIMEOUT=3)(TRANSPORT_CONNECT_TIMEOUT=2)(RETRY_COUNT=2)
(ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=szscan1’s ip)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=shscan1’s ip)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME=sjwdb_s1))
)
(DESCRIPTION=(ENABLE=BROKEN)(CONNECT_TIMEOUT=3)(TRANSPORT_CONNECT_TIMEOUT=2)(RETRY_COUNT=2)
(ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=szscan2’s ip)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=shscan2’s ip)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME=sjwdb_s2))
)
)
注释:
(szscanX, shscanX) are (primary, standby)
(net1, net2) are (public network #1, public network #2)
TRANSPORT_CONNECT_TIMEOUT 如果是oci 接口则需要配置，否则会删除
备注：使用 GDS 监听连接数据库，连接串：
GDS 环境下连接串格式与参数 ( 非必选项 , 无特殊规划请跳过 )
test01=
(DESCRIPTION =(CONNECT_TIMEOUT=3)(RETRY_COUNT=2)(RETRY_DELAY=2)(TRANSPORT_CONNECT_TIMEOUT=2)
(FAILOVER=ON)
(ADDRESS_LIST =
(LOAD_BALANCE=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = gdssz1)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = gdssz2)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = gdssz3)(PORT = 1522))
)
(ADDRESS_LIST =
(LOAD_BALANCE=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = gdssh3)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = gdssh3)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = gdssh3)(PORT = 1522))
)
(CONNECT_DATA = (SERVICE_NAME = sjwdb.sjwdbpool.sjwchina))
)
https://www.oracle.com/technetwork/database/availability/maa-globaldataservices-3413211.pdf
