Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 3

OS환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 19.12.0.0 ADG

 

OS 및 사전 설정, ADG 구성은 아래 게시물 참조

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 1(https://positivemh.tistory.com/819)

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 2(https://positivemh.tistory.com/820)

 

방법 : Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 3

1번 노드(Active) hostname : oel19adg1, db_name : ORAADG, db_unique_name : ORAADG
2번 노드(Standby) hostname : oel19adg2, db_name : ORAADG, db_unique_name : ORAADGDR

 

*본문에 2번노드 표시가 없으면 모두 1번노드에서 작업

 

 

설치파일은 아래 3가지 파일을 이용함(RAC 구성 할때 받아놓은 패치파일 중 grid 패치파일만 빼고 사용함)
DB : LINUX.X64_193000_db_home.zip
OPatch : p6880880_190000_Linux-x86-64.zip(12.2.0.1.27)

RU : p32900083_190000_Linux-x86-64.zip

Patch 32900083 - Combo of OJVM Component Release Update 19.12.0.0.210720 + Grid Infrastructure Jul 2021 Release Update 19.12.0.0.210720

 ㄴ Patch 32895426 - Database Grid Infrastructure Jul 2021 Release Update 19.12.0.0.210720

 ㄴ Patch 32876380 - Oracle JavaVM Component Release Update 19.12.0.0.210720

 

 

구성은 Part1, Part2에서 모두 완료함

이번 게시글에선 adg의 real time redo apply 테스트와 switchover, failover, reinstate를 테스트함

 

 

real time redo apply 테스트

현재 recovery mode 확인(1번, 2번 노드)

SQL>
set lines 200 pages 1000
select dest_id, status, database_mode, recovery_mode, db_unique_name, gap_status 
from v$archive_dest_status where dest_id <=2;
 
  DEST_ID STATUS    DATABASE_MODE   RECOVERY_MODE             DB_UNIQUE_NAME      GAP_STATUS
---------- --------- --------------- ---------------------------------- -------------------- ------------------------
     1 VALID     OPEN          IDLE                 ORAADG
     2 VALID     OPEN_READ-ONLY  MANAGED REAL TIME APPLY WITH QUERY ORAADGDR             NO GAP

현재 2번 노드가 REAL TIME APPLY 모드임

 

 

테이블 생성 및 데이터 삽입, 커밋(1번 노드)

SQL> create table imsidg (no number);     
 
Table created.
 
SQL> insert into imsidg values(1);
 
1 row created.
 
SQL> insert into imsidg values(2);
 
1 row created.
 
SQL> insert into imsidg values(3);
 
1 row created.
 
SQL> commit;
 
Commit complete.

 

 

현재 사용중인 백그라운드 프로세스 확인

1번 노드
SQL> select client_process,process,status,sequence# from v$managed_standby;
 
CLIENT_P PROCESS   STATUS     SEQUENCE#
-------- --------- ------------ ----------
N/A     DGRD       ALLOCATED         0
ARCH     ARCH       CLOSING        21
N/A     DGRD       ALLOCATED         0
ARCH     ARCH       CLOSING        21
ARCH     ARCH       CONNECTED         0
ARCH     ARCH       CONNECTED         0
LNS     LNS       OPENING        22
N/A     DGRD       ALLOCATED         0
LNS     LNS       WRITING        22
 
9 rows selected.
 
2번 노드
CLIENT_P PROCESS   STATUS     SEQUENCE#
-------- --------- ------------ ----------
ARCH     ARCH       CLOSING        21
N/A     DGRD       ALLOCATED         0
N/A     DGRD       ALLOCATED         0
ARCH     ARCH       CONNECTED         0
ARCH     ARCH       CONNECTED         0
ARCH     ARCH       CONNECTED         0
N/A     MRP0       APPLYING_LOG     22
Archival RFS       IDLE          0
LGWR     RFS       IDLE         22
UNKNOWN  RFS       IDLE          0
 
10 rows selected.

1번 노드는 LNS 프로세스가 내려쓰는 중임을 확인 가능함

2번 노드는 MRP 프로세스는 계속 APPLYING LOG 상태임 (로그를 적용하는 상태)

 

 

테이블 데이터 확인

1번 노드
SQL> select * from imsidg;
 
    NO
----------
     1
     2
     3
 
2번 노드
SQL> select * from imsidg;
 
    NO
----------
     1
     2
     3

동일한 값 확인 가능함

 

 

테이블 삭제 시도(2번 노드)

SQL> drop table imsidg purge;
drop table imsidg purge
           *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access

2번 노드는 read only 모드이기 때문에 삭제는 불가능함

 

 

테이블 삭제(1번 노드)

SQL> drop table imsidg purge;
 
Table dropped.

정상적으로 삭제됨

 

 

switchover 테스트

*switchover 와 failover 란?

S/W나 H/W의 작업에 의해 계획된 전환은 Switchover라 하며,

운영 중에 Primary의 예기치 않은 장애에 대한 전환은 Failover 라고함

 

 

switchover 테스트(1번 노드)

$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADGDR
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADGDR"
Connecting ...
Connected to "ORAADGDR"
Connected as SYSDBA.
New primary database "ORAADGDR" is opening...
Operation requires start up of instance "ORAADG" on database "ORAADG"
Starting instance "ORAADG"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADG"
Database mounted.
Database opened.
Connected to "ORAADG"
Switchover succeeded, new primary is "oraadgdr"

정상적으로 switchover됨

 

 

configuration 확인

$ dgmgrl sys/oracle
DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADGDR - Primary database
    ORAADG   - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 13 seconds ago)

정상적으로 switchover됨

 

 

switchover시 1번 노드 alert log

switchover시 1번 노드 alert log

2022-03-30T17:07:29.014219+09:00
SWITCHOVER VERIFY: Send VERIFY request to switchover target ORAADGDR
SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER
SWITCHOVER VERIFY: Send VERIFY request to switchover target ORAADGDR
SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER
2022-03-30T17:07:30.143628+09:00
ALTER DATABASE SWITCHOVER TO 'ORAADGDR'
2022-03-30T17:07:30.143711+09:00
RSM0 (PID:16893): The Time Management Interface (TMI) is being enabled for role transition
RSM0 (PID:16893): information.  This will result in messages beingoutput to the alert log
RSM0 (PID:16893): file with the prefix 'TMI: '.  This is being enabled to make the timing of
RSM0 (PID:16893): the various stages of the role transition available for diagnostic purposes.
RSM0 (PID:16893): This output will end when the role transition is complete.
TMI: dbsdrv switchover to target BEGIN 2022-03-30 17:07:30.143885
RSM0 (PID:16893): Starting switchover [Process ID: 16893]
TMI: kcv_switchover_to_target convert to physical BEGIN 2022-03-30 17:07:30.186726
2022-03-30T17:07:30.186788+09:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 16893] (ORAADG)
RSM0 (PID:16893): Waiting for target standby to receive all redo
2022-03-30T17:07:30.188815+09:00
RSM0 (PID:16893): Waiting for all non-current ORLs to be archived
2022-03-30T17:07:30.188888+09:00
RSM0 (PID:16893): All non-current ORLs have been archived
2022-03-30T17:07:30.188920+09:00
RSM0 (PID:16893): Waiting for all FAL entries to be archived
2022-03-30T17:07:30.188982+09:00
RSM0 (PID:16893): All FAL entries have been archived
2022-03-30T17:07:30.189021+09:00
RSM0 (PID:16893): Waiting for LAD:2 to become synchronized
2022-03-30T17:07:31.189676+09:00
RSM0 (PID:16893): Active, synchronized Physical Standby switchover target has been identified
RSM0 (PID:16893): Preventing updates and queries at the Primary
2022-03-30T17:07:33.191286+09:00
RSM0 (PID:16893): Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 31 has been fixed
Switchover: Primary highest seen SCN set to 0x000000000015a402
RSM0 (PID:16893): Noswitch archival of T-1.S-31
RSM0 (PID:16893): End-Of-Redo Branch archival of T-1.S-31
RSM0 (PID:16893): LGWR is scheduled to archive to LAD:2 after log switch
Expanded controlfile section 11 from 28 to 280 records
Requested to grow by 252 records; added 9 blocks of records
RSM0 (PID:16893): Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
RSM0 (PID:16893): Waiting for target standby to apply all redo
2022-03-30T17:07:34.273239+09:00
Backup controlfile written to trace file /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/ORAADG_rsm0_16893.trc
RSM0 (PID:16893): Converting the primary database to a new standby database
Clearing standby activation ID 2271428741 (0x87634085)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
Archivelog for thread 1 sequence 31 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
TMI: kcv_switchover_to_target convert to physical END 2022-03-30 17:07:34.280699
RSM0 (PID:16893): Sending request(convert to primary database) to switchover target ORAADGDR
2022-03-30T17:07:42.667391+09:00
RSM0 (PID:16893): Switchover complete. Database shutdown required
TMI: dbsdrv switchover to target END 2022-03-30 17:07:42.667426
Completed: ALTER DATABASE SWITCHOVER TO 'ORAADGDR'
2022-03-30T17:07:48.965194+09:00
Data Guard Broker operation requires restart; shutting down instance now
DMON (ospid: 16796): terminating the instance
2022-03-30T17:07:49.975975+09:00
Instance terminated by DMON, pid = 16796
2022-03-30T17:07:55.190291+09:00
Starting ORACLE instance (normal) (OS id: 17231)
2022-03-30T17:07:55.193245+09:00
****************************************************
 Sys-V shared memory will be used for creating SGA 
 ****************************************************
2022-03-30T17:07:55.193622+09:00
**********************************************************************
2022-03-30T17:07:55.193679+09:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA) 
 
2022-03-30T17:07:55.193727+09:00
 Per process system memlock (soft) limit = 7373T
2022-03-30T17:07:55.193788+09:00
 Expected per process system memlock (soft) limit to lock
 instance MAX SHARED GLOBAL AREA (SGA) into memory: 2402M
2022-03-30T17:07:55.193862+09:00
 Available system pagesizes:
  4K, 2048K 
2022-03-30T17:07:55.193934+09:00
 Supported system pagesize(s):
2022-03-30T17:07:55.193985+09:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2022-03-30T17:07:55.194035+09:00
        4K       Configured               7          614407        NONE
2022-03-30T17:07:55.194124+09:00
     2048K                0            1201               0        NONE
2022-03-30T17:07:55.194175+09:00
RECOMMENDATION:
2022-03-30T17:07:55.194201+09:00
 1. For optimal performance, configure system with expected number 
 of pages for every supported system pagesize prior to the next 
 instance restart operation.
2022-03-30T17:07:55.196510+09:00
**********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 4
Number of processor cores in the system is 4
Number of processor sockets in the system is 2
Capability Type : Network 
capabilities requested : 7 detected : 0 Simulated : 0
Capability Type : Runtime Environment 
capabilities requested : 400000FF detected : 40000000 Simulated : 0
Capability Type : Engineered Systems 
capabilities requested : F detected : 0 Simulated : 0
Capability Type : Database Test 
capabilities requested : 3 detected : 0 Simulated : 0
Capability Type : Database Editions 
capabilities requested : 1CC detected : 8 Simulated : 0
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =55
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Cluster configuration type = NONE [2]
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0.
ORACLE_HOME:    /oracle/app/oracle/product/19c
System name:    Linux
Node name:    oel19adg1
Release:    5.4.17-2102.201.3.el8uek.x86_64
Version:    #2 SMP Fri Apr 23 09:05:57 PDT 2021
Machine:    x86_64
Using parameter settings in server-side spfile /oracle/app/oracle/product/19c/dbs/spfileORAADG.ora
System parameters with non-default values:
  processes                = 320
  nls_language             = "AMERICAN"
  nls_territory            = "AMERICA"
  sga_target               = 2400M
  control_files            = "/oracle/app/oracle/oradata/ORAADG/control01.ctl"
  control_files            = "/oracle/app/oracle/oradata/ORAADG/control02.ctl"
  db_block_size            = 8192
  compatible               = "19.0.0"
  log_archive_dest_1       = "LOCATION=/oracle/app/oracle/arch/adg1 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADG"
  log_archive_dest_2       = "service="ORAADGDR""
  log_archive_dest_2       = "ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="ORAADGDR" net_timeout=30"
  log_archive_dest_2       = "valid_for=(online_logfile,all_roles)"
  log_archive_dest_state_1 = "ENABLE"
  log_archive_dest_state_2 = "ENABLE"
  fal_client               = "ORAADG"
  fal_server               = ""
  log_archive_config       = "DG_CONFIG=(ORAADG,ORAADGDR)"
  log_archive_format       = "%t_%s_%r.arc"
  log_archive_max_processes= 4
  db_recovery_file_dest    = "/oracle/app/oracle/fast_recovery_area/"
  db_recovery_file_dest_size= 7851M
  standby_file_management  = "auto"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=ORAADGXDB)"
  local_listener           = "LISTENER_ORAADG"
  audit_file_dest          = "/oracle/app/oracle/admin/ORAADG/adump"
  audit_trail              = "NONE"
  db_name                  = "ORAADG"
  db_unique_name           = "ORAADG"
  open_cursors             = 300
  pga_aggregate_target     = 800M
  dg_broker_start          = TRUE
  diagnostic_dest          = "/oracle/app/oracle"
2022-03-30T17:07:55.430389+09:00
============================================================
NOTE: PatchLevel of this instance 0
============================================================
Starting background process PMON
2022-03-30T17:07:55.473292+09:00
PMON started with pid=2, OS id=17235 
Starting background process CLMN
2022-03-30T17:07:55.483925+09:00
CLMN started with pid=3, OS id=17239 
Starting background process PSP0
2022-03-30T17:07:55.493346+09:00
PSP0 started with pid=4, OS id=17243 
Starting background process VKTM
2022-03-30T17:07:56.526643+09:00
VKTM started with pid=5, OS id=17247 at elevated (RT) priority
2022-03-30T17:07:56.526728+09:00
Starting background process GEN0
2022-03-30T17:07:56.527459+09:00
VKTM running at (1)millisec precision with DBRM quantum (100)ms
2022-03-30T17:07:56.534662+09:00
GEN0 started with pid=6, OS id=17253 
Starting background process MMAN
2022-03-30T17:07:56.542822+09:00
MMAN started with pid=7, OS id=17257 
Starting background process GEN1
2022-03-30T17:07:56.561116+09:00
GEN1 started with pid=9, OS id=17263_17264 
Starting background process DIAG
2022-03-30T17:07:56.568667+09:00
DIAG started with pid=11, OS id=17266 
Starting background process OFSD
2022-03-30T17:07:56.580850+09:00
OFSD started with pid=12, OS id=17268_17269 
Starting background process DBRM
2022-03-30T17:07:56.581753+09:00
Oracle running with ofslib:'Oracle File Server Library' version=2 inc=1
2022-03-30T17:07:56.592428+09:00
DBRM started with pid=14, OS id=17271 
Starting background process VKRM
2022-03-30T17:07:56.602765+09:00
VKRM started with pid=15, OS id=17275 
Starting background process SVCB
2022-03-30T17:07:56.613149+09:00
SVCB started with pid=16, OS id=17277 
Starting background process PMAN
2022-03-30T17:07:56.623087+09:00
PMAN started with pid=17, OS id=17279 
Starting background process DIA0
2022-03-30T17:07:56.631304+09:00
DIA0 started with pid=18, OS id=17283 
Starting background process DBW0
2022-03-30T17:07:56.648691+09:00
DBW0 started with pid=19, OS id=17285 
Starting background process LGWR
Starting background process CKPT
2022-03-30T17:07:56.659382+09:00
LGWR started with pid=20, OS id=17289 
2022-03-30T17:07:56.671963+09:00
CKPT started with pid=21, OS id=17293 
Starting background process SMON
2022-03-30T17:07:56.683350+09:00
LGWR slave LG00 created with pid=22, OS pid=17297
2022-03-30T17:07:56.694367+09:00
SMON started with pid=23, OS id=17299 
LGWR slave LG01 created with pid=24, OS pid=17303
Starting background process SMCO
2022-03-30T17:07:56.712905+09:00
SMCO started with pid=25, OS id=17305 
Starting background process RECO
2022-03-30T17:07:56.722068+09:00
RECO started with pid=26, OS id=17307 
Starting background process LREG
2022-03-30T17:07:56.741035+09:00
LREG started with pid=28, OS id=17311 
Starting background process PXMN
Starting background process DMON
2022-03-30T17:07:56.760502+09:00
PXMN started with pid=30, OS id=17317 
2022-03-30T17:07:56.768649+09:00
DMON started with pid=31, OS id=17319 
Starting background process FENC
2022-03-30T17:07:56.780027+09:00
FENC started with pid=32, OS id=17321 
Starting background process MMON
2022-03-30T17:07:56.789112+09:00
MMON started with pid=33, OS id=17325 
Starting background process MMNL
2022-03-30T17:07:56.796626+09:00
MMNL started with pid=34, OS id=17327 
2022-03-30T17:07:56.796786+09:00
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Starting background process TMON
2022-03-30T17:07:56.819574+09:00
TMON started with pid=36, OS id=17333 
Setting CPU count to 4
ORACLE_BASE from environment = /oracle/app/oracle
2022-03-30T17:07:56.910185+09:00
alter database mount
2022-03-30T17:07:59.666122+09:00
Using default pga_aggregate_limit of 2048 MB
2022-03-30T17:08:00.937627+09:00
.... (PID:17335): RT: Role transition work is not done
.... (PID:17335): Redo network throttle feature is disabled at mount time
2022-03-30T17:08:00.943602+09:00
Successful mount of redo thread 1, with mount id 2271444652
2022-03-30T17:08:00.943904+09:00
Allocating 15937344 bytes in shared pool for flashback generation buffer.
Allocated 15937344 bytes in shared pool for flashback generation buffer
Starting background process RVWR
2022-03-30T17:08:00.955806+09:00
RVWR started with pid=39, OS id=17344 
.... (PID:17335): Database role set to PHYSICAL STANDBY [kcvfdb.c:9362]
Physical Standby Database mounted.
.... (PID:17335): STARTING ARCH PROCESSES
Starting background process ARC0
2022-03-30T17:08:00.971338+09:00
ARC0 started with pid=40, OS id=17348 
2022-03-30T17:08:00.978859+09:00
TT00 (PID:17350): Gap Manager starting
2022-03-30T17:08:00.981767+09:00
.... (PID:17335): ARC0: Archival started
.... (PID:17335): STARTING ARCH PROCESSES COMPLETE
2022-03-30T17:08:00.981968+09:00
ARC0 (PID:17348): Becoming a 'no FAL' ARCH
2022-03-30T17:08:00.982155+09:00
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
Lost write protection disabled
.... (PID:17335): Using STANDBY_ARCHIVE_DEST parameter default value as /oracle/app/oracle/arch/adg1 [krsd.c:18206]
2022-03-30T17:08:00.986838+09:00
TMON (PID:17333): STARTING ARCH PROCESSES
Starting background process ARC1
2022-03-30T17:08:00.994363+09:00
ARC1 started with pid=43, OS id=17354 
Starting background process ARC2
Starting background process ARC3
2022-03-30T17:08:01.002378+09:00
ARC2 started with pid=44, OS id=17356 
2022-03-30T17:08:01.009751+09:00
Archiving previously deferred ORLs (ORAADG)
TMON (PID:17333): ARC1: Archival started
2022-03-30T17:08:01.009907+09:00
ARC3 started with pid=45, OS id=17358 
TMON (PID:17333): ARC2: Archival started
2022-03-30T17:08:01.010018+09:00
NET  (PID:17335): End-Of-Redo Branch archival of T-1.S-31
2022-03-30T17:08:01.019159+09:00
NET  (PID:17335): Archived Log entry 30 added for T-1.S-31 ID 0x87634085 LAD:1
2022-03-30T17:08:01.020478+09:00
TMON (PID:17333): ARC3: Archival started
TMON (PID:17333): STARTING ARCH PROCESSES COMPLETE
Completed: alter database mount
alter database open
2022-03-30T17:08:01.022526+09:00
Data Guard Broker initializing...
2022-03-30T17:08:03.129250+09:00
 rfs (PID:17364): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:13512)
 rfs (PID:17364): Primary database is in MAXIMUM PERFORMANCE mode
