| Database stops working [message #651782] |
Wed, 25 May 2016 02:02  |
TonSchoen
Messages: 24
Registered: June 2007
|
Junior Member |
|
|
Recently we upgraded out databaseversion from Oracle 10 to Oracle 12. Since we upgraded the database we have unpredictable database crashes in our production environment. At the moment of a crash it's not possible to connect to the database. The session that are running are not able to run some queries. At the moment of a crash the load of the database is quiet normal, just like the number of connections and sessions. Both doesn't reach the maximum.
We're using a .NET-client with hardly any queries in it. All the logic is implemented in database packages. The utl-dir package and dbms_scheduler are used a lot in the software. About 15 minutes before the crash appears, no dbms_scheduler jobs are started. I mean no scheduled jobs and no jobs that are started by the dbms_scheduler.run_job procedure.
Who can help us?
Oracle can't or won't do it!
[Updated on: Wed, 25 May 2016 02:14] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Database stops working [message #651788 is a reply to message #651787] |
Wed, 25 May 2016 02:49  |
TonSchoen
Messages: 24
Registered: June 2007
|
Junior Member |
|
|
|
I'm just reporting what's happening. The database doesn't report any errors or messages. It's not possible to use the database anymore. The only thing working is stop and start the database.
|
|
|
|
| Re: Database stops working [message #651791 is a reply to message #651788] |
Wed, 25 May 2016 02:55 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You need to make some attempt at diagnosis. You have not even said how you "stop and start" the database. You have not said what are doing (or trying to do) when "It's not possible to use the database anymore".
What does your DBA say?
|
|
|
|
|
|
| Re: Database stops working [message #651793 is a reply to message #651792] |
Wed, 25 May 2016 03:01 |
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Surely you must get an error when you try to connect. If nothing else the connection process has to time out sooner or later.
How are you trying to connect? Application? Sqlplus? Have you tried connecting as sysdba?
|
|
|
|
|
|
| Re: Database stops working [message #651796 is a reply to message #651794] |
Wed, 25 May 2016 03:40 |
TonSchoen
Messages: 24
Registered: June 2007
|
Junior Member |
|
|
The operating system is Microsoft Windows x86 64-bit Windows 2012 Standard.
In case the database hangs we try to connect using SQL*Plus without any response at all. No errors, just a busy indicator. Usually we've some open sessions in SQL*Plus or TOAD. When we use such an open session to run a query we also don't get any result.
The application uses a connection pool, getting some data results in a application time out after 2 minutes.
|
|
|
|
|
|
|
|
| Re: Database stops working [message #651799 is a reply to message #651796] |
Wed, 25 May 2016 03:51 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
So if I read this correctly, any existing connection and any new connections eventually time out.
SQL*Plus should give you some sort of error message. What is it?
How is your SQL*Plus session attempting to connect? With TCP or IPC?
Are you connecting through a database listener?
What about name resolution - are you using a tnsnames.ora file, or something else?
Do you see any activity in the oracle.exe executable image?
How are you restarting the database instance?
|
|
|
|
| Re: Database stops working [message #651801 is a reply to message #651799] |
Wed, 25 May 2016 04:16 |
TonSchoen
Messages: 24
Registered: June 2007
|
Junior Member |
|
|
Connecting to the database on the database as sysdba without using the listener is also not possible. The database is restarted by stopping and starting the windows service.
The database doesn't do anything at all. It doesn't write to the alertlogfile anymore and SQL*Plus and TOAD doesn't give any message at all.
I added the last statspack snapshot, taken a few minutes before the database stopped working.
[Updated on: Wed, 25 May 2016 04:19] Report message to a moderator |
|
|
|
|
|
| Re: Database stops working [message #651803 is a reply to message #651801] |
Wed, 25 May 2016 04:24 |
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
|
Senior Member |
|
|
It would be handy to have some visibility of what you describe. So, on the database server:
C:\> set ORACLE_SID=<db_name>
C:\> sqlplus / as sysdba
Then:
Please copy the two sessions and paste them here.
|
|
|
|
| Re: Database stops working [message #651805 is a reply to message #651801] |
Wed, 25 May 2016 07:53 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You are working the machine quite hard, it is possible that your problem is Windows related, not Oracle related. What does the OS report for the time when the DB is hanged? In the meantime, there is some tuning you could do to reduce the stress on the database.
Replace your online logfile groups with ones that are 1GB big, I think they are only 200MB at the moment.
Raise your SGA target to 3G
Consider your strategy for dynanmic sampling: why is it disabled?
Reset db_file_multiblock_read_count and _smu_debug_mode
Be sure to gather system statistics
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Database stops working [message #651810 is a reply to message #651807] |
Wed, 25 May 2016 09:30 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
TonSchoen wrote on Wed, 25 May 2016 14:18We created a new virtual machine, on which the a new Oracle database is created.
The Windows machine still works fine at the moment Oracle doesn't work. Come on, man! More information! How do you know Windows is fine? Please show how busy the machine is, and how busy the oracle.exe process is. You have been asked this before.
The fact that is a VM makes the whole business very problematic. Perhaps your hypervisor (or whatever it is) is throttling back the process or the whole VM. I did say that you are working the machine quite hard. What does your VM administrator tell you? I have known VMware go crazy when CPU or IO goes above some limits.
What did Oracle say when you asked for help? Was it that running on a VM is not supported?
|
|
|
|
| Re: Database stops working [message #651823 is a reply to message #651810] |
Wed, 25 May 2016 14:40 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Did you check how full the volume holding the oracle database files and software is? If those run out of space your database will just stop doing anything until space is made available. Since you went from 10 to 12 and 12 takes much more space it is something to check.
[Updated on: Wed, 25 May 2016 14:41] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
