基于rman增量备份减少XTTS的停机时间

godba 2021-01-19

878

点上边蓝字关注我们

在跨平台、跨版本的海量数据数据迁移加升级的场景下，使用OGG/DSG等逻辑数据迁移的方式往往是最优的选择，但是在预算有限的条件下，oracle 传统的XTTS也是比较成熟的方案。

XTTS需要源端表空间至于只读模式下，那么数据文件的数量和大小往往决定了传输和转换字节序的时间。生产中心的迁移涉及业务、数据、中间件、网路、存储、主机等各个专业的联调，都给DBA迁移数据的时间往往是有限的。

本文演示如何使用rman的增量备份来减少XTTS过程中，database的停机时间以保证整个生产业务中心的顺利迁移。

1.概述

源端：solaris5.10,db11.2.0.3.15

目标端：RHEL7.6,db19.9.0.0.0

迁移的表空间：zhyu,zhyu1

本文省略了XTTS迁移数据库的前提条件检查，只演示迁移的过程。至于XTTS的前提条件检查以及需要注意的事项，请参考oracle官方文档。

2.创建相关目录以及dblink

源端：

SQL> select dbms_metadata.get_ddl('DIRECTORY','SRC1') from dual;

DBMS_METADATA.GET_DDL('DIRECTORY','SRC1')
--------------------------------------------------------------------------------

   CREATE OR REPLACE DIRECTORY "SRC1" AS '/export/home/oracle/oradata/orcl'


SQL> select dbms_metadata.get_ddl('DIRECTORY','SRC2') from dual;

DBMS_METADATA.GET_DDL('DIRECTORY','SRC2')
--------------------------------------------------------------------------------

   CREATE OR REPLACE DIRECTORY "SRC2" AS '/export/home/oracle/oradata/orcl1'

目标端：

SQL> select dbms_metadata.get_ddl('DIRECTORY','DST1') from dual;

DBMS_METADATA.GET_DDL('DIRECTORY','DST1')
--------------------------------------------------------------------------------

   CREATE OR REPLACE DIRECTORY "DST1" AS '/u01/app/oracle/oradata/ORCL/'


SQL> select dbms_metadata.get_ddl('DB_LINK','XTTS','PUBLIC') from dual;

DBMS_METADATA.GET_DDL('DB_LINK','XTTS','PUBLIC')
--------------------------------------------------------------------------------

  CREATE PUBLIC DATABASE LINK "XTTS"
   CONNECT TO "SYSTEM" IDENTIFIED BY VALUES ':1'
   USING '192.168.56.102:1521/orcl'

SQL> select * from v$version@xtts;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE  11.2.0.3.0  Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

3.上传并配置xttconvert脚本。

上传并解压后文件如下：

[oracle@vm2:/export/home/oracle/xtts]$unzip rman_xttconvert_v3.zip 
Archive:  rman_xttconvert_v3.zip
  inflating: xtt.properties          
  inflating: xttcnvrtbkupdest.sql    
  inflating: xttdbopen.sql           
  inflating: xttdriver.pl            
  inflating: xttprep.tmpl            
 extracting: xttstartupnomount.sql

修改xtt.properties如下：

[oracle@vm1 xtts]$ cat xtt.properties| grep -v ^#|grep -v ^$
tablespaces=ZHYU,ZHYU1
platformid=20
srcdir=SRC1,SRC2
dstdir=DST1
srclink=XTTS
backupformat=/export/home/oracle/rman
stageondest=/u01/app/oracle/rman
backupondest=/u01/app/oracle/rman
parallel=4
rollparallel=4
getfileparallel=4

4.传输xtts目录到目标端

[oracle@vm2:/export/home/oracle/xtts]$scp -r ./* 192.168.56.101:/home/oracle/xtts/
oracle@192.168.56.101's password: 
rman_xttconvert_v3.z 100% |**********************************************************************************************************| 34672       00:00    
xtt.properties       100% |**********************************************************************************************************|  7963       00:00    
xtt.properties.bk    100% |**********************************************************************************************************|  7969       00:00    
xttcnvrtbkupdest.sql 100% |**********************************************************************************************************|  1390       00:00    
xttdbopen.sql        100% |**********************************************************************************************************|    71       00:00    
xttdriver.pl         100% |**********************************************************************************************************|   138 KB    00:00    
xttprep.tmpl         100% |**********************************************************************************************************| 11710       00:00    
xttstartupnomount.sq 100% |**********************************************************************************************************|    52       00:00

5.源端运行命令，生成传输表间列表，并传到目标端。

[oracle@vm2:/export/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl -S
============================================================
trace file is export/home/oracle/xtts/setupgetfile_Jan12_Tue_17_57_38_99//Jan12_Tue_17_57_38_99_.log
=============================================================

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


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


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


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


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

Prepare source for Tablespaces:
                  'ZHYU'  u01/app/oracle/rman
xttpreparesrc.sql for 'ZHYU' started at Tue Jan 12 17:57:38 2021
xttpreparesrc.sql for  ended at Tue Jan 12 17:57:38 2021
Prepare source for Tablespaces:
                  'ZHYU1'  u01/app/oracle/rman
xttpreparesrc.sql for 'ZHYU1' started at Tue Jan 12 17:57:38 2021
xttpreparesrc.sql for  ended at Tue Jan 12 17:57:38 2021
Prepare source for Tablespaces:
                  ''''  u01/app/oracle/rman
xttpreparesrc.sql for '''' started at Tue Jan 12 17:57:38 2021
xttpreparesrc.sql for  ended at Tue Jan 12 17:57:38 2021

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

[oracle@vm2:/export/home/oracle/xtts]$ls -lrt
total 378
-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    7969 Jun  5  2017 xtt.properties.bk
-rw-r--r--   1 oracle   oinstall  142020 Sep 20  2018 xttdriver.pl
-rw-r--r--   1 oracle   oinstall    7936 Jan 12 17:56 xtt.properties
-rw-r--r--   1 oracle   oinstall      37 Jan 12 17:57 xttplan.txt
-rw-r--r--   1 oracle   oinstall     129 Jan 12 17:57 xttnewdatafiles.txt_temp
-rw-r--r--   1 oracle   oinstall      56 Jan 12 17:57 xttnewdatafiles.txt
-rw-r--r--   1 oracle   oinstall      74 Jan 12 17:57 getfile.sql
drwxr-xr-x   2 oracle   oinstall     512 Jan 12 17:57 setupgetfile_Jan12_Tue_17_57_38_99
[oracle@vm2:/export/home/oracle/xtts]$cat xttnewdatafiles.txt
::ZHYU
5,DST1:/zhyu_01.dbf
::ZHYU1
6,DST1:/zhyu1_01.dbf
[oracle@vm2:/export/home/oracle/xtts]$cat getfile.sql
0,SRC1,zhyu_01.dbf,DST1,zhyu_01.dbf
1,SRC2,zhyu1_01.dbf,DST1,zhyu1_01.dbf

将xttnewdatafiles.txt，getfile.sql传输到目标端xtts目录。

[oracle@vm2:/export/home/oracle/xtts]$scp xttnewdatafiles.txt getfile.sql 192.168.56.101:/home/oracle/xtts/
oracle@192.168.56.101's password: 
xttnewdatafiles.txt  100% |**********************************************************************************************************|    56       00:00    
getfile.sql          100% |**********************************************************************************************************|    74       00:00

6.目标端获取数据文件。

[oracle@vm1 xtts]$ export TMPDIR=/home/oracle/xtts
[oracle@vm1 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -G
============================================================
trace file is home/oracle/xtts/getfile_Jan12_Tue_18_03_40_632//Jan12_Tue_18_03_40_632_.log
=============================================================

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


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


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


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


--------------------------------------------------------------------
Getting datafiles from source
--------------------------------------------------------------------


--------------------------------------------------------------------
Executing getfile for home/oracle/xtts/getfile_Jan12_Tue_18_03_40_632//getfile_src1_zhyu_01.dbf_0.sql
--------------------------------------------------------------------


--------------------------------------------------------------------
Executing getfile for home/oracle/xtts/getfile_Jan12_Tue_18_03_40_632//getfile_src2_zhyu1_01.dbf_1.sql
--------------------------------------------------------------------


--------------------------------------------------------------------
Completed getting datafiles from source
--------------------------------------------------------------------

[oracle@vm1 ORCL]$ pwd
/u01/app/oracle/oradata/ORCL
[oracle@vm1 ORCL]$ ls -l | grep zhyu
-rw-r-----. 1 oracle oinstall   52436992 Jan 12 18:03 zhyu_01.dbf
-rw-r-----. 1 oracle oinstall   52436992 Jan 12 18:03 zhyu1_01.dbf

7.源端模拟增加数据，并作一次增量备份，重新生成增量配置文件，并上传到目标端。

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)

SQL> select * from t;

        ID
----------
         1

SQL> insert into t values(2);

1 row created.

SQL> commit;

Commit complete.

[oracle@vm2:/export/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
============================================================
trace file is export/home/oracle/xtts/incremental_Jan12_Tue_18_12_17_258//Jan12_Tue_18_12_17_258_.log
=============================================================

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


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


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


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


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

Prepare source for Tablespaces:
                  'ZHYU'  u01/app/oracle/rman
xttpreparesrc.sql for 'ZHYU' started at Tue Jan 12 18:12:17 2021
xttpreparesrc.sql for  ended at Tue Jan 12 18:12:17 2021
Prepare source for Tablespaces:
                  'ZHYU1'  u01/app/oracle/rman
xttpreparesrc.sql for 'ZHYU1' started at Tue Jan 12 18:12:17 2021
xttpreparesrc.sql for  ended at Tue Jan 12 18:12:17 2021
Prepare source for Tablespaces:
                  ''''  u01/app/oracle/rman
xttpreparesrc.sql for '''' started at Tue Jan 12 18:12:17 2021
xttpreparesrc.sql for  ended at Tue Jan 12 18:12:17 2021
============================================================
No new datafiles added
=============================================================
Prepare newscn for Tablespaces: 'ZHYU'
Prepare newscn for Tablespaces: 'ZHYU1'
Prepare newscn for Tablespaces: ''''

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


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

[oracle@vm2:/export/home/oracle/xtts]$cat tsbkupmap.txt
ZHYU::5:::1=05vkfbk2_1_1
ZHYU1::6:::1=07vkfbk5_1_1
[oracle@vm2:/export/home/oracle/xtts]$cat incrbackups.txt
/export/home/oracle/rman/05vkfbk2_1_1
/export/home/oracle/rman/07vkfbk5_1_1

[oracle@vm2:/export/home/oracle/xtts]$scp `cat incrbackups.txt` 192.168.56.101:/u01/app/oracle/rman/
oracle@192.168.56.101's password: 
05vkfbk2_1_1         100% |**********************************************************************************************************| 49152       00:00    
07vkfbk5_1_1         100% |**********************************************************************************************************| 40960       00:00    
[oracle@vm2:/export/home/oracle/xtts]$scp xttplan.txt tsbkupmap.txt 192.168.56.101:/home/oracle/xtts/
oracle@192.168.56.101's password: 
xttplan.txt          100% |**********************************************************************************************************|    37       00:00    
tsbkupmap.txt        100% |**********************************************************************************************************|    51       00:00

8.目标端做增量恢复。

[oracle@vm1 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r
============================================================
trace file is home/oracle/xtts/rollforward_Jan12_Tue_18_29_27_660//Jan12_Tue_18_29_27_660_.log
=============================================================

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


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


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


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


--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------


--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------

源端生成下次增量备份的scn。

[oracle@vm2:/export/home/oracle/xtts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl -s
============================================================
trace file is export/home/oracle/xtts/determinescn_Jan12_Tue_18_33_07_643//Jan12_Tue_18_33_07_643_.log
=============================================================

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


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


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


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

Prepare newscn for Tablespaces: 'ZHYU'
Prepare newscn for Tablespaces: 'ZHYU1'
Prepare newscn for Tablespaces: ''''
New export/home/oracle/xtts/xttplan.txt with FROM SCN's generated

9.源端模拟增加数据，源端表空间offline,做最后一次增量备份。

SQL> insert into t values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> conn  as sysdba
Connected.
SQL> alter tablespace zhyu read only;

Tablespace altered.

SQL> alter tablespace zhyu1 read only;

Tablespace altered.

[oracle@vm2:/export/home/oracle/xtts]$scp `cat incrbackups.txt` 192.168.56.101:/u01/app/oracle/rman/
oracle@192.168.56.101's password: 
09vkfd1i_1_1         100% |**********************************************************************************************************| 57344       00:00    
0bvkfd1l_1_1         100% |**********************************************************************************************************| 40960       00:00    
[oracle@vm2:/export/home/oracle/xtts]$scp  xttplan.txt  tsbkupmap.txt 192.168.56.101:/home/oracle/xtts/
oracle@192.168.56.101's password: 
xttplan.txt          100% |**********************************************************************************************************|    41       00:00    
tsbkupmap.txt        100% |**********************************************************************************************************|    51       00:00

10.目标端做走后一次增量恢复。

[oracle@vm1 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r
============================================================
trace file is home/oracle/xtts/rollforward_Jan12_Tue_17_37_27_985//Jan12_Tue_17_37_27_985_.log
=============================================================

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


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


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


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


--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------


--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------

11.目标端生成表空间元数据的导入命令

[oracle@vm1 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -e
============================================================
trace file is home/oracle/xtts/generate_Jan12_Tue_17_39_50_172//Jan12_Tue_17_39_50_172_.log
=============================================================

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


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


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


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


--------------------------------------------------------------------
Generating plugin
--------------------------------------------------------------------


--------------------------------------------------------------------
Done generating plugin file home/oracle/xtts/xttplugin.txt
--------------------------------------------------------------------

[oracle@vm1 xtts]$ cat xttplugin.txt
impdp directory=<DATA_PUMP_DIR> logfile=<tts_imp.log> \
network_link=<ttslink> transport_full_check=no \
transport_tablespaces=ZHYU,ZHYU1 \
transport_datafiles='/u01/app/oracle/oradata/ORCL/zhyu_01.dbf','/u01/app/oracle/oradata/ORCL/zhyu1_01.dbf'

[oracle@vm1 xtts]$ impdp directory=dst1 logfile=xtts.log \
> network_link=xtts transport_full_check=no \
> transport_tablespaces=ZHYU,ZHYU1 \
> transport_datafiles='/u01/app/oracle/oradata/ORCL/zhyu_01.dbf','/u01/app/oracle/oradata/ORCL/zhyu1_01.dbf'

Import: Release 19.0.0.0.0 - Production on Tue Jan 12 17:43:16 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username:  as sysdba

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=dst1 logfile=xtts.log network_link=xtts transport_full_check=no transport_tablespaces=ZHYU,ZHYU1 transport_datafiles=/u01/app/oracle/oradata/ORCL/zhyu_01.dbf,/u01/app/oracle/oradata/ORCL/zhyu1_01.dbf 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Jan 12 17:43:36 2021 elapsed 0 00:00:13

12.目标库打开迁移的表间，检查状态。

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
ZHYU
ZHYU1

7 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/zhyu1_01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/zhyu_01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf

6 rows selected.

SQL> alter tablespace zhyu read write;

Tablespace altered.


SQL> alter tablespace zhyu1 read write;


Tablespace altered.


SQL> select * from zhyu.t;


  ID
----------
   2
   1
   3
   4

13.目标端rman校验数据。

[oracle@vm1 xtts]$ rman target /


Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 12 17:55:24 2021
Version 19.9.0.0.0


Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


connected to target database: ORCL (DBID=1588995992)


RMAN> validate tablespace zhyu1,zhyu check logical;


Starting validate at 12-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/zhyu1_01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/zhyu_01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:02
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              5863         6400            2537038   
  File Name: /u01/app/oracle/oradata/ORCL/zhyu1_01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              379             
  Index      0              0               
  Other      0              158             


File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              4996         6400            2537031   
  File Name: /u01/app/oracle/oradata/ORCL/zhyu_01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1236            
  Index      0              0               
  Other      0              168             


Finished validate at 12-JAN-21

数据库

文章转载自godba，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

基于rman增量备份减少XTTS的停机时间

评论