[제목]
Oracle 19c 2 Node RAC 환경에서 PDB 단위 Flashback Database 실습
[테스트
개요]
멀티테넌트 환경(CDB/PDB)에서 CDB 전체가 아니라 특정 PDB 단위로 Flashback Database를 수행할 수 있음을 검증함
[테스트
환경]
OS : Oracle
Linux Server 8.10 (grep ^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d
'"')
OS Kernal : 5.15.0-206.153.7.1.el8uek.x86_64 (uname -r)
Oracle
Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
(echo -e "set pages 0 lines 200 feedback off heading off\nselect banner from
v\$version where banner like 'Oracle Database%';" | sqlplus -s / as
sysdba)
Oracle Configuration
DB명 : ORA19RS (echo
-e 'set pages 0 feedback off heading off verify off\nselect '\''DB명 : '\''||name
from v$database;' | sqlplus -s / as sysdba) '
PDB명 : ORA19RSP1 (echo -e
"set pages 0 feedback off heading off verify off\nselect 'PDB명 : '||name from
v\$pdbs where name <> 'PDB\$SEED';" | sqlplus -s / as
sysdba)
RAC Node 1
Hostname : ol8ora19rs1
(hostname)
Public IP : 192.168.240.41 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA19RS1 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
RAC Node 2
Hostname :
ol8ora19rs2
Public IP : 192.168.240.42 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA19RS2 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
Patch Info
Grid (opatch lspatches) (grid os
user)
38124772;TOMCAT RELEASE UPDATE 19.0.0.0.0
(38124772)
37962946;OCW RELEASE UPDATE 19.28.0.0.0
(37962946)
37962938;ACFS RELEASE UPDATE 19.28.0.0.0
(37962938)
37960098;Database Release Update : 19.28.0.0.250715
(37960098)
36758186;DBWLM RELEASE UPDATE 19.0.0.0.0
(36758186)
Oracle (opatch lspatches) (oracle os
user)
37962946;OCW RELEASE UPDATE 19.28.0.0.0
(37962946)
37960098;Database Release Update : 19.28.0.0.250715
(37960098)
[내용]
1. PDB가 Local Undo 인지 확인
--> 본 테스트는 PDB가 Local UNDO인 상태를 기준으로 진행함
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Oct 12 19:52:24 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RSP1
READ WRITE NO
[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set container=ORA19RSP1;
Session altered.
Elapsed: 00:00:00.00
select * from database_properties where property_name='LOCAL_UNDO_ENABLED';
|#|PROPERTY_NAME
|PROPERTY_VALUE|DESCRIPTION
|
+-+------------------+--------------+-----------------------------+
|1|LOCAL_UNDO_ENABLED|TRUE
|true if local undo is enabled|
2. Flashback Database 상태인지 확인 (CDB에서 확인)
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Oct 12 19:57:47 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$ show parameter db_recovery
NAME
TYPE
VALUE
------------------------------------ -----------
------------------------------
db_recovery_file_dest
string
+FRA1
db_recovery_file_dest_size
big integer 32767M
[ol8ora19rs1]<SYS@ORA19RS1>$ archive log
list;
Database log
mode
Archive Mode
Automatic
archival
Enabled
Archive
destination
+FRA1
Oldest online log sequence 24
Next log
sequence to archive 26
Current log
sequence
26
[ol8ora19rs1]<SYS@ORA19RS1>$ select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
--> 현재 Flashback Datbase가 OFF인 상황
1 row selected.
Elapsed: 00:00:00.00
3.
Flashback Database 설정 (CDB 단위로 설정해야함)
[ol8ora19rs1]<SYS@ORA19RS1>$ alter database flashback on;
Database altered.
Elapsed: 00:00:06.48
[ol8ora19rs1]<SYS@ORA19RS1>$ select FLASHBACK_ON from
v$database;
FLASHBACK_ON
------------------
YES
--> db_recovery_file_dest 위치인 +FRA1 에 Flashback Database Log가
쌓이게 됨
1 row
selected.
Elapsed: 00:00:00.00
4.
PDB내에 restore point 생성
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Oct 12 20:04:38 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RSP1
READ WRITE NO
[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set
container=ORA19RSP1;
Session altered.
Elapsed:
00:00:00.00
[ol8ora19rs1]<SYS@ORA19RS1>$ select count(*) from tuner.tb_cust;
COUNT(*)
----------
10000
--> tuner.tb_cust 테이블에 데이터가 존재하는 상황
1 row selected.
Elapsed: 00:00:00.02
[ol8ora19rs1]<SYS@ORA19RS1>$ create restore point STAGE1 guarantee
flashback database;
-->
ORA19RSP1 PDB내에 Restore Point 생성함
Restore point created.
Elapsed: 00:00:00.02
[ol8ora19rs1]<SYS@ORA19RS1>$
SELECT
a.scn, a.GUARANTEE_FLASHBACK_DATABASE, a.time, a.name, a.PDB_RESTORE_POINT,
a.CON_ID
, (select l.name from "V_$PDBS" l where
l.CON_ID = a.CON_ID) as pdb_name
FROM V$RESTORE_POINT a
;
|SCN
|GUARANTEE_FLASHBACK_DATABASE|TIME
|NAME |PDB_RESTORE_POINT|CON_ID|PDB_NAME
|
+-------+----------------------------+-------------------+------+-----------------+------+---------+
|3183790|YES
|2025-10-12
20:06:57|STAGE1|YES
| 3|ORA19RSP1|
--> PDB에
생성한 Restore Point 확인
5. PDB내에서 tuner.tb_cust 테이블을
truncate
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Oct 12 20:11:36 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected
to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set container=ORA19RSP1;
Session altered.
Elapsed: 00:00:00.01
[ol8ora19rs1]<SYS@ORA19RS1>$ truncate table
tuner.tb_cust;
-->
tuner.tb_cust 테이블이 truncate되는 사고가 발생함!!!
Table truncated.
Elapsed: 00:00:00.13
[ol8ora19rs1]<SYS@ORA19RS1>$ select count(*) from tuner.tb_cust;
COUNT(*)
----------
0
1 row selected.
Elapsed: 00:00:00.01
6. PDB를 Restore Point로 되돌림
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Oct 12 20:13:05 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RSP1
READ WRITE NO
[ol8ora19rs1]<SYS@ORA19RS1>$ alter pluggable database ORA19RSP1
close immediate;
-->
PDB를 Close함 (PDB가 Mounted 상태로 되는 것임)
Pluggable database altered.
Elapsed:
00:00:00.28
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RSP1
MOUNTED (NULL)
--PDB를 내린 상태에서 STAGE1 지점으로
되돌림
[ol8ora19rs1]<SYS@ORA19RS1>$ flashback pluggable database ORA19RSP1 to restore point STAGE1;
Flashback complete.
Elapsed: 00:00:00.29
[ol8ora19rs1]<SYS@ORA19RS1>$ alter pluggable database ORA19RSP1
open;
alter pluggable database ORA19RSP1
open
*
ERROR at line 1:
ORA-01113: file 16 needs media
recovery
ORA-01110: data file 16:
'+DATA1/ORA19RS/40BB61DA40640933E0632AF0A8C05571/DATAFILE/tuner_idx1.283.1214210175'
--> PDB가 바로 오픈되지 않음
Elapsed: 00:00:00.21
--Resetlogs 로 PDB로 Open시킴
[ol8ora19rs1]<SYS@ORA19RS1>$ alter pluggable database ORA19RSP1
open resetlogs;
-->
CDB는 영향을 주지 않고 해당 PDB만 내부적으로 새로 SCN이 시작되는 것임!
Pluggable database altered.
Elapsed:
00:00:01.45
[ol8ora19rs1]<SYS@ORA19RS1>$
7. PDB 내 데이터 복구 확인
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Oct 12 20:20:39 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RSP1
READ WRITE NO
[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set container=ORA19RSP1;
Session altered.
Elapsed:
00:00:00.02
[ol8ora19rs1]<SYS@ORA19RS1>$ select count(*) from tuner.tb_cust;
COUNT(*)
----------
10000
1 row selected.
Elapsed: 00:00:00.03
8. 테스트 후 restore point drop 및 flashback database 설정 해제
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Oct 12 20:23:44 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected
to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set container=ORA19RSP1;
Session altered.
Elapsed: 00:00:00.00
[ol8ora19rs1]<SYS@ORA19RS1>$ drop restore point
STAGE1;
Restore point dropped.
Elapsed: 00:00:00.02
--flashback database off 설정은
CDB에서 해야함
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Oct 12 20:24:46 2025
Version
19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.28.0.0.0
Elapsed: 00:00:00.02
[ol8ora19rs1]<SYS@ORA19RS1>$ alter database flashback
off;
Database altered.
Elapsed: 00:00:01.28
[ol8ora19rs1]<SYS@ORA19RS1>$ select FLASHBACK_ON from
v$database;
FLASHBACK_ON
------------------
NO
--> 본 테스트 시작 전 상태로 원상 복구 시킴
1 row selected.
Elapsed: 00:00:00.00