揭祕ORACLE備份之－－邏輯備份（EXP）

這一章主要是介紹邏輯備份EXP的使用，涉及部分的IMP匯入操作。EXP是通過SQL語句查詢資料庫。
因為主要圍繞備份展開，之後會繼續完善恢復的相關操作。

[root@dg ~(20:51:50)]# su - oracle
[oracle@dg ~(20:51:58)]$ export ORACLE_SID=wailon
-- 檢視EXP命令幫助
[oracle@dg ~(20:52:04)]$ exp help=y

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 20:52:10 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP) TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
VOLSIZE              number of bytes to write to each tape volume
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Export terminated successfully without warnings.

-- 直接使用命令列備份
[oracle@dg ~(21:36:30)]$ exp scott/tiger tables=dept,emp file=/home/oracle/dbbackup/scott.dmp log=/home/oracle/dbbackup/scott.log buffer=100000

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 21:36:53 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table DEPT 6 rows exported
. . exporting table EMP 18 rows exported
Export terminated successfully without warnings.

-- 使用引數檔案備份
[oracle@dg dbbackup(21:38:57)]$ more exp.par
userid=scott/tiger
tables=dept,emp,bonus
file=/home/oracle/dbbackup/scott2.dmp
log=/home/oracle/dbbackup/scott2.log

[oracle@dg dbbackup(21:39:00)]$ exp parfile=exp.par

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 21:39:14 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           DEPT          6 rows exported
. . exporting table                            EMP         18 rows exported
. . exporting table                          BONUS          0 rows exported
Export terminated successfully without warnings.

-- 以表的形式備份
[oracle@dg dbbackup(21:39:31)]$ exp scott/tiger tables=dept,emp file=/home/oracle/dbbackup/scott3.dmp log=/home/oracle/dbbackup/scott3.log rows=n

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 21:40:29 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
. . exporting table DEPT
. . exporting table EMP
Export terminated successfully without warnings.

-- 使用QUERY備份符合條件的表資料
[oracle@dg dbbackup(21:40:29)]$ more exp1.par
userid=scott/tiger
tables=emp
query='where sal>1000'
file=/home/oracle/dbbackup/exp4.dmp
log=/home/oracle/dbbackup/exp4.log

[oracle@dg dbbackup(21:42:48)]$ exp parfile=exp1.par

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 21:42:55 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table EMP 13 rows exported
Export terminated successfully with warnings.

-- 使用COMPRESS=N生成未壓縮的備份，預設為Y
[oracle@dg dbbackup(21:50:08)]$ more exp2.par
userid=scott/tiger
tables=emp
compress=n
file=/home/oracle/dbbackup/exp4.dmp
log=/home/oracle/dbbackup/exp4.log

[oracle@dg dbbackup(21:50:11)]$ exp parfile=exp2.par

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 21:50:20 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table EMP 18 rows exported
Export terminated successfully without warnings.

-- 使用FLASHBACK_TIME備份閃回資料
[oracle@dg dbbackup(21:54:25)]$ more exp3.par
userid=scott/tiger
tables=emp
file=/home/oracle/dbbackup/exp6.dmp
log=/home/oracle/dbbackup/exp6.log
flashback_time="to_timestamp('2013-09-28 21:50:00','yyyy-mm-dd hh24:mi:ss')"

[oracle@dg dbbackup(21:54:29)]$ exp parfile=exp3.par

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 21:54:32 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table EMP 18 rows exported
Export terminated successfully without warnings.

-- 以SCHEMA的模式備份資料，SCHEMA也就是平時所說的使用者，屬於該使用者的所有物件都將匯出
[oracle@dg dbbackup(21:57:08)]$ more exp-schema.par
userid=scott/tiger
owner=scott
file=/home/oracle/dbbackup/scott-schema.dmp
log=/home/oracle/dbbackup/scott-schema.log
buffer=100000

[oracle@dg dbbackup(21:57:18)]$ exp parfile=exp-schema.par

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 21:57:25 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          6 rows exported
. . exporting table                            EMP         18 rows exported
. . exporting table                       SALGRADE          5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

-- 以表空間的形式備份資料
[oracle@dg dbbackup(21:59:47)]$ more exp-tbs.par
userid=scott/tiger
tablespaces=users
file=/home/oracle/dbbackup/tbs-users.dmp
log=/home/oracle/dbbackup/tbs-users.log
buffer=100000

