Creating a physical standby Database in Oracle10g

STEPS for creating 10g dataguard

prerequisite : 9i dataguard setup knowledge


step1 :

Prepare initSID.ora file for primary and standby databases as follow.

** STANDBY setup parameters are given in BOLD

part A)

**** Production database primary file ****

prod.__db_cache_size=125829120
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=79691776
prod.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0\admin\prod\adump'
*.background_dump_dest='C:\oracle\product\10.2.0\admin\prod\bdump'
*.compatible='10.2.0.3.0'
*.control_files='Q:\oradata\prod\control01.ctl','Q:\oradata\prod\control02.ctl','Q:\oradata\prod\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0\admin\prod\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648

*.instance_name='prod'
*.job_queue_processes=10

*.db_unique_name='prod'
*.fal_client='prod'
*.fal_server='prod_stdby'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=Q:\oradata\prod\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
*.log_archive_dest_2='SERVICE=prod_stdby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby LGWR ASYNC REOPEN=10'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t%s%r.arc'
*.open_cursors=300
*.pga_aggregate_target=72351744
*.processes=150
*.service_names='prod'
*.sga_target=218103808
*.standby_archive_dest='q:\arch'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0\admin\prod\udump'



part B)

**** standby database primary file ****

*.compatible='10.2.0.3.0'
*.control_files='C:\oradata\prod\stdby.ctl'
*.instance_name='stdby'
*.db_name='prod'
*.db_unique_name='stdby'
*.fal_client='prod_stdby'
*.fal_server='prod'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=C:\oradata\prod\stdby\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
*.log_archive_dest_2='SERVICE=prod VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod LGWR ASYNC REOPEN=10'
*.db_file_name_convert='Q:\','C:\'
*.log_file_name_convert='Q:\','C:\'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r'
*.service_names='stdby'
*.shared_pool_size=104857600
*.standby_file_management='AUTO'


STEP 2:

part A)

shutdown primary database cleanly and copy datafiles to standby location.

part B) create standby controlfile by giving following command at production database at mount stage
alter databse create standby controlfiel as 'location\filename' ;

move this generated file to standby controlfile location as pointed by standby initSID.ora file


step 3)
create oracle service and password file with the same password at standby database location

step 4)
prepare the TNSNAMES.ORA and LISTENER.ora at both production and standy locations.





To check archive log gap


SELECT * FROM (
SELECT sequence#, archived, applied,
TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI') AS completed
FROM sys.v$archived_log
ORDER BY sequence# DESC)
WHERE ROWNUM <= 10