XTTS迁移11g到12c

初始化安装

配置跨平台增量备份的环境

目标主机上安装数据库

在目标主机上安装数据库软件和实例，创建要导入表空间对象所在的用户。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 源端建相关对象
create tablespace ts1 datafile '/u01/app/oracle/oradata/ora11g/ts101.dbf' size 20m;
create tablespace ts2 datafile '/u01/app/oracle/oradata/ora11g/ts201.dbf' size 20m;

create user xtts identified by xtts default tablespace ts1;
grant dba to xtts;

conn xtts/xtts
create table t_xtts (id number,name varchar2(10));
insert into t_xtts values (1,'xb');
commit;

-- 目标端创建目标pdb
create pluggable database xtts admin user admin identified by oracle 
    file_name_convert=('/u01/app/oracle/oradata/ora12c/pdbseed','/u01/app/oracle/oradata/ora12c/xtts');

create user xtts identified by xtts;
grant dba to xtts;

确定传输的表空间

基本上除了系统表空间其他都需要传输

源端安装xttconvert脚本

脚本下载地址

测试环境两个数据库在一台机器上，所以建了两个目录

1
2
3
4
5
6
7
8
9
10
11
[oracle@xb ~]mkdir -p s_xtts d_xtts

[oracle@xb s_xtts]$ unzip rman_xttconvert_VER4.zip 
Archive:  rman_xttconvert_VER4.zip
  inflating: xtt.newproperties       
  inflating: xtt.properties          
  inflating: xttcnvrtbkupdest.sql    
  inflating: xttdbopen.sql           
  inflating: xttdriver.pl            
  inflating: xttprep.tmpl            
 extracting: xttstartupnomount.sql

创建目录

  1. 源端：
    • xtt.properties文件里src_scratch_location参数定义的备份目录
  2. 目标端：
    • xtt.properties文件里dest_scratch_location参数定义的备份目录
    • dest_datafile_location参数定义的数据文件存放目录

存放备份文件的目录可以为同一个，比如我测试的环境两个数据库是在一台机器上，或者可以指向NFS地址，这里为了区分，还是建了2个不同的目录 

1
2
3
4
5
6
[root@xb backup]# ll
total 0
drwxr-xr-x. 2 oracle dba 6 Apr  4 14:46 dest
drwxr-xr-x. 2 oracle dba 6 Apr  4 14:46 source
[root@xb backup]# pwd
/backup

修改xtt.properties文件

以下参数为必须修改，其他则为可选

  • tablespaces
  • platformid
  • src_scratch_location
  • dest_scratch_location
  • dest_datafile_location
  • (if using 12c) — usermantransport=1

拷贝xtts脚本和配置文件到目标端

1
2
3
[oracle@xb s_xtts]$ ls
xttcnvrtbkupdest.sql  xttdbopen.sql  xttdriver.pl  xtt.newproperties  xttprep.tmpl  xtt.properties  xttstartupnomount.sql
[oracle@xb s_xtts]$ cp * ../d_xtts/

配置TMPDIR环境变量

在源端和目标端都需要将TMPDIR设置成脚本所在目录，如果没设则生成的文件会默认到/tmp目录下

1
2
echo "export TMPDIR=/home/oracle/s_xtts" >> /home/oracle/.11g
echo "export TMPDIR=/home/oracle/d_xtts" >> /home/oracle/.12c

准备阶段

在准备阶段过程中，表空间中要被传输的数据文件会备份到源端，然后在目标端通过xttdriver.pl脚本恢复出来

