Few days back I found a huge index on my main transaction table in primary production database(9i RAC environment), which I suspected was not used by any query. So I decided to monitor its usage using oracle 9i Monitoring usage command. And after monitoring it for a week I found it was not used by any query and I dropped it just before production hour began at monday after weekly backup and other maintenance activities were finished. This table was replicated to three other databases using updateable materialized view setup.So this production database was mater site for replication.
To meet my surprise I found that even though 45 minutes of production time elapsed no new records were append to the main transaction table whose unused function based index was dropped.
In my database alert log I got very weird error in both my RAC instances
Errors in file d:\oracle\admin\ctdp\udump\ctdp1_ora_424.trc
:ORA-26500: error on caching "SALES"."TRANSACT" "
search on metalink and Google suggested
DBMS_REPUTIL.MAKE_INTERNAL_PKG ('EDBMS','TEST');
DBMS_REPUTIL.SYNC_UP_REP('EDBMS','TEST');
but it was riskier too as said links.
Then I decided to regenerate replciation support.
took following steps on repadmin user at master site
begin
dbms_repcat.suspend_master_activity(gname=>'master_replication_group_name');
end;/
begin
dbms_repcat.generate_replication_support( sname => 'schemanameofmastertable',
oname => 'tablenamewhichisrepliciated',
type => 'TABLE',
min_communication => TRUE);
end;
/
begin
dbms_repcat.resume_master_activity(gname=>'master_replication_group_name');
end;
/
thanks God it worked.
Now It was clear the culprit was poor documentation of Oracle as oracle says only that you need to regenerate replication support when you change the object only. But I did not change the master table .I dropped its one index and that can not be said change to master table.Thats only a indirect change not a change to structure of DB .
To meet my surprise I found that even though 45 minutes of production time elapsed no new records were append to the main transaction table whose unused function based index was dropped.
In my database alert log I got very weird error in both my RAC instances
Errors in file d:\oracle\admin\ctdp\udump\ctdp1_ora_424.trc
:ORA-26500: error on caching "SALES"."TRANSACT" "
search on metalink and Google suggested
DBMS_REPUTIL.MAKE_INTERNAL_PKG ('EDBMS','TEST');
DBMS_REPUTIL.SYNC_UP_REP('EDBMS','TEST');
but it was riskier too as said links.
Then I decided to regenerate replciation support.
took following steps on repadmin user at master site
begin
dbms_repcat.suspend_master_activity(gname=>'master_replication_group_name');
end;/
begin
dbms_repcat.generate_replication_support( sname => 'schemanameofmastertable',
oname => 'tablenamewhichisrepliciated',
type => 'TABLE',
min_communication => TRUE);
end;
/
begin
dbms_repcat.resume_master_activity(gname=>'master_replication_group_name');
end;
/
thanks God it worked.
Now It was clear the culprit was poor documentation of Oracle as oracle says only that you need to regenerate replication support when you change the object only. But I did not change the master table .I dropped its one index and that can not be said change to master table.Thats only a indirect change not a change to structure of DB .