RiCo技術農場

後續文章將發佈在https://medium.com/ricos-note

2010-01-05

[Oracle][Maintain]Datapump Expdp and Impdp

[Oracle][Maintain]Datapump Expdp and Impdp

Datapump當初算Oracle10g新特性之一，該技術提供複製資料庫更細節物件選項

如針對單一Schema、Table、Metadata、Tablespace，而且速度比Oracle9i exp/imp快很多。

通常利用datapump技術，大多都因為跨平台因素居多

當然還有匯出big table(20g)或其他物件也很方便。

 

優點:

1.可以跨平台複製轉移資料庫物件

2.提供更細節資料庫物件選項供DBA利用

 

缺點:

1.操作複雜麻煩

2.如果資料庫物件相依性高，在執行過程可能因不明錯誤遭到oracle強制中斷

3.轉移資料庫物件如果過於龐大複雜，發生錯誤失敗不容易找出兇手

 

Expdp匯出資料庫

Source

Os:win2003 R2 32bit

DB: 10.2.0.4.0 32bit

 

Par檔內容

DIRECTORY=DATAPUMP

CONTENT=METADATA_ONLY–只匯出metadata

SCHEMAS=...._RP,...._ADMIN,..._SAS–只包含清單schema

DUMPFILE=expdp_schem_win_%U.dmp

LOGFILE=expdp_schem_win_.log

EXCLUDE=GRANT,INDEX_STATISTICS,TABLE_STATISTICS–排除權限和table,index統計值，可縮短匯出時間

parallel=2–使用parallel數量

FILESIZE=1.5G–單一檔案大小限制

clip_image002

clip_image002[5]

這樣就完成..._RP,.._ADMIN,.._SAS schema相關object的匯出。

 

oracle10.2.0.1 upgrad 10.2.0.4 in Linux

Linux上安裝Oracle，必須先確認以下 rpm都已經安裝(有順序性)

rpm -ivh libgomp-4.3.2-7.el5.i386.rpm
rpm -ivh kernel-headers-2.6.18-128.el5.i386.rpm
rpm -ivh glibc-headers-2.5-34.i386.rpm
rpm -ivh glibc-devel-2.5-34.i386.rpm
rpm -ivh gcc-4.1.2-44.el5.i386.rpm
rpm -ivh libstdc++-devel-4.1.2-44.el5.i386.rpm
rpm -ivh gcc-c++-4.1.2-44.el5.i386.rpm
rpm -ivh libaio-devel-0.3.106-3.2.i386.rpm
rpm -ivh sysstat-7.0.2-3.el5.i386.rpm
rpm -ivh unixODBC-2.2.11-7.1.i386.rpm
rpm -ivh unixODBC-2.2.11-7.1.i386.rpm
rpm -ivh unixODBC-devel-2.2.11-7.1.i386.rpm         
rpm -ivh unixODBC-devel-2.2.11-7.1.i386.rpm
rpm -ivh glibc-devel-2.5-34.i386.rpm
rpm -ivh gcc-c++-4.1.2-44.el5.i386.rpm
rpm -ivh libaio-devel-0.3.106-3.2.i386.rpm
rpm -ivh elfutils-libelf-devel-static-0.137-3.el5.i386.rpm
rpm -ivh elfutils-libelf-devel-0.137-3.el5.i386.rpm
rpm -ivh elfutils-libelf-devel-static-0.137-3.el5.i386.rpm
rpm -ivh elfutils-libelf-devel-0.137-3.el5.i386.rpm

以下系統配置將省略(小弟太懶了XD)

建立oracle user相關的group

配置/etc/hosts

修改/etc/sysctl.conf

修改/etc/security/limits.conf

修改/etc/pam.d/login

編輯/etc/profile

禁用SELinux

修改oracle .bash_profile(oracle登入)

當上面一切都正確設定完畢後，就可以開始安裝Oracle software

clip_image002[11]clip_image002[13]

clip_image002[15]

root登入執行orainstRoot.sh

clip_image002[17] 

執行root.sh

clip_image002[19]

安裝10.2.0.4.0 patch set

clip_image002[21]

安裝完畢

 

Create DataBase

clip_image002[23]

clip_image002[25]

clip_image002[27]

建立完成後確認狀態

clip_image002[29]

 

Impdp匯入異質平台資料庫

前面我們已匯出windows平台資料庫dmp檔案，準備利用該檔案匯入linux平台

Par檔內容

