分区表备份恢复测试

最新推荐文章于 2023-08-18 17:23:53 发布

转载最新推荐文章于 2023-08-18 17:23:53 发布 · 125 阅读

1.用sys用户登陆建立目录
create directory exptest_gu as '/oracle/expdumpdir';
2.给要导出数据的用户有使用这个目录的权限
grant read,write on directory exptest_gu to vgopdw;
3.导出数据

[@more@]

expdp vgopdw/vgopdw directory=exptest_gu tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003 dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp job_name=news_1007
4.尝试带表元数据导入
impdp vgopdw/vgopdw directory=exptest_gu dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp logfile=test.log tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003

Import: Release 11.1.0.7.0 - 64bit Production on Monday, 12 July, 2010 16:01:01

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "VGOPDW"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "VGOPDW"."SYS_IMPORT_TABLE_01": vgopdw/******** directory=exptest_gu dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp logfile=test.log tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "VGOPDW"."TDW_MANUAL_TV_ACT_USER_M" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "VGOPDW"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:01:03

导入出错，表已经存在，说明如果表结构已经存在，是不能整表元数据导入的
5.只导入数据，但不清除原有数据
impdp vgopdw/vgopdw directory=exptest_gu dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp logfile=test.log tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003 content=data_only

Import: Release 11.1.0.7.0 - 64bit Production on Monday, 12 July, 2010 16:13:28

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "VGOPDW"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "VGOPDW"."SYS_IMPORT_TABLE_01": vgopdw/******** directory=exptest_gu dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp logfile=test.log tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003 content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "VGOPDW"."TDW_MANUAL_TV_ACT_USER_M":"TV_ACT_M_201003" 257.8 MB 5977534 rows
Job "VGOPDW"."SYS_IMPORT_TABLE_01" successfully completed at 16:13:50

成功导入，但此分区的数据量是原来的二倍

6.尝试截断此分区后再导入
alter table TDW_MANUAL_TV_ACT_USER_M truncate partition TV_ACT_M_201003
此分区被截断

impdp vgopdw/vgopdw directory=exptest_gu dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp logfile=test.log tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003 content=data_only

Import: Release 11.1.0.7.0 - 64bit Production on Monday, 12 July, 2010 16:13:28

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "VGOPDW"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "VGOPDW"."SYS_IMPORT_TABLE_01": vgopdw/******** directory=exptest_gu dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp logfile=test.log tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003 content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "VGOPDW"."TDW_MANUAL_TV_ACT_USER_M":"TV_ACT_M_201003" 257.8 MB 5977534 rows
Job "VGOPDW"."SYS_IMPORT_TABLE_01" successfully completed at 16:13:50

select count(*) from TDW_MANUAL_TV_ACT_USER_M a where a.statis_month = 201003 --5977534
数据成功导入

7.尝试删除此分区再导入
alter table TDW_MANUAL_TV_ACT_USER_M drop partition TV_ACT_M_201003

oracle@cp-cs02 /oracle/expdumpdir $ impdp vgopdw/vgopdw directory=exptest_gu dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp logfile=test.log tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003 content=data_only

Import: Release 11.1.0.7.0 - 64bit Production on Monday, 12 July, 2010 16:38:33

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "VGOPDW"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "VGOPDW"."SYS_IMPORT_TABLE_01": vgopdw/******** directory=exptest_gu dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp logfile=test.log tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003 content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "VGOPDW"."TDW_MANUAL_TV_ACT_USER_M":"TV_ACT_M_201003" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-14400: inserted partition key does not map to any partition
Job "VGOPDW"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:38:35

由于分区已经被删除，所以导入失败，尝试创建该分区后再导入

alter table TDW_MANUAL_TV_ACT_USER_M
add PARTITION TV_ACT_M_201003 VALUES (201003)

创建该分区

Import: Release 11.1.0.7.0 - 64bit Production on Monday, 12 July, 2010 16:45:19

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "VGOPDW"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "VGOPDW"."SYS_IMPORT_TABLE_01": vgopdw/******** directory=exptest_gu dumpfile=TDW_MANUAL_TV_ACT_USER_M_TEST.dmp logfile=test.log tables=TDW_MANUAL_TV_ACT_USER_M:TV_ACT_M_201003 content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "VGOPDW"."TDW_MANUAL_TV_ACT_USER_M":"TV_ACT_M_201003" 257.8 MB 5977534 rows
Job "VGOPDW"."SYS_IMPORT_TABLE_01" successfully completed at 16:45:31

导入成功

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7490392/viewspace-1037426/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7490392/viewspace-1037426/