Creating a Physical Standby database on 10gR2
Primary host : erpdevdbs01
Standby host : erpdevasi01
ORACLE_SID = PRIM
DB_UNIQUE_NAME = PRIM, STAN

ON PRIMARY NODE

Edit Initialization parameter file on Primary host (erpdevdbs01) to like this

*.db_name='PRIM'
*.db_unique_name='PRIM'
*.dg_broker_start=True   - Data Guard Broker
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradevdata/arch/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIM'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=STAN VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.remote_login_passwordfile='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.FAL_SERVER=STAN
*.FAL_CLIENT=PRIM
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert=('/oradevdata/PRIM','/oraProdBin/oracle/oradevdata','/oradevdata/arch','/oraPr
odBin/or acle/arch')
*.log_file_name_convert=('/oradevdata/PRIM','/oraProdBin/oracle/oradevdata','/oradevdata/arch','/oraP
rodBin/or acle/arch')

Enable Archivelog

sqlplus / AS sysdba
shut immediate
startup mount
ALTER DATABASE archivelog;
ALTER DATABASE OPEN;
archive log list

Edit tnsnames.ora on Primary host (erpdevdbs01)

STAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdevasi01.mbcgroup.local)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = STAN)
)
)
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdevdbs01.mbcgroup.local)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
)
)

Stop and Start the listener

lsnrctl stop LISTENER_PRIM
lsnrctl start LISTENER_PRIM

Enable Force Logging

ALTER DATABASE force logging;

Create Standby Redologs on Primary database

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/oradevdata/PRIM/redo04.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/oradevdata/PRIM/redo05.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/oradevdata/PRIM/redo06.log' SIZE 50M;

Create standby controlfile on Primary database

shut immediate
startup mount
ALTER DATABASE CREATE standby controlfile AS '/oradevdata/PRIM/stdbycntrl.ctl';
shut immediate

Shutdown and copy the database files,standby controlfile,redol logs and standby logfiles to Standby Server

scp -rp filename  standby IP:standby database files location

Create a password file using orapwd and transfer it to the standby server

orawpd file=$ORACLE_HOME/dbs/orapwPRIM password=xxxx entries=5 force=y

Transfer the file using scp

scp -rp orapwPRIM standby IP:standby location

Rename the password file on standby database

mv orapwdPRIM orapwSTAN

Check the connectivity from Primary to Standby
From Primary do

sqlplus sys/password@STAN AS sysdba
Connected .

ON STANDBY NODE
Edit the Standby Initialization parameter file to look like this

*.db_domain=''
*.db_name='PRIM'
*.db_unique_name='STAN'
*.dg_broker_start=True   - Data Guard Broker
*.local_listener='LISTENER_STAN'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oraProdBin/oracle/arch/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STAN'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=PRIM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.remote_login_passwordfile='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.FAL_SERVER=PRIM
*.FAL_CLIENT=STAN
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert=('/oradevdata/PRIM','/oraProdBin/oracle/oradevdata','/oradevdata/arch','/oraPr
odBin/or acle/arch')
*.log_file_name_convert=('/oradevdata/PRIM','/oraProdBin/oracle/oradevdata','/oradevdata/arch','/oraProdBin/or acle/arch')

Edit tnsnames.ora on Standby host (erpdevasi01)

STAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdevasi01.mbcgroup.local)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = STAN)
)
)
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdevdbs01.mbcgroup.local)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
)
)

Stop and Start the listener

lsnrctl stopLISTENER_STAN
lsnrctl start LISTENER_STAN

Check the connectivity from Standby database to the primary

sqlplus sys/password@PRIM AS sysdba
Connected .

Start the standby database

[oradev@erpdevasi01 dbs]$ sqlplus "/as sysdba"
startup mount

On the standby database, issue the following command to start Redo Apply:

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log and also verify whether the logs have been applied.

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

CHECK ALERT LOGS FOR BOTH INSTANCES FOR ANY ERRORS or LOG SHIPPING MESSAGES
For e.g
On Primary 

ARC0: Standby redo logfile selected for thread 1 sequence 3 for destination LOG_ARCHIVE_DEST_2
ARC0: Standby redo logfile selected for thread 1 sequence 4 for destination LOG_ARCHIVE_DEST_2

On Standby

RFS[1]: Successfully opened standby log 4: '/oraProdBin/oracle/oradevdata/redo04.log'
RFS[1]: Successfully opened standby log 4: '/oraProdBin/oracle/oradevdata/redo04.log'

P.N : To enable Real Time Apply please edit the following parameter in Primary Init file
Set up log_archive_dest_2 on the primary with LGWR ASYNC or SYNC

log_archive_dest_2='SERVICE=STAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN'

shutdown and startup the primary or if done dynamically switch a log file

On the standby cancel out of the current managed recovery and place it in recovery with Real time apply

ALTER DATABASE RECOVER  MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER  MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

To Enable Data Guard Broker follow the below steps
P.N : To Enable Data Guard Broker please add the following value in pfile of PRIMARY and STANDBY database

dg_broker_start=True

Create spfile from pfile

CREATE pfile FROM spfile;
shut immediate
startup

On Primary instance open a terminal and enter the following command

oradev@erpdevdbs01 dbs]$ dgmgrl
DGMGRL FOR Linux: Version 10.2.0.4.0 - Production
Copyright (c) 2000, 2005, Oracle. ALL rights reserved.
Welcome TO DGMGRL, TYPE "help" FOR information.
DGMGRL;
DGMGRL; CONNECT sys/oracle@PRIM
Connected.

Create Borker  Configuration

DGMGRL; CREATE configuration dgbroker AS PRIMARY DATABASE IS PRIM CONNECT identifier IS PRIM;
Configuration "dgbroker" created WITH PRIMARY DATABASE "prim"

Add Standby Database to the above configuration

DGMGRL;ADD DATABASE STAN AS CONNECT identifier IS STAN maintained AS physical;
DATABASE "stan" added

Check the status of Data Guard Broker

DGMGRL; SHOW configuration
Configuration
Name:                dgbroker
Enabled:             NO
Protection Mode:     MaxPerformance
Fast-START Failover : DISABLED
DATABASES:
prim - PRIMARY DATABASE
stan - Physical standby DATABASE
CURRENT STATUS FOR "dgbroker":
DISABLED
DGMGRL;

Enable Configuration & Re-check the status

DGMRL; ENABLE CONFIGURATION
DGMGRL; SHOW CONFIGURATION
Configuration
Name:                dgbroker
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-START Failover : DISABLED
DATABASES:
prim - PRIMARY DATABASE
stan - Physical standby DATABASE
CURRENT STATUS FOR "dgbroker":
SUCCESS
DGMGRL;
Tagged with →  
Share →
0 comments
Skip to toolbar