XTTS示例，源端11.2.0.4，目标端19.6 PDB

参考MOS：11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 1389592.1)

源端获取USER和权限，根据具体情况改查询条件

--CREATE_USER_DDL
select to_char(dbms_metadata.get_ddl('USER',USERNAME)) CREATE_USER_DDL from dba_users where ACCOUNT_STATUS='OPEN' and USERNAME not in ('SYS','SYSTEM');
--GRANT_ROLE_DDL
select to_char(dbms_metadata.get_granted_ddl('ROLE_GRANT',GRANTEE)) GRANT_ROLE_DDL from dba_role_privs where GRANTEE in (select USERNAME from dba_users where ACCOUNT_STATUS='OPEN' and USERNAME not in ('SYS','SYSTEM'));
--default_tablespace
select 'alter user '||username||' default tablespace '||default_tablespace||';' from dba_users where ACCOUNT_STATUS='OPEN' and USERNAME not in ('SYS','SYSTEM');

建议源端清理并关闭回收站

select OWNER,TYPE,count(1) from dba_recyclebin group by OWNER,TYPE order by OWNER;
show parameter recyclebin
alter systemn set recyclebin='off' scope=both;
purge recyclebin;

源端自包含检查

execute sys.dbms_tts.transport_set_check('TBS,TEST,TEST2,GG_TBS,JJ_TBS,USERS', true);
select * from sys.transport_set_violations;

源端创建测试数据，可用于验证同步情况

create user xttst identified by xttst default tablespace USERS;
grant connect,resource to xttst;

create table xttst.xtts (aa date);
insert into xttst.xtts values (sysdate);
commit;
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select * from xttst.xtts;

源端解压XTTS

[/bin/bash]$mkdir xtts
[/bin/bash]$unzip rman_xttconvert_v3.zip -d xtts/

源端开启块跟踪，共享位置

alter database enable block change tracking using file '/oracle/oradata/orcldb/block_change_tracking.log';

目标端创建PDB

SQL> alter pluggable database orcldb close instances=all;
SQL> drop pluggable database orcldb including datafiles;
SQL> create pluggable database orcldb admin user admin identified by admin;
SQL> alter pluggable database orcldb open instances=all;

目标端配置tnsnames.ora

vi /u02/app/oracle/product/19.0.0/db_1/network/admin/tnsnames.ora 

orcldb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.5.10)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldb)
    )
  )

orclpdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.8.110)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldb)
    )
  )

tnsping orcldb

目标端创建目录和DBLINK

$ sqlplus system/oracle@orcldb

SQL> ALTER SESSION SET CONTAINER=orcldb;
create public database link to_orcldb connect to system identified by oracle using 'orcldb';
select name from v$database@to_orcldb;

create directory PUMP_DIR as '/data/expdp';
Grant read,write on directory PUMP_DIR to system;

源端配置xtt.properties

SYS@orcl1> select PLATFORM_ID from V$DATABASE;

    PLATFORM_ID
---------------
              6

[/bin/bash]$cat xtt.properties
tablespaces=TBS,TEST,TEST2,GG_TBS,JJ_TBS,USERS
platformid=6
dfcopydir=/xtts/rmanbak
backupformat=/xtts/rmanbak
stageondest=/xtts/rmanbak
storageondest=+DATA/ORCL/A58613850A629F/DATAFILE
backupondest=/xtts/rmanbak
parallel=8
rollparallel=8
getfileparallel=8

目标端解压XTTS，源端将xtt.properties复制到目标端

源端创建数据文件副本

more full_backup.sh
export ORACLE_SID=orcl1
export TMPDIR=/home/oracle/xtts
export XTTDEBUG=1
$ORACLE_HOME/perl/bin/perl xttdriver.pl -p

nohup sh full_backup.sh > full_backup.log &

传输源端数据文件副本到目标端，使用NFS可忽略此步

传输源端 rmanconvert.cmd 到目标端

$scp /home/oracle/xtts/rmanconvert.cmd oracle@10.10.8.110:/home/oracle/xtts

目标端创建辅助实例

创建辅助实例的原因：目标端执行增量恢复时会重启实例，对于PDB架构，会影响其他PDB正常使用。

[oracle@rac1:/home/oracle/xtts]$ vi /u02/app/oracle/product/19.0.0/db_1/dbs/initxtts.ora
*.db_name=xtts
*.compatible=19.0.0

[oracle@rac1:/home/oracle/xtts]$ export ORACLE_SID=xtts
[oracle@rac1:/home/oracle/xtts]$ sqlplus / as sysdba
SQL> startup nomount force;

目标端转换数据文件副本

more dfile_convert.sh
export ORACLE_SID=xtts
export TMPDIR=/home/oracle/xtts
export XTTDEBUG=1
$ORACLE_HOME/perl/bin/perl xttdriver.pl -c

nohup sh dfile_convert.sh > dfile_convert.log &

源端插入测试数据

insert into xttst.xtts values (sysdate);
commit;
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select * from xttst.xtts;

源端执行增量备份

more incr_backup.sh
export ORACLE_SID=orcl1
export TMPDIR=/home/oracle/xtts
export XTTDEBUG=1
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i

nohup sh incr_backup.sh > incr_backup.log &

传输源端增量备份文件到目标端，使用NFS可忽略此步

