Skip navigation.

Feed aggregator

Best of OTN - Week of August 31st

OTN TechBlog - Fri, 2014-09-05 09:19
Architect Community

Over the past seven days the following OTN technical articles were the most popular on the topic of Identity and Security:

  1. How To Configure Browser-based SSO with Kerberos/SPNEGO and Oracle WebLogic Server [May 2013]
    by Abhijit Patil
    Oracle WebLogic Server offers a complete solution for single sign-on with Microsoft clients using Kerberos.
  2. Working with Oracle Security Token Service in an Architecture Involving Oracle WebLogic and Oracle Service Bus [January 2014]
    by Ronaldo Fernandes
    A step by step guide to creating a single sign-on between Oracle WebLogic and Oracle Service Bus using Oracle Security Token Service (OSTS) to generate SAML tokens.
  3. Oracle Identity Manager UI Customizations [February 2014]
    by Gaurav Singh
    Tips, tricks, and technical insight into customizations for the Oracle Identity Manager user interface.

Click here for more Identity and Security articles.

Friday Fun from OTN Architect Community Manager Bob Rhubart:
You will be forgiven for jumping out of your chair and dancing along to this fantastic performance by my guitar hero, Ry Cooder, along Louisiana accordion great with Buckwheat Zydeco and rocker Lenny Kravitz: Click here for the video.

Database Community -

Oracle Key Vault Option is Here - Finally, a centralized way to manage all the encryption keys and credential files in the data center.    learn more!.

Java Community -Listen to Greg Wilkins talk about his Jetty/Servlet sessions at JavaOne 2014  - Want another good reason to go to JavaOne this year?  What this!

Tech Article: Understanding Java JIT Compilation with JITWatch - Java expert Ben Evans provide a primer on Just-In-Time compilation with JITWatch

Interview with Nikita Salnikov-Tarnovski
- Another sneak peek of what is in store for JavaOne sessions!

Systems Community -

New Articles for Oracle Solaris Developers - Rick Ramsey point us to three NEW articles for Oracle Solaris Developers. 

Brendan Gregg's Quick Reference Page for Linux Performance - Brendan Gregg is a regular contributor to DTrace and other Oracle Solaris technologies. Recently, Brendan turned his high-performance spectacles on Linux.

Tech Article: How to Get the Best Networking Performance from OVM for SPARC  -
Oracle VM lets you assign I/O networking resources to domains with a lot of flexibility. With this flexibility comes a wide range of performance possibilities. This article provides guidance regarding the most suitable configuration for certain workloads.

--

On Disney Parks, Data Science, Drones and Wearables

Oracle AppsLab - Fri, 2014-09-05 09:16

As the parent of a toddler, I have no choice but to pay attention to Disney and its myriad of products and services.

Case in point, this Summer we took our daughter to Disneyland for the first time, which was a whole thing. Pause to h/t Disneyland expert, Friend of the ‘Lab and colleague Kathy for all her park and travel protips.

Being who I am, I found myself wandering around Disneyland and California Adventure thinking about how many hardcore analytics geeks they must employ to come up with systems like FASTPASS.

For the unfamiliar, FASTPASS is a system that allows you to skip some, if not all, of the line-standing for the most popular attractions in the parks. Although it’s difficult to explain in words, the system is rather simple once you get your first pass.

Being in the park, you can feel all the thought and craft that has gone into the experience. Disney is a $45 billion company, and it’s no surprise their R&D is cutting edge. But what makes it so successful?

Attendees of Disney parks are in a very similar to employees of an enterprise in that they will gladly opt-in to new technologies because the value they receive in return is clear and quantifiable.

Put into examples, if Google Glass helps me do my job more effectively, I’ll wear them. If I receive discounted benefits for wearing a fitness tracker, I’ll do it.

If a MagicBand allows me to leave my wallet in my room, not worry about losing the room keycards, and use FastPass+, I’ll wear it, even though it will allow Disney World to track my location at a very fine-grained level. Who cares? FastPass+ is worth it, right?

Odd branding note, the official ways to write these two terms are indeed FASTPASS and FastPass+, according to Disney’s web site.

If you’re interested in reading more about the MagicBand, what’s inside and Disney uses it at Disney World, check out Welcome to Dataland. Imagine all the data science that goes into creating and iterating on these enormous data sets; this is embiggened Big Data when you consider Disney parks occupied the top eight spots in the 2012 Theme Park Index, comprising well over 100 million visits.

It boggles my mind, although for someone like Bill, it would be Christmas every day.

The post also recounts Walt Disney’s futurist vision, which seems to drive their R&D today. It also encompasses the my point nicely:

Rather, because Disney’s theme parks don’t have the same relationship to reality that Google and Costco and the NSA do. They are hybrids of fantasy and reality.

I read Welcome to Dataland only because I’d just been to Disneyland myself. Then came news that Disney had filed severals patents concerning the use of drones for its park shows, one for floating pixels, one for flying projection screens, one for transporting characters, h/t Business Insider.

Click to view slideshow.

We’ve been experimenting (ahem, playing) with quadcopters, and it struck me that Ultan (@ultan) had sent me a Disney video about customized wearables. This one:

That was posted in August 2012.

So beyond casual interest as the father of a daughter who loves Disney Princesses, suddenly it’s obvious that I need to watch Disney much more carefully to see how they’re adopting emerging technologies.

Oh and become a willing data point in their data set.

Find the comments.Possibly Related Posts:

Loose Coupling and Discovery of Services With Consul — Part 2

Pythian Group - Fri, 2014-09-05 08:33
Creating a Consul Client Docker Image

In my previous post, I demonstrated how to create a cluster of Consul servers using a pre-built Docker image. I was able to do this because our use case was simple: run Consul.

In this post, however, we will be creating one or more Consul clients that will register services they provide, which can then be queried using Consul’s DNS and / or HTTP interfaces. As we are now interested in running Consul and an application providing a service, things are going to get a bit more complicated.

Before proceeding, I’m going to need to explain a little bit about how Docker works. Docker images, such as progrium/consul we used in the previous post, are built using instructions from a special file called Dockerfile. There are two related instructions that can be specified in this file which control the container’s running environment: that is, the process or shell that is run in the container. They are ENTRYPOINT and CMD.

There can be only one ENTRYPOINT instruction in a Dockerfile, and it has two forms: either an array of strings, which will be treated like an exec, or a simple string which will execute in ‘/bin/sh -c’. When you specify an ENTRYPOINT, the whole container runs as if it were just that executable.

The CMD instruction is a bit different. It too can only be specified once, but it has three forms: the first two are the same as ENTRYPOINT, but the third form is an array of strings which will be passed as parameters to the ENTRYPOINT instruction. It’s important to note that parameters specified in an ENTRYPOINT instruction cannot be overridden, but ones in CMD can. Therefore, the main purpose of CMD is to provide defaults for an executing container.

It’s probably becoming clear to you by now that Docker images are designed to run one process or shell. We want to run two processes, however: the Consul agent and an application. Thankfully, there is an image available called phusion/baseimage that provides runit for service supervision and management, which will make it easy for me to launch Consul and another service (such as nginx) within my containers.

SIDEBAR: There is quite a bit of debate about the intended / recommend use of Docker, and whether the process run in the container should be your application or an init process that will spawn, manage and reap children. If you’re interested in reading more about the pros and cons of each of these approaches, please refer to Jérôme Petazzoni’s post, phusion’s baseimage-docker page, and / or Google the topics of ‘separation of concerns’ and ‘microservices’.

Now that I’ve provided some background, let’s get into the specifics of the Docker image for my Consul clients. I’ll begin with the full contents of the Dockerfile and then describe each section in detail.

FROM phusion/baseimage:latest
MAINTAINER Bill Fraser 

# Disable SSH
RUN rm -rf /etc/service/sshd /etc/my_init.d/00_regen_ssh_host_keys.sh

# Install nginx
RUN \
  add-apt-repository -y ppa:nginx/stable && \
  apt-get update && \
  apt-get install -y nginx zip && \
  chown -R www-data:www-data /var/lib/nginx

# Clean up apt
RUN apt-get clean && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*

# Define mountable directories.
VOLUME ["/data", "/etc/nginx/sites-enabled", "/var/log/nginx"]

# Add runit configuration for nginx
RUN echo "\ndaemon off;" >> /etc/nginx/nginx.conf
ADD files/nginx/nginx-runit /etc/service/nginx/run
RUN chmod 755 /etc/service/nginx/run

# Install consul
RUN curl -s -L -O https://dl.bintray.com/mitchellh/consul/0.3.0_linux_amd64.zip
RUN unzip -d /usr/bin 0.3.0_linux_amd64.zip
RUN chmod 555 /usr/bin/consul

# Add service configuration
ADD files/consul/consul.json /etc/consul.d/consul.json
RUN chmod 644 /etc/consul.d/consul.json

# Add runit configuration for consul
ADD files/consul/consul-runit /etc/service/consul/run
RUN chmod 755 /etc/service/consul/run

# Expose nginx ports
EXPOSE 80 443

# Expose consul ports
EXPOSE 53/udp 8300 8301 8301/udp 8302 8302/udp 8400 8500

ENV HOME /root

ENTRYPOINT [ "/sbin/my_init" ]

The first section specifies that my image will be based on that of phusion/baseimage, and that I am the maintainer of my image. So far so good.

Next, I am removing the SSHD service from the container. This is part of phusion’s image and is not something I am interested in using for the purposes of my demonstration.

The next step is to install nginx and should look fairly straight forward. I have taken the liberty of installing zip at the same time, as I will be using it later on to install Consul.

The VOLUME instruction lets me define mount points that can be used for mounting volumes in the container, passed as arguments of the docker run command. I am not actually using this in my demonstration, it is just there to make you aware of the capability.

Next I am telling nginx not to daemonize itself, and am adding an nginx configuration for runit. The ADD instruction adds a local file to the image in the specified path. The runit configuration I am adding is pretty simple and looks like this:

#!/bin/sh
exec /usr/sbin/nginx -c /etc/nginx/nginx.conf 2>&1

Now that I am done with nginx, I want to install and configure Consul. I simply retrieve the binary package and extract it into /usr/bin in the image. I then use another ADD instruction to supply a configuration file for Consul. This file is in JSON format and tells Consul to register a service named ‘nginx’.

{
	"service": {
		"name": "nginx",
		"port": 80
	}
}

I then use an ADD instruction to supply a runit configuration for Consul in the same manner I did for nginx. Its content is as follows:

#!/bin/sh
if [ -f "/etc/container_environment.sh" ]; then
  source /etc/container_environment.sh
fi

# Make sure to use all our CPUs, because Consul can block a scheduler thread
export GOMAXPROCS=`nproc`

# Get the public IP
BIND=`ifconfig eth0 | grep "inet addr" | awk '{ print substr($2,6) }'`

exec /usr/bin/consul agent \
  -config-dir="/etc/consul.d" \
  -data-dir="/tmp/consul" \
  -bind=$BIND \
  ${CONSUL_FLAGS} \
  >>/var/log/consul.log 2>&1

With all of the hard stuff out of the way, I now define the nginx and Consul ports to EXPOSE to other containers running on the host, and to the host itself.

And last but not least, I set the HOME environment variable to /root and set the init process of /sbin/my_init as the ENTRYPOINT of my container.

This creates a good foundation. If we were to run the image as is, we would end up with nginx running and listening on port 80, and Consul would be running as well. However, we haven’t provided Consul with any details of the cluster to join. As you have probably guessed, that’s what CONSUL_FLAGS is for, and we’ll see it in action in the next section.

Creating Consul Clients With Vagrant

So far we’ve gone to the trouble of creating a Docker image that will run Consul and nginx, and we’ve supplied configuration to Consul that will have it register nginx as a service. Now we’ll want to create some clients with Vagrant and see querying of services in action.

