【数据泵迁移】- expdp/impdp

圆️️

已于 2025-02-11 10:38:44 修改

阅读量2.6k

点赞数 35

CC 4.0 BY-SA版权

分类专栏： # 数据泵文章标签： oracle 数据迁移数据泵 expdp/impdp

于 2024-09-01 21:21:22 首次发布

本文链接：https://blog.csdn.net/weixin_39099660/article/details/141788955

数据泵专栏收录该内容

10 篇文章

订阅专栏

一、数据泵迁移优缺点

1.1 优点
- 对数据库版本和平台无限制
- 支持开启并行（参数parallel）
- 可以直接从一个数据库到另一个数据库进行数据传输，无需将数据导出到中间文件
- 可以选择导出或导入特定的表、用户等，也可以指定导出或导入数据的具体部分
- 支持压缩功能（参数compress），可以减少存储空间和传输时间
1.2 缺点
- 不同版本的 Oracle 数据库之间可能会存在兼容性问题，特别是在源数据库和目标数据库版本差异较大的情况下
- 导出数据量大时，可能会对源数据库性能产生影响，尤其是当并行处理设置不当时
- 迁移前需要做好详细的规划和准备，迁移后需要校验数据

二、生产库信息检查及确认

2.1 确认要迁移的用户

SQL> SELECT USERNAME,CREATED FROM DBA_USERS WHERE ACCOUNT_STATUS='OPEN' AND USERNAME NOT IN ('SYS','SYSTEM') AND LOCK_DATE IS NULL ORDER BY 2;

2.2 查看字符集，表空间

字符集：

SQL> COL PARAMETER FOR a30

SQL> COL VALUE FOR a30

SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER LIKE 'NLS_%CHARACTERSET';

表空间：

当前表空间大小：

        SQL> SELECT TS.TABLESPACE_NAME,TS.BIGFILE,SUM(DF.BYTES)/1024/1024         "M" FROM DBA_TABLESPACES TS,DBA_DATA_FILES DF WHERE         TS.TABLESPACE_NAME=DF.TABLESPACE_NAME GROUP BY         TS.TABLESPACE_NAME,TS.BIGFILE ORDER BY 1;

        迁移用户使用的表空间：

        SQL> SELECT ''''||TABLESPACE_NAME||'''' FROM (

        SELECT LISTAGG(TABLESPACE_NAME,''',''') AS TABLESPACE_NAME FROM (

        SELECT DISTINCT TABLESPACE_NAME FROM DBA_TABLES WHERE OWNER IN         ('USERNAME1','USERNAME2')

        UNION ALL

        SELECT DISTINCT TABLESPACE_NAME FROM DBA_TAB_PARTITIONS WHERE         TABLE_OWNER IN ('USERNAME1','USERNAME2')

        UNION ALL

        SELECT DISTINCT TABLESPACE_NAME FROM DBA_TAB_SUBPARTITIONS         WHERE TABLE_OWNER IN ('USERNAME1','USERNAME2')

        UNION ALL

        SELECT DISTINCT TABLESPACE_NAME FROM DBA_INDEXES WHERE OWNER         IN ('USERNAME1','USERNAME2')

        UNION ALL

        SELECT DISTINCT TABLESPACE_NAME FROM DBA_IND_PARTITIONS WHERE         INDEX_OWNER IN ('USERNAME1','USERNAME2')

        UNION ALL

        SELECT DISTINCT TABLESPACE_NAME FROM DBA_IND_SUBPARTITIONS         WHERE INDEX_OWNER IN ('USERNAME1','USERNAME2')));

        迁移用户使用默认表空间：

        SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME         IN ('USERNAME1','USERNAME2');

        迁移用户使用非默认临时表空间：

        SQL> SELECT DISTINCT TEMPORARY_TABLESPACE FROM DBA_USERS         WHERE USERNAME IN ('USERNAME1','USERNAME2') AND         TEMPORARY_TABLESPACE!='TEMP';

        生产端生产创建表空间SQL，目标端执行

        (1)先创建表空间，小于30G，按照实际大小，大于30G按照30G创建，下面继续添加

        SQL> SELECT 'CREATE TABLESPACE '|| T.TABLESPACE_NAME || ' DATAFILE         SIZE '||        (CASE WHEN SUM(F.BYTES)/1024/1024 < 30720 THEN         SUM(F.BYTES)/1024/1024         ELSE 30720 END)||'M AUTOEXTEND ON MAXSIZE         30G;' FROM         DBA_TABLESPACES T , DBA_DATA_FILES F

        WHERE T.CONTENTS <> 'UNDO' AND T.CONTENTS <> 'TEMPORARY' AND         T.TABLESPACE_NAME NOT IN ('SYSTEM','SYSAUX','TEMP','USERS')

        AND T.TABLESPACE_NAME = F.TABLESPACE_NAME

        GROUP BY T.TABLESPACE_NAME;

        (2)根据生产表空间大小，部分大于30G表空间继续添加数据文件至与生产相当大小

        SQL> SELECT 'ALTER TABLESPACE '|| S.TABLESPACE_NAME || ' ADD DATAFILE         SIZE 30G;' FROM (SELECT T.TABLESPACE_NAME,SUM(F.BYTES)/1024/1024         DBFSIZE FROM DBA_TABLESPACES T,DBA_DATA_FILES F WHERE         T.TABLESPACE_NAME = F.TABLESPACE_NAME AND T.CONTENTS <>         'UNDO' AND T.CONTENTS <> 'TEMPORARY' AND T.TABLESPACE_NAME         NOT IN ('SYSTEM','SYSAUX','TEMP')         GROUP BY T.TABLESPACE_NAME) S

        WHERE S.DBFSIZE > 32670;

        (3)创建临时表空间

        SQL> SELECT 'CREATE TEMPORARY TABLESPACE '|| T.TABLESPACE_NAME || '         TEMPFILE SIZE 30G;' FROM DBA_TEMP_FILES T WHERE          T.TABLESPACE_NAME != 'TEMP';

2.3 查看profile，role，回收站

查看profile：

SET LINES 200 PAGES 2000

COL PROFILE FOR a20

COL RESOURCE_NAME FOR a30

COL LIMIT FOR a20

SELECT * FROM DBA_PROFILES

WHERE PROFILE IN (SELECT PROFILE FROM DBA_USERS

WHERE USERNAME IN ('USERNAME1','USERNAME2')) ORDER BY 1,2,3;

目标端创建对应的PROFILE语句

SET LINES 200 PAGES 2000

SELECT DBMS_METADATA.GET_DDL('PROFILE','PROFILE_NAME') FROM DUAL;

确认role：

SET LINES 200 PAGES 2000

COL GRANTEE FOR a20

COL GRANTED_ROLE FOR a20

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE IN ('USERNAME1','USERNAME2');

目标端创建role SQL：

SET LINES 200 PAGES 2000

SELECT DBMS_METADATA.GET_DDL('ROLE',ROLE_NAME) FROM DUAL;

--授权语句

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='ROLE1,ROLE2'

UNION ALL

SELECT * FROM ROLE_ROLE_PRIVS WHERE ROLE='ROLE1,ROLE2'

UNION ALL

SELECT * FROM ROLE_ROLE_PRIVS WHERE ROLE='ROLE1,ROLE2';

查看回收站：

SET LINES 200 PAGES 2000

SELECT OBJECT_NAME FROM DBA_RECYCLEBIN WHERE OWNER IN ('USERNAME1','USERNAME2');

清理回收站：

PURGE RECYCLEBIN;

2.4 对象/INVALID对象数量

SET LINES 200 PAGES 2000

COL OWNER FOR a20

COL OBJECT_TYPE FOR a30

SELECT OWNER,OBJECT_TYPE,COUNT(*) TOTAL,SUM(CASE WHEN STATUS<>'VALID' THEN 1 ELSE 0 END) INVALID FROM DBA_OBJECTS WHERE OWNER IN ('USERNAME1','USERNAME2') GROUP BY OWNER,OBJECT_TYPE ORDER BY 1,2;

2.5 检查JOB，DBLINK

确认迁移开始前，JOB是否会执行，记录当前JOB状态

SET LINES 200

COL JOB FOR A20

COL WHAT FOR A50

COL NEXT_DATE FOR A20

COL NEXT_SEC FOR A20

SELECT JOB,WHAT,NEXT_DATE,NEXT_SEC,INTERVAL,BROKEN FROM DBA_JOBS WHERE LOG_USER IN ('USERNAME1','USERNAME2');

--SCHEDULER

SET LINES 200

COL JOB_NAME FOR A30

COL PROGRAM_OWNER FOR A10

COL PROGRAM_NAME FOR A30

SELECT JOB_NAME,PROGRAM_OWNER,PROGRAM_NAME,NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS WHERE OWNER IN ('USERNAME1','USERNAME2');

--CRONTAB(ROOT,GRID,ORACLE)

# CRONTAB -L

检查DBLINK

--业务用户

SET LINES 200 PAGES 2000

SELECT DB_LINK,USERNAME,HOST FROM DBA_DB_LINKS WHERE OWNER IN ('USERNAME1','USERNAME2');

--PUBLIC

SET LINES 200 PAGES 2000

COL DB_LINK FOR a10

COL USERNAME, FOR a10

COL HOST FOR a50

SELECT DB_LINK,USERNAME,HOST FROM DBA_DB_LINKS WHERE OWNER='PUBLIC';

生产端生成PUBLIC DBLINK语句，目标端执行

SET LINES 200 PAGES 2000

SELECT DBMS_METADATA.GET_DDL('DB_LINK','DBLINK_NAME,'PUBLIC') FROM DUAL;

2.6 检查外部表，SQL Profile

检查外部表：

SET LINES 200 PAGES 2000

SELECT DISTINCT DEFAULT_DIRECTORY_NAME FROM DBA_EXTERNAL_TABLES WHERE OWNER IN ('USERNAME1','USERNAME2');

检查SQL PROFILE：

SET LINES 200 PAGES 2000

SELECT NAME,CATEGORY,SQL_TEXT,STATUS FROM DBA_SQL_PROFILES ORDER BY 1;

2.7 查看目录

SET LINES 200 PAGES 2000

COL DIRECTORY_NAME FOR a30

COL DIRECTORY_PATH FOR a100

SELECT DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORIES;

三、配置目标端数据库

3.1 创建步骤二用户，表空间，对象等

3.2 根据实际情况配置nfs

四、正式迁移

4.1 生产端停业务，数据库关闭监听，kill所有连接会话

停监听：

单实例：lsnrctl stop listener_name
RAC:srvctl stop listener -l listener_name
srvctl stop scan_listener

kill所有连接会话：

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

4.2 生产端导出数据

expdp \'/ as sysdba\' DIRECTORY=expdp SCHEMAS=USERNAME1,USERNAME2 DUMPFILE=expdp_%U.DMP LOGFILE=EXPDP.LOG PARALLEL=8 CLUSTER=N EXCLUDE=STATISTICS

4.3 目标端导入数据

impdp \'/ as sysdba\' DIRECTORY=expdp DUMPFILE=impdp_%U.DMP LOGFILE=IMPDP.LOG PARALLEL=8 CLUSTER=N

4.4 两端数据验证核对

核对profile：

SET LINES200 PAGES 2000

COL PROFILE FOR A40

COL RESOURCE_NAME FOR A30

COL LIMIT FOR A20

SELECT * FROM DBA_PROFILES

WHERE PROFILE IN (SELECT PROFILE FROM DBA_USERS

WHERE USERNAME IN ('USERNAME1','USERNAME2')) ORDER BY 1,2,3;

核对role：

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE IN ('USERNAME1','USERNAME2');

核对job：

--JOB

SET LINES200 PAGES 2000

SELECT JOB,WHAT,BROKEN FROM DBA_JOBS WHERE LOG_USER IN ('USERNAME1','USERNAME2');

--SCHEDULER

SET LINES200 PAGES 2000

SELECT JOB_NAME,PROGRAM_OWNER,PROGRAM_NAME FROM DBA_SCHEDULER_JOBS WHERE OWNER IN ('USERNAME1','USERNAME2');

--CRONTAB(ROOT,GRID,ORACLE)

crontab -l

核对外部表：

SELECT DISTINCT DEFAULT_DIRECTORY_NAME FROM DBA_EXTERNAL_TABLES WHERE OWNER IN ('USERNAME1','USERNAME2');

核对DBLINK：

--业务用户

SET LINES 200 PAGES 2000

SELECT DB_LINK,USERNAME,HOST FROM DBA_DB_LINKS WHERE OWNER IN ('USERNAME1','USERNAME2');

--PUBLIC

SET LINES 200 PAGES 2000

COL DB_LINK FOR a10

COL USERNAME, FOR a10

COL HOST FOR a50

SELECT DB_LINK,USERNAME,HOST FROM DBA_DB_LINKS WHERE OWNER='PUBLIC';

4.5 编译无效对象

SQL> @?/rdbms/admin/utlrp.sql

两端无效对象对比：

SET LINES200 PAGES 2000

COL OWNER FOR A20

COL OBJECT_TYPE FOR A30

SELECT OWNER,OBJECT_TYPE,COUNT(*) TOTAL,SUM(CASE WHEN STATUS<>'VALID' THEN 1 ELSE 0 END) INVALID FROM DBA_OBJECTS WHERE OWNER IN ('USERNAME1','USERNAME2') GROUP BY OWNER,OBJECT_TYPE ORDER BY 1,2;

4.6 目标端收集统计信息

收集数据字典统计信息：

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(DEGREE=>4);

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

收集全库统计信息：

SQL> begin

dbms_stats.gather_database_stats(

ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all columns size auto', cascade=>true, degree=>72);

end;

/