Data Migration

I need to copy all the objects and data from production database in Oracle 11gR2 to development database in Oracle 11gR2.
What will be the best way to accomplish that in steps by steps if you can provide!

Thanks in advance,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
When you say "all objects" do you mean the entire database with all tablespaces files, etc or a just the schemas with tables and data with indexes etc...?

If everything, I would probably use RMAN duplicate.  Then you can restore it to a different server and rename the instance.

If just the tables, indexes and data, I would look at export/import.

There are examples of each in the online documentation and on the web.

Step by step is really difficult because no two systems are the same.  What works in one will likely need to be modified in another.

Please, can you provide more informations?

Do you want to export:

- one schema object?
- one full schema?
- all schemas?
- entire tablespaces?
- or entire database?

Mark GeerlingsDatabase AdministratorCommented:
You have two options:
1, clone the database
2. export and import the database

Which option is best for you?  That depends.  Cloning is usually faster and simpler, but it may require some downtime for the production system, especially if that is not running in archivelog mode.  The export/import process usually takes longer, but it gives you the flexibility to exclude some objects if you want to.  And, this option does not require any production downtime.

Can you give us some more information including:
1. How often do you need this (one time only, or repeated at some interval)?
2. How large is your database (size of the datafiles on disk)?
3. Do you have SAN or NAS storage that includes "snapshot" capability?
4, What is you Oracle server O/S?
5. What is your Oracle database version?
6. Do you use RMAN or ASM?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

lium1Author Commented:
Do I need to have dba privileges to run RMAN?
slightwv (䄆 Netminder) Commented:
>>Do I need to have dba privileges to run RMAN?

You will be performing a database backup so you'll need more than just normal user permissions.

You actually need different permissions.  RMAN runs at the OS  You will need SYSDBA or SYSOPER at least.
lium1Author Commented:
This will be for one time only and downtime for the production system is not an issue here.
I need to import or migrate the schemas with tables and data with indexes.
slightwv (䄆 Netminder) Commented:
If the schema is relatively small, go export/import.  I personally wouldn't want to deal with terabyte sized export files.

If the schema is on the large size and occupies a high percentage of space consumed in the database, I would look at rman duplication.
Mark GeerlingsDatabase AdministratorCommented:
Either the clone process or the export/import process can get all of your "schemas with tables and data with indexes" plus users, views, PL\SQL objects, etc.  I agree with slightwv's suggestion to use export/import if your database is "small" and use a clone process if your database is "large".

Where is the boundary between a "small" and "large" database these days?  That depends on your server and storage hardware.  If your total database size is just a few Gb or less, that is definitely small.  If your DB size is 1Tb or more, I would say that is large.  If your DB size is somewhere between 5Gb and 1Tb, you can choose what looks the easiest for you.

For the export/import approach, you will have to create the development instance *AND* have a disk and directory structure available there that is identical to the source database *OR* you will have to create the tablespace(s) manually and pass a parameter to impdp so it will use the tablespace(s) that you created manually.
slightwv (䄆 Netminder) Commented:
Just thought of another deciding factor:
with export/import ALL indexes will be rebuilt as part of the import process.  If you have a lot of indexes and any special ones like Oracle Text, they might take a lot of time to rebuild.

If you have this, then I might go with cloning for a smaller database.
lium1Author Commented:
Can I do export and import without sysdba or sysoper privileges?
Since I have no access to dba tables such as dba_data_files, log_files, temp_files, I don't know the size of database, but I know it is not that big.
Mark GeerlingsDatabase AdministratorCommented:
Yes, the export/import option usually takes longer than a clone.  But, it can result in a system with *MUCH* better performance.  This depends on the application though and on whether a lot of deletes have been done, or a lot of updates that changed indexed values or that added a lot of bytes per record.
slightwv (䄆 Netminder) Commented:
>>Can I do export and import without sysdba or sysoper privileges?

Yes but you either need to be the owner of the schemas or have been granted export permissions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
Yes, you can do an export and import with just "normal" user (at least schema owner) privileges.  But, you do need the developer database to exist for you to use this option.  And you can't create that without DBA privileges.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.