Link to home
Create AccountLog in
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
Avatar of sathyagiri
sathyagiri
Flag of United States of America image

Try setting oracle_sid and then try to connect

export ORACLE_SID = <YOUR SID>

sqlplus /nolog

conn / as sysdba
Avatar of ora_pupil
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
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 > 
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.

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 > 
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.
ASKER CERTIFIED SOLUTION
Avatar of sathyagiri
sathyagiri
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account