wget: command not found and repolist : 0

I'm setting up a new environment to demo and workshop Weblogic and Fusion Middleware to other members in my team. After installing Oracle Linux 5.4 x86 and trying to minimize the number of packages while still using the GNOME desktop I came across a bit of a catch 22 situation.

When trying to configure the yum repository for Oracle running

$ wget http://public-yum.oracle.com/public-yum-el5.repo
wget: command not found

Now when I try to install the wget command via yum there is nothing to update and rpm shows it's not installed.

Check if there is a yum repository.

$ yum repolist
repolist: 0

I couldn't find wget in the install media after a quick look through the 5 cd iso's so was stuck and needed a repository so created my own.

Since the public-yum-el5.repo is just a series of links which are enabled or not I grabbed one

$ cat public-yum-el5_temp.repo

[ol5_latest]
name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/latest/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol5
gpgcheck=0
enabled=1

Its important to have gpgcheck=0 or the package will fail a public key check.

Now  I can use yum to install wget and get the latest repository.

ORA-12162 "TNS:net service name is incorrectly specified",

Hadn't seen this for a while and good reason.

After installing 12c locally and then closing down my system, I logged in tonight to play around.

What I had forgotten to do in my haste was set the ORACLE_SID, school boy error.

ORA-12162  "TNS:net service name is incorrectly specified"

export ORACLE_SID=orcl

A simple fix from a misleading error message which might have you looking at your tnsnames.ora

Configure 12c EM Express

If like me you somehow managed to install 12c and DBCA didn't give feedback on EM Express or you didn't save the details then fear not, you can retrieve the port and change it.

If its configured you can connect get the portnumber by running:

SQL > select dbms_xdb_config.gethttpsport() from dual;
Then connect : https://database-hostname:portnumber/em/

If the SQL returns no values then you need to set the port,

SQL > exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

Then connect : https://database-hostname:portnumber/em/

(you will need flash player as well to use it, guess that means no ipad app !)

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]

Change owner of Ebusiness Print Jobs

 

I'm going to have to create a post just to cover printers in the Ebusiness Suite as its an area that will always come up and most people try to avoid it like the plague, i know i did at the start.

I now feel very confident when it comes to looking at printers and setting up new ones but only after a LOT of practise and being tested by lots of scenarios.

In this situation the request was to change the owner of the print job which was sent from Oracle EBS to the print queue. The reason behind this was a new approach to printing, they needed to tie each print job to a unique user.

This was all on Linux using CUPS. Like anything sent from Oracle I didn't think it was possible to change the owner so the only hope was adding information to the print job somehow. We confirmed that you couldn't change the owner with Oracle in an SR.

Printing from Ebusiness is exactly like than printing from the OS (operating system) .
So the owner will be a unix/linux owner .

My intial searches took me to a lot of third party software companies looking after printing and they had articles asking their customers to add information to the PJL-Header information which they could then work with. The problem for me most of these were mostly aimed at SAP and there were solutions out there to handle this common risk.

I tried to get some help in the OTN forums and had a good dialogue going with one of the Guru's : https://forums.oracle.com/forums/thread.jspa?threadID=2517369

This led me to the system administrators guide for printers where i started concentrating on the print command strings.

The system administrator working with the printers initially couldn't use the changes we made to the string, but new what we were trying and suggested we tried using the -U parameter. Now i still cannot find this documented anywhere around the LP print command , maybe a Linux/Unix Guru can enlighten me, but this changed the owner of the print job inside the print job information even though it was coming from 'oracle'.

The solution then was to change the standard driver and we were in business.

OLD VALUE
lp -c -d$PROFILES$.PRINTER -n$PROFILES$.CONC_COPIES -t"$PROFILES$.TITLE" $PROFILES$.FILENAME



NEW VALUE
lp -c -d$PROFILES$.PRINTER -n$PROFILES$.CONC_COPIES -U$PROFILES$.ORIGUSERNAME -t"$PROFILES$.TITLE" $PROFILES$.FILENAME

Windows native operating system authentication for Oracle

While logged into a Windows 2008 server which had an Oracle Database installed, i was trying to get logged in as sysdba using O/S authentication.

I kept getting insufficient priviliges in the error message even as the owner of the oracle software and being in the ora_dba administrator group.

At the time i had been patching their weblogic and fusion middleware tiers and was finishing on the database. I'd completed the task on training environment the day before without issue and this was now their UAT system. I could not find anyone who could tell me the sys password or where i could find it to try to get in through external authentication.

Luckily for me over lunch, my manager who was also onsite remembered hitting the issue before due to a setting in the sqlnet.ora file and after lunch we had a look.

#SQLNET.AUTHENTICATION_SERVICES=(nts)
SQLNET.AUTHENTICATION_SERVICES=(none)
Changing the above around fixed the issue and so i had to go and figure out the why as i wasn't familiar with this setting.

