working with Oracle DBA

[Victor_5]

I have little experience working with Oracle DBA, so I would like to get some thoughts from those experienced people in this forum, now I am wondering,

1. What is the most important content on Unix side so that I can provide qualified service to DBA team?

2. What is the frequently asked questions from DBA team, related with Unix?

3. If a DBA come to my desk and tell me he want to load a new Oracle application, what kind of info I need to know before I do it for him?

Appreciate for any feedback.

[Dave La Mar]

I'm also new to this. So let me share some things that have come up here.
1. How much space will your db require?
2. What resources, and how much will the db require?
3. Are there kernel parameters that will need adjusting? (Usually can be found on Oracle web site)
4. Is there a new group to create for your developers, if so, what priviledges will they need, and what other groups do they need to be a part of?

Sorry I don't have more, or more intelligent ones. These are the major ones we have faced currently.
Best of Luck.
d

[Roger Baptiste]

<>

DBA's are super(headache)users. Kidding ;-)
Actually, a good cooperative
attitude and understanding between the DBA and Unixadmin is very essential for running the system efficiently.
But, at times it can get bit rough especially during those
squeaky database problems which eludes a solution.

<<1. What is the most important content on Unix side so that I can provide qualified service to DBA team? >>

Disk configuration and Filesystem layout. Especially when connecting new disks (raids), it is good to make the dba aware of the layout, extent size etc , such that this understanding helps him in laying out the database.

System performance is another aspect. You would see lots of database processes
hogging cpu and need to get the DBA involved in helping you out to find the source.
Disk throughput is another factor. Basically, on a database server anything related to performance and impacting the database would involve the dba.

Creating/modification/removal of filesystem/rawvolumes.

Backups!!! -> strategy for backup of databse, when, downtime etc etc..

AS you see there are many aspects where you would be better of involving teh DBA
in terms of informing and passing responsibiility.

<<2. What is the frequently asked questions from DBA team, related with Unix? >>

Depends on the DBA! ;-)
But the common ones are regarding performance of the system, backup and the top one being requests for creation of filesystem/rawvolumes.


<3. If a DBA come to my desk and tell me he want to load a new Oracle application, what kind of info I need to know before I do it for him? >

It depends again on what the site guidelines and policies are. But, generally when such request comes, i check up and make sure it is a valid one (ping the boss!),
there is enough resources (space etc) and then look into it.

It is important to have regular meetings with the DBA''s. Remember, DBA is the interface to the User community in a Database environment.

You have raised an intresting question, something which is often discussed and felt in the Sysadmin (and dba) community.

-raj

[Victor_5]

Hi Dave:

Thanks for your quick response.

Hi RajMan:

Your reply is excellent, you should get 10 points, however, I am waiting for more inputs, I only gave you 7 points this time, thanks for your understanding.

Shawn

[Animesh Chakraborty]

<<>>>
1.Keep youself busy with phone and keep them standing for hours.
2.Ask them whether they have raised a change control or not.
3.Is change control approved ?
4.We don't have any free disk space. U need to buy ....(delay for few months)
5.Do not ask any thing on verbal. send your request via email and copy to my boss.

hey I am not kidding. It was like that in my previous company..So no wonder I worked there for few months only.

What Rajman said is correct.
Understanding amongs DBAS and Sys Admins is the key factor.

[Victor_5]

Hey Animesh:

You definitely said it, yes, the communication is important, sometimes it is even more important than technical skills.

However, I am looking for more technical points...

[Lai Nee Shyang_1]

Hi Shwan,

Just a few pointers :

* As mention, you'll need to know what kind of filesystems the DBA wants. Raw, HFS or JFS. Check with DBA if the filesystems block size need to be specified. (eg. SAP-oracle uses 8192 block size).
* Another consideration is whether to stripe the logical volumes (usually not recommended on PV level) and its optimal stripe (64K, 128K) size.
* As for backup strategy, you may want to choose between online backup or offline backup. or off line filesystem backup(using OB2 or fbackup). I personally prefer off line filesystem backup as it is flexible and I can cover the DB binaries, config and other misc, provided you have the luxury of downtime for backup.


cheers.

Lai

[Animesh Chakraborty]

