Creating a Physical Standby using RMAN/Data Guard Broker on 11g

Posted at Saturday, October 20, 2007
In this example, I am creating a Physical Standby database - on the same host - using RMAN to create the standby and the Data Guard Broker to manage the Standby's configuration.

The main impetus for doing this is to evaluate some enhancements that
are part of 11g. In particular, I want to test out the new Data Guard functionality that allows for simultaneous application of changes to the Physical Standby and processing of clients' read-only queries. As you are probably aware, these activities used to be mutually exclusive. In the past, issuing queries to the Standby - ostensibly to offload production load - required stopping the application of redo and opening the database in a read-only mode.

I start by creating the Standby using RMAN. In this case, I will be creating the Standby from an RMAN backup, instead of from the live database.

Preparing the primary database:

Ensure that the database in question is in force logging mode. If not, enable force logging as in below.

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

Preparing the auxiliary instance:

Add an entry to /etc/oratab

$ grep stdby /etc/oratab
stdby:/u03/app/oracle/product/db/11.1.0.6:N

Create a passwordfile under $ORACLE_HOME/dbs. Remember that the value of the password needs to be the same as that of the primary database's password file. I have found that the ignorecase parameter is essential in 11g when putting one of these configurations together.

$ orapwd file=orapwstdby password=password ignorecase=y entries=25

Add a static listener entry to $TNS_ADMIN/listener.ora (and reload) as well as a net service entry to $TNS_ADMIN/tnsnames.ora

listener entry

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = stdby)
)
)

tnsnames entry

STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby.colestock.test)
)
)

Create a barebones init.ora file for the auxiliary instance

initstdby.ora

db_name=stdby
control_files=(/u03/app/oracle/oradata/stdby/control01.ctl,/u03/app/oracle/orad
ata/stdby/control02.ctl)
log_file_name_convert=('/nf/','/stdby/')

Issue the commands to create the directories needed for the new database:

$ mkdir /u03/app/oracle/oradata/stdby
$ mkdir -p /u03/app/oracle/oradata/stdby/arch
$ mkdir -p /u03/app/oracle/admin/stdby/adump
$ mkdir -p /u03/app/oracle/admin/stdby/bdump
$ mkdir -p /u03/app/oracle/admin/stdby/cdump
$ mkdir -p /u03/app/oracle/admin/stdby/dpdump
$ mkdir -p /u03/app/oracle/admin/stdby/pfile
$ mkdir -p /u03/app/oracle/admin/stdby/scripts
$ mkdir -p /u03/app/oracle/admin/stdby/udump

Startup the instance in no mount. I perform the optional step of opening up the wallet, just to ensure that the wallet location is set appropriately in sqlnet.ora

$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 20 14:13:10 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 150667264 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 6758400 bytes
SQL> alter system set wallet open identified by "password";

System altered.

Create the necessary RMAN duplicate script

$ more create_standby.rman
DUPLICATE TARGET DATABASE
FOR STANDBY
DORECOVER
DB_FILE_NAME_CONVERT=('/nf/','/stdby/')
SPFILE
PARAMETER_VALUE_CONVERT '/nf','/stdby'
SET SERVICE_NAMES 'stdby.colestock.test'
SET "db_unique_name"="stdby"
SET log_file_name_convert '/nf/','/stdby/';

Create a wrapper script that calls this script and creates all necessary instance connections

$ more create_standby.bsh
#!/bin/bash

export ORACLE_SID=stdby
. oraenv

rman target=sys/password@nf catalog=rman/password@nf auxiliary=/ @create_standby.rman;

Run the aforementioned script

./create_standby.bsh
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Oct 20 14:32:20 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: NF (DBID=2018903642)
connected to recovery catalog database
connected to auxiliary database: STDBY (not mounted)

RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> DORECOVER
4> DB_FILE_NAME_CONVERT=('/nf/','/stdby/')
5> SPFILE
6> PARAMETER_VALUE_CONVERT '/nf','/stdby'
7> SET SERVICE_NAMES 'stdby.colestock.test'
8> SET "db_unique_name"="stdby"
9> SET log_file_name_convert '/nf/','/stdby/';
10>
Starting Duplicate Db at 20-OCT-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK

contents of Memory Script:
{
set until scn 1335853;
restore clone spfile to '/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora';
sql clone "alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora''";
}
executing Memory Script

executing command: SET until clause

Starting restore at 20-OCT-07
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora
channel ORA_AUX_DISK_1: reading from backup piece /u03/nf_07iu4roo_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/u03/nf_07iu4roo_1_1.rman tag=TAG20071010T103433
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-OCT-07

sql statement: alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora''

contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u03/app/oracle/admin/stdby/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u03/app/oracle/oradata/stdby/control01.ctl'', ''/u03/app/oracle/oradata/stdby/control02.ctl'', ''/u03/app/oracle/oradata/stdby/control03.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u03/app/oracle/oradata/stdby/arch'' comment=
'''' scope=spfile";
sql clone "alter system set SERVICE_NAMES =
''stdby.colestock.test'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stdby'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/nf/'', ''/stdby/'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u03/app/oracle/admin/stdby/adump'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u03/app/oracle/oradata/stdby/control01.ctl'', ''/u03/app/oracle/oradata/stdby/control02.ctl'', ''/u03/app/oracle/oradata/stdby/control03.ctl'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u03/app/oracle/oradata/stdby/arch'' comment= '''' scope=spfile

sql statement: alter system set SERVICE_NAMES = ''stdby.colestock.test'' comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''stdby'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/nf/'', ''/stdby/'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 196681728 bytes

Fixed Size 1298836 bytes
Variable Size 75501164 bytes
Database Buffers 113246208 bytes
Redo Buffers 6635520 bytes

contents of Memory Script:
{
set until scn 1335853;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 20-OCT-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u03/nf_07iu4roo_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/u03/nf_07iu4roo_1_1.rman tag=TAG20071010T103433
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u03/app/oracle/oradata/stdby/control01.ctl
output file name=/u03/app/oracle/oradata/stdby/control02.ctl
output file name=/u03/app/oracle/oradata/stdby/control03.ctl
Finished restore at 20-OCT-07

sql statement: alter database mount standby database

contents of Memory Script:
{
set until scn 1335853;
set newname for tempfile 1 to
"/u03/app/oracle/oradata/stdby/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u03/app/oracle/oradata/stdby/system01.dbf";
set newname for datafile 2 to
"/u03/app/oracle/oradata/stdby/sysaux01.dbf";
set newname for datafile 3 to
"/u03/app/oracle/oradata/stdby/undotbs01.dbf";
set newname for datafile 4 to
"/u03/app/oracle/oradata/stdby/users01.dbf";
set newname for datafile 5 to
"/u03/app/oracle/oradata/stdby/flashback_data01.dbf";
set newname for datafile 6 to
"/u03/app/oracle/oradata/stdby/ts201.dbf";
set newname for datafile 7 to
"/u03/app/oracle/oradata/stdby/ts301.dbf";
set newname for datafile 8 to
"/u03/app/oracle/oradata/stdby/ts401.dbf";
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /u03/app/oracle/oradata/stdby/temp01.dbf 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

Starting restore at 20-OCT-07
using channel ORA_AUX_DISK_1

the file name for datafile 7 is missing in the control file
the file name for datafile 8 is missing in the control file
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u03/app/oracle/oradata/stdby/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/stdby/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/stdby/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/stdby/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/stdby/flashback_data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u03/app/oracle/oradata/stdby/ts201.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/nf_06iu4rlp_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/u03/nf_06iu4rlp_1_1.rman tag=TAG20071010T103433
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 20-OCT-07

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/flashback_data01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/ts201.dbf

contents of Memory Script:
{
set until scn 1335853;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 20-OCT-07
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 20 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_20_635081437.dbf
archived log for thread 1 with sequence 21 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_21_635081437.dbf
archived log for thread 1 with sequence 22 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf
archived log for thread 1 with sequence 23 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_23_635081437.dbf
archived log for thread 1 with sequence 24 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_24_635081437.dbf
archived log for thread 1 with sequence 25 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_25_635081437.dbf
archived log for thread 1 with sequence 26 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_26_635081437.dbf
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_20_635081437.dbf thread=1 sequence=20
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_21_635081437.dbf thread=1 sequence=21
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf thread=1 sequence=22
creating datafile file number=7 name=/u03/app/oracle/oradata/stdby/ts301.dbf
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf thread=1 sequence=22
creating datafile file number=8 name=/u03/app/oracle/oradata/stdby/ts401.dbf
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf thread=1 sequence=22
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_23_635081437.dbf thread=1 sequence=23
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_24_635081437.dbf thread=1 sequence=24
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_25_635081437.dbf thread=1 sequence=25
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_26_635081437.dbf thread=1 sequence=26
media recovery complete, elapsed time: 00:00:30
Finished recover at 20-OCT-07
Finished Duplicate Db at 20-OCT-07

Recovery Manager complete.

Afterwards, you will find that the database has been created and that all of the files are renamed and in the locations intended

$ ls -lart /u03/app/oracle/oradata/stdby
total 2515888
drwxr-xr-x 5 oracle dba 4096 Oct 20 12:55 ..
drwxr-xr-x 2 oracle dba 4096 Oct 20 13:18 arch
drwxr-xr-x 3 oracle dba 4096 Oct 20 14:35 .
-rw-r----- 1 oracle dba 11542528 Oct 20 14:35 users01.dbf
-rw-r----- 1 oracle dba 110108672 Oct 20 14:35 undotbs01.dbf
-rw-r----- 1 oracle dba 104865792 Oct 20 14:35 ts401.dbf
-rw-r----- 1 oracle dba 104865792 Oct 20 14:35 ts301.dbf
-rw-r----- 1 oracle dba 524296192 Oct 20 14:35 ts201.dbf
-rw-r----- 1 oracle dba 744497152 Oct 20 14:35 system01.dbf
-rw-r----- 1 oracle dba 682106880 Oct 20 14:35 sysaux01.dbf
-rw-r----- 1 oracle dba 262152192 Oct 20 14:35 flashback_data01.dbf
-rw-r----- 1 oracle dba 9748480 Oct 20 16:41 control03.ctl
-rw-r----- 1 oracle dba 9748480 Oct 20 16:41 control02.ctl
-rw-r----- 1 oracle dba 9748480 Oct 20 16:41 control01.ctl

An examination of the v$database view should yield 'PHYSICAL STANDBY'

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

At this point, the basic Duplication routine is complete, now a lot of additional configurations need to be performed in order to have a complete Standby Database configuration

Add explicit entries to the appropriate listener (and reload) for the services that will be part of the Data Guard configuration. These are used because the Data Guard broker needs to be able to stop and start the instances involved regardless of their state.

I appended the static service entries to my listener's sid list:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = stdby)
)
(SID_DESC =
(GLOBAL_DBNAME = stdby_DGMGRL.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = stdby)
)
(SID_DESC =
(GLOBAL_DBNAME = nf_DGMGRL.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = nf)
)
)

Now, I alter the appropriate init.ora parameters for both the primary and standby instances

alter_primary.sql

$ more alter_primary.sql
alter system set dg_broker_start=true scope=both;
alter system set dg_broker_config_file1='/u03/app/oracle/product/db/11.1.0.6/db
s/nf_dg1.conf' scope=spfile;
alter system set dg_broker_config_file2='/u03/app/oracle/product/db/11.1.0.6/db
s/nf_dg2.conf' scope=spfile;
alter system set log_archive_format='nf_%t_%s_%r.arc' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(nf,stdby)' scope=BOTH;
alter system set log_archive_dest_2='SERVICE=stdby VALID_FOR=(ONLINE_LOGF
ILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby' scope=both;
alter system set log_archive_dest_state_2=enable scope=both;
alter system set log_archive_max_processes=4 scope=both;
alter system set fal_server=stdby scope=spfile;
alter system set fal_client=nf scope=spfile;
alter system set db_file_name_convert='/stdby/','/nf/' scope=spfile;
alter system set log_file_name_convert='/stdby/','/nf/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
shutdown immediate;
startup;

alter_standby.sql

$ more alter_standby.sql
alter system set dg_broker_start=true scope=both;
alter system set dg_broker_config_file1='/u03/app/oracle/product/db/11.1.0.6/db
s/stdby_dg1.conf' scope=spfile;
alter system set dg_broker_config_file2='/u03/app/oracle/product/db/11.1.0.6/db
s/stdby_dg2.conf' scope=spfile;
alter system set log_archive_format='stdby_%t_%s_%r.arc' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(nf,stdby)' scope=BOTH;
alter system set log_archive_dest_2='SERVICE=nf VALID_FOR=(ONLINE_LOGFILE
S,PRIMARY_ROLE) DB_UNIQUE_NAME=nf' scope=both;
alter system set log_archive_dest_state_2=defer scope=both;
alter system set log_archive_max_processes=4 scope=both;
alter system set fal_server=nf scope=spfile;
alter system set fal_client=stdby scope=spfile;
alter system set db_file_name_convert='/nf/','/stdby/' scope=spfile;
alter system set log_file_name_convert='/nf/','/stdby/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
shutdown immediate;
startup mount;
alter database recover managed standby database disconnect from session;

Run both scripts after settting the appropriate environment variables

$ export ORACLE_SID=nf
$ . oraenv
$ sqlplus "/ as sysdba" @alter_primary.sql

$ export ORACLE_SID=stdby
$ . oraenv
$ sqlplus "/ as sysdba" @alter_standby.sql

At this point, changes should be propagating to the standby. Verify and troubleshoot as necessary, using the method you prefer.

For example, here is how I verify that my standby is up to date with the primary

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 38 0 0
RFS IDLE 1 38 5406 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0

$ export ORACLE_SID=nf
$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 20 19:04:00 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u03/app/oracle/oradata/nf/arch
Oldest online log sequence 36
Next log sequence to archive 38
Current log sequence 38

As you can see, the standby database is waiting for log sequence 38 from the primary, which is the primary's current log.

Managing switchover/failover operations is simplified by using the DGMGRL command-line interface. Now that I have everything up and working, I will configure this tool to manage my Data Guard configuration.

$ dgmgrl
DGMGRL> connect sys/password@nf
Connected.
DGMGRL> CREATE CONFIGURATION 'MY_DG' AS PRIMARY DATABASE IS 'nf' CONNECT IDENTIFIER IS 'NF';
Configuration "MY_DG" created with primary database "nf"
DGMGRL> ADD DATABASE 'stdby' AS CONNECT IDENTIFIER IS 'stdby';
Database "stdby" added
DGMGRL> show configuration

Configuration
Name: MY_DG
Enabled: NO
Protection Mode: MaxPerformance
Databases:
nf - Primary database
stdby - Physical standby database

Fast-Start Failover: DISABLED

Current status for "MY_DG":
DISABLED

DGMGRL> show database verbose 'nf'

Database
Name: nf
Role: PRIMARY
Enabled: NO
Intended State: OFFLINE
Instance(s):
nf

Properties:
DGConnectIdentifier = 'NF'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac2.colestock.test'
SidName = 'nf'
StandbyArchiveLocation = '/u03/app/oracle/oradata/nf/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'nf_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "nf":
DISABLED

DGMGRL> show database verbose 'stdby';

Database
Name: stdby
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
stdby

Properties:
DGConnectIdentifier = 'stdby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/nf/, /stdby/'
LogFileNameConvert = '/nf/, /stdby/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac2.colestock.test'
SidName = 'stdby'
StandbyArchiveLocation = '/u03/app/oracle/oradata/stdby/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'stdby_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "stdby":
DISABLED

The configuration then needs to be enabled

DGMGRL> enable configuration;
Enabled.
DGMGRL> show database 'nf'

Database
Name: nf
Role: PRIMARY
Enabled: YES
Intended State: TRANSPORT-ON
Instance(s):
nf

Current status for "nf":
Warning: ORA-16789: standby redo logs not configured

As you can see, the 'Maximum Performance' protection mode now absolutely requires Standby redo logs.

Create the standby redo logs. Remember one more group (in quantity) then the online redo logs. Notice that I only have one log member per group - this is a test instance (I would never do this in a production environment)

SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database add standby logfile group 5 '/u03/app/oracle/oradata/stdby/redo05.log' size 50M;

Database altered.
SQL> alter database add standby logfile group 6 '/u03/app/oracle/oradata/stdby/redo06.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 7 '/u03/app/oracle/oradata/stdby/redo07.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 8 '/u03/app/oracle/oradata/stdby/redo08.log' size 50M;

Database altered.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

I also create these on the Primary via

alter database add standby logfile group 5 '/u03/app/oracle/oradata/nf/redo05.log' size 50M;
alter database add standby logfile group 6 '/u03/app/oracle/oradata/nf/redo06.log' size 50M;
alter database add standby logfile group 7 '/u03/app/oracle/oradata/nf/redo07.log' size 50M;
alter database add standby logfile group 8 '/u03/app/oracle/oradata/nf/redo08.log' size 50M;

After adding the Standby Redo logs, you will receive a ORA-16826 from the Data Guard Broker.

Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Remedy via

DGMGRL> remove database 'stdby'
Removed database "stdby" from the configuration
DGMGRL> ADD DATABASE 'stdby' AS CONNECT IDENTIFIER IS 'stdby';
Database "stdby" added
DGMGRL> show database verbose 'stdby';

Database
Name: stdby
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
stdby

Properties:
DGConnectIdentifier = 'stdby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/nf/, /stdby/'
LogFileNameConvert = '/nf/, /stdby/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac2.colestock.test'
SidName = 'stdby'
StandbyArchiveLocation = '/u03/app/oracle/oradata/stdby/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'stdby_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "stdby":
DISABLED

DGMGRL> enable database 'stdby'
Enabled.
DGMGRL> show configuration

Configuration
Name: MY_DG
Enabled: YES
Protection Mode: MaxPerformance
Databases:
nf - Primary database
stdby - Physical standby database

Fast-Start Failover: DISABLED

Current status for "MY_DG":
SUCCESS

DGMGRL> show database 'nf' StatusReport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> show database 'stdby' StatusReport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT

Now we have a fully functioning configuration for our 11g new features testing

Labels: , ,