expdp用户10迁移到新环境11之正式实施

向右看齐 2021-12-21 09:17 182阅读 0赞

expdp迁移  
源端数据库：cu    源端IP：   源端schema： xxx  
目标数据库：ora 目标IP：xxx  
操作流程：31日凌晨应用停，随后使用数据泵迁移，两套库迁移，迁移一套，迁移完毕应用确认结束；

环境说明：源端两套库都是单实例，10.2.0.4、 同一个目标端11.2.0.4 RAC2节点  
数据量：加一起30G不到，数据量小

迁移用户：一套7个用户，一套4个用户  
停机时间：数据库操作1小时，应用1小时，总2小时

1.环境确认  
源端 目标端  
Oracle软件版本 10.2.0.4 单机 11.2.0.4 rac2节点  
操作系统版本 AIX xxx2 Linux Server release 6.7  
IP地址

数据库字符集 ZHS16GBK，UTF8 ZHS16GBK,AL16UTF16  
NLS\_LANG=american\_america.zhs16gbk  
导出时间 15分钟dump(并行分组排序10分钟，实际装载dump数据5分钟)  
导入时间 5分钟  
scp时间待定 10g  
收集统计信息时间 1分钟

2.迁移用户确认  
\---迁移7个用户  
in('xx')

3.杀会话脚本:  
select SID,USERNAME,OSUSER,MACHINE,PROGRAM,LAST\_CALL\_ET,EVENT from v$session where username IN ('xx');  
用户锁定：  
select 'alter system kill session ''' || sid || ',' || SERIAL\# || ''' immediate;' from v$session where username IN ('xx');

alter user user\_name account lock;

\#ps -ef|grep oracle|grep LOCAL=NO|grep -v grep|awk \{'print $2'\} |xargs kill -9

4.导出  
select \* from dba\_directories;  
xdump /xxx/dump

\#expdp system/xxx schemas=xxx directory=xdump dumpfile=CUSS%U.dmp PARALLEL=4 logfile=expdp\_20181212.log

*--测试时间，导出12分钟，导出四个dump*

