close
프로필 배경
프로필 로고

JH 개인 블로그

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

[Oracle - TTS 데이터이관] part2

part1 :https://jhdatabase.tistory.com/90

 

[Oracle - TTS 데이터이관] part1

Transportable Tablespace는 오라클에서 오라클 데이터베이스로 데이터를 이동할때 가장 빠른방법이라고 합니다. 데이터펌프나 Export, Import 유틸리티를 사용할때는 그냥 insert작업을 하는 것과 비슷하

jhdatabase.tistory.com

 

■ AS-IS incremental 백업 테스트를 위해 데이터 insert 

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

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

SQL> commit;


SQL> select * from jh.test1;

NAME
----------
incre
before


SQL> select * from kjh.test2;

NAME
----------
incre
before

 

■ AS-IS Incremental 백업 및 백업본 to-be로 전달
[oracle@jhas-is ~]$ rman target sys/GoodusData070##@pdb

RMAN> run {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'full_backup' format '/u01/app/rman/INCRE%U.inc' tablespace JH, KJH;
}

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=480 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00020 name=+DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/kjh.286.1108874551
input datafile file number=00018 name=+DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/jh.279.1108874515
input datafile file number=00019 name=+DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/jh.276.1108874537
channel ORA_DISK_1: starting piece 1 at 04-JUL-22
channel ORA_DISK_1: finished piece 1 at 04-JUL-22
piece handle=/u01/app/rman/INCRE2h11ocm9_81_1_1.inc tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-JUL-22


[root@jhas-is rman]# scp -rpi /home/opc/oci_DB_rsa_Xshell /u01/app/rman/INCRE2h11ocm9_81_1_1.inc opc@10.0.0.122:/home/opc
INCRE2h11ocm9_81_1_1.inc                                   100%  104KB  42.6MB/s   00:00

 

■ TO-BE Incremetal backup 본 recovery 수행
To-Be 에 첫번째 증분 백업 본을 Recovery 하기 위한 절차입니다.
[root@jh-tobe ~]# mv /home/opc/INCRE2h11ocm9_81_1_1.inc  /home/grid/rman/
[root@jh-tobe ~]# chown -R grid.oinstall /home/grid/rman/

ASMCMD>  cp /home/grid/rman/INCRE2h11ocm9_81_1_1.inc +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/incre01.dbf
copying /home/grid/rman/INCRE2h11ocm9_81_1_1.inc -> +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/incre01.dbf


<AS-IS>사전에  테이블스페이스 file_id 확인
SQL> select file_id, tablespace_name from dba_data_files;

   FILE_ID TABLESPACE_NAME
---------- ------------------------------
     8 SYSTEM
     9 SYSAUX
    10 UNDOTBS1
    12 USERS
    13 KIM
    18 JH
    19 JH
    20 KJH



## dfnumber 는 As-Is file_id 지정, To-Be 파일 경로 지정, Incremental 백업본 파일 위치 지정합니다.

