17 Şubat 2013 Pazar

Using DataPump on Oracle DataBase


1.AMAÇ

 Bu doküman veritabındaki herhangi bir schemanın   taşınması sırasında yapılan çalışmanın detaylarını içermektedir.

2.Kaynak Veritabanında Yapılan Çalışmalar ve Export Alma:

  • Export alınacak Ekoop  şemasının veri büyüklüğünün kontrolü :

[root@localhost ~]# su – oracle
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Nov 16 16:21:00 2012

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>connect ****/*******;
SQL>show user;
SQL>select sum(bytes)/1024/1024 as mb from user_segments;

MB
-------
577.0625

  • Export alınacak kullanıcı  şemasının   table ve tablespacelerini kontrolü:

sqlplus / as sysdba;
SQL>connect *****/*******;
SQL>show user;
SQL>select table_name,tablespace_name from user_tables;

table_name       tablespace_name
---------------------------------
PRL_GK_GOREVLENDIRME TBS_KOOP
PRL_GK_TEMSILCI_MAZERET        USERS
PRL_REF_DOSYATIPLERI     TBS_KOOP
PRL_REF_TEMSILCITIPI       TBS_KOOP
PRL_KOOP_ORTAKLAR_ODENTI    TBS_KOOP
PRL_REF_ORTAKODENTITIPI          TBS_KOOP
PRL_WEB_BASVURU TBS_KOOP
PRL_SYS_WEBSESSIONS      TBS_KOOP
PRL_REP_REPORTSHOW      TBS_KOOP
PRL_REP_REPORTCONFIG  TBS_KOOP
PRL_REF_DOSYALAR            TBS_KOOP
PRL_MSG_ORTAKLARDUYURU       TBS_KOOP
PRL_LOG_FILESHOW           TBS_KOOP
PRL_REFERANSLAR  TBS_KOOP
PRL_SYS_AUTHCITY_OLD    TBS_KOOP
PRL_SYS_AUTHCITY TBS_KOOP
PRL_SYS_AUTHLIST_OLD    TBS_KOOP
PRL_SYS_AUTHLIST  TBS_KOOP
PRL_SYS_AUTHGROUPS      TBS_KOOP
PRL_REF_TEMSILCILER       TBS_KOOP
PRL_REF_TEMSILCI  TBS_KOOP
PRL_REF_DENETIMCI          TBS_KOOP
PRL_GENELKURUL_TEMSILCI        TBS_KOOP
PRL_REF_ULKELER  TBS_KOOP
PRL_REF_ALINANSATILANTUR        TBS_KOOP
PRL_SYS_USERS        TBS_KOOP
PRL_SYS_SESSIONS  TBS_KOOP
PRL_SYS_AUTHCODES         TBS_KOOP
PRL_REF_IL   TBS_KOOP
TMP_COLOOK           TBS_KOOP
PRL_REF_SIKAYETEDILEN   TBS_KOOP
PRL_REF_SIKAYETYERI        TBS_KOOP
PRL_DENETIM_DENETIMCI            TBS_KOOP
PRL_KOOP_DENETIM          TBS_KOOP
PRL_KOOP_SUBESI  TBS_KOOP
PRL_REF_MESLEKIBILGI     TBS_KOOP
PRL_REF_DOSYANOVER      TBS_KOOP
PRL_TEST_USERS     TBS_KOOP
PRL_REF_MAHKEMELER     TBS_KOOP
PRL_REF_SAVCILIK_KARAR            TBS_KOOP
PRL_GENELKURUL_SAVCILIK        TBS_KOOP
PRL_REF_EVRAK_GIZLILIK TBS_KOOP
PRL_REF_EVRAK_KONUSU TBS_KOOP
PRL_REF_EVRAK_TIPI          TBS_KOOP
PRL_REF_EVRAK_OZELDURUM     TBS_KOOP
PRL_KOOP_EVRAK   TBS_KOOP
PRL_REF_TASFIYE_TERKIN TBS_KOOP
PRL_KOOP_TASFIYE_TERKIN         TBS_KOOP
PRL_KOOP_MADDEDEGISIKLIK    TBS_KOOP
PRL_KOOP_MALIDURUM    TBS_KOOP
PRL_REF_EDINMEYONTEMLERI     TBS_KOOP
PRL_REF_BIRIMLER TBS_KOOP
PRL_REF_MENKULTURU     TBS_KOOP
PRL_KOOP_GAYRIMENKUL TBS_KOOP
PRL_REF_CAGRIYAPAN        TBS_KOOP
PRL_REF_CAGRITIPI            TBS_KOOP
PRL_GENELKURUL_CAGRI  TBS_KOOP
PRL_REF_GENELKURULTIPI           TBS_KOOP
PRL_KOOP_GENELKURUL  TBS_KOOP
PRL_REF_ORTAKLARDURUM          TBS_KOOP
PRL_KOOP_ORTAKLAR        TBS_KOOP
PRL_YONETIM_MALBEYAN  TBS_KOOP
PRL_REF_YONKURULDURUM        TBS_KOOP
PRL_REF_YONKURULGOREV          TBS_KOOP
PRL_KOOP_YONETIMKURULUUYE            TBS_KOOP
PRL_OLD_KOOPERATIF       TBS_KOOP
PRL_REF_VERGIDAIRESI     TBS_KOOP
PRL_REF_ILCELER    TBS_KOOP
PRL_REF_KOOPUSTKURULUSTIPI TBS_KOOP
PRL_REF_KOOPTURU          TBS_KOOP
PRL_REF_INSAATYAPTIPI    TBS_KOOP
PRL_REF_HUKUKIDURUM  TBS_KOOP
PRL_KOOPERATIF    TBS_KOOP
DENEME        TBS_KOOP

