오라클 11g R2 ADG Gap 발생 시나리오1

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4 Active Data Guard

방법 : 오라클 11g R2 ADG Gap 발생 시나리오1

오라클 11g R2 ADG 환경에서 Gap 이 발생하는 상황을 만들어보고 복구해봄

1번 노드(Primary) hostname : adg1, db_name : adg11g, db_unique_name : adg11g

2번 노드(Standby) hostname : adg2, db_name : adg11g, db_unique_name : adg11gsb

1. db_files 파라미터 상이

1번 노드와 2번 노드의 파라미터파일의 db_files 파라미터 값이 다른 경우(각각 5, 4)

1번 노드에 데이터파일 5번까지 만들었을 때 2번노드에 어떤 현상이 발생하는지, Gap 등 확인

파라미터 변경(1번, 2번 노드)

1번 노드
SQL> alter system set db_files = 5 scope=spfile;
 
System altered.
 
2번 노드
SQL> alter system set db_files = 4 scope=spfile;
 
System altered.

1번 노드는 5으로 2번 노드는 4로 설정

재기동(1번, 2번 노드)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size         1124076584 bytes
Database Buffers      520093696 bytes
Redo Buffers            7094272 bytes
Database mounted.
Database opened.

파라미터 확인

1번 노드
SQL> show parameter db_files
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_files                 integer     5
 
2번 노드
SQL> show parameter db_files
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_files                 integer     4

1번 노드는 5으로 2번 노드는 4로 설정됨

현재 데이터파일 확인(1번, 2번 노드)

SQL> 
set lines 200 pages 1000 
col file_name for a60
select file_id, tablespace_name, file_name from dba_data_files order by 1;
 
FILE_ID   TABLESPACE_NAME      FILE_NAME
----------  ---------------- ---------------------------
     1     SYSTEM              /app/oracle/oradata/adg11g/system01.dbf
     2     SYSAUX              /app/oracle/oradata/adg11g/sysaux01.dbf
     3     UNDOTBS1            /app/oracle/oradata/adg11g/undotbs01.dbf
     4     USERS              /app/oracle/oradata/adg11g/users01.dbf

4개가 존재함

데이터파일 1개 추가(1번 노드)

SQL> alter tablespcae users add datafile '/app/oracle/oradata/adg11g/users02.dbf' size 5m;
 
Tablespace altered.

alert log 확인(1번, 2번 노드)

1번 노드
Mon Jan 18 16:47:21 2021
alter tablespace users add datafile '/app/oracle/oradata/adg11g/users02.dbf' size 5m
Completed: alter tablespace users add datafile '/app/oracle/oradata/adg11g/users02.dbf' size 5m
 
2번 노드
Mon Jan 18 16:47:21 2021
MRP0: Background Media Recovery terminated with error 59
Errors in file /app/oracle/diag/rdbms/adg11gsb/adg11gsb/trace/adg11gsb_mrp0_8437.trc:
ORA-00059: maximum number of DB_FILES exceeded
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Standby recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 1271604 but controlfile is ahead at change 1271608.
Database remains open for continuous queries. Please continue recovery.
MRP0: Background Media Recovery process shutdown (adg11gsb)

1번 노드에서는 정상적으로 Completed 가 떨어졌지만

2번 노드에서는 ORA-00059 메세지가 발생하고 MRP 프로세스가 중지됨

샘플 테이블 및 데이터 삽입(1번 노드)

SQL> create table imsidata (nu number) tablespace users;
 
Table created.
 
SQL> insert into imsidata select object_id from dba_objects;
 
86263 rows created.
 
SQL> insert into imsidata select * from imsidata;
 
86263 rows created.
 
SQL> /
 
172526 rows created.
 
SQL> commit;
 
Commit complete.

로그 스위치 3번 실행(1번 노드)

SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.

데이터 파일 확인(1번, 2번 노드)

1번 노드
SQL> 
set lines 200 pages 1000 
col file_name for a60
select file_id, tablespace_name, file_name from dba_data_files order by 1;
 
   FILE_ID TABLESPACE_NAME          FILE_NAME
---------- ------------------------------ ------------------------------------------------------------
     1 SYSTEM              /app/oracle/oradata/adg11gsb/system01.dbf
     2 SYSAUX              /app/oracle/oradata/adg11gsb/sysaux01.dbf
     3 UNDOTBS1              /app/oracle/oradata/adg11gsb/undotbs01.dbf
     4 USERS              /app/oracle/oradata/adg11gsb/users01.dbf
     5 USERS              /app/oracle/oradata/adg11g/users02.dbf
 
