HPUX / Oracle administration

[Mike Tufariello]

Hello to all.

First, I am an HPUX sys admin, not an Oracle DB admin. So please keep that in mind if you reply.

I am running HPUX 11.11i on an RP7420 server, fiber cabled to an EVA 5000, which holds our Oracle 9i database. (We are running software known as Amisys). I backup my environment using Data Protector.

My last reboot of the UX system was 12/30/07 and my last re-boot of the EVA was earlier than that. I recently upgraded Data Protector from 5.1 to 5.5.

Since 12/2007 we have noticed that all our jobs that use the Oracle database and our backup jobs of the Oracle database, slowly took longer and longer to complete. To the point that a job that took 1 hour in Dec was taking 2 1/2 hours to run by the end of April. This was true of the backup jobs as well. Of course, we realized that the database was also growing, but not at such a fast rate.

On Sunday morning, April 27, we were forced to shutdown our computer room, which included our HPUX system and EVA with Oracle databases. We re-started all systems later that afternoon.

On Monday, April 28, our jobs that orginally took 1 hour to complete and grew to 2 1/2 completion time, is now back to running in 1 hour. A corresponding reduction in time also too place with our backups as well.

As a Sys Adm, it would seem to me that something in the complete shutdown of hardware took place that "fixed" our database.

Every night prior to our backups we do shutdown the Oracle databases and then restart them. However we never gained any run time of jobs. It seems like the hardware shutdown is what did the trick.

Is there maintainance, either hardware or Oracle, that should be performed on a regular basis to keep the database in "better" shape?

Thanks,

[Steven E. Protter]

Shalom,

The problem source is not identified.

So I recommend the following:
1) Review your OS patch list with the latest recommendations from Oracle, a sysadmin task.
2) See that your kernel parameters versus use do not create bottlnecks.
3) Have the DBA check metalink for oracle patches.
4) Have the DBA check the SGA, status pack tuning and other options to improve database performance.
5) Have the DBA perform some sql statemetns to identify top i/o queries and review with the applications people if those sql queries can be improved.

You need to be part of a team here, work with the DBA.

SEP

[Mike Tufariello]

SEP,

I appreciate your feedback.

To quickly go off on a tangent, the reason I submitted an inquiry to this forum is because the Oracle DBA perception is that there is no problem.

I am at the current Patch level that HP has recommended. Our kernel settings, etc. were based strictly on what the VAR said we needed to make their software function "properly".

I will defintely review the other items you noted.

Thanks

[Oviwan]

hey

can you specify "backup job"

>prior to our backups we do shutdown the Oracle databases
do you take an offline backup or you use rman? do you write direct to the tape or first on filesystem and then backup with dataprotector?

Regards

[TTr]

This sounds like a resource leak somewhere and since you shutdown oracle every night before the backups I would agree with your DBAs that it is not an oracle problem. First check that when you shutdown oracle and the app, there are no oracle or app processes and memory segments that are left behind.

The next thing I would try is to shutdown and restart data protector maybe every 2 -3 days. I think you can do this safely during the day. Is this server a dataprotector server or is it a client? You may have to shutdown DP in both the server and the client. Ensure that no processes are left behind when you restart DP.

[Yogeeraj_1]

hi Mike,

Questions:
1. How big is the database?
2. By how much has the database increase in the past 4 months?


I belive Data protector is using RMAN to backup the database. Can you try to do a disk backup and compare with the time taken to backup the same on tape media?

>Is there maintainance, either hardware or Oracle, that should be performed on a regular basis to keep the database in "better" shape?

The database is in good 'shape' when it is always ON. :) Restart of the database has never been a means to solve performance problems of any kind except in cases of changes in the initialisation parameters.

You should verify if there are scheduled jobs or any other processes running at the same time interval that the backup is running.

kind regards
yogeeraj

[Mike Tufariello]

Thanks to all who have replied so far.

A number of you seem to be focusing in on Data Protector and or database size. The drastic decrease in run time is not limited to the backup jobs, but all jobs running aganist the Oracle database. If backup run time were the only issue I would look more seriously into DP.

But to answer some concerns that were raised. I shut down DP once a week to backup it's database. I run my backups to tape not to disk. I use DP functionality, not rman. The Oracle database is stopped, as are all Oracle processes, when the backup is running.

The size of the database did not change between the jobs and backups run on Friday 4/25 and Monday 4/27, yet the run time for identical jobs and backups, runo nFriday and then on Monday, decreased by almost 1 hour, after the UNIX host and EVA were powered off and then powered back on.

I've looked at other documentation concerning online JFS and file and directory defrag is mentioned as a regular utility to run. I've been told that this is not necessary, particularly on an EVA. But I throw this out there for other's opinions.

Thanks again,

[TTr]

> The drastic decrease in run time is not limited to the backup jobs, but all jobs running aganist the Oracle database. If backup run time were the only issue I would look more seriously into DP.

If there is a resource leak and it is caused by DP, the impact would be everywhere on the server not just DP. For example if you have runaway processes, whether they are oracle or DP, and they eat up memory and keep it reserved or eat up a CPU at 100% with NOOP activity, your entire server would suffer.

I would seriously look if there are any leftover oracle processes both when oracle is shutdown and even right now. Check with the ps command if there are oracle processes that have a STIME earlier that last nights oracle restart.
Do the same for DP processes.
When oracle is shutdown check for un-released memory "ipcs -a".
With ipcs -a check for unusual and/or large memory segments that are in use, check there creation time too.

Oracle and DP are prime suspects. Then look at what else is running on that server. if you don't have any nonHP-UX software look at nonessential HP-UX sw such as EMS, STM, CIMs, etc and see if you can cleanup or temporarily turn off some of them.

Do you have glance installed? Check for CPU, mem, (and mem cache), disk, swap etc and observe which one goes up corelating to performance decrease.

[Mike Tufariello]

Thanks for the follow up. I will watch the items you mention. If the pattern repeats itself it will time, maybe months, for the jobs to start running longer. BUt in the meantime I can examine those things you point out.

[Rita C Workman]

Backups simply write files to tape. They don't think - they just write files to tape.

So...go back on your older backups that showed the final high level of increase in time to complete. You might be able to narrow down where the increase is coming from. Check the time it took for each mountpoint to finish. Which one(s)increased significantly.
More then like something is left running and that is writing out files steadily - till you reboot. You may be surprised to find it might be only 1 or 2 mountpoints.

If you can narrow down where the increase is located - then watch those and see if you can track down what is writing so much. Wouldn't it be a 'giggle' if it were oracle archives or logs that the someone should be doing a regular cleanup on. But it could be some rogue process that is just writing over and over again.......

Happy hunting,
Rgrds,
Rita

[Rita C Workman]

One dumb question....

Is your Data Protector cell manager on an HPUX server or on a Windows box ??

/rcw

[Mike Tufariello]

I will examine my backup logs and see if I can idntify a specific mount point. But I beleive last time I checked all the mount points were taking long to backup up.

My cell manager is install on my HP-UX server.

Thanks,

[Paul Gerke]

Hi Mike,
This sounds like a memory leak and paging issue, although on systems with Oracle one often sees failures to start the database due to insufficient memory before page thrashing slows jobs, but...

Have you looked at swap to determine that swap utilization is not increasing?