Communicating data dictionary information of database objects through a redo stream

[0001] The present invention relates to providing metadata relating to the association between the internal numbers used by a database schema to identify database objects and the corresponding user-defined names of the database objects in the redo stream itself.

[0002] The occurrence of a hardware, software, network, process, or system failures in a database system could result in corruption, inconsistencies, or errors to database data. To prevent such problems from occurring, many database systems implement recovery mechanisms capable of restoring the database to a consistent and error-free state if such failures are detected. Recovery mechanisms typically utilize one or more recovery logs or recovery files (collectively referred to herein as “recovery logs”) to perform the recovery process. Recovery logs record information about the database data, such as changes to the database data or the state of database data as of a certain point in time.

[0003] A commonly implemented recovery log is a “redo log” or “redo stream”. Redo streams contain records of all changes made to objects in a database system, regardless of whether the changes are committed or uncommitted. For example, the redo log may record a stream of data containing the identity of each data item that changed in the database, as well as the precise change that is made to the data item. If a failure occurs, the redo stream can be used during the recovery process to “redo” any changes that occurred prior to the failure, to place the database in a consistent state as of a specific point in time.

[0004] Since recovery logs, such as redo logs or streams, effectively become a historical repository for all changes made to the database data, the information recorded in the recovery logs can be used for many purposes beyond just system recovery operations. For example, the redo stream information may be used to drive asynchronous applications that provide a variety of functionality, such as:

[0005] Logical Standby where a standby database shadows a primary database by extracting committed transactions out of the redo stream and applying them;

[0006] Log Based replication where a replica site extracts committed changes made to the tables of interest and applies them to keep the replica tables synchronized; and

[0007] Log Analysis whereby a user issues Structured Query Language (SQL) queries against a fixed view to determine changes applied to the database.

[0008] For example, the redo stream may be analyzed by processing each record in the redo stream to reconstruct the equivalent Data Manipulation Language (DML) statement. DMLs belonging to the same transaction are grouped together and committed transactions are returned to the application.

[0009] However, since redo records only identify the modified schema objects (or the associated columns) by internally generated numbers, log analysis and subsequent application of transactions generate output that is not easily readable by a person. In order to produce output that is more easily readable by a person, a data dictionary may be used to provide the mapping between the numbers and the corresponding user defined names. For example, Structured Query Language (SQL) statements use column names and table names that typically have meaning to a person, while the internal database schema identifies the corresponding columns and tables with internally generated numbers.

[0010] Thus, the need arises to generate and utilize a data dictionary to provide the mapping between the internal numbers and the corresponding user defined names so that applications that utilize the redo stream can generate output that is more easily readable by a person. One conventional technique saved the data dictionary information in a file that was separate from the redo stream. Use of this data dictionary then required the entries in the data dictionary to be temporally matched with the entries in the redo stream, which was a difficult and time-consuming process.

[0011] The need arises for a technique by which information needed to provide data dictionary information of database objects relating to the association between the internal numbers used by a database schema to identify database objects and the corresponding user-defined names of the database objects may be generated so that the association information may be easily and quickly used in the analysis of a redo stream of the database transactions.

[0012] The present invention is a system, method, and computer program product that provide data dictionary information of database objects in the form of metadata, which provides the association between the internal numbers used by a database schema to identify database objects and the corresponding user-defined names of the database objects, in the redo stream itself, so that the association information may be easily and quickly used in the analysis of the redo stream of the database transactions.

[0013] In one embodiment of the present invention, a method of communicating data dictionary information of database objects through a redo stream, comprising the steps of logging information relating to internal identifier mapping information associated with a first database data table to the redo stream, processing a transaction related to the first database data table, and logging redo information representing changes to the first database data table caused by processing of the transaction to the redo stream. The step of processing a transaction related to the first database data table may comprise the steps of receiving data manipulation language statements representing a transaction, and performing the data manipulation language statements to cause a change to a first database data table. The step of logging redo information representing changes to the first database data table caused by processing of the transaction to the redo stream may comprise the steps of generating redo information representing the change to the first database data table, and logging the redo information representing the change to the first database data table to the redo stream. The step of logging information relating to internal identifier mapping information associated with the first database data table to the redo stream may comprise the steps of inserting information relating to internal identifier mapping information associated with the first database data table into a second database data table and logging the information representing the information inserted into the second database data table to the redo stream.

