happydba

 

主题:	Healthcheck for Multimaster Replication and Updatable Materialized View Replication
	文档 ID:	751506.1	类型:	SCRIPT
	Modified Date:	02-MAR-2009	状态:	PUBLISHED

In this Document
  Purpose
  Software Requirements/Prerequisites
  Configuring the Script
  Running the Script
  Caution
  Script
  Script Output

---

 

Applies to:

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

Purpose

The script published in this note is intended to gather a wide variety of information needed to troubleshoot Advanced Replication issues.  It is primarily intended to collect information from Master Replication sites, but can also be used to gather information from Updatable Materialized View sites as well.  The script should be run at each site in the replication environment, spooled to a file, and each output uploaded to the relevant Oracle Service Request.

Software Requirements/Prerequisites

This script is intended to be run from SQL*Plus.

Configuring the Script

The script attempts to log in as SYS AS SYSDBA initially, and prompts for the username/password of the replication or materialized view administrator to use later in the script.  It also prompts for the pathname / filename of the spool file to write the output to, and spools the output to this location.

Running the Script

Simply run the script from the SQL*Plus prompt.

Caution

Script

conn / as sysdba

set echo off
set heading off
alter session set nls_date_format=’HH24:Mi:SS MM/DD/YY’;
set feedback off

PROMPT
PROMPT Please enter the username/password of the Replication Administrator
PROMPT or the Materialized View Administrator below:
PROMPT

ACCEPT rep_administrator PROMPT ‘Username: ‘
ACCEPT rep_password PROMPT ‘Password: ‘ HIDE
PROMPT
PROMPT
PROMPT Please enter the pathname / filename of the spool file to write the output to below:
PROMPT
ACCEPT pathname PROMPT ‘Filename/Pathname: ‘

spool &pathname

PROMPT
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select ‘Advanced Replication Environment Script Ouput for ‘||global_name||’ on Instance=’||instance_name||’ generated: ‘||sysdate o from global_name, v$instance;
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT

set heading on timing off

– Get environment information

prompt
prompt
prompt ++ VERSION ++

select * from v$version;

prompt
prompt
prompt ++ GLOBAL NAMES INFORMATION ++

select * from global_name;

prompt
prompt
prompt ++ SNAME, DBLINK FROM SYSTEM.REPCAT$_REPSCHEMA ++

select sname, dblink from system.repcat$_repschema;

prompt
prompt
prompt ++ JOB QUEUE PROCESSES PARAMETER ++

col name for a25
show parameter job_queue_processes

prompt
prompt
prompt ++ GLOBAL_NAMES PARAMETER ++

show parameter global_names

prompt
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
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
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
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 distributed transaction queue information

prompt
prompt
prompt ++ DEFTRAN COUNT ++

select count(*) from deftran;

prompt
prompt
prompt ++ DEFTRANDEST COUNT ++

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

prompt
prompt
prompt ++ DEFERROR COUNT ++

select count(*) from deferror;

prompt
prompt
prompt ++ DEFERROR ERROR SUMMARY ++

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

prompt
prompt
prompt ++ DEFCALL COUNT ++

select count(*) from defcall;

prompt
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’;

prompt
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’;

prompt
prompt
prompt ++ BLOCKS USED BELOW THE HWM FOR DEF$_AQCALL ++

select count (distinct dbms_rowid.rowid_block_number(rowid)) “used blocks” from SYSTEM.DEF$_AQCALL;

– 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
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
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 &rep_administrator/&rep_password

set heading on

prompt
prompt
prompt ++ REPADMIN USER_SYS_PRIVS ++

col username format a30
col privilege format a40
select * from user_sys_privs;

prompt
prompt
prompt ++ REPADMIN USER_ROLE_PRIVS ++

select * from user_role_privs

prompt
prompt
prompt ++ REPADMIN USER_TAB_PRIVS ++

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

prompt
prompt
prompt
prompt End script.
spool off
prompt
prompt Done spooling to &pathname
prompt

Script Output

The script will write all output to the spool file indicated.  It gathers a variety of data relevant to Advanced Replication environments.  Sample output for the first few lines is as follows:

 

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Advanced Replication Environment Script Ouput for ORCL102A.WORLD on Instance=orcl102a generated: 10:00:27 12/04/08
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++ VERSION ++

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Prod
PL/SQL Release 10.2.0.3.0 – Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 – Production
NLSRTL Version 10.2.0.3.0 – Production

++ GLOBAL NAMES INFORMATION ++

GLOBAL_NAME
——————————————————————————
ORCL102A.WORLD

++ SNAME, DBLINK FROM SYSTEM.REPCAT$_REPSCHEMA ++

SNAME DBLINK
———— ——————————
REPTEST ORCL102A.WORLD

< and so on >