[Oracle] 11g to 19c Migration_1

Oracle 11g 에서 19c로 이관 시에 11g 세부 버전에 따라 가능한 이전 방법은 다양하다. 

 

이번 포스팅에서 이관하려는 서버 정보는 다음과 같다. 

AS-IS
 - OS : Unix
 - DB 버전 : Oracle 11g


TO-BE
 - OS : Linux
 - DB 버전 :  Oracle 19c

AS-IS DB 버전이 11.2.0.1 이기 때문에 가능한 방법은 위의 사진을 보면 Transportable Tablespaces와 Data Pump라고 나와있지만 TO-BE의 DB가 Standard Edition이기 때문에 Transportable tablespaces가 불가하다. 그러므로 이관 방법으로 Data Pump를 선택했다. 

본 글은 TO-BE가 OS와 DB 버전이 변경되기 때문에  DB 설치와 Datapump를 이용한 이관 작업을 설명할 것이다. 

Oracle 19c 설치파일을 다운로드 받으면 19.3.0.0 밖에 없을 것이다.

19.3.0.0으로만 DB를 운영하면 많은 버그에 노출되는 것과 같으므로 본 글에서는 패치를 통해 19.19.0.0까지 업그레이드 시킨다. 

2023년 11월 기준 19.19.0.0 버전을 선택한 이유는

최신 패치로 설치하면 간혹 DB가 안올라가는 불상사가 있어 최소 6개월 ~ 1년이 지난 패치로 선택하였고 패치 날짜 기준으로 그 버전이 19.19.0.0 이었다. 

패치는 DB 설치시에 같이 진행한다. 

 

[이관 순서]

1. AS-IS DB 조사
2. EXPDP
3. TO-BE DB 설치 및 패치
4. IMPDP 
5. TO-BE 이관 정합성 조사

* 이관 시 주의사항 ( datapump를 이용해 oracle 11gR2에서 full=y로 expdp로 dump 파일을 만든 뒤 impdp schemas 옵션으로 19c나 다른 db로 이관시)

expdp full=y로 dump 파일을 뽑은 경우 데이터베이스의 모든 오브젝트가 다 들어있다. 

하지만 impdp schemas로 import 할 시 일부 오브젝트가 빠지게 된다. 

그러므로 role과 profile을 schames로 import 전 생성하여야 하며 (<-- role과 profile이 없으면 schema import 불가하므로)

DB 사용자가 만든 public db link와 public synonym은 import 후 생성하면 된다. (<--여기서 말하는 DB 사용자란 DB의 모든 계정 중에서 DB 설치 시 생성되는 계정을 제외한 DB를 사용하는 사용자가 생성한 계정을 뜻한다.)

impdp schemas로 import 할 시 빠지는 오브젝트 

1. role
2. profile
3. DB 사용자가 만든 public db link 
4. DB 사용자가 만든  public synonym 

*출처 : 내맘대로 긍정 https://positivemh.tistory.com/810

 

 

[TO-BE DB 설치를 위한 AS-IS DB의 수집할 정보]

1. SID
2. DBname
3. Oracle Base Destination
4. Oracle Home Destination
5. Archive log Destination
6. Oracle Memory(SGA, PGA)
7. NLS_CHARACTERSET
8. NLS_NCHAR_CHARACTERSET
9. TERRITORY
10. LANGUAGE
11. lIistener.ora 파일 정보
12. tnsnames.ora 파일 정보
13. sqlnet.ora 파일 정보
14. Redo log group 및 member와 용량
15. Archive mode 유무
16. DB component 확인 

 

1. SID

 select instance_name from v$instance;

 

2. DBname

select name from v$database;

 

3. Oracle Base Destination

echo $ORACLE_BASE

 

4. Oracle Home Destination

echo $ORACLE_HOME

 

5. Archive log Destination

archive log list

 

6. Oracle Memory(SGA, PGA)

 - SGA

show parameter sga

- PGA

show parameter pga

 

7. NLS_CHARACTERSET

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

 

8. NLS_NCHAR_CHARACTERSET

  select * from nls_database_parameters where parameter = 'NLS_NCHAR_CHARACTERSET';

 

9. TERRITORY

show parameter territory

 

10. LANGUAGE

show parameter language

 

11. lIistener.ora 파일 정보

cat $ORACLE_HOME/network/admin/listener.ora

 

12. tnsnames.ora 파일 정보

cat $ORACLE_HOME/network/admin/tnsnames.ora

 

13. sqlnet.ora 파일 정보

cat $ORACLE_HOME/network/admin/sqlnet.ora

 

14. Redo log group 및 member와 용량

SELECT group#, members, bytes/1024/1024 "Size(MB)"
FROM v$log;

 

15. Archive mode 유무

archive log list

 

16. DB component 확인 

select comp_name, version, status from dba_registry;

 

