StandBy database

Primary init.ora file
NOTE:- LOG_ARCHIVE_DEST_STATE_2=ENABLE parameter should be Defer and Enable only at the time of creation of standby Controle file.
***************************************
*.db_cache_size=324288000
*.java_pool_size=4194304
*.large_pool_size=4194304
*.shared_pool_size=192937984
*.streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0\admin\OMS\adump'
*.background_dump_dest='D:\oracle\product\10.2.0\admin\OMS\bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\product\10.2.0\oradata\OMS\control01.ctl','D:\oracle\product\10.2.0\oradata\OMS\control02.ctl','D:\oracle\product\10.2.0\oradata\OMS\control03.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0\admin\OMS\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='OMS'
*.db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OMSXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=634003200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0\admin\OMS\udump'
DB_UNIQUE_NAME=OMS
LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(OMS,TESTDB)'
LOG_ARCHIVE_DEST_1= 'LOCATION=D:\oracle\product\10.2.0\flash_recovery_area\OMS
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=OMS'
LOG_ARCHIVE_DEST_2='SERVICE=TESTDB
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=TESTDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_ARCHIVE_DEST='E:\PROD\flash_recovery_area\TESTDB'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
DB_FILE_NAME_CONVERT= ('D:\oracle\product\10.2.0\oradata\OMS\' , 'E:\PROD\ORADATA\TESTDB')
LOG_FILE_NAME_CONVERT= ('D:\oracle\product\10.2.0\oradata\OMS\' , 'E:\PROD\ORADATA\TESTDB')
STANDBY_FILE_MANAGEMENT=auto
FAL_SERVER=TESTDB
FAL_CLIENT=OMS


set oracle_sid=oms
sql
conn sys/sys as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;


ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('D:\oracle\product\10.2.0\oradata\OMS\stylog4.ora')SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('D:\oracle\product\10.2.0\oradata\OMS\stylog5.ora')SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('D:\oracle\product\10.2.0\oradata\OMS\stylog6.ora')SIZE 500M;

SHUTDOWN IMMEDIATE;

$copy D:\oracle\product\10.2.0\oradata\OMS\* E:\PROD\oradata\TESTDB

STARTUP MOUNT pfile=d:\ as mention above' ;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\product\stdby.ctl';
ALTER DATABASE OPEN;


*******on standby******

Standby init.ora file
***************************************
*.db_cache_size=324288000
*.java_pool_size=4194304
*.large_pool_size=4194304
*.shared_pool_size=192937984
*.streams_pool_size=0
*.audit_file_dest='E:\PROD\admin\testdb\adump\'
*.background_dump_dest='E:\PROD\admin\testdb\bdump\'
*.compatible='10.2.0.1.0'
*.control_files='E:\PROD\oradata\testdb\control01.ctl','E:\PROD\oradata\testdb\control02.ctl','E:\PROD\oradata\testdb\control03.ctl'
*.core_dump_dest='E:\PROD\admin\testdb\cdump\'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='OMS'
*.db_recovery_file_dest='E:\PROD\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OMSXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=634003200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\PROD\admin\testdb\udump\'
DB_UNIQUE_NAME=TESTDB
LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(TESTDB,OMS)'
LOG_ARCHIVE_DEST_1= 'LOCATION=E:\PROD\flash_recovery_area\testdb
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=TESTDB'
LOG_ARCHIVE_DEST_2='SERVICE=OMS
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=OMS'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_ARCHIVE_DEST='E:\PROD\flash_recovery_area\TESTDB'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
DB_FILE_NAME_CONVERT= ('D:\oracle\product\10.2.0\oradata\OMS\','E:\PROD\oradata\TESTDB\')
LOG_FILE_NAME_CONVERT= ('D:\oracle\product\10.2.0\oradata\OMS\','E:\PROD\oradata\TESTDB\')
STANDBY_FILE_MANAGEMENT=auto
FAL_SERVER=OMS
FAL_CLIENT=TESTDB
****************************
Rename controle file
'D:\oracle\product\stdby.ctl' to location E:\PROD\oradata\TESTDB\

STARTUP MOUNT pfile='d:\as mention above';

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

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


Test Real time apply :

Primary : create a table 'test' and insert a record.
INSERT INTO test VALUES ( 101, 'testing');
COMMIT;
Do not switch a log
On the standby :
SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

Notice the block# for the RFS and MRP0 increasing
Cancel out of real time apply and open it in read only mode

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
SELECT * FROM.test;
You will see the committed record.
Place the standby back in managed recover mode
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENTLOGFILE DISCONNECT;


NOTE : Be Care full While Creating the Password File At Stand by location should be same Pass as Primary