如何基於oracle 11.2.0.4資料庫版本在資料庫間遷移非表特殊物件類比如序列及儲存過程

wisdomone1發表於2017-06-09
測試結論
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




來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-2140554/,如需轉載,請註明出處,否則將追究法律責任。

相關文章