本方案通过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 环境具备的基本条件

  1. 源数据库和目的数据库操作系统正常运行;
  2. 源数据库和目的数据库数据库运行正常;
  3. 源数据库和目的数据库网络可以相互通讯;
  4. 源数据库和目的数据库数据库需要有具有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

  1. 给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>
  1. 在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>
  1. 测试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为例:

来源;https://www.modb.pro/db/107685

最后修改:2021 年 10 月 02 日
如果觉得我的文章对你有用,请随意赞赏