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.