2022-03-30T17:08:03.133640+09:00
 rfs (PID:17362): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:13348)
2022-03-30T17:08:03.141157+09:00
 rfs (PID:17364): Selected LNO:4 for T-1.S-33 dbid 2271425693 branch 1100700253
2022-03-30T17:08:03.170547+09:00
 rfs (PID:17366): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:13352)
2022-03-30T17:08:03.174249+09:00
 rfs (PID:17366): Opened log for T-1.S-32 dbid 2271425693 branch 1100700253
2022-03-30T17:08:03.179844+09:00
 rfs (PID:17366): Archived Log entry 31 added for B-1100700253.T-1.S-32 ID 0x87630ec7 LAD:2
2022-03-30T17:08:05.093540+09:00
Starting Data Guard Broker (DMON)
Starting background process INSV
2022-03-30T17:08:05.102910+09:00
INSV started with pid=49, OS id=17368 
2022-03-30T17:08:09.256355+09:00
Data Guard Broker initialization complete
Ping without log force is disabled:
  instance mounted in exclusive mode.
2022-03-30T17:08:09.260662+09:00
Beginning Standby Crash Recovery.
 Started logmerger process
2022-03-30T17:08:09.276811+09:00
PR00 (PID:17370): Managed Standby Recovery starting Real Time Apply
2022-03-30T17:08:09.312154+09:00
ALTER SYSTEM SET fal_server='ORAADGDR' SCOPE=BOTH;
Starting background process NSV0
2022-03-30T17:08:09.326798+09:00
NSV0 started with pid=51, OS id=17382 
2022-03-30T17:08:09.340585+09:00
Parallel Media Recovery started with 4 slaves
2022-03-30T17:08:09.450954+09:00
Stopping change tracking
2022-03-30T17:08:09.518034+09:00
Media Recovery Log /oracle/app/oracle/arch/adg1/1_31_1100700253.arc
2022-03-30T17:08:09.677384+09:00
PR00 (PID:17370): Resetting standby activation ID 0 (0x0)
2022-03-30T17:08:09.677467+09:00
Incomplete Recovery applied until change 1418242 time 03/30/2022 17:07:33
Stopping change tracking
2022-03-30T17:08:09.761858+09:00
Completed Standby Crash Recovery.
Endian type of dictionary set to little
Undo initialization finished serial:0 start:28971958 end:28971958 diff:0 ms (0.0 seconds)
Dictionary check beginning
Dictionary check complete
Database Characterset is KO16MSWIN949
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open
2022-03-30T17:08:10.094007+09:00
Unable to obtain current patch information as the database or pluggable database was opened for read-only access.
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 16000
===========================================================
2022-03-30T17:08:10.332170+09:00
db_recovery_file_dest_size of 7851 MB is 5.22% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2022-03-30T17:08:10.886390+09:00
Starting background process RSM0
2022-03-30T17:08:10.893088+09:00
RSM0 started with pid=60, OS id=17414 
2022-03-30T17:08:15.055964+09:00
RSM0 (PID:17414): Using STANDBY_ARCHIVE_DEST parameter default value as /oracle/app/oracle/arch/adg1 [krsd.c:18206]
2022-03-30T17:08:15.058936+09:00
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
2022-03-30T17:08:29.171657+09:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2022-03-30T17:08:29.172174+09:00
Attempt to start background Managed Standby Recovery process (ORAADG)
Starting background process MRP0
2022-03-30T17:08:29.179490+09:00
MRP0 started with pid=64, OS id=17432 
2022-03-30T17:08:29.180184+09:00
Background Managed Standby Recovery process started (ORAADG)
2022-03-30T17:08:34.194634+09:00
 Started logmerger process
2022-03-30T17:08:34.200214+09:00
 
IM on ADG: Start of Empty Journal 
 
IM on ADG: End of Empty Journal 
PR00 (PID:17434): Managed Standby Recovery starting Real Time Apply
2022-03-30T17:08:34.241375+09:00
Parallel Media Recovery started with 4 slaves
2022-03-30T17:08:34.357086+09:00
Stopping change tracking
2022-03-30T17:08:34.368237+09:00
TT02 (PID:17444): Waiting for all non-current ORLs to be archived
2022-03-30T17:08:34.368310+09:00
TT02 (PID:17444): All non-current ORLs have been archived
TT02 (PID:17444): Clearing ORL LNO:1 /oracle/app/oracle/oradata/ORAADG/redo01.log
Clearing online log 1 of thread 1 sequence number 30
2022-03-30T17:08:34.424914+09:00
PR00 (PID:17434): Media Recovery Log /oracle/app/oracle/arch/adg1/1_32_1100700253.arc
PR00 (PID:17434): Media Recovery Waiting for T-1.S-33 (in transit)
2022-03-30T17:08:34.889216+09:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 33 Reading mem 0
2022-03-30T17:08:34.889260+09:00
TT02 (PID:17444): Clearing ORL LNO:1 complete
2022-03-30T17:08:34.889262+09:00
  Mem# 0: /oracle/app/oracle/oradata/ORAADG/standby_redo01.log
