Instantiate A Table On Logical Standby

Seems today is the day for logical standby issues.  Two tables received a conflict, which we have suffered with previously and applied a patch.  It’s interesting we started receiving this again, however, I’m going to skip to how I resolved the immediate issue syncing the logical objects and allowing the SQL Apply to continue.  Will need a MOS for the other issue.

I started with trying to use DBMS_LOGSTDBY.INSTANTIATE_TABLE, which is nice and convenient since it uses a dblink — no moving files around.

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE(schema_name=>’MySchema’, table_name=>’TABLE1′, dblink=>’mydb’);

An error is thrown issue with a reference constraint.  Problem the parent table is out of sync as well.

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE(schema_name=>’MySchema’, table_name=>’PARENT1′,dblink=>’mydb’);

An error is thrown due to the PARENT1 having a LONG datatype column.

So now I’m forced to move files around using datapump.  I already have datapump directories defined on both databases so I won’t cover that task.

These tables are not updated frequently usually only throw a weekly file load.

alter database stop logical standby apply;

expdp system dumpfile=logical.dmp directory=data_pump_dir tables=myschema.parent1, myschema.table1

Once completed I move the dumpfile over to the logical database server under the appropriate directory.

impdp system dumpfile=logical.dmp directory=data_pump_dir tables=myschema.parent1, myschema.table1

alter database start logical standby apply immediate;

Then I watched the SQL Apply process close the lag.

Advertisements

Tagged: ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: