Feed aggregator

Oracle OpenWorld 2008 - Just Say Hi!

Mark A. Williams - Fri, 2008-08-22 20:35

Just a quick note... if you happen to be coming to Oracle OpenWorld 2008 in San Francisco, I will be helping out in the .NET Hands-On Labs Monday (22 Sep) afternoon. I'll also be at the "OTN ACE Office Hours" on Monday (22 Sep) as well as Thursday (25 Sep). See the link for schedules as well as the great folks who will be available. I've also been known to be around the demo grounds booth as well. Please say "hi" if you get the chance!

Data Mining in Action: Oracle Sales Prospector

Marcos Campos - Fri, 2008-08-22 12:10
I firmly believe that a major trend in applications is the incorporation of analytic-enabled functionality. Users want more than just reports or a replay of the past. Users want to have insights and their attention directed to key points. This is where analytics can make a big impact across all types of applications. Notice that I am not proposing exposing analytical capabilities (e.g., data Marcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com2
Categories: BI & Warehousing

Oracle Transparent Gateway for MS SQL Server - Part II

Aviad Elbaz - Fri, 2008-08-22 08:28

Following my last post about Oracle Transparent Gateways which was theoretical post, this post will be more practical.

In this post I'll show an example of configuring Oracle Transparent Gateway for MS SQL Server.
The goal is to make it possible to retrieve data from MS SQL server database by querying from Oracle database.

The steps are:

  1. install Oracle Transparent Gateway for MS SQL Server (not explained here)
  2. Configure the gateway initialization parameter file
  3. Configure the listener
  4. Configure the Oracle database tnsnames.ora
  5. Creating DB link from Oracle database to MS SQL Server

In my environment there are 3 servers involved:

  1. Oracle database server - name: orclsrv01, with Oracle database installed name: orcldb
  2. Microsoft SQL server - name: msqlsrv01, with MS SQL server installed name: msqldb
  3. Oracle Gateway server - name: oragwsrv01, with Oracle Transparent Gateway installed.


Configure the gateway initialization parameter file

After Oracle Transparent Gateway for MS SQL server installed on ORAGWSRV01 server, a new default initialization file is created at: $ORACLE_HOME\tg4msql\admin\inittg4msql.ora.

$ORACLE_HOME - is the directory where Transparent Gateway installed.
inittg4msql.ora - is the default initialization parameter file, as tg4msql represents the Gateway SID.
If you choose to not use the default SID (or when you have more than one MS Sql server to connect to), the initialization parameter file should be named as follow: init[GW_SID].ora

Edit the inittg4msql.ora file and specify the MS Sql Server connection details as follow:
HS_FDS_CONNECT_INFO=server_name.database_name

in our case:
HS_FDS_CONNECT_INFO=msqlsrv01.msqldb

Alternatively you can set it as follow:
HS_FDS_CONNECT_INFO="SERVER=msqlsrv01; DATABASE=msqldb"

Configure the listener

In order to make the listener work with the gateway we should configure it.
The listener have to be on the same server like the Gateway.
We should edit the listener.ora within the Oracle home on which the Gateway installed.

The listener.ora must have at least 2 entries:

  • The address to listen on
  • the Gateway to start

On ORAGWSRV01 server at $ORACLE_HOME\network\admin edit the listener.ora as follow:

LISTENER=
               (ADDRESS=
                   (PROTOCOL=TCP)
                   (HOST=host_name)
                   (PORT=port_number))

SID_LIST_LISTENER=
      (SID_LIST=
           (SID_DESC=
                 (SID_NAME=gateway_sid)
                 (ORACLE_HOME=oracle_home)
                 (PROGRAM=tg4msql)
           ) 
      )

Where:
host_name - is the server name on which the gateway is installed
port_number - is the listener port
gateway_sid - is the gateway SID (default is tg4msql)
oracle_home - is the Oracle directory on which the gateway is installed

In our case:

LISTENER=
              (ADDRESS= 
              (PROTOCOL=TCP)
              (HOST=oragwsrv01)
              (PORT=1521))

SID_LIST_LISTENER=
      (SID_LIST=
          (SID_DESC= 
               (SID_NAME=tg4msql)
               (ORACLE_HOME=C:\oracle\product\10.2.0)
               (PROGRAM=tg4msql)
          )
      )

After finishing the Gateway configuration, restart the listener (lsnrctl stop; lsnrctl start).

Configure the Oracle database tnsnames.ora

Now, when the Gateway configured, we need to configure the Oracle database server to communicate with the Gateway via Oracle Net services.
On ORCLSRV01 server at $TNS_ADMIN ($ORACLE_HOME/network/admin) edit the tnsnames.ora file and add the Gateway's connect descriptor as follow:

connect_descriptor=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=host_name)
         (PORT=port_number)
      )
      (CONNECT_DATA=
         (SID=gateway_sid))
      (HS=OK))

Where:

connect_descriptor - you can specify a name which will be used later when we create a db link
host_name - is the server on which the Gateway installed
port_number - is the port used by the listener that listening for the Gateway
gateway_sid - is the gateway SID (default tg4msql)
HS=OK - indicates this connect descriptor is using heterogeneous Services

In our case:

mytg4msql=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=oragwsrv01)
         (PORT=1521)
      )
      (CONNECT_DATA=
         (SID=tg4msql))
      (HS=OK))

Creating DB link from Oracle database to MS SQL Server

Now, when all configurations were done, we only need to create a Database Link from Oracle to the Gateway.
Connect to orcldb database and create a db link as follow:

create database link ORCLDB2MSQLDB
  connect to [MSQLUSER] identified by [MSQLPWD]
  using 'mytg4msql';

Where MSQLUSER & MSQLPWD are the user name and password to connect to the MS SQL server.

Test it...

At this stage we can get data from MS SQL Server via Oracle Database as if we query from Oracle database.

For example, if we have a RESERVATIONS table in MSQLDB, we can retrieve RESERVATIONS rows from ORCLDB by issuing the following SQL statement:

select *
from reservations@ORCLDB2MSQLDB;

For more information about Transparent Gateway for Microsoft SQL Server:
Oracle® Transparent Gateway for Microsoft SQL Server Administrator's Guide 10g Release 2 (10.2) for Microsoft Windows (32-bit)

For more information about other Transparent Gateways for other Database systems:
Oracle Database Documentation Library 10g release 2 (10.2) - Information Integration

You are welcome to leave a comment.

Aviad

Categories: APPS Blogs

NoCOUG conference 8/21/08

Claudia Zeiler - Wed, 2008-08-20 08:09
For those in the San Francisco Bay Area. The NoCoug Congerence is tomorrow Thursday 8/21. Cary Millsap is the keynote speaker. All are invited.


Summer Conference 2008
Hosted by Chevron August 21, 2008 at Chevron, San Ramon, CA


See directions to the conference location near the bottom of this page.

If you will be coming, then please RSVP online now. Remember, conference attendance is free for NoCOUG members and $40 for non-members.

8:00 - 9:00 Registration and Continental Breakfast - Refreshments Served 9:00 - 9:30 General Session and Welcome - Roger Schrag, NoCOUG President 9:30 - 10:30 Keynote: Millsap's Grand Unified Theory of "Tuning" - Cary Millsap, Method-R.com 10:30 - 11:00 Break Room 1220 Room 1240 Room 1130 11:00 - 12:00
Session 1
Case Studies in Performance Problem Diagnosis and Repair by Cary Millsap, Method-R.com Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros by Greg Pike, Piocon Architecting Data Systems for Compliance by John Weathington, Excellent Management Systems 12:00 - 1:00 Lunch 1:00 - 2:00
Session 2
Getting Coherence: Introduction to Data Grids by Raanan Dagan, Oracle Data Warehousing with Oracle 11g by George Lumpkin, Oracle Corporation Building a Web-based Application Using Application Express by Willie Albino, Lockheed Martin 2:00 - 2:30 Break and Refreshments 2:30 - 3:30
Session 3
Power at Your Fingertips - Overlooked Gems in Oracle Enterprise Manager by John Sheaffer, Oracle Corporation Introduction to Java-PL/SQL Developers Take Heart by Peter Koletzke, Quovera Oracle Fusion Middleware Roadmap & Strategy by Margaret Lee, Oracle Corporation 3:30 - 4:00 Raffle
In the vendor area. 4:00 - 5:00
Session 4
A Tour of the AWR Tables by Dave Abercrombie, Convio What the Oracle Really Meant: The Quest for PL/SQL Testing Using Code Tester by Arnie Weinstein, Lawrence Livermore National Laboratory Oracle Analytical Functions - the hidden treasure by Ron Warshawsky, DBA Infopower 5:00 - ??? NoCOUG networking and happy hour at San Ramon Marriott, 2600 Bishop Dr., San Ramon, CA 94583 -- (925) 244-6171

Directions: Leaving the conference, go straight across Bollinger onto Camino Ramon. Turn left onto Bishop Drive. The Marriott is less than half a mile, on the left.
*** Walk through the bar to the outside patio. ***

Mark your calendar for NoCOUG's Fall Conference:
November 13, 2008 at CarrAmerica Conference Center in Pleasanton.

Speaker Abstracts for Spring Conference

Keynote “Millsap's Grand Unified Theory of "Tuning"” - Cary Millsap, Method-R.com


First there were the v$ and x$ views. And tkprof. Then came bstat/estat, Statspack, ASH, ADDM, OEM, and plenty of third-party tools that look kind of like those tools. There are loads of OS tools too that have been around a long time, like top and sar and strace and pstack and gprof. And then there are the methods: OPIM, and YAPP, and Method R to name three. In this presentation, Cary Millsap gives a brief tour of the moving parts and reveals his own perspective about how all these parts should fit together.

Room 1220 “Case Studies in Performance Problem Diagnosis and Repair” - Cary Millsap, Method-R.com


While we certainly learn from our own experiences, we can save ourselves plenty of time and frustration if we can learn from the experiences of others. This presentation reviews the real life experiences of professionals like you who faced some nasty performance problems and lived to tell the tale. Come experience their journeys as a spectator so that next time, when you're faced with a similar problem, you may have just the information you need to handle it with greater ease and efficiency.

“Getting Coherence: Introduction to Data Grids” - Raanan Dagan, Oracle


Grid-based infrastructures are being developed, deployed and used to achieve unlimited application scalability and continuous availability across multiple datacenters. Understanding the additional capabilities of these infrastructures and how they can be improved with the use of Data Grid technology to solve increasingly difficult and complex problems ensures that your organization is getting the maximum utility from Grid Computing.

This session focuses on how Oracle Coherence Data Grid can easily help you achieve all of these goals and more!

“Power at Your Fingertips - Overlooked Gems in Oracle Enterprise Manager” - John Sheaffer, Oracle Corporation


Today's DBA is increasingly tasked with providing greater support across the enterprise - in some cases, being stretched so thin that leveraging automated tools is your only survival mechanism. In this session, you will learn about how often overlooked features of Oracle Enterprise Manager will help you improve efficiency, lower your blood pressure, and get you home on time!

“A Tour of the AWR Tables” - Dave Abercrombie, Convio


Introduced in version 10g, Oracle's Automatic Workload Repository (AWR) provides diagnostic information for performance and scalability studies, automatically recording a rich variety of database performance statistics.

What's the best way to leverage this wealth of data? While you can run Oracle-supplied AWR reports, or use Oracle features such as the Automatic Database Diagnostic Monitor (ADDM), each Oracle database presents its own unique tuning challenges. In this session you'll learn how to work directly with AWR tables, using customized queries to improve insight into your own particular scalability issues.

Topics include:

  • Important AWR tables, their contents, how to join them, and their quirks and limitations.
  • Sample queries that can be easily adapted to focus on your own unique set of problems.
  • Estimating the "Average Active Session" metric.
  • Simple statistical techniques to find spikes and other types of anomalous behavior.
  • A comparison of techniques used for historical scalability studies with those used for real-time performance crisis resolution..
  • Use of DBMS_APPLICATION_INFO and JDBC end-to-end metrics.
  • Useful tips on configuring AWR.
Room 1240 “Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros” - Greg Pike, Piocon


Although seasoned professionals understand the benefits of solving business problems with efficient queries or PL/SQL, the volunteer experts and Oracle Ace contributors on OTN's SQL and PL/SQL discussion forums raise the query writing bar to an entirely new level. Oracle professionals at any skill level will find this forum packed with a treasure chest of tips, tricks and techniques. With over 60,000 topics and 325,000 posts, it's the mother lode of SQL and PL/SQL education from recognized experts worldwide.

In this session, the powerful techniques of advanced query authoring are explored by reviewing real-world forum threads and the unique solutions posted by the gurus. The resident experts from the OTN forums solve problems using a combination of analytic functions, hierarchical queries (CONNECT BY), collections (COLLECT ), XML functions/operators (SYS_XMLGEN, and XMLSEQUENCE), Pipelined Functions, the MODEL clause and more.

“Data Warehousing with Oracle 11g” - George Lumpkin, Oracle Corporation


Satisfying business intelligence requirements for all users throughout the enterprise requires a fast, reliable and scalable data warehouse to protect and maintain quality business information. Come learn why Oracle is the #1 database for data warehousing, why Oracle Warehouse Builder is the best tool for building data warehouses, how ground-breaking new features in Oracle Database 11g will dramatically speed query performance, and, how you can accelerate data warehousing deployments.

“Introduction to Java-PL/SQL Developers Take Heart” - Peter Koletzke, Quovera


Oracle's current focus on implementing database and development features based on the Java language may have you thinking that you need to learn Java. However, if you are familiar with PL/SQL, your first view of Java may be a bit discouraging because its object-oriented core makes it look very different. Also, you may be wonder about Java's strengths and weaknesses and where it fits in the industry.

This presentation explains to PL/SQL developers who have had little or no exposure to Java, the basic concepts of, and terms used in Java. The presentation provides an overview of the language and reviews the concepts of object orientation upon which Java is based. It also discusses the fundamental Java code structures -- classes and methods -- as well as control statements; exception handling; data types; and variables. This explanation will act as a springboard for further study. Level: Beginner

“What the Oracle Really Meant: The Quest for PL/SQL Testing Using Code Tester” - Arnie Weinstein, Lawrence Livermore National Laboratory


Software developers face constant pressure to produce highly complex PL/SQL code under tight deadlines. Without an efficient and reliable way to perform thorough code testing, software is released with defects that would otherwise be eliminated. Using an automated test tool carries risks that may be mitigated by certain practices. These practices greatly improved our ability to develop high quality and efficient testing software. In this paper, we will share some of these practices

Room 1130 “Architecting Data Systems for Compliance” - John Weathington, Excellent Management Systems


A gentle introduction to compliance for database system architects, and introductory concepts for the architecture that's optimal for supporting the growing need for your company's compliance.

“Building a Web-based Application Using Application Express” - Willie Albino, Lockheed Martin


Application Express is a free web application development environment that comes with the Oracle database (Oracle 9.0.3+). Using a simple web browser, you can create and deploy web-based, database-centric applications very quickly. In addition you can upload and create applications from MS Excel spreadsheets. In this session, an introduction to the Application Express environment will be provided. In addition, an application will be built from tables existing in the database, as well as data uploaded from some Excel spreadsheets.

“Oracle Fusion Middleware Roadmap & Strategy” - Margaret Lee, Oracle Corporation


With the recent acquisition of BEA, a number of best-in-class products have been added to Oracle's robust portfolio of middleware solutions. Customers are interested in knowing what Oracle's strategy and roadmap will be around protecting existing BEA customers' investments and integrating BEA products into existing Fusion Middleware solutions. This session will detail how Oracle will leverage best products from both companies to provide solutions in Java & Transaction Processing, SOA and Business Process Management, and User Interaction and Enterprise 2.0.

“Oracle Analytical Functions - the hidden treasure” - Ron Warshawsky, DBA Infopower


Oracle introduced them in 8i, but do we really use them to their full potential? Oracle analytical functional can save great deal of coding and provide amazing results very fast.

How about doing this - and this is only a beginning:

  • Ranking data within subsets of a data set.
  • Aggregating data within subsets of a data set.
  • Performing aggregations over moving windows.
  • Displaying and comparing aggregates to individual entries within a single query.
  • Comparing two or more rows within a given data set.

If you have suggestions for future meetings or would like to offer feedback on previous conferences, then please complete our online survey or send us an email.

Directions to Chevron Park in San Ramon:

Address:
6101 Bollinger Canyon Road, San Ramon, CA 94583

Please note that the conference building is just after the Chevron Campus.
Upon arrival, enter the building at the East entrance where you'll find NoCOUG representatives ready to sign you in.

From Highway 680 South:
Exit at Bollinger Canyon Road. Turn left onto Bollinger Canyon Road (heading east over the freeway). After passing Chevron Park Circle West, turn right into Bishop Ranch 1. Address 6101 is the first building after entering the parking lot. Park anywhere not restricted.

From Highway 680 North:
Exit at Bollinger Canyon Road. Turn right onto Bollinger Canyon Road (heading east). After passing Chevron Park Circle West, turn right into Bishop Ranch 1. Address 6101 is the first building after entering the parking lot. Park anywhere not restricted.
Map

Copyright © 2008 NoCOUG. All rights reserved.

AWR Usage Poll

Jared Still - Mon, 2008-08-18 14:04
A number of recent threads in the Oracle-L list have made it pretty clear that Automated Workload Repository (AWR) is a tool that you are expected to use when troubleshooting a database problem.

Never mind the fact that AWR is still a product that is licensed separately from the database, and that a large segment of the Oracle DBA population doesn't seem to realize that. Or that Active Session History (ASH) is part of AWR, and falls under the same license restrictions.

So I conducted a poll regarding the use of AWR. AWR Usage Poll. If you haven't in the AWR Poll, please do so.

While the web site does provide a chart of results, those results don't include the extra comments made by poll takers. You may are may not be able to download all the results, I'm not sure if that is restricted to the poll owner.

Nonetheless, I have compiled the results from a 100 or so respondents in to an Excel workbook, along with a few charts. You may find some of the additional comments of interest as well. AWR Usage Results

Draw your own conclusions regarding these results. I think it interesting to that AWR appears to be quite widely used. Personally I fall into the category of not using it because of the expense. I may work on changing that for a couple of key servers, as AWR is not that expensive, but in a small shop, spending $20k on feature that is not often needed is sometimes a hard sell.

One question I purposely left out was "Do you use AWR even though you have not licensed it"? While it might satisfy the curiosity of some (including me) I didn't want to give any Oracle sales people (or Oracle attorneys for that matter) any reasons to contact me regarding the poll.

In retrospect a good question would have been: "Did you realize AWR/ASH is a separately licensed product?". Too late to add that now, but bringing that up quite often leads to lively discussion.

Another interesting bit was that a few people have extended STATSPACK in some way, even using it on Oracle 10g+. One even mentioned the excellent repository of statspack scripts assembled by Tim Gorman. Tim Gorman's Statspack Scripts
Categories: DBA Blogs

Matrix Inversion Using PL/SQL

Marcos Campos - Mon, 2008-08-18 07:18
Recently someone asked me how to invert a matrix using the UTL_NLA PL/SQL package. This can be done by solving a system of linear equations AX = B like the ones I described in this post but setting B to the identity matrix. I thought that the question was interesting enough to deserve its own post. So I am replicating the answer here for those interested. The UTL_NLA package has many different Marcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com5
Categories: BI & Warehousing

Steven Chan's "Identity Crisis: Are Patches to EBS 11.5.9 Equivalent to 11.5.10?"

Madhu Thatamsetty - Sun, 2008-08-17 21:55
I was going through Steven Chan's "Identity Crisis: Are Patches to EBS 11.5.9 Equivalent to 11.5.10? - very well explained from Oracle and Support perspective for Apps customers, gave me a food for thought and hence this post of mine.From Apps DBA perspective, the way to resolve this confusion is to follow the simple checks.#1. Check the release name/Applications version using the sql .select Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

Adding Cost Center value to the OTL Timecard

RameshKumar Shanmugam - Sun, 2008-08-17 21:35
The common requirement in any timecard configuration is to have a cost center field to charge the time against. By default in OTL Oracle provides the cost center field but for displaying the values in the Timecard you need to configure the ltd file and create a View to hold the cost center value

