expdp用户10迁移到新环境11之正式实施
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)))';
