So, I have a client that is using Oracle Change Data Capture. This environment is very handy but can be a bear to set up and manage, especially if you are in an environment where you are constantly "refreshing" databases from production, doing upgrades, renaming databases, and other activities that may mess with your data dictionary.
Today, after a database was upgraded, I wanted to "redo" all the CDC setup. The first step is to DROP everything. When I got to dropping the CHANGE SET, I got the following:
SQL> exec dbms_cdc_publish.drop_change_set('INTERFACE_CHANGES_SET');
BEGIN dbms_cdc_publish.drop_change_set('INTERFACE_CHANGES_SET'); END;
*
ERROR at line 1:
ORA-01741: illegal zero-length identifier
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 761
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 965
ORA-06512: at line 1
Well, whether or not this is a bug, there is a MOSC workaround for it. They aren't calling it a bug, but, seems like it is to me. Anyway, the Doc ID is 988837.1. Here is the workaround. See the MOSC Doc for the full explanation:
SQL> update sys.cdc_change_sets$ set time_scn_name = 'CDC$M_INTERFACE_CHANGES_SET'
2 where set_name = 'INTERFACE_CHANGES_SET';
1 row updated.
SQL> commit;
Commit complete.
SQL> exec dbms_cdc_publish.drop_change_set('INTERFACE_CHANGES_SET');
PL/SQL procedure successfully completed.
Now, this database had just been upgraded to 11.2.0.2 on RHEL from 10.2.0.4. The MOSC Doc indicates that this issue is recognized through 11.1...well, I guess they have to add to the list.
Gotta love a solution that works. Too bad we had to encounter it in the first place.
Subscribe to:
Posts (Atom)