내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
OS환경 : Oracle Linux6.8(64bit)
DB 환경 : Oracle Database 12.2.0.1
에러 :
오라클 데이터가드 구성 후 log switch 시 아래 메세지 발생
RFS[4]: No standby redo logfiles available for T-1
노드1
SQL>alter system archive log current;
@alert log
2018-06-15T23:24:14.577882+09:00
ALTER SYSTEM ARCHIVE LOG
2018-06-15T23:24:14.593743+09:00
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log# 1 seq# 7 mem# 0: /app/oracle/oradata/adg1/redo01.log
2018-06-15T23:24:14.601500+09:00
Archived Log entry 8 added for T-1.S-6 ID 0x4fcbb42f LAD:1
노드2
@alert log
2018-06-15T23:24:14.721742+09:00
Archived Log entry 3 added for thread 1 sequence 6 rlc 978907375 ID 0x4fcbb42f LAD2 :
2018-06-15T23:24:14.847500+09:00
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process (PID:62869)
RFS[4]: No standby redo logfiles available for T-1
RFS[4]: Opened log for T-1.S-7 dbid 1338746415 branch 978907375
해결 방법 :
Oracle Doc에 비슷한 메세지 트러블슈팅 방법이 있어서 확인해보니
A.8 Log Files Must Match to Avoid Primary Database Shutdown
For example, if the primary database uses two online redo log groups whose log files are 100K, then the standby database should have 3 standby redo log groups with log file sizes of 100K.
이런 문구가 있었다.
=> 노드1(엑티브노드)와 노드2(스탠바이노드)의 Redo log file size 및 갯수가 같아야한다는 것이다.
이점은 잊고 노드 2에서 standby redo 를 추가 할 때 용량을 다르게 줘서 alert log에 저런 메세지가 발생한 것이다.
redo size 확인
노드1
SQL> select bytes/1048576 mb from v$log;
MB
----------
200
200
200
노드2
SQL> select bytes/1048576 mb from v$standby_log
MB
----------
500
500
500
redo size 를 동일하게 변경
새로운 리두 thread 1 로 추가(redo size를 active 노드의 redo size와 동일하게 설정)
노드2
SQL> alter database add standby logfile thread 1 '/app/oracle/oradata/adg2/standbylog04.log' size 209715200;
SQL> alter database add standby logfile thread 1 '/app/oracle/oradata/adg2/standbylog05.log' size 209715200;
SQL> alter database add standby logfile thread 1 '/app/oracle/oradata/adg2/standbylog06.log' size 209715200;
기존 size 상이한 redo 제거
SQL> alter database drop standby logfile '/app/oracle/oradata/adg2/standbylog01.log';
SQL> alter database drop standby logfile '/app/oracle/oradata/adg2/standbylog02.log';
SQL> alter database drop standby logfile '/app/oracle/oradata/adg2/standbylog03.log';
로그 확인
노드1
@alert log
2018-06-15T23:50:57.814026+09:00
ALTER SYSTEM ARCHIVE LOG
2018-06-15T23:50:57.828998+09:00
Thread 1 advanced to log sequence 13 (LGWR switch)
Current log# 1 seq# 13 mem# 0: /app/oracle/oradata/adg1/redo01.log
2018-06-15T23:50:57.831474+09:00
Archived Log entry 20 added for T-1.S-12 ID 0x4fcbb42f LAD:1
2018-06-15T23:50:57.954567+09:00
TT02: Standby redo logfile selected for thread 1 sequence 13 for destination LOG_ARCHIVE_DEST_2
노드2
@alert log
2018-06-15T23:50:57.967361+09:00
Archived Log entry 9 added for T-1.S-12 ID 0x4fcbb42f LAD:1
2018-06-15T23:50:57.984976+09:00
RFS[4]: Selected log 7 for T-1.S-13 dbid 1338746415 branch 978907375
thread 1에 sequence 13 두 노드 모두 동일한 것을 확인할 수 있다.
원인 : 노드1(엑티브노드)와 노드2(스탠바이노드)의 Redo log file size가 달라서 발생한 메세지
참조 : https://docs.oracle.com/cd/B19306_01/server.102/b14239/troubleshooting.htm#i639505