[0014] In one aspect of the present invention, the information relating to internal identifier mapping information comprises information relating to mapping information from internal identifiers of schema objects of the database to user readable information relating to the schema objects of the database. The user readable information may comprise at least one of a name of a schema object of the database and a datatype of a schema object of the database.

[0015] In one aspect of the present invention, the information relating to internal identifier mapping information comprises metadata. The metadata may comprise information relating to mapping information from internal identifiers of schema objects of the database to user readable information relating to the schema objects of the database. The user readable information may comprise at least one of a name of a schema object of the database and a datatype of a schema object of the database.

[0016] In one aspect of the present invention, the method further comprises the step of processing the redo stream. The step of processing the redo stream may comprise the steps of analyzing the information in the redo relating to the information representing the information inserted into the second database data table to generate a data dictionary table, and using the data dictionary table to supply a mapping between internal identifiers of schema objects of the database and user readable information relating to the schema objects of the database. The user readable information may comprise at least one of a name of a schema object of the database and a datatype of a schema object of the database. The information relating to internal identifier mapping information may comprise metadata.

[0024] The present invention is a system, method, and computer program product that provide metadata, which provides the association between the internal numbers used by a database schema to identify database objects and the corresponding user-defined names of the database objects, in the redo stream itself, so that the association information may be easily and quickly used in the analysis of the redo stream of the database transactions.

[0025] An exemplary database management system (DBMS) 102, in which the present invention may be implemented, is shown in FIG. 1. Database management system (DBMS) 102 provides the capability to store, organize, modify, and extract information from one or more databases included in DBMS 102. From a technical standpoint, DBMSs can differ widely. The terms relational, network, flat, and hierarchical all refer to the way a DBMS organizes information internally. The internal organization can affect how quickly and flexibly you can extract information.

[0026] Each database included in DBMS 102 includes a collection of information organized in such a way that computer software can select and retrieve desired pieces of data. Traditional databases are organized by fields, records, and files. A field is a single piece of information; a record is one complete set of fields; and a file is a collection of records. An alternative concept in database design is known as Hypertext. In a Hypertext database, any object, whether it be a piece of text, a picture, or a film, can be linked to any other object. Hypertext databases are particularly useful for organizing large amounts of disparate information, but they are not designed for numerical analysis.

[0027] Typically, a database includes not only data, but also low-level database management functions, which perform accesses to the database and store or retrieve data from the database. Such functions are often termed queries and are performed by using a database query language, such as Structured Query Language (SQL). SQL is a standardized query language for requesting information from a database. Historically, SQL has been a popular query language for database management systems running on minicomputers and mainframes. Increasingly, however, SQL is being supported by personal computer database systems because it supports distributed databases (databases that are spread out over several computer systems). This enables several users on a local-area network to access the same database simultaneously.

[0028] Most full-scale database systems are relational database systems. Small database systems, however, use other designs that provide less flexibility in posing queries. Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways. An important feature of relational systems is that a single database can be spread across several tables. This differs from flat-file databases, in which each database is self-contained in a single table.

[0029] DBMS 102 may also include one or more database applications, which are software that implements a particular set of functions that utilize one or more databases. Examples of database applications include:

[0030] computerized library systems

[0031] automated teller machines

[0032] flight reservation systems

[0033] computerized parts inventory systems

[0034] Typically, a database application, includes data entry functions and data reporting functions. Data entry functions provide the capability to enter data into a database. Data entry may be performed manually, by data entry personnel, automatically, by data entry processing software that receives data from connected sources of data, or by a combination of manual and automated data entry techniques. Data reporting functions provide the capability to select and retrieve data from a database and to process and format that data for other uses. Typically, retrieved data is used to display information to a user, but retrieved data may also be used for other functions, such as account settlement, automated ordering, numerical machine control, etc.

[0035] DBMS 102 includes one or more databases, such as database 104. Database 104 includes one or more data tables. One or more streams of transactions, such as transaction stream 106, are input to DBMS 102. A transaction is any database operation that may result in a change to database 104 or to the data stored in database 104. Each transaction includes one or more Data Manipulation Language (DML) statements 107, which are used to store, retrieve, modify, and erase data from database 104. The performance of the DML statements 107 making up each transaction results in changes being made to the data stored in database 104. These changes are used to generate redo stream 108, which may be output from DBMS 102. Redo stream 108 includes a plurality of redo records, in which each redo record specifies one or more changes that were made to the database or to the data stored in the database.

