hello! Hello everyone, I am [IT Bond], known as jeames007 in the world. I have more than 10 years of experience in DBA and big data work.
A highly motivated [blogger in the field of big data]!
Member of China DBA Alliance (ACDU), currently serving the industrial Internet
Specialized in mainstream Oracle, MySQL, PG, Gaussian and Greenplum operation and maintenance development, backup and recovery, installation and migration, performance optimization, fault emergency handling, etc.
? If there is a [little cutie] who is interested in [database], please follow [IT Bond]
Thank you all the cuties!
Article directory
- Preface
-
- 1 Introduction
- 2.Ora2Pg features
- 3. Environment preparation
-
- ? 3.1 Install openGauss
- ? 3.2 Install Oracle
- 4. Install Ora2Pg
-
- ? 4.1 Dependency installation
- ? 4.2 Official installation
- ? 4.3 Environment variables
- 5. Create a migration project
- 6. Official migration
-
- ? 6.1 Create test table
- ? 6.2 Configure ora2pg.conf
- ? 6.3 Table migration
Foreword
This article details the entire process of migrating Oracle to openGauss using the Ora2Pg tool.
1. Preface
With the current development of the information innovation industry, the needs of some projects require the transition from Oracle to openGauss. This transition transformed me from an experienced Oracle DBA to a newbie in the openGauss world. After going through this migration, I felt compelled to share the insights I gained along the way.
This article details my journey, the challenges encountered, and the indispensable tool ora2og that facilitated this transformation. I hope sharing these experiences will make your openGauss journey smoother.
Migrating from Oracle to openGauss is more than just flipping a switch. It is a journey that consists of a series of steps such as architecture transformation, data migration, application migration and performance tuning. Each stage had its own problems and I needed a toolbox of solutions to deal with them.
2.Ora2Pg features
Application of Ora2Pg in openGauss
The main language of Ora2Pg is perl. It uses the Perl DBI module and connects to the PostgreSQL target database through DBD:Pg. openGauss is compatible with PostgreSQL’s communication protocol and most of the grammar, so only some naming changes are needed. Ora2Pg can also be applied to openGauss.
Features:
Supports exporting most database object types, including tables, views, sequences, indexes, foreign keys, constraints, functions, stored procedures, etc.
Provides automatic conversion of PL/SQL to PL/PGSQL syntax, avoiding manual correction to a certain extent.
Migration reports can be generated, including migration difficulty assessment and man-day estimation.
Optional compression of exported data to save disk overhead.
Rich configuration options to customize migration behavior.
Official website: https://ora2pg.darold.net/
3. Environment preparation
? 3.1 Install openGauss
– Create user group dbgroup
groupadd dbgroup
–Create user opengauss
Create the common user opengauss under the user group dbgroup, and set the password of the common user opengauss
useradd -g dbgroup opengauss
passwd opengauss
– Unzip the installation package
mkdir -p /app/openGauss
chmod 755 -R /opt/openGauss-5.0.0-CentOS-64bit.tar.bz2
chown opengauss:dbgroup -R /opt/openGauss-5.0.0-CentOS-64bit.tar.bz2
chmod 755 -R /app/openGauss
chown opengauss:dbgroup -R /app/openGauss
[root@opengauss /]# yum install bzip2
su-opengauss
cd /app/openGauss
tar -jxf /opt/openGauss-5.0.0-CentOS-64bit.tar.bz2 -C /app/openGauss
–Install openGauss
##Enter simpleInstall in the decompressed directory and execute the install.sh script to install openGauss
su-opengauss
cd /app/openGauss/simpleInstall
sh install.sh -w “jeames@007” & &source ~/.bashrc
#Loginopengauss
[opengauss@centos79 ~]$ gsql -d postgres
[opengauss@opengauss simpleInstall]$ gsql -d postgres
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------- + ---------- + ---------- + ------------- + - ------------ + ------------------------
finance | opengauss | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | opengauss | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
school | opengauss | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | opengauss | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/opengauss +
| | | | | opengauss=CTc/opengauss
template1 | opengauss | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/opengauss +
| | | | | opengauss=CTc/opengauss
(5 rows)
##Edit postgresql.conf
[opengauss@centos79 ~]$ cd $GAUSSHOME/data/single_node
Modify the following two parameters
listen_addresses = *’
max_connections = 1000
##Edit pg_hba.conf
Added at the end of the article
host all all 0.0.0.0/0 md5
Restart the openGauss service
gs_ctl restart -D $GAUSSHOME/data/single_node -Z single_node
? 3.2 Install Oracle
docker run -itd --name jemora11204 -h jemora11204 \ --privileged=true -p 21521:1521 -p 1222:22 -p 21158:1158 \ --network=mynet --ip 172.18.12.30 \ registry.cn-shanghai.aliyuncs.com/techerwang/oracle:ora11g11204 init
4. Install Ora2Pg
? 4.1 Dependency installation
1. Install dependency packages Ora2Pg language is perl, so the required perl modules need to be installed. --Operation under root user yum install gcc make net-tools.x86_64 yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker yum install perl-CPAN 2. Install some common modules of perl. Ora2Pg relies on these software to connect to the database. DBI, JSON, DBD:Pg, DBD:Oracle [root@ora2pg /]# perl -MCPAN -e 'install DBI' Appending installation info to /usr/lib64/perl5/perllocal.pod TIMB/DBI-1.643.tar.gz /usr/bin/make install -- OK [root@ora2pg /]# perl -MCPAN -e 'install JSON' Appending installation info to /usr/lib64/perl5/perllocal.pod ISHIGAKI/JSON-4.10.tar.gz /usr/bin/make install -- OK [root@ora2pg /]# yum install postgresql-devel [root@ora2pg /]# perl -MCPAN -e 'install DBD::Pg' Appending installation info to /usr/lib64/perl5/perllocal.pod TURNSTEP/DBD-Pg-3.17.0.tar.gz /usr/bin/make install -- OK ##Install DBD:Oracle, you need to install the Oracle client first https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/ yum install libaio rpm -ivh oracle-instantclient19.11-basic-19.11.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.11-devel-19.11.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.11-jdbc-19.11.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.11-sqlplus-19.11.0.0.0-1.x86_64.rpm Set environment variables: vi /etc/profile ##Add content export ORACLE_HOME=/usr/lib/oracle/19.11/client64/ export LD_LIBRARY_PATH=$ORACLE_HOME/lib [root@ora2pg ~]# source /etc/profile # Install DBD:Oracle yum install perl-Test-Simple perl -MCPAN -e 'install DBD::Oracle' Using perl -MCPAN -e 'install DBD::Oracle', the installation reported an error, so I changed to another way of compiling it myself. perl -MCPAN -e shell cpan[1]> get DBD::Oracle cpan[2]> quit [root@ora2pg /]# cd /root/.cpan/sources/authors/id/Z/ZA/ZARQUON/ [root@ora2pg ZARQUON]# tar -zxvf DBD-Oracle-1.83.tar.gz [root@ora2pg ZARQUON]# cd DBD-Oracle-1.83 [root@ora2pg DBD-Oracle-1.80]# perl Makefile.PL [root@ora2pg DBD-Oracle-1.80]# make & amp; & amp; make install
? 4.2 official installation
1. Download the installation package
https://github.com/darold/ora2pg/releases/tag/v24.1
[root@ora2pg opt]# mkdir /ora2pg
[root@ora2pg opt]# tar -zxvf ora2pg-24.1.tar.gz
[root@ora2pg opt]# cd ora2pg-24.1
[root@ora2pg ora2pg-24.1]# ll
total 660
-rw-rw-r– 1 root root 21 Sep 8 11:16 INSTALL
-rw-rw-r– 1 root root 32472 Sep 8 11:16 LICENSE
-rw-rw-r– 1 root root 180 Sep 8 11:16 MANIFEST
-rw-rw-r– 1 root root 74326 Sep 8 11:16 Makefile.PL
-rw-rw-r– 1 root root 169519 Sep 8 11:16 README
-rw-rw-r– 1 root root 366059 Sep 8 11:16 changelog
drwxrwxr-x 2 root root 4096 Sep 8 11:16 doc
drwxrwxr-x 3 root root 4096 Sep 8 11:16 lib
drwxrwxr-x 5 root root 4096 Sep 8 11:16 packaging
drwxrwxr-x 2 root root 4096 Sep 8 11:16 scripts
perl Makefile.PL PREFIX=/ora2pg
make & amp; & amp; make install
[root@ora2pg ora2pg-24.1]# ll /opt/ora2pg-24.1/lib
total 696
drwxrwxr-x 2 root root 4096 Sep 8 11:16 Ora2Pg
-rw-rw-r– 1 root root 707565 Sep 8 11:16 Ora2Pg.pm
[root@ora2pg ora2pg-24.1]# ll /ora2pg/usr/local/bin
total 60
-r-xr-xr-x 1 root root 47260 Nov 13 16:07 ora2pg
-r-xr-xr-x 1 root root 10549 Nov 13 16:07 ora2pg_scanner
? 4.3 Environment variables
##Set environment variables
vi /etc/profile
export PERL5LIB=/opt/ora2pg-24.1/lib
export PATH=$PATH:/ora2pg/usr/local/bin
source /etc/profile
[root@ora2pg ora2pg-24.1]# ora2pg –help
[root@ora2pg ora2pg-24.1]# ora2pg -v
Ora2Pg v24.1
5. Create a migration project
[root@ora2pg ora2pg-24.1]# ora2pg --init_project oramig
Creating project oramig.
./oramig/
schema/
dblinks/
directories/
functions/
grants/
mviews/
packages/
partitions/
procedures/
sequences/
sequence_values/
synonyms/
tables/
tablespaces/
triggers/
types/
views/
sources/
functions/
mviews/
packages/
partitions/
procedures/
triggers/
types/
views/
data/
config/
reports/
Generating generic configuration file
Creating script export_schema.sh to automate all exports.
Creating script import_all.sh to automate all imports.
illustrate:
It mainly contains two scripts, export_schema.sh and import_all.sh. These two scripts are used for subsequent export and import.
The schema and sources directories store the DDL statements of each object. The difference is that
1) Schema stores the statements converted from PL/SQL syntax to PL/PGSQL.
2) The sources directory stores PL/SQL statements before conversion
The data directory stores table data files
The config directory contains the configuration file ora2pg.conf
The reports directory stores migration reports
6. Official migration
? 6.1 Create test table
## Oracle table creation
create user jeames identified by oracle;
grant dba to jeames;
create table jeames.test(name char(10));
insert into jeames.test values('opengauss');
create table jeames.machine(name char(20));
insert into jeames.machine values('it');
2. Create a new database mydb and user tuser on the openGauss side
su-opengauss
gs_ctl start -D $GAUSSHOME/data/single_node -Z single_node
gsql -d postgres -r
openGauss=# create database mydb;
openGauss=# CREATE USER test WITH PASSWORD 'adm@23456';
openGauss=# GRANT ALL PRIVILEGES TO test;
openGauss=# alter database mydb owner to test;
? 6.2 Configure ora2pg.conf
Parameter file: /opt/ora2pg-24.1/oramig/config/ora2pg.conf ORACLE related parameters: ORACLE_HOME /u01/app/oracle/product/11.2.0/ ORACLE_DSN dbi:Oracle:host=oracleIP;sid=orcl;port=1521 ORACLE_USER customerchat //Oracle ordinary user and password used here ORACLE_PWD XXXXX SCHEMA customerchat //Generally the same as the username openGauss related parameters: PG_DSN dbi:Pg:dbname=mydb;host=localhost;port=5432 PG_USER tuser PG_PWD self-defined password Tool’s own parameters: DATA_LIMIT defaults to 10000. If the Oracle server memory is small, such as less than 4G, it can be modified to 2500 or 5000, otherwise it may report insufficient memory. For more detailed configuration item descriptions, please view the ora2pg.darold.net official website. Test the configuration: Executing the following command will return the connected Oracle version number [root@ora2pg /]# ora2pg -t SHOW_VERSION -c /opt/ora2pg-24.1/oramig/config/ora2pg.conf Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 SHOW_VERSION : display Oracle version -c specifies the configuration file
? 6.3 Table migration
– Modify the export types EXPORT_TYPE and SOURCE_TYPE in the export_schema.sh in the oramig directory of the migration tool
If you migrate tables and functions, please make the following modifications
vi export_schema.sh
EXPORT_TYPE=“TABLE FUNCTION”
SOURCE_TYPE=“FUNCTION”
[root@ora2pg /]# cd /opt/ora2pg-24.1/oramig/
1.Export
Execute in oramig directory
[root@ora2pg oramig]# cd /opt/ora2pg-24.1/oramig/
[root@ora2pg oramig]# sh export_schema.sh
[2023-11-14 01:55:03] [> ] 2/2 tables (100.0%) end of scanning.
[2023-11-14 01:55:05] [< /mark>>] 5/5 objects types (100.0%) end of objects auditing.
Running: ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf
[2023-11-14 01:55:47] [> ] 2/2 tables (100.0%) end of scanning.
[2023-11-14 01:55:50] [< /mark>>] 2/2 tables (100.0%) end of table export.
Running: ora2pg -p -t FUNCTION -o function.sql -b ./schema/functions -c ./config/ora2pg.conf
[2023-11-14 01:55:51] [> ] 0/0 functions (100.0%) end of functions export.
Running: ora2pg -t FUNCTION -o function.sql -b ./sources/functions -c ./config/ora2pg.conf
[2023-11-14 01:55:53] [< /mark>>] 0/0 functions (100.0%) end of functions export.
To extract data use the following command:
ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf
Note: Execute the export script and wait for the migration to complete. After completion, generate DDL files of the corresponding type in the subdirectories of schema and sources, and give the command to export table data at the end.
At the same time, a migration report in html format is also generated in the reports directory.
cd /opt/ora2pg-24.1/oramig/reports
--Import, or perform the import in the oramig directory The following requirements must be met before importing: 1. First create a database in the openGauss library, create a user under the database, and set the owner of mydb to this user. Import_all.sh uses PostgreSQL-specific createuser and createdb to create users and databases 2. In order to use the openGauss command line tool gsql, you need to add the bin and lib of the database to the environment variables PATH and LD_LIBRARY_PATH of the operating system. vi /etc/profile export GAUSSHOME=/app/openGauss export PATH=$GAUSSHOME/bin:$PATH export LD_LIBRARY_PATH=$GAUSSHOME/lib:$LD_LIBRARY_PATH gsql -U test -d mydb –h 172.18.12.60 -p 5432 -W adm@23456 3. Change psql in import_all.sh to gsql cd /opt/ora2pg-24.1/oramig/ 4. When using an ordinary user to import data, you can add an option to execute the script and specify the user password to avoid frequently entering passwords. sh import_all.sh -d mydb -o test –h 172.18.12.60 -p 5432 –f Note: Executing the import script means using user test to log in to the database named mydb. The IP and port are 172.18.12.60 and 5432 respectively. The -f option means skipping the check whether the user and database need to be created.
