Skip navigation.

Feed aggregator

New Revenue Opportunity for Video Producers and Videographers

Bradley Brown - Fri, 2014-08-29 09:38
I absolutely love it when we're able to generate income for people who share their knowledge through our platform.  Take my technical training videos (on Oracle Application Express) for example.  It's so cool that I can produce a set of videos, upload it into our platform and sell the material to people around the world - and I get to maintain my brand (I'm not lost in a marketplace) on my own website.  Anyone can be making money in no time.  We see it happening EVERY day!

At the same time, most videos (at least professional videos) are produced by a videographer.  My good friend Will and I used a videographer to create our Sled Like a Pro series which teaches people how to snowmobile.  Traditionally, videographers charge a fee for creating, producing, editing a video.  They might charge $50/hr or $200/hr (or more).  But once the video is finished, they typically turn their work over to someone who creates a DVD or simply uses it as they wish.  Photographers on the other hand often retain the rights to the photo and how you use it.

As a producer of video, what if you could negotiate a royalty on all revenue generated from the product you produced?  What if you could do this without impacting the price of product?  With InteliVideo we allow you to do this.  If you're a reseller of the InteliVideo platform, you'll receive 10% of the net revenue generated - whether it's platform fees or video/product sales.

Be a reseller for InteliVideo!  Sign up for a free account and let us know you're a videographer and that you have an interest in being a reseller.  We'll get you all set up.  We'll provide you with a URL that you can distribute in your emails, which will make sure you get credit for everyone who signs up.

Here's the best part.  Your customers will be able to provide their customers with exactly what they are looking for!  Our platform allows the end customers to watch videos anytime, anywhere.  They can download their video and watch it on a plane, train or automobile.  We protect your customer's content too, so only the app on the device can access the video.

Sign your customers up today!

Log Buffer #386, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2014-08-29 08:16

If you ever wanted an easy-peazy way to get few of the best blog posts of the week from Oracle, SQL Server and MySQL then Log Buffer Editions are the place to be.

Oracle:

The Product Management team have released a knowledge article for Enterprise Performance Management (EPM) 11.1.2.2.x and 11.1.2.3.x containing details for EPM support with Internet Explorer (IE) 11.

As if anyone needs to be reminded, there’s a ridiculous amount of hype surrounding clouds and big data. There’s always oodles of hype around any new technology that is not well understood.

By mapping an external table to some text file, you can view the file contents as if it were data in a database table.

Vikram has discovered a utility adopreports utility in R12.2.

As a lot of the new APEX 5 features are “by developers for developers”, this one is also a nifty little thing that make our lives easier.

SQL Server:

Data Mining: Part 15 Processing Data Mining components with SSIS.

SQL Server AlwaysOn Availability Groups Fail the Initial Failover Test.

Stairway to PowerPivot and DAX – Level 6: The DAX SUM() and SUMX() Functions.

Questions about T-SQL Expressions You Were Too Shy to Ask

SQL Server Service Engine fails to start after applying CU4 for SQL Server 2008 SP1.

MySQL:

MySQL for Visual Studio 1.2.x recently became a GA version. One of the main features included in this version was the new MySQL ASP.NET MVC Wizard.

Resources for Database Clusters: Performance Tuning for HAProxy, Support for MariaDB 10, Technical Blogs & More.

Trawling the binlog with FlexCDC and new FlexCDC plugins for MySQL

InnoDB provides a custom mutex and rw-lock implementation.

You probably already know that Sphinx supports MySQL binary network protocol. But, if you haven’t heard– Sphinx can be accessed with the regular ol’ MySQL API.

Categories: DBA Blogs

Presenting at OOW 2014

DBASolved - Fri, 2014-08-29 07:26

This year I’ll be presenting at Oracle Open World with many of the best in the industry.  If you are going to be in the San Francisco area between September 28 thru October 2 2014, stop by and check out the conference.  Registration information can be found here.

The topics which I’ll be presenting or assisting with this year are:

  • OTN RAC Attack – Sunday, September 28, 2014 – 9 am – 3 pm PST @ OTN Lounge
  • How many ways can I monitor Oracle GoldenGate – Sunday, September 28, 2014 0 3:30 pm – 4:15 pm PST @ Moscone South 309
  • Oracle Exadata’s Exachk and Oracle Enterprise Manager 12c: Keeping Up with Oracle Exadata – Thursday, October 2, 2014 10:45 am – 11:30 am PST @ Moscone South 310

Hope to see you there!

Enjoy!

about.me: http://about.me/dbasolved


Filed under: General
Categories: DBA Blogs

Remote Support for Windows/UNIX/LINUX: Additional Services Series Pt. 5 [VIDEO]

Chris Foot - Fri, 2014-08-29 06:17

Transcript

When outsourcing your operating system support, you want to know that you have expert professionals with knowledge of all your platforms handling your data. At RDX, that’s something you don’t have to worry about.

Welcome back to our Additional Services series!

Whether you use Windows, UNIX or LINUX systems, we support anything and everything an admin does onsite remotely. Our Windows OS tech support includes hardware selection, monitoring and tuning, among many others. We assume total ownership of everything: your server’s security, performance, availability and improvement, and we understand the mutually dependent OS/DB relationship that affects all these things. The same things goes with UNIX and LINUX.

Financially, you pay a single bill for both database and OS support services, and you only pay for the services you need, when you need them.

For more details on our extensive operating system support services, follow the link below. We’ll see you next time!

The post Remote Support for Windows/UNIX/LINUX: Additional Services Series Pt. 5 [VIDEO] appeared first on Remote DBA Experts.

PostgreSQL vs. MySQL: Part Two

Chris Foot - Fri, 2014-08-29 01:34

Part One outlined the histories and basic foundations of PostgreSQL and MySQL, respectively.

In Part Two, we'll focus on the benefits of using both of these structures and how remote DBA professionals use them to perform mission-critical functions for enterprises.

What is a relational database management system?
Before going into further detail on PostgreSQL and MySQL, it's important to define what RDMS is, as both of these systems subscribe to this model. According to DigitalOcean, RDMS stores information by identifying related pieces of data to form comprehensive sets, or schemas. The tables are easily queried by data analysts, applications and other entities because they are made of columns defined by attributes held in rows.

MySQL: Support, advantages and drawbacks

As Carla Schroder of OpenLogic noted, MySQL is a solid choice for IT professionals working with Web architectures. It's capable of organizing unstructured information, such as the kind of data found on Twitter, Facebook and Wikipedia (all of which are powered by MySQL). DigitalOcean asserted the platform possesses sound security functions for data access and tasks that are easy to perform.

As for the disadvantages, the latter source acknowledged MySQL can sanction read tasks really well but falls somewhat short when it comes to read-write. In addition, the platform lacks a full-text search component.

PostgreSQL: Support, advantages and drawbacks
DigitalOcean maintained PostgreSQL can handle a large variety of responsibilities

quite efficiently due to its high programmability and ACID compliance. Users can implement custom procedures, a few of which can be developed to simplify intricate, common database administration operations. Because it works objectively, it can support nesting and other powerful features. Complex, customized tasks can be easily implemented and deployed.

What are its shortcomings? For one thing, it's difficult for people to find hosting services for PostgreSQL because of the sheer amount of variations. Also, its read-heavy operations can be "overkill" as DigitalOcean described it.

The post PostgreSQL vs. MySQL: Part Two appeared first on Remote DBA Experts.

Going to Oracle Open World? PeopleSoft Your Primary Interest?

PeopleSoft Technology Blog - Thu, 2014-08-28 15:33
We look forward to Oracle Open World every year for a number of reasons.  Chief among them is the opportunity to interact with customers and partners in person.  We also relish the opportunity to show you the latest PeopleSoft applications and tools--the stuff we've been working on over the past year.  If you are attending the conference and building your schedule, there is a handy document on-line that provides information on most or all of the PeopleSoft-focused activities at the conference, including sessions/presentations, meet the experts, demos, exhibition schedules, SIG meetings, user group gatherings and receptions, and more.  It's going to be a great week!  Hope to see you there.

Monitoring the Filesystem for READONLY mounts using Metric Extension in OEM12c

Arun Bavera - Thu, 2014-08-28 07:29
Our Client faced many times the mounted  filesystem going into READONLY status.
We created this User Defined Metrics or now called as Metric Extesnion to monitor and send alert.
image

image

image

#!/bin/sh
#echo "SlNo MountPoint MountStatus"
nl  /proc/mounts |/bin/awk '{print $1"|" $3"|"substr($5,1,2)}'
image

image

Credentials
Host Credentials
: Uses Monitoring Credentials of Target.
 
You have to create a NamedCredential set to test this like this and then set the username and password for this set from Security->Monitoring Credentials:
emcli create_credential_set -set_name=SOA_ORABPEL_STAGE -target_type=oracle_database -auth_target_type=oracle_database -supported_cred_types=DBCreds -monitoring -description='SOA ORABPEL DB Credentials'

Categories: Development

Missing Named Credentials in OEM 12c

Arun Bavera - Thu, 2014-08-28 06:56

We are seeing that the list sometimes doesn’t show all the named credentials.

Yet to see if this resolves the issue but need to restart OMS …

emctl set property -name oracle.sysman.emdrep.creds.region.maxcreds -value 500

Oracle Enterprise Manager Cloud Control 12c Release 3

Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

SYSMAN password:

Property oracle.sysman.emdrep.creds.region.maxcreds has been set to value 500 for all Management Servers

OMS restart is required to reflect the new property value

Ref:

EM 12c: Missing Named Credentials in the Enterprise Manager 12c Cloud Control Jobs Drop Down List (Doc ID 1493690.1)

Categories: Development

PRECOMPUTE_SUBQUERY hint

XTended Oracle SQL - Wed, 2014-08-27 16:01

I’ve just found out that we can specify query block for PRECOMPUTE_SUBQUERY: /*+ precompute_subquery(@sel$2) */
So we can use it now with SQL profiles, SPM baselines and patches.

SQL> select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in (select chr(level) from dual connect by level<=100);

D
-
X

SQL> @last

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c437vsqj7c4jy, child number 0
-------------------------------------
select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in
(select chr(level) from dual connect by level<=100)

Plan hash value: 272002086

---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |      1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("DUMMY"='' OR "DUMMY"='' OR "DUMMY"='♥' OR "DUMMY"='♦'
              OR "DUMMY"='♣' OR "DUMMY"='♠' OR "DUMMY"='' OR "DUMMY"=' OR
              "DUMMY"=' ' OR "DUMMY"=' ' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=' '
              OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='►' OR "DUMMY"='◄' OR
              "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"='' OR "DUMMY"='↑' OR "DUMMY"='↓' OR "DUMMY"='' OR
              "DUMMY"=' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"=' ' OR "DUMMY"='!' OR "DUMMY"='"' OR "DUMMY"='#' OR
              "DUMMY"='$' OR "DUMMY"='%' OR "DUMMY"='&' OR "DUMMY"='''' OR
              "DUMMY"='(' OR "DUMMY"=')' OR "DUMMY"='*' OR "DUMMY"='+' OR "DUMMY"=','
              OR "DUMMY"='-' OR "DUMMY"='.' OR "DUMMY"='/' OR "DUMMY"='0' OR
              "DUMMY"='1' OR "DUMMY"='2' OR "DUMMY"='3' OR "DUMMY"='4' OR "DUMMY"='5'
              OR "DUMMY"='6' OR "DUMMY"='7' OR "DUMMY"='8' OR "DUMMY"='9' OR
              "DUMMY"=':' OR "DUMMY"=';' OR "DUMMY"='<' OR "DUMMY"='=' OR "DUMMY"='>'
              OR "DUMMY"='?' OR "DUMMY"='@' OR "DUMMY"='A' OR "DUMMY"='B' OR
              "DUMMY"='C' OR "DUMMY"='D' OR "DUMMY"='E' OR "DUMMY"='F' OR "DUMMY"='G'
              OR "DUMMY"='H' OR "DUMMY"='I' OR "DUMMY"='J' OR "DUMMY"='K' OR
              "DUMMY"='L' OR "DUMMY"='M' OR "DUMMY"='N' OR "DUMMY"='O' OR "DUMMY"='P'
              OR "DUMMY"='Q' OR "DUMMY"='R' OR "DUMMY"='S' OR "DUMMY"='T' OR
              "DUMMY"='U' OR "DUMMY"='V' OR "DUMMY"='W' OR "DUMMY"='X' OR "DUMMY"='Y'
              OR "DUMMY"='Z' OR "DUMMY"='[' OR "DUMMY"='\' OR "DUMMY"=']' OR
              "DUMMY"='^' OR "DUMMY"='_' OR "DUMMY"='`' OR "DUMMY"='a' OR "DUMMY"='b'
              OR "DUMMY"='c' OR "DUMMY"='d'))

PS. I’m not sure, but as far as i remember, when I tested it on 10.2, it didn’t work with specifying a query block.
And I have never seen such usage.

Categories: Development

Partner Webcast – Oracle Internet of Things Platform: Java 8 connecting the world

The Internet of Things Revolution is gaining speed. There are more and more devices, data and connections, thus more and more complexity to handle. But in the first place it brings complete...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Subscription Notifier Version 4.0 Enables WebCenter Users to Create Custom Content Email Notifications

Fishbowl Solutions’ Subscription Notifier has been used by many of our customers for years to manage business content stored in Oracle WebCenter Content. Subscription Notifier automatically sends email notifications based on scheduled queries. Fishbowl released version 4.0 of the product last week, and it includes several significant updates.

Now, users of Subscription Notifier can:

  • Attach native or web-viewable files to notification emails
  • Send individual notification emails for each content item
  • Configure hourly notification schedules
  • Run subscription side effects without sending emails

In addition to the latest updates, the product also offers a host of other features that enable WebCenter users to keep track of their high-value content.

You begin by naming the subscription and specifying whether emails should be sent for items matching the query. The scheduler lets you specify exactly when you want email notifications to go out (note the hourly option, new with version 4.0).

 

SubNoti general settings

The email settings specify who you want to send emails to and how they should appear to recipients. The new “Attach Content” feature gives you the option of sending web-viewable or native files, which provides a way for recipients who don’t use Oracle WebCenter to still see important files. Using the query builder is very simple and determines what content items are included in the subscription. Advanced users also have the option to write more complex queries using SQL.

SubNoti email

The Current Subscription Notifications page gives a summary of all subscriptions. In Version 4.0, simple changes such as enabling, disabling, or deleting subscriptions can be done here.

SubNoti current subscription notifications

Subscription Notifier is a very useful tool for any organization that needs to keep tabs on a large amount of business content. It is part of Fishbowl’s Administration Suite, which also includes Advanced User Security Mapping, Workflow Solution Set, and Enterprise BatchLoader. This set of products works together to simplify the most common administrative tasks in Oracle WebCenter Content.

To learn more about Subscription Notifier, visit Fishbowl’s website or read the press release announcing Version 4.0.

The post Subscription Notifier Version 4.0 Enables WebCenter Users to Create Custom Content Email Notifications appeared first on Fishbowl Solutions' C4 Blog.

Categories: Fusion Middleware, Other

My Speaking Schedule for Oracle Open World 2014

Galo Balda's Blog - Wed, 2014-08-27 12:22

A quick post to let you know about the two presentations that I’ll be doing at Oracle Open World 2014.

Session ID:         UGF4482
Session Title:     “Getting Started with SQL Pattern Matching in Oracle Database 12c
Venue / Room:  Moscone South – 301
Date and Time:  9/28/14, 13:30 – 14:15

Session ID:          CON4493
Session Title:      “Regular Expressions in Oracle Database 101″
Venue / Room:   Moscone South – 303
Date and Time:   10/2/14, 13:15 – 14:00

As usual, you might have to check before the session to make sure the room has not changed.

I hope to see you there.


Filed under: 12C, Open World, Oracle, Regular Expressions, Row Pattern Matching, SQL Tagged: 12C, Open World, Oracle, Regular Expressions, Row Pattern Matching, SQL
Categories: DBA Blogs

Dress Code 2.0: Wearable Tech Meetup at the OTN Lounge at Oracle OpenWorld 2014

Usable Apps - Wed, 2014-08-27 09:38

What? Dress Code 2.0: Wearable Tech Meetup at the OTN Lounge at Oracle OpenWorld 2014

When? Tuesday, 30-September-2014, 4-6 PM

Partners! Customers! Java geeks! Developers everywhere! Lend me your (er, wearable tech) ears!

Get your best wearables technology gear on and come hang out with the Oracle Applications User Experience team and friends at the OTN Lounge Wearables Technology Meetup at Oracle OpenWorld 2014.

Oracle Apps UX and OTN augmenting and automating work with innnovation and the cloud
  • See live demos of Oracle ideation and proof of concept wearable technology—smart watches, heads-up displays, sensors, and other devices and UIs—all integrated with the Oracle Java Cloud.
  • Try our wearable gadgets for size, and chat with the team about using OTN resources to design and build your own solutions.
  • Show us your own wearables and discuss the finer points of use cases, APIs, integrations, UX design, and fashion and style considerations for wearable tech development, and lots more!

Inexpensive yet tasteful gifts for attendees sporting wearable tech, while supplies last!

Note: A 2014 Oracle OpenWorld or JavaOne conference badge is required for admittance to the OTN Lounge. 

More?

Open World Session--Functional Overview of the PeopleSoft Fluid User Experience: The Home Page

PeopleSoft Technology Blog - Tue, 2014-08-26 14:50

PeopleTools 8.54 is a landmark release for Oracle/PeopleSoft, and you are starting to see a lot of information on it, both in this blog and elsewhere.  One of the most important aspects of this release is the new Fluid User Experience.  This is a broad-ranging subject, so you will see posts from a functional perspective (this post), a developer’s perspective, and announcements about Fluid applications that are being delivered by PeopleSoft.

Perhaps you’ve heard about how the Fluid user experience provides support for mobile applications.  While that is certainly true, Fluid offers much more than that.  What the Fluid UX really provides is the ability to access PeopleSoft applications across a variety of form factors from smart phones to tablets to desktops/laptops.  Fluid applications present a common user experience on a variety of devices regardless of screen size.  These applications are efficient and attractive as well, and offer the kind of user experience the modern work force is expecting.  So no matter how your users access PeopleSoft, they will be presented with the same intuitive user experience.  This post is the first of a series covering the main features that you will see in Fluid applications when you install them or if you develop some yourself.  We’ll also cover how to get started with Fluid/PeopleTools 8.54, and how new Fluid application pages will work with your existing applications.

We’ll start today with perhaps the most fundamental feature of the Fluid UX: the Home Page.  This page provides a base or launch pad for users to navigate to their essential work.  Home pages are designed for specific roles, so they contain all the essentials for each role without extraneous menus or content that might distract users.  In this way, Fluid Home Pages are conceptually similar to current home pages or dashboards, but Fluid home pages employ the new responsive UI that renders well on different form factors.

 Let’s look at the main features of the Home page.

The first thing you’ll notice about Home Pages is that they contain a variety of Tiles.  Tiles can serve as navigation mechanisms, but may also convey dynamic information.  Tiles are similar in purpose to pagelets, but are not as interactive.  They are responsive, however, and can automatically change size and position to accommodate different form factors.

Now let’s take a look at the Home Page header, which contains several useful features.  Central is the Home Page drop down menu.  This takes the place of tabs, and enables users to move among all the home pages that they use.  Users may serve in a variety of roles in an enterprise, and they may therefore have more than one Home Page—one for each role they play.  For example, a person may be a manager, but they are also an employee, and as such they have different activities and tasks they perform in both those roles.  They would likely have different home pages for those different roles. 

 Next is the PeopleSoft Search widget.  This provides for a search-centric navigation paradigm, and enables users to search from almost anywhere within their PeopleSoft system.  In addition, with the new PeopleSoft search, users can search across pillars and retrieve results from all their PeopleSoft content.  The search results are even actionable, so users can often complete a task right from the search results page.

Notifications are a handy mechanism that lets users know when there are tasks requiring their attention.  The Notifications widget displays the number of items requiring attention.  When the user clicks the widget a window displays the sum of all notifications from all applications to which the user has access.  The user can act on those items directly from the Notifications window.

Next is the Actions widget.  This menu is configurable, but one of the main actions available is Personalizations. This takes the user to a page where they can add or remove tiles from a Home Page, delete Home Pages, or even create new home pages and configure them.

Finally, and perhaps most importantly, we have the Navigation Bar widget.  This opens the Nav Bar, which enables users to get anywhere in their PeopleSoft system.  The Nav Bar is flexible configurable, powerful and intuitive.

The Nav Bar is a rich topic in its own right, and will be covered in a separate blog post in the near future.  In fact, the Fluid user experience is a large subject, so we’ll be posting many more articles describing its features in greater depth.  We’ll also provide a taste of developing Fluid applications.

If you would like more information on the Fluid UX (and everything PeopleSoft) please see the PeopleSoft Information Portal.

We will also be covering the Fluid UX in great depth in several sessions at Oracle Open World.  Come see us at the conference!  You’ll not only acquire useful information, but you can talk with us personally and see live demos of these features.  Here are a few sessions in particular that cover the Fluid UX:

  • A Closer Look at the New PeopleSoft Fluid User Experience (CON7567)
  • PeopleSoft Mobility Deep Dive: PeopleSoft Fluid User Interface and More (CON7588)
  • PeopleSoft Fluid User Interface: A Modern User Experience for PeopleSoft HCM on Any Device (CON7667)
  • PeopleSoft PeopleTools 8.54: PeopleSoft Fluid User Interface in Action (CON7595)
These sessions cover a wide variety of subjects from the functional (for end users and SMEs) to the technical (for developers).

Starting out with MAF?

Angelo Santagata - Tue, 2014-08-26 07:45
If your starting out with MAF, Oracle's Mobile Application Framework, then you MUST read this blog entry and make sure everything is right.. even if your a seasoned ADF mobile developer like me.. you wanna check this out, got me a couple of times!

https://blogs.oracle.com/mobile/entry/10_tips_for_getting_started

%sql: To Pandas and Back

Catherine Devlin - Tue, 2014-08-26 05:03

A Pandas DataFrame has a nice to_sql(table_name, sqlalchemy_engine) method that saves itself to a database.

The only trouble is that coming up with the SQLAlchemy Engine object is a little bit of a pain, and if you're using the IPython %sql magic, your %sql session already has an SQLAlchemy engine anyway. So I created a bogus PERSIST pseudo-SQL command that simply calls to_sql with the open database connection:

%sql PERSIST mydataframe

The result is that your data can make a very convenient round-trip from your database, to Pandas and whatever transformations you want to apply there, and back to your database:



In [1]: %load_ext sql

In [2]: %sql postgresql://@localhost/
Out[2]: u'Connected: @'

In [3]: ohio = %sql select * from cities_of_ohio;
246 rows affected.

In [4]: df = ohio.DataFrame()

In [5]: montgomery = df[df['county']=='Montgomery County']

In [6]: %sql PERSIST montgomery
Out[6]: u'Persisted montgomery'

In [7]: %sql SELECT * FROM montgomery
11 rows affected.
Out[7]:
[(27L, u'Brookville', u'5,884', u'Montgomery County'),
(54L, u'Dayton', u'141,527', u'Montgomery County'),
(66L, u'Englewood', u'13,465', u'Montgomery County'),
(81L, u'Germantown', u'6,215', u'Montgomery County'),
(130L, u'Miamisburg', u'20,181', u'Montgomery County'),
(136L, u'Moraine', u'6,307', u'Montgomery County'),
(157L, u'Oakwood', u'9,202', u'Montgomery County'),
(180L, u'Riverside', u'25,201', u'Montgomery County'),
(210L, u'Trotwood', u'24,431', u'Montgomery County'),
(220L, u'Vandalia', u'15,246', u'Montgomery County'),
(230L, u'West Carrollton', u'13,143', u'Montgomery County')]

To Hint or not to hint (Application Engine), that is the question

David Kurtz - Mon, 2014-08-25 12:36
Over the years Oracle has provided a number of plan stability technologies to control how SQL statements are executed.  At the risk of over simplification, Outlines (deprecated in 11g), Profiles, Baselines and Patches work by injecting a set of hints into a SQL statement at parse time.  There is quite a lot of advice from Oracle to use these technologies to fix errant execution plans rather than hint the application.  I think it is generally good advice, however, there are times when this approach does not work well with PeopleSoft, and that is due to the behaviour and structure of PeopleSoft rather than the Oracle database.

It is possible to produce a SQL profile from a plan captured by AWR.  A part of distribution for the SQLT Diagnostic Tool (Doc ID 215187.1) is a script called coe_xfr_sql_profile.sql written by Carlos Sierra.
The only thing I would change in the delivered script, (for use with PeopleSoft and as suggested in a comment) is to create the profile with FORCE_MATCHING so that similar statements with different literal values still match. 
The Slings and Arrows of outrageous execution plans Let's take an example of a statement (from the vanilla Financials product that has not been customised) that performed poorly because it didn't generate a good execution plan (although I have cut out most of the statement for readability.  Note, that it references instance 5 of PeopleTools temporary record CA_SUM_TAO.
INSERT INTO PS_CA_SUM_RC_TAO5 (…) SELECT

FROM PS_CA_PR_SUMM A, PS_CA_SUM_TAO5 B , PS_CA_SUM_IN_USE C WHERE

B.PROCESS_INSTANCE = 51381955 AND C.IN_USE_FLAG = 'Y'

Plan hash value: 2039212279
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 14424 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 430 | 14424 (1)| 00:02:54 |
| 4 | NESTED LOOPS | | 1 | 318 | 14421 (1)| 00:02:54 |
| 5 | TABLE ACCESS FULL | PS_CA_SUM_IN_USE | 1 | 85 | 14420 (1)| 00:02:54 |
| 6 | TABLE ACCESS BY INDEX ROWID| PS_CA_SUM_TAO5 | 1 | 233 | 1 (0)| 00:00:01 |
| 7 | INDEX UNIQUE SCAN | PS_CA_SUM_TAO5 | 1 | | 0 (0)| |
| 8 | INDEX RANGE SCAN | PSACA_PR_SUMM | 1 | | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | PS_CA_PR_SUMM | 1 | 112 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
However, below is the plan we get on instance 4.  We get this plan because there is already a profile that has been applied in the past, but now we are on a different non-shared instance of the temporary table, so the profile cannot match because we are on different objects, and we get the same problem, but on different non-shared instances of the temporary record.  Different literal values, such as those for Process Instance can be handled by FORCE_MATCHING, but not different tables.  This is a totally different SQL statement.
SQL_ID 5gtxdqbx0d0c3
--------------------
INSERT INTO PS_CA_SUM_RC_TAO4 (…) SELECT

FROM PS_CA_PR_SUMM A, PS_CA_SUM_TAO4 B , PS_CA_SUM_IN_USE C WHERE

B.PROCESS_INSTANCE = 51377796 AND C.IN_USE_FLAG = 'Y'

Plan hash value: 3552771247

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 36361 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | HASH JOIN | | 1 | 430 | 36361 (3)| 00:07:17 |
| 3 | TABLE ACCESS FULL | PS_CA_SUM_IN_USE | 1 | 85 | 14347 (1)| 00:02:53 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 1 | 345 | 22014 (3)| 00:04:25 |
| 6 | TABLE ACCESS FULL | PS_CA_PR_SUMM | 5268K| 562M| 21539 (1)| 00:04:19 |
| 7 | INDEX UNIQUE SCAN | PS_CA_SUM_TAO4 | 1 | | 0 (0)| |
| 8 | TABLE ACCESS BY INDEX ROWID| PS_CA_SUM_TAO4 | 1 | 233 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Note
-----
- SQL profile "coe_gn3n77gs6xj2a_3552771247" used for this statement
Of course, the statement on instance 4 had a profile because it was added as a short term fix and then left in situ long term.  It worked fine until a process errored, left the non-shared instance of the temporary record allocated to that process instance, and so PeopleSoft allocated instance 5 on the next execution.
So we could just create another profile using the coe_xfr_sql_profile.sql script
SPO coe_xfr_sql_profile_5gtxdqbx0d0c3_3552771247.log;
SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;
REM
REM $Header: 215187.1 coe_xfr_sql_profile_5gtxdqbx0d0c3_3552771247.sql 11.4.1.4 2014/08/13 csierra $
REM
REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM carlos.sierra@oracle.com
REM
REM SCRIPT
REM coe_xfr_sql_profile_5gtxdqbx0d0c3_3552771247.sql
REM
REM DESCRIPTION
REM This script is generated by coe_xfr_sql_profile.sql
REM It contains the SQL*Plus commands to create a custom
REM SQL Profile for SQL_ID 5gtxdqbx0d0c3 based on plan hash
REM value 3552771247.
REM The custom SQL Profile to be created by this script
REM will affect plans for SQL commands with signature
REM matching the one for SQL Text below.
REM Review SQL Text and adjust accordingly.
REM
REM PARAMETERS
REM None.
REM
REM EXAMPLE
REM SQL> START coe_xfr_sql_profile_5gtxdqbx0d0c3_3552771247.sql;
REM
REM NOTES
REM 1. Should be run as SYSTEM or SYSDBA.
REM 2. User must have CREATE ANY SQL PROFILE privilege.
REM 3. SOURCE and TARGET systems can be the same or similar.
REM 4. To drop this custom SQL Profile after it has been created:
REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_5gtxdqbx0d0c3_3552771247');
REM 5. Be aware that using DBMS_SQLTUNE requires a license
REM for the Oracle Tuning Pack.
REM
WHENEVER SQLERROR EXIT SQL.SQLCODE;
REM
VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
INSERT INTO PS_CA_SUM_RC_TAO4 (PROCESS_INSTANCE, BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, ANALYSIS_TYPE, RESOURCE_TYPE, RESOURCE_CATEGORY, RESOURCE_SUB_CAT, BI_DISTRIB_STATUS, GL_DISTRIB_STATUS, FOREIGN_CURRENCY, CONTRACT_CURRENCY, CONTRACT_NUM, CONTRACT_LINE_NUM, CA_FEE_STATUS, RESOURCE_QUANTITY, FOREIGN_AMOUNT_BSE, FOREIGN_AMOUNT_INC, FOREIGN_AMOUNT, CONTRACT_AMT_BSE, CONTRACT_AMT_INC, CONTRACT_AMT, MIN_TRANS_DT, MAX_TRANS_DT, CAND_MIN_TRANS_DT, CAND_MAX_TRANS_DT) SELECT B.PROCESS_INSTANCE, A.BUSINESS_UNIT, A.PROJECT_ID, A.ACTIVITY_ID, A.ANALYSIS_TYPE, A.RESOURCE_TYPE, A.RESOURCE_CATEGORY, A.RESOURCE_SUB_CAT, A.BI_DISTRIB_STATUS, A.GL_DISTRIB_STATUS,
A.FOREIGN_CURRENCY, A.CONTRACT_CURRENCY, A.CONTRACT_NUM, A.CONTRACT_LINE_NUM, A.CA_FEE_STATUS, (A.RESOURCE_QUANTITY+B.RESOURCE_QUANTITY), A.FOREIGN_AMOUNT, B.FOREIGN_AMOUNT, (A.FOREIGN_AMOUNT+B.FOREIGN_AMOUNT), A.CONTRACT_AMT, B.CONTRACT_AMT, (A.CONTRACT_AMT+B.CONTRACT_AMT), A.MIN_TRANS_DT, A.MAX_TRANS_DT, B.CAND_MIN_TRANS_DT, B.CAND_MAX_TRANS_DT FROM PS_CA_PR_SUMM A, PS_CA_SUM_TAO4 B , PS_CA_SUM_IN_USE C WHERE B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PROJECT_ID = C.PROJECT_ID AND B.ACTIVITY_ID = C.ACTIVITY_ID AND B.ANALYSIS_TYPE = C.ANALYSIS_TYPE AND B.RESOURCE_TYPE = C.RESOURCE_TYPE AND B.RESOURCE_CATEGORY = C.RESOURCE_CATEGORY AND B.RESOURCE_SUB_CAT =
C.RESOURCE_SUB_CAT AND B.BI_DISTRIB_STATUS = C.BI_DISTRIB_STATUS AND B.GL_DISTRIB_STATUS = C.GL_DISTRIB_STATUS AND B.FOREIGN_CURRENCY = C.FOREIGN_CURRENCY AND B.CONTRACT_CURRENCY = C.CONTRACT_CURRENCY AND B.CONTRACT_NUM = C.CONTRACT_NUM AND B.CONTRACT_LINE_NUM = C.CONTRACT_LINE_NUM AND B.CA_FEE_STATUS = C.CA_FEE_STATUS AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.ACTIVITY_ID = B.ACTIVITY_ID AND A.ANALYSIS_TYPE = B.ANALYSIS_TYPE AND A.RESOURCE_TYPE = B.RESOURCE_TYPE AND A.RESOURCE_CATEGORY = B.RESOURCE_CATEGORY AND A.RESOURCE_SUB_CAT = B.RESOURCE_SUB_CAT AND A.BI_DISTRIB_STATUS = B.BI_DISTRIB_STATUS AND A.GL_DISTRIB_STATUS =
B.GL_DISTRIB_STATUS AND A.FOREIGN_CURRENCY = B.FOREIGN_CURRENCY AND A.CONTRACT_CURRENCY = B.CONTRACT_CURRENCY AND A.CONTRACT_NUM = B.CONTRACT_NUM AND A.CONTRACT_LINE_NUM = B.CONTRACT_LINE_NUM AND A.CA_FEE_STATUS = B.CA_FEE_STATUS AND B.PROCESS_INSTANCE = 51377796 AND C.IN_USE_FLAG = 'Y'
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
q'[DB_VERSION('11.2.0.3')]',
q'[OPT_PARAM('_unnest_subquery' 'false')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 4)]',
q'[OPT_PARAM('_optimizer_cost_based_transformation' 'off')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[OUTLINE_LEAF(@"INS$1")]',
q'[FULL(@"INS$1" "PS_CA_SUM_RC_TAO4"@"INS$1")]',
q'[FULL(@"SEL$1" "A"@"SEL$1")]',
q'[INDEX(@"SEL$1" "B"@"SEL$1" ("PS_CA_SUM_TAO4"."PROCESS_INSTANCE" "PS_CA_SUM_TAO4"."BUSINESS_UNIT" "PS_CA_SUM_TAO4"."PROJECT_ID" "PS_CA_SUM_TAO4"."ACTIVITY_ID" "PS_CA_SUM_TAO4"."ANALYSIS_TYPE" "PS_CA_SUM_TAO4"."RESOURCE_TYPE" "PS_CA_SUM_TAO4"."RESOURCE_CATEGORY" "PS_CA_SUM_TAO4"."RESOURCE_SUB_CAT" "PS_CA_SUM_TAO4"."BI_DISTRIB_STATUS" "PS_CA_SUM_TAO4"."GL_DISTRIB_STATUS" "PS_CA_SUM_TAO4"."FOREIGN_CURRENCY" "PS_CA_SUM_TAO4"."CONTRACT_CURRENCY" "PS_CA_SUM_TAO4"."CONTRACT_NUM" ]',
q'[ "PS_CA_SUM_TAO4"."CONTRACT_LINE_NUM" "PS_CA_SUM_TAO4"."CA_FEE_STATUS"))]',
q'[FULL(@"SEL$1" "C"@"SEL$1")]',
q'[LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1" "C"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "B"@"SEL$1")]',
q'[NLJ_BATCHING(@"SEL$1" "B"@"SEL$1")]',
q'[USE_HASH(@"SEL$1" "C"@"SEL$1")]',
q'[SWAP_JOIN_INPUTS(@"SEL$1" "C"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_5gtxdqbx0d0c3_3552771247',
description => 'coe 5gtxdqbx0d0c3 3552771247 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
PRINT signature
PRO
PRO ... manual custom SQL Profile has been created
PRO
SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";
SPO OFF;
PRO
PRO COE_XFR_SQL_PROFILE_5gtxdqbx0d0c3_3552771247 completed
But then we must manually change the table and index names from 4 to 5.
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
INSERT INTO PS_CA_SUM_RC_TAO5 (PROCESS_INSTANCE, BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, ANALYSIS_TYPE, RESOURCE_TYPE, RESOURCE_CATEGORY, RESOURCE_SUB_CAT, BI_DISTRIB_STATUS, GL_DISTRIB_STATUS, FOREIGN_CURRENCY, CONTRACT_CURRENCY, CONTRACT_NUM, CONTRACT_LINE_NUM, CA_FEE_STATUS, RESOURCE_QUANTITY, FOREIGN_AMOUNT_BSE, FOREIGN_AMOUNT_INC, FOREIGN_AMOUNT, CONTRACT_AMT_BSE, CONTRACT_AMT_INC, CONTRACT_AMT, MIN_TRANS_DT, MAX_TRANS_DT, CAND_MIN_TRANS_DT, CAND_MAX_TRANS_DT) SELECT B.PROCESS_INSTANCE, A.BUSINESS_UNIT, A.PROJECT_ID, A.ACTIVITY_ID, A.ANALYSIS_TYPE, A.RESOURCE_TYPE, A.RESOURCE_CATEGORY, A.RESOURCE_SUB_CAT, A.BI_DISTRIB_STATUS, A.GL_DISTRIB_STATUS,
A.FOREIGN_CURRENCY, A.CONTRACT_CURRENCY, A.CONTRACT_NUM, A.CONTRACT_LINE_NUM, A.CA_FEE_STATUS, (A.RESOURCE_QUANTITY+B.RESOURCE_QUANTITY), A.FOREIGN_AMOUNT, B.FOREIGN_AMOUNT, (A.FOREIGN_AMOUNT+B.FOREIGN_AMOUNT), A.CONTRACT_AMT, B.CONTRACT_AMT, (A.CONTRACT_AMT+B.CONTRACT_AMT), A.MIN_TRANS_DT, A.MAX_TRANS_DT, B.CAND_MIN_TRANS_DT, B.CAND_MAX_TRANS_DT FROM PS_CA_PR_SUMM A, PS_CA_SUM_TAO5 B , PS_CA_SUM_IN_USE C WHERE B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PROJECT_ID = C.PROJECT_ID AND B.ACTIVITY_ID = C.ACTIVITY_ID AND B.ANALYSIS_TYPE = C.ANALYSIS_TYPE AND B.RESOURCE_TYPE = C.RESOURCE_TYPE AND B.RESOURCE_CATEGORY = C.RESOURCE_CATEGORY AND B.RESOURCE_SUB_CAT =
C.RESOURCE_SUB_CAT AND B.BI_DISTRIB_STATUS = C.BI_DISTRIB_STATUS AND B.GL_DISTRIB_STATUS = C.GL_DISTRIB_STATUS AND B.FOREIGN_CURRENCY = C.FOREIGN_CURRENCY AND B.CONTRACT_CURRENCY = C.CONTRACT_CURRENCY AND B.CONTRACT_NUM = C.CONTRACT_NUM AND B.CONTRACT_LINE_NUM = C.CONTRACT_LINE_NUM AND B.CA_FEE_STATUS = C.CA_FEE_STATUS AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.ACTIVITY_ID = B.ACTIVITY_ID AND A.ANALYSIS_TYPE = B.ANALYSIS_TYPE AND A.RESOURCE_TYPE = B.RESOURCE_TYPE AND A.RESOURCE_CATEGORY = B.RESOURCE_CATEGORY AND A.RESOURCE_SUB_CAT = B.RESOURCE_SUB_CAT AND A.BI_DISTRIB_STATUS = B.BI_DISTRIB_STATUS AND A.GL_DISTRIB_STATUS =
B.GL_DISTRIB_STATUS AND A.FOREIGN_CURRENCY = B.FOREIGN_CURRENCY AND A.CONTRACT_CURRENCY = B.CONTRACT_CURRENCY AND A.CONTRACT_NUM = B.CONTRACT_NUM AND A.CONTRACT_LINE_NUM = B.CONTRACT_LINE_NUM AND A.CA_FEE_STATUS = B.CA_FEE_STATUS AND B.PROCESS_INSTANCE = 51377796 AND C.IN_USE_FLAG = 'Y'
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
q'[DB_VERSION('11.2.0.3')]',
q'[OPT_PARAM('_unnest_subquery' 'false')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 4)]',
q'[OPT_PARAM('_optimizer_cost_based_transformation' 'off')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[OUTLINE_LEAF(@"INS$1")]',
q'[FULL(@"INS$1" "PS_CA_SUM_RC_TAO5"@"INS$1")]',
q'[FULL(@"SEL$1" "A"@"SEL$1")]',
q'[INDEX(@"SEL$1" "B"@"SEL$1" ("PS_CA_SUM_TAO5"."PROCESS_INSTANCE" "PS_CA_SUM_TAO5"."BUSINESS_UNIT" "PS_CA_SUM_TAO5"."PROJECT_ID" "PS_CA_SUM_TAO5"."ACTIVITY_ID" "PS_CA_SUM_TAO5"."ANALYSIS_TYPE" "PS_CA_SUM_TAO5"."RESOURCE_TYPE" "PS_CA_SUM_TAO5"."RESOURCE_CATEGORY" "PS_CA_SUM_TAO5"."RESOURCE_SUB_CAT" "PS_CA_SUM_TAO5"."BI_DISTRIB_STATUS" "PS_CA_SUM_TAO5"."GL_DISTRIB_STATUS" "PS_CA_SUM_TAO5"."FOREIGN_CURRENCY" "PS_CA_SUM_TAO5"."CONTRACT_CURRENCY" "PS_CA_SUM_TAO5"."CONTRACT_NUM" ]',
q'[ "PS_CA_SUM_TAO5"."CONTRACT_LINE_NUM" "PS_CA_SUM_TAO5"."CA_FEE_STATUS"))]',
q'[FULL(@"SEL$1" "C"@"SEL$1")]',
q'[LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1" "C"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "B"@"SEL$1")]',
q'[NLJ_BATCHING(@"SEL$1" "B"@"SEL$1")]',
q'[USE_HASH(@"SEL$1" "C"@"SEL$1")]',
q'[SWAP_JOIN_INPUTS(@"SEL$1" "C"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_5gtxdqbx0d0c3_3552771247',
description => 'coe 5gtxdqbx0d0c3 3552771247 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
Or to take Arms against a Sea of statements, The profile has advantage that it can be applied quickly without a code change.  It is the perfect tool for the DBA with a production performance problem. However, there are some other considerations.
  • If applying to statement that references a PS temp record then we need to apply the profile to all instances of the record (both non-shared instances and the shared instance).
  • We were lucky that we referenced instance 5 of two temporary records. However, you could get a situation where a statement references different instances of different temporary records.  So perhaps instance 5 of one table and instance 6 of another.  In which case, you might also get instance 6 of the first table and instance 5 of the other.  A SQL profile could be needed for each permutation.
  • Bear in mind also that some areas of PeopleSoft use dynamically generated SQL.  So you get similar SQL statements which are sufficiently different for the profile not to match.  
  • Any changes to the expansion of Application Engine and PeopleCode MetaSQL on upgrading PeopleTools, or potentially even patching, will also prevent matching.
  • There is also the challenge of dealing with code changes as the system is upgraded, patched and customised.  A small code change, perhaps just an extra field in the select clause, can result in a performance regression because the profile stops matching. Of course, this challenge is not limited to PeopleSoft systems! 
Profiles are likely to be effective if there are no PeopleSoft temporary records present.  So you can generally use them in COBOL and SQR processes and the on-line application (other than in on-line Application Engine processes). Aye, there's the rub,I would use a profile (or a set of profiles) as a short-term temporary fix that is easier to introduce into production, and then add hints to the source code and so fix all instances of the code, not just the ones that have been profiled. Of course, that does entail a code change, and everything that goes with that.  One strong argument against making code change is that you have to change the code again to remove or change the hint if it becomes unnecessary at some time in future after a significant change, such as an Oracle upgrade.  However, on balance, I think it is better than the scenario where the profile stops working one day without warning.The rest is silence.Unless you add a comment.©David Kurtz, Go-Faster Consultancy Ltd.

Configure your Oracle Net Client for AD LDAP Signing

Laurent Schneider - Mon, 2014-08-25 12:14

If you don’t know about the “Require Signing” option, read this http://support.microsoft.com/kb/935834

LDAP is a cool alternative to tnsnames.ora. If your MSAD is highly available and you have friends there, you could go down that route : tnsnames and active directory

Amoung others, the AD “Require Signing” option prevents the ldap clients from sending clear text passwords to the Microsft Active Directory Server.

Since 11gR2, NAMES.LDAP_AUTHENTICATE_BIND=TRUE, enable authenticated bind. If you turn on “Require Signing” on the ActiveDirectory, you will need to use SSL. Unsigned search queries (tnsping/sqlplus/…) will be rejected.

Therefore the default port 389 won’t do the trick any more.


C:\> nslookup

> set type=all
> _ldap._tcp.dc._msdcs.example.com

_ldap._tcp.dc._msdcs.example.com     SRV service location:
          priority       = 0
          weight         = 100
          port           = 389
          svr hostname   = msad01.example.com

You must hardcode the SSL port in your ldap.ora


DIRECTORY_SERVERS=example.com:389:636
DIRECTORY_SERVER_TYPE = AD
DEFAULT_ADMIN_CONTEXT = "OU=Oracle,OU=Test,DC=example,DC=com"

And that’s not it! You need to have a wallet
So in your sqlnet.ora


WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\WALLET)))
NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=TRUE

And in your wallet, you need the root certificates of your Active Directory server. If you do not where to download them, try
1) Internet Explorer – Tools – Internet Options – Contents – Certificates
2) Double click on your Trusted certicate “Example.com Root CA”
3) Details – Copy to file – Base64 – Save

You may need intermediate “issuer” authorities too. Check with your AD Admin if you are in doubt.

Now create your wallet


mkdir c:\oracle\wallet
orapki wallet create -wallet c:\oracle\WALLET -auto_login -pwd welcome1
orapki wallet add -wallet c:\oracle\WALLET -pwd welcome1 -trusted_cert -cert c:\oracle\WALLET\ExampleComIssuingCA.cer
orapki wallet add -wallet c:\oracle\WALLET -pwd welcome1 -trusted_cert -cert c:\oracle\WALLET\ExampleComRootCA.cer
orapki wallet display -wallet c:\download\WALLET 

That’s all you need


C:\> tnsping DB01
OK (10 msec)

The best tools to debug are
1) tnsping. Increase the trace level in sqlnet.ora


TNSPING.TRACE_LEVEL = ADMIN
TNSPING.TRACE_DIRECTORY = C:\temp
DIAG_ADR_ENABLED=off

Without SSL and Require Signing, tnsping was using the SNNFL cache (DNS?):


Adding parameter DIRECTORY_SERVERS=MSAD01.example.com discovered from SNNFL into cache

With SSL and DIRECTORY_SERVERS, you should get


Inserted value DIRECTORY_SERVSSL=example.com:636 at index 0 into NLPA_CACHE
Inserted value DIRECTORY_SERVERS=example.com:389 at index 0 into NLPA_CACHE

2) ldapsearch
the one in %ORACLE_HOME%\bin


ldapsearch -d 2147483647 -b "OU=Oracle,OU=Test,DC=example,DC=com" -h example.com -p 636 -D "CN=Laurent Schneider,OU=Users,DC=example,DC=com" -w MyWindowsPW -W file:c:/oracle/WALLET -P welcome1 -s sub "(&(objectclass=*)(cn=DB01))" orclNetDescString 

3) ldp
A Microsoft LDAP gui

Once you get it, you will feel a lot more secure :)

★ How BIG is Oracle OpenWorld?

Eddie Awad - Sat, 2014-08-23 15:19

Oracle OpenWorld

Here is how big it was in 2013. Compare it to its size in 2012. It is safe to assume that it will be bigger in 2014!

I will attend this year’s event by invitation from the Oracle ACE Program. Prior to the start of the conference, I will be attending a two day product briefing with product teams at Oracle HQ. It’s like a mini OpenWorld but only for Oracle ACE Directors.

During the briefing, Oracle product managers talk about the latest and greatest product news. They also share super secret information that is not yet made public. I will report this information to you here on awads.net and via Twitter, unless of course it is protected by a non-disclosure agreement.

See you there!

Continue reading...

© Eddie Awad's Blog, 2014. | Permalink | Add a comment | Topic: Oracle | Tags:

Related articles: