V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup(Doc ID 2471245.1)
本文环境：
源数据库版本：11.2.0.4 单实例
源平台：aix7.1
本例没有aix环境，使用rhel7下的oracle 11.2.0.4 rac版本朝rhel7 平台下的oracle 12.2.0.1的pdb中迁移
目标数据库版本：19.3.0.0 RAC CDB
目标平台：linux 7 x86
业务表空间：
JANTBS
DOGTBS
ZLYTBS
xtts脚本工具：V4
工具下载地址和官方使用说明：
一、环境检查
源端平台信息检查
line 300
col PLATFORM_NAME for a40
select a.PLATFORM_ID,a.PLATFORM_NAME,a.ENDIAN_FORMAT from v$transportable_platform a, v$database b where a.PLATFORM_NAME=b.PLATFORM_NAME;
SQL> SQL>
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
6 AIX-Based Systems (64-bit) Big
Note：
记录平台ID，下面需要用到。
字符集检查
set line 300
col value$ for A40;
select name, value$ from props$ where name like '%CHARACTERSET%';
源：
NAME VALUE$
------------------------------ ----------------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
目标PDB：
检查数据库时区
源：
SQL> select dbtimezone from dual;
DBTIMEZONE
------------
+08:00
目标：
16:40:30 TARGET@cc1>select dbtimezone from dual;
DBTIMEZONE
------------
+00:00
检查不一致，可能会导致后面用expdp导入目标端的时候，如果有些表中有依赖于时间的TSLTZ字段ORA-39360: Table string was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
修改目标端时区：
alter session set container=ORCL1PDB ;
ALTER DATABASE SET TIME_ZONE='+08:00';
16:41:09 TARGET@cc1>ALTER DATABASE SET TIME_ZONE='+08:00';
Database altered.
修改后需要重启pdb
SQL> alter pluggable database ORCL1PDB close;
SQL> alter pluggable database ORCL1PDB open;
SQL> select dbtimezone from dual;
XTTS作为Oracle很受欢迎的数据迁移方式之一，本文将详细描述整个过程，大概主要迁移阶段分为：全量阶段、增量阶段、最后增量阶段和元数据迁移阶段。
xtts优点：
跨平台，跨版本，停机时间短，一致性高，适用大数据量的环境等
xtts缺点：
操作难度高，比较复杂等
参考MOS
V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup(Doc ID 2471245.1)
本文环境：
源数据库版本：11.2.0.4 单实例
源平台：aix7.1
目标数据库版本：19.3.0.0 RAC CDB
目标平台：linux 7 x86
业务表空间：
JANTBS
DOGTBS
ZLYTBS
xtts脚本工具：V4
一、环境检查
源端平台信息检查
line 300
col PLATFORM_NAME for a40
select a.PLATFORM_ID,a.PLATFORM_NAME,a.ENDIAN_FORMAT from v$transportable_platform a, v$database b where a.PLATFORM_NAME=b.PLATFORM_NAME;
SQL> SQL>
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
6 AIX-Based Systems (64-bit) Big
Note：
记录平台ID，下面需要用到。
字符集检查
set line 300
col value$ for A40;
select name, value$ from props$ where name like '%CHARACTERSET%';
源：
NAME VALUE$
------------------------------ ----------------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
目标PDB：
NAME VALUE$
------------------------------ ----------------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
Note：源端和目标端的字符集需要保持一致。
数据文件检查
源：
select ts#,file#,status from v$datafile where status not in ('ONLINE','SYSTEM');
no rows selected
SQL>
Note：
检查是否有不正常的数据文件。
表空间自包含检查
源：
execute DBMS_TTS.TRANSPORT_SET_CHECK('USERS,SOE',TRUE,TRUE);
select * from TRANSPORT_SET_VIOLATIONS;
SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK('USERS,SOE',TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> select * from TRANSPORT_SET_VIOLATIONS;
no rows selected
SQL>
Note：
表空间名称必须要大写，逗号分隔
主要检查是否有对象关联在系统表空间（SYSTEM,SYSAUX），
关联在系统表空间的对象需要迁移到非系统的表空间，
有关联的表空间需要一并传输。
二、XTTS配置
配置TNS
hisdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.30.30)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hisdb)
)
)
ORCL1PDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.33.207)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL1PDB)
)
)
Note：
rac注意，pdb配置单节点的vip
创建目录
源：
mkdir /home/oracle/xtts
mkdir -p /home/oracle/xttsbk
目标：
mkdir /home/oracle/xtts
mkdir -p /home/oracle/xttsbk
工具包解压
unzip -d /home/oracle/xtts rman_xttconvert_VER4.3.zip
[oracle@aix173 /home/oracle/xtts]$ls -lrt
total 528
-rw-r--r-- 1 oracle oinstall 1390 May 24 2017 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 52 May 24 2017 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 11710 May 24 2017 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 71 May 24 2017 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 5169 Feb 19 2019 xtt.newproperties
-rw-r--r-- 1 oracle oinstall 5169 Feb 19 2019 xtt.properties
-rw-r--r-- 1 oracle oinstall 180408 Jul 07 2019 xttdriver.pl
配置属性文件
cd /home/oracle/xtts
vi xtt.properties
tablespaces=USERS,SOE
platformid=13
src_scratch_location=/home/oracle/xttsbk
dest_scratch_location=/home/oracle/xttsbk
dest_datafile_location=+DATADG
parallel=4
destconnstr=wufan/wufan123456@hisdb
Note：
tablespaces里面列的是源端需要迁移的表空间名称，必须大写
平台ID是源平台的ID，
CDB环境需要设置连接串，
parallel可以根据数据量进行修改。
目标端如果是pdb，需要设置连接字符串destconnstr，是pdb的的用户和对应权限，我这里给的是dba权限
拷贝脚本到目标端
scp -r /home/oracle/xtts oracle@10.0.33.199:/home/oracle/
oracle用户密码：Oracle123
配置临时目录环境变量
源端：
su - oracle
export ORACLE_SID=hisdb2
export TMPDIR=/home/oracle/xtts
目标端：
su - oracle
export ORACLE_SID=cc1
export TMPDIR=/home/oracle/xtts
三、0级（全备）备份还原
源端创建测试表
SQL> create table abc.test1 tablespace soe as select * from dba_objects ;
Table created.
su - oracle
export ORACLE_SID=hisdb2
export TMPDIR=/home/oracle/xtts
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3
oracle@rac2[/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3
============================================================
trace file is /home/oracle/xtts/backup_Mar21_Tue_17_52_40_178//Mar21_Tue_17_52_40_178_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: platformid
Values: 13
Key: src_scratch_location
Values: /home/oracle/xttsbk
Key: parallel
Values: 4
Key: dest_scratch_location
Values: /home/oracle/xttsbk
Key: dest_datafile_location
Values: +DATADG
Key: rollparallel
Values: 2
Key: tablespaces
Values: USERS,SOE
Key: destconnstr
Values: sys/Oracle123@ORCL1PDB
Key: getfileparallel
Values: 4
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : hisdb2
ORACLE_HOME : /oracle/app/oracle/product/11.2.0/db
112040
PRIMARY
Running on PRIMARY
--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------
Parallel:4
scalar(or2
XXX: adding here for 2, 0, USERS,SOE
XXX: adding proper here for index 0, b4 added 'USERS'
,
XXX: adding proper here for index 0, added 'USERS'
XXX: adding proper here for index 1, b4 added 'SOE'
,
XXX: adding proper here for index 1, added 'SOE'
--------------------------------------------------------------------
Find list of datafiles in system
--------------------------------------------------------------------
sqlplus -L -s / as sysdba @/home/oracle/xtts/backup_Mar21_Tue_17_52_40_178//diff.sql +DATADG
::USERS5,+DATADG/USERS_5.dbf
::SOE
6,+DATADG/SOE_6.dbf
--------------------------------------------------------------------
Done finding list of datafiles in system
--------------------------------------------------------------------
/ as sysdba
size of tablespace 2
No. of tablespaces per batch 1
TABLESPACE STRING :'USERS'
Prepare source for Tablespaces:
'USERS' /home/oracle/xttsbk
xttpreparesrc.sql for 'USERS' started at Tue Mar 21 17:52:46 2023
#PLAN:USERS::::12474292
#CONVERT:host 'echo ts::USERS';
#CONVERT: convert from platform 'Linux x86 64-bit'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/home/oracle/xttsbk/USERS_5.tf'
5
#PLAN:5
#CONVERT: format '+DATADG/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/home/oracle/xttsbk/%N_%f.tf';
xttpreparesrc.sql for ended at Tue Mar 21 17:52:47 2023
#PLAN:USERS::::12474292#CONVERT:host 'echo ts::USERS';
#CONVERT: convert from platform 'Linux x86 64-bit'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/home/oracle/xttsbk/USERS_5.tf'
5
#PLAN:5
#CONVERT: format '+DATADG/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/home/oracle/xttsbk/%N_%f.tf';
/home/oracle/xtts/backup_Mar21_Tue_17_52_40_178//xttprepare.cmd
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 21 17:52:47 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: HISDB (DBID=2008814877)
RMAN> #PLAN:USERS::::12474292
2> #CONVERT:host 'echo ts::USERS';
3> #CONVERT: convert from platform 'Linux x86 64-bit'
4> #CONVERT: datafile
5> backup as copy tag 'prepare' datafile
6> #CONVERT: '/home/oracle/xttsbk/USERS_5.tf'
7> 5
8> #PLAN:5
9> #CONVERT: format '+DATADG/%N_%f.dbf'
10> #CONVERT: parallelism 4;
11> format '/home/oracle/xttsbk/%N_%f.tf';
12>
RMAN-03090: Starting backup at 21-MAR-23
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=494 instance=hisdb2 device type=DISK
RMAN-08580: channel ORA_DISK_1: starting datafile copy
RMAN-08522: input datafile file number=00005 name=+DATADG/hisdb/datafile/users.267.1096198861
RMAN-08586: output file name=/home/oracle/xttsbk/USERS_5.tf tag=PREPARE RECID=3 STAMP=1132077169
RMAN-08581: channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 21-MAR-23
Recovery Manager complete.
TABLESPACE STRING :'SOE'
Prepare source for Tablespaces:
'SOE' /home/oracle/xttsbk
xttpreparesrc.sql for 'SOE' started at Tue Mar 21 17:52:51 2023
#PLAN:SOE::::12474275
#CONVERT:host 'echo ts::SOE';
#CONVERT: convert from platform 'Linux x86 64-bit'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/home/oracle/xttsbk/SOE_6.tf'
6
#PLAN:6
#CONVERT: format '+DATADG/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/home/oracle/xttsbk/%N_%f.tf';
xttpreparesrc.sql for ended at Tue Mar 21 17:52:51 2023
#PLAN:SOE::::12474275#CONVERT:host 'echo ts::SOE';
#CONVERT: convert from platform 'Linux x86 64-bit'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/home/oracle/xttsbk/SOE_6.tf'
6
#PLAN:6
#CONVERT: format '+DATADG/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/home/oracle/xttsbk/%N_%f.tf';
/home/oracle/xtts/backup_Mar21_Tue_17_52_40_178//xttprepare.cmd
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 21 17:52:51 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: HISDB (DBID=2008814877)
RMAN> #PLAN:SOE::::12474275
2> #CONVERT:host 'echo ts::SOE';
3> #CONVERT: convert from platform 'Linux x86 64-bit'
4> #CONVERT: datafile
5> backup as copy tag 'prepare' datafile
6> #CONVERT: '/home/oracle/xttsbk/SOE_6.tf'
7> 6
8> #PLAN:6
9> #CONVERT: format '+DATADG/%N_%f.dbf'
10> #CONVERT: parallelism 4;
11> format '/home/oracle/xttsbk/%N_%f.tf';
12>
RMAN-03090: Starting backup at 21-MAR-23
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=494 instance=hisdb2 device type=DISK
RMAN-08580: channel ORA_DISK_1: starting datafile copy
RMAN-08522: input datafile file number=00006 name=+DATADG/hisdb/datafile/soe.273.1096202257
RMAN-06731: command backup:22.3% complete, time left 00:07:20
RMAN-06731: command backup:44.0% complete, time left 00:05:13
RMAN-06731: command backup:64.4% complete, time left 00:03:22
RMAN-06731: command backup:85.2% complete, time left 00:01:25
RMAN-08586: output file name=/home/oracle/xttsbk/SOE_6.tf tag=PREPARE RECID=4 STAMP=1132077745
RMAN-08581: channel ORA_DISK_1: datafile copy complete, elapsed time: 00:09:35
RMAN-03091: Finished backup at 21-MAR-23
Recovery Manager complete.
TABLESPACE STRING :''''
Prepare source for Tablespaces:
'''' /home/oracle/xttsbk
xttpreparesrc.sql for '''' started at Tue Mar 21 18:02:29 2023
xttpreparesrc.sql for ended at Tue Mar 21 18:02:30 2023
/home/oracle/xtts/backup_Mar21_Tue_17_52_40_178//xttprepare.cmd
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 21 18:02:30 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: HISDB (DBID=2008814877)
RMAN>
Recovery Manager complete.
--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------
/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'
TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'
TABLESPACE STRING :''''
Prepare newscn for Tablespaces: ''''
New /home/oracle/xtts/xttplan.txt with FROM SCN's generated
scalar(or2
XXX: adding here for 2, 0, USERS,SOE
XXX: adding proper here for index 0, b4 added 'USERS'
,
XXX: adding proper here for index 0, added 'USERS'
XXX: adding proper here for index 1, b4 added 'SOE'
,
XXX: adding proper here for index 1, added 'SOE'
Number of tb arrays is 2
::USERS:::SCN:::124744295=12474429,USERS,+DATADG/USERS_5.dbf
::SOE:::SCN:::12474455
6=12474455,SOE,+DATADG/SOE_6.dbf
::USERS:::SCN:::12474429
/home/oracle/xtts/newfile.txt: ::USERS:::SCN:::12474429
/home/oracle/xtts/newfile.txt: 5=12474429,USERS,+DATADG/USERS_5.dbf
/home/oracle/xtts/newfile.txt: ::SOE:::SCN:::12474455
/home/oracle/xtts/newfile.txt: 6=12474455,SOE,+DATADG/SOE_6.dbf
/home/oracle/xtts/backup_Mar21_Tue_17_52_40_178//xttnewdatafiles.txt.added: ::USERS:::SCN:::12474429
/home/oracle/xtts/backup_Mar21_Tue_17_52_40_178//xttnewdatafiles.txt.added: 5=12474429,USERS,+DATADG/USERS_5.dbf
Writing new 5=12474429,USERS,+DATADG/USERS_5.dbf
Writing1 new 5, +DATADG/USERS_5.dbf
/home/oracle/xtts/backup_Mar21_Tue_17_52_40_178//xttnewdatafiles.txt.added: ::SOE:::SCN:::12474455
/home/oracle/xtts/backup_Mar21_Tue_17_52_40_178//xttnewdatafiles.txt.added: 6=12474455,SOE,+DATADG/SOE_6.dbf
Writing new 6=12474455,SOE,+DATADG/SOE_6.dbf
Writing1 new 6, +DATADG/SOE_6.dbf
Added fname here 1:/home/oracle/xttsbk/USERS_5.tf
Added fname here 1:/home/oracle/xttsbk/SOE_6.tf
YYY: ::USERS:::SCN:::12474429
YYY: ::SOE:::SCN:::12474455
============================================================
No new datafiles added
=============================================================
拷贝到目标端
scp /home/oracle/xttsbk/* oracle@10.0.33.199:/home/oracle/xttsbk
scp res.txt oracle@10.0.33.199:/home/oracle/xtts
目标端还原
export ORACLE_SID=cc1
export TMPDIR=/home/oracle/xtts
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3
oracle@rac02[/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3
============================================================
trace file is /home/oracle/xtts/restore_Mar21_Tue_20_00_25_760//Mar21_Tue_20_00_25_760_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: parallel
Values: 4
Key: getfileparallel
Values: 4
Key: dest_datafile_location
Values: +DATADG
Key: destconnstr
Values: sys/Oracle123@ORCL1PDB
Key: rollparallel
Values: 2
Key: tablespaces
Values: USERS,SOE
Key: src_scratch_location
Values: /home/oracle/xttsbk
Key: platformid
Values: 13
Key: dest_scratch_location
Values: /home/oracle/xttsbk
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : cc1
ORACLE_HOME : /oracle/app/oracle/product/12.2.0/db
122000
#0:::5,13,USERS_5.dbf,0,12474292,0,0,0,USERS,USERS_5.dbf
--------------------------------------------------------------------
Performing convert for file 5
--------------------------------------------------------------------
#0:::6,13,SOE_6.dbf,0,12474275,0,0,0,SOE,SOE_6.dbf
--------------------------------------------------------------------
Performing convert for file 6
--------------------------------------------------------------------
/home/oracle/xtts/restore_Mar21_Tue_20_00_25_760//rman_convert_USERS_5.cmd
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Mar 21 20:00:26 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: CC:ORCL1PDB (DBID=784581796)
RMAN> convert from platform 'Linux x86 64-bit' datafile '/home/oracle/xttsbk/USERS_5.tf' format '+DATADG/USERS_5.dbf' ;
2>
RMAN-03090: Starting conversion at target at 21-MAR-23
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=504 instance=cc1 device type=DISK
RMAN-08589: channel ORA_DISK_1: starting datafile conversion
RMAN-08506: input file name=/home/oracle/xttsbk/USERS_5.tf
RMAN-08588: converted datafile=+DATADG/users_5.dbf
RMAN-08590: channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
RMAN-03091: Finished conversion at target at 21-MAR-23
Recovery Manager complete.
/home/oracle/xtts/restore_Mar21_Tue_20_00_25_760//rman_convert_SOE_6.cmd
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Mar 21 20:00:26 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: CC:ORCL1PDB (DBID=784581796)
RMAN> convert from platform 'Linux x86 64-bit' datafile '/home/oracle/xttsbk/SOE_6.tf' format '+DATADG/SOE_6.dbf' ;
2>
RMAN-03090: Starting conversion at target at 21-MAR-23
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=388 instance=cc1 device type=DISK
RMAN-08589: channel ORA_DISK_1: starting datafile conversion
RMAN-08506: input file name=/home/oracle/xttsbk/SOE_6.tf
RMAN-08588: converted datafile=+DATADG/soe_6.dbf
RMAN-08590: channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:03:15
RMAN-03091: Finished conversion at target at 21-MAR-23
Recovery Manager complete.
YYY:
全备完了以后，我发现库是begin backup的状态（正常不是这样）
select d.file#,d.name
from v$datafile d,v$tablespace t ,v$backup b
where d.ts#=t.ts#
and b.file#=d.file#
and b.status='ACTIVE' ;
然后导致我手工执行
alter system checkpoint;
数据文件并不会刷新，一直还是之前全备的时候的scn，这样导致的结果就是即使有增备变化，rman由于数据文件的scn没有变化，认为数据文件没有变化，不产生任何增备文件
alter database end backup ;
然后在
alter system checkpoint ;
数据文件scn就刷新了；
四、1级（增量）备份
SQL> create table abc.test2 tablespace soe as select * from dba_objects ;
Table created.
su - oracle
export ORACLE_SID=hisdb2
export TMPDIR=/home/oracle/xtts
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3
Note：
此阶段可以一直重复下去，
直到源和目标数据差异很小，
有助于缩短停机时间。
建议开启块跟踪，有助于提升增量备份的速度。
alter database enable block change tracking using file '/oracle/app/oracle/oradata/fordb/bltracking.f';
oracle@rac2[/home/oracle/xtts]$export TMPDIR=/home/oracle/xtts
oracle@rac2[/home/oracle/xtts]$export ORACLE_SID=hisdb2
oracle@rac2[/home/oracle/xtts]$
oracle@rac2[/home/oracle/xtts]$
oracle@rac2[/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3
============================================================
trace file is /home/oracle/xtts/backup_Mar21_Tue_14_32_28_706//Mar21_Tue_14_32_28_706_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: platformid
Values: 13
Key: src_scratch_location
Values: /home/oracle/xttsbk
Key: parallel
Values: 4
Key: dest_scratch_location
Values: /home/oracle/xttsbk
Key: dest_datafile_location
Values: +DATADG
Key: rollparallel
Values: 2
Key: tablespaces
Values: SOE,USERS
Key: destconnstr
Values: sys/Oracle123@ORCL1PDB
Key: getfileparallel
Values: 4
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : hisdb2
ORACLE_HOME : /oracle/app/oracle/product/11.2.0/db
112040
PRIMARY
Running on PRIMARY
--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------
scalar(or2
XXX: adding here for 2, 0, SOE,USERS
XXX: adding proper here for index 0, b4 added 'SOE'
,
XXX: adding proper here for index 0, added 'SOE'
XXX: adding proper here for index 1, b4 added 'USERS'
,
XXX: adding proper here for index 1, added 'USERS'
Number of tb arrays is 2
::USERS:::SCN:::124651085=12465108,USERS,+DATADG/USERS_5.dbf
::SOE:::SCN:::12465108
6=12465108,SOE,+DATADG/SOE_6.dbf
::USERS:::SCN:::12465108
/home/oracle/xtts/newfile.txt: ::USERS:::SCN:::12465108
/home/oracle/xtts/newfile.txt: 5=12465108,USERS,+DATADG/USERS_5.dbf
/home/oracle/xtts/newfile.txt: ::SOE:::SCN:::12465108
/home/oracle/xtts/newfile.txt: 6=12465108,SOE,+DATADG/SOE_6.dbf
/home/oracle/xtts/backup_Mar21_Tue_14_32_28_706//xttnewdatafiles.txt.added: ::USERS:::SCN:::12465108
/home/oracle/xtts/backup_Mar21_Tue_14_32_28_706//xttnewdatafiles.txt.added: 5=12465108,USERS,+DATADG/USERS_5.dbf
Writing new 5=12465108,USERS,+DATADG/USERS_5.dbf
Writing1 new 5, +DATADG/USERS_5.dbf
/home/oracle/xtts/backup_Mar21_Tue_14_32_28_706//xttnewdatafiles.txt.added: ::SOE:::SCN:::12465108
/home/oracle/xtts/backup_Mar21_Tue_14_32_28_706//xttnewdatafiles.txt.added: 6=12465108,SOE,+DATADG/SOE_6.dbf
Writing new 6=12465108,SOE,+DATADG/SOE_6.dbf
Writing1 new 6, +DATADG/SOE_6.dbf
Added fname here 1:/home/oracle/xttsbk/USERS_5.tf
Added fname here 1:/home/oracle/xttsbk/SOE_6.tf
YYY: ::USERS:::SCN:::12465108
YYY: ::SOE:::SCN:::12465108
============================================================
No new datafiles added
=============================================================
/ as sysdba
size of tablespace 2
No. of tablespaces per batch 1
TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'
SOE::::12465108 6
TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'
USERS::::12465108 5
TABLESPACE STRING :''''
Prepare newscn for Tablespaces: ''''
--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------
/home/oracle/xtts/backup_Mar21_Tue_14_32_28_706//rmanincr.cmd
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 21 14:32:29 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: HISDB (DBID=2008814877)
RMAN> set nocfau;
2> host 'echo ts::SOE';
3> backup incremental from scn 12075457
4> tablespace 'SOE' format
5> '/home/oracle/xttsbk/%U';
6> set nocfau;
7> host 'echo ts::USERS';
8> backup incremental from scn 12075457
9> tablespace 'USERS' format
10> '/home/oracle/xttsbk/%U';
11>
RMAN-03023: executing command: SET NOCFAU
RMAN-06009: using target database control file instead of recovery catalog
ts::SOE
RMAN-06134: host command complete
RMAN-03090: Starting backup at 21-MAR-23
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=392 instance=hisdb2 device type=DISK
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00006 name=+DATADG/hisdb/datafile/soe.273.1096202257
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 21-MAR-23
RMAN-06731: command backup:52.3% complete, time left 00:01:54
RMAN-06731: command backup:100.0% complete, time left 00:00:00
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 21-MAR-23
RMAN-08530: piece handle=/home/oracle/xttsbk/2f1njsbv_1_1 tag=TAG20230321T143231 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:04:05
RMAN-03091: Finished backup at 21-MAR-23
RMAN-03023: executing command: SET NOCFAU
ts::USERS
RMAN-06134: host command complete
RMAN-03090: Starting backup at 21-MAR-23
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00005 name=+DATADG/hisdb/datafile/users.267.1096198861
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 21-MAR-23
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 21-MAR-23
RMAN-08530: piece handle=/home/oracle/xttsbk/2g1njsjl_1_1 tag=TAG20230321T143636 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 21-MAR-23
Recovery Manager complete.
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------
/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'
TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'
TABLESPACE STRING :''''
Prepare newscn for Tablespaces: ''''
New /home/oracle/xtts/xttplan.txt with FROM SCN's generated
######################################################################################################################################
拷贝增量到目标端
scp `cat incrbackups.txt` oracle@10.0.33.199:/home/oracle/xttsbk
scp res.txt oracle@10.0.33.199:/home/oracle/xtts
传目标端如果是通过root传的，需要修改属主s
chown -R oracle:oinstall /home/oracle/xttsbk/
chown -R oracle:oinstall /home/oracle/xtts/
目标端还原增备
su - oracle
export ORACLE_SID=cc1
export TMPDIR=/home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3
Note：从日志可以看到在进行增量恢复了。
oracle@rac02[/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3
============================================================
trace file is /home/oracle/xtts/restore_Mar21_Tue_15_06_53_989//Mar21_Tue_15_06_53_989_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: parallel
Values: 4
Key: dest_datafile_location
Values: +DATADG
Key: rollparallel
Values: 2
Key: destconnstr
Values: sys/Oracle123@ORCL1PDB
Key: src_scratch_location
Values: /home/oracle/xttsbk
Key: platformid
Values: 13
Key: getfileparallel
Values: 4
Key: dest_scratch_location
Values: /home/oracle/xttsbk
Key: tablespaces
Values: SOE,USERS
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : cc1
ORACLE_HOME : /oracle/app/oracle/product/12.2.0/db
122000
6
5
YYY: SOE_6.dbf::6:::1=2f1njsbv_1_1 USERS_5.dbf::5:::1=2g1njsjl_1_1
--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------
ROLLFORWARD: Starting DB in nomount mode
ORACLE instance started.
Total System Global Area 2466250752 bytes
Fixed Size 8795760 bytes
Variable Size 805308816 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7979008 bytes
rdfno 6
BEFORE ROLLPLAN
datafile number : 6
datafile name : +DATADG/SOE_6.dbf
AFTER ROLLPLAN
rdfno 5
BEFORE ROLLPLAN
datafile number : 5
datafile name : +DATADG/USERS_5.dbf
AFTER ROLLPLAN
sqlplus -L -s "/ as sysdba" @/home/oracle/xtts/restore_Mar21_Tue_15_06_53_989//xxttconv_2g1njsjl_1_1_5.sql /home/oracle/xttsbk/2g1njsjl_1_1 /home/oracle/xttsbk 13
CONVERTED BACKUP PIECE/home/oracle/xttsbk/xib_2g1njsjl_1_1_5
PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtts/restore_Mar21_Tue_15_06_53_989//xxttconv_2f1njsbv_1_1_6.sql /home/oracle/xttsbk/2f1njsbv_1_1 /home/oracle/xttsbk 13
CONVERTED BACKUP PIECE/home/oracle/xttsbk/xib_2f1njsbv_1_1_6
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
PL/SQL procedure successfully completed.
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------
############################################################################################################################################
增备完了，我检查了下数据库状态不是bebin backup的状态
select d.file#,d.name
from v$datafile d,v$tablespace t ,v$backup b
where d.ts#=t.ts#
and b.file#=d.file#
and b.status='ACTIVE' ;
set pages 1000 lines 1000
col file# for 99999999999
col CHECKPOINT_CHANGE# for 99999999999
col NAME for a60
col LAST_CHANGE# for 99999999999999
select FILE# , CHECKPOINT_CHANGE# ,NAME, LAST_CHANGE# from v$datafile ;
select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;
然后导致我手工执行（最后一次增备的时候，由于需要置表空间为read only，此时他会自己刷表空间数据文件的scn到最新，所以不需要手工checkpoint）
alter system checkpoint;
数据文件并不会刷新，一直还是之前全备的时候的scn，这样导致的结果就是即使有增备变化，rman由于数据文件的scn没有变化，认为数据文件没有变化，不产生任何增备文件
alter database end backup ;
然后在
alter system checkpoint ;
数据文件scn就刷新了；
五、最后（停机阶段）的备份还原
Note：
此阶段就是停机阶段进行最后的补增量。
SQL> create table abc.test3 tablespace soe as select * from dba_objects ;
Table created.
原机设置只读表空间
alter tablespace users read only;
alter tablespace soe read only;
SQL> alter tablespace users read only;
Tablespace altered.
SQL> alter tablespace soe read only;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
继续增备份
su - oracle
export ORACLE_SID=hisdb2
export TMPDIR=/home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3
oracle@rac2[/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3
============================================================
trace file is /home/oracle/xtts/backup_Mar21_Tue_15_15_33_920//Mar21_Tue_15_15_33_920_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: platformid
Values: 13
Key: src_scratch_location
Values: /home/oracle/xttsbk
Key: parallel
Values: 4
Key: dest_scratch_location
Values: /home/oracle/xttsbk
Key: dest_datafile_location
Values: +DATADG
Key: rollparallel
Values: 2
Key: tablespaces
Values: SOE,USERS
Key: destconnstr
Values: sys/Oracle123@ORCL1PDB
Key: getfileparallel
Values: 4
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : hisdb2
ORACLE_HOME : /oracle/app/oracle/product/11.2.0/db
112040
PRIMARY
Running on PRIMARY
--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------
scalar(or2
XXX: adding here for 2, 0, SOE,USERS
XXX: adding proper here for index 0, b4 added 'SOE'
,
XXX: adding proper here for index 0, added 'SOE'
XXX: adding proper here for index 1, b4 added 'USERS'
,
XXX: adding proper here for index 1, added 'USERS'
Number of tb arrays is 2
::USERS:::SCN:::124666615=12466661,USERS,+DATADG/USERS_5.dbf
::SOE:::SCN:::12466672
6=12466672,SOE,+DATADG/SOE_6.dbf
::USERS:::SCN:::12466661
/home/oracle/xtts/newfile.txt: ::USERS:::SCN:::12466661
/home/oracle/xtts/newfile.txt: 5=12466661,USERS,+DATADG/USERS_5.dbf
/home/oracle/xtts/newfile.txt: ::SOE:::SCN:::12466672
/home/oracle/xtts/newfile.txt: 6=12466672,SOE,+DATADG/SOE_6.dbf
/home/oracle/xtts/backup_Mar21_Tue_15_15_33_920//xttnewdatafiles.txt.added: ::USERS:::SCN:::12466661
/home/oracle/xtts/backup_Mar21_Tue_15_15_33_920//xttnewdatafiles.txt.added: 5=12466661,USERS,+DATADG/USERS_5.dbf
Writing new 5=12466661,USERS,+DATADG/USERS_5.dbf
Writing1 new 5, +DATADG/USERS_5.dbf
/home/oracle/xtts/backup_Mar21_Tue_15_15_33_920//xttnewdatafiles.txt.added: ::SOE:::SCN:::12466672
/home/oracle/xtts/backup_Mar21_Tue_15_15_33_920//xttnewdatafiles.txt.added: 6=12466672,SOE,+DATADG/SOE_6.dbf
Writing new 6=12466672,SOE,+DATADG/SOE_6.dbf
Writing1 new 6, +DATADG/SOE_6.dbf
Added fname here 1:/home/oracle/xttsbk/USERS_5.tf
Added fname here 1:/home/oracle/xttsbk/SOE_6.tf
YYY: ::USERS:::SCN:::12466661
YYY: ::SOE:::SCN:::12466672
============================================================
No new datafiles added
=============================================================
/ as sysdba
size of tablespace 2
No. of tablespaces per batch 1
TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'
SOE::::12466672 6
TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'
USERS::::12466661 5
TABLESPACE STRING :''''
Prepare newscn for Tablespaces: ''''
--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------
/home/oracle/xtts/backup_Mar21_Tue_15_15_33_920//rmanincr.cmd
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 21 15:15:33 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: HISDB (DBID=2008814877)
RMAN> set nocfau;
2> host 'echo ts::SOE';
3> backup incremental from scn 12465108
4> tablespace 'SOE' format
5> '/home/oracle/xttsbk/%U';
6> set nocfau;
7> host 'echo ts::USERS';
8> backup incremental from scn 12465108
9> tablespace 'USERS' format
10> '/home/oracle/xttsbk/%U';
11>
RMAN-03023: executing command: SET NOCFAU
RMAN-06009: using target database control file instead of recovery catalog
ts::SOE
RMAN-06134: host command complete
RMAN-03090: Starting backup at 21-MAR-23
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=588 instance=hisdb2 device type=DISK
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00006 name=+DATADG/hisdb/datafile/soe.273.1096202257
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 21-MAR-23
RMAN-06731: command backup:51.5% complete, time left 00:01:57
RMAN-06731: command backup:100.0% complete, time left 00:00:00
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 21-MAR-23
RMAN-08530: piece handle=/home/oracle/xttsbk/2h1njuso_1_1 tag=TAG20230321T151536 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:04:05
RMAN-03091: Finished backup at 21-MAR-23
RMAN-03023: executing command: SET NOCFAU
ts::USERS
RMAN-06134: host command complete
RMAN-03090: Starting backup at 21-MAR-23
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00005 name=+DATADG/hisdb/datafile/users.267.1096198861
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 21-MAR-23
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 21-MAR-23
RMAN-08530: piece handle=/home/oracle/xttsbk/2i1njv4e_1_1 tag=TAG20230321T151942 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 21-MAR-23
Recovery Manager complete.
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------
/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'
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/xtts/backup_Mar21_Tue_15_15_33_920//xttpreparenextiter.sql
####################################################################
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284
TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'
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/xtts/backup_Mar21_Tue_15_15_33_920//xttpreparenextiter.sql
####################################################################
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284
TABLESPACE STRING :''''
Prepare newscn for Tablespaces: ''''
New /home/oracle/xtts/xttplan.txt with FROM SCN's generated
##########################################################################################################################################################################
scp `cat incrbackups.txt` oracle@10.0.33.199:/home/oracle/xttsbk
scp res.txt oracle@10.0.33.199:/home/oracle/xtts
传目标端如果是通过root传的，需要修改属主s
chown -R oracle:oinstall /home/oracle/xttsbk/
chown -R oracle:oinstall /home/oracle/xtts/
目标端还原
su - oracle
export ORACLE_SID=cc1
export TMPDIR=/home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3
oracle@rac02[/home/oracle/xtts]$export ORACLE_SID=cc1
oracle@rac02[/home/oracle/xtts]$export TMPDIR=/home/oracle/xtts
oracle@rac02[/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3
============================================================
trace file is /home/oracle/xtts/restore_Mar21_Tue_15_31_16_778//Mar21_Tue_15_31_16_778_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: dest_datafile_location
Values: +DATADG
Key: src_scratch_location
Values: /home/oracle/xttsbk
Key: destconnstr
Values: sys/Oracle123@ORCL1PDB
Key: parallel
Values: 4
Key: platformid
Values: 13
Key: rollparallel
Values: 2
Key: dest_scratch_location
Values: /home/oracle/xttsbk
Key: getfileparallel
Values: 4
Key: tablespaces
Values: SOE,USERS
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : cc1
ORACLE_HOME : /oracle/app/oracle/product/12.2.0/db
122000
6
5
YYY: SOE_6.dbf::6:::1=2h1njuso_1_1 USERS_5.dbf::5:::1=2i1njv4e_1_1
--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------
ROLLFORWARD: Starting DB in nomount mode
ORACLE instance started.
Total System Global Area 2466250752 bytes
Fixed Size 8795760 bytes
Variable Size 805308816 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7979008 bytes
rdfno 6
BEFORE ROLLPLAN
datafile number : 6
datafile name : +DATADG/SOE_6.dbf
AFTER ROLLPLAN
rdfno 5
BEFORE ROLLPLAN
datafile number : 5
datafile name : +DATADG/USERS_5.dbf
AFTER ROLLPLAN
sqlplus -L -s "/ as sysdba" @/home/oracle/xtts/restore_Mar21_Tue_15_31_16_778//xxttconv_2i1njv4e_1_1_5.sql /home/oracle/xttsbk/2i1njv4e_1_1 /home/oracle/xttsbk 13
CONVERTED BACKUP PIECE/home/oracle/xttsbk/xib_2i1njv4e_1_1_5
PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtts/restore_Mar21_Tue_15_31_16_778//xxttconv_2h1njuso_1_1_6.sql /home/oracle/xttsbk/2h1njuso_1_1 /home/oracle/xttsbk 13
CONVERTED BACKUP PIECE/home/oracle/xttsbk/xib_2h1njuso_1_1_6
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
PL/SQL procedure successfully completed.
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------
六、迁移元数据
创建目录对象
源：
su - oracle
mkdir /home/oracle/dumpfile
create directory mydir as '/home/oracle/dumpfile';
目标：
su - oracle
mkdir /home/oracle/dumpfile
create directory mydir as '/home/oracle/dumpfile';
Note：源和目标都需要创建，可以自行定义路径。
源端导出
vi xtts_exp.par
dumpfile=xttdump.dmp
directory=mydir
statistics=NONE
transport_tablespaces=USERS,SOE
transport_full_check=y
logfile=tts_export.log
expdp system/System123 parfile=xtts_exp.par
oracle@rac2[/home/oracle/xtts]$expdp system/System123 parfile=xtts_exp.par
Export: Release 11.2.0.4.0 - Production on Tue Mar 21 15:37:29 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "statistics=NONE" Location: Parameter File, ignored.
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=xtts_exp.par reuse_dumpfiles=true
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/dumpfile/xttdump.dmp
******************************************************************************
Datafiles required for transportable tablespace SOE:
+DATADG/hisdb/datafile/soe.273.1096202257
Datafiles required for transportable tablespace USERS:
+DATADG/hisdb/datafile/users.267.1096198861
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Mar 21 15:38:22 2023 elapsed 0 00:00:45
拷贝到目标端
scp /home/oracle/dumpfile/xttdump.dmp oracle@10.0.33.199:/home/oracle/dumpfile
chown -R oracle:oinstall /home/oracle/dumpfile/
目标端导入
源端生成模板
su - oracle
export ORACLE_SID=cc1
export TMPDIR=/home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -e
oracle@rac2[/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl -e
============================================================
trace file is /home/oracle/xtts/generate_Mar21_Tue_15_41_54_216//Mar21_Tue_15_41_54_216_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Generating plugin
--------------------------------------------------------------------
--------------------------------------------------------------------
Done generating plugin file /home/oracle/xtts/xttplugin.txt
--------------------------------------------------------------------
Note：在数据文件繁多的时候模板就体现作用了，transport_datafiles会帮忙写好。
#原机查询所有的业务用户
select username from dba_users ;
create user wufan identified by wufan123 ;
grant dba to wufan ;
create user SOE identified by soe123456;
grant dba to soe ;
create user abc identified by abc123456 ;
grant dba to abc ;
Note：权限根据实际情况而定。
15:37:51 TARGET@cc1>show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL1PDB MOUNTED
15:51:30 TARGET@cc1>alter session set container=ORCL1PDB ;
Session altered.
15:51:58 TARGET@cc1>alter pluggable database ORCL1PDB open ;
Pluggable database altered.
15:52:28 TARGET@cc1>show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCL1PDB READ WRITE NO
15:52:49 TARGET@cc1>create user wufan identified by wufan123 ;
User created.
15:53:03 TARGET@cc1>grant dba to wufan ;
Grant succeeded.
目标端开始导入
vi xtts_imp.par
dumpfile=xttdump.dmp
directory=mydir
transport_datafiles='+DATADG/USERS_5.dbf','+DATADG/SOE_6.dbf'
logfile=xtts_imp20230321.log
impdp wufan/wufan123@ORCL1PDB parfile=xtts_imp.par
oracle@rac02[/home/oracle/xtts]$impdp wufan/wufan123@ORCL1PDB parfile=xtts_imp.par
Import: Release 12.2.0.1.0 - Production on Tue Mar 21 16:11:27 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "WUFAN"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone is +08:00 and target time zone is +00:00.
Starting "WUFAN"."SYS_IMPORT_TRANSPORTABLE_01": wufan/********@ORCL1PDB parfile=xtts_imp.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39360: Table "SOE"."ORDERS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"SOE"."ORD_SALES_REP_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORDER_PK" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_ORDER_DATE_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_CUSTOMER_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"SOE"."ORD_WAREHOUSE_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"SOE"."ORDER_TOTAL_MIN" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"SOE"."ORDER_MODE_LOV" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"SOE"."ORDER_PK" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"SOE"."ORDER_ITEMS_ORDER_ID_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "SOE"."ORDER_ITEMS" ADD CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID") REFERENCES "SOE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE
ORA-39112: Dependent object type REF_CONSTRAINT:"SOE"."ORDERS_CUSTOMER_ID_FK" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "WUFAN"."SYS_IMPORT_TRANSPORTABLE_01" completed with 11 error(s) at Tue Mar 21 16:25:46 2023 elapsed 0 00:14:10
####################################################################################################################################
#目标端由于有users表空间，导致导入报错，做下面的操作
create tablespace users1 datafile '+datadg' size 1g autoextend off ;
select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
alter database default tablespace users1;
drop tablespace users ;
set pagesize 1000
set linesize 1000
col index_name for a30
col owner for a10
col TABLESPACE_NAME for a30
col TABLE_NAME for a40
col TABLE_OWNER for a10
select index_name,owner,TABLESPACE_NAME,TABLE_OWNER,TABLE_NAME from dba_indexes where index_name=upper('ORD_ORDER_DATE_IX');
select owner,table_name,tablespace_name from dba_tables where table_name = upper('ORDERS');
select owner,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects where OBJECT_NAME = upper('ORDERS');
select table_name from dba_tables where table_name like upper('%part%') ;
ORA-39360: Table "SOE"."ORDERS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
ORA-39112: Dependent object type INDEX:"SOE"."ORD_SALES_REP_IX" skipped, base object type TABLE:"SOE"."ORDERS" creation failed
这两类错误说明：
ORA-39360:是源端和目标端的时区不同，导致导入的时候在目标端创建表失败（表里面有timestamp类型的字段，该类型依赖于源端的时区）
ORA-39112:该问题本例是因为上面的表创建不成功，导致表上面的索引创建不成功；还有一种可能的情况是所以创建在不同的表空间，而该表空间没有在源端导出来；
该例中我们第一次导入报错了，有上面两种错，我们可以在目标端删除导入的表空间后重新导入；
alter session set container=ORCL1PDB ;
17:02:38 TARGET@cc1>drop tablespace users ;
Tablespace dropped.
17:02:44 TARGET@cc1>drop tablespace soe ;
drop tablespace soe
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
#下面的语句会将表空间restore出来的数据文件一起删掉了，然后导入的时候就报错，需要重新全备重新导
17:02:54 TARGET@cc1>drop tablespace soe INCLUDING CONTENTS ;
Tablespace dropped.
################################################################################################################
第二次导入成功如下：
oracle@rac02[/home/oracle/xtts]$impdp wufan/wufan123@ORCL1PDB parfile=xtts_imp.par
Import: Release 12.2.0.1.0 - Production on Tue Mar 21 21:23:12 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "WUFAN"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "WUFAN"."SYS_IMPORT_TRANSPORTABLE_01": wufan/********@ORCL1PDB parfile=xtts_imp.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "WUFAN"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Mar 21 21:37:13 2023 elapsed 0 00:13:45
############################################################################################################################################
###验证数据
set pagesize 1000 linesize 1000
col owner for a20
col table_name for a20
select OWNER,TABLE_NAME from dba_tables where table_name like upper('%test%') ;
21:39:22 TARGET@cc1>select OWNER,TABLE_NAME from dba_tables where table_name like upper('%test%') ;
OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
DBSNMP
MGMT_LATEST
DBSNMP
MGMT_LATEST_SQL
ABC
TEST3
OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
ABC
TEST2
ABC
TEST1
21:40:04 TARGET@cc1>select count(1) from abc.test1 ;
COUNT(1)
----------
68713
21:53:20 TARGET@cc1>select count(1) from abc.test2;
COUNT(1)
----------
68714
21:53:27 TARGET@cc1>select count(1) from abc.test3;
COUNT(1)
----------
68715
21:53:33 TARGET@cc1>
##至此迁移成功
扫一扫
816
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
