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

Master of Some

In picking the title of this Blog i tried to find something to some up the nature of work a DBA will have to do in their lifetime. Having moved into this role after being an Oracle Apps Developer i suddenly realised that knowing about how the database works and how to patch/install software wasn't going to get me through the day.

I've found that i have had to learn the different operating systems ( more recently Oracle on Windows which given the number of years using Windows thought would be quite intuitive) , revisit my university days of networking and database theory; handle disk storage queries and make recommendations, backup and recovery with third party involvement and manage releases to the different environments. When you couple that in with taking ownership of issues/projects that may span many different internal/external teams, you also are developing relationships and your communications skills daily.

The saying 'They wear many hats" gets used a lot when talking about the skills of a DBA but i always thought that seemed a bit of a chaotic description, which it may seem like when a system is down and you have 3 people all phoning for an update/solution. I don't think though it does justice to the amount of learning both on the job and outside to keep up to date with the ever expanding product ranges and developments going on.

I like the term Jack of all trades but know that mostly its used in a derogatory tone when someones knowledge is good, but not good enough to give an in depth answer. Checking wikipedia i found that you could still use this in a positive way and counteract the negatives by extending it and that's how i came up with this title.




I may not know the answer or give enough information right now, but I've a good understanding on whats going on and I'll go and find out the exact answer or come up with options very quickly.