Timing task mechanism in data replication (transfer)

Timing task mechanism in data replication

(Shi Wei July 20, 2001) 01:40) Introduction This article first briefly introduces the basic concept of Data copy of Oracle 8, then detail the ORAALCE 8 task queue mechanism for implementing Oracle 8 Data Replication Delay Transaction Series, including the task Queue "Subsystem installation, API program interface DBMS_JOB package introduction, and the concept of each parameter in the API process, the system structure of the task queue, the runtime, the task queue data dictionary view, and the interval parameter algorithm. First, Oracle 8 data copy replication, as the name refer to copy data in the database to the database of different physical locations to support distributed applications, which is an important part of the entire distributed computing solution. Oracle8's data replication is mainly divided into three categories: basic (simple) copy, advanced copy, and hybrid replication, and advanced replication can be divided into multi-master node replication and update snapshot replication. Figure 1 is a schematic diagram of a simple Oracle data replication. Figure 1 Oracle Data Replication Schematic Figure 1 is a simple schematic of Oracle data replication, does not represent any replication type. In Node 1 If you need to copy database tables (insert, delete, or update, etc.), Oracle uses some internal mechanism to place this change in a delayed transaction queue (DEFTRAN). After a specific time or a certain time interval, Oracle will "push" to the database node 2 in the transaction queue. If the library table to be copied on node 2 changes, the same mechanism is also adopted. If an error occurs during the propagation, the error is placed in the error queue deflerror. What is the transaction stored in the latency transaction queue? This is the Oracle's task queue mechanism. Oracle's task queue allows us to repeat the ORALALCE PL / SQL program (which is the task) at a certain time interval. The task queue uses the largest place is the timing replication task in Oracle's data replication. The earliest designed to the Oracle Task Querari subsystem is to refresh the Oracle snapshot (Oracle simple replication). Of course, now Oracle's timing task mechanism is not only applied to data replication. The database administrator also often uses timed tasks to make some daily maintenance tasks for the database, can also be used in practical applications to arrange some heavy batch tasks in the database. When the system is not busy, it can also be used to launch a service program that listens to the database pipeline to respond to user sessions, and so on. The timing task mechanism for Oralce will be discussed in detail below. Second, the Timing Task Introduction DBMS_JOB system package is an API programming interface of the Oracle "Task Query" subsystem. The DBMS_JOB package provides the following features for the task queue: submit and execute a task, changing the execution parameters of the task and delete or temporarily suspend task. The DBMS_JOB package is created by DBMSJob.sql and prVtJob.SQL and prVtJob.plb in the RDBMS / Admin Label in the Oracle_Home directory. These two files are called by the CatProc.sql script file, and the CatProc.sql file is usually performed immediately after the database is created. The script creates a public meaning for the DBMS_JOB package and grands the public executable permissions, so all Oracle users can use this package. The following data dictionary view is about task queue information, mainly DBA_JOBS, USER_JOBS, and DBA_JOBS_RUNNING. These dictionary views are created by script files called Catjobq.sql. This script file is also called in the RDBMS / Admin subdirectory of the Oracle_Home directory as the script file that creates a DBMS_JOB package, is also called by the script file CatProc.sql. Finally, to make the task queue can run normally, you must start its own background process.

Starting the background process is performed by setting the initialization parameters in the initialization file init *. Ota (unlike the instance, the initialization file name is slightly different). The following is this parameter: job_queue_processes = n where N can be between 0 and 36. In addition to this parameter, there are several initialization parameters for the task queue, which will be discussed in detail later. There are many processes in the DBMS_JOB package, see Table 1. Table 1 DBMS_JOB package Name Type Description DBMS_Job.isubmit process submit a new task, the user specifies a task number dbms_job.submit process submit a new task, the system specifies a task number DBMS_Job.Remove process from the queue to delete an existing task DBMS_JOB. Change Procedure Change User Setting Task Parameters DBMS_Job.WHAT Procedure Change PL / SQL Task Definition DBMS_JOB.NEXT_DATE Procedure Change Task Next Run Time DBMS_Job.Interval Procedure Change Task Run Time Interval DBMS_Job.Broken procedure DBMS_Job.Broken process will hang the task, do not let It repeatedly runs the dbms_job.run process Immediately execute the task DBMS_Job.user_export process in the current session, uses a task. Three, the DBMS_JOB package parameter dbms_job package all the processes have a set of public parameters, used for Define tasks, tasks running time, and time intervals running at task timing. These public tasks definition parameters are shown in Table 2. Table 2 Public parameters for dbms_job processes