*SCP  
\-- Zhiban16!  
scp CUSS\* ora11g@xx:/11g/dump/*

ls -lrt 对比  
\-rw-r----- 1 ora10g dba 5416284160 Mar 31 00:46 CUSS04.dmp  
\-rw-r----- 1 ora10g dba 1528893440 Mar 31 00:46 CUSS03.dmp  
\-rw-r----- 1 ora10g dba 2703183872 Mar 31 00:46 CUSS02.dmp  
\-rw-r----- 1 ora10g dba 1574539264 Mar 31 00:46 CUSS01.dmp  
===============  
\-rw-r----- 1 ora11g dba 1574539264 Mar 31 00:48 CUSS01.dmp  
\-rw-r----- 1 ora11g dba 2703183872 Mar 31 00:49 CUSS02.dmp  
\-rw-r----- 1 ora11g dba 1528893440 Mar 31 00:49 CUSS03.dmp  
\-rw-r----- 1 ora11g dba 5416284160 Mar 31 00:53 CUSS04.dmp

ora11g@nf5280q9-rac$ du -sm CUSS\*  
1504 CUSS01.dmp  
2581 CUSS02.dmp  
1460 CUSS03.dmp  
5171 CUSS04.dmp

00:04:07 --导入5分钟  
\#SYSTEM"."SYS\_IMPORT\_SCHEMA\_01": system/\*\*\*\*\*\*\*\* schemas=xxx directory=xdump dumpfile=CUSS%U.dmp PARALLEL=8 cluster=N logfile=impdp\_CUSS\_20181212.log

节点1导入  
cd /xx/dump/  
impdp system/xx schemas=xx,xxG directory=xdump dumpfile=CUSS%U.dmp PARALLEL=8 cluster=N logfile=impdp\_CUSS\_20190331.log

5.失效对象  
select 'alter ' ||decode(object\_type, 'PACKAGE BODY', 'PACKAGE', object\_type) || ' '  
|| OWNER ||'.'||OBJECT\_NAME || decode(object\_type, 'PACKAGE BODY',' compile body ; ', ' compile; ')from dba\_objects  
where owner in('OCKI','CUSS\_TUXEDO','CUSS','B\_OCKI','B\_CUSS','B\_TASK','P\_LUWANG') and status <> 'VALID' AND OBJECT\_TYPE NOT LIKE 'JAVA%';

6.对比  
set lines 160 pages 1000  
col owner for a20  
col object\_type for a20  
col status for a10  
col s\_cnt for 99999  
col d\_cnt for 99999  
col lost\_cnt for 99999  
col object\_name for a30  
select s.owner,s.object\_type,s.status,s.cnt s\_cnt,nvl(d.cnt,0) d\_cnt,s.cnt-nvl(d.cnt,0) lost\_cnt from  
(SELECT OWNER,OBJECT\_TYPE,STATUS,COUNT(\*) cnt FROM sys.DBA\_OBJECTS WHERE object\_name not like '%BIN%' and OWNER  
in('xxx') GROUP BY OWNER,OBJECT\_TYPE,STATUS) s  
,  
(SELECT OWNER,OBJECT\_TYPE,STATUS,COUNT(\*) cnt FROM sys.DBA\_OBJECTS@db\_test\_user\_yang WHERE object\_name not like '%BIN%' and OWNER in('xxx') GROUP BY OWNER,OBJECT\_TYPE,STATUS ) d  
where s.owner=d.owner(+)  
and s.object\_type=d.object\_type(+)  
and s.status=d.status(+)  
and s.status='VALID'  
ORDER BY 1,2,3;  
\#S\_CNT 当前，迁移后的环境数量  
\#D\_CNT 老环境数量  
\#LOST\_CNT 差异数量 0正常

\--统计数据库中回收站对象，可忽略  
SELECT OWNER,OBJECT\_TYPE,STATUS cnt FROM sys.DBA\_OBJECTS@db\_test\_user\_yang WHERE object\_name like '%BIN%' and OWNER in('CUSS') GROUP BY OWNER,OBJECT\_TYPE,STATUS;  
OWNER OBJECT\_TYPE CNT  
\-------------------- -------------------- -------  
CUSS TABLE VALID  
CUSS INDEX VALID

OWNER OBJECT\_TYPE STATUS S\_CNT D\_CNT LOST\_CNT  
\-------------------- -------------------- ---------- ------ ------ --------  
CUSS INDEX VALID 182 172 10  
CUSS LOB VALID 10 10 0  
CUSS PACKAGE VALID 2 2 0  
CUSS PACKAGE BODY VALID 2 2 0  
CUSS PROCEDURE VALID 18 18 0  
CUSS SEQUENCE VALID 42 42 0  
CUSS TABLE VALID 175 175 0  
CUSS\_TUXEDO SYNONYM VALID 7 7 0  
OCKI INDEX VALID 96 94 2  
OCKI LOB VALID 2 2 0  
OCKI PROCEDURE VALID 9 9 0  
OCKI SEQUENCE VALID 5 5 0  
OCKI TABLE VALID 117 117 0  
OCKI VIEW VALID 1 1 0

14 rows selected.

\--对比校验指定类型数量  
select OWNER,OBJECT\_TYPE,STATUS,object\_name from dba\_objects@db\_test\_user\_yang where owner='CUSS' and OBJECT\_TYPE='TABLE'  
minus  
select OWNER,OBJECT\_TYPE,STATUS,object\_name from dba\_objects where owner='CUSS' and OBJECT\_TYPE='TABLE'  
;

select OWNER,OBJECT\_TYPE,STATUS,object\_name from dba\_objects where owner='CUSS' and OBJECT\_TYPE='TABLE'  
minus  
select OWNER,OBJECT\_TYPE,STATUS,object\_name from dba\_objects@db\_test\_user\_yang where owner='CUSS' and OBJECT\_TYPE='TABLE'  
;

\#确定LOB查询数量一致即可  
SQL> select owner,table\_name,column\_name,segment\_name,index\_name from dba\_lobs where owner in('xx');  
SQL> select owner,table\_name,column\_name,segment\_name,index\_name from dba\_lobs@db\_test\_user\_yang where owner in('xx');

select owner,segment\_type,round(sum(bytes)/1024/1024,2) Mb from dba\_segments where owner in('xx') group by owner,segment\_type;

select owner,segment\_type,round(sum(bytes)/1024/1024,2) Mb from dba\_segments@db\_test\_user\_yang where owner in('xx') group by owner,segment\_type;

7.用户解锁  
alter user USERNAME account unlock;  
SQL> select username,ACCOUNT\_STATUS,DEFAULT\_TABLESPACE,TEMPORARY\_TABLESPACE,PROFILE from dba\_users where username in('xxx');

7.收集统计信息  
exec dbms\_stats.gather\_schema\_stats(ownname =>'xx',degree=>6,cascade=>true);

8.删除对比db\_link  
drop database link db\_test\_user\_yang;  
\#drop public database link dest\_to\_source;  
\#create database link db\_test\_user\_yang connect to system identified by "shi9daDBA" using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1523)) (CONNECT\_DATA = (SERVER = DEDICATED) (SERVICE\_NAME = service\_name)))';

转载于:https://www.cnblogs.com/lvcha001/p/10653597.html