Data Guard Physical Standby (19c): Data Guard Broker Configuration, Switchover/Failover - Part 3/3
- ousseinioumarou68
- 1. Nov. 2021
- 13 Min. Lesezeit
Aktualisiert: 22. Okt.
Assumption/Prerequistes
A data guard sytem with a primary database and a physical database has been successfully setup and is running. Now, the data guard broker needs to be configured in order to perform switchover and failover operations.
1. Data Guard Broker Configuration
On primary
SYS@cdbdg> show parameter dg_broker_start
NAME TYPE VALUE
---------------------------- ----------- ---------------------
dg_broker_start boolean FALSE
SYS@cdbdg>
SYS@cdbdg> alter system set dg_broker_start=true;
System altered.
SYS@cdbdg>
On Standby
SYS@cdbdg> show parameter dg_broker_start
NAME TYPE VALUE
---------------------------- ----------- --------------------
dg_broker_start boolean FALSE
SYS@cdbdg> alter system set dg_broker_start=true;
System altered.
SYS@cdbdg>
SYS@cdbdg> show parameter dg_broker_start
NAME TYPE VALUE
---------------------------- ----------- ----------------
dg_broker_start boolean TRUE
SYS@cdbdg>
On the primary server, issue the following command to register the primary server with the broker.
[oracle@dbsrv1 ~]$ dgmgrl sys/oracle_4U@cdbdg
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Oct 25 19:33:27 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cdbdg"
Connected as SYSDBA.
DGMGRL> create configuration dguard19c_config as primary database is cdbdg connect identifier is cdbdg;
Configuration "dguard19c_config" created with primary database "cdbdg"
DGMGRL>
The above create command internally will set the value for the log_archive_dest_2
SYS@cdbdg> show parameter log_archive_dest_2
NAME TYPE VALUE
--------------------------- ----------- ---------------------------
log_archive_dest_2 string service="cdbdg_stby", ASYNC NO
AFFIRM delay=0 optional compre
ssion=disable max_failure=0 re
open=300 db_unique_name="cdbdg
_stby" net_timeout=30, valid_for=
(online_logfile,all_roles)
log_archive_dest_20 string
SYS@cdbdg>
Add the Standy site in DG Broker
DGMGRL> add database cdbdg_stby as connect identifier is cdbdg_stby maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Failed.
Solution: reset LOG_ARCHIVE_DEST_2 on Standby site
SYS@cdbdg> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
-------------------------- ---------- ------------------------------
log_archive_dest_2 string service=cdbdg ASYNC valid_for=
(ONLINE_LOGFILE,PRIMARY_ROLE)
db_unique_name=cdbdg
SYS@cdbdg> alter system set LOG_ARCHIVE_DEST_2='' scope=both;
System altered.
SYS@cdbdg> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
------------------------- ----------- ----------------------
log_archive_dest_2 string
SYS@cdbdg>
DGMGRL> add database cdbdg_stby as connect identifier is cdbdg_stby maintained as physical;
Database "cdbdg_stby" added
DGMGRL>
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg - Primary database
cdbdg_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 23 seconds ago)
DGMGRL>
DGMGRL> show database cdbdg;
Database - cdbdg
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
cdbdg
Database Status:
SUCCESS
DGMGRL> show database cdbdg_stby;
Database - cdbdg_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: 1.00 KByte/s
Real Time Query: OFF
Instance(s):
cdbdg
Database Status:
SUCCESS
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg - Primary database
cdbdg_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 55 seconds ago)
DGMGRL>
Now set log_archive_dest_2 on Standby
SYS@cdbdg> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
--------------------- ------------ -------------------------
log_archive_dest_2 string
SYS@cdbdg>
SYS@cdbdg> alter system set log_archive_dest_2='service=cdbdg ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=cdbdg';
System altered.
SYS@cdbdg> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
--------------------- ----------- ------------------------------
log_archive_dest_2 string service=cdbdg ASYNC valid_for=
(ONLINE_LOGFILE,PRIMARY_ROLE)
db_unique_name=cdbdg
SYS@cdbdg>
Stop/Start Managed Recovery
Managed recovery can be stopped and started on the standby database using the following commands from SQL*Plus.
Stop managed recovery
SYS@cdbdg> alter database recover managed standby database cancel;
Database altered.
SYS@cdbdg>
Start managed recovery.
SYS@cdbdg> alter database recover managed standby database disconnect;
Database altered.
SYS@cdbdg>
2. Switchover Test
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs.
This process is known as a Switchover and can be performed using the following commands.
Connect to the primary database (cdbdg) and switchover to the standby database (cdbdg_stby).
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg - Primary database
cdbdg_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 55 seconds ago)
DGMGRL>
DGMGRL> switchover to cdbdg_stby;
Performing switchover NOW, please wait...
Operation requires a connection to database "cdbdg_stby"
Connecting ...
Connected to "cdbdg_stby"
Connected as SYSDBA.
New primary database "cdbdg_stby" is opening...
Oracle Clusterware is restarting database "cdbdg" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "cdbdg"
Connected to "cdbdg"
Switchover succeeded, new primary is "cdbdg_stby"
DGMGRL>
Verify the database roles transition
[oracle@dbsrv1 ~]$ srvctl config database -d cdbdg
Database unique name: cdbdg
Database name: cdbdg
Oracle home: /u01/app/oracle/product/19.3.0/db_1
Oracle user: oracle
Spfile: +DATA/CDBDG/PARAMETERFILE/spfile.269.1086651125
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
OSDBA group: oinstall
OSOPER group:
Database instance: cdbdg
[oracle@dbsrv1 ~]$
crsctl stat res -t | grep -i cdbdg
ora.cdbdg.db
[oracle@dbsrv1 ~]$
[oracle@dbsrv1 ~]$ crsctl stat res ora.cdbdg.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbdg.db
1 ONLINE INTERMEDIATE dbsrv1 Mounted (Closed),HOME=/u01/app
/oracle/product/19.3.0/db_1,STABLE
--------------------------------------------------------------------------------
[oracle@dbsrv1 ~]$
[oracle@dbsrv2 ~]$ srvctl config database -d cdbdg_stby
Database unique name: cdbdg_stby
Database name: cdbdg
Oracle home: /u01/app/oracle/product/19.3.0/db_1
Oracle user: oracle
Spfile: +DATA/CDBDG_STBY/PARAMETERFILE/spfile.276.1086884021
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
OSDBA group: oinstall
OSOPER group:
Database instance: cdbdg
[oracle@dbsrv2 ~]$
[oracle@dbsrv2 ~]$ crsctl stat res ora.cdbdg_stby.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbdg_stby.db
1 ONLINE ONLINE dbsrv2 Open,HOME=/u01/app/oracle/
product/19.3.0/db_1,STABLE
--------------------------------------------------------------------------------
[oracle@dbsrv2 ~]$
Switch logfile and check standby site
SYS@cdbdg> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
2 from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
--------- ------------------------------ -------------------- ---------------- -------------------- --------
CDBDG cdbdg_stby READ WRITE PRIMARY TO STANDBY ENABLED
SYS@cdbdg>
SYS@cdbdg> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SYS@cdbdg>
SYS@cdbdg> alter system switch logfile;
System altered.
SYS@cdbdg>/
System altered.
SYS@cdbdg>
SYS@cdbdg> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SYS@cdbdg>
SYS@cdbdg> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
2 from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
--------- ------------------------------ -------------------- ---------------- -------------------- --------
CDBDG cdbdg MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
SYS@cdbdg> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 0
Current log sequence 20
SYS@cdbdg>
SYS@cdbdg> select sequence#, applied, first_time, next_time
2 from v$archived_log order by sequence#;
SEQUENCE# APPLIED FIRST_TI NEXT_TIM
---------- --------- -------- --------
8 YES 23.10.21 23.10.21
9 YES 23.10.21 24.10.21
10 YES 24.10.21 25.10.21
11 YES 25.10.21 25.10.21
12 YES 25.10.21 25.10.21
13 YES 25.10.21 25.10.21
14 YES 25.10.21 25.10.21
14 YES 25.10.21 25.10.21
15 YES 25.10.21 25.10.21
15 YES 25.10.21 25.10.21
16 YES 25.10.21 25.10.21
16 YES 25.10.21 25.10.21
17 YES 25.10.21 25.10.21
17 YES 25.10.21 25.10.21
18 YES 25.10.21 25.10.21
18 YES 25.10.21 25.10.21
19 YES 25.10.21 25.10.21
19 YES 25.10.21 25.10.21
20 NO 25.10.21 25.10.21
20 YES 25.10.21 25.10.21
21 YES 25.10.21 25.10.21
22 YES 25.10.21 25.10.21
22 rows selected.
Let's switch back to the original primary. Connect to the new primary (cdbdb_stby)
and switchover to the new standby database (cdbdg).
[oracle@dbsrv2 ~]$ dgmgrl sys/oracle_4U@cdbdg_stby
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Oct 25 20:55:03 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cdbdg_stby"
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg_stby - Primary database
cdbdg - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 24 seconds ago)
DGMGRL> switchover to cdbdg;
Performing switchover NOW, please wait...
Operation requires a connection to database "cdbdg"
Connecting ...
Connected to "cdbdg"
Connected as SYSDBA.
New primary database "cdbdg" is opening...
Oracle Clusterware is restarting database "cdbdg_stby" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "cdbdg_stby"
Connected to "cdbdg_stby"
Switchover succeeded, new primary is "cdbdg"
DGMGRL>
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg - Primary database
cdbdg_stby - Physical standby database
Warning: ORA-16854: apply lag could not be determined
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 9 seconds ago)
DGMGRL>
[oracle@dbsrv2 ~]$ crsctl stat res ora.cdbdg_stby.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbdg_stby.db
1 ONLINE INTERMEDIATE dbsrv2 Mounted (Closed),HOME=/u01/app
/oracle/product/19.3.0/db_1,STABLE
--------------------------------------------------------------------------------
[oracle@dbsrv2 ~]$
SYS@cdbdg> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
2 from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
--------- ------------------------------ -------------------- ---------------- -------------------- --------
CDBDG cdbdg_stby MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
SYS@cdbdg>
[oracle@dbsrv1 ~]$ crsctl stat res ora.cdbdg.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbdg.db
1 ONLINE ONLINE dbsrv1 Open,HOME=/u01/app/oracle
/product/19.3.0/db_1,STABLE
--------------------------------------------------------------------------------
[oracle@dbsrv1 ~]$
SYS@cdbdg> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
2 from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
--------- ------------------------------ -------------------- ---------------- -------------------- --------
CDBDG cdbdg READ WRITE PRIMARY TO STANDBY ENABLED
SYS@cdbdg>
3. Failover Test
Database Failover
If the primary database is not available the standby database can be activated as a primary database using the following statements.
Connect to the standby database (cdbdg_stby) and failover
[oracle@dbsrv2 ~]$ dgmgrl sys/oracle_4U@cdbdg_stby
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Oct 25 21:07:58 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cdbdg_stby"
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg - Primary database
cdbdg_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 61 seconds ago)
DGMGRL> failover to cdbdg_stby;
Performing failover NOW, please wait...
Failover succeeded, new primary is "cdbdg_stby"
DGMGRL>
DGMGRL> show configuration
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg_stby - Primary database
cdbdg - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 46 seconds ago)
DGMGRL>
Check the old primary database server configuration
[oracle@dbsrv1 ~]$ dgmgrl sys/oracle_4U@cdbdg
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Oct 25 21:14:51 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cdbdg"
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg - Primary database
cdbdg_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGM-17290: Role change detected. This database may no longer be the primary database.
DGMGRL>
DGMGRL> reinstate database cdbdg;
Reinstating database "cdbdg", please wait...
Error: ORA-16815: member does not need to be reinstated
Failed.
Reinstatement of database "cdbdg" failed
DGMGRL>
Since the standby database is now the primary database it should be backed up immediately.
The original primary database can now be configured as a standby. If flashback database was enabled on the primary database, then this can be done relatively easily with the following command.
Reinstate from the old standby
DGMGRL> reinstate database cdbdg;
Reinstating database "cdbdg", please wait...
Oracle Clusterware is restarting database "cdbdg" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "cdbdg"
Connected to "cdbdg"
Continuing to reinstate database "cdbdg" ...
Reinstatement of database "cdbdg" succeeded
DGMGRL>
DGMGRL> show configuration
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg_stby - Primary database
cdbdg - Physical standby database
Warning: ORA-16809: multiple warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 30 seconds ago)
DGMGRL>
[oracle@dbsrv1 ~]$ dgmgrl sys/oracle_4U@cdbdg
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Oct 25 21:21:12 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cdbdg"
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg_stby - Primary database
cdbdg - Physical standby database
Warning: ORA-16809: multiple warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 40 seconds ago)
DGMGRL>
[oracle@dbsrv1 ~]$ crsctl stat res ora.cdbdg.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbdg.db
1 ONLINE INTERMEDIATE dbsrv1 Mounted (Closed),,HOME=/u01/app
/oracle/product/19.3.0/db_1,STABLE
--------------------------------------------------------------------------------
[oracle@dbsrv1 ~]$
SYS@cdbdg> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
2 from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
--------- ------------------------------ -------------------- ---------------- -------------------- --------
CDBDG cdbdg MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
SYS@cdbdg>
DGMGRL> show database cdbdg;
Database - cdbdg
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: 21.00 KByte/s
Real Time Query: OFF
Instance(s):
cdbdg
Database Status:
SUCCESS
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg_stby - Primary database
cdbdg - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 57 seconds ago)
DGMGRL>
[oracle@dbsrv2 ~]$ crsctl stat res ora.cdbdg_stby.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbdg_stby.db
1 ONLINE ONLINE dbsrv2 Open,HOME=/u01/app/oracle
/product/19.3.0/db_1,STABLE
--------------------------------------------------------------------------------
[oracle@dbsrv2 ~]$
SYS@cdbdg> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
2 from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
--------- ------------------------------ -------------------- ---------------- -------------------- --------
CDBDG cdbdg_stby READ WRITE PRIMARY TO STANDBY ENABLED
SYS@cdbdg>
SYS@cdbdg> alter system switch logfile;
System altered.
SYS@cdbdg> l
1* alter system switch logfile
SYS@cdbdg> /
System altered.
SYS@cdbdg>
SYS@cdbdg> /
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
--------- ------------------------------ -------------------- ---------------- -------------------- --------
CDBDG cdbdg MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
SYS@cdbdg> select sequence#, applied, first_time, next_time
2 from v$archived_log order by sequence#;
SEQUENCE# APPLIED FIRST_TI NEXT_TIM
---------- --------- -------- --------
1 YES 25.10.21 25.10.21
2 YES 25.10.21 25.10.21
3 YES 25.10.21 25.10.21
...... ........ . . . . .. .
24 YES 25.10.21 25.10.21
25 YES 25.10.21 25.10.21
26 YES 25.10.21 25.10.21
26 YES 25.10.21 25.10.21
27 YES 25.10.21 25.10.21
27 YES 25.10.21 25.10.21
28 YES 25.10.21 25.10.21
34 rows selected.
SYS@cdbdg> select process, status, thread#, sequence#, block#, blocks
2 from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 1 2048 2035
ARCH CLOSING 1 3 1 105
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 4 1 11
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
MRP0 APPLYING_LOG 1 5 99 409600
RFS IDLE 1 0 0 0
RFS IDLE 1 5 99 1
RFS IDLE 0 0 0 0
10 rows selected.
SYS@cdbdg>
Switchover to cdbdg to have the original configuration
DGMGRL> switchover to cdbdg;
Performing switchover NOW, please wait...
Operation requires a connection to database "cdbdg"
Connecting ...
Connected to "cdbdg"
Connected as SYSDBA.
New primary database "cdbdg" is opening...
Oracle Clusterware is restarting database "cdbdg_stby" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "cdbdg_stby"
Connected to "cdbdg_stby"
Switchover succeeded, new primary is "cdbdg"
DGMGRL>
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg - Primary database
cdbdg_stby - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 5 seconds ago)
DGMGRL> show database cdbdg_stby;
Database - cdbdg_stby
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: 71.00 KByte/s
Real Time Query: OFF
Instance(s):
cdbdg
Database Status:
SUCCESS
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg - Primary database
cdbdg_stby - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 45 seconds ago)
DGMGRL> show configuration;
Configuration - dguard19c_config
Protection Mode: MaxPerformance
Members:
cdbdg - Primary database
cdbdg_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 22 seconds ago)
DGMGRL>
[oracle@dbsrv2 ~]$ crsctl stat res ora.cdbdg_stby.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbdg_stby.db
1 ONLINE INTERMEDIATE dbsrv2 Mounted (Closed),HOME=/u01/app
/oracle/product/19.3.0/db_1,STABLE
--------------------------------------------------------------------------------
[oracle@dbsrv2 ~]$
SYS@cdbdg> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
2 from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
--------- ------------------------------ -------------------- ---------------- -------------------- --------
CDBDG cdbdg_stby MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
SYS@cdbdg>
[oracle@dbsrv1 ~]$ crsctl stat res ora.cdbdg.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbdg.db
1 ONLINE ONLINE dbsrv1 Open,HOME=/u01/app/oracle
/product/19.3.0/db_1,STABLE
--------------------------------------------------------------------------------
[oracle@dbsrv1 ~]$
SYS@cdbdg> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
2 from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
--------- ------------------------------ -------------------- ---------------- -------------------- --------
CDBDG cdbdg READ WRITE PRIMARY TO STANDBY ENABLED
SYS@cdbdg>
That's! I 'm glad to share my oracle database experiences with you
#####################################################
Here you can find the other articles:
Part 1:
Part 2:
#####################################################
Appendix: Disaster Recovery Concept and How it works!
Disaster recovery is an organization’s method of regaining access and functionality to its IT infrastructure after events like a natural disaster, cyber attack, or even business disruptions related to human errors. A variety of disaster recovery (DR) methods can be part of a disaster recovery plan. DR is one aspect of business continuity.
Disaster recovery relies upon the replication of data and computer processing in an off-premises location not affected by the disaster. When servers go down because of a natural disaster, equipment failure or cyber attack, a business needs to recover lost data from a second location where the data is backed up. Ideally, an organization can transfer its computer processing to that remote location as well in order to continue operations.
It should be a plan for an effective Disaster Recovery.
An oracle Database Data Guard can be used for Disaster Recovery.
Difference between Switchover and Failover
Switchover (Role Switch)
When implementing a DR solution, there is always a primary and secondary (standby) site. In some cases, there is more than one secondary site to provide extra protection.
When talking about Switchover, you can see it as a controlled process to change the roles between the primary and secondary (standby) sites. It is done in a controlled manner, and the key is that there is no data loss during this process. Yes, downtime is usually experienced with this process, but it is done during a scheduled, planned amount of time. Therefore, the risk with this process is low and should be tested regularly so that you are familiar with the process and the timing to help with your planning, as you will know exactly how long this process takes.
In Standby, we have implemented a process to perform this “Switchover” called “Graceful Switchover (GS).” During this process, the roles of the primary and standby database are interchanged, and the standby database will become the primary, and the original primary will become a standby.
During the switchover operation, there is a small outage. How long the outage lasts depends on several factors, including the network, the number, and the sizes of the database redo logs.
Failover (Activation)
Failover, also referred to by many as Activation of the standby database, is a process when you open the standby database read/write - this can be performed if a disaster has happened.
In this case, your primary database is not accessible to your users or, for some reason, became corrupt, for example, due to a faulty firmware upgrade on the storage or a malicious attack. During this “failover” process, the standby database is opened read/write, changing it to a primary database. Once the standby database is opened read/write, your users (applications) can use it. This process cannot be reversed, so the decision to failover should be carefully made. The failover process is initiated during a real disaster or severe outage.
It is also important to note that there is always the risk of potential data loss during a Failover operation. There is the possibility that changes have not been applied to the standby database yet. Due to the primary system not being available, changes are not applied to the standby database.
It is always important to ensure your standby database is up to date and that the time difference between the primary and standby does not exceed your recovery point and recovery time objectives (RPO and RTO).
Automatic Failover
Automatic failover is an external process that observes (monitors) the primary and standby configuration, ensuring both systems are running and that a certain set of rules are met. If any of these rules are not met, for example, if the primary is found to be inaccessible (down) for some time, the “observer” process can then perform a failover (activation) of the standby database. All of this is automated; the user will specify rules for the “observer” process to ensure there are no false positives.
Using Automatic Failover is becoming more and more important to companies as it can help reduce downtime by automating processes based on predefined rules.
If you are using the Oracle Database and looking at implementing a DR solution, you can make use of Standby™️, which helps you easily configure and perform tasks such as Graceful Switchover (Switchover), Failover (Activation), or even configure an observer process to perform Automatic Failover if required.
For more detail, please oracle documentation, Oracle Data Guard Installation, which has detailed instructions on how this can be configured.
Kommentare