PostgreSQL to Oracle 데이터베이스 마이그레이션

데이터베이스

PostgreSQL to Oracle 데이터베이스 마이그레이션

샐리_ 2023. 9. 21. 17:02

새로운 프로젝트 시작.

고객사 데이터베이스를 oracle을 사용한다고 하여 마이그레이션 작업을 진행하였다.

사용할 버전은 아직 확인 중이어서 일단 11g 버전으로 설치하였다. 11g를 사용한 이유는 찾아보니 대부분의 기업에서 11g 버전을 많이 사용한다고 하여 선택하게 되었다.

그리고 도커를 사용해 오라클을 사용하려고 한다. 오라클이 무겁다는 얘기도 많이 들었고, 이미 이것저것 많은 것들이 설치되어 있어 도커를 사용하기로 하였다.

오라클 설치(11g version)

도커 이미지 pull

docker pull jaspeen/oracle-xe-11g

도커 오라클 실행

docker run --name oracle -d -p 8080:8080 -p 1521:1521 jaspeen/oracle-xe-11g

도커 오라클 bash 접속

docker exec -it oracle bash

접속했다면 사용자를 생성하자.

sql 접속

$ sqlplus
$ Enter user-name: system
$ Enter password: oracle

오라클 버전 19c

도커 오라클 19c 이미지 pull

docker pull doctorkirk/oracle-19c

도커 오라클 실행

docker run -e ORACLE_SID=ORCL --name oracle19c -d -p 9090:8080 -p 1521:1521 doctorkirk/oracle-19c

도커 오라클 접속

docker exec -it oracle19c bash

sql 접속

sqlplus '/as sysdba'

사용자 목록 확인

SELECT * FROM ALL_USERS;

사용자(데이터베이스) 생성

오라클은 mysql과 달리 데이터 테이블을 관리할 수 있는 데이터베이스를 만들기가 더 까다롭다. 우선 사용자라는 것을 만들고 권한을 부여해야만 한다. 그리고 나서 데이터베이스를 따로 만드는 것이 아니라 사용자를 중심으로 테이블을 관리한다. 즉, 데이터베이스를 만드는 것처럼 사용자를 만들고 사용자에 맞게 테이블을 관리하면 된다.

CREATE USER 사용자명
IDENTIFIED BY 비밀번호
[DEFAULT TABLESPACE USERS 기본 테이블 스페이스
TEMPORARY TABLESPACE 임시 테이블 스페이스];

기본 테이블 스페이스, 임시 테이블 스페이를 지정해줘야 한다. 테이블 스페이스란 다양한 데이터들을 저장하는 공간으로 사용자 생성 시 별도 생성하는 것이 좋다고 한다. 만약 지정하지 않으면 system 계정과 이를 공유하게 되는데 이는 데이터 분리에도 좋지 않다고 한다.
그러나 나는 테스용이기 때문에 따로 만들고 설정하지 않았다!

사용자 삭제

DROP USER 사용자명 CASCADE;

사용자 삭제 시 에러
ORA-01940: cannot drop a user that is currently connected
접속 중인 사용자는 삭제할 수 없다.
도커 프로세스를 재시작하고 사용자를 삭제하면 된다.
그러나 만약 이러한 상황이 아니라면?
그렇다면 사용자명으로 사용 중인 세션을 죽이고 삭제하면 된다.

세션 조회

SELECT sid,serial#,username,status FROM v$session WHERE USERNAME='유저명';

kill 명령어로 해당 세션 죽이기

ALTER SYSTEM KILL SESSION 'sid값,serial값';

계정 권한 부여

사용자를 생성한 후 권한을 주지 않으면 계정을 제대로 쓸 수 없다.
보통 실습할 때는 3개의 권한을 준다고 한다. 그래서 나도 3개의 권한만 주었다.

connect : 데이터 베이스 접속과 관련한 권한
resource : 사용자가 테이블, 시퀸스 등 데이터 관련 객체를 생성할 수 있는 권한
dba : 시스템 자원의 무제한 사용, 다른 사용자 권한 조정 등 강력한 기능에 대한 권한

GRANT CONNECT TO 유저명;
GRANT RESOURCE TO 유저명;
GRANT DBA TO 유저명;

반대로 권한을 회수할 때는 아래와 같다.

REVOKE CONNECT TO 유저명;
REVOKE RESOURCE TO 유저명;
REVOKE DBA TO 유저명;