[0036] Transaction stream 106 includes a plurality of transactions, which include commands and/or statements that cause the performance of database operations that may result in a change to database 104 or to the data stored in database 104. The commands and/or statements included in transaction stream 106 may be DML statements, or they may be higher-level commands, such as Application Program Interface (API) calls. Where transaction stream 106 includes API calls, these calls typically are converted to DML statements 107, in order for the transactions to be performed. Where transaction stream 106 includes DML statements, in some embodiments, the DML statements may be performed directly, while in other embodiments, the DML statements may be converted to lower-level DML statements, which are then performed. For example, in some embodiments, transaction stream 106 may include DML statements, such as SQL statements, the SQL statements may be performed directly. In other embodiments, transaction stream 106 may include DML statements, such as SQL statements, but the SQL statements are converted to lower-level DML statements, which are then performed.

[0037] Typically, the redo records included in redo stream 108 are processed to reconstruct the equivalent DML statement that produced them. DML statements belonging to the same transaction are grouped together and committed transactions are returned to the application. Since redo records identify the database objects affected by the transactions by internally generated numbers, in order to perform log analysis and subsequent application of transactions, a data dictionary is needed to provide the mapping between the internally generated numbers and the corresponding user defined names. For example, Structured Query Language (SQL) statements use column names and table names that typically have meaning to a person, while the internal database schema identifies the corresponding columns and tables with internally generated numbers.

[0038] The present invention includes in redo stream 108 metadata that provides the mapping between the internally generated numbers and the corresponding user defined names, as shown in FIG. 2. As shown in FIG. 2, redo stream 108 includes redo marker 201 and metadata, such as metadata 202, that provides the mapping between the internally generated numbers and the corresponding user defined names. Redo marker 201 indicates that metadata 202 is present in redo stream 108 and preferably, immediately follows redo marker 201. Redo marker 201 is generated upon performance of the procedure that causes the generation of metadata 202. Redo stream 108 also includes redo data, such as redo data 204, 206, 208, etc., that is generated by processing transactions in DBMS 102.

[0039] An example of a technique by which metadata that provides the mapping between the internally generated numbers and the corresponding user defined names may be generated and included in the redo stream is shown in FIG. 3. As shown in FIG. 3, database 104 include one or more data tables, such as data table 302, and one or more metadata tables, such as metadata table 304. A table, such as data table 302 or metadata table 304, is a basic unit of storage in a relational or object-relational database system. Each table typically comprises one or more rows and columns, such as columns 306A–C of data table 302 and columns 308A–C of metadata table 304 and rows 310A–C of data table 302 and rows 312A–C of metadata table 304. Each column is typically associated with a column name and a column datatype. A row comprises a set of related column data, and normally corresponds to a single database record.

[0040] DMLs 107 specify actions that are performed that cause changes to data table 302. The performance of DMLs 107 generates redo records 314, which are included in redo stream 108. In order to include metadata in redo stream 108, the database executes a procedure 316 that performs DMLs on metadata table 304. In particular, in procedure 316, database 104 reads data from metadata source 305 and inserts that data into metadata table 304. Metadata source 305 includes system metadata such as names of data tables, column data types, etc., that are typically user-defined. Metadata source typically includes such system objects as the system data dictionary, etc. Since metadata table 304 is a data table in database 104, the performance of insertion of data into metadata table 304 by procedure 316 causes the generation of redo records that include the inserted metadata, metadata records 318, by the same redo logging mechanism that generates redo records for any data table in database 104. Thus, metadata records 318 are similar in format to other redo records, but different in content. Metadata records 318 include mapping information from internal identifiers for schema objects and their names. Because they are similar in format to redo records, and are generated by the same mechanism, metadata records 318 may be readily included in redo stream 108 along with redo records 314.

[0041] In order to distinguish metadata records 318 from redo records 314, metadata records 318 are marked by the logging of a special marker to the redo logging mechanism. Metadata source 305 is locked while it is being read, so that the snapshot of metadata taken can not be changed during the extraction. Thus, a consistent snapshot of the metadata is generated, and this snapshot is consequently reflected, through the redo logging mechanism, in the redo stream.

