sap oracle表迁移,SAP使用表空间传输异构系统迁移

SAP使用表空间传输异构系统迁移

此文档乃原创总结，仅适用于oracle数据库。此方法特点，速度快、停机时间短，配合RMAN使用甚至可以做到不停机、在线迁移并可以支持裸设备和ASM。以次方法做迁移，在实际测试中，从Solaris sparc迁移到Solaris X86的停机时间根据硬件配置不同大概为：离线冷备时间+1个小时左右。CPU越高，时间越短。

方法如下：

1.在目标机全新安装同版本SAP系统。

2.在源系统做一次备份。(可选，推荐做)

3.在源系统执行exp.sh脚本分别导出PSAPSR3，PSAPSR37**，PSAPSR3usr。(exp.sh脚本参考附件一，此脚本非通用形脚本，需要根据自身情况修改)

4.使用sqlDevelopoer将源系统视图创建命令导出。

5.将exp.sh命令导出的文件以及导出的创建视图脚本传到目标机。

6.DROP掉目标机PSAPSR3,PSAPSR37**,PSAPSR3usr，3个表空间及其数据文件。

7.在RMAN中使用convert命令将数据文件转换至目标平台。

8.根据需要，将数据文件按照sap的数据文件存放方式，移动到各自目录(可选，可自己写脚本完成)。

9.使用imp脚本将表空间PSAPSR3,PSAPSR37**,PSAPSR3usr导入目标数据库。(命令参考附件二)

10.将PSAPSR3，PSAPSR37**,PSAPSR3usr3个表空间设置为可读写，再执行创建视图的脚本。

11.启动SAP，完成SystemCopy后的后续操作。如SE06等，具体参考James Yan的文档：

12.大功告成！

附件一：exp.sh脚本，(此脚本仅供参考，如需直接使用建立对应目录)

#!/bin/bash

echo -n "Enter tablespace name:"

read TABLESPACE

echo Transport tablespace $TABLESPACE Begin time:`date +%Y/%m/%d' '%H:%M:%S` >> /sappool/exp/exp_ts_$TABLESPACE.log

#

VALUE=`sqlplus -S /nolog <

set heading off

set echo off

set line 200

set pages 0

connect / as sysdba

alter tablespace $TABLESPACE read only;

exit;

EOF`

if [ "$VALUE" = "`echo -e '\nTablespace altered.'`" ]; then

echo Alter tablespace $TABLESPACE read only SUCCESS;

else

echo Alter tablespace $TABLESPACE read only Failed;

echo "ERROR message is:"

echo "$VALUE"

fi

echo exp tablespace $TABLESPACE Begin time:`date +%Y/%m/%d' '%H:%M:%S` >> /sappool/exp/exp_ts_$TABLESPACE.log

exp tablespaces=$TABLESPACE transport_tablespace=y file=/sappool/exp/exp_ts_$TABLESPACE.dmp log=/sappool/exp/log/exp_ts_$TABLESPACE.log

echo exp tablespace $TABLESPACE finish time:`date +%Y/%m/%d' '%H:%M:%S` >> /sappool/exp/exp_ts_$TABLESPACE.log

VALUE=`sqlplus -S /nolog <

set heading off

set echo off

set line 200

set pages 0

set feed off

connect / as sysdba

select file_name from dba_data_files where tablespace_name=upper('$TABLESPACE');

exit;

EOF`

echo copy tablespace "$TABLESPACE"\'s datafie finish time :`date +%Y/%m/%d' '%H:%M:%S` >> /sappool/exp/exp_ts_$TABLESPACE.log

mkdir -p /sappool/exp/datafile_$TABLESPACE

chmod -R 777 /sappool/exp/datafile_$TABLESPACE

cp -p $VALUE /sappool/exp/datafile_$TABLESPACE/

echo copy tablespace begin

echo copy tablespace "$TABLESPACE"\'s datafie finish time :`date +%Y/%m/%d' '%H:%M:%S` >> /sappool/exp/exp_ts_$TABLESPACE.log

VALUE=`sqlplus -S /nolog <

set heading off

set echo off

set line 200

set pages 0

connect / as sysdba

alter tablespace $TABLESPACE read write;

exit;

EOF`

if [ "$VALUE" = "`echo -e '\nTablespace altered.'`" ]; then

echo Alter tablespace $TABLESPACE read write SUCCESS;

exit 0

else

echo Alter tablespace $TABLESPACE read write Failed;

echo "ERROR message is:"

echo "$VALUE"

fi

echo Transport tablespace $TABLESPACE Finish time:`date +%Y/%m/%d' '%H:%M:%S` >> /sappool/exp/exp_ts_$TABLESPACE.log

cat /sappool/exp/exp_ts_$TABLESPACE.log >> /sappool/exp/log/exp_ts_$TABLESPACE.log && rm -f /sappool/exp/exp_ts_$TABLESPACE.log

附件二：imp脚本和命令(以PSAPSR3700表空间为例，其他表空间参考)

date >> /oracle/imp/log/imp_ts_psapsr3700.log.time && \

imp tablespaces=PSAPSR3700 \

transport_tablespace=y \

file='/oracle/exp_ts_psapsr3700.dmp'\

datafiles='/oracle/PRD/sapdata1/sr3700_5/sr3700.data5',\

'/oracle/PRD/sapdata1/sr3700_1/sr3700.data1',\

'/oracle/PRD/sapdata1/sr3700_9/sr3700.data9',\

'/oracle/PRD/sapdata1/sr3700_13/sr3700.data13',\

'/oracle/PRD/sapdata2/sr3700_2/sr3700.data2',\

'/oracle/PRD/sapdata2/sr3700_6/sr3700.data6',\

'/oracle/PRD/sapdata2/sr3700_10/sr3700.data10',\

'/oracle/PRD/sapdata2/sr3700_14/sr3700.data14',\

'/oracle/PRD/sapdata3/sr3700_7/sr3700.data7',\

'/oracle/PRD/sapdata3/sr3700_11/sr3700.data11',\

'/oracle/PRD/sapdata3/sr3700_15/sr3700.data15',\

'/oracle/PRD/sapdata3/sr3700_3/sr3700.data3',\

'/oracle/PRD/sapdata4/sr3700_16/sr3700.data16',\

'/oracle/PRD/sapdata4/sr3700_8/sr3700.data8',\

'/oracle/PRD/sapdata4/sr3700_12/sr3700.data12',\

'/oracle/PRD/sapdata4/sr3700_4/sr3700.data4'\

log=/oracle/imp/log/imp_ts_psapsr3700.log\

&& date >> /oracle/imp/log/imp_ts_psapsr3700.log.time

附件三：rman conver命令。

CONVERT DATAFILE '/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data1'

,'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data2',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data3',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data4',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data5',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data6',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data7',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data8',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data9',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data10',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data11',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data12',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data13',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data14',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data15',

'/sapbackup/hhdbackup/exp/datafile_psapsr3700/sr3700.data16'

FROM PLATFORM="Solaris[tm] OE (64-bit)"

DB_FILE_NAME_CONVERT= "/sapbackup/hhdbackup/exp/datafile_psapsr3700/" "/oracle/convert/sapdata3/";

附件四：以SAP习惯移动数据文件。

#!/bin/bash

for((i=1;i<=16;i++));

do

c=$(($i%4));

if ((c==0))

then

mkdir -p /oracle/QAS/sapdata4/sr3700.data$i/

cp -p ./sr3700.data$i /oracle/QAS/sapdata4/sr3700_$i/

else

mkdir -p /oracle/QAS/sapdata$c/sr3700_$i/

cp -p ./sr3700.data$i /oracle/QAS/sapdata$c/sr3700_$i/

fi

done