传输源端 xttplan.txt tsbkupmap.txt 到目标端

$scp xttplan.txt tsbkupmap.txt oracle@10.10.8.110:/home/oracle/xtts

目标端增量恢复

more recover_incr.sh
export ORACLE_SID=xtts
export TMPDIR=/home/oracle/xtts
export XTTDEBUG=1
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r

nohup sh recover_incr.sh > recover_incr.log &

源端确定下一次增量备份的FROM_SCN

export ORACLE_SID=orcl1
export TMPDIR=/home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -s

数据库切割

insert test data

insert into xttst.xtts values (sysdate);
commit;

源端将表空间设置为只读

alter tablespace TBS read only;
alter tablespace TEST read only;
alter tablespace TEST2 read only;
alter tablespace GG_TBS read only;
alter tablespace JJ_TBS read only;
alter tablespace USERS read only;

执行最后一次增量备份和恢复

目标端创建角色

CREATE ROLE "SELECT_ROLE";
GRANT SELECT ANY DICTIONARY to SELECT_ROLE;
GRANT SELECT ANY TABLE to SELECT_ROLE;
GRANT SELECT ANY SEQUENCE to SELECT_ROLE;
GRANT SELECT ANY TRANSACTION to SELECT_ROLE;

目标端创建用户

CREATE USER "USER1" IDENTIFIED BY VALUES 'S:C31AE3752D23E329F997612E6D5CAB5438727;770F43' DEFAULT TABLESPACE "SYSTEM" TEMPORARY TABLESPACE "TEMP";
CREATE USER "USER2" IDENTIFIED BY VALUES 'S:312D3507AD174D18717F9F6331E2CB5932F60;D07664' DEFAULT TABLESPACE "SYSTEM" TEMPORARY TABLESPACE "TEMP";
CREATE USER "USER3" IDENTIFIED BY VALUES 'S:33E9BB7738DC61157114F9AE395175632289D;C47786' DEFAULT TABLESPACE "SYSTEM" TEMPORARY TABLESPACE "TEMP";

目标端创建DBLINK

SELECT to_char(dbms_metadata.get_ddl('DB_LINK',DB_LINK,OWNER)) DB_LINK_DDL FROM dba_db_links;

目标端导入表空间元数据

export TMPDIR=/home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -e
cat /home/oracle/xtts/xttplugin.txt

[oracle@rac1:/home/oracle/xtts]$ cat xtts_impdp.par
network_link=to_orcldb 
transport_full_check=no 
directory=PUMP_DIR 
logfile=impdp_orcldb_log_20200514.log 
parallel=8
cluster=N
transport_tablespaces=TBS,TEST,TEST2,GG_TBS,JJ_TBS,USERS 
transport_datafiles='+DATA/ORCL/A58613850A629F/DATAFILE/TBS.dbf','+DATA/ORCL/A58613850A629F/DATAFILE/TEST.dbf','+DATA/ORCL/A58613850A629F/DATAFILE/TEST2.dbf','+DATA/ORCL/A58613850A629F/DATAFILE/GG_TBS.dbf','+DATA/ORCL/A58613850A629F/DATAFILE/JJ_TBS.dbf','+DATA/ORCL/A58613850A629F/DATAFILE/USERS.dbf'

[oracle@rac1:/home/oracle/xtts]$ impdp system/oracle@orclpdb parfile=xtts_impdp.par

目标端将表空间设置为读写模式

select TABLESPACE_NAME,STATUS from dba_tablespaces;
alter tablespace TBS read write;
alter tablespace TEST read write;
alter tablespace TEST2 read write;
alter tablespace GG_TBS read write;
alter tablespace JJ_TBS read write;
alter tablespace USERS read write;

目标端验证测试数据

SQL> ALTER SESSION SET CONTAINER=orcldb;
SQL> select * from xttst.xtts;

目标端更改用户默认表空间

select 'alter user '||username||' default tablespace '||default_tablespace||';' from dba_users where ACCOUNT_STATUS='OPEN' and USERNAME not in ('SYS','SYSTEM');

目标端导入其他对象元数据

[oracle@rac1:/home/oracle/xtts]$ cat metadata_only_impdp.par
network_link=to_orcldb 
directory=PUMP_DIR 
schemas='USER1','USER2','USER3'
content=metadata_only 
EXCLUDE=table,index 
parallel=4 
logfile=impdp_orcldb_20200507.log 
cluster=N

[oracle@rac1:/home/oracle/xtts]$ impdp system/oracle@orclpdb parfile=metadata_only_impdp.par

PUBLIC SYNONYM

set pagesize 6000
select 'CREATE or replace PUBLIC SYNONYM '||synonym_name||' FOR '||table_owner||'.'||table_name||';' FROM dba_synonyms where table_owner in ('NRMDB','NMOSDB','KPIDB') and owner='PUBLIC';

同步tnsnames.ora

统计信息

exec dbms_stats.gather_database_stats(estimate_percent => 10, degree => 32, granularity => 'ALL', cascade => TRUE);

无效对象

对象数量对比

select owner,object_type,count(*) from dba_objects where owner in ('USER1','USER2','USER3') group by owner,object_type order by owner,object_type;

检查文件头

select FILE#,STATUS,TABLESPACE_NAME,ERROR from v$datafile_header;