Oracle Documentation Example

Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.

nts for Windows native operating system authentication

Clear as day, it needs to be set to NTS for windows o/s authentication, i have a feeling i'll remember this setting from now on.

Setting up a Sandbox environment for Oracle in VMPlayer

The following assumes you want an environment which will have an oracle database installed in a windows virtual environment. Whether this is going onto a laptop which will be connecting to multiple wireless networks or a desktop, chances are you’ll be using DHCP to get your IP. Since you’ll be using your VM for Oracle, you’ll want to have a static IP setup in your VM and it’s better to do it at the start than trying to reconfigure later, trust me.

You may also need a static ip address to connect to the guest from the host and possibly from other Virtual Machines on your host.

I’ll not cover installing Windows on a VM , that is covered all over the internet and no point reinventing the wheel.

For this scenario I would recommend setting your VM to use a Host Only adapter. I’ve found this is the simplest solution for getting a local play area set up on VMware Player. You will not have internet in your guest VM but that’s hardly the main reason for this setup. (A Bridged adpater may be the answer but I’ve found trying to configure it difficult as the network tool doesn’t install by default )

When installing VMware Player, it creates three virtual network interfaces, VMnNet0, VMnet1 and VMnet8.  VMnet1 is the 'host only' interface which we will be using and you can see this in your hosts network adapters. It will already have an IP assigned(I’ve found it doesn’t use this IP range when assigning  IP’s  to the VM’s, currently it’s a mystery to me but I think its probably done through the network tool).

The good thing is that it keeps the same IP for the VM as long as it’s around. Once configured with a host-only network card I’ve found my VM’s will keep the same IP. I’ve tested this starting them in different orders and copying them and starting them up, each time the existing VM’s maintain their IP and the new VM;’s are assigned a new one.

The new step before installing the oracle software is to configure your virtual windows environment with a loopback adpater as per oracle instructions.

Here is a summary for Windows 2003 or Windows XP:

1. Open the Windows Control Panel.
2. Double-click Add Hardware to start the Add Hardware wizard.
3. In the Welcome window, click Next.
4. In the ‘Is the hardware connected? window, select Yes, I have already connected the hardware, and click Next.
5. In the The following hardware is already installed on your computer window, in the list of installed hardware, select Add a new hardware device, and click Next.
6. In the The wizard can help you install other hardware window, select Install the hardware that I manually select from a list, and click Next.
7. From the list of hardware types, select the type of hardware you are installing window, select Network adapters, and click Next.
8. In the Select Network Adapter window, make the following selections:
• Manufacturer: Select Microsoft.
• Network Adapter: Select Microsoft Loopback Adapter.
9. Click Next.
10. In the The wizard is ready to install your hardware window, click Next.
11. In the Completing the Add Hardware Wizard window, click Finish.
12. If you are using Windows 2003, restart your computer.
13. Right-click My Network Places on the desktop and choose Properties. This displays the Network Connections Control Panel.
14. Right-click the connection that was just created. This is usually named "Local Area Connection 2". Choose Properties.
15. On the General tab, select Internet Protocol (TCP/IP), and click Properties.
16. In the Properties dialog box, click Use the following IP address and do the following:

1. IP Address: Enter a non-routable IP for the loopback adapter. Oracle recommends the following non-routable addresses:
• 192.168.x.x (x is any value between 0 and 255)
• 10.10.10.10
2. Subnet mask: Enter 255.255.255.0.
3. Record the values you entered, which you will need later in this procedure.
4. Leave all other fields empty.
5. Click OK.
16. Click OK.
17. Close Network Connections.
18. Restart the computer.
19. Add a line to the SYSTEM_DRIVE:\WINDOWS\system32\drivers\etc\hosts file with the following format, after the localhost line:

          IP_address   hostname.domainname   hostname

where:

• IP_address is the non-routable IP address you entered in step 16.
• hostname is the name of the computer.
• domainname is the name of the domain.

            For example:

10.10.10.10   mycomputer.mydomain.com   mycomputer

This way oracle will always resolve its connection correctly.

The last step is to install the Oracle Software and then test your connection from your host.

At the end, you should be able to connect from your host desktop to the guest VM over remote desktop or through a SQL client like SQL Developer. The Guest IP will not change so you could add a shortcut to your hosts file. In the Guest VM, the loopback adapter will resolve all traffic back to the guest IP and oracle will have no issues no matter what network your connected to on your host.

In the below example i've used ORADBHOST1 for the install and that is what is present in my tnsnames.ora.

 

Checking status of an Index Rebuild

Without getting into a debate about whether an index needs rebuilt as this is an area that i'll get into in the future once i've learned a LOT more, sometimes its good to be able to know how an index rebuild is going.

