Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to migrate data from Oracle to GaussDB. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

I. background

Recently, the database of the core business system of a provincial operator O domain is planned to be migrated from Oracle to GaussDB, and the database server is also transferred from SUN minicomputer to Huawei TaishanARM server. I have the honor to participate in it, mainly responsible for the data migration from Oracle to GaussDB. Datasync tool is used for derivative test in the early stage of data migration, but because the derivative efficiency can not meet the downtime requirements of the production system, Huawei launched a new migration tool-SDR at this time, so it carried out a new round of migration test using SDR.

1. Environmental information

1) hardware configuration information

The target library Taishan server hardware configuration information is as follows:

The hardware configuration of the source database Sun minicomputer is as follows:

2) Software configuration information

The Taishan server operating system and database software versions are as follows:

The operating system and database software version of the source database Sun is as follows:

Since the focus of this article is not on the construction of the environment, the installation process of the operating system and GaussDB will not be described in detail.

2. SDR product description

GaussDB SDR (Swift Data Replicator) is a log-based real-time CDC software that supports real-time capture, conversion and loading of transaction data in a variety of relational databases. It is mainly used in scenarios such as data migration, data replication, remote disaster recovery, emergency backup, dual business centers, real-time data warehouse / data lake, query and unload, etc. The main features are: real-time, efficient data synchronization, rich heterogeneous synchronization capabilities, no plug-ins, non-intrusion, security, no interference and visual operation and maintenance management.

II. Environmental preparation of SDR

The source side is Oracle 9i or above (the SDR version used in the test is only supported to 11R2)

Support target side is GaussDB 100V300R100

Carry out full data synchronization with optional schema and optional table

Support configurable conditions to export source database data, and import data that meet the conditions into the target database

Multi-table concurrent synchronization is supported, and the number of concurrent tables can be configured.

The concurrent export of single table data is supported, the number of concurrent data can be configured, and the concurrent import of single table is not supported.

Support automatic synchronization of table structure and automatic data type mapping, as shown in the following table

1) hardware requirements

At present, SDR only supports x86 64-bit operating system, and the requirement for hardware resources mainly depends on the data change of the source database.

Table 2-1 Resource requirements

2) Software requirements

Note: in this migration test, SDR is deployed on a separate x86 server to access both sides of the database through the database business port. Since most of the migration operations are carried out on the platform web pages, it is also recommended to prepare a windows server that can directly access the x86 service 7007spindle 7009port.

1. SDR tool preparation

1) create installation user and directory (operation on x86 server)

Create a new user for the SDR software and create an installation directory / home/sdr:

Su-root groupaddsdr useradd-g sdr-d / home/sdr-s / bin/shsdr chown-R sdr:sdr / home/sdr/

2) switch to the installation user and upload the installation package

Su-sdr ls-l / home / sdr/ depend.tgz GaussDB_SDR_8.0.RC1_RHEL_x86_64.tar.gz licence.dat.tar.gz

3) decompress all compressed packages

Tar-xf depend.tgz tar-xf GaussDB_SDR_8.0.RC1_RHEL_x86_64.tar.gz tar-xf licence.dat.tar.gz

2. SDR tool installation

1) set the SDR_HOME environment variable

Su-sdr export DIP_HOME=/opt/sdr

2) perform installation

Cd / home/sdr. / GaussDB_SDR_8.0.RC1_INSTALL Creating directory SDR.pack Verifying archive integrity... All good. Uncompressing swift data replicator 100% Swift Data Replicator installation! ©2019 Huawei Technologies Co. Ltd. Swift Data Replicator software will install into directory [/ home/sdr] Please input Swift Data Replicator web server IP address: [127.0.0.1] Please input Swift Data Replicator web server port: [7008] Please input Swift Data Replicator manager server IP address: [127.0.0.1] Please input Swift Data Replicator manager server port: [7009] Please input Swift Data Replicator queue transfer server port: [7007] Please input Swift Data Replicator admin server port: [7006] Please input Swift Data Replicator kafka component port: [ 7005] Please input Swift Data Replicator database IP address: [127.0.0.1] Please input Swift Data Replicator database port: [1888] Please set default Swift Data Replicator Share Memory Size: [1]-1GB [2]-4GB [3]-8GB [4]-16GB [5]-input other value (GB) please choose 1 focus 2 3,4: [1] 4 Input value and choice list below, please confirm: http IP address: 127.0.0.1 http port: 7008 Manager IP address: 127.0.0.1 Manager Port: 7009 Transfer Port: 7007 AdminServerPort: 7006 Kafka component Port: 7005 SDR Database IP: 127.0.0.1 SDR Database Port: 1888 Share memory size: 16G Kafka ServerPort: 7005 Press any key to begin install. (Ctrl-c to interrupt) Uncompressing bin.tgz... Uncompressing dbpkg.tgz... Uncompressing etc.tgz... Uncompressing lib.tgz... Uncompressing web.tgz... Wait a few of minutes, installing database... Checking parameters... Checking user... Checking old install... Checking kernel parameters... Checking dir... Checking integrality of run file... Decompressing run file... Warning: Dialect Scripts not found! But still proceed installation Setting user env... Checking data dir and config file Initiate db instance... Creating database... Changing file permission due to security audit... Install successfully, for more detail information see / home/sdr/zengineinstall.log. Refreshing config... Creating db user... Initialize data... All done → installation completed successfully

