oracle数据库使用expdp指定FLASHBACK_TIME遇到ORA-39150错误
Mar222017
目前正在做一个数据库合并的项目，有一套系统使用分库分表并且读写分离的架构，订单系统和用户系统都是6个写库和6个读库，目前订单系统已经由6个写库合并为3个写库，马上要做用户系统的数据库合并工作，用户系统使用到了OGG，在搭建测试环境时，也要搭建OGG环境。
再搭建用户系统的测试数据库时，使用了数据泵的方式，因为要尽量保证所有数据库的数据尽量接近同一时间，指定了FLASHBACK_TIME选项，再导出数据时，遇到了ORA-39150错误。
[oracle@SL010M6-DB-USER1 ~]$ expdp MEDA_USER1/xxxxxxxxxx DIRECTORY=expbak DUMPFILE=user1.dmp FLASHBACK_TIME=\"TO_TIMESTAMP\('2017-03-20 10:25:00', 'YYYY-MM-DD HH24:MI:SS'\)\"
Export: Release 11.2.0.4.0 - Production on Mon Mar 20 10:36:32 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-00907: missing right parenthesis
使用parfile的方式同样也遇到这个错误。
[oracle@SL010M6-DB-USER1 ~]$ vi p1.par
DIRECTORY=expbak
DUMPFILE=user1.dmp
FLASHBACK_TIME=TO_TIMESTAMP"('2017-03-20 10:10:00','YYYY-MM-DD HH24:MI:SS')"
[oracle@SL010M6-DB-USER1 ~]$ expdp MEDA_USER1/xxxxxxxxxxx parfile=p1.par
Export: Release 11.2.0.4.0 - Production on Mon Mar 20 10:17:57 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-00972: identifier is too long
查了下资料，发现使用expdp指定FLASHBACK_TIME选项的时候，绝大多数情况都会遇到这个问题，而且没有找到好一些的解决方法，因为我的需求是6个数据库都要指定相同的时间，所以使用FLASHBACK_TIME=to_timestamp(localtimestamp)和FLASHBACK_TIME=to_timestamp_tz(systimestamp)的方法是不行的，这都不能保证6个数据库指定的都是同一时间，所以将FLASHBACK_TIME的方式改成了FLASHBACK_SCN的方式，这样就需要在6个数据库查到同一时间的SCN，然后分别按照对应的SCN导出数据就可以了。
可以通过timestamp_to_scn函数来查看具体的时间对应的SCN值。
SQL> col scn for 9999999999999999
SQL> select timestamp_to_scn(to_timestamp('2017-03-20 10:25:00','yyyy-mm-dd hh24.mi.ss')) as scn from dual;
SCN
------------------
149893337269
然后在通过FLASHBACK_SCN的方式导出数据，再测试数据库中导入就可以了。
[oracle@SL010M6-DB-USER1 ~]$ expdp MEDA_USER1/xxxxxxxxx DIRECTORY=expbak DUMPFILE=user6.dmp FLASHBACK_SCN=149893337269 Export: Release 11.2.0.4.0 - Production on Mon Mar 20 10:31:32 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "MEDA_USER1"."SYS_EXPORT_SCHEMA_01": MEDA_USER1/******** DIRECTORY=expbak DUMPFILE=user6.dmp FLASHBACK_SCN=149893337269 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 2.499 GB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "MEDA_USER1"."ZT_USR_M" 546.0 MB 2442145 rows . . exported "MEDA_USER1"."ZT_USR_M_0928" 516.8 MB 2442145 rows . . exported "MEDA_USER1"."APP_SIGN_HISTORY" 451.6 MB 13544918 rows . . exported "MEDA_USER1"."ZT_USR_SHIPPING_INFO_20170222" 206.2 MB 951391 rows . . exported "MEDA_USER1"."ZT_USR_SHIPPING_INFO" 168.0 MB 784397 rows . . exported "MEDA_USER1"."ZT_USR_UGO_E" 115.1 MB 2442145 rows . . exported "MEDA_USER1"."IF_USER_TICKETS" 45.59 MB 755073 rows . . exported "MEDA_USER1"."ZT_SHOW_USRSIGN" 40.03 MB 108375 rows . . exported "MEDA_USER1"."ZT_USR_REGIS" 22.40 MB 472984 rows . . exported "MEDA_USER1"."ZT_USR_KEY" 20.33 MB 883720 rows . . exported "MEDA_USER1"."IF_USER_POINT" 9.007 MB 191636 rows . . exported "MEDA_USER1"."ZT_USR_IDCARD" 3.753 MB 36596 rows . . exported "MEDA_USER1"."ZT_USR_MOBILE_FEEDBACK" 3.337 MB 17573 rows . . exported "MEDA_USER1"."ZT_USR_NEW_FEEDBACK" 1.329 MB 7995 rows . . exported "MEDA_USER1"."ELECTRONIC_INVOICE_MOBILE" 12.34 KB 146 rows . . exported "MEDA_USER1"."GG_HEARTBEAT" 5.835 KB 1 rows . . exported "MEDA_USER1"."UC_MIDDLE_USER" 6.679 KB 0 rows . . exported "MEDA_USER1"."ZT_USR_AWARD_ROLE" 8.335 KB 1 rows . . exported "MEDA_USER1"."ZT_USR_ADDR_WHITE" 0 KB 0 rows . . exported "MEDA_USER1"."ZT_USR_UPDATE_TMP" 0 KB 0 rows Master table "MEDA_USER1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for MEDA_USER1.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/expbk/user6.dmp Job "MEDA_USER1"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Mar 20 10:31:57 2017 elapsed 0 00:00:23
oracle数据库使用expdp指定FLASHBACK_TIME遇到ORA-39150错误：等您坐沙发呢！
发表评论
