Overview of Oracle Adaptation and Migration of Gaussian Databases
In the context of the switch of domestic database systems, the system that uses the relational Oracle database can be replaced with Huawei's Gaussian database. Gaussian database is a distributed, multi-model database, based on the PostgreSQL open source database and developed in accordance with SQL-related standards, with high performance, high reliability, and high scalability. In contrast, there are certain differences between the two databases in terms of data storage, kernel structure, data types, system function functions, etc., as well as some SQL syntax. These differences objectively cause certain differences in database object creation, stored procedure compilation, SQL analysis and execution, etc., and are not fully compatible.
Oracle migration process and tools for Gaussian databases
1. Migration tool
UGO: Migration of heterogeneous database structures It is a data object migration tool, which mainly includes the compatibility evaluation of database objects before migration, SQL syntax conversion configuration during migration, database object migration synchronization, and database object verification after migration. The tool can automatically convert the DDL, DML, DCL, DCL, and SQL statements of the database encapsulated in business programs into the SQL syntax of GasussDB with one click, improving the conversion rate and minimizing the database migration cost. This tool implements the automatic migration of mainstream commercial databases to GaussDB through two core functions: pre-migration assessment and schema migration.
DRS: Real-time Data Migration Service After UGO migrates database objects, you can use this tool to migrate existing and incremental data.
2. Migration tool
Oracle's database system can be migrated in three parts: schema migration, data migration, and application migration. This is shown in Figure 1 below.
Schema migration: Use UGO to evaluate the database and obtain the transformed heterogeneous database table structure.
Data migration: Use DRS on the HUAWEI CLOUD Management page to migrate data to full data and verify its integrity.
Application migration: Locate the service code at the DAO layer, use the heterogeneous database structure migration platform UGO to perform syntax conversion, and adjust the syntax based on the conversion result.
Oracle migrates Gausss library bidirectional synchronization scheme
There are three ways to migrate Gausss from an Oracle database: switch the entire database at once to ensure that Gauss is synchronized to Oracle; Oracle and Gausss are synchronized in both directions; Dual-write transformation of application testing. This section introduces the DRS two-way synchronization solution.
On the premise of ensuring the consistency and security of the data in the source and destination databases, you can consider adopting the migration strategy of "full bottom-laying, incremental synchronization, two-way synchronization, and phased migration" to achieve a steady switch from Oracle to Gaussian database. The following figure shows the migration solution:
Full data provisioning: When you synchronize data from Oracle to Gaussian database for the first time, you can fully synchronize the current Oracle database data to Gaussian database based on full online migration. During the online migration, the service may be temporarily interrupted or not interrupted, and the user is not aware of it, which can meet the real-time requirements of the system.
Incremental synchronization: You can use the source and destination databases to synchronize data from one side after manipulating the data of one side.
Two-way synchronization: incremental synchronization can support synchronization from Oracle to gauss and from gauss to oracle.
Phased migration: If the system level is high and the number of tables is thousands, you can consider the batch migration method in the preceding figure to migrate each microservice after decoupling. Migrate the microservices with low impact first, and then migrate the remaining microservices after they run stably.
Data consistency check
After full data synchronization is completed, you need to verify the consistency of the data in DRS, which allows you to verify the table-level fields of the selected tables in the source and destination databases on the one hand, and compare the table contents between the source and destination databases on the other hand. In short, operations such as full migration + incremental synchronization and reverse incremental synchronization require data consistency verification.
Oracle has learned from migrating Gaussian databases
Oracle databases involve the migration of schemas such as tables, indexes, sequences, views, stored procedures, and triggers. During the migration, because the individual schemas are not compatible with GaussDB databases, you need to perform further processing before migration. In addition, special problems encountered in data synchronization were resolved.
Handle fabric migration issues
1. Trigger issues
The triggers used in Oracle are not compatible with GaussDB, so they need to be modified before migration.
a.触发器中long类型可修改为bigint、number或numeric；所有的表结构名和字段名均需大写。
b. The updating function is not supported under the Gaussian library, so the need to unify the updating function used in the Oracle trigger.
2. Sequence issues
a. Sequences are usually used instead of oracles that do not have auto-increment primary keys. However, Gauss's sequence cache is session-side, so the sequence is discontinuous. For example, if the cache is 100, A gets 1-100 connections and 101-200 connections in connection pool B, but the connection pool A is not used up, and connection pool B runs out of sequences. When Connection Pool A uses the sequence again, it will continue to use the unused sequence first, but the sequence is already 200, and the sequence of < 100 will appear. It is suggested that the sequence be changed to 1.
b. The maximum supported value of oracle sequence is 9999999999999999999, while the maximum value of Gaussian sequence is 9223372036854775807
For example:
CREATE SEQUENCE[TABLE_NAME]MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT BY 1 START WITH 275974021 CACHE 1 NO CYCLE
3. Stored Procedures
Stored procedures under Oracle are no longer applicable in the Gaussian environment, and the logic needs to be rolled up to the application level.
4. No primary key table
For schema migration, we recommend that you add a primary key to a table without a primary key. If you migrate data to the data synchronization level, you may need to verify the consistency.
5. Built-in functions
If Oracle's built-in functions do not exist in the Gaussian library, after DRS synchronization is completed, a function with the same function will be created for the Gaussian library schema to "dsc_ora_ext" by default.
For example, oracle has a built-in function sys_guid(); After syncing, it will become dsc_fn_sys_guid ().
Handling data synchronization issues
1. BigDecimal format issues
Problem description: The numeric field in the following table structure of Oracle displays the current precision by default, while the original precision of the numeric field in the Gauss database displays the original precision of the numeric field in the table structure by default (the precision is automatically completed with 0).
Workaround: Correction at the system level by changing the configuration
Set behavior_compat_options=“hide_tailing_zero”
2.Encoding'UTF-8'无法转换
Problem description: If a table in an Oracle database has an empty string, the character \u0000 can be written to the oracle in the form of char(0) or bytes, but the Gauss family cannot write the character \u0000, causing an exception to oracle2Gauss.
Solution: Add the following configuration to the DRS O&M side:
全量：sync.datamove.replicator.standardizeStringType=true
增量：sync.increment.replicator.standardizeStringType=true
3. Java heap space problem
Problem description: If a large amount of data is involved in a one-time full synchronization, the error message "service DATAMOVE failed" is reported during DRS synchronization, and the error message "cause by:javaheap space" is displayed.
Solution: Add the following configurations to the O&M management side:
sync.tungstenEnv.OPS_FULL_INCRE_MAX=10240
sync.datamove.replicator.fetchSize=100
Common SQL statements
At the end of the article, I invite you to join our software testing learning exchange group, where you can discuss and exchange software testing, learn all aspects of software testing such as software testing techniques and interviews, and understand the latest trends in the testing industry, so as to help you quickly advance to Python automated testing/test development, stabilize your current position and move towards a high salary.
At last:
1) Follow + reply by private message: "Test", you can get a free 10G software test engineer interview book document. And the corresponding video learning tutorials are free to share!