Solved

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

Posted on 2014-09-22
14
269 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
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 375 total points
Comment Utility
>> 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
Comment Utility
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 375 total points
Comment Utility
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
 

Author Comment

by:xeon 63654
Comment Utility
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
Comment Utility
Yes.. Those are valid and stand correct based on this conversation
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:xeon 63654
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
full doesn't import system schemas
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 375 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now