Thursday, April 9, 2026

Complete Standby Database Setup from Scratch

 

Complete Standby Database Setup from Scratch

Environment Details (Example)

Primary Server  : primary_server   IP: 192.168.1.10
Standby Server  : standby_server   IP: 192.168.1.20
DB Name         : ORCL
Primary SID     : ORCL
Standby SID     : ORCLDG
Oracle Home     : /u01/app/oracle/product/19c/dbhome_1
Oracle Base     : /u01/app/oracle

PHASE 1 — PRIMARY SERVER PREPARATION

Step 1 — Check Primary Database Mode

-- Must be in ARCHIVELOG mode
SQL> SELECT LOG_MODE FROM V$DATABASE;

-- If NOARCHIVELOG, enable it
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

-- Verify
SQL> SELECT LOG_MODE FROM V$DATABASE;
-- OUTPUT: ARCHIVELOG

Step 2 — Enable Force Logging on Primary

SQL> ALTER DATABASE FORCE LOGGING;

-- Verify
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
-- OUTPUT: YES

Step 3 — Enable Flashback (Recommended)

SQL> ALTER DATABASE FLASHBACK ON;

-- Verify
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

Step 4 — Create Standby Redo Logs on Primary

-- Formula: (primary redo log groups + 1) x threads
-- Example: Primary has 3 groups → create 4 SRL groups

-- Check existing redo log size first
SQL> SELECT GROUP#, BYTES/1024/1024 MB FROM V$LOG;

-- Create Standby Redo Logs (same size as online redo logs)
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
     '/u01/app/oracle/oradata/ORCL/stdby_redo04.log' SIZE 200M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
     '/u01/app/oracle/oradata/ORCL/stdby_redo05.log' SIZE 200M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
     '/u01/app/oracle/oradata/ORCL/stdby_redo06.log' SIZE 200M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
     '/u01/app/oracle/oradata/ORCL/stdby_redo07.log' SIZE 200M;

-- Verify
SQL> SELECT GROUP#, MEMBER FROM V$STANDBY_LOG;

Step 5 — Configure Primary init.ora / spfile Parameters

-- Check current parameter file type
SQL> SHOW PARAMETER spfile;

-- Set Data Guard parameters on PRIMARY
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MODE=TRUE SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
     'LOCATION=/u01/app/oracle/archivelog/ORCL/
      VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
      DB_UNIQUE_NAME=ORCL' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
     'SERVICE=ORCLDG LGWR ASYNC
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=ORCLDG' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ORCL' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=
     'DG_CONFIG=(ORCL,ORCLDG)' SCOPE=BOTH;

SQL> ALTER SYSTEM SET FAL_SERVER='ORCLDG' SCOPE=BOTH;
SQL> ALTER SYSTEM SET FAL_CLIENT='ORCL' SCOPE=BOTH;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH;

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=
     '/oradata/ORCL/','/oradata/ORCLDG/' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=
     '/oradata/ORCL/','/oradata/ORCLDG/' SCOPE=SPFILE;

Step 6 — Configure listener.ora on Primary

# Edit /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_DGMGRL)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCL)
    )
  )

Step 7 — Configure tnsnames.ora on Primary

vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLDG)
    )
  )

# Reload listener
lsnrctl reload
lsnrctl status

Step 8 — Create Password File on Primary

# Check existing password file
ls $ORACLE_HOME/dbs/orapwORCL

# Create if not exists
orapwd file=$ORACLE_HOME/dbs/orapwORCL password=SysPassword123 entries=10

# Copy password file to standby (MUST be identical)
scp $ORACLE_HOME/dbs/orapwORCL \
    oracle@standby_server:$ORACLE_HOME/dbs/orapwORCLDG

PHASE 2 — STANDBY SERVER PREPARATION

Step 9 — Create Directory Structure on Standby

# Login to standby server as oracle user
ssh oracle@standby_server

# Create all required directories
mkdir -p /u01/app/oracle/oradata/ORCLDG
mkdir -p /u01/app/oracle/archivelog/ORCLDG
mkdir -p /u01/app/oracle/fast_recovery_area/ORCLDG
mkdir -p /u01/app/oracle/admin/ORCLDG/adump
mkdir -p /u01/app/oracle/admin/ORCLDG/bdump
mkdir -p /u01/app/oracle/admin/ORCLDG/cdump
mkdir -p /u01/app/oracle/admin/ORCLDG/udump
mkdir -p /u01/app/oracle/admin/ORCLDG/pfile
mkdir -p /backup/standby

# Set correct permissions
chown -R oracle:oinstall /u01/app/oracle/oradata/ORCLDG
chown -R oracle:oinstall /u01/app/oracle/archivelog/ORCLDG
chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area/ORCLDG
chown -R oracle:oinstall /u01/app/oracle/admin/ORCLDG
chown -R oracle:oinstall /backup/standby

chmod -R 755 /u01/app/oracle/oradata/ORCLDG
chmod -R 755 /u01/app/oracle/archivelog/ORCLDG

# Verify directories
ls -lR /u01/app/oracle/oradata/ORCLDG
ls -lR /u01/app/oracle/admin/ORCLDG

Step 10 — Create standby init.ora on Standby

vi /u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora

# ── Database Identity ──────────────────────────────────
*.db_name='ORCL'                          # Same as primary
*.db_unique_name='ORCLDG'                 # Different from primary
*.db_domain=''

# ── Instance ───────────────────────────────────────────
*.instance_name='ORCLDG'

# ── Memory ─────────────────────────────────────────────
*.sga_target=2G
*.pga_aggregate_target=512M
*.memory_target=0

# ── Paths ──────────────────────────────────────────────
*.db_create_file_dest='/u01/app/oracle/oradata/ORCLDG'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/ORCLDG'
*.db_recovery_file_dest_size=50G

*.audit_file_dest='/u01/app/oracle/admin/ORCLDG/adump'
*.core_dump_dest='/u01/app/oracle/admin/ORCLDG/cdump'
*.background_dump_dest='/u01/app/oracle/admin/ORCLDG/bdump'
*.user_dump_dest='/u01/app/oracle/admin/ORCLDG/udump'

# ── File Name Convert (Primary path → Standby path) ───
*.db_file_name_convert=
    '/u01/app/oracle/oradata/ORCL/',
    '/u01/app/oracle/oradata/ORCLDG/'

*.log_file_name_convert=
    '/u01/app/oracle/oradata/ORCL/',
    '/u01/app/oracle/oradata/ORCLDG/'

# ── Archivelog ─────────────────────────────────────────
*.log_archive_dest_1=
    'LOCATION=/u01/app/oracle/archivelog/ORCLDG/
     VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
     DB_UNIQUE_NAME=ORCLDG'

*.log_archive_dest_2=
    'SERVICE=ORCL LGWR ASYNC
     VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
     DB_UNIQUE_NAME=ORCL'

*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_config='DG_CONFIG=(ORCL,ORCLDG)'

# ── Data Guard ─────────────────────────────────────────
*.fal_server='ORCL'
*.fal_client='ORCLDG'
*.standby_file_management='AUTO'

# ── Redo Transport ─────────────────────────────────────
*.remote_login_passwordfile='EXCLUSIVE'

# ── Misc ───────────────────────────────────────────────
*.compatible='19.0.0'
*.open_cursors=300
*.processes=300
*.undo_tablespace='UNDOTBS1'
*.control_files='/u01/app/oracle/oradata/ORCLDG/control01.ctl',
                '/u01/app/oracle/oradata/ORCLDG/control02.ctl'

Step 11 — Configure listener.ora on Standby

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDG)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCLDG)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDG_DGMGRL)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCLDG)
    )
  )

Step 12 — Configure tnsnames.ora on Standby

vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLDG)
    )
  )

# Start listener on standby
lsnrctl start
lsnrctl status

Step 13 — Set Oracle Environment on Standby

# Edit .bash_profile on standby
vi ~/.bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export ORACLE_SID=ORCLDG
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

# Apply
source ~/.bash_profile

# Verify
echo $ORACLE_SID

Step 14 — Test Connectivity Between Primary and Standby

# From primary — test connection to standby
tnsping ORCLDG

# From standby — test connection to primary
tnsping ORCL

# Test RMAN connection from standby
rman TARGET sys/password@ORCL
rman TARGET sys/password@ORCLDG

PHASE 3 — BUILD STANDBY USING RMAN DUPLICATE

Step 15 — Start Standby in NOMOUNT

# On standby server
export ORACLE_SID=ORCLDG

sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora';

Step 16 — Run RMAN DUPLICATE from Standby Server

# Connect RMAN to both primary (TARGET) and standby (AUXILIARY)
rman TARGET sys/SysPassword123@ORCL \
     AUXILIARY sys/SysPassword123@ORCLDG

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY
      FROM ACTIVE DATABASE
      DORECOVER
      SPFILE
        PARAMETER_VALUE_CONVERT
          '/u01/app/oracle/oradata/ORCL/',
          '/u01/app/oracle/oradata/ORCLDG/'
        SET db_unique_name='ORCLDG'
        SET db_file_name_convert=
            '/u01/app/oracle/oradata/ORCL/',
            '/u01/app/oracle/oradata/ORCLDG/'
        SET log_file_name_convert=
            '/u01/app/oracle/oradata/ORCL/',
            '/u01/app/oracle/oradata/ORCLDG/'
        SET control_files=
            '/u01/app/oracle/oradata/ORCLDG/control01.ctl',
            '/u01/app/oracle/oradata/ORCLDG/control02.ctl'
        SET log_archive_dest_1=
            'LOCATION=/u01/app/oracle/archivelog/ORCLDG/'
        SET fal_server='ORCL'
        SET fal_client='ORCLDG'
        SET standby_file_management='AUTO'
      NOFILENAMECHECK;

RMAN will now automatically copy all datafiles, controlfile, and apply archivelogs. This may take time depending on DB size.

PHASE 4 — POST DUPLICATE STEPS

Step 17 — Create spfile from pfile on Standby

SQL> CREATE SPFILE FROM PFILE=
     '/u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora';

-- Restart using spfile
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 18 — Start MRP Apply Process

-- Start Managed Recovery Process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
     DISCONNECT FROM SESSION;

-- OR with real-time apply (preferred)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
     USING CURRENT LOGFILE
     DISCONNECT FROM SESSION;

Step 19 — Verify Standby is Working

-- Check database role
SQL> SELECT DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE,
            PROTECTION_MODE FROM V$DATABASE;

-- Check MRP process
SQL> SELECT PROCESS, STATUS, SEQUENCE#,
            BLOCKS FROM V$MANAGED_STANDBY;

-- Check archive log apply status
SQL> SELECT SEQUENCE#, APPLIED, FIRST_TIME,
            NEXT_TIME FROM V$ARCHIVED_LOG
     ORDER BY SEQUENCE# DESC;

-- Check gap between primary and standby
SQL> SELECT * FROM V$ARCHIVE_GAP;

-- Compare sequences
-- On Primary:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

-- On Standby:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
     WHERE APPLIED = 'YES';

Complete Checklist

PRIMARY SIDE
─────────────────────────────────────────────────
[ ] Database in ARCHIVELOG mode
[ ] FORCE LOGGING enabled
[ ] Standby Redo Logs created
[ ] LOG_ARCHIVE_DEST_2 pointing to standby
[ ] LOG_ARCHIVE_CONFIG set
[ ] FAL_SERVER / FAL_CLIENT set
[ ] listener.ora updated with static entry
[ ] tnsnames.ora has both ORCL and ORCLDG entries
[ ] Password file created and copied to standby

STANDBY SIDE
─────────────────────────────────────────────────
[ ] All directories created with correct permissions
[ ] init_ORCLDG.ora created with standby parameters
[ ] db_file_name_convert set correctly
[ ] log_file_name_convert set correctly
[ ] listener.ora configured with static SID entry
[ ] tnsnames.ora has both entries
[ ] ORACLE_SID set to ORCLDG in .bash_profile
[ ] tnsping working both ways
[ ] STARTUP NOMOUNT successful
[ ] DUPLICATE completed without errors
[ ] spfile created from pfile
[ ] MRP process started
[ ] Sequence# in sync with primary
[ ] No gaps in V$ARCHIVE_GAP

Key Points to Remember

Item Value
db_name Same on primary and standby
db_unique_name Different (ORCL vs ORCLDG)
Password file Must be identical on both
Standby Redo Logs Must exist on both servers
NOFILENAMECHECK Use when directory paths are different
MRP Must be running for real-time sync
After switchover
 Always take fresh Level 0 backup
___________________________________***______________________________________

Minimal init.ora for Standby Database

Absolute Minimum Parameters Required



# /u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora

# ── Must Have ──────────────────────────────────────────

# Same as primary db_name
db_name='ORCL'

# Unique name for standby
db_unique_name='ORCLDG'

# Controlfile location on standby
control_files='/u01/app/oracle/oradata/ORCLDG/control01.ctl',
              '/u01/app/oracle/oradata/ORCLDG/control02.ctl'

# Convert primary paths to standby paths
db_file_name_convert='/u01/app/oracle/oradata/ORCL/',
                     '/u01/app/oracle/oradata/ORCLDG/'

log_file_name_convert='/u01/app/oracle/oradata/ORCL/',
                      '/u01/app/oracle/oradata/ORCLDG/'

# Required for redo transport authentication
remote_login_passwordfile='EXCLUSIVE'

# Audit directory must exist
audit_file_dest='/u01/app/oracle/admin/ORCLDG/adump'

# Oracle version compatibility
compatible='19.0.0'
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Enable OpenSSH on Windows 11

Step 1: Install OpenSSH Server You can do this via PowerShell (run as Administrator ): Check if it's already available: Get-WindowsCapab...