The below are 100% practical steps I followed. Just implement them and enjoy “standby database” :-))
Note: The operating system i used is windows
——————————————————————————–
Assumptions:
A. Oracle version used: 9.x
B. Both the primary and the standby database are in the same machine.
C. The primary database name will be db1 and the secondary database name will be db2
D. ORACLE_HOME=D:\oracle\ora92
E. The drive going to be used (here D) has atleast 1.2 GB free space
Steps: 
——————————————————————————–
PRIMARY DATABASE CONFIGURATION
——————————————————————————–
1. Create an initialization parameter file i.e. initdb1.ora with the following entries and place it ORACLE_HOME\database:
##########################################################
*.compatible=’9.2.0.0.0′
*.background_dump_dest=’D:\oracle\ora92\admin\db1\bdump’
*.core_dump_dest=’D:\oracle\ora92\admin\db1\cdump’
*.user_dump_dest=’D:\oracle\ora92\admin\db1\udump’
*.control_files=’d:\oracle\oradata\db1\control01.ctl’,’d:\oracle\oradata\db1\control02.ctl’
*.db_domain=”
*.DB_NAME=’db1′
*.instance_name=’db1′
*.service_names=’db1′
*.db_block_size=8192
*.db_cache_size=33554432
*.db_file_multiblock_read_count=16
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)’
*.java_pool_size=33554432
*.large_pool_size=8388608
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=200
*.query_rewrite_enabled=’FALSE’
*.sga_max_size=157286400
*.shared_pool_size=50331648
*.sort_area_size=524288
*.timed_statistics=TRUE
*.undo_management=’AUTO’
*.undo_retention=10800
*.undo_tablespace=’UNDOTBS1′
##########################################################
2. Create the folders- bdump, cdump, udump, pfile (under D:\oracle\ora92\admin\db1) and the folder for the control files (d:\oracle\oradata\db1) as mentioned in the initdb1.ora file

3. In the command prompt:
oradim -new -sid db1
set ORACLE_SID=db1
sqlplus /nolog
conn / as sysdba
startup nomount pfile=’D:\oracle\ora92\database\initdb1.ora’

4. Create a database with the name db1. This will be the primary database. In the SQL Plus prompt:

CREATE DATABASE db1
CONTROLFILE REUSE
LOGFILE
GROUP 1 ‘d:\oracle\oradata\db1\LOG01.LOG’ SIZE 5M REUSE,
GROUP 2 ‘d:\oracle\oradata\db1\LOG02.LOG’ SIZE 5M REUSE
DATAFILE ‘d:\oracle\oradata\db1\SYSTEM01.DBF’ SIZE 400M REUSE
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE ‘d:\oracle\oradata\db1\TEMP01.DBF’ SIZE 50M REUSE
UNDO TABLESPACE UNDOTBS1
DATAFILE ‘d:\oracle\oradata\db1\UNDO01.DBF’ SIZE 50M REUSE

5. In the SQL Plus prompt:

ALTER DATABASE FORCE LOGGING;

6. Run catalog.sql and cataproc.sql

7. CONN SYSTEM/MANAGER and run pupbld.sql

8. Execute the following:

CONN / as SYSDBA
SHUTDOWN IMMEDIATE

9. In the listener.ora file make the following entry:

(SID_DESC =
(GLOBAL_DBNAME = db1)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = db1)
)

10. In the tnsnames.ora file make the following entry (change the host name appropriately):

db1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.43.151)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = db1)
)
)

11. In the initdb1.ora file set

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.log_archive_dest_1=’LOCATION=D:\oracle\oradata\db1 MANDATORY’

12. In the command promt run: ORAPWD FILE=D:\oracle\ora92\database\pwddb1.ora PASSWORD=DB1 ENTRIES=5

13. In the SQL Plus prompt:

CONN / AS SYSDBA
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
CREATE SPFILE FROM PFILE;
ALTER DATABASE OPEN;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘D:\oracle\oradata\db1\STBY_DB1.CTL’;
##########################################################
############Configuring the Standby Database:#############
##########################################################

1. Shutdown the primary database:

SHUTDOWN IMMEDIATE

2. Create the folders- bdump, cdump, udump, pfile (under D:\oracle\ora92\admin\db2) and the folder for the control files (d:\oracle\oradata\db2) as mentioned.

3. Create an initialization parameter file i.e. initdb2.ora with the following entries and place it ORACLE_HOME\database:

##########################################################
*.compatible=’9.2.0.0.0′
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=”
*.db_file_multiblock_read_count=16
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db2XDB)’
*.java_pool_size=33554432
*.large_pool_size=8388608
*.log_archive_dest_1=”
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=200
*.query_rewrite_enabled=’FALSE’
*.sga_max_size=157286400
*.shared_pool_size=50331648
*.sort_area_size=524288
*.timed_statistics=TRUE
*.undo_management=’AUTO’
*.undo_retention=10800
*.undo_tablespace=’UNDOTBS1′
*.REMOTE_LOGIN_PASSWORDFILE=’EXCLUSIVE’
*.background_dump_dest=’D:\oracle\ora92\admin\db2\bdump’
*.core_dump_dest=’D:\oracle\ora92\admin\db2\cdump’
*.user_dump_dest=’D:\oracle\ora92\admin\db2\udump’
*.DB_FILE_NAME_CONVERT=’D:\oracle\oradata\db1\’,’D:\oracle\oradata\db2\’
*.DB_NAME=’db1′
*.instance_name=’db2′
*.service_names=’db2′
*.FAL_CLIENT=’db2′
*.FAL_SERVER=’db1′
*.LOCK_NAME_SPACE=’db2′
*.LOG_FILE_NAME_CONVERT=’D:\oracle\oradata\db1\’,’D:\oracle\oradata\db2\’
*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db2′
*.log_archive_dest=’D:\oracle\oradata\db2′
*.control_files=’D:\oracle\oradata\db2\STBY_DB1.CTL’
##########################################################
4. Copy all datafiles and controlfiles of db1 as shown below to the location D:\oracle\oradata\db2.should be there:
D:\oracle\oradata\db1\LOG02.LOG
D:\oracle\oradata\db1\LOG01.LOG
D:\oracle\oradata\db1\STBY_DB1.CTL
D:\oracle\oradata\db1\SYSTEM01.DBF
D:\oracle\oradata\db1\TEMP01.DBF
D:\oracle\oradata\db1\UNDO01.DBF

5. In the command prompt:

oradim -new -sid db2
set ORACLE_SID=db2
orapwd FILE=D:\oracle\ora92\database\pwddb2.ora PASSWORD=db2 ENTRIES=5
sqlplus /nolog
conn / as sysdba
startup nomount pfile=’D:\oracle\ora92\database\INITdb2.ORA’;
ALTER DATABASE MOUNT STANDBY DATABASE;
CREATE SPFILE FROM PFILE;

6. Startup the primary database and then change the following parameter in the spfile:

conn / as sysdba
startup OPEN
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=db2 MANDATORY REOPEN=60′ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST=2 SCOPE=SPFILE;

7. In the listener.ora file add the following entry and reload the listener:

(SID_DESC =
(GLOBAL_DBNAME = db2)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = db2)
)

8. In the tnsnames.ora file add the following entry (change the host name appropriately):

db2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.43.151)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = db2)
)
)

9. In the command prompt, shutdown and Startup the primary database:

SHUTDOWN IMMEDIATE;
STARTUP OPEN;
CREATE PFILE FROM SPFILE;

10. In the command prompt opened for standby database (step-5):

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11. You can check the status of the standby database by:

select process,status,sequence#,thread#,block#,blocks from v$managed_standby;

#########Verifying Standby Database is working:###########

1. Create an user in the primary database and create a table in it:
create user test identified by test;
grant connect, resource to test;
create table test.temp
(name varchar2(10),
value number(3));
insert into test.temp values(‘a’,100);
insert into test.temp values(‘b’,200);
insert into test.temp values(‘c’,300);
commit;
alter system archive log current;

2. Cancel the recovery mode of the standby database and open it in read only mode:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
SELECT * FROM TEST.TEMP;

If you are able to see the records inserted in the temp table under test schema in the primary database in this secondary database your standby is working fine. 
-=========================
USEFULL QUERIES
——————————————————————————–
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; –IN PRIMARY DB

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; –IN STANDBY DB

select status, error from v$archive_dest where dest_id=2; –BOTH

SELECT process, SEQUENCE#, block#, thread#,status FROM V$managed_standby order by SEQUENCE#;

######Switchover of primary and secondary databases#######

db1 will be standby
db2 will be primary

Before going through the following steps make a backup copy of the following files of both db1 and db2:

1. Initialization parameter file.
2. Control file.
——————————————————————————–

STEPS:

Change the old primary to secondary

——————————————————————————–

1. Open the primary database (db1).

2. Open the secondary database (db2) in read only mode.

3. In primary execute:

Select max (sequence#) from v$archived_log;

4. In secondary execute:

Select max (al.sequence#) “Last Seq Recieved”, max(lh.sequence#) “Last Seq Applied”
from v$archived_log al, v$log_history lh;

The results of the queries fired in step-3 and 4 should be identical

5. In initialization parameter file of db1:

Comment out the existing control_files parameter as following:

#*.control_files=’d:\oracle\oradata\db1\control01.ctl’,’d:\oracle\oradata\db1\control02.ctl’

Add the following parameters:

*.FAL_CLIENT=’db1′
*.FAL_SERVER=’db2′
*.control_files=’D:\oracle\oradata\db1\STBY_DB1.CTL’
*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db1\dest_1′

6. Change the primary database (db1) to secondary as below:

alter database commit to switchover to standby with session shutdown;

shutdown immediate;

7. Startup the old primary database (db1) as secondary:

startup nomount pfile=’D:\oracle\ora92\database\initdb1.ora’

alter database mount standby database;

8. Start log apply process:

alter database recover managed standby database disconnect from session;

9. Re-create the spfile:

create spfile from pfile;

10. Reload the listener using the lsnrctl utility.

Change the old secondary to primary
——————————————————————————–

1. Execute the following statement in the old secondary database (db2):

alter database commit to switchover to primary;

If asked for recovery, then recover it using:

recover managed standby database;

If recovered then again execute:

alter database commit to switchover to primary;

2. Shutdown the old secondary database (db2):

shutdown immediate;

3. Make the following changes to the initdb2.ora file:

comment out the following lines as:

#*.FAL_CLIENT=’db2′
#*.FAL_SERVER=’db1′

#*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db2′
#*.log_archive_dest=’D:\oracle\oradata\db2′
4. Add the following lines:

*.log_archive_dest_1=’LOCATION=D:\oracle\oradata\db2 MANDATORY’
*.log_archive_dest_2=’SERVICE=db1 MANDATORY REOPEN=60′
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_min_succeed_dest=2

5. Startup the new primary database (db2):

startup open pfile=D:\oracle\ora92\database\INITdb2.ORA;

6. Reload the listener using the lsnrctl utility.
Now you can verify the role of the new primary(db2) and secondary(db1) in exactly the same way
you did it while testing earlier by making some changes in the “test” table.
The new secondary (db1) may ask recovery.

 

##########################################################
##########################################################

######Switchback of primary and secondary databases#######

##########################################################
##########################################################

 
——————————————————————————–

db2 will be standby
db1 will be primary
STEPS:

Change the database (db2) from primary to secondary

 

——————————————————————————–

1. Open the primary database (db2).

2. Open the secondary database (db1) in read only mode.

3. In primary execute:

Select max (sequence#) from v$archived_log;

4. In secondary execute:

Select max (al.sequence#) “Last Seq Recieved”, max(lh.sequence#) “Last Seq Applied”
from v$archived_log al, v$log_history lh;

The results of the queries fired in step-3 and 4 should be identical

5. In initialization parameter file of db2 uncomment the following lines which were commented:

#*.FAL_CLIENT=’db2′
#*.FAL_SERVER=’db1′
#*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db2′
#*.log_archive_dest=’D:\oracle\oradata\db2′

and remove the following parameters:

*.log_archive_dest_1=’LOCATION=D:\oracle\oradata\db2 MANDATORY’
*.log_archive_dest_2=’SERVICE=db1 MANDATORY REOPEN=60′
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_min_succeed_dest=2

6. Change the primary database (db2) to secondary as below:

alter database commit to switchover to standby with session shutdown;

shutdown immediate;

7. Startup the database (db2) as secondary:

startup nomount pfile=’D:\oracle\ora92\database\initdb2.ora’

alter database mount standby database;

8. Start log apply process:

alter database recover managed standby database disconnect from session;

9. Re-create the spfile:

create spfile from pfile;

 

Change the database (db1) from secondary to primary

 

——————————————————————————–

1. In initialization parameter file of db1:

The following lines should be there:

*.control_files=’D:\oracle\oradata\db1\STBY_DB1.CTL’
*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db1\dest_1′

and remove/comment the following lines:

*.FAL_CLIENT=’db1′
*.FAL_SERVER=’db2′

2. Change the secondary database (db1) to primary as below:

alter database commit to switchover to primary;

If asked for recovery, then recover it using:

recover managed standby database;

If recovered then again execute:

alter database commit to switchover to primary;

3. Shutdown the database (db1):

shutdown immediate;

4. Startup the primary database (db1):

startup open pfile=’D:\oracle\ora92\database\initdb1.ora’

5. Re-create the spfile:

create spfile from pfile;

6. Verify the roles of the primary and standby database by making some change in the

 

##########################################################
##########################################################

############Failover to secondary databases###############

##########################################################
##########################################################

Failover is performed when the primary is completely crashed
and there is no option other then activating the standby as
primary.

 
——————————————————————————–

1. Comment/remove the following lines from the initialization file of db2 i.e. initdb2.ora

*.FAL_CLIENT=’db2′
*.FAL_SERVER=’db1′

2. Execute the following:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

SHUTDOWN IMMEDIATE;

3. Startup and open the database with the updated initialization file:

startup open pfile=’D:\oracle\ora92\database\initdb2.ora’

4. Recreate the spfile

CREATE SPFILE FROM PFILE;

NOTE: ONCE THE DATABASE IS FAILEDOVER WITH THE “ALTER….FINISH” STATEMENT
IT CANNOT BE ROLLEDBACK TO PREVIOUS STAGE OF STANDBY BECAUSE THE LOG SEQUENCE
NUMBERS WILL BE RESET.