Streams and GrantsPosted: July 30, 2008
I didn’t blog about streams in a while. Since the last time I blogged about streams our configuration changed a lot, but I’ll write about it another time. Its more fun to write about war stories than about nice clean architectures with ultra easy setup scripts.
Yesterday another happy customer went live on our reporting database. After the ultra easy setup, everything seemed to work fine. But today between 11am to 3pm, we accumulated over 20 errors in dba_apply_errors table. All of them were “ORA-00942: table or view does not exist”. All of them for “create view” and “create table” DDL.
Important streams fact of the day: Schema level replication does not replicate object grants given to that schema or on that schema. Even if you replicate DDL.
To resolve the issue we had to give the grants manually on the reporting database, and then use dbms_apply_adm.execute_all_errors to rerun all the DDLs that failed. It was very neat to see that once the grants were given, execute_all_errors worked perfectly and recreated all the views we missed.
If we anticipated more privileges changes on the replicated schemas, we would have had to find a permenant solution for their replication. Perhaps adding a global DDL rule with a filter to replicate grants only for these specific schemas. Lucky for me, privilege changes are not expected to happen in the future.