dba基礎課程：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以上