srv-dg-db1 == primary
srv-dg-db2 == standby
Note: both databases are running and in sync. Standby is in Max. Performance mode with Real-Time Apply.
################################
Correct db parameter:
################################
>>> oracle@srv-dg-db1 / srv-dg-db2
SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;
Note: Otherwise you will get following error during the next step "ORA-16525: The Oracle Data Guard broker is not yet available."
#######################################
Register PRIMARY in broker:
#######################################
>>> oracle@srv-dg-db1
$ dgmgrl sys/***@PROD
++++++++++
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Mar 18 13:12:05 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "PROD"
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION dg_config_1 AS PRIMARY DATABASE IS PROD CONNECT IDENTIFIER IS PROD;
Configuration "dg_config_1" created with primary database "prod"
DGMGRL> show configuration;
Configuration - dg_config_1
Protection Mode: MaxPerformance
Members:
prod - Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
++++++++++
Note: it took about 2 min on my laptop environment without any database load.
#######################################
Register STANDBY in broker:
#######################################
>>> oracle@srv-dg-db1
DGMGRL> ADD DATABASE PROD_STBY AS CONNECT IDENTIFIER IS PROD_STBY MAINTAINED AS PHYSICAL;
##############
ISSUE:
##############
DGMGRL> ADD DATABASE PROD_STBY AS CONNECT IDENTIFIER IS PROD_STBY MAINTAINED AS PHYSICAL;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Failed.
##############
FIX:
##############
Ref. to "Create Configuration Failing with ORA-16698 (Doc ID 1582179.1)"
>>> oracle@srv-dg-db2 [STANDBY]
SQL> alter system set log_archive_dest_2='' scope=both;
Retry to register standby once again:
>>> oracle@srv-dg-db1
DGMGRL> ADD DATABASE PROD_STBY AS CONNECT IDENTIFIER IS PROD_STBY MAINTAINED AS PHYSICAL;
++++++++++
Database "prod_stby" added
++++++++++
DGMGRL> show configuration;
++++++++++
Configuration - dg_config_1
Protection Mode: MaxPerformance
Members:
prod - Primary database
prod_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
++++++++++
#######################################
Enable configuration:
#######################################
>>> oracle@srv-dg-db1
DGMGRL> ENABLE CONFIGURATION;
++++++++++
Enabled.
++++++++++
DGMGRL> show configuration;
++++++++++
Configuration - dg_config_1
Protection Mode: MaxPerformance
Members:
prod - Primary database
prod_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 9 seconds ago)
++++++++++
DGMGRL> SHOW DATABASE PROD;
++++++++++
Database - prod
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PROD
Database Status:
SUCCESS
++++++++++
DGMGRL> SHOW DATABASE PROD_STBY;
++++++++++
Database - prod_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: 7.00 KByte/s
Real Time Query: ON
Instance(s):
PROD
Database Status:
SUCCESS
++++++++++
#######################################
Switchover example:
#######################################
>>> oracle@srv-dg-db1
$ dgmgrl sys/***@PROD
DGMGRL> show database PROD staticconnectidentifier;
++++++++++
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv-dg-db1.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD_DGMGRL)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)))'
++++++++++
DGMGRL> show database PROD_STBY staticconnectidentifier;
++++++++++
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv-dg-db2.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD_STBY_DGMGRL)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)))'
++++++++++
DGMGRL> edit database PROD set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv-dg-db1.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)))';
++++++++++
Property "staticconnectidentifier" updated
++++++++++
DGMGRL> edit database PROD_STBY set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv-dg-db2.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD_STBY)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)))';
++++++++++
Property "staticconnectidentifier" updated
++++++++++
>>> primary
$ cat $ORACLE_HOME/network/admin/listener.ora
++++++++++
LISTENER_PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db1.oracle.com)(PORT = 1521))
)
)
SID_LIST_LISTENER_PROD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = PROD)
(SERVICE_NAME = PROD)
)
)
++++++++++
>>> standby
$ cat $ORACLE_HOME/network/admin/listener.ora
++++++++++
LISTENER_PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv-dg-db2.oracle.com)(PORT = 1521))
)
)
SID_LIST_LISTENER_PROD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = PROD)
(SERVICE_NAME = PROD_STBY)
)
)
++++++++++
Note: without above corrections switchover procedure will fail with error:
--------------------------------
DGMGRL> SWITCHOVER TO PROD_STBY;
Performing switchover NOW, please wait...
Operation requires a connection to database "prod_stby"
Connecting ...
Connected to "PROD_STBY"
Connected as SYSDBA.
New primary database "prod_stby" is opening...
Operation requires start up of instance "PROD" on database "prod"
Starting instance "PROD"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv-dg-db1.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD_DGMGRL)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Please complete the following steps to finish switchover:
start up instance "PROD" of database "prod"
--------------------------------
DGMGRL> SWITCHOVER TO PROD_STBY;
++++++++++
Performing switchover NOW, please wait...
Operation requires a connection to database "prod_stby"
Connecting ...
Connected to "PROD_STBY"
Connected as SYSDBA.
New primary database "prod_stby" is opening...
Operation requires start up of instance "PROD" on database "prod"
Starting instance "PROD"...
Connected to an idle instance.
ORACLE instance started.
Connected to "PROD"
Database mounted.
Database opened.
Connected to "PROD"
Switchover succeeded, new primary is "prod_stby"
++++++++++
#######################################
Broker Log file:
#######################################
From file below you can get additional information about errors identified by broker:
/u01/app/oracle/diag/rdbms/prod/PROD/trace/drcPROD.log