检查条件

  1. 源库的compatible初始参数至少是10.2.0以上，目标数据库的compatible参数必须等于或高于源库。
  2. 数据库版本目标库必须大于或等于源库
  3. 源库必须是archivelog模式
  4. 源数据库的默认rman通道设置不能指定compressed指令为configure device type disk 通道属性。
  5. 源库准备迁移的表空间必须都处于联机状态，不能包含离线文件。
  6. 所使用的操作系统账户必须是dba权属组
  7. 源端和目标端字符集一致
  8. 源库开启快追踪，加快备份速度
  9. 确定目标端无相同表空间名称
  10. 检查是否有用户对象在系统表空间(system,user,sysaux)
  11. 检查表空间内自包含
  12. 迁移对象检查
  13. 失效对象统计

1.源库目标库compatible参数查看

  1. select   *  from v$parameter where name like '%compatible%';

2.数据库版本查看

  1. select   *  from v$version ;

3.查看源库归档

  1. archive  log list;

4.查看rman compressed 通道属性

  1. show  all

5.源库准备迁移的表空间状态

  1. select tablespace_name,status  from dba_tablespaces;

6.所使用的操作系统账户必须是dba权属组

7.查看源端和目标端字符集

  1. select * from nls_database_parameters;

8.源库开启快跟踪

  1.  select * from v$block_change_tracking;
  2. alter database enable block change tracking using file '+DATA';

9.确定目标端无相同表空间名称

  1. select tablespace_name from dba_tablespaces;

10.检查是否有用户对象在系统表空间(system,user,sysaux)

  1. select tablespace_name,owner,COUNT(*)  from  dba_segments 
  2. where tablespace_name not  in ('SYSTEM','SYSAUX','TEMP','UNDOTBS1','EXAMPLE')  GROUP BY tablespace_name,owner;

11.检查表空间自包含

  1. exec sys.dbms_tts.transport_set_check('tt1',TRUE,TRUE); 
  2. select * from transport_set_violations;

12.检查迁移对象

  1.  select   owner,object_type,count(*) from dba_objects where object_name not like 'BIN%' and owner in ('XTTS')  group by owner ,object_type order by 1,2 desc;.

13.失效对象统计

  1. select   owner,object_type,count(*) from dba_objects where STATUS  <> 'VALID' and owner in 'XXTS'
  2.  GROUP BY OWNER,object_type ORDER BY 1,2 DESC;

实验环境

1.确认目标和源数据库的版本

2.下载xtts脚本

3.创建源数据库对象

  1. mkdir /home/oracle/script
  2. mkdir /home/oracle/inc_bk
  3. mkdir /home/oracle/XTTSFILES
  4. create or replace directory orcl_dbf as '/opt/oracle/oradata/ORCL' ;
  5. grant read,write on directory orcl_dbf to public;
  6. create or replace directory xttsfiles as '/home/oracle/XTTSFILES' ;
  7. grant  read,write on directory xttsfiles to public;
  9. create tablespace xtts1  datafile '/opt/oracle/oradata/ORCL/xtts1.dbf' size 30m;
  10. create tablespace xtts2  datafile  '/opt/oracle/oradata/ORCL/xtts2.dbf' size 30m;
  11. create user xtts identified by "xtts" default tablespace xtts1;
  12. grant dba to xtts;
  14. create table xtts.xtts1 as select * from dba_objects;
  15. create index idx_obj on xtts1(object_id) tablespace xtts2;

4.目标端

  1. mkdir /home/oracle/script
  2. mkdir /home/oracle/inc_bk
  3. mkdir /home/oracle/XTTS
  4. mkdir /home/oracle/XTTSFILES
  5. create or replace directory ty_dbf as '/opt/oracle/oradata/TY' ;
  6. grant read,write on directory ty_dbf to public;
  7. create or replace directory xttsfiles as '/home/oracle/XTTSFILES' ;
  8. grant  read,write on directory xttsfiles to public;
  10. drop  database link orcl;
  11. create database link orcl connect to system identified by "Oracle123" using '192.168.5.150:1521/orcl';
  12. create user xtts identified by "xtts" default tablespace xtts1;
  13. grant dba to xtts;

5.解压xtts文件包

  1.  unzip -d /home/oracle/XTTS rman_xttconvert_VER4.3.zip
  1.                                                        xtts脚本和对象
脚本/对象
xttdriver.pl perl脚本drive根据源和目标数据库的版本执行xtts操作
xtts.properties 设置源和目标数据库xtts属性
xttcnvrtbkupdest.sql 处理增量备份的转换
xttdbopen.sql 完成目标数据库从mount到open的过渡
xttstartupnomount.sql 在xtts操作期间，将目标数据库打开至nomount状态
xttrep.tmpl Pl/sql对象用于构造从源到目标端复制和转换的语句

