Setting Xhost for the Oracle User and Creating a custom Yum Repository for the local Media

In setting up environments and running the GUI I've always just used xhost + as the root user.

I'm currently moving a customer to Oracle Linux from Windows and helped out our system administrator who was on site with a list of packages needed for an Oracle Database and Ebusiness Suite ( oracle-validated took care of most things).

Its actually a great project to be involved with as we are using VMware and have created template images to deploy new environments as needed. It takes about 20mins to create a new machine which is already configured and has the Oracle Database 11gR2 software installed.

Of course in any deployment there have been missing packages not listed in Oracles Documentation(but they are on oracle support as notes for some reason)  and interactions with the GUI's when running the installers.

At the start I was using xhost + to help with the GUI's , but having come from a support background, I realized I was breaking a big rule. I promised myself that once involved with projects i'd stay away from root as much as possible( a bad experience where a person installed the oracle RAC clusterware as root).

So I needed to work out how to stop using xhost +

Answer :
The below turns back on the access control lists
$ xhost -
The below add the oracle user to the list and the local IP
$ xhost +SI:localuser:oracle
$ xhost + hostname

The second thing bugging me was that the templates were on dhcp and could use yum for installing the packages but with the cloned vm's , no such luck as they were static and locked down. I found a great note on mounting the media dvd and creating a custom yum repository which could then be used.

The steps are below :

# mkdir /media/disk

Insert EL5.7 DVD (or attach to your Oracle VM guest)

# mount /dev/cdrom /media/disk

Edit /etc/yum.conf, adding the following section:

[EL5.7 DVD]

name = Enterprise Linux 5.7 DVD

baseurl=file:///media/disk/Server/

gpgcheck=1

enabled=1

# yum install oracle-validated

The last step was to go back to the template and update it with all the lessons learned from the first couple of environments.

Scaphoid Fracture and Oracle 12c

After taking up American football with the American Trojans back in October I had an injury on St Patricks day which I put down to a sprain. 8 weeks later and after some physio I decided to get an Xray and found out I had a fracture.

I've had a cast on the arm for the last 6 weeks so although i'm working and in the middle of an R12 upgrade for a customer and studying for the 10g OCM , 11g OCP upgrade exams, blogging has been put on a back burner.

I think I'll need to start back with Web Services , ODI when I return as their is enough blog posts about the release of 12c which would put me to shame. I installed it on the 2 hour return train journey the other day without any issue on OEL 6_4 and look forward to playing with it. The guys over at pythian already have a great post on setting up a 12c RAC cluster on the laptop, building on previous post on setting up the 11gR2 cluster.

Having read the 12c concepts guide, they have a nice picture of a bunch of DBA's being replaced by Container and Pluggable database Administrators. I'd just about convinced my friends what I did, now I have to learn a whole new piece.

VirtualBox hde: irq timeout: status=0x50 { DriveReady SeekComplete }

If you get the below error when trying to install Linux ( in my case Oracle Linux) through virtualbox on a windows machine then then the reason will probably be that the hard drive created for the virtual machine has been created as a SATA drive by default.

ide: failed opcode was: 0xef
hde:hde: irq timeout: status=0x50 { DriveReady SeekComplete }

Remove the image from SATA controller and add it under IDE controller to resolve the issue

 

 

Great tip from the folks at the virtualbox forum

https://forums.virtualbox.org/viewtopic.php?f=6&t=35106

 

**EDIT 19/05/2013**

Setting up your VM's under IDE is fine but limiting to 4 disks. In trying to create a 10g Cluster using multiple ASM disks i realised i was going to find another way around this problem.

 

To convert from IDE to SATA, boot the existing VM with the IDE disk, then force-add the SATA driver to a new boot image for the running kernel version as root

mkinitrd -v -f --with=ahci /boot/initrd-`uname -r`.img `uname -r`

 

