Users unable to save Changes to the database

[covaro]

A little bit of pre-info:
I'm fairly new to managing a site with an Oracle DB and might be missing some obvious stuff here and there. Awoke this morning with msg from customer stating that no one could access their Oracle DB. Note: this DB has been working fine as of Friday @ 1PM when the business closed for the holiday.

Upon arrival this morning the Forms program was returing errors ORA-12519 and 12516. Attempting to log into EM gave the following:
"The database status is currently unavailable. It is possible that the
database is in mount or nomount state. Click 'Startup' to obtain the current
status and open the database. If the database cannot be opened, click 'Perform
Recovery' to perform an appropriate recovery operation."

Worked through with Oracle and was able to resolve the issue and users could connect to the Database again.

Short time later attempted to connect to EM and noted that is was not active and was returning nothing when attempting to login. Started EM on the server and recieved the same message as above. Then the users were not able to access the database again as well. =(

Corrected issue again so that users could connect and then was informed that they could not save data.

Created a test table called "blargh" and input some data into it. Able to read the data back from the local station. Committed it to the database and tried from another workstation... could not find the table "blargh" nor display and of the data that was put in it. Attempting to drop the table from the any station results in the following:

SQL> drop table blargh;
drop table blargh
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: 'F:\ORACLE\ORADATA\TRPB\SYSAUX01.DBF'
ORA-06512: at line 16

Currently have a TAR open with Oracle, but I'm just curious on any pointers of where I could start looking regarding this problem while I wait for them to get back to me.

-Scott

[marist89]

does F:\ORACLE\ORADATA\TRPB\SYSAUX01.DBF exist? maybe the filesystem was unmounted?

[covaro]

Yes, the file does indeed exist. The F:\ is read and writeable via Explorer or any other program.

Currently I'm awaiting on a cold DB restore from Saturday night to complete. The DB is pretty much only used M-F 9-5, so there is no dataloss anticipated.

Currently it seems that Oracle is a bit perplexed at my situation as well since I spent pretty much the first 4 hours of the day on the phone with them and they currently have the TAR in for research.

-Scott

[marist89]

Yes, the file does indeed exist. The F:\ is read and writeable via Explorer or any other program.

That's not what the error says.

[covaro]

Well, I can only tell you what I know so far (not as much as I would like). But I can definately do whatever I want in the F:\ as long as it doesn't have to do with Oracle it seems. I've created new files, etc on the drive, deleted them, etc. All works great.

I'm still in mad scramble mode getting myself learned on the whole Oracle setup here (this had been working fine for them for about a year, so I was hoping to start learning more about the setup in a month or so once I got the rest of the problems sorted out)...

With the huge myriad of problems that just suddenly appeared over the weekend I'm just not sure what to think atm.

-Scott

[marist89]

With the huge myriad of problems that just suddenly appeared over the weekend I'm just not sure what to think atm.

what changed?

[thomasp]

Did you have backups running over the weekend? maybe the file is being locked by an OS backup or some other process.... you never know

[covaro]

Saturday nights the entire Oracle DB is stopped via script and then a full backup via Veritas is run on the cold database. Sunday Morning around 6 or so the Database is brought back up via another script. Plenty of time for the backup to run (20GB or so with a 10 hour window to complete).

Machine was rebooted Monday morning before I arrived.

Monday Morning no one could access the database via their Forms program (or anything else for that matter). Was getting ORA-12519 and ORA-12516 errors.

EM was giving me their message from the first post I put up regarding the database.

Called Oracle, worked through with them:
Started with ORA-1012: Not logged in when we first got into SQLPlus
Shutdown immediate resulted in:
ORA-24324: service handle not intitialized
ORA-24323: value not allowed
ORA-00020: maximum number of processes (%s) exceeded
Shutdown abort gave us:
ORA-01031: insufficient privileges

Stopped the service
Restarted the service
Had me SET ORACLE_HOME and ORACLE_SID
Tested Database to verify we were connected via:
select count(*) from dba_objects;
Got 48374
Was able to connect via the Forms program

About 20-30 minutes later, recieved word from a user that they couldn't save any information to the database. Do some testing, call Oracle.

They have me create a table with some data via SQLPlus on the server, can see the data after it is created. Go to workstation, login via SQLPlus, can't see that newly created table. Attempt to create table via this instance of SQLPlus, creates and adds data, can find the data on that machine. Can not see data on Server SQLPlus instance.

Attempt to drop table recieves:
SQL> drop table blargh;
drop table blargh
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: 'F:\ORACLE\ORADATA\TRPB\SYSAUX01.DBF'
ORA-06512: at line 16

Currently the listener service enters a blocked state about every 10 minutes or so and needs to be restarted as well (this is fairly new... was working as read only yesterday).

lsnrctl service:

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 18-APR-2006 11:29:26

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=trpb06.trpb.com)(PORT=1521)))
Services Summary...
Service "TRPB.TRPB.com" has 1 instance(s).
Instance "trpb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:152 refused:0 state:blocked
LOCAL SERVER
Service "TRPBXDB.TRPB.com" has 1 instance(s).
Instance "trpb", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=trpb06.TRPB.com)(PORT=1276))
The command completed successfully


Had made an offline image of the machine yesterday and attempted to restore from Saturday's backup and was recieving the same issues after that, so I had restored the image and I'm basically back where we started.

-Scott

[KenCunningham]

A couple of things:

Are you sure the weekend backup completed and the database rerstarted successfully? Do you have logs to tell your this?

Who rebooted the machine? Do they usually do the reboot and did they notice anything unusual?

Have you looked at the alert log for the database to see whether it can offer any clues?

Sorry - more questions than answers at the moment!!

[thomasp]

I have the same questions as Ken. Do you know if the backup completed sucessfully? We use veritas here for backup and veritas locks all the files that are specified to be backed up, so if you access the database at this point and you are backing up the datafile, oracle will not be able to access this file.

Because you're saying the system was rebooted, any reason for the reboot? Since you said noone could access via the forms program, I assume the database was in a shutdown state and you did start up the database. Like Ken mentioned, did you check the alert logs for any errors or the state of the database at the time?

Also, are all of you trying to connect to the same database and are you all using the same schema name to connect?