Failover 수행

플래시백 활성화

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



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.



Standby로 failover 수행

FAILOVER TO [standby 글로벌 DB명];
-- 또는 강제로 Primary 정지
DGMGRL> FAILOVER TO orcl_stby;
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl_stby"



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



Failover 직후 데이터베이스 상태 확인

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



Primary 데이터베이스 reinstate 수행

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



Reinstate 후 데이터베이스 상태 확인

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



Primary로 복원

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"



복원 결과 확인

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



복원 후 데이터베이스 상태 확인

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