Systems, methods and software for automating database tasks

• US 7,440,973 B2
• Filed: 07/29/2004
• Issued: 10/21/2008
• Est. Priority Date: 07/29/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Tsukerman, Alex, Ramacher, Mark, Voss, Eric P., Takahara, Mineharu, Chen, Jane Xin, Draaller, Jacco
• Primary Examiner(s): PANNALA, SATHYANARAYA R
• Application Number:
  US10/903,594
• Publication Number:
  US 20060026212A1
• Time in Patent Office: 1,545 Days
• Field of Search: 707/200, 700/98, 702/62, 718/101
• US Class Current: 1/1
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing
• CPC Class Codes: Y10S 707/99948 Application of database or data structure, e.g. distributed, multimedia, or image

Abstract

Various embodiments of the invention provide solutions to facilitate the management and/or automation of various database tasks, in particular system tasks. Merely by way of example, some embodiments provide methods systems and/or software products that can facilitate automation of such tasks. In accordance with certain embodiments, a maintenance window may be defined. The maintenance window can provide an opportunity for system tasks (and/or other tasks, especially those that consume significant database resources) to execute without significantly impacting the usability of the database. In particular embodiments, a maintenance window may be defined to coincide with a period of relatively low user activity. In some cases, a maintenance window may be implemented in conjunction with a job scheduler and/or a resource manager. Merely by way of example, a resource manager might allocate relatively more system and/or database resources to system tasks during the maintenance window, and/or a job scheduler may schedule system tasks to execute during the maintenance window.

Claims

1. In a database environment, a computer-implemented method for managing system resources of a computer system, the method comprising:

defining a plurality of maintenance windows during which system tasks may be executed by the computer system without significantly impacting user tasks, the plurality of maintenance windows comprising a first maintenance window and a second maintenance window;

scheduling a first system task to execute during the first maintenance window;

scheduling a second system task to execute during the second maintenance window;

allocating system resources for the first system task during the first maintenance window; and

inhibiting the at least one system task from executing on the computer system, except during the first maintenance window;

wherein;

the first maintenance window is defined to recur periodically over a first interval, such that the first system task executes periodically over the first interval; and

the second maintenance window is defined to recur periodically over a second interval, such that the second system task executes periodically over the second interval.

2. A method for managing system resources as recited in claim 1, the method further comprising:

estimating whether a duration of the first maintenance window will be sufficient to allow complete execution of the first system task.

3. A method for managing system resources as recited in claim 2, the method farther comprising:

issuing a warning if it is estimated that the duration of the first maintenance window will not be sufficient to allow complete execution of the first task.

4. A method for managing system resources as recited in claim 2, the method further comprising:

disabling the first system task if it is estimated that the duration of the first maintenance window will not be sufficient to allow complete execution of the first system task.

5. A method for managing system resources as recited in claim 2, the method further comprising:

extending the first maintenance window if it is estimated that the duration of the first maintenance window will not be sufficient to allow complete execution of the at least one system task.

6. A method for managing system resources as recited in claim 1, the method further comprising:

evaluating the first system task to determine whether the first system task likely will consume sufficient resources to impact user tasks.

7. A method for managing system resources as recited in claim 6, the method further comprising:

determining, based on an evaluation of the first system task, that the first system task should be inhibited from executing except during the first maintenance window.

8. A method for managing system resources as recited in claim 1, the method further comprising:

establishing a resource plan that defines how system resources should be allocated among a plurality of competing tasks, the plurality of competing tasks comprising the at least one user task and the first system task.

9. A method for managing system resources as recited in claim 8, the method further comprising:

modifying the resource plan to allocate relatively more system resources to the first system task during the first maintenance window.

10. A method for managing system resources as recited in claim 8, wherein the resource plan is a first resource plan, and wherein the first resource plan defines how system resources should be allocated outside the first maintenance window, the method further comprising:

establishing a second resource plan that defines how system resources should be allocated among the plurality of competing tasks during the first maintenance window, wherein the second resource plan allocates relatively more system resources to the first system task during the first maintenance window than does the first resource plan.

11. A method for managing system resources as recited in claim 1, the method further comprising:

inhibiting the execution of the at least one user task during the first maintenance window.

12. A method for managing system resources as recited in claim 1, the method further comprising:

allowing a user to schedule a user task to run as a system task during the first maintenance window.

13. A method for managing system resources as recited in claim 1, wherein defining a plurality of maintenance windows comprises automatically defining at least one maintenance window.

14. A method for managing system resources as recited in claim 1, wherein defining a plurality of maintenance windows comprises:

analyzing a historical pattern of user activity for the database, wherein the historical pattern of user activity comprises at least one periodic segment of relatively high user activity and at least one periodic segment of relatively low user activity; and

defining at least one maintenance window that coincides with the at least one periodic segment of relatively low user activity.

15. A method for managing system resources as recited in claim 1, wherein defining a plurality of maintenance windows comprises allowing a user to define at least one maintenance window.

16. A method for managing system resources as recited in claim 1, wherein the plurality of maintenance windows comprises a first maintenance window and a second maintenance window, the method further comprising:

defining a maintenance window group, the maintenance window group comprising the first maintenance window and the second maintenance window.

17. A method for managing system resources as recited in claim 1, wherein the first interval is not the same as the second interval.

18. A method for managing system resources as recited in claim 1, wherein the first interval is offset from the second interval, such that the first maintenance window and the second maintenance window do not occur at the same time.

19. A method for managing system resources as recited in claim 1, the method further comprising:

if the first system task has not finished executing before the first maintenance window has expired, allowing the first system task to finish executing outside the first maintenance window.

20. A method for managing system resources as recited in claim 1, wherein allocating resources for the first system task comprises allocating fewer system resources for the at least one user task during the first maintenance window than at other times, and allocating more system resources for the first system task during the first maintenance window than at other times.

21. In a database environment, a computer-implemented method for managing system resources of a computer system, the method comprising:

defining a maintenance window during which system tasks may be executed by the computer system without significantly impacting user tasks;

scheduling at least one system task to execute during the maintenance window;

allocating system resources for the at least one system task during the maintenance window; and

inhibiting the at least one system task from executing on the computer system, except during the defined maintenance window; and

if the at least one system task has not finished executing before the maintenance window has expired, stopping the execution of the at least one system task at the expiration of the maintenance window;

wherein the at least one system task comprises an operation selected from the group consisting of a purge operation, a statistic gathering operation, a database analysis operation, a feature usage analysis operation, a backup operation and a database replication operation.

22. A method for managing system resources as recited in claim 21, the method further comprising:

rolling back a result of the at least one system task to a state that existed before the execution of the at least one system task.

23. A method for managing system resources as recited in claim 21, the method further comprising:

if the execution of the at least one system task is stopped before the at least one system task has fully executed, saving a state of the at least one system task.

24. A method for managing system resources as recited in claim 23, the method further comprising:

resuming during a subsequent maintenance window the at least one system task, based on the saved state of the at least one system task.

25. A method for managing system resources as recited in claim 21, the method further comprising:

if the execution of the at least one system task is stopped before the at least one system task has fully executed, notifying an administrator that the at least one system task failed to fully execute.

26. A method for managing system resources as recited in claim 21, the method further comprising:

if the execution of the at least one system task is stopped before the at least one system task has fully executed, allowing an administrator to increase the duration of a subsequent maintenance window to allow the at least one system task to fully execute during the subsequent maintenance window.

27. A method for managing system resources as recited in claim 21, the method further comprising:

if the execution of the at least one system task is stopped before the at least one system task has fully executed, automatically increasing the duration of a subsequent maintenance window to allow the at least one system task to fully execute during the subsequent maintenance window.

28. A computer system comprising:

a database;

a processor in communication with the database; and

a computer readable medium in communication with the processor, the computer readable medium comprising instructions executable by the processor to;

define a plurality of maintenance windows for the database during which system tasks may be executed by the computer system without significantly impacting user tasks, the plurality of maintenance windows comprising a first maintenance window and a second maintenance window;

schedule a first system task to execute during the maintenance window;

schedule a second system task to execute during the second maintenance window;

allocate system resources to the first system task during the first maintenance window; and

inhibit the first system task from executing except during the first maintenance window;

wherein;

the first maintenance window is defined to recur periodically over a first interval, such that the first system task executes periodically over the first interval; and

the second maintenance window is defined to recur periodically over a second interval, such that the second system task executes periodically over the second interval.

29. A computer-readable storage medium having stored thereon a computer program for managing system resources for a database, the computer program comprising instructions executable by a computer system to:

define a plurality of maintenance windows for the database during which system tasks may be executed by the computer system without significantly impacting user tasks, the plurality of maintenance windows comprising a first maintenance window and a second maintenance window;

schedule a first system task to execute during the maintenance window, to mitigate an impact imposed on a least one user task by the execution of the first system task by the computer system;

schedule a second system task to execute during the second maintenance window;

allocate system resources to the first system task during the first maintenance window; and

inhibit the first system task from executing except during the first maintenance window;

wherein;

the first maintenance window is defined to recur periodically over a first interval, such that the first system task executes periodically over the first interval; and

the second maintenance window is defined to recur periodically over a second interval, such that the second system task executes periodically over the second interval.

30. A computer program as recited in claim 29, wherein the computer program is incorporated in a relational database management system.

31. A computer program as recited in claim 29, wherein the computer program is configured to interoperate with a relational database management system.

32. A computer system comprising:

a database;

a processor in communication with the database; and

a computer readable medium in communication with the processor, the computer readable medium comprising instructions executable by the processor to;

define a maintenance window during which system tasks may be executed by the computer system without significantly impacting user tasks;

schedule at least one system task to execute during the maintenance window;

allocate system resources for the at least one system task during the maintenance window; and

inhibit the at least one system task from executing on the computer system, except during the defined maintenance window; and

if the at least one system task has not finished executing before the maintenance window has expired, stop the execution of the at least one system task at the expiration of the maintenance window;

wherein the at least one system task comprises an operation selected from the group consisting of a purge operation, a statistic gathering operation, a database analysis operation, a feature usage analysis operation, a backup operation and a database replication operation.

33. A computer-readable storage medium having stored thereon a computer program for managing system resources for a database, the computer program comprising instructions executable by a computer system to:

define a maintenance window during which system tasks may be executed by the computer system without significantly impacting user tasks;

schedule at least one system task to execute during the maintenance window;

allocate system resources for the at least one system task during the maintenance window; and

inhibit the at least one system task from executing on the computer system, except during the defined maintenance window; and

if the at least one system task has not finished executing before the maintenance window has expired, stop the execution of the at least one system task at the expiration of the maintenance window;

wherein the at least one system task comprises an operation selected from the group consisting of a purge operation, a statistic gathering operation, a database analysis operation, a feature usage analysis operation, a backup operation and a database replication operation.