[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle 8i Access Denied to Random Data Files

Posted on 2009-04-22
10
Medium Priority
?
491 Views
Last Modified: 2013-12-19
Hello all,

This is the first time I have posted a question to Expert's Exchange, so I hope I get it right for all the long time experts out there.

I have run into an Oracle problem that I can't seem to find an explainantion for.  On a random day on a random Oracle data file I get an access denied error when the database starts up.  This database has been in production for the last 10 years and the only time I have seen these errors is when our Backup software or Antivirus software has locked the file for its use.

To give a bit of background to the Oracle instance,
   Oracle 8.1.7.3.0 Enterprise Edition
   Windows 2003 Standard R2 SP2
   4GB of RAM (Windows is configured with /3GB switch to allow more memory to the Oracle process)
   Hard disks are connected via an EMC SAN and data files are spread across the SAN to try and best accomodate disk I/O
   There haven't been any major changes to the system in the past 2 weeks (the period in which the problem has started to occur)
   The only recent change has been to a couple of the disk drives as we added space and moved a couple data files around (these files have never been reported as a problem)
   General history of changes are
       Monthly Windows Updates
       Daily updates to McAfee antivirus
       Monthly reboot of the Windows Server

I have attached a few trace files and the an portion of the Oracle alert log.  I am all out of ideas as I can't think of any other items that would be causing an Access Denied error for Oracle.  The backs have been modified such that they don't touch the database drives and the same is true for antivirus, there is a policy implemented not to scan the drives with the database files located on them.

If anyone has other ideas it would greatly appreciated if you shared them.

Thanks.
or03ALRT.txt
or03CKPT.txt
or03LGWR.txt
or03SNP0.txt
0
Comment
Question by:nftcadmins
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24206808
Running active antivirus on an Oracle server is not good. I never recommend this, historically you can bring down your instance and cause other problems, besides performance problems. It is better to ensure a secure database environment, where people cannot use an unsecured browser from the database box, and only scan using an antivirus CD when needed, but not using an active setup.

If I were you, I would deinstall or deactivate Mcafee, and see if the problem happens again.
 
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 24207807
If the backup of the Oracle database files is not done before the Oracle database is started, that *WILL* cause this problem!  Make sure the backup is done first or exclude the Oracle database files from the backup.  I usually back up the Oracle database files to an alternate disk or directory manually (or via a scheduled shell script or batch file) then have the backup software only back up the Oracle files that are already in the alternate location, but leave the (possibly active) Oracle database files alone.
0
 
LVL 1

Author Comment

by:nftcadmins
ID: 24208315
Thanks guys for the comments.  Definitely, I agree with the Backup and Antivirus.   To be a bit more clear the backup is actually a shell script started 5 mins after the database is shutdown to copy the files to a location that the backup software can then backup.  Since this is a Windows box, we use XCOPY to complete this.  Checking the date stamps on the copied database files, they are all consist with the shutdown time leaving me to believe that the copy job is completed successfully before the database is started back up.

As for the antivirus, it has a policy in place to ignore pretty much everything except the C:\Windows folders.  But since its understandable that the behaviour of McAfee is sometimes a bit non-conventional I have gone forward and shutdown the services.

So with the initial two typical problems that cause these error messages, can I ask.  Is there any other ideas or experiences that people have run across?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 750 total points
ID: 24208534
With Oracle on Windows, it is a good idea to also shut down the Oracle service if you use SQL*Plus or SQLDBA to do the actual database shutdown, since the service will keep a lock on the database files for a while (at least a few minutes) after the shutdown otherwise.  Backup software is the only thing that I have seen cause the "access denied" or "unable to open file" problem with Oracle on Windows if it tries to back up the Oracle database files while the database is open.
0
 
LVL 1

Author Comment

by:nftcadmins
ID: 24208621
Mark, yes.  We also stop the Windows Oracle instance and listener service as part of the shutdown scripts.  I have too only seen backup software cause this problem with Oracle.  This is why I am posting my question to Experts Exchange.  I can't seem to come up with an explaination as to why this is happening and was hoping to draw out some experiences from other Oracle DBAs.

Thanks for your suggestions and comments.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 750 total points
ID: 24208691
Mcafee is one of the most agresive antivirus.
Stopping do not helps. You have to deinstall it.
Also check for firewalls.

But this can also mean block error of the file.
There is an utility DBVERIFY that can check
the file.
0
 
LVL 1

Author Comment

by:nftcadmins
ID: 24210138
Ran the DB Verify utility, it comes back and indicates that the data file is fine.  So, it definitely isn't a corrupt file just an unknown access issue between Oracle and Microsoft.  There are no firewalls other than what McAfee does for port blocking.

I am hoping that stopping the service will be sufficient for now.  But after talking with a few different sources, they definitely recommend to remove antivirus.  I will plan this for a future maintenance period.
0
 
LVL 1

Author Closing Comment

by:nftcadmins
ID: 31573358
Thanks for your help guys, I split the point awards between the two of you that answered.
0
 
LVL 1

Author Comment

by:nftcadmins
ID: 24261617
Turns out, with all the performance testing that we have been doing.  The additional tablespaces that were added to house the stats pack data + some other files that were sitting on the hard drives made the back up extend to +/- the entire backup window.  This pretty much explains why it happened one day and then not the next day, it was hit any missed.

My suggestion too everyone that reads this, is too make sure that you double, then triple check and then manually watch the backup run to ensure that there is no way your backup overlaps the startup of your database.  Even if the backup is just an shell copy command.

Thanks again everyone!
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24262891
Say hello to the good old friend Oracle 8i!
We miss him!
Good luck!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question