A real-time data collection method based on ORACLE backup database

Abstract

The invention discloses a real-time data collection method based on an ORACLE standby database, comprising: creating a separate new Oracle instance based on a physical machine where the Oracle standby database is located; creating a database dictionary file Flat File based on an Oracle main database; screening the log files of the original instance in the Oracle standby database to obtain path information corresponding to the log files to be parsed; parsing the log files to be parsed using the database dictionary file on the new Oracle instance, and pushing the parsing results to a target component. The method avoids the collection process occupying the main database resources and thus affecting the business system, and is a method for real-time collection of Oracle data based on the Oracle standby database.

Description

Technical Field

[0001] The present invention relates to the field of computer data processing, and in particular to a real-time data acquisition method based on an ORACLE backup database. Background Art

[0002] In today's information age, data has become an important asset for enterprises, and as the main carrier for data storage and processing, the management and maintenance of databases have become crucial. As the world's largest database software manufacturer, Oracle Database is widely used in various industries, including finance, retail, manufacturing, government, education, etc. With the development of business, enterprises' demand for data is also growing, and higher requirements are also put forward for real-time data collection and processing. Compared with other open source databases, since Oracle Database is closed source, the Oracle collection tools widely used in the market are basically commercial products, with high fees and high cost pressure.

[0003] There are three common Oracle real-time data collection methods: the official Oracle GoldenGate tool; using LogMiner to parse the Redo Log and Archive Log of the primary database; using LogMiner to parse the Standby RedoLog and Standby Archive Log of the standby database. However, Oracle GoldenGate is a paid product, and the charging standard is relatively high, which puts a great cost pressure on small and medium-sized enterprises. Using the LogMiner tool to directly parse the Redo Log and Archive Log of the primary database to collect data may affect the performance of the primary database due to resource occupation in the actual production environment, and thus potentially interfere with the smoothness of business operations. Using LogMiner to parse the Standby Redo Log and Standby Archive Log of the standby database, because the relevant processes in the Oracle standby database will lock the Standby Log file in the Active state, the LogMiner process cannot parse it at the same time. The lock will only be released when the Standby Log file is switched, resulting in a large delay in the parsing result of LogMiner, and real-time parsing cannot be achieved.

[0004] Therefore, in order to avoid the collection process occupying the main database resources and thus affecting the business system, a method for real-time collection of Oracle data based on the Oracle standby database is urgently needed. Summary of the invention

[0005] In view of the deficiencies in the background technology, the present invention provides a real-time data collection method based on an ORACLE standby database; wherein the method solves the problem that log files in the standby database cannot be parsed in real time.

[0006] The present invention provides a real-time data collection method based on an ORACLE standby database, comprising:

[0007] S1: Create a separate new Oracle instance based on the physical machine where the Oracle standby database is located; in specific implementation, the relevant processes in the Oracle standby database will lock the data log files, making it impossible for the collection process to operate on them at the same time. However, since this lock is limited to the Oracle instance where these processes are located rather than the operating system where the instance is located, creating a new Oracle instance on the same physical machine can solve this problem. Therefore, the new Oracle instance is used for real data collection operations. S2: Create a database dictionary file Flat File based on the Oracle main database. Data collection requires parsing the data based on the corresponding database dictionary information, but the dictionary information corresponding to the database to be collected cannot be obtained in the new Oracle instance. It is necessary to generate a database dictionary file in the Oracle main database for use in the subsequent Oracle new instance collection process.

[0008] S3: Filter the log files of the original instance in the Oracle standby database to obtain the log files to be parsed and the corresponding path information. The log parsing operation in the new instance depends on the log file path filtered out in the original instance of the standby database.

[0009] S4: Use the database dictionary file to parse the log file to be parsed on the new Oracle instance, and push the parsing results to the target component.

[0010] Furthermore, the specific process of creating a database dictionary file based on the Oracle master library in S2 is as follows:

[0011] S21: Create a directory object in the Oracle master database;

[0012] S22: Generate a dictionary file Flat File according to the directory object and the file name.

[0013] Furthermore, the log files to be parsed in S3 are obtained by connecting to the original instance in the Oracle standby database through the Sqlplus client, executing the filter file SQL, and obtaining the path information of the log files to be parsed;

[0014] The screening criteria of the screening file SQL are: screening and grouping based on V$LOGFILE, V$ARCHIVED_LOG, and V$STANDBY_LOG system view records; wherein the basis includes archiving status, log file type, and archiving destination.

[0015] Furthermore, the log files to be parsed are Standby Redo Log and Standby Archive Log.

