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('------------------');
for t_rep_stat in c_rep_stat loop
dbms_output.put_line('.');
dbms_output.put_line('方案: '||t_rep_stat.sname);
dbms_output.put_line('是否主节点?:'||t_rep_stat.master);
dbms_output.put_line('状态: '||t_rep_stat.status);
dbms_output.put_line('.');
end loop;
end rep_stat;
end rep_diag;
/
三、列出一个延迟事务的所有调用
作为一个复制管理员，我们经常需要查看某个延迟事务中到底包含那些调用，而这些调用的参数又是什么。oracle复制包中没有提供相应的脚本来实现该功能，通常我们的做法只能是借助于oracle的复制管理器来查看，但是如果延迟事务很多，且没有延迟事务的调用个数也很多的话，oracle复制管理器非常的慢，而且最重要的是我们根本无法直接操作这些数据。下面这个脚本可以列出延迟队列中的某个事务的所有调用内容，如果再对这个脚本加以改造的话，甚至可以恢复出延迟事务中的oracle ddl语句。这对于oracle复制管理员是非常有用的功能。
在对复制环境的管理中，还经常作这样一个工作，如果复制发生错误，将会将错误信息写入错误队列中（deferror视图），系统会显示出在一个延迟事务中错误的调用号，也可以将下面的程序加以改造，让其直接输出某个事务的某个调用。由于在很多情况下，一个事务通常含有很多的调用，将所有的都显示出来没有必要，其实我们更关心其中的某个调用。该存储过程这里就不详述，其实根据下面的这个过程改造是非常容易的。有感兴趣的也可以和我联系。
存储过程 p_list_calls可以列出一个延迟事务中的所有调用的参数类型和值，支持所有的复制类型，包括nchar, nvarchar和所有的lob.
运行方法和前面谈到的存储过程一样，首先需要将输出定位到屏幕，
set serveroutput on size 200000
其中参数存储过程的输入参数t为延迟事务的id号，可以通过视图deferror或者defcall得到，下面是一个典型的调用过程例子：
sql> select * from deftran;
deferred_tran_id delivery_order d start_time
------------------------------ -------------- - ----------
7.0.3741 65040962 r 25-7月 -01
8.41.3747 65040963 r 25-7月 -01
6.18.3739 65040974 r 25-7月 -01
8.39.3746 65040843 r 25-7月 -01
sql> set serveroutput on size 1000000
sql> execute p_list_calls('7.0.3741');
调用顺序: 0
操作： db_zgxt.pa_rep_jb.p_rep_dj_nsrxx_u
参数个数: 12
参数 数据类型 值
-------------------- -------------- ----------------------
01 n_nsrnm varchar2 034530001
02 n_pzwh varchar2 (null)
03 n_tbrq date (null)
04 n_bgrq date 2000-12-28 00:00:00
05 n_jbr varchar2 (null)
06 n_fzr varchar2 (null)
07 n_swjgyj varchar2 (null)
08 n_bz varchar2 (null)
09 n_rydm varchar2 030811
10 n_bglrrq date 2000-12-28 14:57:01
11 n_zhwzbm varchar2 13302030000270999999
12 n_kzbz char 1
pl/sql 过程已成功完成。
附：存储过程代码。下载该脚本
create or replace procedure p_list_calls (t in varchar2) is
argno number;
argtyp number;
argform number;
callno number;
tranid varchar2(30);
typdsc char(15);
rowid_val rowid;
char_val varchar2(255);
nchar_val nvarchar2(255);
date_val date;
number_val number;
varchar2_val varchar2(2000);
nvarchar2_val nvarchar2(2000);
raw_val raw(255);
arg_name varchar2(20);
arg_name_c char(20);
table_name varchar2(100);
col_name varchar2(100);
pk_char char(1);
-- 延迟队列光标
cursor c_defcall (t varchar2) is
select callno, deferred_tran_id, schemaname, packagename, procname,argcount
from defcall
where deferred_tran_id = t;
-- 获得参数名称
cursor c_arg_name (p_schema varchar2, p_procname varchar2,
p_pkgname varchar2, p_call_count varchar2) is
select argument_name
from all_arguments
where owner = p_schema
and package_name = p_pkgname
and object_name = p_procname
and (overload = (select ovrld.overload from
(select overload, object_name, package_name, max(position) pos
from all_arguments
where object_name = p_procname
and package_name = p_pkgname
group by overload, object_name, package_name
) ovrld
where p_call_count = ovrld.pos
and object_name = p_procname
and package_name = p_pkgname
)
or overload is null
)
order by position;
-- 该光标用来获得某个列是否为该表的主键
cursor pk_cursor (schema varchar2, t_name varchar2, col_name varchar2) is
select decode (count(*),1,'*',' ')
from dba_constraints t1, dba_cons_columns t2
where t1.constraint_name = t2.constraint_name
and t1.owner = t2.owner
and t1.owner = schema
and t1.constraint_type = 'p'
and t1.table_name = t_name
and t2.column_name like col_name;
begin
for c1rec in c_defcall (t) loop
dbms_output.put_line('调用顺序: ' ||c1rec.callno);
dbms_output.put_line('操作： '||c1rec.schemaname||'.'||c1rec.packagename||'.'||c1rec.procname);
dbms_output.put_line('参数个数: '||c1rec.argcount);
dbms_output.put_line(' 参数 ' || ' 数据类型 ' || '值');
dbms_output.put_line(' ---------------- ' || '---------------- ' ||'----------------------');
argno := 1;
callno := c1rec.callno;
tranid := c1rec.deferred_tran_id;
open c_arg_name (c1rec.schemaname, c1rec.procname, c1rec.packagename,c1rec.argcount);
while true loop
if (argno > c1rec.argcount) then
close c_arg_name;
exit;
end if;
argtyp := dbms_defer_query.get_arg_type(callno, argno, tranid);
argform := dbms_defer_query.get_arg_form(callno, argno, tranid);
fetch c_arg_name into arg_name;
arg_name_c := arg_name;
table_name := substr(c1rec.packagename, 1, instr(c1rec.packagename, '$') - 1);
col_name := substr(arg_name, 1, length(arg_name) - 5) || '%';
open pk_cursor (c1rec.schemaname, table_name, col_name);
fetch pk_cursor into pk_char;
close pk_cursor;
if (argtyp = 1 and argform = 1) then
typdsc := 'varchar2';
varchar2_val := dbms_defer_query.get_varchar2_arg(callno, argno,tranid);
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(varchar2_val,'(null)'));
elsif argtyp = 1 and argform = 2 then
typdsc := 'nvarchar2';
nvarchar2_val := dbms_defer_query.get_nvarchar2_arg(callno, argno,tranid);
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(translate(nvarchar2_val using char_cs),'(null)'));
elsif argtyp = 2 then
typdsc := 'number';
number_val := dbms_defer_query.get_number_arg(callno, argno, tranid);
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(to_char(number_val),'(null)'));
elsif argtyp = 11 then
typdsc := 'rowid';
rowid_val := dbms_defer_query.get_rowid_arg(callno, argno, tranid);
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(rowid_val,'(null)'));
elsif argtyp = 12 then
typdsc := 'date';
date_val := dbms_defer_query.get_date_arg(callno, argno, tranid);
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(to_char(date_val,'yyyy-mm-dd hh24:mi:ss'),'(null)'));
elsif argtyp = 23 then
typdsc := 'raw';
raw_val := dbms_defer_query.get_raw_arg(callno, argno, tranid);
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(raw_val,'(null)'));
elsif argtyp = 96 and argform = 1 then
typdsc := 'char';
char_val := dbms_defer_query.get_char_arg(callno, argno, tranid);
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(char_val,'(null)')||'|');
elsif argtyp = 96 and argform = 2 then
typdsc := 'nchar';
nchar_val := dbms_defer_query.get_nchar_arg(callno, argno, tranid);
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(translate(nchar_val using char_cs),'(null)')||'|');
elsif argtyp = 113 then
typdsc := 'blob';
varchar2_val := dbms_lob.substr(dbms_defer_query.get_blob_arg(callno,argno, tranid));
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(varchar2_val,'(null)'));
elsif argtyp = 112 and argform = 1 then
typdsc := 'clob';
varchar2_val := dbms_lob.substr(dbms_defer_query.get_clob_arg(callno,argno, tranid));
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(varchar2_val,'(null)'));
elsif argtyp = 112 and argform = 2 then
typdsc := 'nclob';
nvarchar2_val := dbms_lob.substr(dbms_defer_query.get_nclob_arg(callno, argno, tranid));
dbms_output.put_line(to_char(argno,'09')||pk_char||arg_name_c||typdsc||' '||nvl(translate(nvarchar2_val using char_cs),'(null)'));
end if;
argno := argno + 1;
end loop;
end loop;
end;
/
