Re: Oracle 7.3.3 to 8.0.4 migration using exp/imp -Problems

The WE8ISO8859P1 character set is a superset of the US7ASCII. Conversion is not going to be any problem. In fact, living in Europe, I consider the WE8ISO8859P1 to be the default and I don't create databases in US7ASCII. People who did use the US7ASCII sooner or later regretted using it.
The export problem looks like to be caused by incorrect export/import views. The only nasty problem is the primary key constraint, the rest is not important.
If you run the catexp7 script and retry the import be aware, that upto 7.3.4 the SYSTEM.HELP table doesn't have a primary key. You will have a table full of duplicate records, which however can easily be deleted.

Hth,

Sybrand Bakker, Oracle DBA

On 5 Dec 1998 10:50:03 GMT, "Sanjay Hans" <sanhans_at_mbox5.singnet.com.sg> wrote:

>I am trying to do migration from Oracle 7.3.3 on Digital Alpha unix to
>Oracle 8.0.4 on Digital Alpha NT. I took the full export backup as
>'system' user from Oracle 7.3.3 and installed the Oracle 8.0.4 on NT. I
>created all the required tablespaces/rollback segments in Oracle 8.0.4
>
>For importing the backup a gave the command: -
>Imp80 system/manager file=full.db full=y ignore=y log=full.log
>
>The log files is showing following lines:
>
>Connected to: Oracle8 Enterprise Edition 8.0.4.1.0 - Very Large Memory
>(VLM) - Production
>PL/SQL Release 8.0.4.1.0 - Production
>
>Export file created by EXPORT:V07.03.03 via conventional path
>. importing OEM's objects into OEM
>. importing SYSTEM's objects into SYSTEM
> . importing table "DEF$_CALL" 0 rows imported
> . importing table "DEF$_CALLDEST" 0 rows imported
>IMP-00017: following statement failed with ORACLE error 904:
> "ALTER TABLE "DEF$_CALLDEST" ADD CONSTRAINT "DEF$_CALLDEST_PRIMARY"
>PRIMARY"
> " KEY ("DEFERRED_TRAN_DB","DEFERRED_TRAN_ID","CALLNO","DBLINK") USING
>INDEX "
> "STORAGE (INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE
> 5"
> "0 FREELISTS 1 ) TABLESPACE "SYSTEM""
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_CALLDEST"."DEFERRED_TRAN_DB" IS 'Originating
>datab"
> "ase for the deferred transaction'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_CALLDEST"."DEFERRED_TRAN_ID" IS 'Transaction
>ID'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_CALLDEST"."CALLNO" IS 'Unique ID of call within
>tr"
> "ansaction'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_CALLDEST"."DUMMY_BUFFER_NUMBER" IS 'Used for
>forei"
> "gn key reference only'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
> . importing table "DEF$_DEFAULTDEST" 0 rows imported
> . importing table "DEF$_DESTINATION" 0 rows imported
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_DESTINATION"."LAST_TRAN_ID" IS 'Transaction ID
>of "
> "last transaction propagated'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_DESTINATION"."LAST_TRAN_DB" IS 'Source of last
>tra"
> "nsaction propagated'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_DESTINATION"."LAST_ERROR" IS 'Oracle error
>number "
> "from last push'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_DESTINATION"."LAST_MSG" IS 'Error message from
>las"
> "t push'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
> . importing table "DEF$_ERROR" 0 rows imported
>IMP-00017: following statement failed with ORACLE error 904:
> "ALTER TABLE "DEF$_ERROR" ADD CONSTRAINT "DEF$_ERROR_PRIMARY" PRIMARY KEY
> ("
> ""DEFERRED_TRAN_DB","DEFERRED_TRAN_ID","DESTINATION") USING INDEX STORAGE
>(I"
> "NITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50
>FREELIST"
> "S 1 ) TABLESPACE "SYSTEM""
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_ERROR"."DEFERRED_TRAN_DB" IS 'The database
>origina"
> "ting or copying the deferred transaction'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_ERROR"."DEFERRED_TRAN_ID" IS 'The ID of the
>transa"
> "ction that originated or copied the deferred transaction'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_ERROR"."CALLNO" IS 'Unique ID of call that
>caused "
> "an error'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
>IMP-00017: following statement failed with ORACLE error 904:
> "COMMENT ON COLUMN "DEF$_ERROR"."ERROR_TIME" IS 'Time error occurred'"
>IMP-00003: ORACLE error 904 encountered
>ORA-00904: invalid column name
> . importing table "HELP" 25583 rows imported
> . importing table "PBCATCOL" 0 rows imported
> . importing table "PBCATEDT" 21 rows imported
> . importing table "PBCATFMT" 20 rows imported
> . importing table "PBCATTBL" 0 rows imported
> . importing table "PBCATVLD" 0 rows imported
> . importing table "PRODUCT_PROFILE" 0 rows imported
> . importing table "USER_PROFILE" 0 rows imported
>
>
>The import continued after this, I am not sure what to do now? Can I ignore
>this? I have not run the catexp7.sql on Oracle 8.0.4 before importing. Is
>it required?
>
>Another thing, which is not clear to me is the NLS CHARACTER SET setting.
>The Oracle 7.3.3 was having US7ASCII as the NLS Character set so I have
>created the Database in Oracle 8.0.4 with US7ACSII as NLS Character set.
>The Oracle 8.0.4 is also showing NLS_NCHAR__CHARACTERSET as US7ASCII.
>What is the implications in importing the data, if I create Database in
>Oracle 8.0.4 with character set as WE8ISO8859P1 and
>NLS_NCHAR__CHARACTERSET as WE8ISO8859P1 (please note that Database in
>Oracle 7.3.3 is having US7ASCII as NLS CHARACTER SET).
>
>Would appreciate your help/suggestions on this problem.
>
>Thanks in advance.
>

Sybrand Bakker, Oracle DBA Received on Sat Dec 05 1998 - 06:57:29 CST