暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / oracle11 to oracle12 xtts迁移步骤（停机前）

oracle11 to oracle12 xtts迁移步骤（停机前）

原创 lvzhengwei 2022-06-22
420

新12c prm xtts步骤：

环境介绍：
源端：PRM生产库： Linux prmdb1 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
目标端：prm新库： Linux raco1 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

SQL> select sum(bytes/1024/1024/1024) from dba\_data\_files;  
  
SUM(BYTES/1024/1024/1024)  
\-------------------------  
7791.42967

目标端ASM大小：

NAME TOTAL\_GB FREE\_GB TYPE USE\_RATE  
\-------------------------------- ---------- ---------- ------------------ ----------  
CRSDG 300 299.152344 NORMAL 0  
DATADG 9000 8928.15234 EXTERN 1  
ARCHDG 1000 993.054688 EXTERN 1

目标端挂载：mount -t nfs -o rw,hard,rsize=32768,wsize=32768,vers=3,nolock 10.62.250.142:/nfs /nfsnew

开启块跟踪：

ASMCMD> pwd  
+datadg/prmdb/bct  
  
select \* from v$block\_change\_tracking;  
  
alter database enable block change tracking using file '+datadg/prmdb/bct/bct';

上次需要传输的表空间：

PRM\_INDEX\_02  
COMM\_DATA  
PRM\_DATA\_03  
PRM\_DATA\_BAK  
PRM\_DATA\_01  
SETTLE\_DATA  
PRM\_DATA\_04  
PETRI\_DATA  
PRM\_INDEX\_01  
PRM\_DATA\_02  
PRM\_INTER\_INDEX  
PRM\_TINTER  
COMM\_IDX  
SETTLE\_ACCT  
USERS  
CUSTOM  
COMM\_TBS

这次需要传输的表空间：

TABLESPACE\_NAME TABLESPACE\_SIZE\_GB USED\_SPACE\_GB USED\_PERCENT  
\-------------------- ------------------ ------------- ------------  
PRM\_DATA\_03 680 589 86.6772921  
COMM\_DATA 992 818 82.4491524  
ZHJS\_CPSP\_DATA 420 307 73.1656174  
PRM\_INDEX\_02 110 80 72.3451416  
PRM\_INDEX\_01 80 53 66.3114576  
PRM\_DATA\_BAK 2415 1477 61.1646536  
PRM\_DATA\_01 1184 702 59.289114  
SETTLE\_ACCT 96 52 53.7175584  
PETRI\_DATA 32 17 52.1153171  
PRM\_DATA\_02 288 136 47.0727223  
PRM\_INTER\_INDEX 144 61 42.4245045  
SETTLE\_DATA 444 179 40.2748219  
PRM\_DATA\_04 337 125 37.2019604  
PRM\_TINTER 344 106 30.7280088  
SYSAUX 32 10 29.7361516  
COMM\_IDX 96 23 24.1905496  
SYSTEM 32 2 6.31752315  
UNDOTBS1 96 1 .994364895  
CUSTOM 40 0 .927542425  
USERS 0 0 .857843137  
COMM\_TBS 16 0 .510087291  
UNDOTBS2 32 0 .089263959  
TEMP 217 0 .001347346  
  
  
PRM\_DATA\_03  
COMM\_DATA  
ZHJS\_CPSP\_DATA  
PRM\_INDEX\_02  
PRM\_INDEX\_01  
PRM\_DATA\_BAK  
PRM\_DATA\_01  
SETTLE\_ACCT  
PETRI\_DATA  
PRM\_DATA\_02  
PRM\_INTER\_INDEX  
SETTLE\_DATA  
PRM\_DATA\_04  
PRM\_TINTER  
COMM\_IDX  
CUSTOM  
USERS  
COMM\_TBS  
  
PRM\_DATA\_03,COMM\_DATA,ZHJS\_CPSP\_DATA,PRM\_INDEX\_02,PRM\_INDEX\_01,PRM\_DATA\_BAK,PRM\_DATA\_01,SETTLE\_ACCT,PETRI\_DATA,PRM\_DATA\_02,PRM\_INTER\_INDEX,SETTLE\_DATA,PRM\_DATA\_04,PRM\_TINTER,COMM\_IDX,CUSTOM,USERS,COMM\_TBS  
  
