Home » RDBMS Server » Server Administration » Database stops working (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Database stops working [message #651782] Wed, 25 May 2016 02:02 Go to next message
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 #651783 is a reply to message #651782] Wed, 25 May 2016 02:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
"crash" is not a known error message. Useful error messages are of the form ORA-nnnnn. What are they?
Re: Database stops working [message #651786 is a reply to message #651783] Wed, 25 May 2016 02:17 Go to previous messageGo to next message
TonSchoen
Messages: 24
Registered: June 2007
Junior Member
At the moment the database stops working no records are written to the alert log and no errors are returned. The database just doesn't work anymore, without reporting anything.

[Updated on: Wed, 25 May 2016 02:17]

Report message to a moderator

Re: Database stops working [message #651787 is a reply to message #651786] Wed, 25 May 2016 02:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
"just doesn't work" is not a known error message.
Re: Database stops working [message #651788 is a reply to message #651787] Wed, 25 May 2016 02:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #651792 is a reply to message #651788] Wed, 25 May 2016 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So do it as we have no information this is only advice we can give you.
We even don't know if the database is up or no. You said it crashed but didn't post any evidence of this. How do you know it crashed?

Re: Database stops working [message #651793 is a reply to message #651792] Wed, 25 May 2016 03:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #651794 is a reply to message #651793] Wed, 25 May 2016 03:15 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
What O/S?
Re: Database stops working [message #651796 is a reply to message #651794] Wed, 25 May 2016 03:40 Go to previous messageGo to next message
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 #651797 is a reply to message #651796] Wed, 25 May 2016 03:49 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Any clues in the Windows Event Log?
Re: Database stops working [message #651798 is a reply to message #651796] Wed, 25 May 2016 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So the database did not crash and is still alive if you have some answer.
You have to connect on the database server to know what is happening.
What is the database server CPU usage? How many CPU?
Connect as sysdba from sqlplus locally on the database server.

Re: Database stops working [message #651799 is a reply to message #651796] Wed, 25 May 2016 03:51 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 #651802 is a reply to message #651801] Wed, 25 May 2016 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CPU usage?
And in which state is it now?
As already mentioned, anything in the Event Log?

Re: Database stops working [message #651803 is a reply to message #651801] Wed, 25 May 2016 04:24 Go to previous messageGo to next message
gazzag
Messages: 1118
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:
C:\> lsnrctl status

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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #651806 is a reply to message #651782] Wed, 25 May 2016 08:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
TonSchoen wrote on Wed, 25 May 2016 00:02
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.


Was the upgrade done "in place"? By that I mean is the V12 database running on the exact same hardware as original V10 database.
Is V12 database running of different hardware that is separate & distinct from old V10 database?

Is Windows OS crashing & rebooting making Oracle the victim & not the culprit?
Re: Database stops working [message #651807 is a reply to message #651806] Wed, 25 May 2016 08:18 Go to previous messageGo to next message
TonSchoen
Messages: 24
Registered: June 2007
Junior Member
We 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.
Re: Database stops working [message #651808 is a reply to message #651807] Wed, 25 May 2016 08:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
But does the virtual machine still work fine when oracle doesn't?
Re: Database stops working [message #651809 is a reply to message #651807] Wed, 25 May 2016 08:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
TonSchoen wrote on Wed, 25 May 2016 06:18
We created a new virtual machine, on which the a new Oracle database is created.


OH! This is news.

What OS is the VM running?
is the new VM crashing?
Re: Database stops working [message #651810 is a reply to message #651807] Wed, 25 May 2016 09:30 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
TonSchoen wrote on Wed, 25 May 2016 14:18
We 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 Go to previous message
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

Previous Topic: Can we upgrade an os of an existing db installation to one that is not certified?
Next Topic: Is it better to use DBUA for database upgrade on windows platofrm?
Goto Forum:
  


Current Time: Thu Mar 28 16:03:22 CDT 2024