DIRECTORY=DATAPUMP

SCHEMAS=..._RP,..._ADMIN,...._SAS

DUMPFILE=EXPDP_SCHEM_WIN_%U.DMP

LOGFILE=impdp_schem.log

parallel=2

 

Create directory

create or replace directory DATAPUMP
  as '/home/oracle/datapump';

clip_image002[7]

clip_image002[9]

Create tablespace(須一環境修改datafile路徑)

script會產生建立tablespace語法 

-- create tablespace script for filesystem 
declare  
  p_file_size number;
  p_file_cnt  number;
begin
  for c in (select v.*,
                   tbs_used / file_cnt file_size,
                   ceil(tbs_used / 2 / 1024) sugg_file_cnt
              from (select d.tablespace_name,
                           nvl(a.bytes, 0) / 1024 / 1024 tbs_size,
                           decode(d.contents,
                                  'UNDO',
                                  nvl(u.bytes, 0),
                                  nvl(a.bytes - nvl(f.bytes, 0), 0)) / 1024 / 1024 tbs_used,
                           decode(d.contents,
                                  'UNDO',
                                  nvl(nvl(u.bytes, 0) / a.bytes * 100, 0),
                                  nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100,
                                      0)) used_pct,
                           a.file_cnt
                      from sys.dba_tablespaces d,
                           (select tablespace_name,
                                   sum(bytes) bytes,
                                   count(1) file_cnt
                              from dba_data_files
                             group by tablespace_name) a,
                           (select tablespace_name, sum(bytes) bytes
                              from dba_free_space
                             group by tablespace_name) f,
                           (select tablespace_name,
                                   sum(bytes) bytes,
                                   count(1) file_cnt
                              from dba_undo_extents
                             where status in ('ACTIVE', 'UNEXPIRED')
                             group by tablespace_name) u
                     where d.tablespace_name = a.tablespace_name(+)
                       and d.tablespace_name = f.tablespace_name(+)
                       and d.tablespace_name = u.tablespace_name(+)
                       and d.tablespace_name not in ('TEMP')                   
                    ) v
             order by tablespace_name) loop 
    if c.tablespace_name not in
       ('SYSTEM', 'SYSAUX', 'USERS', 'UNDOTBS1', 'UNDOTBS2') then
      dbms_output.put_line('CREATE SMALLFILE TABLESPACE ' ||
                           c.tablespace_name || ' DATAFILE ''' ||
                           '/home/oracle/oradata/lnxdemo/' || c.tablespace_name ||
                           '01.dbf''' ||
                           ' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;');
    elsif c.tablespace_name in ('V3') then
      dbms_output.put_line('CREATE SMALLFILE TABLESPACE ' ||
                           c.tablespace_name || ' DATAFILE ''' ||
                           '/home/oracle/oradata/lnxdemo/' || c.tablespace_name ||
                           '01.dbf''' ||
                           ' SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;');
    end if;
    if c.tablespace_name in ('V3') then
      for i in 2 .. 3 loop
        dbms_output.put_line('ALTER TABLESPACE ' || c.tablespace_name ||
                             ' ADD DATAFILE ''' || '/home/oracle/oradata/lnxdemo/' ||
                             c.tablespace_name || '0' || to_char(i) ||
                             '.dbf''' ||
                             ' SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;');
      end loop;
    else
      for i in 2 .. 3 loop
        dbms_output.put_line('ALTER TABLESPACE ' || c.tablespace_name ||
                             ' ADD DATAFILE ''' || '/home/oracle/oradata/lnxdemo/' ||
                             c.tablespace_name || '0' || to_char(i) ||
                             '.dbf''' ||
                             ' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;');
      end loop;
      dbms_output.new_line;
    end if;

clip_image002[31]

Create schema

依照par檔案內容，我習慣先建立相關schema和給予適當權限

image image

image

Run impdp

clip_image002[33]

clip_image002[35]

clip_image002[37]

clip_image002[39]

因物件相依性關係，所以匯入成功後會有Invalid object

只需再重新compile該schema下所有objects還有更新相關統計值即完成資料庫匯入作業。

 

確認schema objects 數量:來源和目標應該相同

clip_image002[41]

關於三種Oracle複製(轉移)資料庫技術，小弟大概都介紹差不多

而在速度和可用性的表現上，個人覺得MS SQL再匯入和匯出部分還滿不認真的XD。

RiCo技術農場粉絲頁

標籤雲

系列文章

最新留言