'PRM\_DATA\_03','COMM\_DATA','ZHJS\_CPSP\_DATA','PRM\_INDEX\_02','PRM\_INDEX\_01','PRM\_DATA\_BAK','PRM\_DATA\_01','SETTLE\_ACCT','PETRI\_DATA','PRM\_DATA\_02','PRM\_INTER\_INDEX','SETTLE\_DATA','PRM\_DATA\_04','PRM\_TINTER','COMM\_IDX','CUSTOM','USERS','COMM\_TBS'

自包含检查：

execute dbms\_tts.transport\_set\_check('PRM\_DATA\_03,COMM\_DATA,ZHJS\_CPSP\_DATA,PRM\_INDEX\_02,PRM\_INDEX\_01,PRM\_DATA\_BAK,PRM\_DATA\_01,SETTLE\_ACCT,PETRI\_DATA,PRM\_DATA\_02,PRM\_INTER\_INDEX,SETTLE\_DATA,PRM\_DATA\_04,PRM\_TINTER,COMM\_IDX,CUSTOM,USERS,COMM\_TBS',true,true);  
  
select \* from TRANSPORT\_SET\_VIOLATIONS;  
  
  
  
select PLATFORM\_ID from v$database;  
PLATFORM\_ID  
\-----------  
13

编辑xtts配置文件：
老库配置：

vi xtt.properties  
  
tablespaces=PRM\_DATA\_03,COMM\_DATA,ZHJS\_CPSP\_DATA,PRM\_INDEX\_02,PRM\_INDEX\_01,PRM\_DATA\_BAK,PRM\_DATA\_01,SETTLE\_ACCT,PETRI\_DATA,PRM\_DATA\_02,PRM\_INTER\_INDEX,SETTLE\_DATA,PRM\_DATA\_04,PRM\_TINTER,COMM\_IDX,CUSTOM,USERS,COMM\_TBS  
platformid=13  
dfcopydir=/nfsnew/xtts/src\_backup  
backupformat=/nfsnew/xtts/backup\_incre  
backupondest=/nfsnew/xtts/backup\_incre  
stageondest=/nfsnew/xtts/src\_backup  
storageondest=+DATADG/prmdb/datafile  
parallel=16  
rollparallel=16  
getfileparallel=6

新库配置：

vi xtt.properties  
  
tablespaces=PRM\_DATA\_03,COMM\_DATA,ZHJS\_CPSP\_DATA,PRM\_INDEX\_02,PRM\_INDEX\_01,PRM\_DATA\_BAK,PRM\_DATA\_01,SETTLE\_ACCT,PETRI\_DATA,PRM\_DATA\_02,PRM\_INTER\_INDEX,SETTLE\_DATA,PRM\_DATA\_04,PRM\_TINTER,COMM\_IDX,CUSTOM,USERS,COMM\_TBS  
platformid=13  
dfcopydir=/nfsnew/xtts/src\_backup  
backupformat=/nfsnew/xtts/backup\_incre  
backupondest=/nfsnew/xtts/backup\_incre  
stageondest=/nfsnew/xtts/backup\_incre  
storageondest=+DATADG/prmdb/datafile  
parallel=16  
rollparallel=16  
getfileparallel=6  
asm\_home=/oracle/app/12.2.0/grid  
asm\_sid=+ASM1

创建通往老库的dblink：

tns：  
prmdb\_old =  
(DESCRIPTION =  
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.62.248.206)(PORT = 11521))  
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.62.248.208)(PORT = 11521))  
(LOAD\_BALANCE = yes)  
(CONNECT\_DATA =  
(SERVER = DEDICATED)  
(SERVICE\_NAME = prmdb)  
)  
)  
  
create public database link ttslink connect to QUERY identified by "query\_prmdb" using 'prmdb\_old';

创建测试用户： 33个