[TO-BE DB 이관을 위한 AS-IS DB의 수집할 정보]

이관을 위한 수집 정보는 이관 후 AS-IS에서 TO-BE로 이관이 잘 되었는지 확인 또는 이관을 잘하기 위한 용도이다. 

* 이관은 DB 설치시 생생된 스미카를 제외한 스키마를 import 한다. 

 --> 그 이유는 DB 설치 시 생성된 스키마를 제외하지 않고 모든 데이터베이스의 정보를 Datapump로 import 시  새로운 DB 설치시 생성된 스키마도 있기때문에 import 시 error가 발생하며 DB 엔진에 의해 생성된 데이터까지  import 하는 것은 시간낭비이다. 

--> 그러므로 DB 설치시 생생된 스미카(DB 엔진에 의해 생성된 데이터를 가진 스키마)를 제외한 스키마를 조사해야 한다. 

1. 이관할 DB 스키마 조사
2. 스키마별 valid/invalid object 조사 
3. 이관하는 스키마가 만든 public dblink, public synonym 조사 
4. job 조사 
5. constraint 조사
6. Table Row 조사
7. Tablespace DDL 추출
8. Index DDL 추출
9. role과 profile 조사

 

 

1. 이관할 DB 스키마 조사

DB 설치 시 생성된 스키마가 어떤 건지 구분하는 방법은 AS-IS DB 환경과 똑같이 Test DB를 설치한 후 Test DB의 스키마를 조사하면 된다. 

select username, account_status from dba_users;

 

2. 스키마별 valid/invalid object 조사 

select owner, object_type , count(*) total_cnt ,
sum(case when status ='VALID' then 1 else 0 end) as valid_cnt ,
sum(case when status <> 'VALID' then 1 else 0 end) as invalid_cnt
from dba_objects
where owner in ('이관할 스키마이름')
group by owner, object_type
order by 1;

 

3. 이관하는 스키마가 만든 public dblink, public synonym 조사

1. public synonym ddl 추출
select 'create public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';' as "create" from dba_synonyms
where owner = 'PUBLIC'
and table_owner in ('이관할 스키마명');

