Troubleshooting tips for Advanced
Replication
The following tips and commands have been put together
as a result of multiple troubleshooting sessions spent with Oracle Worldwide
Support, training classes, Metalink notes and years of experience working
with production servers running Advanced Replication configurations.
These notes are also included within the repdoc.txt
documentation file which is generated with each group of scripts.
-- checking global_name of database
select * from global_name;
-- changing global_name of database
1) Write global_name to database using the format "PROD1.WORLD".
2) Then if the global_name needs updated to a non-standard format like
"PROD1" (with no db_domain), update the table after step 1
has been completed:
update global_name set global_name = 'PROD1';
commit
or
update global_name set global_name = 'PROD1.NEW';
commit
Note: In all cases, the database link name must exactly match the database
global_name because the init.ora global_names parameter must be set
to TRUE in order for replication to work correctly.
-- if dbms_repcat.add_master_database fails
Add_master_database may fail if replicated objects were not completely
removed from other master sites. To remove these objects manually, run
the following commands at each site which causes errors:
execute dbms_repcat.drop_master_repschema('schema');
execute dbms_repcat.purge_master_log(sname =>'schema');
--- check for invalid objects
select object_name, object_type, status, owner from dba_objects where
status='INVALID';
-- check status
select * from dba_repcatlog;
-- dba_repcatlog should contain 0 items at the master def and the other
master sites.
-- Keep running the do_deferred_repcat_admin job manually at the master_def
site
-- then alternating back and forth to the master sites running it as
well. Keep
-- doing this until all items in dba_repcatlog have been processed.
If there are error
-- jobs remaining, fix the errors or delete the error jobs.
select * from dba_jobs;
execute dbms_job.run(48);
select job, broken, failures, what from user_jobs;
execute dbms_job.broken(26,false);
execute dbms_defer_sys.execute(1);
select * from dba_jobs_running;
-- deferred transactions are held here, these transactions
need pushed to the other sites
select count(*) from deftran;
select * from deftran;
select * from defcall;
-- destinations which will get replication changes
select count(*) from deftrandest;
select * from deftrandest;
select job, what from dba_jobs;
select count(*) from deferror;
select * from deferror;
select deferred_tran_id,error_msg from deferror;
-- it is Ok to delete from def$_error according
to Oracle support
delete from sys.def$_error;
commit;
-- generate sql which will re-apply deferror transactions
-- apply ORA-0060 transactions first - to make sure records get added
-- order by start_time to apply transactions in correct order
select 'execute DBMS_DEFER_SYS.EXECUTE_ERROR ( deferred_tran_id =>'''
|| DEFERRED_TRAN_ID || ''', destination =>''' || DESTINATION || ''');'
from deferror where error_number in (-60)
order by START_TIME;
-- apply "data not found" transactions
now that the records exist
-- order by start_time to apply transactions in correct order
select 'execute DBMS_DEFER_SYS.EXECUTE_ERROR ( deferred_tran_id =>'''
|| DEFERRED_TRAN_ID || ''', destination =>''' || DESTINATION || ''');'
from deferror where error_number in (-100, 1403)
order by START_TIME;
select count(*) from sys.dba_repcatlog;
select deferred_tran_id,error_msg from deferror;
-- transactions stay in deftran until dbms_defer_sys.purge
-- removed from deftrandest when changes have been committed
-- remove one entry from deferror
execute DBMS_DEFER_SYS.DELETE_ERROR ('2.19.804','ORA.WORLD');
-- remove all entries from deferror
execute DBMS_DEFER_SYS.DELETE_ERROR (null,null);
-- remove all entries from deftran
-- *** only do this if replication needs removed and re-created ***
execute dbms_defer_sys.delete_tran(deferred_tran_id =>'', destination
=>'site1');'
-- if tab$.trigflag != 0 - then Oracle internal
replication triggers are enabled
select * from tab$ where trigflag != 0;
-- to remove Oracle internal replication triggers
if replication is not working
-- and needs to be removed in an emergency to allow writing to the tables
-- also do this as sys user
run catrepr
-- also do this as sys user
update tab$ set trigflag=0 where trigflag !=0;
commit;
shutdown immediate;
startup
select * from sys.def$_error;
select count(*) from dba_2pc_pending;
select count(*) from dba_2pc_neighbors;
-- should return 0 records
select count(*) from sys.pending_trans$;
-- if returns more than 0 then run dbms_repcat.purge_master_log
select count(*) from dba_repcatlog;
set transaction use rollback segment system;
delete from dba_2pc_pending where local_tran_id = '13.193.590';
delete from pending_sessions$ where local_tran_id = '13.193.590';
delete from pending_sub_sessions$ where local_tran_id = '13.193.590';
-- only do this if advised by Oracle support
select * from deftran where DEFERRED_TRAN_ID = '13.193.590';
execute dbms_transaction.purge_lost_db_entry('13.193.590');
-- reconciling replicated tables
One way to reconcile tables in earlier versions of Replication
(7.1.6,7.2.X) is through a
SELECT * FROM tab@db1 MINUS SELECT * FROM tab@db2
SELECT * FROM tab@db2 MINUS SELECT * FROM tab@db1
However in release 7.3.X and greater there is a
DIFFERENCE facility
to make this easier. See Note:1062732.6 for more details on
using the DBMS_RECTIFIER_DIFF.DIFFERENCES procedure.
-- obtaining the site id within a trigger
:NEW.SITE_ID := DBMS_REPUTIL.GLOBAL_NAME;
-- Oracle TS tracing
alter session set events '10046 trace name context forever, level 12';
-- compiling of invalid objects
-- create sql code to compile invalid objects
-- This task is usually done by utlrp.sql, but sometimes it may
-- be helpful to manually recompile some objects.
set echo off
set feedback off
set verify off
set pagesize 0
conn / as sysdba
spool compile_invalid.sql
select 'alter '||object_type||' ' ||owner||'.'|| chr(34) || object_name
|| chr(34)||' compile '|| object_type || ';' from dba_objects where
status = 'INVALID' and object_type = 'PACKAGE';
select 'alter PACKAGE ' ||owner||'.'|| chr(34) || object_name || chr(34)||'
compile BODY;' from dba_objects where status = 'INVALID' and object_type
= 'PACKAGE BODY';
select 'alter '||object_type||' ' ||owner||'.'|| chr(34) || object_name
|| chr(34) ||' compile;' from dba_objects where status = 'INVALID' and
object_type = 'VIEW';
spool off
set echo on
set feedback on
set verify on
-- removal of invalid objects
-- create sql code to drop invalid objects
-- Warning: examine and edit the output file as needed before running
-- drop_invalid.sql
-- run as SYS user
set echo off
set feedback off
set verify off
set pagesize 0
spool drop_invalid.sql
select 'drop '||object_type||' ' ||owner||'.'|| chr(34) ||object_name
|| chr(34)||';' from dba_objects where status = 'INVALID';
spool off
set echo on
set feedback on
set verify on