2022-03-30T17:08:34.889468+09:00
TT02 (PID:17444): Clearing ORL LNO:2 /oracle/app/oracle/oradata/ORAADG/redo02.log
Clearing online log 2 of thread 1 sequence number 31
2022-03-30T17:08:35.187967+09:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
TT02 (PID:17444): Clearing ORL LNO:2 complete
2022-03-30T17:08:40.421225+09:00
TT02 (PID:17444): Waiting for all non-current ORLs to be archived
2022-03-30T17:08:40.421304+09:00
TT02 (PID:17444): All non-current ORLs have been archived

 

 

switchover시 1번 노드 브로커 log

switchover시 1번 브로커 log

2022-03-30T17:07:28.942+09:00
Initiating a healthcheck...
SWITCHOVER TO ORAADGDR
Switchover to physical standby database cannot be initiated from the primary database
redirecting connection to switchover target database ORAADGDR...
...using connect identifier: ORAADGDR
SWITCHOVER TO ORAADGDR
Notifying Oracle Clusterware to prepare primary database for switchover
2022-03-30T17:07:30.143+09:00
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'ORAADGDR']
2022-03-30T17:07:42.667+09:00
SQL [ALTER DATABASE SWITCHOVER TO 'ORAADGDR'] executed successfully
2022-03-30T17:07:43.734+09:00
Switchover successful
Switchover to physical standby database requires this instance to be restarted.
2022-03-30T17:07:48.965+09:00
Shutting down instance after Switchover
2022-03-30T17:08:05.093+09:00
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
      dg_broker_config_file1 = "/oracle/app/oracle/product/19c/dbs/dr1ORAADG.dat"
      dg_broker_config_file2 = "/oracle/app/oracle/product/19c/dbs/dr2ORAADG.dat"
2022-03-30T17:08:05.093+09:00                      DMON: Attach state object
2022-03-30T17:08:05.093+09:00                      DMON: Initialization of broker state requires reconciliation
2022-03-30T17:08:05.093+09:00                      DMON: Broker state reconciled, version = 0, state = 00000000
2022-03-30T17:08:05.093+09:00                      DMON: Broker State Initialized
2022-03-30T17:08:05.093+09:00                            Version = 1
2022-03-30T17:08:05.093+09:00                            State = 00000000
2022-03-30T17:08:05.093+09:00                      DMON: Creating INSV process
2022-03-30T17:08:08.165+09:00                      INSV: Opening configuration file '/oracle/app/oracle/product/19c/dbs/dr1ORAADG.dat' with blocksize 4096
2022-03-30T17:08:08.165+09:00                      INSV: Successfully opened configuration file '/oracle/app/oracle/product/19c/dbs/dr1ORAADG.dat'
2022-03-30T17:08:08.165+09:00                      INSV: home block of /oracle/app/oracle/product/19c/dbs/dr1ORAADG.dat: zzz=0x40, Seq.MIV=2.1, fsfo flags=0x0, version=0, pstseq=0.0
2022-03-30T17:08:08.165+09:00                      INSV: Opening configuration file '/oracle/app/oracle/product/19c/dbs/dr2ORAADG.dat' with blocksize 4096
2022-03-30T17:08:08.165+09:00                      INSV: Successfully opened configuration file '/oracle/app/oracle/product/19c/dbs/dr2ORAADG.dat'
2022-03-30T17:08:08.165+09:00                      INSV: home block of /oracle/app/oracle/product/19c/dbs/dr2ORAADG.dat: zzz=0x40, Seq.MIV=3.0, fsfo flags=0x0, version=0, pstseq=0.0
2022-03-30T17:08:08.165+09:00                      INSV: Set current metadata file ID to 2 (Seq.MIV=3.0)
2022-03-30T17:08:08.165+09:00                      INSV: Opening configuration file '/oracle/app/oracle/product/19c/dbs/dr2ORAADG.dat' with blocksize 4096
2022-03-30T17:08:08.165+09:00                      INSV: Successfully opened configuration file '/oracle/app/oracle/product/19c/dbs/dr2ORAADG.dat'
2022-03-30T17:08:08.165+09:00                      INSV: Uploading persisted FSFO state:
2022-03-30T17:08:08.166+09:00                             flags=0x00000000, version=0, pstseq=0, target=0, obid=1
2022-03-30T17:08:08.166+09:00                             envsn=0, ackseq=0, new_target=255, new_obid=0
2022-03-30T17:08:08.166+09:00                          pmyshut=1, aft=30, laglim=30, obslim=30
2022-03-30T17:08:08.166+09:00                      INSV: INSV is ready
2022-03-30T17:08:09.188+09:00                      DMON: rfm_get_chief_lock() called for CTL_BOOTSTRAP, reason BOOTSTRAP, called from rfm_dmon_wakeup_fn
2022-03-30T17:08:09.189+09:00 7fffffff           0 DMON: start task execution: broker initialization
2022-03-30T17:08:09.189+09:00                      DMON: Boot configuration (0.0.0), loading from "/oracle/app/oracle/product/19c/dbs/dr2ORAADG.dat"
2022-03-30T17:08:09.190+09:00                      DMON: My instance_name is ORAADG (SID ORAADG), broker profile found at (1.1)
2022-03-30T17:08:09.190+09:00                      DMON: Verifying configuration service ORAADG_CFG
2022-03-30T17:08:09.190+09:00                      DMON: Configuration service not registered
2022-03-30T17:08:09.190+09:00                      DMON: Adding configuration service... ORAADG_CFG
2022-03-30T17:08:09.191+09:00                      DMON: Registering 1 service/s with listener(s)
2022-03-30T17:08:09.191+09:00                      DMON: Executing SQL [ALTER SYSTEM REGISTER]
2022-03-30T17:08:09.191+09:00                      SQL [ALTER SYSTEM REGISTER] Executed successfully
2022-03-30T17:08:09.191+09:00                      CLSR: clsr_crs_status CRS not configured, config = 2
.
2022-03-30T17:08:09.191+09:00                      DMON: Clusterware not configured; status -10
Broker Configuration:             DR_ORAADG
  Oracle Release:                 19.0.0.0.0
  Oracle Version:                 19.12.0.0.0
  Metadata Version:               4.1 / UID=0x6112f11d / Seq.MIV=3.0 / blksz.grain=4096.8
  Protection Mode:                Maximum Performance
  Fast-Start Failover (FSFO): DISABLED, flags=0x0, version=0
  Name                            Member Type                Handle      Enabled
  ORAADGDR                        Primary Database           0x02010000  YES
  ORAADG                          Physical Standby Database  0x01010000  YES
2022-03-30T17:08:09.193+09:00
Physical standby restart after broker operation requires Oracle Clusterware buildup
2022-03-30T17:08:10.886+09:00
Configuration Validation Results:
      Primary database ORAADGDR returned: ORA-0: normal, successful completion
Member ORAADG successfully completed version check
Broker configuration file is current, completing initialization
2022-03-30T17:08:29.157+09:00
Starting redo apply services...

 

 

switchover시 2번 노드 alert log

switchover시 2번 노드 alert log

2022-03-30T17:07:28.966442+09:00
 rfs (PID:13474): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:16796)
2022-03-30T17:07:29.006838+09:00
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY COMPLETE
2022-03-30T17:07:29.059522+09:00
 rfs (PID:13482): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:16796)
2022-03-30T17:07:29.098561+09:00
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY COMPLETE
2022-03-30T17:07:29.122759+09:00
ALTER SYSTEM SET fal_server='ORAADG' SCOPE=BOTH;
2022-03-30T17:07:30.140173+09:00
 rfs (PID:13488): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:16893)
2022-03-30T17:07:30.182430+09:00
 rfs (PID:13492): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:16827)
2022-03-30T17:07:33.188724+09:00
 rfs (PID:13494): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:16893)
2022-03-30T17:07:33.200613+09:00
 rfs (PID:13494): Selected LNO:4 for T-1.S-31 dbid 2271425693 branch 1100700253
2022-03-30T17:07:33.250372+09:00
ARC1 (PID:13352): Archived Log entry 9 added for T-1.S-31 ID 0x87634085 LAD:1
2022-03-30T17:07:33.298728+09:00
PR00 (PID:13433): Resetting standby activation ID 2271428741 (0x87634085)
2022-03-30T17:07:33.301118+09:00
Media Recovery End-Of-Redo indicator encountered
2022-03-30T17:07:33.301181+09:00
Media Recovery Continuing
PR00 (PID:13433): Media Recovery Waiting for T-1.S-32
2022-03-30T17:07:34.272289+09:00
.... (PID:13498): The Time Management Interface (TMI) is being enabled for role transition
.... (PID:13498): information.  This will result in messages beingoutput to the alert log
.... (PID:13498): file with the prefix 'TMI: '.  This is being enabled to make the timing of
.... (PID:13498): the various stages of the role transition available for diagnostic purposes.
.... (PID:13498): This output will end when the role transition is complete.
SWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.
2022-03-30T17:07:34.272593+09:00
ALTER DATABASE SWITCHOVER TO PRIMARY (ORAADGDR)
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2022-03-30 17:07:34.272647
Switchover: Media recovery is still active
 rmi (PID:13498): Role Change: Canceling MRP - no more redo to apply