2. public dblink ddl 추출
select 'create public database link '||db_link||' connect to '||username||' identified by '||'"PW"'||' using '''||host||''';' as "link" from dba_db_links
where owner = 'PUBLIC';

 

4. job 조사 

select job, schema_user, next_date, broken, interval from dba_jobs
where schema_user in ('이관할 스키마이름');

 

5. constraint 조사

SELECT OWNER,
CONSTRAINT_TYPE,
STATUS
COUNT( CONSTRAINT_NAME ) AS "CON_CNT"
FROM dba_constraints
WHERE OWNER  in ('이관할 스키마 이름')
GROUP BY OWNER, CONSTRAINT_TYPE, STATUS
ORDER BY OWNER ,CONSTRAINT_TYPE, STATUS;

 

6. Table Row 조사

select owner, tablespace_name, table_name, num_rows
from dba_tables
where owner in ('이관할 스키마 이름');

 

 

7. Tablespace DDL 추출

Tablespace 중 'SYSTEM',  'SYSAUX', 'TEMP', 'UNDOTBS1' 는 제외하였다. 

그 이유는 TO-BE DB에서는 TO-BE DB의  'SYSTEM', 'SYSAUX', 'TEMP', 'UNDOTBS1' 를 사용할 것이기 때문이다. 

그럼 TO-BE DB에도 'USERS'가 있는데 제외하지 않는 이유가 무엇일까? 

그 이유는 Oracle은 계정을 생성할 시 사용할 defualt tablespace를 지정하지 않으면 'USERS' tablespace로 저장한다. 

그러므로 'USERS' tablespace에는 이관할 스키마가 생성한 데이터도 들어있을 수 있기 때문에 해당 'USERS' tablespace ddl을 확인할 때 추가로 datafile을 생성하였는지만 확인하면 된다.

그 이유는 AS-IS에서 'USERS' tablespace에 데이터 양이 많아추가로 datafile을 생성하여 운영하고 있는 상황에서 TO-BE로 이관할 때 'USERS' tablespace에 datafile를 추가해주지 않으면 impdp 시 'USERS' tablespace 공간이 부족하다고 에러가 나올 것이다. 에러를 방지하고자 사전에 datafile을 추가하기 위한 작업이라고 보면 된다. 

Set pages 500
Set line 500
Set long 20000000

select tablespace_name, dbms_metadata.get_ddl('TABLESPACE',tablespace_name)||';' script
from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');

 

 

8. Index DDL 추출

select index_name, dbms_metadata.get_ddl('INDEX', index_name, owner)||';' script
from dba_indexes
where owner in ('이관할 스키마 이름');

 

 

9. role과 profile 조사

role과 profile을 조사하는 이유는 다음과 같다.

impdp schema로 import 시 schema를 생성하는데 이때 해당 스키마가 DB에서 기본적으로 존재하는 role과 profile이 아닌 손수 생성한 role과 profile을 사용하고 있다면 에러가 발생할 것이다. 

그러므로 impdp schema  명령어 수행 전에  이관할 schema가 생성한 role과 profile을 생성시켜줘야 한다. 

1. role 조사
select * from dba_role_privs where grantee in ('이관할 스키마');

2. 이관할 스키마가 생성한 role이 있다면 조사
select * from dba_sys_privs where grantee='이관할 스키마가 생성한 role 이름';

3.이관할 스키마가 생성한 role 생성 DDL 추출
select 'create role '||grantee||';' as "role" from dba_sys_privs 
where grantee='이관할 스키마가 생성한 role 이름'
and rownum <= 1
union all
select 'grant '||privilege||' to '||grantee||';' as "role"
from dba_sys_privs 
where grantee='이관할 스키마가 생성한 role 이름';

4. profile 조사 
select username, profile from dba_users;

5.이관할 스키마가 생성한 profile 생성 DDL 추출
select 'create profile '||profile||' limit ' as "profile" from dba_profiles
where profile = 'TEST_PROFILE'
and rownum<= 1
union all
select resource_name||' '||limit as "profile" from dba_profiles
where profile = 'TEST_PROFILE'
union all
select ';' from dual;

*출처 : 내맘대로 긍정 https://positivemh.tistory.com/810

 

 

[EXPDP]

1. snapshot too old 방지를 위한 undo 설정 변경

 - undo tablespace 용량 증설

select tablespace_name, retention
from dba_tablespaces
where tablespace_name ='UNDOTBS1';

alter tablespace undotbs1 add datafile '파일위치' size 1G autoextend on next 100m maxsize 30G;

 

- undo retention guarantee 설정

alter tablespace undotbs1 retention guarantee;

 

- undo retention 증량 

alter system set undo_retention=7200;

 

2. expdp 

- expdp 디렉토리 조회 

select * from dba_directories;

 

 

- expdp 디렉토리 만들기 

mkdir /dump

 

 

- expdp 디렉토리 생성 (디렉토리 생성 전 파티션 확인 필요)

create directory dump_full as '/dump';

 

- expdp 실행

* 이관시 DB의 모든 작업은 멈춰있어야 한다. 리스너와 WEB/WAS를 중지시키는 것을 권장한다. 

* 아래의 expdp 명령어는 sys의 비밀번호를 모르는 상황이라면 유용합니다. 

* expdp 시 parallel 옵션은 Enterprise Edition에서만 가능하며 parallel 개수는 물리cpu 개수의 2배 이하로 권장드립니다. 

* job_name 옵셥을 설정해놓으면 expdp 명령어 실행 후 모니터링할 때 expdp 정보를 쉽게 가져올 수 있어 권장드립니다. 

expdp \'/ as sysdba\' directory=dump_full full=y job_name=full_dump dumpfile=full_%U.dmp logfile=full.log parallel=3

 

3. expdp 명령어 실행 후 모니터링 

 

-  alert log 모니터링

Oracle 10g 이하

cat $ORACLE_BASE/admin/<SID>/bdump/alert_<SID>.log

 

Oracle 11g 이상

cat $ORACLE_BASE/diag/rdbms/<DB_UNIQUE_NAME>/<ORACLE_SID>/trace/alert_<SID>.log

* 일반적으로 DB_UNIQUE_NAME은 아래 쿼리로 확인 할수 있다.(일반적으로 SID이름의 소문자이다)
  SQL> SELECT DB_UNIQUE_NAME FROM v$database;

 - 출처 : https://fliedcat.tistory.com/208

 

- expdp 현재 상황 조회 쿼리 

select b.username, a.sid, b.opname, b.target,
round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.time_remaining,
to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
from v$session_longops b, v$session a
where a.sid = b.sid
and b.opname='펌프 잡 이름'
order by 6;

 

 

- expdp wait event 조회 쿼리 

펌프가 멈춘거 같을 때 아래 event를 확인하는 것도 좋은 방법이다. 

펌프의 SID 값은 위의 expdp 현재 상황 조회 쿼리에서 얻을 수 있다. 

select sid, event from v$session_wait where sid=펌프 SID;
select sid, event from v$session_event where sid=펌프 SID;

 

 

- undo tablespace 사용량 조회 쿼리 

expdp 시 undo tablespace가 부족한 상황을 막는 것이 expdp 잘 끝낼 수 있는 방법 중 하나이다.

select tablespace_name, round(sum(bytes)/1024/1024) as MB, status 
from dba_undo_extents
group by tablespace_name, status;

 

 

4. expdp로 나온 dump file을 TO-BE 서버로 옮김  

rsync 또는 veritas net backup 또는 scp를 통해 옮김