소스 보기

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

Failover 테스트를 수행한 후 원복을 수월하게 하기 위해서 flashback을 활성화시킵니다.

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가 정상적으로 완료되었는지 확인합니다.

기존의 Primary는 disabled로 복구가 필요한 상태입니다.

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로 되어 있는 것을 확인할 수 있습니다.

Failover 이전의 primary는 현재 비정상적이므로 standby에서의 switchover는 now 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

Flashback Database가 활성화 되어 있었다면, reinstate 명령어로 간단하게 복구할 수 있습니다.

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와 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"

복구가 된 primary로 switchover를 수행할 수 있습니다.

복원 결과 확인

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

정상적으로 switchover 되었는지 확인할 수 있습니다.

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

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

원래의 상태로 복구된 것을 확인할 수 있습니다.