Thursday, July 12, 2012

How to create a 11gR2 physical standby database using Active Database Duplication and virtual machines on Oracle VM 3.0.3: step 4

Previous steps are here:
step 1
step 2
step 3
So we have a running database on a machine and a simply Oracle database software installation on the second machine. My goal is to create a physical standby database on the second machine using the Active Database Duplication copying the live database over the network.

My dataguard environment will be formed by the following machines:
primary database machine: database unique name DB01prmr on vsi08devpom (hostname)
physical standby machine: database unique name DB01sby1 on vsi10devpom (hostname)
Both on release: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Let's start.
There are some steps to execute in order to prepare the primary database to be part of a data guard configuration.

- Enable Forced Logging
First step, recommended for physical and logical standby database, is to enable forced logging. In this way Oracle logs every changes on the database, even for those tables created with the NOLOGGING option. So on the primary database:
[oracle@vsi08devpom ~]$ sqlplus / as sysdba
SQL> set linesize 180
SQL> SELECT DBID, NAME, CREATED, LOG_MODE, OPEN_MODE, GUARD_STATUS, FORCE_LOGGING, FLASHBACK_ON
  2  FROM V$DATABASE;

      DBID NAME      CREATED   LOG_MODE     OPEN_MODE   GUARD_S FOR FLASHBACK_ON
---------- --------- --------- ------------ -------------------- ------- --- ------------------
2874530197 DB01PRMR  15-JUN-12 NOARCHIVELOG READ WRITE   NONE  NO  NO


SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT DBID, NAME, CREATED, LOG_MODE, OPEN_MODE, GUARD_STATUS, FORCE_LOGGING, FLASHBACK_ON
  2  FROM V$DATABASE;

      DBID NAME      CREATED   LOG_MODE     OPEN_MODE   GUARD_S FOR FLASHBACK_ON
---------- --------- --------- ------------ -------------------- ------- --- ------------------
2874530197 DB01PRMR  15-JUN-12 NOARCHIVELOG READ WRITE   NONE  YES NO
- Configuring STANDBY REDO LOG:
Next step is to create the STANDBY REDO LOGS on the primary database: they are used when the redo logs are shipped from the primary database to the standby and are recommended in the role reversal and required on synchronous transport mode at real time apply; 
If configured on the primary database (as recommended), they are also created (automatically I mean) on the standby when you use the rman command DUPLICATE TARGET DATABASE, as I will do; each standby redo log must be as large as the largest redo log of the primary database and on the standby there should be at least one more redo log group than the primary database;
As you can see I have three groups (and yes I know... formed by a single member... but this is a test environment); every member has the same size and it's 50MB. So I need to add four standby redo log file of the same size.
SQL> Select group# "GROUP", thread# thread, sequence# sequence, 
  2  bytes/1024/1024 MB, members, archived, status, first_change# first_change, first_time
  3  from sys.v_$log order by group#, thread#;

     GROUP     THREAD SEQUENCE  MB    MEMBERS ARC STATUS     FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
  1     1      136  50      1 NO  INACTIVE   3937272 09-JUL-12
  2     1      137  50      1 NO  CURRENT   3969353 10-JUL-12
  3     1      135  50      1 NO  INACTIVE   3927184 09-JUL-12

SQL> alter database add standby logfile '/opt/app/oracle/oradata/DB01prmr/standby_redo04.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/opt/app/oracle/oradata/DB01prmr/standby_redo05.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/opt/app/oracle/oradata/DB01prmr/standby_redo06.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/opt/app/oracle/oradata/DB01prmr/standby_redo07.log' size 50m;

Database altered.
The following is my new configuration: three redo log groups, seven logfile member (four of them are STANDBY type), four standby redo log groups:
SQL> Select group# "GROUP", thread# thread, sequence# sequence,
  2  bytes/1024/1024 MB, members, archived, status, first_change# first_change, first_time
  3  from sys.v_$log order by group#, thread#;

     GROUP     THREAD SEQUENCE  MB    MEMBERS ARC STATUS     FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
  1     1      136  50      1 NO  INACTIVE   3937272 09-JUL-12
  2     1      137  50      1 NO  CURRENT   3969353 10-JUL-12
  3     1      135  50      1 NO  INACTIVE   3927184 09-JUL-12

SQL> set pagesize 999
SQL> col member format a60
SQL> select * from sys.v_$logfile;

    GROUP# STATUS  TYPE    MEMBER       IS_