SELECT MESSAGE
FROM V$SESSION_LONGOPS
WHERE SID IN (SELECT SID
                             FROM V$SESSION
                             WHERE USERNAME='SYS' AND STATUS='ACTIVE')
ORDER BY START_TIME;

If you have a spare couple of hours and want to learn more about indexes , Index Internals would be a good place to start where some very good case studies are carried out and myths challenged.

I got my first Oracle Certification

So after years of toying with the idea of getting a certification and reading about the benefits I finally decided to make the push. In the past i have studied for exams and then for one reason or another never made the move to a 'Professional Certification' by a vendor.

Some people are not to fussed on certifications and possibly this is due to the number of years of experience they have,  so when looking for jobs this will outway the need to be certified.

My new role in Managed Service company puts a large emphasis on getting certified and provide time and support so i was quite excited to take the challenge. Working for an Oracle Partner  has its benefits around exam vouchers, access to training as well as other things so this also made the steps quite easy.

Back when i made the move to a D.B.A i attended an Oracle University cource on Oracle Admin I for 10gR2 so that took care of the approved courses requirement. I also thought this would be the best place to start as i should already know the material, little did i know how much I hadn't used since then. The exam was 1Z0-042 -  Oracle Database 10g: Administration I .

I bought 'Oracle Database 10g OCP Certification All-in-One Exam Guide' and read and made notes on the first half of the book. Then i took every test exam i could before booking an appointment with Perasonvue and took the exam at the local approved test centre, passed on the frist attempt. The questions were not what i had expected and nothing like the exams in the book !!

I then had to back track a little and take the 1Z0-007 -  Introduction to Oracle9i SQL exam. Your probably wondering why take a SQL exam on 9i, well the reason was I already had the exam study guide in hardback copy from when i first started working with Oracle and also because it was still a valid exam.

This exam was non-proctored so i was able to take it at home but that didn't make it a lot easier. So many of the fuctions which on a day to day basis you would look up quickly you can certainly get caught up in checking your notes and lose track of time. I took the approach of leaving the lengthy questions until the end when i had spare time. Exam Passed and look what i got :)

It won't stop here though as OCP is the next step, then upgrading this to 11g and 12c. Oracle R12 will be next and also trying to pick up some opn specializations.

Diagnosing Oracle Database Issues

From studying for the Oracle Database Admin II OCP Exam

The Alert Log

There are a number of sources of information that the Oracle Database provides to help in getting to the bottom of issues. The first place in a situation where you do not know what has caused the issue is to check the alert log for the instance. This log file contains messages for significant events which will include general information and warnings/errors

·         All startup and shutdown commands, times taken for transitions from nomount to mount

·         All Log switches and archive logs

·         Details on tablespace changes

·         Changes to the database involving datafiles and redo logs.

·         Initialization parameters

·         Unable to open file ( if there is an issue with a datafile or control file during normal startup)

·         Corrupt data blocks

·         Deadlocks

·         Problems with archiving such as the destination being 100%

·         Trace file information for background processes which have encountered problems

These messages are in chronological order so checking the last number of changes or errors is as easy as going to the end of the file and scanning up. Many of the messages will also include the details of a trace file. These trace files are generated when a server or background process encounters an error.

The Location of the Alert Log is set in the database pararamter : BACKGROUND_DUMP_DEST

(This database parameter is dynamic and can be changed with shutting down the instance)

The naming convention for the alert log is  alert_<ORACLE_SID>.log

Viewing the alert log at the Operating System level can be achieved by navigating to its location and opening with any editor. You can also find it in Enterprise Manager on the database home page in the Diagnostic Summary section with an Alert log link.

Trace Files

As mentioned in relation to the alert log, when a background process encounters a problem it will write its output to a trace file in the same location as the alert log : BACKGROUND_DUMP_DEST.

When a server process encounters a problem it will go to: USER_DUMP_DEST

Trace file headers contain the version of Oracle and Operating System the trace file was generated on , instance name and process/thread id, followed by the actual error. When a trace file is generated then unlike message in the alert log which can be informational, an actual error has occurred.

You can limit the size a trace file will be (unlike the alert log which cannot be limited) with the database parameter MAX_DUMP_FILE_SIZE which by default is unlimited.

SQL> ALTER SYSTEM SET MAX_DUMP_FILE_SIZE = "50m" scope = both

Server Generated Alerts

Both the alert log and trace files looked at previously provide a starting point when looking at an issue, but out of the box, Oracle comes with an alert system which is very configurable. As well as using this to look at immediate problems, it can be configured to alert you before the issue becomes critical and effects the end users.

You can interact with the alert system through Database Control which will be the easiest way but you can also you setup changes through API’s and be notified by subscribing to the ALERT_QUE which will be covered in the Alert System Architecture.

