1. 실습 환경
<소스>
-> 특정 테이블이 DROP TABLE PURGE가 된 DB
OS : Oracle Linux Server 7.9 (Linux rdb01d
5.4.17-2102.201.3.el7uek.x86_64)
DB : Oracle Database 19c Enterprise Edition
Release 19.0.0.0.0 - Production (Version 19.27.0.0.0)
RAC1 :
ol7ora19r1(192.168.0.21) : ORA19R1
RAC2 : ol7ora19r2(192.168.0.22) :
ORA19R2
<타켓> -> 특정 테이블이 복구된
DB
OS
: Oracle Linux Server 7.9 (Linux rdb01d 5.4.17-2102.201.3.el7uek.x86_64)
DB :
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production (Version
19.26.0.0.0)
SINGLE : tdb01t(192.168.0.51) : ORA19R 로 복구
2. 소스에서 신규 유저 및 테이블
생성
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 -
Production on Wed Jun 11 21:25:46 2025
Version
19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.27.0.0.0
[2025-06-14:19:48:49][ol7ora19r1]<SYS@ORA19R1>
--USER 및
TABLESPACE 생성
DROP USER TUNER CASCADE;
CREATE USER TUNER IDENTIFIED BY
"oracle";
GRANT RESOURCE, DBA, CONNECT TO TUNER;
ALTER USER TUNER ACCOUNT
UNLOCK;
DROP TABLESPACE TUNER_DATA1 INCLUDING
CONTENTS AND DATAFILES;
CREATE TABLESPACE TUNER_DATA1
DATAFILE
'+DATA1_NEW' SIZE 100M
AUTOEXTEND ON NEXT 512M MAXSIZE
UNLIMITED
;
DROP TABLESPACE TUNER_IDX1 INCLUDING
CONTENTS AND DATAFILES;
CREATE TABLESPACE TUNER_IDX1
DATAFILE '+DATA1_NEW'
SIZE 100M
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
;
DROP TABLESPACE TUNER_TEMP INCLUDING
CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE TUNER_TEMP
TEMPFILE
'+DATA1_NEW' SIZE 64M
AUTOEXTEND ON NEXT 100M MAXSIZE
UNLIMITED
;
ALTER USER TUNER DEFAULT TABLESPACE
TUNER_DATA1;
ALTER USER TUNER TEMPORARY TABLESPACE
TUNER_TEMP;
--테이블
생성
DROP TABLE TUNER.TB_CUST PURGE;
CREATE TABLE
TUNER.TB_CUST
(
CUST_NO VARCHAR2(10) NOT NULL
, CUST_ID
VARCHAR2(20) NOT NULL
, CUST_NM VARCHAR2(50) NOT NULL
, BRTHDY
VARCHAR2(8)
, SEX_CD VARCHAR2(6) NOT NULL
, JOIN_DT VARCHAR2(14) NOT
NULL
, CUST_STS_CD VARCHAR2(6) NOT NULL
, INPUT_ID VARCHAR2(20) NOT
NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT
VARCHAR2(14)
)
TABLESPACE TUNER_DATA1;
COMMENT ON COLUMN TUNER.TB_CUST.CUST_NO
IS '고객번호';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_ID IS '고객아이디';
COMMENT ON
COLUMN TUNER.TB_CUST.CUST_NM IS '고객명';
COMMENT ON COLUMN TUNER.TB_CUST.BRTHDY
IS '생년월일';
COMMENT ON COLUMN TUNER.TB_CUST.SEX_CD IS '성별코드';
COMMENT ON
COLUMN TUNER.TB_CUST.JOIN_DT IS '가입일시';
COMMENT ON COLUMN
TUNER.TB_CUST.CUST_STS_CD IS '고객상태코드';
COMMENT ON COLUMN
TUNER.TB_CUST.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN TUNER.TB_CUST.INPUT_DT
IS '입력일시';
COMMENT ON COLUMN TUNER.TB_CUST.UPDT_ID IS '수정아이디';
COMMENT ON
COLUMN TUNER.TB_CUST.UPDT_DT IS '수정일시';
COMMENT ON TABLE TUNER.TB_CUST IS
'고객';
ALTER TABLE TUNER.TB_CUST NOLOGGING;
--데이터
입력
INSERT /*+ APPEND */
INTO TUNER.TB_CUST
SELECT 'C' ||
LPAD(ROWNUM, 9, '0') AS CUST_NO
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS
CUST_ID
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS CUST_NM
,
TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80),
'YYYYMMDD') AS BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2),
4, '0') AS SEX_CD
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
1000000;
COMMIT;
CREATE UNIQUE INDEX TUNER.PK_TB_CUST ON
TUNER.TB_CUST(CUST_NO) TABLESPACE TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER
INDEX TUNER.PK_TB_CUST PARALLEL 1;
ALTER INDEX TUNER.PK_TB_CUST
LOGGING;
ALTER TABLE TUNER.TB_CUST ADD CONSTRAINT PK_TB_CUST PRIMARY KEY
(CUST_NO);
CREATE INDEX TUNER.IDX_TB_CUST_01 ON
TUNER.TB_CUST(CUST_STS_CD) TABLESPACE TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER
INDEX TUNER.IDX_TB_CUST_01 PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_CUST_01
LOGGING;
CREATE INDEX TUNER.IDX_TB_CUST_02 ON
TUNER.TB_CUST(BRTHDY) TABLESPACE TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.IDX_TB_CUST_02 PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_CUST_02
LOGGING;
CREATE INDEX TUNER.IDX_TB_CUST_03 ON
TUNER.TB_CUST(CUST_STS_CD, BRTHDY) TABLESPACE TUNER_IDX1 PARALLEL 4
NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_03 PARALLEL 1;
ALTER INDEX
TUNER.IDX_TB_CUST_03 LOGGING;
-- 다시
LOGGING으로 설정 (중요!)
ALTER TABLE TUNER.TB_CUST LOGGING;
--통계 정보
생성
EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_CUST', method_opt =>
'for all indexed columns' , cascade => true, DEGREE=>
4);
[2025-06-14:19:48:49][ol7ora19r1]<SYS@ORA19R1> select count(*) from tuner.tb_cust;
COUNT(*)
----------
1000000
1 row selected.
--> 현재 TUNER.TB_CUST 테이블에는 100만건의 데이터가 있는
상태임
SELECT CURRENT_SCN
FROM
V$DATABASE;
CURRENT_SCN
-----------
6287390
-->
tuner.tb_cust 테이블 생성 완료함
3. 소스에서 풀
백업 작업 수행
--소스에서 백업받을 디렉토리
생성
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$
mkdir -p
/home/oracle/ORA19R_BACKUP/autobackup
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$
ls -l /home/oracle/ORA19R_BACKUP/autobackup
total 0
--RMAN으로
접속
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
export NLS_DATE_FORMAT='YYYY-MM-DD
HH24:MI:SS'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ alias
rt
alias rt='rman target
/'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ rt
Recovery Manager: Release 19.0.0.0.0 -
Production on Wed Jun 11 21:06:58 2025
Version
19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19R (DBID=1824822448)
--백업 작업
수행
RMAN>
run {
CONFIGURE RETENTION POLICY TO REDUNDANCY
1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO
DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE
AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/home/oracle/ORA19R_BACKUP/autobackup/%F';
CONFIGURE DEVICE TYPE DISK
PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR
DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
DISK TO 1;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE
MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE
ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF
RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
}
RUN {
sql 'alter system archive log
current';
sql 'alter system checkpoint';
crosscheck
backupset;
crosscheck backup;
crosscheck copy;
crosscheck archivelog
all;
BACKUP AS COMPRESSED BACKUPSET TABLESPACE SYSTEM FORMAT
'/home/oracle/ORA19R_BACKUP/SYSTEM_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET
TABLESPACE SYSAUX FORMAT '/home/oracle/ORA19R_BACKUP/SYSAUX_%U.bkp';
BACKUP
AS COMPRESSED BACKUPSET TABLESPACE UNDOTBS1 FORMAT
'/home/oracle/ORA19R_BACKUP/UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET
TABLESPACE USERS FORMAT '/home/oracle/ORA19R_BACKUP/USERS_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET TABLESPACE UNDOTBS2 FORMAT
'/home/oracle/ORA19R_BACKUP/UNDOTBS2_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET
TABLESPACE TUNER_DATA1 FORMAT
'/home/oracle/ORA19R_BACKUP/TUNER_DATA1_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TUNER_IDX1 FORMAT
'/home/oracle/ORA19R_BACKUP/TUNER_IDX1_%U.bkp';
delete noprompt
obsolete;
delete noprompt expired backup;
}
--> 테이블스페이스 별로 백업을 받음!
run {
crosscheck archivelog
all;
backup archivelog all format
'/home/oracle/ORA19R_BACKUP/%d_ARCHIVE_%T_%u_s%s_p%p' delete input;
delete
backup of archivelog all completed before
'SYSDATE-14';
}
--> 백업 작업이 완료됨! 아래와 같이 백업된 파일 목록을 확인!
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ ls -lRa /home/oracle/ORA19R_BACKUP
/home/oracle/ORA19R_BACKUP:
total
774644
drwxr-xr-x. 3 oracle oinstall 4096 Jun
14 19:59 .
drwx------. 8 oracle oinstall 4096
Jun 14 19:56 ..
drwxr-xr-x. 2 oracle
oinstall 70 Jun 14 19:59
autobackup
-rw-r-----. 1 oracle asmadmin 25600 Jun 14
19:59 ORA19R_ARCHIVE_20250614_533s0up8_s163_p1
-rw-r-----. 1 oracle asmadmin
249167872 Jun 14 19:58 SYSAUX_4s3s0un7_156_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 458104832 Jun 14 19:58 SYSTEM_4r3s0ulp_155_1_1.bkp
-rw-r-----. 1
oracle asmadmin 68681728 Jun 14 19:59
TUNER_DATA1_503s0uoi_160_1_1.bkp
-rw-r-----. 1 oracle asmadmin 13606912
Jun 14 19:59 TUNER_IDX1_513s0uoq_161_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1253376 Jun 14 19:59
UNDOTBS1_4t3s0uob_157_1_1.bkp
-rw-r-----. 1 oracle asmadmin
1138688 Jun 14 19:59 UNDOTBS2_4v3s0uof_159_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1245184 Jun 14 19:59
USERS_4u3s0uod_158_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup:
total 39364
drwxr-xr-x. 2
oracle oinstall 70 Jun 14 19:59
.
drwxr-xr-x. 3 oracle oinstall 4096 Jun 14 19:59
..
-rw-r-----. 1 oracle asmadmin 20152320 Jun 14 19:59
c-1824822448-20250614-03
-rw-r-----. 1 oracle asmadmin 20152320 Jun 14 19:59
c-1824822448-20250614-04
-->
'c-1824822448-20250614-04' --> 이게 나중에 백업받은 control file
임
4. 소스에서
DROP TABLE PURGE 가 되는 사고가 발생함!
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias
st
alias st='rlwrap sqlplus tuner/oracle'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ st
SQL*Plus: Release 19.0.0.0.0 -
Production on Thu Jun 12 23:41:38 2025
Version
19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.27.0.0.0
[2025-06-14:20:00:58][ol7ora19r1]<TUNER@ORA19R1> select count(*) from tuner.tb_cust;
COUNT(*)
----------
1000000
1 row selected.
Elapsed: 00:00:00.35
[2025-06-14:20:00:58][ol7ora19r1]<TUNER@ORA19R1>
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS
CUR_DATE
FROM DUAL;
CUR_DATE
---------------------------------------------------------
2025-06-14 20:01:11
1 row selected.
Elapsed: 00:00:00.00
[2025-06-14:20:00:58][ol7ora19r1]<TUNER@ORA19R1> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
6289887
--> 해당
scn번호가 복구 목표인 SCN번호임
--> 결국
해당 SCN번호까지만 불완전 복구를 하면 TUNE.TB_CUST 테이블의 내용을 다시 볼수 있는 것임!
1 row selected.
Elapsed: 00:00:00.01
[2025-06-14:20:00:58][ol7ora19r1]<TUNER@ORA19R1> drop table tuner.tb_cust purge;
--> drop table purge를 하는 사고가 발생함!!!!
Table dropped.
Elapsed: 00:00:00.76
[2025-06-14:20:00:58][ol7ora19r1]<TUNER@ORA19R1> select * from tuner.tb_cust;
select * from
tuner.tb_cust
*
ERROR at line 1:
ORA-00942:
table or view does not exist
Elapsed: 00:00:00.06
[2025-06-14:20:00:58][ol7ora19r1]<TUNER@ORA19R1>
SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
6289959
1 row selected.
Elapsed: 00:00:00.01
[2025-06-14:20:00:58][ol7ora19r1]<TUNER@ORA19R1>
--> SCN : 6289887
시점에는 TUNER.TB_CUST 테이블이 존재했었던 상황임! 결국 해당
시점까지 불완전 복구를 해야하는 상황임!
5. 소스에서 아카이브를 발생
시킴
--아카이브 발생 시 check point도 자동으로 하게됨
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls -sl
+FRA1_NEW/ORA19R/ARCHIVELOG
-->
아카이브 로그가 없음
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Wed Jun 11 22:20:24 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[2025-06-14:20:02:16][ol7ora19r1]<SYS@ORA19R1> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls -sl
+FRA1_NEW/ORA19R/ARCHIVELOG
Type Redund
Striped Time Sys Block_Size Blocks Bytes
Space Name
Y
2025_06_14/
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls -sl
+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_14
Type Redund
Striped
Time
Sys Block_Size Blocks Bytes
Space Name
ARCHIVELOG UNPROT
COARSE JUN 14 20:00:00
Y 512
11903 6094336 8388608 thread_1_seq_155.274.1203797633
ARCHIVELOG UNPROT COARSE JUN 14
20:00:00 Y
512 10955 5608960 8388608
thread_2_seq_79.257.1203797633
--> RAC 1, 2에서 각각 아카이브 로그가
생성됨
[2025-06-14:20:02:16][ol7ora19r1]<SYS@ORA19R1>
col name for a80
select NAME, THREAD#, SEQUENCE#, FIRST_CHANGE#,
NEXT_CHANGE#
from v$archived_log
where
6289887 between FIRST_CHANGE# and NEXT_CHANGE#;
--> 불완전 복구를 해야하는 SCN번호 기준으로 어떤 아카이브 파일이 해당 SCN번호에 걸쳐있는지
확인
NAME
THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
--------------------------------------------------------------------------------
---------- ---------- ------------- ------------
+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_14/thread_2_seq_79.257.1203797633
2
79 6288814
6291694
+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_14/thread_1_seq_155.274.1203797633
1
155 6288810
6291697
--> 복구 목표 시점인 scn
6289887은 위의 아카이브 로그에 걸쳐 있음
--아래와 같이 RMAN에서 해당 아카이브 로그를
백업받아야함
--(백업본 restore 후 불완전 복구 시 필요함)
--불완전 복구 시 필요한 아카이브 로그를 백업받는 것임, 해당 아카이브로도 타켓으로 같이 넘길꺼임!
(중요!)
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias
rt
alias rt='rman target /'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$
rt
Recovery Manager:
Release 19.0.0.0.0 - Production on Sat Jun 14 20:27:33 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19R (DBID=1824822448)
RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG FROM SEQUENCE 79 THREAD 2 FORMAT '/home/oracle/ORA19R_BACKUP/arch_t2_s79_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET ARCHIVELOG FROM SEQUENCE 79 THREAD 2 FORMAT
'/home/oracle/ORA19R_BACKUP/arch_t2_s79_%U.bkp';
Starting backup at 2025-06-14 20:24:19
current log archived
using target
database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1116 instance=ORA19R1 device
type=DISK
channel ORA_DISK_1: starting compressed
archived log backup set
channel ORA_DISK_1: specifying
archived log(s) in backup set
input archived log
thread=2 sequence=79 RECID=223 STAMP=1203797632
input
archived log thread=2 sequence=80 RECID=226 STAMP=1203798262
channel ORA_DISK_1: starting piece 1 at 2025-06-14
20:24:25
channel ORA_DISK_1: finished piece 1 at
2025-06-14 20:24:26
piece
handle=/home/oracle/ORA19R_BACKUP/arch_t2_s79_553s107p_165_1_1.bkp
tag=TAG20250614T202424 comment=NONE
channel ORA_DISK_1:
backup set complete, elapsed time: 00:00:01
Finished
backup at 2025-06-14 20:24:26
Starting Control
File and SPFILE Autobackup at 2025-06-14 20:24:27
piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250614-05
comment=NONE
Finished Control File and SPFILE Autobackup
at 2025-06-14 20:24:28
RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG FROM
SEQUENCE 155 THREAD 1 FORMAT
'/home/oracle/ORA19R_BACKUP/arch_t1_s155_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET ARCHIVELOG FROM SEQUENCE 155 THREAD 1 FORMAT
'/home/oracle/ORA19R_BACKUP/arch_t1_s155_%U.bkp';
Starting backup at 2025-06-14 20:24:43
current log archived
using channel
ORA_DISK_1
channel ORA_DISK_1: starting compressed
archived log backup set
channel ORA_DISK_1: specifying
archived log(s) in backup set
input archived log
thread=1 sequence=155 RECID=224 STAMP=1203797633
input
archived log thread=1 sequence=156 RECID=225 STAMP=1203798260
input archived log thread=1 sequence=157 RECID=227
STAMP=1203798285
channel ORA_DISK_1: starting piece 1 at
2025-06-14 20:24:46
channel ORA_DISK_1: finished piece 1
at 2025-06-14 20:24:47
piece
handle=/home/oracle/ORA19R_BACKUP/arch_t1_s155_573s108e_167_1_1.bkp
tag=TAG20250614T202446 comment=NONE
channel ORA_DISK_1:
backup set complete, elapsed time: 00:00:01
Finished
backup at 2025-06-14 20:24:47
Starting Control File and SPFILE Autobackup at 2025-06-14
20:24:47
piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250614-06 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-06-14
20:24:50
--> 'c-1824822448-20250614-06' 해당 컨트롤 파일에는
아카이브를 백업 받은 내용도 같이 저장된 상태임
-->
이부분을 이해하는것이 매우 중요함
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ls -lR
/home/oracle/ORA19R_BACKUP/
/home/oracle/ORA19R_BACKUP/:
total
779144
-rw-r-----. 1 oracle asmadmin 2381312
Jun 14 20:24 arch_t1_s155_573s108e_167_1_1.bkp
-rw-r-----. 1 oracle asmadmin 2227712 Jun 14
20:24 arch_t2_s79_553s107p_165_1_1.bkp
drwxr-xr-x. 2
oracle oinstall 4096 Jun 14 20:24 autobackup
-rw-r-----. 1 oracle asmadmin 25600
Jun 14 19:59 ORA19R_ARCHIVE_20250614_533s0up8_s163_p1
-rw-r-----. 1 oracle asmadmin 249167872 Jun 14 19:58
SYSAUX_4s3s0un7_156_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 458104832 Jun 14 19:58 SYSTEM_4r3s0ulp_155_1_1.bkp
-rw-r-----. 1 oracle asmadmin 68681728 Jun 14 19:59
TUNER_DATA1_503s0uoi_160_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 13606912 Jun 14 19:59 TUNER_IDX1_513s0uoq_161_1_1.bkp
-rw-r-----. 1 oracle asmadmin 1253376 Jun 14
19:59 UNDOTBS1_4t3s0uob_157_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1138688 Jun 14 19:59 UNDOTBS2_4v3s0uof_159_1_1.bkp
-rw-r-----. 1 oracle asmadmin 1245184 Jun 14
19:59 USERS_4u3s0uod_158_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup:
total 78720
-rw-r-----. 1 oracle
asmadmin 20152320 Jun 14 19:59 c-1824822448-20250614-03
-rw-r-----. 1 oracle asmadmin 20152320 Jun 14 19:59
c-1824822448-20250614-04
-rw-r-----. 1 oracle asmadmin
20152320 Jun 14 20:24 c-1824822448-20250614-05
-rw-r-----. 1 oracle asmadmin 20152320 Jun 14 20:24
c-1824822448-20250614-06
6. 소스에서 pfile
생성
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Jun 14 20:28:31 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[2025-06-14:20:28:32][ol7ora19r1]<SYS@ORA19R1>
create
pfile='/home/oracle/ORA19R_BACKUP/initORA19R.ora' from spfile;
File created.
Elapsed:
00:00:00.04
7. 타켓에서 백업본을 저장할
디렉토리 생성 및 백업본을 가져옴 (scp)
--타켓 서버에 접속해서 복구 작업에 사용할 디렉토리를 생성하고 소스 서버의 백업본 및 PFILE을 SCP로 가져오는 것임!
[ORCL:oracle@tdb01t][/home/oracle]$ mkdir -pv
/home/oracle/ORA19R_BACKUP/
mkdir: created directory
‘/home/oracle/ORA19R_BACKUP’
[ORCL:oracle@tdb01t][/home/oracle]$ cd /home/oracle/ORA19R_BACKUP
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
scp -r 192.168.0.21:/home/oracle/ORA19R_BACKUP/*
/home/oracle/ORA19R_BACKUP/
oracle@192.168.0.21's password:
arch_t1_s155_573s108e_167_1_1.bkp
100% 2326KB 12.1MB/s 00:00
arch_t2_s79_553s107p_165_1_1.bkp
100% 2176KB 16.6MB/s 00:00
c-1824822448-20250614-03
100% 19MB 10.0MB/s 00:01
c-1824822448-20250614-04
100% 19MB 8.1MB/s 00:02
c-1824822448-20250614-05
100% 19MB 27.1MB/s 00:00
c-1824822448-20250614-06
100% 19MB 35.8MB/s 00:00
initORA19R.ora
100% 1951 263.1KB/s 00:00
ORA19R_ARCHIVE_20250614_533s0up8_s163_p1
100% 25KB 7.0MB/s 00:00
SYSAUX_4s3s0un7_156_1_1.bkp
100% 238MB 47.0MB/s 00:05
SYSTEM_4r3s0ulp_155_1_1.bkp
100% 437MB 53.7MB/s 00:08
TUNER_DATA1_503s0uoi_160_1_1.bkp
100% 66MB 69.4MB/s 00:00
TUNER_IDX1_513s0uoq_161_1_1.bkp
100% 13MB 6.1MB/s 00:02
UNDOTBS1_4t3s0uob_157_1_1.bkp
100% 1224KB 4.2MB/s 00:00
UNDOTBS2_4v3s0uof_159_1_1.bkp
100% 1112KB 4.8MB/s 00:00
USERS_4u3s0uod_158_1_1.bkp
100% 1216KB 17.9MB/s 00:00
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ ls -laR
/home/oracle/ORA19R_BACKUP
/home/oracle/ORA19R_BACKUP:
total
779156
drwxr-xr-x. 3 oracle
oinstall 4096 Jun 14 20:34 .
drwx------. 11 oracle
oinstall 4096 Jun 14 20:33 ..
-rw-r-----. 1 oracle oinstall 2381312 Jun
14 20:33 arch_t1_s155_573s108e_167_1_1.bkp
-rw-r-----. 1 oracle oinstall 2227712 Jun
14 20:33 arch_t2_s79_553s107p_165_1_1.bkp
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 14 20:33 autobackup
-rw-r--r--. 1 oracle
oinstall 1951 Jun 14 20:33 initORA19R.ora
-rw-r-----. 1 oracle oinstall
25600 Jun 14 20:33 ORA19R_ARCHIVE_20250614_533s0up8_s163_p1
-rw-r-----. 1 oracle oinstall 249167872 Jun 14 20:33
SYSAUX_4s3s0un7_156_1_1.bkp
-rw-r-----. 1 oracle
oinstall 458104832 Jun 14 20:34 SYSTEM_4r3s0ulp_155_1_1.bkp
-rw-r-----. 1 oracle oinstall 68681728 Jun 14
20:34 TUNER_DATA1_503s0uoi_160_1_1.bkp
-rw-r-----.
1 oracle oinstall 13606912 Jun 14 20:34 TUNER_IDX1_513s0uoq_161_1_1.bkp
-rw-r-----. 1 oracle oinstall 1253376 Jun
14 20:34 UNDOTBS1_4t3s0uob_157_1_1.bkp
-rw-r-----.
1 oracle oinstall 1138688 Jun 14 20:34
UNDOTBS2_4v3s0uof_159_1_1.bkp
-rw-r-----. 1 oracle
oinstall 1245184 Jun 14 20:34
USERS_4u3s0uod_158_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup:
total 78728
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 14 20:33 .
drwxr-xr-x. 3 oracle oinstall 4096
Jun 14 20:34 ..
-rw-r-----. 1 oracle oinstall 20152320
Jun 14 20:33 c-1824822448-20250614-03
-rw-r-----. 1
oracle oinstall 20152320 Jun 14 20:33 c-1824822448-20250614-04
-rw-r-----. 1 oracle oinstall 20152320 Jun 14 20:33
c-1824822448-20250614-05
-rw-r-----. 1 oracle oinstall
20152320 Jun 14 20:33 c-1824822448-20250614-06
8. 타켓에서 pfile 수정
우선 타켓 서버의 grid os user로 접속해서 신규 리스너를 생성해줌 (타켓 서버가 Oracle Restart Server이기 때문에 리스너는 grid os user로 띄어야함, 설치를 grid/oracle로 분리시켜 해놓은 상태)
[+ASM:grid@tdb01t][/home/grid]$ srvctl add listener
-listener LISTENER_ORA19R -oraclehome /u01/app/19c/grid -endpoints
TCP:1525
[+ASM:grid@tdb01t][/home/grid]$ srvctl start listener
-listener LISTENER_ORA19R
[+ASM:grid@tdb01t][/home/grid]$ lsnrctl status
LISTENER_ORA19R
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 14-JUN-2025 21:08:19
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_ORA19R)))
STATUS of the LISTENER
------------------------
Alias
LISTENER_ORA19R
Version
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start
Date
14-JUN-2025 21:08:13
Uptime
0 days 0 hr. 0 min. 6 sec
Trace
Level
off
Security
ON: Local OS Authentication
SNMP
OFF
Listener Parameter File
/u01/app/19c/grid/network/admin/listener.ora
Listener
Log File
/u01/app/oracle/diag/tnslsnr/tdb01t/listener_ora19r/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_ORA19R)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1525)))
The listener supports no services
The command completed successfully
-->
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1525)))
--> 를 타켓 db의 local_listener 파라미터의 값으로 할
것임
지금부터 타켓 서버에서 (소스서버에서
가져온 pfile을 수정함)
--소스에서 가져온 pfile의
내용
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ cat
/home/oracle/ORA19R_BACKUP/initORA19R.ora
ORA19R2.__data_transfer_cache_size=0
ORA19R1.__data_transfer_cache_size=0
ORA19R1.__db_cache_size=3741319168
ORA19R2.__db_cache_size=3875536896
ORA19R2.__inmemory_ext_roarea=0
ORA19R1.__inmemory_ext_roarea=0
ORA19R2.__inmemory_ext_rwarea=0
ORA19R1.__inmemory_ext_rwarea=0
ORA19R2.__java_pool_size=0
ORA19R1.__java_pool_size=0
ORA19R2.__large_pool_size=16777216
ORA19R1.__large_pool_size=16777216
ORA19R1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set
from environment
ORA19R2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set
from environment
ORA19R2.__pga_aggregate_target=1728053248
ORA19R1.__pga_aggregate_target=1728053248
ORA19R2.__sga_target=5167382528
ORA19R1.__sga_target=5167382528
ORA19R2.__shared_io_pool_size=50331648
ORA19R1.__shared_io_pool_size=134217728
ORA19R1.__shared_pool_size=1258291200
ORA19R2.__shared_pool_size=1207959552
ORA19R2.__streams_pool_size=0
ORA19R1.__streams_pool_size=0
ORA19R2.__unified_pga_pool_size=0
ORA19R1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORA19R/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA1_NEW/ORA19R/CONTROLFILE/current.256.1202820017','+FRA1_NEW/ORA19R/CONTROLFILE/current.261.1202820017'
*.db_block_size=8192
*.db_create_file_dest='+FRA1_NEW'
*.db_create_online_log_dest_1='+DATA1_NEW'
*.db_create_online_log_dest_2='+FRA1_NEW'
*.db_name='ORA19R'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19RXDB)'
family:dw_helper.instance_mode='read-only'
ORA19R1.instance_number=1
ORA19R2.instance_number=2
*.local_listener='-oraagent-dummy-'
*.log_archive_dest_1='LOCATION=+FRA1_NEW'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=1638m
*.processes=1432
*.remote_login_passwordfile='exclusive'
*.sga_target=4915m
ORA19R2.thread=2
ORA19R1.thread=1
ORA19R2.undo_tablespace='UNDOTBS2'
ORA19R1.undo_tablespace='UNDOTBS1'
--> 위의 내용을 아래와 같이 변경한후 저장함
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ vi
/home/oracle/ORA19R_BACKUP/initORA19R.ora
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ cat
/home/oracle/ORA19R_BACKUP/initORA19R.ora
*.audit_file_dest='/u01/app/oracle/admin/ORA19R/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile01.ctl','/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/ORA19R_BACKUP'
*.db_create_online_log_dest_1='/home/oracle/ORA19R_BACKUP/redolog_1'
*.db_create_online_log_dest_2='/home/oracle/ORA19R_BACKUP/redolog_2'
*.db_name='ORA19R'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19RXDB)'
family:dw_helper.instance_mode='read-only'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1525)))'
*.log_archive_dest_1='LOCATION=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=1638m
*.processes=1432
*.remote_login_passwordfile='exclusive'
*.sga_target=4915m
*.undo_tablespace='UNDOTBS1'
수정한 pfile을 기반으로 타켓서버에서
필요한 디렉토리를 미리 생성해둠
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ mkdir -p
/u01/app/oracle/admin/ORA19R/adump
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ mkdir -p
/home/oracle/ORA19R_BACKUP/redolog_1
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ mkdir -p
/home/oracle/ORA19R_BACKUP/redolog_2
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ mkdir -p
/home/oracle/ORA19R_BACKUP/ORA19R/ARCH
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ mkdir -p
/home/oracle/ORA19R_BACKUP/ORA19R/datafile
9. 타켓에서 nomount
모드로 인스턴스 시작
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ export
ORACLE_SID=ORA19R
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Wed Jun 11 22:59:06 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to an idle instance.
[2025-06-11
22:59:07][]<ORA19R@SYS> startup nomount
pfile='/home/oracle/ORA19R_BACKUP/initORA19R.ora'
ORACLE instance
started.
Total System
Global Area 5167379008 bytes
Fixed
Size
8950336 bytes
Variable
Size
922746880 bytes
Database
Buffers 4227858432 bytes
Redo
Buffers
7823360 bytes
타켓 서버에서 또다른 ssh창을 하나 더
열어서 oracle user로 접속한 다음 alert log를 모니터링! (항상 alert log를 모니터링
하자!)
[ORA19R:oracle@tdb01t][/u01/app/oracle/diag/rdbms/ora19r/ORA19R/trace]$
tail -f
/u01/app/oracle/diag/rdbms/ora19r/ORA19R/trace/alert_ORA19R.log
Starting background process TMON
2025-06-14T21:32:10.047118+09:00
TMON started with pid=36, OS id=7833
ORACLE_BASE from environment = /u01/app/oracle
2025-06-14T21:32:12.309870+09:00
Using default pga_aggregate_limit of 4296 MB
2025-06-14T21:32:13.347405+09:00
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
/u01/app/oracle/diag/rdbms/ora19r/ORA19R/trace/ORA19R_vktm_7652.trc
-->
alert log 모니터링 시작
10. 타켓에서 restore
controlfile 한후 mount 모드로 변경
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP/autobackup]$
export ORACLE_SID=ORA19R
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP/autobackup]$
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP/autobackup]$
alias rt
alias rt='rman target /'
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP/autobackup]$
rt
Recovery Manager:
Release 19.0.0.0.0 - Production on Sat Jun 14 21:40:58 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19R (not mounted)
RMAN> restore controlfile from '/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250614-06';
restore
controlfile from
'/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250614-06';
Starting restore at 2025-06-14 21:41:32
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1630 device
type=DISK
channel
ORA_DISK_1: restoring control file
channel ORA_DISK_1:
restore complete, elapsed time: 00:00:01
output file
name=/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile01.ctl
output file
name=/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile02.ctl
Finished restore at 2025-06-14 21:41:34
RMAN> alter
database mount;
alter database mount;
released
channel: ORA_DISK_1
Statement processed
--> resotre controlfile을 한
직후에는 컨트롤 파일의 데이터파일 경로 및 파일정보에 맞게 실제 파일이
--> 존재하지 않아도
mount까지 갈 수 있음
11. 소스에서 REDO LOG를
RENAME하는 스크립트를 생성하는 SQL문 실행
SELECT 'alter
database rename file ' ||''''|| member ||''''|| ' ' ||''|| ' to '|| ''''
|| case when member like '+DATA1_NEW%'
then '/home/oracle/ORA19R_BACKUP/redolog_1/'
when
member like '+FRA1_NEW%' then '/home/oracle/ORA19R_BACKUP/redolog_2/'
end
|| substr(member, instr(member,
'group'))||'''' ||';' AS rename_redo_log
FROM
v$logfile
;
<결과>
alter database
rename file '+DATA1_NEW/ORA19R/ONLINELOG/group_3.259.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_3.259.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_3.264.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_3.264.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_2.257.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_2.257.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_2.262.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_2.262.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_1.258.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_1.258.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_1.263.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_1.263.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_4.260.1202820853' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_4.260.1202820853';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_4.267.1202820853' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_4.267.1202820853';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_5.261.1202820855' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_5.261.1202820855';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_5.268.1202820855' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_5.268.1202820855';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_6.262.1202820857' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_6.262.1202820857';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_6.269.1202820857' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_6.269.1202820857';
12. 위에서 생성한 REDO LOG를 RENAME하는 스크립트를 타켓에서 실행
시킴!
타켓에서 실행 시킬 때 RMAN 혹은 sqlplus든 모두 가능함!
alter database
rename file '+DATA1_NEW/ORA19R/ONLINELOG/group_3.259.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_3.259.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_3.264.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_3.264.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_2.257.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_2.257.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_2.262.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_2.262.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_1.258.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_1.258.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_1.263.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_1.263.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_4.260.1202820853' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_4.260.1202820853';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_4.267.1202820853' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_4.267.1202820853';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_5.261.1202820855' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_5.261.1202820855';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_5.268.1202820855' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_5.268.1202820855';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_6.262.1202820857' to
'/home/oracle/ORA19R_BACKUP/redolog_1/group_6.262.1202820857';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_6.269.1202820857' to
'/home/oracle/ORA19R_BACKUP/redolog_2/group_6.269.1202820857';
타켓에서 REDO LOG RENAME 후
아래의 SQL문으로 상태를 확인!
[2025-06-12:10:11:32][tdb01t]<ORA19R@SYS>
col member for a80
select a.group#, a.thread#, a.bytes/1024/1024 as mb,
a.status, b.member from v$log a, v$logfile b where a.group# =
b.group#;
GROUP# THREAD#
MB STATUS MEMBER
---------- ----------
---------- ---------
-----------------------------------------------------------
1
1 200 ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_2/group_1.263.1202820021
1
1 200 ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_1/group_1.258.1202820019
2
1 200 CURRENT
/home/oracle/ORA19R_BACKUP/redolog_1/group_2.257.1202820019
2
1 200 CURRENT
/home/oracle/ORA19R_BACKUP/redolog_2/group_2.262.1202820021
3
1 200 ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_2/group_3.264.1202820021
3
1 200 ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_1/group_3.259.1202820019
4
2 200 CURRENT
/home/oracle/ORA19R_BACKUP/redolog_1/group_4.260.1202820853
4
2 200 CURRENT
/home/oracle/ORA19R_BACKUP/redolog_2/group_4.267.1202820853
5
2 200 ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_1/group_5.261.1202820855
5
2 200 ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_2/group_5.268.1202820855
6
2 200 ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_1/group_6.262.1202820857
6
2 200 ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_2/group_6.269.1202820857
12 rows
selected.
13. 소스에서 set
newname 스크립트를 생성하는 sql문 실행 및 skip할 테이블 스페이스를 출력하는 sql문 실행
현재 소스 서버의 DB에는 사용자 테이블 스페이스로 TUNER_DATA1, TUNER_IDX1, USERS가 존재하는 상황임
이상 상황에서 USERS는 복구할 필요가 없으므로 제외 시킬것임
이러한 작업을 원활하게 하기 위해서 아래의 스크립트를 실행함 (실전에서는 제외 시켜야할 유저 테이블 스페이스 스페이스의 개수가 매우 많을 것이므로!)
select 'set newname for datafile ' || file_id || ' ' || 'to
' || ''''
|| replace(file_name,
'+DATA1_NEW/ORA19R/DATAFILE/', '/home/oracle/ORA19R_BACKUP/ORA19R/datafile/') ||
'''' || ';' as set_newname
from dba_data_files
where tablespace_name in ('SYSTEM', 'SYSAUX',
'UNDOTBS1', 'UNDOTBS2', 'TUNER_DATA1', 'TUNER_IDX1');
-->
USERS 테이블스페이스는 제외하고 생성
-->
파라미터 파일(pfile) 설정 시 *.db_create_file_dest='/home/oracle/ORA19R_BACKUP' 로 했기
때문에
--> /home/oracle/ORA19R_BACKUP/ORA19R/datafile/
위치가 datafile의 위치가 됨
--> db_create_file_dest 위치 아래에
datafile 경로를 추가해서 그 밑에 넣어주게됨
set newname for
datafile 1 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797';
set newname for datafile 3 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873';
set newname for datafile 5 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057';
set newname for datafile 2 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203796157';
set newname for datafile 4 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029';
set newname for datafile 8 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203796161';
--> skip 할 테이블 스페이스를
출력
[2025-06-14:20:28:32][ol7ora19r1]<SYS@ORA19R1> col SKIP_TABLESPACE_NAME for a20
select listagg(tablespace_name, ',') WITHIN GROUP(ORDER BY
TABLESPACE_NAME) as skip_tablespace_name
from
DBA_TABLESPACES
where tablespace_name not in
('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'UNDOTBS2', 'TUNER_DATA1', 'TUNER_IDX1')
and CONTENTS in ('PERMANENT');
SKIP_TABLESPACE_NAME
--------------------
USERS
14. 타켓에서 RESTORE
DATABASE
타켓에서 RMAN으로 접속해서 아래와 같이 RESTORE를 수행함
RMAN에서 SKIP TABLESPACE 구문 사용 시 테이블 스페이스 명은 반드시 대문자로 해야함!
run
{
allocate channel ch1 device type
disk ;
allocate channel ch2 device type disk
;
set newname for
datafile 1 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797';
set newname for datafile 3 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873';
set newname for datafile 5 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057';
set newname for datafile 2 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203796157';
set newname for datafile 4 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029';
set newname for datafile 8 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203796161';
restore database skip tablespace 'USERS';
switch datafile
1;
switch datafile 3;
switch
datafile 5;
switch datafile 2;
switch datafile 4;
switch datafile
8;
release channel ch1;
release channel ch2;
}
--> 중요! 중요! 매우 중요! skip
tablespace 'USERS' --> 테이블스페이스명을 대문자로 기재할것!
--switch
datafile all; ---> 이걸로 한번에 다 switch datafile 할 수 있음
allocated channel:
ch1
channel ch1: SID=10 device type=DISK
allocated channel:
ch2
channel ch2: SID=1097 device
type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2025-06-14 22:09:53
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/SYSTEM_4r3s0ulp_155_1_1.bkp
channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup
set
channel ch2: restoring datafile 00003 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
channel ch2: reading from backup piece
/home/oracle/ORA19R_BACKUP/SYSAUX_4s3s0un7_156_1_1.bkp
channel ch2: piece
handle=/home/oracle/ORA19R_BACKUP/SYSAUX_4s3s0un7_156_1_1.bkp
tag=TAG20250614T195831
channel ch2: restored backup
piece 1
channel ch2: restore complete, elapsed time:
00:00:37
channel ch2: starting datafile backup set
restore
channel ch2: specifying datafile(s) to restore
from backup set
channel ch2: restoring datafile 00004 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
channel ch2: reading from backup piece
/home/oracle/ORA19R_BACKUP/UNDOTBS1_4t3s0uob_157_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/SYSTEM_4r3s0ulp_155_1_1.bkp
tag=TAG20250614T195745
channel ch1: restored backup
piece 1
channel ch1: restore complete, elapsed time:
00:00:52
channel ch1: starting datafile backup set
restore
channel ch1: specifying datafile(s) to restore
from backup set
channel ch1: restoring datafile 00005 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/UNDOTBS2_4v3s0uof_159_1_1.bkp
channel ch2: piece
handle=/home/oracle/ORA19R_BACKUP/UNDOTBS1_4t3s0uob_157_1_1.bkp
tag=TAG20250614T195907
channel ch2: restored backup
piece 1
channel ch2: restore complete, elapsed time:
00:00:15
channel ch2: starting datafile backup set
restore
channel ch2: specifying datafile(s) to restore
from backup set
channel ch2: restoring datafile 00002 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203796157
channel ch2: reading from backup piece
/home/oracle/ORA19R_BACKUP/TUNER_DATA1_503s0uoi_160_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/UNDOTBS2_4v3s0uof_159_1_1.bkp
tag=TAG20250614T195911
channel ch1: restored backup
piece 1
channel ch1: restore complete, elapsed time:
00:00:01
channel ch1: starting datafile backup set
restore
channel ch1: specifying datafile(s) to restore
from backup set
channel ch1: restoring datafile 00008 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203796161
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/TUNER_IDX1_513s0uoq_161_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_IDX1_513s0uoq_161_1_1.bkp
tag=TAG20250614T195922
channel ch1: restored backup
piece 1
channel ch1: restore complete, elapsed time:
00:00:07
channel ch2: piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_DATA1_503s0uoi_160_1_1.bkp
tag=TAG20250614T195914
channel ch2: restored backup
piece 1
channel ch2: restore complete, elapsed time:
00:00:16
Finished restore at 2025-06-14
22:11:05
datafile 1
switched to datafile copy
input datafile copy RECID=8
STAMP=1203804665 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
datafile 3
switched to datafile copy
input datafile copy RECID=9
STAMP=1203804666 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
datafile 5
switched to datafile copy
input datafile copy RECID=10
STAMP=1203804666 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
datafile 2
switched to datafile copy
input datafile copy RECID=11
STAMP=1203804666 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203796157
datafile 4
switched to datafile copy
input datafile copy RECID=12
STAMP=1203804667 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
datafile 8
switched to datafile copy
input datafile copy RECID=13
STAMP=1203804667 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203796161
released channel: ch1
released channel: ch2
[2025-06-14
21:31:52][]<ORA19R@SYS> col name for a80
[2025-06-14
21:31:52][]<ORA19R@SYS> SELECT FILE#, STATUS, NAME FROM
V$DATAFILE;
FILE#
STATUS
NAME
---------- ---------------------
--------------------------------------------------------------------------------
1
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
2
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203796157
3
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
4
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
5
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
7
ONLINE
+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071
8
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203796161
-->
+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071 이게 아직 살아 있음 이걸 offline
시켜야함
15. 소스DB에서 복구 대상이
아닌 테이블 스페이스에 대한 offline drop 문을 생성하는 SQL문을 실행
select 'alter
database datafile'
|| ''''
||FILE_NAME || ''''
|| ' offline
drop;' as offline_drop
from dba_data_files
where tablespace_name not in ( 'SYSTEM'
, 'SYSAUX'
, 'UNDOTBS1'
, 'UNDOTBS2'
, 'TUNER_DATA1'
, 'TUNER_IDX1');
OFFLINE_DROP
--------------------------------------------------------------------------------------
alter database
datafile'+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071' offline drop;
16. 타켓에서 복구 대상이 아닌
테이블 스페이스 offline
USERS 테이블 스페이스를 OFFLINE해서 RECOVER 시 복구 대상에서 제외 시키는 작업임
RMAN> alter
database datafile'+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071' offline
drop;
alter database
datafile'+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071' offline drop;
Statement processed
[2025-06-14
21:31:52][]<ORA19R@SYS> col name for a80
[2025-06-14
21:31:52][]<ORA19R@SYS> SELECT FILE#, STATUS, NAME FROM
V$DATAFILE;
FILE# STATUS NAME
---------- -------
----------------------------------------------------------------------
1
SYSTEM /home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
2
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203796157
3
ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
4
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
5
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
7 RECOVER
+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071
8
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203796161
-->
+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071 의 상태가 recover로 되어
있음
17. 타켓에서 필요한 아카이브
로그 파일을 모두 RMAN이 인식하고 있는지 확인
소스 디비에서 조회했을 때 SCN : 6289887 시점에는 TUNER.TB_CUST 테이블이 존재했었던 상황임!
그러므로 해당 SCN번호가 속해있는 아카이브 로그 파일을 전부 인식하고 있는지 확인해야하는 것임!
아까 소스에서 SCN : 6289887 이 속해있는 아카이브 파일을 아래와 같이 백업 받았었음!
BACKUP AS
COMPRESSED BACKUPSET ARCHIVELOG FROM SEQUENCE 79 THREAD 2 FORMAT
'/home/oracle/ORA19R_BACKUP/arch_t2_s79_%U.bkp';
BACKUP
AS COMPRESSED BACKUPSET ARCHIVELOG FROM SEQUENCE 155 THREAD 1 FORMAT
'/home/oracle/ORA19R_BACKUP/arch_t1_s155_%U.bkp';
소스에서 백업 받았었고, 소스에서 백업 시
그 내용이 컨트롤 파일에도 백업 받은 내용이 저장되었고! 그 (아카이브 백업 내역이 저장된)컨트롤 파일로 복구 했으니 아래에서와 같이 타켓에서
아카이브 로그 백업 상황을 확인해보면 필요한 아카이브 로그가 있다고 나오는 것임
만약 인식 못한다면 CATALOG START WITH로 인식시켜줘야함 (이부분을 이해하는 것이 매우 중요함)
RMAN> list backup of
archivelog all;
list backup of archivelog all;
List of Backup
Sets
===================
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ -------------------
151 24.50K
DISK 00:00:00
2025-06-14 19:59:36
BP Key:
151 Status: AVAILABLE Compressed: NO Tag:
TAG20250614T195936
Piece Name:
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250614_533s0up8_s163_p1
List of
Archived Logs in backup set 151
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 154 6288492
2025-06-14 19:57:37 6288810 2025-06-14 19:59:32
2 78
6288496 2025-06-14 19:57:40 6288814
2025-06-14 19:59:34
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
153
2.12M
DISK 00:00:01
2025-06-14 20:24:26
BP Key:
153 Status: AVAILABLE Compressed: YES Tag:
TAG20250614T202424
Piece Name:
/home/oracle/ORA19R_BACKUP/arch_t2_s79_553s107p_165_1_1.bkp
List of
Archived Logs in backup set 153
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
2 79 6288814
2025-06-14 19:59:34 6291694 2025-06-14 20:13:52
2 80
6291694 2025-06-14 20:13:52 6292262
2025-06-14 20:24:21
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
155
2.27M
DISK 00:00:00
2025-06-14 20:24:46
BP Key:
155 Status: AVAILABLE Compressed: YES Tag:
TAG20250614T202446
Piece Name:
/home/oracle/ORA19R_BACKUP/arch_t1_s155_573s108e_167_1_1.bkp
List of Archived Logs in backup set 155
Thrd Seq Low
SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 155 6288810
2025-06-14 19:59:32 6291697 2025-06-14 20:13:52
1 156
6291697 2025-06-14 20:13:52 6292258
2025-06-14 20:24:19
1
157 6292258 2025-06-14 20:24:19
6292318 2025-06-14 20:24:45
--> 필요한
아카이브 로그를 모두 제대로 인식하고 있음
RMAN> catalog
start with '/home/oracle/ORA19R_BACKUP';
--> 이 작업을 할 필요가 없음
--> 만약 직접 아카이브 로그를 restore 시킨다면
START---------------------------
--(recover 할 경우 아카이브를
restore한 후 recover함,
--그런데도 불구하고 아카이브로그를
archive log dest로 restore시켜놓고 싶다면)
RMAN> restore archivelog sequence <시퀀스번호> thread
<스레드번호>;
--> 만약 직접 아카이브 로그를
restore 시킨다면 END-----------------------------
18. 불완전 복구
수행
--> skip 할 테이블 스페이스를
출력
[2025-06-14:20:28:32][ol7ora19r1]<SYS@ORA19R1> col SKIP_TABLESPACE_NAME for a20
select
listagg(tablespace_name, ',') WITHIN GROUP(ORDER BY TABLESPACE_NAME) as
skip_tablespace_name
from DBA_TABLESPACES
where tablespace_name not in ('SYSTEM', 'SYSAUX',
'UNDOTBS1', 'UNDOTBS2', 'TUNER_DATA1', 'TUNER_IDX1')
and CONTENTS in ('PERMANENT');
SKIP_TABLESPACE_NAME
--------------------------------------------------------------------------------
USERS
--recover skip tablespace 을 써서
USERS를 빼고 recover 시키기
START------------------------------------------------------------------------
RMAN> recover
database skip tablespace 'USERS' until scn=6289887;
--> skip tablespace 'USERS' 에서 테이블스페이스명을 반드시 대문자로 기재할
것!
recover database
skip tablespace 'USERS' until scn=6289887;
Starting
recover at 2025-06-14 22:38:32
allocated channel:
ORA_DISK_1
channel ORA_DISK_1: SID=10 device
type=DISK
Executing: alter
database datafile 7 offline
starting media
recovery
channel
ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=154
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=78
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250614_533s0up8_s163_p1
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250614_533s0up8_s163_p1
tag=TAG20250614T195936
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH/1_154_1202820019.dbf thread=1
sequence=154
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH/2_78_1202820019.dbf thread=2
sequence=78
channel ORA_DISK_1: starting archived log
restore to default destination
channel ORA_DISK_1:
restoring archived log
archived log thread=1
sequence=155
channel ORA_DISK_1: reading from backup
piece /home/oracle/ORA19R_BACKUP/arch_t1_s155_573s108e_167_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/arch_t1_s155_573s108e_167_1_1.bkp
tag=TAG20250614T202446
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH/1_155_1202820019.dbf thread=1
sequence=155
channel ORA_DISK_1: starting archived log
restore to default destination
channel ORA_DISK_1:
restoring archived log
archived log thread=2
sequence=79
channel ORA_DISK_1: reading from backup
piece /home/oracle/ORA19R_BACKUP/arch_t2_s79_553s107p_165_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/arch_t2_s79_553s107p_165_1_1.bkp
tag=TAG20250614T202424
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH/2_79_1202820019.dbf thread=2
sequence=79
media recovery complete, elapsed time:
00:00:02
Finished recover at 2025-06-14
22:38:39
[2025-06-14
21:31:52][]<ORA19R@SYS>
SET LINESIZE 300
COL
NAME
FOR A10
COL
OPEN_MODE FOR A15
COL DATABASE_ROLE FOR
A20
COL RESETLOGS_CHANGE# FOR
999999999999999
COL CHECKPOINT_CHANGE# FOR
999999999999999
COL
CURRENT_SCN FOR
999999999999999
SELECT
NAME
, OPEN_MODE
, DATABASE_ROLE
, RESETLOGS_CHANGE#
, CHECKPOINT_CHANGE#
, CURRENT_SCN
FROM
V$DATABASE;
NAME
OPEN_MODE
DATABASE_ROLE RESETLOGS_CHANGE#
CHECKPOINT_CHANGE# CURRENT_SCN
---------- --------------- --------------------
----------------- ------------------ ----------------
ORA19R
MOUNTED
PRIMARY
1920977
6291694
0
[2025-06-14 21:31:52][]<ORA19R@SYS> select current_scn, checkpoint_change# from
v$database;
CURRENT_SCN
CHECKPOINT_CHANGE#
----------- ------------------
0
6291694
[2025-06-14 21:31:52][]<ORA19R@SYS>
SET LINESIZE
200
COL
FILE#
FOR 9999
COL
NAME
FOR A80
COL CHECKPOINT_CHANGE# FOR
999999999999999
COL
CHECKPOINT_TIME FOR A20
COL
STATUS
FOR A10
SELECT
FILE#
, NAME
,
CHECKPOINT_CHANGE#
,
TO_CHAR(CHECKPOINT_TIME, 'YYYY-MM-DD HH24:MI:SS') AS CHECKPOINT_TIME
, STATUS
FROM
V$DATAFILE_HEADER
ORDER BY
FILE#;
FILE#
NAME
CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-----
-----------------------------------------------------------------------
------------------ -------------------- ----------
1
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
6289887 2025-06-14 20:01:23 ONLINE
2
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203796157
6289887 2025-06-14 20:01:23 ONLINE
3
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
6289887 2025-06-14 20:01:23 ONLINE
4
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
6289887 2025-06-14 20:01:23 ONLINE
5
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
6289887 2025-06-14 20:01:23 ONLINE
7
(NULL)
0
(NULL)
OFFLINE
8
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203796161
6289887 2025-06-14 20:01:23 ONLINE
--> 모든 datafile 들이 scn번호
6289887까지 제대로 recover됨
--> 모든 datafile 들의 scn번호가 동일하니
resetlogs 오픈이 가능한 상황!
7 rows
selected.
실제로 RESETLOGS는 해당 시점까지의 REDO를
모두 적용 완료한 후 "이 시점이 새로운 기준!"으로 삼겠다는 의미라,
파일 간 SCN이 다르면
일관성을 보장 할 수 없으니 반드시 맞아야 함.
STATUS도 모두 ONLINE(단, 7번은
OFFLINE이지만 NULL이니 무시해도 됨. OFFLINE DROP 으로 복구 안 해도 되는 파일)
OFFLINE 데이터파일(여기선 FILE# 7)은
OFFLINE이거나 DROPPED라면 일치하지 않아도 RESETLOGS 오픈에 영향 없음(복구 대상
아님).
모든 데이터파일의 체크포인트 SCN이 동일하다 = 데이터베이스가 논리적으로 한 시점까지
recover되었으니, RESETLOGS 오픈이 가능하다
19. 불완전 복구 이므로 당연히
RESETLOGS로 오픈!
6289887 부터 디비가 다시 시작하고! 그때부터 다시 전혀 새로운 디비가 되는 것임!
REDO LOG도 초기화됨!
TEMPFILE도 자동으로 만들어짐!
RMAN> alter
database open resetlogs;
alter database open resetlogs;
Statement processed
RMAN> select
count(*) from tuner.tb_cust;
select count(*) from tuner.tb_cust;
COUNT(*)
----------
1000000
[2025-06-14 21:31:52][]<ORA19R@SYS> select current_Scn from v$database;
CURRENT_SCN
----------------
6291009
1 row
selected.
20. 타켓 서버에서 복구하는
과정에서 만들어진 모든 파일을 확인해보자!
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ ls -lRa
.:
total 779164
drwxr-xr-x. 6
oracle oinstall 4096 Jun 14 21:31 .
drwx------. 11 oracle
oinstall 4096 Jun 14 21:35 ..
-rw-r-----. 1 oracle oinstall 2381312 Jun
14 20:33 arch_t1_s155_573s108e_167_1_1.bkp
-rw-r-----. 1 oracle oinstall 2227712 Jun
14 20:33 arch_t2_s79_553s107p_165_1_1.bkp
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 14 20:33 autobackup
-rw-r--r--. 1 oracle
oinstall 1026 Jun 14 21:20 initORA19R.ora
drwxr-xr-x. 5 oracle
oinstall 53 Jun 14 22:08 ORA19R
-rw-r-----. 1 oracle oinstall
25600 Jun 14 20:33 ORA19R_ARCHIVE_20250614_533s0up8_s163_p1
drwxr-xr-x. 3 oracle
oinstall 4096 Jun 14 22:43 redolog_1
drwxr-xr-x. 3 oracle
oinstall 4096 Jun 14 22:43 redolog_2
-rw-r-----. 1 oracle oinstall 249167872 Jun 14 20:33
SYSAUX_4s3s0un7_156_1_1.bkp
-rw-r-----. 1 oracle
oinstall 458104832 Jun 14 20:34 SYSTEM_4r3s0ulp_155_1_1.bkp
-rw-r-----. 1 oracle oinstall 68681728 Jun 14
20:34 TUNER_DATA1_503s0uoi_160_1_1.bkp
-rw-r-----.
1 oracle oinstall 13606912 Jun 14 20:34 TUNER_IDX1_513s0uoq_161_1_1.bkp
-rw-r-----. 1 oracle oinstall 1253376 Jun
14 20:34 UNDOTBS1_4t3s0uob_157_1_1.bkp
-rw-r-----.
1 oracle oinstall 1138688 Jun 14 20:34
UNDOTBS2_4v3s0uof_159_1_1.bkp
-rw-r-----. 1 oracle
oinstall 1245184 Jun 14 20:34
USERS_4u3s0uod_158_1_1.bkp
./autobackup:
total 78728
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 14 20:33 .
drwxr-xr-x. 6 oracle oinstall 4096
Jun 14 21:31 ..
-rw-r-----. 1 oracle oinstall 20152320
Jun 14 20:33 c-1824822448-20250614-03
-rw-r-----. 1
oracle oinstall 20152320 Jun 14 20:33 c-1824822448-20250614-04
-rw-r-----. 1 oracle oinstall 20152320 Jun 14 20:33
c-1824822448-20250614-05
-rw-r-----. 1 oracle oinstall
20152320 Jun 14 20:33 c-1824822448-20250614-06
./ORA19R:
total 12
drwxr-xr-x. 5 oracle
oinstall 53 Jun 14 22:08 .
drwxr-xr-x. 6
oracle oinstall 4096 Jun 14 21:31 ..
drwxr-xr-x. 2
oracle oinstall 4096 Jun 14 22:43 ARCH
drwxr-x---. 2
oracle asmadmin 56 Jun 14 22:05 controlfile
drwxr-xr-x. 2 oracle oinstall 4096 Jun 14 22:43
datafile
./ORA19R/ARCH:
total 11464
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 14 22:43 .
drwxr-xr-x. 5 oracle oinstall
53 Jun 14 22:08 ..
-rw-r-----. 1 oracle
asmadmin 17920 Jun 14 22:38 1_154_1202820019.dbf
-rw-r-----. 1 oracle asmadmin 6094336 Jun 14 22:38
1_155_1202820019.dbf
-rw-r-----. 1 oracle
asmadmin 1024 Jun 14 22:43 2_1_1203806598.dbf
-rw-r-----. 1 oracle asmadmin 4096 Jun 14
22:38 2_78_1202820019.dbf
-rw-r-----. 1 oracle asmadmin
5608960 Jun 14 22:38 2_79_1202820019.dbf
./ORA19R/controlfile:
total
39136
drwxr-x---. 2 oracle
asmadmin 56 Jun 14 22:05 .
drwxr-xr-x. 5 oracle
oinstall 53 Jun 14 22:08 ..
-rw-r-----. 1 oracle asmadmin 20037632 Jun 14 22:45
controlfile01.ctl
-rw-r-----. 1 oracle asmadmin 20037632
Jun 14 22:45 controlfile02.ctl
./ORA19R/datafile:
total 5249076
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 14 22:43 .
drwxr-xr-x. 5 oracle
oinstall 53 Jun 14 22:08 ..
-rw-r-----. 1 oracle asmadmin 35659776 Jun 14
22:43 o1_mf_temp_n4tz44n4_.tmp
-rw-r-----. 1 oracle
asmadmin 67117056 Jun 14 22:43 o1_mf_tuner_te_n4tz44o6_.tmp
-rw-r-----. 1 oracle asmadmin 1939873792 Jun 14 22:43
sysaux.265.1202825873
-rw-r-----. 1 oracle asmadmin
1205870592 Jun 14 22:43 system.264.1202825797
-rw-r-----. 1 oracle asmadmin 641736704 Jun 14 22:43
tuner_data1.271.1203796157
-rw-r-----. 1 oracle
asmadmin 641736704 Jun 14 22:43 tuner_idx1.272.1203796161
-rw-r-----. 1 oracle asmadmin 865083392 Jun 14 22:43
undotbs1.266.1202826029
-rw-r-----. 1 oracle
asmadmin 78651392 Jun 14 22:43
undotbs2.267.1202826057
./redolog_1:
total 1228832
drwxr-xr-x. 3 oracle
oinstall 4096 Jun 14 22:43 .
drwxr-xr-x. 6 oracle oinstall
4096 Jun 14 21:31 ..
-rw-r-----. 1 oracle asmadmin
209715712 Jun 14 22:44 group_1.258.1202820019
-rw-r-----. 1 oracle asmadmin 209715712 Jun 14 22:43
group_2.257.1202820019
-rw-r-----. 1 oracle asmadmin
209715712 Jun 14 22:43 group_3.259.1202820019
-rw-r-----. 1 oracle asmadmin 209715712 Jun 14 22:43
group_4.260.1202820853
-rw-r-----. 1 oracle asmadmin
209715712 Jun 14 22:43 group_5.261.1202820855
-rw-r-----. 1 oracle asmadmin 209715712 Jun 14 22:43
group_6.262.1202820857
drwxr-x---. 3 oracle
asmadmin 23 Jun 14 22:43
ORA19R
./redolog_1/ORA19R:
total 4
drwxr-x---. 3 oracle asmadmin 23 Jun 14 22:43
.
drwxr-xr-x. 3 oracle oinstall 4096 Jun 14 22:43 ..
drwxr-x---. 2 oracle asmadmin 6 Jun 14
22:43 onlinelog
./redolog_1/ORA19R/onlinelog:
total
0
drwxr-x---. 2 oracle asmadmin 6 Jun 14 22:43
.
drwxr-x---. 3 oracle asmadmin 23 Jun 14 22:43
..
./redolog_2:
total 1228832
drwxr-xr-x. 3 oracle
oinstall 4096 Jun 14 22:43 .
drwxr-xr-x. 6 oracle oinstall
4096 Jun 14 21:31 ..
-rw-r-----. 1 oracle asmadmin
209715712 Jun 14 22:44 group_1.263.1202820021
-rw-r-----. 1 oracle asmadmin 209715712 Jun 14 22:43
group_2.262.1202820021
-rw-r-----. 1 oracle asmadmin
209715712 Jun 14 22:43 group_3.264.1202820021
-rw-r-----. 1 oracle asmadmin 209715712 Jun 14 22:43
group_4.267.1202820853
-rw-r-----. 1 oracle asmadmin
209715712 Jun 14 22:43 group_5.268.1202820855
-rw-r-----. 1 oracle asmadmin 209715712 Jun 14 22:43
group_6.269.1202820857
drwxr-x---. 3 oracle
asmadmin 23 Jun 14 22:43
ORA19R
./redolog_2/ORA19R:
total 4
drwxr-x---. 3 oracle asmadmin 23 Jun 14 22:43
.
drwxr-xr-x. 3 oracle oinstall 4096 Jun 14 22:43 ..
drwxr-x---. 2 oracle asmadmin 6 Jun 14
22:43 onlinelog
./redolog_2/ORA19R/onlinelog:
total
0
drwxr-x---. 2 oracle asmadmin 6 Jun 14 22:43
.
drwxr-x---. 3 oracle asmadmin 23 Jun 14 22:43 ..
21. 리스너를 잘 인식했는지
확인
소스서버에서 PFILE을 가져온 후 그 PFILE을 타켓 DB용으로 수정 시 LOCAL_LISTENER 파라미터를 수정했으므로 타켓 서버의 LISTENER_ORA19R 리스너스 타켓 서버 ORA19R DB를 인식하게됨!
만약 인식 못하면 타켓 DB에서 (ALTER SYSTEM REGISTER; 명령 한번 쳐볼 것)
[+ASM:grid@tdb01t][/home/grid]$ lsnrctl status LISTENER_ORA19R
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 14-JUN-2025 23:59:05
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_ORA19R)))
STATUS of the LISTENER
------------------------
Alias
LISTENER_ORA19R
Version
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start
Date
14-JUN-2025 21:08:13
Uptime
0 days 2 hr. 50 min. 52 sec
Trace
Level
off
Security
ON: Local OS Authentication
SNMP
OFF
Listener Parameter File
/u01/app/19c/grid/network/admin/listener.ora
Listener
Log File
/u01/app/oracle/diag/tnslsnr/tdb01t/listener_ora19r/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_ORA19R)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1525)))
Services Summary...
Service
"ORA19R" has 1 instance(s).
Instance "ORA19R",
status READY, has 1 handler(s) for this service...
Service "ORA19RXDB" has 1 instance(s).
Instance "ORA19R", status READY, has 1 handler(s)
for this service...
The command completed
successfully
22. 복구한
tuner.tb_cust 테이블 export하기
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/datapump
[2025-06-14:22:48:29][tdb01t]<ORA19R@SYS> create directory tuner_datapump_dir as '/home/oracle/ORA19R_BACKUP/datapump';
Directory created.
Elapsed: 00:00:00.03
[2025-06-14:22:48:29][tdb01t]<ORA19R@SYS>
grant read, write on directory tuner_datapump_dir to
tuner;
Grant succeeded.
Elapsed: 00:00:00.01
[2025-06-14:22:48:29][tdb01t]<ORA19R@SYS> quit
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ expdp tuner/oracle tables='TB_CUST' directory=tuner_datapump_dir dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust.log
Export: Release
19.0.0.0.0 - Production on Sat Jun 14 22:49:54 2025
Version 19.26.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 "TUNER"."SYS_EXPORT_TABLE_01":
tuner/********/ tables=TB_CUST directory=tuner_datapump_dir
dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object
type TABLE_EXPORT/TABLE/TABLE
Processing object type
TABLE_EXPORT/TABLE/COMMENT
Processing object type
TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type
TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported
"TUNER"."TB_CUST"
123.5 MB 1000000 rows
Master table
"TUNER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TUNER.SYS_EXPORT_TABLE_01 is:
/home/oracle/ORA19R_BACKUP/datapump/tuner_tb_cust.dmp
Job "TUNER"."SYS_EXPORT_TABLE_01" successfully completed at
Sat Jun 14 22:50:08 2025 elapsed 0 00:00:13
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Jun 14 22:51:29 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.26.0.0.0
[2025-06-14:22:51:29][tdb01t]<ORA19R@SYS> drop directory tuner_datapump_dir;
Directory dropped.
Elapsed: 00:00:00.04
23. 타켓에서 export 한 tuner.tb_cust dmp파일을 소스에서 import
하기
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
mkdir -p /home/oracle/ORA19R_BACKUP/datapump
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
scp 192.168.0.51:/home/oracle/ORA19R_BACKUP/datapump/*
/home/oracle/ORA19R_BACKUP/datapump
The authenticity of host '192.168.0.51 (192.168.0.51)'
can't be established.
ECDSA key fingerprint is
SHA256:SD9DHxnpCyTQlmxqRXE3GoBJzHgFjUtNup2VbhRlRlY.
ECDSA key fingerprint is
MD5:5c:25:1b:61:37:91:27:49:ae:9a:64:ee:5b:ea:6c:ce.
Are
you sure you want to continue connecting (yes/no)? yes
Warning:
Permanently added '192.168.0.51' (ECDSA) to the list of known hosts.
oracle@192.168.0.51''s password:
tuner_tb_cust.dmp
100% 124MB 33.7MB/s 00:03
tuner_tb_cust.log 100%
1329 261.8KB/s 00:00
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Jun 14 22:58:15 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[2025-06-14:22:58:15][ol7ora19r1]<SYS@ORA19R1> create directory tuner_datapump_dir as '/home/oracle/ORA19R_BACKUP/datapump';
Directory created.
Elapsed: 00:00:00.17
[2025-06-14:22:58:15][ol7ora19r1]<SYS@ORA19R1> grant read, write on directory tuner_datapump_dir to
tuner;
Grant succeeded.
Elapsed: 00:00:00.08
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ impdp tuner/oracle tables='TB_CUST' directory=tuner_datapump_dir dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust_import.log
Import: Release
19.0.0.0.0 - Production on Sat Jun 14 23:00:02 2025
Version 19.27.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 "TUNER"."SYS_IMPORT_TABLE_01" successfully
loaded/unloaded
Starting
"TUNER"."SYS_IMPORT_TABLE_01": tuner/******** tables=TB_CUST
directory=tuner_datapump_dir dumpfile=tuner_tb_cust.dmp
logfile=tuner_tb_cust_import.log
Processing object type
TABLE_EXPORT/TABLE/TABLE
Processing object type
TABLE_EXPORT/TABLE/TABLE_DATA
. . imported
"TUNER"."TB_CUST"
123.5 MB 1000000 rows
Processing object type
TABLE_EXPORT/TABLE/COMMENT
Processing object type
TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type
TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing
object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job
"TUNER"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jun 14 23:01:12 2025
elapsed 0 00:00:54
24. 최종적으로 소스 서버에서
TUNER.TB_CUST 테이블이 보이는 지 확인!
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
alias st
alias st='rlwrap sqlplus
tuner/oracle'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
st
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Jun 14 23:02:04 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Sat Jun 14 2025 23:00:02 +09:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.27.0.0.0
[2025-06-14:23:02:05][ol7ora19r1]<TUNER@ORA19R1> select count(*) from tuner.tb_cust;
COUNT(*)
----------
1000000
1 row selected.
Elapsed: 00:00:01.50
--> tuner.tb_cust 테이블 복구
성공
[2025-06-14:23:57:56][ol7ora19r1]<SYS@ORA19R1> drop directory tuner_datapump_dir;
Directory dropped.
Elapsed: 00:00:00.27
소스 서버에서 TUNER.TB_CUST 테이블이 DROP TABLE PURGE
됐을 때 당황하지 말고 해당 절차대로 수행하면 충분히 DROP PURGE 된 테이블을 복구해올 수
있음!