Keeping instance from idling

[cfdaddio]

Hello. I am new to Oracle; we are running it on our development server (Windows2000) to support one client. I was successful in installing it and getting it to work successfully within the application.

The problem we're having is that the instance will go idle after a certain time period, leaving the application unuseable until we re-start the instance/database from the command prompt. As you can imagine on our development server, the client might look at the application at any time, so having the database go to sleep and not be available isn't a good thing.

Is there a way to keep the instance running?

In any answers you might provide, please remember I'm inexperienced with Oracle.

Thank you.

Matt

 

[alexhu]

By 'go idle' do you mean hang ?

Do you have archiving switched on and setup correctly ?

Alex

 

[SantaMufasa]

Daddio,

I agree with Alex: there is nothing (normal) that causes an Oracle database to "idle". If what you mean is that the database is "hanging", then there are several things that could cause that to happen. And again, one of the more common causes is the one that Alex mentions: a log switch has occurred on your ARCHIVELOG mode database, but your database parameter "log_archive_start = false", which means that when a log switch occurs, and if your database is in ARCHIVELOG mode, then archiving must occur manually rather than automatically.

In your case, however, that may not be the problem since you say that bouncing the database temporarily resolves the issue.

A good investigative tool at this point is to read your instance's "alert<SID>.log" file. Go read the last several entries and tell us what you are seeing (perhaps even post the entries that look suspicious to you).

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:52 (08Mar04) UTC (aka "GMT" and "Zulu"), 09:52 (08Mar04) Mountain Time)

 

[cfdaddio]

First, thank you both for your replies. I do suppose I mean "hang". Basically, we're testing and our client's looking at application during the day and then when we come back in the morning, the app won't work because it can't connect to the database. I go into command-prompt and re-start database and then everything works fine.

As for the logging, I can't file the log file you've described (even using file/search). But, I can see that archiving is turned off in the database setup.

Hope that helps. If there are bunch of possible reasons, I would chalk it up to just not being configured properly. If you could give me a few of the biggies that a first-timer might have overlooked during setup, I will go through and confirm.

Thanks again.

Matt

 

[cfdaddio]

I found the file after all. Sorry, here are the last 2 entries before instance was terminated on Friday.

Fri Mar 05 22:52:44 2004
Errors in file e:\oracle\admin\orcl\bdump\orcl_ckpt_2440.trc:
ORA-00221: error on write to controlfile
ORA-00206: error in writing (block 3, # blocks 1) of controlfile
ORA-00202: controlfile: 'E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL'
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.

Fri Mar 05 22:52:44 2004
CKPT: terminating instance due to error 221
Fri Mar 05 22:52:46 2004
Errors in file e:\oracle\admin\orcl\bdump\orcl_pmon_1232.trc:
ORA-00221: error on write to controlfile

Thanks again.

Matt

 

[cfdaddio]

I might be onto something here. I think our backup routine is locking the .CTL files and that's causing it to terminate. I've have our network admin disable backup of that directory and I'll know tomorrow morning if that was the problem.

Thank you.

Matt

 

[SantaMufasa]

Matt,

Good detective work ! I'll bet that is it, since no process besides Oracle's should be locking your control files. Oracle needs to be able to write to your control files at each checkpoint. Since it cannot write to a file that another process has locked, the effect is similar to the one that Alex and I described with an archiving hang. So, we'll be eager to hear what you discover.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:14 (08Mar04) UTC (aka "GMT" and "Zulu"), 11:14 (08Mar04) Mountain Time)

 

[cfdaddio]

Just wanted to update you all. Our network guys de-listed the Oracle directory from our nightly backup ... and this morning the app was running just fine. I think that was the problem.

Another quick issue ... do you have another recommendation for a backup solution? We don't really want to leave Oracle without a backup.

Thanks.

Matt

 

[alexhu]

As this is not a production server - I suggest you shut the database down at night and let whatever backup routine you are using for the application save the database as well, then start it up again in the morning

Alex

 

[murraykr]

The backup software you are using may have the option to backup the files "without a lock". If you are using Veritas BackupExec as your backup software, the backup job can be configured to not lock the file when backing it up (right click on job, then properties, advanced tab, "open file backup, without a lock")