If I want to import the production database data into the development database, can i do it using import and export? using full user export? Currently there is an outdated data in the development. What if the path of the oradata is different in the 2 servers? Any other ways of getting the data from the production server into the development server? Thanks! :)
most of the time import and export will work fine for transferring data between databases.
Note the following,
1. Do full export from the production database
2. Make sure you have remove all schema (apart from sys and system) from the development database by DROP USER.
3. If you have pre-created all tablespaces same as production, do full IMPORT to the development database
If not , the file structure of development must be the same as production, full IMPORT will create the data file on OS level and the tablespaces in the database.
* use IGNORE=yes when full IMPORT
If ORADATA is different simply grep the tablespaces and setting from production and pre-create the tablespaces on development.
Look at the DBA_TABLESPACES and DBA_DATA_FILE.
But if the production DB is too big to be copied to development
DB, how could we just copy the data and previliges?
I just need to get the latest records and changes to be updated into the development server from the production server, do I need to do the whole import into the development server again? Any ways to update just the data and previlies only?
I'm not aware of any tool that can help you to identify the differences.
You will enter into different problems.
1. development and production have been changed since last refresh which results some of the sequence or key used in tables conflict each others so that you have no way to append production to development.
2. if all tables designed with PK, you can simpy import the data to the development database with IGNORE=yes so that it will import only rows with no unique constraint error.
you can get it from the production database by query against the production database
select 'grant '||PRIVILEGE ||' on '||
table_name||' to '||
grantee||';' from dba_tab_privs
For triggers and stored procedure,
you should not refresh it because the development is always have changes that are not released to production.
if you want to get the whatever trigger and procedure in production, you can get it by
(a) do a schema export without data and edit the file to extract the trigger and stored procedures source.
(b) get a tool to extract a create script from the data dict from production. (eg. tools from ERWin, PowerDesigner or write your own sql to extract the scripts)
In other words, there is no other way to export data from production to development database without removing the whole schema from the development, am i right? It is going to be very tedious to do it everything I want to update the development server... any other ways of doing it? any suggestions are welcome! :) Thank u!
Tim Onions has a great set of reverse engineering scripts that can save your can. With in 2-3 minutes you can have scripts that can re-create all of your objects such as tables, tablespaces, views, syns etc...
You can even create a script that executes all the rev eng scripts that you need.
They do work and they make the import hassles minimal if you take the time to use them.
The way I refresh test & dev DBs from production is to "clone" the production. I've wriiten shell scripts (Unix) to ease the task (since I do it on a regular basis), but the process is fairly straight forward, and a lot faster than doing an export/import (about 1/2 hour versus 4 hours per DB). However, this does a complete refresh, not incremental.
Someone (somewhere) probably has already written this up, but the basic procedure is:
1. For the destination DB, execute an "alter database backup controlfile to trace", then make a couple of minor edits to that trace file.
2. Delete *all* files (data, log, and control) for the *destination* DB.
3. Shut down the source DB, then *copy* all source DB data and log files (*not* the control files) to the appropriate locations for the destination DB.
4. In server manager "Startup nomount" the destination DB and execute the modified trace file.
5. Start up the source DB
6. You're done with the Oracle part. You may need to do application specific stuff with the data (such as deleting or updating flag records, etc.)
there is no one simple solution to solve yr problem.
There are many scenario that you can not incrementally transfer data from production to development.
Because you have no way to verify what you have copied will work according to the schema design (business rule).
If you know the schema well, you can probably write sql script to to copy a subset of ddata from production to test. However you may find you need to constantly changing this script as schema changed.
For my case, we probably like to do it the other way round. Duplicate the production data from one of the backup. And see what is needed to bring the production to the latest schema (this is always possible) as the development.
For testing data, do more test case and re-enter the data to the final database is also easy. Or check with the developer what is needed and do export-ikmport of part of the schema.
What do you think ?
Hope you can find yr best way to suit yr need.