CUNEYT YILMAZ - ORACLE BLOG

Traveller Tips

2011-11-13T14:45:00.000-08:00

Besides technical articles, I want to share some pictures which I've taken during my trips. I was just thinking about the trips for the last 5 years.. So many places, so many friends, different cultures and experiences. This is the best part of my job.

I uploaded my favorite pictures in my blog: http://www.oratt.com
When you check the travel section, you'll find them :)

One day in Croatia..

One day in Slovenia...

Or in San Francisco...

Oracle Public Cloud Services

2011-11-10T13:59:00.000-08:00

I deliver Oracle Business Intelligence 11g Build Repositories course in Athens this week and want to share information related to Oracle Public Cloud Services.

Oracle announced cloud services for their customers. It's "Oracle Public Cloud". It's not yet enabled but it's on the way. Oracle Public Cloud is divided into 2 main categories:

1-Application Services

-Fusion CRM: Managing opportunity data, territory management and so on by using Fusion CRM

-Fusion HCM (Fusion Human Capital Management): Supports interactive organizational charts, people portraits, analytics, employee benefit administration and so on.

-Social Network: Secure collaboration tool in the enterprise.

2-Platform Services

-Java Cloud Service: Supports applications based on WebLogic server, framework like Oracle ADF, supports high availability based on WebLogic Server Clusters and so on.

-Database Cloud Service: Develop and deploy your databases on Oracle 11g R2 with high availability options and your data will be very securely stored.

You can get more information from the below address:

http://cloud.oracle.com

As a result, you will be easily subscribing to these cloud services and and manage your business processes in a high available and secure environment with less cost.

Oracle Business Intelligence 11g - Dashboard Prompts

2011-10-29T13:05:00.000-07:00

Dashboard prompt is very useful object which can be published in the dashboard in order to filter the data. In this example, I have created 3 reports and published them in the dashboard. Of course, the last object I’ve created is the dashboard prompt. When the users accesses the dashboard for the first time, the default values are used like defined in the analysis.

Once you select the new value from the drop down list, you can see that all the analysis which have a filter defined are effected.

Oracle Business Intelligence 11g Performance & Strategy Management

2011-10-28T15:37:00.000-07:00

It was again very busy days for last couple of months. To be honest, it was as usual. I had to deliver some trainings in different locations and I had a chance to deliver seminars in SIOUG 2011 and HROUG 2011. I deliver 2 sessions in SIOUG 2011 and they were about OBI 11g. I was invited to HROUG 2011 as a keynote speaker about applications and delivered a session about “Business Automation Features of Siebel CRM”. Both of the events were really great and I’m very glad to a be a part of these events. In addition, I’ll be delivering 2 sessions in BGOUG 2011.

As a result I want to share an article about the performance and strategy management feature of Oracle Business Intelligence 11g.

The first object type is the KPI. It is a measurment of business process and compares against a specific goal and it has a trending option which is optional. Also you can create “KPI Watchlists” to have a group of related KPIs and access all the details from a single user interface.

Here is how a KPI looks like:

And if you have multiple KPIs which is obvious then you can gain benefit of using KPI watchlists. When you create the KPI watchlist, you’ll be able to define the dimensionility and then add the KPIs that you’re interested:

Another important feature of Oracle Business Intelligence 11g is you have a solution to implement strategy management techniques like Balanced Scorecards.

First you create a Scorecard.

Then it’s better to define the vision and mission documents by using the Scorecard Documents menu item:

Vision Document: A statement what your organization wants to be in the future.

Mission Document: A statement specifies the key business goals and priorities that are required to achieve the vision.

In order to manage the defined strategy, you’ll need to understand the performance lenses which are called “Perspectives”. There are 4 different default perspectives. New ones can be also created in the case of requirements. So these are the categories in your organization with which to associate initiatives, objectives and KPIs.

Financial, customer, internal processes and learning & growth are the default perspectives.

Maybe the most important object type in the scorecards is the “Objectives”. Objectives are desired outcomes of your organization and can be organization based or organized for each department seperately. KPIs are assigned to them so that you can easily measure its progress. They are created hierarchically.

To monitor the progress in the scorecards, you can use “Initiatives”. They are time specific tasks like milestones:

There are 3 different types of objects more in order to support the scorecards:

1- Strategy Trees: They are created based on the objectives and helps to navigate to entire strategy in the scorecard and provides supporting information such as owners and KPIs.

2- Strategy Maps: A map shows how the objectives that have been defined for a scorecard area aligned by perspectives and indicates the relationship between the objectives.

