3. Oracle to PostgreSQL Migration

📣 설치환경

• OS : CentOS 7.9

• Version : PostgreSQL 14

1. Ora2pg 프로젝트 초기화

# ora2pg --project_base $HOME --init_project migration_project

2. Ora2pg Config 파일 수정

파일 위치 : $HOME/migration_project/config/ora2pg.conf

수정이 필요한 부분들을 찾아 아래와 같이 수정한다.

1) Oracle Connection Section 수정

ORACLE_DSN		dbi:Oracle:host=192.168.1.208;sid=ORA11G2;port=1521
ORACLE_USER		system		# Oracle의 사용자
ORACLE_PWD		manager		# Oracle 사용자의 Password
USER_GRANTS		1
ORA_INITIAL_COMMAND commit

2) Schema Section 수정

EXPORT_SCHEMA	1
SCHEMA			SCHEMA_NAME			# 현재 schema name 작성
CREATE_SCHEMA	1
COMPILE_SCHEMA	1
PG_SCHEMA		public

＊ 현재 schema 확인 방법

SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS "CURRENT_SCHEMA" FROM DUAL;
CURRENT_SCHEMA
----------------------------------------------------------------------------------
SCHEMA_NAME

3) Triggers and Sequences Section 수정

DISABLE_SEQUENCE	1
DISABLE_TRIGGERS	1

4) Object Modification Section 수정

DISABLE_PARTITION	1
USE_RESERVED_WORDS	1

5) PostgreSQL Section 수정

PostgreSQL가 설치되어 있는 서버 IP 및 PostgreSQL의 User와 Password를 입력 한다.

PG_DSN		dbi:Pg:dbname={db_name};host=192.168.1.218;port=5432
PG_USER		{postgresql user}
PG_PWD		{postgresql password}
OUTPUT_DIR	{output 할 directory}

6) PLSQL Section 수정

EMPTY_LOB_NULL	0

7) PostgreSQL Feature Section 수정

PG_VERSION		14

8) FDW Section 수정

FDW_SERVER		orcl		# 주석 해제

3. Oracle Connect Test

# ora2pg -t SHOW_VERSION -c $HOME/migration_project/config/ora2pg.conf
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Oracle의 버전과 비교해서 같으면 연결이 잘 된 것으로 보여진다.

• 참고 Oracle Version 확인

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0		Production
TNS for Linux: Version 11.2.0.1.0 - Prduction
NLSRTL Version 11.2.0.1.0 - Production

4. Ora2pg 보고서 생성

Migration 전 보고서 생성을 하여 미리 자동 변환 할 수 없는 항목을 알 수 있다.

# ora2pg -t SHOW_REPORT -c $HOME/migration_project/config/ora2pg.conf --estimate_cost --dump_as_html > $HOME/migration_report.html

5. Oracle에서 DB schema 내보내기

# cd /root/migration_project
# ./export_schema.sh

6. PostgreSQL DB schema 가져오기

# cd $HOME/migration_project
# ./import_all.sh -d {postgresql db} -o {postgresql user} -U {postgresql user} -I -s

• -d dbname : database name for import
• -o username : owner of the database to create
• -U username : username to connect to PostgreSQL (default: peer username)
• -I : do not try to load indexes, constraints and triggers
• -s : import schema only, do not try to import data

ex) user : system / password : manager / dbname : test_db

Password for user system: manager
Would you like to drop the database test_db before recreate it? [y/N/q] N
Would you like to import SEQUENCE from ./schema/sequences/sequence.sql? [y/N/q] y
Running: psql --single-transaction  -U system -d test_db -f ./schema/sequences/sequence.sql
Password for user system: manager
Would you like to import TABLE from ./schema/tables/table.sql? [y/N/q] y
Running: psql --single-transaction  -U system -d test_db -f ./schema/tables/table.sql
Password for user system: manager
Would you like to import PACKAGE from ./schema/packages/package.sql? [y/N/q] y
Running: psql --single-transaction  -U system -d test_db -f ./schema/packages/package.sql
Password for user system: manager
Would you like to import FUNCTION from ./schema/functions/function.sql? [y/N/q] y
Running: psql --single-transaction  -U system -d test_db -f ./schema/functions/function.sql
Password for user system: manager
Would you like to import PROCEDURE from ./schema/procedures/procedure.sql? [y/N/q] y
Running: psql --single-transaction  -U system -d test_db -f ./schema/procedures/procedure.sql
Password for user system: manager

7. Data 가져오기

▷ schema와 data를 한 번에 옮기는 방법도 있지만, 오류가 생기는 경우가 많아서 schema 먼저 진행 후 data를 가져오는 것이 좋다.

# cd $HOME/migration_project/config
# vi ora2pg.conf
	.
    .
    .
    #FDW_Server	orcl		#주석 처리
    ...
# ora2pg -t COPY -o data.sql $HOME/migration_project/data -c $HOME/migration_project/config/ora2pg.conf

• data 무결성 검사

# ora2pg -t TEST -c $HOMOE/migration_project/config/ora2pg.conf