I have a test database with tables that occasionally has to be updated to the state of the production database tables. These databases both stream several tables to separate databases. (ie database A streams tables to database X and B to Y).

I export the needed schema daily from the production database "A". To update the test database "B", I import the needed tables from this export file. I then export the tables to be streamed from database B with the object_consistent=y parameter. I then import those tables to database Y using the streams_instantiation=y parameter.

So this is the process ...

1. Turn off capture on B
2. Turn off apply on Y
3. Drop the tables in B
4. Import tables from A to B
5. Prepare instantiation on tables in B (DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION)
6. Export from B with object_consistent=y
7. Drop tables in Y
8. Import from B to Y with streams_instantiation=y
9. Start apply on Y
10. Start capture on B

I ran this overnight through an OEM job and it appeared to work, just not how I expected. When I came in there was a lot of activity on both databases. All sessions were from the streams admin account. This continued for some time (long after the job had finished). During this time the "captured scn", "applied scn" & "last enqueued scn" values were quite different on the source side (didn't jot those down). I compared these ot the "last applied message number" on the target side and the valuse were quite a bit off.

After the activity subsided, the SCN values were all in the same ballpark. So I figure all the activity was changes getting applied from database B to database Y. I tested out the streaming and it's working fine.

What I don't understand is I thought the parameters in the export and import statements were supposed to sync up the SCNs. That is I thought they would start off in the same state and there would be no need for applying changes. No one changed anything on the source database overnight.

I must not be understanding how the streams export/import parameters work. There must be a step I'm missing.

Any help on this would be appreciated.

Hi,

>> I export the needed schema daily from the production database "A".

Data Pump export is VERY slow!

*********************************************
>> a test database with tables that occasionally has to be updated to the state of the production database tables.

Here is the way I do it:

http://www.dba-oracle.com/t_refreshing_tes..._production.htm

*********************************************
>> What I don't understand is I thought the parameters in the export and import statements were supposed to sync up the SCNs.

No! The ONLY check is "consistent=y" which makes the table consistent as of the moment that the export started.

This book explains it in detail:

http://www.rampant-books.com/book_0801_oracle_utilities.htm

Thanks for the info Mr B

Unfortunately I've never been able to get datapump working faster than imp/exp. I finally gave up on it. I'll have to revisit it.

So if I export the table with object_consistent=y and import it into an empty table with streams_instantiation=y, the scn's in the two table won't necessarily in snyc? I thought this was the point of these parameters.

I guess I'll have to research this more.

Thanks again

Hi,

It does not make sense that the SCN's must match, since streams is replication by row (but I could be wrong!)

OK, here is the deal:

http://docs.oracle.com/cd/B28359_01/server...322/instant.htm

1 - You instantiate the tables/indexes, NOT the data. The whole idea is to start with a syncronized database.

2 - The data is transported via database links:

http://www.rampant-books.com/t_oraclestrea...ms_capture_.htm

You only need to instantiate Streams ONCE . . . .

*************************************************************
>> I've never been able to get datapump working faster than imp/exp.

Data Pump is synonmous with export/import, different names for the same utilities.

Originally it was exp/imp, later changed to expdp and impdp.

**************************************
>>

Interesting . . . .

I'm not a Streams guru, but it is my understanding that the database must "match", and it does matter what methiod you use, copy, clone, export/import, they all so it . . .

Let's see what I can find . . .

The OLD Oracle import (imp) shows these Streams parms:

streams_configuration – [Y] Determines whether or not any streams metadata present in the export file will be imported.

streams_instantiation – [N] Specifies whether or not to import streams instantiation metadata present in the export file.

This suggests that the Streans instantiation is ONLY for the Streams metedata (the table DDL) not the table data itself.

Oracle has the dbms_capture_adm package to prepare database tables for instantiation from the source to the destination database:

http://dba-oracle.com/googlesearchsite_pro...bms_capture_adm

**********************************

http://dbaforums.org/oracle/index.php?showtopic=10061

An instantiation SCN instructs an apply process at the destination database to apply only changes that committed at the source database after the specified SCN.

That's all, the SCN is just a marker for the starting point for the replicatuion to begin.

Have you tried using the samples provided at Oracle support?

Master Note for Streams Setup Scripts (Doc ID 789445.1)


Lots of examples, answers all your questions.