Primary Database DB UNIQUE NAME: Genoa1 Js

Purpose

This note explains the procedure of creating a Physical Standby database using 11g RMAN DUPLICATE FROM ACTIVE DATABASE feature which is now available in 11g Release 1 onwards. This enables us to create a physical standby database without having to take a backup of the primary database as a prerequisite step.

Environment

Primary Database DB_UNIQUE_NAME: genoa1_js

Standby Database DB_UNIQUE_NAME: genoa1_fc

ORACLE_SID:genoa1

Primary hostname: oatu037

Standby hostname: drou037

Oracle software version: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit

Enable Force Logging on the Primary database

SQL> alter database force logging;

Database altered.

Create the password file on the Standby host

Note - ensure that the same password is used as the one used while creating the password file on the Primary host

genoa1:/u01/oracle/product/11.1.0/db_1/dbs> orapwd file=orapwgenoa1 password=G3nesisoat

Update network configuration files

Add the following entries to the tnsnames.ora file on both Primary as well as Standby hosts. The listener has been configured to run from the ASM home on the server, so we would need to update the tnsnames.ora file in both the database as well as ASM Oracle homes on both machines.

genoa1_js =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oatu037)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = genoa1_js)

)

genoa1_fc =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = drou037)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = genoa1_fc)

)

On the Standby host, add a static entry in the listener.ora file and reload or restart the listener.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oatu037.bankwest.com)(PORT = 1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = genoa1_fc)

(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)

(SID_NAME = genoa1)

)

The database initially had a service_names value of 'genoa1'. We have defined the network configuration files using distinct service names to match the db_unique_name values on both Primary as well as Standby locations. We now need to change the service_names parameter and then we can test connectivity from the using the TNS aliases that we set up in the earlier step.

SQL> alter system set service_names='genoa1_js' scope=both;

System altered.

SQL> show parameter service

NAME TYPE VALUE

------

service_names string genoa1_js

genoa1:/u01/oracle/product/11.1.0/db_1/dbs> sqlplus system/G3nesisoat@genoa1_js

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Mar 15 15:43:43 2010

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning and Real Application Testing options

SQL> select host_name from v$instance;

HOST_NAME

------

oatu037

Create a "scratch" init.ora file on the Standby host with just a single parameter

genoa1:/u01/oracle/product/11.1.0/db_1/dbs> vi initgenoa1.ora

"initgenoa1.ora" [New file]

DB_NAME=genoa1

Create the required directories on the Standby host

Check the value of the parameter audit_file_dest on the Primary database. Ensure that this directory structure also exists on the Standby host. If the directory structure differs in any way on the Primary and Standby server, we will have to ensure that the RMAN rcv file to create the Standby database is amended to reflect this as well.

On Primary database:

SQL> show parameter audit_file

NAME TYPE VALUE

------

audit_file_dest string /u01/oracle/admin/genoa1/adump

On Standby host:

genoa1:/u01/oracle/admin> mkdir genoa1

genoa1:/u01/oracle/admin> cd genoa1

genoa1:/u01/oracle/admin/genoa1> mkdir adump

Create the active_standby.rcv file

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

DORECOVER

SPFILE

SET DB_UNIQUE_NAME='genoa1_fc'

SET LOG_ARCHIVE_DEST_2='service=genoa1_js LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'

Set STANDBY_FILE_MANAGEMENT='AUTO'

SET FAL_SERVER='genoa1_js'

SET FAL_CLIENT='genoa1_fc'

SET CONTROL_FILES='+DATA/genoa1/controlfile/control01.ctl','+DATA/genoa1/controlfile/control02.ctl'

NOFILENAMECHECK

;

Start the Standby database instance in NOMOUNT state

genoa1:/u01/oracle/product/11.1.0/db_1/dbs> sqlas

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Mar 15 15:40:21 2010

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size 2152328 bytes

Variable Size 159385720 bytes

Database Buffers 50331648 bytes

Redo Buffers 5287936 bytes

From the Primary host, run the following RMAN command to create the Standby Database

rman target / auxiliary sys/G3nesisoat@genoa1_fc cmdfile=active_standby.rcv log=cre_actv_stndby.log

Starting Duplicate Db at 16-03-2010 09:51:38

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=97 device type=DISK

contents of Memory Script:

{

backup as copy reuse

file '/u01/oracle/product/11.1.0/db_1/dbs/orapwgenoa1' auxiliary format

'/u01/oracle/product/11.1.0/db_1/dbs/orapwgenoa1' file

'/u01/oracle/product/11.1.0/db_1/dbs/spfilegenoa1.ora' auxiliary format

'/u01/oracle/product/11.1.0/db_1/dbs/spfilegenoa1.ora' ;

sql clone "alter system set spfile= ''/u01/oracle/product/11.1.0/db_1/dbs/spfilegenoa1.ora''";

}

executing Memory Script

Starting backup at 16-03-2010 09:51:40

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=529 device type=DISK

Finished backup at 16-03-2010 09:51:43

sql statement: alter system set spfile= ''/u01/oracle/product/11.1.0/db_1/dbs/spfilegenoa1.ora''

contents of Memory Script:

{

sql clone "alter system set db_unique_name =

''genoa1_fc'' comment=

'''' scope=spfile";

sql clone "alter system set LOG_ARCHIVE_DEST_2 =

''service=genoa1_js LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'' comment=

'''' scope=spfile";

sql clone "alter system set FAL_SERVER =

''genoa1_js'' comment=

'''' scope=spfile";

sql clone "alter system set FAL_CLIENT =

''genoa1_fc'' comment=

'''' scope=spfile";

sql clone "alter system set CONTROL_FILES =

''+DATA/genoa1/controlfile/control01.ctl'', ''+DATA/genoa1/controlfile/control02.ctl'' comment=

'''' scope=spfile";

shutdown clone immediate;

startup clone nomount ;

}

executing Memory Script

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

sql statement: alter system set LOG_ARCHIVE_DEST_2 = ''service=genoa1_js LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'' comment= '''' scope=sp

file

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

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

sql statement: alter system set CONTROL_FILES = ''+DATA/genoa1/controlfile/control01.ctl'', ''+DATA/genoa1/controlfile/control02.ctl'' comment= '''' scope=s

pfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 5344731136 bytes

Fixed Size 2153536 bytes

Variable Size 3154117568 bytes

Database Buffers 2147483648 bytes

Redo Buffers 40976384 bytes

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '+DATA/genoa1/controlfile/control01.ctl';

restore clone controlfile to '+DATA/genoa1/controlfile/control02.ctl' from

'+DATA/genoa1/controlfile/control01.ctl';

sql clone 'alter database mount standby database';

}

executing Memory Script

Starting backup at 16-03-2010 09:51:49

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/oracle/product/11.1.0/db_1/dbs/snapcf_genoa1.f tag=TAG20100316T095149 RECID=23 STAMP=713785910

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

Finished backup at 16-03-2010 09:51:52

Starting restore at 16-03-2010 09:51:52

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=537 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 16-03-2010 09:51:54

sql statement: alter database mount standby database

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

set newname for tempfile 1 to

"+data";

switch clone tempfile all;

set newname for datafile 1 to

"+data";

set newname for datafile 2 to

"+data";

set newname for datafile 3 to

"+data";

set newname for datafile 4 to

"+data";

backup as copy reuse

datafile 1 auxiliary format

"+data" datafile

2 auxiliary format

"+data" datafile

3 auxiliary format

"+data" datafile

4 auxiliary format

"+data" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

Starting backup at 16-03-2010 09:51:59

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/genoa1/datafile/system.260.713696473

output file name=+DATA/genoa1_fc/datafile/system.256.713785921 tag=TAG20100316T095159 RECID=0 STAMP=0

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/genoa1/datafile/sysaux.258.713694281

output file name=+DATA/genoa1_fc/datafile/sysaux.259.713785935 tag=TAG20100316T095159 RECID=0 STAMP=0

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/genoa1/datafile/undotbs1.261.713696297

output file name=+DATA/genoa1_fc/datafile/undotbs1.260.713785951 tag=TAG20100316T095159 RECID=0 STAMP=0

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/genoa1/datafile/users.259.713694329

output file name=+DATA/genoa1_fc/datafile/users.261.713785957 tag=TAG20100316T095159 RECID=0 STAMP=0

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

Finished backup at 16-03-2010 09:52:38

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like "+FRA/genoa1/archivelog/2010_03_16/thread_1_seq_43.317.713785959" auxiliary format

"+FRA" ;

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

Starting backup at 16-03-2010 09:52:39

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=43 RECID=39 STAMP=713785959

output file name=+FRA/genoa1_fc/archivelog/2010_03_16/thread_1_seq_43.265.713785959 RECID=0 STAMP=0

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

Finished backup at 16-03-2010 09:52:40

searching for all files in the recovery area

List of Files Unknown to the Database

======

File Name: +fra/GENOA1_FC/ARCHIVELOG/2010_03_16/thread_1_seq_42.260.713783777

File Name: +fra/GENOA1_FC/ARCHIVELOG/2010_03_16/thread_1_seq_43.265.713785959

File Name: +fra/GENOA1_FC/ONLINELOG/group_4.256.713749183

File Name: +fra/GENOA1_FC/ONLINELOG/group_5.257.713749183

File Name: +fra/GENOA1_FC/ONLINELOG/group_6.258.713749185

File Name: +fra/GENOA1_FC/ONLINELOG/group_7.259.713749185

File Name: +fra/GENOA1_FC/ONLINELOG/group_4.261.713783787

File Name: +fra/GENOA1_FC/ONLINELOG/group_5.262.713783789

File Name: +fra/GENOA1_FC/ONLINELOG/group_6.263.713783789

File Name: +fra/GENOA1_FC/ONLINELOG/group_7.264.713783791

cataloging files...

cataloging done

List of Cataloged Files

======

File Name: +fra/GENOA1_FC/ARCHIVELOG/2010_03_16/thread_1_seq_42.260.713783777

File Name: +fra/GENOA1_FC/ARCHIVELOG/2010_03_16/thread_1_seq_43.265.713785959

List of Files Which Where Not Cataloged

======

File Name: +fra/GENOA1_FC/ONLINELOG/group_4.256.713749183