The SH. COUNTRIES table is required for the 4 sub-questions in session4. When a user creates a database manually, the sh user wants to copy the table from another place without this table. Database of the original exported table: databaseORACLE_SIDTEST0221 version: 11.2.0.4 the database to be imported: databaseORACLE_SIDPROD version: 10.2.0.1 due to Character Set Issues,
The SH. COUNTRIES table is required for the 4 sub-questions in session4. When a user creates a database manually, the sh user wants to copy the table from another place without this table. Database ORACLE_SID = TEST0221: 11.2.0.4: database ORACLE_SID = PROD: 10.2.0.1 due to Character Set Issues,
The SH. COUNTRIES table is required for the 4 sub-questions in session4. When a user creates a database manually, the sh user wants to copy the table from another place without this table.
Database ORACLE_SID = TEST0221: 11.2.0.4: database ORACLE_SID = PROD: 10.2.0.1 due to Character Set Issues, an error with the EXP-00091 occurred because of a database version issue that caused the IMP-00010 Error
[Oracle @ node2 ~] $
Exp sh/sh @ test0221 tables = COUNTRIES rows = y file = COUNTRIES. dmp
Export: Release 11.2.0.4.0-Production on Thu Mar 27 15:19:07 2014
Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-ProductionWith the Partitioning, OLAP, data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path... exporting table COUNTRIES 23 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.Export terminated successfully with warnings. [oracle @ node2 ~] $ Oerr exp 0009100091,000 00, "Exporting questionable statistics. "// * Cause: Export was able export statistics, but the statistics may not be // usuable. the statistics are questionable because one or more of // the following happened during export: a row error occurred, client // character set or NCHARSET does not match with the server, a query // clause was specified on export, only certain partition S or // subpartitions were exported, or a fatal error occurred while // processing a table. // * Action: To export non-questionable statistics, change the client character // set or NCHARSET to match the server, export with no query clause, // export complete tables. if desired, import parameters can be // supplied so that only non-questionable statistics will be imported, // and all questionable statisti Cs will be recalculated. [oracle @ node2 ~] $ Exitexit
Sys @ TEST0221> select * from nls_database_parameters t where t. parameter = 'nls _ CHARACTERSET ';
Parameter value ------------------------------ ------------------------------------------------------------------- NLS_CHARACTERSET AL32UTF8
Or, view the command sys @ TEST0221> select * from v $ nls_parameters where parameter = 'nls _ CHARACTERSET ';
Parameter value parameter ------------------------------------------------------------------ NLS_CHARACTERSET AL32UTF8
Sys @ TEST0221>! [Oracle @ node2 ~] $
Export NLS_LANG = AMERICAN_AMERICA.AL32UTF8 -- set the character set[Oracle @ node2 ~] $
Exp sh/sh @ test0221 tables = COUNTRIES rows = y file = COUNTRIES. dmp-- Re-export. The operation is successful.
Export: Release 11.2.0.4.0-Production on Thu Mar 27 15:22:32 2014
Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path... exporting table COUNTRIES 23 rows exported
Export terminated successfully without warnings.
[Oracle @ node2 ~] $ Lltotal 480-rw-r -- r -- 1 oracle oinstall 438823 Mar 14 awrrpt_1_33_37.html-rw-r -- r -- 1 oracle oinstall 24576 Mar 27
COUNTRIES. dmp-Rw-r -- 1 oracle oinstall 2992 Feb 24 login. SQL-rw-r -- 1 oracle oinstall 195 Mar 14 spcpkg. lis-rw-r -- 1 oracle oinstall 5477 Mar 14. lis-rw-r -- 1 oracle oinstall 1832 Mar 14 spcusr. lis
[Oracle @ node2 ~] $
Scp COUNTRIES. dmp 192.168.1.155:/home/oracle--- Use scp to transfer the file to the system of the library to be imported oracle@192.168.1.155's password: COUNTRIES. dmp 100% 24KB 24.0KB/s [oracle @ node2 ~] $
View the files just transferred by scp in the environment where you want to import the table:
[Oracle @ ocm1 ~] $ Lltotal 32-rw-r -- r -- 1 oracle oinstall 24576 Mar 27 COUNTRIES. dmpdrwxr-xr-x 2 oracle oinstall 4096 Mar 27 39 scriptdrwxr-xr-x 2 oracle oinstall 4096 Mar 19 temp
######################################## #############################
The following is the import:
[Oracle @ ocm1 ~] $ Imp
Import: Release 10.2.0.1.0-Production on Thu Mar 27 15:31:30 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Username: shPassword:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-ProductionWith the Partitioning, OLAP and Data Mining options
Import file: expdat. dmp>/home/oracle/COUNTRIES. dmp
Enter insert buffer size (minimum is 8192) 30720>
IMP-00010: not a valid export file, header failed verificationIMP-00000: Import terminated unsuccessfully [oracle @ ocm1 ~] $
The header verification fails because of different version numbers. You can modify the header information by using the following methods: TEXPORT: V11.01.00, which is the version number of the source database, change it to the version number of the target database, for example, 10.02.01 on the local machine. Import the database again. [oracle @ ocm1 ~] $
Vi COUNTRIES. dmp -- directly use vi to modify the version number of the header.^ C ^ CiEXPORT: V10.02.00 -- previously EXPORT: V11.02.00USH
Import again. Successful:
[Oracle @ ocm1 ~] $ Imp
Import: Release 10.2.0.1.0-Production on Thu Mar 27 15:36:22 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Username: shPassword:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-ProductionWith the Partitioning, OLAP and Data Mining options
Import file: expdat. dmp>/home/oracle/COUNTRIES. dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT: V10.02.00 via conventional pathimport done in AL32UTF8 character set and AL16UTF16 NCHAR character setList contents of import file only (yes/no): no>
Ignore create error due to object existence (yes/no): no>
Import grants (yes/no): yes>
Import table data (yes/no): yes>
Import entire export file (yes/no): no> Username: sh
Enter table (T) or partition (T: P) names. Null list means all tables for userEnter table (T) or partition (T: P) name or. if done:
. Importing SH's objects into SHIMP-00008: unrecognized statement in the export file :. importing SH's objects into SH .. importing table "COUNTRIES" 23 rows importedImport terminated successfully with warnings. [oracle @ ocm1 ~] $
SYS @ PROD> conn sh/shConnected. SH @ PROD> desc COUNTRIES Name Null? Type calculated -------- country COUNTRY_ID not null number COUNTRY_ISO_CODE not null char (2) COUNTRY_NAME not null VARCHAR2 (40) COUNTRY_SUBREGION not null VARCHAR2 (30) could not null number COUNTRY_REGION not null VARCHAR2 (20) COUNTRY_REGION_ID not null number COUNTRY_TOTAL not null VARCHAR2 (11) COUNTRY_TOTAL_ID not null number COUNTRY_NAME_HIST VARCHAR2 (40)
SH @ PROD> select count (*) from COUNTRIES;
COUNT (*) ---------- 23
