오라클 19c begin (핫) 백업 시 expdp 백업 가능여부

OS환경 : Oracle Linux 7.5 (64bit)

DB 환경 : Oracle Database 19.3.0.0

방법 : 오라클 19c begin (핫) 백업 시 expdp 백업 가능여부

begin backup (hot 백업) 시 expdp (datapump) 로 동시에 백업이 가능한지 여부를 테스트함

샘플 데이터 삽입용 유저 생성

SQL> create user expdpman identified by expdpman account unlock default tablespace users quota unlimited on users;
 
User created.
 
SQL> grant resource,connect to expdpman;
 
Grant succeeded.

샘플 데이터 생성

SQL> conn expdpman/expdpman
Connected.
SQL> 
CREATE TABLE EXPTEST(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, 
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), 
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30), COLJ VARCHAR2(30));
 
Table created.
 
SQL>
set serveroutput on;
DECLARE
TYPE tbl_ins IS TABLE OF EXPTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=999;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII';
   w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA';
END LOOP;
   FORALL i in 1..1000000 INSERT INTO EXPTEST VALUES w_ins(i);
   COMMIT;
END;
/
 
PL/SQL procedure successfully completed.

테이블 스페이스 목록 확인

SQL> 
set lines 200 pages 1000
col file_name for a70
select tablespace_name, file_name , bytes/1024/1024 mb from dba_data_files;
 
TABLESPACE_NAME            FILE_NAME                                      MB
------------------------------ ---------------------------------------------------------------------- ----------
SYSTEM                   /app/oracle/oradata/ORCL19/system01.dbf                         700
SYSAUX                   /app/oracle/oradata/ORCL19/sysaux01.dbf                        1060
UNDOTBS1               /app/oracle/oradata/ORCL19/undotbs01.dbf                    11264
USERS                   /app/oracle/oradata/ORCL19/users01.dbf                        177.5
NEWTS                   /app/oracle/oradata/ORCL19/newts01.dbf                         100

expdp용 디렉토리 생성

SQL> create directory expdpdir as '/home/oracle/'
 
Directory created.
 
SQL> grant read, write on directory expdpdir to public;
 
Grant succeeded.

begin backup 실행

SQL> alter tablespace system begin backup; 
 
Tablespace altered.
 
SQL> alter tablespace sysaux begin backup;
 
Tablespace altered.
 
SQL> alter tablespace users begin backup;
 
Tablespace altered.
 
SQL> alter tablespace newts begin backup;
 
Tablespace altered.

cp 명령으로 hot 백업 실행

$ mkdir -p /app/oracle/hot
$ cd /app/oracle/oradata/ORCL19/
$ cp -av system01.dbf sysaux01.dbf users01.dbf newts01.dbf /app/oracle/hot/
??system01.dbf?? -> ??/app/oracle/hot/system01.dbf??
??sysaux01.dbf?? -> ??/app/oracle/hot/sysaux01.dbf??
??users01.dbf?? -> ??/app/oracle/hot/users01.dbf??
??newts01.dbf?? -> ??/app/oracle/hot/newts01.dbf??

동시에 begin backup 종료하지 않고 expdp 실행

$ expdp system/oracle directory=expdpdir tablespaces=users
 
Export: Release 19.0.0.0.0 - Production on Fri Nov 27 21:34:38 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/****** directory=expdpdir tablespaces=users 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "EXPDPMAN"."EXPTEST"                        152.4 MB 1000000 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
  /home/oracle/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Fri Nov 27 21:35:07 2020 elapsed 0 00:00:28

정상적으로 수행됨

end backup 실행

SQL> alter tablespace system end backup; 
 
Tablespace altered.
 
SQL> alter tablespace sysaux end backup;
 
Tablespace altered.
 
SQL> alter tablespace users end backup;
 
Tablespace altered.
 
SQL> alter tablespace newts end backup;
 
Tablespace altered.

결론 : 핫 백업 시에도 expdp(데이터펌프) 백업이 동시에 받아짐

하지만 실제 운영시스템에선 과도한 아카이브로그가 발생할 수 있기때문에 

두 백업의 시간대를 다르게해서 백업하는 것을 권장함

참조 :