Hi,
Yes you should be technically sound enough to handle some escapist DBAs.Every time user complain system/databse is slow ,they will try to throw the ball in your court.
You should be confident about
1.Kernel parameter required for the database.
2.Correct oracle patches are already installed in your system.
3.Frequently show them the sar,glance output that system is performing satisfactoryly.
4.Learn some basic oracle commands.I will suggest to go for basic oracle course for better understanding.

[Printaporn_1]

Hi,

To answer your question is get more database knowledge ,if using Oracle , start with:

1.Installation.
- get familier with orainst / patches.

2.Performance tuning
- kernel parameter , maxssiz.... dbc_max_pct
- SGA , share memory, swap.
- buffer cache

3. Basic admin,
- startup /shutdown script
- tablespace, datafile

4. backup & recovery
- RMAN , Omniback

just goto http://docs.oracle.com or Oracle metalink site

start with Oracle Database concept guide follow by installation guide then Database admin guide. Then you will know what to do next.
finally DBA 's role will be assigned to you ;-)

[Steven Sim Kok Leong · Accepted solution]

Hi,

Some points I would like to add on to those already mentioned above:

1) With regards to Oracle installations,

root.sh has to be run with root privileges. For security reasons (ie. segregation of duties), DBAs usually should not be given root privileges. Thus, you (as the system administrator) have to either
a) run root.sh for the DBA when the need arises.
b) create a restricted sam script (or setuid script) to allow the DBA to run root.sh with root privileges.

Similarly to root.sh which is usually run near the end of the Oracle installation, at the beginning of the installation, pfs_mountd and pfsd has to be run with root privileges for the DBA to access the mounted Oracle CD. To workaround this, you can again create a restricted sam script that performs in sequence:
nohup pfsd &
nohup pfs_mountd &
pfsmount /cdrom # provided that /etc/pfs_fstab has been configured.

With regards to errors during the installation process, often accompanying the Oracle ORA-XXXXX errors are HP-UX error codes. You will be asked to interpret these error codes. To identify the representations, use the file /usr/include/sys/errno.h eg. HP-UX Error 13 is Permission denied.

For Oracle I/O performance, it is equally important for you to provide an I/O topology of which disk device is connected to which controller. Usually the DBA would want to spread his I/O across disks and across controllers, especially for redo log filesystems.

With regards to mirroring, the DBA will have to identify whether he is relying on Oracle-level mirroring for redo logs, OS-level or hardware-based mirroring.

2) With regards to maintenance,

It is important that you keep track of the system performance statistics especially I/O performance (iostat or sar -d), CPU queue, memory, swap and paging utilitization. You can implement MRTG to provide real-time graphic representation of such statistics.

C) With regards to incident response,

If you are already sending alerts on OS health, you should also incorporate alerts on database health such as
a) Availability of oracle background daemons
b) Availability of oracle listener(s)
c) Archive log filesystem space 80% utilised
d) No. of concurrent oracle connections in netstat.
e) No. of oracle server processes spawned in a dedicated two-task Oracle server configuration, etc.

Hope this helps. Regards.

Steven Sim Kok Leong
Brainbench MVP for Unix Admin
http://www.brainbench.com

[A. Clay Stephenson]

Hi Shawn:

The best advice that I can give you is to learn Oracle. I would take the 'kindergarten' Oracle class plus the Admin Class. I can tell you that one thing that is absolutely drilled into DBA's is to spread everything over as many disks as you possibly can. While that rule makes sense in the separate physical disks world, it breaks down in the modern disk array world. I have seen many, many Oracle setups where things were spread over what appeared to be many physical devices when in reality they resolved to one or two physical devices within an array.

The other thing you will hear is: raw/io good; cooked i/o bad. The answer is: it depends. You need to really understand how unix buffers work and how the OnlineJFS options can affect performance. The real answer in all of these cases is measure.

