how to backup database (oracle) and performance tuning

[Oskar Teran]

hello people,
I have two questions:
1) Our hp box ( 10.20 - 9000/800) is running oracle 8.0.6. From what I can see in the cron job setup the database isn't being backed up on tape, just the data. Correct if I'm wrong but isn't it possible to also backup the database? For some reason the previous sys. admin. had told people here that the database on the unix box can't be backed up.
2) Also, we are considering hiring an oracle consultant to come here and do performance tuning on the unix box and the oracle database.
That box runs very slow even with just a little job sent to it.
From what i've been told no one has ever done any tuning on that machine.
What opinions or suggestions do you have the performance tuning being done?
Is it better to get an oracle consultant to do this or maybe someone from HP? The oracle database has Lawson running on it as well....the company here is VERY dependant on the Lawson application running smoothly.

thank you so much for any help or advice,
Oskar

[A. Clay Stephenson]

Yes it is possible to backup an ORACLE database but you can't just backup a normally run database and expect to be able to use it for anything useful like a restore to a functioning state. You must first shutdown the database (cold backup) or place the database (actually tablespaces) in backup mode (hot backup).
Cold backups are by far the most reliable especially for an ORACLE novice but they require the database to be down for the duration of the backup. Hot backups work quite well but the recoveries can be very tricky.
An alternative that I might suggest is a snapshot backup using Online JFS.
Under this scheme you:
1) Shutdown the database.
2) 'Snapshot' mount the database fiulesystem(s)
using mount -F vxfs -o snapof= ....
3) Startup the database
4) Backup the snapshots.
5) Unmount the snapshot(s).
The database downtime will be at most 5 minutes
and probably much less. You can then backup
the snapshots at leisure.
In any event you need to implement some method.
You could probably acquire all the knowledge you need for cold (or snapshot) backups from ORACLE books but I suggest that if you must do hot backups that you find a DBA. At this point compared to the importance of backups any database tuning needs are trivial.
One other suggestion is that you install a test oracle instance to test any backup scheme before moving to production.

[Thierry Poels_1]

hi,

1. "the database on the unix box can't be backed up."
Be glad that he is the 'previous' sysadmin and not the current one ;) Of course the database can be backed up : shutdown & filesystem backup, Oracle export, ...

2. if Oracle is the major application I would certainly recommend an Oracle consultant. Benefits of database tuning 'normally' outweigh operating system tuning. The ultimate choice would be however an Oracle consultant with major HP-UX knowledge,
(like me )

good luck
Thierry.

[Andreas D. Skjervold]

Hi
I'm running 8.0.6 and using Oracles Recovery Manager (RMAN) to run backups.
This allows you to backup online (hot) databases and also backup and remove archived redologs, so that you don't end up with an full archive filesystem, as the database then will stop.
RMAN is included in the Oracle software and can be set up to run standalone or using a recovery database (recomended).

To setup a Recovery solution like this might be quite advanced but once you got it up running you have a nice tool on your hands.

On setup of RMAN I recomend Oracle Metalink Note:109223.1 and 106432.1

As of performance tuning, the main topics that has to be checked and possibly adjusted is:
-database file placement
spread tablespaces over multiple drives, keep applikation/user objects out of the system tablespace
-redolog file size and number
Allow for the archiver to finish before redolog has to be reused, and aviod frequent log switches (generates checkpoint activity)
-rollback segment size and number
4-10 transactions pr segment(OLTP), 1 transaction pr segment for batch systems.
-latches

as well as a big enough SGA:
-DB_BLOCK_BUFFERS
-SHARED_POOL_SIZE
-LOG_BUFFER
Be advised not to overdo the size of the SGA, if you define the size bigger than the size available in your physical memory, Oracle will begin to swap the SGA out to disk and then you'r in trouble.

In the end I might add that tuning the application (SQL statements, indexes etc) has a far greater effect on performance than tuning the database itself. Oracle installations tend to be quite OK setup when installed out of the box.

Good luck!

regards
Andreas

[vince noto]

It looks like you wish to perform a cold backup of the Oracle database , which means you need to ensure the database is completely shutdown before beginning the backup. You should have the cron job issue a shutdown immediate of the database via svrmgrl and wait for the background process to terminate - ie pmon smon lgwr. Then you copy the control files , datafiles , and redolog files to tape. Then it is safe to start the database. You should also copy the archive log files to tape, but they can be copied when the database is running.