A! Help: Adding a New Physical Standby to Existing Data Guard Setup

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
Item	Primary	Standby	New 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-18

9. 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-MEMORY

10. 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

A! Help

Labels

Wednesday, August 1, 2018

Adding a New Physical Standby to Existing Data Guard Setup

About Me

Downloads

Quick Response

Popular

Blog Archive

Total Pageviews

Followers

Oracle Documentation