Following code can be used to create the view
CREATE OR REPLACE FORCE VIEW HXC_CUI_CUSTOM9_V ( display_value ,value ) AS select fv.flex_value'-'fv.DESCRIPTION display_value, to_char(flex_value_id) value from fnd_flex_values_vl fv, fnd_flex_value_sets fvs where fv.flex_value_set_id = fvs.flex_value_set_id and fvs.flex_value_set_name = 'GL_COA_COST_CENTER'

Following are the three layout needed to be modified

Payroll layout - hxczzhxclayt0000.ldt
Payroll Review page - hxczzhxclayt0005.ldt
Payroll Confirmation page - hxczzhxclayt0001.ldt

Replace the cost center segment with the below code

Modify the Payroll layout - hxczzhxclayt0000.ldt

BEGIN HXC_LAYOUT_COMPONENTS "Payroll Timecard Layout - Cost Center"OWNER = "CUSTOM"COMPONENT_VALUE = "COSTCENTER"REGION_CODE = "HXC_CUI_TIMECARD"REGION_CODE_APP_SHORT_NAME = "HXC"ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER"ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC"SEQUENCE = "190"COMPONENT_DEFINITION = "CHOICE_LIST"RENDER_TYPE = "WEB"PARENT_COMPONENT ="NEC Payroll Timecard Layout - Day Scope Building blocks for worker timecard matrix"LAST_UPDATE_DATE = "2004/05/24"
BEGIN HXC_LAYOUT_COMP_QUALIFIERS "NEC Payroll Timecard Layout - Cost Center"OWNER = "CUSTOM"QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST"QUALIFIER_ATTRIBUTE1 = "Custom9VO" ########Changed to Custm9VO ##############QUALIFIER_ATTRIBUTE4 = "N"QUALIFIER_ATTRIBUTE8 = "DisplayValue"QUALIFIER_ATTRIBUTE9 = "Value#NUMBER"
QUALIFIER_ATTRIBUTE10 ="oracle.apps.hxc.selfservice.timecard.server.Custom9VO" ########Changed to Custm9VO ##############
QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATETIMECARD_BIND_END_DATE"QUALIFIER_ATTRIBUTE17 = "OraTableCellText"QUALIFIER_ATTRIBUTE20 = "N"QUALIFIER_ATTRIBUTE21 = "Y"QUALIFIER_ATTRIBUTE22 = "L"QUALIFIER_ATTRIBUTE25 = "FLEX"QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context"QUALIFIER_ATTRIBUTE27 = "Attribute2"LAST_UPDATE_DATE = "2004/05/24"END HXC_LAYOUT_COMP_QUALIFIERS
END HXC_LAYOUT_COMPONENTS

Modify the Payroll Review page - hxczzhxclayt0005.ldt

BEGIN HXC_LAYOUT_COMPONENTS "Payroll Review Layout - Cost Center" OWNER = "CUSTOM" COMPONENT_VALUE = "COSTCENTER" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "160" COMPONENT_DEFINITION = "CHOICE_LIST" RENDER_TYPE = "WEB" PARENT_COMPONENT = "NEC Payroll Review Layout - Day building blocks - matrix layout" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "NEC NEC Payroll Review Layout - Cost Center" OWNER = "CUSTOM" QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST" QUALIFIER_ATTRIBUTE1 = "Custom9VO" #QUALIFIER_ATTRIBUTE8 = "DisplayValue" #QUALIFIER_ATTRIBUTE9 = "Value#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.Custom9VO" #QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATETIMECARD_BIND_END_DATE" #QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context" QUALIFIER_ATTRIBUTE27 = "Attribute2" QUALIFIER_ATTRIBUTE30 = "Y" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS

Modify Payroll Confirmation page - hxczzhxclayt0001.ldt

BEGIN HXC_LAYOUT_COMPONENTS "Payroll Confirmation Layout - Cost Center" OWNER = "CUSTOM" COMPONENT_VALUE = "COSTCENTER" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "210" COMPONENT_DEFINITION = "CHOICE_LIST" RENDER_TYPE = "WEB" PARENT_COMPONENT = "NEC Payroll Confirmation Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "NEC NEC Payroll Confirmation Layout - Cost Center" OWNER = "CUSTOM" QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST" QUALIFIER_ATTRIBUTE1 = "Custom9VO" #QUALIFIER_ATTRIBUTE8 = "DisplayValue" #QUALIFIER_ATTRIBUTE9 = "Value#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.Custom9VO" #QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATETIMECARD_BIND_END_DATE" #QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context" QUALIFIER_ATTRIBUTE27 = "Attribute2" QUALIFIER_ATTRIBUTE30 = "Y" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS
Categories: APPS Blogs

Oracle at KDD 2008 and KDD 2007 Pictures

Marcos Campos - Fri, 2008-08-15 22:34
It is that time of the year again. In about a week I am going to be attending the KDD (Knowledge Discovery in Databases) 2008 conference (conference website) along with some other Oracle colleagues. KDD is one of the primary conferences on data mining. This year it will take place in Las Vegas, Nevada, from August 24 to 27.Oracle is a Gold sponsor for the event and will have again a large Marcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com0
Categories: BI & Warehousing

Introduction to Oracle User Productivity Kit (UPK)

Richard Byrom - Fri, 2008-08-15 19:46

Oracle’s User Productivity Kit is perhaps one of the less well known of the Software Products on the price list, however just a brief consideration of its name indicates that it may be of considerable value to organisations. We invest significant money and resources in the purchase and implementation of software applications but do we realise, in practice, the levels of productivity that we would like to have and that will bring the full return on our investment?

Information from the industry analysts tells us that most of the problems relating to application implementations are, in fact, user related. Butler Group recently reported that 70 percent of CRM implementations fail, and a Gartner study also found that approximately 55 percent of all CRM projects fail to meet software customers’ expectations.

In his article on why CRM projects fail, Rajiv Chaudhry, a leading analyst, quotes: “You can design the best process in the world, and back it with the latest and greatest technology, but if your people don’t buy into the project, it won’t work.”

User acceptance isn’t the only problem affecting project success; there is also the very basic problem of user competence to use and get the best out of the system. According to Meta Group, 76% of Users have a failing or substandard understanding of new systems software.

Well here is UPK, something that claims to be a productivity kit for our users. Does it do what it says on the tin? Will it help us address these user productivity issues? If so, it is something that we should give attention to? Let’s open the kit and see what is inside and how it can help us.

UPK is a software tool that can capture all the steps in a system process. It records every keystroke, every click of the mouse, each menu option chosen and each button pressed. All this is done in the UPK Recorder by going through the transaction and pressing “printscreen” after every user action. From this, without any further effort from the developer, UPK builds a number of valuable outputs.

The most significant of these is the UPK Player Package. This is an HTML based simulation of the application process that operates in 4 modes.

UPK See It Mode See-It Mode is an automated simulation of the process that can act as a demonstration of the system. It is valuable in the change and communication activity, to demonstrate the system. Usually the final system is not available and access to it is not practical at roadshows and other events. The See-It mode simulation can be played from a USB stick on any laptop with Internet Explorer running. It can therefore be easily taken on the road to demonstrate the system in its most favourable light.

