Solved

Oracle Error: ORA-01033

Posted on 2001-06-12
12
5,189 Views
Last Modified: 2012-05-05

Hi,

Here's my problem.  I was executing a delete query and somehow I got a message saying that "ORACLE Terminated".  When I tried to connect to the database again it gave me an error saying that "Oracle initialization or shutdown in progress(ORA-01033)".  I tried restarting my NT box and I am still not able to connect.

Any sugestions would be helpful.

Oracel Version 8.1.5 on NT 4.0

Thanks,
Vuthy
0
Comment
Question by:vpen
12 Comments
 
LVL 5

Expert Comment

by:ORACLEtune
Comment Utility
hi,

1. check your [inst]alert.log file

2. wait a few minutes, sounds like you are in a mult-user environment and someone did a reboot, power-outage, etc.

see ya
Eric
0
 

Expert Comment

by:mohit_bhatnagar
Comment Utility
analyse your alertlog as suggested by ORACLEtune ...
if do not found any problem ....

again try to run this , if same problem , give us your delete query u r running and alert log portion .
0
 
LVL 1

Expert Comment

by:sunrock_in
Comment Utility
TRY RESTARTING THE INSTANCE MANUALLY
GO TO COMMAND PROMPT
>SVRMGRL
>CONNECT INTERNAL/<PASSWORD>
STARTUP PFILE=<PATH OF INIT<SID>.ORA
POST THE MESSAGE IF IT IS STILL IN MOUNT STAGE.
0
 

Expert Comment

by:hani430
Comment Utility
If you are connectet as sysdba you can try to make a "Shutdown abort" and restart the database.
If the database doesn`t shutdown completly, you can stop the service for the database (ORACLESERVICE<sid>) and restart it. If then the database doesn`t open automatically you can start it, if you are internal or sysdba, with the command startup pfile=<Path of init<sid>.ora.

Error messages can be found in the alert.logs or in .trc-files
0
 

Author Comment

by:vpen
Comment Utility
hani430,

I still can't connect to the database, even through the SVRMGR. It still gave me that same error "Oracle initialization or shutdown in progress(ORA-01033)".  Is there anyway to force a shutdown in ORACLE.  I tried to do it through SVRMGR but it won't let me because i'm not connected to the database.  WHich I tried to connect to but can't.

Thanks,
Vuthy
0
 

Author Comment

by:vpen
Comment Utility
hani430,

I still can't connect to the database, even through the SVRMGR. It still gave me that same error "Oracle initialization or shutdown in progress(ORA-01033)".  Is there anyway to force a shutdown in ORACLE.  I tried to do it through SVRMGR but it won't let me because i'm not connected to the database.  WHich I tried to connect to but can't.

Thanks,
Vuthy
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:PerryWhite
Comment Utility
I had this happen once. Login as svrmgr and do a shutdown abort.  Seems something is hung in limbo. Then try to startup the database again. Check your logs as mentioned by others.
pw.
0
 
LVL 1

Expert Comment

by:sunrock_in
Comment Utility
It seems your database files are not in place or got currupted have you tried take up any kind of backup before this . Check ur alert log file if thier any message for missing or currupted file.Post the alert log file
path <Oracle_home>/<SID>/Bdump/<sid>alert.log
Thanks
0
 

Author Comment

by:vpen
Comment Utility
sunrock_in,

here's the logfile from when the error occurred.  I am not seeing anything out o fthe ordinary.

Thanks...