create user PSLOCAL identified by test\_2019;  
create user WEIHU identified by test\_2019;  
create user BJ\_STAT identified by test\_2019;  
create user PRM\_RH identified by test\_2019;  
create user SETTLE identified by test\_2019;  
create user COMM identified by test\_2019;  
create user PETRI identified by test\_2019;  
create user INTF identified by test\_2019;  
create user TEMPDBA identified by test\_2019;  
create user SETTLE\_CHECK identified by test\_2019;  
create user PATROL identified by test\_2019;  
create user ZHJS\_CPSP identified by test\_2019;  
create user PROFILER identified by test\_2019;  
create user ZHUNX identified by test\_2019;  
create user QUERY identified by test\_2019;  
create user PRM\_UOP identified by test\_2019;  
create user PRM\_UAP identified by test\_2019;  
create user PRM\_WORK identified by test\_2019;  
create user PRM\_IN\_INF identified by test\_2019;  
create user PRM\_OUT\_INF identified by test\_2019;  
create user HPBACKUP identified by test\_2019;  
create user HP\_DBSPI identified by test\_2019;  
create user PRM\_STL identified by test\_2019;  
create user HEFENG identified by test\_2019;  
create user PRM\_HP identified by test\_2019;  
create user SETTLE\_ACCT identified by test\_2019;  
create user DUL identified by test\_2019;  
create user BINLL identified by test\_2019;  
create user YEW identified by test\_2019;  
create user LIXINGUO identified by test\_2019;  
create user HAOWG identified by test\_2019;  
create user XIAOJUN identified by test\_2019;  
create user WANGLZ identified by test\_2019;  
  
PSLOCAL,WEIHU,BJ\_STAT,PRM\_RH,SETTLE,COMM,PETRI,INTF,TEMPDBA,SETTLE\_CHECK,PATROL,ZHJS\_CPSP,PROFILER,ZHUNX,QUERY,PRM\_UOP,PRM\_UAP,PRM\_WORK,PRM\_IN\_INF,PRM\_OUT\_INF,HPBACKUP,HP\_DBSPI,PRM\_STL,HEFENG,PRM\_HP,SETTLE\_ACCT,DUL,BINLL,YEW,LIXINGUO,HAOWG,XIAOJUN,WANGLZ  
  
'PSLOCAL','WEIHU','BJ\_STAT','PRM\_RH','SETTLE,COMM','PETRI,INTF','TEMPDBA','SETTLE\_CHECK','PATROL','ZHJS\_CPSP','PROFILER','ZHUNX','QUERY','PRM\_UOP','PRM\_UAP','PRM\_WORK','PRM\_IN\_INF','PRM\_OUT\_INF','HPBACKUP','HP\_DBSPI','PRM\_STL','HEFENG','PRM\_HP','SETTLE\_ACCT','DUL','BINLL','YEW','LIXINGUO','HAOWG','XIAOJUN','WANGLZ'

创建角色：

select role,PASSWORD\_REQUIRED from dba\_roles@ttslink  
minus  
select role,PASSWORD\_REQUIRED from dba\_roles  
  
ROLE  
\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
PASSWORD\_REQUIRED  
\------------------------  
DELETE\_CATALOG\_ROLE  
NO  
  
WEIHU\_ROLE  
NO

创建语句：

\-- Create the role  
create role DELETE\_CATALOG\_ROLE;  
\-- Grant/Revoke object privileges  
grant delete on SYS.AUD$ to DELETE\_CATALOG\_ROLE;  
grant delete on SYS.FGA\_LOG$ to DELETE\_CATALOG\_ROLE;  
  
  
\-- Create the role  
create role WEIHU\_ROLE  
identified by "";  
\-- Grant/Revoke role privileges  
grant connect to WEIHU\_ROLE;  
grant resource to WEIHU\_ROLE;  
\-- Grant/Revoke system privileges  
grant create any procedure to WEIHU\_ROLE;  
grant create any synonym to WEIHU\_ROLE;  
grant create any table to WEIHU\_ROLE;  
grant debug any procedure to WEIHU\_ROLE;  
grant debug connect session to WEIHU\_ROLE;  
grant delete any table to WEIHU\_ROLE;  
grant execute any procedure to WEIHU\_ROLE;  
grant execute any program to WEIHU\_ROLE;  
grant insert any table to WEIHU\_ROLE;  
grant select any dictionary to WEIHU\_ROLE;  
grant select any sequence to WEIHU\_ROLE;  
grant select any table to WEIHU\_ROLE;  
grant update any table to WEIHU\_ROLE;

执行全备：

vi full\_backup.sh  
  
export TMPDIR=/nfsnew/xtts/xtts\_tmp  
export PERL5LIB=$ORACLE\_HOME/perl/lib  
/oracle/app/oracle/product/11.2.0/dbhome\_1/perl/bin/perl xttdriver.pl -p -d  
  