源端执行备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
[oracle@xb s_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
============================================================
trace file is /home/oracle/s_xtts/backup_Apr4_Thu_15_03_25_770//Apr4_Thu_15_03_25_770_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------


--------------------------------------------------------------------
Find list of datafiles in system
--------------------------------------------------------------------

sqlplus -L -s  / as sysdba  @/home/oracle/s_xtts/backup_Apr4_Thu_15_03_25_770//diff.sql //u01/app/oracle/oradata/ora12c/xtts/

--------------------------------------------------------------------
Done finding list of datafiles in system
--------------------------------------------------------------------

Prepare source for Tablespaces:
                  'TS1'  /backup/dest/
xttpreparesrc.sql for 'TS1' started at Thu Apr  4 15:03:25 2019
xttpreparesrc.sql for  ended at Thu Apr  4 15:03:25 2019
Prepare source for Tablespaces:
                  'TS2'  /backup/dest/
xttpreparesrc.sql for 'TS2' started at Thu Apr  4 15:03:28 2019
xttpreparesrc.sql for  ended at Thu Apr  4 15:03:28 2019

--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------

Prepare newscn for Tablespaces: 'TS1'
Prepare newscn for Tablespaces: 'TS2'
New /home/oracle/s_xtts/xttplan.txt with FROM SCN's generated
Added fname here 1:/backup/dest//TS1_5.tf
Added fname here 1:/backup/dest//TS2_6.tf
============================================================
No new datafiles added
=============================================================

传输文件到目标端

  • src_scratch_location下的备份文件到目标端的dest_scratch_location目录
  • 源端$TMPDIR下的res.txt文件到目标端$TMPDIR
1
2
3
4
5
[oracle@xb source]$ pwd
/backup/source
[oracle@xb source]$ cp * ../dest/

[oracle@xb s_xtts]$ cp res.txt ../d_xtts/

目标端恢复数据文件

1
2
3
4
5
6
[oracle@xb d_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

[oracle@xb xtts]$ pwd
/u01/app/oracle/oradata/ora12c/xtts
[oracle@xb xtts]$ ls
sysaux01.dbf  system01.dbf  temp012019-03-13_09-14-08-192-AM.dbf  TS1_5.dbf  TS2_6.dbf  undotbs01.dbf

前滚阶段

在这个阶段过程中，在源端创建增量备份，然后传输到目标端，转换成目标端字节顺序格式，然后应用于转换后的目标数据文件副本，进行前滚。这个阶段可能要重复执行多次，每次成功的增量备份都将会使目标端文件越接近源端数据。

源端可以多次执行增量备份，而不需要等待其应用到目标端，备份文件和res.txt必须要拷贝到目标端，然后才能执行–restore操作

对表空间进行增量备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- 再插入一些数据
XTTS@ora11g> insert into t_xtts values(2,'dba');

1 row created.

XTTS@ora11g> commit;

Commit complete.

[oracle@xb s_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
============================================================
trace file is /home/oracle/s_xtts/backup_Apr4_Thu_15_49_27_152//Apr4_Thu_15_49_27_152_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------

Added fname here 1:/backup/dest//TS1_5.tf
Added fname here 1:/backup/dest//TS2_6.tf
============================================================
No new datafiles added
=============================================================
Prepare newscn for Tablespaces: 'TS1'
Prepare newscn for Tablespaces: 'TS2'

--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------


--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

Prepare newscn for Tablespaces: 'TS1'
Prepare newscn for Tablespaces: 'TS2'
New /home/oracle/s_xtts/xttplan.txt with FROM SCN's generated

传输备份文件和res.txt

1
2
3
[oracle@xb source]$ cp * ../dest/

[oracle@xb s_xtts]$ cp res.txt ../d_xtts/

目标端应用增量备份

1
[oracle@xb d_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

前滚的时候会连接到目标数据库，然后对每一个传输的数据文件进行增量备份的应用

重复上面几个步骤或进入下一阶段

这里有两个选择

  • 如果想让目标端文件与源端更加接近，那就多重复几次前滚阶段
  • 否则则进入最后一次增量备份

最终增量备份

在这个阶段过程中，源端的数据文件会置为只读，通过应用最后一次的增量备份，目标端的数据文件会跟源端保持一致。然后只需要将源端的对象元数据导出，再导入到目标端数据库，整个操作完成之前，源端数据必须一致保持在只读状态。

修改数据文件为只读

1
2
alter tablespace TS1 read only;
alter tablespace TS2 read only;

最终增量备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
[oracle@xb s_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
============================================================
trace file is /home/oracle/s_xtts/backup_Apr4_Thu_16_07_50_775//Apr4_Thu_16_07_50_775_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------

Added fname here 1:/backup/dest//TS1_5.tf
Added fname here 1:/backup/dest//TS2_6.tf
============================================================
No new datafiles added
=============================================================
Prepare newscn for Tablespaces: 'TS1'
Prepare newscn for Tablespaces: 'TS2'

--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------


--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

Prepare newscn for Tablespaces: 'TS1'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,                    >>>>====这些只读的警告可以忽略
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284

####################################################################
Warning:
------
Warnings found in executing /home/oracle/s_xtts/backup_Apr4_Thu_16_07_50_775//xttpreparenextiter.sql
####################################################################
Prepare newscn for Tablespaces: 'TS2'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284

####################################################################
Warning:
------
Warnings found in executing /home/oracle/s_xtts/backup_Apr4_Thu_16_07_50_775//xttpreparenextiter.sql
####################################################################
New /home/oracle/s_xtts/xttplan.txt with FROM SCN's generated

传输增量备份和res.txt

1
2
3
[oracle@xb source]$ cp * ../dest/
[oracle@xb s_xtts]$ cp incrbackups.txt ../d_xtts/
[oracle@xb s_xtts]$ cp res.txt ../d_xtts/

应用增量备份

1
[oracle@xb d_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

传输元数据

源端:

1
expdp system/oracle dumpfile=xttdump.dmp directory=DATA_PUMP_DIR statistics=NONE transport_tablespaces=TS1,TS2 transport_full_check=y logfile=xtts_export.log

目标端：

1
impdp system/oracle@xtts dumpfile=xttdump.dmp directory=DATA_PUMP_DIR transport_datafiles='/u01/app/oracle/oradata/ora12c/xtts/TS1_5.dbf','/u01/app/oracle/oradata/ora12c/xtts/TS2_6.dbf'

验证数据

检查表空间错误

当前目标库中表空间都还是只读状态，通过Rman的VALIDATE TABLESPACE来检查物理和逻辑错误

1
RMAN> validate tablespace TS1, TS2 check logical;

修改表空间状态

1
2
alter tablespace TS1 read write;
alter tablespace TS2 read write;

检查表数据

1
2
3
4
5
6
7
8
9
sys@ORA12C> conn xtts/xtts@xtts
Connected.

xtts@XTTS> select * from t_xtts;

        ID NAME
---------- ------------------------------
         2 dba
         1 xb
数据迁移
#xtts
XTTS迁移11g到12c
https://www.xbdba.com/2019/04/04/xtts-migrate-11g-to-12c/
作者
xbdba
发布于
2019年4月4日
许可协议