close
프로필 배경
프로필 로고

JH 개인 블로그

DataBase/Oracle · 2022. 7. 6. fullscreen 넓게보기

[Oracle - TTS 데이터이관] part1

Transportable Tablespace는 오라클에서 오라클 데이터베이스로 데이터를 이동할때 가장 빠른방법이라고 합니다. 데이터펌프나 Export, Import 유틸리티를 사용할때는 그냥 insert작업을 하는 것과 비슷하다고 한다면 Transport Tablespace는 Datapump를 사용하여 메타데이터 즉 구조만 추출하고 데이터파일 자체를 이동시켜 migration 작업을 합니다. 
Endian의 정보가 다르더라도 RMAN을 통해서 Converting하여 데이터파일의 형식을 변경할 수 있어 모든 OS에서 호환이 가능합니다. Endian에 대한 정보는 v$transportable_platform 뷰를 통해서 확인할 수 있습니다.

 

■ 테스트 환경

Hostname
IP
DB version
Platform
jh-asis
10.0.0.67
Oracle Database 19c EE High Perf 19.15.0.0.0
Oracle Cloud
jh-tobe
10.0.0.122
Oracle Database 19c EE High Perf 19.15.0.0.0
Oracle Cloud

※ 참고 Oracle Cloud에서 SaaS 상품인 DBCS 사용시 자동 TDE를 이용. 비활성화 불가하며, TDE사용시 TTS이관 불가능하다네요..

 

URL : https://docs.oracle.com/en/database/oracle/oracle-database/12.2/rcmrf/TRANSPORT-TABLESPACE.html#GUID-3E1F9103-17E7-40F4-A42C-DED0FB14362D

 

Database Backup and Recovery Reference

 

docs.oracle.com

 

 

■ 초기 데이터 insert (AS-IS)
SQL> create tablespace jh datafile '+DATA' size 5m;

Tablespace created.

SQL> alter tablespace jh add datafile '+DATA' size 5m;

Tablespace altered.

SQL> create tablespace kjh datafile '+DATA' size 10m;

Tablespace created.



SQL> create user jh identified by jh default tablespace JH;

User created.

SQL> create user kjh identified by kjh default tablespace kjh;

User created.



SQL> grant connect, resource to jh;

Grant succeeded.

SQL> grant connect, resource to kjh;

Grant succeeded.



SQL> create table jh.test1(name varchar(10));

Table created.

SQL> create table kjh.test2(name varchar(10));

Table created.



SQL> insert into jh.test1 values ('first data');

1 row created.

SQL> insert into kjh.test2 values ('first data');

1 row created.

SQL> commit;

Commit complete.



SQL> select * from jh.test1;

NAME
----------
first data


SQL> select * from kjh.test2;

NAME
----------
first data

 

■ Violation check
이관하려는 tablespace에 속해 이쓴 table 및 index가 다른 영역의 tablespace에 저장되었는지 확인하는 절차입니다.
no rows selected 나와야 정상. no rows selected 가 아닌 정보가 나온다면 Table 및 Index 를 이관하려는 Tablespace 로 이동 하거나 해당 Tablespace 도 이관합니다.
SQL> exec dbms_tts.transport_set_check('JH,KJH',TRUE);

PL/SQL procedure successfully completed.


SQL> select * from transport_set_violations;

no rows selected

 

■ rman tablespace 백업
cdb/pdb구조 이기때문에 리스너에 pdb 정보를 입력하고 리스너를 통해 직접 접속
[oracle@jhas-is admin]$ vi tnsnames.ora
pdb=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.67)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=jhasis_pdb1.subnet02161018.vcn02161018.oraclevcn.com)))



[oracle@jhas-is admin]$ rman target sys/KJH123##@pdb
run {
BACKUP AS COPY INCREMENTAL LEVEL 0 tag 'full_backup' tablespace jh, kjh format '/u01/app/rman/file%f%U';
}