Wed Jun 13 00:03:16 2001
alter database  mount
Wed Jun 13 00:03:22 2001
Successful mount of redo thread 1, with mount id 958166218.
Wed Jun 13 00:03:22 2001
Database mounted in Exclusive Mode.
Completed: alter database  mount
Wed Jun 13 00:03:22 2001
alter database open
ORA-1113 signalled during: alter database open ...
Shutting down instance (immediate)
License high water mark = 1
archiving is disabled
Dump file d:\orant\admin\pdsd\bdump\pdsdALRT.LOG
Wed Jun 13 08:21:06 2001
ORACLE V8.1.5.0.0 - Production vsnsta=0
vsnsql=d vsnxtr=3
Windows NT V4.0, OS V5.101, CPU type 586
Starting up ORACLE RDBMS Version: 8.1.5.0.0.
System parameters with non-default values:
  processes                = 50
  shared_pool_size         = 4194304
  control_files            = d:\orant\oradata\pdsd\control01.ctl, d:\orant\oradata\pdsd\control02.ctl
  db_block_buffers         = 2048
  db_block_size            = 8192
  compatible               = 8.0.5
  log_buffer               = 32768
  log_checkpoint_interval  = 10000
  log_checkpoint_timeout   = 1800
  db_files                 = 1024
  db_file_multiblock_read_count= 8
  remote_login_passwordfile= EXCLUSIVE
  global_names             = TRUE
  distributed_transactions = 10
  service_names            = pdsd
  instance_name            = pdsd
  sort_area_size           = 66560
  db_name                  = pdsd
  os_authent_prefix        =
  parallel_max_servers     = 5
  background_dump_dest     = d:\orant\admin\pdsd\bdump
  user_dump_dest           = d:\orant\admin\pdsd\udump
  max_dump_file_size       = 10240
  oracle_trace_collection_name=
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
Wed Jun 13 08:21:09 2001
alter database mount exclusive
Wed Jun 13 08:21:15 2001
Successful mount of redo thread 1, with mount id 958233466.
Wed Jun 13 08:21:15 2001
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Wed Jun 13 08:21:15 2001
alter database open
ORA-1113 signalled during: alter database open...
Wed Jun 13 13:09:11 2001
Shutting down instance (immediate)
License high water mark = 1
archiving is disabled
Dump file d:\orant\admin\pdsd\bdump\pdsdALRT.LOG
Wed Jun 13 13:16:56 2001
ORACLE V8.1.5.0.0 - Production vsnsta=0
vsnsql=d vsnxtr=3
Windows NT V4.0, OS V5.101, CPU type 586
Starting up ORACLE RDBMS Version: 8.1.5.0.0.
System parameters with non-default values:
  processes                = 50
  shared_pool_size         = 4194304
  control_files            = d:\orant\oradata\pdsd\control01.ctl, d:\orant\oradata\pdsd\control02.ctl
  db_block_buffers         = 2048
  db_block_size            = 8192
  compatible               = 8.0.5
  log_buffer               = 32768
  log_checkpoint_interval  = 10000
  log_checkpoint_timeout   = 1800
  db_files                 = 1024
  db_file_multiblock_read_count= 8
  remote_login_passwordfile= EXCLUSIVE
  global_names             = TRUE
  distributed_transactions = 10
  service_names            = pdsd
  instance_name            = pdsd
  sort_area_size           = 66560
  db_name                  = pdsd
  os_authent_prefix        =
  parallel_max_servers     = 5
  background_dump_dest     = d:\orant\admin\pdsd\bdump
  user_dump_dest           = d:\orant\admin\pdsd\udump
  max_dump_file_size       = 10240
  oracle_trace_collection_name=
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
Wed Jun 13 13:16:59 2001
alter database mount exclusive
Wed Jun 13 13:17:04 2001
Successful mount of redo thread 1, with mount id 958194896.
Wed Jun 13 13:17:04 2001
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Wed Jun 13 13:17:04 2001
alter database open
ORA-1113 signalled during: alter database open...
Wed Jun 13 14:13:55 2001
Errors in file .\ORA00273.TRC:

Shutting down instance (immediate)
License high water mark = 1
Wed Jun 13 23:31:11 2001
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Wed Jun 13 23:31:11 2001
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
archiving is disabled
Starting up ORACLE RDBMS Version: 8.1.5.0.0.
System parameters with non-default values:
  processes                = 50
  shared_pool_size         = 4194304
  control_files            = d:\orant\oradata\pdsd\control01.ctl, d:\orant\oradata\pdsd\control02.ctl
  db_block_buffers         = 2048
  db_block_size            = 8192
  compatible               = 8.0.5
  log_buffer               = 32768
  log_checkpoint_interval  = 10000
  log_checkpoint_timeout   = 1800
  db_files                 = 1024
  db_file_multiblock_read_count= 8
  remote_login_passwordfile= EXCLUSIVE
  global_names             = TRUE
  distributed_transactions = 10
  service_names            = pdsd
  instance_name            = pdsd
  sort_area_size           = 66560
  db_name                  = pdsd
  os_authent_prefix        =
  parallel_max_servers     = 5
  background_dump_dest     = d:\orant\admin\pdsd\bdump
  user_dump_dest           = d:\orant\admin\pdsd\udump
  max_dump_file_size       = 10240
  oracle_trace_collection_name=
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
Thu Jun 14 00:03:34 2001
alter database  mount
Thu Jun 14 00:03:41 2001
Successful mount of redo thread 1, with mount id 958250332.
Thu Jun 14 00:03:41 2001
Database mounted in Exclusive Mode.
Completed: alter database  mount
Thu Jun 14 00:03:41 2001
alter database open
ORA-1113 signalled during: alter database open ...
0
 
