RE: How to log into a 9.2.0 database that has a hung archiver

well... I'm not arguing that they're poorly or well written.  

the problem is that with this one 9.2.0 instance, occasionally the start arch command will fail, the redo logs fill, and the system hangs.  

my question -- regardless of how poorly written the scripts are -- is how do I log into a hung 9.2.0 database to reenable archiving when a) I don't have svrmgrl or connect internal and b) all the combinations of sqlplus /nolog, sqlplus 'sys as sysdba', and sqlplus '/ as sysdba' do not work?  

What is the correct way to log into a hung database?      

Shannon St. Dennis
Database Administrator
City of Regina
(306) 777-7415 (phone)
(306) 777-6804 (fax)

sstdenni_at_cityregina.com  

The degree of normality in a database is inversely proportional to that of its DBA

>>> mladen_at_wangtrading.com 08/28/03 05:04pm >>>

Lemme guess: you're using home brewed shell scripts, not a product that can do it by itself, automagically, like NetBackup, Networker or SyncSort Backup? Well, your problem is that your scripts are badly written. If you're on a Unix box, I'll rewrite them for half what you would pay for Tivoli ior OmniBackup.      

--
Mladen Gogala
Oracle DBA 


-----Original Message-----
Shannon St. Dennis
Sent: Thursday, August 28, 2003 6:35 PM
To: Multiple recipients of list ORACLE-L


not exactly.
 
we have a process here that runs every 15 minutes or so, turns
archiving off on each system, compress/moves the archive logs, then
turns archiving back on
 
On this system, the archive enable seems to fail.  and the database
hung because it couldn't automatically archive the logs.
 
if this happens on an 8x or 7x system, I would just svrmgrl -- connect
internal -- archive logs all -- (to free the logs) -- then reenable ayto
archiving
 
but on this 9.2 system, svrmgrl does not work
and neither does connecting as '/ as sysdba', or 'sys as sysdba', or
'system as sysdba'
 
what is the correct method to connect into the system when it is hung
for some reason?
 
S
 
 
Shannon St. Dennis
Database Administrator
City of Regina


(306) 777-7415 (phone)

(306) 777-6804 (fax)

sstdenni_at_cityregina.com 
 
The degree of normality in a database is inversely proportional to that
of its DBA

>>> DWILLIAMS_at_LIFETOUCH.COM 08/28/03 04:04pm >>>
Shannon - Help me with the situation. Is this like when the archive
disk is
full and Oracle politely waits until you make more space? For that
situation
I've always just moved some archive logs somewhere else and the
database
immediately takes off. Is that your situation, or have I missed
something
here?



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
dwilliams_at_lifetouch.com 

-----Original Message-----
Sent: Thursday, August 28, 2003 4:24 PM
To: Multiple recipients of list ORACLE-L


sorry
after checking logs again, the connect / as sysdba command and sqlplus
'/ as
sysdba' hung after issuing the command.  a password was never
requested.

thanks
shannon
=======================================

Situation:

Oracle EE 9.2.0 database on AIX v5.1

tail alert_TOOLS.log:

Thu Aug 28 13:46:31 2003
ARC0: Evaluating archive   log 1 thread 1 sequence 69800
ARC0: Beginning to archive log 1 thread 1 sequence 69800
Creating archive destination LOG_ARCHIVE_DEST_1:
'/u00/oracle/archives/tools_698
00.dbf'
ARC0: Completed archiving  log 1 thread 1 sequence 69800
Thu Aug 28 13:46:33 2003
Archive Log Stop
Archiving is disabled
Shutting down archive processes
Archiving is disabled
Thu Aug 28 13:46:34 2003
ARCH shutting down
Thu Aug 28 13:46:34 2003
ARC0: Archival stopped
Thu Aug 28 13:46:34 2003
ARCH shutting down
Thu Aug 28 13:46:34 2003
ARC1: Archival stopped
Thu Aug 28 13:46:39 2003
Thread 1 advanced to log sequence 69802
  Current log# 2 seq# 69802 mem# 0: /u09/oracle/TOOLS/redo02.log
Thread 1 advanced to log sequence 69803
  Current log# 1 seq# 69803 mem# 0: /u09/oracle/TOOLS/redo01.log
Thu Aug 28 13:46:48 2003
ORACLE Instance TOOLS - Can not allocate log, archival required
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 69804
All online logs needed archiving
  Current log# 1 seq# 69803 mem# 0: /u09/oracle/TOOLS/redo01.log

so the archive hung.

on our older 7x and 8x systems, I would do a svrmgrl and connect
internal
then I would do an archive log all to archive the logs

on this 9.2.0 system, I tried sqlplus 'sys as sysdba' --- it hung
after
asking for the password
ok... tried sqlplus /nolog   --- then connect sys as sysdba -- hung
after
asking for password
ok... tried sqlplus '/ as sysdba' -- hung after asking for password
ok... tried sqlplus /nolog -- then connect / as sysdba -- hung after
asking
for password

Killed smon to cause database to crash.
sqlplus 'sys as sysdba' -- asked for password -- got the sql prompt -
startup

everything worked.

I'm glad this is a test database so I could kill it and restart
but what is the correct method to get a sql prompt on a hung database
when
svrmgrl and connect internal aren't supported?

Thanks
Shannon

Shannon St. Dennis
Database Administrator
City of Regina


(306) 777-7415 (phone)

(306) 777-6804 (fax)

sstdenni_at_cityregina.com <mailto:sstdenni_at_cityregina.com>  

The degree of normality in a database is inversely proportional to that
of
its DBA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

 
Note:
This message is for the named person's use only.  It may contain
confidential, proprietary or legally privileged information.  No
confidentiality or privilege is waived or lost by any mistransmission. 
If you receive this message in error, please immediately delete it and
all copies of it from your system, destroy any hard copies of it and
notify the sender.  You must not, directly or indirectly, use, disclose,
distribute, print, or copy any part of this message if you are not the
intended recipient. Wang Trading LLC and any of its subsidiaries each
reserve the right to monitor all e-mail communications through its
networks.  Any views expressed in this message are those of the
individual sender, except where the message states otherwise and the
sender is authorized to state them to be the views of any such entity.
 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Shannon St. Dennis
  INET: SSTDENNI_at_regina.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).