2번 노드
SQL> 
set lines 200 pages 1000 
col file_name for a60
select file_id, tablespace_name, file_name from dba_data_files order by 1;
   FILE_ID TABLESPACE_NAME          FILE_NAME
---------- ------------------------------ ------------------------------------------------------------
     1 SYSTEM              /app/oracle/oradata/adg11gsb/system01.dbf
     2 SYSAUX              /app/oracle/oradata/adg11gsb/sysaux01.dbf
     3 UNDOTBS1              /app/oracle/oradata/adg11gsb/undotbs01.dbf
     4 USERS              /app/oracle/oradata/adg11gsb/users01.dbf

1번 노드에는 datafile 5번이 존재하지만 2번 노드에서는 보이지 않음

데이터 확인(1번, 2번 노드)

1번 노드
SQL> select count(*) from imsidata;
 
  COUNT(*)
----------
    345052
 
2번 노드
SQL> select count(*) from imsidata;
select count(*) from imsidata
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

1번 노드에서는 샘플테이블이 보이지만 2번 노드에서는 보이지 않음

샘플테이블2 생성 후 로그스위치(다른 ts)(1번 노드)

1번 노드
SQL> create table imsidata2 (nu number) tablespace system;
 
Table created.
 
SQL> insert into imsidata2 select object_id from dba_objects;
 
86264 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from imsidata2;
 
  COUNT(*)
----------
     86264
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
2번 노드
SQL> select count(*) from imsidata2;
select count(*) from imsidata2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

system tablespace 에 imsidata2 테이블 생성 후 2번노드에서 확인 시 보이지 않음

현재 users 테이블 스페이스 데이터 뿐만 아니라 다른 테이블 스페이스의 데이터도 동기화 되지 않음

2번 노드 MRP 프로세스가 중지되었기 때문

MRP 프로세스 기동(2번 노드)

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

MRP 프로세스 기동 명령 시 alert log(2번 노드)

Mon Jan 18 17:31:47 2021
ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session  
Attempt to start background Managed Standby Recovery process (adg11gsb)
Mon Jan 18 17:31:47 2021
MRP0 started with pid=33, OS id=8604 
MRP0: Background Managed Standby Recovery process started (adg11gsb)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /app/oracle/arch/adg11gsb/1_27_1059694915.arc
MRP0: Background Media Recovery terminated with error 59
Errors in file /app/oracle/diag/rdbms/adg11gsb/adg11gsb/trace/adg11gsb_mrp0_8604.trc:
ORA-00059: maximum number of DB_FILES exceeded
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Standby recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 1271604 but controlfile is ahead at change 1271608.
Database remains open for continuous queries. Please continue recovery.
MRP0: Background Media Recovery process shutdown (adg11gsb)
Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session  

MRP 프로세스가 기동되면서 복구를 하려다가 

ORA-00059 DB_FILES 문제 때문에 복구가 중단되고 MRP 프로세스가 다시 중지됨

브로커 show configuration 확인(1번 노드)

$ dgmgrl sys/oracle
DGMGRL> show configuration
 
Configuration - adg11g
 
  Protection Mode: MaxPerformance
  Databases:
    adg11g   - Primary database
    adg11gsb - Physical standby database
      Error: ORA-16766: Redo Apply is stopped
 
Fast-Start Failover: DISABLED
 
Configuration Status:
ERROR

2번 노드에 ORA-16766: Redo Apply is stopped 가 발생한 상태

브로커 show database 확인(1번 노드)

$ dgmgrl sys/oracle
DGMGRL> show database adg11gsb
 
