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
