About A Migration Using DataPump

最新推荐文章于 2026-06-22 09:39:41 发布

转载最新推荐文章于 2026-06-22 09:39:41 发布 · 208 阅读

标签

#数据库 #shell #网络

本文详细介绍了一个从源Oracle数据库到目标Oracle数据库的迁移流程，包括前期准备、执行迁移、对比验证等关键步骤，并提供了实用的shell脚本和参数文件示例。

Project Target:

Source
Server: local-ip: 10.10.10.11,10.10.10.12
virt-ip: 10.10.10.13,10.10.10.14
scan-ip: 10.10.10.15
DB: db-rac01, db-rac02 Running Oracle GI, DB 11.2.0.4 on AIX 6.1L
Schemas: 'FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC'
Target
Sever: local-ip: 10.10.10.21,10.10.10.22
virt-ip: 10.10.10.23,10.10.10.24
scan-ip: 10.10.10.25
DB: db-rac03, db-rac04 Running Oracle GI, DB 11.2.0.4 on AIX 6.1L
Schemas: 'FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC'

Planning:
Of course, you should make a plan for the migration.
You should take care of points below:

a. DB directory, user with enough privillage to export the schemas
If not, create it or grant dba role to it!
b. Database NLS_LANG should be same
c. The Edition and help of DataPump(expdb, impdp)
d. Check the data size and types of the schemas
Is there any static data could be migration when the applications are using DB.
If yes, migrat it first.
Is there enough time and network bandwidth to move the dump file to target server.
If not, maybe you need to think about manually building the Index on the target DB instead of export it.
Is there any objects are "INVALID" in the source DB.
If yes, make note.
e. Try to export the data and import the data before you really need to do it.
Now, you might face a lot of problem, resolve them.
This time, you will be able to estimate the time it need to finish the Migration.
In the course of this test, you could find some option could be optimized, 4 example "PARALLEL".
f. Prepare a database link from soruce DB to target DB.
You had better to create the db link in source DB because of the possibility of the listener of source DB \
would be diabled in the process of migration.
Compare the objects in source DB and target DB after migration.
g. Something before finishing.

Executing:
Create DB user, directory for migration:

SQL> create user jackson identified by "jackson_mig" account unlock; -- execute in both
SQL> grant dba to jackson; -- execute in both
SQL> grant create session to jackson; -- execute in both
SQL> create directory EXPDUMP as '/dmpfiles'; -- execute in Source DB
SQL> create directory IMPDUMP as '/dmpfiles'; -- execute in Target DB

Share the dumpfiles on source server to target server (with NFS):
@ Mount the fs on target server with root

# mount -o rw,bg,hard,intr,proto=tcp,vers=3,rsize=65536,wsize=65536,timeo=600 10.10.10.11:/dmpfiles /dmpfiles;

@ If you do not know how to use NFS, just ask your SA or ask TOM.
@ You can alse use scp to cp the dumpfiles to target server, but it take more times.

$ scp /dmpfiles/expdp_*.dmp oracle@10.10.10.21:/dmpfiles/

@ Or just use tar (you can find on any OS, Oh except Windows)

$ cd /dmpfiles; tar -cf - expdp_*.dmp | ssh 10.10.10.21 "tar -xv -C /dmpfiles"; -- for Unix and linux
tar -cO expdp_*.dmp | ssh 10.10.10.21 "tar -xv -C /dmpfiles"; -- for Linux
tar -cf - expdp_*.dmp | rsh 10.10.10.21 "tar -xv -C /dmpfiles"; -- for rsh

Check NLS_LANG in both DB:

SQL> select (select upper(value) from nls_database_parameters where parameter='NLS_LANGUAGE')
|| '_' ||
(select upper(value) from nls_database_parameters where parameter='NLS_TERRITORY')
|| '.' ||
(select upper(value) from nls_database_parameters where parameter='NLS_CHARACTERSET') NLS_LANG
from dual;
NLS_LANG
-----------------------
AMERICAN_AMERICA.ZHS16GBK

Manuals of DataPump:

$ expdp help=yes;
$ impdp help=yes;

    -- I recommend you to read it carefully before you execute it.

