transportable tablespace(테이블스페이스 이관) 알아야 함.
[1차 이관] ora19c → RAC: insa_tbs
# 소스(ora19c)에서 테이블스페이스 및 인덱스 생성
-- 현재 데이터파일 확인
SYS@ora19c> select a.file#,b.name tbs_name,a.name file_name,a.status,a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts#=b.ts#;
FILE# TBS_NAME FILE_NAME STATUS CHECKPOINT_CHANGE#
----- ---------- ------------------------------------------------------------ ------------ ------------------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 15802843
2 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 15802843
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 15802843
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 15802843
5 ASSM_TBS /u01/app/oracle/oradata/ORA19C/assm_tbs01.dbf ONLINE 15802843
4 FLM_TBS /u01/app/oracle/oradata/ORA19C/flm_tbs01.dbf ONLINE 15802843
8 OLTP_TBS /u01/app/oracle/oradata/ORA19C/optp_tbs01.dbf ONLINE 15802843
-- 이관 대상 테이블스페이스 생성
SYS@ora19c> create tablespace insa_tbs datafile '/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf' size 10m autoextend on;
Tablespace created.
SYS@ora19c> select a.file#,b.name tbs_name,a.name file_name,a.status,a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts#=b.ts#;
FILE# TBS_NAME FILE_NAME STATUS CHECKPOINT_CHANGE#
----- ---------- ------------------------------------------------------------ ------------ ------------------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 15802843
2 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 15802843
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 15802843
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 15802843
5 ASSM_TBS /u01/app/oracle/oradata/ORA19C/assm_tbs01.dbf ONLINE 15802843
4 FLM_TBS /u01/app/oracle/oradata/ORA19C/flm_tbs01.dbf ONLINE 15802843
8 OLTP_TBS /u01/app/oracle/oradata/ORA19C/optp_tbs01.dbf ONLINE 15802843
9 INSA_TBS /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf ONLINE 15819658
-- 테스트 테이블 생성(insa_tbs에 위치)
SYS@ora19c> create table hr.insa_emp tablespace insa_tbs as select * from hr.employees;
Table created.
SYS@ora19c> select tablespace_name, bytes, blocks from dba_segments where owner = 'HR' and segment_name = 'INSA_EMP';
TABLESPACE_NAME BYTES BLOCKS
-------------------- ------------- ----------
INSA_TBS 65536 8
-- 인덱스를 일부러 다른 테이블스페이스에 생성
SYS@ora19c> create unique index hr.insa_emp_idx on hr.insa_emp(employee_id) tablespace users;
Index created.
SYS@ora19c> select tablespace_name, bytes, blocks from dba_segments where owner = 'HR' and segment_name = 'INSA_EMP_IDX';
TABLESPACE_NAME BYTES BLOCKS
-------------------- ------------- ----------
USERS 65536 8
메타정보 => 데이터 타입 등이 데이터 딕셔너리 테이블에 있음.
테이블스페이스와 인덱스가 종속되어 있는데 서로 다른 테이블스페이스, 다른 데이터파일에 있다.
# Transportable Tablespace(TTS) 를 사용하기 전에 먼저 해당 테이블스페이스가 운반 가능한 상태인지 검사해야 한다.
SYS@ora19c> exec dbms_tts.transport_set_check(ts_list=>'insa_tbs',incl_constraints=>true)
PL/SQL procedure successfully completed.
-- 테이블의 인덱스가 다른 테이블스페이스에 있는 것이 위반으로 잡혀야 함.
SYS@ora19c> select * from transport_set_violations;
VIOLATIONS
---------------------------------------------------------------------------------------------------------
ORA-39907: Index HR.INSA_EMP_IDX in tablespace USERS points to table HR.INSA_EMP in tablespace INSA_TBS.# index move(위반 해결)
SYS@ora19c> alter index hr.insa_emp_idx rebuild tablespace insa_tbs; -- insa_emp_idx인덱스를 insa_tbs테이블스페이스로 옮김.
Index altered.
재검증. 이제 위반되는 게 없어야 함.
SYS@ora19c> exec dbms_tts.transport_set_check(ts_list=>'insa_tbs',incl_constraints=>true,full_check=>true)
PL/SQL procedure successfully completed.
SYS@ora19c> select * from transport_set_violations;
no rows selected
SYS@ora19c> select tablespace_name, bytes, blocks from dba_segments where owner = 'HR' and segment_name = 'INSA_EMP_IDX';
TABLESPACE_NAME BYTES BLOCKS
--------------------------------- ---------- ----------
INSA_TBS 65536 8
# tablespace read only 변경
alter tablespace insa_tbs read only;
********************************************
=> read only로 돌릴 때 대기 상태가 됐었음.
READ ONLY 전환으로 partial checkpoint(tablespace checkpoint)는 발생하지만, 그것이 직접 로그 스위치를 유발하는 것은 아니고 이번 명령 수행 시점에 DB가 별도로 로그 스위치를 필요로 함.
그런데, 아카이브 저장 공간이 부족해서 아카이빙이 지연되어 명령이 함께 대기한 것이다.
rman target / 들어가서
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1'; <<- 내가 아카이브를 어제 저녁 때 받아서 sysdate-1 거 이전 거를 지움.
이거로 해결함.
********************************************
SYS@ora19c> select a.file#,b.name tbs_name,a.name file_name,a.status,a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts#=b.ts#;
FILE# TBS_NAME FILE_NAME STATUS CHECKPOINT_CHANGE#
----- ---------- ------------------------------------------------------------ ------------ ------------------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 15840569
2 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 15840569
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 15840569
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 15840569
5 ASSM_TBS /u01/app/oracle/oradata/ORA19C/assm_tbs01.dbf ONLINE 15840569
4 FLM_TBS /u01/app/oracle/oradata/ORA19C/flm_tbs01.dbf ONLINE 15840569
8 OLTP_TBS /u01/app/oracle/oradata/ORA19C/optp_tbs01.dbf ONLINE 15840569
9 INSA_TBS /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf ONLINE 15833767
SYS@ora19c> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
INSA_TBS READ ONLY
FLM_TBS ONLINE
ASSM_TBS ONLINE
OLTP_TBS ONLINE
Data Pump 디렉터리 준비
# 물리적 디렉터리 생성
mkdir -p /home/oracle/data_pump
# 논리적 디렉터리 생성
create directory pump_dir as '/home/oracle/data_pump';
# 논리적 디렉터리 권한 부여
grant read, write on directory pump_dir to hr;
# 논리적 디렉터리 삭제
drop directory pump_dir;
data pump 쓰려면 논리적 디렉터리 있어야 한다.
SYS@ora19c> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
--------------- ------------------------- ------------------------------------------------------------ -------------
SYS PUMP_DIR /home/oracle/data_pump <<- 여기 있다. 0
SYS SDO_DIR_WORK 0
SYS SDO_DIR_ADMIN /u01/app/oracle/product/19.3.0/dbhome_1/md/admin 0
SYS XMLDIR /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/xml 0
SYS XSDDIR /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/xml/schema 0
SYS OPATCH_INST_DIR /u01/app/oracle/product/19.3.0/dbhome_1/OPatch 0
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/19.3.0/dbhome_1/ccr/state 0
SYS ORACLE_BASE /u01/app/oracle 0
SYS ORACLE_HOME /u01/app/oracle/product/19.3.0/dbhome_1 0
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/19.3.0/dbhome_1/ccr/state 0
SYS DATA_PUMP_DIR /u01/app/oracle/admin/ora19c/dpdump/ 0
SYS OPATCH_SCRIPT_DIR /u01/app/oracle/product/19.3.0/dbhome_1/QOpatch 0
SYS OPATCH_LOG_DIR /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/log 0
SYS JAVA$JOX$CUJS$DIRECTORY$ /u01/app/oracle/product/19.3.0/dbhome_1/javavm/admin/ 0
# expdp - Transportable Tablespace
[oracle@ora19c ~]$ expdp userid=system/oracle directory=pump_dir transport_tablespaces=insa_tbs dumpfile=insa_tbs.dmp
...
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/data_pump/insa_tbs.dmp
******************************************************************************
Datafiles required for transportable tablespace INSA_TBS:
/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Jul 30 10:59:02 2026 elapsed 0 00:00:19
# dump file, data file을 target db로 전송
/home/oracle/data_pump/insa_tbs.dmp
/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
[oracle@ora19c ~]$ ping -c 3 192.168.56.175 -- rac1의 ip(private ip). 통신이 되는지 확인하려고 ping을 3번 던짐.
PING 192.168.56.175 (192.168.56.175) 56(84) bytes of data.
64 bytes from 192.168.56.175: icmp_seq=1 ttl=64 time=0.867 ms
64 bytes from 192.168.56.175: icmp_seq=2 ttl=64 time=0.845 ms
64 bytes from 192.168.56.175: icmp_seq=3 ttl=64 time=0.392 ms
--- 192.168.56.175 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2021ms
rtt min/avg/max/mdev = 0.392/0.701/0.867/0.219 ms
/home/oracle/data
-- rac1에서 data 디렉터리 생성
[oracle@rac1 ~]$ mkdir data
# scp(secure copy): ssh 프로토콜 이용해서 로컬에서 원격 서버 간 파일 전송하는 명령어
[oracle@ora19c ~]$ scp /home/oracle/data_pump/insa_tbs.dmp oracle@192.168.56.175:/home/oracle/data
The authenticity of host '192.168.56.175 (192.168.56.175)' can't be established.
ECDSA key fingerprint is SHA256:Vx8XCBkUD70O3CgNW6ZjMAMSaJDIYdyTa05ZZeLCnnA.
ECDSA key fingerprint is MD5:27:62:1f:bf:4e:82:42:62:5b:6c:4b:8a:a8:dd:5b:b1.
Are you sure you want to continue connecting (yes/no)? yes <<- yes 입력
Warning: Permanently added '192.168.56.175' (ECDSA) to the list of known hosts.
oracle@192.168.56.175's password: <<- rac1 password 입력
insa_tbs.dmp 100% 188KB 20.2MB/s 00:00
[oracle@ora19c ~]$ scp /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf oracle@192.168.56.175:/home/oracle/data
oracle@192.168.56.175's password: <<- rac1 password 입력
insa_tbs01.dbf 100% 10MB 47.9MB/s 00:00
/home/oracle/data 여기서 이 경로는 RAC1의 data 디렉터리 경로
# target db 확인(RAC1)
[oracle@rac1 data]$ ls -l
total 10436
-rw-r-----. 1 oracle oinstall 10493952 Jul 29 23:00 insa_tbs01.dbf
-rw-r-----. 1 oracle oinstall 192512 Jul 29 23:00 insa_tbs.dmp
# tablespace read write 변경
SYS@ora19c> alter tablespace insa_tbs read write;
Tablespace altered.
SYS@ora19c> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
INSA_TBS ONLINE
FLM_TBS ONLINE
ASSM_TBS ONLINE
OLTP_TBS ONLINE
# target db(RAC1)
[oracle@rac1 data]$ ls -l
total 10436
-rw-r-----. 1 oracle oinstall 10493952 Jul 29 23:00 insa_tbs01.dbf
-rw-r-----. 1 oracle oinstall 192512 Jul 29 23:00 insa_tbs.dmp
# 타겟(rac1)에서 논리적 디렉터리 생성
SYS@racdb1> create directory data_dir as '/home/oracle/data';
Directory created.
SYS@racdb1> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
-------------------- ------------------------------ ------------------------------------------------------------ -------------
SYS DATA_DIR /home/oracle/data 0
SYS SDO_DIR_WORK 0
SYS SDO_DIR_ADMIN /u01/app/oracle/product/19.0.0/dbhome_1/md/admin 0
SYS XMLDIR /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/xml 0
SYS XSDDIR /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/xml/schema 0
SYS OPATCH_INST_DIR /u01/app/oracle/product/19.0.0/dbhome_1/OPatch 0
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/19.0.0/dbhome_1/ccr/state 0
SYS ORACLE_BASE /u01/app/oracle 0
SYS ORACLE_HOME /u01/app/oracle/product/19.0.0/dbhome_1 0
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/19.0.0/dbhome_1/ccr/state 0
SYS DATA_PUMP_DIR /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/ 0
SYS OPATCH_SCRIPT_DIR /u01/app/oracle/product/19.0.0/dbhome_1/QOpatch 0
SYS OPATCH_LOG_DIR /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log 0
SYS JAVA$JOX$CUJS$DIRECTORY$ /u01/app/oracle/product/19.0.0/dbhome_1/javavm/admin/ 0
SYS@racdb1> select a.file#,b.name tbs_name,a.name file_name,a.status,a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts#=b.ts#;
FILE# TBS_NAME FILE_NAME STATUS CHECKPOINT_CHANGE#
---------- ------------------------------ -------------------------------------------------- ------- ------------------
3 SYSAUX +DATA/RACDB/DATAFILE/sysaux.260.1239202861 ONLINE 3833052
1 SYSTEM +DATA/RACDB/DATAFILE/system.259.1239811659 SYSTEM 3833052
4 UNDOTBS1 +DATA/RACDB/DATAFILE/undotbs1.261.1239202887 ONLINE 3833052
7 USERS +DATA/RACDB/DATAFILE/users.262.1239202887 ONLINE 3833052
5 UNDOTBS2 +DATA/RACDB/DATAFILE/undotbs2.267.1239203295 ONLINE 3833052
2 ASM_TBS +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239877429 ONLINE 3833052
# file system data file -> asm data file 변경
# /home/oracle/data/insa_tbs01.dbf -> +DATA
rman 이용해야 함!
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jul 29 23:10:40 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1236819723)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 920 SYSTEM YES +DATA/RACDB/DATAFILE/system.259.1239811659
2 10 ASM_TBS NO +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239877429
3 780 SYSAUX NO +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 355 UNDOTBS1 YES +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 25 UNDOTBS2 YES +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 5 USERS NO +DATA/RACDB/DATAFILE/users.262.1239202887
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 39 TEMP 32767 +DATA/RACDB/TEMPFILE/temp.266.1239202971
RMAN> convert datafile '/home/oracle/data/insa_tbs01.dbf' format '+DATA';
Starting conversion at target at 30-JUL-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=256 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/data/insa_tbs01.dbf
converted datafile=+DATA/RACDB/DATAFILE/insa_tbs.288.1239976111
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 30-JUL-26
Starting Control File and SPFILE Autobackup at 30-JUL-26
piece handle=+FRA/RACDB/AUTOBACKUP/2026_07_30/s_1239976112.286.1239976113 comment=NONE
Finished Control File and SPFILE Autobackup at 30-JUL-26
RMAN> exit
# Import(impdp)
[oracle@rac1 ~]$ impdp userid=system/oracle directory=data_dir dumpfile=insa_tbs.dmp transport_datafiles='+DATA/RACDB/DATAFILE/insa_tbs.288.1239976111'
SYS@racdb1> select a.file#,b.name tbs_name,a.name file_name,a.status,a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts#=b.ts#;
FILE# TBS_NAME FILE_NAME STATUS CHECKPOINT_CHANGE#
----- -------------- ------------------------------------------------ ---------- ------------------
3 SYSAUX +DATA/RACDB/DATAFILE/sysaux.260.1239202783 ONLINE 3855361
1 SYSTEM +DATA/RACDB/DATAFILE/system.259.1239811659 SYSTEM 3855361
4 UNDOTBS1 +DATA/RACDB/DATAFILE/undotbs1.261.1239202799 ONLINE 3855361
7 USERS +DATA/RACDB/DATAFILE/users.262.1239202799 ONLINE 3855361
5 UNDOTBS2 +DATA/RACDB/DATAFILE/undotbs2.267.1239203033 ONLINE 3855361
2 ASM_TBS +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239820425 ONLINE 7137820
8 INSA_TBS +DATA/RACDB/DATAFILE/insa_tbs.279.1239923529 ONLINE 7152190
SYS@racdb1> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name = 'INSA_EMP';
FILE_NAME
-------------------------------------------------
+DATA/RACDB/DATAFILE/insa_tbs.279.1239923529
SYS@racdb1> select tablespace_name, bytes, blocks from dba_segments where owner = 'HR' and segment_name = 'INSA_EMP_IDX';
TABLESPACE_NAME BYTES BLOCKS
------------------------------ ---------- ----------
INSA_TBS 65536 8
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
ASM_TBS ONLINE
INSA_TBS READ ONLY# insa_tbs테이블스페이스를 read write로 변경
SYS@racdb1> alter tablespace insa_tbs read write;
Tablespace altered.
⭐️⭐️⭐️⭐️⭐️ 진짜 별 다섯 개
이관하려면 ora19c와 rac1의 캐릭터셋이 같아야 함.(서버끼리 character set이 같아야 한다!) 안 그러면 import할 때 오류 발생함.
만약 캐릭터셋이 다르면 TTS/impdp를 쓸 수 없고, SQL*Loader로 처리해야 함:
- 메타정보(테이블 구조 등)는 별도로 추출
- 데이터는 CSV 형식으로 spool 하여 추출
- Direct Path Load 방식 사용(제약조건은 패스하고 로드)
캐릭터셋 확인 방법
SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
ora19c 캐릭터셋이 KO16MSWIN949였어서 안 됐었는데 겨우 AL32UTF8(유니코드)로 바꿨다.
ALTER DATABASE CHARACTER SET AL32UTF8는 기본적으로 "binary superset" 체크를 한다. 새 캐릭터셋이 기존 캐릭터셋의 완전한 상위 집합이 아니면 (즉 코드값 바이너리 순서가 안 맞으면) Oracle이 강제로 막는다.
KO16MSWIN949(EUC 계열, 가변 2바이트) → AL32UTF8(유니코드)은 이 binary superset 조건을 만족 못 해서 일반 ALTER는 100% 막힌다.
해결 방법: INTERNAL_USE 옵션 (Oracle 공식 지원 절차)
Oracle이 실제 마이그레이션에서 쓰라고 제공하는 방법이다. superset 체크를 우회하고 문자 코드값만 그대로 두고 캐릭터셋 이름표만 바꾸는 방식이다.
-- 1) 반드시 먼저 CSSCAN으로 데이터 손실 여부 확인 (생략 금지)
-- (csminst.sql 실행 → csscan 실행 → .txt 로그 확인)
-- 2) restrict 모드로 기동
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER DATABASE OPEN;
-- 3) INTERNAL_USE로 강제 변경 (superset 체크 skip)
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
-- 4) 재기동
SHUTDOWN IMMEDIATE;
STARTUP;
[2차 이관] RAC(racdb1) → ora19c: asm_tbs
# 소스(RAC1)에서 datafile 추가 및 상태 확인
SYS@racdb1> alter system set db_create_file_dest = '+DATA' sid='*'; -- Oracle이 데이터파일을 자동 생성할 기본 저장 위치를 ASM 디스크 그룹 +DATA로 지정하는 명령어
System altered.
SYS@racdb1> alter tablespace asm_tbs add datafile;
Tablespace altered.
SYS@racdb1> select a.file#,b.name tbs_name,a.name file_name,a.status,a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts#=b.ts#;
FILE# TBS_NAME FILE_NAME STATUS CHECKPOINT_CHANGE#
---------- ---------- ------------------------------------------------------------ ------- ------------------
3 SYSAUX +DATA/RACDB/DATAFILE/sysaux.260.1239202861 ONLINE 3833052
1 SYSTEM +DATA/RACDB/DATAFILE/system.259.1239811659 SYSTEM 3833052
4 UNDOTBS1 +DATA/RACDB/DATAFILE/undotbs1.261.1239202887 ONLINE 3833052
7 USERS +DATA/RACDB/DATAFILE/users.262.1239202887 ONLINE 3833052
5 UNDOTBS2 +DATA/RACDB/DATAFILE/undotbs2.267.1239203295 ONLINE 3833052
2 ASM_TBS +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239877429 ONLINE 15901024
9 ASM_TBS +DATA/RACDB/DATAFILE/asm_tbs.289.1239982283 ONLINE 15901037
8 INSA_TBS +DATA/RACDB/DATAFILE/insa_tbs.288.1239976111 ONLINE 15887960
SYS@racdb1> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name = 'EMP_ASM';
FILE_NAME
------------------------------------------------------------
+ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239877429
# READ ONLY 전환
SYS@racdb1> alter tablespace asm_tbs read only;
Tablespace altered.
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
ASM_TBS READ ONLY
INSA_TBS ONLINE
# database platform check
- big endian과 little endian은 컴퓨터가 메모리에 여러 바이트의 데이터를 저장하는 순서의 차이
- big endian: 저장할 때 상위 바이트 즉 큰 쪽을 먼저 저장
- little endian: 저장할 때 하위 바이트 즉 작은 쪽을 먼저 저장
SYS@racdb1> select * from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT CON_ID
----------- ---------------------------------------- -------------- ----------
1 Solaris[tm] OE (32-bit) Big 0
2 Solaris[tm] OE (64-bit) Big 0
7 Microsoft Windows IA (32-bit) Little 0
10 Linux IA (32-bit) Little 0
6 AIX-Based Systems (64-bit) Big 0
3 HP-UX (64-bit) Big 0
5 HP Tru64 UNIX Little 0
4 HP-UX IA (64-bit) Big 0
11 Linux IA (64-bit) Little 0
15 HP Open VMS Little 0
8 Microsoft Windows IA (64-bit) Little 0
9 IBM zSeries Based Linux Big 0
13 Linux x86 64-bit Little 0
16 Apple Mac OS Big 0
12 Microsoft Windows x86 64-bit Little 0
17 Solaris Operating System (x86) Little 0
18 IBM Power Based Linux Big 0
19 HP IA Open VMS Little 0
20 Solaris Operating System (x86-64) Little 0
21 Apple Mac OS (x86-64) Little 0
22 Linux OS (S64) Big 0
# 소스
SYS@racdb1> select platform_id, platform_name from v$database; -- 현재 플랫폼
PLATFORM_ID PLATFORM_NAME
----------- ----------------------------------------
13 Linux x86 64-bit
# 타겟
SYS@ora19c> select platform_id, platform_name from v$database;
PLATFORM_ID PLATFORM_NAME
----------- -----------------------------------------
13 Linux x86 64-bit
같은 platform을 갖고 있으니 endian format은 맞춰줄 필요 없고 파일 형식만 맞춰주면 됨.
# datafile 기준으로 convert
RMAN> convert datafile '+ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239877429' format '/home/oracle/data/asm_tbs01.dbf';
RMAN> convert datafile '+DATA/RACDB/DATAFILE/asm_tbs.289.1239982283' format '/home/oracle/data/asm_tbs02.dbf';
만약 endian format이 맞지 않는다면
# 소스서버에서 파일을 타겟 서버로 이동하기 전에 변환(예 소스: AIX-Based Systems (64-bit) Big -> Linux x86 64-bit Little
# convert datafile '+ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239877429' to platform 'Linux x86 64-bit' format '/home/oracle/data/asm_tbs01.dbf';
# 소스서버에서 파일을 타겟 서버로 이동한 후에 변환(예 소스: AIX-Based Systems (64-bit) Big -> Linux x86 64-bit Little
# convert datafile '/home/oracle/data/asm_tbs.256.1239877429' from platform 'Linux x86 64-bit' format '/home/oracle/data/asm_tbs01.dbf';
# tablespace 기준으로 convert
convert tablespace asm_tbs format '/home/oracle/data/%N_%f.dbf' => ASM_TBS_2.dbf, ASM_TBS_9.dbf 이런 식으로 나올 것
%N: 테이블스페이스 이름
%f: 절대파일 번호
# Export(expdp)
[oracle@rac1 ~]$ expdp userid=system/oracle directory=data_dir transport_tablespaces=asm_tbs dumpfile=asm_tbs.dmp
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
ASM_TBS READ ONLY
INSA_TBS ONLINE
# READ WRITE로 변경 후 파일 전송(scp)
SYS@racdb1> alter tablespace asm_tbs read write;
[oracle@rac1 data]$ ls
asm_tbs01.dbf asm_tbs02.dbf asm_tbs.dmp export.log insa_tbs01.dbf insa_tbs.dmp
[oracle@rac1 data]$ scp *.dbf oracle@192.168.56.110:/home/oracle/
[oracle@rac1 data]$ scp *.dmp oracle@192.168.56.110:/home/oracle/data_pump
# 타겟(ora19c)에서 Import(impdp)
[oracle@ora19c ~]$ ls asm*
asm_tbs01.dbf asm_tbs02.dbf
[oracle@ora19c ~]$ ls data_pump
asm_tbs.dmp hr_sal_emp_p1.pump hr_sal_emp.pump insa_tbs.dmp sql_emp.sql
[oracle@ora19c ~]$ impdp userid=system/oracle directory=pump_dir dumpfile=asm_tbs.dmp transport_datafiles='/home/oracle/asm_tbs01.dbf','/home/oracle/asm_tbs02.dbf'
SYS@ora19c> select a.file#,b.name tbs_name,a.name file_name,a.status,a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts#=b.ts#;
FILE# TBS_NAME FILE_NAME STATUS CHECKPOINT_CHANGE#
----- ---------- ------------------------------------------------------------ ------------ ------------------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 15915191
2 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 15915191
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 15915191
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 15915191
5 ASSM_TBS /u01/app/oracle/oradata/ORA19C/assm_tbs01.dbf ONLINE 15915191
4 FLM_TBS /u01/app/oracle/oradata/ORA19C/flm_tbs01.dbf ONLINE 15915191
8 OLTP_TBS /u01/app/oracle/oradata/ORA19C/optp_tbs01.dbf ONLINE 15915191
9 INSA_TBS /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf ONLINE 15915191
10 ASM_TBS /home/oracle/asm_tbs02.dbf ONLINE 15924649
11 ASM_TBS /home/oracle/asm_tbs01.dbf ONLINE 15924649
SYS@ora19c> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name = 'EMP_ASM';
FILE_NAME
--------------------------------------------
/home/oracle/asm_tbs01.dbf
SYS@ora19c> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
INSA_TBS ONLINE
FLM_TBS ONLINE
ASSM_TBS ONLINE
OLTP_TBS ONLINE
ASM_TBS READ ONLY
# READ WRITE로 전환
SYS@ora19c> alter tablespace asm_tbs read write;
Tablespace altered.
더보기
* 데이터 이관할 때 체크해야 할 것
1. 캐릭터셋
2. 데이터 플랫폼
'강남 아이티윌 오라클DBA과정 94기 > RAC' 카테고리의 다른 글
| [오라클 DBA 과정] 93일차 RAC(10) (2026-07-31) (0) | 2026.07.31 |
|---|---|
| [오라클 DBA 과정] 91일차 RAC(8) (2026-07-29) (0) | 2026.07.29 |
| [오라클 DBA 과정] 90일차 RAC(7) (2026-07-28) (0) | 2026.07.29 |
| [오라클 DBA 과정] 89일차 RAC(6) (2026-07-27) (0) | 2026.07.27 |
| [오라클 DBA 과정] 88일차 RAC(5) (2026-07-24) (0) | 2026.07.25 |