Database - adg11gsb
 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       51 minutes 11 seconds (computed 0 seconds ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    adg11gsb
 
  Database Error(s):
    ORA-16766: Redo Apply is stopped
 
Database Status:
ERROR
 
DGMGRL> show database adg11g
 
Database - adg11g
 
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    adg11g
 
Database Status:
SUCCESS

adg11gsb(2번 노드) 는 현재 Apply Lag(적용 지연) 시간이 51분 11초, 

Apply Rate 가 (unknown)에 Database Status가 ERROR임

adg11g(1번 노드) 는 Database Status가 SUCCESS임

db_files 파라미터 정상화 후 재기동(2번 노드)

SQL> alter system set db_files = 5 scope=spfile;
 
System altered.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size         1124076584 bytes
Database Buffers      520093696 bytes
Redo Buffers            7094272 bytes
Database mounted.
Database opened.

db 기동 직후 datafile 확인(2번 노드)

SQL> 
set lines 200 pages 1000 
col file_name for a60
select file_id, tablespace_name, file_name from dba_data_files order by 1;
 
FILE_ID   TABLESPACE_NAME      FILE_NAME
----------  ---------------- ---------------------------
     1     SYSTEM              /app/oracle/oradata/adg11g/system01.dbf
     2     SYSAUX              /app/oracle/oradata/adg11g/sysaux01.dbf
     3     UNDOTBS1            /app/oracle/oradata/adg11g/undotbs01.dbf
     4     USERS              /app/oracle/oradata/adg11g/users01.dbf

4개가 존재함

몇초 뒤 확인(2번 노드)

SQL> 
set lines 200 pages 1000 
col file_name for a60
select file_id, tablespace_name, file_name from dba_data_files order by 1;
 
   FILE_ID TABLESPACE_NAME          FILE_NAME
---------- ------------------------------ ------------------------------------------------------------
     1 SYSTEM              /app/oracle/oradata/adg11gsb/system01.dbf
     2 SYSAUX              /app/oracle/oradata/adg11gsb/sysaux01.dbf
     3 UNDOTBS1              /app/oracle/oradata/adg11gsb/undotbs01.dbf
     4 USERS              /app/oracle/oradata/adg11gsb/users01.dbf
     5 USERS              /app/oracle/oradata/adg11gsb/users02.dbf

users 테이블 스페이스의 두번째 데이터파일이 조회됨

alert log 확인(2번 노드)

Mon Jan 18 17:58:53 2021
RSM0 started with pid=29, OS id=8742 
Using STANDBY_ARCHIVE_DEST parameter default value as /app/oracle/arch/adg11gsb
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='adg11gsb';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' SCOPE=SPFILE SID='adg11gsb';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (adg11gsb)
Mon Jan 18 17:58:57 2021
MRP0 started with pid=30, OS id=8746 
MRP0: Background Managed Standby Recovery process started (adg11gsb)
Mon Jan 18 17:58:57 2021
RFS[1]: Assigned to RFS process 8748
RFS[1]: Selected log 4 for thread 1 sequence 43 dbid -318785682 branch 1059694915
Mon Jan 18 17:58:57 2021
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process 8750
RFS[2]: Selected log 5 for thread 1 sequence 44 dbid -318785682 branch 1059694915
Mon Jan 18 17:58:57 2021
Archived Log entry 164 added for thread 1 sequence 43 ID 0xed0718a9 dest 1:
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /app/oracle/arch/adg11gsb/1_27_1059694915.arc
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='adg11gsb';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' SCOPE=SPFILE SID='adg11gsb';
Recovery created file /app/oracle/oradata/adg11gsb/users02.dbf
Datafile 5 added to flashback set
Successfully added datafile 5 to media recovery
Datafile #5: '/app/oracle/oradata/adg11gsb/users02.dbf'
Media Recovery Log /app/oracle/arch/adg11gsb/1_28_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_29_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_30_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_31_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_32_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_33_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_34_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_35_1059694915.arc
Mon Jan 18 17:59:07 2021
Media Recovery Log /app/oracle/arch/adg11gsb/1_36_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_37_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_38_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_39_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_40_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_41_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_42_1059694915.arc
Media Recovery Log /app/oracle/arch/adg11gsb/1_43_1059694915.arc
Media Recovery Waiting for thread 1 sequence 44 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 44 Reading mem 0
  Mem# 0: /app/oracle/oradata/adg11gsb/standby_log02.log

2번 노드 기동 후 잠시 뒤

6번 째 줄에서 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE 명령을 실행하고

9번 째 줄에서 MRP 프로세스가 시작됨

29번 째 줄에서 Datafile 5 의 recover가 시작됨

32~50번 째 줄까지 아카이브로그 파일을 읽어 변경사항을 적용시킴

브로커 show configuration 확인

DGMGRL> show configuration
 
Configuration - adg11g
 
  Protection Mode: MaxPerformance
  Databases:
    adg11g   - Primary database
    adg11gsb - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

정상화됨

데이터 확인(2번 노드)

SQL> select count(*) from imsidata;
 
  COUNT(*)
----------
    345052
 
SQL> select count(*) from imsidata2;
 
  COUNT(*)
----------
     86264

2번 노드에서도 정상적으로 데이터가 확인됨

참조 : https://docs.oracle.com/cd/E18283_01/server.112/e17110/dynviews_1103.htm