Oracle 11g R2 - Configuring Data Guard / Part II

Enabling Data Guard Broker

The following article, depends on the previous article which is “Configuring Data Guard Part I”.

After I created the physical standby server, although it’s necessary, I decided to enable Data Guard Broker. The advantage of Data Guard Broker is easier management. You’ll see this on my other article (Part III). 

So here are the steps. 

*** Be careful about the server that you execute the commands on. In every section, there’s a note about on which server that the script is going to be executed***

PRIMARY SERVER

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE

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

dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0

                                                 /db_1/dbs/dr1primary.dat

dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0

                                                 /db_1/dbs/dr2primary.dat

dg_broker_start                      boolean     FALSE

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

STANDBY  SERVER

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE

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

dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0

                                                 /db_1/dbs/dr1primary.dat

dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0

                                                 /db_1/dbs/dr2primary.dat

dg_broker_start                      boolean     FALSE

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

LISTENER.ORA    --STANDBY SERVER -- Add this section to listener.ora.

SID_LIST_LISTENER =

  (SID_LIST =

  

  (SID_DESC = 

    (GLOBAL_DBNAME = sby1_DGMGRL.europe)

    (SID_NAME = sby1)

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

  )

lsnrctl stop

lsnrctl start

LISTENER.ORA    --PRIMARY SERVER -- Add this section to listener.ora.

SID_LIST_LISTENER =

  (SID_LIST =

  

  (SID_DESC = 

    (GLOBAL_DBNAME = primary_DGMGRL.europe)

    (SID_NAME = primary)

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

  )

lsnrctl stop

lsnrctl start

Still on the primary server

[oracle@rome ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Oracle_1

Connected.

DGMGRL> show configuration

ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

DGMGRL> create configuration 'DGConfig1' as 

> primary database is 'primary'

> connect identifier is primary;

Configuration "DGConfig1" created with primary database "primary"

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance

  Databases:

    primary - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> add database 'sby1' as 

> connect identifier is sby1;

Database "sby1" added

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance

  Databases:

    primary - Primary database

    sby1    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance

  Databases:

    primary - Primary database

    sby1    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

Oracle 11g R2 - Configuring Data Guard / Part I

High Availability - Creating Standby Server

After several busy weeks, I didn’t want to miss this chance. It was definetely very overloaded time. I spent one week for a LVC (Live Virtual Class) and the participants were from all around the world. Then I went to Malta to provide consultancy service. And now I’m in Bucharest delivering a Siebel 8.1 Core Consultant Course. At last I start to write a new article. I decided to give some examples from high availability point of view. Actually, I was involved in a project and this was the main idea.  So here is the demonstration of Data Guard configuration on 11g R2 database.

In order to demonstrate this infrastructure, I had configured 2 VMware images based on Linux operating systems. I configured all network configurations and hosts files on these 2 Linux computers. So I can easily access from both computers to each other. All the prerequisites are done for Oracle 11g R2 database which includes additional rpm packages,  creating OS groups & users, environment variables etc..

In addition to these, I’ve installed Oracle Software on both of the servers and create a database on one of the instance which will be PRIMARY server. 

So at this point, there are 2 servers. A database is already created on one of them. There is only Oracle Software installed on the other. Host names of the computers are my favorite cities in Italy. :) Primary server is Rome and Standby server will be Venice.

I created a pfile on the standby server.  $ORACLE_HOME/dbs/initSBY1.ora

The contents of the pfile is below, only one parameter:

DB_NAME=SBY1

The next step is configuring the network components of the databases. The content of the listener.ora files are below:

LISTENER.ORA     --STANDBY SERVER so on Venice

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = TCP)(HOST = venice.europe)(PORT = 1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

  

  (SID_DESC = 

    (GLOBAL_DBNAME = sby1.europe)

    (SID_NAME = sby1)

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

  )

-

TNSNAMES.ORA        --PRIMARY & STANDBY Servers  so on Rome & Venice

SBY1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = venice.europe)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = sby1.europe)

    )

  )

PRIMARY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rome.europe)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = primary.europe)

    )

  )

There is one more step before generating the standby database. This should be done before the DUPLICATE command from RMAN console. Otherwise you may experience an Oracle Error 00845: Memory_target is not supported on the standby server. The reason of this is the temp device size is less than Memory_target size. So I just increased the device size by executing these commands with Root user account.

root@venice ~]# mount -t tmpfs shmfs -o size=3g /dev/shm

[root@venice ~]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

                       45G   19G   24G  45% /

/dev/sda1              99M   12M   82M  13% /boot

tmpfs                 3.0G     0  3.0G   0% /dev/shm

/dev/hdc              2.8G  2.8G     0 100% /media/Enterprise Linux dvd 20090127

shmfs                 3.0G     0  3.0G   0% /dev/shm

Then I started the instance on the standby server (Venice):

EXPORT ORACLE_SID=sby1

sqlplus / as sysdba

startup nomount pfile= /u01/app/oracle/product/11.2.0/db_1/dbs/initsby1.ora

Standby server is ready for the Data Guard configuration. Now there are several steps on the Primary server. The tasks below will be done on the server named “Rome”.

sqlplus / as sysdba

alter system set log_archive_dest_1 = 'SERVICE=sby1 ASYNC VALID_FOR = (ONLINE_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=sby1';

alter system set log_archive_config = 'dg_config=(primary,sby1)';

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl01.log' size 50M;

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl02.log' size 50M;

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl03.log' size 50M;

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl04.log' size 50M;