Starting backup at 04-JUL-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=929 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00020 name=+DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/kjh.286.1108874551
output file name=/u01/app/rman/filedata_D-JHASIS_I-3683630618_TS-KJH_FNO-20_2e11nj49data_D-JHASIS_I-3683630618_TS-KJH_FNO-20_2e11nj49.bkp tag=FULL_BACKUP RECID=4 STAMP=1109118090
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=+DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/jh.279.1108874515
output file name=/u01/app/rman/filedata_D-JHASIS_I-3683630618_TS-JH_FNO-18_2f11nj4bdata_D-JHASIS_I-3683630618_TS-JH_FNO-18_2f11nj4b.bkp tag=FULL_BACKUP RECID=5 STAMP=1109118091
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=+DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/jh.276.1108874537
output file name=/u01/app/rman/filedata_D-JHASIS_I-3683630618_TS-JH_FNO-19_2g11nj4cdata_D-JHASIS_I-3683630618_TS-JH_FNO-19_2g11nj4c.bkp tag=FULL_BACKUP RECID=6 STAMP=1109118092
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-JUL-22



[oracle@jhas-is rman]$ ll
total 20504
-rw-r----- 1 oracle asmadmin  5251072 Jul  4 00:21 filedata_D-JHASIS_I-3683630618_TS-JH_FNO-18_2f11nj4bdata_D-JHASIS_I-3683630618_TS-JH_FNO-18_2f11nj4b.bkp
-rw-r----- 1 oracle asmadmin  5251072 Jul  4 00:21 filedata_D-JHASIS_I-3683630618_TS-JH_FNO-19_2g11nj4cdata_D-JHASIS_I-3683630618_TS-JH_FNO-19_2g11nj4c.bkp
-rw-r----- 1 oracle asmadmin 10493952 Jul  4 00:21 filedata_D-JHASIS_I-3683630618_TS-KJH_FNO-20_2e11nj49data_D-JHASIS_I-3683630618_TS-KJH_FNO-20_2e11nj49.bkp

 

■ Full backup 파일 전송 
public key 이용 scp로 파일 전송
[root@jhas-is rman]# scp -rpi /home/opc/oci_DB_rsa_Xshell /u01/app/rman/ opc@10.0.0.122:/home/opc
\The authenticity of host '10.0.0.122 (10.0.0.122)' can't be established.
ECDSA key fingerprint is SHA256:o1iyKt0bswESLwfGyO/cGeWdIrUZ+wEDgYIi0GWcTRE.
ECDSA key fingerprint is MD5:50:26:7e:45:59:10:02:cc:50:72:f6:e3:71:bc:62:9a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.122' (ECDSA) to the list of known hosts.
filedata_D-JHASIS_I-3683630618_TS-KJH_FNO-20_2e11nj49data_ 100%   10MB 245.6MB/s   00:00    
filedata_D-JHASIS_I-3683630618_TS-JH_FNO-19_2g11nj4cdata_D 100% 5128KB 225.2MB/s   00:00    
filedata_D-JHASIS_I-3683630618_TS-JH_FNO-18_2f11nj4bdata_D 100% 5128KB 222.0MB/s   00:00  


<TO-BE>
[root@jh-tobe opc]# mv /home/opc/rman/ /home/grid


## 소유권 변경
[root@jh-tobe rman]# chown -R grid.oinstall /home/grid/rman

 

■ TO-BE 유저 및 테이블 스페이스 생성
사전에 AS-IS와 같은 유저와 default tablespace 생성 => get ddl이용, TO-BE에 유저 생성시 default tablespace는 기존과 다르게 생성.
## as-is
SQL> set long 33333
SQL> select dbms_metadata.get_ddl('USER','JH') from dual;

DBMS_METADATA.GET_DDL('USER','JH')
--------------------------------------------------------------------------------

   CREATE USER "JH" IDENTIFIED BY VALUES 'S:9C90DCE54C310EDCFB99F9F37254479EBBB6
3C638FCFEC9BCD2D8DA038C6;T:642F2A2254F8986074EDB961D03176E469C77A27DBCE1263291A4
F9EA68844859A8C7E81A6CFA1CC2AF487A002F9AECE37DF4D6354C67159B363F6C49EC1F8213DF81
0CADE082EFBB9FAC887808D3AD9'
      DEFAULT TABLESPACE "JH"
      TEMPORARY TABLESPACE "TEMP"


SQL> select dbms_metadata.get_ddl('USER','KJH') from dual;

DBMS_METADATA.GET_DDL('USER','KJH')
--------------------------------------------------------------------------------

   CREATE USER "KJH" IDENTIFIED BY VALUES 'S:EA8E48061C41ABC150EA54C51B87D33F911
