oracle 數(shù)據(jù)庫復(fù)制常用腳本
（石驍騑 2001年07月30日 17:30）
oracle的數(shù)據(jù)復(fù)制是一個(gè)oracle數(shù)據(jù)庫產(chǎn)品中比較成熟的一項(xiàng)技術(shù)，它是整個(gè)分布式計(jì)算解決方案的一個(gè)重要組成部分。對于具有復(fù)制環(huán)境的數(shù)據(jù)庫系統(tǒng)，和oracle dba一樣，同樣要有一個(gè)人來專門負(fù)責(zé)維護(hù)oracle的數(shù)據(jù)復(fù)制問題，稱之為oracle replication administrator（oracle復(fù)制管理員）。本文就oracle數(shù)據(jù)復(fù)制中復(fù)制管理員經(jīng)常關(guān)心的一些關(guān)于復(fù)制系統(tǒng)的問題，編寫了不同的存儲(chǔ)過程。當(dāng)然，通過oracle提供的復(fù)制管理器也可以達(dá)到這些目的，但在實(shí)際應(yīng)用中，oracle復(fù)制管理器具有不靈活，速度慢，同時(shí)我們不能對其查詢結(jié)果進(jìn)行隨意控制等等的缺點(diǎn)。
一、查看oracle8x延遲事務(wù)隊(duì)列調(diào)用及調(diào)用個(gè)數(shù)
推（push）一個(gè)大的延遲事務(wù)（deferred transactions）隊(duì)列是非常慢的。一個(gè)常見的問題就是一個(gè)事務(wù)中含有非常多的調(diào)用（calls）。如果系統(tǒng)檢測到一個(gè)錯(cuò)誤，如ora－01403（數(shù)據(jù)未找到錯(cuò)誤），也就是我們常說的檢測到?jīng)_突，而且沒有沖突消除方法，寫到deferror錯(cuò)誤表和回滾事務(wù)的時(shí)間就會(huì)更長，事務(wù)中如果調(diào)用calls很多，則消耗在任何一個(gè)調(diào)用的時(shí)間就會(huì)以指數(shù)形式增長。
對于數(shù)據(jù)復(fù)制中每個(gè)事務(wù)的調(diào)用數(shù)oracel強(qiáng)烈建議不超過50個(gè)。下面的存儲(chǔ)過程提供了一個(gè)快速查看延遲事務(wù)隊(duì)列中的事務(wù)列表以及每個(gè)事務(wù)中調(diào)用數(shù)的腳本。輸出是以傳輸順序排列的，這個(gè)順序就是事務(wù)將要傳播到主節(jié)點(diǎn)的順序。這對于確定傳播中的延遲以及掛起等是非常有幫助的。
在搭建oracle數(shù)據(jù)復(fù)制環(huán)境中，有一個(gè)經(jīng)驗(yàn)是值得注意的，那就是一定要設(shè)置沖突解決方案，即可使用oracle系統(tǒng)提供的幾種方案，也可以自己編寫腳本來完成沖突處理。為什么這樣說，一定要設(shè)置沖突解決方案，也許我們可以說，我們的復(fù)制環(huán)境是一個(gè)單項(xiàng)復(fù)制，不可能出現(xiàn)沖突現(xiàn)象。在這里我講一個(gè)自己的親身經(jīng)歷的例子來說明這個(gè)問題，在實(shí)際工作中，我搭建了一個(gè)具有15個(gè)節(jié)點(diǎn)的高級復(fù)制環(huán)境，一個(gè)主定義節(jié)點(diǎn)，14個(gè)主節(jié)點(diǎn)，其中14個(gè)主節(jié)點(diǎn)向主定義節(jié)點(diǎn)單向傳遞數(shù)據(jù)。一般來講這種情況下不會(huì)出現(xiàn)沖突。但是在實(shí)際應(yīng)用中，問題就出現(xiàn)了，其中幾個(gè)節(jié)點(diǎn)在傳輸了一些數(shù)據(jù)后，就掛（hang）了起來。所有可能查找的原因到查了，始終發(fā)現(xiàn)不了問題的根結(jié)。最后發(fā)現(xiàn)是由于數(shù)據(jù)傳播到遠(yuǎn)程節(jié)點(diǎn)后，出現(xiàn)了錯(cuò)誤，而在該節(jié)點(diǎn)又沒有設(shè)置沖突解決方案，同時(shí)出現(xiàn)錯(cuò)誤的該事務(wù)又具有超過1000條的調(diào)用。其中有一條調(diào)用出錯(cuò)了，事務(wù)需要回滾，而這個(gè)在本地和遠(yuǎn)程節(jié)點(diǎn)間的回滾時(shí)間就會(huì)以幾何基數(shù)增長。就出現(xiàn)了前面談到的系統(tǒng)掛起（hang）的表現(xiàn)。這也就是說即是在根本不可能出現(xiàn)沖突的系統(tǒng)中，由于偶然的不定的錯(cuò)誤進(jìn)程，批量數(shù)據(jù)的處理以及沒有考慮復(fù)制情況下的數(shù)據(jù)導(dǎo)入都可造成在傳播過程中嚴(yán)重的性能問題，最嚴(yán)重的情況就是系統(tǒng)掛起，不能完成正常的復(fù)制工作。最簡單和有效的辦法就是在多路復(fù)制環(huán)境下設(shè)置一個(gè)系統(tǒng)確省的沖突處理方法來避免這種情況的發(fā)生。
下面這個(gè)存儲(chǔ)過程就是在oracle 8 環(huán)境下列出延遲事務(wù)隊(duì)列中的事務(wù)和事務(wù)中調(diào)用個(gè)數(shù)的腳本，該腳本對oracle 7 不支持，這是因?yàn)閛racle 8 和oracle 7 的復(fù)制機(jī)制發(fā)生了變化。
存儲(chǔ)過程調(diào)用方法：
在sql/plus下，首先運(yùn)行下面的設(shè)置，使存儲(chǔ)過程的輸出到屏幕上，
set serveroutput on size 200000
exec p_list_transactions(ora_sjjk);
其中，存儲(chǔ)過程的參數(shù)as_destination為所要查看的事務(wù)傳播到目的節(jié)點(diǎn)的延遲隊(duì)列，為數(shù)據(jù)庫聯(lián)接（dblink）名。
如果我們看到一個(gè)事務(wù)具有很多的調(diào)用（超過50個(gè)），這個(gè)事務(wù)很可能就是造成延遲事務(wù)隊(duì)列推過程延遲甚至是掛起的原因。
附：存儲(chǔ)過程腳本：下載該腳本
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 ('不能確定目標(biāo)節(jié)點(diǎn)，輸入?yún)?shù)有誤!');
return;
else
dbms_output.put_line ('延遲事務(wù)目標(biāo)節(jié)點(diǎn)為: '||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 ('延遲事務(wù) id='||r.enq_tid||' 調(diào)用個(gè)數(shù)='||to_char(cnt));
end loop;
end;
/
二、oracle 8高級復(fù)制環(huán)境設(shè)置問題診斷腳本
要保證搭建的一個(gè)高級復(fù)制環(huán)境工作，必須保證所有的復(fù)制對象處于正常狀態(tài)，對于一個(gè)高級復(fù)制環(huán)境，要檢查一個(gè)復(fù)制環(huán)境中是否所有對象均處于正常工作狀態(tài)，需要檢查不同的系統(tǒng)字典對象，包括復(fù)制組對象，復(fù)制對象，復(fù)制方案對象等等。如果搭建的這個(gè)高級復(fù)制環(huán)境包含很多節(jié)點(diǎn)，每個(gè)節(jié)點(diǎn)又包含幾個(gè)復(fù)制方案（schema）以及每個(gè)方案又包含多個(gè)復(fù)制對象，則完成一遍檢查需要作很多的重復(fù)工作，這里針對這個(gè)問題，編寫了一個(gè)復(fù)制設(shè)置問題診斷包，只有運(yùn)行該包中相應(yīng)的過程，即可完成對上面提到的相關(guān)對象的診斷，給出相應(yīng)診斷結(jié)果。
運(yùn)行方法是，在sql/plus環(huán)境下，
sql> spool <文件名>
sql> set serveroutput on
sql> exec rep_diag.rep_diag;
這里強(qiáng)調(diào)一點(diǎn)，運(yùn)行該包的用戶，必須具有對系統(tǒng)字典表dba_repschema、dba_diagnose、dba_repcat以及dba_repcatlog的檢索(select)權(quán)限，當(dāng)然，復(fù)制管理員(repadmin)用戶是均有這些權(quán)限的。
附：高級復(fù)制環(huán)境設(shè)置問題診斷包腳本。下載該腳本
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('復(fù)制方案明細(xì)信息');
dbms_output.put_line('-------------------------');
for t_schema in c_schema loop
dbms_output.put_line('方案名稱: '||t_schema.sname);
dbms_output.put_line('是否為主定義節(jié)點(diǎn): '||t_schema.masterdef);
dbms_output.put_line('數(shù)據(jù)庫聯(lián)接名稱: '||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(' 復(fù)制對象 ');
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ài): '||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('復(fù)制目錄錯(cuò)誤信息');
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ài): '||t_rep_error.status);
dbms_output.put_line('信息: '||t_rep_error.message);
dbms_output.put_line('錯(cuò)誤: '||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('復(fù)制狀態(tài)');
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('是否主節(jié)點(diǎn)?:'||t_rep_stat.master);
dbms_output.put_line('狀態(tài): '||t_rep_stat.status);
dbms_output.put_line('.');
end loop;
end rep_stat;
end rep_diag;
/
三、列出一個(gè)延遲事務(wù)的所有調(diào)用
作為一個(gè)復(fù)制管理員，我們經(jīng)常需要查看某個(gè)延遲事務(wù)中到底包含那些調(diào)用，而這些調(diào)用的參數(shù)又是什么。oracle復(fù)制包中沒有提供相應(yīng)的腳本來實(shí)現(xiàn)該功能，通常我們的做法只能是借助于oracle的復(fù)制管理器來查看，但是如果延遲事務(wù)很多，且沒有延遲事務(wù)的調(diào)用個(gè)數(shù)也很多的話，oracle復(fù)制管理器非常的慢，而且最重要的是我們根本無法直接操作這些數(shù)據(jù)。下面這個(gè)腳本可以列出延遲隊(duì)列中的某個(gè)事務(wù)的所有調(diào)用內(nèi)容，如果再對這個(gè)腳本加以改造的話，甚至可以恢復(fù)出延遲事務(wù)中的oracle ddl語句。這對于oracle復(fù)制管理員是非常有用的功能。
在對復(fù)制環(huán)境的管理中，還經(jīng)常作這樣一個(gè)工作，如果復(fù)制發(fā)生錯(cuò)誤，將會(huì)將錯(cuò)誤信息寫入錯(cuò)誤隊(duì)列中（deferror視圖），系統(tǒng)會(huì)顯示出在一個(gè)延遲事務(wù)中錯(cuò)誤的調(diào)用號(hào)，也可以將下面的程序加以改造，讓其直接輸出某個(gè)事務(wù)的某個(gè)調(diào)用。由于在很多情況下，一個(gè)事務(wù)通常含有很多的調(diào)用，將所有的都顯示出來沒有必要，其實(shí)我們更關(guān)心其中的某個(gè)調(diào)用。該存儲(chǔ)過程這里就不詳述，其實(shí)根據(jù)下面的這個(gè)過程改造是非常容易的。有感興趣的也可以和我聯(lián)系。
存儲(chǔ)過程 p_list_calls可以列出一個(gè)延遲事務(wù)中的所有調(diào)用的參數(shù)類型和值，支持所有的復(fù)制類型，包括nchar, nvarchar和所有的lob.
運(yùn)行方法和前面談到的存儲(chǔ)過程一樣，首先需要將輸出定位到屏幕，
set serveroutput on size 200000
其中參數(shù)存儲(chǔ)過程的輸入?yún)?shù)t為延遲事務(wù)的id號(hào)，可以通過視圖deferror或者defcall得到，下面是一個(gè)典型的調(diào)用過程例子：
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');
調(diào)用順序: 0
操作： db_zgxt.pa_rep_jb.p_rep_dj_nsrxx_u
參數(shù)個(gè)數(shù): 12
參數(shù) 數(shù)據(jù)類型 值
-------------------- -------------- ----------------------
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 過程已成功完成。
附：存儲(chǔ)過程代碼。下載該腳本
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);
-- 延遲隊(duì)列光標(biāo)
cursor c_defcall (t varchar2) is
select callno, deferred_tran_id, schemaname, packagename, procname,argcount
from defcall
where deferred_tran_id = t;
-- 獲得參數(shù)名稱
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;
-- 該光標(biāo)用來獲得某個(gè)列是否為該表的主鍵
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('調(diào)用順序: ' ||c1rec.callno);
dbms_output.put_line('操作： '||c1rec.schemaname||'.'||c1rec.packagename||'.'||c1rec.procname);
dbms_output.put_line('參數(shù)個(gè)數(shù): '||c1rec.argcount);
dbms_output.put_line(' 參數(shù) ' || ' 數(shù)據(jù)類型 ' || '值');
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;
/