Name Type Note JobBinary_integer Task's Unique Identification Number Whatvarchar2 as a task execution PL / SQL code next_datevarchar2 task next time INTERVARCHAR2 date table, used to calculate the next task run, let's discuss the meaning and usage of these parameters in detail . 1. The JOB parameter JOB is an integer that uniquely marks a task. This parameter can be specified by the user or by the system automatically, which is completely dependent on the submission process. The DBMS_Job.Submit process gives a task number by obtaining the next value of the sequence sys.jobseq. The task number is returned as an OUT parameter, so the caller can then identify the submitted task. The dbms_job.isubmit process is specified by the caller to assign a identifier. At this time, the uniqueness of the Task number is completely dependent on the caller. In addition to deleting or re-submitting tasks, it is generally not changed. Even if the database is exported or imported, the task number will also be retained. Therefore, a task number conflict occurs when the import / export operation of the task is executed. 2, what what parameter is a string that can be converted into legitimate PL / SQL calls, which will be automatically executed by the task queue. In the WHAT parameter, if the text string is used, the string must be enclosed in single quotes. What parameters can also use VARCHAR2 variables that contain the string values ​​we need. The actual PL / SQL call must be separated by a semicolon. If you want to embed a text string in the PL / SQL call, you must use two single quotes. The length of the WHAT is limited to 2000 bytes in Oracle7.3, and after Oracle 8.0, it has been expanded to 4000 bytes, which is completely sufficient for general applications. The value of this parameter is generally called a PL / SQL stored procedure. In practical applications, although you can use a big anonymous PL / SQL block, it is recommended that you should not use this way. Another actual experience is that it is best to encapsulate stored procedures in an anonymous block, which avoids some inexplicable errors. I will give an example. Under normal circumstances, what parameters can be referenced:

What => 'my_procedure (parameter1); "But more secure references should be written: what =>' begin my_procedure (parameter1); end; '

At any time, we can achieve the purpose of changing the task definition by changing the WHAT parameter. However, it is important to note that by changing the WHAT parameter to change the task definition, the current session settings of the user are also recorded and become part of the task running environment. If the current session settings are different from the session settings when the submission task is initially submitted, it is possible to change the operational behavior of the task. It is very important to realize that this potential side effect is very important, whenever it is applied to the WHAT parameter in any dbms_job, be sure to ensure the correct session settings. 3, the next_date next_date parameter is the time used to schedule the task next time. This parameter is the current time for dbms_job.submit and dbms_job.broken, that is, the task will run immediately. When the NEXT_DATE parameter of one task is assigned to null, the next time the task will be designated as January 1, 4,000, that is, the task will never run. In most cases, this may be the situation we don't want to see. However, if you change an angle, if you want to keep this task in the task queue, you don't want to run, it is a very simple way to set next_date to NULL. Next_date can also be set to a past time. It should be noted here that the execution order of the system task is determined based on their next execution time, so that the next_date parameter can be set back to achieve the purpose of the task in front of the task queue. This is very useful when the task queue process cannot keep up with the task to be executed and a specific task needs to be executed as soon as possible. 4, the interval interface is a string representing an Oracle legal date expression. The value of this date string is calculated when the task is executed, and the calculated date expression has two possibilities, or it is a future time or NULL. Here you should emphasize: Many developers are not realized that next_date is calculated at the beginning of a task, not when the task is successfully completed. When the task is successfully completed, the system is set to the next task to run the next task by updating the task queue. When the next_date is NEXT_DATE by the Interval expression, the task automatically removes from the task queue and will not continue again. Therefore, if a NULL value is transmitted to the interval parameter, the task is only executed once. By assigning a variety of different values ​​to the interval parameter, you can design the task of complex running time plan. The "Task Interval and Date Algorithm" later will discuss the interval expression and give an example of an actual useful Interval expression. 4. Task queue architecture and runtime environment task queues are actually a subsystem in the Oracle system, which has their own backend process and directory table. The purpose of the sub-system design is to automatically run the PL / SQL process without user intervention. 1. The Task Queue Background Process Task Series (SNP) The background process starts at the same time as the Oracle instance is started. In front of the article, you have already talked to the parameter job_queue_processes in initial file init.ra, which is used to set there with several queues. Several processes here are set, and there will be several SNP processes in the system. Job_Queue_processes This parameter can be any number of 0 to 36, that is, for each Oracle instance can have up to 36 SNP processes, or no queue process (= 0). In most operating systems, SNP three letters often appear as part of the process name. For example, in the UNIX system, if the Oracle instance is named ORA8, there are three task queue processes, the three task queue process names are:

ORA_ORA8_SNP0ORA_ORA8_SNP1ORA_ORA8_SNP2 SNP Background Procedure An important difference between the other Oracle background process is to kill a SNP process does not affect the Oracle instance. When a task queue is out of control or consumes too much resources, it can be killed, of course, this situation is not often encountered. When an SNP process is killed or fails, Oracle automatically launches a new SNP process instead of it. 2. The initialization parameters of the task queue initialize the file in init.ra controls the operation of the task queue in the background, and we will discuss it in detail. (1), the Job_Queue_Interval task queue process periodically wakes up and checks if the task queue catalog table needs to be executed. The parameter job_queue_interval determines how long it is "sleep" between the SNP process twice (in seconds). The space-separated too small will result in unnecessary system throughput due to the constant inspection table of the SNP process. Conversely, if the interval is too large, the SNP process is not awake at a specific time, and the task of that time will not be run. The best time interval setting is to consider different tasks in the system environment, and 60 seconds have indeed set up most applications. (2), Job_Queue_keep_Connections In addition to the two parameters described earlier, the third parameter affecting the SNP background process behavior is Job_Queue_keep_Connections. When this parameter is TRUE, the SNP process is still open to Oracle during the operation of the two tasks (during sleep). Conversely, if the SNP process will be disconnected and the task queue is reconnected and the task queue is reconnected when the wake is coming. One of these two methods is mainly considering the effectiveness of the task queue and the database closure method. The efficiency of the long-term remaining connection is relatively high, but the task queue will be affected by the normal shutdown database. This is because the task queue procedure is nothing different from the server manager, and the normal shutdown database needs to be disconnected by all users. Disconnecting and reconnection adds a load to the database, but can periodically enable the database without connecting the SNP process, so that the database can be turned off normally. For environments with many tasks or time intervals (one hour or less), the job (one hour or less) is generally set to True, and modify the script that closes the database is turned off immediately. For a database that is strictly requested using a normal manner or less task, the repetition interval is long, and the parameter is typically set to false. It is best to remind a saying that the SNP process is only disconnected when there is no task run. In this case, the task SNP that needs to be run longer will be unanimously kept connected in their life cycle, which is delayed. Time to close the database normally. 3. Establish a running environment When the SNP process wakes up, it first looks at all tasks in the task queue directory, whether the current time exceeds the date time of the next run. After the SNP detects the tasks that requires immediate execution of this time, these tasks are sequentially executed in accordance with the order of the next execution date. The process is as follows when the SNP process begins to execute a task:

Start a new database session with the username of the task owner. When the task is submitted or lasts last time, change the session NLS settings and the currently ready task match. Calculate the next execution time by interval date expression and system time. The PL / SQL-defined PL / SQL definition is successful, and the next execution date of the task is updated, otherwise, the failure count plus 1. After job_queus_interval seconds, it has reached the runtime of another task, repeating the above process. In the first two steps, the SNP process created a session environment that mimic the user's runtime definition. However, this imitation of the operating environment is not exactly the same as the actual session environment of the user, you need to pay attention to the following two points: First, any available non-provincial roles when submitted, will not be available in the task operation environment. Therefore, those who want to obtain permissions from the nonaptive role cannot be submitted, and the user's modification of the user can complete the modification by the future operation of the task. Second, any task definitions itself or the database needed in the process must fully meet the remote username and password. The SNP process cannot initialize a remote session without explicitly indicating the password. Obviously, the SNP process cannot assume that the password of the local user is part of the remote running environment session. What is the task of submit? When the task is run, the SNP process will try again after 1 minute. If this run has failed, the next attempt will be performed after 2 minutes, then next time it is 4 minutes. Task queues each double-retrieval between them over the normal runtime. After 16 consecutive failures, the task is marked as an interrupted (Broken), and if there is no user intervention, the task queue will no longer be executed repeatedly. 5. Task information in the Task Queue Dictionary Table and View Task Queue can be viewed by several dictionary views shown in Table 3, which are created by Catjobq.sql scripts. Tables 4 and 5 are the meaning of each field of each view. Table 3. Data Dictionary View View View Name Description DBA_JOBS This database is defined in this database DBA_JOBS_RUNNING Currently Running Task User_Jobs Current User Table 4. DBA_JOBS and USER_JOBS. Dictionary View field meaning

Field (column) Type Description JobNumber Task's unique label log_uservarchar2 (30) Submit Task Priv_UserVarChar2 (30) Give User Schema_UserVarChar2 (30) Task Mode Last_Datedate Last Successfully Run Time Last_secchar2 (8) such as HH24: MM: SS format Last_Date date hours, minute and second this_datedate is running the start time of the task, if there is no task, nullthis_secvarchar2 (8) such as HH24: mm: SS format THIS_DATE Date, Minutes and second NEXT_DATEDATE NEXT_SECVARCHAR2 (8) such as HH24: MM: SS format Next_secvarchar2 (8), minute, minute, and second Total_TimeNumber, the total time required for this task run, unit is a second Brokenvarchar2 (1) flag parameter , Y mark the task interrupt, will not run IntervalvarChar2 (200) for calculating the next runtime expression FailuresNumber task running continuously without success, the number of PL / SQL blocks of the task, the PL / SQL block CURRENT_SESSION_LABELRAW MLSLABEL, Trust Oracle Session Clearance_hiraw Mlslabel This task trusted Oracle Maximum Clearance Clearance_Loraw Mlslabel This task trusted Oracle minimum gap NLS_ENVVARCHAR2 (2000) Task Running NLS Session Settings MISC_ENVRAW (32) Task Other Session Parameters Table 5. View DBA_JOBS_RUNNING field Measuring List Data Type Description SidNumber Currently Run Task Session IDJobnumber Task Unique MoguesNumber Continuously Unsecured Date Last_Datedate Last Date Last_secchar2 (8) such as HH24: MM: SS Format Last_Date Date, Minutes and second this_datedate is currently running the task start date this_secvarchar2 (8) such as HH24: MM: SS format THIS_DATE date hours, minutes, and second six, task repeated runtime and interval calculation task Repeat time The interval depends on the date expressions set in the Interval parameter. Let's talk about how to set up an interval parameter in detail to accurately meet our task needs. Generally speaking, there are three timing requirements for timing of a task.

Run the task after a specific time interval. Run the task at a specific date and time. After the task is successfully completed, the next execution should be after a specific time interval. The number of date algorithms required for the first scheduling task is relatively simple, namely 'sysdate n', where n is a time interval in the sky. Table 6 shows some examples of such time intervals. Table 6 Some simple interval parameter setting examples

Description Interval parameter Value Run once a day 'sysdate 1' every hour runs' sysdate 1/24 'every 10 minutes' sysdate 10 / (60 * 24)' Run once every 30 seconds' sysdate 30 / 60 * 24 * 60) 'Run once every week of' sysdate 7 'no longer running this task and delete it NULL Table 6's task intervals indicated by the NULL Table 6 cannot guarantee the next runtime of the task in a specific date or Time, only the time interval between a task runs twice. For example, if a task first runs at 12 am, Interval is specified as 'sysdate 1', and the task will be planned to be executed at 12 am on the next day. However, if a user performs this task in 4 pm (DBMS_Job.Run), the task will be re-scheduled to 4 pm the next day. Another possible reason is that if the database is closed or the task queue is very busy so that the task cannot be executed on time in the scheduled time point. In this case, the task will try to run as soon as possible, that is, as long as the database is open or the task queue is not busy, it will start execution, but at this time, the runtime has drifted from the original submission time to the later real run time. This continuous "drift" of this next runtime is a typical feature of a simple time interval expression. The second scheduling task requirement requires more complex time interval, and Table 7 is an Interval setting example that requires a specific time operation task. Table 7. Task Examples of Timed to Specific Date or Time Description Interval Parameter Value 12 points per day 'Trunc (Sysdate 1)' 8:30 in 8:30 in the morning, 'Trunc (Sysdate 1) (8 * 60 30) / (24 * 60) 'NAXT_DAY (Trunc (trunc (trunc (trunc (tysdate),' 'Tuesday' ') 12/24' The first day of each month 'Trunc (Last_Day (sysdate) 1) 'Trunc (Add_MontHS (Sysdate 2/24, 3),' q ') -1/24' Every Saturday and Japanese Month, 6:10, 6:10 , '' Saturday "), Next_day (sysdate," sunday ")))) (6 × 60 10) / (24 × 60) 'Three scheduling task requirements No matter how to set the interval date expression At this time, because the next runtime of a task is calculated at the beginning of the task, but at this time, it is not known when the task is end. What should I do with this situation? Of course, the method is definitely, we can Implement the way to write a process for the task queue. Here I just briefly introduce the following, can get the system time completed by the task in the previous task queue, then add the specified time interval, take this time to control One of the tasks to be executed. There is a prerequisite here that the task currently runs itself must strictly abide by his time plan. Conclusion The timing task in Oracle is a very important subsystem in the Oracle system, which can be used. Greatly improve our system operation and maintenance. And the delayed transaction queue management of Oracle data replication is entirely based on Oracle's queue tasks, which helps us better manage data replication.