3- Cause & Effect Map: It shows the cause & effect relationship between objectives. It helps identifying other objectives that impact the objectives and other objectives that this objective impacts.

Strategy Tree:

Strategy Map:

Cause & Effect Map:

And the big picture of the scorecard:

HrOUG 2011

2011-10-23T12:36:00.000-07:00

I’ve attended to Croatian Oracle User Group event in Rovinj, Croatia for the first time. The conference was taken in place called Red Island very closed to Rovinj. You can only go by ferry. It was great to attend this conference and I was invited as keynote speaker. My keynote speech was about Siebel CRM application. The main subject was about business automation features of Siebel CRM application. You can access the details from:

http://www.hroug.hr/hr

SIOUG 2011

2011-10-23T12:34:00.000-07:00

Just before the Oracle OpenWorld, I’ve attended to Slovenian Oracle User Group event which was in Portoroz like the previous year. I had a chance to deliver 2 sessions. Both of them were related to Oracle Business Intelligence 11g. One of the session was about the “Performance Management and Scorecards” and the other one was about “Creating Custom Actions in OBI 11g”.
Portoroz is also very nice city in the south part of Slovenia.

http://www.sioug.si/

Oracle OpenWorld 2011

2011-10-23T12:32:00.000-07:00

I have attended to Oracle OpenWorld 2011. It was a great experience to attend different kind of sessions and meeting with colleagues from all around the world.
You can easily access the presentation details and the content itself from the link below. So even if you couldn’t have time to attend OOW 11, you still have an option to download the content and read them. The link is below: (from TOOLS and Content Catalog).
http://www.oracle.com/openworld/index.html
I’m also going to publish some pictures about my trip to San Francisco in the coming days.

Oracle Business Intelligence 11g - How To Limit Data In Your Analyses? Part II

2011-05-18T13:14:00.000-07:00

I’m going to demonstrate how to use prompts in an analysis in order to limit data. After I create the analysis, I check the result. Then click the “Prompt” tab and create 3 column prompts. As a result end users are going to select the values from the dropdown list and eliminate the records based on the user selections. In the first dropdown list, they will select the “Company” then “Organization” and at last “Department”.

The last example is about the selections. So I create a report with 4 columns and grand totals.

I expand the selection steps section to edit the selections. And edit the selections. I select the “Start with selected members” from the actions dropdown list and add two departments. You’ll see that the grand total is changed.

In the last example I’m going to show you how to use remove action in the selections. It starts with all members and remove one member in the second step. As a result the selections are applied after the aggregation and the grand total isn’t changed.

Oracle Business Intelligence 11g - How To Limit Data In Your Analyses? Part I

2011-05-18T12:22:00.000-07:00

There are 2 ways to limit the data in your analyses.

1- Using Filters: This type of limiting data limits data for a column prior to aggregation.

2- Using Selections: This limits data after aggregation. There are 2 types of selections:

a- Using selections based on attribute columns: This limits members after aggregation and doesn't effect the row and grand totals.

b- Using selections based on hierarchical columns to limit members. Effects row and grand totals but doesn't effect the total members aggregation.

USING FILTERS

Creating filters is same as in the previous version of OBI 10g. You can create filters in the analyses and save the filters as named filters in order to access to filter definition and use that filter in another analyses. You can also use "Grouping" option of filters if you've added more then 1 filter by using AND/OR operators. Another information about the filters is using the variables to implement dynamic filters. Dynamic filters can be based 3 types of variables:

-Session variables

-Repository variables

-Presentation variables

Another option about the filters is using a Saved Analysis as a filter. You can use any saved analysis that returns a column of values to filter a selected column in the originating analysis.

Converting an existing filter to SQL is a standard feature in order to eliminate the rows by writing SQL condition.

I'm going to demonstrate these filter types and also create analysis prompts.

USING SELECTIONS

Selections are used to limit the members after the aggregation. Members are assembled from selection steps with the same order which is in the selection pane in the analysis. Another good feature of the selections is using groups. Groups are used grouping members and aggregating the result based on the list of members in the groups.

Here are some demonstrations about limiting data by using filters and selections.

First one will be adding a simple filter to the analysis. I create a simple report with 3 filters. You will see that you can easily use “Grouping” feature. 2 logical operators (AND/OR) are supported.

The second example is creating filter based on another analysis. I create an analysis and filtered data by year column. The column stores only 2009 and 2010 values. Then I create another report and a filter to use the result set of another report based on “Year” column.

