ORACLE 11.2.0.4到ORACLE 19.3 PDB XTTS迁移方案测试

迁移内容

表空间 TBS3
ORACLE 11.2.0.4(LINUX) => ORACLE 19.3 PDB (LINUX)

准备待迁移的表空间

create tablespace tbs3  datafile size 100m;
create user TEST3 idnetified by test default tablespace tbs3;
grant dba to test3;
create table test3.xtt_test as select * from dba_objects where rownum<100;

迁移过程

1. 查看平台信息

SQL> COLUMN PLATFORM_NAME FORMAT A36
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT
----------- ------------------------------------ --------------
          6 AIX-Based Systems (64-bit)           Big
         16 Apple Mac OS                         Big
         19 HP IA Open VMS                       Little
         15 HP Open VMS                          Little
          5 HP Tru64 UNIX                        Little
          3 HP-UX (64-bit)                       Big
          4 HP-UX IA (64-bit)                    Big
         18 IBM Power Based Linux                Big
          9 IBM zSeries Based Linux              Big
         10 Linux IA (32-bit)                    Little
         11 Linux IA (64-bit)                    Little
         13 Linux x86 64-bit                     Little
          7 Microsoft Windows IA (32-bit)        Little
          8 Microsoft Windows IA (64-bit)        Little
         12 Microsoft Windows x86 64-bit         Little
         17 Solaris Operating System (x86)       Little
         20 Solaris Operating System (x86-64)    Little
          1 Solaris[tm] OE (32-bit)              Big
          2 Solaris[tm] OE (64-bit)              Big
 
19 rows selected.

2. 检查字符集(源目标应一致）

SQL> col VALUE$ format a20
SQL> select  name,value$  from PROPS$  where name like '%CHARACTERSET';

NAME                           VALUE$
------------------------------ --------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               ZHS16GBK

• 目标PDB字符集为UTF8，这里转换一下，生产环境还是建议PDB字符集保持生CDB一致。

alter session set container=pdb;
alter system enable restricted session;
alter database character set internal_use zhs16gbk;
alter system disable restricted session;

3. 查看OFFLINE DATAFILE

SQL> select TS#,FILE#,NAME,STATUS from v$datafile where status='OFFLINE';

no rows selected

4. 检查表空间自包含

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS3', TRUE);

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS3', TRUE);


PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

5. 解压，配置XTTS

cd /home/oracle/xtt
[oracle@db1 xtt]$ unzip rman-xttconvert_2.0.zip 
Archive:  rman-xttconvert_2.0.zip
  inflating: xttcnvrtbkupdest.sql    
  inflating: xttdbopen.sql           
  inflating: xttdriver.pl            
  inflating: xttprep.tmpl            
  inflating: xtt.properties          
  inflating: xttstartupnomount.sql   
  
[oracle@db1 xtt]$ mkdir stage_source
[oracle@db1 xtt]$ mkdir stage_dest


[oracle@db1 xtt]$vi xtt.properties
platformid=13
dfcopydir=/home/oracle/xtt/stage_source
backupformat=/home/oracle/xtt/stage_source
stageondest=/home/oracle/xtt/stage_dest
storageondest=/oradata/xtt
backupondest=/home/oracle/xtt/stage_dest

6. xtts传到目标一份

[oracle@db1 xtt]$ scp -r * 192.168.56.211:/home/oracle/xtt/
oracle@192.168.56.211's password: 
rman-xttconvert_2.0.zip                                                                                                                   100%   26KB  26.3KB/s   00:00    
xtt.properties                                                                                                                            100% 7842     7.7KB/s   00:00    
xttcnvrtbkupdest.sql                                                                                                                      100% 1390     1.4KB/s   00:00    
xttdbopen.sql                                                                                                                             100%   71     0.1KB/s   00:00    
xttdriver.pl                                                                                                                              100%   90KB  89.6KB/s   00:00    
xttprep.tmpl                                                                                                                              100%   11KB  11.3KB/s   00:00    
xttstartupnomount.sql                                                                                                                     100%   52     0.1KB/s   00:00    
[oracle@db1 xtt]$ 

7. 设置TMPDIR环境变量

[oracle@db1]$ export TMPDIR=/home/oracle/xtt
[oracle@centos7]$ export TMPDIR=/home/oracle/xtt

8. 目标创建临时实例

vi $ORACLE_HOME/dbs/inittmp.ora
db_name=‘ORCL’
compatible =‘11.2.0’
export ORACLE_SID=tmp

9. source-XTTS创建基础备份，并将备份传至目标

$ORACLE_HOME/perl/bin/perl xttdriver.pl -p
##scp  /home/oracle/xtt/xttplan.txt 192.168.56.211:/home/oracle/xtt/
scp  /home/oracle/xtt/stage_source/* 192.168.56.211:/home/oracle/xtt/stage_dest
scp  /home/oracle/xtt/rmanconvert.cmd 192.168.56.211:/home/oracle/xtt/rmanconvert.cmd

10. dest-convert,restore

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -c

11. source-increment backup

SQL> insert into test3.xtt_test select * from test3.xtt_test;
SQL> commit;

$ORACLE_HOME/perl/bin/perl xttdriver.pl -i

scp `cat incrbackups.txt` 192.168.56.211:/home/oracle/xtt/stage_dest
scp  /home/oracle/xtt/xttplan.txt 192.168.56.211:/home/oracle/xtt
scp  /home/oracle/xtt/tsbkupmap.txt 192.168.56.211:/home/oracle/xtt

12. dest-convert increment backup,recover

$ORACLE_HOME/perl/bin/perl xttdriver.pl -r

13. source-设置表空间为read only,做最后一次增量

SQL> alter tablespace tbs3 read only;

Tablespace altered.
---生成增量SCN
$ORACLE_HOME/perl/bin/perl xttdriver.pl -s
---increment backup
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
scp `cat incrbackups.txt` 192.168.56.211:/home/oracle/xtt/stage_dest

scp  /home/oracle/xtt/xttplan.txt 192.168.56.211:/home/oracle/xtt
scp  /home/oracle/xtt/tsbkupmap.txt 192.168.56.211:/home/oracle/xtt

14. dest-应用最后一次增量
    $ORACLE_HOME/perl/bin/perl xttdriver.pl -r

15. dest-迁移元数据，TTS

SQL> create directory ohome as '/home/oracle';
SQL> grant read,write on directory ohome to dbmt;

SQL> create user TEST3 identified by test;
SQL> create public database link ttslink connect to dbmt identified by dbmt using '//192.168.56.210:1521/orcl11g' ;
$impdp dbmt/dbmt@192.168.56.211:1521/pdb directory=ohome logfile=tts_imp.log network_link=ttslink transport_full_check=no transport_tablespaces=TBS3 transport_datafiles='/oradata/xtt/TBS3_7.xtf';

16. dest-验证

rman target /
validate pluggable database pdb check logical;

SQL> select count(*)from test3.xtt_test;

  COUNT(*)
----------
       198