Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this scenario:

  1. One huge (thousands of tables), complex production database, Oracle 8
  2. One huge (thousands of tables), complex development database, Oracle 9 ( same structure as production )
  3. Development database has modified stored procedures and packages as well as new ones
  4. Development database has new tables in some schemas
  5. Neither Oracle 8 exp nor Oracle 9 imp has CONTENT option

We usually do as follows because it's the only way to get data updated properly because a import with ignore=yes would only insert new data, but wouldn't update pre-existing rows with the same PK but different values in non-PK columns:

  1. Delete one schema, then create the user again to have an empty schema
  2. Import from user to user to the empty schema

The problem is:

  1. How to update the development database with fresh data from a production export without having to delete the schema first, since there're new stored procedures/packages as well as modified ones in the development database ?
  2. The comparing process to re-create only modified or new stored procedures after deleting the schema, for getting them back from a backup, would be too error prone.
  3. There are thousands (literally) of tables so we don't want to program a stored procedure to refresh the data in certain order etc. That would take months to write and test.

What would be an import-based solution to this ?

EDIT: I failed to mention that prod is Solaris and dev is RedHat.

share|improve this question
3  
Well, normally dev schema changes would be implemented using alter table etc scripts (which are stored in some kind of source control system), so you'd take a copy of prod then apply the schema changes to the copy to create an up-to-date dev copy. How are you going to apply the schema changes to production when your development is complete? –  Phil Sep 13 '13 at 12:53
    
@Phil Maybe my question wasn't clear enough. I need to update dev DB with fresh data from production. Your alter table suggestion is not related to the question. Regarding your question, we plan to copy the freshly-data-updated dev DB to a similar server with Ora9 and setting that one as production (with same name and IP as current one). Current Ora8 production will be shutdown. Please read my question carefully as I think you could help me. –  user1598390 Sep 13 '13 at 13:14
1  
I did read your question correctly. My comment describes how this would usually be done –  Phil Sep 13 '13 at 13:23
    
Are you upgrading your prod database to 9i in the process or am I misunderstanding? –  Jack Douglas Sep 13 '13 at 13:26
    
Also, it sounds like you've just taken a copy of prod and starting developing on it with no thought about source control etc, so you have no idea what changes have been made. Bad practice. There is no easy way out –  Phil Sep 13 '13 at 13:27

2 Answers 2

I see two solutions. I did not test them but I hope they are userful.

In contrast to table mode export both user mode and tablespace mode export take care of the dependencies between tables so that the tables are imported in the right order. Both methods import the production table stuctures to dev. Tablespace mode export/import in ORacle 8i or 9i can be done only beteen database systems on the same operating system. On can handle this restriction by importing the data using user mode import into a intermediate schema or database on the same operating system as the target sytem.

Method 1: Using TablespaceMode export/import:
1) Put the tablespace(s) of prod in read only mode
2) TablespaceMode-Export the tablespace(s) and datafile copy from prod
3) Put the tablespaces of prod in read/write mode again
4) Drop the Tablespaces in dev (leaves procedures, views from dev)
5) TablespaceMode-Import the tablespaces in Target (includes tables, triggers , indexes, constraints of prod)
6) Put the tablespace(s) of dev in read/write mode

Maybe there are some problems you have to copy with changes in table structure, missing grants or that sequences have to be adjusted

Method 2: replace procedures by an export
1) UserMod-Export of the prod schema
2) UserMod-Export of the dev schema
CONSTRAINTS=N
GRANTS=N
STATITICS=N
TRIGGERS=N
ROWS=N
3) drop the dev schema
4) import the prod schema (to dev)
5) import the dev-schema (to dev)
IGNORE=Y
CONSTRAINTS=N*
GRANTS=N*
INDEXES=N
ROWS=N*

the second import schould replace the prod objects (procedures, views) by the dev objects but leave the tables and table related objects. Parameters with * are alredady set during the export. Maybe one must not set them during import. If you set IGNORE=N then you will get a lot of error messages but maybe there are some other advantages. It will happen hat you import tables from dev that are not in prod. Changes to the table structure must be handled mynually.

I would prefere a method that extracts the procedures from the dev schema before it is replaced by the prod schema. Tools like Toad from Quest (as mentioned by @r.m in a comment) that can compare schemas an create scripts to implement the differences may help.

1) UserMode-Export from prod
2) Extract the procedures from dev into sql-scripts
3) drop dev schema
4) UserMode-Import to dev
5) drop procedures in dev
6) run the scripts created by the extraction process
7) compile all procedures

The following method leaves the dev structures and only imports the data from production. You will get errors during import of data if the column of a table has been renamed or removed in dev. Adding columns with constraints in dev tables or altering contraints in dev may also make the import of data fail for this tables. If some tables aren`t imported because of this errors dependent tables that were not changed in dev can also have problems when the data is imported. So a lot of complicated problems can arise. The basic idea is:

*) remove the data from the dev tables
*) disable contraints, triggers and indexes on the dev tables
*) import the data only from the prod tables to the dev tables
*) enable the constraints, triggers and indexes on the dev tables
*) import the data to the new dev tables and enable their constaints, triggers and indexes

1)user mode export the dev schema without triggers
ROWS=NO
TRIGGERS=NO
FILE=dev1.dmp
2) user mode export the dev schema again
ROWS=NO
TRIGGERS=NO
FILE=dev1.dmp
3) table mode export of dev tables not found on prod
TABLES=list_of_tables_in_right_order
TRIGGERS=Y
CONSTRAINTS=Y
INDEXES=YES
GRANTS=Y
ROWS=Y
STATISTICS=NO
dev3.dmp
4) drop the schema on dev
5) create the empty schema on dev again
6) import the empty dev schema without triggers, constraints and indexes
ROWS=NO
CONSTRAINTS=N
INDEXES=NO
FILE=dev1.dmp
7) import the indexes in a script
INDEXES=YES
ÌNDEXFILE=index.sql
FILE=dev1.dmp
8) user mode export the prod schema
CONSTRAINTS=NO
GRANTS=NO
TRIGGERS=NO INDEXES=NO FILE=prod1.dmp 9) table import the prod table data
TABLES=*
IGNORE=Y
CONSTRAINTS=N
INDEXES=N
GRANTS=N
FILE=prod1.dmp
10) create the indexes using index.sql with sqlplus
11) import table constraints and triggers
TABLES=*
CONSTRAINT=Y
TRIGGERS=Y
ROWS=N
FILE=dev2.dmp
12) import the dev tables you need
TABLES=list_of_tables_in_right_order
IGNORE=Y
CONSTRAINT=Y
GRANTS=Y
INDEXES=Y

share|improve this answer
    
It won't work. There are schema changes that involve extra columns or data type changes. –  Phil Sep 13 '13 at 23:46
    
My fault :( I failed to mention that prod is Solaris and Dev is RedHat... sorry, datafiles and not copy-compatible. But +1 for your effort, I will evaluate method 2. –  user1598390 Sep 14 '13 at 0:54
    
@Phil: These are one of the "some problems". I will mention this explicitely. Such changes must be implemented by scripts. –  miracle173 Sep 14 '13 at 4:59
    
@user1598390 you can overcome this by using an intermediate schema. I modified this to my answer. –  miracle173 Sep 14 '13 at 6:52
up vote 1 down vote accepted

What finally solved my problem was:

  • turning archive mode off
  • disallowing connections
  • disabling triggers and constraints
  • truncating all tables
  • performing imports ( attemp of creating existing objects fails but data is inserted )
  • re-enabling triggers and constraints
  • turning archive mode back on
  • allowing connections back
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.