The third example is based on a filter that includes a variable. There are 3 kinds of variables:

Presentation Variable: Can be declared in the analysis.
Repository Variable: This should be created in the repository. And repository variables are initiated when the service is started and can be accessed until the service is down.
Session Variables: It’s also created and declared in the repository. The difference is its lifetime. It’s instances declared when the session is opened, stays active until the session is closed.

If you want to gain benefit of variables, you’ll have to get the variable list by accessing the repository. In my example, I use a repository variable as filter. When the variables value is refreshed, the report will be effected.

The last example is converting the filter to SQL in order to edit criteria by using SQL. You achieve this by selecting the checkbox “Convert this filter to SQL”.

Oracle 11g R2 - Configuring Data Guard / Part III

2011-04-07T13:06:00.000-07:00

Performing a Switchover Test

The last article is about performing a switchover test. After you configure the Data Guard configuration, it’s a good practice to simulate a disaster. There are also other ways but I’m going to make a switchover test only.

*** 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/PLUS

[oracle@rome ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 28 10:43:05 2010

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table hr.emp_hist as select * from hr.employees;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> exit

DGMGRL

[oracle@rome ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Oracle_1

Connected.

DGMGRL> show database verbose 'primary';

Database - primary

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

primary

Properties:

DGConnectIdentifier = 'primary'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'MANUAL'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = ''

LogFileNameConvert = ''

FastStartFailoverTarget = ''

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'rome.europe'

SidName = 'primary'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rome.europe)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=primary_DGMGRL.europe)(INSTANCE_NAME=primary)(SERVER=DEDICATED)))'

StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

STANDBY SERVER

[oracle@venice ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Oracle_1

Connected.

DGMGRL> show database verbose 'sby1';

Database - sby1

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

sby1

Properties:

DGConnectIdentifier = 'sby1'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '5'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/primary/, /sby1/'

LogFileNameConvert = '/primary/, /sby1/'

FastStartFailoverTarget = ''

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'venice.europe'

SidName = 'sby1'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=venice.europe)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sby1_DGMGRL.europe)(INSTANCE_NAME=sby1)(SERVER=DEDICATED)))'

StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

PRIMARY SERVER

DGMGRL> edit database 'primary' set property DbFileNameConvert='/sby1/, /primary/';

Property "dbfilenameconvert" updated

DGMGRL> edit database 'primary' set property

> LogFileNameConvert='/sby1/, /primary/';

Property "logfilenameconvert" updated

DGMGRL>

DGMGRL> show configuration ;

Configuration - DGConfig1

Protection Mode: MaxPerformance

Databases:

sby1 - Primary database

primary - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

SQL> select status from v$instance;

STATUS

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

MOUNTED

STANDBY SERVER

DGMGRL> show configuration

Configuration - DGConfig1

Protection Mode: MaxPerformance

Databases:

sby1 - Primary database

primary - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

sby1

LATER SWITCHOVER TO PRIMARY AGAIN

SERVER: SBY1

DGMGRL> switchover to primary;

Performing switchover NOW, please wait...

New primary database "primary" is opening...

Operation requires shutdown of instance "sby1" on database "sby1"

Shutting down instance "sby1"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "sby1" on database "sby1"

Starting instance "sby1"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "primary"

DGMGRL>

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 II

2011-04-07T13:04:00.000-07:00

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

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

2011-04-07T13:02:00.000-07:00

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

32 27-OCT-10 28-OCT-10

33 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)

2011-03-09T13:11:00.000-08:00

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.

OBI 11g Create Analyses and Dashboards Training in Zagreb

2011-02-21T12:58:00.000-08:00

I’ve been to Zagrep, Croatia for the first time last week to deliver OBI 11g Create Analyses and Dashboards training at Oracle University Croatia. Most of the new features are covered in the training and the duration of the course is changed to 4 days with the new version. It was 3 days before. The attendees were highly experienced with the previous version of OBI. So I really enjoyed my time during the lecture with questions and interactivity of the attendees.

Some of the new features that are covered in the training:

-BI Server Architecture

-New User Interface

-Improvements About The Views

-New Formatting Options

-Pre & Post Filtering (Selections)

-Linking Master Detail Views

-Key Performance Indicators (KPIs)

-Balanced Scorecards

-New Dashboards Options

-Conditional Sections with Analyses and KPIs

-Creating Agents

-and more...

I highly recommend the new versions trainings of Oracle Business Intelligence 11g.

OBI 11g Create Analyses and Dashboards - 4 Days

OBI 11g Build Repositories - 5 Days

If you plan to attend these 2 trainings in Istanbul, Bilginc IT Academy will be the solution. You can access all the details about those trainings from the link below:

http://www.bilginc.com

In addition, I really enjoyed my time in Zagreb. It’s a beautiful city with a great architecture. I stayed in the city center so had a chance to walk around easily. The buildings, archictecture is worth to see. You can see some photos from the “Travels” section in my blog. --> www.oratt.com

Oracle Data Integrator 11g - Part III (Creating Repositories)

2011-02-21T09:00:00.000-08:00

You can find the installation steps of Oracle Data Integrator 11g in my previous articles. And today, I’m going to demonstrate creating repositories and then start a new project by using Oracle Data Integrator 11g.

The first difference is about the user interface. There’s only one interface that you use to connect Topoloy Manager, Designer etc. So when you open the tool, you’ll have to choose the Master Repository. If the repository doesn’t exist, then you’ll have to create the repositories. And the first will be “Master Repository”. Just to remind you that all the objects that are created by using Topology Manager and Security Manager will be stored in the Master Repository.

When you click the “Master Repository Creation Wizard”, it’s going to ask you to specify the database connection settings in the next 3 pages.

Once the Master Repository is created, you’re going to user Topology Manager to create the Work Repository. Work Repositories should be attached to the Master Repository. So before creating the work repository, you have to connect to Master Repository.

As you’ll see in the previous image, the connection is established. And you’ll start to create the work repository from the “Topology Manager”. Again you’ll specify the connection details.

Now you have created both repositories. So you can start using Designer tool in order to start a new project. So you’re going to create the data sources and target databases by using “Topology Manager”.

Oracle Data Integrator 11g - Part II

2011-02-03T13:36:00.000-08:00

After a long time, I realized that I don’t write any articles in blogs. :) Ok let’s say it was just a break and now I’m back. Actually I wasn’t in holiday. Just completely busy days which is about delivering trainings in somewhere Europe also in my home country Turkey.

So I’m going to continue for the installation of Oracle Data Integrator 11g. After you download the the insallation files, as usual you just run setup.exe file. At that time you’ll see welcome page:

Then you’ll have to select the installation type by choosing components:

After the prerequisite checks, you specify the installation location:

The next page is about the repositories. I’m going to discuss them in my next article. But repositories will be needed to develop a ETL project. So you may create a new master and work repository or you may use the existing repositories. In my scenario, I’m going to skip the repository configuration and I’m going to configure both just after the installation.

So I select the skip option and the summary page comes up.

At the end, you’ll see the “Installation Completed” page.

And the first screen of Oracle Data Integrator 11g. The next coming step will be about creating master and work repositories.

Oracle Data Integrator 11g Installation - Part I (WebLogic Server)

2010-10-28T14:09:00.000-07:00

I’m going to demonstrate how to install Oracle Data Integrator 11g in this article. I divided the installation process into 2 parts. First part will be related with Oracle WebLogic Server. And I’m going to explain the ODI 11g installation in the second part. So here you are:

You can download the latest version of Oracle WebLogic Server 10.3.3. Then start the installation:

Then installation wizard will ask you for the Middleware path. So you just specify the installation path.

Next usual but important question is about your e-mail address for Oracle Support. It’s highly recommended to fill these information in order to receive security updates.

Then it’s going to confirm the installation directories.

You’re going to choose the short cut folders for the installation:

And you’re going to see the “Installation Summary” page and see the details of the installation. When you click next, installation will start.

You can see the progress of the installation.

After the installation completes, you can trigger the “Configuration Wizard” to create new domain in the WebLogic Server. You don’t have to create a domain in this step. So to skip it, just clear the “Run Quickstart” checkbox.

NEW - Oracle BI 11g Trainings

2010-10-21T07:39:00.000-07:00

New Oracle BI 11g trainings are announced. There are 2 trainings that are similar to the previous trainings for 10g. One of them is about creating analyses and creating dashboards. So this one is related with the web interface. Build Repositories is the other training which focuses on creating and modifying the repository. Here are the details:

I - Oracle BI 11g R1: Create Analyses and Dashboards

Introduction to Oracle Business Intelligence Suite Enterprise Edition

Introduction to Oracle BI
Architecture Overview

Working with Oracle Business Intelligence Analyses

Introduction to Oracle BI Analysis Editor
Oracle BI column types
Working with analyses in Oracle BI
Using advanced formatting