라이브러리 추가

오라클 버전에 따라 라이브러리 버전을 다르게 추가해줘야 한다.

오라클 버전	ojdbc 버전 및 JDK 버전
21.X	ojdbc11.jar(JDK11, 12, 13, 14, 15 및 17 포함) ojdbc8.jar(JDK8, 11, 12, 13, 14 및 15 포함)
19.X	ojdbc10.jar(JDK10, 11 포함) ojdbc8.jar(JDK8, 9, 11 포함)
18.X	ojdbc8.jar JDK8, 9, 10, 11
12.2 또는 12cR2	ojdbc8.jar JDK8이 있는
12.1 또는 12cR1	ojdbc7.jar(JDK 7 및 JDK 8 포함) ojdbc6.jar(JDK 6 포함)
11.2 또는 11gR2	ojdbc6.jar(JDK6, 7 및 8 포함) 참고 : JDK 7 및 8은 11.2.0.3 및 11.2.0.4에서만 지원됨 JDK 5가 포함된 ojdbc5.jar

라이브러리 추가

implementation 'com.oracle.database.jdbc:ojdbc8-production:23.2.0.0'

Dialect(방언)

Dialect 방언에 대해 알아보기 전 기본 SQL을 이해해야 한다.

SQL은 표준 ANCI SQL이 있고 DBMS Vendor(공급업체)인 MS-SQL, Oracle, My-SQL, PostgreSQL에서 제공하는 SQL이 존재한다. MS-SQL은 T-SQL, Oracle은 PL/SQL이 대표적이다.

ANSI SQL은 모든 DBMS에서 공통적으로 사용이 가능한 표준 SQL이지만 DBMS에서 만든 SQL은 자신들만의 독자적인 기능을 추가하기 위해 만든 것으로 사용하는 DBMS에서만 사용이 가능하다.

예를 들면 My-SQL에서는 ID 값을 증가시키기 위해 Auto Increment를 사용할 수 있지만 Oracle에서는 이 기능을 제공하지 않고 Sequence를 사용하여 기능을 제공한다. 동일한 기능을 제공하지만 서로 다르게 제공하는 것이다. 이는 같은 DBMS라도 버전에 따라 달라질 수 있다.

Dialect(방언)이란?

JPA는 기본적으로 어플리케이션에서 직접 JDBC 레벨의 SQL을 작성하지 않고 JPA가 직접 SQL을 작성하고 실행한다. 그런데 DBMS 종류마다 사용하는 SQL이 다르다는 것을 앞에 알아봤다. JPA가 해당 DBMS에 맞춰 SQL을 생성해야 하는데 어떤 종류인지 알지 못한다면 문제가 발생할 수 있다.

그래서 JPA에 어떤 DBMS를 사용하는지 알려주는 방법이 방언을 설정하는 방법이다. JPA에 Dialect을 설정할 수 있는 추상화 방언 클래스를 제공하고 설정된 방언으로 각 DBMS에 맞는 구현체를 제공한다.

그래서 만약 MariaDB로 개발을 진행한다면 Dialect에 MariaDB로 설정해 놓다가 어플리케이션을 설치할 때 DBMS가 Oracle이라면 Dialect을 Oracle로 설정하면 문제없이 어플리케이션을 구동할 수 있다.

Dialect 설정

spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect

오라클 11g 버전

implementation 'com.oracle.database.jdbc:ojdbc6:11.2.0.4'

spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

버전 확인 전 11g 버전에 맞춰 세팅한 후 19c 버전에 맞춰서 설정을 변경한 후 기동하니 에러가 발생하였다.

