DATA GUARD CONFIGURATION USING DUPLICATE

       I.       Introduction

Data guard is Oracle high availability solution. In this article I will demonstrate how to configure data guard using online duplicate. In order to do the duplicate we have to configure a static listener on standby server (listener.ora file) and the tnsnames.ora file must be configured as well. Look at the figure below.

Figure 1-1

Host: 201.101.1.156      Hostname: sakada11

§      Primary Database: ORACLE_SID=db11g, DB_UNIQUE_NAME=db11g

§     Standby Database: ORACLE_SID=db11g_stby, DB_UNIQUE_NAME=db11g_stby

     II.       Setup PFILE

Pfile contents look like below:

-bash-3.2$ cat initdb11g_stby.ora

*.audit_file_dest='/u01/app/oracle/admin/db11g_stby/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+data/controlfile/control1.ctl','+arch/controlfile/control2.ctl'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_recovery_file_dest='+ARCH'

*.db_recovery_file_dest_size='1024M'

*.db_domain=''

*.db_file_name_convert='+data/db11g/','+data/db11g_stby/'

*.db_name='db11g'

*.db_unique_name='db11g_stby'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db11gXDB)'

*.fal_server='DB11G'

*.log_archive_config='DG_CONFIG=(db11g,db11g_stby)'

*.log_archive_dest_1='LOCATION=+arch valid_for=(all_logfiles,all_roles) db_unique_name=db11g_stby'

*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db11g'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.log_file_name_convert='+arc/db11g/','+arch/db11g_stby/'

*.memory_target=1715470336

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

And then create the relevant directories so that the duplicate process can convert data files successful.

  III.       Setup Listener

Create listener.ora file on the Oracle user. Listener.ora file looks like below:

-bash-3.2$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

#      (GLOBAL_DBNAME = db11g_stby)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1/)

      (SID_NAME = db11g_stby)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Start the listener and verify that the listener services the instance.

-bash-3.2$ lsnrctl status

LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 25-AUG-2015 10:44:52

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=201.101.1.157)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Solaris: Version 11.2.0.1.0 - Production

Start Date                24-AUG-2015 15:10:40

Uptime                    0 days 19 hr. 34 min. 12 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/standby/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=201.101.1.157)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM", status READY, has 1 handler(s) for this service...

Service "db11g_stby" has 1 instance(s).

  Instance "db11g_stby", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Tnsnames.ora file must be set like below:

-bash-3.2$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DB11G =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db11g)

    )

  )

DB11G_STBY =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db11g_stby)

      ( UR = A )

    )

  )

Verify that we can tnsping to all the service name.

  IV.       Duplicate

Startup nomount using Pfile.

SQL> startup nomount pfile='/export/home/oracle/backup/initdb11g_stby.ora';

Connect RMAN on Primary. RMAN command looks like below:

rman TARGET sys/sys@DB11G AUXILIARY sys/sys@DB11G_STBY

-bash-3.2$ rman TARGET sys/sys@DB11G AUXILIARY sys/sys@DB11G_STBY

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 25 11:00:26 2015

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

connected to target database: DB11G (DBID=357031128)

connected to auxiliary database: DB11G (not mounted)

Start duplicate by running the following RMAN script:

RMAN> run{

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

DORECOVER

NOFILENAMECHECK;

}

The logs will be like following:

RMAN> run{

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

DORECOVER

NOFILENAMECHECK;

}2> 3> 4> 5> 6> 7>

Starting Duplicate Db at 25-AUG-2015 11:04:44

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=24 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdb11g' auxiliary format

 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdb11g_stby'   ;

}

executing Memory Script

Starting backup at 25-AUG-2015 11:04:46

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=90 device type=DISK

Finished backup at 25-AUG-2015 11:04:48

contents of Memory Script:

{

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

   restore clone controlfile to  '+ARCH/controlfile/control2.ctl' from

 '+DATA/controlfile/control1.ctl';

}

executing Memory Script

Starting backup at 25-AUG-2015 11:04:48

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_db11g.f tag=TAG20150825T110448 RECID=5 STAMP=888663890

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

Finished backup at 25-AUG-2015 11:04:52

Starting restore at 25-AUG-2015 11:04:52

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 25-AUG-2015 11:04:53

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

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

   set newname for datafile  5 to

 "+data";

   set newname for datafile  6 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"   datafile

 5 auxiliary format

 "+data"   datafile

 6 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

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 25-AUG-2015 11:04:59

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/db11g/datafile/system.278.887361391

