Standby Database using 11g RMAN Duplicate from active database feature.

            This is Unbelievable and useful feature of 11 G for me. because you does not required any Backup so you need not to worry about space crunch. I decided to try out it at R&D environment at my home. Mostly  I works on Unix platform so  I had made a mistake while creating password file so you should take care of this things. I have created the standby on same Host windows XP 64 bit.

steps I followed...

Primary Database  : DB_UNIQUE_NAME: PROD

Standby Database : DB_UNIQUE_NAME: PRODSTBY

Enable Force Logging on the Primary database : It is a best practice to place the primary database in force logging mode so that all operation are captured in the redo stream.

 SQL> alter database force logging;

Database altered

create Instance for standby database.

C:\Documents and Settings\Administrator>oradim -new -sid prodstby

Instance created.

Copy password file for standby, in our scenario location is same so just copy at same location and rename it.

copy PWDprod.ora to same location and rename copied file as PWDprodstby.ora.

Add a static entry in the listener.ora file  and reload or restart the listener.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PROD)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (SID_NAME = PROD)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = PRODSTBY)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (SID_NAME = PRODSTBY)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-tq8837fh)(PORT = 1591))

  )

add following entries to tnsnames.ora and check connectivity using tnsping.

PROD =

  (DESCRIPTION =                      

    (ADDRESS = (PROTOCOL = TCP)(HOST =oracle-tq8837fh)(PORT = 1591))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID =PROD)

    )

  )

PRODSTBY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =oracle-tq8837fh)(PORT = 1591))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = PRODSTBY)

    )

  )

Created a init.ora file on the Standby host with just a single parameter 

DB_NAME=PRODSTBY

Created required directories for Standby database. 

Started standby instance in nomount mode.

C:\Documents and Settings\Administrator>set oracle_sid=prodstby

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 9 08:51:18 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved

Enter user-name: sys@prodstby as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1288949760 bytes

Fixed Size                  1376520 bytes

Variable Size             335548152 bytes

Database Buffers          947912704 bytes

Redo Buffers                4112384 bytes

SQL>

Our production database is in open and standby database is in nomount state. now connect to RMAN and fire below command

C:\Documents and Settings\Administrator>rman target sys/sys@PROD auxiliary sys/sys@PRODSTBY

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jun 9 09:17:04 2013

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

connected to target database: PROD (DBID=227918170)

connected to auxiliary database: PRODSTBY (not mounted)

RMAN> DUPLICATE TARGET DATABASE  FOR STANDBY

2> FROM ACTIVE DATABASE  DORECOVER

3> SPFILE

4> SET DB_UNIQUE_NAME='prodstby'

5> SET STANDBY_FILE_MANAGEMENT='AUTO'

6> SET FAL_SERVER='prod'

7> SET FAL_CLIENT='prodstby'

8> SET LOG_ARCHIVE_DEST_1='LOCATION=F:\ORADATA\PRODSTBY\archive\'

9> SET CONTROL_FILES='F:\ORADATA\PRODSTBY\control\controlfile1.ctl','F:\ORADATA\PRODSTBY\control\controlfile2.ctl'

10> SET DB_FILE_NAME_CONVERT='F:\ORADATA\PROD\datafile','F:\ORADATA\PRODSTBY\datafile'

11> SET LOG_FILE_NAME_CONVERT='F:\ORADATA\PROD\log','F:\ORADATA\PRODSTBY\log';

Starting Duplicate Db at 09-JUN-13

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  'C:\app\Administrator\product\11.2.0\dbhome_1\DATABASE\PWDprod.ORA' auxiliary format

 'C:\app\Administrator\product\11.2.0\dbhome_1\DATABASE\PWDprodstby.ORA'   targetfile

 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEPROD.ORA' auxiliary format

 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEPRODSTBY.ORA'   ;

   sql clone "alter system set spfile= ''C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEPRODSTBY.ORA''";

}

executing Memory Script

Starting backup at 09-JUN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=41 device type=DISK

Finished backup at 09-JUN-13

sql statement: alter system set spfile= ''C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEPRODSTBY.ORA''

contents of Memory Script:

{

   sql clone "alter system set  db_unique_name =

 ''prodstby'' comment=

 '''' scope=spfile";

   sql clone "alter system set  STANDBY_FILE_MANAGEMENT =

 ''AUTO'' comment=

 '''' scope=spfile";

   sql clone "alter system set  FAL_SERVER =

 ''prod'' comment=

 '''' scope=spfile";

   sql clone "alter system set  FAL_CLIENT =

 ''prodstby'' comment=

 '''' scope=spfile";

   sql clone "alter system set  LOG_ARCHIVE_DEST_1 =

 ''LOCATION=F:\ORADATA\PRODSTBY\archive\'' comment=

 '''' scope=spfile";

   sql clone "alter system set  CONTROL_FILES =

 ''F:\ORADATA\PRODSTBY\control\controlfile1.ctl'', ''F:\ORADATA\PRODSTBY\control\controlfile2.ctl'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_file_name_convert =

 ''F:\ORADATA\PROD\datafile'', ''F:\ORADATA\PRODSTBY\datafile'' comment=

 '''' scope=spfile";

   sql clone "alter system set  LOG_FILE_NAME_CONVERT =

 ''F:\ORADATA\PROD\log'', ''F:\ORADATA\PRODSTBY\log'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  db_unique_name =  ''prodstby'' comment= '''' scope=spfile

sql statement: alter system set  STANDBY_FILE_MANAGEMENT =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  FAL_SERVER =  ''prod'' comment= '''' scope=spfile

sql statement: alter system set  FAL_CLIENT =  ''prodstby'' comment= '''' scope=spfile

sql statement: alter system set  LOG_ARCHIVE_DEST_1 =  ''LOCATION=F:\ORADATA\PRODSTBY\archive\'' comment= '''' scope=spfile

sql statement: alter system set  CONTROL_FILES =  ''F:\ORADATA\PRODSTBY\control\controlfile1.ctl'', ''F:\ORADATA\PRODSTBY\control\controlfil

e2.ctl'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''F:\ORADATA\PROD\datafile'', ''F:\ORADATA\PRODSTBY\datafile'' comment= '''' scope=

spfile

sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ''F:\ORADATA\PROD\log'', ''F:\ORADATA\PRODSTBY\log'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1288949760 bytes

Fixed Size                     1376520 bytes

Variable Size                335548152 bytes

Database Buffers             947912704 bytes

Redo Buffers                   4112384 bytes

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  'F:\ORADATA\PRODSTBY\CONTROL\CONTROLFILE1.CTL';

   restore clone controlfile to  'F:\ORADATA\PRODSTBY\CONTROL\CONTROLFILE2.CTL' from

 'F:\ORADATA\PRODSTBY\CONTROL\CONTROLFILE1.CTL';

}

executing Memory Script

Starting backup at 09-JUN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFPROD.ORA tag=TAG20130609T091732 RECID=8 STAMP=817636655

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 09-JUN-13

Starting restore at 09-JUN-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=16 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 09-JUN-13

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

 "F:\ORADATA\PRODSTBY\DATAFILE\TEMP01.DBF";

   switch clone tempfile all;

   set newname for datafile  1 to

 "F:\ORADATA\PRODSTBY\DATAFILE\SYSTEM01.DBF";

   set newname for datafile  2 to

 "F:\ORADATA\PRODSTBY\DATAFILE\SYSAUX01.DBF";

   set newname for datafile  3 to

 "F:\ORADATA\PRODSTBY\DATAFILE\UNDOTBS01.DBF";

   set newname for datafile  4 to

 "F:\ORADATA\PRODSTBY\DATAFILE\USERS01.DBF";

   backup as copy reuse

   datafile  1 auxiliary format

 "F:\ORADATA\PRODSTBY\DATAFILE\SYSTEM01.DBF"   datafile

 2 auxiliary format

 "F:\ORADATA\PRODSTBY\DATAFILE\SYSAUX01.DBF"   datafile

 3 auxiliary format

 "F:\ORADATA\PRODSTBY\DATAFILE\UNDOTBS01.DBF"   datafile

 4 auxiliary format

 "F:\ORADATA\PRODSTBY\DATAFILE\USERS01.DBF"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to F:\ORADATA\PRODSTBY\DATAFILE\TEMP01.DBF in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 09-JUN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=F:\ORADATA\PROD\DATAFILE\SYSTEM01.DBF

output file name=F:\ORADATA\PRODSTBY\DATAFILE\SYSTEM01.DBF tag=TAG20130609T091756

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=F:\ORADATA\PROD\DATAFILE\SYSAUX01.DBF

output file name=F:\ORADATA\PRODSTBY\DATAFILE\SYSAUX01.DBF tag=TAG20130609T091756

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:55

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=F:\ORADATA\PROD\DATAFILE\USERS01.DBF

output file name=F:\ORADATA\PRODSTBY\DATAFILE\USERS01.DBF tag=TAG20130609T091756

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=F:\ORADATA\PROD\DATAFILE\UNDOTBS01.DBF

output file name=F:\ORADATA\PRODSTBY\DATAFILE\UNDOTBS01.DBF tag=TAG20130609T091756

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

Finished backup at 09-JUN-13

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "F:\ORADATA\PROD\ARCHIVE\ARC0000000092_0816079834.0001" auxiliary format

 "F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000092_0816079834.0001"   archivelog like

 "F:\ORADATA\PROD\ARCHIVE\ARC0000000093_0816079834.0001" auxiliary format

 "F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000093_0816079834.0001"   ;

   catalog clone archivelog  "F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000092_0816079834.0001";

   catalog clone archivelog  "F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000093_0816079834.0001";

   switch clone datafile all;

}

executing Memory Script

Starting backup at 09-JUN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=92 RECID=86 STAMP=817636675

output file name=F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000092_0816079834.0001 RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=93 RECID=87 STAMP=817637407

output file name=F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000093_0816079834.0001 RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03

Finished backup at 09-JUN-13

cataloged archived log

archived log file name=F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000092_0816079834.0001 RECID=1 STAMP=817637431

cataloged archived log

archived log file name=F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000093_0816079834.0001 RECID=2 STAMP=817637431

datafile 1 switched to datafile copy

input datafile copy RECID=8 STAMP=817637432 file name=F:\ORADATA\PRODSTBY\DATAFILE\SYSTEM01.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=9 STAMP=817637432 file name=F:\ORADATA\PRODSTBY\DATAFILE\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=10 STAMP=817637433 file name=F:\ORADATA\PRODSTBY\DATAFILE\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=11 STAMP=817637433 file name=F:\ORADATA\PRODSTBY\DATAFILE\USERS01.DBF

contents of Memory Script:

{

   set until scn  535142;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 09-JUN-13

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 92 is already on disk as file F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000092_0816079834.0001

archived log for thread 1 with sequence 93 is already on disk as file F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000093_0816079834.0001

archived log file name=F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000092_0816079834.0001 thread=1 sequence=92

archived log file name=F:\ORADATA\PRODSTBY\ARCHIVE\ARC0000000093_0816079834.0001 thread=1 sequence=93

media recovery complete, elapsed time: 00:00:04

Finished recover at 09-JUN-13

Finished Duplicate Db at 09-JUN-13

RMAN>

                

Set required parameter that`s all