6.配置xtts属性以及环境变量

1.4版本

  1. export TMPDIR=/home/oracle/XTTS
  2. ------------------------------------------------
  3. source database parameters:
  4. tablespaces=xtts1,xtts2
  5. platformid=2
  6. srcdir=orcl_dbf
  7. srclink=orcl
  8. dfcopydir=/home/oracle/XTTSFILES
  9. backupformat=/home/oracle/XTTSFILES
  11. destination database parameters:
  12. dstdir=ty_dbf
  13. stageondest=/home/oracle/XTTSFILES
  14. storageondest=/opt/oracle/oradata/TY
  15. backupondest=/home/oracle/XTTSFILES
  16. parallel=4
  17. rollparallel=2

4.0-4.3版本
image.png

  1. tablespaces=XTTS1,XTTS2,FIXF
  2. platformid=13  #select PLATFORM_ID from V$DATABASE; 查询得出
  3. src_scratch_location=/home/oracle/XTTSFILES
  4. dest_datafile_location=+data/orcl/datafile
  5. dest_scratch_location=/home/oracle/XTTSFILES
  6. usermantransport=1

7.迁移存储在其他表空间的表和索引

  1. alter table FIXF.sys_import_transportable_01 MOVE TABLESPACE fixf;
  2. ALTER TABLE FIXF.sys_import_transportable_01 move tablespace fixf  LOB (XML_CLOB) STORE AS (TABLESPACE fixf);

8.源端第一次全备

  1. cd /home/oracle/XTTS
  2. $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup  -d   3

9.cp 文件到目标端

  1. scp /home/oracle/script/*  oracle@192.168.5.133:/home/oracle/script/
  2. scp /home/oracle/XTTSFILES/*  oracle@192.168.5.133:/home/oracle/XTTSFILES/

10.目标端还原备份文件

  1. $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

11.创建差异数据

  1. select   *  from FIXF.sys_import_transportable_01;
  2. UPDATE  FIXF.sys_import_transportable_01  set abort_step=1;
  3. select  *  from XTTS.test;
  4. insert  into XTTS.test (id,name)values(2,'bb');
  5. commit;

12.第一次增量备份

  1. $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
  2. scp `cat incrbackups.txt`  oracle@192.168.5.133:/home/oracle/XTTSFILES/
  3. scp res.txt    oracle@192.168.5.133:/home/oracle/script

13.第一次增量还原

  1. $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

14.重新再次创建差异数据

  1. select   *  from FIXF.sys_import_transportable_01;
  2. UPDATE  FIXF.sys_import_transportable_01  set access_method=1;
  3. select  *  from XTTS.test;
  4. insert  into XTTS.test (id,name)values(3,'cc');
  5. commit;

15.修改表空间为只读

  1. alter tablespace FIXF read only;
  2. alter tablespace XTTS1 read only;
  3. alter tablespace XTTS2 read only;

16.第二次备份

  1. $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
  2. scp `cat incrbackups.txt`  oracle@192.168.5.133:/home/oracle/XTTSFILES/
  3. scp res.txt    oracle@192.168.5.133:/home/oracle/script

17.表空间设置为 read only 后，如下的错误可以忽略

  1. DECLARE*
  2. ERROR at line 1:
  3. ORA-20001: TABLESPACE(S) IS READONLY OR,
  4. OFFLINE JUST CONVERT, COPY
  5. ORA-06512: at line 284
  6. TABLESPACE STRING :''''''''''
  7. Prepare newscn for Tablespaces: ''''''''''

18.第二次还原

  1. $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

19.导出用户和架构

  1.  su - oracle
  2. mkdir expdp
  3. vi exp.par
  5. dumpfile=xttdump.dmp
  6. directory=xttsfiles
  7. statistics=NONE
  8. transport_tablespaces=FIXF,XTTS1,XTTS2
  9. transport_full_check=y
  10. logfile=tts_export.log
  11. cluster=n
  13. expdp system/Oracle123 parfile=exp.par

20.scp 导出的文件到目标端

  1. scp  /home/oracle/XTTSFILES/xttsdump.dmp oracle@192.168.5.133:/home/oracle/XTTSFILES/

21.目标端执行导入

  1.  su - oracle
  2.  mkdir impdp
  3.  vi manual_imp.par
  4. dumpfile= xttsdump.dmp
  5. directory=xttsfiles
  6. transport_datafiles='+data/orcl/datafile/fixf_2.dbf,+data/orcl/datafile/xtts1_9.dbf,+data/orcl/datafile/xtts2_10.dbf'
  8.  impdp system/Oracle123 parfile=manual_imp.par