2022-03-30T17:07:34.281699+09:00
PR00 (PID:13433): MRP0: Background Media Recovery cancelled with status 16037
2022-03-30T17:07:34.281802+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_pr00_13433.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:13433): Managed Standby Recovery not using Real Time Apply
2022-03-30T17:07:34.352507+09:00
Recovery interrupted!
Stopping change tracking
2022-03-30T17:07:34.353335+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_pr00_13433.trc:
ORA-16037: user requested cancel of managed recovery operation
2022-03-30T17:07:34.470237+09:00
Background Media Recovery process shutdown (ORAADGDR)
2022-03-30T17:07:35.274546+09:00
 rmi (PID:13498): Role Change: Canceled MRP
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2022-03-30 17:07:35.274654
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2022-03-30 17:07:35.274768
 rmi (PID:13498): Killing 2 processes (PIDS:13492,13364) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 13498
2022-03-30T17:07:35.275287+09:00
Process termination requested for pid 13364 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:35.275310+09:00
Process termination requested for pid 13492 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:38.304467+09:00
Stopping background process MMNL
2022-03-30T17:07:39.327840+09:00
Stopping background process MMON
2022-03-30T17:07:40.352438+09:00
Stopping Emon pool
Dispatchers and shared servers shutdown
CLOSE: killing server sessions.
2022-03-30T17:07:40.387068+09:00
Process termination requested for pid 13405 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:40.387132+09:00
Process termination requested for pid 13403 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:40.387140+09:00
Process termination requested for pid 13399 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:40.387161+09:00
Process termination requested for pid 13391 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:40.388229+09:00
Process termination requested for pid 13397 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:40.388800+09:00
Process termination requested for pid 13393 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:40.388816+09:00
Process termination requested for pid 13401 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:40.389323+09:00
Process termination requested for pid 13395 [source = rdbms], [info = 2] [request issued by pid: 13498, uid: 54321]
2022-03-30T17:07:40.397589+09:00
Active process 13280 user 'oracle' program 'oracle@oel19adg2 (SVCB)', waiting for 'wait for unread message on broadcast channel'
 
Active process 13413 user 'oracle' program 'oracle@oel19adg2 (W002)', waiting for 'Space Manager: slave idle wait'
 
Active process 13318 user 'oracle' program 'oracle@oel19adg2 (W001)', waiting for 'Space Manager: slave idle wait'
 
Active process 13415 user 'oracle' program 'oracle@oel19adg2 (W003)', waiting for 'Space Manager: slave idle wait'
 
Active process 13429 user 'oracle' program 'oracle@oel19adg2 (W004)', waiting for 'Space Manager: slave idle wait'
 
Active process 13312 user 'oracle' program 'oracle@oel19adg2 (W000)', waiting for 'Space Manager: slave idle wait'
 
Active process 13280 user 'oracle' program 'oracle@oel19adg2 (SVCB)', waiting for 'wait for unread message on broadcast channel'
 
Active process 13413 user 'oracle' program 'oracle@oel19adg2 (W002)', waiting for 'Space Manager: slave idle wait'
 
Active process 13318 user 'oracle' program 'oracle@oel19adg2 (W001)', waiting for 'Space Manager: slave idle wait'
 
Active process 13415 user 'oracle' program 'oracle@oel19adg2 (W003)', waiting for 'Space Manager: slave idle wait'
 
Active process 13429 user 'oracle' program 'oracle@oel19adg2 (W004)', waiting for 'Space Manager: slave idle wait'
 
Active process 13312 user 'oracle' program 'oracle@oel19adg2 (W000)', waiting for 'Space Manager: slave idle wait'
 
2022-03-30T17:07:41.495738+09:00
CLOSE: all sessions shutdown successfully.
2022-03-30T17:07:42.497180+09:00
 
IM on ADG: Start of Empty Journal 
 
IM on ADG: End of Empty Journal 
Stopping Emon pool
Entire Buffer Cache invalidation started
Entire Buffer Cache invalidation completed
Backup controlfile written to trace file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_rmi_13498.trc
SwitchOver after complete recovery through change 1418242
 rmi (PID:13498): ORL pre-clearing operation disabled by switchover
Online log /oracle/app/oracle/oradata/ORAADGDR/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oracle/app/oracle/oradata/ORAADGDR/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oracle/app/oracle/oradata/ORAADGDR/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1418240
 rmi (PID:13498): RT: Role transition work is not done
 rmi (PID:13498): The Time Management Interface (TMI) is being enabled for role transition
 rmi (PID:13498): information.  This will result in messages beingoutput to the alert log
 rmi (PID:13498): file with the prefix 'TMI: '.  This is being enabled to make the timing of
 rmi (PID:13498): the various stages of the role transition available for diagnostic purposes.
 rmi (PID:13498): This output will end when the role transition is complete.
 rmi (PID:13498): Redo network throttle feature is disabled at mount time
2022-03-30T17:07:42.640821+09:00
 rmi (PID:13498): Database role cleared from PHYSICAL STANDBY [kcvs.c:1099]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2022-03-30 17:07:42.641170
SWITCHOVER: completed request from primary database.
2022-03-30T17:07:44.812500+09:00
ALTER SYSTEM SET fal_server='' SCOPE=BOTH;
2022-03-30T17:07:44.812825+09:00
Starting background process NSV1
2022-03-30T17:07:44.820015+09:00
NSV1 started with pid=25, OS id=13504 
2022-03-30T17:07:47.956931+09:00
RSM0 (PID:13387): Using STANDBY_ARCHIVE_DEST parameter default value as /oracle/app/oracle/arch/adg2 [krsd.c:18206]
2022-03-30T17:07:47.957325+09:00
ARC2 (PID:13354): Becoming the 'no SRL' ARCH
2022-03-30T17:07:47.960488+09:00
ALTER SYSTEM SET log_archive_dest_2='service="ORAADG"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="ORAADG" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
2022-03-30T17:07:47.972509+09:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER DATABASE OPEN
2022-03-30T17:07:47.973106+09:00
TMI: adbdrv open database BEGIN 2022-03-30 17:07:47.973070
Data Guard Broker initializing...
2022-03-30T17:07:47.973650+09:00
idle dispatcher 'D000' terminated, pid = (32, 2)
2022-03-30T17:07:47.975553+09:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
2022-03-30T17:07:47.987688+09:00
Assigning activation ID 2271416007 (0x87630ec7)
2022-03-30T17:07:47.998745+09:00
Thread 1 advanced to log sequence 33 (thread open)
Redo log for group 2, sequence 33 is not located on DAX storage
Thread 1 opened at log sequence 33
  Current log# 2 seq# 33 mem# 0: /oracle/app/oracle/oradata/ORAADGDR/redo02.log
Successful open of redo thread 1
2022-03-30T17:07:48.019010+09:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
Undo initialization recovery: Parallel FPTR failed: start:28950790 end:28950794 diff:4 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 28950790 end: 28950807 diff: 17 ms (0.0 seconds)
2022-03-30T17:07:48.040468+09:00
ARC0 (PID:13346): Archived Log entry 10 added for T-1.S-32 ID 0x87630ec7 LAD:1
2022-03-30T17:07:48.049714+09:00
TT05 (PID:13516): Sleep 5 seconds and then try to clear SRLs in 2 time(s)
2022-03-30T17:07:48.057440+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_tt03_13512.trc:
ORA-16146: control file enqueue unavailable
2022-03-30T17:07:48.057510+09:00
TT03 (PID:13512): Error 16146 for LNO:2 to 'ORAADG'
2022-03-30T17:07:48.059313+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_tt03_13512.trc:
ORA-16146: control file enqueue unavailable
2022-03-30T17:07:48.059419+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_tt03_13512.trc:
ORA-16146: control file enqueue unavailable
2022-03-30T17:07:48.085188+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_arc2_13354.trc:
ORA-16146: control file enqueue unavailable
2022-03-30T17:07:48.087210+09:00
ARC2 (PID:13354): FAL: Error 16146 creating remote archived redo log file 'ORAADG'
ARC2 (PID:13354): FAL archive failed with error 16146 (see trace for details)
ARC2 (PID:13354): FAL archive failed, archiver continuing
2022-03-30T17:07:48.088579+09:00
ORACLE Instance ORAADGDR, archival error, archiver continuing
2022-03-30T17:07:48.088622+09:00
ORA-16055: FAL request rejected
2022-03-30T17:07:48.088662+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_arc2_13354.trc:
ORA-16055: FAL request rejected
2022-03-30T17:07:48.118871+09:00
[13387] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 28950807 end: 28950887 diff: 80 ms (0.1 seconds)
Undo initialization finished serial:0 start:28950790 end:28950889 diff:99 ms (0.1 seconds)
Dictionary check beginning
Dictionary check complete
Control autobackup written to DISK device
 