Oracle UPK See It Mode

Because UPK uses a discrete recorder, it only captures the intended and necessary user actions and therefore can play back the transaction with absolute precision and no user errors, mouse jitters or other extraneous elements that can detract from the demonstration. Presenting the application in a clear and uncomplicated way, in a demonstration that can be repeated with absolute certainty every time, is important to the users first view of the new system. You do not get a second chance to make a first impression.

UPK Try It Mode Try-It Mode is also a simulation of the application, but this time the user must interact with the simulation, completing all the actions required rather than watching it run unattended. This mode is most valuable in training; in the classroom or in a self-service learning context. Try-It Mode guides the users through the process, telling them what action to take and highlighting the area on screen where the action is to be performed, at every step through the transaction.

Using Try-It Mode is of great benefit for training, as the same exercise can be trained over and over without the risk of altering the data in the system and causing subsequent operations to fail. It can make the classroom training much more efficient, as when the users are doing the exercises the system is helping them with guidance, freeing the instructor and making the whole exercise more efficient. In addition, it is not necessary to cover every transaction that the user will need during the classroom course, as once users have become familiar with the concept of using UPK for learning, they will be happy to learn further transactions on their own.

One of the difficulties with large training rollouts is that there may be a time gap between the classroom training session and the users’ first time to use the processes after go-live. When UPK is readily available on the system, this ceases to be a problem because they can refresh their knowledge using Try-It mode in UPK.

UPK Know it Mode But how do we know if our users are really learning the system? This is where the third mode of the UPK Player Package comes in. Know-It mode allows the user to go through the process without the on screen guidance. Instead, the system tracks their actions, warning when they stray off course (the system is smart enough to allow alternative ways of navigating the system, such as using the mouse to negotiate the menus or the keyboard shortcut). If the user makes further incorrect attempts, Know-It mode will give more remediation, eventually completing the step for the user. The rub for the user is that at every incorrect action their score is reduced, potentially causing them to fail the test.

The system has usage tracking as standard so it is possible to see which users are making use of the system, in which modes and, if they are using Know-it mode, what their scores were.

UPK Do it Mode The fourth mode of the UPK Player Package is Do-It Mode. This is for Performance Support rather than training. In this mode the user is working on the live application and UPK is hovering in the lower right of their screen, providing visual instruction, in the form of a small video simulation, and written instructions guiding them step by step as they perform their work on the live system.

Oracle UPK Do it Mode

This Do-It mode performance support is instantly accessible from the application help menu or a Smart-Help button on the browser. It is context sensitive and, by recognising the screen that the user is on, will offer only the appropriate help for that step of the process.

When users are shown Do-it Mode as part of a Change and Communication event, or in the classroom, it significantly increases confidence in their ability to use the system and, as a consequence, they are much more positive about the introduction of the new system.

It is, of course, important to provide users with more information than just how to follow the steps of the transaction. UPK provides many facilities to include a wealth of other information in the simulations. Information and explanations can be included at every step of the process. This can be achieved by incorporating additional material or by linking to pre-existing material. For example, on an i-Expenses screen it may be useful to link to the organisation’s travel policy or to some relevant section of it.

Incorporating this information is the work of a UPK development team. UPK content development is a skilled job where, in addition to recording the process flow, the best and most relevant support information is linked in at every step.

From the above you can see how the UPK Player Package can improve user productivity and reduce project risk significantly, if used in an experienced and intelligent way. However, UPK does more than it says on the tin. As well as improving user productivity, it can significantly improve project team productivity. This is because all the information recorded and linked in is extremely valuable across the project lifecycle when output in other appropriate forms.

UPK works within a Single Authoring Session

UPK can directly output Business Process Documents, which are a specification of the transaction. These can, in many cases, be a requirement for compliance and a critical part of the system specification that is often overlooked in a busy project implementation, or created retrospectively as an after thought.

UPK can also output Test Scripts, either to support UAT or the System testing activity. It can generate scripts ready to be input directly to HP Quality Centre.

Finally, UPK can output Instructor manuals, student guides and quick reference cards directly from the same single recording exercise. The efficiency that can be gained across the project is obvious in terms of a single recording and development exercise supporting so many project activities. UPK could be financially justified on the generation of test scripts alone for a medium to large implementation.

I have sought to show that UPK not only does what is promised, but in fact brings many more benefits if used early in the project, and across the project lifecycle. It addresses the most critical and often neglected areas of user acceptance and user competence.

Project Lifecycle

Like all powerful software tools, it is best used by experienced developers who know how to set up and manage a content development project and how to create good quality UPK content.

About Larmer Brown Consulting

Thanks to Larmer Brown Consulting for allowing this article to be posted on the Blog. Larmer Brown’s primary business objective is to help organisations realise the desired business benefit from their enterprise applications, through successful User Adoption Programmes. They facilitate the Change and Communication, User Acceptance Testing, Training and Support activities. Our approach is based on Global Knowledge’s OnDemand Personal Navigator (ODPN) platform, which is also marketed under the name User Productivity Kit (UPK), by Oracle, and Computer Associates Proficiency Accelerator (CAPA), by Computer Associates.

About the Author

Peter McClintock is Director of Services for Larmer Brown Consulting, UPK Training and Project Specialists. He has worked in the Projects, Consulting and Training business for over 20 years. He can be contacted on services@larmerbrown.com

vi for Apps DBAs (2)

Madhu Thatamsetty - Thu, 2008-08-14 04:23
Happy Independence Day !! - August 15th 2008. Its been a year I started blogging. I started my first post with vi for Apps DBAs on Aug 15th 2007. It was a great experience being part of the online community. I admire and I'm inspired by seeing the time and efforts put in by the fellow bloggers in the world to share their experiences. No wonder blogging is a great platform to convey, express and Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

DB Link to Oracle 11g

Pawel Barut - Wed, 2008-08-13 13:59
Written by Paweł Barut
As you know in Oracle 11g passwords are case sensitive by default. This applies to connecting via SQL*Plus or other client tools. And it also applies to database links between databases. So when you link from Oracle 10g to Oracle 11g create database link like this:
CREATE DATABASE LINK my_link
CONNECT TO remote_user IDENTIFIED BY "CaSe_SeNsItIvE_PaSsWoRd"
USING 'TNS_ID_11G';

Do not forget to enclose password by double-quote marks!
When you do not set password this way, you will be getting:
ORA-01017: invalid username/password; logon denied.
Hope this small tip will be useful for someone.

Paweł

More on Creating Database Links.

--
Related Articles on Paweł Barut blog:
Categories: Development

DD Tips and Techniques - 2 Displaying Shapes

Susan Duncan - Tue, 2008-08-12 09:18
When I said occasional in the last blog - I didn't specify the time interval - so here is a second post in as many minutes!

There are a number of ways to tidy a cluttered diagram. Sometimes you don't want to see all the columns/constraints in the table shape, or perhaps you only want to see some of the columns - the keys or the most important columns:

View As -> Compact
Select shapes in a diagram and use this context menu to completely remove any columns or constraints from the shape

Hide Selected Shapes
If you want to show only some of the members of your shape (for instance only the keys and major columns in a table) select members you want to hide and use this context menu. The members are cut from the diagram but are still available to you through the shape editor. To see all the members again use Show All Hidden Members

Tools -> Preferences -> Diagrams -> Database
Under this node you will find many different options for your database diagram - you can change font, shape colors for many different database shapes. For instance - select Table from the dropdown list and uncheck Show Constraints if you want to exclude them from the diagram. Note that changes made in this way only apply to new shapes added to a diagram. So it's good practice to check your preferences before you start work on a diagram



EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet

Madhu Thatamsetty - Tue, 2008-08-12 08:53
I ran into an issue where agent was not uploading the xmls to the OMS server. emctl upload fails with the error "EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..."To resolve the issue try the following. Check if the host is able to communicate with the OMS by using telnet as mentioned below. If you punch in "^]" escape sequence, you will receive Apache version that OMS isMadhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

Database Diagramming Tips and Techniques - 1 Keyboard Accelerators

Susan Duncan - Tue, 2008-08-12 07:03
I'm working on a new database design and thought I'd pass on some of the tips that I use to aid my work. As always with my blog entries, this will be an occasional series.

Keyboard Accelerators - diagram cleaning aid

I often create new offline database tables using a database diagram. I can visualize and edit objects in-place easily and often. But having to resize the shapes and tidy up the relationship lines can be very frustrating! So I do the following:

First the setup -
Go into Tools -> Preferences -> Accelerators
Scroll down the Actions list and select Height and Width
Add a new accelerator (if none set) - I use Ctrl H
Scroll to Select All and check what accelerator is set - default is Ctrl A
Scroll to Straighten Lines and add an accelerator - say Ctrl L

Now, in my diagram whenever I can't see all the info in my table shapes I either select the specific tables and do Ctrl H or do Ctrl A + Ctrl H to resize all my shapes followed by Ctrl L to straighten the lines.

Easy Tip No1!

My agenda at OpenWorld 2008

Oracle Apex Notebook - Mon, 2008-08-11 07:48
Essentially, at OpenWorld I'll be looking for everything related with APEX. Unfortunately it's impossible to attend all APEX related sessions, because (fortunately) there is a huge number of interesting sessions and some of them share the same time schedule. David Peake has built an application that puts together all APEX sessions (good work btw) which helps picking the sessions. Beyond the
Categories: Development

Oracle E-Business Tax: Regime to Rate Flow in Oracle R12

Krishanu Bose - Mon, 2008-08-11 07:46

In Oracle Release12, there has been some very significant changes, one among them being how we define tax. E-Business Tax is now a single point where we define our taxes for all the sub-ledger modules. In this article I will try to do a sample Tax setup using the Regime to Rate Flow of Oracle E-Business Tax in the Tax Manager responsibility.

Creating a Tax Regime

In Oracle E-Business Tax, a Tax Regime is the system of regulations for the determination and administration of one or more taxes.

  1. Navigate to the Create Tax Regime:

• Navigation: Tax Configuration àTax Regimes

• Click the Button “Create”

  1. Enter a new tax regime based on the following information:

· Tax Regime Code: TESTREGIME1

· Name: Test Regime1

· Regime Level: Country

· Country Name: United States

· Parent Regime Code: Blank

· Effective From: 01-JAN-1950

· Effective To: Leave Blank

· Used to group Regimes: Unchecked

  1. Expand the Controls and Defaults region

· Allow Tax Recovery: Checked

· Allow Override and Entry of Inclusive Tax Lines: Unchecked

· Allow Tax Exemptions: Checked

· Allow Tax Exceptions: Checked

· Tax Currency: USD

· Minimum Accountable Unit: 0.01

· Rounding Rule: Nearest

· Tax Precision: 2 (default)

  1. Click on Button “Continue”
  2. Enter tax regime configuration options:
    • Party Name: Vision Operations (Note: Select the Operating Unit owning Tax Content party type)
    • Configuration for Taxes and Rules: Common Configuration with Party Overrides
    • Configuration for Product Exceptions: Party-Specific Configuration
    • Effective From: 01-JAN-1950
    • Effective To: Leave blank
  3. Click on Button “Finish”
  4. You will get the message “The tax regime was successfully created”

Creating a Tax

  1. Navigate: Tax Configuration à Tax Regimes
  2. Search for the Tax Regime you created above using the filter criteria of Country as “United States” and Tax Regime Code as “TESTREGIME1”
  3. Click on button “Go”
  4. Click on icon “Regime to Rate Flow”
  5. Click on button “Create Tax”

  1. 2Create a new Tax as per information mentioned below:
    • Tax Regime Code: TESTREGIME1 (defaults)
    • Configuration Owner: Global Configuration Owner
    • Tax Source: Create a new tax (defaults)
    • Tax: TESTTAX1
    • Tax Name: Test Tax1
    • Tax Type: SALES
    • Effective From: 01-JAN-1950 (defaults)
    • Effective To: Leave blank
    • Geography Type: STATE
    • Parent Geography Type: COUNTRY
    • Parent Geography Name: United States
    • Tax Currency: USD (defaults)
    • Minimum Accountable Unit: 0.01 (defaults)
    • Rounding Rule: Nearest (defaults)
    • Tax Precision: 2 (defaults)
    • Exchange Rate Type: Blank
  2. Click on “Show Controls and Defaults”
  3. Enter the details as per information mentioned below
    • Allow Override and Entry of Inclusive Tax Lines: Unchecked
    • Allow Tax Rounding Override: Unchecked
    • Allow Override for Calculated Tax Lines: Checked
    • Allow Entry of Manual Tax Lines: Checked
    • Use Legal Registration Number: Unchecked
    • Allow Duplicate Tax Registration Numbers: Unchecked
    • Allow Multiple Jurisdictions: Unchecked
    • Tax Accounts Creation Method: Create Tax Accounts (defaults)
    • Allow Tax Exceptions: Checked (defaults)
    • Allow Tax Exemptions: Checked (defaults)
    • Tax Exemptions Creation Method: Create Tax Exemptions (defaults)
    • Allow Tax Recovery: Unchecked

  1. Click on Button “Apply”
  2. You will get the message “The Tax has been successfully created”
  3. Return to Tax Regimes. Click on “Expand All”
  4. Click on icon “Create Tax Status”
  5. Enter the following:
    • Tax Status Code: TESTSTATUS1
    • Name: Test Tax Status1
    • Click on Button “Apply”
    • Check as Default Tax Status: Checked
    • Default Status Effective From: 01-Jan-1950
  6. Click on button “Apply”
  7. You will get the message “The tax status was successfully created.”

Create Tax Rates

  1. Go back to the Regime to Rate Flow
  2. Click on “Expand All”.
  3. Click on icon “Create Tax Rate”. Enter the Tax Rates based on information mentioned below:
    • Tax Regime Code: TESTREGIME1 (defaults)
    • Configuration Owner: Global Configuration Owner (defaults)
    • Tax: TESTTAX1 (defaults)
    • Tax Status Code: TESTSTATUS1 (defaults)
    • Tax Jurisdiction Code: Blank
    • Tax Rate Code: TESTRATE1
    • Rate Type: Percentage (defaults)
    • Percentage Rate: 10
    • Effective From: 01-JAN-1950 (defaults)
    • Effective From: Leave blank

  1. Click on icon “Rate Details”. Enter the Tax Rate Details based on information mentioned below:

· Tax Rate Name: Test Sales Tax Rate1

· Tax Rate Description: Test Sales Tax Rate1

· Set as Default Rate: Checked

· Default Effective From: 01-JAN-1950

· Default Effective To: Leave blank

· Allow Tax Exemptions: Checked (defaults)

· Allow Tax Exceptions: Checked (defaults)

· Internet Expenses Enabled : Checked

  1. Click on the button “Apply” to return to the Create Tax Rate page.
  2. Click on the button “Apply” to return to the Regime to Rate Flow page
  3. You will get the message “The Tax Rate has been successfully created.”

Creating Tax Jurisdiction

  1. Navigate: tax Configuration à Tax Jurisdiction
  2. Click on button “Create”. Enter the Tax Jurisdiction details based on information mentioned below:
    • Tax Jurisdiction Code: TESTJUR1
    • Tax Jurisdiction Name: Test Tax Jurisdiction1
    • Tax Regime Code: TESTREGIME1
    • Tax: TESTTAX1
    • Geography Type: STATE (pick from list!)
    • Effective From: 01-JAN-1950
    • Effective To: Leave Blank
    • Geography Name: CA
    • Precedence Level: 300 (defaults)
    • Collecting Tax Authority: Blank
    • Reporting Tax Authority: Blank
    • Set as default Tax Jurisdiction: Yes (defaults)
    • Default Effective From: 01-JAN-1950
    • Default Effective To: Leave blank

  1. Click on the button “Apply” to return to the Regime to Rate Flow page
  2. You will get the message “The tax jurisdiction was successfully created.”

Creating Tax Accounts

  1. Navigate: Tax Configuration à Taxes
  2. Search for the Taxes that we created just now using the following filter criteria:
    • Country Name: United States
    • Tax Regime Code: TESTREGIME1
    • Tax: TESTTAX1
  3. Click on the button “Go”. Then click on icon “Update”.
  4. Click on button “Tax Accounts”.
  5. Enter the Ledger as “Vision Operations (USA)”
  6. Click on button “Create”
  7. Enter the Tax Account details based on following information:

· Operating Unit : Vision Operations

· Tax Expense: 01-210-7710-0000-000

· Tax Recoverable/Liability: 01-000-2220-0000-000

  1. Click on button “Apply” and return back to Tax Accounts page.
  2. Click the button “Apply”, to return to the Update Tax page.
  3. Click the button “Apply”, to return to the Taxes page.
  4. You will get the message “The tax was successfully updated”.

Creating Tax Rules

  1. Navigate: Tax Configuration à Tax Rules
  2. Access the rule type by filtering on the following criteria:
    • Configuration Owner: Global Configuration Owner
    • Tax Regime Code: TESTREGIME1
    • Tax: TESTTAX1
  3. Click on button “Go”. Then click on “Expand All”.
  4. Set the following values corresponding to the Rule Types:
    • Determine Place of Supply: Ship To
    • Determine Tax Applicability: Applicable
    • Determine Tax Registration: Bill From Party
    • Determine Taxable Basis: STANDARD_TB
    • Calculate Tax Amounts: STANDARD_TC

Making Tax Available for Transactions

  1. Navigate: Tax Configuration àTaxes
  2. Select the Tax created based on the following filter criteria:
    • Country Name: United States
    • Tax Regime Code: TESTREGIME1
    • Tax: TESTTAX1
  3. Click on button “Go”. Then click on icon “Update” on the “TESTTAX1” row.
  4. Check the box “Make Tax Available for Transactions”

  1. Click on button “Apply”.
  2. You will get the message “This tax does not have an exchange rate type. Are you sure you want to enable this tax?” having options “Yes” and “No”. Click on “Yes”
  3. You will get the message “The tax was successfully updated.”

Test your Tax Setup by creating an Invoice in Payables /Receivables or creating a Purchase Order.

Me at Oracle OpenWorld 2008

Oracle Apex Notebook - Mon, 2008-08-11 03:56
It is my first time at Oracle's big event, the OpenWorld. David Peake was kind enough to invite me to participate in one of the official sessions. So, if you want to meet me, you can find me at San Francisco next September :) Here is the session description, don't forget to register: Session Id: S300458 Title Building Commercial Software-as-a-Service Applications with Oracle Application
Categories: Development

Fast-Start Failover - It is reliable

Mihajlo Tekic - Sun, 2008-08-10 20:45
Last Friday there was one post on OTN forums that brought my attention. The OP was wondering “Is Data Guard Buggy” with attention to Fast-Start Failover (FSFO) feature that provides an ability of automatic failover to the standby database if the primary database is not available for certain time.

He had some concerns about FSFO being unreliable, very difficult to be implemented and actually doesn't work properly.

The OP got some immediate response from the OTN users. I couldn't agree more with Joseph Meeks's comment on the topic.

In my opinion, FSFO is very nice feature that plays big role in Oracle's Maximum Availability Architecture. There might be some valid reasons not to implemented it, but if the automatic failover is a requirement, FSFO is the way to go. Should one have any problems implementing it, the best way to go is to get Oracle Support involved.

In this post, I'd like to show that implementation of the FSFO should not be a difficult task once you have the Data Guard environment set up.

I configured data guard physical standby environment on my laptop. Due to hardware limitations, I'll have the observer running on the same machine with the standby databases. Keep in mind this is just an example. In practice, the primary database, the standby database and the observer should run on different hosts. This example also answers one of the questions OP asked: Will it be possible to set it up on one machine? The answer would be it is possible, as shown in the example :-), but it is not the right way to go.

I use DGMGRL utility in the example..

So, I configured a Data Guard environment where DB1.MYDOMAIN.COM is primary database and STDB.MYDOMAIN.COM is physical standby. Both databases are 10.2.x
By default, protection mode of the Data Guard configuration is set to MAXIMUM PERFORMANCE

DGMGRL> connect sys/***@db1
Connected.
DGMGRL> show configuration

Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
db1 - Primary database
stdb - Physical standby database

Current status for "DRTest":
SUCCESS


Enabling Fast-Start Failover requires the following pre-requisites to be met:
Flashback Database feature is enabled on both the primary and the standby database.
The protection mode of the configuration must be set to MAXIMUM AVAILABILITY
tnsnames.ora in the ORACLE_HOME where the observer runs must be set to see both databases, the primary and the standby.
DGMGRL must be available on the observer host.

Enable Flashback Database
I will enable flashback database feature on both databases. This assumes that the flash recovery area is configured.
The flashback database feature provides an ability for an easy reinstatement of the failed primary database to new standby database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup mount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1261348 bytes
Variable Size 100663516 bytes
Database Buffers 176160768 bytes
Redo Buffers 7127040 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.


Set the protection mode to MAXIMUM AVAILABILITY
The Fast-Start Failover can be enabled only if the protection mode is set to MAXIMUM AVAILABILITY.
LogXptMode has to be set to 'SYNC'

DGMGRL> edit database 'db1'
> set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database 'stdb'
> set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL> edit configuration set protection mode as MaxAvailability;
Succeeded.
DGMGRL> show configuration

Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
db1 - Primary database
stdb - Physical standby database

Current status for "DRTest":
SUCCESS

SQL> conn sys/***@db1 as sysdba
Connected.
SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL> conn sys/***@stdb as sysdba
Connected.
SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL>


I ensured that tnsnames.ora are set correctly as well DGMGRL is installed.

Now, since all the prerequisites are met, lets move forward and enable the FSFO.
Before enabling it, make sure each of the databases in the configuration has set a fast start failover target. This is achieved by setting the FastStartFailoverTarget parameter.


DGMGRL> edit database 'db1' set property faststartfailovertarget='stdb';
Property "faststartfailovertarget" updated
DGMGRL> edit database 'stdb' set property faststartfailovertarget='db1';
Property "faststartfailovertarget" updated


Another important parameter that has to be set is FastStartFailoverThreshold. This parameter specifies the amount of time (in seconds) the observers attempts to reconnect to the primary database before starting the fast-start failover to the standby database. The default value is set to 30 seconds. In the example I set this parameter to 120 seconds.


DGMGRL> edit configuration set property FastStartFailoverThreshold=120;
Property "faststartfailoverthreshold" updated


Now lets enable the fast start failover:


DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.


If you check the configuration at this moment you may find the following warning: ORA-16608: one or more databases have warnings.
If you check the status of one of the databases, you can see this warning: ORA-16819: Fast-Start Failover observer not started


DGMGRL> show configuration

Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
db1 - Primary database
stdb - Physical standby database
- Fast-Start Failover target

Current status for "DRTest":
Warning: ORA-16608: one or more databases have warnings


DGMGRL> show database verbose 'db1'

Database
Name: db1
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
db1

Properties:
InitialConnectIdentifier = 'db1.mydomain.com'
LogXptMode = 'SYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'stdb, db1'
LogFileNameConvert = 'stdb, db1'
FastStartFailoverTarget = 'stdb'
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'miki-laptop'
SidName = 'db1'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=miki-laptop)(PORT=1521))'
StandbyArchiveLocation = 'dgsby_db1'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "db1":
Warning: ORA-16819: Fast-Start Failover observer not started


So lets start the observer. I will repeat again, the observer should run on a different host, however for the sake of this example it will run on the same machine as the databases.
In order to start the observer one should start DGMGRL utility and login to the data guard configuration.
Once logged in, issue START OBSERVER command. This will start the observer.
Optionally you can set a log file destination while invoking DGMGRL utility and specify name for the observer configuration file (for more information check Data Guard Command-Line Interface Reference).
Once the observer is started, the control is not returned to the user until the observer is stopped.


DGMGRL for Linux: Version 10.2.0.3.0 - Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/***@db1
Connected.
DGMGRL> start observer;
Observer started


So lets check the configuration now.


$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/***@db1
Connected.
DGMGRL> show configuration verbose;

Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
db1 - Primary database
stdb - Physical standby database
- Fast-Start Failover target

Fast-Start Failover
Threshold: 120 seconds
Observer: miki-laptop

Current status for "DRTest":
SUCCESS

DGMGRL>


As of this moment my configuration has FSFO enabled.

Now lets test if the FSFO really works.
One should be aware of the conditions that must be satisfied for the observer to attempt FSFO.
The full list of conditions can be found in Data Guard Broker user guide under 5.5.2.1 What Happens When Fast-Start Failover and the Observer Are Running?

I will issue SHUTDOWN ABORT to the primary database (SHUTDOWN NORMAL/IMMEDIATE/TRANSACTIONAL would not trigger the failover).
Once I crash the database, the status of the configuration will return Error: ORA-16625: cannot reach the database. Be aware that since the primary database is down, the only way to check the configuration is to connect using the standby database credentials.


SQL> conn sys/***@db1 as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL>

DGMGRL> connect sys/***@stdb
Connected.
DGMGRL> show configuration

Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
db1 - Primary database
stdb - Physical standby database
- Fast-Start Failover target

Current status for "DRTest":
Error: ORA-16625: cannot reach the database


After waiting for two minutes (FSFO threshold was set to 120 seconds), I checked the observer log file and found out that it started the failover.


[W000 08/09 17:58:49.75] Observer started.

18:05:38.31 Saturday, August 09, 2008
Initiating fast-start failover to database "stdb"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "stdb"
18:06:16.82 Saturday, August 09, 2008



DGMGRL> show configuration verbose

Configuration

Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
db1 - Physical standby database (disabled)
- Fast-Start Failover target
stdb - Primary database

Fast-Start Failover

Threshold: 120 seconds
Observer: miki-laptop

Current status for "DRTest":

Warning: ORA-16608: one or more databases have warnings

DGMGRL>


So the observer started the fast-start failover and has successfully converted the former standby to new primary database.

Once the failover is done the observer will attempt to automatically reinstate the former primary database to new physical standby. Certain requirements have to be met for the automatic reinstatement to take place. If not, the database can be manually reinstated. Once the reinstatement is successful you can see that the former primary database became new physical standby.

DGMGRL> show configuration

Configuration

Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:

db1 - Physical standby database
- Fast-Start Failover target
stdb - Primary database

Current status for "DRTest":

SUCCESS

DGMGRL>


Finally, you can do switchover to perform role change and to place the configuration in its initial state.

The example above shows that enabling fast-start failover is pretty straight forward. There are many good documents with information how to enable FSFO feature. Some of them are listed below:

Oracle® Data Guard Broker 10g Release 2 (10.2) - 5.5 Fast-Start Failover
What Happens When Fast-Start Failover and the Observer Are Running?

7.6 Scenario 5: Enabling Fast-Start Failover and the Observer

Metalink Note#359555.1 IMPLEMENTING FAST-START FAILOVER IN 10GR2 DATAGUARD BROKER ENVIRONMENT

Oracle Maximum Availability Architecture – MAA

I hope I didn't miss something.

Cheers!

Pages

Subscribe to Oracle FAQ aggregator