Oracle 数据库复制常用脚本

Oracle 数据库复制常用脚本
（石骁騑　2001年07月30日 17:30）

Oracle的数据复制是一个Oracle数据库产品中比较成熟的一项技术，它是整个分布式计算解决方案的一个重要组成部分。对于具有复制环境的数据库系统，和Oracle DBA一样，同样要有一个人来专门负责维护Oracle的数据复制问题，称之为Oracle Replication Administrator（Oracle复制管理员）。本文就Oracle数据复制中复制管理员经常关心的一些关于复制系统的问题，编写了不同的存储过程。当然，通过Oracle提供的复制管理器也可以达到这些目的，但在实际应用中，Oracle复制管理器具有不灵活，速度慢，同时我们不能对其查询结果进行随意控制等等的缺点。

一、查看Oracle8x延迟事务队列调用及调用个数
推（push）一个大的延迟事务（Deferred Transactions）队列是非常慢的。一个常见的问题就是一个事务中含有非常多的调用（calls）。如果系统检测到一个错误，如ora－01403（数据未找到错误），也就是我们常说的检测到冲突，而且没有冲突消除方法，写到deferror错误表和回滚事务的时间就会更长，事务中如果调用calls很多，则消耗在任何一个调用的时间就会以指数形式增长。

对于数据复制中每个事务的调用数Oracel强烈建议不超过50个。下面的存储过程提供了一个快速查看延迟事务队列中的事务列表以及每个事务中调用数的脚本。输出是以传输顺序排列的，这个顺序就是事务将要传播到主节点的顺序。这对于确定传播中的延迟以及挂起等是非常有帮助的。

在搭建Oracle数据复制环境中，有一个经验是值得注意的，那就是一定要设置冲突解决方案，即可使用Oracle系统提供的几种方案，也可以自己编写脚本来完成冲突处理。为什么这样说，一定要设置冲突解决方案，也许我们可以说，我们的复制环境是一个单项复制，不可能出现冲突现象。在这里我讲一个自己的亲身经历的例子来说明这个问题，在实际工作中，我搭建了一个具有15个节点的高级复制环境，一个主定义节点，14个主节点，其中14个主节点向主定义节点单向传递数据。一般来讲这种情况下不会出现冲突。但是在实际应用中，问题就出现了，其中几个节点在传输了一些数据后，就挂（hang）了起来。所有可能查找的原因到查了，始终发现不了问题的根结。最后发现是由于数据传播到远程节点后，出现了错误，而在该节点又没有设置冲突解决方案，同时出现错误的该事务又具有超过1000条的调用。其中有一条调用出错了，事务需要回滚，而这个在本地和远程节点间的回滚时间就会以几何基数增长。就出现了前面谈到的系统挂起（hang）的表现。这也就是说即是在根本不可能出现冲突的系统中，由于偶然的不定的错误进程，批量数据的处理以及没有考虑复制情况下的数据导入都可造成在传播过程中严重的性能问题，最严重的情况就是系统挂起，不能完成正常的复制工作。最简单和有效的办法就是在多路复制环境下设置一个系统确省的冲突处理方法来避免这种情况的发生。

下面这个存储过程就是在Oracle 8 环境下列出延迟事务队列中的事务和事务中调用个数的脚本，该脚本对Oracle 7 不支持，这是因为Oracle 8 和Oracle 7 的复制机制发生了变化。

存储过程调用方法：

在SQL/PLUS下，首先运行下面的设置，使存储过程的输出到屏幕上，

SET SERVEROUTPUT ON SIZE 200000
EXEC P_LIST_TRANSACTIONS(ORA_SJJK);

其中，存储过程的参数as_destination为所要查看的事务传播到目的节点的延迟队列，为数据库联接（DBLINK）名。

如果我们看到一个事务具有很多的调用（超过50个），这个事务很可能就是造成延迟事务队列推过程延迟甚至是挂起的原因。

附：存储过程脚本：下载该脚本

