Keywords: data recovery, task scheduling, Oracle, PL / SQL
This often occurs in the database operation. Since the moment is negligent, some important data is missed, and some important tasks need to run periodically. Obviously, the previous type of problem is mainly data backup and recovery, and the latter class is mainly the task schedule of the system. This article will give this two types of issues, give a solution from the application development perspective.
One. technical foundation
Since this paper is a solution using PL / SQL as a development platform, first understand the relevant background knowledge.
The PL / SQL itself is just a supplement to the SQL statement, enhancing the database processing power by introducing the concept of processization. However, relative to the processification language such as C, C , Java, the processing function of PL / SQL is still not strong enough. To this end, the Oracle database provides a large number of application development packages to enhance application development capabilities. According to the subject matter of this article, the following two development kits: DBMS_FLASHBACK and DBMS_JOB.
1. The DBMS_FLASHBACK package is mainly used to perform an inside query, that is, by setting the query time to determine the query result in this moment. In general, our usual query is the data under the current time (sysdate). Using the DBMS_FLASHBACK package can query the status of previous data, this feature is extremely important for erroneous processing. Below is the two main functions in this package:
· Enable and Disable: Startup and closing the inner query functionality. It should be noted that the reverse mode should be closed first before you start an ink query.
Enable_at_time: Set the time point of the query, which is set by the current time.
2. The DBMS_JOB package is a patch used to scheduling the PL / SQL block, which allows the PL / SQL block to automatically run in the specified time, similar to the timer such as SetTimer in the VC. To facilitate the operation of the package, you need to set two init.ora parameters first:
· Job_Queue_Process Specifies the number of background processing started. If it is 0 or not set, there will be no background processing into the job, and they will not run.
· Job_Queue_Interval in seconds, specifying each process waiting for the new job. One job can only run once within the time specified by Job_Queue_Interva.
After setting up these two parameters, the program can be scheduled, the package mainly uses the Submit function to schedule, the prototype of this function is:
Submit (returned job number, program process name, sysdate, timest operation);
two. Data Recovery
Data recovery is an extremely important feature of the database itself. It is often important data to be implemented through its system's data backup function, so important data is often easy to recover, but some of the usual data is because Cause some trouble.
For experienced developers, they often do some backups for the basic table (base table is a data sheet for providing data sources). In this way, even after some data errors will not cause major accidents.
More practical and rarely used by developers is to adopt a reversed query. For the convenience, it is assumed that a base Table EMP_TABLE, which is recorded as follows:
EMP_NO EMP_NAME EMP_SALARY 001 JACKY 5000 002 Rose 6000 003 John 7000 This table has only three records, then the first record is deleted due to false operation of the database, then the following SQL statement is performed:
Select * from Emp_Table;
Its execution is:
EMP_NO EMP_NAME EMP_SALARY002 ROSE 6000003 John 7000
Since the submission operation has been performed, it is impossible to rollback, so that the original data cannot be recovered with a normal method. However, due to false operation (prior to 5 minutes before 5 minutes), in this case, the DBMS_FLASHBACK package can be used to recover data, and you can type the following code in SQL * Plus:
Execute dbms_flashback.enable_at_time (sysdate-5/1440);
At this point, adjust the database to the state before 5 minutes, if the command to perform the query table is the following:
EMP_NO EMP_NAME EMP_SALARY001 Jacky 5000002 Rose 6000003 John 7000
Then you can back up your data to EMP_TABLE_BK at this time.
CREATE TABLE EMP_TABLE_BKASSELECT * FROM EMP_TABLE;
In this way, the previous mistakes are returned back.
From the above results, call the Enable_at_time function of the dbms_flashback package to adjust the current query time of the database to previous so that the data recovery provides help.
Pay attention to the following points when using the dbms_flashback package:
· The inverting query is premised, that is, the database must have the cancel management function. The specific approach is that DBA should establish an undo table space and start automatic undo management and establish an undo retention time window. In this way, Oracle will maintain sufficient undo information in the undo table space to support the reverse query within the retention time.
· Since the size of the withdrawal table is directly determined the success or failure of the inquiry query. That is, the larger the table space, the sooner you can query, so the size of the general withdrawal table space, in order to ensure the success of the reverse query, try to query the data within 5 days, so that the probability of success is higher.
three. Task dispatch
In UNIX systems, the concept of task and processes is equivalent, that is, when the system executes a program code, it will automatically assign a process number and task number, so that the task can be operated using the process number and task number (such as Hang up, stop, start, etc.). The Oracle database also has task scheduling, for example, it is necessary to perform periodic execution of a certain operation, or when an event occurs. The general approach is to use triggers, which will be encapsulated in all operations, and then wait for the operation by specifying a trigger event. In addition, you can also implement the operating system directly. For example, in the Windows platform, you can write a Windows script and combine the Task Schedule to implement; if in the UNIX platform, you can write the shell to implement the periodic execution of the task.
And here is mainly implemented by the DBMS_JOB package of the Oracle database.
For example, since the employee needs to be evaluated every month to adjust the salary, the EMP_TABLE table needs to be updated. The update processing code is as follows: create or replace procedure salary_upt (v_emp_no varcha2, v_salary number) asbeginupdate EMP_TABLESET EMP_SALARY = v_salarywhere EMP_NO = V_EMP_NO; Commital; End; /
In order to run the above program periodically, you can do the following code:
Variable v_jobnum number; begin dbms_job.submit (: v_jobnum, 'salary_upt', sysdate, 'sysdate 30'); commit; end; /
Submit will make the Salary_upt process will be executed immediately after execution. In the above code, v_jobnum is the job number (task number) returned by the job, and the latter time is started and end time, so the SALARY_UPT process will execute a Salary_upt program every 30 days to reach regular updates. the goal of.
If you want to prohibit the continuation of the job, you can perform the following command:
DBMS_Job.Remove (: v_jobnum);
Using the DBMS_JOB package to achieve the schedule of tasks facilitates integration with the application, sometimes handling more conveniently.
four. summary
Many times, the functionality of the database can be expanded through the application. For users who perform background database development operations, it is necessary to master certain application development capabilities in addition to the overall architecture of the database. The system can be inspired by this article.
The development environment of this article is:
Server side: UNIX ORACLE9.2
Client: Windows2000 Pro TOAD (or SQL * Plus)
The code in this article has been commissioned in the above environment.