Collect data information:
    @ Talk with application developer about the static data and down time.
    @ Talk with network administrator about the network bandwidth.
    @ collect information of schemas and data.
         Source Tablespaces Size:

SQL> select tablespace_name, sum(bytes)/1024/1024/1024 as "TBS_Size (GB)"
from dba_data_files group by tablespace_name order by 2 desc;

Source Segments Size: -- You can use extents for accurate size, but pay more resource to get it.

SQL> select owner, sum(bytes)/1024/1024 as "DataSize (MB)"
from dba_segments where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
group by owner order by 2 desc;

Source Schemas account information:

SQL> select username, account_status, default_tablespace, temporary_tablespace from dba_users
where username in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');

Source Objects status:

SQL> select owner, object_name, object_type, status
from dba_objects where owner in
('FJZKXT','FJZP','LOOTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
and status = 'INVALID';

Count Objects of Source DB: -- You should do it again in Target DB after data being imported.

SQL> select count(*) from dba_objects
where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');

Create Database Link for compare objects:

SQL> create database link LK_TARGET connect to jackson identified by "jackson_mig"
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TARGET_DB)
)
)';
SQL> select instance_name from sys.v$instance@LK_TARGET;

@ Prepare tablespace in Target DB:
Create the tablespaces before migration in Target DB:

SQL> create tablespace TBS_NAME datafile '+DATA' size 30720M( according to the real size of Source DB);

Create schemas in Target DB (if you want): -- get ddl to create user in Source DB.

SQL> set long 1000;
SQL> set line 500;
SQL> set pagesize 99;
SQL> select dbms_metadata.get_ddl('USER',U.username)
from dba_users U where U.username
in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');
SQL> select * from dba_sys_privs
where grantee in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');
SQL> select * from dba_role_privs
where grantee in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');

Create test shell script for export data and import data:
    @ Why shell script not execute expdp/impdp directly?
          1. Execute directly you will show others the password of jackson(dba) in command history.
          2. You can easily to edit the command in the script file.
          3. It will be easy to executed with "nohup" command for unstable network or time limited connection.
    @ Create the shell script and parfile for export data.

$ cat > expdp_data.sh << EOF
#!/bin/sh
# file name: expdp_data.sh
# execute directory: /dumpfiles
# you'd better referrence to the user oracle's profile on Source server
ORACLE_BASE=/app/oracle
ORACLE_HOME=/app/oracle/product/11.2.0/db_1
ORACLE_SID=source2 # sid of Source DB
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
PATH=$ORACLE_HOME/bin:/usr/sbin:/usr/bin:/etc:/usr/lbin:/usr/bin/X11:/usr/local/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG LD_LIBRARY_PATH PATH
echo "`date`"
# when it is large than 2, the third file can not be created on the NFS.
expdp jackson/oracle \
SCHEMAS=('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC') \
PARALLEL=2 \
directory=EXPDUMP \
dumpfile=expdp_data_%U.dmp \
logfile=expdp_data_.log \
parfile=/dmpfiles/exp_data.par
echo "`date`"
EOF
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- End Of File expdp_data.sh ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
$ cat > exp_data.par << EOF
# attach=expdp_data_20160521 # if you set this, it will be easy to interact with the expdp process. Or it will be
# created automaticly by system. You can find it in the logfile or on the screen.
# schemas=('USER1',...,'USERn') # if you want to export more than one schemas.
# parallel=2 # define threads to export the same time.
# dumpfile=expdp_data #_%U.dmp %U will be instead of 01..99 automaticly when files created
# directory=Dir1,Dir2,Dir3 # if your files are more than 99, you need to set multi values here. Each dir will
# appear files 01..99.
# content=all # "all" is default value, "metadata_only" will not export data, "data_only" will
# only export data.
# compression # don't use it! Trust me! That will be too slow.
# full # Y, if you want to export full database.
# exclude=schema:\"=\'HR\'\" # if you don't want the schema "HR", '\' is very important
# reuse_dumpfiles=Y # if the dumpfile is exist, it will be overwrited.
# parfile=exp_data.par # this file is parfile, if the statment is too long, the parfile will be useful.
EOF
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~ End Of File exp_data.par ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

@ Run the shell script.

