Method and mechanism of handling reporting transactions in database systems

• US 20060190460A1
• Filed: 02/18/2005
• Published: 08/24/2006
• Est. Priority Date: 02/18/2005
• Status: Abandoned Application

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Pruscino, Angelo, Chandrasekaran, Sashikanth
• Application Number:
  US11/061,152
• Publication Number:
  US 20060190460A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 11/1474 in transactions G06F16/20 takes precedence
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06F 2201/84 Using snapshots, i.e. a logical point-in-time copy of the data

Abstract

Disclosed are improved methods, systems, and mediums for handling reporting transactions in database systems. In some embodiments, database snapshots are used to carry out reporting transactions on a failover node concurrently with execution of non-reporting transactions on a primary node.

Claims

1. A method of handling reporting transactions in database systems, the method comprising:

taking a snapshot of a database, wherein the database is linked to a primary node and a failover node;

executing one or more non-reporting transactions on the primary node; and

utilizing the snapshot to carry out a reporting transaction on the failover node concurrently with the execution of the one or more non-reporting transactions on the primary node.

2. The method of claim 1, further comprising:

creating one or more temporary tables on the failover node, wherein the one or more temporary tables are used when the reporting transaction is carried out on the failover node.

3. The method of claim 2, wherein the one or more temporary tables are created through a query script in the reporting transaction.

4. The method of claim 2, wherein at least one of the one or more temporary tables is accessible to more than one query in the reporting transaction.

5. The method of claim 1, further comprising:

modifying one or more schemas in the database, wherein the one or more schemas are used when the reporting transaction is carried out on the failover node.

6. The method of claim 5, wherein the one or more schemas are not accessible to the one or more non-reporting transactions executing on the primary node.

7. The method of claim 5, wherein at least one of the one or more schemas includes one or more tables.

8. The method of claim 1, further comprising:

accessing one or more user-defined procedures on the primary node, wherein the one or more user-defined procedures are used when the reporting transaction is carried out on the failover node.

9. The method of claim 1, further comprising:

reserving a temporary space in the database, wherein the temporary space is used when the reporting transaction is carried out on the failover node.

10. The method of claim 1, wherein the primary node and the failover node are part of a cluster.

11. The method of claim 10, wherein the cluster includes one or more additional failover nodes.

12. The method of claim 1, wherein at least one of the one or more non-reporting transactions is a read-write transaction.

13. The method of claim 1, wherein the reporting transaction and the one or more non-reporting transactions are part of a workload.

14. The method of claim 1, wherein the reporting transaction provides near real-time reporting.

15. The method of claim 1, wherein only the primary node can modify the database.

16. The method of claim 1, wherein the snapshot is taken in response to a user command.

17. The method of claim 1, wherein the snapshot is read-only.

18. The method of claim 1, wherein the snapshot cannot be modified by the primary node.

19. The method of claim 1, wherein the snapshot and the database share a disk space.

20. The method of claim 1, wherein the snapshot is the most current.

21. The method of claim 1, wherein the snapshot is directly used to carry out the reporting transaction on the failover node.

22. A computer program product that includes a computer readable medium, the computer readable medium comprising instructions which, when executed by a processor, causes the processor to execute a process for handling reporting transactions in database systems, the process comprising:

taking a snapshot of a database, wherein the database is linked to a primary node and a failover node;

executing one or more non-reporting transactions on the primary node; and

utilizing the snapshot to carry out a reporting transaction on the failover node concurrently with the execution of the one or more non-reporting transactions on the primary node.

23. A system for handling reporting transactions in database systems, the system comprising:

means for taking a snapshot of a database, wherein the database is linked to a primary node and a failover node;

means for executing one or more non-reporting transactions on the primary node; and

means for utilizing the snapshot to carry out a reporting transaction on the failover node concurrently with the execution of the one or more non-reporting transactions on the primary node.