Hi
We have 9i db on win2k3. Since last two days our alert log file
is not showing anything . But database is running smoothly without
having any problem. Why alter log file is not updating ? Is there
any problem ? If So, what is it ?
Thanks in adv
Printable View
Hi
We have 9i db on win2k3. Since last two days our alert log file
is not showing anything . But database is running smoothly without
having any problem. Why alter log file is not updating ? Is there
any problem ? If So, what is it ?
Thanks in adv
Perform SHOW PARAMETER background_dump_dest
Check whether the destination directory is correct.
I have checked it already,the directory is correct. Any more ?
Check free space, rights ...
does it have any reason to update the alert.log? Perform a log switch (if you're logging log switches) and see if that is recorded.
if you cannot update the alert log you are fubared. If oracle cannot wrie to the alert log it will terminate itself when it tries to do so
try a log switch as suggested.
No thats not true at all..... Oracle can run even if it cannot write to alert.log file. However, if Windows permissions are not set correctly (or have recently been incorrectly changed) the database can potentially suffer serious problems such as errors writing to files and/or crashing.Quote:
Originally posted by stmontgo
if you cannot update the alert log you are fubared. If oracle cannot wrie to the alert log it will terminate itself when it tries to do so
try a log switch as suggested.
Check if SYSTEM has necessary permissions to write to alertlog file. If SYSTEM does not have full permissions to where the alert log resides, there will be no writes made to the alert log file.
Taken log switches. But it not recorded. Database is running nicely.
What is the problem ?
Make sure you are checking correct alert log file.
Check the maxsize set for the alert log. At times if it has reached the maxsize it stops writing to the alert log.
-Chintz
hmmm oh really?Quote:
Originally posted by adewri
No thats not true at all..... Oracle can run even if it cannot write to alert.log file.
Note:1055297.6 Subject: ORA-03113 ON STARTUP NOMOUNT
"If Oracle cannot write to the alert log for any reason, the instance will crash."
Ok maybe the note is dated but I have seen many instances where the db would not open or would crash with a change in permission to where oracle writes the alert log to or perhaps a change in mount point
steve
That is true if you are trying to start the instance and there is no space in the filesystem where the trace file is being written to.Quote:
Originally posted by stmontgo
hmmm oh really?
Note:1055297.6 Subject: ORA-03113 ON STARTUP NOMOUNT
"If Oracle cannot write to the alert log for any reason, the instance will crash."
Ok maybe the note is dated but I have seen many instances where the db would not open or would crash with a change in permission to where oracle writes the alert log to or perhaps a change in mount point
steve
But if an instance is running and space is full it will still keep running unless the next time it tries to write to the alert log file(I have seen this situation before but never faced a crash cause we increased the space). Though after reading that note, im not sure what will happen when it tries to write (Technically it should not crash, rather throw some other error message on screen when performing tasks that usually get logged in the alert log file).
Well i just did a simple test...
I renamed the bdump to bdumpold on running DB and tried creating tablespace, and was able to create it. Did log switch many times. Every thing is fine. I was even able to do a shutdown... though it did take a long time...
Though startup seems to be in a hanging state right now... will you know about it in the morning...
Well guess what it did get started.... :D
Adewri, if you renamed the bdump directory then your instance simply created a new alert_sid.log in default bdump destination. Check O_H\rdbms\trace (or whatever the default for your OS is) and you'll find it started to write into a new alert.log. And this doesn't happen only at startup, it happens during normal database operation if the initial bdump destination is no longer available or is full.
This also goes to prasadvd - check your OS's default bdump destination to see if oracle has created a new alert.log there.
That was the first thing i checked... but no could not find any trace file in O_H\rdbms\trace. No nothing. Had there been any trace file generated then shutdown and startup would not have taken sooo long time. Shutdown took about 5 Mins and startup around 7 Mins. When i rename bdumpold to bdump, shutdown took less than 10 secs and startup less than 30 secs....Quote:
Originally posted by jmodic
Adewri, if you renamed the bdump directory then your instance simply created a new alert_sid.log in default bdump destination. Check O_H\rdbms\trace (or whatever the default for your OS is) and you'll find it started to write into a new alert.log. And this doesn't happen only at startup, it happens during normal database operation if the initial bdump destination is no longer available or is full.
This also goes to prasadvd - check your OS's default bdump destination to see if oracle has created a new alert.log there.
BTW OS is windows XP prof. Oracle9i 9.2.0.3.0
Hm, that's interesting - I tested on W2K, Oracle9i 9.0.1.?.? and after renaming the initial bdump directory (during normal database operations) it created new alert file in rdbms\admin\trace.Quote:
Originally posted by adewri
BTW OS is windows XP prof. Oracle9i 9.2.0.3.0
Yep thats more confusing.. as i was trying it on unix after renaming.Quote:
Originally posted by jmodic
Hm, that's interesting - I tested on W2K, Oracle9i 9.0.1.?.? and after renaming the initial bdump directory (during normal database operations) it created new alert file in rdbms\admin\trace.
see what i got...
Where as on windows i was able to startup the instance... even after renaming the bdump file and no trace ...Code:
oracle():/oracle9i/app/product/admin/...> mv bdump/ bdumpold
oracle():/oracle9i/app/product/admin/...> c
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Nov 4 12:59:20 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
idle> startup
ORA-00449: background process 'CKPT' unexpectedly terminated with error 7446
ORA-07446: sdnfy: bad value '' for parameter .
idle> exit
Disconnected
oracle():/oracle9i/...> ls
bdumpold cdump create pfile udump
oracle():/oracle9i/...> mv bdumpold/ bdump
oracle():/oracle9i/...> c
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Nov 4 13:00:26 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 471559784 bytes
Fixed Size 736872 bytes
Variable Size 436207616 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes
shutdown immediate
Database mounted.
Database opened.
idle> Database closed.
Database dismounted.
ORACLE instance shut down.
idle>
it crashes in linux
Code:
mv bdump bdumpold
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 4 11:51:40 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
sys@LNX920>alter system switch logfile;
System altered.
[oracle@rac1 lnx920]$ ll $ORACLE_HOME/rdbms/log
total 0
sys@LNX920> alter system switch logfile
alter system switch logfile
*
ERROR at line 1:
ORA-00449: background process 'LGWR' unexpectedly terminated with error 7446
ORA-07446: sdnfy: bad value '' for parameter .
see windows is better!! (oh god, I have finally lost it completely)Quote:
Originally posted by pando
it crashes in linux
Code:
mv bdump bdumpold
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 4 11:51:40 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
sys@LNX920>alter system switch logfile;
System altered.
[oracle@rac1 lnx920]$ ll $ORACLE_HOME/rdbms/log
total 0
sys@LNX920> alter system switch logfile
alter system switch logfile
*
ERROR at line 1:
ORA-00449: background process 'LGWR' unexpectedly terminated with error 7446
ORA-07446: sdnfy: bad value '' for parameter .
Hi guys! I've read al your thread from top to bottom. I was having the exact same annoyance. I'll tell a long story short about my problem:
Today I was called from our branch at Spain. They used to have a local DBA but there resource was to expensive, so whatta'heck... lets give the DBA to the Argentine DBA Team... (sounds like a bunch but it's just my boss and me).
It turns out the DB in question was(is) running over a Windows 2003. We are talking about a 9.2.0.1 Oracle Server.
The database holds a META4 application and that's about it. The thing is I was mailed today by an IT Manager from spain saying the they had to "fix" the database by restarting both: The Application Server (META4) and the Win2k3 that holds the Oracle. I got kindda pist off because those are the things that I believe, one never does... the last thing you wanna try as a workaround is a warm restart.
So, after a while measuring this and that (they also said it was kindda slow) I go RDP to the win2k3, to go take a look at the Alert_$SID.log.... Woops... where is it? --> select * from v$parameter where name like '%dump%'. Yeap there it is = background_dump_dest=D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORAM4B\BDUMP
Alertlog Last modified like 2 months ago... Darn!... How can that be! so I issued the switch logfile... no modification yet... I shutdown immediate (on maintenance window), startup again... nothing!!
I change the destination paths:
alter system set background_dump_dest='D:\' scope=both;
...
Nothing...
Okay... I tried a lot of things all mixed up... my bad!.
So the things I tried were:
-->File access and attributes(discovered that oracle sets the udump cdump bdump folders to Read-Only folders!!) Everything OK there
-->Changed destination folders!... Nothing happened
-->Changed static parameters for the PFILE ... Nothing happened
-->Many other stupid things as I was hopeless.
-->Last thing I try is set the database to startup from an SPFILE the same as the above mentioned.
-->Startup mount pfile='spfilainit.ora' (flat file containing "spfile='spfilepath.ora'" (noquotes)
-->Intance started alright... and then issued:
-->Alter system set background_dump_dest='D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORAM4B\BDUMP' scope=both;
System altered.
And guess what??
The SOAB appeared again!
so just in case I did another logfile switch, and for my blessing... this AlertSID.log was working just fine!
I hope this helps anyone... So what's with the alertlog and the SPFILE?
ThankUall!