ORA-01033 Oracle initialisation or shutdown in progress Error

[thebull1]

Hi All.
Im running an Oracle Release 9.2.0.1.0 database on my PC. (Windows XP). It has been perfect for a long time, but now every time I try to log in I get the error "ORA-01033: ORACLE initialization or shutdown in progress", and I cannot log in.
Kindly assist. Situation getting desperate.

Thanks.

 

[KenCunningham]

Sounds like the system has hung for some reason. Can you issue an sqlplus "/ as sysdba" and shutdown immediate (or shutdown abort if immediate doesn't work)? Then try to restart.

 

[Thargy]

thebull1,

Oracle is doing what it's supposed to, and shutting down. However, this is obviously taking some considerable time.

If there are sessions connected, other than the one you used to issue the shutdown command, Oracle will wait until they are finished before shutting down. You should verify that all other sessions have finished, and if they haven't, close them down.

You can issue several types of shutdown command. In approximately increasing order of 'oomph' they are shutdown, shutdown transactional, shutdown immediate and shutdown abort.

I suspect that you have issued a simple shutdown command. Start another sql plus session as someone with sysdba privilege and try shutdown immediate. If that doesn't work, then shutdown abort will definitely stop the database dead in its tracks.

Be warned that shutdown abort effectively crashes the database, so you may need to recover your undo tablespace afterwards.

A slightly less nasty, but still potentially as bad as a shutdown abort, is to go to the windows services and stop the oracle database service. To get to them, click on start, programs, administrative tools, services. Select the relevant oracle service (you may have more than one database on your PC, so make sure you've got the right one) and stop it. This too will bring the database to a shuddering halt.

To help you identify the service, if your database is called 'mydb1' then you should see a service called 'OracleServicemydb1'.

I suspect that you're running Oracle on your own PC. If that's so, there's no great hassle in doing this. If it's controlled and administered by someone else, you may need to get them to do it, or grant you permissions and privileges to do so.

As an aside, on my home PC I have altered the oracle service from automatic to manual start. That way, if I just want to use the PC and not Oracle, I don't have an unwanted instance sitting there consuming resources. You might find this beneficial in a personal/home computing environment.

Regards

Tharg

Grinding away at things Oracular

 

[thebull1]

Thanks Guys!
But the error persists even after I shut down the database from the control panel/Admin Tools. And I dont have the admin, sys or sysdba password so I cant log into SQLPUS and alter the database. The person who created the database is long gone.
Anything else I can try?

 

[KenCunningham]

Have you tried stopping any oracle-related processes in task manager? Incidentally, Tharg, are you a Gary Larson fan?

 

[thebull1]

There is no Oralce Related task there. I ahve even restarted my PC, but to no avail.

 

[Thargy]

thebull1,

There is another way in. If you get your ID assigned to the administrators group on the server on which the database sits, you can log in as sysdba without a valid password, because the database will recognise your OS credentials.

You have to go in as sysdba, not an ordinary user, but this would give you access, and enough 'wellie' to order the database to shutdown.

I suspect the restart did you no good because the oracle service is hung and/or automatically restarting with the machine, and carrying on right where it left off. If you can get admin level access, try disabling the oracle service. This should prevent it from doing anything, and give you time to get in through sql plus, and make the database do what it's told.

Regards

Tharg

P.S. Ken, I do occasionally enjoy Mr Larson's efforts, and have been to the far side of reality on occasions.

Grinding away at things Oracular

 

[SantaMufasa]

Bull,

ORA-01033: ORACLE initialization or shutdown in progress

Actually, the more likely problem is that something has gone awry during Oracle's startup rather than during shutdown. I is probably helpful to go out to your Oracle alert log to see what message(s) are generating at the end of the log file. Your Oracle log file should be in the path that matches whatever is your "BACKGROUND_DUMP_DEST=<path>" speceification in your "init<SID>.ora" file. Look there for the alert<SID>.log file, go to its end, and see what the last several messages say.


If the messages seem imperceptible, post them here and we can help. (Since today is US Thanksgiving and I am in charge of preparing all the food for our 40 guests, I don't know how "Johnny on the spot" I'll be with responses, but, as you know, I'm not the only one out here willing to help.

"Thankfully",

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[thebull1]

Hi All....
I appreciate your continued assistance on this.
My SID is called SPMS2. I could not locate the initspms2.ora file. I have however found the alert_spms2 file and I have attached the last few lines that were appended after today's attempted logon.....

Dump file f:\oracle\admin\spms2\bdump\alert_spms2.log
Fri Nov 25 08:23:09 2005
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Fri Nov 25 08:23:09 2005
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 50331648
large_pool_size = 8388608
java_pool_size = 33554432
control_files = f:\oracle\oradata\spms2\CONTROL01.CTL, f:\oracle\oradata\spms2\CONTROL02.CTL, f:\oracle\oradata\spms2\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 25165824
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = spms2
dispatchers = (PROTOCOL=TCP) (SERVICE=spms2XDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = f:\oracle\admin\spms2\bdump
user_dump_dest = f:\oracle\admin\spms2\udump
core_dump_dest = f:\oracle\admin\spms2\cdump
sort_area_size = 524288
db_name = spms2
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Fri Nov 25 08:23:13 2005
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Nov 25 08:23:14 2005
alter database mount exclusive
Fri Nov 25 08:23:19 2005
Successful mount of redo thread 1, with mount id 1101226226.
Fri Nov 25 08:23:19 2005
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Fri Nov 25 08:23:19 2005
alter database open
ORA-1113 signalled during: alter database open...

 

[KenCunningham]

Looks like a recovery problem. Take a look at:

http://www.cs.umb.edu/cs634/ora9idocs/server.920/a96572/troublereco.htm

Oh, and a belated Happy Thanksgiving to all!

 

[SantaMufasa]

Yes, Bull, Ken is correct...a file that is part of your database has become invalid. The first steps you can try are (from a Windows command prompt):

[o/s prompt]> sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown abort
SQL> startup mount
SQL> recover database
(then if the response is "Recovery complete.", then you can issue the command...)
SQL> alter database open;

...and Oracle should be successfully up and running. If, however, you receive some unsuccessful response to your "recover database" command, then you are in a little deeper hot water. To recover from a more drastic media failure, your database must be in "ARCHIVELOG mode". To determine if this is the case for your database, following your "startup mount" command, you can issue the command (at the SQL> prompt):

SQL> archive log list

This command lists the status of your archive logging. If the response is "No Archive Mode", then you cannot recover your database...the best you can hope for is to restore your database from a previous cold backup. If you have not ever done a cold backup of your database (in which your database is properly shutdown and you have copied, using an o/s copy command of all of your database datafiles, including control files, on-line redo log files, and database data files), then you are S.O.L (Surely Out of Luck) and you must re-create your database from scratch.

But if your database is in "Archive Log" mode, then with your database shutdown, you restore (from a previous full backup of your database) a copy of the file that is corrupted. Then you follow the same steps that I listed, above.

Hopefully, you will have success following the "recover database" steps, above. If you are successful, then this can be one of the things for which you are thankful during this holiday season. If you are not successful, then you still can be thankful that you learned an important lesson about backing up your database on just a simple, personal "sandbox" database and not a corporate, fiduciary database.

Let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[thebull1]

Thanks Mufasa..
Your instructions are very clear and go on very well until I get to
connect / as sysdba

This gives me the following error:-

C:\Documents and Settings\UFA01118>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 25 18:30:42 2005

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

SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges


How do I proceed?

 

[SantaMufasa]

That message means that your PC login is apparently not a member of your PC's user group named ORA_DBA. To add your login to that group, connect as a member of the Administrator group, then (on Windows 2000 for example) click Start..Settings..Control Panel..Users and Passwords..Advanced..Advanced..Groups..<right click>ORA_DBA..Add to Group..(Choose your login)..Add.

If you are already a member of the ORA_DBA group, then check the contents of your "sqlnet.ora" file located in your %ORACLE_HOME%\network\adming directory. Ensure that an entry in that file reads:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Once you have confirmed those two items, try again and let us know the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[thebull1]

I have finally succeeded in logging into the database after including myself into the ORA_DBA group! THANKS! Making progress.
These are the commands that I have executed as instructed by SantaMusafa.
The following has now transpired :- .............

C:\Documents and Settings\UFA01118>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Nov 26 11:07:05 2005

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

SQL> connect / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 5736 change 1220902254 time 11/10/2005
12:15:27
ORA-00312: online log 2 thread 1: 'F:\ORACLE\ORADATA\SPMS2\REDO02.LOG'


SQL>

Kindly advise on how to proceed from here....

 

[KenCunningham]

Hi. Try alter database open resetlogs replacing the recover database above.

 

[thebull1]

Hi All..
I have substituted the recover database and now Iget the following message:-
SQL> connect / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\SPMS2\SYSTEM01.DBF'

NOTE: I have also tried a recovery until cancel with the following results:-

SQL> recover until cancel
ORA-00279: change 1220894693 generated at 11/12/2005 13:28:43 needed for thread
1
ORA-00289: suggestion : F:\ORACLE\ORA92\RDBMS\ARC00279.001
ORA-00280: change 1220894693 for thread 1 is in sequence #279


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log 'F:\ORACLE\ORA92\RDBMS\ARC00279.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\SPMS2\SYSTEM01.DBF'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\SPMS2\SYSTEM01.DBF'


SQL>

Kindly note that the referred file ORA-00308: cannot open archived log 'F:\ORACLE\ORA92\RDBMS\ARC00279.001' is NOT even existing in that location on my PC.

Please help.

 

[KenCunningham]

OK. Try mounting the the database and issue an alter database backup control file to <filename.ctl>, then shut down the database and copy this newly created control file to overwrite your original one(s) (it is a good idea to rename these first perhaps).

Then try restarting the database with the resetlogs option as above.

 

[thebull1]

Hi All?
Did I miss soething from Ken's instruction? This command is returning a syntax error...

SQL> connect / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database backup control file to 'F:\oracle\oradata\spms2\CONTROL01.CT
L';
alter database backup control file to 'F:\oracle\oradata\spms2\CONTROL01.CTL'
*
ERROR at line 1:
ORA-00905: missing keyword


SQL> alter database backup control file to F:\oracle\oradata\spms2\CONTROL01.CTL
;
alter database backup control file to F:\oracle\oradata\spms2\CONTROL01.CTL
*
ERROR at line 1:
ORA-00905: missing keyword


Kindly assist.

 

[hoinz]

it should be:
alter database backup controlfile to ...
(no space in between)

 

[KenCunningham]

Sorry - my bad. Mondayitis to blame methinks.