Production Environment:Source database: RHEL + Oracle 11.2.0.3Target database: HP-UX + Oracle 10.2.0.4Requirements: Migrate part of the table 11.2.0.3-->10.2.0.4, if some tables within the migration scope already exist in the target library, then replace. This migration data volume <1g.
Initial scenario: the lower version of the client connects to the high-version database, with the lower version exported, and the lower version imported. 1. Using the initial plan, the target database is located in the server connected to the source database, EXP export process error.
Zjcrnopdb $: Sqlplus-Versionsql*plus:release10.2.0.4.0-productionzjcrnopdbPanax Notoginseng: MoreMod_homepage.SHExp Crnop/[email protected] parfile=/usr3/orabak2/par_mod_homepagezjcrnopdb -: MorePar_mod_homepagefile=/usr3/orabak2/mod_homepage.dmp Log=/usr3/orabak2/Mod_homepage.logtables=... ,......, mod_homepage_alarm_stat_infozjcrnopdb the:./mod_homepage.SHExport:release10.2.0.4.0-Production on Wednesday May + -: the: - theCopyright (c)1982, -, Oracle. All rights reserved. Connect to: Oracle Database 11g Enterprise Edition Release11.2.0.3.0-64bit Productionwith The partitioning, Automatic Storage Management, OLAP, Data miningand Real Application Testing op tions exported ZHS16GBK character set and Al16utf16 NCHAR character set are about to export the specified table through the regular path ..... Exporting table Mod_homepage_bts_check_detail Export3502523line ... exporting a table Mod_homepage_notice_type export7Line Exp-00008: ORACLE Error encountered1455ORA-01455: Convert column overflow integer data type exp-00000: Export termination failed zjcrnopdb +:
Encountering ORA-01455, proving that there is a problem with the initial plan, low-version clients cannot export high-version data. A replacement scenario is required.2. Using the EXPDP/IMPDP migration scheme:2.1 EXPDP Export plus parameter version=10.2.0.4.0 on the source database server
More par_mod_homepage Directory=jydumpfile=mod_homepage.dmp logfile=mod_ Homepage.logversion=10.2. 0.4. 0 Tables= more expdp_mode_homepage. SH EXPDP crnop/password parfile=par_mod_homepage
The DIRECTORY=JY library already exists and does not need to be established. Run the export script directly to export the data.
[Email protected] jy]$./expdp_mode_homepage.SHExport:release11.2.0.3.0-Production on Wed + -: +: - theCopyright (c)1982, ., Oracle and/or its affiliates. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release11.2.0.3.0-64bit Productionwith The partitioning, Automatic Storage Management, OLAP, Data miningand Real Application Testing op Tionsstarting"Crnop"."sys_export_table_08": Crnop/*
2.2 In the target database IMPDP import, because the requirement is to replace the existing table, so add the following parameter: Table_exists_action=replaceTable_exists_action the action to take when an imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP], and TRUNCATE. Create your directory, and then you will import the DMP file CP into this directory.
SQL>Createorreplaceas'/usr3/orabak2/' ;
Zjcrnopdb $: Moreimpdp*IMPDP Crnop/password directory=jy dumpfile=mod_homepage.dmp Logfile=impdp_mod_homepage.log TABLE_EXISTS_ACTION=Replace zjcrnopdb About:./impdp_mod_homepage.SHImport:release10.2.0.4.0-64bit Production on Wednesday, +May, the -: the: inCopyright (c)2003, -, Oracle. All rights reserved. Connect to: Oracle Database 10g Enterprise Edition Release10.2.0.4.0-64bit Productionwith The partitioning, OLAP, Data Mining and Real application testing options loaded successfully/uninstalled the Main table"Crnop"."sys_import_full_01"Start"Crnop"."sys_import_full_01": Crnop/******** directory=jy dumpfile=mod_homepage.dmp logfile=impdp_mod_homepage.log table_exists_action=replace Handles object type Table_export/table/table handles object type Table_export/table/table_data. . "Crnop" was imported. " Mod_homepage_bts_check_detail "291.5 MB 3502523 rows ... Handling object Type Table_export/table/comment Processing object type table_export/table/index/indexora-31684: Object type INDEX: "Crnop". " Pk_mod_wf_topn_problem "already exists processing object type table_export/table/constraint/constraintora-31684: Object type CONSTRAINT:" Crnop "." Pk_mod_wf_topn_problem "Processing object type table_export/table/index/statistics/index_statisticsora-39111: Skipped dependent object type INDEX_ STATISTICS, Base Object type INDEX: "Crnop". " Pk_mod_wf_topn_problem "The Processing object type Table_export/table/statistics/table_statistics job" Crnop "already exists." Sys_import_full_01 "has been completed, but there are 3 errors (completed at 14:46:54)
Import success, the above error is the index of the non-canonical naming cause the Index object name, the name of the reconstruction under the index can be, and this case is not related.3. Summary: High-and low-version migration data, the migration scenario is 11g->10g, it is recommended to replace the traditional Exp/imp EXPDP/IMPDP way, EXPDP from the high version of the export to add the parameter version=10g version number.