Avatar of ora_pupil
ora_pupil
 asked on

sysdba is not working.... (urgent)

Hi gurus,

Need you guys help, this is very urgent...

Can't connect at unix Oracle 9i AIX 5.2


sqlplus '/as sysdba'

is not working, just hanging...

nothing is happening, no error or anything

only I have made one change, did

alter system archive log stop

bcz it was creating a lot of logs, and don't have a lot of space....

Need you guys help....urgent

thanks
Oracle Database

Avatar of undefined
Last Comment
MohanKNair

8/22/2022 - Mon
sathyagiri

Try setting oracle_sid and then try to connect

export ORACLE_SID = <YOUR SID>

sqlplus /nolog

conn / as sysdba
ora_pupil

ASKER
thanks and have tried everything and it is doing this:

pfr01l03:GDWP:/home/oraGDWP > sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Sun Sep 10 19:27:29 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.





that's it nothing happening, keep hanging
ora_pupil

ASKER
and when I press ctl C then this:

pfr01l03:GDWP:/home/oraGDWP > sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Sun Sep 10 19:27:29 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.





ERROR:
ORA-01075: you are currently logged on


pfr01l03:GDWP:/home/oraGDWP > 
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
sathyagiri

Try this instead

sqlplus /nolog

Then in sqlplus prompt

conn / as sysdba

See if this works.


Also check if there are any errors in your alert log files.

ora_pupil

ASKER
alert.log file last 100 lines

pfr01l03:GDWP:/app/oracle/admin/GDWP/bdump > tail -f alert_GDWP.log
Sun Sep 10 19:33:31 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_1818656.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 19:45:14 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_7397440.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
pfr01l03:GDWP:/app/oracle/admin/GDWP/bdump > tail -100 alert_GDWP.log
Sun Sep 10 14:04:23 2006
ARCH shutting down
Sun Sep 10 14:04:23 2006
ARCH shutting down
Sun Sep 10 14:04:23 2006
ARC0: Archival stopped
Sun Sep 10 14:04:23 2006
ARC1: Archival stopped
Sun Sep 10 14:04:23 2006
Errors in file /app/oracle/admin/GDWP/bdump/gdwp_arc1_3420412.trc:
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 4
Sun Sep 10 14:04:23 2006
Errors in file /app/oracle/admin/GDWP/bdump/gdwp_arc0_5242890.trc:
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 4
Sun Sep 10 14:07:47 2006
Thread 1 advanced to log sequence 106534
  Current log# 2 seq# 106534 mem# 0: /u73/oradata/GDWP/GDWP_redo_02a.log
  Current log# 2 seq# 106534 mem# 1: /u74/oradata/GDWP/GDWP_redo_02b.log
Thread 1 advanced to log sequence 106535
  Current log# 1 seq# 106535 mem# 0: /u73/oradata/GDWP/GDWP_redo_01a.log
  Current log# 1 seq# 106535 mem# 1: /u74/oradata/GDWP/GDWP_redo_01b.log
Sun Sep 10 14:08:05 2006
ORACLE Instance GDWP - Can not allocate log, archival required
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 106536
All online logs needed archiving
  Current log# 1 seq# 106535 mem# 0: /u73/oradata/GDWP/GDWP_redo_01a.log
  Current log# 1 seq# 106535 mem# 1: /u74/oradata/GDWP/GDWP_redo_01b.log
Sun Sep 10 15:02:47 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_7585996.trc:
ORA-01013: user requested cancel of current operation
Sun Sep 10 16:06:46 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_4198408.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 16:06:49 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_7602218.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 16:25:24 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_5525510.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 16:47:19 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_4108412.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 16:47:42 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_4751386.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 17:24:39 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_3260462.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 18:26:16 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_4526292.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 18:33:13 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_2425038.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 18:41:00 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_3260560.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 18:51:12 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_2863126.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 19:05:10 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_7037050.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 19:33:31 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_1818656.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Sun Sep 10 19:45:14 2006
Errors in file /app/oracle/admin/GDWP/udump/gdwp_ora_7397440.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
pfr01l03:GDWP:/app/oracle/admin/GDWP/bdump > 
techji

your database is hung. Because you database is in archive log mode and you stopped the archiver processes. All your redo logs are full and they want to be archived.

Is this production ?

At the current moment nobody will be able to do anything so go ahead and kill the database. Kill smon or pmon process. it is alright when you restart, automatic recovery will take care of any recovery the database will need.

> sqlplus "/ as sysdba"
> sqlplus > startup mount;
> sqlplus > alter database noarchivelog;
> sqlplus > alter database open;

This will clear the redo logs and the database will come back to normal.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
sathyagiri

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.