Step by Step Oracle 11gR2 Physical Standby Database Setup

This article steps through the process of creating a physical standby database using RMAN from a live database.

Preparing the primary database for standby environment

Enable Force logging

It is required to place the primary database in FORCE LOGGING mode after database creation. This makes the Oracle Database to force writing of redo records even when NOLOGGING has been specified in DDL.

SQL> alter database force logging;

Primary database archivelog mode

Whole concept of Standby depends on redo and archive logging. Standby site may need archived log if for some reason online could not be transported to Standby site in timely manner.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Current log sequence 16

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2230080 bytes
Variable Size 432015552 bytes
Database Buffers 293601280 bytes
Redo Buffers 2867200 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL>

Enable Primary to receive redo

This is required in the role transition scenario. The synchronous and asynchronous redo transport modes require that a redo transport destination have a standby redo log.

alter dataabse add standby logfile ‘/u01/oradata/PRIMDB/stdby_redo01.log’ SIZE 51200K;
alter dataabse add standby logfile ‘/u01/oradata/PRIMDB/stdby_redo02.log’ SIZE 51200K;
alter dataabse add standby logfile ‘/u01/oradata/PRIMDB/stdby_redo03.log’ SIZE 51200K;

If the redo source database is an Oracle RAC, then standby logs needs to be created for each of the redo thread of the primary database.
Note: Creating standby on the primary database before doing the rman duplicate make sure that stand by log are getting created at the stand by site as well.

Primary database initialization parameters

On the primary database, define initialization parameters that control redo transport services while the database is in the primary role. There are additional parameters need to add that control the receipt of the redo data and apply services when the primary database is transitioned to the standby role.

Primary Standby
PRIMDB STDYDB

alter system set db_name=PRIMDB scope=spfile;
alter system set db_unique_name=PRIMDB scope=spfile;
alter system set log_archive_config=’dg_config=(primdb,stdydb)’ scope=spfile;
alter system set log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=primdb’ scope=spfile;
alter system set log_archive_dest_2=’service=stdydb async valid_for=(online_logfiles,primary_role) db_unique_name=stdydb’ scope=spfile;
alter system set log_archive_dest_state_1=enable scope=spfile;
alter system set log_archive_dest_state_2=enable scope=spfile;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter system set log_archive_format=’primdb_%t_%s_%r.arc’ scope=spfile;
alter system set fal_server=primdb scope=spfile;
alter system set db_file_name_convert=’stdydb’,'primdb’ scope=spfile;
alter system set log_file_name_convert=’/stdydb/stdydb’,'/primdb/primdb/’ scope=spfile;
alter system set standby_file_management=auto scope=spfile;

Note: log_archive_format does not have any impact if use_db_recovery_file_dest is used for archive log destination. The generated filenames for the archived redo logs in the flash recovery area are Oracle Manged Filenames and are not determined by LOG_ARCHIVE_FORMAT.

Set the above parameters and restart the database.

Set up network and tns entries

Create listener on both primary and standby then configure the TNS entries in the tnsnames.ora file.

PRIMDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.62.59.140)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = PRIMDB)
)
)
STDYDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.62.59.139)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = STDYDB)
)
)

Build the standby database using RMAN.

Create an init ora file and password file on the oracle home of the standby database. Startup the auxilary datasbe in nomount mode.

Go to $ORACLE_HOME
Echo “DB_NAME=STDYDB” > initSTDYDB.ora
$ orapwd file=orapwSTDYDB password=

Save the following file as cr_stdby.rc
connect target sys/way2g0@PRIMDB
connect auxiliary sys/way2g0@STDYDB
run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel a1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert ‘PRIMDB’,'STDYDB’
set db_unique_name=’STDYDB’

set control_files=’/u01/oradata/STDYDB/control01.ctl’,
‘/opt/app/flash_recovery_area/STDYDB/control02.ctl’
set db_file_name_convert=’/PRIMDB/’,'/STDYDB/’
set log_file_name_convert=’/PRIMDB/’,'/STDYDB/’
set log_archive_max_processes=’5′
set fal_client=’STDYDB’
set fal_server=’PRIMDB’
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(PRIMDB,STDYDB)’
set db_recovery_file_dest=’/opt/app/flash_recovery_area’
set db_recovery_file_dest_size=’5G’
set log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stdydb’ scope=spfile;
}

Than start rman

$ rman

Recovery Manager: Release 11.2.0.2.0 – Production on Thu Aug 11 09:41:43 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> @rc_standby.rman

RMAN> connect target *
connected to target database: PRIMDB (DBID=433286985)

RMAN> connect auxiliary *
connected to auxiliary database: STDYDB (not mounted)

RMAN> run
2> {
3> allocate channel p1 type disk;
4> allocate channel p2 type disk;
5> allocate auxiliary channel a1 type disk;
6> duplicate target database for standby from active database
7> spfile
8> parameter_value_convert ‘PRIMDB’,'STDYDB’
9> set db_unique_name=’STDYDB’
10>
11> set control_files=’/u01/oradata/STDYDB/control01.ctl’,
12> ‘/opt/app/flash_recovery_area/STDYDB/control02.ctl’
13> set db_file_name_convert=’/PRIMDB/’,'/STDYDB/’
14> set log_file_name_convert=’/PRIMDB/’,'/STDYDB/’
15> set log_archive_max_processes=’5′
16> set fal_client=’STDYDB’
17> set fal_server=’PRIMDB’
18> set standby_file_management=’AUTO’
19> set log_archive_config=’dg_config=(PRIMDB,STDYDB)’
20> set db_recovery_file_dest=’/opt/app/flash_recovery_area’
21> set db_recovery_file_dest_size=’5G’
22> set log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stdydb’;
23> }
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=40 device type=DISK

allocated channel: p2
channel p2: SID=42 device type=DISK

allocated channel: a1
channel a1: SID=96 device type=DISK

Starting Duplicate Db at 11-AUG-11

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/opt/app/oracle/product/11.2.0.2/dbs/orapwPRIMDB’ auxiliary format
‘/opt/app/oracle/product/11.2.0.2/dbs/orapwSTDYDB’ targetfile
‘/opt/app/oracle/product/11.2.0.2/dbs/spfilePRIMDB.ora’ auxiliary format
‘/opt/app/oracle/product/11.2.0.2/dbs/spfileSTDYDB.ora’ ;
sql clone “alter system set spfile= ”/opt/app/oracle/product/11.2.0.2/dbs/spfileSTDYDB.ora””;
}
executing Memory Script

Starting backup at 11-AUG-11
Finished backup at 11-AUG-11

sql statement: alter system set spfile= ”/opt/app/oracle/product/11.2.0.2/dbs/spfileSTDYDB.ora”

contents of Memory Script:
{
sql clone “alter system set audit_file_dest =
”/opt/app/admin/STDYDB/adump” comment=
”” scope=spfile”;
sql clone “alter system set dispatchers =
”(PROTOCOL=TCP) (SERVICE=STDYDBXDB)” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”STDYDB” comment=
”” scope=spfile”;
sql clone “alter system set control_files =
”/u01/oradata/STDYDB/control01.ctl”, ”/opt/app/flash_recovery_area/STDYDB/control02.ctl” comment=
”” scope=spfile”;
sql clone “alter system set db_file_name_convert =
”/PRIMDB/”, ”/STDYDB/” comment=
”” scope=spfile”;
sql clone “alter system set log_file_name_convert =
”/PRIMDB/”, ”/STDYDB/” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_max_processes =
5 comment=
”” scope=spfile”;
sql clone “alter system set fal_client =
”STDYDB” comment=
”” scope=spfile”;
sql clone “alter system set fal_server =
”PRIMDB” comment=
”” scope=spfile”;
sql clone “alter system set standby_file_management =
”AUTO” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_config =
”dg_config=(PRIMDB,STDYDB)” comment=
”” scope=spfile”;
sql clone “alter system set db_recovery_file_dest =
”/opt/app/flash_recovery_area” comment=
”” scope=spfile”;
sql clone “alter system set db_recovery_file_dest_size =
5G 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=stdydb” comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ”/opt/app/admin/STDYDB/adump” comment= ”” scope=spfile

sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=STDYDBXDB)” comment= ”” scope=spfile

sql statement: alter system set db_unique_name = ”STDYDB” comment= ”” scope=spfile

sql statement: alter system set control_files = ”/u01/oradata/STDYDB/control01.ctl”, ”/opt/app/flash_recovery_area/STDYDB/control02.ctl” comment= ”” scope=spfile

sql statement: alter system set db_file_name_convert = ”/PRIMDB/”, ”/STDYDB/” comment= ”” scope=spfile

sql statement: alter system set log_file_name_convert = ”/PRIMDB/”, ”/STDYDB/” comment= ”” scope=spfile

sql statement: alter system set log_archive_max_processes = 5 comment= ”” scope=spfile

sql statement: alter system set fal_client = ”STDYDB” comment= ”” scope=spfile

sql statement: alter system set fal_server = ”PRIMDB” comment= ”” scope=spfile

sql statement: alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile

sql statement: alter system set log_archive_config = ”dg_config=(PRIMDB,STDYDB)” comment= ”” scope=spfile

sql statement: alter system set db_recovery_file_dest = ”/opt/app/flash_recovery_area” comment= ”” scope=spfile

sql statement: alter system set db_recovery_file_dest_size = 5G 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=stdydb” comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 730714112 bytes

Fixed Size 2230080 bytes
Variable Size 432015552 bytes
Database Buffers 289406976 bytes
Redo Buffers 7061504 bytes
allocated channel: a1
channel a1: SID=134 device type=DISK

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u01/oradata/STDYDB/control01.ctl’;
restore clone controlfile to ‘/opt/app/flash_recovery_area/STDYDB/control02.ctl’ from
‘/u01/oradata/STDYDB/control01.ctl’;
}
executing Memory Script

Starting backup at 11-AUG-11
channel p1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.2.0.2/dbs/snapcf_PRIMDB.f tag=TAG20110811T094210 RECID=4 STAMP=758886131
channel p1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 11-AUG-11

Starting restore at 11-AUG-11

channel a1: copied control file copy
Finished restore at 11-AUG-11

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
“/u01/oradata/STDYDB/temp01.dbf”;
switch clone tempfile all;
set newname for datafile 1 to
“/u01/oradata/STDYDB/system01.dbf”;
set newname for datafile 2 to
“/u01/oradata/STDYDB/sysaux01.dbf”;
set newname for datafile 3 to
“/u01/oradata/STDYDB/undotbs01.dbf”;
set newname for datafile 4 to
“/u01/oradata/STDYDB/users01.dbf”;
backup as copy reuse
datafile 1 auxiliary format
“/u01/oradata/STDYDB/system01.dbf” datafile
2 auxiliary format
“/u01/oradata/STDYDB/sysaux01.dbf” datafile
3 auxiliary format
“/u01/oradata/STDYDB/undotbs01.dbf” datafile
4 auxiliary format
“/u01/oradata/STDYDB/users01.dbf” ;
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oradata/STDYDB/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 11-AUG-11
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/oradata/PRIMDB/system01.dbf
channel p2: starting datafile copy
input datafile file number=00002 name=/u01/oradata/PRIMDB/sysaux01.dbf
output file name=/u01/oradata/STDYDB/sysaux01.dbf tag=TAG20110811T094226
channel p2: datafile copy complete, elapsed time: 00:03:35
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/oradata/PRIMDB/undotbs01.dbf
output file name=/u01/oradata/STDYDB/system01.dbf tag=TAG20110811T094226
channel p1: datafile copy complete, elapsed time: 00:04:11
channel p1: starting datafile copy
input datafile file number=00004 name=/u01/oradata/PRIMDB/users01.dbf
output file name=/u01/oradata/STDYDB/users01.dbf tag=TAG20110811T094226
channel p1: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/oradata/STDYDB/undotbs01.dbf tag=TAG20110811T094226
channel p2: datafile copy complete, elapsed time: 00:01:00
Finished backup at 11-AUG-11

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=4 STAMP=758886420 file name=/u01/oradata/STDYDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=758886420 file name=/u01/oradata/STDYDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=758886420 file name=/u01/oradata/STDYDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=758886420 file name=/u01/oradata/STDYDB/users01.dbf
Finished Duplicate Db at 11-AUG-11
released channel: p1
released channel: p2
released channel: a1

RMAN>
RMAN> **end-of-file**

RMAN> exit

Recovery Manager complete.

Verify the spfile

Now that RMAN has completed it’s work, time to double check the spfile created by RMAN.

STDYDB.__db_cache_size=289406976
PRIMDB.__java_pool_size=4194304
STDYDB.__java_pool_size=4194304
PRIMDB.__large_pool_size=4194304
STDYDB.__large_pool_size=4194304
PRIMDB.__oracle_base=’/opt/app/oracle’#ORACLE_BASE set from environment
STDYDB.__oracle_base=’/opt/app/oracle’#ORACLE_BASE set from environment
PRIMDB.__pga_aggregate_target=293601280
STDYDB.__pga_aggregate_target=293601280
PRIMDB.__sga_target=440401920
STDYDB.__sga_target=440401920
PRIMDB._a_shared_io_pool_size=0
STDYDB.__shared_io_pool_size=0
PRIMDB.__shared_pool_size=134217728
STDYDB.__shared_pool_size=130023424
PRIMDB.__streams_pool_size=0
STDYDB.__streams_pool_size=0
*.audit_file_dest=’/opt/app/admin/STDYDB/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/oradata/STDYDB/control01.ctl’,'/opt/app/flash_recovery_area/STDYDB/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_file_name_convert=’/PRIMDB/’,'/STDYDB/’
*.db_name=’PRIMDB’
*.db_recovery_file_dest=’/opt/app/flash_recovery_area’
*.db_recovery_file_dest_size=5368709120
*.db_unique_name=’STDYDB’
*.diagnostic_dest=’/opt/app’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=STDYDBXDB)’
*.fal_client=’STDYDB’
*.fal_server=’PRIMDB’
*.log_archive_config=’dg_config=(PRIMDB,STDYDB)’
*.log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stdydb’
*.log_archive_dest_2=’service=stdydb async valid_for=(online_logfiles,primary_role) dCC”b_unique_name=stdydb’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’primdb_%t_%s_%r.arc’
*.log_archive_max_processes=5
*.log_file_name_convert=’/PRIMDB/’,'/STDYDB/’
*.memory_max_target=734003200
*.memory_target=734003200
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Looks like most of them looks ok. Note that db_name is set to ‘PRIMDB’ and db_unique_name to ‘STDYDB’. A standby database does not get a new DBID if it was created by RMAN.

Enable redo apply process

Now the standby database has duplicated and restored but not recovered. We can start the Media recovery by starting the MRP process (apply process).

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2230080 bytes
Variable Size 432015552 bytes
Database Buffers 289406976 bytes
Redo Buffers 7061504 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Here is the output of the alert log file at the time we started the MRP process.

alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (STDYDB)
Thu Aug 11 09:50:02 2011
MRP0 started with pid=28, OS id=28566
MRP0: Background Managed Standby Recovery process started (STDYDB)
started logmerger process
Thu Aug 11 09:50:07 2011
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived…
All non-current ORLs have been archived.
Media Recovery Log /opt/app/flash_recovery_area/STDYDB/archivelog/2011_08_11/o1_mf_1_31_747tw2y5_.arc
Media Recovery Log /opt/app/flash_recovery_area/STDYDB/archivelog/2011_08_11/o1_mf_1_32_747tw337_.arc
Media Recovery Log /opt/app/flash_recovery_area/STDYDB/archivelog/2011_08_11/o1_mf_1_33_747tw5ct_.arc
Completed: alter database recover managed standby database disconnect from session
Media Recovery Log /opt/app/flash_recovery_area/STDYDB/archivelog/2011_08_11/o1_mf_1_34_747v0390_.arc
Media Recovery Waiting for thread 1 sequence 35 (in transit)

If you see above output in the alert log file then everything looks good.

Now we can verify the redo apply by checking the sequence# and redo apply.

On Primary:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 33
Next log sequence to archive 35
Current log sequence 35
SQL> alter system switch logfile;

System altered.

SQL>

On Standby:

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
———- —————————
31 YES
32 YES
33 YES
34 YES
35 IN-MEMORY

SQL> select database_role from v$database;

DATABASE_ROLE
————————————————
PHYSICAL STANDBY

Now we are done with creating standby database. Next thing to do is to enable the Data Guard Broker.

Enable Data Guard Broker

Connect to primary and set following parameters.

alter system set dg_broker_start=true scope=both;
alter system set dg_broker_config_file1=’/opt/app/admin/PRIMDB/bg_config/primary_dg1.conf’ scope=spfile;
alter system set dg_broker_config_file2=’/opt/app/admin/PRIMDB/bg_config/primary_dg2.conf’ scope=spfile;

Then connect to standby:

alter system set dg_broker_start=true scope=both;
alter system set dg_broker_config_file1=’/opt/app/admin/STDYDB/bg_config/stdydb_dg1.conf’ scope=spfile;
alter system set dg_broker_config_file2=’/opt/app/admin/STDYDB/bg_config/stdydb_dg2.conf’ scope=spfile;

Note: You may need to make sure local_listener settings are done appropriately on primary and standby side to avoid error like ‘Fatal NI connect error 12514, connecting to:’ and ‘TNS-12564: TNS:connection refused’

Add database to Data Guard.

$ dgmgrl
DGMGRL> connect sys/password@PRIMDB
Connected.
DGMGRL> CREATE CONFIGURATION ‘PRIMDB’ AS PRIMARY DATABASE IS ‘PRIMDB’ CONNECT IDENTIFIER IS ‘PRIMDB’;
Configuration ” PRIMDB” created with primary database ” PRIMDB ”
DGMGRL> ADD DATABASE ‘STDYDB’ AS CONNECT IDENTIFIER IS ‘STDYDB’;
Database “STDYDB” added

DGMGRL> show database verbose ‘STDYDB’;

Database – STDYDB

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
STDYDB

Properties:
DGConnectIdentifier = ‘STDYDB’
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 = ’5′
LogArchiveMinSucceedDest = ’1′
DbFileNameConvert = ‘/PRIMDB/, /STDYDB/’
LogFileNameConvert = ‘/PRIMDB/, /STDYDB/’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
SidName = ‘STDYDB’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=htrdbl02.qcorpbb.bb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STDYDB_DGMGRL)(INSTANCE_NAME=STDYDB)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’
AlternateLocation = ”
LogArchiveTrace = ’0′
LogArchiveFormat = ‘primdb_%t_%s_%r.arc’
TopWaitEvents = ‘(monitor)’

Database Status:
DGM-17016: failed to retrieve status for database “STDYDB”
ORA-16664: unable to receive the result from a database

DGMGRL> enable configuration;
Enabled.

At this time I was getting lot of errors in the standby alert log

***********************************************************************

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11.62.59.140)(PORT=1523))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRIMDB_DGB)(INSTANCE_NAME=PRIMDB)(CID=(PROGRAM=oracle)(HOST=htrdbl02.qcorpaa.aa.com)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 11.2.0.2.0 – Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 – Production
Time: 11-AUG-2011 14:27:33
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0

I checked my local_listener setting on my primary database it was empty. And I modified it as follows.

alter system set local_listener=’(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=11.62.59.140)(PORT=1523))(CONNECT_DATA=(SERVICE_NAME=PRIMDB)(INSTANCE_NAME=PRIMDB)(SERVER=DEDICATED)))’

After local_listener was modified, I started getting the database details in the Data Guard. Also the error in the alert log went away.

DGMGRL> show database verbose ‘STDYDB’;

Database – STDYDB

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 2 minutes 2 seconds
Real Time Query: OFF
Instance(s):
STDYDB
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting

Database Warning(s):
ORA-16826: apply service state is inconsistent with the DelayMins property

Properties:
DGConnectIdentifier = ‘STDYDB’
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 = ’5′
LogArchiveMinSucceedDest = ’1′
DbFileNameConvert = ‘/PRIMDB/, /STDYDB/’
LogFileNameConvert = ‘/PRIMDB/, /STDYDB/’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
SidName = ‘STDYDB’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=htrdbl02.qcorpbb.bb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STDYDB_DGMGRL)(INSTANCE_NAME=STDYDB)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’
AlternateLocation = ”
LogArchiveTrace = ’0′
LogArchiveFormat = ‘primdb_%t_%s_%r.arc’
TopWaitEvents = ‘(monitor)’

Database Status:
WARNING

DGMGRL> show database verbose ‘PRIMDB’;

Database – PRIMDB

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRIMDB

Properties:
DGConnectIdentifier = ‘PRIMDB’
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 = ‘stdydb, primdb’
LogFileNameConvert = ‘/stdydb/stdydb, /primdb/primdb/’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
SidName = ‘PRIMDB’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=11.62.59.140)(PORT=1523))(CONNECT_DATA=(SERVICE_NAME=PRIMDB)(INSTANCE_NAME=PRIMDB)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’
AlternateLocation = ”
LogArchiveTrace = ’0′
LogArchiveFormat = ‘primdb_%t_%s_%r.arc’
TopWaitEvents = ‘(monitor)’

Database Status:
SUCCESS

DGMGRL>

I am still playing with dataguard. Will post a detailed datagurad article soon.

This entry was posted in Standby and Data Guard. Bookmark the permalink.