Method and device for realizing database migration based on Oracle object dependency relationship

Abstract

The invention relates to a method for realizing database migration based on an Oracle object dependency relationship. The method comprises the following steps: selecting a migration object and performing object grammar conversion; the dependency relationship of the migration objects is obtained, the migration objects are grouped and sorted according to the dependency relationship, and concurrent migration is carried out according to grouping; after all migration is completed, checking whether a failed object exists or not, and if yes, retrying the failed object; and after the retry is completed, checking whether the number of the failed objects is the same as that before the retry, if the number of the failed objects is reduced, restarting the retry, and if the number of the failed objects is the same as that before the retry, ending the migration. According to the method, two strategies are introduced, intelligent sequencing is carried out before migration, retry compensation is carried out after migration, uncertainty caused by the sequence problem is solved, the stability of database structure migration and the success rate of migration are remarkably improved, and the migration efficiency is improved. Therefore, an efficient and reliable solution is provided for structure migration from the Oracle database to the GaussDB database.

Description

Technical Field

[0001] The present invention belongs to the technical field of database migration methods, and in particular relates to a method, a device and a readable storage medium for implementing database migration based on Oracle object dependency. Background Art

[0002] In the context of replacing domestic databases, more and more institutions and enterprises are beginning to consider switching from foreign databases such as Oracle and MySQL to domestic databases. Huawei's independently developed GaussDB database has become the target database for many enterprises to consider replacing due to its distributed characteristics, high performance, high reliability, and high scalability. In the process of replacing the database, it is first necessary to migrate all types of object structures in the source database to the new database with consistent semantics. Taking Oracle as an example, the object types usually include tables, indexes, constraints, sequences, types, views, functions, stored procedures, triggers, etc.

[0003] In general structural migration, migration is performed in the order of type, sequence, table, view, materialized view, index, constraint, and other objects. That is, the first priority is type and sequence, which are generally relied on by table objects and will be migrated first to ensure that the table migration will not fail due to the non-existence of type or sequence; the second priority is table objects, which are usually used as base objects for other objects such as views (views are created based on one or some objects, which are called base objects), so they also need to be migrated first. For simple scenarios, the above migration method can ensure the successful migration of most objects. However, in actual customer scenarios, the business is relatively complex. When there are dependencies within objects of the same type, or when multiple migrations are required due to various problems during migration, the uncertainty of the order often brings uncertainty to the migration results, causing some objects to fail to migrate. Summary of the invention

[0004] In order to overcome the above defects of the existing database migration methods, the present invention proposes a new method for implementing database migration based on Oracle object dependencies. The method of the present invention can intelligently arrange the migration sequence according to the Oracle object dependencies, thereby eliminating the uncertainty caused by the sequence problem and achieving the effect of successful migration of all database objects.

[0005] In the present invention, we introduce two strategies to ensure that the base objects of all database objects have been created before they are created, and that the objects will not be cascaded deleted due to the drop operation before the base object migration due to multiple executions of the migration task (assuming that view viewA is created based on viewB, because viewB has been created during the first migration, if the order of migration is viewA first and then viewB for the second migration, when viewA is created, it will be migrated normally because viewB exists, but when viewB is migrated, viewA will be cascaded deleted).

[0006] Specifically, the present invention provides a method for implementing database migration based on Oracle object dependency. Figure 4 As shown, the method comprises the following steps: S1. Select the object to be migrated and perform object syntax conversion; S2. Obtain the dependencies of the migration objects, group and sort the migration objects according to the dependencies, and then perform concurrent migration based on the groups; S3. After all migrations are completed, check whether there are any failed objects. If there are any failed objects, retry the failed objects. S4. After the retry is completed, check whether the number of failed objects is the same as before the retry. If the number of failed objects decreases, start the retry again. If the number of failed objects is the same as before the retry, end the migration.

[0007] Furthermore, in the method for implementing database migration based on Oracle object dependency in step S2 of the present invention, obtaining the dependency of the migration object is achieved by querying Oracle's system view.

