[ORACLE] ADG : Cascaded Redo Transport Destinations

ADG의 Cascade Redo 전송 = 계단식 = terminal destination

방식은 Primary Database에서 Redo를 직접 수신하는 것이 아닌 Standby Database에서 간접적으로 수신하는 방식

primary -> standby 1 -> standby 2

 

이 방식은 Primary에서 Standby1로 Redo 적용과 관련된 I/O가 오프로드 되기 때문에

primary -> standby 1,2 방식보다 Primary에서 오버헤드를 줄일 수 있음

 

구성

# primary CONFIGURATION
DB_UNIQUE_NAME=PRMR
FAL_SERVER=STBY1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMR,STBY1,STBY2)'
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST VALID_FOR=(ALL_LOGFILES,A
                                                 LL_ROLES) DB_UNIQUE_NAME=PRMR
                                                 
log_archive_dest_2                   string      SERVICE=STBY1 LGWR ASYNC VALID
                                                 _FOR=(standby_logfiles,standby
                                                 _role) DB_UNIQUE_NAME=STBY1




# standby 1 CONFIGURATION
DB_UNIQUE_NAME=STBY1
FAL_SERVER=PRMR
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMR,STBY1,STBY2)'
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST VALID_FOR=(ALL_LOGFILES,A
                                                 LL_ROLES) DB_UNIQUE_NAME=STBY1
                                                 
log_archive_dest_2                   string      SERVICE=STBY2 LGWR ASYNC VALID
                                                 _FOR=(standby_logfiles,standby
                                                 _role) DB_UNIQUE_NAME=STBY2

# TNSNAMES
PRMR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ug19-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRMR)
    )
  )

STBY1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.111)(PORT = 1537))
                (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.112)(PORT = 1537))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY1)
    )
  )
STBY2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.111)(PORT = 1637))
                (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.112)(PORT = 1637))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY2)
    )
  )

# standby 2 DB parameter file 생성
vi $ORACLE_HOME/dbs/initSTBY2.ora

*.control_files='+DATA'
*.audit_file_dest='/u01/app/oracle/admin/STBY2/adump'
*.audit_trail='db'
*.db_name='PRMR'
*.db_unique_name='STBY2'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=8451m
*.fal_client='STBY2'
*.fal_server='STBY1'
*.log_archive_config='dg_config=(PRMR,STBY1,STBY2)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY2'
*.standby_file_management='AUTO'

 

# Standby 2 (cascade) db 생성
1. NOMOUNT
startup nomount pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initSTBY2.ora' 

2. ACTIVE DATAGUARD DUPLICATE
rman target /
connect target sys/welcome1@PRMR
connect auxiliary sys/welcome1@STBY2

run{
allocate channel prmy1 type disk ;
allocate channel prmy2 type disk ;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
duplicate target database for standby from active database nofilenamecheck;
release channel prmy1;
release channel prmy2;
release channel stby1;
release channel stby2;
}

-- primary & standby1
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';

-- standby 2
sqlplus / as sysdba

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      PRMR
SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      STBY2

 

STBY2 복구 및 archive 적용

recover standby database ;


-- archive 먹이기
ORA-00279: change 3745108 generated at  needed for thread 1
ORA-00289: suggestion : +RECO
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'STBY2'
ORA-00280: change 3745108 for thread 1 is in sequence #227

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

##########################################################
# ---------------> PRMR에서 ARCHIVE 조회
# select thread#, FIRST_CHANGE#, NEXT_CHANGE#, name
# from v$archived_log
# where 3745108 between FIRST_CHANGE# and NEXT_CHANGE# ;
##########################################################

+RECO/PRMR/ARCHIVELOG/2023_07_21/thread_1_seq_227.530.1142766493


ORA-00279: change 3745108 generated at  needed for thread 2
ORA-00289: suggestion : +RECO
ORA-00280: change 3745108 for thread 2 is in sequence #109

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+RECO/PRMR/ARCHIVELOG/2023_07_21/thread_2_seq_109.591.1142766493

ORA-00279: change 3746363 generated at 07/21/2023 11:08:13 needed for thread 1
ORA-00289: suggestion : +RECO
ORA-00280: change 3746363 for thread 1 is in sequence #228
ORA-00278: log file
'+RECO/PRMR/ARCHIVELOG/2023_07_21/thread_1_seq_227.530.1142766493' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.


--복구수행
alter database recover managed standby database disconnect;

--동기화종료
alter database recover managed standby database cancel;

# 실시간 동기화 시작
alter database open ;
alter database recover managed standby database disconnect;



SQL> select database_role, open_mode from v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

### 동기화 체크 1. gap차이 (VALUE) 조회
set line 200 pages 1000
col NAME for a30
col VALUE for a30
col UNIT for a30
col TIME_COMPUTED for a25
select NAME,VALUE,UNIT,TIME_COMPUTED from v$dataguard_stats;


### 동기화 체크 2. mrp 조회
select process, status, THREAD#,sequence#, block#, blocks, delay_mins 
from v$managed_standby;