oracle資料匯入與匯出____oracle

  資料的匯入匯出 說明：

針對的對象：  資料的匯入匯出牽涉到的角色主要是工程實施人員。

需解決的問題：把所需要的資料從一個資料庫中匯入到另外一個資料庫中。 1    工具方式 1.1         工具說明

1.  使用PLSQL Developer工具主要為了方便工程實施人員操作資料庫，如匯入、匯出資料庫物件。

2.    使用TOAD工具方便工程實施人員操作資料庫且匯入、匯出資料的效率比較高。

3.    使用以上兩種工具，實施人員無需在命令列下操作資料庫，方便而且不易出錯。 1.1.1      準備工作

1.  工具軟體PLSQL Developer 、TOAD。

2.  記錄資料來源的oracle資料庫資訊（IP、連接埠、全域資料庫名），記為SOURCE_DB；

3.  scheme（SOURCE_SCHM）、tablespace(預設資料表空間 SOURCE _SPACE,索引資料表空間 INDEX _SPACE )；

4.  用於資料匯出的帳號（記錄為EXPUSR）；

5.  記錄資料目標的oracle資料庫資訊（IP、連接埠、全域資料庫名），記為DEST_DB；

6.  scheme(DEST_SCHM)、tablespace(預設資料表空間 DEST_SPACE,索引資料表空間 DESINDEX _SPACE)；

7.  用於資料匯入的帳號（記錄為IMPUSR）等資訊。

8.  記錄需要從資料來源中匯出的資料庫物件，記為DB_OBJECTS；

9.  如果需要過濾匯出資料，則要記錄過濾條件，記為DATA_FILTER。

  1.2         資料庫物件 1.2.1      匯出資料庫物件

1.  啟動PL/SQL Developer，以使用者 EXPUSR 登入源Oracle資料庫SOURCE_DB；

2.  從PL/SQL Developer菜單Tools中選擇Export User Objects；

3.  匯出資料庫物件

a、 在User的下拉框中選擇需要匯出資料的<SOURCE_SCHM>。

b、選中屬性 Single file，include Owner, include Storage。

c、選擇需要匯出的資料庫物件；

d、點擊Export按鈕則可以將資料庫物件匯出，此時會產生一個以sql為副檔名的檔案（記錄為EXPORT_FILE）。 1.1.1. 匯入資料庫物件

1.  修改Sql指令碼

如果來源資料的scheme和目標資料庫的scheme不一致，或者來源資料庫的資料表空間和目標資料庫的資料表空間不一致，需修改匯出的EXPORT_FILE。

create table <SOURCE_SCHM>.ACTION_INFO 修改為

create table <DEST_SCHM>.ACTION_INFO

tablespace <SOURCE _SPACE> (源預設資料表空間)   --   改為目標預設資料表空間(<DEST_SPACE>)

pctfree 10                               

initrans 1                                  

maxtrans 255                                 

storage                                       

(                                              

initial 64K                                    

minextents 1                                  

maxextents unlimited                          

);                                              

create index AK_CDR_D_DETAIL on CDR_D_DETAIL (USER_NUMBER)

tablespace <INDEX _SPACE>（源索引資料表空間）  --à改為目標索引資料表空間(<DESINDEX _SPACE>)

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

  using index

 tablespace <INDEX _SPACE>（源索引資料表空間）  --à改為目標索引資料表空間(<DESINDEX _SPACE>)

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

2.  啟動PL/SQL Developer，以使用者 IMPUSR登入目標Oracle資料庫DEST_DB；

3.  從PL/SQL Developer菜單中選擇New下面的Command Window；

選擇Editor選項，將EXPORT_FILE檔案內容拷貝到Editor選項框中

按F8即可將資料庫物件匯入到DEST_SCHM中。

         1.3         資料匯入匯出

使用TOAD工具進行資料匯入匯出。 1.3.1      匯出資料

1.  啟動TOAD，以EXPUSR登入來源資料庫SOURCE_DB；

2選擇菜單中的Database->Export->Data Pump Export Wizard選項

 

 

2.  在Export選項中選擇Tables

 

3.    在下拉框中選擇來源資料庫的Scheme：SOURCE_SCHM，並選擇需要匯出的資料表。

4.  如果資料需過濾，則此處填寫過濾條件DATA_FILTER。

 

5.  Content下拉框選擇Data Only選項。

6.  Directory中選擇所要匯出資料的目錄，Output file name(.dmp)給匯出的資料指定一個檔案名稱記為DUMP_FILE。

7.  按Finish即可將資料匯出。

          1.3.2      匯入資料        

8.  以使用者 <IMPUSR>登入目標Oracle資料庫DEST_DB；

9.  選擇菜單中的Database->Import-> Data Pump Import Wizard選項

Import選項中選擇Entire dumpfile。

 

10.      Source為匯出來源資料庫的<SOURCE_SCHM>，Target為匯入目標資料庫的<DEST_SCHM>。

 

11.      Content下拉框選項中選擇Data Only。

12.      Directory：指定匯入資料檔案的路徑；Input file name為所要匯入的資料檔案DUMP_FILE。

13.      按Finish即可將資料匯入。

 

 

  1.4         匯入匯出記錄

SOURCE_DB	來源資料庫資訊
SOURCE_SCHM	來源資料要匯出的使用者
SOURCE _SPACE	源預設資料表空間
INDEX _SPACE	源索引資料表空間
EXPUSR	資料匯出的帳號
DEST_DB	目標資料庫資訊
DEST_SCHM	目標資料匯入的使用者
DEST_SPACE	目標預設資料表空間
DESINDEX _SPACE	目標索引資料表空間
IMPUSR	資料匯入的帳號
EXPORT_FILE	匯出的sql指令碼
DUMP_FILE	匯出的資料檔案
DB_OBJECTS	匯出的資料庫物件
DATA_FILTER	匯出資料的過濾條件

  2    命令列方式       2.1         資料庫物件匯入

1、開啟資料對象指令碼，檢查指令碼是否正確。資料表空間是否匹配，索引是否建在正確的索引空間。

2、在命令列視窗輸入資料對象指令碼：@檔案路徑+檔案名稱，例如：

@e:/biao/tables.sql 把資料對象指令碼匯入後，檢查是否正確。包括表，索引，預存程序，視圖等。檢查對象個數是否匹配。

  2.2         資料匯出

使用expdp要先在資料庫中建立directory如在D盤下建立dumptestdir,並給相應的使用者read,write許可權. 注意要以有許可權的的使用者登陸；

SQL>create directory dumpdir as 'd:\dumptestdir';

SQL>grant read,write on directory dumpdir to HUNAN;

1)        資料的匯出方法

從資料庫中把表資料匯出到目錄中：

>Expdp hunan/hunan@192.168.1.22 tables(action_info) dumpfile=dumptest.dmp directory=dumpdir logfile=dumptest.log connect=data_only 2.3         資料匯入

1)  使用Impdp要先在資料庫中建立directory如在D盤下建立dumptestdir,並給相應的使用者read,write許可權.並把要匯入的檔案拷貝在此目錄下，注意要以有許可權的的使用者登陸；

2)  把表資料匯入到資料庫中（先由上述方法把表結構匯入到資料庫中）：

Impdp kuang/kzp4715712@192.168.1.44 dumpfile=”dumptestsj.dmp” directory=dumpdir remap_schema=hunan:kuang remap_tablespace=cdmadata:cdma content=data_only

 

常用一些參數說明:

DUMPFILE:匯出後的檔案名稱(*)

DIRECTORY:匯出檔案存放位置(位於伺服器端)(*)

CONTENT:匯出檔案中包含的內容(預設為:ALL,可選DATA_ONLY/METADATA_ONLY)

FILESIZE:指定匯出檔案大小(單位為bytes).

JOB_NAME:此次匯出進程使用的名稱,方便跟蹤查詢(可選)

LOGFILE:記錄檔名(預設為:export.log)

INCLUDE:匯出時包含指定的類型

(例:INCLUDE=TABLE_DATA,

INCLUDE=TABLE:"LIKE 'TAB%'"

INCLUDE=TABLE:”NOT LIKE ‘TAB%’”…)

EXCLUDE:匯出時排除的資料類型(例:EXCLUDE=TABLE:EMP)

FULL:全庫匯出時使用(同EXP的FULL,預設為N)

SCHEMA:匯出某一個SCHEMA下的所有資料

TABLES:按表匯出(這裡的方法和EXP一樣)

TABLESPACE:指定一個資料表空間匯出.

QUERY:按表匯出時,使用條件陳述式限定匯出範圍(同exp中的QUERY

 

  3    Oracle備份恢複

3           3.1         基於RMAN備份策略

1    

2     3.1.1      RMAN簡介

RMAN(Recovery Manager)是DBA的一個重要工具，用於備份、還原和恢複oracle資料庫，RMAN 可以用來備份和恢複資料庫檔案、歸檔日誌和控制檔案，也可以用來執行完全或不完全的資料庫恢複。具有如下特點：

1)  RMAN操作相對簡單，很多工作自動完成

2)  RMAN 工具能以很多種方法備份 Oracle 資料庫，給備份和恢複方法提供了很大的靈活性

3)  執行增量備份。RMAN 具有執行增量備份和完全備份的能力。增量備份僅備份從上次備份後修改了的內容，此方法允許你一周只有一天執行完全備份，而其它幾天都執行增量備份，這樣就提高了備份的效能