88609786EFB7FE83E63CFD0A5;T:85585D6DE6736CBCC4A2F536FEB11E32CDA49E52D65D68176EDC
F85B3C949BBC5E289D528E446759CFC9FC6388ED6A3F91D570B7101432C8E61B6BA40DB4F3B60D4C
CB536E8D2F129FAB2092925A3402'
      DEFAULT TABLESPACE "KJH"
      TEMPORARY TABLESPACE "TEMP"



## 권한 조회
SQL> select grantee, granted_role from dba_role_privs where grantee in('JH','KJH');

GRANTEE           GRANTED_ROLE
------------------------- -------------------------
KJH              RESOURCE
JH              DBA
JH              CONNECT
KJH              CONNECT
JH              RESOURCE
KJH              DBA

6 rows selected.



## to-be
SQL> CREATE USER "JH" IDENTIFIED BY VALUES 'S:9C90DCE54C310EDCFB99F9F37254479EBBB63C638FCFEC9BCD2D8DA038C6;T:642F2A2254F8986074EDB961D03176E469C77A27DBCE1263291A4F9EA68844859A8C7E81A6CFA1CC2AF487A002F9AECE37DF4D6354C67159B363F6C49EC1F8213DF810CADE082EFBB9FAC887808D3AD9' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

User created.


SQL> CREATE USER "KJH" IDENTIFIED BY VALUES 'S:EA8E48061C41ABC150EA54C51B87D33F91188609786EFB7FE83E63CFD0A5;T:85585D6DE6736CBCC4A2F536FEB11E32CDA49E52D65D68176EDCF85B3C949BBC5E289D528E446759CFC9FC6388ED6A3F91D570B7101432C8E61B6BA40DB4F3B60D4CCB536E8D2F129FAB2092925A3402' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

User created.



## 권한 부여
SQL> grant dba to jh, kjh;

Grant succeeded.

 

 

■ TO-BE 백업본 copy수행 

## ASM이기 떄문에 DATA diskgroup으로 백업본을 copy해줍니다.
[grid@jh-tobe ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304    262144   252176                0          252176              0             Y  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304    262144   257952                0          257952              0             N  RECO/



## cp <backup본 파일 경로> <옮길 경로>   => 하나씩 옮겨야합니다..

[grid@jh-tobe ~]$ asmcmd
ASMCMD> cd +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE


ASMCMD> cp /home/grid/rman/filedata_D-JHASIS_I-3683630618_TS-JH_FNO-18_2f11nj4bdata_D-JHASIS_I-3683630618_TS-JH_FNO-18_2f11nj4b.bkp  +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh01.dbf
copying /home/grid/rman/filedata_D-JHASIS_I-3683630618_TS-JH_FNO-18_2f11nj4bdata_D-JHASIS_I-3683630618_TS-JH_FNO-18_2f11nj4b.bkp -> +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh01.dbf

ASMCMD> cp /home/grid/rman/filedata_D-JHASIS_I-3683630618_TS-JH_FNO-19_2g11nj4cdata_D-JHASIS_I-3683630618_TS-JH_FNO-19_2g11nj4c.bkp  +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh02.dbf
copying /home/grid/rman/filedata_D-JHASIS_I-3683630618_TS-JH_FNO-19_2g11nj4cdata_D-JHASIS_I-3683630618_TS-JH_FNO-19_2g11nj4c.bkp -> +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh02.dbf

ASMCMD> cp /home/grid/rman/filedata_D-JHASIS_I-3683630618_TS-KJH_FNO-20_2e11nj49data_D-JHASIS_I-3683630618_TS-KJH_FNO-20_2e11nj49.bkp  +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/kjh01.dbf
copying /home/grid/rman/filedata_D-JHASIS_I-3683630618_TS-KJH_FNO-20_2e11nj49data_D-JHASIS_I-3683630618_TS-KJH_FNO-20_2e11nj49.bkp -> +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh02.dbf

 

이후 작업은 part2에서 진행하겠습니다. 

저작자표시

'DataBase > Oracle' 카테고리의 다른 글

[Oracle - ACFS (ASM Cluster File System) 생성]  (0) 2022.07.30
[Oracle - TTS 데이터이관] part2  (0) 2022.07.06
[Oracle - Shrink Space]  (0) 2022.07.06
[Oracle - Statspack 그래프 출력 use Python] part 3  (0) 2022.03.10
[Oracle - Statspack manual / Snapshot설정] part 2  (0) 2022.03.10
DataBase/Oracle 관련 글
더 보기

티스토리툴바