$ nohup sh exp_data.sh >> nohup_exp_data.log &
$ tail -f nohup_exp_data.log -- follow the log of exporting.
$ expdp jackson/oracle attach=jackson.expdp_data_20160521 -- Now some one may see your password in command history.
$ expdp attach=jackson.expdp_data_20160521
username:
password: -- It would be safe like this.
Export> status -- it'll show you the status of the job, you can try other command by referrencing "expdp help=yes".
-- you can also interact with impdp like this.

@ Create the shell script and parfile for import data.

$ cat > impdp_data.sh << EOF
#!/bin/sh
# file name: impdp_data.sh
# execute directory: /dumpfiles
# you'd better referrence to the user oracle's profile on Target server
ORACLE_BASE=/app/oracle
ORACLE_HOME=/app/oracle/product/11.2.0/db_1
ORACLE_SID=target2 -- sid of Target DB
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
PATH=$ORACLE_HOME/bin:/usr/sbin:/usr/bin:/etc:/usr/lbin:/usr/bin/X11:/usr/local/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG LD_LIBRARY_PATH PATH
echo "`date`"
impdp jackson/oracle \
PARALLEL=2 \
directory=IMPDUMP \
dumpfile=impdp_data_%U.dmp \
logfile=impdp_data_.log \
parfile=/dmpfiles/imp_data.par
echo "`date`"
EOF
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- End Of File impdp_data.sh ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
$ cat > imp_data.par << EOF
# attach=impdp_data_20160521 # if you set this, it will be easy to interact with the expdp process. Or it will
# be created automaticly. You can find it in the logfile or on the screen.
# schemas=('USER1',...,'USERn') # if you don't want to import all schemas exported before.
# dumpfile=expdp_data_%U.dmp # %U will be instead of 01..99 automaticly when files created
# directory=Dir1,Dir2,Dir3 # according to the PATH in Source DB or the PATH you copy the dumpfiles to.
# content=all # "all" is default value, "metadata_only" will not export data, "data_only" will
# only export data.
# remap_tablespace=tbs1:tbs2,tbs3:tbs4 # if you want to change the tablespace of the objects in Target DB.
# remap_schema # change the schemas of data in Target DB.
# remap_table # change table name, I recommend you don't make it to complicated.
# parfile=exp_data.par # this file is parfile, if the statment is too long, the parfile will be useful.
EOF
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~ End Of File imp_data.par ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

@ Run the shell script.

$ nohup sh imp_data.sh >> nohup_imp_data.log &
$ tail -f nohup_imp_data.log -- follow the log of importing.

Comparing:
Compare the logs of expdp and impdp:
@ Make sure the shell script was executed succesfully.

$ tail -n 5 /dumpfils/impdp_data.log

@ Make sure that all tables was imported.

$ grep rows /dumpfiles/expdp_data.log | wc -l
$ grep rows /dumpfiles/impdp_data.log | wc -l

Compare the data of the two DB:
@ Compare objects

SQL> select count(*) from dba_objects
where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');

@ If the results is different, run below in Source DB( Some Index name and LOB name will change ).

SQL> select owner,object_name,object_type
from dba_objects where owner in
('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
and object_type not in ('INDEX','LOB')
minus
select owner,object_name,object_type
from dba_objects@lk_target where owner in
('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
and object_type not in ('INDEX','LOB');

@ Compare "INDEX" and "LOB" of which schemas are different, Source DB.

SQL> select owner, count(*) from dba_objects
where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
and object_type in ('INDEX','LOB') group by owner;
SQL> select owner, count(*) from dba_objects@lk_target
where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
and object_type in ('INDEX','LOB') group by owner;

@ Find out the difference of "INDEX" and "LOB" for each schema, Source DB.

SQL> select table_name, count(*) from dba_indexes where owner = 'SCHEMA1' group by table_name;
SQL> select table_name, count(*) from dba_indexes@lk_target where owner = 'SCHEMA1' group by table_name;
SQL> select table_name, count(*) from dba_lobs where owner = 'SCHEMA1' group by table_name;
SQL> select table_name, count(*) from dba_lobs@lk_target where owner = 'SCHEMA1' group by table_name;