Sudden shutdown and startup of Oracle instance

[Shilpa]

Hi,

We are facing a problem in Oracle OPS. One of the Oracle systems in
customer site, Oracle database instance shuts down and starts up on
its own.

The set-up is as follows:

1. 2-node Solaris-8 cluster.
2. Oracle 9.2.0.4


From the alert log file, we find the following shutdown messages.
#----------------------------------------------------------------------------------------------------------------------------
Sun Dec 16 06:36:07 2007
Starting control autobackup
Control autobackup failed with following error :
ORA-00230: operation disallowed: snapshot controlfile enqueue
unavailable
Sun Dec 16 07:04:43 2007
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 43
Sun Dec 16 07:04:46 2007
ALTER DATABASE CLOSE NORMAL
Sun Dec 16 07:04:47 2007
SMON: disabling tx recovery
SMON: disabling cache recovery
Sun Dec 16 07:04:47 2007
Shutting down archive processes
Archiving is disabled
Sun Dec 16 07:04:47 2007
Thread 1 closed at log sequence 53329
Sun Dec 16 07:04:47 2007
ARCH shutting down
Sun Dec 16 07:04:47 2007
ARCH shutting down
Successful close of redo thread 1.
Sun Dec 16 07:04:48 2007
ARC1: Archival stopped
Sun Dec 16 07:04:48 2007
ARC0: Archival stopped
Sun Dec 16 07:04:48 2007
Completed: ALTER DATABASE CLOSE NORMAL
Sun Dec 16 07:04:48 2007
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sun Dec 16 07:05:20 2007
Starting ORACLE instance (normal)
Sun Dec 16 07:05:20 2007
Global Enqueue Service Resources = 5072, pool = 16
Sun Dec 16 07:05:20 2007
Global Enqueue Service Enqueues = 10277
Disable cache advisory with old cache parameters
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
GES IPC: Receivers 5 Senders 5
GES IPC: Buffers Receive 1000 Send 830 Reserve 600
GES IPC: Msg Size Regular 440 Batch 2048
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
processes = 300
sessions = 500
timed_statistics = FALSE
shared_pool_size = 167772160
shared_pool_reserved_size= 20971520
_shared_pool_reserved_min_alloc= 4000
_kghdsidx_count = 1
control_files = /export/home/oracle/dev/rT001.ctrl1, /
export/home/oracle/dev/rT001.ctrl2
db_block_buffers = 10000
db_block_size = 4096
buffer_pool_recycle = buffers:100, lru_latches:1
max_commit_propagation_delay= 0
compatible = 9.2.0
log_archive_start = TRUE
log_archive_dest_1 = LOCATION=/export/home/oracle/dev/
archivelog1 REOPEN=10
log_archive_format = LOG_%s_%t.ARC
log_buffer = 16384
log_checkpoint_interval = 1000
db_files = 32
db_file_multiblock_read_count= 64
cluster_database = TRUE
cluster_database_instances= 4
thread = 1
fast_start_mttr_target = 60
recovery_parallelism = 15
dml_locks = 500
row_locking = always
instance_number = 1
transactions = 500
transaction_auditing = FALSE
undo_management = AUTO
undo_tablespace = rollback1
remote_login_passwordfile= NONE
cursor_space_for_time = TRUE
session_cached_cursors = 100
background_dump_dest = /export/home/oracle/admin/T001/bdump
user_dump_dest = /export/home/oracle/admin/T001/udump
core_dump_dest = /export/home/oracle/admin/T001/cdump
audit_trail = FALSE
sort_area_size = 4194304
db_name = T001
open_cursors = 1000
ifile = ?/dbs/configT001.ora
optimizer_mode = first_rows
Sun Dec 16 07:05:21 2007

#----------------------------------------------------------------------------------------------------------------------------

We have escalated this problem to Oracle and they asked us to collect
the listener and the Statspack output. But so far we haven't received
any concrete reply from them.

Could you please help in this,as we do not have a very deep
understanding of these logfiles.

Best Regards
Shilpa

[fitzj...@cox.net]

Comments embedded.

On Dec 18, 7:20 am, Shilpa <rsh...@gmail.com> wrote:
> Hi,
>
> We are facing a problem in Oracle OPS.

No, you're not. OPS was last seen in Oracle 8.1.7.4; you're using
RAC, which is MUCH different.