Let’s start by modifying our Vagrantfile. Just as was done with the Consul servers, we’ll want to create an array for the nginx members and tell Vagrant to use the Docker provider. This time, however, some additional configuration will be necessary. The full Vagrantfile is now going to look like this:

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  JOIN_IP = ENV['JOIN_IP']
  
  # A hash of containers to define.
  # These will be the Consul cluster members.
  consul_members = [ "consul1", "consul2", "consul3" ]
  consul_members.each do |member|
    config.vm.define member do |consul_config|

      # Use Docker provider
      consul_config.vm.provider "docker" do |docker|
        docker.name = member
        docker.image  = 'progrium/consul'
        docker.cmd = [ "-server", "-node=#{member}", "-join=#{JOIN_IP}" ]
      end
    end
  end

  # Create an nginx container running the consul agent
  nginx_members = [ "nginx1", "nginx2", "nginx3" ]
  nginx_members.each do | member|
    config.vm.define member do |nginx_config|
      nginx_config.vm.provider "docker" do |docker|
        docker.name = member
        docker.build_args = [ "-t", "bfraser/consul-nginx", "--rm=true" ]
        docker.build_dir = "."
        docker.cmd = [ "/bin/bash" ]
        docker.create_args = [ "--dns=#{JOIN_IP}", "-t", "-i" ]
        docker.env = { "CONSUL_FLAGS" => "-node=#{member} -join=#{JOIN_IP}" }
      end
    end
  end
end

Note that this time we are not using docker.image to supply the name of an existing Docker image to use for our containers. Instead, we are going to use docker.build_args and docker.build_dir to build our own.

docker.build_args = [ "-t", "bfraser/consul-nginx", "--rm=true" ]

This is a list of extra arguments to pass to the docker build command. Specifically, I am naming the image bfraser/consul-nginx and telling Docker to remove intermediate containers after a successful build.

docker.build_dir = "."

This should be fairly self-explanatory: I am simply telling Docker to use the current working directory as the build directory. However, I have some files (including the Vagrantfile) that I do not want to be part of the resulting image, so it is necessary to tell Docker to ignore them. This is accomplished with a file called .dockerignore and mine looks like this:

.git
.vagrant
Vagrantfile

Next, I am using docker.cmd to pass /bin/bash as an extra parameter to the image’s ENTRYPOINT, which allows me to have a shell in the container. A little later, I will show you how this can be useful.

The next line:

docker.create_args = [ "--dns=#{JOIN_IP}", "-t", "-i" ]

is a list of extra arguments to pass to the ‘docker run‘ command. Specifically, I am providing a custom DNS server and instructing Docker to allocate a TTY and keep STDIN open even if not attached to the container.

Lastly, I am supplying a hash to docker.env which will expose an environment variable named CONSUL_FLAGS to the container. The environment variable contains additional parameters to be used when starting Consul.

With this configuration in place, we can now use Vagrant to create three additional containers, this time running Consul and nginx.

$ JOIN_IP=172.17.42.1 vagrant up --provider=docker

This time if we check the output of ‘consul members‘ we should see our host and six containers: three Consul servers and three nginx servers functioning as Consul clients.

$ consul members -rpc-addr=172.17.42.1:8400
Node     Address           Status  Type    Build  Protocol
nginx1   172.17.0.18:8301  alive   client  0.3.0  2
nginx2   172.17.0.19:8301  alive   client  0.3.0  2
laptop   172.17.42.1:8301  alive   server  0.3.0  2
consul2  172.17.0.9:8301   alive   server  0.3.0  2
consul3  172.17.0.10:8301  alive   server  0.3.0  2
consul1  172.17.0.8:8301   alive   server  0.3.0  2
nginx3   172.17.0.20:8301  alive   client  0.3.0  2
Querying Services

As I mentioned in ‘Where Does Consul Fit In?’ in my original post, Consul is a tool for enabling discovery of services, and it provides two interfaces for doing so: DNS and HTTP. In this section, I’ll show you how we can use each of these interfaces to query for details of services being provided.

First, let’s use the HTTP interface to query which services are being provided by members of the Consul cluster.

$ curl http://172.17.42.1:8500/v1/catalog/services | python -m json.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    25  100    25    0     0  12722      0 --:--:-- --:--:-- --:--:-- 25000
{
    "consul": [],
    "nginx": []
}

This returns JSON-encoded data which shows that ‘consul’ and ‘nginx’ services are being provided. Great, now let’s query for details of the ‘nginx’ service.

$ curl http://172.17.42.1:8500/v1/catalog/service/nginx | python -m json.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   362  100   362    0     0   210k      0 --:--:-- --:--:-- --:--:--  353k
[
    {
        "Address": "172.17.0.18",
        "Node": "nginx1",
        "ServiceID": "nginx",
        "ServiceName": "nginx",
        "ServicePort": 80,
        "ServiceTags": null
    },
    {
        "Address": "172.17.0.19",
        "Node": "nginx2",
        "ServiceID": "nginx",
        "ServiceName": "nginx",
        "ServicePort": 80,
        "ServiceTags": null
    },
    {
        "Address": "172.17.0.20",
        "Node": "nginx3",
        "ServiceID": "nginx",
        "ServiceName": "nginx",
        "ServicePort": 80,
        "ServiceTags": null
    }
]

We can see here that there are three nodes providing the nginx service, and we have details of the IP address and port they are listening on. Therefore, if we were to open http://172.17.0.18 in a web browser, we would see the ‘Welcome to nginx!’ page.

Notice how the REST endpoint changed between the first and second curl requests, from /v1/catalog/services to /v1/catalog/service/nginx. Consul provides extensive documentation of the various REST endpoints available via the HTTP API.

While the HTTP API is the most powerful method of interacting with Consul, if we are only interested in querying for information about nodes and services, it is also possible to use its DNS server for simple name lookups. Querying for details of the nginx service via the DNS interface is as simple as running the following:

$ dig @172.17.42.1 -p 8600 nginx.service.consul

; <> DiG 9.9.5-3-Ubuntu <> @172.17.42.1 -p 8600 nginx.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 3084
;; flags: qr aa rd; QUERY: 1, ANSWER: 3, AUTHORITY: 0, ADDITIONAL: 0
;; WARNING: recursion requested but not available

;; QUESTION SECTION:
;nginx.service.consul.		IN	A

;; ANSWER SECTION:
nginx.service.consul.	0	IN	A	172.17.0.19
nginx.service.consul.	0	IN	A	172.17.0.18
nginx.service.consul.	0	IN	A	172.17.0.20

;; Query time: 1 msec
;; SERVER: 172.17.42.1#8600(172.17.42.1)
;; WHEN: Sat Aug 16 22:35:51 EDT 2014
;; MSG SIZE  rcvd: 146

As you can see, while it is certainly possible to develop a client to tightly integrate with Consul through its API, it is also easy to take advantage of its DNS interface and not have to write a client at all.

Attaching To A Docker Container

I have one last tip, which is especially useful if you are new to Docker: how to attach to your containers.

I mentioned earlier in this post that I was including the following line in my Vagrantfile:

docker.cmd = [ "/bin/bash" ]

What this does is pass /bin/bash as an extra parameter to the image’s ENTRYPOINT instruction, resulting in the /sbin/my_init process spawning a bash shell.

I also instructed Vagrant, via the docker.create_args line, to have Docker allocate a TTY and keep STDIN open even if not attached to the container. This means I can attach to my containers and interact with them through a bash shell as follows (note: press the ‘Enter’ key following the command to get the prompt):

$ docker attach nginx1

root@4b5a98093740:/# 

Once you are done working with the container, you can detach from it by pressing ^P^Q (that’s CTRL-P followed by CTRL-Q).

Summary

With that, we have reached the end of my demonstration. Thanks for sticking with me!

First I described the importance of loose coupling and service discovery in modern service-oriented architectures, and how Consul is one tool that can be used for achieving these design goals.

Then I detailed, by way of a demonstration, how Vagrant and Docker can be used to form a Consul cluster, and how to create a custom Docker image that will run both your application and a Consul agent.

And, last but not least, I showed how you can make use of Consul’s HTTP API and DNS interface to query for information about services provided.

Hopefully you have found these posts useful and now have some ideas about how you can leverage these technologies for managing your infrastructure. I encourage you to provide feedback, and would be very interested in any tips, tricks or recommendations you may have!

Categories: DBA Blogs

Throw Away Out-of-the-Box Monitoring Solutions, Additional Services Series Pt. 7 [VIDEO]

Chris Foot - Fri, 2014-09-05 08:24

Transcript

Welcome to RDX. Today, we're going to talk about how we deploy our non-database server monitoring program. What's a non-DB server? The machines that run your enterprise applications, handle network connections and perform other critical functions.

When customizing server monitoring solutions, we follow a five-step process:

One: Understand each customer’s unique monitoring needs
Two: Identify critical monitoring parameters
Three: Develop thresholds, goals and objectives
Four: Develop and document monitoring, remediation and escalation procedures
Five: Monitor and adjust the strategy as necessary

RDX dedicates an entire team who are responsible for creating, implementing and enhancing a strategic blueprint for the proactive monitoring and trouble-shooting methods required to prevent availability and performance problems before they occur.

Companies employing RDX to support their environments do not have to spend the additional monies required to create a proactive monitoring environment.

Thanks for watching!
 

The post Throw Away Out-of-the-Box Monitoring Solutions, Additional Services Series Pt. 7 [VIDEO] appeared first on Remote DBA Experts.

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

Pythian Group - Fri, 2014-09-05 08:04

Benefits of blogs transcends the technologies as they not only enable the bloggers to pen down their valued experiences but also provide readers to get first hand practical information. This Log Buffer Edition shares those benefits from Oracle, SQL Server and MySQL.

Oracle:

Cloud Application Foundation is the innovator’s complete and integrated modern cloud application infrastructure, built using best of breed components, such as Oracle WebLogic Server 12c, the industry’s best application server for building and deploying enterprise Java EE applications.

Migrating Existing PeopleSoft Attachments into the Managed Attachments Solution.

How to identify SQL performing poorly on an APEX application?

How can you keep your Oracle Applications and systems running at peak performance? What will it take to get more out of your Oracle Premier Support coverage?

Projects Create Accounting Performance Issues With 11.2.0.3 Database Version.

SQL Server:

If your log restores aren’t happening when they’re meant to, you want to know about it. You’ll be relying on restoring from logs should anything happen to your databases, and if you can’t restore to a certain point in time, you risk losing valuable data.

Agile data warehousing can be challenging. Pairing the right methodologies and tools can help.

Introduction to Azure PowerShell Modules for the SQL Server DBA.

The Clustered columnstore index generates “unable to find index entry” error and a memory dump after few DMLs on the table.

With any application organizations face consistent key challenges such as high efficiency and business value, complex configuration, and low total cost of ownership. Extending applications to the cloud in hybrid scenarios addresses many of these challenges.

MySQL:

Analyzing Twitter Data using Datasift, MongoDB and Pig

Cloud storage for MySQL Enterprise Backup (MEB) users

Tracing down a problem, finding sloppy code.

Cloud storage for MySQL Enterprise Backup (MEB) users.

MySQL Enterprise Backup (MEB) is a highly efficient tool for taking backups of your MySQL databases.

Categories: DBA Blogs

Documentum upgrade project - sharing experience

Yann Neuhaus - Fri, 2014-09-05 06:01

A few weeks ago, we finished a Documentum upgrade at a customer site. Now, after having analyzed and made a status point (we do that after each upgrade project to optimize our procedures and skills) it is time to share our experiences. In this post and in the ones that will follow we will describe which issues we were confronted with, which bugs we discovered, and how we proceed to go forward to have a running environment at the end - and of course, a satisfied and happy customer!

 

Aim of the project

We had to upgrade four environments; development, test, clone, and prod. We had to upgrade or do new installations for the following components: Content Server from 6.5SP3 to 6.7SP2, D2 3.0 to D2 3.1SP1, DTS 6.5SP3 to ADTS 6.7SP2, DA 6.5SP3 to DA6.7SP2, DFS6.7SP2, xPlore 1.4

 

Upgrade plan

We first started with the Dev environment to define our processes. Then, we built a clone of the production repositories to validate the upgrade process, to do the test, and to validate the xPlore high availability installation. The xPlore HA has been covered with the installation of two xPlore instances on different physical servers, and the use of a DSearch URL configured in F5 (master-slave and not in load balancing mode) for the search. With all that, not only has the xPlore installation been validated, but the upgrade process could also be run faster as we had to index only a gap of objects on the day we did the production upgrade. But let's wait for a future blog where this will be described.

Issues/bugs summary

