importing 8i to 10g

[jennifer2007]

Hi Friends,

I have a full(entire) export backup of my 8i database, i want to import it to
my 10g database but i dont want to include object in SYS or SYSTEM schema.
How can I do this please.

Thanks a lot

[thomasp]

run the import individually for each schema involved without the full=y option.

If you have many schemas involved, you could just put them all in a script and run.

[jennifer2007]

Thanks dear....


I tried what u said dear, so I started import a single user first, the one with
has the most number of tables in it, probably the entire owner of the
application. After I finished importing this schema, I was surprise to get
an ORA-600 error

I can not login to any user now....what could be wrong?

=============
C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 15 22:16:29 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected.
SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-01012: not logged on


SQL> shutdown immediate;
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-00600: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [
%s], [%s]
SQL>

[PAVB]

Please post export/import scripts and logs

[jennifer2007]

Hi dear,

Its just normal export/import scripts and logs i do and see all the time
except this table OTHERS wich got export error:


. . exporting table OTHERNAMES 45 rows exported
. . exporting table OTHERNAMES_TEMP 0 rows exported
. . exporting table OTHERS
EXP-00056: ORACLE error 1591 encountered
ORA-01591: lock held by in-doubt distributed transaction 1.17.57220
. . exporting table OTHERS2 0 rows exported

My suspect is this in-doubt transation caused the ora-600.
How can I skip this table?
How can I recover my database from ora-600 error? Its just newly installed
one, or I may have to recreate it.
Whats the command line again do delete/drop a database? I want to recreate
and reuse all the existing tablespaces I have.


Thanks

[Bore]

ora-600 - I guess you know what that means?
To be more precise, 'Internal Oracle error. Contact Oracle support'
Although Oracle will tell you that this is not allways a bug, in 80 to 90% of the situations that is considered to be a bug

Regarding the import
SYS is NEVER imported even with full import
SYSTEM is imported however, so you should do something about that.
I think you can specify list of users to import in the imp prompt

Regards
Boris

[PAVB]

Jenn --

It's very unlikely a export failure will cause ORA-600 when importing dump file.

There are two courses of action here:
1) find out what went wrong and then try to fix it
2) give up and recreate you instance

I would choose #1, if you do, can you please:
-- post Export script
-- post Import script
-- post Export log
-- post Import log
-- tell us O/S and Oracle versions

If you do: ps -ef | grep smon ... can you see your smon instance active?

Last but not least, do you have access to Metalink? there is a whole section and tool devoted to identify and troubleshoot ORA-600 error.

[dbasan]

I think you should resolve the in-doubt transaction first.

EXP-00056: ORACLE error 1591 encountered
ORA-01591: lock held by in-doubt distributed transaction 1.17.57220
. . exporting table OTHERS2 0 rows exported

My suspect is this in-doubt transation caused the ora-600.

Yes you may be right. Why not double check!

How can I skip this table?

Choose all the tables you want to export leaving the one you do not need.

How can I recover my database from ora-600 error?

I think when you resolve the in-doubt ora-600 may go off.

Its just newly installed one, or I may have to recreate it.

Mostly not needed, resolving in-doubt would get back the database.

I hope the db is up and running. Check the dba_2pc_pending table for any values for the in-doubt transaction. Then try to rollback the transaction which removes the entry from the dba_2pc_pending. Restart the database would get back your database to normal.

Why don't you give a try?

[jennifer2007]

Thanks hunnies
This in-doubt transaction is really a pain in the neck....we haven't resolved
it long time ago.

And the ora-600 too. My only choice was to recreate the database, reinstall
everything, because it wont allow u to create another db or instance since
all the db scripts will hit the ora-600. good thing Its a first installation.

So after re-installing 10g i imported it per user per table and skip the in-doubt
table and then all is fine now