How can I update oracle test racledatabase? (Oracle Gurus)

[GerGon]

Hi, I have Oracle 8i v8.1.7.0 in two servers, one is production and the other one is test database for developers.
Test rdbms: have 12 schemas, 6 schemas have about 500 records by table.
Production rdbms: have 6 schemas, each one have tables with thousands of records.

I need to update on each weekend the TEST rdbms with all new records from Production rdbms on these schemas, How I do that?

I was review Export/Import with 'incremental option', but it work over a base with full option, in my case not apply, and these option will be desoported and out soon.

I need updated data like: on production under the schema ZXY the most tables have 2000 new rows by week, I need to pass them to ZXY schema on test rdbms????

At this moment on production database are one table with 2,500,000; the same table on test database have 7,000 rows, I need to update that table without schema procedure, triggers, etc, only data. I can't do this with simple schema export/import, because it take long time, 4 days maybe.

(I don't use rman or other tool)

What can I do?

Thanks...

[Brian Crabtree]

This is a quick dirty way to do it. You will need a database link from test to production (or test to production with some modifications). You will also need to have a primary key on the table, and know what the primary key columns are.


insert into TABLEA
select * from TABLEA@production a
where not exists
(select 1 from TABLEA b where
a.prikey1 = b.prikey1 and
a.prikey2 = b.prikey2 and
a.prikey3 = b.prikey3);

You will need to subsitute your table names and primary key columns in, but this will insert any rows that do not exist on your development server. You will probably need to have a large rollback segment setup for this. If you wanted to, you could convert this into a PL/SQL block and have commits generated every 10,000 rows.

Thanks,

Brian

[twang]

Do you have disk space for staging the datafiles production instance first?
In my environment, we clone the whole production to test machine (copy the datafile, then change the instance name), and truncate or remove records from the new test database.
In you case, is it possible to
1. backup your schema objects from test database (tablespace export, user export)
2. clone production database to test database and change instance name
3. truncate the test database and remove unneeded data and schemas
4. import the "test data" back to the test database.
(but it is impossible to daily update the test database)

If the test database is used for reporting purpose, I will use "read only" standby database instead.

[Yogeeraj_1]

hi,

My personal favorite is the sqlplus copy command. Its in the "none of the above" category. Doesn't need dblinks, no need to write code, very fast -- and can be selective.


hope this helps!

regards
Yogeeraj

[Yogeeraj_1]

hi again,

attached documentation with examples.

regards
Yogeeraj

[Massimo Bianchi]

HI,
if you can afford a little downtime i wuold go to a refresh of the database.

restoring and offline backup of the production system to the test system, doing the sid change.

IF you have different trigger or some users only in yhe test system, you can export that and re-import afterwards.

Another trick i saw from one of my customer was the implementation of advanced queuing, for keeping up-to-date and offsite DB.

Another customer used materialized view, to do this.

HTH,
Massimo

[GerGon]

I think that we are forgetting something...

The test database has designer 6i on it with private synonyms, at this point, I can't drop any schema or drop datafiles....
It only allow me, do an "append" or add new records to the already existents records in test database.
Can you understand my issue, I need add new records on tables in 6 schemas on test, the others 4 schemas are under developt at threir firts stages.
The other think is, I can drop all tables without drop designer tables and objects stored in each aplication schema....!!! This is very dificult task and take long time..

I need to find the way like "incremental" option in the export tool. Remember that incremental export work fine with one base "complete", I can use "complete" base because are diferents databases.

These enigma is very complicated..!!!

Any other idea is wellcomed...

PD: I do twice the assigment points, but I nerver see them put after 'submit', I hope that it has worked yet.

[GerGon]

I think that we are forgetting something...

The test database has designer 6i on it with private synonyms, at this point, I can't drop any schema or drop datafiles....
It only allow me, do an "append" or add new records to the already existents records in test database.
Can you understand my issue, I need add new records on tables in 6 schemas on test, the others 4 schemas are under developt at threir firts stages.
The other think is, I can drop all tables without drop designer tables and objects stored in each aplication schema....!!! This is very dificult task and take long time..

I need to find the way like "incremental" option in the export tool. Remember that incremental export work fine with one base "complete", I can use "complete" base because are diferents databases.

These enigma is very complicated..!!!

Any other idea is wellcomed...

PD: I do twice the assigment points, but I nerver see them put after 'submit', I hope that it has worked yet.

[Volker Borowski]

Well,

just a few questions:

- Do you need to get a "consistent" copy of the entire production database ?
- Do you need ALL or only partially data.
- New rows are easy, because the will have new keys, but what if a row is deleted in prod ? Should it be deleted in Test as well ? What about updates (hell) ?
- What do you want to do with the data in the test environment ?
- What about data that is entered in the test environment for the next refreh ? Should be kept or should be deleted ?

I'd ever prefer to get a consistent copy, to be sure I have everything ok and I do not like to have downtimes for consistent exports in production. This leaves "backup/restore" as the only way, as incr-exp will be desupported and some type of replication will be complicated and put workload on production.

If you need the data for testing reports or mostly SELECT accesses, I'd drop the tables in Test-db and create DBLINKS to a second "prodcopy"-DB, which can be refreshed with backup / restore (several ways described in this forum [can even be done from online backups without causing downtime or additional workload in production]). This is easy.

If you have OLTP processing spreading about two databases, you might need to setup/configure additional stuff to activate two-phase-commit for distributed transactions. I am not sure HOW to do this (never had to configure) but I am sure Oracle supports this.
(distributed_transactions=true ?).
I am not sure either if this will affect your coding in your application.

If this leads to nothing, you might need to check out the replication features of Oracle or do it manually by creating triggers, that update the data in your testdatabase in addition, but this will put significant load on your test db.

Stuff to think about
Volker

[Volker Borowski]

Sorry,

... significant load on your PROD - db ....
(because this is where the triggers/replication will be processed)

V.