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大表