LVL 1

Expert Comment

by:sunrock_in
Comment Utility
Your Datafiles are not in sync anyway check this out
An ORA-1113 will be issued whenever a datafile is not in sync with the rest of  
the database:
 
01113, 00000, "file %s needs media recovery"
// *Cause:  An attempt was made to online or open a database with a file that
//         is in need of media recovery.
// *Action: First apply media recovery to the file.
 
Often, ORA-1113 occurs together with ORA-1110. The most common scenarios for  
an ORA-1113 are:
 
1. AT STARTUP TIME (usually followed by ORA-1110)
 
- The database crashed or was shut down abort or the machine was rebooted  
while the datafile's tablespace was in hot backup mode. At startup,  
you get ORA-1113.    
       
- You attempt to open the database with an old version of a datafile that was  
restored from a backup without first bringing it up-to-date.  
 
2. TRYING TO ONLINE A DATAFILE
 
You try to bring an offline datafile back online and get ORA-1113.  
 
 
Problem Explanation:
====================
 
Oracle's architecture is tightly coupled in the sense that all database files  
-- datafiles, redolog files, and controlfile -- must be in sync when the  
database is opened or at the end of a checkpoint.  This implies that the  
checkpoint SCN (System Commit Number) of all datafiles must be the same.  If  
that is not the case for a particular datafile, an ORA-1113 will be generated.  
For example, when you put a tablespace in hot backup mode, the checkpoint  
SCN of all its datafiles is frozen at the current value until you issue the  
corresponding end backup.  If the database crashes during a hot backup and you
try to restart it without doing recovery, you will likely get ORA-1113 for at  
least one of the datafiles in the tablespace that was being backed up, since  
its SCN will probably be lower than that of the controlfile and the datafiles
in other tablespaces.  Likewise, offlining a datafile causes its checkpoint
SCN to freeze.  If you simply attempt to online the file without recovering it
first, its SCN will likely be much older than that of the online datafiles,
and thus an ORA-1113 will result.

Solution: PERFORM MEDIA RECOVERY ON THE DATAFILE(S)


Solution Description:
=====================
 
The solution for an ORA-1113 is to perform media recovery on the file(s)
having problems using the RECOVER DATAFILE command.  If you know that most or
all of the files in a tablespace need to be recovered and the database is
open, use RECOVER TABLESPACE. If a number of tablespaces need recovery, use
RECOVER DATABASE with the database mounted.
 
The way to do that varies a little according to the scenario.
 
 
Solution Explanation:
=====================
 
Start by querying V$LOG and V$LOGFILE.  If the database is down, you need to
mount it first.   Then connect internal in SQL*DBA or Server Manager and issue
the query:
 
      SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#  
      FROM V$LOG V1, V$LOGFILE V2
      WHERE V1.GROUP# = V2.GROUP# ;  
 
This will list all your online redolog files and their respective sequence and
first change numbers.
 
The steps to take next depend on the scenario in which the ORA-1113 was
issued:
 
 
I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
----------------------------------------------------------
 
A. WITH ORACLE 7.1 OR LOWER
 
1. Mount the database.  
 
2. Apply media recovery to the database.
 
      RECOVER DATABASE
 