SQL>
set serveroutput on ;
DECLARE
d  varchar2(512);
h  varchar2(512) ;
t  varchar2(30) ;
b1 boolean ;
b2 boolean ;
DONE  boolean ;
FAILOVER boolean ;
BEGIN
d := sys.dbms_backup_restore.deviceAllocate;
DBMS_OUTPUT.PUT_LINE(d);
sys.dbms_backup_restore.applysetdatafile(check_logical=>false, cleanup=>false) ;
sys.dbms_backup_restore.applyDatafileTo( dfnumber=>18    , toname => '+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh01.dbf' , fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.applyDatafileTo( dfnumber=>19   , toname => '+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh02.dbf' , fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.applyDatafileTo( dfnumber=>20    , toname => '+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/kjh01.dbf' , fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.restoreSetPiece( handle=>'+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/incre01.dbf', tag=>null,fromdisk=>true,recid=>0,stamp=>0) ;
sys.dbms_backup_restore.restoreBackupPiece( done=>DONE, params=>null, outhandle=>h, outtag=>t, FAILOVER=>FAILOVER);
sys.dbms_backup_restore.restoreCancel(TRUE);
sys.dbms_backup_restore.deviceDeallocate;
END;
/


PL/SQL procedure successfully completed.

 

 

■ AS-IS Incremetal 백업 테스트를 위한 마지막 데이터 입력  

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

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

SQL> commit;


SQL> select * from jh.test1;

NAME
----------
first data
incre
Last data


SQL> select * from kjh.test2;

NAME
----------
first data
incre
Last data

 

■ AS-IS Tablespace Read Only 모드 변경  //DownTime start
앞단계에서 최종데이터라고 가정하였으므로 Read Only 모드로 변경합니다. 
이 시점이 Down Time 시작입니다.
데이터 변경이 많은 시스템에서는 증분 백업을 여러 번 할수 있습니다.
SQL> alter tablespace jh read only;

SQL> alter tablespace kjh read only;

Tablespace altered.

 

 

■ AS-IS 최종 Incremental 백업 & 백업본 TO-BE로 전송 

[root@jhas-is ~]# mkdir /u01/app/last
[root@jhas-is ~]# chown oracle.oinstall /u01/app/last/

[oracle@jhas-is ~]$ rman target sys/GoodusData070##@pdb

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jul 4 11:00:24 2022
Version 19.15.0.0.0

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

connected to target database: JHASIS:JHASIS_PDB1 (DBID=194417655)

RMAN> run {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'full_backup' format '/u01/app/last/INCRE%U.inc' tablespace jh, kjh;
}

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=18 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00020 name=+DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/kjh.286.1108874551
input datafile file number=00018 name=+DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/jh.279.1108874515
input datafile file number=00019 name=+DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/jh.276.1108874537
channel ORA_DISK_1: starting piece 1 at 04-JUL-22
channel ORA_DISK_1: finished piece 1 at 04-JUL-22
piece handle=/u01/app/last/INCRE2q11oolq_90_1_1.inc tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-JUL-22


[root@jhas-is ~]# scp -rpi /home/opc/oci_DB_rsa_Xshell /u01/app/last/ opc@10.0.0.122:/home/opc
INCRE2q11oolq_90_1_1.inc                                   100%  104KB  50.8MB/s   00:00

 

 

■ To-BE 마지막 Incremental backup본 Recovery  

[root@jh-tobe ~]# mv /home/opc/last/ /home/grid/
[root@jh-tobe ~]# chown -R grid.oinstall /home/grid/last


ASMCMD> cp /home/grid/last/INCRE2q11oolq_90_1_1.inc +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/last01.dbf
copying /home/grid/last/INCRE2q11oolq_90_1_1.inc -> +DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/last01.dbf


SQL> set serveroutput on ;
DECLARE
d  varchar2(512);
h  varchar2(512) ;
t  varchar2(30) ;
b1 boolean ;
b2 boolean ;
DONE  boolean ;
FAILOVER boolean ;
BEGIN
d := sys.dbms_backup_restore.deviceAllocate;
DBMS_OUTPUT.PUT_LINE(d);
sys.dbms_backup_restore.applysetdatafile(check_logical=>false, cleanup=>false) ;
sys.dbms_backup_restore.applyDatafileTo( dfnumber=>18    , toname => '+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh01.dbf' , fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.applyDatafileTo( dfnumber=>19   , toname => '+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh02.dbf' , fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.applyDatafileTo( dfnumber=>20    , toname => '+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/kjh01.dbf' , fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.restoreSetPiece( handle=>'+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/last01.dbf', tag=>null,fromdisk=>true,recid=>0,stamp=>0) ;
sys.dbms_backup_restore.restoreBackupPiece( done=>DONE, params=>null, outhandle=>h, outtag=>t, FAILOVER=>FAILOVER);
sys.dbms_backup_restore.restoreCancel(TRUE);
sys.dbms_backup_restore.deviceDeallocate;
END;
/

PL/SQL procedure successfully completed.

 

 

■ AS-IS TTS Tablespace export & dump파일 전송 

[root@jhas-is ~]# mkdir /u01/app/datapump
[root@jhas-is ~]# chown oracle.oinstall /u01/app/datapump/

SQL> create or replace directory datapump as '/u01/app/datapump';

Directory created.

SQL> grant read, write on directory datapump to system;

Grant succeeded.


[oracle@jhas-is ~]$ expdp \'sys/GoodusData070##@PDB as sysdba\' dumpfile=tablespace.dmp logfile=exp.log directory=datapump transport_tablespaces=JH,KJH

Export: Release 19.0.0.0.0 - Production on Mon Jul 4 11:23:09 2022
Version 19.15.0.0.0

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

Connected to: Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "sys/********@PDB AS SYSDBA" dumpfile=tablespace.dmp logfile=exp.log directory=datapump transport_tablespaces=JH,KJH
ORA-39396: Warning: exporting encrypted data using transportable option without password

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/datapump/tablespace.dmp
******************************************************************************
Datafiles required for transportable tablespace JH:
  +DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/jh.276.1108874537
  +DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/jh.279.1108874515
Datafiles required for transportable tablespace KJH:
  +DATA/JHASIS_ICN1BK/E28C2FC907C15B62E0534300000A1AC6/DATAFILE/kjh.286.1108874551
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" completed with 1 error(s) at Mon Jul 4 11:23:36 2022 elapsed 0 00:00:24



[root@jhas-is datapump]# scp -rpi /home/opc/oci_DB_rsa_Xshell /u01/app/datapump opc@10.0.0.122:/home/opc
exp.log                                                    100% 1606     6.1MB/s   00:00    
tablespace.dmp                                             100%  180KB  68.6MB/s   00:00

 

 

■ TO-BE TTS Tablespace Import 

[root@jh-tobe ~]# mkdir /u01/app/datapump
[root@jh-tobe ~]# chown oracle.oinstall /u01/app/datapump/


SQL> create or replace directory datapump as '/u01/app/datapump';

SQL> grant read, write on directory datapump to system;


[root@jh-tobe app]# mv /home/opc/datapump/* /u01/app/datapump
[root@jh-tobe app]# chown -R oracle.oinstall /u01/app/datapump/


[oracle@jh-tobe ~]$ impdp \'sys/GoodusData070##@PDB as sysdba\' dumpfile=tablespace.dmp logfile=imp.log directory=datapump transport_datafiles='+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh01.dbf','+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh02.dbf','+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/kjh01.dbf '

Import: Release 19.0.0.0.0 - Production on Mon Jul 4 11:32:37 2022
Version 19.15.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 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "sys/********@PDB AS SYSDBA" dumpfile=tablespace.dmp logfile=imp.log directory=datapump transport_datafiles=+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh01.dbf,+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/jh02.dbf,+DATA/JHTOBE_ICN1R7/E2A4287149473C4CE0537A00000A5062/DATAFILE/kjh01.dbf  
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
...(중략)
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 4 11:34:14 2022 elapsed 0 00:00:16

 

 

■ TO-BE default tablespace 수정 read write 

SQL> alter user jh default tablespace JH; 

SQL> alter user kjh default tablespace KJH;

SQL> alter tablespace jh read write;

SQL> alter tablespace kjh read write; 
Tablespace altered.


SQL> select * from jh.test1;

NAME
----------
first data
incre
Last data


SQL> select * from kjh.test2;

NAME
----------
first data
incre
Last data

 

 

참고 

https://blog.goodusdata.com/76

 

[굿어스데이터] DB Tech Note [32회] TTS & Block Recovery

[굿어스데이터_GoodusData] TTS & Block Recovery [목차] 1) Transportable Tablespace 정의 2) oerr12700diga.sql ORA-600 [12700] 3) Block Brower and Editor (BBED) 4) RMAN Block Recovery #굿어스데이터 #G..

blog.goodusdata.com

저작자표시

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

[Oracle - 리눅스 서버에서 RDS 접속 방법] use. oracle client  (7) 2023.05.25
[Oracle - ACFS (ASM Cluster File System) 생성]  (0) 2022.07.30
[Oracle - TTS 데이터이관] part1  (1) 2022.07.06
[Oracle - Shrink Space]  (0) 2022.07.06
[Oracle - Statspack 그래프 출력 use Python] part 3  (0) 2022.03.10
DataBase/Oracle 관련 글
더 보기

티스토리툴바