export 9.2 import 9.1

[SirCharles]

How to export from 9.2 and use the dmp file to import
to 9.1?

Charlie
9/7/04

 

[SantaMufasa]

Charles,

The Oracle rule of thumb is, you cannot import a dump file that was created with a version of "exp" that is newer than the import-target database.

Therefore, in your case, you should use a version of "exp" that is for Oracle 9.1 or earlier to export from the 9.2 database. You can then import the resulting dump file into your 9.1 database.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:41 (08Sep04) UTC (aka "GMT" and "Zulu"), 17:41 (07Sep04) Mountain Time)

 

[SirCharles]

Tried that and got a 942 error.

Charlie
9/7/04

 

[SirCharles]

Dave,
Here is some more detail. I wonder if I should run the catexp.sql package from the target box on the source?

Invoking exp from box with 9.1 DB on it where tnsnames.ora has
reference to source db, where source db resides on another box
and is v 9.2:

exp user_schema/password@source_sid


Export: Release 9.0.1.5.0 - Production on Tue Sep 7 18:57:36 2004

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp >

(2)U(sers), or (3)T(ables): (2)U > U

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user INET
. exporting object type definitions for user INET
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully


NOTE: Got same error as above after running @catexp.sql under sys as sysdba
account on target box. Though error message seems to be coming from target
login schema.
NOTE: Was able to get export/import to work when going same source, ie. box with 9.2,
and other box with 9.2 as target.

Charlie
9/7/04

 

[SantaMufasa]

Charlie,

Give some hints here...is that an "ORA-00942 Table or View does not exist"? If that is the case, I presume it happened on import, which typically means that the export STILL occurred with a version of "exp" that was newer than your target database.

So we can help you troubleshoot this, could you please either show the commands you used to ensure you used at most an Oracle 9.1 version of "exp" (preferrable) or explain what you did to achieve the correct result?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:23 (08Sep04) UTC (aka "GMT" and "Zulu"), 20:23 (07Sep04) Mountain Time)

 

[SirCharles]

Yes, that is an ORA-00942 error. It occurred as I attempted to export data on the source 9.2 db using
the 9.1 exp utility. I did this by setting the ORACLE_SID
to the source instance on another box (using tnsnames entry) and then executing the exp command on the 9.1 target box.

As shown above, the exp command above yields an initial
'Export: Release 9.0.1.5.0' which indicates the version of exp used on the target box is below 9.1.

The next string is output from the exp command, as well,
and indicates the version of the source DB.

'Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0...'

Charlie
9/7/04

 

[SantaMufasa]

Charlie,

Sorry I didn't look closer earlier. It appears to me that you did everything right. If time is of the essence on this issue, I believe it is well qualified for a MetaLink incident. Do you have access to MetaLink?

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:23 (08Sep04) UTC (aka "GMT" and "Zulu"), 22:23 (07Sep04) Mountain Time)

 

[SirCharles]

Yes, time is of the essence, and I do have metalink access.
Sounds like a good idea. Thanks for the advice.

Charlie
9/7/04

 

[SantaMufasa]

Charlie,

Once OTS helps you resolve this (how's that for Positive Mental Attitude?), could you please advise us the outcome? I'm sure others will be running into this issue, as well, and appreciate the wisdom you'll have.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:32 (08Sep04) UTC (aka "GMT" and "Zulu"), 22:32 (07Sep04) Mountain Time)

 

[SirCharles]

Sure. In the mean time, we may be able to solve our
current issue by installing two instances of Oracle
on the target, one running 9.1 (for Oracle Portal 10G)
and the other running 9.2 so we can port some other
data to the same box.

Where might I find procedure to do that?

Charlie
9/8/04

 

[SantaMufasa]

Charlie,

When you ask, "Where might I find procedure to do that?", what specifically is "that"? If you are referring to having two versions of Oracle on the same box, you do an install (from CD) and patching (if necessary) of the version you are missing. You ensure that the ORACLE_HOME that you specify is is different from the other ORACLE_HOME. For example, if the first ORACLE_HOME is something like ".../oracle/app/oracle/product/9.0.1", then specify the new ORACLE_HOME as ".../oracle/app/oracle/product/9.2.0". Then after creating the database, you should have two entries in your "/var/opt/oracle/oratab" (or "/etc/oratab" if not using Solaris) that reflects both database instances.

Let us know if this did (or did not) answer your question(s).

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:01 (08Sep04) UTC (aka "GMT" and "Zulu"), 09:01 (08Sep04) Mountain Time)

 

[Turkbear]

Hi, I'm a little confused by your statement :

I did this by setting the ORACLE_SID
to the source instance on another box (using tnsnames entry) and then executing the exp command on the 9.1 target box.



With the command line you used, there is no need to set the ORACLE_SID to anything..The
exp user_schema/password@sourcesid

will connect you to the sourcesid ( the tnsnames entry
for the 9.2 instance) - It is possible the using ORACLE_SID confused the 9.1 EXP command.

Be sure catexp.sql has been run on both boxes..
( I am not sure 9.2 and 9.1 need separate EXP commands, usually that only applies to Major release Numbers - 8.1 to 9.1 for instance)
I suspect the catexp.sql ( or some variant of it) is the root cause of the error.

 

[SirCharles]

Do I have to specify a different port in the tnsnames
for the 9.2 version?

 

[SantaMufasa]

Charlie,

No. One port and one listener can take care of virtually any number of Oracle instances on the same machine.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:09 (09Sep04) UTC (aka "GMT" and "Zulu"), 17:09 (08Sep04) Mountain Time)

 

[SirCharles]

Metalink hasn't given a solution yet. I wonder if we could renew efforts here?

Charlie
10/11/04

 

[SantaMufasa]

SirCharles,

Just to be certain that everything is kosher, on each of your two databases, please re-run the respective versions of catalog.sql, catproc.exp, and catexp.sql. As you are probably aware, there is no harm in re-running these scripts, and in doing so, it will ensure that the proper structures are in place for export and import. Then, using Oracle 9.1's "exp" binaries, export the objects you wish from your 9.2 database, then "imp" the dumpfile into your 9.1 database.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 04:43 (12Oct04) UTC (aka "GMT" and "Zulu"), 21:43 (11Oct04) Mountain Time)

 

[SirCharles]

On 9.2 instance/box I got some errors when running each of the three scripts.
Also got errors on each of the three scripts on the 9.1 instance. Some of
those errors were as follows:
create public synonym dbms_application_info for sys.dbms_application_info
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
CREATE TABLE sys.rule_set$ (
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


CREATE UNIQUE INDEX sys.i_rule_set on sys.rule_set$(obj#)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


CREATE TABLE sys.rule$(
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


CREATE UNIQUE INDEX sys.i_rule on sys.rule$(obj#)
*
...

etc, etc, etc.



From the 9.1 box issued command exp user_schema/password@9.2sid
...
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user INET_APP
. exporting object type definitions for user INET_APP
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully

Charlie
10/13/04

 

[SirCharles]

Update: I'm still looking at this one and working with metalink.

Charlie
10/21/04

 

[KenCunningham]

SirCharles - did you ever get a resolution for this?

 

[SirCharles]

Latest from Oracle implies maybe applying 9205 pathset on 9201 base, run catpatch.sql in migration mode, catexp.sql and then try the export again. Those instructions not really clear to me. I'll try and get clarification how to run in migration mode.

Charlie
11/19/04