[oracle@dg dbbackup(22:00:09)]$ exp parfile=exp-tbs.par

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 22:00:20 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export selected tablespaces ...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          BONUS          0 rows exported
. . exporting table                     CHECKPOINT          2 rows exported
. . exporting table                 CHECKPOINT_LOX          0 rows exported
. . exporting table                           DEPT          6 rows exported
. . exporting table                            EMP         18 rows exported
. . exporting table                       SALGRADE          5 rows exported
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.

-- EXP的傳輸表空間
[oracle@dg dbbackup(22:01:54)]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 22:04:53 2013

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

22:04:53 SYS@wailon> create tablespace trans_tbs datafile '/home/oracle/trans_tbs.dbf' size 5m;

Tablespace created.

22:05:37 SYS@wailon> create table scott.employee (id number(5),name varchar2(30)) tablespace trans_tbs;

Table created.

22:07:15 SYS@wailon> insert into scott.employee values(1,'wailon');

1 row created.

22:07:21 SYS@wailon> commit;

Commit complete.

22:07:23 SYS@wailon> -- EXP匯出備份前設定表空間為只讀
22:07:51 SYS@wailon> alter tablespace trans_tbs read only;

Tablespace altered.

22:09:59 SYS@wailon> host

-- 設定EXP引數匯出傳輸表空間
[oracle@dg dbbackup(22:12:30)]$ more exp-trans.par
userid='sys/oracle as sysdba'
tablespaces=trans_tbs
transport_tablespace=y
file=/home/oracle/trans_tbs.dmp
log=/home/oracle/trans_tbs.log

[oracle@dg dbbackup(23:05:08)]$ exp parfile=exp-trans.par

Export: Release 11.2.0.3.0 - Production on Sat Sep 28 23:05:11 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TRANS_TBS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table EMPLOYEE
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

-- 進入另外一個資料庫
[oracle@dg dbbackup(22:13:43)]$ export ORACLE_SID=dg
[oracle@dg dbbackup(22:14:15)]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 22:14:19 2013

22:14:44 SYS@dg> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------
/u01/app/oracle/oradata/dg/system01.dbf
/u01/app/oracle/oradata/dg/sysaux01.dbf
/u01/app/oracle/oradata/dg/undotbs01.dbf
/u01/app/oracle/oradata/dg/users01.dbf

22:14:57 SYS@dg> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- 編輯IMP引數匯入傳輸表空間
[oracle@dg dbbackup(22:16:15)]$more imp-trans.par
userid='sys/oracle as sysdba'
tablespaces=trans_tbs
transport_tablespace=y
file=/home/oracle/trans_tbs.dmp
datafile='/u01/app/oracle/oradata/dg/trans_tbs.dbf'
log=/home/oracle/trans_tbs_imp.log

-- 將表空間包含的檔案複製到指定位置
[oracle@dg dbbackup(23:11:37)]$ cp /home/oracle/trans_tbs.dbf /u01/app/oracle/oradata/dg/trans_tbs.dbf

-- 執行IMP，將表空間匯入
[oracle@dg dbbackup(23:12:29)]$ imp parfile=imp-trans.par

Import: Release 11.2.0.3.0 - Production on Sat Sep 28 23:12:37 2013

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

Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "EMPLOYEE"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

-- 匯入成功後，檢查表空間是否傳輸成功
[oracle@dg dbbackup(23:12:39)]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 23:12:46 2013

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

23:12:09 SYS@wailon> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
USERS                          ONLINE
TBS_LRJ                        ONLINE
TEMP01                         ONLINE
TRANS_TBS                      ONLINE

7 rows selected.

23:12:46 SCOTT@dg> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
EMPLOYEE                       TABLE
SALGRADE                       TABLE

5 rows selected.

23:12:50 SCOTT@dg> select * from employee;

ID NAME
---------- ------------------------------
1 wailon

23:12:58 SCOTT@dg> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- 將原資料庫的表空間設定為可讀寫
[oracle@dg dbbackup(23:13:03)]$ export ORACLE_SID=wailon
[oracle@dg dbbackup(23:13:10)]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 23:13:13 2013

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

23:13:13 SYS@wailon> alter tablespace trans_tbs read write;

Tablespace altered.

23:13:23 SYS@wailon>

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

揭祕ORACLE備份之－－邏輯備份（EXP）

相關文章