The recommended practice is the number of standby log files should be greate than the number of redo log files. That’s why I created one extra standby log on the primary. It’s very obvious that Primary database should be running in Archivelog mode. So:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/flash_recovery_area

Oldest online log sequence     38

Next log sequence to archive   40

Current log sequence           40

Before going one step ahead, check the archive log destinations. There should be 2 destinations, one on the localhost and one on the remote server which is standby server. If not, take the corrective action.

In order to establish a succesful connection from Primary to Standy server, you should create a password file on the standby server. The steps are:

STANDBY SERVER

[root@venice ~]# rpm -q vsftpd

vsftpd-2.0.5-12.el5

[root@venice ~]# service vsftpd start

Starting vsftpd for vsftpd:                                [  OK  ]

[root@venice ~]#

PRIMARY SERVER

cd /u01/app/oracle/product/11.2.0/db_1/dbs/orapwprimary.ora

ftp venice

user: oracle

pwd: oracle

put orapwprimary.ora

STANDBY SERVER

mv /home/oracle orapwprimary.ora /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsby1.ora

PRIMARY SERVER

rman target sys/xxx@primary auxiliary sys/yyy@sby1

RMAN>@dg.txt

DG.TXT Content is:

run 

{

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile parameter_value_convert 'primary','sby1'

set db_unique_name='sby1'

set db_file_name_convert='/primary/','/sby1/'

set log_file_name_convert='/primary/','/sby1/'

set control_files='/u01/app/oracle/oradata/sby1.ctl'

set log_archive_max_processes='5'

set fal_client='sby1'

set fal_server='primary'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(primary,sby1)'

set log_archive_dest_1='service=primary ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primary';

}

This will take sometime. After it finishes the process, the next step is:

PRIMARY SERVER

SQLPLUS / AS  SYSDBA

SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME

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

        31 27-OCT-10 27-OCT-10

        31 27-OCT-10 27-OCT-10

        32 27-OCT-10 28-OCT-10

        32 27-OCT-10 28-OCT-10

        33 28-OCT-10 28-OCT-10

        33 28-OCT-10 28-OCT-10

        34 28-OCT-10 28-OCT-10

        34 28-OCT-10 28-OCT-10

alter system switch logfile;

And the last step is starting the Apply Service on the Standby server:

STANDBY SERVER

sqlplus sys/yyy@sby1 as dba

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME

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

        31 27-OCT-10 27-OCT-10

        32 27-OCT-10 28-OCT-10

        33 28-OCT-10 28-OCT-10

        34 28-OCT-10 28-OCT-10

/%

After several log swithces on the primary server --> Query the archived_log on the standby server

%/

SQL> select sequence#, first_time, next_time, applied from v$archived_log

  2  order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

        31 27-OCT-10 27-OCT-10 NO

        32 27-OCT-10 28-OCT-10 NO

        33 28-OCT-10 28-OCT-10 NO

        34 28-OCT-10 28-OCT-10 NO

        35 28-OCT-10 28-OCT-10 NO

        36 28-OCT-10 28-OCT-10 NO

        37 28-OCT-10 28-OCT-10 NO

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select sequence#, first_time, next_time, applied from v$archived_log

  2  order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

        31 27-OCT-10 27-OCT-10 YES

        32 27-OCT-10 28-OCT-10 YES

        33 28-OCT-10 28-OCT-10 YES

        34 28-OCT-10 28-OCT-10 YES

        35 28-OCT-10 28-OCT-10 YES

        36 28-OCT-10 28-OCT-10 YES

        37 28-OCT-10 28-OCT-10 YES

        38 28-OCT-10 28-OCT-10 YES

        39 28-OCT-10 28-OCT-10 IN-MEMORY

OBIEE 11g - Creating Key Performance Indicators (KPI)

One of the important new feature is Performance Management objects in the new version of OBIEE 11g. These kind of features were not implemented in the previous version.  I’m going to demonstrate how to create KPI object in OBIEE 11g. 

After you login to OBI, you’ll see create section on the left side. You’ll just click “Create a new KPI” link and it’s going to ask to select the subject area as usual. I use “Sample Sales Lite” subject area in my demo.

There are 4 parts for creating the KPI:

1- General

2- Dimensionality

3-Thresholds

4- Related Documents

In the “General Section” you’ll have to specify the “Business Owner”. Based on the KPI values, you’ll be able to contact with the “Business Owner”.  You must select one measure column for the “Actual Value” and another measure column for the “Target Value”. Just a simple example: Units Ordered and Units Shipped. Another option is “Trending”. If you’ve created a Time dimension in the repository, you’ll gain benefit of trending option. By using this option, you’ll be able to compare periods. 

In the dimensionality page, you’ll select the dimensions and levels that you want to focus on. And if you don’t want to select a value for the member, you just write “Not Pinned”.

In the thresholds page, you specify the threshold values. By default, you can specify only 2 values but it’s customizable. You can create more threshold values and also you can use custom images. 

In the last page named “Related Documents”, you just specify the links for additional documents, web pages or sample reports. This is optional but good have descriptions for the KPI.

Then you’ll save the KPI. 

After you save the KPI in the Presentation Catalog, you have several options to publish the KPI. Here are some examples:

• Pusblish in the dashboard.
• Use in Balanced Scorecard.
• Publish in the KPI watchlists.

As a result I’m going to publish new articles about using KPIs.