[0042] “Metadata” is information in a database system that describes and defines other data. Metadata information includes data that describes the structure and parameters of tables and data maintained in the system, such as the exact column configuration or column “signature” of database tables. In many database systems, metadata is maintained separately from its associated data and tables, and is collected into a central “data dictionary.” Thus, the data dictionary normally contains the foundational data that sets forth the basic structures of storage entities in a database system. When creating a new database table, the schema definition of that new table must be entered in the data dictionary before it is recognized by the system. If the schema definition of a table is changed (e.g., a column is to be added or dropped from the table), the associated metadata for that table must be modified in the data dictionary before the schema change is recognized.

[0043] An exemplary block diagram of a database management system (DBMS) 102, in which the present invention may be implemented, is shown in FIG. 4. DBMS 102 is typically a programmed general-purpose computer system, such as a personal computer, workstation, server system, and minicomputer or mainframe computer. DBMS 102 includes one or more processors (CPUs) 402A–402N, input/output circuitry 404, network adapter 406, and memory 408. CPUs 402A–402N execute program instructions in order to carry out the functions of the present invention. Typically, CPUs 402A–402N are one or more microprocessors, such as an INTEL PENTIUM® processor. FIG. 4 illustrates an embodiment in which DBMS 102 is implemented as a single multi-processor computer system, in which multiple processors 402A–402N share system resources, such as memory 408, input/output circuitry 404, and network adapter 406. However, the present invention also contemplates embodiments in which DBMS 102 is implemented as a plurality of networked computer systems, which may be single-processor computer systems, multi-processor computer systems, or a mix thereof.

[0044] Input/output circuitry 404 provides the capability to input data to, or output data from, database/DBMS 102. For example, input/output circuitry may include input devices, such as keyboards, mice, touchpads, trackballs, scanners, etc., output devices, such as video adapters, monitors, printers, etc., and input/output devices, such as, modems, etc. Network adapter 406 interfaces database/DBMS 102 with Internet/intranet 410. Internet/intranet 410 may include one or more standard local area network (LAN) or wide area network (WAN), such as Ethernet, Token Ring, the Internet, or a private or proprietary LAN/WAN.

[0045] Memory 408 stores program instructions that are executed by, and data that are used and processed by, CPU 402 to perform the functions of DBMS 102. Memory 408 may include electronic memory devices, such as random-access memory (RAM), read-only memory (ROM), programmable read-only memory (PROM), electrically erasable programmable read-only memory (EEPROM), flash memory, etc., and electro-mechanical memory, such as magnetic disk drives, tape drives, optical disk drives, etc., which may use an integrated drive electronics (IDE) interface, or a variation or enhancement thereof, such as enhanced IDE (EIDE) or ultra direct memory access (UDMA), or a small computer system interface (SCSI) based interface, or a variation or enhancement thereof, such as fast-SCSI, wide-SCSI, fast and wide-SCSI, etc, or a fiber channel-arbitrated loop (FC-AL) interface.

[0046] In the example shown in FIG. 4, memory 408 includes database management routines 410, database 104, data tables 414, metadata tables 416, and operating system 418. Database management routines 410 include software routines that provide the database management functionality of DBMS 102. Database management routines typically include a database query language interface, such as a Structured Query Language (SQL) interface, a Data Manipulation Language (DML) interface, and database processing routines to carry out the data manipulations specified by the DML. For example, an SQL interface accepts database queries using the SQL database query language, converts the queries to a series of DML statements, calls the database processing routines to perform the series of DMLs, and returns the results of the query to the source of the query.

[0047] Database 412 includes a collection of information organized in such a way that computer software can select, store, and retrieve desired pieces of data. Typically, database 412 includes a plurality of data tables, such as data tables 414. A data table is a basic unit of storage in a relational or object-relational database system. Each table typically comprises one or more rows and columns. Each column is typically associated with a column name and a column datatype. A row comprises a set of related column data, and normally corresponds to a single database record.

[0048] Database 104 also includes metadata tables 416. Metadata tables 416 include system metadata related to mapping information from internal identifiers for schema objects and their names that has been inserted. The metadata is inserted into metadata tables 304 by the performance of procedure 316.

[0049] “Metadata” is information in a database system that describes and defines other data. Metadata information includes data that describes the structure and parameters of tables and data maintained in the system, such as the exact column configuration or column “signature” of database tables. In many database systems, metadata is maintained separately from its associated data and tables, and is collected into a central “data dictionary.” Thus, the data dictionary normally contains the foundational data that sets forth the basic structures of storage entities in a database system. When creating a new database table, the schema definition of that new table must be entered in the data dictionary before it is recognized by the system. If the schema definition of a table is changed (e.g., a column is to be added or dropped from the table), the associated metadata for that table must be modified in the data dictionary before the schema change is recognized.

[0050] Operating system 418 provides overall system functionality.

[0051] As shown in FIG. 4, the present invention contemplates implementation on a system or systems that provide multi-processor, multi-tasking, multi-process, and/or multi-thread computing, as well as implementation on systems that provide only single processor, single thread computing. Multi-processor computing involves performing computing using more than one processor. Multi-tasking computing involves performing computing using more than one operating system task. A task is an operating system concept that refers to the combination of a program being executed and bookkeeping information used by the operating system. Whenever a program is executed, the operating system creates a new task for it. The task is like an envelope for the program in that it identifies the program with a task number and attaches other bookkeeping information to it. Many operating systems, including UNIX®, OS/2®, and WINDOWS®, are capable of running many tasks at the same time and are called multitasking operating systems. Multi-tasking is the ability of an operating system to execute more than one executable at the same time. In some systems each executable is running in its own address space, meaning that the executables have no way to share any of their memory. This has advantages, because it is impossible for any program to damage the execution of any of the other programs running on the system. However, the programs have no way to exchange any information except through the operating system (or by reading files stored on the file system). In other systems, the tasks may communicate and/or operate on one or more portions of shared memory. Multi-process computing is similar to multi-tasking computing, as the terms task and process are often used interchangeably, although some operating systems make a distinction between the two.

[0052] A process 500 of operation of the present invention is shown in FIG. 5. It is best viewed in conjunction with FIG. 3. Process 500 begins with step 502, in which the DBMS is commanded to capture the metadata to be included in the redo stream. Typically, this command is expressly issued by a user or administrator of the DBMS, but in some cases, the command may be automatically or implicitly issued. In response to the command, in step 504, metadata records are generated and included in the redo stream, as is further described below.

[0053] In step 506, DMLs representing a transaction are received by database 104. In step 508, the received DMLs are performed and changes made to data tables, such as data table 302. The performance of the DMLs causes the generation of redo records by the redo logging mechanism. In step 510, the redo records that were generated in step 508 are logged in the redo stream. In step 512, the redo stream is processed, typically using an application. For example, an asynchronously executing application may analyze the captured system metadata from the redo stream and load it into data dictionary tables, which can then be used to supply the needed mapping between identification numbers and user-defined names. Column data type information also may be available.

[0054] A sub-process 600 of step 504 of FIG. 5 is shown in FIG. 6. It is best viewed in conjunction with FIG. 3. Process 600 begins with step 602, in which metadata source 305 is locked so that the snapshot of metadata taken can not be changed during the extraction. Metadata source 305 includes system metadata such as names of data tables, column data types, etc., that are typically user-defined. Metadata source typically includes such system objects as the system data dictionary, etc. In step 604, metadata source 305 is read, that is, metadata is extracted from metadata source 305. In step 606, the metadata extracted from metadata source 305 is inserted into metadata table 304. The insertion of the metadata to metadata table 304 causes the generation of redo records that include the inserted metadata, metadata records 318, by the same redo logging mechanism that generates redo records for any data table in the database. Thus, metadata records 318 are similar in format to other redo records, but different in content. Metadata records 318 include mapping information from internal identifiers for schema objects and their names. Because they are similar in format to redo records, and are generated by the same mechanism, metadata records may be readily included in redo stream 108 along with redo records.

[0055] In step 608, metadata records 318 are logged to redo stream 108. In order to distinguish metadata records from the regular redo records, metadata records are marked by the logging of a special marker, redo marker 201, shown in FIG. 2, to the redo logging mechanism.

[0056] It is important to note that while the present invention has been described in the context of a fully functioning data processing system, those of ordinary skill in the art will appreciate that the processes of the present invention are capable of being distributed in the form of a computer readable medium of instructions and a variety of forms and that the present invention applies equally regardless of the particular type of signal bearing media actually used to carry out the distribution. Examples of computer readable media include recordable-type media such as floppy disc, a hard disk drive, RAM, and CD-ROM's, as well as transmission-type media, such as digital and analog communications links.

[0057] Although specific embodiments of the present invention have been described, it will be understood by those of skill in the art that there are other embodiments that are equivalent to the described embodiments. Accordingly, it is to be understood that the invention is not to be limited by the specific illustrated embodiments, but only by the scope of the appended claims.