Recently in the course of my work, I had to implement a logical standby database for a production 10g Release 2 (10.2.0.5) database. Now, this is not a huge task in itself, but most Dataguard configuration requests are usually for physical standby databases, which are identical (right down to the sizing and number of datafiles) to the production primary database. So in the interests of guiding anyone else who may be called upon to perform a similiar task (and, as usual, to document any interesting/fairly unusual tasks I’m called upon to perform), the following is an overview of the process.
Oracle Dataguard Broker is a distributed management client for managing an Oracle Dataguard configuration. In my experience, when configured correctly, this tool provides an extremely useful (and simple!) interface for the database administrator to carry out most tasks (as well as provides the capability to implement Fast Start Failover, which is not possible without it).
As per any implementation, there are several pre-requisites that need to be met first:
i. ensure that both production and standby sites have Enterprise Editions of Oracle database installed (with the exact product versions) *
ii. ensure that both production and standby sites are running on the same platform and release level**
iii. ensure that the production database is running in ARCHIVELOG mode
iv. ensure that there is network connectivity between Dataguard sites, on the required protocol (via a simple /etc/hosts entry, or the enterprise DNS settings)
* Oracle Dataguard requires Enterprise Edition, and it is recommended to be on the same version
** It is possible to implement heterogeneous configurations, but this introduces an unnecessary level of ambiguity (especially relating to bugs and the like) that most enterprises avoid at all costs
Some background information about my current test setup environment (for those particular about such things):
| Platform | RHEL 5 64-bit |
| Database version | Oracle 10g R2 (10.2.0.5) |
| Primary hostname/IP | rhel1/192.168.247.131 |
| Standby hostname/IP | rhel2/192.168.247.132 |
| Listener ports | 1522 (on both servers) |
| TNS service names (primary and standby) | DBPROD and DBDR |
| DB Unique names (primary and standby) | db10g and db10gdr |
| Oracle SID (primary and standby) | db10g and db10gdr |
As with any Dataguard configuration, first we need to prepare the sites for our Dataguard configuration, as a matter of fact, for a logical standby database we first start with a physical standby, with some additional steps as shown below:
i. Setup network entries on both the TNSNAMES.ORA and LISTENER.ORA files for all sites
TNSNAMES.ORA (production and standby)
DBPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.131)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = db10g)
)
)
DBDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.132)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = db10gdr)
)
)
Note:
The hostname is specified as an IP address as I do not have any DNS configured in this environment, but it’s possible to use hostnames if you do in your environment
LISTENER.ORA (production)
SID_LIST_LISTENER_PROD =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DB_NAME = db10g)
(SID_NAME = db10g)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DB_NAME = db10g_DGMGRL)
(SID_NAME = db10g)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
)
)
LISTENER_PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.131)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
LISTENER.ORA (standby)
SID_LIST_LISTENER_DR =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DB_NAME = db10gdr)
(SID_NAME = db10gdr)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DB_NAME = db10gdr_DGMGRL)
(SID_NAME = db10gdr)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
)
)
LISTENER_DR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.132)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
Note:
There are 2 static entries in the LISTENER.ORA file for both the production and standby database (1 for Dataguard Broker, and 1 for the database itself), this is necessary to enable switchover via Dataguard broker
Test the TNSNAMES.ORA entries via TNSPING (and later, via logging on to SQLPlus with these entries) before proceeding further
ii. Enable FORCE LOGGING mode on the primary database (via SQLPlus as the SYS user)
SQL> alter database force logging;
Database altered.
iii. Enable supplemental logging for all tables that will be replicated
SQL> alter database add supplemental log data (PRIMARY KEY, UNIQUE INDEX) columns;
Database altered.
Note:
With logical standby databases, every row in a table that is replicated needs to be uniquely identified (via primary or unique key constraints). This is done as transactions/changes to these tables are replicated at the standby database as separate SQL statements, and change vectors from archived logs cannot be directly applied, as with physical standby databases.
To verify which tables currently do not have primary or unique key constraints enabled at production, run the following query:
SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = ‘Y’;
To verify if supplemental logging has already been enabled for primary and unique keys, run the following:
select name, SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
iv. Create a PFILE from the production database, edit the necessary parameters use it to create an SPFILE at the standby location
SQL> create pfile from spfile;
File created.
Note:
It is recommended to use an SPFILE, as Oracle’s Dataguard Broker will make several parameter changes when in use (and also during switchover/failover operations), that are not possible with a static parameter file.
Parameters that are edited include:
| Primary database | Standby database |
| *.db_unique_name=’db10g’ | *.db_unique_name=’db10gdr’ |
| *.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.131)(PORT=1522)))’ | *.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.132)(PORT=1522)))’ |
At standby site:
SQL> create spfile from pfile=’$ORACLE_HOME/dbs/initdb10g.ora’
File created.
v. Backup the primary database and restore it to the standby site
Note:
Various methods exist for performing a database backup and restoration, for this particular request, I had to utilize an offline backup (via storage replication) to the DR site. But if possible, utilize Oracle’s Recovery Manager (RMAN) to perform a database backup and restoration (in Oracle 11g, the restoration can be carried out without even having a prior RMAN backup directly to the DR site)
vi. Create a standby controlfile and transfer it to the standby site
SQL> alter database create standby controlfile as ‘/home/oracle/control.ctl’;
Database altered.
Note:
To create a logical standby database, we first start with a physical standby database, which cannot be mounted using a regular (open READ-WRITE) controlfile.
Replace the existing controlfile locations at the standby site with this file (a simple OS-level cp command will suffice)
vii. Copy the password file from production to the DR site
Note:
Oracle Dataguard requires privileged (SYS) connections to all standby databases in the configuration, and as such a password file is required, with the same SYS password as in the current production database.
Additionally, ensure that the current value for the remote_login_passwordfile is set to EXCLUSIVE
viii. Startup and mount the standby database at the DR site
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2082496 bytes
Variable Size 113248576 bytes
Database Buffers 50331648 bytes
Redo Buffers 6303744 bytes
SQL> alter database mount standby database;
Database altered.
Note:
At this point it will be possible to test connectivity status for both the primary and standby databases (both listeners on primary and standby servers will need to be up for this to work) via SQLPlus
[oracle@/home/oracle, SID=db10g]sqlplus sys/oracle@dbprod as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jul 15 20:26:41 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
[oracle@/home/oracle, SID=db10g]sqlplus sys/oracle@dbdr as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jul 15 20:26:46 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
ix. Create an Oracle Dataguard Broker configuration and add the standby database (physical standby)
[oracle@/home/oracle, SID=db10g]dgmgrl sys/oracle
DGMGRL for Linux: Version 10.2.0.4.0 – 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected.
DGMGRL> CREATE CONFIGURATION testconfig as primary database is db10g connect identifier is dbprod;
Configuration “testconfig” created with primary database “db10g”
DGMGRL> add database db10gdr as connect identifier is dbdr maintained as physical;
Database “db10gdr” added
DGMGRL> show configuration;
Configuration
Name: testconfig
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
db10g – Primary database
db10gdr – Physical standby database
Current status for “testconfig”:
DISABLED
DGMGRL> enable configuration;
Enabled.
— after a few minutes
DGMGRL> show configuration;
Configuration
Name: testconfig
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
db10g – Primary database
db10gdr – Physical standby database
Current status for “testconfig”:
SUCCESS
Note:
Before starting the steps shown above, ensure that the dg_broker_start parameter is set to TRUE on both the primary and standby databases
Additionally, if there are existing Dataguard Broker files from an earlier configuration, occasionally there will be errors triggered when adding a database. To avoid this, delete/move existing Dataguard Broker files (based on the dg_broker_config_file1 and dg_broker_config_file2 database parameters) before re-trying
x. Verify the recovery status of the standby database
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied
1 97 97
Note:
Before converting the existing physical standby database to a logical standby database, we first need to ensure that all transactions have been applied and both databases are synchronized
xi. Stop recovery on the physical standby database
DGMGRL> disable database db10gdr;
Disabled.
– on standby database
SQL> alter database recover managed standby database cancel;
Database altered.
xii. Configure archived log destination parameters on primary
| Parameter | Value |
| log_archive_dest_1 | location=”/oracle/product/10.2.0/db_1/dbs/arch”, valid_for=(online_logfiles,all_roles) db_unique_name=db10g |
| log_archive_dest_2 | service=dbdr LGWR ASYNC NOAFFIRM
db_unique_name=”db10gdr” valid_for=(online_logfile,primary_role) |
| log_archive_dest_3 | location=/u01/arch/standby/ valid_for=(standby_logfiles,standby_role) db_unique_name=db10g |
Note:
All 3 destinations on the primary database must also be enabled (log_archive_dest_n_state parameter)
The third archived log destination is only valid when the current primary database is in a standby role
xiii. Create standby redo logs on both production and standby databases
SQL> select b.group#, a.member, b.bytes/1024/1024 “MB” from v$logfile a, v$log b where a.group#=b.group#;
GROUP# MEMBER MB
1 /oracle/oradata/db10g/redo01.log 50
2 /oracle/oradata/db10g/redo02.log 50
SQL> alter database add standby logfile group 3 ‘/oracle/oradata/db10g/redo03.log’ size 50m;
Database altered.
SQL> alter database add standby logfile group 4 ‘/oracle/oradata/db10g/redo04.log’ size 50m;
Database altered.
SQL> alter database add standby logfile group 5 ‘/oracle/oradata/db10g/redo05.log’ size 50m;
Database altered.
Note:
Standby logfiles do not need to be multiplexed, and the number of standby logfile groups are (online logfile group# + 1)
xiv. Build a data dictionary in the redo data on the primary database
SQL> exec dbms_logstdby.build;
PL/SQL procedure successfully completed.
xv. Convert standby database to logical standby database and start recovery
SQL> alter database recover to logical standby db10gdr;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Edit archived log destination parameters as necessary
| Parameter | Value |
| log_archive_dest_1 | location=”/oracle/product/10.2.0/db_1/dbs/arch”, valid_for=(online_logfiles,all_roles) db_unique_name=db10gdr |
| log_archive_dest_2 | service=dbprod LGWR ASYNC NOAFFIRM db_unique_name=”db10g” valid_for=(online_logfile,primary_role) |
| log_archive_dest_3 | location=/u01/arch/standby/ valid_for=(standby_logfiles,standby_role) db_unique_name=db10gdr |
SQL> alter database open resetlogs;
Database altered.
SQL> alter database start logical standby apply immediate;
Database altered.
DGMGRL> remove database db10gdr;
Removed database “db10gdr” from the configuration
DGMGRL> add database db10gdr as connect identifier is dbdr maintained as logical;
Database “db10gdr” added
DGMGRL> enable configuration;
DGMGRL> show configuration;
Configuration
Name: testconfig
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
db10g – Primary database
db10gdr – Logical standby database
Current status for “testconfig”:
SUCCESS
oracletempspace (or my adventures with Oracle) 