[0008] Furthermore, in step S2 of the method for implementing database migration based on Oracle object dependencies of the present invention, migration objects are grouped and sorted according to the dependencies, and migration objects with dependencies are allocated in the same thread in the order of base objects (dependent objects) first and dependent objects later.

[0009] Furthermore, the method step S2 of implementing database migration based on Oracle object dependency of the present invention includes: S21. Check whether the object to be migrated has a dependent base object, if not, directly put it into the array to be migrated; S22. If the object to be migrated has a dependent base object, it is placed in the array and the base object is checked to see if it exists in the array; S23. If the base object exists in the array, the object to be migrated is placed after the base object, and the number of sub-objects of the base object is marked plus 1; if the base object does not exist in the array, the base object is deleted from the unprocessed object list and placed in the array, and the number of sub-objects of the base object is marked plus 1; S24. Perform concurrent migration according to the preset concurrency. If the number of child objects of the base object is not 0, migrate its child objects in the same thread.

[0010] Furthermore, the method for implementing database migration based on Oracle object dependency in step S4 of the present invention includes: S41. Initialize the number of objects that failed last time to 0; S42. Calculate and record the number of failed objects. If there is no failed object, the migration ends directly. S43. If there are failed objects this time, check whether the number of failed objects this time is the same as the number of failed objects last time. If so, end the migration directly. If not, re-migrate all failed objects.

[0011] Furthermore, the method for implementing database migration based on Oracle object dependency of the present invention is implemented by using data structures and thread pools in the Java programming language.

[0012] On the other hand, the present invention also provides a device for implementing database migration based on Oracle object dependency. When the device is running, the steps of the method for implementing database migration based on Oracle object dependency are implemented.

[0013] In addition, the present invention also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the steps of the above-mentioned method for realizing database migration based on Oracle object dependency are implemented.

[0014] In summary, the method for implementing database migration based on Oracle object dependency of the present invention introduces two strategies, intelligently arranges the order before migration, and performs retry compensation after migration, thereby ensuring the success rate of migration of all objects to the greatest extent. The present invention solves the uncertainty caused by the sequence problem well, significantly improves the stability of database structure migration and the success rate of migration, thereby providing an efficient and reliable solution for the structure migration from Oracle database to GaussDB database. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] In order to more clearly illustrate the technical solution of the present invention, the following briefly introduces the drawings required for use in the description of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without paying creative work.

[0016] Figure 1 It is an overall implementation framework diagram of the method of the present invention.

[0017] Figure 2 This is a technical implementation flow chart of Strategy 1 in the method of the present invention (i.e., sorting and grouping objects according to the dependencies between the objects).

[0018] Figure 3 This is a technical implementation flow chart of Strategy 2 in the method of the present invention (i.e., a failure retry mechanism until the number of failed objects no longer changes, indicating that all objects that failed to be created due to dependencies have been created).

[0019] Figure 4 It is a schematic diagram of the implementation process of the method of the present invention. DETAILED DESCRIPTION

[0020] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. The present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.

[0021] At the same time, it should be understood that the protection scope of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the protection scope of the present invention.

[0022] Embodiment: A method for implementing database migration based on Oracle object dependency Figure 1 The following is the overall implementation process of this method, taking Oracle object migration as an example: Strategy 1: If Figure 2 As shown, before starting object migration, query Oracle's system view to accurately find the dependencies of each object, group and sort the objects to be migrated according to the dependencies, and then perform concurrent migration.

[0023] Assume that viewA is created based on viewB. When the existing method is used for the first migration, viewB has already been created. If the order of migration is viewA first and viewB second, viewA will be migrated normally because viewB exists when viewA is created, but viewA will be deleted in cascade when viewB is migrated. In this strategy, first query Oracle's system view to find out the relationship between viewA and viewB, then allocate viewA and viewB in the same thread, and ensure that the order is viewB first and viewA second.

[0024] In order to implement this strategy more effectively, you can use the data structure and thread pool in the Java programming language to assist. Taking Oracle as an example, if the schemas to be migrated are named test1 and test2, you can query the dependencies between all objects in these two schemas from Oracle's system view ALL_DEPENDENCIES using the following SQL statement: SELECT NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE FROM ALL_DEPENDENCIES WHERE OWNER IN ('test1', 'test2') -- Schema to be queried ORDER BY TYPE, REFERENCED_OWNER, REFERENCED_NAME; Strategy 2: If Figure 3As shown in the figure, after migration, failed objects are retried to ensure that some objects that failed to migrate due to special reasons such as circular dependencies are created during the retry, until the number of failed objects stops decreasing (if the number of failed objects stops decreasing, it means that there are no objects that failed to migrate due to the non-existence of the base object in this migration). For example, some objects cannot be accurately sorted in strategy 1 due to special reasons, that is, objectA is created based on objectB, but objectA is created first and then objectB during the actual migration, resulting in objectA failing to be created because objectB has not been created. At this time, this strategy records the failed object objectA and retries objectA after the entire task is completed. At this time, objectA will also be successfully created because objectB has been created.

[0025] In addition, in order to better carry forward Strategy 1, Strategy 2 can also use the Java programming language to implement related logic.

[0026] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any technician familiar with the profession can make some changes or modifications to the technical contents disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments of equivalent changes. However, any simple modification, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. A method for implementing database migration based on Oracle object dependency, characterized in that: The method comprises: S1. Select the object to be migrated and perform object syntax conversion; S2. Obtain the dependencies of the migration objects, group and sort the migration objects according to the dependencies, and then perform concurrent migration based on the groups; S3. After all migrations are completed, check whether there are any failed objects. If there are any failed objects, retry the failed objects. S4. After the retry is completed, check whether the number of failed objects is the same as before the retry. If the number of failed objects decreases, start the retry again. If the number of failed objects is the same as before the retry, end the migration.

2. The method for implementing database migration based on Oracle object dependency according to claim 1, characterized in that: The step S2 of obtaining the dependency relationship of the migration object is achieved by querying the Oracle system view.

3. The method for implementing database migration based on Oracle object dependency according to claim 1, characterized in that: In step S2, the migration objects are grouped and sorted according to the dependency relationship, and the migration objects with dependency relationship are allocated in the same thread in the order of base object first and dependent object later.

4. The method for implementing database migration based on Oracle object dependency according to claim 1, characterized in that: Step S2 includes: S21. Check whether the object to be migrated has a dependent base object, if not, directly put it into the array to be migrated; S22. If the object to be migrated has a dependent base object, it is placed in the array and the base object is checked to see if it exists in the array; S23. If the base object exists in the array, the object to be migrated is placed after the base object, and the number of sub-objects of the base object is marked plus 1; if the base object does not exist in the array, the base object is deleted from the unprocessed object list and placed in the array, and the number of sub-objects of the base object is marked plus 1; S24. Perform concurrent migration according to the preset concurrency. If the number of child objects of the base object is not 0, migrate its child objects in the same thread.

5. The method for implementing database migration based on Oracle object dependency according to claim 1, characterized in that: Step S4 includes: S41. Initialize the number of objects that failed last time to 0; S42. Calculate and record the number of failed objects. If there is no failed object, the migration ends directly. S43. If there are failed objects this time, check whether the number of failed objects this time is the same as the number of failed objects last time. If so, end the migration directly. If not, re-migrate all failed objects.

6. The method for implementing database migration based on Oracle object dependency according to claim 1, characterized in that: The method is implemented by using a data structure and a thread pool in the Java programming language.

7. A device for implementing database migration based on Oracle object dependency, characterized in that: When the device is running, the steps of the method for implementing database migration based on Oracle object dependency described in any one of claims 1 to 6 are implemented.

8. A computer-readable storage medium having a computer program stored thereon, wherein when the program is executed by a processor, the steps of the method for implementing database migration based on Oracle object dependency as described in any one of claims 1 to 6 are implemented.

Images