nohup /oracle/xtts/full\_backup.sh > /oracle/xtts/full\_backup.log &  
  
  
mount -t nfs -o rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp 192.168.1.90:/tmp/nfs /nfs  
  
mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600 10.62.250.142:/nfs /nfsnew

全恢复：

export TMPDIR=/nfsnew/xtts/xtts\_tmp  
export ORACLE\_SID=prmdb1  
export PERL5LIB=$ORACLE\_HOME/perl/lib  
/oracle/app/oracle/product/12.2.0/dbhome\_1/perl/bin/perl xttdriver.pl -c -d  
  
nohup /nfsnew/xtts/xtts/full\_restore.sh > /nfsnew/xtts/xtts/full\_restore.log &

第一次增量备份：

cd /oracle/xtts  
vi incre\_backup.sh  
  
export TMPDIR=/nfsnew/xtts/xtts\_tmp  
export ORACLE\_SID=prmdb2  
export PERL5LIB=$ORACLE\_HOME/perl/lib  
/oracle/app/oracle/product/11.2.0/dbhome\_1/perl/bin/perl xttdriver.pl -i -d  
  
nohup /oracle/xtts/incre\_backup.sh > /oracle/xtts/incre\_backup.log &  
  
15:33 - 15:44

第一次增量恢复：

cd /nfsnew/xtts/xtts  
vi /nfsnew/xtts/xtts/incre\_recover.sh  
export TMPDIR=/nfsnew/xtts/xtts\_tmp  
export ORACLE\_SID=prmdb1  
export PERL5LIB=$ORACLE\_HOME/perl/lib  
/oracle/app/oracle/product/12.2.0/dbhome\_1/perl/bin/perl xttdriver.pl -r -d

执行：

nohup /nfsnew/xtts/xtts/incre\_recover.sh > /nfsnew/xtts/xtts/incre\_recover.log &  
  
  
  
\[oracle@prmdb2 xtts\_tmp\]$ ls -lrt  
total 3232  
\-rw-r--r-- 1 oracle oinstall 3044 Jun 21 21:01 xttprepare.cmd1561123826  
\-rw-r--r-- 1 oracle oinstall 182 Jun 21 21:01 xttplan.txt1561123826  
\-rw-r--r-- 1 oracle oinstall 2011 Jun 21 21:01 rmanconvert.cmd1561123826  
\-rw-r--r-- 1 oracle oinstall 2127504 Jun 21 21:01 rmantrc\_99169\_290\_prepare.trc  
\-rw-r--r-- 1 oracle oinstall 6755 Jun 21 21:01 FAILED.bak  
\-rw-r--r-- 1 oracle oinstall 2073 Jun 24 15:08 xttplan.txt  
\-rw-r--r-- 1 oracle oinstall 22783 Jun 24 15:08 rmanconvert.cmd  
\-rw-r--r-- 1 oracle oinstall 777954 Jun 24 20:30 rmantrc\_168220\_363\_convert.trc  
\-rw-r--r-- 1 oracle oinstall 28025 Jun 24 20:30 FAILED.bak2  
\-rw-r--r-- 1 oracle oinstall 19431 Jun 25 18:32 xttnewdatafiles.txt  
\-rw-r--r-- 1 oracle oinstall 66 Jun 26 15:32 xttprepare.cmd  
\-rw-r--r-- 1 oracle oinstall 2502 Jun 26 15:32 xttplan.txt.new  
\-rw-r--r-- 1 oracle oinstall 3092 Jun 26 15:32 rmanincr.cmd  
\-rw-r--r-- 1 oracle oinstall 3331 Jun 26 15:43 tsbkupmap.txt  
\-rw-r--r-- 1 oracle oinstall 2340 Jun 26 15:43 incrbackups.txt  
\-rw-r--r-- 1 oracle oinstall 41 Jun 26 18:46 FAILED.bak3

第二次增量备份：

cd /oracle/xtts  
  
nohup /oracle/xtts/incre\_backup.sh > /oracle/xtts/incre\_backup0627\_2.log &  
  
10:38 - 10:48

第二次增量恢复：

cd /nfsnew/xtts/xtts  
  
mv xttplan.txt xttplan.old1.txt  
  