handle '/oracle/app/oracle/rman/db_ctl_c-2271425693-20220330-07'
 
2022-03-30T17:07:48.212210+09:00
Starting background process SMCO
Database Characterset is KO16MSWIN949
2022-03-30T17:07:48.218997+09:00
SMCO started with pid=37, OS id=13518 
No Resource Manager plan active
Starting background process SVCB
2022-03-30T17:07:48.373685+09:00
SVCB started with pid=46, OS id=13524 
joxcsys_required_dirobj_exists: directory object exists with required path /oracle/app/oracle/product/19c/javavm/admin/, pid 13387 cid 0
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2022-03-30T17:07:48.870077+09:00
AQPC started with pid=58, OS id=13542 
Starting background process MMNL
2022-03-30T17:07:48.949963+09:00
MMNL started with pid=59, OS id=13544 
Starting background process MMON
2022-03-30T17:07:48.960434+09:00
MMON started with pid=60, OS id=13546 
2022-03-30T17:07:49.118474+09:00
TMI: adbdrv open database END 2022-03-30 17:07:49.118381
Starting background process CJQ0
2022-03-30T17:07:49.130126+09:00
CJQ0 started with pid=7, OS id=13548 
Completed: ALTER DATABASE OPEN
2022-03-30T17:07:49.188825+09:00
ALTER SYSTEM SET fal_server='' SCOPE=BOTH;
2022-03-30T17:07:49.211881+09:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
2022-03-30T17:07:49.217530+09:00
 
 
***********************************************************************
 
Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg2)(USER=oracle))))
 
  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.12.0.0.0
  Time: 30-MAR-2022 17:07:49
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537
    
TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507
    
TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0
2022-03-30T17:07:49.222992+09:00
 
 
***********************************************************************
 
Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg2)(USER=oracle))))
 
  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.12.0.0.0
  Time: 30-MAR-2022 17:07:49
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537
    
TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507
    
TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0
 
 
***********************************************************************
 
Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg2)(USER=oracle))))
 
  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.12.0.0.0
  Time: 30-MAR-2022 17:07:49
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537
    
TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507
    
TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0
 
 
***********************************************************************
 
 
***********************************************************************
 
Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg2)(USER=oracle))))
 
Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg2)(USER=oracle))))
 
  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
 
  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.12.0.0.0
  Version 19.12.0.0.0
  Time: 30-MAR-2022 17:07:49
  Time: 30-MAR-2022 17:07:49
  Tracing not turned on.
  Tracing not turned on.
  Tns error struct:
  Tns error struct:
    ns main err code: 12537
    ns main err code: 12537
    
    
TNS-12537: TNS:connection closed
TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    ns secondary err code: 12560
    nt main err code: 507
    nt main err code: 507
    
    
TNS-00507: Connection closed
    nt secondary err code: 0
TNS-00507: Connection closed
    nt OS err code: 0
    nt secondary err code: 0
    nt OS err code: 0
 
 
***********************************************************************
 
 
***********************************************************************
 
Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg2)(USER=oracle))))
 
Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg2)(USER=oracle))))
 
  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
 
  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.12.0.0.0
  Version 19.12.0.0.0
  Time: 30-MAR-2022 17:07:49
  Time: 30-MAR-2022 17:07:49
  Tracing not turned on.
  Tracing not turned on.
  Tns error struct:
  Tns error struct:
    ns main err code: 12537
    ns main err code: 12537
    
    
TNS-12537: TNS:connection closed
TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    ns secondary err code: 12560
    nt main err code: 507
    nt main err code: 507
    
    
TNS-00507: Connection closed
TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0
    nt secondary err code: 0
    nt OS err code: 0
2022-03-30T17:07:49.244237+09:00
TT00 (PID:13348): Error 12537 received logging on to the standby
2022-03-30T17:07:49.244368+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_tt00_13348.trc:
ORA-12537: TNS:connection closed
TT00 (PID:13348): krsg_check_connection: Error 12537 connecting to standby 'ORAADG'
 
 
***********************************************************************
 
Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg2)(USER=oracle))))
 
  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.12.0.0.0
  Time: 30-MAR-2022 17:07:49
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537
    
TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507
    
TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0
2022-03-30T17:07:49.253538+09:00
TT03 (PID:13512): Error 12537 received logging on to the standby
2022-03-30T17:07:49.253671+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_tt03_13512.trc:
ORA-12537: TNS:connection closed
TT03 (PID:13512): Error 12537 for LNO:2 to 'ORAADG'
2022-03-30T17:07:49.257800+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_tt03_13512.trc:
ORA-12537: TNS:connection closed
2022-03-30T17:07:49.257903+09:00
Errors in file /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/ORAADGDR_tt03_13512.trc:
ORA-12537: TNS:connection closed
2022-03-30T17:08:03.083491+09:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
2022-03-30T17:08:09.298508+09:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
2022-03-30T17:08:10.858129+09:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

 

 

switchover시 2번 노드 브로커 log

switchover시 2번 브로커 log

2022-03-30T17:07:28.918+09:00
Initiating a healthcheck...
Forwarding CTL_SWITCH operation to member ORAADG for processing
FSFO SetState(st=12 "SET OBID", fl=0x0 "", ob=0x1, tgt=0, v=0)
Updated broker configuration file available, loading from "/oracle/app/oracle/product/19c/dbs/dr2ORAADGDR.dat"
2022-03-30T17:07:30.113+09:00
Switchover processing to this database has started
Notifying Oracle Clusterware to prepare target standby database for switchover
2022-03-30T17:07:38.880+09:00
Switchover in progress...
2022-03-30T17:07:44.800+09:00
Updated broker configuration file available, loading from "/oracle/app/oracle/product/19c/dbs/dr1ORAADGDR.dat"
2022-03-30T17:07:47.925+09:00
New primary database after switchover
Informing client: OPENING instance 'ORAADGDR' of database 'ORAADGDR'
Informing client: STARTUP instance 'ORAADG' of database 'ORAADG'
Switchover completed, notifying client
Proceeding with buildup after switchover...
Notifying Oracle Clusterware to buildup the primary database after switchover
Opening database after Switchover
2022-03-30T17:07:49.222+09:00
Evaluating archivelog destinations for switchover
SWITCHOVER TO ORAADGDR completed successfully, new primary database is ORAADGDR
2022-03-30T17:08:41.420+09:00
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               DR_ORAADG                         Warning  ORA-16608: one or more members have warnings
  Primary Database            ORAADGDR                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ORAADG                            Warning  ORA-16854: apply lag could not be determined

 

 

failover 테스트

*switchover 와 failover 란?

S/W나 H/W의 작업에 의해 계획된 전환은 Switchover라 하며,

운영 중에 Primary의 예기치 않은 장애에 대한 전환은 Failover 라고함

 

 

failover 테스트를 위해 다시 switchover를 해서 ORAADG가 primary, ORAADGDR이 standby로 변경됨

$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADG
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADG"
Connecting ...
Connected to "ORAADG"
Connected as SYSDBA.
New primary database "ORAADG" is opening...
Operation requires start up of instance "ORAADGDR" on database "ORAADGDR"
Starting instance "ORAADGDR"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADGDR"
Database mounted.
Database opened.
Connected to "ORAADGDR"
Switchover succeeded, new primary is "oraadg"
 
DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADG   - Primary database
    ORAADGDR - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 54 seconds ago)

 

 

1번 노드 shutdown abort 실행(1번 노드)

SQL> shutdown abort
ORACLE instance shut down.

 

 

브로커 configuration 확인

1번 노드
$ dgmgrl sys/oracle
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Mar 30 17:19:26 2022
Version 19.12.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to an idle instance.
Connected as SYSDBA.
DGMGRL> show configuration
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
 
Configuration details cannot be determined by DGMGRL
 
