앞의 글에서 기본적인 시나리오로 테스트를 했었는데 이번에는 추가시나리오로 다양한 테스트를 진행해보겠습니다.
1.다양한 Object 이관 테스트(Table,Index 이외)
- Table/Index 뿐만 아니라 여러 Object(Package,function,view, Recycle bin 데이터 등)을 이관 해보고 이관을 할 수 있는지 테스트 해봅니다.
- 위에서 테스트 했던 테이블/인덱스 자료는 삭제 후 새로 진행합니다.
사전준비사항
▶ 테이블 생성
Sql>create table tts_1 as
select l.*, o.*
from
(select dbms_random.string('l',10) as name from dual) o,
(select level as lvl
from dual connect by level <= 1000000) l ;
Table created.
▶ 인덱스 생성
Sql>create index tts_1_lvl on tts_1(lvl);
Index created.
▶ Recycle bin 데이터 생성
Sql> create table tts_2
as select * from tts_1;
Table created.
sql> drop table tts_2;
Table dropped.
sql> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$lrsuaiZLEingUKjAOjgGVQ==$0 TABLE (null)
TTS_1 TABLE (null)
▶ 뷰 생성
Sql > create or replace view tts_1_view
as select * from tts_1 where lvl < 100000
▶ 트리거 생성
sql> create table tts_3
2 as select * from tts_1 where 1=2;
Table created.
Sql>create or replace trigger trg_tts_1
after
insert on tts_1
for each row
declare
begin
insert into tts_3
values (:new.lvl,:new.name);
end;
/
-트리거가 잘 작동하는지 확인
sql> insert into tts_1
2 values (1,'BAE');
1 row created.
Sql> commit;
Commit complete.
Sql> select * from tts_3;
LVL NAME
--------------
BAE 1
▶ 패키지 생성
Sql>create or replace package test_pkg
is
procedure sp_test_01 ;
procedure sp_test_02 ;
end ;
/
create or replace package body test_pkg
is
procedure sp_test_01
is
begin
dbms_output.put_line('test ....01') ;
end sp_test_01 ;
procedure sp_test_02
is
j number := 0 ;
begin
for i in 1 .. 10 loop
j := j + 1 ;
dbms_output.put_line(i || ' ---> ' || i) ;
end loop ;
end sp_test_02 ;
end test_pkg ;
/
▶ 함수 생성
Sql> CREATE OR REPLACE FUNCTION TEST_FUNC
RETURN VARCHAR
IS
test VARCHAR2(100);
BEGIN
test := 'THIS IS TEST.';
RETURN test;
END;
/
Function created.
Sql> select test_func from dual;
TEST_FUNC
--------------
THIS IS TEST.
▶ DB링크 생성
tnsnames.ora에 원격 DB의 접속정보 추가
YSBAE2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.113)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = YSBAE2)
)
)
sql> create database link DBLINK_TTS
connect to scott
identified by tiger
using 'YSBAE2';
Database link created.
Sql>select * from emp@DBLINK_TTS;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
…생략
14 rows selected.
▶ 현재 TTS 유저가 가지고 있는 Object들을 확인
SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
TEST_FUNC FUNCTION VALID
TEST_PKG PACKAGE BODY VALID
TEST_PKG PACKAGE VALID
TRG_TTS_1 TRIGGER VALID
TTS_1_VIEW VIEW VALID
TTS_3 TABLE VALID
TTS_1_LVL INDEX VALID
TTS_1 TABLE VALID
DBLINK_TTS DATABASE LINK VALID
9 rows selected.
▶ 테이블 오브젝트를 확인
sql> select * From tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$lrsuaiZLEingUKjAOjgGVQ==$0 TABLE (null)
TTS_1 TABLE (null)
TTS_1_VIEW VIEW (null)
TTS_3 TABLE (null)
-Recycle bin에 들어 있는 Table을 확인할 수 있습니다.
▶ Unused Columns
Sql>create table tts_4
as select * from tts_1 where lvl <1000
Sql> alter table tts_4 set unused column name;
Table altered.
Sql> select * from user_unused_col_tabs;
TABLE_NAME COUNT
------------------------------ ----------
TTS_4 1
1 row selected.
▶ 사전체크(SYS계정에서 진행)
sql> exec dbms_tts.transport_set_check('TTS',true);
PL/SQL procedure successfully completed.
sql> select * from transport_Set_violations;
no rows selected
-위반사항이 없으므로 TTS를 진행합니다.
Source DB
▶ Tablespace Read Only 변경
sql> alter tablespace tts read only;
Tablespace altered.
▶ Datapump 실행 (TTS로 이관 진행)
$ expdp system/oracle dumpfile=trans_dir:tts_t.dmp transport_tablespaces=tts;
Export: Release 11.2.0.1.0 - Production on Thu Nov 7 15:49:01 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:tts_t.dmp transport_tablespaces=tts
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tts/tts_t.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
/app/oracle/oradata/ysbae/tts.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:49:29
-expdp가 완료되면 Target DB로 파일 전송.(dmp파일과dbf파일)
Target DB
기본시나리오에서 테스트 했던 TTS Tablespace는 미리 삭제(아래는 삭제 sql)
Sql> drop tablespace tts including contents and datafiles;
▶ Rman Convert 진행
$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Nov 7 15:32:53 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAASM (DBID=1893767306)
RMAN> convert datafile '/home/oracle/tts/tts.dbf' format '+DATA/ORAASM/tts.dbf';
Starting conversion at target at 19/11/07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/tts/tts.dbf
converted datafile=+DATA/ORAASM/tts.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
Finished conversion at target at 19/11/07
Starting Control File and SPFILE Autobackup at 19/11/07
piece handle=+FRA/ORAASM/AUTOBACKUP/2019_11_07/s_1023723244.321.1023723245 comment=NONE
Finished Control File and SPFILE Autobackup at 19/11/07
RMAN-08591: warning: invalid archived log deletion policy
▶ tts 유저 생성 및 권한 부여
SQL> create user tts identified by "tocsg0124!" default tablespace users;
User created.
SQL> grant resource,connect to tts;
Grant succeeded.
SQL> select username,default_tablespace from dba_users where username='TTS';
USERNAME DEFAULT_TABLESPACE
-----------------------------
TTS USERS
▶Impdp실행
$ impdp system/oracle dumpfile=trans_dir:tts_t.dmp transport_datafiles='+DATA/ORAASM/tts.dbf'
Import: Release 19.0.0.0.0 - Production on Thu Nov 7 15:39:24 2019
Version 19.5.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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:tts_t.dmp transport_datafiles=+DATA/ORAASM/tts.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Nov 7 15:39:44 2019 elapsed 0 00:00:15
▶Object들이 잘 이관 되었는지 확인
SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
--------------- --------------- ---------------------
TTS_1 TABLE VALID
TTS_3 TABLE VALID
TTS_4 TABLE VALID
TTS_1_LVL INDEX VALID
TRG_TTS_1 TRIGGER VALID
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
---------- ---------- ----------
TTS_1 TABLE
TTS_3 TABLE
TTS_4 TABLE
SQL> select * from user_unused_col_tabs;
TABLE_NAME COUNT
--------------------------------------------------------------------------------
TTS_4 1
- 테이블,인덱스,트리거만 이관되었고 나머지 Object는 이관되지 않았습니다. 또한 Unused Column을 한 Table은 유지되었습니다.
- Recycle bin에 있는 Table정보는 이관되지 않았다.
나머지 Object 이관
▶ Metadata_only 옵션을 이용하여 다른 Object들도 이관
Source DB
$ expdp system/oracle dumpfile=trans_dir:sch_meta.dmp schemas=TTS content=metadata_only
Export: Release 11.2.0.1.0 - Production on Thu Nov 7 16:17:44 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=trans_dir:sch_meta.dmp schemas=TTS content=metadata_only logfile=sch_meta.log
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/tts/sch_meta.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:18:08
▶Target DB로 전송 후 진행
▶ dump파일 Target DB에 이관
Target DB
$ impdp system/oracle dumpfile=trans_dir:sch_meta.dmp schemas=TTS content=metadata_only
Import: Release 19.0.0.0.0 - Production on Thu Nov 7 15:55:07 2019
Version 19.5.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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** dumpfile=trans_dir:sch_meta.dmp schemas=TTS content=metadata_only
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TTS" already exists
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "TTS"."TTS_1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "TTS"."TTS_3" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "TTS"."TTS_4" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 4 error(s) at Thu Nov 7 15:55:15 2019
▶ Object 확인
SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
--------------------------------------------------------------------------------
TTS_1 TABLE VALID
TTS_3 TABLE VALID
TTS_4 TABLE VALID
TTS_1_LVL INDEX VALID
TRG_TTS_1 TRIGGER VALID
TEST_PKG PACKAGE VALID
TEST_FUNC FUNCTION VALID
TTS_1_VIEW VIEW VALID
TEST_PKG PACKAGE BODY VALID
DBLINK_TTS DATABASE LINK VALID
10 rows selected.
생성했던 나머지 Object들도 이관된 것을 확인할 수 있습니다.
SYS Object 이관 테스트
▶디렉토리,프로파일,db_link을 SYS유저로 생성
Source DB
디렉토리
sql> create directory tts_test as '/home/oracle/tts'
2 ;
Directory created.
sql> select * from dba_directories where directory_name='TTS_TEST'
OWNER DIRECTORY_NAME DIRECTORY_PATH
-----------------------------------------------------------------
SYS TTS_TEST /home/oracle/tts
프로파일
Sql> create profile TTS_PRO limit
password_life_time 365
failed_login_attempts 300;
Sql>Select * from dba_profiles where profile='TTS_PRO'
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ---------
TTS_PRO COMPOSITE_LIMIT KERNEL DEFAULT
TTS_PRO SESSIONS_PER_USER KERNEL DEFAULT
TTS_PRO CPU_PER_SESSION KERNEL DEFAULT
TTS_PRO CPU_PER_CALL KERNEL DEFAULT
TTS_PRO LOGICAL_READS_PER_SESSION KERNEL DEFAULT
TTS_PRO LOGICAL_READS_PER_CALL KERNEL DEFAULT
TTS_PRO IDLE_TIME KERNEL DEFAULT
TTS_PRO CONNECT_TIME KERNEL DEFAULT
TTS_PRO PRIVATE_SGA KERNEL DEFAULT
TTS_PRO FAILED_LOGIN_ATTEMPTS PASSWORD 300
TTS_PRO PASSWORD_LIFE_TIME PASSWORD 365
TTS_PRO PASSWORD_REUSE_TIME PASSWORD DEFAULT
TTS_PRO PASSWORD_REUSE_MAX PASSWORD DEFAULT
TTS_PRO PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
TTS_PRO PASSWORD_LOCK_TIME PASSWORD DEFAULT
TTS_PRO PASSWORD_GRACE_TIME PASSWORD DEFAULT
16 rows selected.
sql> create database link DBLINK_TTS_SYS
connect to scott
identified by tiger
using 'YSBAE2';
sql> select owner,object_name,object_type from dba_objects where object_type='DATABASE LINK' and owner='SYS'
OWNER OBJECT_NAME OBJECT_TYPE
---------------------------------
SYS DBLINK DATABASE LINK
-include/exclude 가능 Object 확인은
database_export_objects,schema_export_objects,table_export_objects뷰 조회로 확인 가능합니다.
$ expdp system/oracle dumpfile=trans_dir:sys_obj_full_inc.dmp full=y content=metadata_only include=DB_LINK,PROFILE,DIRECTORY
Export: Release 11.2.0.1.0 - Production on Tue Nov 12 15:50:48 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=trans_dir:sys_obj_full_inc.dmp full=y content=metadata_only include=DB_LINK,PROFILE,DIRECTORY
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/tts/sys_obj_full_inc.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 15:50:52
▶파일 전송 후
Target DB
전송 전 데이터 확인
sql> select * from dba_profiles where profile='TTS_PRO';
no rows selected
sql> select * from dba_directories where directory_name='TTS_TEST'
no rows selected
sql> select owner,object_name,object_type,status from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------
SYS SYS_HUB DATABASE LINK VALID
$ impdp system/oracle dumpfile=trans_dir:sys_obj_full_inc.dmp include=DB_LINK,PROFILE,DIRECTORY
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=trans_dir:sys_obj_full_inc.dmp include=DB_LINK,PROFILE,DIRECTORY
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
ORA-31684: Object type DB_LINK:"TTS"."DBLINK_TTS" already exists
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
ORA-31684: Object type DIRECTORY:"ORACLE_OCM_CONFIG_DIR" already exists
…진행
▶이관 후 데이터 확인
프로파일
sql> select * from dba_profiles where profile='TTS_PRO';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
---------------------------------------------------------------------------------
TTS_PRO COMPOSITE_LIMIT KERNEL DEFAULT
TTS_PRO SESSIONS_PER_USER KERNEL DEFAULT
TTS_PRO CPU_PER_SESSION KERNEL DEFAULT
TTS_PRO CPU_PER_CALL KERNEL DEFAULT
TTS_PRO LOGICAL_READS_PER_SESSION KERNEL DEFAULT
TTS_PRO LOGICAL_READS_PER_CALL KERNEL DEFAULT
TTS_PRO IDLE_TIME KERNEL DEFAULT
TTS_PRO CONNECT_TIME KERNEL DEFAULT
TTS_PRO PRIVATE_SGA KERNEL DEFAULT
TTS_PRO FAILED_LOGIN_ATTEMPTS PASSWORD 300
TTS_PRO PASSWORD_LIFE_TIME PASSWORD 365
TTS_PRO PASSWORD_REUSE_TIME PASSWORD DEFAULT
TTS_PRO PASSWORD_REUSE_MAX PASSWORD DEFAULT
TTS_PRO PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
TTS_PRO PASSWORD_LOCK_TIME PASSWORD DEFAULT
TTS_PRO PASSWORD_GRACE_TIME PASSWORD DEFAULT
TTS_PRO INACTIVE_ACCOUNT_TIME PASSWORD DEFAULT
디렉토리
sql> select * from dba_directories where directory_name='TTS_TEST';
OWNER DIRECTORY_PATH DIRECTORY_NAME
-----------------------------------------------------------
SYS /home/oracle/tts TTS_TEST
DB링크
sql> select owner,object_name,object_type,status from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------
SYS SYS_HUB DATABASE LINK VALID
DB링크는 이관되지 않았습니다. Source DB에서 DB Link를 Public 명령어로 다시 만든 후 이관을 진행 합니다.
Source DB
sql>create public database link DBLINK_TTS_SYS_PUB
connect to scott
identified by tiger
using 'YSBAE2';
sql> select owner,object_name,object_type,status from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------
PUBLIC DBLINK_TTS_SYS_PUB DATABASE LINK VALID
SYS DBLINK_TTS_SYS DATABASE LINK VALID
$ expdp system/oracle dumpfile=trans_dir:sys_obj_dblink2.dmp full=y content=metadata_only include=DB_LINK
Export: Release 11.2.0.1.0 - Production on Wed Nov 13 10:55:31 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=trans_dir:sys_obj_dblink2.dmp full=y content=metadata_only include=DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/tts/sys_obj_dblink2.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 10:55:35
▶ 덤프 파일 TargetDB로 전송 후 import작업 진행
$ impdp system/oracle dumpfile=trans_dir:sys_obj_dblink2.dmp include=DB_LINK
Import: Release 19.0.0.0.0 - Production on Wed Nov 13 10:31:40 2019
Version 19.5.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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=trans_dir:sys_obj_dblink2.dmp include=DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 13 10:31:42 2019 elapsed 0 00:00:02
sql> select owner,object_name,object_type,status from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------
SYS SYS_HUB DATABASE LINK VALID
PUBLIC DBLINK_TTS_SYS_PUB DATABASE LINK VALID
▶테스트용 Table index 생성
Source DB
Sql>create table TTS_TA_IND as
select l.*, o.*
from
(select dbms_random.string('l',10) as name from dual) o,
(select level as lvl
from dual connect by level <= 1000000) l ;
sql> select count(*) from tts_ta_ind;
COUNT(*)
----------
1000000
sql>create index tts_ta_ind_lvl on tts_ta_ind(lvl)
tablespace system;
- Table은 TTS에, Index는 SYSTEM에 생성합니다.
sql> select index_name,table_name,tablespace_name,table_owner from user_indexes;
INDEX_NAME TABLE_NAME TABLESPACE_NAME TABLE_OWNER
----------------- ----------- ----------------- -------------
TTS_1_LVL TTS_1 TTS TTS
TTS_TA_IND_LVL TTS_TA_IND SYSTEM TTS
- 방금 생성한 TTS_TA_IND_LVL의 Tablespace가 system인 것을 확인할 수 있습니다.
▶인덱스 Rebuild를 통해 Tablespace를 변경
sql> alter index TTS_TA_IND_LVL rebuild tablespace tts;
Index altered.
sql> select index_name,index_type,status,tablespace_name from user_indexes;
INDEX_NAME INDEX_TYPE STATUS TABLESPACE_NAME
----------------- ------------- -------- ----------------
TTS_TA_IND_LVL NORMAL VALID TTS
TTS_1_LVL NORMAL VALID TTS
- Tts Tablespace로 변경된 것을 확인할 수 있습니다.
▶expdp 진행
sql> alter tablespace tts read only;
Tablespace altered.
sql> exec dbms_tts.transport_set_check('TTS',true);
PL/SQL procedure successfully completed.
sql> select * from transport_Set_violations;
no rows selected
$ expdp system/oracle dumpfile=trans_dir:tts_ta_ind.dmp transport_tablespaces=tts ;
Export: Release 11.2.0.1.0 - Production on Fri Nov 15 14:29:26 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:tts_ta_ind.dmp transport_tablespaces=tts
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tts/tts_ta_ind.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
/app/oracle/oradata/ysbae/tts.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:29:56
▶Target DB로 dbf파일과 dmp파일 전송
▶Rman convert 수행
$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Nov 15 14:08:59 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAASM (DBID=1893767306)
RMAN> convert datafile '/home/oracle/tts/tts.dbf' format '+DATA/ORAASM/tts.dbf'
2> ;
Starting conversion at target at 19/11/15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/tts/tts.dbf
converted datafile=+DATA/ORAASM/tts.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
Finished conversion at target at 19/11/15
Starting Control File and SPFILE Autobackup at 19/11/15
piece handle=+FRA/ORAASM/AUTOBACKUP/2019_11_15/s_1024409385.318.1024409387 comment=NONE
Finished Control File and SPFILE Autobackup at 19/11/15
RMAN-08591: warning: invalid archived log deletion policy
RMAN> exit
▶ Target DB 유저생성
Sql> create user tts identified by "tocsg0124!" default tablespace users;
Sql> grant connect,resource to tts;
Sql>select username,default_tablespace from dba_users where username='TTS';
USERNAME DEFAULT_TABLESPACE
-------------------------------
TTS USERS
▶ impdp수행
$ impdp system/oracle dumpfile=trans_dir:tts_ta_ind.dmp transport_datafiles='+DATA/ORAASM/tts.dbf'
Import: Release 19.0.0.0.0 - Production on Fri Nov 15 14:10:35 2019
Version 19.5.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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:tts_ta_ind.dmp transport_datafiles=+DATA/ORAASM/tts.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Nov 15 14:10:46 2019 elapsed 0 00:00:10
▶ 이관 확인
Sql>select index_name,index_type,status,tablespace_name from user_indexes;
INDEX_NAME INDEX_TYPE STATUS TABLESPACE_NAME
----------------------------------------------------------------------------------
TTS_1_LVL NORMAL VALID TTS
TTS_TA_IND_LVL NORMAL VALID TTS
-인덱스가 잘 이관된 것을 확인할 수 있습니다.
- TTS를 지원하는 platform 조회
sql> select platform_name,endian_format from v$transportable_platform;
PLATFORM_NAME ENDIAN_FORMAT
--------------------------------- --------------
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux x86 64-bit Little
Apple Mac OS Big
Microsoft Windows x86 64-bit Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big
HP IA Open VMS Little
Solaris Operating System (x86-64) Little
Apple Mac OS (x86-64) Little
- 해당 서버의 Endian_format 확인
sql> select d.dbid,d.name,tp.platform_id,tp.platform_name,endian_format from v$transportable_platform tp,v$database d
where tp.platform_name=d.platform_name
;
DBID NAME PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
---------- --------- ----------- -------------------------------
1681187063 YSBAE 13 Linux x86 64-bit Little
▶ Source DB에서 Rman으로 Convert 작업 수행(Target DB 형식으로 변환해야함)
RMAN> convert tablespace 'TTS' to platform 'Solaris Operating System (AMD64)' format '/app/oracle/tts/%U';
- Rman으로 해당 Endian_format으로 사용할 수 있게 변환한 후 Target DB에서도 변환 후 사용하면 됩니다.
- 변환할 format은 임의의 이름이 아니라 Test.dbf와 같이 명시적으로 지정하여도 됩니다.
impdp옵션 sqlfile 사용법
- impdp사용 시 sqlfile=[Directory명]:파일이름 옵션을 사용하면 impdp가 실행될 때 내부적으로 도는 쿼리를 확인할 수 있습니다.
▶ maxgauge 유저 정보를 expdp
Source DB
$ expdp system/oracle dumpfile=trans_dir:user_max.dmp schemas=maxgauge
Export: Release 11.2.0.1.0 - Production on Fri Nov 22 14:37:25 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=trans_dir:user_max.dmp schemas=maxgauge
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
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
…
▶ maxgauge 유저 정보를 impdp
$ impdp system/oracle dumpfile=trans_dir:user_max.dmp sqlfile=trans_dir:tts1.txt
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** dumpfile=trans_dir:user_max.dmp sqlfile=trans_dir:tts1.txt
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/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at Fri Nov 22 14:19:10 2019 elapsed 0 00:00:01
▶tts1.txt 파일 내 확인
$vi tts1.txt
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
CREATE USER "MAXGAUGE" IDENTIFIED BY VALUES 'S:29C00ED9D43BA0001CF61F7D9385DA85667081D94A7BE05E9845B3BF326D;BDD660D2B3C2FB35'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT SELECT ANY DICTIONARY TO "MAXGAUGE";
GRANT CREATE ANY PROCEDURE TO "MAXGAUGE";
GRANT CREATE DATABASE LINK TO "MAXGAUGE";
GRANT SELECT ANY TABLE TO "MAXGAUGE";
GRANT UNLIMITED TABLESPACE TO "MAXGAUGE";
GRANT ALTER SESSION TO "MAXGAUGE";
GRANT CREATE SESSION TO "MAXGAUGE";
GRANT ALTER SYSTEM TO "MAXGAUGE";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
GRANT "RESOURCE" TO "MAXGAUGE";
GRANT "SELECT_CATALOG_ROLE" TO "MAXGAUGE";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "MAXGAUGE" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT MAXGAUGE
…생략
-위와 같이 쿼리형식으로 확인 할 수 있습니다.
6,Raw Device to ASM 이관 테스트
Raw Device 구성 만드는 방법은 운영중인 싱글 DB에 Raw Device 추가하기 글을 참고하시길 바라겠습니다.
▶ Tablespace를 생성하고 테스트 테이블,인덱스를 생성합니다.
sql> create tablespace rawtest
2 datafile '/dev/raw/raw1'
3 size 2G;
Tablespace created.
sql> select name from v$datafile;
NAME
------------------
…생략
/dev/raw/raw1
14 rows selected.
sql> create user rawtest identified by rawtest default tablespace rawtest;
User created.
sql> grant connect,resource to rawtest;
Grant succeeded.
sql> create table rawtest as
select l.*, o.*
from
(select dbms_random.string('l',10) as name from dual) o,
(select level as lvl
from dual connect by level <= 1000000) l ;
Table created.
sql> create index rawtest_lvl on rawtest(lvl);
Index created.
▶ TTS가 가능한지 체크
sql> exec dbms_tts.transport_set_check('RAWTEST',true);
PL/SQL procedure successfully completed.
sql> select * from transport_set_violations;
no rows selected
▶ Tablespace Read only로 변경
sql> alter tablespace rawtest read only;
sql> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
…생략
TEST ONLINE
MAXREPO45 ONLINE
MAXREPOT ONLINE
TTS ONLINE
RAWTEST READ ONLY
▶ RMAN을 통해 Raw Device의 datafile을 file system의 datafile로 변환
$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Nov 29 14:37:02 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: YSBAE (DBID=1681187063)
RMAN> copy datafile '/dev/raw/raw1' to '/home/oracle/tts/rawtest.dbf';
Starting backup at 29-NOV-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/dev/raw/raw1
output file name=/home/oracle/tts/rawtest.dbf tag=TAG20191129T143818 RECID=5 STAMP=1025620711
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 29-NOV-19
▶ tts 메타데이터를 expdp를 통해 추출
$ expdp system/oracle dumpfile=trans_dir:raw_tts.dmp transport_tablespaces=rawtest;
Export: Release 11.2.0.1.0 - Production on Fri Nov 29 14:48:15 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:raw_tts.dmp transport_tablespaces=rawtest
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tts/raw_tts.dmp
******************************************************************************
Datafiles required for transportable tablespace RAWTEST:
/dev/raw/raw1
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:48:40
- dbf파일과 dmp파일을 Target DB 쪽으로 전송하고 이관을 실시
Target DB
▶ DB 유저를 생성
sql> create user rawtest identified by rawtest default tablespace users;
User created.
sql> grant connect,resource to rawtest;
Grant succeeded.
sql> conn rawtest/rawtest
Connected.
sql> select * from tab;
no rows selected
▶ file system에서 ASM으로 이관하는것과 동일한 방법으로 rman convert를 통해 이관을 진행
$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Dec 2 13:29:24 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAASM (DBID=1893767306)
RMAN> convert datafile '/home/oracle/tts/rawtest.dbf' format '+DATA/ORAASM/DATAFILE/RAWTEST.DBF';
Starting conversion at target at 19/11/29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/tts/rawtest.dbf
converted datafile=+DATA/ORAASM/DATAFILE/rawtest.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:35
Finished conversion at target at 19/11/29
Starting Control File and SPFILE Autobackup at 19/11/29
piece handle=+FRA/ORAASM/AUTOBACKUP/2019_11_29/s_1025620476.317.1025620477 comment=NONE
Finished Control File and SPFILE Autobackup at 19/11/29
RMAN-08591: warning: invalid archived log deletion policy
▶ TTS할 대상의 metadata도 impdp를 사용하여 이관
$impdp system/oracle dumpfile=trans_dir:raw_tts.dmp transport_datafiles='+DATA/ORAASM/DATAFILE/RAWTEST.DBF'
Import: Release 19.0.0.0.0 - Production on Fri Nov 29 14:35:52 2019
Version 19.5.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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:raw_tts.dmp transport_datafiles=+DATA/ORAASM/DATAFILE/RAWTEST.DBF
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Nov 29 14:36:12 2019 elapsed 0 00:00:14
▶ 이관된 데이터를 확인
sql> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
MIG_TAB
MIG_IDX
TTS
RAWTEST
sql> select * from user_objects;
OBJECT_NAME OBJECT_TYPE
-------------------------------------------
RAWTEST TABLE
RAWTEST_LVL INDEX
sql> select count(*) from rawtest;
COUNT(*)
----------
1000000
- 이관이 완료되었다.
- Raw Device -> ASM으로 바로 이관은 할 수 없고, Rman을 통해 Raw Device -> file system -> ASM방식으로 이관이 가능합니다.
7.Datapump를 빠르게 작업할 수 있는 방법
병렬로 작업을 진행하여 한꺼번에 여러 개의 작업으로 expdp나 impdp를진행할 수 있고, 시간을 절약할 수 있습니다.
▶ full_database_export를 parallel 옵션 유무에 따라 속도차이가 발생하는지 테스트합니다.
No Parallel 옵션
$ expdp system/oracle dumpfile=trans_dir:full_noparallel.dmp full=y logfile=full_noparallel.log reuse_dumpfiles=y
Export: Release 19.0.0.0.0 - Production on Mon Dec 2 15:20:36 2019
Version 19.5.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
rocessing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/PROFILE
...생략
. . exported "TEST"."PARTITION_TEST":"P80" 0 KB 0 rows
. . exported "TEST"."PARTITION_TEST":"P90" 0 KB 0 rows
. . exported "TEST"."PLAN_TABLE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/tts/full_noparallel.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Mon Dec 2 16:34:35 2019 elapsed 0 01:13:09
Parallel 옵션 (8을 주고 테스트 하였습니다)
$ expdp system/oracle full=y dumpfile=trans_dir:full_parallel8_%U.dmp parallel=8 logfile=full_parallel8.log reuse_dumpfiles=y
Export: Release 19.0.0.0.0 - Production on Mon Dec 2 17:09:15 2019
Version 19.5.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 "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y dumpfile=trans_dir:full_parallel8_%U.dmp parallel=8 logfile=full_parallel8.log reuse_dumpfiles=y
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/PROFILE
...생략
. . exported "TTS"."TTS_KO" 5.5 KB 2 rows
. . exported "CUBEONE"."DIA_ACCESS_LOG_SUCCESS_FLAG" 5.578 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/tts/full_parallel8_01.dmp
/home/oracle/tts/full_parallel8_02.dmp
/home/oracle/tts/full_parallel8_03.dmp
/home/oracle/tts/full_parallel8_04.dmp
/home/oracle/tts/full_parallel8_05.dmp
/home/oracle/tts/full_parallel8_06.dmp
/home/oracle/tts/full_parallel8_07.dmp
/home/oracle/tts/full_parallel8_08.dmp
/home/oracle/tts/full_parallel8_09.dmp
/home/oracle/tts/full_parallel8_10.dmp
/home/oracle/tts/full_parallel8_11.dmp
/home/oracle/tts/full_parallel8_12.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Mon Dec 2 17:22:36 2019 elapsed 0 00:13:20
테스트 결과 아래와 같은 결과가 나왔고 parallel을 사용하여 병렬로 작업하는 것이 속도를 더 빠르게 할 수 있는 방법임을 알 수 있습니다.
No Parallel = 01:13:09
Parallel 8 = 00:13:20
8.CHARTERSET(K016KSC5601<->K016MSWIN949)이 다른 DB 이관
TTS의 기본 요구사항으로 소스 DB와 타겟 DB의 CHARTERSET이 같아야 한다고 명시되어 있습니다.
‘UTF-8 <-> K016MSWIN94’과 같이 다른 종류의 CHARTERSET끼리의 TTS사용은 되지 않지만, 같은 종류의 CHARTERSET인 K016KSC5601과 K016MSWIN949끼리의 이관은 가능한지 테스트를 통해 확인해보겠습니다.
▶ 소스DB CHARTERSET 확인
sql> select * from nls_database_parameters where parameter like '%CHAR%';
PARAMETER VALUE
------------------------------ --------------------------------------------------------------------
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET KO16MSWIN949
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
▶ 타겟DB CHARTERSET 확인
sql> select * from nls_database_parameters where parameter like '%CHAR%';
PARAMETER VALUE
----------------------------------------
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET KO16MSWIN949
현재 둘다 동일한 상태이기 때문에 소스의 CHARTERSET을 변경한다.
▶ 소스DB CHARTERSET 변경
sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup mount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 1207961592 bytes
Database Buffers 436207616 bytes
Redo Buffers 7135232 bytes
Database mounted.
sql> alter system enable restricted session;
System altered.
Elapsed: 00:00:02.03
sql> alter database open;
Database altered.
sql> alter database character set internal_use KO16KSC5601;
Database altered.
Elapsed: 00:00:05.08
sql> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 1207961592 bytes
Database Buffers 436207616 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
sql>
sql> select * from nls_database_parameters where parameter like '%CHAR%';
PARAMETER VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET KO16KSC5601
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
변경되었으므로 TTS를 진행
▶ KO16KSC5601 -> KO16MSWIN949 TTS 진행
sql> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
MAXREPO ONLINE
TEST ONLINE
MAXREPO45 ONLINE
MAXREPOT ONLINE
TTS READ ONLY
sql> exec dbms_tts.transport_set_check('TTS',true);
PL/SQL procedure successfully completed.
Elapsed: 00:01:30.40
sql> sql> select * from transport_Set_violations;
no rows selected
$ expdp system/oracle dumpfile=trans_dir:tts_nls_char.dmp transport_tablespaces=tts
Export: Release 11.2.0.1.0 - Production on Mon Nov 18 09:54:21 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:tts_nls_char.dmp transport_tablespaces=tts
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tts/tts_nls_char.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
/app/oracle/oradata/ysbae/tts.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 09:54:49
DATAFILE과 DMP파일을 타겟 DB로 전송
타겟DB 유저생성 및 준비
sql> create user tts identified by "tocsg0124!" default tablespace users;
User created.
sql> grant connect,resource to tts;
Grant succeeded.
sql> select username,default_tablespace from dba_users where username='TTS';
USERNAME DEFAULT_TABLESPACE
--------------------------------
TTS USERS
$ rman target /
RMAN> convert datafile '/home/oracle/tts/tts.dbf' format '+DATA/ORAASM/tts.dbf';
Starting conversion at target at 19/11/18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/tts/tts.dbf
converted datafile=+DATA/ORAASM/tts.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
Finished conversion at target at 19/11/18
Starting Control File and SPFILE Autobackup at 19/11/18
piece handle=+FRA/ORAASM/AUTOBACKUP/2019_11_18/s_1024652066.322.1024652069 comment=NONE
Finished Control File and SPFILE Autobackup at 19/11/18
RMAN-08591: warning: invalid archived log deletion policy
$ impdp system/oracle dumpfile=trans_dir:tts_nls_char.dmp transport_datafiles='+DATA/ORAASM/tts.dbf';
Import: Release 19.0.0.0.0 - Production on Mon Nov 18 09:36:14 2019
Version 19.5.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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
export done in KO16KSC5601 character set and AL16UTF16 NCHAR character set
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:tts_nls_char.dmp transport_datafiles= +DATA/ORAASM/tts.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Nov 18 09:36:29 2019 elapsed 0 00:00:11
확인
Sql>select * from tab;
TNAME TABTYPE CLUSTERID
----------------------------------------
TTS_1 TABLE (null)
TTS_3 TABLE (null)
TTS_4 TABLE (null)
TTS_TA_IND TABLE (null)
잘 이관되는 것을 확인할 수 있습니다.
▶ KO16MSWIN949 -> KO16KSC5601 TTS 진행
소스 CHARTERSET 변경
sql> select * from nls_database_parameters where parameter like '%CHAR%';
PARAMETER VALUE
------------------------------ -------------
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET KO16KSC5601
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
sql> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> sql> startup mount;
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 1207961592 bytes
Database Buffers 436207616 bytes
Redo Buffers 7135232 bytes
Database mounted.
sql> alter system enable restricted session;
System altered.
Elapsed: 00:00:02.03
sql> alter database open;
Database altered.
Elapsed: 00:00:00.43
sql> alter database character set internal_use KO16MSWIN949;
Database altered.
Elapsed: 00:00:04.75
sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 1207961592 bytes
Database Buffers 436207616 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
sql> select * from nls_database_parameters where parameter like '%CHAR%';
PARAMETER VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET KO16MSWIN949
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
타겟 DB CHARTERSET 변경
sql> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup mount
ORACLE instance started.
Total System Global Area 1560277408 bytes
Fixed Size 8896928 bytes
Variable Size 637534208 bytes
Database Buffers 905969664 bytes
Redo Buffers 7876608 bytes
Database mounted.
sql> alter system enable restricted session;
System altered.
Elapsed: 00:00:03.19
sql> alter database open;
sql> alter database character set internal_use KO16KSC5601;
Database altered.
sql> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup
ORACLE instance started.
Total System Global Area 1560277408 bytes
Fixed Size 8896928 bytes
Variable Size 637534208 bytes
Database Buffers 905969664 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
sql> select * from nls_database_parameters where parameter like '%CHAR%';
PARAMETER VALUE
----------------------------------------
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET KO16KSC5601
타겟 DB 테스트를 위해 이전에 테스트했던 TABLESPACE와 유저를 삭제합니다.
sql> drop tablespace tts including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:06.06
sql> drop user tts cascade;
User dropped.
사전준비 완료
TTS 진행
소스 DB
sql> alter tablespace tts read only;
sql> exec dbms_tts.transport_set_check('TTS',true);
PL/SQL procedure successfully completed.
Elapsed: 00:01:30.40
sql> sql> select * from transport_Set_violations;
no rows selected
$ expdp system/oracle dumpfile=trans_dir:tts_nls_char2.dmp transport_tablespaces=tts
Export: Release 11.2.0.1.0 - Production on Mon Nov 18 10:40:38 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:tts_nls_char2.dmp transport_tablespaces=tts
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tts/tts_nls_char2.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
/app/oracle/oradata/ysbae/tts.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:41:07
타겟DB로 파일전송
타겟 DB
sql> create user tts identified by "tocsg0124!" default tablespace users;
User created.
Elapsed: 00:00:00.27
sql> grant connect,resource to tts;
Grant succeeded.
RMAN Convert
RMAN> convert datafile '/home/oracle/tts/tts.dbf' format '+DATA/ORAASM/tts.dbf';
Starting conversion at target at 19/11/18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/tts/tts.dbf
converted datafile=+DATA/ORAASM/tts.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
Finished conversion at target at 19/11/18
Starting Control File and SPFILE Autobackup at 19/11/18
piece handle=+FRA/ORAASM/AUTOBACKUP/2019_11_18/s_1024655540.319.1024655541 comment=NONE
Finished Control File and SPFILE Autobackup at 19/11/18
RMAN-08591: warning: invalid archived log deletion policy
$ impdp system/oracle dumpfile=trans_dir:tts_nls_char2.dmp transport_datafiles='+DATA/ORAASM/tts.dbf';
Import: Release 19.0.0.0.0 - Production on Mon Nov 18 10:33:37 2019
Version 19.5.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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
import done in KO16KSC5601 character set and AL16UTF16 NCHAR character set
export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=trans_dir:tts_nls_char2.dmp transport_datafiles=+DATA/ORAASM/tts.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible character set
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Mon Nov 18 10:33:48 2019 elapsed 0 00:00:05
K016MSWIN949 -> KO16KSC5601의 TTS는 되지 않습니다.
이유는 K016MSWIN949가 KO16KSC5601의 슈퍼셋(superset)이기 때문입니다. K016MSWIN949은 KO16KSC5601을 그대로 포함하고 있으며 추가로 현대 한글조합으로 표현 할 수 있는 모든 가짓수에 해당하는 8822자의 한글을 추가로 포함하여 사용할 수 있습니다. 따라서 KO16KSC5601 -> K016MSWIN949의 TTS는 가능한 것이며 반대의 경우는 되지 않습니다.
'Oracle > 이관' 카테고리의 다른 글
| Redo log,Datafile 경로 변경 (0) | 2020.10.27 |
|---|---|
| MSSQL to Oracle DB Link (2) | 2020.09.14 |
| [TTS]11g linux to 18c linux migration (0) | 2019.11.05 |
| [TTS]11g linux to 11g window migration (0) | 2019.11.05 |
| [TTS]11g file system to 19c ASM Migration (0) | 2019.11.05 |
댓글