This post shows the steps for adding a physical standby to an existing data guard setup. The
existing data guard setup consists of a single primary and single physical standby with Oracle restart. To this DG setup a new physical standby would be added followed by
DG broker update with the new standby. The summary of hostnames, DB names is shown in the table below.
| Item | Existing Data Guard Setup | New Standby |
|---|
| Primary | Standby |
|---|
| Host name | city7 | city7s | city7s2 |
| Database Name | prodcdb | stbycdb | stby2cdb |
| Diskgroup Names | DATA
FRA | DATA
FRA | DATA
FRA |
| TNS Entry Name | PRODCDBTNS | STBYTNS | STBY2TNS |
The steps here are of the configuration of the data guard portion only. It's assumed relevant software is installed and ASM disk configuration is done on the new standby server.
1. On the new standby create the adump directory.
cd $ORACLE_BASE/admin
mkdir stby2cdb
cd stby2cdb
mkdir adump dpdump hdump pfile
2. Add the static listner entries to the listener.ora file. In this case entries for both standard listener entry and DG broker specific entry (with DGMGRL) is added the same time.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stby2cdb)
(SID_NAME = stby2cdb)
(ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = stby2cdb_DGMGRL)
(SID_NAME = stby2cdb)
(ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
)
)Start the listener on the new standby and verify static services are up
Service "stby2cdb" has 1 instance(s).
Instance "stby2cdb", status UNKNOWN, has 1 handler(s) for this service...
Service "stby2cdb_DGMGRL" has 1 instance(s).
Instance "stby2cdb", status UNKNOWN, has 1 handler(s) for this service...
3. Create TNS entry on all servers (both on existing and new standby) pointing to the standby instance that would be running on the new standby server.
STBY2TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = city7s2)(PORT = 1581))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby2cdb)
)
)4. Copy the Oracle password file to new standby server and rename the file to reflect the new standby instance name.
scp orapwprodcdb city7s2:$ORACLE_HOME/dbs/orapwstby2cdb
5. Create init file on new standby with just the db_name entry and start the standby instance in nomount mode
more initstby2cdb.ora
db_name=stby2cdb
6. On the primary modify the log_archive_config parameter by including the new standby DB. Also add a new log_archive_dest value pointing to the new standby instance. Update the fal_server list to include the TNS entry for the new standby. Update both DB and Log file name convert parameter considering how the file name conversion must happen if and when each of the standby databases become primary.
alter system set log_archive_config='dg_config=(prodcdb,stbycdb,stby2cdb)' scope=both ;
alter system set log_archive_dest_3='service=STBY2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stby2cdb' scope=both;
alter system set fal_server='STBYTNS','STBY2TNS' scope=both;
alter system set db_file_name_convert='/stbycdb/','/prodcdb/','/stby2cdb/','/prodcdb/' scope=spfile;
alter system set log_file_name_convert='/stbycdb/','/prodcdb/','/stby2cdb/','/prodcdb/' scope=spfile;
7. Same set of parameters should be updated on the existing standby as well.
alter system set log_archive_config='dg_config=(prodcdb,stbycdb,stby2cdb)' scope=both ;
alter system set log_archive_dest_3='service=STBY2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stby2cdb' scope=both;
alter system set fal_server='PRODCDBTNS','STBY2TNS' scope=both;
alter system set db_file_name_convert='/prodcdb/','/stbycdb/','/stby2cdb/','/stbycdb/' scope=spfile;
alter system set log_file_name_convert='/prodcdb/','/stbycdb/','/stby2cdb/','/stbycdb/' scope=spfile;
8. Finally run the rman command to create the new standby.
Earlier post showed multiple ways of creating the standby DB. In this instance the standby is created using active database option. Following rman command is run from the current primary.
rman target sys/prodcdbdb auxiliary sys/prodcdbdb@stby2tns
duplicate target database for standby from active database
spfile
parameter_value_convert 'prodcdb','stby2cdb','PRODCDB','STBY2CDB','stbycdb','stby2cdb','STBYCDB','STBY2CDB'
set db_name='prodcdb'
set db_unique_name='stby2cdb'
set db_file_name_convert='/prodcdb/','/stby2cdb/','/stbycdb/','/stby2cdb/'
set log_file_name_convert='/prodcdb/','/stby2cdb/','/stbycdb/','/stby2cdb/'
set log_archive_max_processes='10'
set fal_server='PRODCDBTNS','STBYTNS'
reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_3='service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby2cdb';
Full output of the duplication is shown below
rman target sys/prodcdbdb auxiliary sys/prodcdbdb@stby2tns
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jul 16 14:41:23 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODCDB (DBID=2963914998)
connected to auxiliary database: STBY2CDB (not mounted)
RMAN> duplicate target database for standby from active database
2> spfile
3> parameter_value_convert 'prodcdb','stby2cdb','PRODCDB','STBY2CDB','stbycdb','stby2cdb','STBYCDB','STBY2CDB'
4> set db_name='prodcdb'
5> set db_unique_name='stby2cdb'
set db_file_name_convert='/prodcdb/','/stby2cdb/','/stbycdb/','/stby2cdb/'
6> 7> set log_file_name_convert='/prodcdb/','/stby2cdb/','/stbycdb/','/stby2cdb/'
8> set log_archive_max_processes='10'
9> set fal_server='PRODCDBTNS','STBYTNS'
10> reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
11> 12> set log_archive_dest_3='service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb'
13> set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby2cdb';
Starting Duplicate Db at 16-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/orapwstby2cdb' targetfile
'+DATA/prodcdb/spfileprodcdb.ora' auxiliary format
'/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora' ;
sql clone "alter system set spfile= ''/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora''";
}
executing Memory Script
Starting backup at 16-JUL-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
Finished backup at 16-JUL-18
sql statement: alter system set spfile= ''/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/opt/app/oracle/admin/stby2cdb/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DATA/STBY2CDB/CONTROLFILE/current.266.965841019'', ''+FRA/STBY2CDB/CONTROLFILE/current.256.965841019'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=stby2cdbXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_name =
''prodcdb'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stby2cdb'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/prodcdb/'', ''/stby2cdb/'', ''/stbycdb/'', ''/stby2cdb/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/prodcdb/'', ''/stby2cdb/'', ''/stbycdb/'', ''/stby2cdb/'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
10 comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''PRODCDBTNS'', ''STBYTNS'' comment=
'''' scope=spfile";
sql clone "alter system reset local_listener scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_3 =
''service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby2cdb'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/opt/app/oracle/admin/stby2cdb/adump'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DATA/STBY2CDB/CONTROLFILE/current.266.965841019'', ''+FRA/STBY2CDB/CONTROLFILE/current.256.965841019'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=stby2cdbXDB)'' comment= '''' scope=spfile
sql statement: alter system set db_name = ''prodcdb'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''stby2cdb'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/prodcdb/'', ''/stby2cdb/'', ''/stbycdb/'', ''/stby2cdb/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/prodcdb/'', ''/stby2cdb/'', ''/stbycdb/'', ''/stby2cdb/'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 10 comment= '''' scope=spfile
sql statement: alter system set fal_server = ''PRODCDBTNS'', ''STBYTNS'' comment= '''' scope=spfile
sql statement: alter system reset local_listener scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_3 = ''service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby2cdb'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1191182336 bytes
Fixed Size 8792104 bytes
Variable Size 452986840 bytes
Database Buffers 721420288 bytes
Redo Buffers 7983104 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/STBY2CDB/CONTROLFILE/current.264.981652083'', ''+FRA/STBY2CDB/CONTROLFILE/current.318.981652083'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/STBY2CDB/CONTROLFILE/current.290.981652083';
restore clone primary controlfile to '+FRA/STBY2CDB/CONTROLFILE/current.276.981652083' from
'+DATA/STBY2CDB/CONTROLFILE/current.290.981652083';
sql clone "alter system set control_files =
''+DATA/STBY2CDB/CONTROLFILE/current.290.981652083'', ''+FRA/STBY2CDB/CONTROLFILE/current.276.981652083'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/STBY2CDB/CONTROLFILE/current.264.981652083'', ''+FRA/STBY2CDB/CONTROLFILE/current.318.981652083'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 16-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_prodcdb.f tag=TAG20180716T144207
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-JUL-18
Starting restore at 16-JUL-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=137 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 16-JUL-18
sql statement: alter system set control_files = ''+DATA/STBY2CDB/CONTROLFILE/current.290.981652083'', ''+FRA/STBY2CDB/CONTROLFILE/current.276.981652083'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1191182336 bytes
Fixed Size 8792104 bytes
Variable Size 452986840 bytes
Database Buffers 721420288 bytes
Redo Buffers 7983104 bytes
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA";
set newname for tempfile 2 to
"+DATA";
set newname for tempfile 3 to
"+DATA";
switch clone tempfile all;
set newname for datafile 1 to
"+DATA";
set newname for datafile 2 to
"+DATA";
set newname for datafile 3 to
"+DATA";
set newname for datafile 4 to
"+DATA";
set newname for datafile 5 to
"+DATA";
set newname for datafile 6 to
"+DATA";
set newname for datafile 7 to
"+DATA";
set newname for datafile 33 to
"+DATA";
set newname for datafile 34 to
"+DATA";
set newname for datafile 35 to
"+DATA";
set newname for datafile 121 to
"+DATA";
backup as copy reuse
datafile 1 auxiliary format
"+DATA" datafile
2 auxiliary format
"+DATA" datafile
3 auxiliary format
"+DATA" datafile
4 auxiliary format
"+DATA" datafile
5 auxiliary format
"+DATA" datafile
6 auxiliary format
"+DATA" datafile
7 auxiliary format
"+DATA" datafile
33 auxiliary format
"+DATA" datafile
34 auxiliary format
"+DATA" datafile
35 auxiliary format
"+DATA" datafile
121 auxiliary format
"+DATA" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 16-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/PRODCDB/DATAFILE/sysaux.265.965841035
output file name=+DATA/STBY2CDB/DATAFILE/sysaux.296.981652125 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/PRODCDB/DATAFILE/system.259.965841027
output file name=+DATA/STBY2CDB/DATAFILE/system.298.981652161 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/PRODCDB/DATAFILE/undotbs1.262.965841045
output file name=+DATA/STBY2CDB/DATAFILE/undotbs1.289.981652175 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/PRODCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/sysaux.263.965841045
output file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/sysaux.285.981652183 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00034 name=+DATA/PRODCDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/sysaux.277.979213603
output file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/sysaux.306.981652199 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/PRODCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.258.965841031
output file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.275.981652213 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/PRODCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/undotbs1.261.965841047
output file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/undotbs1.282.981652221 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00033 name=+DATA/PRODCDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/system.276.979213603
output file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/system.268.981652227 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00035 name=+DATA/PRODCDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/undotbs1.275.979213603
output file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/undotbs1.262.981652235 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00121 name=+DATA/PRODCDB/DATAFILE/test.303.969113983
output file name=+DATA/STBY2CDB/DATAFILE/test.283.981652243 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/PRODCDB/DATAFILE/users.269.965841065
output file name=+DATA/STBY2CDB/DATAFILE/users.311.981652243 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-JUL-18
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=981652244 file name=+DATA/STBY2CDB/DATAFILE/system.298.981652161
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=981652244 file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.275.981652213
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=981652245 file name=+DATA/STBY2CDB/DATAFILE/sysaux.296.981652125
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=981652245 file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/sysaux.285.981652183
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=981652245 file name=+DATA/STBY2CDB/DATAFILE/undotbs1.289.981652175
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=981652245 file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/undotbs1.282.981652221
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=981652245 file name=+DATA/STBY2CDB/DATAFILE/users.311.981652243
datafile 33 switched to datafile copy
input datafile copy RECID=8 STAMP=981652245 file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/system.268.981652227
datafile 34 switched to datafile copy
input datafile copy RECID=9 STAMP=981652245 file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/sysaux.306.981652199
datafile 35 switched to datafile copy
input datafile copy RECID=10 STAMP=981652245 file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/undotbs1.262.981652235
datafile 121 switched to datafile copy
input datafile copy RECID=11 STAMP=981652245 file name=+DATA/STBY2CDB/DATAFILE/test.283.981652243
Finished Duplicate Db at 16-JUL-189. Start the managed recovery on the new standby and verify applying of the archived logs
alter database recover managed standby database using current logfile disconnect;
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
497 YES
498 YES
499 IN-MEMORY10. Next step is to add the new standby to the Oracle restart. Update the oracle binary permission on the Oracle home of the new standby to
avoid the ORA-27303 issue. Once done add the new standby to Oracle restart configuration. Run the following as Oracle user
srvctl add database -db stby2cdb -oraclehome $ORACLE_HOME -spfile "/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora" -role physical_standby -startoption mount -diskgroup "DATA,FRA"
srvctl config database -db stby2cdb
Database unique name: stby2cdb
Database name:
Oracle home: /opt/app/oracle/product/12.2.0/dbhome_1
Oracle user: oracle
Spfile: /opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
OSDBA group:
OSOPER group:
Database instance: stby2cdb
srvctl stop database -db stby2cdb
srvctl start database -db stby2cdb
11. Final step is to add the new standby to the existing data guard broker configuration. On the new standby cancel the managed recovery and set dg_broker_start to true. It is assumed that dg_broker_config_file* parameter will be set to default value.
alter database recover managed standby database cancel;
alter system set dg_broker_start=true scope=both sid='*';
12. To avoid the ORA-16575, clear the log_archive_dest values added during the earlier steps. On all databases, including the new standby, run the following to clear the new log_archive_dest value
alter system reset log_archive_dest_3 scope=both;
Additionally on the new standby run the following to clear the second log archive dest as well.
alter system reset log_archive_dest_2 scope=both;
13. Once the log archive dest are cleared add the new standby to the existing data guard configuration and enable it.
DGMGRL> add database stby2cdb as connect identifier is stby2tns;
Database "stby2cdb" added
DGMGRL> show configuration
Configuration - dg12c2
Protection Mode: MaxAvailability
Members:
prodcdb - Primary database
Warning: ORA-16792: configurable property value is inconsistent with member setting
stbycdb - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with member setting
stby2cdb - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 39 seconds ago)
DGMGRL> enable database stby2cdb;
Enabled.
DGMGRL> show configuration
Configuration - dg12c2
Protection Mode: MaxAvailability
Members:
prodcdb - Primary database
Warning: ORA-16792: configurable property value is inconsistent with member setting
stbycdb - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with member setting
stby2cdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 36 seconds ago)14. The warning on the existing databases is down to mismatch of db/log file name convert values. The Oracle doc states the following (only db_file_name convert text is quoted here) "
when a database is added to the configuration, the broker sets the initial value of this property to the in-memory value of the DB_FILE_NAME_CONVERT initialization parameter. It is possible that the in-memory value and server parameter file (SPFILE) value of this parameter will differ. If you want to use the parameter's in-memory value, then enable the database and the broker will ensure that the SPFILE value of the parameter is set to the in-memory value. If you want to use the SPFILE value, then set the property value to be the parameter's value stored in the SPFILE. Then enable the database". Listing inconsistent properties for each database list these values
DGMGRL> show database prodcdb inconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
prodcdb DbFileNameConvert /stbycdb/, /prodcdb/ /stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/ /stbycdb/, /prodcdb/
prodcdb LogFileNameConvert /stbycdb/, /prodcdb/ /stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/ /stbycdb/, /prodcdb/
DGMGRL> show database stbycdb inconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
stbycdb DbFileNameConvert /prodcdb/, /stbycdb/ /prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/ /prodcdb/, /stbycdb/
stbycdb LogFileNameConvert /prodcdb/, /stbycdb/ /prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/ /prodcdb/, /stbycdb/15. To fix this manually update each property for each database that report inconsistency. Following is executed on current production
DGMGRL> edit database prodcdb set property 'DbFileNameConvert'='/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect
Property "DbFileNameConvert" updated
show database prodcdb DbFileNameConvert
DbFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/'
edit database prodcdb set property 'LogFileNameConvert'='/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect
Property "LogFileNameConvert" updated
show database prodcdb LogFileNameConvert
LogFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/'
Following is executed on the existing standby
DGMGRL> edit database stbycdb set property 'DbFileNameConvert'='/prodcdb/,/stbycdb/,/stby2cdb/,/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/,/stby2cdb/,/stbycdb/' ;
Warning: ORA-16675: database instance restart required for property value modification to take effect
Property "LogFileNameConvert" updated
DGMGRL> show database stbycdb LogFileNameConvert
LogFileNameConvert = '/prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/'
DGMGRL> show database stbycdb DbFileNameConvert
DbFileNameConvert = '/prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/'
16. As highlighted above, for this change to take effect restart of the databases, both primary and exiting standby is required incurring down time. Configuration status after restart of the existing standby (stbycdb)
DGMGRL> show configuration
Configuration - dg12c2
Protection Mode: MaxAvailability
Members:
prodcdb - Primary database
Warning: ORA-16809: multiple warnings detected for the member
stbycdb - Physical standby database (disabled)
stby2cdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 16 seconds ago)
DGMGRL> enable database stbycdb
Enabled.
DGMGRL> show configuration
Configuration - dg12c2
Protection Mode: MaxAvailability
Members:
prodcdb - Primary database
Warning: ORA-16792: configurable property value is inconsistent with member setting
stbycdb - Physical standby database
stby2cdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 1 second ago)17.After the restart of primary
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 27 seconds ago)18. The existing DG configuration has the protection mode set to maximum availability. However the new standby was added with log transport mode of ASYNC. Change this to SYNC to keep the same protection level even if one standby goes down.
DGMGRL> show database stby2cdb LogXptMode
LogXptMode = 'ASYNC'
DGMGRL> edit database stby2cdb set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> show database stby2cdb LogXptMode
LogXptMode = 'SYNC'
DGMGRL> show database stbycdb LogXptMode
LogXptMode = 'SYNC'
19. If possible switchover to the newly added standby and verify DG configuration is working as expected.
DGMGRL> switchover to stby2cdb
Performing switchover NOW, please wait...
New primary database "stby2cdb" is opening...
Oracle Clusterware is restarting database "prodcdb" ...
Switchover succeeded, new primary is "stby2cdb"
DGMGRL> show configuration
Configuration - dg12c2
Protection Mode: MaxAvailability
Members:
stby2cdb - Primary database
prodcdb - Physical standby database
stbycdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 58 seconds ago)20. Rotate the primary among all databases to verify file name conversion and log transport is working as expected.
DGMGRL> switchover to prodcdb
Performing switchover NOW, please wait...
Operation requires a connection to database "prodcdb"
Connecting ...
Connected to "prodcdb"
Connected as SYSDBA.
New primary database "prodcdb" is opening...
Oracle Clusterware is restarting database "stby2cdb" ...
Switchover succeeded, new primary is "prodcdb"
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 61 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
stby2cdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 65 seconds ago)This concludes the adding of new physical standby to an existing data guard configuration.
Related Posts
Creating Data Guard Broker for an Existing 12.2 Data Guard Setup with CDB
Oracle Data Guard on 12.2 CDB with Oracle Restart
Creating Data Guard Broker on 12c
Adding Far Sync Instances to Existing Data Guard Configuration
11gR2 RAC to RAC Data Guard
Data Guard Broker for 11gR2 RAC
11gR2 Standalone Data Guard (with ASM and Role Separation)
Setting Up Data Guard Broker for an Existing Data Guard Configuration with Far Sync
Enable Active Dataguard on 11gR2 RAC Standby