3. Confirm each of the archived logs that you are prompted for until you
receive the message "Media recovery complete".  If you are prompted for an
archived log that does not exist, Oracle probably needs one or more of the
online logs to proceed with the recovery.  Compare the sequence number
referenced in the ORA-280 message with the sequence numbers of your online
logs.  Then enter the full path name of one of the members of the redo group
whose sequence number matches the one you are being asked for.  Keep entering
online logs as requested until you receive the message "Media recovery
complete".
 
4. Open the database.
 
 
B. WITH ORACLE 7.2 OR HIGHER
 
1. Mount the database.
 
2. Find out which datafiles were in hot backup mode when the database crashed
or was shutdown abort or the machine was rebooted by running the query:
 
      SELECT V1.FILE#, NAME
      FROM V$BACKUP V1, V$DATAFILE V2
      WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE# ;
   
3. For each of the files returned by the above query, issue the command:
 
      ALTER DATABASE DATAFILE '<full path name>' END BACKUP;
 
4. Open the database.
 
 
II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP  
---------------------------------------------------------------------
 
A. WITH THE DATABASE IN ARCHIVELOG MODE
 
1. Mount the database.
 
2. Recover the datafile:
 
      RECOVER DATAFILE '<full path name>'
 
   If recovering more than one datafile, issue a  
 
      RECOVER DATABASE
 
3. Confirm each of the archived logs that you are prompted for until you  
receive the message "Media recovery complete".  If you are prompted for an  
archived log that does not exist, Oracle probably needs one or more of the  
online logs to proceed with the recovery.  Compare the sequence number  
referenced in the ORA-280 message with the sequence numbers of your online  
logs.  Then enter the full path name of one of the members of the redo group  
whose sequence number matches the one you are being asked for.  Keep entering  
online logs as requested until you receive the message "Media recovery  
complete".
 
4. Open the database.
 
 
B. WITH THE DATABASE IN NOARCHIVELOG MODE
 
In this case, you will only succeed in recovering the datafile or tablespace
if the redo to be applied to it is within the range of your online logs. Issue
the query:
 
      SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
 
Compare the change number you obtain with the FIRST_CHANGE# of your online
logs.  
 
If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs, the
datafile can be recovered.  In this case, the procedure to be followed is
analogous to that of scenario II.A above, except that you must always enter
the appropriate online log when prompted, until recovery is finished.
 
If the CHANGE# is LESSER than the minimum FIRST_CHANGE# of your logs, the file
cannot be recovered.  Your options at this point include:
 
- If the datafile is in a temporary or index tablespace, you may drop it with
an ALTER DATABASE DATAFILE '<full path name>' OFFLINE DROP statement and then
open the database.  Once the database is up, you must drop the tablespace to
which the datafile belongs and recreate it.  
 
- If the datafile is in the SYSTEM or in a rollback tablespace, restore an
up-to-date copy of the datafile (if available) or your most recent full
backup.  If a full, consistent backup is not available, please contact Oracle
Customer Support.
 
- For all other cases in this scenario, you must weigh the cost of going to a
backup versus the cost of recreating the tablespace involved, as described in
the two previous cases.  For more details or to assist you in your decision,
please contact Oracle Customer Support.  
 
 
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
----------------------------------------------
 
1. Recover the datafile:
 
      RECOVER DATAFILE '<full path name>'
 
   If recovering a tablespace, do
 
      RECOVER TABLESPACE <tablespace>
 
2. Confirm each of the archived logs that you are prompted for until you  
receive the message "Media recovery complete".  If you are prompted for an  
archived log that does not exist, Oracle probably needs one or more of the  
online logs to proceed with the recovery.  Compare the sequence number  
referenced in the ORA-280 message with the sequence numbers of your online  
logs.  Then enter the full path name of one of the members of the redo group  
whose sequence number matches the one you are being asked for.  Keep entering  
online logs as requested until you receive the message "Media recovery  
complete".

thanks
sunrock
 

0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
Hi vpen,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. If there is no objection or further activity, I will suggest to:

    Refund points and save as a 0-pt PAQ.

EXPERTS: Post a comment if you think somebody deserves credit here!

Please do not accept this comment as an answer!
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 6

Accepted Solution

by:
Mindphaser earned 0 total points
Comment Utility
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Add 0 to end of Number 21 70
Bulk insert into global temporary table 2 40
Pl/SQL Query 31 61
File generation using utl_file 4 28
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now