A server generated alert will fire when a certain limit is reached or when certain events occur. These are looked after by the background process MMON. When we talk about limits we are talking about database metrics of which over 120 can be configured ( V$ALERT_TYPES).  A metric is a statistic converted into some meaningful figure such as disk reads(statistic) per second or space used(statistic) is in proportion to total space which would be metrics.

Two Types of Alerts

1.       Threshold (stateful) -  these can persist for some time until cleared. An example would be a warning when a tablespace it 87% full. Once action is taken these clear

2.       Nonthreshold(stateless) – unpredictable events for example snapshot to old errors which are resolved immediately

For threshold alerts, when the problem condition is cleared by fixing it, it’s moved to the alert history unlike Nonthreshold alerts which go there directly.

Again you can view alerts in enterprise manager on the home page under Alert and Related Alerts and under the Alert Log link. Each alert can be drilled down into to find out more information.

Alert System Architecture

The statistics used by the alert system are collected when the database parameter STATISTICS_LEVEL is set to TYPICAL(DEFAULT) or ALL. Setting this to BASIC will disable statistic gathering. MMON gathers these statistics and calculates the metrics as all as monitoring thresholds. If a metric breaks a given threshold then it will write it to the predefined ALERT_QUE(if there is a problem with this queue or any other it would write it to the alert log, this is the only interaction between the alert system and the alert log). The ALERT_QUE can have more than one consumer so third-party software could subscribe to it using the DBMS_AQADM package and CREATE_AQ_AGENT , ADD_SUBSCRIBER procedures.

A notification rule can be created that when an alert occurs, the DBA can take appropriate action sometimes even before the end user notices.

User Trace Files

Trace files for a user’s session which normally are generated on demand in helping to diagnose an issue locally.

Setting the SQL_TRACE value to TRUE for the session will generate performance statistics for the processing of all SQL in that session.

SQL> ALTER SESSION SET SQL_TRACE TRUE;

These files will be produced in : USER_DUMP_DEST

If the user is using an application then they probably will not be able to open a SQL session and run commands like the above. In this case you need to first find the SID from v$session that the user is running. This can be narrowed down using any of the columns in v$session.

Then you can monitor the session using the DBMS_MONITOR package like below

SQL> execute dbms_monitor.session_trace_enable( session_id=>101,serial_num=>99);

SQL> execute dbms_monitor.session_trace_disable( session_id=>101,serial_num=>99);

You can implement tracing through Database Control and indeed it maybe easier to do this, especially in identifying the session for the user be that through TOP CONSUMERS or something else.

System Log Files

You may or not also by the System Administrator or have to co-ordinate with another team who manage the operating system but these would need checked periodically and immediately if there was an issue. From experience I’ve spent time going through the whole above process only to find out the System Administrator never checked the system log files and in there found out the root cause ( possible mount point issue or SMTP issue) e.g. in Unix systems : /var/log/syslog or /var/adm/messages

Summary

For reactive situations, you will want to first check the alert log and then drill down into any trace files which look like candidates for the issue.

For proactive monitoring you will want to start playing with and configuring the server alert system.

If trying to identify unique issues,  you can trace the database by user , session or program 

Long Running Concurrent Request

Everynow and then you will get asked to look at a concurrent request which seems to be taking to long or is 'stuck'. This could be a job scheduled to run frequently or sometimes during a large month end job like a payroll run.

First thing to do is get the SID for the concurrent request and then use this to find if any SQL is running

-- GET THE CURRENT SQL STATEMENT RUNNING FOR A CONCURRENT REQUEST

SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID, E.SQL_TEXT

FROM APPS.FND_CONCURRENT_REQUESTS A, APPS.FND_CONCURRENT_PROCESSES B, V$PROCESS C, V$SESSION D, V$SQL E

WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID   

AND C.PID = B.ORACLE_PROCESS_ID   

AND B.SESSION_ID = D.AUDSID   

AND D.SQL_ADDRESS = E.ADDRESS

AND A.REQUEST_ID = &REQUEST_ID;

 

SELECT sql_text
FROM v$session a, v$sqltext b
WHERE a.sql_address = b.address
AND a.sql_hash_value = b.hash_value
AND a.sid = &SID
ORDER BY b.piece;

 

--SQL STATEMENTS RUNNING BY A USER , FIND OUT WHO 

SELECT A.SID, A.SERIAL#, B.SQL_TEXT F

FROM V$SESSION A, V$SQLAREA B

WHERE A.SQL_ADDRESS = B.ADDRESS

AND A.USERNAME = 'APPS';

 

-- GET THE BLOCKING SESSIONS FOR GIVEN SID

SELECT BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS, SECONDS_IN_WAIT FROM V$SESSION

WHERE BLOCKING_SESSION IS NOT NULL

AND SID = &SID

ORDER BY BLOCKING_SESSION;

 

Original SQL for this was found at :

http://erpschools.com/scripts/get-sql-for-a-concurrent-request-id