OraFAQ Forum: Server Utilities » Oracle 9i V/s Oracle 10g Import / Export Problem

Home » RDBMS Server » Server Utilities » Oracle 9i V/s Oracle 10g Import / Export Problem (oracle 9i windows / oracle 10g in linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle 9i V/s Oracle 10g Import / Export Problem [message #547486]

Wed, 14 March 2012 07:42

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

Dear Friend

i successfully export database file like this command
Oracle 9i
exp system/manager file=sysbk.dmp full=Y

when i import this file in oracle 10g then show this error
how to resolve this error & import complete database.

please reply must export i am your always thankful.

Error Start:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.00.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE UNDO TABLESPACE "UNDOTBS" BLOCKSIZE 4096 DATAFILE 'E:\RUNING_ORACLE"
"\ORADATA\ORACLE\UNDOTBS01.DBF' SIZE 209715200 AUTOEXTEND ON NEXT 5242"
"880 MAXSIZE 16383M EXTENT MANAGEMENT LOCAL "
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE TABLESPACE "CWMLITE" BLOCKSIZE 4096 DATAFILE 'E:\RUNING_ORACLE\ORAD"
"ATA\ORACLE\CWMLITE01.DBF' SIZE 20971520 AUTOEXTEND ON NEXT 655360 MA"
"XSIZE 16383M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT "
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes

[EDITED by LF: applied [SPOILER] tags to cut a long story short]

[Updated on: Wed, 14 March 2012 15:44] by Moderator

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547492 is a reply to message #547486]

Wed, 14 March 2012 07:57

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Your 9i database has block of size 4K and your 10g block of size 8K (most likely) so import can't create the tablespaces.
The solution is to precreate them and then to import using "ignore=y".

I also advise you to NOT full import but precreates the users and import schema by schema omitting Oracle ones (SYSTEM, WKSYS...)

Regards
Michel

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547544 is a reply to message #547492]

Wed, 14 March 2012 11:48

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

can you please explain here how to import and export
i am your always thankful

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547545 is a reply to message #547544]

Wed, 14 March 2012 11:55

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You already exported, this is done.
At SQL prompt
- create the tablespaces that exist in the source database: CREATE TABLESPACE...
- create your users that exist in the source database (not system/Oracle ones): CREATE USER...
- create the roles that exist in the source database: CREATE ROLE...
- grant the system privileges and roles to the roles as they are in the source db: query ROLE_SYS_PRIVS and ROLE_ROLE_PRIVS
- grant the roles and system privileges to the users as they are in the source db: query DBA_ROLE_PRIVS and DBA_SYS_PRIVS

Then, import each user:
imp system/manager file=sysbk.dmp fromuser=<user1> touser=<user1>
imp system/manager file=sysbk.dmp fromuser=<user2> touser=<user2>
...

Regards
Michel

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547546 is a reply to message #547545]

Wed, 14 March 2012 12:00

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

i understand but it is big problem here

i have many users in database,
your advise required very long period and i have short of time
can you please advise me without touser or fromuser import all users data.

again i am your thankful.

[Updated on: Wed, 14 March 2012 12:02]

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547551 is a reply to message #547546]

Wed, 14 March 2012 12:28

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do you really have many users owning objects?
These are the only one you have to import.

In addition you can launch several import jobs in parallel and so you may use less time than we a lone complete import.

Regards
Michel

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547554 is a reply to message #547551]

Wed, 14 March 2012 12:45

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

YES I HAVE MANY USER UNDER THE SYSDBA

I ALWAYS FULL BACKUP EXPORT AND RE-INSTALL WINDOWS THEN I IMPORT FULL BACKUP BUT 9I TO 9I BUT 10G NOT ACCEPT FULL BACKUP CAN YOU HAVE ANY SOLUTION.

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547556 is a reply to message #547554]

Wed, 14 March 2012 12:51

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do not post in UPPER case, this is considering as shouting and rude.

Quote:

CAN YOU HAVE ANY SOLUTION.

And what did I explain and describe in my posts?

Regards
Michel

[Updated on: Wed, 14 March 2012 12:51]

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547557 is a reply to message #547554]

Wed, 14 March 2012 12:53

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

YES I HAVE MANY USER UNDER THE SYSDBA

This is:
1/ VERY bad
2/ Irrelevant to the question

I asked about OWNERS, those that OWN objects.

Regards
Michel

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547558 is a reply to message #547557]

Wed, 14 March 2012 13:00

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

I asked about OWNERS, those that OWN objects.

sorry sir but i dont understand what are you asking me.

all company data not my personal

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547559 is a reply to message #547558]

Wed, 14 March 2012 13:05

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So start with the begin.
Do you what is a database schema?
A schema is a set of objects like tables, indexes, procedures... that is owned by an account (a user) and has the name of this user.
For instance, to a user named SCOTT is associtaeed a schema also named SCOTT which contains the objects of SCOTT.
"Owners" are these users/accounts which schema contains at least one object like a table...

Regards
Michel

[Updated on: Wed, 14 March 2012 13:05]

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547562 is a reply to message #547559]

Wed, 14 March 2012 13:31

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

ok i tell you i was how to create user/account
create user abc identified by abc
grant dba, connect to abc;

how to know that is witch schema or owner
all user create with this query

[Updated on: Wed, 14 March 2012 13:31]

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547563 is a reply to message #547562]

Wed, 14 March 2012 13:47

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

biscaly problem is here i am ocp developer i am not dba administrator our dba administrator without notice leave the job thats why i am in problem you simply help just import the all users in 10g we can do it

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547571 is a reply to message #547563]

Wed, 14 March 2012 15:32

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

how to know that is witch schema or owner

grep 'CREATE USER' sysbk.dmp

Regards
Michel

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547689 is a reply to message #547571]

Thu, 15 March 2012 15:09

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

any one help me & solve my problem

we are using oracle 9i database & i exp full dump file like exp

exp system/manager file=database.dmp full=Y

when i import oracle 10g database then show error

imp system/manager file=database.dmp full=Y

i try it one bye one user import data but not perfectly import data

so you advise me how can i import my 9i backup into 10g database

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547691 is a reply to message #547689]

Thu, 15 March 2012 15:16

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

i try it one bye one user import data but not perfectly import data

What does this mean?
Do you expect any one can read in your mind to know what was wrong?

Quote:

so you advise me how can i import my 9i backup into 10g database

Read all what I posted.
I don't think anyone can give you more than what I wrote.
Now you seem to not have the necessary knowledge and experience to fulfil this task, you should tell your boss that you can't do it and he has to find someone else to do it.

Regards
Michel

[Updated on: Thu, 15 March 2012 15:22]

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547692 is a reply to message #547691]

Thu, 15 March 2012 15:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547695 is a reply to message #547692]

Thu, 15 March 2012 15:27

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

ok but you define me
how to 9i oracle full database.dmp file in oracle 10g database

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547697 is a reply to message #547695]

Thu, 15 March 2012 15:31

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

import how to oracle 9i database full backup file into oracle 10g database

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547698 is a reply to message #547697]

Thu, 15 March 2012 15:33

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

simply i import this with this command
imp system/manager file=database.dmp full=Y
then oracle 10g not importing data and show this error

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.00.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE UNDO TABLESPACE "UNDOTBS" BLOCKSIZE 4096 DATAFILE 'E:\RUNING_ORACLE"
"\ORADATA\ORACLE\UNDOTBS01.DBF' SIZE 209715200 AUTOEXTEND ON NEXT 5242"
"880 MAXSIZE 16383M EXTENT MANAGEMENT LOCAL "
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE TABLESPACE "CWMLITE" BLOCKSIZE 4096 DATAFILE 'E:\RUNING_ORACLE\ORAD"
"ATA\ORACLE\CWMLITE01.DBF' SIZE 20971520 AUTOEXTEND ON NEXT 655360 MA"
"XSIZE 16383M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT "

[EDITED by LF ... again. Added another [SPOILER]]

[Updated on: Thu, 15 March 2012 15:43] by Moderator

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547699 is a reply to message #547698]

Thu, 15 March 2012 15:45

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

@ka_wish, what is the purpose of posting hundreds of lines of the log file? The error is just the same in all cases. If you insist on it, please, use [SPOILER] tags which will hide most of those vast log lines and make this topic easier to read.

P.S. Yikes! 1853 lines! Thousands, not hundreds! You can't really expect anyone to read them all, can you?

[Updated on: Thu, 15 March 2012 15:47]

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547700 is a reply to message #547699]

Thu, 15 March 2012 15:50

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

Respected Littlefoot please slove my problem .

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547701 is a reply to message #547699]

Thu, 15 March 2012 16:26

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

(Please, note that I'm not a DBA either, but - until someone more experienced offers a solution, here's what I have to say. Some steps might not be needed, some might be missing. Sorry if it is misleading).

Export your 9i database:

exp system/pw@ora9i file=export.dmp full=y

Now, connect to 9i with SQL*Plus and see what users you have in the source database:

SQL> select * from all_users order by username;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ----------
ANONYMOUS                              28 07.02.2006
APEX_PUBLIC_USER                       36 06.12.2009
APEX_030200                            38 06.12.2009
COMPTECH                               57 10.12.2011
CTXSYS                                 25 07.02.2006
DBSNMP                                 23 07.02.2006
DIP                                    18 07.02.2006
FLOWS_FILES                            34 07.02.2006
HR                                     33 07.02.2006
KNJIZARA                               44 15.06.2010 ***
MDSYS                                  32 07.02.2006
MIKE                                   41 09.03.2010 ***
MY_SYS                                 50 13.12.2010 ***
OUTLN                                  11 07.02.2006
RADNI                                  45 18.06.2010 ***
SCOTT                                  40 08.12.2009
SYS                                     0 07.02.2006
SYSTEM                                  5 07.02.2006
TSMSYS                                 20 07.02.2006
TWODAYPLUS                             39 06.12.2009
XDB                                    27 07.02.2006

21 rows selected.

SQL>

Check which privileges these users have by issuing the following queries:

SQL> select * from dba_sys_privs where grantee = 'KNJIZARA';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
KNJIZARA                       CREATE VIEW                              NO
KNJIZARA                       CREATE SEQUENCE                          NO
KNJIZARA                       CREATE PROCEDURE                         NO
KNJIZARA                       CREATE TABLE                             NO
KNJIZARA                       CREATE SESSION                           NO

SQL> select * from dba_role_privs where grantee = 'KNJIZARA';

no rows selected

SQL> select * from dba_tab_privs where grantee = 'KNJIZARA';

no rows selected

SQL>

Now, connect to 10g with SQL*Plus and create users you find interesting (you probably don't need all of them; I marked "my" users above with asterisks (***)). Privileges I granted will probably be different for your users, but you'll find that out.

SQL> create user knjizara identified by knjizara
  2  default tablespace users
  3  temporary tablespace temp
  4  profile default
  5  quota unlimited on users;

User created.

SQL> grant
  2    create session,
  3    create table,
  4    create view,
  5    create procedure,
  6    create sequence
  7  to knjizara;

Grant succeeded.

SQL>

You'd do the same for all your users. As Michel said, omit Oracle ones (such as SYS, SYSTEM, XDB, ...).

Once you're done, import them, one by one:

imp system/pw@ora10g file=export.dmp fromuser=knjizara touser=knjizara ignore=y log=imp_knjizara.log

Repeat import for all users you created. Hopefully, you'll manage to do the job.

If not, well, your boss will have to find someone who knows how to do that. If that happens, try to stick around and, after you finish the job, share the experience. It might be interesting!

[Updated on: Thu, 15 March 2012 16:27]

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547803 is a reply to message #547701]

Fri, 16 March 2012 14:43

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

Littlefoot Thank you So much
are you genius my problem has been solve.
if you not clearly explain then i will not done this problem again thanks

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547804 is a reply to message #547803]

Fri, 16 March 2012 14:46

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

1 more question if you dont mind tell me who is os batter in oracle
linux or windows

[Updated on: Fri, 16 March 2012 14:46]

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547805 is a reply to message #547803]

Fri, 16 March 2012 14:53

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ka_wish wrote on Fri, 16 March 2012 20:43

Littlefoot Thank you So much
are you genius my problem has been solve.
if you not clearly explain then i will not done this problem again thanks

OK, I now know it is not worth spending several hours to try to help you, I'll just come in the end as, if you didn't notice it, Littlefoot just gave you the SQL commands for what I have explained you for hours (except he didn't give the one for the tablespaces and you couldn't import without this important point which solve your very first and locking problem).

Regards
Michel

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547806 is a reply to message #547805]

Fri, 16 March 2012 15:13

ka_wish
Messages: 87
Registered: October 2007
Location: karachi

Member

rizwanadmani@hotmail.com

Michel are you very sweet person and you also my help and i your thankful in heart problem is explain to coding i am fresh in dba i dont understand what is schema i am your student and you are my teacher all of you i will appreciated to you

2nd i very slowly understand what are you saying if any my talk hart you & mind you then please realy sorry 2nd can you advise me witch OS using i am

Report message to a moderator

Re: Oracle 9i V/s Oracle 10g Import / Export Problem [message #547807 is a reply to message #547806]

Fri, 16 March 2012 15:24

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no best OS generally speaking for Oracle.
The best one is the one you best know and master.

Regards
Michel

Report message to a moderator

Previous Topic:	Copy database
Next Topic:	sql loader skip blank line

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:44:59 CST 2025