如何基于oracle 11.2.0.4数据库版本在数据库间迁移非表特殊对象类比如序列及存储过程

测试结论
1,对于表空间数据库用户存储特殊对象,即非表的对象类型如:序列及触发器和存储过程

2,在数据库间迁移上述特殊对象类型的expdp以及impdp的语句如下:

—源端数据库
expdp /’sys/system as sysdba/’ directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only   schemas=user_obj  include=sequence,procedure

—传输源端数据库的DUMPFILE至目标端数据库
scp /oracle/admin/mygirl/dpdump/exp_metadata.dmp oracle@10.0.0.39:/oracle/admin/esbdb/dpdump/

—目标端数据库
impdp /’sys/system as sysdba/’ directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only   schemas=user_obj  include=sequence,procedure

测试明细
1,源端数据库版本

SQL> select * from v$version where rownum=1;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

2,源端数据库用户
SQL> select name from v$tablespace;

NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TBS_FATHER
TBS_MOTHER
TBS_SUN
TBS_ZXY

9 rows selected.

SQL> select name from v$datafile;

NAME
——————————————————————————–
/oracle/mygirl/system01.dbf
/oracle/mygirl/sysaux01.dbf
/oracle/mygirl/undotbs01.dbf
/oracle/mygirl/users01.dbf
/oracle/admin/mygirl/dpdump/tbs_father01.dbf
/oracle/admin/mygirl/dpdump/tbs_mother01.dbf
/oracle/admin/mygirl/dpdump/tbs_sun01.dbf
/oracle/mygirl/tbs_zxy01.dbf

8 rows selected.

SQL> create tablespace tbs_obj datafile ‘/oracle/mygirl/tbs_obj01.dbf’ size 10m;

Tablespace created.

SQL> create user user_obj identified by system default tablespace tbs_obj account unlock;

User created.

SQL> grant resource,connect,dba to user_obj;

Grant succeeded.

3,创建源端数据库用户的序列及存储过程
SQL> conn user_obj/system
Connected.
SQL> create sequence sequence_zxy;

Sequence created.

SQL> create procedure proc_nb
  2  as
  3  begin 
  4  dbms_output.put_line(1);
  5  end;
  6  /

Procedure created.

SQL> select object_type,object_name,status from user_objects

OBJECT_TYPE         OBJECT_NAME                    STATUS
——————- —————————— ——-
PROCEDURE           PROC_NB                        VALID
SEQUENCE            SEQUENCE_ZXY                   VALID

4,导出源数据库数据库用户的序列及存储过程
SQL> set linesize 300
SQL> col directory_path for a50
SQL> r
  1* select directory_name,directory_path from dba_directories

DIRECTORY_NAME                 DIRECTORY_PATH
—————————— ————————————————–
XMLDIR                         /oracle/product/11.2.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR          /oracle/product/11.2.0/db_1/ccr/hosts/mygirl/state
DATA_PUMP_DIR                  /oracle/admin/mygirl/dpdump/
ORACLE_OCM_CONFIG_DIR2         /oracle/product/11.2.0/db_1/ccr/state

[oracle@mygirl ~]$ expdp /’sys/system as sysdba/’ directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only   schemas=user_obj  include=sequence,procedure

Export: Release 11.2.0.4.0 – Production on Fri Jun 9 23:16:35 2017

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, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″:  “sys/******** AS SYSDBA” directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only schemas=user_obj include=sequence,procedure 
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /oracle/admin/mygirl/dpdump/exp_metadata.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Fri Jun 9 23:16:41 2017 elapsed 0 00:00:04

[oracle@mygirl ~]$ 

5,目标数据库创建数据库用户
SQL> select * from v$version where rownum=1;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

SQL> select name from v$datafile;

NAME
——————————————————————————–
/oracle/esbdb/system01.dbf
/oracle/esbdb/sysaux01.dbf
/oracle/esbdb/undotbs01.dbf
/oracle/esbdb/users01.dbf

SQL> create tablespace tbs_obj datafile ‘/oracle/esbdb/tbs_obj01.dbf’ size 10m;

Tablespace created.

SQL> create user user_obj identified by system default tablespace tbs_obj account unlock;

User created.

SQL> grant resource,connect,dba to user_obj;

Grant succeeded.

6,复制源端数据库DUMP文件到目标端数据库对应目录
SQL> set linesize 300
SQL> col directory_path for a50
SQL> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME                 DIRECTORY_PATH
—————————— ————————————————–
XMLDIR                         /oracle/product/11.2.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR          /oracle/product/11.2.0/db_1/ccr/hosts/suse11/state
DATA_PUMP_DIR                  /oracle/admin/esbdb/dpdump/
ORACLE_OCM_CONFIG_DIR2         /oracle/product/11.2.0/db_1/ccr/state

SQL> 

[oracle@mygirl ~]$ scp /oracle/admin/mygirl/dpdump/exp_metadata.dmp oracle@10.0.0.39:/oracle/admin/esbdb/dpdump/
Password: 
exp_metadata.dmp

SQL> host ls -l /oracle/admin/esbdb/dpdump/exp_metadata.dmp
-rw-r—– 1 oracle oinstall 159744 2017-06-09 23:25 /oracle/admin/esbdb/dpdump/exp_metadata.dmp

7,在目标端数据库导入源端导出的元数据DMP FILE

oracle@suse11:~> impdp /’sys/system as sysdba/’ directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only   schemas=user_obj  include=sequence,procedure

Import: Release 11.2.0.4.0 – Production on Fri Jun 9 23:27:41 2017

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, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_SCHEMA_01″:  “sys/******** AS SYSDBA” directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only schemas=user_obj include=sequence,procedure 
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Job “SYS”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Fri Jun 9 23:27:49 2017 elapsed 0 00:00:05

oracle@suse11:~> 

8,在目标端数据库验证数据导入的一致性及完整性
SQL> set linesize 300
SQL> col object_name for a50
SQL> r
  1* select object_type,object_name,status from user_objects

OBJECT_TYPE         OBJECT_NAME                                        STATUS
——————- ————————————————– ——-
PROCEDURE           PROC_NB                                            VALID
SEQUENCE            SEQUENCE_ZXY                                       VALID

PS:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处:Harries Blog™ » 如何基于oracle 11.2.0.4数据库版本在数据库间迁移非表特殊对象类比如序列及存储过程

赞 (0)

分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址