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

[Shannon St. Dennis]

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) [EMAIL PROTECTED]   The degree of normality in a database is inversely proportional to that of its DBA >>> [EMAIL PROTECTED] 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----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shannon St. Dennis Sent: Thursday, August 28, 2003 6:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to log into a 9.2.0 database that has a hung archiver 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) [EMAIL PROTECTED]   The degree of normality in a database is inversely proportional to that of its DBA >>> [EMAIL PROTECTED] 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. [EMAIL PROTECTED] -----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) [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>  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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.