---------- ------- ------- ------------------------------------------------------------ ---
  3    ONLINE  /opt/app/oracle/oradata/DB01prmr/redo03.log   NO
  2    ONLINE  /opt/app/oracle/oradata/DB01prmr/redo02.log   NO
  1    ONLINE  /opt/app/oracle/oradata/DB01prmr/redo01.log   NO
  4    STANDBY /opt/app/oracle/oradata/DB01prmr/standby_redo04.log  NO
  5    STANDBY /opt/app/oracle/oradata/DB01prmr/standby_redo05.log  NO
  6    STANDBY /opt/app/oracle/oradata/DB01prmr/standby_redo06.log  NO
  7    STANDBY /opt/app/oracle/oradata/DB01prmr/standby_redo07.log  NO

7 rows selected.

SQL> select group#, dbid, thread#, sequence#, status from v$standby_log;

    GROUP# DBID            THREAD# SEQUENCE# STATUS
---------- ---------------------------------------- ---------- ---------- ----------
  4 UNASSIGNED          0  0 UNASSIGNED
  5 UNASSIGNED          0  0 UNASSIGNED
  6 UNASSIGNED          0  0 UNASSIGNED
  7 UNASSIGNED          0  0 UNASSIGNED
- Set Primary Database Initialization Parameters
Now it's time to set some initialization parameters on the primary database. Don't miss this step and set them correctly and with care.
Let's start with LOG_ARCHIVE_CONFIG: for each database in the dataguard environment it specifies with the DG_CONFIG attribute the unique database name list; it's used also to enable and disable sending and receiving of redolog. Sending and receiving redo log are enabled by default; in a physical standby scenario the database name is the same but not the unique database name. On a logical standby scenario the database names are usually different;
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB01prmr,DB01sby1)' scope=both;
System altered.
The LOG_ARCHIVE_DEST_n parameter controls the redo transport services and should include at least one of the following parameters: LOCATION which is a local path name or SERVICE which is an Oracle Net Service name to reference the standby database. I won't use LOCATION parameter, but I will use only the SERVICE one.
The attributes SYNC and ASYNC are used to specify the transport mode; when using SYNC every redo generated have to be received at the destination before the commit is executed, ASYNC is the default so I won't specify it on the alter system command; the attributes AFFIRM and NOAFFIRM are used to let the redo transport destination acknowledges it received the redo data after or before the redo data are written to the standby redo log. When using SYNC transport mode AFFIRM is the default, when using ASYNC, NOAFFIRM is the default; in my case so I will use ASYNC and NOAFFIRM;
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=DB01sby1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB01sby1' scope=both;
System altered.
The LOG_ARCHIVE_DEST_STATE_n parameter is used to specify the destination state. Possible values are: ENABLE and DEFER; I will enable it after have completed the standby setup.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER scope=both;                                
System altered.
The DB_LOST_WRITE_PROTECT parameter checks for data corruption on the primary database during redo transportations and on the standby database during redo apply. Default is NONE, so I willl set it to TYPICAL;
SQL> alter system set DB_LOST_WRITE_PROTECT=TYPICAL scope=both;
System altered.
The DB_FILE_NAME_CONVERT parameter is configured only on a physical standby database and if you have the datafiles in different paths. The same concept applies on
LOG_FILE_NAME_CONVERT parameter: configured only on a physical standby database and if you have the redo logs in different paths. My paths will differ just for the two unique database names.
SQL> alter system set db_file_name_convert='/DB01sby1/','/DB01prmr/' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='/DB01sby1/','/DB01prmr/' scope=spfile;
System altered.
The default value of STANDBY_FILE_MANAGEMENT parameter is MANUAL: it means that when you add a datafile on the primary you have to manually add it on the standby... every standby databases!!! You can use AUTO to create it automatically but consider that some kind of ALTER commands are no more allowed on the standby. DataGuard broker configure it as AUTO: I'll do the same and then test it in my environment.
SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.
The DB_UNIQUE_NAME parameter specifies a unique name for the database; during a reverse roles this parameter doesn't change. In my case DB_UNIQUE_NAME will be DB01prmr for the primary database and DB01sby1 for the standby database; the DB_NAME will be on both machines always DB01prmr
SQL> alter system set DB_UNIQUE_NAME=DB01prmr scope=spfile;
System altered.
FAL_SERVER and FAL_CLIENT specifies the Oracle Net service name where and to whom request missing archived redo log.
SQL> alter system set FAL_SERVER=DB01sby1 scope=both;
System altered.
SQL> alter system set FAL_CLIENT=DB01prmr scope=both;
System altered.
To specify the format of archived redo log use LOG_ARCHIVE_FORMAT parameter.
SQL> alter system set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc' scope=spfile;
System altered.
- Enable Archiving
Another important step to rpepare the primary database is to put it in archivelog mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1686925312 bytes
Fixed Size      2213976 bytes
Variable Size   1291847592 bytes
Database Buffers   385875968 bytes
Redo Buffers      6987776 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.
We have not finished... it just continues on this next step

10 comments:

Marco V. said...
This comment has been removed by the author.
oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose