本方案通过expdp/impdp+dblink进行oracle数据库迁移;先通过expdp/impdp进行oracle元数据的迁移,再通过dblink进行表数据的迁移;支持单实例、多实例(oracle rac),支持跨平台、跨版本数据迁移。
1 数据库环境
1.1 源数据库
操作系统:Red Hat Enterprise Linux Server release 4 (Tikanga)
Oracle database版本:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
IP:10.218.233.12
数据库名称:jsdbfx
用户:jsdb
表空间:js_stat
1.2 目的数据库
操作系统:Red Hat Enterprise Linux Server release 7.6 (Maipo)
Oracle database版本:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
IP:10.221.200.119
数据库名称:jsdbfx
用户:jsdb
表空间:js_stat
2 迁移前的准备工作
2.1 环境具备的基本条件
- 源数据库和目的数据库操作系统正常运行;
- 源数据库和目的数据库数据库运行正常;
- 源数据库和目的数据库网络可以相互通讯;
- 源数据库和目的数据库数据库需要有具有DBA权限的用户/密码。
2.2 查看源数据库的数据库情况
2.2.1 查看源数据库jsdb用户的默认表空间
$ sqlplus as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 30 17:32:14 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
SQL> select username,default_tablespace from dba_users where username='JSDB';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
JSDB JS_STAT
SQL>
2.2.2 查看源数据库jsdb用户默认表空间大小
SQL> set pagesize 100;
SQL> set linesize 200;
SQL> select df.tablespace_name "表空间名 ",totalspace "总空间M",freespace " 剩余空间M",round((1 -freespace/totalspace)*100, 2) "使用率 %",(totalspace-freespace)/1024 " 已用空间G"
2 from
3 (select tablespace_name,round( sum(bytes)/1024 1024) totalspace
4 from dba_data_files
5 group by tablespace_name) df,
6 (select tablespace_name,round( sum(bytes)/1024 1024) freespace
7 from dba_free_space
8 group by tablespace_name) fs
9 where df.tablespace_name=fs.tablespace_name ORDER BY "使用率 %" DESC;
表空间名 总空间M 剩余空间M 使用率 % 已用空间G
------------------------------ ---------- -------------- ---------- --------------
JS_PARAM 30720 2769 90.99 27.2958984
UNDOTBS2 8192 2316 71.73 5.73828125
UNDOTBS1 8192 2480 69.73 5.578125
JS_STAT 1228800 484550 60.57 726.806641
SYSAUX 4096 3068 25.1 1.00390625
SYSTEM 4096 3643 11.06 .442382813
JS_DATA 307200 306276 .3 .90234375
JS_INDEX 153600 153594 0 .005859375
9 rows selected.
SQL>
由此可得jsdb用户的默认表空间是1228800MB左右,使用了726GB。
2.3 目的数据库的准备工作
查看目的数据库上是否有jsdb用户以及相关表空间,若没有则应该先创建。
2.3.1 查看目的数据库jsdb用户的默认表空间
$ sqlplus as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 3 15:35:44 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL>
SQL> select username,default_tablespace from dba_users where username='JSDB';
USERNAME DEFAULT_TABLESPACE
-------------------- ------------------------------
JSDB JS_STAT
SQL>
2.3.2 查看jsdb用户的默认表空间大小
SQL> set pagesize 100;
SQL> set linesize 200;
SQL> select df.tablespace_name "表空间名 ",totalspace "总空间M",freespace " 剩余空间M",round((1 -freespace/totalspace)*100, 2) "使用率 %",(totalspace-freespace)/1024 " 已用空间G"
2 from
3 (select tablespace_name,round( sum(bytes)/1024 1024) totalspace
4 from dba_data_files
5 group by tablespace_name) df,
6 (select tablespace_name,round( sum(bytes)/1024 1024) freespace
7 from dba_free_space
8 group by tablespace_name) fs
9 where df.tablespace_name=fs.tablespace_name ORDER BY "使用率 %" DESC;
表空间名 总空间M 剩余空间M 使用率 % 已用空间G
------------------------------ ---------- -------------- ---------- --------------
SYSAUX 4096 2718 33.64 1.34570313
SYSTEM 4096 3177 22.44 .897460938
UNDOTBS1 8192 8170 .27 .021484375
UNDOTBS2 8192 8172 .24 .01953125
INTERCONN_INDEX 92160 92157 0 .002929688
JS_STAT 1075200 1075165 0 .034179688
JS_INDEX 92160 92157 0 .002929688
INTERCONN_DATA 92160 92157 0 .002929688
JS_PARAM 61440 61438 0 .001953125
JS_DATA 92160 92157 0 .002929688
10 rows selected.
SQL>
3 数据迁移
本次数据迁移是从11.2.0.4版本到19.12.0.0版本,主要使用expdp/impdp+dblink方法(在此不赘述,详细命令使用方式请查阅oracle手册);还可以使用其它工具导出各种对象后,在新数据库中重新生成,也可以使用copy的方法导入较小的表和sqlload导入较大的表。
3.1 在源数据库导出表结构、约束、序列号、存储过程等元数据
在源数据库服务器的oracle用户下使用expdp工具导出元数据;
$ expdp system/123456 directory=dmp_bak schemas=jsdb dumpfile=jsdb_jsdbfx_metadata_20210901.dmp content=metadata_only logfile=expdp_jsdb_jsdbfx_metadata_20210901.log
Export: Release 11.2.0.4.0 - Production on Wed Sep 1 19:38:35 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dmp_bak schemas=jsdb dumpfile=jsdb_jsdbfx_met
adata_20210830.dmp content=metadata_only logfile=expdp_jsdb_jsdbfx_metadata_20210830.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
bakdata/dmp_bak/jsdb_jsdbfx_metadata_202100901.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Sep 1 19:40:49 2021 elapsed 0 00:02:12
$
3.2 目的数据库导入表结构、约束、序列号、存储过程等元数据
将在源数据库服务器上导出的dmp文件传到目的数据库服务器的dmp_bak目录下,使用impdp工具导入元数据;
$ impdp system/123456 remap_schema=jsdb:jsdb dumpfile=jsdb_jsdbfx_metadata_20210901.dmp directory=dmp_bak logfile=impdp_jsdb_jsdbfx_metadata_20210901.log
Import: Release 19.0.0.0.0 - Production on Fri Sep 3 16:26:54 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** remap_schema=jsdb:jsdb dumpfile=jsdb_jsdbfx_metadata_20210901.dmp directory=dmp_bak logfile=impdp_jsdb_jsdbfx_metadata_20210901.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JSDB" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39082: Object type FUNCTION:"JSDB"."F_EDITFIELDVALUE_NBRAREA_1" created with compilation warnings
ORA-39082: Object type FUNCTION:"JSDB"."F_EDITFIELDVALUE" created with compilation warnings
ORA-39082: Object type FUNCTION:"JSDB"."F_EDITFIELDVALUE_NBRAREA_2" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_CS_GET_DAILTEST" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_CS_GET_ERRTICKET" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_SI_SS7_CAL_ST_ANN" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_SI_SS7_CAL_ST_ANNOY_N" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_SI_SS7_CAL_ST_RTNNBR" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_ST_COMM_CHEAT" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_ST_COMM_LCLSRV" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_ST_COMM_NETSRV" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_ST_COMM_SMSSRV_TEMP" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_ST_COMM_MMSSRV_TEMP" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_ST_STTL_BALANCE_DC" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_ST_STTL_BALANCE_TEMP" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_GW_CHECKING" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_GW_INTEGRATE" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_GW_MONI_WARNING" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_GW_OPER_ANAL" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_GW_STTL_BALANCE" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_GW_STTL_COMM" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_GW_TRAFFIC" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_GW_USER_ANAL" created with compilation warnings
ORA-39082: Object type PROCEDURE:"JSDB"."P_SEND_MAIL" created with compilation warnings
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 25 error(s) at Fri Sep 3 17:01:48 2021 elapsed 0 00:34:42
$
3.3 通过dblink导入数据
3.3.1 在目的数据库创建到源数据库的dblink
- 给jsdb用户授权能
[oracle@jsfxdb01 ~]$ sqlplus as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 3 16:38:59 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL>
SQL> grant create database link to jsdb;
Grant succeeded.
SQL>
SQL> grant create session to jsdb;
Grant succeeded.
SQL>
- 在jsdb用户下创建dblink
SQL> connect jsdb/dba
Connected.
SQL>
SQL> create database link jsdbfxold connect to jsdb identified by "123456" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.218.233.12)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME =jsdbfx)))';
Database link created.
SQL>
- 测试dblink是否正常
在源数据库上找一个jsdb用户下表通过dblink查询;
SQL> select count(*) from ST_USER_COUNT_D_201201@jsdbfxold;
COUNT(*)
----------
456558
SQL>
3.3.2 在目的数据库利用dblink生成导入数据脚本
SQL> set echo off head off pages 0 linesize 3000 trims on
SQL> spool dblink_insert_jsdbfx.sh
SQL> select 'insert * +append nologing */ into '||table_name||' select * from '||table_name||'@jsdbfxold;' from dba_tables@jsdbfxold where owner='JSDB';
SQL> spool off
3.3.3 编辑生成导入数据脚本
编辑脚本,去掉无效部分,加上头尾部分;举例脚本:
#!/usr/bin/bash
sqlplus jsdb/dba <<EOF
set timing on
set autocommit on
insert * +append nologing */ into ACC_ITEM_DEF select * from ACC_ITEM_DEF@jsdbfxold;
insert * +append nologing */ into AT_COMM_SHTNBR select * from AT_COMM_SHTNBR@jsdbfxold;
insert * +append nologing */ into AT_USER_RANK select * from AT_USER_RANK@jsdbfxold;
insert * +append nologing */ into A_LT_20170727 select * from A_LT_20170727@jsdbfxold;
insert * +append nologing */ into A_LT_20170831 select * from A_LT_20170831@jsdbfxold;
insert * +append nologing */ into A_LT_20171116 select * from A_LT_20171116@jsdbfxold;
........
EOF
exit
3.3.4 执行dblink脚本导入数据库
在运行该脚本前,必须把相关表内容清空,否则可能会产生重复记录;同时注意目的表空间的容量是否足够。
$ sh dblink_insert_jsdbfx.sh
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 3 17:41:24 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Fri Sep 03 2021 17:11:18 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> SQL> SQL> SQL> SQL>
330 rows created.
Commit complete.
Elapsed: 00:00:00.32
SQL>
9842 rows created.
Commit complete.
Elapsed: 00:00:00.15
SQL>
76285 rows created.
.......
SQL> SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0
3.3.5 重新编译失效对象
所有导入工作完成后,用sys用户运行下述脚本恢复所有失效对象:
$ cd $ORACLE_HOME/rdbms/admin
$ ls utlrp.sql
utlrp.sql
$
$ sqlplus as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 3 17:53:28 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL>
SQL> @utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-09-03 17:53:37
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2021-09-03 17:53:40
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
3.4 导入后数据检查
3.4.1 表数据记录比对
数据完全导完后,检查源数据库和目的数据库表里面的记录是否一致;分别在源数据库和目的数据库中分别执行查询进行比对,以其中一个数据库的查询为例:
$ sqlplus as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 3 20:57:57 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL>
SQL> select table_name,num_rows from dba_all_tables where owner='JSDB';
.............
TABLE_NAME NUM_ROWS
------------------------------ ----------
T475_20210826 10260
T482_20210826 20
T477_20210826 2005
TMMS_20210826 102
2919 rows selected.
SQL>
3.4.2 存储过程核查
检查存储过程是否有效,用PL/SQL Developer、Oracle DBA studio或toad工具检查,若无效用这三个工具任何一个重新编译即可。以PL/SQL Developer为例: