JAVA J2EE 4 U

 

Setting up a new physical standby database for an existing Oracle primary database (Data Guard configuration) requires careful planning and a series of precise steps. The process below assumes you are creating a physical standby (not logical) database and both servers (primary and standby) run on similar OS and Oracle versions.

---

✅ Assumptions

• Primary DB Name: PROD
• Standby DB Name: PROD (must be the same for physical standby)
• Oracle version: 19c (similar steps apply to 12c/21c with minor changes)
• Oracle is running in ARCHIVELOG mode
• Primary DB is using Force Logging
• Oracle Net (TNS) is properly configured between the two servers
• You have password file and listener setup
• You have sufficient disk space and network access between primary and standby

---

🛠️ High-Level Steps

1. [ ] Enable Force Logging & ArchiveLog on Primary
2. [ ] Configure Initialization Parameters (Primary & Standby)
3. [ ] Create Password File on Standby
4. [ ] Configure Network (tnsnames.ora, listener.ora)
5. [ ] Create Standby Control File
6. [ ] Copy Files to Standby Server
7. [ ] Prepare the Standby Parameter File (pfile/spfile)
8. [ ] Start Standby Instance in NOMOUNT
9. [ ] Restore or Duplicate the Database
10. [ ] Start Redo Apply (MRP)
11. [ ] Configure Data Guard Broker (Optional but Recommended)
12. [ ] Test the Setup

---

🔍 Step-by-Step Details

---

🔹 1. Enable ARCHIVELOG and FORCE LOGGING on Primary

-- Log in to SQL*Plus on primary as SYSDBA
SQL> ARCHIVE LOG LIST;
-- If not enabled:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

-- Enable Force Logging
SQL> ALTER DATABASE FORCE LOGGING;

---

🔹 2. Configure Primary Database Initialization Parameters

Modify or add these to PROD primary (typically in spfile):

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD)' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PROD_STBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STBY' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

ALTER SYSTEM SET FAL_SERVER=PROD_STBY SCOPE=BOTH;
ALTER SYSTEM SET FAL_CLIENT=PROD SCOPE=BOTH;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/oradata/PROD_STBY','/u02/oradata/PROD' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u02/oradata/PROD_STBY','/u02/oradata/PROD' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

Replace /u02/oradata/... paths with your actual datafile/log locations.

---

🔹 3. Create Password File on Primary and Copy to Standby

On primary:

cd $ORACLE_HOME/dbs
orapwd file=orapwPROD password=oracle entries=10 format=12

Copy it to the standby server:

scp $ORACLE_HOME/dbs/orapwPROD oracle@standby_host:$ORACLE_HOME/dbs/

---

🔹 4. Configure Network Files (tnsnames.ora, listener.ora)

On both servers, add TNS entries for both PROD and PROD_STBY:

Example tnsnames.ora:

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

PROD_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

Start listener on both:

lsnrctl start

Test connectivity using:

tnsping PROD
tnsping PROD_STBY

---

🔹 5. Create Standby Control File from Primary

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby.ctl';

---

🔹 6. Copy Files to Standby Server

Copy from primary to standby:

• Datafiles
• Standby control file
• Online Redo Logs (optional if using Data Guard to create them)
• Password file
• Init file if using pfile
• Archivelogs (at least a few for recovery)
• tnsnames.ora and listener.ora

Example commands:

scp /u02/oradata/PROD/* oracle@standby_host:/u02/oradata/PROD_STBY/
scp /tmp/standby.ctl oracle@standby_host:/u02/oradata/PROD_STBY/control01.ctl

---

🔹 7. Prepare Init or Spfile on Standby

You can either:

• Create initPROD.ora manually
• Or copy and modify a pfile from primary and create a spfile

Sample initPROD.ora on standby:

DB_NAME=PROD
DB_UNIQUE_NAME=PROD_STBY
CONTROL_FILES='/u02/oradata/PROD_STBY/control01.ctl'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PROD_STBY)'
LOG_ARCHIVE_DEST_1='LOCATION=/archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD_STBY'
LOG_ARCHIVE_DEST_2='SERVICE=PROD ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
FAL_SERVER=PROD
FAL_CLIENT=PROD_STBY
DB_FILE_NAME_CONVERT='/u02/oradata/PROD','/u02/oradata/PROD_STBY'
LOG_FILE_NAME_CONVERT='/u02/oradata/PROD','/u02/oradata/PROD_STBY'
STANDBY_FILE_MANAGEMENT=AUTO

---

🔹 8. Start Standby in NOMOUNT

export ORACLE_SID=PROD
sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initPROD.ora';

(Use spfile if already created.)

---

🔹 9. Restore or Duplicate the Database

From primary, connect to standby using RMAN and duplicate the database:

rman TARGET sys/oracle@PROD AUXILIARY sys/oracle@PROD_STBY

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
      DORECOVER
      NOFILENAMECHECK;

This will copy the datafiles and perform media recovery automatically.

---

🔹 10. Start Redo Apply on Standby

-- On standby
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

For real-time apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

---

🔹 11. (Optional) Use Data Guard Broker

Create a broker configuration:

dgmgrl sys/oracle@PROD

DGMGRL> CREATE CONFIGURATION 'DGConfig' AS PRIMARY DATABASE IS 'PROD' CONNECT IDENTIFIER IS 'PROD';
DGMGRL> ADD DATABASE 'PROD_STBY' AS CONNECT IDENTIFIER IS 'PROD_STBY' MAINTAINED AS PHYSICAL;
DGMGRL> ENABLE CONFIGURATION;

---

🔹 12. Verify & Monitor

On standby:

SQL> SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;
-- Should show: PHYSICAL STANDBY and MOUNTED

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;

On primary, force log switch:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Check that logs are shipped and applied.

---

✅ Final Notes

• Ensure time is in sync between primary and standby
• If using DNFS, configure accordingly
• Set up monitoring for lag and MRP process
• Consider using Fast-Start Failover (FSFO) for high availability

---

Would you like a bash script version of the steps for semi-automation or a checklist document for team use?