Data Guard

Set Oraclesid=oms
sqlplus /nolog
conn sys/sys as sysdba
17:00:23 SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

Elapsed: 00:00:00.01
17:00:32 SQL> alter system set DG_BROKER_START = true;
(both on primary and standby)

System altered.

Elapsed: 00:00:01.45
17:00:35 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

C:\Documents and Settings\oracle.dba>dgmgrl
DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.

DGMGRL> create configuration 'omssty' as primary database is 'oms' connect identifier is oms;
Configuration "omssty" created with primary database "OMS"

DGMGRL> add database 'testdb' as connect identifier is testdb maintained as physical;
Database "testdb" added

DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration;

Configuration
Name: omssty
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
OMS - Primary database
testdb - Physical standby database

Current status for "omssty":

DGMGRL> REMOVE CONFIGURATION; (For Deleting the configuration)

DGMGRL> SHOW DATABASE VERBOSE OMS;

DGMGRL> SHOW DATABASE VERBOSE testdb;

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