Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to log into a 9.2.0 database that has a hung archiver

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

From: <Jared.Still_at_radisys.com>
Date: Thu, 28 Aug 2003 16:04:26 -0800
Message-ID: <F001.005CDD0F.20030828160426@fatcity.com>


Well, if you're login to Oracle via "sqlplus '/ as sysdba'" hangs, there really is no way for you to login to the database until the problem is corrected.

What happens when you try to login as a user? ie. sqlplus system/manager

Error? Or does it hang?

Normally I would say use truss or strace to see what's going on, but since you're on the user unfriendly AIX, you can't do that. Not unless you buy an additional tool to do that for you.

Also, have the backed up archive logs been removed from your filesystem?

If that drive is full, you can't login.

There is another way to get going again if the drive is full.

If you have set LOG_ARCHIVE_DEST_1 to locate the archive logs, you can use 'ALTER SYSTEM' to direct the archive logs to another location temporarily.

I've had to do that a couple times in the past when the archive log backups
didn't work, and I didn't keep close watch on the file system.

Also, why are you taking the databases out of archive mode to do the archive
log backups? Any changes to the database made during that time will be unrecoverable if the logs cycle through all members during the backup in worst
case.

There's no reason to turn off archive logging while backing up the archive logs.

Jared

"Shannon St. Dennis" <SSTDENNI_at_regina.ca> Sent by: ml-errors_at_fatcity.com
 08/28/2003 04:19 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        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-----
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: INET: Jared.Still_at_radisys.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).
Received on Thu Aug 28 2003 - 19:04:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US