oracle| Script | data | database
Oracle database replication Common scripts
(Shi July 30, 2001 17:30)
Oracle data replication is a relatively mature technology in Oracle database products and is an important part of the entire distributed computing solution. For database systems with replication environments, as well as Oracle DBAs, there is also a person who is dedicated to maintaining Oracle's data replication issues, called Oracle Replication Administrator (Oracle Replication Manager). This article writes different stored procedures for replication administrators who are often concerned about replicating systems in Oracle data replication. Of course, the Replication Manager provided through Oracle can also achieve these goals, but in practical applications, Oracle Replication management appliances are inflexible, slow, and we can not control the results of their query arbitrary and so on the shortcomings.
First, view oracle8x delay transaction queue call and number of calls
Push (push) a large delay transaction (Deferred transactions) queue is very slow. A common problem is that a transaction contains very many calls (calls). If the system detects an error, such as ora-01403 (the data does not find the error), which is what we often say detects a conflict, and there is no conflict-eliminating method, the time to write to the Deferror error table and ROLLBACK transaction will be longer, and if there are many calls in the transaction, The time spent in any one call will grow exponentially.
Oracel strongly recommends no more than 50 calls to each transaction in data replication. The following stored procedure provides a quick view of the list of transactions in deferred transaction queues and the number of calls in each transaction. The output is arranged in a transport order, which is the order in which the transaction will propagate to the master node. This is useful for determining latency in propagation and for hanging.
One of the lessons to be noted in building Oracle data replication environments is that you must set up a conflict solution, use several scenarios provided by the Oracle system, or write your own scripts to complete conflict processing. Why say so, must set up the conflict solution, perhaps we can say, our replication environment is a single replication, there is no conflict phenomenon. Here I have an example of my own experience to illustrate this problem, in practice, I set up a 15-node advanced replication environment, a primary definition node, 14 master nodes, of which 14 primary node to the primary definition node to pass data. In general, there is no conflict in this case. However, in the practical application, the problem arises, several nodes in the transmission of some data, hang (hang) up. All possible reasons to find out, never found the root knot of the problem. The last discovery was that there was an error when the data was propagated to the remote node, and there was no conflict resolution on that node, and there were more than 1000 calls to the transaction with the error. One of the calls went wrong, the transaction needs to be rolled back, and the rollback time between the local and remote nodes grows with the geometric cardinality. There is the performance of the system hang (hang) mentioned earlier. This means that in a system where there is no conflict at all, due to accidental indefinite error process, the processing of batch data and the data import without considering replication can cause serious performance problems in the process of propagation, the most serious situation is that the system hangs and cannot complete the normal copying work. The simplest and most effective approach is to set up a system-correct conflict-resolution approach in the context of a multiple replication environment to prevent this from happening.
The following stored procedure is a script that lists the number of calls in transactions and transactions in the deferred transaction queue in the Oracle 8 environment, which is not supported by Oracle 7, because the replication mechanism for Oracle 8 and Oracle 7 has changed.
Stored Procedure Call Method:
Under Sql/plus, first run the following settings to make the output of the stored procedure to the screen,
SET serveroutput on SIZE 200000
EXEC p_list_transactions (ORA_SJJK);
Where the parameters of the stored procedure are propagated to the deferred queue of the destination node for the transaction to be viewed, the database join (dblink) name As_destination.
If we see a transaction with a lot of calls (more than 50), this transaction is likely to cause delay or even suspension of the transaction queue push process.
Attached: Stored procedure script: downloading the script
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 the 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 (' cannot determine target node, input parameter is wrong! ');
return;
ELSE
Dbms_output. Put_Line (' Deferred transaction target node is: ' | | 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 the CNT from system.def$_aqcall WHERE enq_tid = R.enq_tid;
Dbms_output. Put_Line (' Deferred transaction id= ' | | r.enq_tid| | ' number of calls = ' | | To_char (CNT));
End LOOP;
End;
/
Oracle 8 Advanced Replication Environment Settings problem Diagnostic script
To ensure that an advanced replication environment is built to work, to ensure that all replicated objects are in a normal state, for an advanced replication environment, to check whether all objects in a replicated environment are working properly, you need to check for different system dictionary objects, including copying group objects, copying objects, copying schema objects, and so on. If this high-level replication environment consists of a number of nodes, each containing several replication scenarios (schemas), and each containing multiple replicated objects, the completion of the review requires a lot of duplication, and here, a replication Setup issue diagnostic Kit is written for this issue. Only by running the corresponding process in the package can we complete the diagnosis of the related objects mentioned above and give the corresponding diagnosis results.
The method of operation is, in the sql/plus environment,
Sql> Spool < filename >
Sql> set Serveroutput on
sql> exec Rep_diag.rep_diag;
It is emphasized here that the user running the package must have the retrieve (select) permissions on the System dictionary table Dba_repschema, Dba_diagnose, Dba_repcat, and Dba_repcatlog, of course, the Replication administrator (RepAdmin) These permissions are available to the user.
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 (' Copy scheme details ');
Dbms_output. Put_Line ('-------------------------');
For T_schema in C_schema loop
Dbms_output. Put_Line (' scheme name: ' | | T_schema. sname);
Dbms_output. Put_Line (' Whether the primary definition node: ' | | T_schema. MASTERDEF);
Dbms_output. Put_Line (' Database join name: ' | | 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 (' Copy object ');
Dbms_output. Put_Line ('----------------------------------');
For T_rep_object in C_rep_object loop
Dbms_output. Put_Line ('. ');
Dbms_output. Put_Line (' owner: ' | | T_rep_object. sname);
Dbms_output. Put_Line (' Object name: ' | | T_rep_object. Oname);
Dbms_output. Put_Line (' object type: ' | | T_rep_object. TYPE);
Dbms_output. Put_Line (' Status: ' | | 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 (' Copy directory error message ');
Dbms_output. Put_Line ('---------------');
For T_rep_error in C_rep_error loop
Dbms_output. Put_Line ('. ');
Dbms_output. Put_Line (' request: ' | | T_rep_error. REQUEST);
Dbms_output. Put_Line (' Status: ' | | T_rep_error. STATUS);
Dbms_output. Put_Line (' Information: ' | | T_rep_error. message);
Dbms_output. Put_Line (' ERROR: ' | | 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 (' copy State ');
Dbms_output. Put_Line ('------------------');
For T_rep_stat in C_rep_stat loop
Dbms_output. Put_Line ('. ');
Dbms_output. Put_Line (' scheme: ' | | T_rep_stat. sname);
Dbms_output. Put_Line (' Whether the master node?: ' | | T_rep_stat. MASTER);
Dbms_output. Put_Line (' Status: ' | | T_rep_stat. STATUS);
Dbms_output. Put_Line ('. ');
End LOOP;
End Rep_stat;
End Rep_diag;
/
Third, list all calls that delay a transaction
As a replication administrator, we often need to see exactly what calls are included in a deferred transaction, and what are the arguments for those calls. The Oracle replication package does not provide the appropriate script to implement this functionality, and it is common practice to view it only with the help of Oracle's replication Manager, but Oracle Replication Manager is very slow if there is a lot of latency and no delay in the number of calls to the transaction. And the most important thing is that we simply cannot manipulate the data directly. The following script can list all the calls to a transaction in a deferred queue, and if the script is modified again, it can even restore the Oracle DDL statements in the deferred transaction. This is a useful feature for Oracle replication administrators.
In the management of the replication environment, it is also often done that if errors occur in replication, the error message is written to the error queue (Deferror view), the system displays the call number that is wrong in a deferred transaction, or the following program can be modified to directly output a call to a transaction. Because in many cases, a transaction usually contains many calls, it is not necessary to show all of them, but we are more concerned about one of the calls. This stored procedure is not detailed here, in fact, according to the following process of transformation is very easy. If you are interested, you can contact me.
Stored procedure p_list_calls can list parameter types and values that delay all calls in a transaction, and support all types of replication, including nchar, nvarchar, and all lobs.
As with the stored procedures discussed earlier, you first need to position the output to the screen,
Set serveroutput on size 200000
The input parameter t of the parameter stored procedure is the ID number of the deferred transaction, which can be obtained through view deferror or Defcall, the following is a typical example of a call process:
Sql> select * from Deftran;
deferred_tran_id Delivery_order D start_time
------------------------------ -------------- - ----------
7.0.3741 65040962 R 2 May-July-01
8.41.3747 65040963 R 2 May-July-01
6.18.3739 65040974 R 2 May-July-01
8.39.3746 65040843 R 2 May-July-01
Sql> set serveroutput on size 1000000
Sql> Execute p_list_calls (' 7.0.3741 ');
Call Order: 0
Operation: DB_ZGXT.PA_REP_JB. P_rep_dj_nsrxx_u
Number of parameters: 12
Parameter data type value
-------------------- -------------- ----------------------
N_NSRNM VARCHAR2 034530001
N_PZWH VARCHAR2 (NULL)
N_tbrq DATE (NULL)
N_BGRQ DATE 2000-12-28 00:00:00
N_JBR VARCHAR2 (NULL)
N_FZR VARCHAR2 (NULL)
Modified N_swjgyj VARCHAR2 (NULL)
N_bz VARCHAR2 (NULL)
N_RYDM VARCHAR2 030811
Ten N_bglrrq DATE 2000-12-28 14:57:01
One N_ZHWZBM VARCHAR2 13302030000270999999
N_KZBZ CHAR 1
The PL/SQL process has completed successfully.
Attached: Stored procedure Code. Download the script
--Deferred queue cursor
CURSOR C_defcall (T VARCHAR2) is
SELECT Callno, deferred_tran_id, SCHEMANAME, PackageName, Procname,argcount
From Defcall
WHERE deferred_tran_id = T;
--Get the parameter name
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;
--the cursor is used to obtain whether a column is the primary key of the table
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;
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
