ORACLE 8I增量导出导入问题

显示全部楼层 · 2007-10-20 08:38:44

各位朋友晚上好，我现在在做一个oracle 8i（oracle817 os为Solaris 2.9）的增量导出导入时碰到一些问题，想请教下大家，按照论坛上说法：
（１）、“完全”增量导出（Complete）
即备份三个数据库，比如：
exp system/manager inctype=complete file=1.dmp
（２）、“增量型”增量导出
备份上一次备份后改变的数据，比如：
exp system/manager inctype=incremental file=2.dmp
或者
“累积型”增量导出
累计型导出方式是导出自上次“完全”导出之后数据库中变化了的信息。比如：
exp system/manager inctype=cumulative file=2.dmp
如果在星期日，数据库遭到意外破坏，数据库管理员可按一下步骤来回复数据库：
第一步：用命令CREATE DATABASE重新生成数据库结构；
第二步：创建一个足够大的附加回滚。
第三步：完全增量导入A：
imp system/manager inctype=RESTORE FULL=y FILE=1.dmp
第四步：累计增量导入E：
imp system/manager inctype=RESTORE FULL=Y FILE=2.dmp
或者：最近增量导入F：
imp system/manager inctype=RESTORE FULL=YFILE=2.dmp

可是我在测试过程中发现，在导出时所谓的增量导出或者是累积型增量导出并不是把与上次（步骤1）不同的数据export出来，而是又全部把数据export出来，更恐怖的是在先把全备份导入后，再导入增量备份，其做法是先把table的数据delete掉，然后再把备份的数据导进去，而不是把差异数据导进去，这样的话，增量导进导出就没有任何意义，反而是画蛇添足。。。请有经验的pub友指导！！！！

千问 · 2007-10-20 08:38:44

1.建table xzp，使有数据1，2，3，4 然后导出
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Nov 26 18:01:26 2007
(c) Copyright 2000 Oracle Corporation.All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
SQL> select * from xzp.xzp;
ID
----------
1
2
3
4
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
ora817@facmesdb::/u02/xzp/baan >ls
1.dmp
baan1126_add.dmp
date.tmp
mnt1
Disk1.iso
baan1126_add.log
date.tmp1
mnt2
Disk2.iso
baan8171123_complete.logdate817.tmp
nohup.out
baan1123_complete.dmp baan8171126_add.log impbaaniv1.sh
setcontrolfile.sh
ora817@facmesdb::/u02/xzp/baan >rm 1.dmp
ora817@facmesdb::/u02/xzp/baan >ls
Disk1.iso
baan1126_add.log
date.tmp1
mnt2
Disk2.iso
baan8171123_complete.logdate817.tmp
nohup.out
baan1123_complete.dmp baan8171126_add.log impbaaniv1.sh
setcontrolfile.sh
baan1126_add.dmp
date.tmp
mnt1
ora817@facmesdb::/u02/xzp/baan >exp userid=system/system file=1.dmp inctype=complete;
Export: Release 8.1.7.0.0 - Production on Mon Nov 26 18:34:33 2007
(c) Copyright 2000 Oracle Corporation.All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
Export done in ZHS16GBK character set and ZHS16GBK NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table
DEF$_AQCALL
0 rows exported
. . exporting table
DEF$_AQERROR
0 rows exported
. . exporting table
DEF$_CALLDEST
0 rows exported
. . exporting table
DEF$_DEFAULTDEST
0 rows exported
. . exporting table
DEF$_DESTINATION
0 rows exported
. . exporting table
DEF$_ERROR
0 rows exported
. . exporting table
DEF$_LOB
0 rows exported
. . exporting table
DEF$_ORIGIN
0 rows exported
. . exporting table
DEF$_PROPAGATOR
0 rows exported
. . exporting table DEF$_PUSHED_TRANSACTIONS
0 rows exported
. . exporting table
DEF$_TEMP$LOB
0 rows exported
. . exporting tableSQLPLUS_PRODUCT_PROFILE
0 rows exported
. about to export OUTLN's tables via Conventional Path ...
. . exporting table
OL$
0 rows exported
. . exporting table
OL$HINTS
0 rows exported
. about to export DBSNMP's tables via Conventional Path ...
. about to export TRACESVR's tables via Conventional Path ...
. about to export XZP's tables via Conventional Path ...
. . exporting table
XZP
4 rows exported
. exporting referential integrity constraints
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully without warnings.
2.插入一行，再作增量导出
ora817@facmesdb::/u02/xzp/baan >sqlplus "/as sysdba"
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Nov 26 18:35:00 2007
(c) Copyright 2000 Oracle Corporation.All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
SQL> select * from xzp.xzp;
ID
----------
1
2
3
4
SQL> insert into xzp.xzp values(5);
1 row created.
SQL> select * from xzp.xzp;
ID
----------
1
2
3
4
5
SQL>
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
ora817@facmesdb::/u02/xzp/baan >exp userid=system/system file=2.dmp inctype=incremental;
Export: Release 8.1.7.0.0 - Production on Mon Nov 26 18:36:02 2007
(c) Copyright 2000 Oracle Corporation.All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
Export done in ZHS16GBK character set and ZHS16GBK NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. about to export OUTLN's tables via Conventional Path ...
. about to export DBSNMP's tables via Conventional Path ...
. about to export TRACESVR's tables via Conventional Path ...
. about to export XZP's tables via Conventional Path ...
. . exporting table
XZP
5 rows exported
. exporting referential integrity constraints
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting information about dropped objects
. exporting statistics
Export terminated successfully without warnings.

3.drop table xzp，再把第一份全备份导进去，然后插入其他数据6.

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Nov 26 18:37:17 2007
(c) Copyright 2000 Oracle Corporation.All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
SQL> select * from xzp.xzp;
ID
----------
1
2
3
4
SQL> insert into xzp.xzp values(6);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xzp.xzp;
ID
----------
1
2
3
4
6
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
(c) Copyright 2000 Oracle Corporation.All rights reserved.
4.把增量导出的再增量导进，发现原先插入的6不见了。可以得知，增量导入是先把target database的table的数据删掉，再全导入。
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
Export file created by EXPORT:V08.01.07 via conventional path
import done in ZHS16GBK character set and ZHS16GBK NCHAR character set
. importing XZP's objects into XZP
. . importing table
"XZP"
5 rows imported
Import terminated successfully without warnings.
ora817@facmesdb::/u02/xzp/baan >sqlplus "/as sysdba"
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Nov 26 18:38:34 2007
(c) Copyright 2000 Oracle Corporation.All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
SQL> select * from xzp.xzp;
ID
----------
1
2
3
4
5

千问 · 2007-10-20 08:38:44

急切等待大家的回复！！

千问 · 2007-10-20 08:38:44

很长啊，顶一下

千问 · 2007-10-20 08:38:44

怎么没人??