Physical Standby Database - Step by Step Configuration

Server : HP 386 2 x Xeon 3.4 (6GB RAM, HDD 73x3)
Operating System : Microsoft Windows Server 2003 x86
Database : Oracle 10gR2, 11gR1
IP Address :192.168.1.29, 192.168.1.30
Host Name :dbserver, physicaldb
Database Unique Name : orcl, physicaldb
Service Name :primarydb, physicaldb

Note: following steps required for physical standby database

1. Pre-Installation Configuration
2. Installing Oracle Software and Database
3. Prepare Production Database as Primary Database

3.1. Enable and Verify Archive Log mode
3.2. Enable force longing
3.3. Create STANDBY redo log
3.4. Create STANDBY control file for standby database
3.5. Create PFILE from SPFILE and set primary and standby database initialization parameters file
3.6. Backup Primary Database, copy on standby nodes
3.7. Startup primary database in mount mode using PFILE and create binary/SPFILE file from PFILE

4. Configure listener and tnsnames files on primary and standby database nodes
5. Prepare standby database node

5.1. Copy primary database backup at standby nodes at same directories
5.2. Copy and change standby control file which created in step 3.4 (standby.ctl to control01.ctl etc)
5.3. Copy and change standby initialization parameter file witch create in step 3.5
5.4. Create a window based service for standby instance, using the ORADIM
5.5. Set environment variables (ORACLE_HOME, ORACLE_SID) to point the standby database
5.6. Startup standby database in nomount mode and create SPFILE from PFILE
5.7. Startup Standby database in mount mode and perform recovery

6. Verify the physical standby database is performing properly

Follow Following Steps for deploying Oracle Physical Standby Database Microsoft Windows

1. Pre-Installation Configuration

Check operating system install and working properly, confirm ip and hostnames on all nodes.

2. Install Oracle Software and Database

Install Oracle Database software on primary node
Install only Oracle software on Standby Nods.

3. Prepare production database for Primary database

3.1 If production/primary database not in archive mode, first enable archive log mode.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;

SQL> ARCHIVE LOG LIST;


SQL> ALTER DATABASE FLASHBACK ON;

3.2 Enable force longing

SQL> ALTER DATABASE FORCE LOGGING;

Verify archive log mode and force logging

SQL> SELECT LOG_MODE, FLASHBACK_ON, FORCE_LOGGING FROM V$DATABASE;

3.3 Create Standby Redo Log

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('D:\oracle\product\10.2.0\oradata\ORCL\REDO11.LOG') SIZE 51200K;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('D:\oracle\product\10.2.0\oradata\ORCL\REDO12.LOG') SIZE 51200K;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('D:\oracle\product\10.2.0\oradata\ORCL\REDO13.LOG') SIZE 51200K;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('D:\oracle\product\10.2.0\oradata\ORCL\REDO14.LOG') SIZE 51200K;

Check Primary Database redo log

SQL> SELECT * FROM V$LOGFILE;

Check Standby Database redo log

SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;

3.4 Create STANDBY control file for standby database

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\physical.ctl';

3.5 Create PFILE from SPFILE and Set Primary/Standby Database initialization Parameters

SQL> CREATE PFILE='D:\Oracle\INITorcl.ora' FROM SPFILE;

Edit the PFILE parameter for primary configuration, here shown colored:

db_domain=''
db_name='orcl'
instance_name='orcl'

db_unique_name='orcl'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,physicaldb)'
LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\product\10.2.0\oradata\ORCL\ARCHIVE VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_2='SERVICE=physicaldb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=physicaldb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=10
fal_server=physicaldb
fal_client=orcl
standby_file_management=auto

Copy primary PFILE and change database initialization parameter file for standby database

db_domain=''
db_name='orcl'
instance_name='orcl'
db_unique_name='logicaldb'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,logicaldb)'
LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\product\10.2.0\oradata\ORCL\ARCHIVE VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=physicaldb'
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
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
#LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=10
fal_server=orcl
fal_client=phsicaldb
standby_file_management=auto

3.6 Use RMAN for backup or copy database folders (ORADATA, ADMIN, Recovery_Flash_Area), standby
Parameters and control files at standby nodes.

3.7 Startup in mount mode using PFILE and Create binary/SPFILE file from PFILE

SQL> STARTUP NOMOUNT PFILE='D:\ORACLE\initorcl.ora';
SQL> CREATE SPFILE FROM PFILE='D:\ORACLE\initorcl.ora';

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

4. Configure Listener and services on all nodes

On Production/Primary node Listener and service automatically created for primary database during installing, just add/create standby service. Create Listener and services (orcl, primarydb,physicaldb) at standby node that will be used by log transport services. For configuration use Oracle Net Manger or directly copy/paste and edit primary (listener.ora and tnsnames.ora) files. After configuration check services.

Note : Its recemended use Oracle supplied utility NETCA, NETMGR

After configuring listnners and services try to connect standby database from primary node, and from standby node to primary database. Following connections should work now, first check listener from primary and standby nodes.

C:\> TNSPING orcl
C:\> TNSPING physicaldb

From Primary host

SQL:>sys/oracle@orcl as sysdba –> This will connect to primary database
SQL:>sys/oracle@physicaldb as sysdba –> It will connect to standby database from primary host

From Standby host

sqlplus sys/oracle@orcl as sysdba –> This will connect to primary database from standby host
sqlplus sys/oracle@physicaldb as sysdba –> This will connect to standby database


5. Prepare standby nods Step by Step

5.1 Copy primary database backup at standby nodes at same directories use OS commands.
5.2 Copy and change standby control file (change standby.ctl rename to control01.ctl,2,3 etc)
5.3 Copy changed standby initialization parameter file on standby node witch create in step 3.5
5.4 Create a window based service for standby instance, using the ORADIM

C:\>ORADIM –NEW –SID orcl –INTPWD oracle –STARTMODE manual

5.5 Create password file.

C:\>ORAPWD FILE=PWDstandby.ora PASSWPRD=SECRET ENTRIES=20 FORCE=Y

5.6 Set environment variables (ORACLE_HOME, ORACLE_SID) to point the standby database in window’s registry.

5.7 Startup standby database in nomount mode and create SPFILE from PFILE

SQL> STARTUP NOMOUNT PFILE='D:\Oracle\initphysicaldb.ora' ;
SQL> CREATE SPFILE FORM PFILE='D:\Oracle\initphysicaldb.ora';
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

5.8 Startup standby database in mount mode and perform recovery.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

View primary and standby database alert logs.

6. Verify the Physical Standby database

Check archived redo log on Standby
First check database parameter’s values

SQL> SHOW PARAMETERS DB_UNIQUE_NAME;
SQL> ARCHIVE LOG LIST;
SQL> SELECT NAME,DB_UNIQUE_NAME,LOG_MODE,FORCE_LOGGING FROM V$DATABASE;

Now, Check archived redo log

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Switch log files on Primary

SQL> ALTER SYSTEM SWITCH LOGFILE;

Check Archived Statistics

SQL> ARCHIVE LOG LIST;

Check archived redo log on Standby

SQL> SELECT SEQUENCE#,FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;