4)  提供目錄資訊列表。可以使用 RMAN的LIST和REPORT命令查詢儲存在catalog目錄中有關備份的資訊，這些命令提供顯示資訊的有效方法

5)  RMAN在進行聯機備份時，自動判斷block的一致性，不需要將資料檔案處於備份狀態

6)  RMAN可以提供一些關鍵資訊的報告

7)  可以方便的檢查介質上儲存的備份是否可用 3.2         備份策略    3.2.1      備份目標

保證目標資料庫安全，使資料庫的失效次數減到最少，從而使資料庫保持最大的可用性；

當資料庫不可避免地失效後，要使恢復減到最少，從而使恢複的效率達到最高；

當資料庫失效後，要確保盡量少的資料丟失或根本不丟失，從而使資料具有最大的可恢複性。

  3.2.2      備份策略制定準備

1) 以什麼方式備份：

用RMAN的增量備份（差量備份），採用多級備份是為了減少了恢複所需要的時間和減少每天備份所需要的時間，而又保證系統有很好的恢複性。

2) 備份的資料儲存在什麼地方：

備份的資料應該異地儲存，這樣備份的安全才能有保證。

3) 備份時間安排：

由於備份時對系統I/O有較大影響，所以，建議在下班以後進行備份工作。

4) RMAN備份注意事項：

任何資料庫的更改需要重新同步CATALOG目錄或重新備份 3.2.3      具體的備份策略

1)    配置目標資料庫：

確定資料庫為歸檔模式

設定Flashback啟用為ON

設定資料庫啟動時使用spfile參數

2)    配置rman資料庫

3)    差異增量備份：每個月一次全備，周日一次零備，周一，二做二級備份，周三做一次一級的，周四，五，六再做二級備份。

rman> backup database;(資料庫全備)

rman> backup incremental level 0 database;(周日)

rman> backup incremental level 2 database; (周一)

rman> backup incremental level 2 database; (周二)

rman> backup incremental level 1 database; (周三)

rman> backup incremental level 2 database; (周四)

rman> backup incremental level 2 database; (周五)

rman> backup incremental level 2 database; (周六)

說明：全備是對整個資料庫的所有資料進行完整備份和0級備份的區別在0級可以有增量備份，全備則不可以。

0級備份是對整個資料庫的所有資料進行完整備份

      1級備份是只對0級備份以後修改過的資料進行備份

      2級備份只是對1級備份以後修改過的資料進行備份

注意：具體備份恢複步驟參見1.3           3.3         備份步驟

備份目標資料庫和RMAN資料庫準備工作：

(一)    目標資料庫

a)  修改資料庫歸檔狀態，查看歸檔狀態：

SQL> archive log list;

如果資料庫未處於未歸檔模式下，則設定為歸檔模式

b)  啟用 FLASHBACK DATABASE 事件記錄，這樣在不慎drop表時可以快速恢複。查看Flashback啟用狀態：

SQL> select dbid,name,flashback_on,current_scn from v$database;

如果沒有flashback on,則進行如下設定：

SQL> shutdown immediate

SQL> startup mount

SQL> alter database flashback on;  

SQL> select dbid,name,flashback_on,current_scn from v$database;

SQL> alter database open;

c)  資料庫啟動時使用spfile參數，這樣備份時spfile會和控制檔案一起備份。

查看是否用spfile參數檔案：

SQL> show parameter spfile

VALUE的值為空白，資料庫是用pfile檔案啟動的，如果有值，則為spfile啟動，如果沒有值，則進行如下設定：

建立spfile參數檔案

SQL> create spfile from pfile

SQL> shutdown immediate

SQL> startup

SQL> show parameter spfile;

(二)    配置rman資料庫

建立RMAN目錄，以下步驟說明了在一個資料庫中建立RMAN目錄的過程。

a. 為目錄建立一個單獨的資料表空間

SQL>Create tablespace rmantablespace datafile ‘D:/rmantablespace.ora’ size 500m;

b.建立RMAN使用者

SQL>Create user RMAN identified by RMAN default tablespace rmantablespace temporary tablespace temp;

c.給RMAN授予許可權

SQL>Grant connect , resource , recovery_catalog_owner to rman;

d.開啟RMAN

$>RMAN

e.串連資料庫

RMAN>connect catalog rman/rman

f.建立恢複目錄

RMAN>Create catalog tablespace rmantablespace;

註冊目標資料庫，恢複目錄建立成功後，就可以註冊目標資料庫了，目標資料庫就是需要備份的資料庫，一個恢複目錄可以註冊多個目標資料庫，註冊目標資料庫的命令為：

$>RMAN target sys/kzp4715712@kuang

RMAN>connect catalog rman/rman

RMAN>Register database;

資料庫註冊完成,就可以用RMAN來進行備份了。

d)  查看和修改RMAN備份配置

