XTTS迁移表空间--DFT方式
上篇文章说了RMAN BACKUP的方式进行XTTS表空间的迁移，还有其余两种方案。本次测试DFT的方式进行XTTS迁移，他与RMAN之间的区别就是，在初始化备份的时候，DFT可以不落地，直接通过网络从源端存储到目标端数据目录中，适用于本地没有多余可用空间的场景。dbms_file_transfer DBMS_FILE_TRANSFER 包是 Oracle 提供的一个用于复制二进制数据库文件或在数据库之间传输二进制文件的程序包，在 XTTS 迁移中，利用不同的参数进行数据文件传输转换完成迁移。
但是在v4版本中，此方案已经不支持了。
以下基于上篇测试，进行简单测试如下：
迁移步骤
环境说明
| column1 | 源端 | 目标端 |
|---|---|---|
| db类型 | 单实例 | 单实例 |
| db version | 10.2.0.4 | 11.2.0.4 |
| db 存储 | 文件系统 | ASM |
| OS版本及kernel版本 | SunOS 5.10 Generic_147148-26 | RHEL 6.8 |
| db name | orcl | orcl |
| 用户表空间 | old_data_tbs | old_data_tbs |
| 业务用户 | old_test | old_test |
| 字节序 | Little | Little |
| 归档模式 | 归档模式 | 归档模式 |
源库环境准备a
--造数据
create tablespace dft_data_tbs datafile size 1G;
create user dft identified by "dft" default tablespace dft_data_tbs;
grant connect ,resource to dft;
alter user dft quota unlimited on dft_data_tbs;
conn dft/dft
create table TB0101_08_09(id number,name varchar2(1000),other_col char(1000));
begin
for i in 1..1000 loop
insert into TB0101_08_09 values(i,lpad('a',995,'a')||i,'other col..');
end loop;
commit;
end;
/
create index idx_id_name_TB0101_08_09 on TB0101_08_09(id,name) online;
create index idx_name_id_TB0101_08_09 on TB0101_08_09(name,id) online;
exec dbms_stats.gather_table_stats(null,'TB0101_08_09',NO_INVALIDATE => FALSE);
数据库环境检查
和上篇相同
前期处理
唯一不同的就是xtts的配置参数，以下进行说明。
上传xtts v3脚本，配置参数.
目标端：
-bash-3.2$ cat xtt.properties tablespaces=DFT_DATA_TBS platformid=20 srclink=to_old backupformat=/export/home/oracle/xtts/backup stageondest=/home/oracle/xtts/backup srcdir=SDIR1 dstdir=DDIR1 backupondest=/home/oracle/xtts/backup asm_home=/u01/app/grid/11.2.0/grid asm_sid=+ASM parallel=3 rollparallel=2 getfileparallel=8
整个目录传输到目标端：
-bash-3.2$ scp -r /export/home/oracle/xtts/* 10.1.11.12:/home/oracle/
创建相关directories
select file_name from dba_data_Files;
--源端
create directory SDIR1 as '/export/home/oracle/opt/oradata/ORCL/datafile/';
grant read,write on directory SDIR1 to public;
--目标端
create directory DDIR1 as '+DATADG/orcl/datafile/';
grant read,write on directory DDIR1 to public;
全量传输
- xtts执行-S参数，源端生成传输的文件清单
export PERL5LIB=$ORACLE_HOME/perl/lib
export TMPDIR=/export/home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -S
执行过程为：
-bash-3.2$ export PERL5LIB=$ORACLE_HOME/perl/lib -bash-3.2$ export TMPDIR=/export/home/oracle/xtts -bash-3.2$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -S ============================================================ trace file is /export/home/oracle/xtts/setupgetfile_Oct19_Thu_19_00_38_73//Oct19_Thu_19_00_38_73_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Starting prepare phase -------------------------------------------------------------------- Prepare source for Tablespaces: 'DFT_DATA_TBS' /home/oracle/xtts/backup xttpreparesrc.sql for 'DFT_DATA_TBS' started at Thu Oct 19 19:00:38 2023 xttpreparesrc.sql for ended at Thu Oct 19 19:00:38 2023 Prepare source for Tablespaces: '''' /home/oracle/xtts/backup xttpreparesrc.sql for '''' started at Thu Oct 19 19:00:38 2023 xttpreparesrc.sql for ended at Thu Oct 19 19:00:38 2023 -------------------------------------------------------------------- Done with prepare phase -------------------------------------------------------------------- -bash-3.2$ ls -ltr total 660 -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 142020 Sep 20 2018 xttdriver.pl -rwxrwxr-x 1 oracle oinstall 34672 Oct 18 11:33 rman_xttconvert_v3.zip drwxr-xr-x 2 oracle oinstall 2 Oct 19 18:43 backup -rw-r--r-- 1 oracle oinstall 7969 Oct 19 18:56 xtt.propertiesbak -rw-r--r-- 1 oracle oinstall 340 Oct 19 18:56 xtt.properties -rw-r--r-- 1 oracle oinstall 25 Oct 19 19:00 xttplan.txt -rw-r--r-- 1 oracle oinstall 101 Oct 19 19:00 xttnewdatafiles.txt_temp -rw-r--r-- 1 oracle oinstall 53 Oct 19 19:00 xttnewdatafiles.txt -rw-r--r-- 1 oracle oinstall 72 Oct 19 19:00 getfile.sql drwxr-xr-x 2 oracle oinstall 8 Oct 19 19:00 setupgetfile_Oct19_Thu_19_00_38_73 -bash-3.2$ cat xttplan.txt DFT_DATA_TBS::::660754 6 -bash-3.2$ cat xttnewdatafiles.txt ::DFT_DATA_TBS 6,DDIR1:/o1_mf_dft_data_lm21tsvr_.dbf -bash-3.2$ cat getfile.sql 0,SDIR1,o1_mf_dft_data_lm21tsvr_.dbf,DDIR1,o1_mf_dft_data_lm21tsvr_.dbf -bash-3.2$ cat xttnewdatafiles.txt_temp ::DFT_DATA_TBS 6,DESTDIR:/export/home/oracle/opt/oradata/ORCL/datafile,/o1_mf_dft_data_lm21tsvr_.dbf -bash-3.2$ pwd /export/home/oracle/xtts/backup -bash-3.2$ ls -bash-3.2$
只是生成的传输文件列表，没有备份，backup文件夹为空。
2. 将源端的getfile.sql、xttnewdatafiles.txt传到目标端
-bash-3.2$ scp getfile.sql xttnewdatafiles.txt 10.1.11.12:/home/oracle/xtts
- 目标端同步数据文件
[oracle@11gasm xtts]$ export TMPDIR=/home/oracle/xtts [oracle@11gasm xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -G
执行过程如下：
[oracle@11gasm xtts]$ export TMPDIR=/home/oracle/xtts [oracle@11gasm xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -G ============================================================ trace file is /home/oracle/xtts/getfile_Oct19_Thu_19_07_18_745//Oct19_Thu_19_07_18_745_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Getting datafiles from source -------------------------------------------------------------------- -------------------------------------------------------------------- Executing getfile for /home/oracle/xtts/getfile_Oct19_Thu_19_07_18_745//getfile_sdir1_o1_mf_dft_data_lm21tsvr_.dbf_0.sql -------------------------------------------------------------------- -------------------------------------------------------------------- Completed getting datafiles from source --------------------------------------------------------------------
一般库比较大，推荐nohup后台执行：
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl -G &
本步骤将花费数据库迁移的大部分时间，因为本步骤会传输源端的数据文件到目标端。
本步骤执行完成，可以在目标端数据库数据文件存储目录发现从源端传输过来的数据文件。
若字节序格式不同，也会在该步骤自动隐式进行转换。
结果执行完成后，数据文件已经生成
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 19 17:00:00 Y OLD_DATA_TBS.267.1150643465
DATAFILE UNPROT COARSE OCT 19 17:00:00 Y SYSAUX.261.1023146139
DATAFILE UNPROT COARSE OCT 19 17:00:00 Y SYSTEM.260.1023146137
DATAFILE UNPROT COARSE OCT 19 17:00:00 Y UNDOTBS1.262.1023146139
DATAFILE UNPROT COARSE OCT 19 17:00:00 Y USERS.264.1023146143
DATAFILE UNPROT COARSE OCT 19 17:00:00 Y ZHUO.266.1024792071
N old_data_tbs_5.dbf => +DATADG/ORCL/DATAFILE/OLD_DATA_TBS.267.1150643465
DATAFILE UNPROT COARSE OCT 19 19:00:00 Y FILE_TRANSFER.268.1150657639
N o1_mf_dft_data_lm21tsvr_.dbf => +DATADG/ORCL/DATAFILE/FILE_TRANSFER.268.1150657639
多次前滚
- 源端做增量备份
-bash-3.2$ export PERL5LIB=$ORACLE_HOME/perl/lib -bash-3.2$ export TMPDIR=/export/home/oracle/xtts -bash-3.2$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i ============================================================ trace file is /export/home/oracle/xtts/incremental_Oct19_Thu_19_20_56_938//Oct19_Thu_19_20_56_938_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Backup incremental -------------------------------------------------------------------- Prepare source for Tablespaces: 'DFT_DATA_TBS' /home/oracle/xtts/backup xttpreparesrc.sql for 'DFT_DATA_TBS' started at Thu Oct 19 19:20:56 2023 xttpreparesrc.sql for ended at Thu Oct 19 19:20:56 2023 Prepare source for Tablespaces: '''' /home/oracle/xtts/backup xttpreparesrc.sql for '''' started at Thu Oct 19 19:20:56 2023 xttpreparesrc.sql for ended at Thu Oct 19 19:20:56 2023 ============================================================ No new datafiles added ============================================================= Prepare newscn for Tablespaces: 'DFT_DATA_TBS' Prepare newscn for Tablespaces: '''' -------------------------------------------------------------------- Starting incremental backup -------------------------------------------------------------------- -------------------------------------------------------------------- Done backing up incrementals --------------------------------------------------------------------
- 将源端的增量数据传到目标端
3个txt文件和增量备份文件：
-bash-3.2$ scp xttplan.txt tsbkupmap.txt incrbackups.txt 10.1.11.12:/home/oracle/xtts -bash-3.2$ scp 0b29b9sp_1_1 10.1.11.12:/home/oracle/xtts/backup
- 目标端进行增量转换和数据写入同步
[oracle@11gasm xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r ============================================================ trace file is /home/oracle/xtts/rollforward_Oct19_Thu_19_26_35_696//Oct19_Thu_19_26_35_696_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Start rollforward -------------------------------------------------------------------- Can't locate strict.pm in @INC (@INC contains: /u01/app/grid/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0 /u01/app/grid/11.2.0/grid/lib /u01/app/grid/11.2.0/grid/lib/asmcmd /u01/app/grid/11.2.0/grid/rdbms/lib/asmcmd /u01/app/grid/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/site_perl .) at /u01/app/grid/11.2.0/grid/bin/asmcmdcore line 143. BEGIN failed--compilation aborted at /u01/app/grid/11.2.0/grid/bin/asmcmdcore line 143. -------------------------------------------------------------------- End of rollforward phase --------------------------------------------------------------------
- 源端确定下一个增量备份的FROM_SCN
该步骤会计算下一个FROM_SCN，将其记录在xttplan.txt文件中，然后在创建下一个增量备份时使用该SCN。
该步骤会将-i时生成的xttplan.txt.new改名为xttplan.txt，并将原来的xttplan.txt备份。
建议在目标端每次做完recover动作后，源端就执行一次该命令，以免遗忘。
-bash-3.2$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -s
============================================================
trace file is /export/home/oracle/xtts/determinescn_Oct19_Thu_19_28_45_531//Oct19_Thu_19_28_45_531_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'DFT_DATA_TBS'
Prepare newscn for Tablespaces: ''''
New /export/home/oracle/xtts/xttplan.txt with FROM SCN's generated
在割接前，可以重复以上4步，是目标端和源端不断还原。
1.每次增量时都必须复制xttplan.txt、tsbkupmap.txt和incrbackups.txt，因为它们的内容在每次增量时都是不同的。
2.不修改、不复制文件incrbackups.txt.new。
3.该过程每次执行都会重启目标数据库。
4.如果重新开始，那么需要删除/home/oracle/scripts/FAILED
5.XTTDEBUG=1为打开debug模式，进行调试。Debug 模式可以打印更多的屏幕输出，并且开启 RMAN 的 debug 模式。要启用 debug 模式，或者以 -d 参数运行 xttdriver.pl 或者在运行 xttdriver.pl 前设置环境变量 XTTDEBUG=1。这个参数接受3种级别，-d[1/2/3]级别3会显示最多的信息。
模拟在前滚之前，有数据文件的新增，如何处理？
源端检查datafile是否发生变化：
col name for a80
select file#,name,creation_time from v$datafile where creation_time in (select max(creation_time) from v$datafile);
如下，手动加了个数据文件，在下次前滚前，有新增的数据文件。
SQL> select file#,name,creation_time from v$datafile where creation_time in (select max(creation_time) from v$datafile);
FILE# NAME CREATION_
---------- -------------------------------------------------------------------------------- ---------
7 /export/home/oracle/opt/oradata/ORCL/datafile/o1_mf_dft_data_lm25158y_.dbf 19-OCT-23
源端执行增量备份：
-bash-3.2$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i ============================================================ trace file is /export/home/oracle/xtts/incremental_Oct19_Thu_19_36_25_678//Oct19_Thu_19_36_25_678_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Backup incremental -------------------------------------------------------------------- Prepare source for Tablespaces: 'DFT_DATA_TBS' /home/oracle/xtts/backup xttpreparesrc.sql for 'DFT_DATA_TBS' started at Thu Oct 19 19:36:25 2023 xttpreparesrc.sql for ended at Thu Oct 19 19:36:25 2023 Prepare source for Tablespaces: '''' /home/oracle/xtts/backup xttpreparesrc.sql for '''' started at Thu Oct 19 19:36:25 2023 xttpreparesrc.sql for ended at Thu Oct 19 19:36:25 2023 ============================================================ 7 1 new datafiles added ============================================================= ============================================================ Running prepare cmd for new files o1_mf_dft_data_lm25158y_.db.(.*) ============================================================= !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Error: ------ The incremental backup was not taken as a datafile has been added to the tablespace: Please Do the following: -------------------------- 1. Copy fixnewdf.txt from source to destination temp dir 2. On Destination, run $ORACLE_HOME/perl/bin/perl xttdriver.pl --fixnewdf 3. Re-execute the incremental backup in source: $ORACLE_HOME/perl/bin/perl xttdriver.pl -i NOTE: Before running incremental backup, delete FAILED in source temp dir or run xttdriver.pl with -L option !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -bash-3.2$ cat fixnewdf.txt STARTXTTNEW ::DFT_DATA_TBS 6,DDIR1:/o1_mf_dft_data_lm21tsvr_.dbf 7,DDIR1:/o1_mf_dft_data_lm25158y_.dbf ENDTXTTNEW STARTCONV::5 0,SDIR1,o1_mf_dft_data_lm25158y_.dbf,DDIR1,o1_mf_dft_data_lm25158y_.dbf ENDCONV
会有明显的报错信息，和处理方法。按照提示完成即可。
源端
-bash-3.2$ scp fixnewdf.txt 10.1.11.12:/home/oracle/xtts
目标端；
[oracle@11gasm xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --fixnewdf -d
============================================================
trace file is /home/oracle/xtts/fixnewdf_Oct19_Thu_19_38_09_875//Oct19_Thu_19_38_09_875_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: backupondest
Values: /home/oracle/xtts/backup
Key: platformid
Values: 20
Key: backupformat
Values: /export/home/oracle/xtts/backup
Key: parallel
Values: 3
Key: srclink
Values: to_old
Key: asm_sid
Values: +ASM
Key: dstdir
Values: DDIR1
Key: srcdir
Values: SDIR1
Key: rollparallel
Values: 2
Key: stageondest
Values: /home/oracle/xtts/backup
Key: tablespaces
Values: DFT_DATA_TBS
Key: getfileparallel
Values: 8
Key: asm_home
Values: /u01/app/grid/11.2.0/grid
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
ARGUMENT stageondest
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : orcl
ORACLE_HOME : /u01/app/oracle/product/11.2.0/dbhome_1
--------------------------------------------------------------------
Performing new datafile addition
--------------------------------------------------------------------
--------------------------------------------------------------------
Getting datafiles from source
--------------------------------------------------------------------
fetchCheckDirObjectsDST: Check dir path
fetchDirEntry: remotelink not present
--------------------------------------------------------------------
Executing getfile for /home/oracle/xtts/fixnewdf_Oct19_Thu_19_38_09_875//getfile_sdir1_o1_mf_dft_data_lm25158y_.dbf_0.sql
--------------------------------------------------------------------
PL/SQL procedure successfully completed.
--------------------------------------------------------------------
Completed getting datafiles from source
--------------------------------------------------------------------
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 19 19:00:00 Y FILE_TRANSFER.268.1150657639
DATAFILE UNPROT COARSE OCT 19 19:00:00 Y FILE_TRANSFER.269.1150659491
DATAFILE UNPROT COARSE OCT 19 19:00:00 Y OLD_DATA_TBS.267.1150643465
DATAFILE UNPROT COARSE OCT 19 19:00:00 Y SYSAUX.261.1023146139
DATAFILE UNPROT COARSE OCT 19 19:00:00 Y SYSTEM.260.1023146137
DATAFILE UNPROT COARSE OCT 19 19:00:00 Y UNDOTBS1.262.1023146139
DATAFILE UNPROT COARSE OCT 19 19:00:00 Y USERS.264.1023146143
DATAFILE UNPROT COARSE OCT 19 19:00:00 Y ZHUO.266.1024792071
N o1_mf_dft_data_lm21tsvr_.dbf => +DATADG/ORCL/DATAFILE/FILE_TRANSFER.268.1150657639
N o1_mf_dft_data_lm25158y_.dbf => +DATADG/ORCL/DATAFILE/FILE_TRANSFER.269.1150659491
N old_data_tbs_5.dbf => +DATADG/ORCL/DATAFILE/OLD_DATA_TBS.267.1150643465
xtts还是很智能的，会自动把这个文件rman copy过来。
按照上面提示，再次执行-i即可，与原来前滚步骤相同了。
源端：
-bash-3.2$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i ============================================================ trace file is /export/home/oracle/xtts/incremental_Oct19_Thu_19_43_25_690//Oct19_Thu_19_43_25_690_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Backup incremental -------------------------------------------------------------------- Prepare source for Tablespaces: 'DFT_DATA_TBS' /home/oracle/xtts/backup xttpreparesrc.sql for 'DFT_DATA_TBS' started at Thu Oct 19 19:43:26 2023 xttpreparesrc.sql for ended at Thu Oct 19 19:43:26 2023 Prepare source for Tablespaces: '''' /home/oracle/xtts/backup xttpreparesrc.sql for '''' started at Thu Oct 19 19:43:26 2023 xttpreparesrc.sql for ended at Thu Oct 19 19:43:26 2023 ============================================================ No new datafiles added ============================================================= Prepare newscn for Tablespaces: 'DFT_DATA_TBS' Prepare newscn for Tablespaces: '''' -------------------------------------------------------------------- Starting incremental backup -------------------------------------------------------------------- -------------------------------------------------------------------- Done backing up incrementals -------------------------------------------------------------------- -bash-3.2$ scp xttplan.txt tsbkupmap.txt incrbackups.txt 10.1.11.12:/home/oracle/xtts -bash-3.2$ scp backup/0c29bb6u_1_1 10.1.11.12:/home/oracle/xtts/backup
目标端：
[oracle@11gasm xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r ============================================================ trace file is /home/oracle/xtts/rollforward_Oct19_Thu_19_46_05_790//Oct19_Thu_19_46_05_790_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Start rollforward -------------------------------------------------------------------- Can't locate strict.pm in @INC (@INC contains: /u01/app/grid/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0 /u01/app/grid/11.2.0/grid/lib /u01/app/grid/11.2.0/grid/lib/asmcmd /u01/app/grid/11.2.0/grid/rdbms/lib/asmcmd /u01/app/grid/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/app/grid/11.2.0/grid/perl/lib/site_perl/5.10.0 /u01/app/grid/11.2.0/grid/perl/lib/site_perl .) at /u01/app/grid/11.2.0/grid/bin/asmcmdcore line 143. BEGIN failed--compilation aborted at /u01/app/grid/11.2.0/grid/bin/asmcmdcore line 143. -------------------------------------------------------------------- End of rollforward phase --------------------------------------------------------------------
源端：
-bash-3.2$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -s ============================================================ trace file is /export/home/oracle/xtts/determinescn_Oct19_Thu_19_48_21_83//Oct19_Thu_19_48_21_83_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- Prepare newscn for Tablespaces: 'DFT_DATA_TBS' Prepare newscn for Tablespaces: '''' New /export/home/oracle/xtts/xttplan.txt with FROM SCN's generated
以上步骤执行多轮到停机时间.
正式停机割接
- 清空回收站
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
- 源端表空间read only
set lines 500
set pages 1000
select 'ALTER TABLESPACE ' || tablespace_name || ' READ ONLY;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX')
and contents = 'PERMANENT';
select TABLESPACE_NAME,status,CONTENTS from dba_tablespaces;
SQL> select 'ALTER TABLESPACE ' || tablespace_name || ' READ ONLY;'
2 from dba_tablespaces
3 where tablespace_name not in ('SYSTEM','SYSAUX')
4 and contents = 'PERMANENT';
'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;'
----------------------------------------------------------
ALTER TABLESPACE USERS READ ONLY;
ALTER TABLESPACE OLD_DATA_TBS READ ONLY;
ALTER TABLESPACE DFT_DATA_TBS READ ONLY;
SQL> ALTER TABLESPACE DFT_DATA_TBS READ ONLY;
Tablespace altered.
- 最后一次增量
重复上面多次回滚步骤,唯一不用做生成下次scn，因为已经是最后一次增量备份恢复了。
源端增量备份:
-bash-3.2$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i
传输3个txt+增量备份文件。
目标端增量应用：
[oracle@11gasm xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r
- 创建所有的业务用户
直接利用之前创建的dblink，在目标端创建用户即可。
SQL> set serveroutput ON echo on
SQL> DECLARE
2 v_sql VARCHAR2 (2000);
3 BEGIN
4 FOR c_username IN (SELECT name, password
5 FROM sys.user$@to_old
6 WHERE name NOT IN ('ANONYMOUS',
7 'APEX_030200',
8 'APEX_PUBLIC_USER',
9 'APPQOSSYS',
10 'CTXSYS',
11 'DBSNMP',
12 'DIP',
13 'EXFSYS',
14 'FLOWS_FILES',
15 'MDDATA',
16 'MDSYS',
17 'MGMT_VIEW',
18 'OLAPSYS',
19 'ORACLE_OCM',
20 'ORDDATA',
21 'ORDPLUGINS',
22 'ORDSYS',
23 'OUTLN',
24 'OWBSYS',
25 'OWBSYS_AUDIT',
26 'SI_INFORMTN_SCHEMA',
27 'SPATIAL_CSW_ADMIN_USR',
28 'SPATIAL_WFS_ADMIN_USR',
29 'SYS',
30 'SYSMAN',
31 'SYSTEM',
32 'WMSYS',
33 'XDB',
34 'XS$NULL','DMSYS','TSMSYS')
35 AND TYPE# = 1)
36 LOOP
37 v_sql :=
38 'create user '
39 || c_username.name
40 || ' identified by values '||chr(39)
41 || c_username.password||chr(39)
42 || ';';
43 DBMS_OUTPUT.put_line (v_sql);
44 END LOOP;
45 END;
46 /
create user OLD_TEST identified by values '87F029CDFF3E3ABA';
create user DFT identified by values 'AED48E44688E2601';
PL/SQL procedure successfully completed.
SQL> create user DFT identified by values 'AED48E44688E2601';
User created.
- 目标端生成元数据导出命令
[oracle@11gasm xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -e
生成文件：Done generating plugin file /home/oracle/xtts/xttplugin.txt
内容如下：
[oracle@11gasm xtts]$ cat xttplugin.txt impdp directory=<DATA_PUMP_DIR> logfile=<tts_imp.log> \ network_link=<ttslink> transport_full_check=no \ transport_tablespaces=DFT_DATA_TBS \ transport_datafiles='+DATADG/orcl/datafile/o1_mf_dft_data_lm21tsvr_.dbf','+DATADG/orcl/datafile/o1_mf_dft_data_lm25158y_.dbf'
他主要生成transport_datafiles 参数比较方便，剩下的参数需要自己根据实际情况修改。
6. 导入表空间元数据
[oracle@11gasm xtts]$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR logfile=dft.log network_link=to_old transport_full_check=no transport_tablespaces=DFT_DATA_TBS transport_datafiles='+ DATADG/orcl/datafile/o1_mf_dft_data_lm21tsvr_.dbf','+DATADG/orcl/datafile/o1_mf_dft_data_lm25158y_.dbf' exclude=TABLE_STATISTICS,INDEX_STATISTICS Import: Release 11.2.0.4.0 - Production on Thu Oct 19 19:57:35 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, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR logfile=dft.log network_link=to_old transport_full_check=no transport_tablespaces=DFT_DATA_TBS transport_datafiles=+DATADG/orcl/datafile/o1_mf_dft_data_lm21tsvr_.dbf,+DATADG/orcl/datafile/o1_mf_dft_data_lm25158y_.dbf exclude=TABLE_STATISTICS,INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Oct 19 19:57:43 2023 elapsed 0 00:00:07
- 目标端表空间read write
set lines 500
set pages 1000
select 'ALTER TABLESPACE ' || tablespace_name || ' READ WRITE;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX')
and contents = 'PERMANENT';
select TABLESPACE_NAME,status,CONTENTS from dba_tablespaces;
SQL> ALTER TABLESPACE DFT_DATA_TBS READ WRITE;
Tablespace altered
收尾阶段
- 导入其他元数据，如存储过程、视图等
[oracle@11gasm xtts]$ impdp "'/ as sysdba'" network_link=to_old SCHEMAS='DFT' content=metadata_only TABLE_EXISTS_ACTION=SKIP exclude=table,index parallel=16 metrics=y directory=DATA_PU MP_DIR Import: Release 11.2.0.4.0 - Production on Thu Oct 19 19:59:27 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, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" network_link=to_old SCHEMAS=DFT content=metadata_only TABLE_EXISTS_ACTION=SKIP exclude=table,index parallel=16 metrics=y directory=DATA_PUMP_DIR Startup took 0 seconds Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"DFT" already exists Completed 1 USER objects in 0 seconds Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Completed 1 SYSTEM_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/ROLE_GRANT Completed 2 ROLE_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Completed 1 DEFAULT_ROLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Completed 1 TABLESPACE_QUOTA objects in 0 seconds Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Completed 1 PROCACT_SCHEMA objects in 0 seconds Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Oct 19 19:59:29 2023 elapsed 0 00:00:02
- 将用户默认表空间还原
SQL> select 'alter user '||username||' default tablespace '||default_tablespace||';' from dba_users where username in ('DFT') order by created;
'ALTERUSER'||USERNAME||'DEFAULTTABLESPACE'||DEFAULT_TABLESPACE||';'
--------------------------------------------------------------------------------------------
alter user DFT default tablespace DFT_DATA_TBS;
数据校验
checkobject.sql
收集统计信息
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
总结
DFT模式与RMAN backup最大的不同，就是全量初始化的时候，DFT直接通过网络，就从源端入库目标端了，不占用本地空间。特别适合于那些库特别大，但是本地没有空间的常经。主要就是xtts的配置文件中的参数起作用了：
srcdir=SDIR1 dstdir=DDIR1 srclink=to_old create directory SDIR1 as '/export/home/oracle/opt/oradata/ORCL/datafile/'; create directory DDIR1 as '+DATADG/orcl/datafile/';
直接就指定了数据库文件的存储位置，避免落地。
最后修改时间：2023-10-19 21:45:58
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