mv xttplan.txt.new xttplan.txt  
  
nohup /nfsnew/xtts/xtts/incre\_recover.sh > /nfsnew/xtts/xtts/incre\_recover0627\_2.log &  
  
11:02 - 11:24

第三次增量备份：

cd /oracle/xtts  
  
nohup /oracle/xtts/incre\_backup.sh > /oracle/xtts/incre\_backup0703\_1.log &  
  
11:01 - 13:13

第三次增量恢复：

cd /nfsnew/xtts/xtts  
  
mv xttplan.txt xttplan.old2.txt  
  
mv xttplan.txt.new xttplan.txt  
  
nohup /nfsnew/xtts/xtts/incre\_recover.sh > /nfsnew/xtts/xtts/incre\_recover0703\_1.log &  

15：04 - 18: 54 --- 失败 有人修改权限导致读取失败

重新开始

10:43 - 21:25

第四次增量备份：

cd /oracle/xtts  
  
nohup /oracle/xtts/incre\_backup.sh > /oracle/xtts/incre\_backup0705\_1.log &  
  
16:14 - 16:39

第四次增量恢复：

cd /nfsnew/xtts/xtts  
  
mv xttplan.txt xttplan.old3.txt  
  
mv xttplan.txt.new xttplan.txt  
  
nohup /nfsnew/xtts/xtts/incre\_recover.sh > /nfsnew/xtts/xtts/incre\_recover0705\_1.log &  
  
17：04 - 17:42

第五次增量备份：

cd /oracle/xtts  
  
nohup /oracle/xtts/incre\_backup.sh > /oracle/xtts/incre\_backup0708\_1.log &  
  
11：51 - 12:02

第五次增量恢复：

cd /nfsnew/xtts/xtts  
  
mv xttplan.txt xttplan.txt.old4  
  
mv xttplan.txt.new xttplan.txt  
  
nohup /nfsnew/xtts/xtts/incre\_recover.sh > /nfsnew/xtts/xtts/incre\_recover0708\_1.log &  
  
  
14:02 - 14:39

创建目录：新老都得有：

create directory xtts as '/nfsnew/xtts/dmp';  
  
  
  
vi expdp\_xtts.sh  
export ORACLE\_SID=prmdb2  
expdp \\' /as sysdba\\' parfile=expdp\_xtts.par

第六次增量备份：

cd /oracle/xtts  
  
nohup /oracle/xtts/incre\_backup.sh > /oracle/xtts/incre\_backup0715\_1.log &

第六次增量恢复：

cd /nfsnew/xtts/xtts  
  
mv xttplan.txt xttplan.txt.old5  
  
mv xttplan.txt.new xttplan.txt  
  
nohup /nfsnew/xtts/xtts/incre\_recover.sh > /nfsnew/xtts/xtts/incre\_recover0715\_1.log &

第七次增量备份：

cd /oracle/xtts  
  
nohup /oracle/xtts/incre\_backup.sh > /oracle/xtts/incre\_backup0715\_2.log &

第七次增量恢复：

cd /nfsnew/xtts/xtts  
  
mv xttplan.txt xttplan.txt.old6  
  
mv xttplan.txt.new xttplan.txt  
  
nohup /nfsnew/xtts/xtts/incre\_recover.sh > /nfsnew/xtts/xtts/incre\_recover0715\_2.log &

第八次增量备份：

cd /oracle/xtts  
  
nohup /oracle/xtts/incre\_backup.sh > /oracle/xtts/incre\_backup0715\_3.log &  
  
22:38 - 22:40

第八次增量恢复：

cd /nfsnew/xtts/xtts  
  
mv xttplan.txt xttplan.txt.old7  
  
mv xttplan.txt.new xttplan.txt  
  
nohup /nfsnew/xtts/xtts/incre\_recover.sh > /nfsnew/xtts/xtts/incre\_recover0715\_3.log &  
  
22:41 - 22:43  
  
  
  
mount -t nfs -o rw,hard,rsize=32768,wsize=32768,vers=3,nolock 10.62.250.142:/nfs /nfsnew

注意：imp xtts.par的数据文件写全路径: +datadg 并且数据文件写的是新库的数据文件

墨力计划oracleoracle创建用户testperl
最后修改时间：2022-06-27 10:14:32
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论