Oracle 数据泵迁移步骤规范

1、调研模块

1.1、确认迁移用户

以全库迁移为标准，也可直接通过需求方获取需要迁移的用户

1）确认数据库中所有用户及其创建时间

alter session set nls_date_format='yyyy-mm-dd-hh24:mi:ss'; 
select username,created   from dba_users  order by 2;

2）以系统用户创建时间为限确认需要的用户

select username,created,ACCOUNT_STATUS from dba_users where created >to_date('2008-03-14 00:31:40','yyyy-mm-dd hh24:mi:ss') order by 2;

1.2、确认数据量

1）查看总的数据量大小

set lines 200  
SELECT SUM (bytes) / 1024 / 1024 / 1024
FROM dba_segments where owner  in
(
select username from dba_users where created >to_date('2008-03-14 00:31:40','yyyy-mm-dd hh24:mi:ss') 
)
ORDER BY 1;

2）查看各个用户数据量大小

set lines 200  
SELECT owner,segment_name, SUM (bytes) / 1024 / 1024 
FROM dba_segments where owner  in
(
select username from dba_users where created >to_date('2008-03-14 00:31:40','yyyy-mm-dd hh24:mi:ss') 
)
GROUP BY owner,segment_name
ORDER BY 3;

3）确认各用户的lob对象大小

 set lines 200  
   SELECT owner, SUM (bytes) / 1024 / 1024 / 1024
       FROM dba_segments
      WHERE (owner, segment_name) IN (SELECT owner, segment_name FROM dba_lobs)
      and owner in 
      (
  select username from dba_users where created >to_date('2008-03-14 00:31:40','yyyy-mm-dd hh24:mi:ss') 
   )
   GROUP BY owner
   ORDER BY 2;

1.3、确认long字段情况

用以评估是否适合使用dblink，或者如何使用dblink迁移

1）确认是否存在long字段

 set lines 200  
select   OWNER,table_name from dba_tab_columns where  data_type='LONG' and owner  in
(
select username from dba_users where created >to_date('2008-03-14 00:31:40','yyyy-mm-dd hh24:mi:ss') 
)
order by 1;

2）确认long字段对象大小

 set lines 200  
   SELECT owner, SUM (bytes) / 1024 / 1024 / 1024,segment_name
       FROM dba_segments
      WHERE (owner, segment_name) IN 
      (select   OWNER,table_name from dba_tab_columns where  data_type='LONG' and owner  in
  (
  select username from dba_users where created >to_date('2008-03-14 00:31:40','yyyy-mm-dd hh24:mi:ss') 
  )
   )
   GROUP BY owner,segment_name
   ORDER BY 2;

2、业务停止前导入操作

2.1、删除用户

提前整理删除用户脚本，并在正式迁移前执行

1)生成删除脚本

select 'drop user'|| username || ' cascade;'  from dba_users 
 where created >to_date('2008-03-14 00:31:40','yyyy-mm-dd hh24:mi:ss') ;

2）执行删除命令

@/home/oracle/dblight/drop_user.sql

2.2、导入sqlprofile

1）创建dblink

drop public database link mig_toold;
create public database link mig_toold
connect to system
identified by "2wsx3edc" using 'INAS1522';

2）原端导出sqlset

select * from dba_sql_profiles
begin  dbms_sqltune.create_stgtab_sqlprof(table_name => 'STAGING_TABLE',  schema_name => 'dl_mig');end;
begin dbms_sqltune.pack_stgtab_sqlprof( staging_table_name => 'STAGING_TABLE',  staging_schema_owner => 'dl_mig');end;
select * from dl_mig.STAGING_TABLE

3）目标段导入sqlset

create table system.STAGING_TABLE
as
select * from dl_mig.STAGING_TABLE@dl_to_old

select * from dba_sql_profiles
begin dbms_sqltune.unpack_stgtab_sqlprof( staging_schema_owner=>'system',replace => TRUE,   staging_table_name => 'STAGING_TABLE');end; 

4）检查脚本

select name from dba_sql_profiles@dl_to_old where name not in (select name from dba_sql_profiles)

2.3、导入profile

