Oracle備份恢復五(資料泵)

煙花丶易冷發表於2015-01-28
Oracle
平臺：redhat linux as5,oracle10g
    Oracle10g 引入了最新的資料泵(Data Dump)技術,使得DBA或應用開發人員可以將資料庫的後設資料庫和資料快速移動到別一個Oracle資料庫中,因為它可以匯出資料庫(表空間等),所以也叫邏輯備份,資料泵匯出匯入命今行選項非常多,大家可以到聯機文件檢視各個選項的用法.,本文詳細介紹最常用的匯出匯入資料庫表空間,然後介紹如何匯入匯出整個資料庫及資料檔案等

表空間

匯出表空間
先準備一個表空間,並建表
SQL> conn y/123
SQL> create tablespace test1 datafile '/home/oracle/oracle/oradata/db2/test1.dbf' size 10M;
SQL> create table test1(i number) tablespace test1;
SQL> insert into test1 values(10);
SQL> commit;
SQL> select * from test1;
         I
---------------------
        10
建立dumpdir目錄,並給使用者y賦權
[oracle@oracle]# mkdir /dumpdir
SQL> create directory dumpdir as '/rman';
SQL> conn sys as sysdba
SQL> grant read,write on directory dumpdir to y;
SQL> grant dba to y;
SQL> conn y/123
分析表test1是否滿足匯出條件
SQL> alter tablespace test1 read only;
Tablespace altered.
SQL> exec sys.dbms_tts.transport_set_check('test1',true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected
#可見沒有不滿足的資料
匯出表空間
SQL> ! expdp directory=dumpdir dumpfile=tbs.dmp transport_tablespaces=test1
Username: sys as sysdba
Password:
Export: Release 10.2.0.4.0 - Production on Saturday, 13 June, 2009 1:58:13
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "Y"."SYS_EXPORT_TABLESPACE_01":  y/******** directory=dumpdir dumpfile=ttbs.dmp tablespaces=t
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
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/COMMENT
. . exported "Y"."T"                                     4.906 KB       1 rows
Master table "Y"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for Y.SYS_EXPORT_TABLESPACE_01 is:
  /dumpdir/tbs.dmp
Job "Y"."SYS_EXPORT_TABLESPACE_01" successfully completed at 01:58:42
沒有報錯,已成功匯出表空間test1

2.匯入表空間

    現在把剛才匯出的表空間匯入到別一個資料庫(2)中
    在資料庫(2)建dumpdir目錄,把庫1匯出的tbs.dmp複製到dumpdir同時把庫1的資料檔案test1.dbf複製到庫2的/home/oracle/oracle/oradata/db2/
#庫2的IP為192.168.1.2
[oracle@oracle22]$ mkdir /dumpdir
[oracle@oracle11]$ scp /dumpdir/tbs.dmp root@192.168.1.2:/dumpdir/
[oracle@oracle11]$ scp /home/oracle/oracle/oradata/test1.dbf
root@192.168.1.2:/home/oracle/oracle/oradata/db2/
開始匯入(以下在庫2操作)
SQL> ! impdp directory=dumpdir dumpfile=tbs.dmp transport_datafiles='/home/oracle/oracle/oradata/db2/test1.dbf'
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLESPACE_01":  sys/******** AS SYSDBA directory=dumpdir dumpfile=ttbs.dmp tablespaces=tt
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at 04:53:23
匯入成功,但這時表空間為offline狀態,
現在把表空間online就可以查到表test1的資料了,如下:
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
TEST1                            OFFLINE
把表空間online並檢視test1表
SQL> alter tablespace test1 online;
SQL> alter tablespace test1 read write;
SQL> select * from test1;
         I
-----------------------
        10
OK了,成功把表空間test1從庫1匯入到庫2

其它匯入匯出

1.匯出
expdp directory=dumpdir dumpfile=full.dmp full=y
expdp directory=dumpdir dumpfile=tbs.dmp tablespaces=test1,test2
expdp directory=dumpdir dumpfile=table.dmp tables=test1,test2
expdp directory=dumpdir dumpfile=schma.dmp schemas=test1,test2
2.匯入
impdp directory=dumpdir dumpfile=full.dmp full=y
impdp directory=dumpdir dumpfile=tbs.dmp transport_datafiles='/home/oracle/oralce/oradata/db2/test1.dbf', '/home/oracle/oralce/oradata/db2/test2.dbf'
impdp directory=dumpdir dumpfile=table.dmp tables=test1,test2
impdp directory=dumpdir dumpfile=schma.dump schemas=test1,test2

注意事項：

    匯入匯出時注意如下幾點
    1.源資料庫和目標資料庫要有相同的字符集
    2.名稱不能相同(導庫時庫名不能相同,導表空間時表空間名不能相同等)
    3.不能搬移system表空間和有sys使用者物件的表空間
    4.要在不同OS上搬移表空間，要保證compatible 設定為10.0以上

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

相關文章