DGMGRL> show configuration
Configuration - dg12c2
Protection Mode: MaxAvailability
Members:
prodcdb - Primary database
stbycdb - Physical standby database
stby2cdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 17 seconds ago)The standby database named "stby2cdb" is to be removed from the configuration. This is achieved by the use of "remove database" command in the data guard broker. If "preserve destination" clause is not used then this command remove all reference to the database being removed from other databases that will remain the data guard configuration. The default behaviour is to remove all references to the removed database so simply run the remove command with the database nameDGMGRL> remove database stby2cdb; Removed database "stby2cdb" from the configurationThe data guard configuration reflect the change.
DGMGRL> show configuration
Configuration - dg12c2
Protection Mode: MaxAvailability
Members:
prodcdb - Primary database
stbycdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 26 seconds ago)On the alert log of the primary (prodcdb) it could be seen that log_archive_config and log_archive_destination parameter being modified. The commands to update these parameters are issue by the data guard broker.2018-08-06T12:05:04.410188+01:00 Destination LOG_ARCHIVE_DEST_3 no longer supports SYNCHRONIZATION Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST 2018-08-06T12:05:04.433444+01:00 ALTER SYSTEM SET log_archive_dest_3='' SCOPE=BOTH; Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(prodcdb,stbycdb)'] 2018-08-06T12:05:04.476398+01:00 ALTER SYSTEM SET log_archive_config='dg_config=(prodcdb,stbycdb)' SCOPE=BOTH; 2018-08-06T12:05:06.540027+01:00 ALTER SYSTEM SWITCH ALL LOGFILE start (prodcdb)On the alert log of the remaining standby (stbycdb) it could be seen that log_archive_config parameter is updated to reflect only the databases that remain on the data guard configuration.
2018-08-06T13:38:40.813124+01:00 Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(stbycdb,prodcdb)'] 2018-08-06T13:38:40.886959+01:00 ALTER SYSTEM SET log_archive_config='dg_config=(stbycdb,prodcdb)' SCOPE=BOTH;On the alert log of the database that was removed (stby2cdb), the log_archive_config and fal_server parameter values were updated by removing any references to the database in the DG configuration.
2018-08-06T14:31:27.917074+01:00 ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH; 2018-08-06T14:31:27.927364+01:00 ALTER SYSTEM SET fal_server='' SCOPE=BOTH;Though these DG related parameters were updated and reset all databases, the db/log file name convert related value remain the same Following output is from production DB.
DGMGRL> show database prodcdb LogFileNameConvert LogFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/' DGMGRL> show database prodcdb DbFileNameConvert DbFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/'Update DbFileNameConvert and LogFileNameConvert values on all databases that remain in DG configuration by removing any references to the removed database. The changes require a restart of the database to take effect.
DGMGRL> edit database prodcdb set property 'LogFileNameConvert'='/stbycdb/,/prodcdb/'; Warning: ORA-16675: database instance restart required for property value modification to take effect Property "LogFileNameConvert" updated DGMGRL> edit database prodcdb set property 'DbFileNameConvert'='/stbycdb/,/prodcdb/'; Warning: ORA-16675: database instance restart required for property value modification to take effect Property "DbFileNameConvert" updated DGMGRL> edit database stbycdb set property 'DbFileNameConvert'='/prodcdb/,/stbycdb/'; Warning: ORA-16675: database instance restart required for property value modification to take effect Property "DbFileNameConvert" updated DGMGRL> edit database stbycdb set property 'LogFileNameConvert'='/prodcdb/,/stbycdb/'; Warning: ORA-16675: database instance restart required for property value modification to take effect Property "LogFileNameConvert" updatedIf possible carry out a switchover to test the DG configuration.
DGMGRL> show configuration
Configuration - dg12c2
Protection Mode: MaxAvailability
Members:
prodcdb - Primary database
stbycdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 54 seconds ago)
DGMGRL> switchover to stbycdb
Performing switchover NOW, please wait...
Operation requires a connection to database "stbycdb"
Connecting ...
Connected to "stbycdb"
Connected as SYSDBA.
New primary database "stbycdb" is opening...
Oracle Clusterware is restarting database "prodcdb" ...
Switchover succeeded, new primary is "stbycdb"
DGMGRL> show configuration
Configuration - dg12c2
Protection Mode: MaxAvailability
Members:
stbycdb - Primary database
prodcdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 31 seconds ago)This conclude the removing of a standby database from a DG configuration.The physical standby that was removed from the DG configuration could be converted to an active database open for read/write. If the DG configuration was in maximum availability or maximum protection mode then lower the protection mode of the database to maximum performance. If not opening the database will fail with LGWR complaining the following.
LGWR: Primary database is in MAXIMUM AVAILABILITY mode LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR LGWR: Minimum of 1 LGWR standby database required 2018-08-06T15:01:23.198018+01:00 Errors in file /opt/app/oracle/diag/rdbms/stby2cdb/stby2cdb/trace/stby2cdb_lgwr_28545.trc: ORA-16072: a minimum of one standby database destination is requiredRun the following to lower the protection mode
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; SQL> select protection_mode,protection_level from v$database; PROTECTION_MODE PROTECTION_LEVEL -------------------- -------------------- MAXIMUM PERFORMANCE UNPROTECTEDActivate the physical standby for read/write.
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;Reset db/log file name convert parameters. This requires restart of the database to take affect.
alter system reset db_file_name_convert scope=spfile; alter system reset log_file_name_convert scope=spfile;Set DG broker auto start to false
alter system set dg_broker_start=false scope=both;If archive log deletion policy was set to DG specific policy such as "CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY" then reset it to none
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;The standby was managed by clusterware as part of Oracle restart configuration. The database configuration information would still be listed as physical standby and mount or read only open mode (whichever was set for standby).
srvctl config database -d stby2cdb Database unique name: stby2cdb ... Start options: read only Stop options: immediate Database role: PHYSICAL_STANDBYModify the database to reflect its new role and start up mode
srvctl modify database -d stby2cdb -role primary -startoption open srvctl config database -d stby2cdb Database unique name: stby2cdb ... Start options: open Stop options: immediate Database role: PRIMARYModify any database services that were created to start when the database was in physical standby role.
srvctl config service -d stby2cdb -s abc Service name: abc Cardinality: SINGLETON Service role: PHYSICAL_STANDBYModify to the service configuration so it starts when the database is in primary role (if this is applicable to business needs).
srvctl modify service -d stby2cdb -s abc -l primary srvctl config service -d stby2cdb -s abc Service name: abc Cardinality: SINGLETON Service role: PRIMARYShutdown the database and start it using srvctl to validate the configuration.
srvctl start database -d stby2cdb
