这一章主要是介绍逻辑备份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博客

揭秘ORACLE备份之－－逻辑备份（EXP）

最新文章