Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2012
    Posts
    7

    Unanswered: ORA-01033 Error cannot be traced in the alert log or v$views

    Hello There,
    I'm hoping you can shed some light on what seems a rather odd occurrence on our Production Oracle Instance.
    Before i elaborate on the nature of the problem, I must confess that i am not an Oracle DBA and have been compelled to post this query since i have no joy from the in house DBA community on the origins of this error.

    We've had an ORA-01033 error being issued by our ETL installation (deployed on a Linux machine) a couple of days ago whence trying to extract some data from the Oracle instance (during overnight DataWarehouse loads) which has consequently aborted the loads necessitating a cleanup.
    This event has already occurred twice before, the first time being a month ago when the event was captured in the v$instance (startup time) and also in the V$Dataguard_status.
    Since then, This error has been generated twice although there seems to be no evidence of this in either the Oracle alert log(as confirmed by DBA team) or in any of the v$views (as pointed out by Tom in an earlier post) such as DBA_LOGSTDBY_HISTORY, v$logstdby_state, $logstdby_stats, dba_logstdby_log, dba_logstdby_events,v$dataguard_status, v$dataguard_stats, v$dataguard_config,v$database,v$database_incarnati on, v$managed_standby, v$standby_log, v$instance.

    The DBA team have pretty much disowned this issue since they claim to not have any actual evidence of this from the logs and this is the crux of the matter here.
    The Problem i have as the downstream "recepient" of this error is to prove to the DBA team that this is indeed a genuine issue with the Oracle instance affecting it's availability and concomittantly affecting the DW loads.

    FYI, The Oracle instance is in Failover mode, so it's swiftly back online after bombing out for a few seconds.
    I don't have access to the alert log as it's a Production environment and employs restricted access policy to the server.

    Where else is the ORA-01033 error logged in the dictionary besides the Alert log? Where else can i find evidence of this?

    Thoughts??

    Regards

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    01033, 00000, "ORACLE initialization or shutdown in progress"
    // *Cause: An attempt was made to log on while Oracle is being started up
    //         or shutdown.
    // *Action: Wait a few minutes. Then retry the operation.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2012
    Posts
    7
    thanks but my query is a bit more convoluted than the error resolution in the Oracle manual.
    i am not asking why this has happenned, rather, where can i trace/track this error besides the alert log?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since user never gets connected to the DB, the DB can NOT record this occurrence.
    If application is correctly instrumented, this error gets recorded in application log file.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2012
    Posts
    7
    thanks, can you provide the location for the application log file? i presume this is different to the alert log?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by shareeman View Post
    thanks, can you provide the location for the application log file? i presume this is different to the alert log?
    >We've had an ORA-01033 error being issued by our ETL installation
    Consider actually talking to the ETL support staff to for assistance regarding YOUR application!

    We don't have any idea what software is being (ab)used.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2012
    Posts
    7
    I appreciate that you are trying to help but i reckon you ought to read the original query again. This is an Oracle error being generated by the instance and i have no trace of this from my end (ETL) and apparently the Oracle database end too. The latter issue is the reason for this post.
    To answer your specific query, I am the ETL administrator so am best placed to track this from an application perspective.
    Nevertheless, Thank you for your time.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The error indicates that the server is shutdown or crashed while the user was trying to connect. The error will NOT be logged unless the application captured the error and logged it, however the shutdown/crash that caused it will be in the ALERT log file.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ask the DBA for complete excerpt for 15 minutes before error until 15 minutes after error from alert_SID.log file.
    error indicates the DB was going down or coming up when error got reported.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Quote Originally Posted by shareeman View Post
    I appreciate that you are trying to help but i reckon you ought to read the original query again. This is an Oracle error being generated by the instance and i have no trace of this from my end (ETL) and apparently the Oracle database end too. The latter issue is the reason for this post.
    To answer your specific query, I am the ETL administrator so am best placed to track this from an application perspective.
    Nevertheless, Thank you for your time.
    Just check in you shell script that runs sqlplus, Right before you run the sqlplus command if the database(s) are running and then sleep and try again in a loop, only doing the loop for say 5 times before exiting script:
    Code:
    running_databases=`ps -ef | grep smon | grep -v grep | awk '{print $9}' | awk -F_ '{print$3}'`
    or in addition log error to log file, example shell function:
    Code:
    example_update ()
    {
    pkey=$1
    update_results=`sqlplus -s $USER << ENDSQL
    set linesize 500 heading off feedback off
    whenever sqlerror exit 1
    update schema.file_status
      set  exonerated_date = null,
           rpt_url         = null,
           status          = 75
    where  rowid           = '${pkey}';
    commit;
    exit 0
    ENDSQL`
    if [[ $? = 1 ]] then
      (( error_count+=1 ))
      ts=`date "+%Y-%m-%d-%H.%M.%S"`
      print "`echo "$update_results" | sed 's/^[ \t\n]*/'$ts' /'`" >> ${LOG}
      print `date "+%Y-%m-%d-%H.%M.%S"` "**The above error occurred in function(example_update) updating file_status row to denote unsuccessful run: rowid-${pkey}" >> ${LOG}
     return 1
    fi
    return 0
    }

  11. #11
    Join Date
    Oct 2012
    Posts
    7
    thank you all for the suggestions.
    I have requested a copy of the alert log for the Oracle instance in question
    and set up an exception handling/logging routine on the Application side for next time. I will endeavour to keep this post updated upon progress

  12. #12
    Join Date
    Oct 2012
    Posts
    7
    I've got a copy of the Alert log and have found redo log issues (DBWR/LGWR) very close to the time (a min after ORA-01033) of the shutdown event.

    ALTER SYSTEM ARCHIVE LOG
    Thread 1 cannot allocate new log, sequence 117732
    Checkpoint not complete

    I've looked into this a fair bit and this error apparently causes Oracle to suspend all processing on the database until the log switch is made.

    Is this the causal link for my issue?
    Does " suspend all processing on the database " actually translate into an actual ORA-01033 error (or some form of) when the ETL application is trying to connect to and extract data from the Oracle database at that time?

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The checkpoint not complete message means that there are so many changes being sent tot he database, that the Archiver process has not processed all of the previous logs before the redo logs have cycled around. It should nit cause the error you are seeing. It should just be reflected as slightly poor performance. You can suggest to the DBAs that they either increase the size or number of the redo log groups (a little hard to say without knowing how many and how large they are, now). What anacedent was hoping to find would be shutdown or startup messages. In the snippet of log that you have, do you see anything along the lines of "Database Open", or "Shutdown Complete"?

  14. #14
    Join Date
    Oct 2012
    Posts
    7
    i'm afraid there's nothing along those lines in the Alert log.
    i've searched for various permutations of the following phrases, "Database Open", or "Shutdown Complete" but found nothing.
    All very bizarre indeed, I'm going to trawl through the application logs once more to see if i can spot any anomalies.

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Starting ORACLE instance (normal)
    Starting up:
    PMON started with pid=2, OS id=2205
    PSP0 started with pid=3, OS id=2207
    VKTM started with pid=4, OS id=2209 at elevated priority
    GEN0 started with pid=5, OS id=2213
    DIAG started with pid=6, OS id=2215
    DBRM started with pid=7, OS id=2217
    DIA0 started with pid=8, OS id=2219
    MMAN started with pid=9, OS id=2221
    DBW0 started with pid=10, OS id=2223
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •