ITPub博客

首页 > 数据库 > Oracle > oracle xtts迁移 AIX to Linux

oracle xtts迁移 AIX to Linux

原创 Oracle 作者:巡完南山巡南山 时间:2019-01-14 17:55:44 0 删除 编辑

以下是XTTS迁移的全过程,

需要根据实际情况更改,主要包括以下几个流程:

检查数据库用户自包含

准备 xtts 前期环境

进行全量备份并传输备份集到目标端

目标端进行全量恢复

源端目标端多次增量恢复

表空间 readonly 进行最后一次增量

元数据导入导出

检查对象


源端

目标端

Oracle 版本

10.2.0.3

11.2.0.4

操作系统

AIX 5.3

RHEL 6.7

是否 RAC

数据库名称

oraold

oranew

 

1         迁移实施方案

1.1          迁移主要步骤

                                             

1.2          备份前准备

1.2.1            校验自包含

本次只迁移 TESTUSER 用户,只检查 TESTUSER 用户所在表空间的自包含验证即可,无需额外操作。

SQL> select distinct tablespace_name from dba_segments where owner='TESTUSER';

 

TABLESPACE_NAME

------------------------------

TESTUSER_DAT_4

TESTUSER_DAT_1

TESTUSER_DAT_2

TESTUSER_IDX_2

TESTUSER_DAT_3

TESTUSER_IDX_3

TESTUSER_ALL

TESTUSER_IDX_1

TESTUSER_IDX_4

 

9 rows selected.

SQL> execute dbms_tts.transport_set_check ('TESTUSER_DAT_4,TESTUSER_DAT_1,TESTUSER_DAT_2,TESTUSER_IDX_2,TESTUSER_DAT_3,TESTUSER_IDX_3,TESTUSER_ALL,TESTUSER_IDX_1,TESTUSER_IDX_4' ,true,true);

 

PL/SQL procedure successfully completed.

 

SQL> select * from TRANSPORT_SET_VIOLATIONS;

 

no rows selected

1.2.2            创建 xtts 所需目录

源端和目标端创建 相关 目录

mkdir -p  /exp/xtts

mkdir -p  /exp/xtts/src_backup

mkdir -p  /exp/xtts/tmp

mkdir -p  /exp/xtts/dump

mkdir -p /exp/xtts/backup_incre

chown -R ora103:dba /exp/xtts

源端AIX上传rman-xttconvert_2.0.zip至/exp/xtts

cd /exp/xtts

unzip rman-xttconvert_2.0.zip

 

目标端上传rman-xttconvert_2.0.zip至/exp/xtts

cd /exp/xtts

unzip rman-xttconvert_2.0.zip

目标端创建其他相关目录

mkdir -p  /exp/xtts

mkdir -p  /exp/xtts/src_backup

mkdir -p  /exp/xtts/tmp

mkdir -p  /exp/xtts/dump

mkdir -p /exp/xtts/backup_incre

chown -R ora11g:dba /exp/xtts

1.2.3            源端开启 tracking

SQL> alter database enable block change tracking using file '/exp/xtts/bct';

Database altered.

SQL> select * from v$ block_change_tracking ;

 

STATUS

----------

FILENAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     BYTES

----------

ENABLED

/exp/xtts/bct  11599872

关闭tracking可以使用如下命令完成:

alter database disable block change tracking;

1.2.4            配置 xtt.properties

配置源端AIX xtt.properties属性文件

cd /exp/xtts

vi xtt.properties

#增加如下配置信息:

tablespaces=TESTUSER_DAT_4,TESTUSER_DAT_1,TESTUSER_DAT_2,TESTUSER_IDX_2,TESTUSER_DAT_3,TESTUSER_IDX_3,TESTUSER_ALL,TESTUSER_IDX_1,TESTUSER_IDX_4

platformid=6

dfcopydir=/exp/xtts/src_backup

backupformat=/exp/xtts/backup_incre

backupondest=/exp/xtts/backup_incre

stageondest=/exp/xtts/src_backup

storageondest=+DG_DATA/oranew/datafile

parallel=16

rollparallel=16

getfileparallel=6

配置目标端Linux xtt.properties属性文件

cd /exp/xtts

vi xtt.properties

#增加如下配置信息:

tablespaces=TESTUSER_DAT_4,TESTUSER_DAT_1,TESTUSER_DAT_2,TESTUSER_IDX_2,TESTUSER_DAT_3,TESTUSER_IDX_3,TESTUSER_ALL,TESTUSER_IDX_1,TESTUSER_IDX_4

platformid=6

dfcopydir=/exp/xtts/src_backup

backupformat=/exp/xtts/backup_incre

backupondest=/exp/xtts/backup_incre

stageondest=/exp/xtts/backup_incre

storageondest=+DG_DATA/oranew/datafile

parallel=16

rollparallel=16

getfileparallel=6

asm_home=/opt/app/11.2.0/grid

asm_sid=+ASM1

1.2.5            目标端提前建立用户角色

以下命令为创建用户命令参考,也可以使用 plsql 等工具生成类似相关信息。用户和角色必须手工创建。最好使用 plsql 拿出所需用户的 ddl 以免漏掉权限。

创建 EBPF 用户,完成元数据导入后才可修改默认表空间

SQL> select name ,PASSWORD from user$ where name='TESTUSER';

 

NAME                           PASSWORD

------------------------------ ------------------------------

TESTUSER                       C987AC3B738BCF43

 

create user TESTUSER identified by values 'C987AC3B738BCF43';

 

grant connect to TESTUSER;

grant resource to TESTUSER;

grant alter session to TESTUSER;

grant create session to TESTUSER;

grant select any dictionary to TESTUSER;

grant unlimit tablespace to TESTUSER;

 

创建角色

create role R_SELTESTUSER;

create role R_UPDTESTUSER;

赋予角色相应操作权限

1.3       全量备份及全量恢复

1.3.1            表空间全量备份

源端AIX执行被传输业务表空间全量备份创建xtts表空间全量备份脚本执行过程中产生的配置文件,用于数据文件转换及每次增量备份及恢复,同时每次执行增量备份过程中,配置文件内容会发生变化,用于新的增量恢复,主要是SCN的变化。增加rman备份并行度

rman target /

show all;

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

 

cd /exp/xtts

full_backup.sh脚本内容如下

export ORACLE_SID=TESTUSER

export TMPDIR=/exp/xtts/tmp

export PERL5LIB=/opt/app/ora103/10.2.0/product/perl/lib

/opt/app/ora103/10.2.0/product/perl/bin/perl /exp/xtts/xttdriver.pl -p -d

执行全量备份

cd /exp/xtts

nohup sh full_backup.sh > full_backup.log &

1.3.2            表空间全量恢复及转换

目标端Linux执行表空间恢复并将数据文件转换至ASM磁盘组中,每次恢复失败时会在 /exp/xtts/tmp 产生fails文件需要删除后方可再次运行(做之前查看下文中的特别说明步骤)

cd /exp/xtts

full_restore.sh脚本内容如下

export TMPDIR=/exp/xtts/tmp

export ORACLE_SID=oranew1

/opt/app/ora11g/product/11.2.0/perl/bin/perl /exp/xtts/xttdriver.pl -c -d

执行恢复

nohup sh full_restore.sh > full_restore.log &

1.4          增量备份及恢复

1.4.1            表空间增量备份

源端进行增量备份

cd /exp/xtts

增量备份脚本incre_backup.sh内容如下

export ORACLE_SID=TESTUSER

export TMPDIR=/exp/xtts/tmp

export PERL5LIB=/opt/app/ora103/10.2.0/product/perl/lib

/opt/app/ora103/10.2.0/product/perl/bin/perl /exp/xtts/xttdriver.pl -i -d

执行增量备份

cd /exp/xtts

nohup sh incre_backup.sh > incre_backup.log &

1.4.2            表空间增量恢复

cd /exp/xtts

incre_recover.sh脚本内容如下

export TMPDIR=/exp/xtts/tmp

export ORACLE_SID=oranew1

/opt/app/ora11g/product/11.2.0/perl/bin/perl /exp/xtts/xttdriver.pl -r -d

执行增量恢复

nohup sh incre_recover.sh > incre_recover.log &

1.5          迁移阶段

1.5.1            表空间 readonly

源端AIX将被传输业务表空间修改为READ ONLY状态

alter system set job_queue_processes=0;

--恢复

alter system set job_queue_processes=10;

 

select tablespace_name,status from dba_tablespaces order by 2;

alter tablespace TESTUSER_DAT_4 read only;

alter tablespace TESTUSER_DAT_1 read only;

alter tablespace TESTUSER_DAT_2 read only;

alter tablespace TESTUSER_IDX_2 read only;

alter tablespace TESTUSER_DAT_3 read only;

alter tablespace TESTUSER_IDX_3 read only;

alter tablespace TESTUSER_ALL read only;

alter tablespace TESTUSER_IDX_1 read only;

alter tablespace TESTUSER_IDX_4 read only;

1.5.2            最后一次增量操作

按照 2.4.1 、2.4.2完成最后一次增量备份与恢复。

1.5.3            目标端开启闪回

目标端Linux开启在导入元数据前开启闪回

SQL> alter system set db_recovery_file_dest_size=50g scope=both;

 

System altered.

 

SQL> alter system set db_recovery_file_dest='+DG_DATA' scope=both;

 

System altered.

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

------------------

YES

 

SQL> create restore point before_imp_xtts guarantee flashback database;

 

Restore point created.

 

SQL> select name from v$restore_point;

1.5.4            导入 XTTS 元数据

AIX 源端导出XTTS元数据

导出表空间元数据

expdp system/passwd parfile=expdp_xtts.par

expdp_xtts.par内容如下

directory=dump

dumpfile=tbs_xtts.dmp

logfile=expdp_xtts.log

transport_tablespaces=('TESTUSER_DAT_4','TESTUSER_DAT_1','TESTUSER_DAT_2','TESTUSER_IDX_2','TESTUSER_DAT_3','TESTUSER_IDX_3','TESTUSER_ALL','TESTUSER_IDX_1','TESTUSER_IDX_4')

transport_full_check=y

cluster=n

metrics=yes

 

导出用户元数据

expdp system/passwd parfile=expdp_xtts_other.par

expdp_xtts_other.par内容如下

directory=dump

dumpfile=tbs_xtts_other.dmp

logfile=expdp_xtts_other.log

content=metadata_only

schemas=TESTUSER

metrics=yes

 

执行

cd /exp/xtts/dump

./expdp_xtts.sh

./expdp_xtts_other.sh

 

LINUX 目标端导入元数据

impdp system/passwd parfile=impdp_xtts.par

impdp_xtts.par内容如下

directory=dump

logfile=impdp_xtts.log

dumpfile=tbs_xtts.dmp

cluster=n

metrics=yes

transport_datafiles='+DG_DATA/ORANEW/DATAFILE/TESTUSER_ALL.456.995114385',

'+DG_DATA/ORANEW/DATAFILE/TESTUSER_DAT_1.532.995106883',

'+DG_DATA/ORANEW/DATAFILE/TESTUSER_DAT_1.528.995107837',

'+DG_DATA/ORANEW/DATAFILE/TESTUSER_DAT_1.524.995107839',

'+DG_DATA/ORANEW/DATAFILE/TESTUSER_DAT_1.543.995106883',

'+DG_DATA/ORANEW/DATAFILE/TESTUSER_IDX_2.496.995109429',

'+DG_DATA/ORANEW/DATAFILE/TESTUSER_IDX_4.432.995115329',

'+DG_DATA/ORANEW/DATAFILE/TESTUSER_IDX_4.430.995115329'

 

cd /exp/xtts/dump

./impdp_xtts.sh

1.5.5            表空间 READ WRITE

LINUX 目标端表空间 readwrite

alter tablespace TESTUSER_DAT_4 read write;

alter tablespace TESTUSER_DAT_1 read write;

alter tablespace TESTUSER_DAT_2 read write;

alter tablespace TESTUSER_IDX_2 read write;

alter tablespace TESTUSER_DAT_3 read write;

alter tablespace TESTUSER_IDX_3 read write;

alter tablespace TESTUSER_ALL read write;

alter tablespace TESTUSER_IDX_1 read write;

alter tablespace TESTUSER_IDX_4 read write;

select tablespace_name,status from dba_tablespaces order by 1;

1.5.6            第二次开启闪回

目标端Linux在其他元数据导入前再次开启闪回

sqlplus / as sysdba

select flashback_on from v$database;

create restore point before_imp_other guarantee flashback database;

select name from v$restore_point;

1.5.7            导入其它元数据

impdp system/passwd parfile=impdp_xtts_other.par

impdp_xtts_other.par 内容如下

directory=dump

dumpfile=tbs_xtts_other.dmp

logfile=impdp_xtts_other.log

content=metadata_only

schemas=TESTUSER

cluster=n

metrics=yes

执行

cd /exp/xtts/dump

./impdp_xtts_other.sh


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26964624/viewspace-2557295/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-04-11

  • 博文量
    28
  • 访问量
    12737