Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2011
    Posts
    12

    Unanswered: ORA-01033 and ORA-12514

    I have a database and previously, it worked fine when I login into PL/SQL. Recently, when I tried to login using PLSQL, i recevied error ora 01033.

    In order to solve it, i connected to the database thru cmd window and shutdown abort the database,

    SQL> conn sys/sys as sysdba
    Connected.
    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> startup nomount
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor

    I kept receiving the same error when i tried to startup.

    Someone, please help me out. Any suggestion/feedback would be very much appreciated.

    Thank you very much.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what is OS name & version?
    What is Oracle version to 4 decimal places?

    When did Oracle last work OK?
    What changed since then?
    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
    Aug 2011
    Posts
    12
    Dear anacedent,

    I'm using windows xp and Oracle 10g 10.2.0.1.0. It was working about a month ago, I haven't used it since then and made no changes to the database.

    When i ping it, it's ok.

    Any idea? Anyway, thx for the response.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    is OS Service for DB started?

    open Command Window & issue following OS comands

    lsnrctl status
    lsnrctl service
    set

    COPY commands & results then PASTE all back here
    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
    Aug 2011
    Posts
    12
    yes, the service for DB is started.

    as requested, below is the results each commands. Actually, before posting this thread,my colleague and I tried all the requested commands and found nothing useful from the results.

    my apology, my Oracle version is 10g 10.2.0.3.0.

    thx for your help, really appreciate it.

    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.

    C:\Documents and Settings\AAziz31>tnsping pm3nf

    TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 21-AUG-2
    011 11:55:42

    Copyright (c) 1997, 2006, Oracle. All rights reserved.

    Used parameter files:
    D:\oracle\product\10.2.0\db_2\network\admin\sqlnet .ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =AAZIZ31-1
    -EAG)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pm3nf))
    )
    OK (10 msec)

    C:\Documents and Settings\AAziz31>lsnrctl status

    LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 21-AUG-2011 11:56
    :19

    Copyright (c) 1991, 2006, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AAZIZ31-1-EAG)(PORT=1521
    )))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Produ
    ction
    Start Date 21-AUG-2011 10:33:46
    Uptime 0 days 1 hr. 22 min. 38 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File D:\oracle\product\10.2.0\db_2\network\admin\listen er.o
    ra
    Listener Log File D:\oracle\product\10.2.0\db_2\network\log\listener .log

    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AAziz31-1-EAG.fea.slb.com)(PORT=1521
    )))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "orcl" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclXDB" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orcl_XPT" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "pm3nf" has 1 instance(s).
    Instance "pm3nf", status READY, has 1 handler(s) for this service...
    Service "pm3nf_XPT" has 1 instance(s).
    Instance "pm3nf", status READY, has 1 handler(s) for this service...
    The command completed successfully

    C:\Documents and Settings\AAziz31>lsnrctl service

    LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 21-AUG-2011 11:5
    :54

    Copyright (c) 1991, 2006, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AAZIZ31-1-EAG)(PORT=152
    )))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    Service "orcl" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "orclXDB" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1002 state:ready
    DISPATCHER <machine: AAZIZ31-1-EAG, pid: 4580>
    (ADDRESS=(PROTOCOL=tcp)(HOST=AAziz31-1-EAG.fea.slb.com)(PORT=1110))
    Service "orcl_XPT" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "pm3nf" has 1 instance(s).
    Instance "pm3nf", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "pm3nf_XPT" has 1 instance(s).
    Instance "pm3nf", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully

    C:\Documents and Settings\AAziz31>set
    ALLUSERSPROFILE=C:\Documents and Settings\All Users
    APPDATA=C:\Documents and Settings\AAziz31\Application Data
    CommonProgramFiles=C:\Program Files\Common Files
    COMPUTERNAME=AAZIZ31-1-EAG
    ComSpec=C:\WINDOWS\system32\cmd.exe
    DEFLOGDIR=C:\Documents and Settings\All Users\Application Data\McAfee\DesktopPr
    tection
    DRVDIR=C:\DRV
    FP_NO_HOST_CHECK=NO
    HOMEDRIVE=C:
    HOMEPATH=\Documents and Settings\AAziz31
    LOGONSERVER=\\MY0010DOM19
    MODEL=Dx10
    NUMBER_OF_PROCESSORS=2
    OS=Windows_NT
    Path=D:\oracle\product\10.2.0\db_2\bin;C:\WINDOWS\ system32;C:\WINDOWS;C:\WINDOW
    \System32\Wbem;C:\oracle\ora10\bin;C:\Program Files\Smart Cards an
    Terminals\Cyberflex Access Kits\v4\;C:\Program Files\Common Files\Roxio Shared
    DLLShared\;C:\Program Files\Common Files\Roxio Shared\10.0\DLLShared\;C:\Progra
    Files\Windows Imaging\
    PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WS F;.WSH
    PERL5LIB=D:\oracle\product\10.2.0\db_2\perl\5.8.3\ lib\MSWin32-x86;D:\oracle\pro
    uct\10.2.0\db_2\perl\5.8.3\lib;D:\oracle\product\1 0.2.0\db_2\perl\5.8.3\lib\MSW
    n32-x86;D:\oracle\product\10.2.0\db_2\perl\site\5.8.3; D:\oracle\product\10.2.0\
    b_2\perl\site\5.8.3\lib;D:\oracle\product\10.2.0\d b_2\sysman\admin\scripts;D:\o
    acle\product\10.2.0\db_2\perl\lib\5.8.3\MSWin32-x86;D:\oracle\product\10.2.0\db
    2\perl\lib\5.8.3;D:\oracle\product\10.2.0\db_2\per l\5.8.3\lib\MSWin32-x86-multi
    thread;D:\oracle\product\10.2.0\db_2\perl\site\5.8 .3;D:\oracle\product\10.2.0\d
    _2\perl\site\5.8.3\lib;D:\oracle\product\10.2.0\db _2\sysman\admin\scripts
    PROCESSOR_ARCHITECTURE=x86
    PROCESSOR_IDENTIFIER=x86 Family 6 Model 15 Stepping 10, GenuineIntel
    PROCESSOR_LEVEL=6
    PROCESSOR_REVISION=0f0a
    ProgramFiles=C:\Program Files
    PROMPT=$P$G
    RoxioCentral=C:\Program Files\Common Files\Roxio Shared\10.0\Roxio Central36\
    SESSIONNAME=Console
    SLBSLS_LICENSE_FILE=1700@localhost;
    SystemDrive=C:
    SystemRoot=C:\WINDOWS
    SYSTYPE=PORTABLE
    TEMP=C:\Temp
    TMP=C:\Temp
    TYPE=Notebook
    UATDATA=C:\WINDOWS\system32\CCM\UATData\D9F8C395-CAB8-491d-B8AC-179A1FE1BE77
    USERDNSDOMAIN=DIR.SLB.COM
    USERDOMAIN=DIR
    USERNAME=AAziz31
    USERPROFILE=C:\Documents and Settings\AAziz31
    VSEDEFLOGDIR=C:\Documents and Settings\All Users\Application Data\McAfee\Deskto
    Protection
    windir=C:\WINDOWS
    X_BIOSVER=A14
    X_BOOTBUS=IDE
    X_CPUFAM=Intel(R) Core(TM)2 Duo CPU T7300@ 2.00GHz
    X_DETECTEDMODEL=D610
    X_LOB=Latitude
    X_MANUFACT=Dell
    X_MODEL=D630
    X_SNUM=CZ6DHH1
    X_SPEED=1994

    C:\Documents and Settings\AAziz31>c
    Last edited by ax_slave; 08-22-11 at 22:31.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    (HOST=AAziz31-1-EAG.fea.slb.com)(PORT=1521)))
    (HOST=AAZIZ31-1-EAG)(PORT=152)))

    Notice differences between the two lines above.

    might difference matter?
    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
    Aug 2011
    Posts
    12
    as far as I'm concerned, the difference shouldn't be a problem.

    any other suggestion where i should check?

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Where are the environmental variables ORACLE_HOME and ORACLE_SID?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2011
    Posts
    12
    Dear beilstwh,

    The ORACLE_HOME is located at D:\oracle\product\10.2.0\db_2 and the ORACLE_SID is in tsnames.ora doc at D:\oracle\product\10.2.0\db_2\network\admin\tnsnam es.ora. Please see the attachment from registry.

    Thanks for the response.
    Attached Thumbnails Attached Thumbnails oracle10g.bmp  

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    That's not what i am asking, in your dump of the environmental variables above the REQUIRED variables ORACLE_HOME and ORACLE_SID are missing.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Aug 2011
    Posts
    12
    can you point me out where exactly you mean? when i compare results of tnsping, lsnrctl status, lsnrctl service and set for a ok databse, it looks similar with the one i posted. Anyway, if they're missing, what should i do to solve it.

    thx you so much.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post content of following file
    D:\oracle\product\10.2.0\db_2\network\admin\sqlnet .ora
    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.

  13. #13
    Join Date
    Aug 2011
    Posts
    12
    Below is the content from D:\oracle\product\10.2.0\db_2\network\admin\sqlnet .ora :-

    # This file is actually generated by netca. But if customers choose to
    # install "Software Only", this file wont exist and without the native
    # authentication, they will not be able to connect to the database on NT.

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

  14. #14
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Hi,

    looking into the environment you posted it seems that you have at least 2 Oracle homes installed:

    Code:
    Path=D:\oracle\product\10.2.0\db_2\bin;C:\WINDOWS\ system32;C:\WINDOWS;C:\WINDOW
    \System32\Wbem;C:\oracle\ora10\bin;C:\Program Files\Smart Cards an
    Terminals\Cyberflex Access Kits\v4\;C:\Program Files\Common Files\Roxio Shared
    DLLShared\;C:\Program Files\Common Files\Roxio Shared\10.0\DLLShared\;C:\Progra
    Files\Windows Imaging\
    and that there are at least 2 databases running (presumably from those different homes)

    Code:
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "orcl" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclXDB" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orcl_XPT" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "pm3nf" has 1 instance(s).
    Instance "pm3nf", status READY, has 1 handler(s) for this service...
    Service "pm3nf_XPT" has 1 instance(s).
    Instance "pm3nf", status READY, has 1 handler(s) for this service...
    This leads to the assumtion, that you are simply using the wrong Oracle environment


    To prove this theory, try the following in a command line window:


    Code:
    set ORACLE_SID=pm3nf
    set TNS_ADMIN=D:\oracle\product\10.2.0\db_2\network\admin
    sqlplus / as sysdba
    SQL> startup
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  15. #15
    Join Date
    Aug 2011
    Posts
    12
    Dear magicwand,

    Yes, you're right, I have two Oracle installed in my machine (client/server), one in C drive and the other one in D drive. I'm using the one in D drive and I have 10 databases. Previously, everything works fine.

    Below is the result:-

    set ORACLE_SID=pm3nf
    set TNS_ADMIN=D:\oracle\product\10.2.0\db_2\network\ad min
    sqlplus / as sysdba
    SQL> startup
    Total System Global Area 612368384 bytes
    Fixed Size 1292036 bytes
    Variable Size 230689020 bytes
    Database Buffers 373293056 bytes
    Redo Buffers 7094272 bytes
    Database mounted.
    ORA-01172: recovery of thread 1 stuck at block 105 of file 2
    ORA-01151: use media recovery to recover block, restore backup if needed


    What should I do to correct the Oracle environment?

    Thank you for your help.

Posting Permissions

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