Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If a database created as Physical Standby is converted to a Logical Standby database while the Broker configuration is enabled, you may receive an error as follows.
ORA-16674: standby database type has changed
To fix this error, remove the relevant database from Broker, then add it again.
The process is as follows.
Step1:
Find out the Data Guard Broker Configuration
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DGMGRL> show configuration Configuration - DRSolution Protection Mode: MaxPerformance Members: primary - Primary database prmyFS - Far sync instance physical - Physical standby database snapshot - Snapshot standby database logical - Logical standby database logical2 - Physical standby database Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 53 seconds ago) |
Step2:
Convert Physical Standby database to Logical Standby
Convert Physical Standby database named “Logical2” to Logical Standby with SQL commands. You may want to read the article “Convert Physical Standby Database to Logical Standby Database” for this process.
Check if its converted correctly:
1 2 3 4 5 | [Logical2 - 07-APR-17] SQL> select open_mode, database_role, flashback_on from v$database; OPEN_MODE DATABASE_ROLE FLASHBACK_ON -------------------- ---------------- ------------------ READ WRITE LOGICAL STANDBY NO |
Step3:
Check Data Guard Broker Configuration
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | DGMGRL> show configuration Configuration - DRSolution Protection Mode: MaxPerformance Members: primary - Primary database prmyFS - Far sync instance physical - Physical standby database snapshot - Snapshot standby database logical - Logical standby database logical2 - Physical standby database (disabled) ORA-16674: standby database type has changed Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 41 seconds ago) |
Step4:
Try to Enable Logical2 Database
1 2 3 4 | DGMGRL> enable database logical2; Error: ORA-16674: standby database type has changed Failed. |
Step5:
REMOVE Logical2 database from configuration
1 2 3 4 | DGMGRL> remove database logical2; Error: ORA-16691: cannot remove a configuration member that is specified in a RedoRoutes property Failed. |
1 2 | DGMGRL> edit far_sync 'prmyFS' set property 'RedoRoutes'='(primary:physical,snapshot,logical ASYNC)'; Property "RedoRoutes" updated |
1 2 3 4 | DGMGRL> remove database logical2; Error: ORA-16691: cannot remove a configuration member that is specified in a RedoRoutes property Failed. |
1 2 | DGMGRL> edit far_sync 'physclFS' set property 'RedoRoutes'='(physical:primary,snapshot,logical ASYNC)'; Property "RedoRoutes" updated |
1 2 | DGMGRL> remove database logical2; Removed database "logical2" from the configuration |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DGMGRL> show configuration Configuration - DRSolution Protection Mode: MaxPerformance Members: primary - Primary database prmyFS - Far sync instance physical - Physical standby database snapshot - Snapshot standby database logical - Logical standby database Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 19 seconds ago) |
Step6:
Add the database to the configuration
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | DGMGRL> add database logical2 as connect identifier is logical2; Database "logical2" added DGMGRL> show configuration Configuration - DRSolution Protection Mode: MaxPerformance Members: primary - Primary database prmyFS - Far sync instance physical - Physical standby database snapshot - Snapshot standby database logical - Logical standby database Members Not Receiving Redo: physclFS - Far sync instance logical2 - Logical standby database (disabled) Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 49 seconds ago) |
Step7:
Configure RedoRoutes parameters
1 2 3 4 | DGMGRL> edit far_sync 'prmyFS' set property 'RedoRoutes'='(primary:physical,snapshot,logical,logical2 ASYNC)'; Property "RedoRoutes" updated DGMGRL> edit far_sync 'physclFS' set property 'RedoRoutes'='(physical:primary,snapshot,logical,logical2 ASYNC)'; Property "RedoRoutes" updated |
Step8:
ENABLE database in configuration
1 2 | DGMGRL> enable database logical2; Enabled. |
Step9:
Check if everything is correct
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DGMGRL> show configuration Configuration - DRSolution Protection Mode: MaxPerformance Members: primary - Primary database prmyFS - Far sync instance physical - Physical standby database snapshot - Snapshot standby database logical - Logical standby database logical2 - Logical standby database Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 59 seconds ago) |