> One of the Oracle systems in
> customer site, Oracle database instance shuts down and starts up on
> its own.
>

And is this particular instance/server configured exactly as the
other, working instances/servers?

> The set-up is as follows:
>
> 1. 2-node Solaris-8 cluster.
> 2. Oracle 9.2.0.4

And 9.2.0.4 is not a supported release; likely you'll be told to patch
this to the terminal release of 9.2 which is 9.2.0.8.

>
> From the alert log file, we find the following shutdown messages.

> #-----------------------------------------------------------------------------------------------------------------------------

This ^^^^^^^^^^^^^^^ may not be set large enough as you have
cursor_space_For_time set to TRUE.

> shared_pool_reserved_size= 20971520

> _shared_pool_reserved_min_alloc= 4000
> _kghdsidx_count = 1

These 'hidden' parameters are set by whose instruction?

> control_files = /export/home/oracle/dev/rT001.ctrl1, /
> export/home/oracle/dev/rT001.ctrl2
> db_block_buffers = 10000
> db_block_size = 4096

Shouldn't you be using a db_block_size of 8192?

> buffer_pool_recycle = buffers:100, lru_latches:1
> max_commit_propagation_delay= 0
> compatible = 9.2.0
> log_archive_start = TRUE
> log_archive_dest_1 = LOCATION=/export/home/oracle/dev/
> archivelog1 REOPEN=10
> log_archive_format = LOG_%s_%t.ARC
> log_buffer = 16384
> log_checkpoint_interval = 1000
> db_files = 32
> db_file_multiblock_read_count= 64
> cluster_database = TRUE
> cluster_database_instances= 4
> thread = 1

> fast_start_mttr_target = 60

Normally I see this ^^^^^^ set to 300, not 60.

> recovery_parallelism = 15
> dml_locks = 500

> row_locking = always

Why? ^^^^^^^^^^

> instance_number = 1
> transactions = 500
> transaction_auditing = FALSE
> undo_management = AUTO
> undo_tablespace = rollback1
> remote_login_passwordfile= NONE

> cursor_space_for_time = TRUE

Why? ^^^^^^^^^^^^

> session_cached_cursors = 100
> background_dump_dest = /export/home/oracle/admin/T001/bdump
> user_dump_dest = /export/home/oracle/admin/T001/udump
> core_dump_dest = /export/home/oracle/admin/T001/cdump
> audit_trail = FALSE
> sort_area_size = 4194304
> db_name = T001
> open_cursors = 1000
> ifile = ?/dbs/configT001.ora
> optimizer_mode = first_rows
> Sun Dec 16 07:05:21 2007
>

> #-----------------------------------------------------------------------------------------------------------------------------

>
> We have escalated this problem to Oracle and they asked us to collect
> the listener and the Statspack output.

One presumes you've honored their request. Why, then, could you not
see fit to post that information here as well?

> But so far we haven't received
> any concrete reply from them.

And you think a newsgroup will be faster and more reliable?
Especially when you won't provide sufficient information to analyze
the perceived problem?

>
> Could you please help in this,as we do not have a very deep
> understanding of these logfiles.

Nor do you have any respect for the people in this newsgroup, asking
them to do work which you should already have done when providing data
in support of your claims.

>
> Best Regards
> Shilpa


Provide us what you supplied Oracle and, maybe, someone can answer
your question.


David Fitzjarrell

[geese howard]

On Dec 18, 9:20 pm, Shilpa <rsh...@gmail.com> wrote:
> Hi,
>
> We are facing a problem in Oracle OPS. One of the Oracle systems in
> customer site, Oracle database instance shuts down and starts up on
> its own.
>
> The set-up is as follows:
>
> 1. 2-node Solaris-8 cluster.
> 2. Oracle 9.2.0.4
>
> From the alert log file, we find the following shutdown messages.

> #--------------------------------------------------------------------------­--------------------------------------------------

> #--------------------------------------------------------------------------­--------------------------------------------------

>
> We have escalated this problem to Oracle and they asked us to collect
> the listener and the Statspack output. But so far we haven't received
> any concrete reply from them.
>
> Could you please help in this,as we do not have a very deep
> understanding of these logfiles.
>
> Best Regards
> Shilpa

i think the problem is about

ORA-00230: operation disallowed: snapshot controlfile enqueue
unavailable

maybe your system has enquen lock.run this sql to find enquen lock.

select s.sid, username, program, module, action, logon_time, l.*
from v$session s, v$enqueue_lock l
where l.sid = s.sid
and l.type = 'CF'
and l.id1 = 0
and l.id2 = 2

you can find something from metalink
https://metalink.oracle.com/metalink/plsql/f?p=130:14:4845429712286414701::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,145619.1,1,1,1,helvetica

[Harirama Patil]

Hi shilpa,

In which company you are working

 

[fitzj...@cox.net]

Comments embedded.

How did you determine this? What metrics did you use? What proof do
you have, other than the error message in the alert log? Do you have
access to the trace files the OP failed to post?

> run this sql to find enquen lock.

They're called enqueue locks, not enquen lock. And all you've done
here is post content from the Metalink document you've referenced at
the end of your submission.

>
> select s.sid, username, program, module, action, logon_time, l.*
> from v$session s, v$enqueue_lock l
> where l.sid = s.sid
> and l.type = 'CF'
> and l.id1 = 0
> and l.id2 = 2
>

> you can find something from metalinkhttps://metalink.oracle.com/metalink/plsql/f?p=130:14:484542971228641...

Certainly he can, if he's running 8.1.6 or 8.1.7 and running RMAN
backups that fail. Funny, that's not what the OP is reporting, and
8.1.x is not the release the OP is running (the stated release is
9.2.0.4). Running pell mell through Metalink scrounging for any
message related to an ORA-00230 could reveal all sorts of documents,
however none which are appropriate to the situation as posted. Yes,
it appears the OP is running RMAN backups (a fact he also failed to
mention) but the Metalink document you reference has nothing to do
with the database shutting down, only the fact that the RMAN backup
fails. Those are two different situations.

If you're going to provide assistance provide USEFUL assistance, not
anything that passes before your eyes and matches the reported error
number.

>
>

My original comments still stand: supply to us the same information
you've supplied to Oracle. When you do so then we will be better able
to assess your situation and properly address the problem at hand.



David Fitzjarrell

[fitzj...@cox.net]

On Dec 26, 11:20 pm, "Harirama Patil" <harirama.pa...@gmail.com>
wrote:

> Hi shilpa,
> In which company you are working
>
> On 12/18/07, Shilpa <rsh...@gmail.com> wrote:
>
>
>
>
>
> > Hi,
>
> > We are facing a problem in Oracle OPS. One of the Oracle systems in
> > customer site, Oracle database instance shuts down and starts up on
> > its own.
>
> > The set-up is as follows:
>
> > 1. 2-node Solaris-8 cluster.
> > 2. Oracle 9.2.0.4
>
> > From the alert log file, we find the following shutdown messages.
>

> > #--------------------------------------------------------------------------­--------------------------------------------------

> > #--------------------------------------------------------------------------­--------------------------------------------------

>
> > We have escalated this problem to Oracle and they asked us to collect
> > the listener and the Statspack output. But so far we haven't received
> > any concrete reply from them.
>
> > Could you please help in this,as we do not have a very deep
> > understanding of these logfiles.
>
> > Best Regards

> > Shilpa- Hide quoted text -
>
> - Show quoted text -

Of what use is that information? Why do you need to know where he is
working? Does that somehow clarify the problem?


David Fitzjarrell

[vesty]

observation:-

Sun Dec 16 07:04:43 2007
> Shutting down instance: further logons disabled
> Shutting down instance (immediate)
> License high water mark = 43
> Sun Dec 16 07:04:46 2007
> ALTER DATABASE CLOSE NORMAL
> Sun Dec 16 07:04:47 2007

==============

it clearly states a shutdown (manual/through script )is received by
oracle.
if had it been clusterfailure an shutdown abort would had been there
in alert log.

so shilpa
1. has to 1st look into this;
2. anyways alert log and cluster logs will be helpful for root
cause...

> David Fitzjarrell- Hide quoted text -

[fitzj...@cox.net]

On Dec 27, 11:41 am, vesty <ometal...@gmail.com> wrote:
> observation:-
>
> Sun Dec 16 07:04:43 2007> Shutting down instance: further logons disabled
> > Shutting down instance (immediate)
> > License high water mark = 43
> > Sun Dec 16 07:04:46 2007
> > ALTER DATABASE CLOSE NORMAL
> > Sun Dec 16 07:04:47 2007
>
> ==============
>
> it clearly states a shutdown (manual/through script )is received by
> oracle.

