?
Solved

How to imp data from Oracle 10g to 11g and how to verify before decommission 10g?

Posted on 2014-09-22
14
Medium Priority
?
399 Views
Last Modified: 2015-06-23
I have Oracle 10g server running on w2003 and used it since 2005. Now we purchased new 11g running on windows 2008. I do daily backup using exp and imp command. My plan is to export all but import selected schema only (full export but not full import). At the moment, we have around 35 schemas and we only want to import 20 schemas only.

I can do it by running imp command in new server and choose enter selected schemas. But how do I know it actually imported 100% ? What are the things that I need to do before doing import? How does oracle imp work? Does it create users/schema/password, tablespaces same as old server? Does it really importing all objects such as index, view, materialized views, database links, function, procedures etc…?

I want to understand how import works. Consider the following scenario (old 10g server).

  UserA was created. Then we create database link to other remote database (RemoteA) to access few tables.
  Later we created synonym and views for all RemoteA tables. No physical table was created under UserA.
  After that we created another user, UserB. There are some tables, views etc created under this user.
  This user also needs to connect with RemoteA. Aprt from that, UserB also granted access to its table (UserB_table)
  to UserA.

  With this scenario, (correct me if wrong) We have to create and import UserA first, then UserB so that UserB can
  grant UserB_table to UserA. Does imp process know the right sequence when I imported selected user/schema
  UserA, UserB, UserC etc… ?


For your info, I am not a dba but there is noone else than me (limited resources). I am a developer using database driven tools such as phpbuilder and my knowledge on this (oracle backup/restore) is very limited but willing to learn more with your guidance. So please help me.

Thanks in advance.
0
Comment
Question by:xeon 63654
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 1500 total points
ID: 40338379
>> But how do I know it actually imported 100% ?

You can refer the import log or perform a random check in any object

>>What are the things that I need to do before doing import?

You need to check the export log whether the export is successful and has all the information you need and is without any errors

>>How does oracle imp work?

Probably docs are the good place to start about how they work

http://docs.oracle.com/cd/B19306_01/server.102/b14215/exp_imp.htm,

Export: Basically it reads objects sequentially and writes the content in a file(export.dmp)
Import:reads (export.dmp) file and creates objects in a sequential  mannner/as specified in import options

>>Does it create users/schema/password, tablespaces same as old server?

For user/schema/password
Its a YES/NO, there is an option where in you can user may get created as same as exported database if you want.
If you dont want user creation, you can skip that part too, or if you want to import it in different schema also you can do that

For tablespace

Only a full database export (Full=y) generates 'CREATE TABLESPACE' statements. When you run a full database import too, tablespaces are created. But if you are doing selective user imports then you have to create tablespaces before hand as any objects referring to the tablespace may get errored out as tablespace may not exist during the selective import

>>Does it really importing all objects such as index, view, materialized views, database links, function, procedures etc…?

Yes, it will import all the things in a sequential order


Probably you need to refer documentation to know how a classic import and export works

http://docs.oracle.com/cd/B19306_01/server.102/b14215/exp_imp.htm

Now your scenario

>> With this scenario, (correct me if wrong) We have to create and import UserA first, then UserB so that UserB can grant UserB_table to UserA. Does imp process know the right sequence when I imported selected user/schema
  UserA, UserB, UserC etc… ?

Yes, Import would know the sequence, it generally creates the users first and user objects and grants next

however there can be a conflict at times where in you would get errors in grants in that case, you can import the database without any grants and later on after the import is completed, you can re-import grants only via the grants option

There is some documentation on this and one of the user had faced a similar scenario where in experts had suggested to use grants only import here

http://www.experts-exchange.com/Database/Oracle/Q_28519961.html
0
 

Author Comment

by:xeon 63654
ID: 40338560
So based on your advise,
I need to do the followings:

1. Export data (full) from source DB
2. In Target/new DB, I need to create same tablespaces as source DB
3. Import users/schema name with their respective password. (I need sample code for this)
4. Import data with selected users/schema (no granting anything yet)
5. Repeat 4 not data but with grant option only
6. done
Am I right ? Of course have to check all logs during export and import processes.

>>For user/schema/password
>>Its a YES/NO, there is an option where in you can user may get created as same as exported database if you want.

Yes, I want this option, import user/schema with their password as well. Some system really depends on it, so I don't know their password. If I changed it or reset it, the application may failed to run.
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 1500 total points
ID: 40338582
Then you can simply give system user and password and execute the following command

