[2025-04-30] RAC to Single ADG 수동환경에서 Snapshot Standby 전환
Snapshot Standby는 Physical Standby DB를 일시적으로 읽기/쓰기 가능한 테스트 DB로 전환할 수 있는 기능
전환 중 발생한 모든 변경 사항은 Flashback Database를 통해 FRA에 저장되며, 원본 데이터에는 영향을 주지 않음
필요 시 언제든지 Physical Standby로 되돌려 Data Guard 동기화를 재개할 수 있음
[테스트 환경]
primary(source) : rdb01d(192.168.0.31) 서버의 DRDB1, rdb02d(192.168.0.32)
서버의 DRDB2
standby(target) : tdb01p(192.168.0.41) 서버의 DRDB
[테스트 개요]
대상 환경은 RAC to Single ADG 수동 구성에서 Standby DB를 Snapshot Standby로 전환하는 시나리오임.
Snapshot Standby 전환 후 DDL/DML 수행 가능 여부와 Flashback Restore Point 생성을 확인.
다시 Physical Standby로 전환 후 Redo Apply 정상 동작 및 소스 DB와 동기화 여부를 검증.
--타켓에서 작업
--현재 tdb01p 서버에는 db unique name 기준 PTDB와 DRDB_STB가 있음 (DRDB_STB가 Standby DB임)
[PTDB:oracle@tdb01p][/home/oracle]$ cat
.bash_profile
set_drdb_stb() {
export ORACLE_UNQNAME=DRDB_STB
export
ORACLE_UNQNAME_LOWER=drdb_stb
export ORACLE_SID=DRDB
export
ORACLE_SID_LOWER=drdb
export ORACLE_DBNAME=DRDB
export
ORACLE_DBNAME_LOWER=drdb
}
[PTDB:oracle@tdb01p][/home/oracle]$
set_drdb_stb
[DRDB:oracle@tdb01p][/home/oracle]$ ss --> alias임 sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 24 22:03:00 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
READ
ONLY WITH APPLY
1 row selected.
Elapsed: 00:00:00.04
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
PHYSICAL
STANDBY
--우선 FlashBack Database 켜기
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> shutdown
immediate;
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> startup
mount;
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> show parameter db_recovery_file_dest
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
db_recovery_file_dest
string
(NULL)
db_recovery_file_dest_size
big
integer
0
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> alter system set db_recovery_file_dest_size=20G
scope=both;
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> alter system set db_recovery_file_dest='+FRA1'
scope=both;
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> show parameter db_recovery_file_dest
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
db_recovery_file_dest
string
+FRA1
db_recovery_file_dest_size
big
integer
20G
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> alter database flashback on;
Database altered.
Elapsed: 00:00:03.45
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------
YES
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select status from v$instance;
STATUS
------------------------------------
MOUNTED
--snapshot standby 로 전환
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> alter database convert to snapshot
standby;
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> alter database
open;
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
SNAPSHOT
STANDBY
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
READ
WRITE
--복구 시점이 생긴걸 알 수 있음
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB>
col name
for a50
col GUARANTEE_FLASHBACK_DATABASE for a30
select
NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
NAME
GUARANTEE_FLASHBACK_DATABASE
--------------------------------------------------
------------------------------
SNAPSHOT_STANDBY_REQUIRED_04/24/2025
22:08:57 YES
--DDL 및 DML 테스트
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> create table tuner.tb_snapshot_standby_test_250424 (col1 number);
Table created.
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> insert into tuner.tb_snapshot_standby_test_250424 values(1);
1 row created.
Elapsed: 00:00:07.96
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> commit;
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select count(*) from tuner.tb_snapshot_standby_test_250424;
COUNT(*)
----------
1
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------------------------------------------
YES
1 row selected.
Elapsed: 00:00:00.02
Snapshot Standby로 작업하다가 FRA 공간 부족 시 동작
Snapshot Standby는 Flashback Database를 기반으로 하기 때문에 모든 변경 사항은 FRA(Flash Recovery Area)에 저장되는 Flashback 로그로 관리됨.
FRA가 꽉 차면 새로운 Flashback 로그를 쓸 수 없어 트랜잭션이 실패하거나 DB가 hang/block 상태로 빠질 수 있음.
따라서 Snapshot Standby 운용 시 반드시 충분한 FRA 용량을 확보하거나, db_flashback_retention_target/db_recovery_file_dest_size 조정 및 모니터링을 필수적으로 수행해야 함.
즉, FRA가 꽉 차면 더 이상 트랜잭션을 처리할 수 없고 정상적인
Snapshot Standby 활용이 불가능
--다시 PHYSICAL STANDBY로 변경
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> alter database convert to physical standby;
Database altered.
Elapsed: 00:00:04.80
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
PHYSICAL
STANDBY
1 row selected.
Elapsed: 00:00:00.02
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select status from v$instance;
STATUS
------------------------------------
MOUNTED
1 row selected.
Elapsed: 00:00:00.00
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> shut immediate
ORA-01109: database not
open
Database dismounted.
ORACLE instance shut
down.
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> startup
ORACLE instance
started.
Total
System Global Area 805306368 bytes
Fixed
Size
8625856 bytes
Variable
Size
348127552 bytes
Database
Buffers 440401920
bytes
Redo
Buffers
8151040 bytes
Database mounted.
Database opened.
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
Elapsed:
00:00:06.75
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
READ
ONLY WITH APPLY
1 row selected.
Elapsed:
00:00:00.08
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
PHYSICAL
STANDBY
[2025-04-24:22:03:00][tdb01p]<SYS@DRDB> select count(*) from
tuner.tb_snapshot_standby_test_250424;
select count(*) from
tuner.tb_snapshot_standby_test_250424
*
ERROR at line 1:
ORA-00942: table or view does not
exist
Elapsed: 00:00:00.02
--동기화 테스트
--소스
create table tuner.TB_ADG_TEST_250424
(
col1 number
);
insert into tuner.TB_ADG_TEST_250424 values (1);
commit;
--타켓
SQL> select count(*) from
tuner.TB_ADG_TEST_250424;
COUNT(*)
---------
1