So first of all, I will summarize the issues/bugs we had to deal with. The details will be published in the next blogs.

On the content server side:

- We discovered a bug when we moved to the summer time; for instance a job that had to run at 4:00 AM started indeed at four o'clock but it also started every two minutes until 5 AM.

- D2EventSenderMailMethod does not work anymore since we installed the patch 12 for the Content Server 6.7SP2.

On ADTS:

- Some ADTS processes randomly stopped working using a domain user as the installation owner.

On DA:

- Documents could not be downloaded.

On the D2-Client:

- Facets are configured in xPlore, but date attributes cannot be listed by date.

- We had some difficulties to implement the SSO based on NTLM. As we use D2 3.1SP1 which is in my point of view a 4.x version, we did not know exactly which documentation to use. Even the EMC support had to investigate to find some information.

- The ActiveX for D2 3.1SP1 could not be installed.

- SSO and D2DFS.

- Some icons where missing in D2-Client.

 

So let's see what will be posted in the next days/weeks, which I hope will help people in their upgrade projects.

The title of the related blogs will begin with "Documentum upgrade project".

Enjoy the reading!

Arqiva Achieves a 90% Adoption Rate for Its Innovative, Near-Real-Time Network Monitoring Portal

WebCenter Team - Fri, 2014-09-05 06:00
#panelDiv_search .BottomleftCurv{background:none; width:15px; height:30px; float:left;} #panelDiv_search .centerBottom{background:none;width:153px;height:30px;float:left;} #panelDiv_search .BottomrightCurv{background:none;width:15px;height:30px;float:right;} #panelDiv_search .contentBg{border-bottom:1px solid #ababab;} #navigation{width:971px;} Arqiva Achieves a 90% Adoption Rate for Its Innovative, Near-Real-Time Network Monitoring Portal .sitefinder_content {position:absolute;right:50%; margin-right:-495px; z-index:100002;overflow: auto; top:5px;} #Wrapper_FixedWidth { min-width:974px; } .header{height:140px; *height:130px;} .menu-otn {display:none;}

Another Great WebCenter Customer Success Story from Oracle's Customer and Partner Successes

Arqiva Achieves a 90% Adoption Rate for Its Innovative, Near-Real-Time Network Monitoring Portal

Oracle Customer: Arqiva

Location:  Winchester, England

Industry: Communications

Employees:  2,000

Arqiva provides and operates much of the infrastructure behind television, radio, satellite, and wireless communications in the United Kingdom. Its customers include major broadcasters, such as the BBC, ITV, and BSkyB, as well as independent radio groups. Arqiva’s engineers led the switch from analogue to digital in 2012,  which was the largest broadcast engineering project in UK history. Arqiva also provides satellite distribution services for Turner Broadcasting System, Inc. in the United States, leading French media company Canal+ Group, and the internationally renowned Al Jazeera Network.


Challenges
  • Improve the customer experience for broadcast-radio customers by providing self-service access to a near-real-time view of their network status, 24/7 notification of faults and fixes, and maintenance schedules
  • Ensure staff—from CEOs through to technicians, presenters, and DJs—is aware of network performance, incidents, and other issues that might affect the transmission of programs to audiences
  • Enable customers to determine the operational status of transmitters and view the current and future configuration data of multiplexes (streams of digital information containing audio and other data) on the networks that broadcast their programs
  • Give users access to network performance data via a mobile phone, tablet, or desktop PC
  • Provide secure identity and access management to ensure that access to commercially sensitive network data is restricted to the relevant radio broadcaster
  • Gain ability to provide network status data to TV and satellite customers in the future
A word from Arqiva

"Oracle WebCenter Portal transformed customer service by giving radio broadcasters a near-real-time window into the performance of the networks that transmit programs to their audiences." – Helen Knight, Head of Product, TV and Radio, Arqiva


Solutions
  • Used Oracle WebCenter Portal to build a window that enables radio broadcasters to view the status of their network in near real time, with a traffic-light system to alert them to faults and their severity via an intuitive interface
  • Gained immediate acceptance by radio groups, 90% of whom signed up for access as soon as it was launched and rated the service as “fantastic,” “innovative,” and “brilliant”
  • Rolled out portal access to 180 users in 20 radio groups covering 3,500 transmitters within three months
  • Used the portal as part of strategy to deliver continuous customer service improvements by enhancing the radio-desk support function that automatically notifies customers of faults on their network and sends daily, weekly, and monthly reports to radio broadcasters
  • Benefited from Oracle WebCenter Portal’s standards-based framework to integrate Google Maps into the portal to give customers a visual image of their sites and services
  • Used out-of-the-box tools and library of prebuilt components in Oracle WebCenter Portal to create graphs and tables depicting network and infrastructure performance
  • Benefited from the flexible navigation components of Oracle WebCenter Portal to allow users to personalize the interface to individual preferences and determine the dashboards they wish to access first—such as the status of transmitters within their broadcast regions
  • Built a multiplex tab to allow users to view the configuration and capacity of multiplexes and see a list of broadcasters on those multiplexes
  • Enabled customers to create a watch list of specific incidents or planned maintenance work they wish to monitor closely and increase productivity by accessing the list immediately when they sign on to the portal
  • Gave users access to the portal from any Web-based device, enabled them to click through to service tables quickly and easily, and created views optimized for mobile phone users
  • Cut time to develop and refresh content by using Oracle WebCenter Portal’s framework to create standards-based portlets and ensure uniformity of page structure and layout
  • Benefited from standards-based, service-oriented architecture of Oracle BPEL Process Manager to assemble and integrate data from the Oracle-based, core-incident management system, and third-party sources into end-to-end process flows for publishing via the portal
  • Used Oracle Unified Directory to create and apply identity management and access security rules to protect sensitive customer network-performance data from unauthorized viewing
  • Benefited from the scalability of Oracle WebCenter Portal and Oracle Unified Directory to gain ability to accommodate more users in line with Arqiva’s plans to extend portal access to its TV and satellite customers
Oracle Product and Services

   Why Oracle?

Arqiva conducted a competitive bid and evaluated the open-source product Liferay Portal. The company chose Oracle WebCenter Portal for its standards-based interoperability, out-of-the-box functionality, and reusable portlets that would minimize development and allow Arqiva to refresh content rapidly and share it seamlessly across internal and customer-facing applications. Arqiva selected Oracle Unified Directory to provide fine-grained control of policies at an organizational-unit level and for scalability needed to meet the demands presented by additional TV and satellite customers accessing the portal at a later date.


"We chose Oracle WebCenter Portal for its ranking as a leader in Gartner’s Magic Quadrant for Horizontal Portals and because of its unrivalled functionality and strong pedigree," said Eamonn Sorohan, head of applications, Arqiva. Partner

Oracle Partner

Griffiths Waite

 Oracle Specialized Partner Griffiths Waite worked with key business users to design and build the portal to the unique requirements of different types of users. It optimized the interface for each device to ensure the best possible customer experience. Arqiva benefited from Griffiths Waite’s expertise to ensure seamless integration between the portal, back-office, and transmitter data and ensured access security was maintained for broadcasters’ critical data. As user and data volumes increased, Griffiths Waite monitored the portal to optimize performance and ensure a consistently positive customer experience.
"Griffiths Waite’s expertise enabled us to build the solution in three months," said Helen Knight, head of product, TV and radio, Arqiva. "The portal is intuitive and aesthetically pleasing while being highly functional and a great advertisement for Arqiva."

Resources More Reference Assets About Communications

Oracle 12c Real Application Security and Standard Database Auditing - Warning Database Logins Not Logged

Oracle 12c introduces several major new security features. Data redaction is one new feature and Real Application Security (RAS) is another.  Per Oracle, RAS is the next generation Virtual Private Database (VPD) and is installed with Oracle Enterprise Edition – no additional license required. RAS is a new declarative and granular authorization model and is designed to be an application security platform for end-to-end application security. For those developing APEX applications (also installed with Enterprise Edition), RAS will certainly become an integral tool.

With RAS, developers define security policies instead of having to create and maintain PL/SQL code. Most notably, RAS however extends the security solution to define both application users and roles separate from database users and roles.

RAS allows for the creation of users, complete with user names and passwords, and stores them in the database. RAS users are not stored in DBA_USERS. RAS users are defined in DBA_XS_USERS, and their passwords are stored in SYS. XS$VERIFIERS.

With 12.1.0.1, RAS users can also directly connect to the database. It appears that with 12.1.0.2, RAS users can be defined with a flag to allow or disallow direct database logons. As any database security monitoring and logging solution should be monitoring database logon activity, it should be known that RAS users will NOT show up in standard Oracle database auditing. Standard database auditing instead picks up login activity by the generic user XS$NULL. Because it is designed to be part of an application, RAS has its own logging and auditing solution.

Basic logon activity for RAS users, however is logged in SYS.UNIFIED_AUDIT_TRAIL.  Even if you have NOT enabled Unified Auditing in 12c, SYS.UNIFIED_AUDIT_TRAIL is being populated. Why this is the case will be the topic of another blog post.  If you have compliance requirements to log and audit database logons, you will need to monitor SYS.UNIFIED_AUDIT_TRAIL for RAS user activity as well as for the creation of RAS users if not also potentially configuring RAS auditing. The example below should get you started.

With the below you can test for yourself how standard database auditing logs RAS user logons:

  1. Ensure auditing for create session is enabled, if not: audit create session by access;
  2. Create Real application security user

BEGIN

XS_PRINCIPAL.CREATE_USER(NAME=>'INTEGRIGY_RAS_USER');

END;

  1. Set password for Real Application Security user

BEGIN

XS_PRINCIPAL.SET_PASSWORD('INTEGRIGY_RAS_USER','oracle');

END;

  1. Review both dba_users and dba_xs_users to see for yourself where RAS users are defined.
  2. Log into the database with: INTEGRIGY_RAS_USER/oracle
  3. Look at your auditing and see a logon from XS$NULL instead of INTEGRIGY_RAS_USER

select * from sys.aud$ order by 1 desc

  1. Now look at SYS.UNIFIED_AUDIT_TRAIL. You will see XS$NULL for the DBUSERNAME but you will see  'INTEGRIGY_RAS_USER' in XS_USER_NAME.

select dbusername,xs_user_name ,event_timestamp

from SYS.UNIFIED_AUDIT_TRAIL

where xs_user_name = 'INTEGRIGY_RAS_USER'

order by event_timestamp

If you are not familiar with XS$NULL, XS$NULL is created when the database component Oracle XML Database (XDB) is installed. XDB is now a mandatory component of 12c and as such, XS$NULL must exist in the database.  Per Oracle, XS$NULL is an internal account that represents the absence of a user in a session.  It is used by the lightweight session infrastructure for APEX, RAS and XDB and the name of this user is hard coded in those modules.  Because XS$NULL is not really a user, this account can only be accessed by the Oracle Database instance.  XS$NULL has no privileges, and no one can authenticate as XS$NULL, nor can authentication credentials ever be assigned to XS$NULL. 

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

References Tags: AuditingSecurity Strategy and StandardsOracle Database
Categories: APPS Blogs, Security Blogs

Implementation Training Initiative: ORACLE Sales Cloud and ORACLE HCM Cloud

Oracle Human Capital Management Cloud: Modern HR differentiates the business with a talent centric and consumer based strategy that leverages technology to provide a...

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

Oracle Priority Service Infogram for 04-SEP-2014

Oracle Infogram - Thu, 2014-09-04 18:01

OpenWorld
Each week leading up to OpenWorld we will be publishing various announcements, schedules, tips, etc. related to the event.
Data Warehouse Modernization.
From Brendan Tierney - Oralytics Blog: me at Oracle Open World 2014.
RDBMS
From Databases at CERN: How to verify if archived log deletion policy is correctly applied?
SQLNet
The network part of the DBA’s job often gets short shrift. That’s not a good thing, since it can be what bites you in the most serious and sudden way. Here’s an interesting article on it from AMIS: Sqlnet tracing during nightly hours…
Performance
From Kyle Hailey: Oracle performance video library.
Crossplatform transportable tablespaces - part 2, from the Oracle Performance and Backup Blog
Exadata
Oracle gives Exadata some backward compatibility, from Computerworld.
Java
Java Security, from The Java Source.
GoldenGate
Oracle GoldenGate is Certified on IBM z/OS 2.1, from Data Integration.
Coding

SOA
SOA Suite 12c and BPM Suite 12c & AppAdvantage & IoT sales kits! At our SOA Community Workspace (SOA Community membership required) from the SOA & BPM Partner Community Blog.
MySQL
Optimistic Backup, from MySQL Enterprise Backup.
OU
From Oracle University: Top 20 Training On Demand Courses.
EBS
From the Oracle E-Business Suite Support Blog:
Projects Create Accounting Performance Issues With 11.2.0.3 Database Version
Implementing SFTP with Paymentech For Oracle Payments R12
6 Sure-Fire Tips to Finding Solutions in MOS
Newly Released EBS Financials August 2014 Recommended Patch Collections (RPCs)
Problem with one time address functionality in iProcurement?
Are you looking for more information on Defaulting Rules in Purchase Orders and Agreements?

Subledger Accounting: Proration Types in Multi Period Accounting

Analyzing Twitter Data using Datasift, MongoDB and Pig

Rittman Mead Consulting - Thu, 2014-09-04 17:08

If you followed our recent postings on the updated Oracle Information Management Reference Architecture, one of the key concepts we talk about is the “data reservoir”. This is a pool of additional data that you can add to your data warehouse, typically stored on Hadoop or NoSQL databases, where you store unstructured, semi-structured or unprocessed structured data in large volume and at low cost. Adding a data reservoir gives you the ability to leverage the types of data sources that previously were thought of as too “messy”, too high-volume to store for any serious amount of time, or require processing or storing by tools that aren’t in the usual relational data warehouse toolset.

NewImage

By formally including them in your overall information management architecture though, with common tools, security and data governance over the entire dataset, you give your users the ability to consider the whole “360-degree view” of their customers and their interactions with the market.

To take an example, a few weeks ago I posted a series of articles on the blog where I captured user activity on our website, http://www.rittmanmead.com, transported it to one of our Hadoop clusters using Apache Flume, and then analysed it using Hive, Pig and finally Spark. In one of the articles I used Pig and a geocoding API to determine the country that each website visitor came from, and then in a final five-part series I automated the whole process using ODI12c and then copied the final output tables to Oracle using Oracle Loader for Hadoop. This is quite a nice example of ETL-offloading into Hadoop, with an element of Hadoop-native event capture using Flume, but once the processing has finished the data moves out of Hadoop and into the Oracle database.

NewImage

What would be interesting though would be to start adding data into Hadoop that’s permanent, not transitory as part of an ETL process, to start building out this concept of the “data reservoir”. Taking our website activity dataset, something that would really add context to the visits to our site would be corresponding activity on social networks, to see who’s linking to our posts, who’s discussing them, whether those discussions are positive or negative, and which wider networks those people belong to. Twitter is a good place to start with this as it’s the place we see our articles and activities most discussed, but it’s be good to build out this picture over time to add in activity on social networks such as Facebook, Youtube, LinkedIn and Google+; if we did this, we’d be able to consider a much broader and richer picture when looking at activity around Rittman Mead, potentially correlating activity and visits to our website with mentions of us in the press, comments made by our team and the wider picture of what’s going on in our world.

NewImage

There are a number of ways you can bring Twitter data into your Hadoop cluster or data warehouse, but the most convenient way we’ve found is to use DataSift, a social media aggregation site and service that license raw feeds from the likes of Twitter, Facebook, WordPress and other social media platforms, enhancing the data feeds with sentiment scores and other attributes, and then sell access to the feeds via a number of formats and APIs. Accessing Twitter data through DataSift costs money, particularly if you want to go back and look at historical activity vs. just filtering on a few keywords in new Twitter activity, but they’re very developer-friendly and able to provide greater volumes of firehose activity than the standard Twitter developer API allows.

So assuming you can get access to a stream of Twitter data on a particular topic – in our case, all mentions of our website, our team’s Twitter handles, retweets of our content etc – the question then becomes one of how to store the data. Looking at the Datasift Sample Output page, each of these streams delivers their payloads via JSON documents, XML-like structures that nest categories of tweet metadata within parent structures that make up the total tweet data and metadata dataset.

NewImage

And there’s a good reason for this; individual tweets might not use every bit of possible tweet metadata, for example not including entries under “mentions” or “retweets” if those aren’t used in a  particular message. Certain bits of metadata might be repeated X numbers of times – @ mentions, for example, and the JSON document might have a different structure altogether if a different JSON schema version is used for a particular tweet. Altogether not an easy type of data structure for a relational database to hold – though Oracle 12.1.0.2.0 has just introduced native JSON support to the core Oracle database – but NoSQL databases in contrast find these sorts of data structures easy, and one of the most popular for this type of work is MongoDB.

MongoDB is a open-source “document” database that’s probably best known to the Oracle world through this internet cartoon; what the video is getting at is NoSQL advocates recommending databases such as MongoDB for large-scale web work when something much more mainstream like mySQL would do the job better, but where NoSQL and document-style database come into their own is storing just this type of semi-structured, schema-on-read datasets. In fact, Datasift support MongoDB as an API end-point for their Twitter feed, so let’s go ahead and set up a MongoDB database, prepare it for the Twitter data, and then set-up a Datasift feed into it.

MongoDB installation on Linux, for example to run alongside a Hadoop installation, is pretty straightforward and involves adding a YUM repository and then running “sudo yum install mongodb-org” (there’s an OS X installation too, but I wanted to run this server-side on my Hadoop cluster). Once you’ve installed the MongoDB software, you start the mongod service to enable the server element, and then log into the mongo command-shell to create a new database.

MongoDB, being a schema-on-read database, doesn’t require you to set up a database schema up-front; instead, the schema comes from the data you load into it, with MongoDB’s equivalent of tables called “collections”, and with those collections made up of documents, analogous to rows in Oracle. Where it gets interesting though is that collections and databases only get created when you first start using them, and individual documents can have slightly, or even completely, different schema structures to each other – which makes them ideal for holding the sorts of datasets generated by Twitter, Facebook and DataSift.

[root@cdh51-node1 ~]# mongo
MongoDB shell version: 2.6.4
connecting to: test
> use datasift2
switched to db datasift2

Let’s create a couple of simple documents, and then add those to a collection. Note that the document becomes available just by declaring it, as does the collection when I add documents to it. Note also that the query language we’re using to work with MongoDB is Javascript, again making it particularly suited to JSON documents, and web-type environments.

> a = { name : "mark" }
{ "name" : "mark" }
> b = { product : "chair", size : "L" }
{ "product" : "chair", "size" : "L" }
> db
datasift2
> db.testData.insert(a)
WriteResult({ "nInserted" : 1 })
> db.testData.insert(b)
WriteResult({ "nInserted" : 1 })
> db.testData.find()
{ "_id" : ObjectId("54094081b5b6021fe9bc8b10"), "name" : "mark" }
{ "_id" : ObjectId("54094088b5b6021fe9bc8b11"), "product" : "chair", "size" : "L" }

And note also how the second entry (document) in the collection has a different schema to the entry above it – perfect for our semi-structured Twitter data, and something we could store as-is in MongoDB in this loose data format and then apply more formal structures and schemas to when we come to access the data – as we’ll do in a moment using Pig, and more formally using ODI and Hive in the next article in this series.

