oracle数据泵导入提示00972,oracle数据库使用expdp指定FLASHBACK_TIME遇到ORA-39150错误 | 信春哥，系统稳，闭眼上线不回滚！...
目前正在做一個數據庫合并的項目，有一套系統使用分庫分表并且讀寫分離的架構，訂單系統和用戶系統都是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数据泵导入提示00972,oracle数据库使用expdp指定FLASHBACK_TIME遇到ORA-39150错误 | 信春哥，系统稳，闭眼上线不回滚！...的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： php detailview,GridV
- 下一篇： linux spyder 目录,linu