Setting a Standby DB

Setting a standby DB on the same box as that of the primary db.
=============================================

Physical Standby Database on the same machine as of Primary Database
==================================================================
1.Shutdown the Primary database

2.Copy all the datafiles,initPRI.ora and logs to the Standby destination.

3.mv initPRI.ora initSTBY.ora

4.In the initPRI.ora

LOG_ARCHIVE_DEST_1='LOCATION=/bankaus/db/TEST/dump/a'
LOG_ARCHIVE_DEST_2='SERVICE=STBYt'
#the service name should be there in the listener.ora and tnsnames.ora

5.In the initSTBY.ora DB_NAME= # should be primary db name if on the same machine
LOG_ARCHIVE_DEST=/bankaus/db/STBYt/dump/a/ standby_archive_dest=/bankaus/db/STBYt/dump/a/ #it will be in this path PRIMARY will keep the arch log file
db_file_name_convert="/u2/pri","/u2/stby" #this will convert the path for the with respect to the standby
log_file_name_convert="/u2/pri","/u2/stby"#if these convert parameters are not there, since the DB_NAME is same as the PRI, while applying archive logs the system will try to write to the Primary's Datafiles
LOCK_NAME_SPACE= #any thing other than primary SID, without this parameter it is not possible to mount Standby db as DB_NAME conflict will happen with the Primary. Also if still problem persists delete the file lk / LK in the $ORALCE_HOME/dbs/

6.Create the standby control file $$export ORACLE_SID=PRI $sqlplus "/ as sysdba" $sql>alter database create standby controlfile as '/dir1/dir2' Copy the control files to the stanby path witch is mentioned in the initSTBY.ora

7. Restart the listener service

8.Start the Primary db
$export ORACLE_SID=PRI
$sqlplus "/ as sysdba"
$sql>startup

9.Start the Standby in mounted state
$export ORACLE_SID=STBYt
$sqlplus "/ as sysdba"
$sql>startup nomount
$sql>alter database mount standby database
$sql>recover standby database #this applies the archive log created.
or
$sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION # will happen in backgroud
$sql>ALTER DATABASE STANDBY DATABASE CANCEL
To keep the database in recovery mode $recover managed standby database; #this will keep on applying the logs as soon as it is generated in the standby archive path.

10.Switch a log file in the Primary
$sql>alter system archive log current;
$sql>archive log list

11.Check in the Standby path whether the file is created or not
$sql>select max(sequence#) from v$log_history;

Notes
======
We can 've 10 LOG_ARCHIVE_DEST_n destinations where n ranges from 1-10. The view V$ARCHIVE_DEST contains details of all the destinations.When an error occurs while writing to a particular location, the status of that LOG_ARCHIVE_DEST_n will be ERROR, once the problem is rectified we need to ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBYt'; and now the status will be ACTIVE.

Reference: http://dba.shilpatech.com/stdby.html