[Oracle 19c] 11g XE에서 19c (19.3)로 Upgrade 및 Migration (expdp / impdp)

검은체리 2021. 10. 14. 18:15

1. 현재 사용중인 11g XE에서 Version 및 Dump 디렉토리 확인

접속 계정 : Redhat7 : oracle / oracle

oracle 19c : ORACLE / ORACLE

[oracle@dcbsdb dpdump] sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 14 14:57:25 2021

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>
SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DATA_PUMP_DIR
/u01/app/oracle/admin/XE/dpdump/

SQL>select * from dba_tablespaces;
# Tablespace 확인 (복구시 필요)

[oracle@dcbsdb ~]$ cd /u01/app/oracle/admin/XE/dpdump/

[oracle@dcbsdb dpdump] ll

drwxr-x--- 2 oracle dba     4096 Oct  4 17:40 ./
drwxr-x--- 6 oracle dba     4096 Oct 16  2019 ../
-rw-r----- 1 oracle dba 75685888 Oct  1 14:54 DB_20211001.DMP
-rw-r--r-- 1 oracle dba 16700 Oct  1 14:54 DB_20211001.LOG

2. 현재 사용중인 Oracle DB 백업

[oracle@dcbsdb ~]$ cd /u01/app/oracle/admin/XE/dpdump/

[oracle@dcbsdb ~]$ expdp ORACLE/ORACLE@xe directory=DATA_PUMP_DIR dumpfile=Test_DB_202110.DMP logfile=Test_DB_202110.LOG

# ORACLE/ORACLE : DB 계정 (OS 계정과 혼돈하지 말자)

# xe : DB명

# DATA_PUMP_DIR : /u01/app/oracle/admin/XE/dpdump/ (위에서 확인한 디렉토리)

# Test_DB_202110.DMP : 저장할 파일명

# Test_DB_202110.LOG : 저장할 LOG명

Export: Release 11.2.0.2.0 - Production on Thu Oct 14 15:38:53 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "DEVPAYON"."SYS_EXPORT_SCHEMA_01": ORACLE/********@xe directory=DATA_PUMP_DIR dumpfile=Test_DB_20211015.DMP logfile=Test_DB_20211015.LOG
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 113.5 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
.
.
. . exported "DEVPAYON"."SPRING_SESSION_ATTRIBUTES"          0 KB       0 rows
Master table "DEVPAYON"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEVPAYON.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/XE/dpdump/Test_DB_20211015.DMP
Job "DEVPAYON"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:39:19

[oracle@dcbsdb dpdump] ll

drwxr-x--- 2 oracle dba 4096 Oct 4 17:40 ./
drwxr-x--- 6 oracle dba 4096 Oct 16 2019 ../
-rw-r----- 1 oracle dba 75755520 Oct 14 15:39 Test_DB_20211015.DMP
-rw-r--r-- 1 oracle dba 16685 Oct 14 15:39 Test_DB_20211015.LOG

==========================================================================

3. 다른 Redhat7에 백업 파일 oracle 19c에 복구 하기

- oracle 19c 설치 완료 후...

- 백업한 파일 복구할 Redhat7에 복사 (/swdb/app/oracle/admin/orcl/dpdump)

- 신규 설치 경로 권한

chown -R oracle:oinstall /swdb/app/oracle/admin/orcl/dpdump
cd /swdb/app/oracle/admin/orcl/dpdump

- DB 계정생성

"alter session set "_ORACLE_SCRIPT"=true;"를 먼저 입력하지 않으면 계정 생성시 "ORA-65096: invalid common user or role name"가 나옵니다.

[oracle@dcbsdb dpdump]$ sqlplus '/ as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 14 15:51:40 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>
SQL>
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL>
SQL> create user ORACLE identified by ORACLE;

User created.
SQL>

- Table Space 생성 (11g XE에서 사용하던 Table Space로 복구)

create tablespace TABLESPACE_DATA datafile '/swdb/app/oracle/admin/orcl/dpdump/TABLESPACE_DATA ' size 1000M autoextend on next 100M maxsize unlimited;

#TABLESPACE_DATA : 기존에 사용하던 TABLESPACE_DATA이름

[oracle@tcbsdb dpdump]$ impdp ORACLE/ORACLE@orcl directory=DATA_PUMP_DIR dumpfile=Test_DB_202110.DMP logfile=Test_DB_202110.LOG

Import: Release 19.0.0.0.0 - Production on Fri Oct 1 17:21:52 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "DEVPAYON"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEVPAYON"."SYS_IMPORT_FULL_01": DEVPAYON/********@orcl directory=DATA_PUMP_DIR dumpfile=PayOn_Live_DB_2021001.DMP logfile=PayOn_Live_DB_20211001.LOG
Processing object type SCHEMA_EXPORT/USER
.
.

# ORACLE/ORACLE : DB 계정 (OS 계정과 혼돈하지 말자)

# orcl : DB명

# DATA_PUMP_DIR : /swdb/app/oracle/admin/orcl/dpdump

# Test_DB_202110.DMP : 복구할 파일명

# Test_DB_202110.LOG : 복구할 LOG명

4. 검증하기

- sql developer 다운로드 (oracle 로그인 필요)

https://www.oracle.com/tools/downloads/sqldev-downloads.html

- 다운로드후 DB 접속하여 데이터 확인