No, it clearly states a shutdown immediate is occurring. Whether or
not that was initiated by a script has yet to be proven.

> if had it been clusterfailure an shutdown abort would had been there
> in alert log.

You can prove this? What I see in the alert log from a process
failure is this (killed PMON at the O/S level):

Thu Dec 27 15:25:09 2007
MMNL: terminating instance due to error 472
Instance terminated by MMNL, pid = 5124242

No 'shutdown abort' reported. Killing the LGWR process produces:

Thu Dec 27 15:31:09 2007
Errors in file /oradba/app/oracle/admin/oralimd1/bdump/
oralimd1_pmon_1532046.trc
:
ORA-00470: LGWR process terminated with error
Thu Dec 27 15:31:09 2007
PMON: terminating instance due to error 470
Instance terminated by PMON, pid = 1532046

Again, no 'shutdown abort' reported by Oracle in the alert log.

Of course you're welcome to produce alert log entries to support your
claim.

>
> so shilpa
> 1. has to 1st look into this;

He has been making an attempt to do so; how is your advice different
from actions he's already undertaken?

> 2. anyways alert log and cluster logs will be helpful for root
> cause...
>

He's looked into the alert log, and possibly his cluster logs (we
don't know as he hasn't stated such). Again, what does this advice
provide the OP?

> > - Show quoted text -- Hide quoted text -

>
> - Show quoted text -

David Fitzjarrell

[Shilpa]

Thanks a lot for the information...
There is a full FS backup during this problem...
1) The problem is seen only during a full Filesystem backup of /export/
home/
2) Problem not seen during incremental backups
3) The problem autmatically stops when the backup is stopped.

Can you please let me know what's the relationship between the backup
and Oracle shutdown/startup ?
I shall upload few logs:
1) statspack
2) sar
3) listener logs

[Shilpa]

Hello,

I have uploaded the following logs.

1) IN2.tar.gz
2) IN3.tar.gz
3) messages.in3ce1.tar.gz
4) Showrev_Algeria.zip
5) sar_output.doc

PS: Its Ms. Shilpa and not Mr. Shilpa ;)

[fitzj...@cox.net]

My apologies for the mistake in gender.

What, exactly, is contained in the /export/home filesystem? I suspect
it's also your ORACLE_HOME for this installation and therein may lie
your problem. A full filesystem backup of your ORACLE_HOME (and any
database files you've located there) may be sufficient to cause Oracle
to shutdown the database automatically. This may be a similar problem
to that listed in Metalink Note 277274.1.


David Fitzjarrell

[Sachin Arora]

just an observation in last david's mail.

"shutdown abort" is never reported in alert log unless a "shutdown abort" command is issued.
If instance dies because of failure of any background process, the alert log will show that "process" as the cause of instance failure.

in this case "immediate" is there in alert log. So it is clear that either "immediate" was issued by user or by script [user created or orale's own].

immediate shutdown cant be without an intent [of oracle or of user].

Sachin

On Dec 28, 2007 3:07 AM, fitzj...@cox.net <fitzj...@cox.net> wrote:

> On Dec 27, 6:59pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
>
>
> > On Dec 26, 11:20pm, "Harirama Patil" <harirama.pa...@gmail.com>

[fitzj...@cox.net]

Comments embedded.

On Dec 27, 11:22 pm, "Sachin Arora" <oraclear...@googlemail.com>
wrote:

> just an observation in last david's mail.
>
> "shutdown abort" is never reported in alert log unless a "shutdown abort"
> command is issued.

I have already stated this. Why repeat it?

> If instance dies because of failure of any background process, the alert log
> will show that "process" as the cause of instance failure.
>

As illustrated. Your point being?

> in this case "immediate" is there in alert log. So it is clear that either
> "immediate" was issued by user or by script [user created or orale's own].
>

Incorrect. This is a RAC environment and Metalink Note 277274.1
clearly provides a scenario where a database shuts down cleanly on its
own without user intervention.

> immediate shutdown cant be without an intent [of oracle or of user].
>

Again, this is incorrect. Please see Metalink Note 277274.1.

> Sachin

David Fitzjarrell