Finally, the most importtant lesson (and I'm sure this will ruffle feathers) is that many times what you do won't make much difference. Most of the performance problems lie in the SQL code itself not in the OS. If I assume that you are a wonderful admin and your DBA is superb you might get a 2x performance boost - more realistically 20% would be very good. However, if the application needs a 10x performance increase (and that is quite common), the answer is to tune the SQL code. Often adding a single index can make a tremendous difference to the execution speed of a query.

That is why I suggest that you must learn some Oracle; in the best environments the DBA and the sys admin complement each other and trust each other. You guys must also work together to develop a comprehensive backup plan as well.
This is especially true if you have application meta-data which describes Oracle data. In this case, backing up the meta-data at one point in time and the Oracle data at another point in time (even if separated by only minutes) might result in a completely useless backup. This is especially true if you have separate database and application servers.

Regards, Clay

[John Payne_2]

I agree. The most important thing is to get to know the DBA's and their product as much as you can. I sit on the DB Commisson here. (As the representative of Server Engineering.) Or DBA's always do their homework and have details ready when they need something of us. If they are installing a new version of Oracle, they have already done the footwork and know info about space needs, kernel tuning, etc. It is a very nice thing that we do not have to hunt that info down. We also help them with their backups and batch problems. (And all other hardware and OS problems, of course) It is a two way street at all times.

One thing that DBA's always seem to need is disk space. Lots and Lots of disk space.

But seriously, a good DBA will bring what they need to you, not expect you to figure out how to tune, install, etc. When it comes to the databases, it really is their application, and their baby. If that is not really feasible in your environment, I agree with Clay, take a course or two. It will help you to at least be able to think like them when they come to you wanting something. If you can understand a person's way of thinking, your are better able to help them meet their needs.

It might help if you bought them doughtnuts...

John

[Michael Tully]

hi,

You've already been given some great advice
here. A few more things.....

1) The communication between the Unix SA
and the DBA is an essential part of either
project or production based tasks.

2) Do not ever give a DBA the root password
or any type of super-user access. That what
the Unix admin is for.

3) Do not give them restricted sam either.
If CD's are to be mounted, particularly in
RockRidge format (for Oracle installations)
the mounting and unmounting can be a
diabolical task.

4) Make sure that DBA's login as their own
account and then su - oracle
This is so each instance is at least logged.
This is particularly useful when there are
more than one DBA, even better with 'sudo'

Doughnuts don't always help.... sometimes
bananas are better.

-Michael

[Steven Sim Kok Leong]

Hi,

Giving DBA access to restricted SAM will actually reduce the tedious task of mounting and unmounting oracle CDs to just a "Select and Press Enter" menu.

Here's my restricted sam script that does the PFS mounting and un-mounting. This allows flexibility of accessing the Oracle CDs without compromising system security.

==============================
#!/usr/bin/ksh

if ps -ae|grep -v grep|grep pfs_mountd > /dev/null 2>/dev/null
then
echo pfs_mountd already started
else
echo Starting pfs_mountd
nohup /usr/sbin/pfs_mountd &
sleep 5
fi
if ps -ae|grep -v grep|grep pfsd > /dev/null 2>/dev/null
then
echo pfsd already started
else
echo Starting pfsd
nohup /usr/sbin/pfsd &
sleep 5
fi
if /sbin/mount|grep /cdrom > /dev/null 2>/dev/null
then
echo Unmounting /cdrom
pfs_umount /cdrom > /dev/null 2>/dev/null
else
echo Mounting /cdrom
pfs_mount /cdrom > /dev/null 2>/dev/null
fi
===============================

Hope this helps. Regards.

Steven Sim Kok Leong
Brainbench MVP for Unix Admin
http://www.brainbench.com

[Tom Geudens]

Hi,

Well ... I used to be an Oracle DBA and now I'm on the other side :-).

In my humble opinion you would benifit from integrating your more technically inclined DBA's (the key people that actually create the databases, install new versions, take care of backup and recovery, etc.) into the System Engineers group. That's where they belong.
This would also solve problems such as having to do the actual CD-ROM mounts. Oracle 9i (for example) has 3 CD's and in our environment this means that the System Engineer has to run around with those CD's. Which is humiliating ...

On a more technical note you need to check every new release or major patchupgrade for memory(and other resource)-usage. Did you know that the pmon-process in an 8.0.6 database uses 5 times as much private memory as the same process in an 8.0.5 or an 8.1.7 database. I do, but only after almost everything is upgraded to 8.1.7.

If the DBA's are not integrated in the System Engineers group, have frequent "state of the union" meetings. Remember that end-users dump their problems on DBA's, DBA's dump them on System Engineers ... and we have to solve them. You want to keep those DBA's on your side, you want to know what's coming.

Other than that ... DBA's are the salt of the earth :-).
Tom Geudens

[Thierry Poels_1]

hi,

I've been Unix sysadmin AND Oracle DBA for several years now. I'm having best of both worlds ;-)

I've experienced that with seperated jobs communication and trust (remember those privileges) is extremely important. The sysadmin doesn't want his system to be touched, and the DBA doesn't want the sysadmin to touch the DB. But they should cooperate! The DBA needs basic OS knowledge, and the sysadmin should have basic database administration knowledge. Every action on the system can have consequences for the DB, and vice versa.
The DBA will probably ask for the root password, not really necessary (only for initial install) but will be handy time to time.

good luck,
Thierry.

[Victor_5]

Thanks a lot, all! I learned a lot from all of you.

Shawn

[Roger Baptiste]

hi,

Regarding the Oracle CD mounts -> Yes! that was a big pain, since i used to have DBA''s from various groups requesting to mount CD's , unmount them etc etc. (and that is PFS on a production box! ;-) ). But, we found a better solution to the headache. Copied the CD's (two or three or whatever)
onto the disk of a NFS server.
Exported it (automount) globally with read permissions.
Now, if any DBA wants to install Oracle, all he would need to do is to cd to /net/systemname/oracle/CD
directory and use it as a source for the install.

But, you would still need to run root.sh! ;-) Worth the trouble for keeping root access.

On login, the suggested method is to make the DBA's
use "sudo" from their accounts to login as DBA.
(this depends on the number of DBA's handling the system and site. If it is just one DBA, then it is not necessary).

-raj

[Deshpande Prashant]

HI
Also involve the DBA in outlining the backup/restore procedure for database.
Online backup/off line backup, Oracle exports?
Backup window? Any need for batch/cron jobs.

Thanks.
Prashant.

[u856100]

Hi Shawn,

I am currently on a fast track scheme to get the OCP (Oracle Certified Professional). I am also attempting to get UNIX certified in May next year. What I have found so far (with my limited knowledge), is that Oracle is a monster! you can't just pick up a oracle in 24hours book and expect to be reasonably conversant (I have foolishly tried). One book that I have found very useful for a conceptual understanding about how oracle operates is 'ORACLE8i A Beginners Guide'. The authors, three Oracle press guru's, really drill in the info you need to know. Personally it has been a real help, and if you are new to Oracle, then this book should be at least considered and thumbed.

At this early stage, I think there are a good many areas that should be considerred :

- Your knowledge of disk availability. The more disks available the better. Not only for space, but also for concurrency of dbse transactions to increase throughput.
- Provision of backup data. The dba will want to know ASAP if and when the dbse backups were successful or not. Successful backups to a dba are like a comfort blanket.
- System knowledge. I don't expect a dba will ask you to install Oracle for them as it is the biggest ball ache to setup (not helped by Oracles convoluted installation guide). But they will want your participation at there shoulder during some of the process (kernel configuration, patching, VG & user creation, etc).

Well, I hope that is of some use.

One last thing, if you are looking for one of the best courses given by Oracle, then get your company to book you in for the beginners course in Reading. It was voted best training centre for Oracle and is reguarly booked up by 'The' gurus at Oracle in the states.

cheers

P.s. DBA's won't bite, honest

[Roger Baptiste]

Hi,

Regarding books, i found ORACLE DBA 101 an entertaining and informative read. Grab one, if you want to know the world of Oracle (administration).

Hmm, no end to this discussion ;-)

-raj

[Tim Krego_2]

While I don't have much advice to offer I'd like to add my thanks to the people who have responded so far.

I am a MCSE that was given the chance to switch to unix admin and Oracle while learning on the job.

The company I work for had our HPUX Admin / Oracle DBA leave just as I was learning both. I have been spending most of my time the past 12 months learning the HP admin stuff. I'm finding it hard to master both HP/Unix and Oracle DB at the same time. I'm just happy that we picked the hardware and software we did. HP hardware and HP/UX have not let me down. And Oracle is an excellent database.

I just hope I get up to speed before a disaster strikes.