이전에 이미 만들었던 디비를 이용해서 데이터 이관에 대해서 테스트 합니다.
디비가 없어서 새로만들어야 한다면 아래 링크를 참고하세요
2023.12.01 - [Database/Oracle] - 62 - [Oracle 19C] DB Link
62 - [Oracle 19C] DB Link
DATABASE LINK • A database link is a schema object in one database that enables you to access objects on another database. • The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneo
unsungit.tistory.com
PROD1의 X1 스키마를 PROD2 의 X2 스키마로 복원하는 방법에 대해서 알아보자
PROD1, PROD2 를 만들었다면 아래의 명령어로 권한을 할당합니다.
# PROD1 에서 필요한 작업들.
alter session set container=prod1;
show con_name
show user;
/*
Session altered.
CON_NAME
------------------------------
PROD1
USER is "SYS"
*/
grant create session, dba to prod1_admin;
/*
Grant succeeded.
*/
# conn prod1_admin
SQL> conn prod1_admin/Manager123#@prod1
Connected.
SQL> show user
USER is "PROD1_ADMIN"
SQL> show con_name
CON_NAME
------------------------------
PROD1
SQL> create user x1 identified by x1;
User created.
SQL> grant create session, create view, create table, unlimited tablespace to x1;
Grant succeeded.
# conn x1
SQL> conn x1/x1@prod1
Connected.
SQL> show user
USER is "X1"
SQL> show con_name
CON_NAME
------------------------------
PROD1
SQL> create table emp
(id number primary key, name varchar2(100) );
Table created.
SQL> Insert into emp values (1, ' khaled');
1 row created.
SQL> Insert into emp values (2, ' ahmed');
1 row created.
SQL>Commit;
Commit complete.
SQL> Create table dept ( deptno number primary key , name varchar2(100 ) );
Table created.
SQL>Insert into dept values (1, ' sales dept ');
1 row created.
SQL> Commit;
Commit complete.
# PROD2 에서 필요한 작업들.
alter session set container=prod2;
show con_name
show user;
/*
Session altered.
CON_NAME
------------------------------
PROD2
USER is "SYS"
*/
grant create session, dba to prod2_admin;
/*
Grant succeeded.
*/
# conn prod_admin
SQL> conn prod2_admin/Manager123#@prod2
Connected.
SQL> show user
USER is "PROD2_ADMIN"
SQL> show con_name
CON_NAME
------------------------------
PROD2
SQL> create user x2 identified by x2;
User created.
SQL> grant create session, create view, create table, unlimited tablespace to x2;
Grant succeeded.
# conn x2
SQL> conn x2/x2@prod2
Connected.
SQL> show user
USER is "X2"
SQL> show con_name
CON_NAME
------------------------------
PROD2
# DIRECTORY 생성 및 할당
SQL> conn prod1_admin/Manager123#@prod1
Connected.
SQL> show user
USER is "PROD1_ADMIN"
SQL> show con_name
CON_NAME
------------------------------
PROD1
SQL> CREATE DIRECTORY d_prod1 AS '/u01/app/oracle/admin/orcl/dpdump';
Directory created.
SQL> GRANT read, write ON DIRECTORY d_prod1 TO x1;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
# x1 사용자를 이용하여 x1 스키마 백업 시작
[oracle@test dpdump]$ expdp x1/x1@prod1 SCHEMAS=x1 DIRECTORY= d_prod1 DUMPFILE=expx1.dmp
Export: Release 19.0.0.0.0 - Production on Thu Dec 21 15:04:03 2023
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
Starting "X1"."SYS_EXPORT_SCHEMA_01": x1/********@prod1 SCHEMAS=x1 DIRECTORY=d_prod1 DUMPFILE=expx1.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "X1"."DEPT" 5.492 KB 1 rows
. . exported "X1"."EMP" 5.492 KB 2 rows
Master table "X1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for X1.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/orcl/dpdump/expx1.dmp
Job "X1"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 21 15:04:35 2023 elapsed 0 00:00:29
# 실행결과를 로그파일에서 확인하는 방법
[oracle@test dpdump]$ cat export.log
;;;
Export: Release 19.0.0.0.0 - Production on Thu Dec 21 15:04:03 2023
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
Starting "X1"."SYS_EXPORT_SCHEMA_01": x1/********@prod1 SCHEMAS=x1 DIRECTORY=d_prod1 DUMPFILE=expx1.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "X1"."DEPT" 5.492 KB 1 rows
. . exported "X1"."EMP" 5.492 KB 2 rows
Master table "X1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for X1.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/orcl/dpdump/expx1.dmp
Job "X1"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 21 15:04:35 2023 elapsed 0 00:00:29
[oracle@test dpdump]$
# dump 내용을 sql 파일로 변환하는 방법
[oracle@test dpdump]$ impdp x1/x1@prod1 SCHEMAS=x1 DIRECTORY= d_prod1 DUMPFILE=expx1.dmp SQLFILE=x1_sql
Import: Release 19.0.0.0.0 - Production on Thu Dec 21 15:07:48 2023
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
Master table "X1"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "X1"."SYS_SQL_FILE_SCHEMA_01": x1/********@prod1 SCHEMAS=x1 DIRECTORY=d_prod1 DUMPFILE=expx1.dmp SQLFILE=x1_sql
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "X1"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Thu Dec 21 15:07:51 2023 elapsed 0 00:00:02
# dump 파일은 아래와 같다.
[oracle@test dpdump]$ vi x1_sql.sql
# 백업파일을 복구하기 위해서 DIRECTORY 파일을 prod2 에 생성
SQL> conn prod2_admin/Manager123#@prod2
Connected.
SQL> show user
USER is "PROD2_ADMIN"
SQL> show con_name
CON_NAME
------------------------------
PROD2
SQL> CREATE DIRECTORY d_prod1 AS '/u01/app/oracle/admin/orcl/dpdump';
Directory created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@test dpdump]$
# 데이터 복원은 dba 권한이 있는 prod2_admin 사용자를 이용하여 복원.
# 스키마를 x1 -> x2 로 변경함
[oracle@test dpdump]$ impdp prod2_admin/Manager123#@prod2 REMAP_SCHEMA=x1:x2 DIRECTORY= d_prod1 DUMPFILE=expx1.dmp
Import: Release 19.0.0.0.0 - Production on Thu Dec 21 15:17:59 2023
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
Master table "PROD2_ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PROD2_ADMIN"."SYS_IMPORT_FULL_01": prod2_admin/********@prod2 REMAP_SCHEMA=x1:x2 DIRECTORY=d_prod1 DUMPFILE=expx1.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "X2"."DEPT" 5.492 KB 1 rows
. . imported "X2"."EMP" 5.492 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "PROD2_ADMIN"."SYS_IMPORT_FULL_01" successfully completed at Thu Dec 21 15:18:16 2023 elapsed 0 00:00:15
# 복원 결과를 아래 로그 파일을 통해서 확인 가능하다
[oracle@test dpdump]$ cat import.log
;;;
Import: Release 19.0.0.0.0 - Production on Thu Dec 21 15:17:59 2023
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
Master table "PROD2_ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PROD2_ADMIN"."SYS_IMPORT_FULL_01": prod2_admin/********@prod2 REMAP_SCHEMA=x1:x2 DIRECTORY=d_prod1 DUMPFILE=expx1.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "X2"."DEPT" 5.492 KB 1 rows
. . imported "X2"."EMP" 5.492 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "PROD2_ADMIN"."SYS_IMPORT_FULL_01" successfully completed at Thu Dec 21 15:18:16 2023 elapsed 0 00:00:15
[oracle@test dpdump]$
# 복원 확인, x2 사용자로 로그인하여 확인.
[oracle@test dpdump]$ sqlplus x2/x2@prod2
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 21 15:19:36 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Dec 21 2023 14:24:05 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user
USER is "X2"
SQL> show con_name
CON_NAME
------------------------------
PROD2
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
# 사용자 생성까지 포함된 백업을 만들기 위해서는 dba 권한이 있는 prod1_admin 유저를 이용하면 된다.
[oracle@test dpdump]$ expdp prod1_admin/Manager123#@prod1 SCHEMAS=x1 DIRECTORY= d_prod1 DUMPFILE=expx1_admin.dmp
Export: Release 19.0.0.0.0 - Production on Thu Dec 21 15:26:29 2023
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
Starting "PROD1_ADMIN"."SYS_EXPORT_SCHEMA_01": prod1_admin/********@prod1 SCHEMAS=x1 DIRECTORY=d_prod1 DUMPFILE=expx1_admin.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "X1"."DEPT" 5.492 KB 1 rows
. . exported "X1"."EMP" 5.492 KB 2 rows
Master table "PROD1_ADMIN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PROD1_ADMIN.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/orcl/dpdump/expx1_admin.dmp
Job "PROD1_ADMIN"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 21 15:26:55 2023 elapsed 0 00:00:25
# 백업내용을 확인하기 위해서 sql 파일로 변환
[oracle@test dpdump]$ impdp prod1_admin/Manager123#@prod1 SCHEMAS=x1 DIRECTORY= d_prod1 DUMPFILE=expx1_admin.dmp SQLFILE=x1_admin_sql
Import: Release 19.0.0.0.0 - Production on Thu Dec 21 15:31:16 2023
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
Master table "PROD1_ADMIN"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "PROD1_ADMIN"."SYS_SQL_FILE_SCHEMA_01": prod1_admin/********@prod1 SCHEMAS=x1 DIRECTORY=d_prod1 DUMPFILE=expx1_admin.dmp SQLFILE=x1_admin_sql
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "PROD1_ADMIN"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Thu Dec 21 15:31:20 2023 elapsed 0 00:00:03
x1 사용자를 이용하여 백업한 경우의 sql 변환파일 내용
PROD1_ADMIN 사용자를 이용하여 백업한 경우의 sql 변환파일 내용, x1 유저를 생성하는 부분이 추가되어 있다.
# 유저 x3를 생성하지 않고 바로 매핑 정보를 이용해서 사용자를 생성하면서 데이터를 복원해보자.
[oracle@test dpdump]$ impdp prod2_admin/Manager123#@prod2 REMAP_SCHEMA=x1:x3 DIRECTORY= d_prod1 DUMPFILE=expx1_admin.dmp
Import: Release 19.0.0.0.0 - Production on Thu Dec 21 16:17:49 2023
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
Master table "PROD2_ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PROD2_ADMIN"."SYS_IMPORT_FULL_01": prod2_admin/********@prod2 REMAP_SCHEMA=x1:x3 DIRECTORY=d_prod1 DUMPFILE=expx1_admin.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "X3"."DEPT" 5.492 KB 1 rows
. . imported "X3"."EMP" 5.492 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "PROD2_ADMIN"."SYS_IMPORT_FULL_01" successfully completed at Thu Dec 21 16:18:05 2023 elapsed 0 00:00:15
[oracle@test dpdump]$
# 스키마 x3를 만들었지만 비밀번호는 스키마 x1의 것과 동일하다
SQL> conn x3/x1@prod2
Connected.
# 복원된 내용을 확인
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
SQL> select * from dept;
DEPTNO NAME
---------- --------------------
1 sales dept
SQL> select * from emp;
ID NAME
---------- --------------------
1 khaled
2 ahmed
'Database > Oracle' 카테고리의 다른 글
| 112 - [Oracle 19C] SQL*Loader Loading Methods - Conventional Load (1) | 2023.12.25 |
|---|---|
| 111 - [Oracle 19C] SQL Loader - discard file example (0) | 2023.12.25 |
| 109 - [Oracle 19C] External Table - ORACLE_DATAPUMP (0) | 2023.12.21 |
| 108 - [Oracle 19C] External Table - ORACLE_LOADER (2) | 2023.12.21 |
| 107 - [Oracle 19C] How to use SQL*Loader (1) | 2023.12.20 |