Logging system failed to initialize using configuration from 'classpath:logback-local.xml'
java.lang.IllegalStateException: Could not initialize Logback logging from classpath:logging-config-local.xml
	at org.springframework.boot.logging.logback.LogbackLoggingSystem.loadConfiguration(LogbackLoggingSystem.java:158)
	at org.springframework.boot.logging.AbstractLoggingSystem.initializeWithSpecificConfig(AbstractLoggingSystem.java:66)
	at org.springframework.boot.logging.AbstractLoggingSystem.initialize(AbstractLoggingSystem.java:57)
	at org.springframework.boot.logging.logback.LogbackLoggingSystem.initialize(LogbackLoggingSystem.java:118)
	at org.springframework.boot.context.logging.LoggingApplicationListener.initializeSystem(LoggingApplicationListener.java:311)
	at org.springframework.boot.context.logging.LoggingApplicationListener.initialize(LoggingApplicationListener.java:281)
	at org.springframework.boot.context.logging.LoggingApplicationListener.onApplicationEnvironmentPreparedEvent(LoggingApplicationListener.java:239)
	at org.springframework.boot.context.logging.LoggingApplicationListener.onApplicationEvent(LoggingApplicationListener.java:216)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:127)
	at org.springframework.boot.context.event.EventPublishingRunListener.environmentPrepared(EventPublishingRunListener.java:80)
	at org.springframework.boot.SpringApplicationRunListeners.environmentPrepared(SpringApplicationRunListeners.java:53)
	at org.springframework.boot.SpringApplication.prepareEnvironment(SpringApplication.java:345)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:308)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
	at com.kt.aicc.ktbot.cms.CmsApplication.main(CmsApplication.java:45)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
Caused by: ch.qos.logback.core.joran.spi.JoranException: Parser configuration error occurred
	at ch.qos.logback.core.joran.event.SaxEventRecorder.buildSaxParser(SaxEventRecorder.java:89)
	at ch.qos.logback.core.joran.event.SaxEventRecorder.recordEvents(SaxEventRecorder.java:57)
	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:151)
	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:110)
	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:53)
	at org.springframework.boot.logging.logback.LogbackLoggingSystem.configureByResourceUrl(LogbackLoggingSystem.java:178)
	at org.springframework.boot.logging.logback.LogbackLoggingSystem.loadConfiguration(LogbackLoggingSystem.java:155)
	... 23 more
Caused by: javax.xml.parsers.ParserConfigurationException: SAX feature 'http://xml.org/sax/features/external-general-entities' not supported.
	at oracle.xml.jaxp.JXSAXParserFactory.setFeature(JXSAXParserFactory.java:272)
	at ch.qos.logback.core.joran.event.SaxEventRecorder.buildSaxParser(SaxEventRecorder.java:82)
	... 29 more

BUILD SUCCESSFUL in 10s
9 actionable tasks: 1 executed, 8 up-to-date
오전 10:55:19: Execution finished 'bootRun -Dspring.profiles.active=local'.

logback-local.xml 파일 내 경로가 지정되지 않아서 발생한 에러일 수 있다고 하여 경로도 추가해 봤는데 계속 오류가 났다..
(근데 경로 지정문제였다면 이전부터 에러가 발생되어야 하지 않나 싶기도 하다...)

찾아보니 원인은 ojdbc8 버전으로 업그레이드하면서 나온 에러라고 한다.

의존성이 충돌하거나 logback 버전을 호환하지 못하는 경우 에러가 발생할 수 있다고 한다. ojdbc 라이브러리에서 xmlparserv2 의존성을 제거했더니 정상 동작하였다.

ojdbc8-production 버전을 사용했는데 대신 ojdbc8 기본 버전을 사용해도 해당 문제를 해결할 수 있다고 한다.

자 이제 에러를 맞이해 볼 시간이다.

1. ORA-00972: identifier is too long

식별자가 너무 길다. 30자보다 긴 열이나 테이블 같은 객체 사용 시 발생한다.

테이블 명, 컬럼 명 길이를 줄이자.

2. ORA-00904: invalid indentifier

열 이름이 잘못되었을 경우 발생한다.

존재하지 않는 열 이름을 지정
정의된 열 이름과 대소문자가 일치하지 않는 경우
작은 따옴표와 큰 따옴표 사용방법의 오류
열 이름에 특수문자 사용
열 이름에 오라클 예약어 사용

comment 키워드를 컬럼 이름으로 사용하였었는데 이 키워드는 오라클 예약어이다.

3. ORA-01754: a table may contain only one column of type Long

Long 타입 컬럼이 2개 이상 있을 경우 발생한다.

Long 타입 대신 CLOB이나 다른 타입을 사용해야 한다.

CLOB이란?

오라클에서 varchar와 varchar2는 동일한 의미의 가변 길이 문자열이다. 둘 다 데이터 길이의 상한치는 4000바이트이다. 데이터 유형은 동일하지만 오라클에서 장래에 varchar를 다른 용도로 사용할 예정으로 varchar 사용을 권장하지 않는다고 한다.

(그래서 PostgreSQL 4000 길이 이하의 컬럼은 varchar2 타입으로 생성된 것 같다.)

그럼 4000길이 이상의 데이터는 어떻게 저장해야 할까?

CLOB 타입을 사용하면 된다. LOB이란 Large Object의 약자로 대용량 데이터를 저장할 수 있는 데이터 타입이다.

LOB 타입의 종류

CLOB : 문자 대형 객체(Character). 오라클은 CLOB과 varchar2 사이에 암시적 변환을 수행한다.
BLOB : 이진 대형 객체(Binary). 이미지, 동영상 등
NCLOB : 내셔널 문자 대형 객체(National). 오라클에서 정의되는 National Character Set을 따르는 문자
BFILE : OS에 저장되는 이진 파일의 이름과 위치를 저장. 읽기 전용 모드로만 액세스 가능

4. ORA-02264: name already used by an existing constraint

이미 사용 중인 제약조건 이름을 사용해서 발생한다.

오라클은 제약조건 이름도 유일해야 한다. PostgreSQL에서는 동일한 이름의 제약조건을 여러 개 사용하였는데 그게 오류를 발생하였다.

테이블 제약조건 확인

SELECT * FROM USER_CONSTRAINTS;

5. ORA-02275: such a referential constraint already exists in the table

참조 제약이 이미 테이블에 존재해서 발생한다.

6. ORA-00932: 일관성 없는 데이터 유형: -이(가) 필요하지만 CLOB임

타입 유형이 달라서 발생한다.

String과 CLOB 타입을 비교하여서 발생하였다. DB에서 가져온 데이터를 TO_CHAR()를 사용하였다.

7. ORA-08177: can't serialize access for this transaction

JobRepository를 가지고 동시에 실행이 될 때 발생할 수 있는 문제라고 한다. JobRepository 설정 중 transactionManager의 IsolationLevel의 속성을 따로 지정하지 않을 경우 default 값으로 ISOLATION_SERIALIZABLE로 설정된다.

jpaProperties에 ISOLATION_DEFAULT 설정을 추가하였다.

properties.put(AvailableSettings.ISOLATION, "ISOLATION_DEFAULT");

8. ORA-00933: SQL command not properly ended

쿼리에 오타가 있는 경우 발생한다.

native 쿼리로 작성된 구분에 alias 설정 시 as를 사용해 발생하였다.

또 DTO에 날짜 타입이 DB와 맞지 않아 발생하였다. DTO는 Date 타입, Table은 LocalDateTime 이여서 타입을 맞춰주었다.

9. ORA-22859: invalid modification of columns

오라클은 CLOB -> VARCHAR2, VARCHAR2 -> CLOB 변경이 안되는것 같다. 나는 CLOB -> VARCHAR2로 변경하려했을때 에러가 발생하였는데 찾아보니 다른사람은 VARCHAR2 -> CLOB으로 변경 시 에러가 발생하였다.

해당 문제의 해결 방법은 원하는 타입의 컬럼을 추가 후 기존 컬럼의 데이터를 새로운 컬럼으로 이동 후 기존 컬럼을 삭제하는 것이다.

(VARCHAR2 -> CLOB으로 변경 시에는 VARCHAR2를 LONG으로 바꾼 후 다시 CLOB 타입으로 변경하는 방법이 있다. CLOB 타입은 LONG 타입으로 변환시에도 에러가 발생하여서 위 방법처럼 새로운 컬럼을 만들고 기존 컬럼을 삭제하였다.)

오라클 인코딩 설정

한글저장이 안 돼서 인코딩 설정을 하게 되었다.

1. 오라클 CHARACTERSET 확인

SELECT * FROM sys.props$ WHERE NAME LIKE '%CHARACTERSET%';

2. DBA 계정 로그인

sqlplus '/as sysdba'

3. DB Character Set 변경

SQL> UPDATE sys.props$ SET value$='AL32UTF8' WHERE name='NLS_CHARACTERSET';

1 row updated.

SQL> UPDATE sys.props$ SET value$='AL16UTF16' WHERE name='NLS_NCHAR_CHARACTERSET';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1610609200 bytes
Fixed Size                  8897072 bytes
Variable Size             402653184 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7876608 bytes
Database mounted.

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;

Database altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 1610609200 bytes
Fixed Size                  8897072 bytes
Variable Size             402653184 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

4. DB Character Set 확인