1. Failover 수행
A) 플래시백 활성화
sys 유저로 primary와 standby에서 수행
ALTER DATABASE FLASHBACK ON; ALTER SYSTEM SWITCH LOGFILE; SELECT FLASHBACK_ON FROM V$DATABASE; -- ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=720 SCOPE=BOTH; -- SHOW PARAMETER DB_FLASHBACK_RETENTION_TARGET -- SELECT FLASHBACK_SIZE, ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
FLASHBACK_ON
------------------------------------------------------
YES
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=720 SCOPE=BOTH;
System altered.
SQL> SHOW PARAMETER DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_flashback_retention_target integer 720
SQL> SELECT FLASHBACK_SIZE, ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------- ------------------------
629145600 130424832
{}
B) DGMGRL로 standby DB 접속
DGMGRL로 standby DB에 접속
dgmgrl sys/[sys 계정 암호]@[standby 네트워크 서비스명]
[oracle@orcl ~]$ dgmgrl sys/oracle@ORCL_STANDBY DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Feb 14 09:38:31 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "orcl_stby" Connected as SYSDBA.
C) Standby로 failover 수행
Standby로 failover 수행
FAILOVER TO [standby 글로벌 DB명]; -- 또는 강제로 Primary 정지
DGMGRL> FAILOVER TO orcl_stby; Performing failover NOW, please wait... Failover succeeded, new primary is "orcl_stby"
D) Failover 결과 확인
Failover 후 상태 확인
SHOW CONFIGURATION; SHOW DATABASE [primary 글로벌 DB명]; SHOW DATABASE [standby 글로벌 DB명];
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl_stby - Primary database
orcl - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 26 seconds ago)
DGMGRL> SHOW DATABASE orcl;
Database - orcl
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
orcl
Database Status:
DISABLED - ORA-16661: the standby database needs to be reinstated
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_stby
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
E) Failover 직후 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:40:30 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl READ WRITE PRIMARY NOT ALLOWED
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:41:04 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby READ WRITE PRIMARY NOT ALLOWED
2. Primary 데이터베이스 reinstate 수행
A) Reinstate 수행 및 결과 확인
Primary DB의 Reinstate 수행 및 결과 확인
REINSTATE DATABASE [primary 글로벌 DB명]; SHOW CONFIGURATION; SHOW DATABASE [primary 글로벌 DB명];
DGMGRL> REINSTATE DATABASE orcl;
Reinstating database "orcl", please wait...
Operation requires shut down of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
Connected to "orcl"
ORACLE instance shut down.
Operation requires start up of instance "orcl" on database "orcl"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl"
Database mounted.
Connected to "orcl"
Continuing to reinstate database "orcl" ...
Reinstatement of database "orcl" succeeded
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl_stby - Primary database
orcl - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 26 seconds ago)
DGMGRL> SHOW DATABASE orcl;
Database - orcl
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 751.00 KByte/s
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
B) Reinstate 후 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:45:02 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby READ WRITE PRIMARY TO STANDBY
3. Primary로 복원
A) Primary로 switchover
Primary로 switchover 수행
SWITCHOVER TO [primary 글로벌 DB명];
DGMGRL> SWITCHOVER TO orcl; Performing switchover NOW, please wait... Operation requires a connection to database "orcl" Connecting ... Connected to "orcl" Connected as SYSDBA. New primary database "orcl" is opening... Operation requires start up of instance "orcl" on database "orcl_stby" Starting instance "orcl"... Connected to an idle instance. ORACLE instance started. Connected to "orcl_stby" Database mounted. Connected to "orcl_stby" Switchover succeeded, new primary is "orcl"
B) 복원 결과 확인
복원 결과 확인
SHOW CONFIGURATION; SHOW DATABASE [primary 글로벌 DB명]; SHOW DATABASE [standby 글로벌 DB명];
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Error: ORA-16786: unable to access Oracle Data Guard broker configuration files
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 32 seconds ago)
DGMGRL> SHOW CONFIGURATION;
Configuration - dg_orcl
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 6 seconds ago)
DGMGRL> SHOW DATABASE orcl;
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE orcl_stby;
Database - orcl_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 159.00 KByte/s
Real Time Query: OFF
Instance(s):
orcl
Database Status:
SUCCESS
C) 복원 후 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:49:43 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl READ WRITE PRIMARY TO STANDBY
[oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 09:49:53 2024 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> set lines 200 SQL> col db_unique_name form a20 SQL> col open_mode form a20 SQL> col database_role form a20 SQL> col switchover_status form a20 SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS -------------------- -------------------- -------------------- -------------------- orcl_stby MOUNTED PHYSICAL STANDBY NOT ALLOWED
