Creation of physical stand by on same host for testing purpose

#Creation of physical stand by on same host for testing purpose.               
#DB version Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
#Red hat Linux 6.3 64 bit                              
#pri database:orcl                                  
#standby:stby                                      
################Pushpak Pattewar####################

-)Primary
1)To create the logs on primary forcefully
Alter database force logging;

2)Configure log archive dest 1 for pri and dest 2 for standby.  
alter system set LOG_ARCHIVE_DEST_1='location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

3)Alter pfile parameter
alter system set FAL_SERVER=STBY;
alter system set FAL_CLIENT=orcl;

alter system set DB_UNIQUE_NAME='orcl' scope=spfile;

alter system set log_archive_config='dg_config=(orcl,stby)';

4)Copy password file
cp orapworcl orapwstby

5)make copy of pfile rename it to initstby.ora and do  changes
stby.__db_cache_size=306184192
stby.__java_pool_size=4194304
stby.__large_pool_size=4194304
stby.__oracle_base='/home/cybage/app/cybage'#ORACLE_BASE set from environment
stby.__pga_aggregate_target=314572800
stby.__sga_target=469762048
stby.__shared_io_pool_size=0
stby.__shared_pool_size=146800640
stby.__streams_pool_size=0
*.audit_file_dest='/home/cybage/app/cybage/admin/stby/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/cybage/app/cybage/oradata/stby/control01.ctl','/home/cybage/app/cybage/flash_recovery_area/stby/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/home/cybage/app/cybage/oradata/orcl/','/home/cybage/app/cybage/oradata/stby'
*.db_domain='cybage.com'
*.db_name='orcl'
*.db_recovery_file_dest='/home/cybage/app/cybage/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='stby'
*.diagnostic_dest='/home/cybage/app/cybage'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'
*.fal_client='stby'
*.fal_server='orcl'
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.200.129)(PORT=1521))'
*.log_archive_config='dg_config=(orcl,stby)'
*.log_archive_dest_1='location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby'
*.log_archive_dest_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/home/cybage/app/cybage/oradata/orcl/','/home/cybage/app/cybage/oradata/stby'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='auto'


6)Put pri db on archivelog mode. Go to mount state and fire.
alter database archivelog;

7)Listner configuration
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /home/cybage/app/cybage/product/11.2.0/dbhome_3)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (ORACLE_HOME = /home/cybage/app/cybage/product/11.2.0/dbhome_3)
      (SID_NAME = stby)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.129)(PORT = 1521))
  )

8)Tnsnames.ora configuration

STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stby)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

9)Start standby in nomount mode
rman target sys/oracle@orcl auxiliary sys/oracle@stby

10)Rman>duplicate target database for standby dorecover from active database ;

11)create stand by logs on PRIMARY

alter database add standby logfile '/home/cybage/app/cybage/oradata/orcl/stdby_redo03.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/orcl/stdby_redo02.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/orcl/stdby_redo01.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/orcl/stdby_redo04.rdo' size 50M;

lOGS ON STANDBY

alter database add standby logfile '/home/cybage/app/cybage/oradata/stby/stdby_redo01.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/stby/stdby_redo02.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/stby/stdby_redo03.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/stby/stdby_redo04.rdo' size 50M;

12)On STANDBY – START MEDIA RECOVERY
sql > Recover managed standby database disconnect;

#########################################################################################################
On Primary
To check queries

1)select NAME,DBID,LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;

2)select thread#, sequence#, archived, applied from v$archived_log where dest_id=2 and sequence# in ( select max(sequence#) from v$archived_log);

3)To check the archive log gap
select
LOG_ARCHIVED_ON_PRIMARY, LOG_APPLIED_ON_STANDBY, LOG_ARCHIVED_ON_PRIMARY-LOG_APPLIED_ON_STANDBY LOG_GAP
from
(select sequence# LOG_ARCHIVED_ON_PRIMARY from v$archived_log where dest_id=1 and archived='YES' and completion_time=(select max(completion_time) from v$archived_log where dest_id=1)) primary,
(select sequence# LOG_APPLIED_ON_STANDBY from v$archived_log where dest_id=2 and applied='YES' and completion_time=(select max(completion_time) from v$archived_log where dest_id=2));

4)select sequence#,applied from v$archived_log order by sequence#;

5)to check error message
select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';