PRL_GK_TEMSILCI_MAZERET tablosu haricinden diğer tüm tablolar TBS_KOOP tablespace’sin altında üretilmiştir. User tablespace’i default olarak oluşturuldugundan sadece TBS_KOOP tablespace’ni hedef veritabanında oluşturulacaktır.


  • KOOP kullanıcısına ait tablespace’nin DDL komutu:

sqlplus / as sysdba;
SQL>connect /**********;
SQL>show user;

SQL> select trim(DBMS_METADATA.GET_DDL('TYPES',a.OBJECT_NAME, a.OWNER)) "TYPES_DDL" from dba_objects a where a.OBJECT_TYPE ='TYPES' and a.OWNER in ('KOOP') order by a.OWNER, a.OBJECT_NAME;

CREATE TABLESPACE "TBS_KOOP" DATAFILE
  '/oracle/app/oradata/ORCL/tbs_koop01.dbf' SIZE 2097152000
  AUTOEXTEND ON NEXT 52428800 MAXSIZE 4000M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL;

  • Exportu alınacak  veritabanına export alınacak pathi tanımlama ve KOOP kullanıcısında read,write yetkisi verme.

sqlplus sys/ as sydba
create or replace directory export_e_kop as '/home/oracle/Import2';\\linux için ekstretan path verilecek
grant read,write on directory export_e_kop  to KOOP;
quit;


  • Oracle Datapump özelliğini kullanarak export alma:

[oracle@localhost bin]$ cd  /home/oracle/product/11.2.0/orcl/bin/

expdp *****/******* include=table,view,package,index,trigger directory=export_e_kop dumpfile=export_e_kop.dmp logfile=export_e_kop.log ;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "KOOP"."SYS_EXPORT_TABLE_01":  *****/******** table,view,package,index,trigg parallel=4 directory=export_e_kop dumpfile=export_e_kop.dmp logfile=export_e_kop.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method:
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "KOOP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Dump file set for KOOP.SYS_EXPORT_TABLE_01 is:





Job "KOOP"."SYS_EXPORT_TABLE_01" successfully completed at 04:37:53

Başarılı bir şekilde export alındı.

  • Export işlemi gercekleştirildikten sonra Ekoop user’da oluşturulan Objeler kontrol edildi.

Type            Count(*)
-------------------------------------
INDEX           101
TABLE           74
VIEW              92
TRIGGER      72
FUNCTION      7
TYPE               37
SEQUENCE   69



    3 . Hedef Veritabanında Yapılan Çalışmalar ve Import Yapma:

  • Hedef veritabanında KOOP kullanıcısı oluşturulduktan sonra session açma, tablo oluşturma ve limitsiz tablespace kullanma yetkileri verildi.

            [root@localhost ~]# su – oracle
           [oracle@localhost ~]$ sqlplus sys / as sysdba
           SQL>
           CREATE USER KOOP IDENTIFIED BY *******;
          GRANT CREATE SESSION TO KOOP;
         GRANT UNLIMITED TABLESPACE TO KOOP;
        GRANT CREATE TABLE TO KOOP;
    

  • Import Tablespace oluşturulmasi:

[root@localhost ~]# su – oracle
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> CREATE TABLESPACE "TBS_KOOP" DATAFILE
  '/oracle/app/oradata/ORCL/tbs_koop01.dbf' SIZE 2097152000
  AUTOEXTEND ON NEXT 52428800 MAXSIZE 4000M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL;

Tablespace created...

  • Import Directory oluşturma:

[root@localhost ~]# su – oracle
[oracle@localhost ~]$ sqlplus “EKOOP/********@xxxxRaC”
create or replace directory export_e_kop as '/backup/koop/export';\\
grant read,write on directory export_e_kop  to KOOP;
quit;

Import Directory oluşturduktan sonra export aldımız dump file ve log file dosyasini hedef veritabanına  adresine ssh secure file transfer kullanarak veriyi import directory taşınmıştır. Ayrıca chmod -777 yetkisi export dump ve log file verilmiştir.

  • Import işleminin yapılmasi:

Import: Release 11.2.0.3.0 - Production on Wed Nov 21 17:52:38 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "KOOP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "KOOP"."SYS_IMPORT_FULL_01":  *****/******** include=table,view directory=export_e_kop2 dumpfile=export_e_kop.dmp logfile=export_e_kop.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "KOOP"."PRL_KOOP_YONETIMKURULUUYE"          120.4 MB 1079716 rows
. . imported "KOOP"."PRL_KOOP_ORTAKLAR"                  42.88 MB  248804 rows
. . imported "KOOP"."PRL_SYS_SESSIONS"                   25.82 MB  333039 rows
. . imported "KOOP"."PRL_KOOP_GENELKURUL"                21.15 MB  578299 rows
. . imported "KOOP"."PRL_KOOPERATIF"                     20.45 MB   85259 rows
. . imported "KOOP"."PRL_REP_REPORTSHOW"                 10.10 MB   32308 rows
. . imported "KOOP"."PRL_KOOP_MALIDURUM"                 9.268 MB  114023 rows
. . imported "KOOP"."PRL_GENELKURUL_CAGRI"               7.879 MB  214770 rows
. . imported "KOOP"."PRL_OLD_KOOPERATIF"                 4.460 MB   77214 rows
. . imported "KOOP"."PRL_YONETIM_MALBEYAN"               4.009 MB   55809 rows
. . imported "KOOP"."PRL_KOOP_GAYRIMENKUL"               3.455 MB   45716 rows
. . imported "KOOP"."PRL_REF_DOSYALAR"                   3.020 MB   21409 rows
. . imported "KOOP"."PRL_KOOP_TASFIYE_TERKIN"            2.539 MB   45338 rows
. . imported "KOOP"."PRL_SYS_WEBSESSIONS"                1.870 MB   14768 rows
. . imported "KOOP"."PRL_GENELKURUL_TEMSILCI"            1.137 MB   28200 rows
. . imported "KOOP"."PRL_KOOP_EVRAK"                     1.056 MB   14859 rows
. . imported "KOOP"."PRL_LOG_FILESHOW"                   1.121 MB   16362 rows
. . imported "KOOP"."PRL_SYS_AUTHLIST_OLD"               639.9 KB   23410 rows
. . imported "KOOP"."PRL_SYS_AUTHLIST"                   293.6 KB   10438 rows
. . imported "KOOP"."PRL_KOOP_MADDEDEGISIKLIK"           268.8 KB    2477 rows
. . imported "KOOP"."PRL_GENELKURUL_SAVCILIK"            207.0 KB    1996 rows
. . imported "KOOP"."PRL_SYS_USERS"                      162.4 KB    1661 rows
. . imported "KOOP"."DENEME"                             6.289 KB       1 rows
. . imported "KOOP"."PRL_DENETIM_DENETIMCI"              11.81 KB      69 rows
. . imported "KOOP"."PRL_KOOP_DENETIM"                   37.03 KB     133 rows
. . imported "KOOP"."PRL_KOOP_ORTAKLAR_ODENTI"           78.14 KB    1337 rows
. . imported "KOOP"."PRL_KOOP_SUBESI"                    10.37 KB       8 rows
. . imported "KOOP"."PRL_MSG_ORTAKLARDUYURU"             26.11 KB      40 rows
. . imported "KOOP"."PRL_REFERANSLAR"                    10.32 KB       3 rows
. . imported "KOOP"."PRL_REF_ALINANSATILANTUR"           6.921 KB       4 rows
. . imported "KOOP"."PRL_REF_BIRIMLER"                   6.929 KB       6 rows
. . imported "KOOP"."PRL_REF_CAGRITIPI"                  6.945 KB       5 rows
. . imported "KOOP"."PRL_REF_CAGRIYAPAN"                     7 KB       7 rows
. . imported "KOOP"."PRL_REF_DENETIMCI"                  10.04 KB      10 rows
. . imported "KOOP"."PRL_REF_DOSYANOVER"                 6.328 KB      81 rows
. . imported "KOOP"."PRL_REF_DOSYATIPLERI"               7.085 KB       9 rows
. . imported "KOOP"."PRL_REF_EDINMEYONTEMLERI"           6.890 KB       3 rows
. . imported "KOOP"."PRL_REF_EVRAK_GIZLILIK"             6.851 KB       3 rows
. . imported "KOOP"."PRL_REF_EVRAK_KONUSU"               6.796 KB       1 rows
. . imported "KOOP"."PRL_REF_EVRAK_OZELDURUM"            6.890 KB       5 rows
. . imported "KOOP"."PRL_REF_EVRAK_TIPI"                 6.867 KB       3 rows
. . imported "KOOP"."PRL_REF_GENELKURULTIPI"             7.031 KB      10 rows
. . imported "KOOP"."PRL_REF_HUKUKIDURUM"                7.117 KB       8 rows
. . imported "KOOP"."PRL_REF_IL"                         7.257 KB      82 rows
. . imported "KOOP"."PRL_REF_ILCELER"                    50.37 KB     979 rows
. . imported "KOOP"."PRL_REF_INSAATYAPTIPI"              6.898 KB       4 rows
. . imported "KOOP"."PRL_REF_KOOPTURU"                   8.421 KB       8 rows
. . imported "KOOP"."PRL_REF_KOOPUSTKURULUSTIPI"         6.937 KB       4 rows
. . imported "KOOP"."PRL_REF_MAHKEMELER"                 11.48 KB     121 rows
. . imported "KOOP"."PRL_REF_MENKULTURU"                 6.968 KB       7 rows
. . imported "KOOP"."PRL_REF_MESLEKIBILGI"               11.64 KB      41 rows
. . imported "KOOP"."PRL_REF_ORTAKLARDURUM"              7.023 KB       6 rows
. . imported "KOOP"."PRL_REF_ORTAKODENTITIPI"            6.890 KB       5 rows
. . imported "KOOP"."PRL_REF_SAVCILIK_KARAR"             7.695 KB      22 rows
. . imported "KOOP"."PRL_REF_SIKAYETEDILEN"              6.921 KB       6 rows
. . imported "KOOP"."PRL_REF_SIKAYETYERI"                7.023 KB       9 rows
. . imported "KOOP"."PRL_REF_TASFIYE_TERKIN"             6.867 KB       3 rows
. . imported "KOOP"."PRL_REF_TEMSILCILER"                71.89 KB     831 rows
. . imported "KOOP"."PRL_REF_TEMSILCITIPI"               6.828 KB       2 rows
. . imported "KOOP"."PRL_REF_ULKELER"                    11.93 KB     227 rows
. . imported "KOOP"."PRL_REF_VERGIDAIRESI"               36.21 KB     947 rows
. . imported "KOOP"."PRL_REF_YONKURULDURUM"              6.992 KB       6 rows
. . imported "KOOP"."PRL_REF_YONKURULGOREV"              7.460 KB      19 rows
. . imported "KOOP"."PRL_REP_REPORTCONFIG"                   7 KB      25 rows
. . imported "KOOP"."PRL_SYS_AUTHCITY"                   17.73 KB     417 rows
. . imported "KOOP"."PRL_SYS_AUTHCITY_OLD"               93.68 KB    3350 rows
. . imported "KOOP"."PRL_SYS_AUTHCODES"                  17.97 KB     175 rows
. . imported "KOOP"."PRL_SYS_AUTHGROUPS"                 13.28 KB      89 rows
. . imported "KOOP"."PRL_WEB_BASVURU"                    55.29 KB     263 rows
. . imported "KOOP"."TMP_COLOOK"                         27.19 KB     360 rows
. . imported "KOOP"."PRL_GK_GOREVLENDIRME"               6.632 KB       1 rows
. . imported "KOOP"."PRL_GK_TEMSILCI_MAZERET"                0 KB       0 rows
. . imported "KOOP"."PRL_REF_TEMSILCI"                       0 KB       0 rows
. . imported "KOOP"."PRL_TEST_USERS"                         0 KB       0 rows
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/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "KOOP"."SYS_IMPORT_FULL_01" completed successfully at 17:55:33

  • Sequence,trigger,types… ve diğer objecleri hedef veritabanında oluşturuldu.

  •  Import işlemi gercekleştirildikten sonra **** user’da oluşturulan Objecleri kontrol edildi.


Type            Count(*)
INDEX           101
TABLE           74
VIEW              92
TRIGGER      72
FUNCTION      7
TYPE               37
SEQUENCE   69


 :

Oracle  kullanıcısına ait veriler yeni veritabanı sunucusuna başarılı bir şekilde taşınmıştır.
Gönderen zaman:

Hiç yorum yok:

Yorum Gönder

Kaydol: Kayıt Yorumları (Atom)