Setting up the Twitter feed from DataSift is a two-stage process, once you’ve got an account with them and an API key; first you define your search terms against a nested document model for the data source, then you activate the feed, in this case into my MongoDB database, and wait for the tweets to roll in. For my feed I selected tweets written by myself and some of the Rittman Mead team, tweets mentioning us, and tweets that included links to our blog in the main tweet contents (there’s also a graphical query designer, but I prefer to write them by hand using what DataSift call their “curated stream definition language” (CSDL).

NewImage

You can then preview the feed, live, or go back and sample historic data if you’re interested in loading old tweets, rather than incoming new ones. Once you’re ready you then need to activate the feed, in my case by calling a URL using CURL with a bunch of parameters (our API key and other sensitive data has been masked):

curl -X POST 'https://api.datasift.com/v1/push/create' \
-d 'name=connectormongodb' \
-d 'hash=65bd9dc4943ec426b04819exxxxxxxxx' \
-d 'output_type=mongodb' \
-d 'output_params.host=rittmandev.com' \
-d 'output_params.port=27017' \
-d 'output_params.use_ssl=no' \
-d 'output_params.verify_ssl=no' \
-d 'output_params.db_name=datasiftmongodb' \
-d 'output_params.collection_name=rm_tweets' \
-H 'Auth: rittmanmead:xxxxxxxxxxxxxxxxxxxxxxxxxxx'

The “hash” in the parameter list is the specific feed to activate, and the output type is MongoDB. The collection name is new, and will be created by MongoDB when the first tweet comes in; let’s run the curl command now and sit back for a while, and wait for some twitter activity to arrive in MongoDB …

… and a couple of hours later, eight tweets have been captured by the DataSift filter, with the last of them being one from Michael Rainey about his trip tonight to the Seahawks game:

> db.rm_tweets.count()
8
> db.rm_tweets.findOne()
{
    "_id" : ObjectId("54089a879ad4ec99158b4d78"),
    "interactionId" : "1e43454b1a16a880e074e49c51369eac",
    "subscriptionId" : "f6cf211e03dca5da384786676c31fd3e",
    "hash" : "65bd9dc4943ec426b04819e6291ef1ce",
    "hashType" : "stream",
    "interaction" : {
        "demographic" : {
            "gender" : "male"
        },
        "interaction" : {
            "author" : {
                "avatar" : "http://pbs.twimg.com/profile_images/476898781821018113/YRkKyGDl_normal.jpeg",
                "id" : 14551637,
                "language" : "en",
                "link" : "https://twitter.com/mRainey",
                "name" : "Michael Rainey",
                "username" : "mRainey"
            },
            "content" : "Greyson and I will be ready for the @Seahawks game tonight! #GoHawks! #kickoff2014 #GBvsSEA http://t.co/4u16ziBhnD",
            "created_at" : "Thu, 04 Sep 2014 16:58:29 +0000",
            "hashtags" : [
                "GoHawks",
                "kickoff2014",
                "GBvsSEA"
            ],
            "id" : "1e43454b1a16a880e074e49c51369eac",
            "link" : "https://twitter.com/mRainey/status/507573423334100992",
            "mention_ids" : [
                23642374
            ],
            "mentions" : [
                "Seahawks"
            ],
            "received_at" : 1409849909.2967,
            "schema" : {
                "version" : 3
            },
            "source" : "Instagram",
            "type" : "twitter"
        },
        "language" : {
            "tag" : "en",
                "tag_extended" : "en",
            "confidence" : 98
        },
        "links" : {
            "code" : [
                200
            ],
            "created_at" : [
                "Thu, 04 Sep 2014 16:58:29 +0000"
            ],
            "meta" : {
                "charset" : [
                    "CP1252"
                ],
                "lang" : [
                    "en"
                ],
                "opengraph" : [
                    {
                        "description" : "mrainey's photo on Instagram",
                        "image" : "http://photos-d.ak.instagram.com/hphotos-ak-xfa1/10655141_1470641446544147_1761180844_n.jpg",
                        "site_name" : "Instagram",
                        "type" : "instapp:photo",
                        "url" : "http://instagram.com/p/sh_h6sQBYT/"
                    }
                ]
            },
            "normalized_url" : [
                "http://instagram.com/p/sh_h6sQBYT"
            ],
            "title" : [
                "Instagram"
            ],
            "url" : [
                "http://instagram.com/p/sh_h6sQBYT/"
            ]
        },
        "salience" : {
            "content" : {
                "sentiment" : 0,
                "topics" : [
                    {
                        "name" : "Video Games",
                        "hits" : 0,
                        "score" : 0.5354745388031,
                        "additional" : "Greyson and I will be ready for the @Seahawks game tonight!"
                    }
                ]
            }
        },
        "trends" : {
            "type" : [
                "San Jose",
                "United States"
            ],
            "content" : [
                "seahawks"
            ],
            "source" : [
                "twitter"
            ]
        },
        "twitter" : {
            "created_at" : "Thu, 04 Sep 2014 16:58:29 +0000",
            "display_urls" : [
                "instagram.com/p/sh_h6sQBYT/"
            ],
            "domains" : [
                "instagram.com"
            ],
            "filter_level" : "medium",
            "hashtags" : [
                "GoHawks",
                "kickoff2014",
                "GBvsSEA"
            ],
            "id" : "507573423334100992",
            "lang" : "en",
            "links" : [
                "http://instagram.com/p/sh_h6sQBYT/"
            ],
            "mention_ids" : [
                23642374
            ],
            "mentions" : [
                "Seahawks"
            ],
            "source" : "<a href=\"http://instagram.com\" rel=\"nofollow\">Instagram</a>",
            "text" : "Greyson and I will be ready for the @Seahawks game tonight! #GoHawks! #kickoff2014 #GBvsSEA http://t.co/4u16ziBhnD",
            "user" : {
                "created_at" : "Sat, 26 Apr 2008 21:18:01 +0000",
                "description" : "Data Integration (#ODI #GoldenGate #OBIA) consultant / blogger / speaker @RittmanMead.\nOracle ACE.\n#cycling #Seahawks #travel w/ @XiomaraRainey\n#GoCougs!",
                "favourites_count" : 746,
                "followers_count" : 486,
                "friends_count" : 349,
                "geo_enabled" : true,
                "id" : 14551637,
                "id_str" : "14551637",
                "lang" : "en",
                "listed_count" : 28,
                "location" : "Pasco, WA",
                "name" : "Michael Rainey",
                "profile_image_url" : "http://pbs.twimg.com/profile_images/476898781821018113/YRkKyGDl_normal.jpeg",
                "profile_image_url_https" : "https://pbs.twimg.com/profile_images/476898781821018113/YRkKyGDl_normal.jpeg",
                "screen_name" : "mRainey",
                "statuses_count" : 8549,
                "time_zone" : "Pacific Time (US & Canada)",
                "url" : "http://www.linkedin.com/in/rainey",
                "utc_offset" : -25200,
                "verified" : false
            }
        }
    }
}

If you’ve not looked at Twitter metadata before, it’s surprising how much metadata accompanies what’s ostensibly an 140-character tweet. As well as details on the author, where the tweet was sent from, what Twitter client sent the tweet and details of the tweet itself, there’s details and statistics on the sender, the number of followers they’ve got and where they’re located, a list of all other Twitter users mentioned in the tweet and any URLs and images referenced.

Not every tweet will use every element of metadata, and some tweets will repeat certain attributes – other Twitter users you’ve mentioned in the tweet, for example – as many times as there are mentions. Which makes Twitter data a prime candidate for analysis using Pig and Spark, which handle easily the concept of nested data structures, tuples (ordered lists of data, such as attribute sets for an entity such as “author”), and bags (sets of unordered attributes, such as the list of @ mentions in a tweet).

There’s a MongoDB connector for Hadoop on Github which allows MapReduce to connect to MongoDB databases, running MapReduce jobs on MongoDB storage rather than HDFS (or S3, or whatever). This gives us the ability to use languages such as Pig and Hive to filter and aggregate our MongoDB data rather than MongoDB’s Javascript API, which isn’t as fully-featured and scaleable as MapReduce and has limitations in terms of the number of documents you can include in aggregations; let’s start then by connecting Pig to our MongoDB database, and reading in the documents with no Pig schema applied:

grunt> tweets = LOAD 'mongodb://cdh51-node1:27017/datasiftmongodb.rm_tweets' using com.mongodb.hadoop.pig.MongoLoader;                                                                                                                                                  2014-09-05 06:40:51,773 [main] INFO  com.mongodb.hadoop.pig.MongoStorage - Initializing MongoLoader in dynamic schema mode.                                                                                                                                        
2014-09-05 06:40:51,838 [main] INFO  com.mongodb.hadoop.pig.MongoStorage - Initializing MongoLoader in dynamic schema mode.
grunt> tweets_count = FOREACH (GROUP tweets ALL) GENERATE COUNT (tweets);                                             
2014-09-05 06:41:07,772 [main] INFO  com.mongodb.hadoop.pig.MongoStorage - Initializing MongoLoader in dynamic schema mode.
2014-09-05 06:41:07,817 [main] INFO  com.mongodb.hadoop.pig.MongoStorage - Initializing MongoLoader in dynamic schema mode.
grunt> dump tweets_count
...
(9)
grunt>

So there’s nine tweets in the MongoDB database now. Let’s take a look at one of the documents by creating a Pig alias containing just a single record.

grunt> tweets_limit_1 = LIMIT tweets 1;
2014-09-05 06:43:12,351 [main] INFO  com.mongodb.hadoop.pig.MongoStorage - Initializing MongoLoader in dynamic schema mode.
2014-09-05 06:43:12,443 [main] INFO  com.mongodb.hadoop.pig.MongoStorage - Initializing MongoLoader in dynamic schema mode.
grunt> dump tweets_limit_1
...
([interaction#{trends={source=(twitter), content=(seahawks), type=(San Jose,United States)}, twitter={filter_level=medium, text=Greyson and I will be ready for the @Seahawks game tonight! #GoHawks! #kickoff2014 #GBvsSEA http://t.co/4u16ziBhnD, mention_ids=(23642374), domains=(instagram.com), links=(http://instagram.com/p/sh_h6sQBYT/), lang=en, id=507573423334100992, source=<a href="http://instagram.com" rel="nofollow">Instagram</a>, created_at=Thu, 04 Sep 2014 16:58:29 +0000, hashtags=(GoHawks,kickoff2014,GBvsSEA), mentions=(Seahawks), user={profile_image_url_https=https://pbs.twimg.com/profile_images/476898781821018113/YRkKyGDl_normal.jpeg, location=Pasco, WA, geo_enabled=true, statuses_count=8549, lang=en, url=http://www.linkedin.com/in/rainey, utc_offset=-25200, id=14551637, time_zone=Pacific Time (US & Canada), favourites_count=746, verified=false, friends_count=349, description=Data Integration (#ODI #GoldenGate #OBIA) consultant / blogger / speaker @RittmanMead.
Oracle ACE.
#cycling #Seahawks #travel w/ @XiomaraRainey
#GoCougs!, name=Michael Rainey, created_at=Sat, 26 Apr 2008 21:18:01 +0000, screen_name=mRainey, id_str=14551637, profile_image_url=http://pbs.twimg.com/profile_images/476898781821018113/YRkKyGDl_normal.jpeg, followers_count=486, listed_count=28}, display_urls=(instagram.com/p/sh_h6sQBYT/)}, salience={content={topics=([score#0.5354745388031,additional#Greyson and I will be ready for the @Seahawks game tonight!,hits#0,name#Video Games]), sentiment=0}}, links={created_at=(Thu, 04 Sep 2014 16:58:29 +0000), title=(Instagram), code=(200), normalized_url=(http://instagram.com/p/sh_h6sQBYT), url=(http://instagram.com/p/sh_h6sQBYT/), meta={lang=(en), charset=(CP1252), opengraph=([image#http://photos-d.ak.instagram.com/hphotos-ak-xfa1/10655141_1470641446544147_1761180844_n.jpg,type#instapp:photo,site_name#Instagram,url#http://instagram.com/p/sh_h6sQBYT/,description#mrainey's photo on Instagram])}}, interaction={schema={version=3}, id=1e43454b1a16a880e074e49c51369eac, content=Greyson and I will be ready for the @Seahawks game tonight! #GoHawks! #kickoff2014 #GBvsSEA http://t.co/4u16ziBhnD, author={id=14551637, username=mRainey, language=en, avatar=http://pbs.twimg.com/profile_images/476898781821018113/YRkKyGDl_normal.jpeg, name=Michael Rainey, link=https://twitter.com/mRainey}, received_at=1.4098499092967E9, source=Instagram, mention_ids=(23642374), link=https://twitter.com/mRainey/status/507573423334100992, created_at=Thu, 04 Sep 2014 16:58:29 +0000, hashtags=(GoHawks,kickoff2014,GBvsSEA), type=twitter, mentions=(Seahawks)}, language={tag=en, confidence=98, tag_extended=en}, demographic={gender=male}},interactionId#1e43454b1a16a880e074e49c51369eac,_id#54089a879ad4ec99158b4d78,hash#65bd9dc4943ec426b04819e6291ef1ce,subscriptionId#f6cf211e03dca5da384786676c31fd3e,hashType#stream])

And there’s Michael’s tweet again, with all the attributes from the MongoDB JSON document appended together into a single record. But in this format the data isn’t all that useful as we can’t easily access individual elements in the Twitter record; what would be better would be to apply a Pig schema definition to the LOAD statement, using the MongoDB document field listing that we saw when we displayed a single record from the MongoDB collection earlier.

I can start by referencing the document fields that become simple Pig dataypes; ID and interactionId, for example:

grunt> tweets = LOAD 'mongodb://cdh51-node1:27017/datasiftmongodb.my_first_test' using com.mongodb.hadoop.pig.MongoLoader('id:chararray,interactionId:chararray','id');
2014-09-05 06:57:57,985 [main] INFO  com.mongodb.hadoop.pig.MongoStorage - Initializing MongoLoader in dynamic schema mode.
2014-09-05 06:57:58,022 [main] INFO  com.mongodb.hadoop.pig.MongoStorage - Initializing MongoLoader in dynamic schema mode.
grunt> describe tweets
2014-09-05 06:58:11,611 [main] INFO  com.mongodb.hadoop.pig.MongoStorage - Initializing MongoLoader in dynamic schema mode.
tweets: {id: chararray,interactionId: chararray}
grunt> tweets_limit_1 = LIMIT tweets 1;
...
(53fae22e9ad4ec93658b513e,1e42c2747542a100e074fff55100414a)
grunt>

Where the MongoDB document has fields nested within other fields, you can reference these as a tuple if they’re a set of attributes under a common header, or a bag if they’re just a list of values for a single attribute; for example, the “username” field is contained within the author tuple, which in-turn is contained within the interaction tuple, so to count tweets by author I’d need to first flatten the author tuple to turn its fields into scalar fields, then project out the username and other details; then I can group the relation in the normal way on those author details, and generate a count of tweets, like this:

grunt> tweets = LOAD 'mongodb://cdh51-node1:27017/datasiftmongodb.rm_tweets' using com.mongodb.hadoop.pig.MongoLoader('id:chararray,interactionId:chararray,interaction:tuple(interaction:tuple(author:tuple(id:int,language:chararray,link:chararray,name:chararray,username:chararray)))','id');
grunt> tweets_author_tuple_flattened = FOREACH tweets GENERATE id, FLATTEN(interaction.$0);                                            
grunt> tweets_with_authors = FOREACH tweets_author_tuple_flattened GENERATE id, interaction::author.username, interaction::author.name;
grunt> tweets_author_group = GROUP tweets_with_authors by username; 
grunt> tweets_author_count = FOREACH tweets_author_group GENERATE group, COUNT(tweets_with_authors); 
...
(rmoff,1)
(dw_pete,1)
(mRainey,3)
(P_J_FLYNN,3)
(davidhuey,7)
(EdelweissK,1)
(JamesOickle,3)
(markrittman,3)
(rittmanmead,2)
(RedgraveChris,1)
grunt>

So there’s obviously a lot more we can do with the Twitter dataset as it stands, but where it’ll get really interesting is combining this with other social media interaction data – for example from Facebook, LinkedIn and so on – and then correlating that with out main site activity data. Check back in a few days when we’ll be covering this second stage in a further blog article, using ODI12c to orchestrate the process.

Categories: BI & Warehousing

Partner Webcast – Oracle WebLogic 12c Enabling Development of Modern Applications: WebSocket and Maven Support

Cloud Application Foundation is the innovator’s complete and integrated modern cloud application infrastructure, built using best of breed components, such as Oracle WebLogic Server 12c, the...

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

Migrating Existing PeopleSoft Attachments into the Managed Attachments Solution

This post comes from Fishbowl’s Mark Heupel. Mark is an Oracle Webcenter consultant, and he has worked on a few different projects over the last year helping customers integrate WebCenter with Oracle E-Business Suite and PeopleSoft. One of WebCenter’s strengths is it provides these integrations out-of-the-box, including a document imaging integration to automate invoice processing with WebCenter’s capture, forms recognition and imaging capabilities, as well as workflows leveraging Oracle Business Process Management. Mark discusses WebCenter’s integration with PeopleSoft and its managed attachments solution below.

Application Integration

Oracle’s Managed Attachments solution enables business users in PeopleSoft to attach, scan, and retrieve document attachments stored in an Oracle WebCenter Content Server repository.

One of the issues that our clients face when moving to Oracle’s Managed Attachments solution is determining what to do with the attachments that already exist in PeopleSoft. We at Fishbowl have come up with a method to migrate these attachments into WebCenter Content in bulk while still maintaining the attachments’ context within PeopleSoft.

A high-level view of the solution is as follows. Queries are written on the PeopleSoft side to export each of the attachments, as well as a file containing each attachment’s metadata and PeopleSoft contextual information, to a network share. This is a task done by a PeopleSoft administrator. We then use our Enterprise Batchloader product to bulk load these files into WebCenter Content. We’ve written a customization that overrides the set of services that qualify for Managed Attachments to include our Enterprise Batchloader service. Since the context of the attachments is included in the metadata file, the Enterprise Batchloader check-ins work in the same way that a normal check-in from Managed Attachments would and the attachments retain their PeopleSoft context. Let’s get into the details of how this works.

Managed Attachments Overview

In order to understand the migration strategy, we first need to understand how Managed Attachments works under the covers. The important piece to know for this migration is that the table that stores the Managed Attachment object information on the WebCenter side is the AFObjects table. This table stores the PeopleSoft context information as well as the dDocName of each of the attachments currently being stored in WebCenter. Here is an example of what the AFObjects table looks like:

AFObjects Table

Each row in this table represents one PeopleSoft attachment being managed in WebCenter Content. The dAFApplication, dAFBusinessObjectType, and dAFBusinessObject fields make up the context for where the attachment is located in PeopleSoft. The dAFApplication field represents the application, the dAFObjectType field represents the page, and the dAFBusinessObject field is a pipe delimited list of the primary key values from the page where the attachment is located in PeopleSoft. The dDocName field is simply the dDocName of the content item in WebCenter.

When a user clicks the Managed Attachments link on the PeopleSoft screen a request is made over to WebCenter that contains the contextual page information from PeopleSoft (dAFApplication, dAFBusinessObjectType, and dAFBusinessObject). Using this contextual information, a query is then made against the AFObjects table to find the content IDs of the attachments that should be returned back to the user. A similar request is made when a user checks in a document through the Managed Attachments screen in PeopleSoft. The PeopleSoft context information is sent to WebCenter, the document is checked in, and then a row is inserted into the AFObjects table that contains the PeopleSoft contextual information as well as the dDocName of the newly checked-in document.

Loading Content into WebCenter

In order to be able to successfully load a large number of content items into WebCenter, while still maintaining the correct PeopleSoft context, we had to write a customization to hook into the existing Managed Attachments check-in functionality. The AppAdapterCore component, one of the two components installed on WebCenter for Managed Attachments, contains the core Managed Attachments code. This component contains a list of services such as CHECKIN_NEW that, when called with the PeopleSoft contextual information in the binder (dAFApplication, dAFObjectType, and dAFObject), executes the query that inserts a row into the AFObjects table. The customization that we wrote overrides the list of services specified in the AppAdapterCore component to include our Enterprise Batchloader check-in services. By doing so, we’re able to hook into the same insert query that Managed Attachments already uses, assuming we have placed the correct PeopleSoft context information in the binder.

Here is an example of what a standard Enterprise Batchloader blf (batch load file) would look like:

Batch Load File
As you can see, the file simply contains the action to take (insert), the location of the primary file, and the required metadata fields for WebCenter. In order to assign the correct PeopleSoft context we simply need to specify the dAFApplication, dAFObjectType, and dAFObject fields in the blf file:

Batch Load File 2

This effectively places each of those fields into the binder in WebCenter. When Enterprise Batchloader is run and performs its check-ins into WebCenter, the Managed Attachments functionality gets called and a row is inserted into the AFObjects table for each attachment that specifies the PeopleSoft context information. As long as the correct PeopleSoft contextual information is placed into the Enterprise Batchloader blf file, we’re able to bulk load as many attachments as needed into WebCenter while still retaining the correct PeopleSoft context information for use with the Managed Attachments solution.

I hope this provides you with an example of how your existing PeopleSoft Managed Attachments content could be migrated to WebCenter. After all, getting this content into WebCenter has many additional benefits, such as version control, renditions, retention management and the ability to surface this content to WebCenter-based mobile apps and portals. If you have questions or would like to engage with Fishbowl on such projects, please email info@fishbowlsolutions.com.

 

The post Migrating Existing PeopleSoft Attachments into the Managed Attachments Solution appeared first on Fishbowl Solutions' C4 Blog.

Categories: Fusion Middleware, Other

OpenWorld - Absorb and Adapt

Floyd Teter - Thu, 2014-09-04 15:21
It's September.  Which means the marketing and sales nirvana that is Oracle OpenWorld is upon us.

OOW is a big show...one person simply cannot take it all in.  You have to "pick your spots".

In the spirit of my previous blog post, my focus at OOW this year is on absorbing and adapting.  I'm up to my eyeballs in a new market with Higher Education, and I'm still learning about those customers.  So that's my overall filter going into OOW - soaking up the information relevant to Higher Ed.  Means I'll be spend quite a bit of time at the Marriott Marquis, as that's where the Higher Ed events are taking place.  I'm specifically checking for news and messaging on:
  • Oracle's upcoming Student Cloud and Higher Education Cloud offerings - especially news on the post-award Grants Management functionality under development for Oracle's Fusion Project Portfolio Management applications suite.
  • How Simplified UI will be applied to Student Cloud
  • Oracle Business Intelligence, especially Student Information Analytics
  • The future, if any, for Informatica and Oracle BI - because so many colleges and universities use Informatica with Oracle BI today
  • The upcoming release of Campus Solutions 9.2
  • Mobile, especially in regards to the future direction of Campus Solutions Mobile and Oracle's Mobile Applications Foundation
I'll likely do some digging into some favorite areas as well, such as the EBS tech stack and new developments with Oracle ADF.  Might even do some digging into the latest info on the Oracle BPM Suite if the opportunity arises.

What I learn at OOW will probably set my direction for research, presentations, etc. for the coming year...that's the "adapt" part.

Only one presentation for me this year.  So I'll put in a plug for it right now.  I'm sitting on a panel for:

General Session: Oracle’s Future in Higher Education (GEN7628)
Tuesday, Sep. 30, 10:15-11:45 AM – Marriott Marquis – Golden Gate C3



Swing by and introduce yourself if you get a chance, especially if you're a customer or have an interest in Higher Ed.

One last thing:  I'm always interested in how others manage their time at OOW.  So find the comments and let us know what you're hoping to gain from OOW this year.

OS Watcher Paging Example

Bobby Durrett's DBA Blog - Thu, 2014-09-04 13:13

I have an example of paging in some Exadata OS Watcher log files.  We got an error in the alert log about a high load but reviewing AWR reports it did not seem like we had an unusual number of active sessions.  Also, the CPU use seemed low but the system load was high.  Reviewing the OS Watcher logs and finding a similar case on Oracle’s support site convinced me that our issue was due to a large amount memory being paged out to the swap file.

Here is the top report from the problematic time:

top - 11:07:58 up 239 days, 14:57,  0 users,  load average: 204.84, 183.48, 106.74
Tasks: 1097 total,  71 running, 1022 sleeping,   0 stopped,   4 zombie
Cpu(s):  1.3%us, 98.0%sy,  0.0%ni,  0.2%id,  0.5%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  74026796k total, 72456380k used,  1570416k free,     2436k buffers
Swap: 16771852k total,  7884656k used,  8887196k free,   597336k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                   
  894 oracle    15   0  324m  26m  10m S 309.1  0.0 129:46.34 /u01/app/11.2.0.2/grid/bin/diskmon.bin -d -f                                             
 1053 root      20  -5     0    0    0 R 100.0  0.0  32:10.62 [kswapd0]                                                                                
 3366 oracle    16   0 1440m  54m  18m S 146.6  0.1   3:03.54 /u01/app/11.2.0.2/grid/bin/oraagent.bin                                                  
23809 oracle    18   0  992m 179m  16m S 128.1  0.2 158:45.67 /u01/app/oracle/Middleware/agent11g/bin/emagent                                          
28878 orarom    18   0  982m 360m  11m S 101.5  0.5  94:58.36 /opt/OracleHomes/agent_home/core/12.1.0.3.0/jdk/bin/java -Xmx154M -XX:MaxPermSize=96M -se
 4501 oracle    19   0 4423m  96m  24m R 89.6  0.1   2435:54 ora_dia0_ourprdfs1                                                                        
18318 oracle    20   0 26.3g 610m  70m R 86.3  0.8   2025:17 ora_p001_ourprd1                                                                          
18226 oracle    18   0 26.3g 595m  61m D 82.3  0.8 212:20.62 ora_p013_ourprd1                                                                          
 7619 oracle    19   0 1727m  16m  14m R 80.5  0.0   0:50.29 oracle+ASM1 (LOCAL=NO)                                                                    
 4389 oracle    25   0 25.9g 158m  27m R 78.5  0.2   2557:06 ora_dia0_ourprd1                                                                          
11008 oracle    18   0 26.3g 561m 133m D 77.9  0.8 373:34.37 ora_p010_ourprd1                                                                          
 8484 root      25   0  238m 6276 2600 R 77.3  0.0   0:46.60 python /usr/local/bin/dcli -s -q -l root -g cell_group exec 2>&1; cellcli -e list quaranti
 7937 oracle    18   0 26.3g 603m  86m R 76.5  0.8   1656:21 ora_p021_ourprd1                                                                          
24118 oracle    25   0 26.3g 602m 103m R 72.7  0.8   2997:08 ora_p007_ourprd1                                                                          
 4491 oracle    16   0 5912m 121m  25m D 72.6  0.2   2407:59 ora_dia0_obrprd1                                                                          
 7919 oracle    25   0 26.4g 680m 247m R 72.2  0.9   4256:57 ora_p011_ourprd1                                                                          
 4384 oracle    18   0 4407m  95m  25m D 70.8  0.1   2344:36 ora_dia0_otrprd1                                                                          
 4397 oracle    23   0 25.8g  44m  23m D 69.5  0.1   1695:21 ora_lmd0_ourprd1                                                                          
 2548 oracle    20   0 1809m 116m  44m R 69.3  0.2   2393:41 asm_dia0_+ASM1

Note the load of 204 and the system cpu % of 98%.  By 11:07 CPU utilization had gone back up so I assume that the swapping I/O had completed but then a bunch of system I/O kicked off.  Also I assume that [kswapd0] has something to do with swapping.  Here is the mpstat report:

             CPU   %user   %nice    %sys %iowait    %irq   %soft  %steal   %idle    intr/s
10:59:00     all    3.49    0.00    2.05   93.81    0.01    0.16    0.00    0.48   4144.00
10:59:05     all    4.94    0.00    2.45   92.21    0.01    0.11    0.00    0.28   2548.00
10:59:16     all    1.42    0.00    1.82   95.64    0.02    0.13    0.00    0.97   3108.66
10:59:21     all    3.84    0.00    3.58   90.68    0.04    0.49    0.00    1.38   3868.40
10:59:26     all    1.99    0.00    1.85   93.56    0.01    0.13    0.00    2.46   2942.48
10:59:31     all    1.38    0.00    1.85   91.37    0.01    0.10    0.00    5.29   2479.04
10:59:36     all    1.75    0.00    2.81   94.19    0.01    0.16    0.00    1.08   2762.60
10:59:41     all    0.89    0.00    1.09   97.41    0.00    0.05    0.00    0.56   2116.80
10:59:46     all    1.19    0.00    1.21   93.91    0.03    0.11    0.00    3.55   2992.40
10:59:51     all    1.79    0.00    1.75   95.88    0.01    0.28    0.00    0.29   3820.00
10:59:56     all    2.09    0.00    1.55   92.85    0.03    0.21    0.00    3.28   4410.80
11:00:01     all    1.85    0.00    1.91   96.00    0.01    0.11    0.00    0.11   3433.47
11:00:06     all    1.50    0.00    1.45   89.07    0.01    0.11    0.00    7.85   3746.00
11:00:11     all    6.41    0.00   10.68   80.29    0.09    0.80    0.00    1.73   9269.46
11:00:16     all   17.14    0.00   19.42   57.12    0.14    0.83    0.00    5.35  17018.64
11:00:21     all    8.58    0.00   80.85    9.01    0.05    0.23    0.00    1.29   6951.70
11:00:27     all    6.21    0.00   91.77    1.12    0.04    0.09    0.00    0.78   5417.11
11:00:32     all    5.65    0.00   93.75    0.53    0.01    0.04    0.00    0.02   3289.82
11:00:38     all    2.72    0.00   96.81    0.35    0.01    0.02    0.00    0.09   2480.87
11:00:44     all    1.73    0.00   97.89    0.28    0.00    0.02    0.00    0.08   1745.89
11:01:00     all    1.85    0.00   97.72    0.16    0.01    0.04    0.00    0.22   2710.93
11:01:05     all    0.79    0.00   98.84    0.25    0.00    0.04    0.00    0.09   2543.39
11:02:23     all    1.00    0.00   98.72    0.15    0.00    0.02    0.00    0.10   1558.00
11:02:33     all    0.95    0.00   98.74    0.09    0.00    0.09    0.00    0.13   1488.49
11:02:39     all    1.64    0.00   98.13    0.08    0.01    0.05    0.00    0.08   1441.73
11:02:44     all    0.46    0.00   99.29    0.10    0.00    0.09    0.00    0.06   1465.73
11:03:20     all    1.54    0.00   97.96    0.25    0.01    0.07    0.00    0.17   2757.95
11:03:25     all    0.40    0.00   99.24    0.21    0.00    0.02    0.00    0.12   1550.60
11:03:30     all    0.39    0.00   99.22    0.23    0.00    0.01    0.00    0.15   1421.20
11:03:35     all    3.29    0.00   96.32    0.25    0.01    0.03    0.00    0.10   1952.51
11:03:40     all    5.90    0.00   93.76    0.28    0.00    0.01    0.00    0.05   1407.00
11:03:45     all    1.14    0.00   98.56    0.20    0.00    0.01    0.00    0.09   1418.00
11:03:50     all    0.19    0.00   99.55    0.21    0.00    0.03    0.00    0.03   1356.69
11:04:23     all    0.36    0.00   99.31    0.18    0.00    0.04    0.00    0.10   1621.67
11:06:13     all    0.58    0.00   99.13    0.14    0.00    0.04    0.00    0.11   1550.73
11:06:18     all    3.32    0.00   96.36    0.23    0.00    0.04    0.00    0.06   2055.30
11:06:24     all    4.39    0.00   95.32    0.24    0.00    0.02    0.00    0.03   1991.53
11:06:31     all    4.14    0.00   95.48    0.28    0.01    0.02    0.00    0.07   1598.53
11:07:21     all    1.38    0.00   98.32    0.19    0.00    0.01    0.00    0.09   1444.49

Notice how it is almost all IO until around 11 am and then it switches to all system cpu%.  I assume the I/O was swapping memory out.  The meminfo report has these numbers:

zzz ***Mon Sep 1 11:00:11 CDT 2014
MemTotal:     74026796 kB
MemFree:        330888 kB
...
SwapTotal:    16771852 kB
SwapFree:      8153352 kB

I take this to mean that we have about 330 meg free out of 74 gig total, but still 8 gig of swap.  Presumably when free memory gets low then idle processes get swapped out.  A member of our Unix team pointed out the swapping on the vmstat output.  Here is a section that shows a lot of pages getting swapped out (so).  Each line represents 5 seconds and I think it starts 10:57:56.

zzz ***Mon Sep 1 10:05:31 CDT 2014 Sample interval 5 seconds
-- deleted 629 lines = 629*5 seconds = 3145 seconds
-- 10:57:56 am
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
86 16 7567956 328800    800 610940   56 5350   157  5381 1626 10013  4 96  0  0  0
44 32 7602804 327956    996 613940  222 10464   554 10510 2177 13818  0 99  0  0  0
 1 109 7632732 330412   2284 619780  290 7986  1738  8130 3382 14002  1 21  7 70  0
35 41 7702076 353744   2504 621392  623 16054  1290 16206 8776 19234  2  3  2 92  0
 1 103 7749296 327480   2692 622004  422 11307   789 11574 3839 16138  3  2  1 94  0
 4 109 7849812 330972   3036 624808 1974 24691  2844 25110 11705 36080  4  3  1 92  0
 0 66 7869628 346228   3108 626444  859 6309  1055  6309 5538 16922  3  1  1 94  0
23 104 7895176 1998796   3300 624864 1032 7582  1283  7784 4032 24443 56  3  1 40  0
 3 90 7918016 328212   4144 632880 4910 5939  6866  6406 13908 47820 40  8  3 49  0
 1 101 7983136 333008   4480 627416 1180 15655  1523 15960 3977 16945  5  3  1 91  0
 1 125 8033148 327376   4712 628312  922 12213  1302 12295 3545 16946  4  3  0 93  0
 1 141 8072048 329060   4856 626976  764 10862   986 11082 3684 21305  2  1  1 96  0
 2 76 8106056 327556   5056 627124  797 8855  1048  8946 3968 15044  2  2  0 96  0
 0 103 8181216 328348   5284 611140 2415 16583  3076 16938 4374 17445  4  5  2 90  0
 4 92 8222500 330044   5408 610708 1470 9698  1874  9898 2842 15110  2  2  2 94  0
 4 94 8285204 328932   5936 614128 1015 14754  1678 15213 3324 18240  2  2  5 91  0
 1 111 8326908 327348   6184 614600 1034 9759  1465 10012 2577 14809  2  2  1 95  0
 1 119 8338520 328352   6332 616076  610 4896   834  4952 2505 15373  1  1  1 97  0
 0 98 8378304 329308   6536 615944  975 9795  1334  9931 3802 15897  2  2  3 93  0
 0 89 8404456 327204   6800 618872 1232 8327  1662  8413 4557 16856  2  2  2 94  0
 0 123 8430092 327272   7092 619016 1004 7379  1393  7672 2852 14689  1  1  2 96  0
 1 92 8453420 327212   7284 622700 1738 7250  2259  7494 4241 17781  2  2  2 94  0
 7 76 8562308 329132   7860 628932 2616 23847  3409 25162 6833 20938  4  7  7 82  0
 5 60 8691628 334124   7012 635948 6066 28059  8119 32201 13584 32154 15 23  1 61  0
63 37 8683600 327404   2812 582200 8138  661 12947  2870 12507 26754 12 44  6 38  0
98 22 8696516 328056   1260 560572 10494 7070 15550  9394 28854 128944  3 96  0  0  0
72 14 8694008 327148   1960 557616 1693    3  3222   161 5014 29030  5 95  0  0  0
125 19 8693816 327172   1020 558488  226    1   569   227 1991 13841  0 99  0  0  0
97 15 8703600 327044   1032 554792 5083 3217 10447  3722 26436 202860  1 99  0  0  0
102 20 8733556 327272   1432 556224 2573 6713  5399  7194 16118 66262  1 98  0  0  0
84  9 8732984 327164   1100 555640  470    2  1090   117 1585 11922  0 99  0  0  0
85 13 8732940 327304    880 554024   60    0   317    50 1470 11247  0 99  0  0  0
149 24 8732808 327428    988 553988  133    0   814   334 1962 12802  4 95  0  0  0
153 22 8732640 327284    592 553576  187    0   875   548 4619 36573  2 98  0  0  0
159 15 8732584 327104    604 552956   50    0    96   118 2294 11239  0 99  0  0  0
142 14 8732432 328644   1524 554196  174    0  1706   260 6035 45843  0 99  0  0  0
158 12 8755436 327460   2704 555568 1130 4927  2564  5486 14539 103001  0 99  0  0  0
125 14 8754920 327980    748 554368  406    0   662     6 1583 10952  0 100  0  0  0
115 16 8752204 327472   1724 553740 2453    5  6014   532 11366 82957  1 99  0  0  0
124 19 8752276 327076   1092 554200   78   28   113    81 1767 11712  1 99  0  0  0
82  8 8757568 346356   2384 556132 1316 3202  4373  3837 6633 42986  1 99  0  0  0
151 57 8756348 329524   3632 559164  828    0  3718   429 2076 11613  3 96  0  0  0
159 49 8755120 328488   3248 557720 1033    9  4298   212 2248 12981  4 95  0  0  0
107 36 8771748 332068   1016 557272 2765 3977  9481  4370 14773 103588  2 98  0  0  0
117 16 8771592 327180   1168 558788  156    0   979    58 1466 9988  1 99  0  0  0
 3 101 7875688 1448712   5300 599300 29898 73754 52723 83528 62497 360936  2 94  0  4  0
 3 96 7874004 1260800   8856 624552 7470    0 14911   569 3814 17649  5  1 33 61  0
 1 92 7859256 558968  11228 649948 5590    0 11118  2798 10027 41964 20  5 12 63  0
 0 96 7915604 327600  14060 652636 4674 11822  7818 12173 9053 22067  8  7 20 65  0
 8 98 7963060 327240  11800 635164 1994 11097  3294 12031 6762 16549  3  7  1 90  0
 1 104 7969976 340320   8812 579564 9600 1558 12002  2998 8588 22984 17 34 10 39  0
 4 89 7642788 666372  10244 603740 7154    0 12148  2375 9681 23420  7  5 22 67  0
 1 94 7642548 327912  12556 625672 7011    3 12173  1704 7865 21465  6  5 18 71  0
 7 95 7630564 327572   3396 557488 5970   33 10358  1827 4756 15284  5 67  3 25  0
113 45 7630124 330244   1900 545524 3819    5  7540  1314 3781 13454  2 88  0 10  0
 0 88 6897380 1321012   3560 573532 3635    1  9076  2802 3414 11193  3 64  7 26  0
 1 94 6889436 981560   6188 624532 8463    0 16870  4122 4949 17243  2  1 31 65  0
 0 79 6889308 890800   8444 666940 7332    0 13390  3077 3337 15930  5  1 33 60  0
 1 74 6878180 797212  10720 715612 6776    0 14430  4653 3424 13069 11  6 37 47  0
 0 141 6876812 406704  12856 739628 7202    0 11406  2342 4737 23064  6  5 24 64  0
 2 66 6874848 336480  13428 656312 6296    1 10126  1848 4375 16755  7  9 32 52  0
 2 69 6874192 338016   8288 577196 7297    0 14230  1208 5771 16410 10  5 38 47  0
 1 72 6873696 342908   3796 576568 6161    1 12562  1149 5506 13421 11  5 34 50  0
 2 78 6873524 336716   3992 589684 5718    0 12693   280 5268 11889  9  2 31 58  0
97 48 6873180 331280   2884 559732 5110    4  7616   934 6994 14502  7 38 12 43  0
120 41 6872636 327192   1504 549972 4309   32  7965  1497 7029 14159  5 92  0  3  0
101 26 6870204 327620   1992 548548 4122   19  8110  1191 9559 20993  4 96  0  0  0
82 16 6886048 327184    984 547140 2050 3186  3662  3385 4978 16982  2 98  0  0  0
83  6 6886384 327096   1376 545516  563   69  1284   184 1946 7812  1 99  0  0  0
93 11 6886364 328100    836 545120  798    0   911    18 5495 23655  0 100  0  0  0
27 42 7008372 328376   3216 550820 7050 26176  9220 26390 9919 42056  0 100  0  0  0
 3 112 7044188 328628   4552 554876 2424 8493  3526  8570 2402 7432  2 78  1 19  0
 3 113 7108916 348172   5532 560420 3186 14382  4325 14512 9715 15529  4  6  4 87  0
 0 125 7136504 366528   7040 567140 4077 10618  5413 10906 15231 26777  4  1 12 83  0
 1 111 7149172 387580   7320 568044  391 4344   719  4394 2460 7519  1  1  0 99  0
 1 94 7178000 328136   9984 580152 5768 7927  8617  8990 20026 30064  8  3  9 80  0
 0 86 7204696 327100  10268 583912 1972 6991  2825  7515 5943 12124  3  2  8 87  0
 0 103 7218656 347300  10564 585360  457 4985   733  5144 1936 8120  0  1  6 93  0
 5 98 7236196 345460  10784 585224 1298 5318  1467  5477 7973 13715  3  2  6 89  0
 1 111 7243488 328920  10932 586500  488 3097   839  3136 1843 7555  1  1  6 92  0
 0 103 7265100 328016  11132 591452 1439 6088  2170  6241 3984 9707  2  1  2 95  0
 2 91 7274576 335116  11272 591240  709 3526   789  3750 2162 7734  3  1  2 95  0
 1 124 7303804 329200  11408 593516 1170 7348  1587  7614 4230 9955  3  1  6 90  0
 4 61 7403224 343940  11724 601684 3143 20878  4191 22518 7000 14053  4  4  3 89  0
11 62 7589208 349448  12504 607764 4350 38374  5784 38847 13038 21696  6  6  9 79  0
 3 56 7777608 338348  13328 614952 6506 38412  7919 38711 11993 18575  6  6 16 71  0
 2 49 8025300 331604  13568 618408 5642 50410  6727 50490 14740 19779  7  7 35 51  0
 1 69 8159548 977560  14304 624024 4725 27423  6727 28257 13122 22037 10  7 12 72  0
 5 50 8159156 869324  18188 647964 9421    0 13542  1034 14117 30070 16  4 24 55  0
 2 46 8158644 768600  20324 663440 10323    0 13395  2023 18356 27259 15  3 52 30  0
 3 43 8158308 551160  22344 678548 12241    0 16135   623 14080 23349 19  3 42 36  0

We got this message in the alert log:

Mon Sep 01 11:09:28 2014
 GES: System Load is HIGH.
 GES: Current load is 204.89 and high load threshold is 80.00

After this the instance crashed:

Instance terminated by LMS0, pid = 4532

Bug 17316830, “ROOT CAUSE ALL DATABASE INSTANCES CRASHED ON ONE OF OUR NODES”, relates high paging and low free memory to the same GES load messages and to instances crashing as ours did.  So, it seems that when you have a lot of paging it can cause an instance to crash.  Is this a bug or a feature? :)

It was interesting to look at the OS Watcher files to see information about the nature of the system when our issues occurred.  Looks like lots of paging.  The only question is what caused the paging?  We are trying to figure out if we just had some abnormally high memory use from our application or if some bug caused some system processes to eat up the normally free memory.  But, I thought I would share the investigation we did using the OS Watcher logs because it was interesting to see an example with paging, even though we have not gotten to the root of the problem.

– Bobby

 

Categories: DBA Blogs

Group By Bug

Jonathan Lewis - Thu, 2014-09-04 10:11

This just in from OTN Database Forum – a surprising little bug with “group by elimination” exclusive to 12c.


alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';

select
       /* optimizer_features_enable('12.1.0.1')*/
       trunc (ts,'DD') ts1, sum(fieldb) fieldb
from (
  select
        ts, max(fieldb) fieldb
  from (
  select trunc(sysdate) - 1/24 ts, 1 fieldb from dual
  union all
  select trunc(sysdate) - 2/24 ts, 2 fieldb from dual
  union all
  select trunc(sysdate) - 3/24 ts, 3 fieldb from dual
  union all
  select trunc(sysdate) - 4/24 ts, 4 fieldb from dual
  union all
  select trunc(sysdate) - 5/24 ts, 5 fieldb from dual
  )
  group by ts
)
group by trunc (ts,'DD')
/

You might expect to get one row as the answer – but this is the result I got, with the execution plan pulled from memory:


TS1                      FIELDB
-------------------- ----------
03-Sep-2014 00:00:00          1
03-Sep-2014 00:00:00          5
03-Sep-2014 00:00:00          4
03-Sep-2014 00:00:00          2
03-Sep-2014 00:00:00          3

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |    11 (100)|          |
|   1 |  HASH GROUP BY   |      |     5 |    60 |    11  (10)| 00:00:01 |
|   2 |   VIEW           |      |     5 |    60 |    10   (0)| 00:00:01 |
|   3 |    UNION-ALL     |      |       |       |            |          |
|   4 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

You’ll notice that I’ve got an “optimizer_features_enable()” comment in the code: if I change it into a hint I get the following (correct) result and plan:


TS1                      FIELDB
-------------------- ----------
03-Sep-2014 00:00:00         15

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |    12 (100)|          |
|   1 |  HASH GROUP BY   |      |     5 |    60 |    12  (17)| 00:00:01 |
|   2 |   VIEW           |      |     5 |    60 |    11  (10)| 00:00:01 |
|   3 |    HASH GROUP BY |      |     5 |    60 |    11  (10)| 00:00:01 |
|   4 |     VIEW         |      |     5 |    60 |    10   (0)| 00:00:01 |
|   5 |      UNION-ALL   |      |       |       |            |          |
|   6 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
|   9 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
|  10 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

Somehow 12.1.0.2 has managed to get confused by the combination of “group by ts” and “group by trunc(ts,’DD’)” and has performed “group-by elimination” when it shouldn’t have. If you use the ‘outline’ option for dbms_xplan.display_cursor() you’ll find that the bad result reports the hint elim_groupby(@sel$1), which leads to an alternative solution to hinting the optimizer_features level. Start the code like this:


select
       /*+ qb_name(main) no_elim_groupby(@main) */
       trunc (ts,'DD') ts1, sum(fieldb) fieldb
from  ...

The (no_)elim_groupby is a hint that appeared in v$sql_hints only in the 12.1.0.2.


What's the consequence of NOLOGGING loads?

Yann Neuhaus - Thu, 2014-09-04 09:25

When you load data in direct-path and have the NOLOGGING attribute set, you minimize redo generation, but you take the risk, in case of media recovery, to loose the data in the blocks that you've loaded. So you probably run a backup as soon as the load is done. But what happens if you have a crash, with media failure, before the backup is finish?
I encountered recently the situation but - probably because of a bug - the result was not exactly what I expected. Of course, before saying that it's a bug I need to clear any doubt about what I think is the normal situation. So I've reproduced the normal situation and I'm sharing it here in case someone wants to see how to handle it.

First, let me emphasize something that is very important. I didn't say that you can loose the data that you've loaded. You loose the data which were in the blocks that have been allocated by your load. It may concern conventional DML happening long time after the nologging load. And anyway, you probably loose the whole table (or partition) because as you will see the proper way to recover from nologging recovery is to truncate the table (or partition).

I'm in 12c so I can run my SQL statements from RMAN. I create a DEMO tablespace and a 1000 rows table in it:

RMAN> echo set on

RMAN> create tablespace DEMO datafile '/tmp/demo.dbf' size 10M;
Statement processed

RMAN> create table DEMO.DEMO pctfree 99 tablespace DEMO nologging as select * from dual connect by level commit;
Statement processed

Imagine that I've a media failure and I have to restore my tablespace:

RMAN> alter tablespace DEMO offline;
Statement processed


RMAN> restore tablespace DEMO;
Starting restore at 04-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK

creating datafile file number=2 name=/tmp/demo.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 04-SEP-14

and recover up to the point of failure:

RMAN> recover tablespace DEMO;
Starting recover at 04-SEP-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 04-SEP-14

RMAN> alter tablespace DEMO online;
Statement processed

Then here is what happen when I want to query the table where I've loaded data without logging:

RMAN> select count(*) from DEMO.DEMO;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/04/2014 16:21:27
ORA-01578: ORACLE data block corrupted (file # 2, block # 131)
ORA-01110: data file 2: '/tmp/demo.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Let's see that:

RMAN> validate tablespace DEMO;
Starting validate at 04-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/tmp/demo.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     167            974          1280            6324214
  File Name: /tmp/demo.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              306

Finished validate at 04-SEP-14

167 blocks have been marked as corrupt.
The solution is to truncate the concerned table.
And if you don't know what are the tables that are concerned then you need to check v$database_block_corruption and dba_extents. So, my advise is that the tables loaded in NOLOGGING should be documented in the recovery plan, with the way to reload the data. Of course, that's not an easy task because NOLOGGING is usually done by developers and recovery is done by the DBA. The other alternative is to prevent any NOLOGGING operation and put the database in FORCE LOGGING. In a Data Guard configuration, you should do that anyway.

So I truncate my table:

RMAN> truncate table DEMO.DEMO;
Statement processed

and if I check my tablespace, I still see the blocks as 'Marked Corrupt':

RMAN> validate tablespace DEMO;
Starting validate at 04-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/tmp/demo.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     167            974          1280            6324383
  File Name: /tmp/demo.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              306

Finished validate at 04-SEP-14

This is the normal behaviour. The blocks are still marked as corrupt until they are formatted again.

I put back my data;

RMAN> insert /*+ append */ into DEMO.DEMO select * from dual connect by level commit;
Statement processed

And check my tablespace again:

RMAN> validate tablespace DEMO;
Starting validate at 04-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/tmp/demo.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              974          1280            6324438
  File Name: /tmp/demo.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              167
  Index      0              0
  Other      0              139

Finished validate at 04-SEP-14

The 167 corrupted blocks have been reused, now being safe and containing my newly loaded data.
This is the point I wanted to validate because I've seen a production database where the blocks remained marked as corrupted. The load has allocated exents containing those blocks but, fortunately, has avoided to put rows in it. However, monitoring is still reporting corrupt blocks and we have to fix that as soon as we can move the tables to another tablespace.

Last point. If you want to see if some tablespace had NOLOGGING operations since the last backup, run:

RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
2    full                    /tmp/demo.dbf

This is an indication that you should backup that datafile now. Knowing the objects concerned if a lot more complex...

I'll not open a SR as I can't reproduce the issue I encountered (corrupt flag remaining after reallocating blocks) but if anyone had that kind of issue, please share.

My Oracle Openworld APEX Sessions

John Scott - Thu, 2014-09-04 01:53

Oracle Openworld 2014 is just around the corner (it’s still not too late to register) and I’m going to be presenting a couple of sessions –

  • Oracle Application Express + Node.JS: A Primer [UGF5222] – Sunday, Sep 28, 2:30 PM – 3:15 PM - Moscone South – 300
  • Testing Oracle Application Express: Removing the Boring from Testing [CON5219] – Thursday, Oct 2, 2:30 PM – 3:15PM - Moscone South – 303

I presented these sessions at KScope14 and got great feedback from both sessions (I’m very proud to say I got a Top Speaker Award for the Testing session).

Hope to see you there!

Variable in list with Postgres JDBC and Greenplum

Pas Apicella - Wed, 2014-09-03 21:59
I previously blogged on how to create a variable JDBC IN list with Oracle. Here is how you would do it with Pivotal Greenplum. Much easier , without having to write a function. In the Greenplum demo below we use the any function combined with string_to_array

http://theblasfrompas.blogspot.com.au/2008/02/variable-in-list-with-oracle-jdbc-and.html

Code as follows
  
import java.sql.*;
import java.sql.DriverManager;

/**
* Created by papicella on 4/09/2014.
*/
public class VariableInListGreenplum
{

public VariableInListGreenplum()
{
}

private Connection getConnection() throws SQLException, ClassNotFoundException
{
Class.forName("org.postgresql.Driver");
Connection conn = null;
conn = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/apples","pas", "pas");

return conn;
}

public void run() throws SQLException
{
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rset = null;
String queryInList =
"SELECT DEPTNO, " +
" DNAME, " +
" LOC " +
"FROM scott.DEPT " +
"WHERE DEPTNO = any(string_to_array(?,', ')) ";

try
{
conn = getConnection();
stmt = conn.prepareStatement(queryInList);
stmt.setString(1, "10, 20, 30");
rset = stmt.executeQuery();

while (rset.next())
{
System.out.println("Dept [" + rset.getInt(1) + ", " +
rset.getString(2) + "]");
}
}
catch (Exception e)
{
System.out.println("Exception occurred");
e.printStackTrace();
}
finally
{
if (conn != null)
{
conn.close();
}

if (stmt != null)
{
stmt.close();
}

if (rset != null)
{
rset.close();
}
}
}

public static void main(String[] args) throws Exception
{
VariableInListGreenplum test = new VariableInListGreenplum();
test.run();
}
}
http://feeds.feedburner.com/TheBlasFromPas
Categories: Fusion Middleware