3) configure depend

Enter the depend directory.

Cd / home/sdr/depend/ sh enable.sh Enable success

4) load licence file

Cd / home/sdr cp licence.dat etc/ ls etc/ dip_config.xml licence.dat odbcinst.ini version xlog.ini

5) start the SDR component

You need to load the environment variables before starting the component

Su-sdr cd / home/sdr/.. / env (1) start the background database cd / home/sdr/db/app/bin python zctl.py-t start (2) start other components cd / home/sdr/ start_all (3) start the web service cd / home/sdr start_web

At this point, all the preparations for the installation of SDR are complete.

III. SDR configuration

The SDR management interface needs to be accessed through a browser, which can choose chrome or IE 9.0 or above. The URL is the IP of the x86 server, the port is the port specified at the time of installation, and must be HTTPS, such as https://IP:port. Initial account password admin/admin.

Figure-1-Log in to the SDR console

After logging in, you need to create a new project first.

Figure-2-create a project

Enter a project name and save it.

After clicking to enter the project, you will enter the configuration management main interface. Click the "New" button in the upper left corner to create a task group.

Figure-3-New Task Group

Enter the group name and the running log retention time of the processes under the group, and click "Save Group".

The migration test is from Oracle to GaussDB, which belongs to heterogeneous database migration. You can directly use the heterogeneous links in the template library to configure, and click "heterogeneous links", as shown in the following figure:

Figure-4-heterogeneous link configuration

After the link is generated, double-click the source library component in the link to configure the source library in the pop-up dialog box. (after the first configuration, you can choose to save as a template for later use.)

Figure-5-Source Database configuration

Note: when the source database type is Oracle, you need to add additional logs to the exported objects. The SQL for adding additional logs can be generated in "necessary environment configuration" and executed in the source database.

After configuring the source database information, the next step is to configure the capture process. The capture process is mainly used to extract data from the source database, configure the schema and object types to be migrated, and configure parameters such as decimation interval, memory allocation and so on.

Figure-6-capture configuration

Figure-7-configure advanced options for the capture process

Note: when the source database is Oracle RAC, you need to enter the information of all nodes and select "ENABLE".

After you have configured the capture process, you need to configure the queue information. The entire link needs to be configured with two queues, one is the export queue and the other is the import queue. You can first configure the export queue sequentially. The specific configuration is as follows:

Figure-8-configure the export queue

After the queue configuration is complete, you need to configure the ETL conversion link. After the source database information is configured, most of the contents of the ETL will be automatically populated, and you can just use the default configuration. The specific configuration is as follows:

Figure-9-configuring ETL

The next step is to configure the import queue, which is not described here. The configuration is as follows:

Figure-10-configure the import queue

According to the link order, the next step is to configure the reprint process, but before configuring it, you will be prompted to configure the target library information first, so you can only skip to the final target library information configuration, and then return to configure the reprint process information. So here is the information about configuring the target GaussDB library (again, you can choose to save the template as the GaussDB library for later use when you configure it for the first time.)

Figure-11-configure target library information

Then you can finally happily configure the reprint process information. There are many configurable contents in the reprint process, which are mainly divided into: inclusion conditions, exclusion conditions and advanced options.

Contains conditions that can configure the schema and object types to import, and whether to replace schema_name in the target library. (this test has carried on the schema_name conversion, the test result is smooth.)

Figure-12-configure load inclusion conditions

The next step is to configure the exclusion conditions. There are no exclusion conditions added to this test. Students in need can add tests.

Figure-13-configure load exclusion conditions

The following are the advanced configuration options, and the screenshots below are all the default settings that can be adjusted as needed.

Figure-14-configure advanced mount options

After confirming the save, the configuration of the components required for the migration is complete.

IV. Full migration of SDR

The SDR tool comes with the function of data initialization. Click "tool set > inventory data synchronization" on the toolbar to pop up the configuration interface. You can configure the number of concurrency of export tasks and the number of concurrency of a single table. You need to control the number of concurrency. In extreme cases, the total number of export processes may be equal to the number of task concurrency * the number of concurrency in the table. In addition, the option of "Export data as a string" must be checked, otherwise the speed will be greatly accelerated, but the failure rate of export and import will also be greatly improved.

Figure-15-configure full data synchronization-Export conditional configuration

(note: before enabling initialization, you need to manually create users / permissions / tablespaces in the target environment. The creation command is basically the same as Oracle.)

After the export condition configuration is finished and saved, the next step is to configure the import condition configuration. Because there is no business pressure on the target library, you can choose a larger number of concurrency, or you can choose whether to "rebuild the target table", "empty the target table" and "rebuild the index" according to the demand.

Figure-16-configure full data synchronization-Import conditional configuration

After the configuration is completed, click "start full synchronization" to start the stock data synchronization.

Figure-17-View full synchronization real-time information

Wait for the export and import to complete the migration of the stock data. The error information that exists during the migration process can be looked up directly in "Export error message" and "Import error message". If the error message is missing or insufficient, you can go to the log directory of the installation directory on the x86 server for more details.

5. SDR incremental migration (steps used in formal migration)

Draw the point! The previous full migration and the following incremental migration are two different things. If you follow the previous steps to complete the data migration, you also need to delete all the data in the target database and start all over again.

Before using add-up, you need to obtain the latest scn number from the source database. Obtain the latest SCN number from the source-side Oracle library:

Select current_scn from vault database; CURRENT_SCN-16493654040783

1. Start the capture process

Heterogeneous links need to be configured according to "full SDR Migration" in advance, which will not be repeated here. Start the capture process separately after the configuration is completed. After selecting the capture process in the workspace, click "start" to open it.

Figure-18-start the capture process

2. Start stock backup

This inventory backup needs to enter the latest scn number in order to synchronize all the data of this scn number to the target database.

Figure-19-configure inventory data synchronization

3. Start the additional equipment.

After waiting for the stock backup to complete, start the conversion and loading process separately. Start the conversion process in the specified SCN mode, and the scn entered is the latest SCN number obtained at the beginning.

Figure-20-specify scn to start the conversion process

After the ETL is started, the figure is as follows:

Figure-21-after starting the conversion process

Start the loading process in the specified SCN mode, and the scn entered is the latest SCN number obtained at the beginning.

Figure-22-specify scn to start the loading process

After the reprint process is started, the status is as shown in the figure:

Figure-23-after the loading process is started

After the startup is complete, select the "list" mode in the workspace to view the status of each process. Running is normal.

Figure-24-list view status

At this point, the additional equipment has been started.

After the addition is initiated, the DML and DDL of the source database to the operation table will be synchronized to the destination side in quasi-real time until it is manually terminated or an error is reported. (some DDL does not support it, such as add database,add datafile,role related operations, etc.)

4. Migration process

1) when you choose to create the table automatically, the partition information (interval, etc.) of the partition table cannot be automatically synchronized, causing the partition table to change back to the normal table after GaussDB is imported.

Solution: before starting the derivative, manually create all partition tables in the target environment, and do not select the "rebuild table" option when importing stock data

2) when exporting, fields with both long and clob types on a table will report an error

Error message: table [dcpp.aqua _ explain_1483468080] include LOB and LONG column,unsupport

Solution: before starting the derivative, manually create all error tables in the target environment, and do not select the "rebuild table" option when importing stock data

3) when starting stock data synchronization, even if it is the same configuration option, after clicking "start full synchronization", the synchronization process often fails to start normally.

The specific phenomena are:

Time consuming: 00H00M00S, do not start timing

In the source database Oracle, after initiating a SQL to query the full scale information, the export SQL of the specific table is not continued, but the session is terminated directly.

There is no information in the export / import error message

Solution: try a few more times, or reduce the concurrency.

Using the process feels that SDR is similar to Oracle's OGG tool. The workflow can be simplified as follows: specify scn to initialize all data before importing this scn, and after initialization, start automatic incremental synchronization according to this scn until the synchronization ends manually. The whole migration process only needs to be configured in the early stage, and there is very little time for manual intervention in the later stage (it is still necessary to check the synchronization process status and log status regularly), and because the data has been synchronized in quasi-real time, therefore, the migration time on the production system can be compressed very short, allowing more time for data verification and other operations.

After reading the above, do you have any further understanding of how to migrate data from Oracle to GaussDB? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.