happydba

 

主题:	Advanced replication diagnostic script
	文档 ID:	471702.1	类型:	HOWTO
	Modified Date:	25-FEB-2009	状态:	PUBLISHED

In this Document
  Goal
  Solution

---

 

 

Applies to:

Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 10.2.0.1
Information in this document applies to any platform.

Goal

This script will extract the detail from different advanced replication views which will help in diagnosing the advanced replication issues.

Solution

1. Please spool and upload the output of the following script from each master site in your replication environment:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Replication Environment Script
Please modify login information as appropriate for
– * sys as sysdba
– * the replication or materialized view administrator
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

conn / as sysdba

– Get environment information

prompt ++ VERSION ++

select * from v$version;

prompt ++ GLOBAL NAMES INFORMATION ++

select * from global_name;
select sname, dblink from system.repcat$_repschema;

prompt
prompt ++ JOB QUEUE PROCESSES PARAMETER ++

show parameter job_queue_processes

prompt
prompt ++ GLOBAL_NAMES PARAMETER ++

show parameter global_names

prompt
prompt ++ DATABASE LINKS ++

column dblink format a30
column owner format a15
column to_host format a20
column connect_as format a15

select db_link, owner, created, host to_host, username connect_as_usr from dba_db_links order by db_link;

prompt
prompt ++ DBA_REPSITES INFORMATION ++

column gname format a12
column dblink format a30
column masterdef format a9
column master format a6
column snapmaster format a10
column my_dblink format a10

select gname, masterdef, master, snapmaster, dblink, my_dblink from dba_repsites order by gname, dblink;

prompt
prompt ++ DBA_REPGROUP INFORMATION ++

column gname format a12
column master format a6
column rpc_dis format a7

select gname, master, status, rpc_processing_disabled rpc_dis from dba_repgroup order by gname;

prompt
prompt ++ DBA_REPOBJECT INFORMATION ++

column sname format a12
column oname format a25
column type format a14
column gname format a12
column $RP format a3
column $RT format a3
column min_comm format a8

select gname, sname, oname, type, status, generation_status
REPSUP_STAT, REPLICATION_TRIGGER_EXISTS “$RT”, INTERNAL_PACKAGE_EXISTS “$RP” from dba_repobject order by gname, oname;

– Get DTQ information

prompt
prompt
prompt ++ DEFTRAN COUNT ++

select count(*) from deftran;

prompt
prompt ++ DEFTRANDEST COUNT ++

select count(*), dblink from deftrandest group by dblink order by dblink;

prompt
prompt ++ DEFERROR COUNT ++

select count(*) from deferror;

prompt
prompt ++ DEFERROR ERROR SUMMARY ++

col error format 9999999999999
select distinct(error_number) error, count(*) from deferror group by error_number;

prompt
prompt ++ DEFCALL COUNT ++

select count(*) from defcall;

prompt
prompt ++ DBA_SEGMENTS INFORMATION FOR DEF$_AQCALL ++

col segment_name format a20
SELECT segment_name,segment_type,blocks,extents FROM dba_segments WHERE segment_name=’DEF$_AQCALL’;

ANALYZE TABLE system.def$_aqcall ESTIMATE STATISTICS;

prompt
prompt ++ DBA_TABLES INFORMATION FOR DEF$_AQCALL ++

col table_name format a15
col tablespace_name format a15
SELECT table_name,tablespace_name,num_rows,blocks,empty_blocks,max_extents FROM dba_tables WHERE table_name=’DEF$_AQCALL’;

ANALYZE TABLE system.def$_aqcall delete statistics;

– Get job queue information

prompt
prompt
prompt ++ DBA_JOBS INFORMATION ++

alter session set nls_date_format = ‘DD-MON-YYYY HH24:MI:SS’;
select job, what, failures, broken, log_user, last_date, next_date from dba_jobs;

prompt
prompt ++ DBA_JOBS_RUNNING INFORMATION ++

alter session set optimizer_mode = RULE;
select * from dba_jobs_running;

– Query the replication administration queue

prompt
prompt
prompt ++ DBA_REPCATLOG INFORMATION ++

col source format a30
col master format a30
select gname, source, status, master, request, oname, type, errnum from dba_repcatlog;

– Determine invalid objects, components

prompt
prompt
prompt ++ INVALID OBJECTS QUERY ++

col object_name for a35
select owner, object_type, object_name, status from dba_objects where status ! = ‘VALID’ and owner in (‘SYS’, ‘SYSTEM’)
order by 1, 2, 3;

prompt
prompt ++ DBA_REGISTRY QUERY ++

column comp_name format a35
select comp_name, status, substr(version,1,10) as version from dba_registry;

– Determine what privileges have been granted to the replication
– administrator/propagator/receiver (if all the same user)

conn repadmin/repadmin

prompt
prompt
prompt ++ REPADMIN USER_SYS_PRIVS ++

select * from user_sys_privs;

prompt
prompt ++ REPADMIN USER_ROLE_PRIVS ++

select * from user_role_privs

prompt
prompt ++ REPADMIN USER_TAB_PRIVS ++

col privilege format a20
col grantor format a20
select table_name, privilege, grantor from user_tab_privs;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
END SCRIPT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Keywords

TABLE.RTF ; MATERIALIZED~VIEW ; REPLICATION ; JOB_QUEUE_PROCESSES ; DBA_REPSITES ; DBA_REPCATLOG ; DBA_REPGROUP ; DBA_REPOBJECT ; MATERIALIZED~VIEW ; 

.