(reference: https://www.virtualbox.org/ticket/6293 )

 

Now before shutting down the machine, you will want to update your /boot/grub/grub.conf with the below to avoid the same errors as the start of this post.

 

Solution:

Add this line at the end of the kernal argument

before : "kernal /vmlinuz-2.6.9-78.EL ro root=/"

after : "kernal /vmlinuz-2.6.9-78.EL ro root=/ ide2=noprobe ide3=noprobe"

Getting Started with Oracle Data Integrator

Just before i finished for 2 weeks leave ( new arrival to the family last week) I had the opportunity to spend some time working with Oracle Data Integrator , ODI.

The challenge set was to help automate the loading of external XML files using ODI to start and then use its other functionalities in the future like exposing some of these processes as web services.

I had spent some time using the Oracle 'Getting Started' document : http://docs.oracle.com/cd/E28280_01/integrate.1111/e12641/toc.htm

I also purchased this book 'Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial' which was an excellent introduction into using ODI and even had a couple of sections on using XML.

Although my main responsiblity was to tackle the architecture and deployment of all the components, I'd spent some time playing and so was able to get involved with the early design solutions.

The next posts are going to cover deploying the components and then a few posts on using it to load data in from an XML file , from a database source to target, enriching the data ....

Generate a Web Service Client from a WSDL using Jdeveloper

I recently had to code one half of an automated service which would connect to a Webservice and retrieve data based on the parameters sent. It needed to be converted back to XML from a Java Object and stored in a local directory. It wasn’t an easy one to start with as it involved security/authentication certificates which although I have had exposure to with OID/Single Sign on and external Tiers in the E-Business Suite, I had never really got to grips with, after this little project I sure have.

The other half which I didn’t have to code consisted of java stored procedures in an Oracle database which loaded the XML file , validated it and then loaded it into some target tables. This was all to remove the manual process of downloading and uploading the file which needed down by an end user daily. I’ve played around with this on my own time and will expand on this tutorial of sorts in the future to cover that.

First thing is first, download the latest version of Jdeveloper, I’ve used 11g Release 2

http://www.oracle.com/technetwork/developer-tools/jdev/downloads/index.html

Finding an example to use isn’t that hard, there are plenty of websites which list publicly available webservices.

http://www.xmethods.net
http://www.webservicex.net/ws/default.aspx

I’ve decided to use the stock quote example , the wsdl can be found at the below

http://www.restfulwebservices.net/wcf/StockQuoteService.svc?wsdl

That’s enough links for now !!!

Open up Jdeveloper

Choose New Application > Custom Application

Enter an Application Name

Select Next

Enter Project Name

Click Finish

Right Click the newly created project and select New.

Under Categories, ‘Select Web Services’

Under Items, ‘Web Service Client and Proxy’

 

 

For this it was left as default. JAX-WS Style

Select Next
It is here you paste in the address of the WSDL.

Copy WSDL into Project.

 

Click Next to go through the other options but for me I choose to Finish.

Jdeveloper will start importing in the required packages and classes and building the client.

You should now have something like the above in your project.

The basic code for the client is ready

Thanks to my excellent naming it looks like I’ve ended up with quite the mouthful for the client file , oh dear, never mind.

StockQuoteService defines a Java interface that represents the service. It defines a method that returns a stub for the service.

IStockQuoteService defines a Java interface that represents the methods of the Web Service. These methods will match up with the elements in the WSDL

The client as its created first creates an instance of the StockQuoteService class which implements the interface which represents the service. It then retrieves a stub that represents the remote Web Service.

This is all well and good but we haven’t actually interacted with the webservice yet to get back anything meaningful.

To do this you should use the below code

Create JAXB object instance to accept the response

      StockQuote stockQuote = new StockQuote();

Populate the JAXB instance by calling the interface to the Webservice with a value, in this case  the ticker symbol for Oracle

      stockQuote = iStockQuoteService.getStockQuote("ORCL");

What you then do with the object is very much open, you can take individual elements and print them to a text file after converting them to a string. I choose to output the whole object as an XML file by using the JAXB Marshaller class which is used to convert java objects to XML 

I added the below to my imports for the class BasicHttpBinding_IStockQuoteServiceClient

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;

I added this entry to the StockQuote.java above the class definition to allow the Marsharller to have a root reference

@XmlRootElement(name="StockQuote")

The last part was to write the code to convert the object and output to the console, you can see below the code and result of running this.

This was just a very basic attempt at interacting with a webservice that I thought I’d write up, hopefully it gets some people started and over a few bumps when using jdeveloper.

 

 

Oracle Database 10g Administrator Certified Professional

Well today as part of a large Oracle event at my work in which you were able to go and sit some Oracle exams i took the 10g Admin II exam and passed only missing a few questions.

The amount of work to get ready for it was a lot more than i expected. I've been doing quite a few development projects of late which have been taking up a lot of time as I rediscover the wondeful world of Java and learn webservices.

Luckily i was able to cram in enough revision and only missed 3 questions. The timing couldn't be better. I've a backlog of posts to make about creating java clients for webservices, deploying a pl/sql procedure as a webservice on weblogic and posting the useful topics I've made notes on while revising.

And did I mention i've a new baby due next week, let the fun begin

: ORA-24010: QUEUE APPLSYS.WF_NOTIFICATION_OUT does not exist

We had this error in a test environment where they were trying to run a few things through workflow mailer in the Ebusiness Suite. It was quite a recent clone but for the most part, I prefer not to have workflow on without reason in test systems without the over-ride email address set.

I've been in the position in the past where a junior DBA has cloned, forgotten to do this or not reset the workflow accounts and workflow has sent out emails to customers and also connected to the live account and moved emails into the discard folder.

There was a direct hit for the error on Metalink [ID 886377.1] which was tried but didnt resolve the issue. On checking then if the workflow queue existed under applsys it wasnt found so it was time to rebuild the queue.

The below should bring back 3 objects under applsys, table, queue and synonym.


select owner, object_type, status
from dba_objects
where object_name='WF_NOTIFICATION_OUT';



I've rebuilt the out queue hundreds of times and its usually down to issues in the INBOX with spamming mail killing the workflow IMAP connection to the account and it 'hangs'. Another popular one is customers replying to a workflow email or copying the address in on a reply to their manager. Nothing is sure to stop the workflow mailer from working as quick.

To rebuild the out queue automatically you would run the below.

select distinct  tablespace_name
from dba_indexes,dba_queues
where index_name like 'WF%N1'
and table_name=queue_table
and name like 'WF%';

connect to SQL*Plus as applmgr user

@$FND_TOP/patch/115/sql/wfntfqup.sql APPS APPSPW APPLSYS

this rebuilds the queue, now to rebuild the index.

connect as applsys , replace question marks with value from first select.

CREATE INDEX WF_NOTIFICATION_OUT_N1
ON WF_NOTIFICATION_OUT(CORRID)
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
TABLESPACE ?????????????;

If you want to rebuild it yourself as I did manually you will want to follow Metalink Note [400493.1]