Translate this Page

 

How To Create Physical Standby Database – Oracle10g
Primary :

  • Host : primsvr
  • ORACLE_SID : orcl
  • service names : primdb

Standby :

  • Host : stbysvr
  • ORACLE_SID : orcl
  • service names : stbydb

Steps to configure Oracle Data Guard :

1. Install Oracle Software in primsvr and stbysvr

2. edit init parameter in Primary and Standby Database

#########	# Oracle Data Guard Parameter Primary DB	#########
*.db_name='orcl'
*.db_unique_name='primdb'
#########	# Data Guard Broker Parameter	#########
*.dg_broker_start=TRUE
*.dg_broker_config_file1='/u01/product/db_1/dbs/dr1PRIMDB.dat'
*.dg_broker_config_file2='/u01/product/db_1/dbs/dr2PRIMDB.dat'
#### Common Configuration
*.log_archive_config='DG_CONFIG=(PRIMDB,STBYDB)'
*.log_archive_dest_1='LOCATION=/u01/orcl/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb'
*.log_archive_dest_2='service=stbydb','ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0
 max_connections=1 reopen=300 db_unique_name="stbydb" register net_timeout=180
 valid_for=online_logfile,primary_role)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=2
*.log_archive_format='arch_%t_%s_%r.arc'
*.log_file_name_convert='/u01/PRIMDB','/u01/STBYDB','/u02/PRIMDB','/u02/STBYDB'
#### Specific to Standby Role
*.standby_file_management=AUTO
*.standby_archive_dest='/u01/orcl/arch'
*.fal_client=primdb
*.fal_server=stbydb

#########	# Oracle Data Guard Parameter Standby DB	#########
*.db_name='orcl'
*.db_unique_name='stbydb'
#########	# Data Guard Broker Parameter	#########
*.dg_broker_start=TRUE
*.dg_broker_config_file1='/u01/product/db_1/dbs/dr1STBYDB.dat'
*.dg_broker_config_file2='/u01/product/db_1/dbs/dr2STBYDB.dat'
#### Common Configuration
*.log_archive_config='DG_CONFIG=(primdb,stbydb)'
*.log_archive_dest_1='LOCATION=/u01/orcl/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb'
*.log_archive_dest_2='service=primdb','ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0
max_connections=1 reopen=300 db_unique_name="primdb" register net_timeout=180  valid_for=(online_logfile,primary_role)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=2
*.log_archive_format='arch_%t_%s_%r.arc'
*.log_file_name_convert='/u01/PRIMDB','/u01/STBYDB','/u02/PRIMDB','/u02/STBYDB'
# Specific to Standby Role
*.standby_file_management=AUTO
*.standby_archive_dest='/u01/orcl/arch'
*.fal_client=stbydb
*.fal_server=primdb
    3. Assure Primary DB already activate the Archivelog.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

4. Edit listener.ora ob both server. Set to a concatenation of db_unique_name_DGMGRL.db_domain :

SID_LIST_LISTENER = 
                (SID_LIST =    
                       (SID_DESC =
                       (GLOBAL_DBNAME = db_unique_name_DGMGRL)
                       (ORACLE_HOME = $ORACLE_HOME_PATH)
                       (SID_NAME = db_name)
                       )
                )
LISTENER = (DESCRIPTION_LIST =
                     (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
                     )
                 )

NOTE: GLOBAL_DBNAME should used format “db_unique_name_DGMGRL”, if not you will found error :  ”ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”

5. Edit tnsnames.ora, assure Primary DB and Standby DB can connect each other.

primdb =
       (DESCRIPTION =
             (ADDRESS_LIST =
             (ADDRESS = (PROTOCOL = TCP)(HOST = primsvr)(PORT = 1521)))
             (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))
        )

stbydb =
       (DESCRIPTION =
             (ADDRESS_LIST =
             (ADDRESS = (PROTOCOL = TCP)(HOST = stbydb)(PORT = 1521)))
             (CONNECT_DATA =	(SERVER = DEDICATED) (SERVICE_NAME = orcl))
             )

6. Create Standby Logfile on Primary DB

-- STANDBY LOGFILE
alter database add standby logfile GROUP 6 '/u01/orcl/stbyredo01.log'  SIZE 100M;
alter database add standby logfile GROUP 7 '/u01/orcl/stbyredo02.log'  SIZE 100M;
alter database add standby logfile GROUP 8 '/u01/orcl/stbyredo03.log'  SIZE 100M;
alter database add standby logfile GROUP 9 '/u01/orcl/stbyredo04.log'  SIZE 100M;
alter database add standby logfile GROUP 10 '/u01/orcl/stbyredo05.log' SIZE 100M;

7. Backup Primary DB, copy database files & redolog files into Standby DB.

8. Create Standby Control File on Primary DB

SQL>STARTUP MOUNT;
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stby.ctl';
SQL>ALTER DATABASE OPEN;

9. FTP/Copy Standby Control File (‘/tmp/stby.ctl’) and password file ($ORACLE_HOME/dbs/orapworcl) into Standby Server.    Put it into directory and rename it as same as parameter “control_files” in Init Parameter.

10. If you are still using PFILE (initSID.ora), don’t forget to convert it into SPFILE(spSID.ora) before startup the Database.

SQL> CREATE spfile FROM pfile;

11. Startup The Standby DB

SQL>STARTUP MOUNT
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

12. Verify the archivelog sequence in Primary DB and Standby DB

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

 

 

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

   
© 2012 LevICORP's webnote Suffusion theme by Sayontan Sinha