数据泵迁移(10g-19c).txt

明靓

云和恩墨交付战队

1668

3页

20次

2021-05-18

10墨值下载

--源库

影像系统-cache 数据库版本为 10.2.0.5

--目标库

影像系统-cache 数据库版本为 19.8.0.0

1.源库相关信息查询

set linesize 200 pagesize 200

select username,created,

ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE

from dba_users where username not in('ANONYMOUS',

'CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS',

'ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTE

M',

'WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','TSMSYS','ORACLE_OCM','FLOWS_FILES','A

PPQOSSYS',

'APEX030200','OWBSYS_AUDIT','ORDDATA','OWBSYS','XS$NULL','BI','PM','IX','SH','OE

','HR',

'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','QUERY','APEX_PUBLIC_USER','APEX

_030200');

USERNAME CREATED ACCOUNT_STAT DEFAULT_TABLESPACE

TEMPORARY_TABLESPACE PROFILE

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

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

FNOS00 12-AUG-19 OPEN FNOS00_TBS

FNOS00_TMP_TBS DEFAULT

FNGCD 12-AUG-19 OPEN FNGCD_TBS

FNGCD_TMP_TBS DEFAULT

DBAMONITOR 20-NOV-20 OPEN USERS TEMP

DEFAULT

FNOS01 12-AUG-19 OPEN FNOS01_TBS

FNOS01_TMP_TBS DEFAULT

##其中 DBAMONITOR 为监控用户，沟通后可不用迁移

select owner,sum(bytes)/1024/1024/1024 size_gb from dba_segments where owner in

('FNOS00','FNGCD','FNOS01') group by owner;

OWNER SIZE_GB

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

FNGCD .011779785

FNOS00 .016052246

FNOS01 57.0625

select distinct owner,tablespace_name from dba_segments where owner in

('FNOS00','FNGCD','FNOS01');

OWNER TABLESPACE_NAME

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

FNGCD FNGCD_TBS

FNOS00 FNOS00_TBS

FNOS01 FNOS01_TBS

2.目标端删除相关用户（这里删除是由于前期已做过测试）

sqlplus / as sysdba

alter session set container=FILENET;

drop user FNGCD cascade;

drop user FNOS00 cascade;

drop user FNOS01 cascade;

3.源端导出数据

##锁定业务用户

alter user FNGCD account unlock;

alter user FNOS00 account unlock;

alter user FNOS01 account unlock;

mkdir /picclife/soft/backup

chown oracle:oinstall /picclife/soft/backup

create directory edump as '/picclife/soft/backup';

nohup expdp \'/ as sysdba\' DIRECTORY=edump DUMPFILE=fn_user_0519_%U.dmp

LOGFILE=fn_user_0519.log SCHEMAS=FNGCD,FNOS00,FNOS01 parallel=16 &

##开始结束时间

Export: Release 10.2.0.5.0 - 64bit Production on Friday, 14 May, 2021 14:41:18

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:43:51

//消耗：2.5min 左右

4.scp dmp 文件

scp *.dmp oracle@10.135.30.106:/picclife/software/backup

//消耗：5min 左右

5.源端创建相关表空间（前期测试已执行）

create tablespace FNGCD_TBS datafile '+DATA' size 8192M autoextend off;

create tablespace FNOS00_TBS datafile '+DATA' size 8192M autoextend off;

create tablespace FNOS01_TBS datafile '+DATA' size 8192M autoextend off;

alter tablespace FNOS01_TBS add datafile '+DATA' size 8192M;

create temporary tablespace FNGCD_TMP_TBS tempfile size 500m autoextend on;

create temporary tablespace FNOS00_TMP_TBS tempfile size 500m autoextend on;

create temporary tablespace FNOS01_TMP_TBS tempfile size 500m autoextend on;

#srvctl add service -db yxcache -service cachedb -preferred yxcache1,yxcache2

-pdb filenet -role PRIMARY -policy AUTOMATIC -failovertype SELECT

-failovermethod BASIC -failoverretry 5 -failoverdelay 180

#srvctl start service -db yxcache -service cachedb -n yxcache1

#srvctl start service -db yxcache -service cachedb -n yxcache2

6.目标端导入

mkdir /picclife/software/backup

chown oracle:oinstall /picclife/software/backup

create directory edump as '/picclife/software/backup';

nohup impdp system/'oC16SvUIdW^g1Gv5'@10.135.30.106:1521/filenet directory=edump

DUMPFILE=fn_user_0519_%U.dmp logfile=impdp_filenet_0519.log parallel=8 cluster=N

EXCLUDE=STATISTICS &

##开始结束时间

Import: Release 19.0.0.0.0 - Production on Fri May 14 14:51:40 2021

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri May 14 15:12:54

2021 elapsed 0 00:21:04

//消耗：21min 左右