2번 노드 dgmgrl로 접속
$ dgmgrl sys/oracle@ORAADGDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Mar 30 17:19:31 2022
Version 19.12.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "ORAADGDR"
Connected as SYSDBA.
DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADG   - Primary database
    Error: ORA-1034: ORACLE not available
 
    ORAADGDR - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
ERROR   (status updated 0 seconds ago)

1번 노드 DB가 비정상 종료 되었기 때문에 configuration 조회 불가능함

2번 노드에서 조회는 가능하지만 현재 1번 노드가 사용불가능 상태임을 알려줌

 

 

switchover, failover 테스트(1번 노드)

$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADGDR
Error: 
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
 
Configuration details cannot be determined by DGMGRL
 
DGMGRL> failover to ORAADGDR
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
 
Configuration details cannot be determined by DGMGRL

현재 1번 노드가 비정상 종료 되었기 때문에 switchover, failover이 불가능함

 

 

failover 테스트(2번 노드로 접속)

$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> failover to ORAADGDR
Performing failover NOW, please wait...
Failover succeeded, new primary is "oraadgdr"

failover 가 성공적으로 끝남 새로운 Primary db는 ORAADGDR(기존 2번 노드)가 됨

 

 

1번 노드 startup 시도(1번 노드)

SQL> startup
ORACLE instance started.
 
Total System Global Area 2516581456 bytes
Fixed Size            8899664 bytes
Variable Size          536870912 bytes
Database Buffers     1962934272 bytes
Redo Buffers            7876608 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from being opened

mount 까지는 올라오지만 open 되지 못함

 

 

상태 확인

DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADGDR - Primary database
    ORAADG   - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 56 seconds ago)
 
DGMGRL> show database ORAADGDR
 
Database - ORAADGDR
 
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ORAADGDR
 
Database Status:
SUCCESS
 
DGMGRL> show database ORAADG
 
Database - ORAADG
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      (unknown)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    ORAADG
 
Database Status:
DISABLED - ORA-16661: the standby database needs to be reinstated

ORAADG 에 대해 ORA-16661: the standby database needs to be reinstated가 발생함

1번 노드는 failover로 인해 사용불가 상태가됨

재사용을 하려면 flashback database 가 on으로 되어있거나

off 인경우에는 데이터가드 재구성을 해줘야함

 

 

failover 한 db 복구(reinstate)(flashback database 가 on으로 설정되어 있어야함)

failover시 db가 open 상태였다면 open 상태에서 reinstate를 실행하면 되고

shutdown 되어있다면 startup mount 로 기동후 진행해야함

startup mount 기동

SQL> startup mount
ORACLE instance started.
 
Total System Global Area 2516581456 bytes
Fixed Size            8899664 bytes
Variable Size          536870912 bytes
Database Buffers     1962934272 bytes
Redo Buffers            7876608 bytes
Database mounted.

 

 

db 상태 확인

1번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADG                   READ WRITE        PRIMARY         FAILED DESTINATION
 
2번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADGDR               READ WRITE        PRIMARY         NOT ALLOWED

1번노드가 FAILED DESTINATION 로 나타남

 

 

reinstate 수행(2번 노드)(flashback database 가 on으로 설정되어있어야 가능)

$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> reinstate database 'ORAADG'
Reinstating database "ORAADG", please wait...
Reinstatement of database "ORAADG" succeeded

정상적으로 완료됨

 

 

설정 확인

DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADGDR - Primary database
    ORAADG   - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 60 seconds ago)
 
DGMGRL> show database ORAADG
 
Database - ORAADG
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 229.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORAADG
 
Database Status:
SUCCESS
 
DGMGRL> show database ORAADGDR
 
Database - ORAADGDR
 
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ORAADGDR
 
Database Status:
SUCCESS

ORAADGDR이 primary가 되고 ORAADG가 standby가 됨

 

 

db 상태 확인

1번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADG                   READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
 
2번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADGDR               READ WRITE        PRIMARY         TO STANDBY

ORAADGDR이 primary(read write)가 되고 ORAADG가 standby(read only)가 됨

 

 

다시 원래 primary와 standby로 변경하기위해 switchover

$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADG
Performing switchover NOW, please wait...
New primary database "ORAADG" is opening...
Operation requires start up of instance "ORAADGDR" on database "ORAADGDR"
Starting instance "ORAADGDR"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADGDR"
Database mounted.
Database opened.
Connected to "ORAADGDR"
Switchover succeeded, new primary is "oraadg"

 

 

상태 확인

DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADG   - Primary database
    ORAADGDR - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 86 seconds ago)
 
DGMGRL> show database ORAADG
 
Database - ORAADG
 
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ORAADG
 
Database Status:
SUCCESS
 
DGMGRL> show database ORAADGDR
 
Database - ORAADGDR
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 16.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORAADGDR
 
Database Status:
SUCCESS

 

 

db 상태 확인

1번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADG                   READ WRITE        PRIMARY         TO STANDBY
 
2번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADGDR               READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

다시 ORAADG가 primary(read write)가 되고 ORAADGDR이 standby(read only)가 됨

 

 

failover 한 db 복구(adg 재구성)(flashback database 가 off 인 경우 adg 재구성)

기존 브로커 configuration 제거(2번노드)

$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> remove configuration
Warning: ORA-16620: one or more members could not be reached for a remove operation
 
Removed configuration

 

 

브로커 파라미터 false 설정(1번, 2번 노드)

1번 노드가 이미 shutdown 상태라면 아래 파라미터 파일 수정시 수정해줘도됨

SQL> alter system set dg_broker_start=false;
 
System altered.

 

 

장애 발생한 1번 노드 shutdown(1번 노드)

이미 shutdown 상태라면 다음단계로 넘어가도됨

SQL> shutdown immediate

 

 

브로커 설정파일 삭제(1번, 2번 노드)

1번 노드
$ cd $ORACLE_HOME/dbs
$ rm -rf dr1ORAADG.dat dr2ORAADG.dat
 
2번 노드
$ cd $ORACLE_HOME/dbs
$ rm -rf dr1ORAADGDR.dat dr2ORAADGDR.dat

 

 

파라미터 생성(1번 노드)

SQL> create pfile from spfile;
 
File created.

 

 

파라미터 파일 수정(1번 노드)

$ cd $ORACLE_HOME/dbs
$ vi initORAADG.ora 
*.audit_file_dest='/oracle/app/oracle/admin/ORAADG/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADG/control01.ctl','/oracle/app/oracle/oradata/ORAADG/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_name='ORAADG'
*.db_recovery_file_dest_size=7851m
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/'
*.db_unique_name='ORAADG'
*.dg_broker_start=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGXDB)'
*.fal_client='ORAADG'
*.fal_server=''
*.local_listener='LISTENER_ORAADG'
*.log_archive_config='DG_CONFIG=(ORAADG,ORAADGDR)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch/adg1 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADG'
#*.log_archive_dest_2=''
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert = '/oracle/app/oracle/oradata/ORAADGDR/','/oracle/app/oracle/oradata/ORAADG/'
*.log_file_name_convert = '/oracle/app/oracle/oradata/ORAADGDR/','/oracle/app/oracle/oradata/ORAADG/'

11번째 줄 db_broker_start FALSE 처리

20번째 줄 log_archive_dest_2 주석 처리

34, 35번째 줄 convert 절 추가

 

 

기존 spfile 백업(1번 노드)

$ mv spfileORAADG.ora spfileORAADG.ora.bak

 

 

pfile을 이용해 db nomount 기동(1번 노드)

SQL> startup nomount pfile='/oracle/app/oracle/product/19c/dbs/initORAADG.ora'
ORACLE instance started.
 
Total System Global Area 2516581456 bytes
Fixed Size            8899664 bytes
Variable Size          536870912 bytes
Database Buffers     1962934272 bytes
Redo Buffers            7876608 bytes

 

 

rman 원격 접속

$ rman target sys/oracle@ORAADGDR auxiliary sys/oracle@ORAADG
 
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 30 17:43:24 2022
Version 19.12.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ORAADG (DBID=2271425693)
connected to auxiliary database: ORAADG (not mounted)

 

 

duplicate 명령으로 복제

RMAN> duplicate target database for standby from active database;
 
Starting Duplicate Db at 30-MAR-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=389 device type=DISK
 
contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/oracle/app/oracle/product/19c/dbs/orapwORAADG'   ;
}
executing Memory Script
 
Starting backup at 30-MAR-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
Finished backup at 30-MAR-22
 
contents of Memory Script:
{
   restore clone from service  'ORAADGDR' standby controlfile;
}
executing Memory Script
 