this will import only objects related to scott,hr users

imp system/<password> file=exp.dmp fromuser=(scott,hr) log=logfile.txt rows=y grants=n;

You may need to do the import grants again, in case there are any errors, this may not happen at all times, try with normal import as suggested above, in case you encounter any errors then you can consider the option of importing grants separately

Also just want to mention that the commands/approach which you  have asked are for classic import which is a traditional utility provided by oracle

Over the last few years(Starting from Oracle 10g) there have been significant improvements in this part and new technology/utility called data pump has evolved which comes with more options and has more flexibility when compared with classic import/export

You can consider that option too as you are already with Oracle 10g and above

For more info on data pump, consider reading this

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:xeon 63654
ID: 40338685
I will study oracle datapump later. What I am worried is, I don't remember all passwords for all schemas. There are about 20 schemas to be imported and many system depends on it. If I reset the password, I need to edit each application which I don't want to touch it right now.  I have tried to import all before but with full import (everything) which I don't want to do it.

Can I do the following instead?, ie, with system user

1. Import schemas and password only,
>>imp system/hello file=exp.dmp fromuser=(scott,hr,userA,UserB) log=logfile.txt rows=n grants=n

2. Import data for all schemas that I just imported (no grant) and
>>imp system/hello file=exp.dmp fromuser=(scott,hr,userA,UserB) log=logfile2.txt rows=y grants=n

3. Import grant for all schemas
>>imp system/hello file=exp.dmp fromuser=(scott,hr,userA,UserB) log=logfile3.txt rows=n grants=y

Are all above correct?
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40338708
Yes.. Those are valid and stand correct based on this conversation
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 40338903
If you have the space you could simply do a full import and then drop the schemas you don't need when done.

This way you'll get things that aren't owned by schemas like public grants, roles, profiles, etc.
0
 

Author Comment

by:xeon 63654
ID: 40339182
I don't have probplem with space. The main reason I don't want to do full import is because what will happen if system users in 10g  like sys, system, outln,sysman etc... overwrite system users in 11g ? I don't mind to do it if both source db and target db are in same version and release.
This is migration from 10g to 11g and I believe there are different.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40339209
full doesn't import system schemas
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 1500 total points
ID: 40339518
Yes sdstuber had suggested a better option. Go with it if it suits your requirement.

Schemas such as
('ORDSYS',  'MDSYS', 'CTXSYS','ORDPLUGINS',
  'LBACSYS', 'XDB',   'SI_INFORMTN_SCHEMA',
   'DIP', 'DMSYS', 'DBSNMP', 'EXFSYS','WMSYS')

Which are part of catexp.sql do contain data dictionary information and are ignored by import/export utilities
0
 

Author Comment

by:xeon 63654
ID: 40340639
Thanks, Actually I already import all users using import all excep data and grant, which I do it later.
It seems working, all uesrs are created with their respective password including system user.
These are the parameter of my 1st import:
List contents = no
ignore create=yes
grant=no
table data=no
entire exp file=yes
import terminated successfully with warnings.

Then when I tried the 2nd one, with data but no grant, there are many warning, even errors:
Imp-00019: row rejected due to ORACLE error 2291
IMP-00003:ORACLE error 2291 encountered
ORA-02291: integrity constraint <BKD.SYS_C00012473> violated – parent key not found

When I check the table, some are imported and some are not.

What is the correct command line to import all data except grant?
Some table may be created after granted. How's that be handle?
0
 

Author Comment

by:xeon 63654
ID: 40340643
My 1st import attempt done without producing any log file, not prompted. So basically I just watch it. Exported file is abpout 1.2G.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40340787
Please club these two statements in a single one.

1. Import schemas and password only,
>>imp system/hello file=exp.dmp fromuser=(scott,hr,userA,UserB) log=logfile.txt rows=n grants=n

2. Import data for all schemas that I just imported (no grant) and
>>imp system/hello file=exp.dmp fromuser=(scott,hr,userA,UserB) log=logfile2.txt rows=y grants=n

as  you had specified the table data to be imported later, it has created all the necessary constraints on the corresponding tables in the step1 itself due to which while importing now with data, it says integrity constraint error,  please club 1 & 2 steps into a single step like this.

This should solve the problem, there is no need to split user and data creation

Import data and users for all schemas that I just imported (no grant) and
>>imp system/hello file=exp.dmp fromuser=(scott,hr,userA,UserB) log=logfile2.txt grants=n
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40845978
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question