output file name=+DATA/db11g_stby/datafile/system.259.888663899 tag=TAG20150825T110459

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/db11g/datafile/sysaux.279.887361393

output file name=+DATA/db11g_stby/datafile/sysaux.256.888663965 tag=TAG20150825T110459

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/db11g/datafile/undotbs1.262.887361393

output file name=+DATA/db11g_stby/datafile/undotbs1.257.888664011 tag=TAG20150825T110459

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=+DATA/db11g/datafile/example.256.887361579

output file name=+DATA/db11g_stby/datafile/example.260.888664019 tag=TAG20150825T110459

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=+DATA/db11g/datafile/users.265.887705307

output file name=+DATA/db11g_stby/datafile/users.263.888664029 tag=TAG20150825T110459

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/db11g/datafile/users.263.887361393

output file name=+DATA/db11g_stby/datafile/users.258.888664033 tag=TAG20150825T110459

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

Finished backup at 25-AUG-2015 11:07:15

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "+ARC/db11g/archivelog/2015_08_25/thread_1_seq_164.326.888663911" auxiliary format

 "+ARCH"   archivelog like

 "+ARC/db11g/archivelog/2015_08_25/thread_1_seq_165.319.888664037" auxiliary format

 "+ARCH"   ;

   catalog clone recovery area;

   switch clone datafile all;

}

executing Memory Script

Starting backup at 25-AUG-2015 11:07:16

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=164 RECID=233 STAMP=888663912

output file name=+ARCH/db11g_stby/archivelog/2015_08_25/thread_1_seq_164.353.888664037 RECID=0 STAMP=0

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

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=165 RECID=234 STAMP=888664036

output file name=+ARCH/db11g_stby/archivelog/2015_08_25/thread_1_seq_165.354.888664037 RECID=0 STAMP=0

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

Finished backup at 25-AUG-2015 11:07:19

searching for all files in the recovery area

List of Files Unknown to the Database

=====================================

File Name: +arch/db11g_stby/ARCHIVELOG/2015_08_25/thread_1_seq_164.353.888664037

File Name: +arch/db11g_stby/ARCHIVELOG/2015_08_25/thread_1_seq_165.354.888664037

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +arch/db11g_stby/ARCHIVELOG/2015_08_25/thread_1_seq_164.353.888664037

File Name: +arch/db11g_stby/ARCHIVELOG/2015_08_25/thread_1_seq_165.354.888664037

List of files in Recovery Area not managed by the database

==========================================================

File Name: +ARCH/controlfile/control2.ctl

  RMAN-07526: Reason: File is not an Oracle Managed File

number of files not managed by recovery area is 1, totaling 9.56MB

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=888664038 file name=+DATA/db11g_stby/datafile/system.259.888663899

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=888664038 file name=+DATA/db11g_stby/datafile/sysaux.256.888663965

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=888664038 file name=+DATA/db11g_stby/datafile/undotbs1.257.888664011

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=888664038 file name=+DATA/db11g_stby/datafile/users.258.888664033

datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=888664038 file name=+DATA/db11g_stby/datafile/example.260.888664019

datafile 6 switched to datafile copy

input datafile copy RECID=10 STAMP=888664038 file name=+DATA/db11g_stby/datafile/users.263.888664029

contents of Memory Script:

{

   set until scn  2539587;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 25-AUG-2015 11:07:19

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 164 is already on disk as file +ARCH/db11g_stby/archivelog/2015_08_25/thread_1_seq_164.353.888664037

archived log for thread 1 with sequence 165 is already on disk as file +ARCH/db11g_stby/archivelog/2015_08_25/thread_1_seq_165.354.888664037

archived log file name=+ARCH/db11g_stby/archivelog/2015_08_25/thread_1_seq_164.353.888664037 thread=1 sequence=164

archived log file name=+ARCH/db11g_stby/archivelog/2015_08_25/thread_1_seq_165.354.888664037 thread=1 sequence=165

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

Finished recover at 25-AUG-2015 11:07:22

Finished Duplicate Db at 25-AUG-2015 11:07:41

After the duplication completes successfully start apply log process:

SQL> alter database recover managed standby database disconnect from session;

Create spfile:

SQL> create spfile from pfile='/export/home/oracle/backup/initdb11g_stby.ora';

    V.       Summary

The duplicate technology is faster than using backup and restore on standby server, but we have to configure the network between the two servers properly. Take the listener.ora and tnsnames.ora as consideration.