Administering the Presentation Catalog

Maintaining the Oracle BI Presentation Catalog in the Catalog page
Oracle BI and catalog security Overview
Managing security Using roles
Understanding security inheritance
Setting object permissions
Setting system privileges
Archiving catalog items

Limiting and Grouping Data in Analyses

Introduction to filters and selections
Creating, editing, and grouping filters
Adding prompts to analyses
Dynamic filtering
Using saved analyses as filters
Creating groups
Creating calculated items
Creating selection steps

Oracle Business Intelligence Analyses: Advanced Features

Setting analysis properties
Combining analysis criteria by using set operations
Executing direct database analyses
Editing logical SQL generated by an analysis
Creating a link to a saved analysis
Creating an Excel Web Query file

Working with Views and Graphs in Analyses

Introduction to views, graphs, and editors
Working with views in Compound Layouts
Creating and editing graphs
Linking master-detail views
Performing common view tasks

Showing Results with Gauge Views and Map Views

Introduction to Gauge views
Working with Gauge views
Introduction to Map views and their components
Creating and editing Map views

Measuring Results with Key Performance Indicators

Introduction to KPIs
Creating KPIs, setting trending, dimensionality, and thresholds
Introduction to KPI Watchlists
Creating a KPI Watchlist
Changing Dimensionality for a KPI Watchlist event

Showing Results With Pivot Tables

Introduction to pivot tables
Creating, arranging, and formatting a pivot table
Using hierarchical columns
Sorting in pivot tables
Setting aggregation and using totals
Showing an item's relative value
Building calculations and displaying running sums

Creating Oracle Business Intelligence Dashboards

Introduction to dashboards and dashboard pages
Creating and editing dashboards
Using the Dashboard Builder
Exploring dashboard oject properies and options
Publishing dashboard pages
Creating personal customizations and using other page options

Configuring Oracle Business Intelligence Dashboards

Types of dashboard content
Embedding content in dashboards

Creating Dashboard Prompts and Variables

Overview of variables
Introduction to dashboard prompts and prompt types
Adding a named dashboard prompt to a dashboard
Adding a hidden named dashboard prompt to a dashboard
Creating additional prompt page and setting page preferences
Variables and prompts
Adding variable prompts to a dashboard
Wiring dashboard prompts

Using Oracle Business Intelligence Delivers

Introduction to Oracle BI Delivers
Configuring delivery devices and adding delivery profiles
Introduction to alerts
Adding an Alert Section to a dashboard
Overview of Agents
Configuring an Agent
Using Analysis and KPI conditions to Deliver content with Agents
Subscribing to an Agent

Integrating Analyses with MS Office

Introduction to Oracle BI Office Add-in
Working with Oracle BI content in Microsoft Office applications

Working with Oracle Business Intelligence Briefing Books

Introduction to Oracle BI Briefing Books
Adding Content to Briefing Books
Editing Briefing Books
Downloading and viewing Briefing Books
Adding a list of Briefing Books to a dashboard
Using Briefing Books with Oracle BI Deliver

II - Oracle BI 11g R1: Build Repositories