[0016] Furthermore, the specific process of S4 is as follows:

[0017] S41: Connect to the new instance through the Sqlplus client and add the path information of the log file to be parsed to the Logminer process;

[0018] S42: Logminer selects the data dictionary file Flat File in the new instance to perform log parsing and obtains a parsing result; the parsing result is a DML or DDL SQL statement;

[0019] S43: setting filtering conditions in the new instance to query the parsing results;

[0020] S44: By monitoring the changes of the standby log and archive log files to be parsed in real time, the latest log files are continuously parsed and the parsing results are pushed to the external storage (Kafka), thus realizing the real-time collection of Oracle standby database data.

[0021] Furthermore, the filtering condition includes one or more of a library name, a table name, a time range, and an SCN range.

[0022] Beneficial Effects

[0023] The present invention proposes a real-time data collection method based on an ORACLE standby database. The method solves the problem of not being able to use LogMiner to parse logs in real time on an Oracle standby database by creating a new separate Oracle instance based on the standby database and cooperating with the original instance of the standby database, thereby improving the timeliness of Oracle standby database data collection and avoiding the stability risk caused by the main database collection. At the same time, the real-time collection of Oracle data can significantly improve the processing speed and application efficiency of data, help enterprises quickly respond to market changes and customer needs, optimize business processes, and improve the competitiveness and innovation capabilities of enterprises. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0025] Figure 1 The present invention provides a flowchart of a method for real-time data collection based on an ORACLE standby database. DETAILED DESCRIPTION

[0026] To make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be described in detail below. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other implementation methods obtained by ordinary technicians in this field without creative work belong to the scope of protection of the present invention.

[0027] The mainstream Oracle master-slave solution in the industry is ADG. Parsing the Oracle standby database actually uses LogMiner to parse the Standby Log of the ADG standby database, and then combines the Archive Log generated by the rollover of the ADG database Standby Log to obtain all the operational changes of the database; because the relevant processes in the ADG standby database will lock the Standby Log file in the Active state, the LogMiner process cannot parse it at the same time, and the lock will only be released when the Standby Log file is switched, resulting in a large delay in the parsing result of LogMiner, and real-time parsing cannot be achieved; the uncertainty delay caused by this mechanism is unacceptable to the production environment business. Therefore, this application provides a real-time data collection method based on the ORACLE standby database.

[0028] Example 1

[0029] like Figure 1 As shown, this embodiment provides a real-time data collection method based on an ORACLE standby database, including:

[0030] S1: Create a separate new Oracle instance based on the physical machine where the Oracle standby database is located and name it newIns. The new instance and the original instance in the primary database collaborate with each other, that is, the log parsing operation in the new instance depends on the dictionary file generated in the primary database; the log parsing operation in the new instance depends on the log file path selected in the original instance of the standby database. The ADG standby database-related process locks the Standby Log file only in the related process, not in the operating system where the Oracle instance is located. By creating a new Oracle instance on the same operating system, the parsing delay caused by locking can be successfully avoided.

[0031] When implementing this embodiment, a new database instance is created by executing the following on the server where the Oracle standby database is located:

[0032] dbca-silent-createDatabase-templateName General_Purpose.dbc-gdbnamenewIns-sid newIns-sysPassword logmnr-systemPassword logmnr-responseFile NO_VALUE-datafileDestination / opt / oracle / oradata-redoLogFileSize 50-recoveryAreaDestination / opt / oracle / oradata / flash_recovery_area-storageTypeFS-characterSet AL32UTF8-nationalCharacterSet UTF8-sampleSchema true-emConfigurationNONE.

[0033] S2: Create a database dictionary file Flat File based on the Oracle main database;

[0034] S21: Create a directory object in the Oracle master database.

[0035] When implementing this embodiment, the execution code is as follows:

[0036] CREATE DIRECTORY"flatfile_dir"AS' / opt / oracle / oradata / flatfile';

[0037] S22: Generate a dictionary file Flat File according to the directory object and the file name.

[0038] In this embodiment, the execution code is as follows:

[0039] EXECUTE dbms_logmnr_d.build(dictionary_location=>'flatfile_dir',-

[0040] dictionary_filename=>'FlatDic.ora',-

[0041] options=>dbms_logmnr_d.store_in_flat_file).

[0042] The dictionary file is used to convert the physical representation of meta-information such as table names and field names into logical representation. If Logminer does not specify a dictionary file when parsing log files, the resulting Redo SQL will not be able to correctly identify meta-information such as table names and field names.

[0043] Oracle database provides three ways to access dictionary files: directly use the online catalog; export the dictionary file to Redo Log; export the dictionary file to Flat File (i.e., a separate dictionary file). When implementing, you need to select to extract data in a separate new Oracle instance, so you cannot obtain the online catalog. Exporting the dictionary file to Redo Log will increase the data delay from a few seconds to 3-5 minutes, and real-time analysis cannot be achieved. Therefore, the dictionary file is exported and stored in Flat File.

[0044] S3: Filter the log files of the original instance in the Oracle standby database to obtain the log files to be parsed and the corresponding path information.

[0045] The log files to be parsed are obtained by connecting to the original instance in the Oracle standby database through the Sqlplus client, executing the filter file SQL, and obtaining the path information of the log files to be parsed. The screening criteria of the filter file SQL are: screening and grouping based on the V$LOGFILE, V$ARCHIVED_LOG, and V$STANDBY_LOG system view records; the basis includes the archive status, log file type, and archive destination.

[0046] The details are as follows:

[0047] Log file type:

[0048] The query is divided into two parts, one part focuses on the online (V$LOGFILE and V$STANDBY_LOG) and standby log files, and the other part focuses on the archived log file (V$ARCHIVED_LOG).

[0049] Online / standby log files are marked as 'ONLINE' type, whereas archived log files are marked as 'ARCHIVED' type.

[0050] Archive Status:

[0051] For archived log files, the query selects only those records whose status is 'A' (ie archived) and whose NEXT_CHANGE# is greater than 1.

[0052] At the same time, the archive log files must also belong to a valid local archive destination (filtered from V$ARCHIVE_DEST_STATUS through a subquery).

[0053] Association of online / standby log files with archived log files:

[0054] An attempt is made to associate online / standby log files with archived log files via LEFT JOIN, but the association condition is applied only to archived log files.

[0055] If no matching archived log record is found, or the archived log record has a status other than 'A' or FIRST_CHANGE# is NULL, the online / standby log record is still considered.

[0056] Grouping and aggregation:

[0057] For online / standby log files, the query uses GROUP BY to group records and selects a representative file name based on the smallest member of each group (which may be a file path).

[0058] This helps to reduce duplicate logging because the same log file group may contain multiple members (such as multiple mirrors or redundant paths).

[0059] Other filters include:

[0060] The NAME field of the archive log file must be non-empty.

[0061] For online / standby log files, there is no direct filtering on the NAME field, but it is possible to correlate with records in V$LOGFILE and V$STANDBY_LOG via GROUP#.

[0062] Merge the results:

[0063] Use UNION to combine the result sets of online / standby log files and archived log files into a unified result set.

[0064] The log files to be parsed are Standby Redo Log and Standby Archive Log.

[0065] Specifically, the newly created Oracle instance does not contain any metadata of the collection source. The collection of the Oracle standby database data can be completed by obtaining the path of the log file to be parsed by combining the original instance connection of the Oracle standby database.

[0066] When implementing this embodiment, the SQL is as follows:

[0067] SELECT MIN(F.MEMBER)AS FILE_NAME,L.FIRST_CHANGE#FIRST_CHANGE,L.NEXT_CHANGE#NEXT_CHANGE,L.ARCHIVED,L.STATUS,'ONLINE'AS TYPE,L.SEQUENCE#AS SEQ,'NO'ASDICT_START,'NO'ASDICT_END FROM

[0068] V$LOGFILE F,V$STANDBY_LOG L

[0069] LEFT JOIN V$ARCHIVED_LOGA

[0070] ON A.FIRST_CHANGE#=L.FIRST_CHANGE#AND A.NEXT_CHANGE#=L.NEXT_CHANGE#

[0071] WHERE(A.STATUS<>'A'OR A.FIRST_CHANGE#IS NULL)AND F.GROUP#=L.GROUP#

[0072] GROUP BY F.GROUP#,L.FIRST_CHANGE#,L.NEXT_CHANGE#,L.STATUS,L.ARCHIVED,L.SEQUENCE#

[0073] UNION

[0074] SELECT A.NAME AS FILE_NAME,A.FIRST_CHANGE#FIRST_CHANGE,A.NEXT_CHANGE#NEXT_CHANGE,'YES',NULL,'ARCHIVED',A.SEQUENCE#AS SEQ,A.DICTIONARY_BEGIN,A.DICTIONARY_END

[0075] FROM V$ARCHIVED_LOG A

[0076] WHERE A.NAME IS NOT NULLAND A.ARCHIVED='YES'AND A.STATUS='A'

[0077] ANDA.NEXT_CHANGE#>1--scn

[0078] AND A.DEST_ID IN(SELECT DEST_ID FROM V$ARCHIVE_DEST_STATUS WHERESTATUS='VALID' AND TYPE='LOCAL').

[0079] S4: Use the database dictionary file to parse the log file to be parsed on the new Oracle instance, and push the parsing results to the target component. The specific process is as follows:

[0080] S41: Connect to the new instance through the Sqlplus client and add the path information of the log file to be parsed to the Logminer process;

[0081] S42: Logminer selects the data dictionary file Flat File in the new instance to perform log parsing and obtains a parsing result; the parsing result is a DML or DDL SQL statement;

[0082] S43: Logminer sets filtering conditions in the new instance to query the parsing results; wherein the filtering conditions include one or more of a library name, a table name, a time range, and an SCN range.

[0083] S44: By monitoring the changes of the standby log and archive log files to be parsed in real time, the latest log files are continuously parsed and the parsing results are pushed to external storage, thus achieving real-time collection of Oracle standby database data.

[0084] It can be understood that the same or similar parts of the above embodiments can be referenced to each other, and the contents not described in detail in some embodiments can refer to the same or similar contents in other embodiments.

[0085] Although the embodiments of the present invention have been shown and described above, it is to be understood that the above embodiments are exemplary and are not to be construed as limitations of the present invention. A person skilled in the art may change, modify, replace and vary the above embodiments within the scope of the present invention.

[0086] It should be understood that in the embodiments of the present invention, the processor may be a central processing unit (CPU), and the processor may also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc. The memory may include a read-only memory and a random access memory, and provide instructions and data to the processor. A portion of the memory may also include a non-volatile random access memory. For example, the memory may also store information about the type of device.

[0087] The readable storage medium is a computer-readable storage medium, which may be an internal storage unit of the controller described in any of the foregoing embodiments, such as a hard disk or memory of the controller. The readable storage medium may also be an external storage device of the controller, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc. equipped on the controller. Furthermore, the readable storage medium may also include both an internal storage unit of the controller and an external storage device. The readable storage medium is used to store the computer program and other programs and data required by the controller. The readable storage medium may also be used to temporarily store data that has been output or is to be output.

[0088] Based on this understanding, the technical solution of the present invention, in essence or in other words, the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, which is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned readable storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc., various media that can store program codes.

Claims

1. A real-time data collection method based on ORACLE backup database, characterized in that: include: S1: Create a separate Oracle new instance based on the physical machine where the Oracle standby database is located; S2: Create a database dictionary file Flat File based on the Oracle main database; S3: Filter the log files of the original instance in the Oracle standby database to obtain the log files to be parsed and the corresponding path information; S4: Use the database dictionary file to parse the log file to be parsed on the new Oracle instance, and push the parsing results to the target component.

2. The real-time data collection method based on ORACLE standby database according to claim 1 is characterized in that: The specific process of creating a database dictionary file based on the Oracle master database in S2 is as follows: S21: Create a directory object in the Oracle master database; S22: Generate a dictionary file Flat File according to the directory object and the file name.

3. The real-time data collection method based on ORACLE standby database according to claim 1 is characterized in that: The log files to be parsed in S3 are obtained by connecting to the original instance in the Oracle standby database through the Sqlplus client, executing the filter file SQL, and obtaining the path information of the log files to be parsed; The screening criteria of the screening file SQL are: screening and grouping based on V$LOGFILE, V$ARCHIVED_LOG, and V$STANDBY_LOG system view records; wherein the basis includes archiving status, log file type, and archiving destination.

4. The real-time data collection method based on ORACLE standby database according to claim 3 is characterized in that: The log files to be parsed are Standby Redo Log and Standby Archive Log.

5. The real-time data collection method based on ORACLE standby database according to claim 1 is characterized in that: The specific process of S4 is as follows: S41: Connect to the new instance through the Sqlplus client and add the path information of the log file to be parsed to the Logminer process; S42: Logminer selects the data dictionary file Flat File in the new instance to perform log parsing and obtains a parsing result; the parsing result is a DML or DDL SQL statement; S43: setting filtering conditions in the new instance to query the parsing results; S44: By monitoring the changes of the standby log and archive log files to be parsed in real time, the latest log files are continuously parsed and the parsing results are pushed to external storage, thus achieving real-time collection of Oracle standby database data.

6. The real-time data collection method based on ORACLE standby database according to claim 5 is characterized in that: The filtering condition includes one or more of a library name, a table name, a time range, and an SCN range.

Images