Master Oracle Data Guard: Step‑by‑Step Master‑Slave Setup Guide

Preface

Our projects currently use many Oracle databases. Although domestic databases are trending, Oracle, MySQL, and PostgreSQL will remain mainstream for the next one to two years. Existing online guides for Oracle master‑slave configuration are often incomplete or contain errors. Since a Shandong project also uses Oracle, I record a practical tutorial for configuring Oracle master‑slave to help others.

The tutorial is divided into several parts:

Basic Setup : Introduces the environment and prerequisites for Oracle master‑slave configuration.

Detailed Steps : Provides a step‑by‑step walkthrough with full command explanations.

Common Issues and Solutions : Shares problems that may arise during configuration and how to resolve them.

Maintenance and Management : Offers advice and tips for ongoing master‑slave database maintenance.

Hope this guide becomes a useful reference for Oracle users, especially teams needing to configure master‑slave databases.

Data Guard Overview

Oracle Data Guard is a high‑availability solution that synchronizes redo logs between a primary and a standby database to ensure data consistency, enable fast failover, and support disaster recovery without requiring additional hardware or software components.

Deployment Architecture

Configuration Preparation

Open Order : Start standby first, then primary (start listener, open alert log). Close Order : Shut down primary first, then standby.

Check database version

# Primary IP: 10.41.170.30; Standby IP: 10.41.170.130
# Verify database version; must be Enterprise Edition for Data Guard
SQL> select * from v$version;

Standby should only have software installed to avoid many errors.

Ensure standby installation path and instance name match the primary exactly.

1. Primary Database Configuration

1. Create interlib folder and log directory

# Log file path: 'logUrl=E:\oracl11\interlib\log'
# Control file path: 'standbyUrl=E:\oracl11\interlib'

2. Enable Archivelog Mode

# Start database in mount mode and enable archivelog and force logging
sqlplus / as sysdba
shutdown immediate;
startup mount;
alter database archivelog;
startup open;
alter database force logging;
select log_mode,force_logging from v$database;
archive log list;

3. Create standby redo log files

# logUrl=E:\oracl11\interlib\log (adjust as needed)
SQL> alter database add standby logfile group 4 ('E:/oracl11/interlib/log/STAN04.LOG') size 50m;
SQL> alter database add standby logfile group 5 ('E:/oracl11/interlib/log/STAN05.LOG') size 50m;
SQL> alter database add standby logfile group 6 ('E:/oracl11/interlib/log/STAN06.LOG') size 50m;

4. Create standby control file

SQL> alter database create standby controlfile as 'E:\oracl11\interlib\standby.ctl';

5. Export and modify initialization parameters

SQL> create pfile='E:/oracl11/interlib/initora.ora' from spfile;
# Modify paths and add missing parameters, e.g.:
orcl11.__db_cache_size=24293408768
orcl11.__java_pool_size=134217728
orcl11.__large_pool_size=134217728
orcl11.__oracle_base='E:\oracl11'
... (other parameters omitted for brevity)

6. Reload configuration and start services

# Stop service
SQL> shutdown immediate;
# Start with new pfile
SQL> startup pfile='E:\oracl11\interlib\initora.ora' nomount;
# Create spfile from pfile
SQL> create spfile from pfile='E:\oracl11\interlib\initora.ora';
# Restart
SQL> shutdown immediate;
SQL> startup;

7. Create password file

# If needed, create password file (example password: mstchina*2022)
orapwd file=E:\oracl11\product\11.2.0\dbhome_1\database\PWDorcl11.ora password=mstchina*2022 entries=10

8. Configure listener and tnsnames

# listener.ora (example snippet)
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\oracl11\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl11)
      (ORACLE_HOME = E:\oracl11\product\11.2.0\dbhome_1)
      (SID_NAME = orcl11)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.170.30)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = E:\oracl11

# tnsnames.ora (example snippet)
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
orcl11 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-QEDP6N8PVGK)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl11)
    )
  )
PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.170.30)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl11)
    )
  )
STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.170.130)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl11)
    )
  )

9. Test listener

# Stop and start listener
lsnrctl stop
lsnrctl start
# Verify connection
sqlplus sys/mstchina*2022@primary as sysdba

PS: If connection fails, check firewall settings or ping the IP address.

10. Shut down primary database

SQL> shutdown immediate;

PS: The primary must be shut down before configuring the standby to avoid generating extra logs.

2. Standby Database Configuration

1. Copy data to standby

Create E:\oracl11\interlib\tmp on both primary and standby and copy the rest of interlib there.

Copy the oradata directory from primary to standby.

Copy admin, cfgtollogs, diag, flash_recover_area, and the password file E:\oracl11\product\11.2.0\dbhome_1\database\PWDorcl11.ora to the standby.

Copy standby.ctl to both E:\oracl11\oradata\orcl11\ and E:\oracl11\flash_recovery_area\orcl11\, renaming them to control01.ctl and control02.ctl.

Copy listener.ora and tnsnames.ora to the standby and change the listener IP to the standby IP.

2. Create standby instance

# Register Oracle instance on standby (if Oracle is installed)
oradim -new -sid orcl11
lsnrctl start

3. Modify standby parameters and create instance

# Adjust parameters copied from primary (excerpt)
orcl11.__db_cache_size=24293408768
orcl11.__java_pool_size=134217728
... (other parameters identical to primary, with standby‑specific values for control files, log_archive_dest, etc.)

4. Start standby with new parameter file

SQL> startup nomount pfile='E:\oracl11\interlib\initora.ora';
SQL> create spfile from pfile='E:\oracl11\interlib\initora.ora';
SQL> shutdown immediate; -- may show ORA‑01507, ignore
SQL> startup nomount;

At this point, logging into sqlplus may raise ORA‑12560: TNS: protocol adapter error. The fix is to ensure the listener service OracleOraDb11g_home1TNSlistener is running or start it via emca.bat, and set the system environment variable oracle_sid to the SID (e.g., orcl11).

3. Primary Database Execution

1. Create primary backup

# On standby, start database
sqlplus / as sysdba
startup;
# On primary, use RMAN to create backup for standby
rman target /
RMAN> backup full database format='E:\oracl11\interlib\tmp\FOR_STANDBY_%u%p%s,RMN' include current controlfile for standby;
RMAN> sql 'alter system archive log current';

2. Copy database

Copy the backup set from E:\oracl11\interlib\tmp on the primary to the same path on the standby.

# After copying, run on standby (must be in NOMOUNT state)
RMAN> connect auxiliary sys/mstchina*2022@standby
RMAN> duplicate target database for standby nofilenamecheck;

3. Set standby to standby mode

# Start standby in mount mode
SQL> startup nomount;
SQL> alter database mount standby database; -- may show ORA‑01100, ignore
SQL> alter database recover managed standby database disconnect from session;
SQL> shutdown immediate;
SQL> startup;

4. Verification and Testing

1. Status check

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

2. Log switch test

# On primary, manually switch archive log
alter system switch logfile;
# Verify both primary and standby have a new .DBF log file via
SELECT name FROM v$archived_log;

3. Check primary/standby role and protection mode

SELECT open_mode, protection_mode, database_role, switchover_status FROM v$database;

5. Data Guard Switchover

# On primary, perform switchover to standby
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
# If using standby redo logs for real‑time apply:
alter database recover managed standby database using current logfile disconnect from session;

On standby, complete the switchover back to primary:

# On standby
alter database commit to switchover to primary with session shutdown;
alter database recover managed standby database disconnect from session;
shutdown immediate;
startup;

6. Post‑Switchover Recovery

After the switchover, the original primary becomes standby and can be switched back to primary using the same steps.

# Open order: start standby first, then primary (start listener, open alert log)
# Close order: shut down primary first, then standby
lsnrctl stop
lsnrctl start

7. Common Error Handling

ORA‑01507: Database not installed

Can be ignored.

ORA‑10456: Unable to open standby database (media recovery in progress)

alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

ORA‑01031: Insufficient privileges

Check that the password file exists and follows the naming rule PWD<SID>.ora (e.g., PWDorcl11.ora).

ORA‑01110: Data file not found

# Re‑backup primary and duplicate to standby as described in the backup section.

ORA‑02232, ORA‑01090, ORA‑01100, ORA‑01153

Typical resolution: abort the current session, restart the database, and retry the operation.

Source: https://juejin.cn/post/7331980540208840713#heading-38 (© original author)