CREATE OR REPLACE PROCEDURE REPADMIN.P_LIST_TRANSACTION(as_destination in VARCHAR2) IS
local_node VARCHAR2(128);
remote_node VARCHAR2(128);
last_scn NUMBER;
last_tid VARCHAR2(22);
last_tdb VARCHAR2(128);
cnt NUMBER;

CURSOR c(last_delivered NUMBER, last_tid VARCHAR2, last_tdb VARCHAR2) IS
select cscn, enq_tid,
dscn, DECODE(c.recipient_key, 0, ''''D'''', ''''R'''')
from system.def$_aqcall c where
(c.cscn >= last_delivered)
and ((c.cscn > last_delivered) or (c.enq_tid > last_tid))
and (
( c.recipient_key = 0
and exists ( select /*+ index(cd def$_calldest_primary) */ null
from system.def$_calldest cd
where cd.enq_tid = c.enq_tid
and cd.dblink = remote_node ) )
or ( c.recipient_key > 0
and ( ( exists (
select null from system.repcat$_repprop P
where P.dblink = remote_node
and P.how = 1
and P.recipient_key = c.recipient_key
and ((P.delivery_order is NULL)
or (P.delivery_order < c.cscn))))
or ( exists
( select /*+ ordered use_nl(rp) */ null
from system.def$_aqcall cc, system.repcat$_repprop rp
where cc.enq_tid = c.enq_tid
and cc.cscn is null
and rp.recipient_key = cc.recipient_key
and rp.how = 1
and rp.dblink = remote_node
and ((rp.delivery_order is NULL)
or (rp.delivery_order < c.cscn)))))))
order by c.cscn, c.enq_tid;

BEGIN
SELECT NLS_UPPER(global_name) INTO local_node FROM global_name;
SELECT dblink INTO remote_node from deftrandest
WHERE dblink LIKE UPPER (as_destination||''''%'''') AND ROWNUM < 2;
IF (remote_node IS NULL) THEN
DBMS_OUTPUT.PUT_LINE (''''不能确定目标节点，输入参数有误!'''');
RETURN;
ELSE
DBMS_OUTPUT.PUT_LINE (''''延迟事务目标节点为: ''''||remote_node);
DBMS_OUTPUT.PUT_LINE (''''-------------------------------------------'''');
END IF;
SELECT last_delivered, last_enq_tid, dblink
INTO last_scn, last_tid, last_tdb
FROM system.def$_destination
WHERE dblink = remote_node;

FOR R IN C(last_scn,last_tid,last_tdb) LOOP
SELECT count(*) INTO cnt FROM system.def$_aqcall WHERE enq_tid = r.enq_tid;
DBMS_OUTPUT.PUT_LINE (''''延迟事务 ID=''''||r.enq_tid||'''' 调用个数=''''||to_char(cnt));
END LOOP;
END;
/
 

二、Oracle 8高级复制环境设置问题诊断脚本

要保证搭建的一个高级复制环境工作，必须保证所有的复制对象处于正常状态，对于一个高级复制环境，要检查一个复制环境中是否所有对象均处于正常工作状态，需要检查不同的系统字典对象，包括复制组对象，复制对象，复制方案对象等等。如果搭建的这个高级复制环境包含很多节点，每个节点又包含几个复制方案（schema）以及每个方案又包含多个复制对象，则完成一遍检查需要作很多的重复工作，这里针对这个问题，编写了一个复制设置问题诊断包，只有运行该包中相应的过程，即可完成对上面提到的相关对象的诊断，给出相应诊断结果。

运行方法是，在SQL/PLUS环境下，

SQL> spool <文件名>
SQL> set serveroutput on
SQL> exec rep_diag.rep_diag;

这里强调一点，运行该包的用户，必须具有对系统字典表dba_repschema、dba_diagnose、dba_repcat以及dba_repcatlog的检索(select)权限，当然，复制管理员(RepAdmin)用户是均有这些权限的。

附：高级复制环境设置问题诊断包脚本。下载该脚本

CREATE OR REPLACE PACKAGE REP_DIAG IS
PROCEDURE REP_DIAG;
PROCEDURE REP_SCHEMA;
PROCEDURE REP_OBJECT;
PROCEDURE REP_ERROR;
PROCEDURE REP_STAT;
END REP_DIAG;
/

CREATE OR REPLACE PACKAGE BODY REP_DIAG IS
PROCEDURE REP_DIAG IS
BEGIN
        REP_SCHEMA;
        REP_OBJECT;
        REP_ERROR;
        REP_STAT;       
END REP_DIAG;

PROCEDURE REP_SCHEMA AS
   CURSOR C_SCHEMA IS SELECT SNAME, DBLINK, MASTERDEF
                FROM SYS.DBA_REPSCHEMA;
BEGIN
        DBMS_OUTPUT.PUT_LINE(''''复制方案明细信息'''');
        DBMS_OUTPUT.PUT_LINE(''''-------------------------'''');
        FOR T_SCHEMA IN C_SCHEMA LOOP
                DBMS_OUTPUT.PUT_LINE(''''方案名称:    ''''||T_SCHEMA.SNAME);
                DBMS_OUTPUT.PUT_LINE(''''是否为主定义节点: ''''||T_SCHEMA.MASTERDEF);
                DBMS_OUTPUT.PUT_LINE(''''数据库联接名称:    ''''||T_SCHEMA.DBLINK);
                DBMS_OUTPUT.PUT_LINE(''''.'''');
        END LOOP;
END REP_SCHEMA;

PROCEDURE REP_OBJECT AS
   CURSOR C_REP_OBJECT IS SELECT SNAME, ONAME, TYPE, STATUS
                FROM SYS.DBA_REPOBJECT;                
BEGIN
        DBMS_OUTPUT.PUT_LINE(''''            复制对象              '''');
        DBMS_OUTPUT.PUT_LINE(''''----------------------------------'''');
        FOR T_REP_OBJECT IN C_REP_OBJECT LOOP
                DBMS_OUTPUT.PUT_LINE(''''.'''');
                DBMS_OUTPUT.PUT_LINE(''''属主: ''''||T_REP_OBJECT.SNAME);
                DBMS_OUTPUT.PUT_LINE(''''对象名称:  ''''||T_REP_OBJECT.ONAME);
                DBMS_OUTPUT.PUT_LINE(''''对象类型:  ''''||T_REP_OBJECT.TYPE);
                DBMS_OUTPUT.PUT_LINE(''''状态:       ''''||T_REP_OBJECT.STATUS);
                DBMS_OUTPUT.PUT_LINE(''''.'''');
        END LOOP;
END REP_OBJECT;

PROCEDURE REP_ERROR IS
        CURSOR C_REP_ERROR IS SELECT REQUEST, STATUS, MESSAGE, ERRNUM
                     FROM SYS.DBA_REPCATLOG;
BEGIN
        DBMS_OUTPUT.PUT_LINE(''''复制目录错误信息'''');
        DBMS_OUTPUT.PUT_LINE(''''---------------'''');
        FOR T_REP_ERROR IN C_REP_ERROR LOOP
                DBMS_OUTPUT.PUT_LINE(''''.'''');
                DBMS_OUTPUT.PUT_LINE(''''请求: ''''||T_REP_ERROR.REQUEST);
                DBMS_OUTPUT.PUT_LINE(''''状态:  ''''||T_REP_ERROR.STATUS);
                DBMS_OUTPUT.PUT_LINE(''''信息: ''''||T_REP_ERROR.MESSAGE);
                DBMS_OUTPUT.PUT_LINE(''''错误:   ''''||T_REP_ERROR.ERRNUM);
                DBMS_OUTPUT.PUT_LINE(''''.'''');
        END LOOP;
END REP_ERROR;

PROCEDURE REP_STAT IS
        CURSOR C_REP_STAT IS SELECT SNAME, MASTER, STATUS
                     FROM SYS.DBA_REPCAT;
BEGIN
        DBMS_OUTPUT.PUT_LINE(''''复制状态'''');
        DBMS_OUTPUT.PUT_LINE(''''------------------'''');
   &nbs

[1] [2]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18