Starting restore at 30-MAR-22
using channel ORA_AUX_DISK_1
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/app/oracle/oradata/ORAADG/control01.ctl
output file name=/oracle/app/oracle/oradata/ORAADG/control02.ctl
Finished restore at 30-MAR-22
 
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
 
sql statement: alter database mount standby database
 
contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/oracle/app/oracle/oradata/ORAADG/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/oracle/app/oracle/oradata/ORAADG/system01.dbf";
   set newname for datafile  2 to 
 "/oracle/app/oracle/oradata/ORAADG/sysaux01.dbf";
   set newname for datafile  3 to 
 "/oracle/app/oracle/oradata/ORAADG/undotbs01.dbf";
   set newname for datafile  4 to 
 "/oracle/app/oracle/oradata/ORAADG/users01.dbf";
   restore
   from  nonsparse   from service 
 'ORAADGDR'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script
 
executing command: SET NEWNAME
 
renamed tempfile 1 to /oracle/app/oracle/oradata/ORAADG/temp01.dbf in control file
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
Starting restore at 30-MAR-22
using channel ORA_AUX_DISK_1
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/ORAADG/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/app/oracle/oradata/ORAADG/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/ORAADG/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/ORAADG/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 30-MAR-22
 
sql statement: alter system archive log current
 
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
 
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1100713801 file name=/oracle/app/oracle/oradata/ORAADG/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1100713801 file name=/oracle/app/oracle/oradata/ORAADG/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1100713801 file name=/oracle/app/oracle/oradata/ORAADG/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1100713801 file name=/oracle/app/oracle/oradata/ORAADG/users01.dbf
Finished Duplicate Db at 30-MAR-22

 

 

상태확인(1번 노드)

SQL> select instance_name, status from v$instance;
 
INSTANCE_NAME     STATUS
---------------- ------------
ORAADG         MOUNTED

duplicate 완료 후에는 2번 노드가 mount 상태가됨

 

 

db role 확인(1번, 2번 노드)

1번 노드
SQL>  select database_role, open_mode from v$database;
 
DATABASE_ROLE     OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
 
2번 노드
SQL>  select database_role, open_mode from v$database;
 
DATABASE_ROLE     OPEN_MODE
---------------- --------------------
PRIMARY      READ WRITE

 

 

파라미터 파일 변경

spfile 생성(1번 노드)

SQL> create spfile from pfile;
 
File created.

 

 

1번 노드 mount 재기동(1번 노드)

SQL> shutdown immediate
SQL> startup mount

 

 

브로커 파라미터 true 설정(1번, 2번 노드)

SQL> alter system set dg_broker_start=true scope=both;
 
System altered.

 

 

configuration 설정(2번노드)

$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> create configuration 'DR_ORAADG' as primary database is 'ORAADGDR' connect identifier is 'ORAADGDR';
Configuration "DR_ORAADG" created with primary database "ORAADGDR"
DGMGRL> add database 'ORAADG' as connect identifier is 'ORAADG';
Database "ORAADG" added

 

 

설정 확인

DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADGDR - Primary database
    ORAADG   - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
DISABLED

 

 

설정 활성화

$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> enable configuration
Enabled.

 

 

enable 후 바로 조회 했을 때는 에러가 발생했지만 잠시후 시도하니 에러가 사라짐

에러가 사라지지 않을 경우 노드1(standby db) shutdown immediate 후 startup mount로 재기동

DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADGDR - Primary database
    ORAADG   - Physical standby database 
      Warning: ORA-16854: apply lag could not be determined
 
Fast-Start Failover:  Disabled
 
Configuration Status:
WARNING   (status updated 34 seconds ago)
 
잠시후 다시 조회(1분 미만) 또는 standby 재기동 mount 후
DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADGDR - Primary database
    ORAADG   - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 36 seconds ago)

정상화됨

 

 

로그스위치 확인

1번, 2번 노드 모두 alert log tail 로 확인

1번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/alert_ORAADG.log 
 
2번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/alert_ORAADGDR.log 

 

 

2번 노드(primary)에서 로그 스위치 실행(2번 노드)

SQL> alter system switch logfile;
 
System altered.

 

 

1번, 2번 노드 모두 alert log tail 로 확인

1번 노드
2022-03-30T18:03:25.796271+09:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 4 Reading mem 0
  Mem# 0: /oracle/app/oracle/oradata/ORAADG/standby_redo01.log
 
2번 노드
2022-03-30T18:03:24.530214+09:00
Thread 1 advanced to log sequence 4 (LGWR switch),  current SCN: 1524525
  Current log# 1 seq# 4 mem# 0: /oracle/app/oracle/oradata/ORAADGDR/redo01.log
2022-03-30T18:03:24.536695+09:00
ARC0 (PID:13847): Archived Log entry 20 added for T-1.S-3 ID 0x87637cbb LAD:1

정상적으로 redo가 양쪽에 기록됨

 

 

db상태확인

1번 노드
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS      DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADG                   MOUNTED            PHYSICAL STANDBY NOT ALLOWED      ENABLED
 
2번 노드
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS      DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADGDR               READ WRITE        PRIMARY         TO STANDBY       ENABLED

Oracle Data Guard 구성 완료(Standby db가 mount 상태)

 

 

Active Data Guard로 전환(Standby db를 open 상태로 전환)

Managed Recovery Process 일시 중지 후 open 후 MRP 다시 기동(1번 노드)

SQL> alter database recover managed standby database cancel;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> alter database recover managed standby database using current logfile disconnect;
 
Database altered.

 

 

db 상태 확인

1번 노드
SQL> 
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS      DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADG                   READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED      ENABLED
 
2번 노드
SQL> 
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS      DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADGDR               READ WRITE        PRIMARY         TO STANDBY       ENABLED

active db 상태는 그대로지만 standby db는 open_mode가 read only로 변경됨

 

 

에러 방지를 위한 추가 설정

StaticConnectIdentifierd 변경($ORACLE_HOME/network/admin/tnstnames.ora에 있는 tns 별칭으로 변경)(2번 노드)

$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> edit database 'ORAADG' set property StaticConnectIdentifier = 'ORAADG';
Property "staticconnectidentifier" updated
DGMGRL> edit database 'ORAADGDR' set property StaticConnectIdentifier = 'ORAADGDR';
Property "staticconnectidentifier" updated

정상적으로 다시 구성이 완료됨

 

 

flashback database 확인(1번 노드)

SQL> select db_unique_name, flashback_on from v$database;
 
DB_UNIQUE_NAME               FLASHBACK_ON
------------------------------ ------------------
ORAADG                   NO

 

 

switchover로 정상화

$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> switchover to ORAADG
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADG"
Connecting ...
Connected to "ORAADG"
Connected as SYSDBA.
New primary database "ORAADG" is opening...
Operation requires start up of instance "ORAADGDR" on database "ORAADGDR"
Starting instance "ORAADGDR"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADGDR"
Database mounted.
Database opened.
Connected to "ORAADGDR"
Switchover succeeded, new primary is "oraadg"

 

 

상태 확인

DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADG   - Primary database
    ORAADGDR - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 65 seconds ago)
 
DGMGRL> show database ORAADG
 
Database - ORAADG
 
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ORAADG
 
Database Status:
SUCCESS
 
DGMGRL> show database ORAADGDR
 
Database - ORAADGDR
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 51.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORAADGDR
 
Database Status:
SUCCESS

정상적으로 ORAADG가 primary가 되고 ORAADGDR이 standby가 됨

 

 

switch over 후 ORAADG를 primary로 변경 한 상태에서

flashback database 활성화(선택)(fra 설정되어있어야 가능함)(1번 노드)

SQL> alter database flashback on;
 
Database altered.

 

 

flashback database 확인(1번, 2번 노드)

1번 노드
SQL> select db_unique_name, flashback_on from v$database;
 
DB_UNIQUE_NAME               FLASHBACK_ON
------------------------------ ------------------
ORAADG                   YES
 
2번 노드
SQL> select db_unique_name, flashback_on from v$database;
 
DB_UNIQUE_NAME               FLASHBACK_ON
------------------------------ ------------------
ORAADGDR               YES

failover 후 복구(adg 재구성) 완료 

 

 

참조 :

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/index.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/index.html

https://positivemh.tistory.com/674

Oracle Linux 6.8에 Oracle 11g R2 ADG 설치 가이드

https://dataforum.io/display/ORCL/Oracle+Database+19c+Data+Guard 

https://positivemh.tistory.com/819

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 1

https://positivemh.tistory.com/820

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 2

https://positivemh.tistory.com/821

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 3

썸네일용