Repository Basics
Exploring Oracle BI architecture components
Exploring a repository's structure, features, and functions
Using the Oracle BI Administration Tool
Creating a repository
Loading a repository into Oracle BI Server memory
Building the Physical Layer of a Repository
Importing data sources
Setting up connection pool properties
Defining keys and joins
Examining physical layer object properties
Creating alias tables
Building the Business Model and Mapping Layer of a Repository
Building a business model
Building logical tables, columns, and sources
Defining logical joins
Building measures
Examining business model object properties
Building the Presentation Layer of a Repository
Exploring Presentation layer objects
Creating Presentation layer objects
Modifying Presentation layer objects
Examining Presentation layer object properties
Testing and Validating a Repository
Checking repository consistency
Turning on logging
Defining a repository in the initialization file
Executing analyses to test a repository
Inspecting the query log
Managing Logical Table Sources
Adding multiple logical table sources to a logical table
Specifying logical content
Adding Calculations to a Fact
Creating new calculation measures based on existing logical columns
Creating new calculation measures based on physical columns
Creating new calculation measures using the Calculation Wizard
Creating measures using functions
Working with Logical Dimensions
Creating logical dimension hierarchies
Creating level-based measures
Creating share measures
Creating dimension-specific aggregation rules
Creating presentation hierarchies
Creating parent-child hierarchies
Using calculated members
Using Aggregates
Modeling aggregate tables to improve query performance
Setting the number of elements in a hierarchy
Testing aggregate navigation
Using the Aggregate Persistence Wizard
Using Partitions and Fragments
Exploring partition types
Modeling partitions in an Oracle BI repository
Using the Calculation Wizard to create derived measures
Using Repository Variables
Creating session variables
Creating repository variables
Creating initialization blocks
Using the Variable Manager
Using dynamic repository variables as filters
Modeling Time Series Data
Using time comparisons in business analysis
Using Oracle BI time series functions to model time series data
Modeling Many-to-Many Relationships
Using bridge tables to resolve many-to-many relationships between dimension tables and fact tables
Localizing Oracle BI Metadata and Data
Localizing repository metadata
Localizing Oracle BI data
Setting an Implicit Fact Column
Adding fact columns automatically to dimension-only queries
Ensuring the expected results for dimension-only queries
Selecting a predetermined fact table source
Specifying a default join path between dimension tables
Importing Metadata from Multidimensional Data Sources
Importing a multidimensional data source into a repository
Incorporating horizontal federation into a business model
Incorporating vertical federation into a business model
Adding Essbase measures to a relational model
Displaying data from multidimensional sources in Oracle BI analyses and dashboards
Security
Exploring Oracle BI default security settings
Creating users and groups
Creating application roles
Setting up object permissions
Setting row-level security (data filters)
Setting query limits and timing restrictions
Cache Management
Restricting tables as non-cacheable
Using Cache Manager
Inspecting cache reports
Purging cache entries
Modifying cache parameters and options
Seeding the cache
Enabling Usage Tracking
Setting up the sample usage tracking repository
Tracking and storing Oracle BI Server usage at the detailed query level
Using usage tracking statistics to optimize query performance and aggregation strategies
Analyzing usage results using Oracle BI Answers and other reporting tools
Multi-user Development
Setting up a multiuser development environment
Developing a repository using multiple developers
Tracking development project history
Configuring Write Back
Enabling write back in a repository
Creating a write back template
Granting write back privileges
Enabling write back in an analysis
Performing a Patch Merge
Comparing repositories
Equalizing objects
Creating a patch
Applying a patch
Making merge decisions

Oracle Business Intelligence 11g - Multiple Compound Layouts in Analysis

2010-10-18T22:38:00.000-07:00

You will be able to create multiple compound layouts in the reports in the analysis. This means you don’t have to create many reports for different views of the reports. You are going to create one analysis with different views and these views will be placed in different compound layouts. When it comes to publishing the analysis, you’re going to select which compound layout view will be used.

So to demonstrate this scenario, I’m going to login to the BI Server and start to create a new analysis.

And add some different views to the default compound layout. So now there are 3 views in the compound layout view:

Title View
Table View
Chart View

The next step is creating the second compound layout. You can either create the compound layout or duplicate the existing one. I used both ways. As a result now there are 3 compound layouts in the analysis.

After saving the analysis, I’m going to publish the analysis in the dashboard. To publish 3 different compound layouts, I created 3 Dashboard Pages for this analysis. I drag the analysis to the section place holder in the dashboard editor page. You’ll see the show view option just there. And choose the one of the compound layout based on your requirements. As a result there are 2 columns in the page and one section in each column. I publish the same analysis with different compound layouts.

Oracle Business Intelligence 11g - Authentication and Creating Users

2010-10-16T14:40:00.000-07:00

Now it comes to user authentication in Oracle Business Intelligence 11g. As you already see the installation steps, OBI is installed with the integration of WebLogic Server. Although there’s a repository file which is created by default as sample, you need to create user account in the WebLogic Server. You’ll have a chance for accessing the list of users in the repository file (*.rpd). You still edit the repository by using the BI Administration tool. But creating the user is done through the WebLogic Server Console. First I login to WebLogic console.

I open the BI Administration Tool and open the repository in “Online” mode. When you click the manage menu on the tool bar, you’ll see the “Identity” management which about security, user accounts and permissions etc. It’s the same as Security Manager in the 10g version with some exceptions. I can see the list of users and groups but don’t have any option to create the user object.

So that’s why I goto Security Realm in the WebLogic Console. In the “Users and Groups” tab, you’ll see the users and groups tabs. I click the new button and start to create the user.

First I specify the username and password. And set the authentication provider.

Next step is related with the group membership. I make the new user as a member of BI Administrators group. Then save the changes.

After creating the new user, I can see the user in the identity manager (security manager). In addition to that list, I can successfully login to the BI server with the new user.