Standby Database using 11g DUPLICATE FROM ACTIVE DATABASE

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

 

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

 

 

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

 

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

 

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

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

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

RMAN-07529: Reason: catalog is not supported for this file type

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

RMAN-07529: Reason: catalog is not supported for this file type

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

RMAN-07529: Reason: catalog is not supported for this file type

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

RMAN-07529: Reason: catalog is not supported for this file type

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

RMAN-07529: Reason: catalog is not supported for this file type

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

RMAN-07529: Reason: catalog is not supported for this file type

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

RMAN-07529: Reason: catalog is not supported for this file type

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

RMAN-07529: Reason: catalog is not supported for this file type

 

datafile 1 switched to datafile copy

input datafile copy RECID=23 STAMP=713785961 file name=+DATA/genoa1_fc/datafile/system.256.713785921

datafile 2 switched to datafile copy

input datafile copy RECID=24 STAMP=713785961 file name=+DATA/genoa1_fc/datafile/sysaux.259.713785935

datafile 3 switched to datafile copy

input datafile copy RECID=25 STAMP=713785961 file name=+DATA/genoa1_fc/datafile/undotbs1.260.713785951

datafile 4 switched to datafile copy

input datafile copy RECID=26 STAMP=713785961 file name=+DATA/genoa1_fc/datafile/users.261.713785957

 

contents of Memory Script:

{

set until scn  3817576;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 16-03-2010 09:52:42

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 43 is already on disk as file +FRA/genoa1_fc/archivelog/2010_03_16/thread_1_seq_43.265.713785959

archived log file name=+FRA/genoa1_fc/archivelog/2010_03_16/thread_1_seq_43.265.713785959 thread=1 sequence=43

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

Finished recover at 16-03-2010 09:52:43

Finished Duplicate Db at 16-03-2010 09:52:46

 

Recovery Manager complete.

 

 

Change the init.ora parameters related to redo transport and redo apply

 

On primary :

 

SQL> alter system set fal_server=’genoa1_fc’ scope=both;

 

System altered.

 

SQL> alter system set fal_client=’genoa1_js’ scope=both;

 

System altered.

 

SQL> alter system set standby_file_management=AUTO scope=both;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=genoa1_js’;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=genoa1_fc LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=genoa1_fc’;

 

 

Shutdown the Standby database, add the Standby log files and then start real time recovery

 

 

SQL> shutdown immediate;

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

 

SQL> startup

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

Database mounted.

Database opened.

 

 

SQL> alter database add standby logfile group 4 size 500m;

 

Database altered.

 

SQL> alter database add standby logfile group 5 size 500m;

 

Database altered.

 

SQL> alter database add standby logfile group 6 size 500m;

 

Database altered.

 

SQL>  alter database add standby logfile group 7 size 500m;

 

Database altered.

 

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

 

SQL> !ps -ef |grep mrp

 

oracle 471268      1   0 10:51:16      –  0:02 ora_mrp0_genoa1

oracle 475150 270568   1 11:02:47  pts/0  0:00 grep mrp

 

 

Change the Protection Level of the Standby Database to MAXIMIZE AVAILABILITY

 

On the Primary database:

 

SQL> shutdown immediate;

 

Database dismounted.

ORACLE instance shut down.

 

SQL> startup mount

 

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

Database mounted.

 

SQL> alter database add standby logfile group 4 size 500m;

 

Database altered.

 

SQL> alter database add standby logfile group 5 size 500m;

 

Database altered.

 

SQL> alter database add standby logfile group 6 size 500m;

 

Database altered.

 

SQL>  alter database add standby logfile group 7 size 500m;

 

Database altered.

 

SQL> alter database set standby database to maximize availability;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

 

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Anúncios

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em DATAGUARD, TUTORIAL. Bookmark o link permanente.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s