Creating a Physical Standby with Active Duplicate in 11.2.0.3

Creating a physical standby database is a fundamental skill for any DBA, but tutorials often skip over the "why" or assume pre-existing knowledge. This guide aims to bring order to the chaos by breaking down the terminology and providing clear, reproducible steps for building a standby using RMAN Active Duplication.

1. Understanding Data Guard Terminology

Before executing scripts, it is essential to understand the initialization parameters that control redo transport services. These definitions from Oracle’s documentation are critical to a successful setup.

Critical Initialization Parameters

• LOG_ARCHIVE_DEST_n: Controls how redo transport services transfer redo data.
  • ASYNC (Default): Transactions commit without waiting for redo data to be received at the destination.
  • SYNC: Transactions must be received by the standby before they can commit on the primary.
  • AFFIRM / NOAFFIRM: Controls whether a destination acknowledges redo data before (AFFIRM) or after (NOAFFIRM) writing it to the standby redo log.
• DB_UNIQUE_NAME: A required, unique name for the database at this destination.
• VALID_FOR: Identifies when redo transport should transmit data based on the redo_log_type and database_role.
• FAL_SERVER & FAL_CLIENT: Specifies the Fetch Archive Log (FAL) server and client service names to resolve gaps in redo data.
• LOG_ARCHIVE_CONFIG: Enables/disables redo log transport. The DG_CONFIG attribute lists up to 30 unique database names in the configuration.

2. Preparing the Primary Database

For this exercise, we are using:

• Primary: testgg1 on server dlabvm13
• Standby: testgg2 on server dlabvm14

Enabling Archive Log and Force Logging

Before continuing, ensure the primary database is in ARCHIVELOG mode and that FORCE LOGGING is enabled.

SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; SQL> alter database force logging; SQL> select log_mode, force_logging from v$database;  LOG_MODE     FORCE_LOGGING ------------ ------------- ARCHIVELOG   YES 

Network and Authentication Setup

Ensure Listener and TNS entries are set correctly so that you can tnsping both aliases from both servers. Additionally, replicate the password file from the primary to the standby. The file name must match the ORACLE_SID of the standby.

# On Primary (dlabvm13) orapwd file=orapwtestgg1 password=test entries=5 scp orapwtestgg1 dlabvm14:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg2 

3. Initializing the Standby Instance

One advantage of this method is that most work happens on the Primary. On the Standby server, you only need to create directories for diagnostics, redo logs, and datafiles, then start the instance in NOMOUNT using a minimal parameter file.

Minimal Parameter File and Startup

Create a basic init.ora on the standby and start the idle instance.

-- dg.ora on Standby DB_NAME=testgg1 DB_UNIQUE_NAME=testgg2 DB_BLOCK_SIZE=8192  -- Start instance SQL> startup nomount pfile='/home/oracle/bin/dg.ora'; 

4. Building the Standby via RMAN Active Duplication

We will now perform the heavy lifting from the Primary database server, including the configuration of Standby Redo Logs (SRLs).

Configuring Standby Redo Logs (SRLs)

Create SRLs on the primary to ensure it is ready to transition roles if necessary. These should be the same size as your Online Redo Logs.

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 104857600; SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG; 

Executing the RMAN Duplicate Script

We use an RMAN block to automate the duplication and set necessary SPFILE parameters for the standby.

run {  allocate channel prmy1 type disk;  allocate channel prmy2 type disk;  allocate auxiliary channel stby type disk;  duplicate target database for standby from active database  spfile  parameter_value_convert 'testgg1','testgg2'  set db_unique_name='testgg2'  set db_file_name_convert='/u01/app/oracle/oradata/testgg1/','/u01/app/oracle/oradata/testgg2/'  set log_file_name_convert='/u01/app/oracle/oradata/testgg1/','/u01/app/oracle/oradata/testgg2/'  set control_files='/u01/app/oracle/oradata/testgg2/control/control01.ctl','/u01/app/oracle/oradata/testgg2/control/control02.ctl'  set fal_client='testgg2'  set fal_server='testgg1'  set standby_file_management='AUTO'  set log_archive_config='dg_config=(testgg1,testgg2)'  set log_archive_dest_2='service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'; } 

5. Final Verification and Monitoring

Once RMAN finishes, you can open the standby database and start the Managed Recovery Process (MRP).

Testing Synchronization

If using Active Data Guard (note: this is a licensed option), you can open the database to verify synchronization in real-time.

SQL> alter database open; SQL> alter database recover managed standby database disconnect; 

Verify by creating a table on the Primary and checking for its existence on the Standby:

-- On Primary SQL> create table sender.test(id number); SQL> insert into sender.test values(1); SQL> commit; SQL> alter system switch logfile;  -- On Standby SQL> select * from sender.test;         ID ----------          1 

Monitoring Log Apply Status

Use the following queries to check the status of log transport and application.

On Standby (Check last received vs applied):

SELECT 'Last Applied : ' Logs, TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME, sequence#  FROM v$archived_log WHERE applied='YES' AND sequence# = (SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES') UNION SELECT 'Last Received : ' Logs, TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME, sequence#  FROM v$archived_log WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log); 

On Primary (Check ship/apply lag):

SELECT MAX(sequence#) AS Current_Primary_Seq,        (SELECT MAX(sequence#) FROM v$archived_log WHERE dest_id = 2) AS Max_Stby_Received,        ((SELECT MAX(sequence#) FROM v$archived_log WHERE dest_id = 1) -          (SELECT MAX(sequence#) FROM v$archived_log WHERE dest_id = 2)) AS To_Be_Shipped FROM v$archived_log WHERE dest_id = 1; 

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?