RMAN>show all;

查看RMAN預設備份參數，根據實際需求更改

RMAN>configure controlfile autobackup on;

配置參數指定進行任何備份時，都對控制檔案和參數檔案同時進行備份（預設OFF）

(三)    備份目標資料庫

a)  對目標資料庫進行日誌切換，以便產生最新的歸檔檔案進行備份

rman>sql ‘alter system archive log current’;

b)  全庫備份：

RMAN>backup database;

備份目標資料庫上所有的資料檔案，控制檔案與參數檔案以及歸檔檔案

c)  查看目標資料庫的備份資訊：

RMAN>list backup of database;

d)  增量備份：

Rman> backup incremental level 0 database; (周日)

Rman> backup incremental level 2 database; (周一)

Rman> backup incremental level 2 database; (周二)

Rman> backup incremental level 1 database; (周三)

Rman> backup incremental level 2 database; (周四)

Rman> backup incremental level 2 database; (周五)

Rman> backup incremental level 2 database; (周六) 3.4         恢複步驟

(一)    全庫恢複

a)  串連目標伺服器

>rman

RMAN> connect target sys/kzp4715712@orcl

b)  串連RMAN伺服器

RMAN>connect catalog rman/rman

c)  假設目標伺服器損壞

d)  啟動目標伺服器

RMAN>startup nomount;

RMAN>restore spfile from autobackup;修複參數檔案

RMAN>restore controlfile from autobackup;修複控制檔案

RMAN>restore database;修複資料檔案

RMAN>alter database mount;啟動到MOUNT狀態

RMAN>recover database;進行修複

RMAN>alter database open resetlogs;重設記錄檔開啟資料庫

e)  最後重建暫存資料表空間

(二)    基於時間的恢複

基於時間的恢複可以將資料庫恢複到過去某一個時間點，常用於在誤操作刪除資料後，將庫恢複到刪除時間之前，從而挽回丟失的資料。

RMAN> run{

sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss" ';

set until time '2009-04-08:11:00:00';

restore database;

recover database;

alter database open resetlogs;

} 4    基於EMP/IMP備份策略（oracle10g已不支援）

Oracle匯出／匯入（Export／Import）Database Backup方法：利用Export可將資料從資料庫中提取出來，利用Import則可將提取出來的資料送回到Oracle資料庫中去。      4.1         簡單匯出資料（Export）和匯入資料（Import）

Oracle支援三種方式類型的輸出：

(一)    表方式（T方式），將指定表的資料匯出。

(二)    使用者方式（U方式），將指定使用者的所有對象及資料匯出。

(三)    全庫方式（Full方式），將資料庫中的所有對象匯出。

　　資料匯入（Import）的過程是資料匯出（Export）的逆過程，分別將資料檔案匯入資料庫和將資料庫資料匯出到資料檔案。 4.2         增量匯出／匯入

　　增量匯出是一種常用的資料備份方法，它只能對整個資料庫來實施，並且必須作為SYSTEM來匯出。在進行此種匯出時，系統不要求回答任何問題。匯出檔案名稱預設為export.dmp，如果不希望自己的輸出檔案定名為export.dmp，必須在命令列中指出要用的檔案名稱。

　　增量匯出包括三種類型：

(一)    “完全”增量匯出（Complete）

　　即備份三個資料庫，比如：

　　exp system/kzp4715712 inctype=complete file=040731.dmp

(二)    “增量型”增量匯出

　　備份上一次備份後改變的資料，比如：

　Exp system/kzp4715712 inctype=incremental file=040731.dmp

(三)    “累積型”增量匯出

　　累計型匯出方式是匯出自上次“完全”匯出之後資料庫中變化了的資訊。比如：

　　exp system/manager inctype=cumulative file=040731.dmp

　　資料庫管理員可以排定一個備份議程表，用資料匯出的三個不同方式合理高效的完成。

　　比如資料庫的備份任務可以做如下安排：（括弧中字母表示備份檔案名）

　　星期一：完全備份（A）

　　星期二：增量匯出（B）

　　星期三：增量匯出（C）

　　星期四：增量匯出（D）

　　星期五：累計匯出（E）

　　星期六：增量匯出（F）

　　星期日：增量匯出（G）

　　如果在星期日，資料庫遭到意外破壞，資料庫管理員可按一下步驟來回複資料庫：

　　第一步：用命令CREATE DATABASE重建資料庫結構；

　　第二步：建立一個足夠大的附加復原。

　　第三步：完全增量匯入A：

　　imp system/manager inctype=RESTORE FULL=y FILE=A

　　第四步：累計增量匯入E：

　　imp system/manager inctype=RESTORE FULL=Y FILE=E

　　第五步：最近增量匯入F：

　　imp system/manager inctype=RESTORE FULL=Y FILE=F