Adding a new replicated schema into an existing streams setup



I love streams - i think it's one of the best features in the database - it's incredibly powerful and incredibly underused. I still prefer it to goldengate.

Anyway what i wanted to share was how simple it was to add a schema into an already existing setup - i thought this may be a little tricky but was a piece of cake.

All i had to do was use one of the built in plsql procedures that did everything for me and everything worked fine first second time.

The code just looked like this

begin
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
   schema_names                 =>'COMPLIANCE',
   source_directory_object      =>'DATA_PUMP_DIR',
   destination_directory_object =>'DATA_PUMP_DIR',
   source_database              =>'DB1',
   destination_database         =>'DB2',
   capture_name=>'CAPTURE_COMP',
propagation_name=>'PROP_COMP',
apply_name=>'APPLY_COMP',
include_ddl=>TRUE);
end;
/


So i just want to replicate the schema COMPLIANCE from DB1 to DB2 (with a few explicitly named streams components and including ddl)

So the thing chugs away for a few minutes and then says

PL/SQL procedure successfully completed.

Great?

However it didn't work - so it's helpful that it throws no error......

The problem was the datapump import of the schema in DB2 failed as the tablespace for this new user did not exist.

So I remove all of the config (using cloud control - lazy i know......) and add the tablespace in DB2.

And run the script again - and it again reports

PL/SQL procedure successfully completed.

But this time it really has worked and everything is replicating just fine.

I'm quite imporessed by how easy that was - I'm pretty sure doing that in GG is not so simple......

0 comments:

Post a Comment