Data Migration

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.

Hi,

Please, can you provide more informations?

Do you want to export:

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

Thanks
ElectivoJr

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?

Do I need to have dba privileges to run RMAN?

>>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.

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.

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.

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.

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.

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.

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.