nohup time impdp userid=system/1qaz2wsx parallel=4 metrics=yes network_link=mig_toold include=profile  full=y content=metadata_only > imp_profile.log &

tail -f  imp_profile.log 

2.4、导入role

nohup time impdp userid=system/1qaz2wsx parallel=4 metrics=yes network_link=mig_toold include=role full=y content=metadata_only > imp_role.log &

tail -f imp_role.log

2.5、导入用户及权限

nohup time impdp userid=system/1qaz2wsx parallel=4 metrics=yes network_link=mig_toold include=USER,system_grant,OBJECT_GRANT,ROLE_GRANT SCHEMAS=* content=metadata_only > imp_user.log &

tail -f  imp_user.log

2.6、导入dblink

nohup time impdp userid=system/system123 parallel=4 NETWORK_LINK=to_old include=DB_LINK full=y CONTENT=METADATA_ONLY > imp_db_link.log &

tail -f imp_db_link.log

3、正式迁移时期原端停止业务

3.1、停止业务

3.2、停止1521监听

srvctl stop listener -l LISTENER

crsctl status res -t

3.3、查杀LOCAL=NO的会话

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

ps -ef|grep LOCAL=NO

3.4、修改job参数，以及查杀JOB会话

ALTER SYSTEM SET job_queue_processes=0 scope=both sid='*';
ps -ef|grep ora_j | grep -v grep |awk '{print $2}'| xargs kill -9

ps -ef|grep ora_j 

3.5、修改local_listener参数

alter system  set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=133.37.22.228)(PORT=1522)))' sid='inasdb1' scope=both;
alter system  set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=133.37.22.229)(PORT=1522)))' sid='inasdb2' scope=both;

show parameter local_listener

3.6、检查大事务、停止源端数据库(两个节点)

sqlplus / as sysdba
alter system checkpoint;
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
alter system switch logfile;
alter system switch logfile;
shutdown immediate;

3.7、启动源端数据库

sqlplus / as sysdba
startup

3.8、检查并停止1521监听

srvctl stop listener -l LISTENER

crsctl status res -t

4、数据导入导出

4.1、单独导出大表、LOB、LONG（可分多个脚本）

nohup sh /home/oracle/dblight/*exp.sh >  /home/oracle/dblight/*exp.log 2>&1 &

4.2、单独导入大表、LOB、LONG（可分多个脚本）

nohup sh /home/oracle/dblight/impdp/*_mig.sh >  /home/oracle/dblight/impdp/*_mig.log 2>&1 &

4.3、小表统一导入

nohup sh /home/oracle/dblight/other_mig.sh >  /home/oracle/dblight/other_mig.log 2>&1 &

4.4、创建索引

nohup sqlplus system/Sm#2dbca@NEW_WBSDB @create_scwy_index01.sql &

4.5、导入约束序列触发器并补充权限

nohup time impdp userid=system/1qaz2wsx parallel=4 metrics=yes network_link=mig_toold include=OBJECT_GRANT,SYSTEM_GRANT,CONSTRAINT,SEQUENCE,trigger SCHEMAS=*content=metadata_only > grant_finish.log &

5、结尾

5.1、运行检查脚本检查对象情况

purge dba_recyclebin;    @ /home/oracle/dblight/impdp/checkobject.sql 

5.2、启动监听

srvctl start listener -l LISTENER

5.3、恢复job

ALTER SYSTEM SET job_queue_processes=10000 scope=both sid='*';

5.4、通知备份

6、特殊处理

6.1、通用

合理利用并行，数据量小的schema一起导入，数据量大的单独导入、分表导入

6.2、表数据量大或者lob大表

可以考虑分片导入数据，且lob对象可以“ENABLE STORAGE IN ROW”，参考：墨天轮脚本：SPLIT_TAB_ROWID https://www.modb.pro/download/3569

6.3、创建过慢索引

添加对应参数，并行执行

6.4、约束创建过慢

添加对应参数，并行执行或者直接NOVALIDATE创建

novalidate模板：
Alter Table stuInfo Add Constraint CK_stuAge check(stuAge between 15 and 40)  enable novalidate;