Oracle Database Copy Common Script (Shi Wei July 30, 2001 17:30)
Oracle's data replication is a relatively mature technology in an Oracle database product, which is an important part of the entire distributed computing solution. For database systems with replication environments, like Oracle DBA, there is also a person to be responsible for maintaining data replication issues of Oracle, called Oracle Replication Administrator. This article writes different stored procedures for some questions about replication systems on the replication of administrators in Oracle data replication. Of course, the replication manager provided by Oracle can also achieve these purposes, but in practical applications, the Oracle replication manager has a shortcomings of unfoliosity, slow speed, and we cannot perform free control of its query results.
1. Viewing the Oracle8x Delayed Service Querament and call a Push (PUSH) a large delayed transaction (Deferred Transactions) queue is very slow. A common problem is a transaction with a lot of calls (Calls). If the system detects an error, such as ORA-01403 (the data is not found), that is, the detection conflict we often say, and there is no conflict cancellation method, and the time to write the deflerror error table and the time of the rollback transaction will be longer. If there is a lot of Calls in the transaction, it will grow in an index form when it is called.
The number of calls for each transaction in data replication is strongly recommended not more than 50. The following stored procedures provide a list of transactions in the delay in the delay in transaction queue and a script for adjusting the number of records in each transaction. The output is arranged in the transmission order, this order is the order in which transactions will propagate to the primary node. This is very helpful for determining the delay and hang of the propagation.
In building an Oracle data replication environment, there is a business that is worth noting, that is, you must set a conflict solution to use several options available in the Oracle system, or write your script to complete conflict processing. Why do you say that you have to set a conflict solution, maybe we can say that our replication environment is a single copy, it is impossible to conflict. Here I tell the example of my personal experience to illustrate this problem. In actual work, I built a high-level replication environment with 15 nodes, one main defined node, 14 main nodes, 14 main nodes The main definition node is one-way passed data. Generally speaking, there is no conflict in this case. However, in practical applications, the problem will appear, and several nodes are transmitted after transmitting some data, they will hang (hang). All possible reasons have been found, and always find the root knot. Finally, it was found that the error occurred after the data propagated to the remote node, and the node did not set a conflict solution, and the transaction of the error has more than 1,000 calls. One of these calls have been wrong, and the transaction needs to roll back, and this rollback time between local and remote nodes will grow in geometric branches. The performance of the system suspend (HANG) that appeared earlier. This means that in systems that are not impossible to conflict, due to accidental uncertain error processes, processing of batch data and data imports without copying can cause serious performance problems during the propagation process, the most The serious situation is that the system hangs and cannot complete normal replication. The simplest and effective ways are to set up a system conflict in multiple replication environments to avoid this happening.
The following stored procedure is a script that calls a number in transactions and transactions in the delay in the Oracle 8 environment. This script does not support Oracle 7 because Oracle 8 and Oracle 7 have changed. Storage procedure calling method:
Under SQL / PLUS, first run the following settings, make the output of the stored procedure to the screen,
Set ServerOutput on size 200000exec p_list_transactions (ORA_SJJK);
The parameter as_destination of the stored procedure propagates to the delay queue of the destination node to the transaction to be viewed, the DBLINK name.
If we see a lot of calls (more than 50), this transaction is probably the reason for delaying the transaction queue push process delay or even hang.
Attachment: Storage Procedure Script: Download this script
CREATE OR REPLACE PROCEDURE REPADMIN.P_LIST_TRANSACTION (as_destination in VARCHAR2) ISlocal_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) ISselect 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 = 0and exists (select / * index (cd def $ _calldest_primary) * / nullfrom system.def $ _calldest cdwhere cd .enq_tid = c.enq_tidand cd.dblink = remote_node)) or (c.recipient_key> 0and ((exists (select null from system.repcat $ _repprop Pwhere P.dblink = remote_nodeand P.how = 1and P.recipient_key = c.recipient_keyand ((P.delivery_order is NULL) or (P.delivery_order BEGINSELECT NLS_UPPER (global_name) INTO local_node FROM global_name; SELECT dblink INTO remote_node from deftrandestWHERE dblink LIKE UPPER (as_destination || '%') AND ROWNUM <2; IF (remote_node IS NULL) THENDBMS_OUTPUT.PUT_LINE ( 'the target node can not be determined, the input parameters Incorrectly! '); Return; elsedbms_output.put_line (' Delayed Transaction Destination Node: '|| Remote_Node); dbms_output.put_line (' ------------------------------------------------------------------------------------------------------------------------ --------------------- '); end if; select last_delivered, last_enq_tid, dblinkinto last_scn, limited_tid, last_tdbfrom system.def $ _DESTINATIONWHERE DBLINK = Remote_Node; R IN C (last_scn, last_tid, last_tdb) loopselect count (*) INTO CNT from system.def $ _AQCALL WHERE ENQ_TID = R.Enq_tid; dbms_output.put_line ('Delayed Transaction ID =' || R.enq_tid || 'Pixel Number = '|| to_CHAR (CNT)); end loop; end; / Second, Oracle 8 Advanced Replication Environment Settings Question Diagnostics Scripts To ensure a high-level replication environment of the built, you must ensure that all replicated objects are normal. For a advanced copy environment, check all objects in a replication environment, all objects are in a normal working state, need to check different system dictionary objects, including Replication group objects, copy objects, copy scheme objects, and more. If this advanced copy environment contains a lot of nodes, each node contains several replication schemes (Schema) and each scheme contains multiple replication objects, and completes a lot of repetitions for each other, this problem, A copy setting problem diagnostic package is written, and only the corresponding diagnosis of the relevant objects mentioned above will be completed, and the corresponding diagnosis results are given. Running method is, in the SQL / PLUS environment, SQL> Spool Here, it is emphasized that the user who runs the package must have the search for the system dictionary table DBA_REPSCHEMA, DBA_DIAGNOSE, DBA_REPCAT, and DBA_REPCATLOG, of course, the replication administrator (RepAdmin) user has these privileges. Attachment: Advanced Copy Environment Settings Diagnostics Package Scripts. Download this script Create or replace package rep_diag isprocedure rep_diag; procedure rep_schema; procedure rep_ object; procedure rep_rroROR; processure rep_stat; end rep_diag; / CREATE OR REPLACE PACKAGE BODY REP_DIAG ISPROCEDURE REP_DIAG ISBEGIN 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 program detailed information "); DBMS_OUTPUT.PUT_LINE ('-----------------------'); for t_schema in c_schema loop dbms_output.put_line ('Scheme Name:' || T_SChema .Sname); dbms_output.put_line ('Is the main definition node:' || t_schema.masterdef); dbms_output.put_line ('database connection 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 ( 'duplicate object'); DBMS_OUTPUT.PUT_LINE ( '--------------- ------------------- '); for t_rep_object in c_rep_Object loop dbms_output.put_line ('. '); Dbms_output.put_line (') Lord: '|| 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 ( 'error replicate directory'); 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 ( 'message:' || 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 status'); dbms_output.put_line ('--------------'); for t_rep_stat IN c_rep_stat loop dbms_output.put_line ('.'); Dbms_output.put_line ('):' || t_rep_st At.sname); dbms_output.put_line ('is the primary 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 the calls for delayed transactions As a replication administrator, we often need to see a delayed transaction containing those calls, and what the parameters of these calls are. There is no corresponding script to implement this feature in the Oracle Copy Pack, usually our approach can only be viewed by the aid of Oracle's replication manager, but if there are many delayed transactions, there is a lot of calls for transactions, Oracle The replication manager is very slow, and the most important thing is that we cannot do this directly. Below this script can list all the call contents of a transaction in the latency queue. If you retrore this script, you can even restore the Oracle DDL statement in the delay transaction. This is a very useful feature for Oracle replication administrators. In the management of the replication environment, it is often made to work. If the copy has an error, the error message will be written to the error queue (Deferror view), and the system will display the wrong call number in a delayed transaction. The following procedures can be transformed so that they directly output a call for a transaction. Since a business usually contains a lot of calls, there is no need to display all all, in fact, we are more concerned about some of them. This stored procedure is not detailed here, in fact, according to the following process modification is very easy. It is also interested in contact me. Store procedure p_list_calls can list all the calls and values of all calls in a delay, support all replication types, including nchar, nvarchar, and all LOBs. The operation method is the same as the stored procedure mentioned earlier, first need to position the output to the screen. Set ServerOutput on Size 200000 The input parameter T of the parameter stored procedure T is the ID number of the delay transaction, which can be obtained by view deflerror or defcall, and below is a typical calling process example: SQL> SELECT * from DEFTRAN; DeferRed_Tran_ID Delivery_Order D Start_time ------------------------------------- ----- - ---------- 7.0.3741 65040962 R 25-July -018.41.3747 65040963 R 25-July -016.18.3739 65040974 R 25-July -018.39.3746 65040843 R 25-July -01SQL> SET ServerOutput on size 1000000SQL> EXECUTE P_LIST_CALLS ('7.0.3741'); call order: 0 operation: db_zgxt.pa_rep_jb.p_rep_dj_nsrxx_u parameter number: 12 Parameter data type value ------------ ---------------------------------------------- 01 N_NSRNM VARCHAR2 03453000102 N_PZWH VARCHAR2 (NULL) 03 N_TBRQ DATE (NULL) 04 N_BGRQ DATE 2000-12-28 00: 00: 0005 N_JBR VARCHAR2 (NULL) 06 N_FZR VARCHAR2 (NULL) 07 N_SWJGYJ VARCHAR2 (NULL) 08 N_BZ VARCHAR2 (NULL 09 N_RYDM VARCHAR2 03081110 N_BGLRRQ DATE 2000-12-28 14: 57: 0111 n_zhwzbm varcha2 13302030000002709999912 N_KZBZ Char 1PL / SQL process has been successfully completed. Attachment: Store procedure code. Download this script CREATE OR REPLACE PROCEDURE P_LIST_CALLS (T IN VARCHAR2) ISARGNO 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); - Delay queue cursor Cursor c_defcall (t varchar2) ISSELECT CALLNO, DeferRed_TRAN_ID, Schemaname, PackageName, Procname, argcountfrom defcallwhere deferred_tran_id = t; - obtaining parameter name CURSOR C_ARG_NAME (P_SCHEMA VARCHAR2, P_PROCNAME VARCHAR2, P_PKGNAME VARCHAR2, P_CALL_COUNT VARCHAR2) ISSELECT ARGUMENT_NAMEFROM ALL_ARGUMENTSWHERE OWNER = P_SCHEMAAND PACKAGE_NAME = P_PKGNAMEAND OBJECT_NAME = P_PROCNAMEAND (OVERLOAD = (SELECT OVRLD.OVERLOAD FROM (SELECT OVERLOAD, OBJECT_NAME, PACKAGE_NAME, MAX (POSITION) POSFROM ALL_ARGUMENTSWHERE OBJECT_NAME = P_PROCNAMEAND PACKAGE_NAME = P_PKGNAMEGROUP BY OVERLOAD, OBJECT_NAME, PACKAGE_NAME) OVRLDWHERE P_CALL_COUNT = OVRLD.POSAND OBJECT_NAME = P_PROCNAMEAND PACKAGE_NAME = P_PKGNAME) OR OVERLOAD IS NULL) ORDER BY POSITION; - a column on which the cursor is used to obtain if the primary key for the table CURSOR PK_CURSOR (SCHEMA VARCHAR2, T_NAME VARCHAR2, COL_NAME VARCHAR2) ISSELECT DECODE (COUNT (*), 1, '*', '') FROM DBA_CONSTRAINTS T1, dBA_CONS_COLUMNS T2WHERE T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAMEAND T1. Owner = t2.OWNERAND T1.OWNER = Schemand T1.UWNER = Schemaand T1.constraint_type = 'p'and t1.table_name = t_nameand t2.column_name like col_name; Begin For c1rec in c_defcall (t) loopdbms_output.put_line ('call order:' || c1rec.callno); dbms_output.put_line ('action:' || c1rec.schemaname || '.' || c1rec.pagenAme || '. '|| c1rec.procname; dbms_output.put_line (' parameter number: '| c1rec.argcount; dbms_output.put_line (' parameter '||' Data Type '||' value '); dbms_output.put_line (' ---------------- '||' ---------------- '|| ---------- ------------ '); argno: = 1; callno: = c1rec.callno; tranid: = c1rec.deferred_tran_id; open c_arg_name (c1rec.schemaname, c1rec.procname, c1rec.pagename, C1REC .ARGCOUNT); WHILE TRUE LOOPIF (ARGNO> C1REC.ARGCOUNT) THENCLOSE 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_C URSOR; IF (ARGTYP = 1 AND ARGFORM = 1) THENTYPDSC: = '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 THENTYPDSC: = '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 || nvl (TRANSLATE (NVARCHAR2_VAL USING CHAR_CS), '(NULL)'); ELSIF ARGTYP = 2 THENTYPDSC: = '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 THENTYPDSC: = '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 THENTYPDSC: = '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 || nvl (to_char (date_val, 'yyyy-mm-dd HH24: MI: SS '),' (Null) ')); Elsif ArgTyp = 23 TENTYPDSC: =' 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 THENTYPDSC: = '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 THENTYPDSC: = '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 THENTYPDSC: = 'blob'; varchar2_val: =
