Create a standby database on the same host, using RMAN with a backup from an active database .

PRIMARY DATABASE   ORCL ON DEVU026 BOX

STANDBY DATABASE   STBY ON DEVU026 BOX.

In PRIMARY   orcl database >

export ORACLE_SID=orcl

export ORACLE_HOME=/u01/oracle/product/11.2

sql > Alter database force logging;

Create password file for the primary and standby database.

cd $ORACLE_HOME/dbs

orapwd file=orapworcl entries=10 password=temp force=y

cp orapworcl orapwstby   Do not create separate password file for standby   use cp .

Try a test connection 

$ sqlplus sys/temp@orcl as sysdba   should connect to primary.

ON PRIMARY   ORCL database.

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’;

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)’;

STANDBY DATABASE ON SAME BOX.

Copy initorcl.ora and edit to reflect all stby entries.

vi initstby.ora

stby.__db_cache_size=155189248
stby.__java_pool_size=4194304
stby.__large_pool_size=4194304
stby.__oracle_base=’/u01/ofsad2'#ORACLE_BASE set from environment
stby.__pga_aggregate_target=159383552
stby.__sga_target=423624704
stby.__shared_io_pool_size=0
stby.__shared_pool_size=243269632
stby.__streams_pool_size=4194304
*.audit_file_dest=’/u01/ofsad2/admin/stby/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0'
*.control_files=’/u02/oradata/stby/control01.ctl’,'/u02/oradata/stby/control02.ctl’
*.db_block_size=8192
*.db_file_name_convert=’/u02/oradata/orcl/’,'/u02/oradata/stby/’
*.db_name=’orcl’
*.db_recovery_file_dest=’/u02/oradata/flash_recovery_area’
*.db_recovery_file_dest_size=7340032000
*.db_unique_name=’stby’
*.diagnostic_dest=’/u01/ofsad2'
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=stbyXDB)’
*.fal_client=’STBY’
*.fal_server=’ORCL’
*.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=’/u02/oradata/orcl/’,'/u02/oradata/stby/’
*.open_cursors=300
*.pga_aggregate_target=157286400
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=423624704
*.standby_file_management=’auto’
*.undo_tablespace=’UNDOTBS1'

. setstby

sql > create spfile from pfile;

Configure Listener for Static service registration

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = apex4)
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = apex4)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = orcl)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = stby)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
)

Configure service name in tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)

STARTUP NOMOUNT STANDBY DATABASE 

sql > startup nomount;

Now run rman command to create standby database 

stby:/u01/oracle/product/11.2/dbs> rman target sys/temp@orcl auxiliary /

Recovery Manager: Release 11.2.0.1.0   Production on Tue Jul 27 11:55:10 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1246075258)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby dorecover from active database ;

Starting Duplicate Db at 27/JUL/10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/27/2010 12:00:53
RMAN-06217: not connected to auxiliary database with a net service name

Therefore connect to auxiliary using connect string 

rman target sys/temp@orcl auxiliary sys/temp@stby

Recovery Manager: Release 11.2.0.1.0   Production on Tue Jul 27 12:02:22 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1246075258)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby dorecover from active database ;

Starting Duplicate Db at 27/JUL/10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/oracle/product/11.2/dbs/orapworcl’ auxiliary format
‘/u01/oracle/product/11.2/dbs/orapwstby’ ;
}
executing Memory Script

Starting backup at 27/JUL/10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
Finished backup at 27/JUL/10

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u02/oradata/stby/control01.ctl’;
restore clone controlfile to ‘/u02/oradata/stby/control02.ctl’ from
‘/u02/oradata/stby/control01.ctl’;
}
executing Memory Script

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/oracle/product/11.2/dbs/snapcf_orcl.f tag=TAG20100727T121951 RECID=4 STAMP=725458793
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27/JUL/10

Starting restore at 27/JUL/10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 27/JUL/10

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
'/u02/oradata/stby/temp01.dbf';
switch clone tempfile all;
'/u02/oradata/stby/fla_tbs1.dbf' ;
and more 
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oradata/stby/temp01.dbf in control file

executing command: SET NEWNAME

And more

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/orcl/sysaux01.dbf
And more .
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27/JUL/10

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like '/u02/oradata/flash_recovery_area/ORCL/archivelog/2010_07_27/o1_mf_1_221_64wqpxq8_.arc' auxiliary format
'/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_%u_.arc' ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=221 RECID=180 STAMP=725458909
output file name=/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 27/JUL/10

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc
cataloging files
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc

datafile 1 switched to datafile copy
And more
input datafile copy RECID=10 STAMP=725458912 file name=/u02/oradata/stby/fla_tbs1.dbf

contents of Memory Script:
{
set until scn 5284605;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27/JUL/10
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 221 is already on disk as file /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc
archived log file name=/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc thread=1 sequence=221
media recovery complete, elapsed time: 00:00:01
Finished recover at 27/JUL/10
Finished Duplicate Db at 27/JUL/10

RMAN>

CREATE STANDBY LOGS 

Run on both instances 

ON PRIMARY

alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo03.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo02.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo01.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo04.rdo’ size 52428800;

ON STANDBY

alter database add standby logfile ‘/u02/oradata/stby/stdby_redo01.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo02.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo03.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo04.rdo’ size 52428800;

On STANDBY   START MEDIA RECOVERY.

sql > Recover managed standby database disconnect;

Tail  f alert logs in standy site to check if all is well

Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_269_65dll3bx_.arc
Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_270_65dll45g_.arc
Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_271_65dll3z5_.arc
Media Recovery Waiting for thread 1 sequence 272 (in transit)

Also confirm with this sql that all scripts are in place

ON PRIMARY -

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

THREAD# SEQUENCE# ARC APPLIED
=
1 274 YES YES
SQL>

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))
/

LOG_ARCHIVED_ON_PRIMARY LOG_APPLIED_ON_STANDBY LOG_GAP

274 274 0