This discussion is archived
1 2 3 Previous Next 41 Replies Latest reply: Sep 17, 2013 5:33 AM by sb92075 Go to original post RSS
  • 30. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    EdStevens Guru
    Currently Being Moderated

    c66e295b-e424-45df-a343-9e2219231da4 wrote:

     

    I thank you all for your suggestions.. Unfortunately i tried everything but i couldn't solve the problem i will try a system restore otherwise i'll reinstall DB...

    You haven't tried "everything".  If you had, you'd have fixed the problem.

    Reinstall the DB?  That's like burning your house down and rebuilding just because you lost the door key.  Work the problem.  What would you do if this were a production db and re-installing were not an option.

  • 31. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    c66e295b-e424-45df-a343-9e2219231da4 Newbie
    Currently Being Moderated

    i NEED HELP PLZ.. i changed the listener.ora file as you had asked.. then after reading an article by Ed Stevens.. stopped listener.. then executed "sqlplus /nolog" then startup.. then i was able to login to "se3" database.. i reloaded the listener and then checked its status.. now the "se3" database instance had changed state from 'unknown" to ready... but im not able to connect to "mydb" database.. everything is same in the listener.ora file btw.. PLZ HELP

  • 32. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    sb92075 Guru
    Currently Being Moderated

    c66e295b-e424-45df-a343-9e2219231da4 wrote:

     

    i NEED HELP PLZ.. i changed the listener.ora file as you had asked.. then after reading an article by Ed Stevens.. stopped listener.. then executed "sqlplus /nolog" then startup.. then i was able to login to "se3" database.. i reloaded the listener and then checked its status.. now the "se3" database instance had changed state from 'unknown" to ready... but im not able to connect to "mydb" database.. everything is same in the listener.ora file btw.. PLZ HELP

     

    DISCONNECT

    SET INSTANCE MYDB

    STARTUP

  • 33. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    c66e295b-e424-45df-a343-9e2219231da4 Newbie
    Currently Being Moderated

    I did the following in sequence.. firstly i modified the listener.ora file.. then i stopped and started the listener.. then i executed "sqlplus /nolog", then "connect / as sysdba"

    After that, i issued startup.. It was success.. and then i checked the "lsnrctl status" which is as follows:

     

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

    Services Summary...

    Service "CLRExtProc" has 1 instance(s).

      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Service "mydb" has 1 instance(s).

      Instance "mydb", status UNKNOWN, has 1 handler(s) for this service...

    Service "se3" has 1 instance(s).

      Instance "se3", status UNKNOWN, has 1 handler(s) for this service...

    Service "se3.orcl" has 1 instance(s).

      Instance "se3", status READY, has 1 handler(s) for this service...

    Service "se3XDB.orcl" has 1 instance(s).

      Instance "se3", status READY, has 1 handler(s) for this service...

    The command completed successfully

     

    I had also executed "set instance=mydb" in the beginning (after starting the listener).. Im ABLE to login to se3 database (both sql*plus and sqldeveloper/visual studio) but cannot use mydb databse.. If you ask, i can post the listener.ora (modified) file too..

    THANK YOU SO MUCH IN ADVANCE FOR YOUR TIME AND HELP.. I APPRECIATE..

  • 34. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    sb92075 Guru
    Currently Being Moderated

    Realize that if you just COPY & PASTE completely & exactly what you do & how Oracle responds, then we can see for ourselves what you did.

     

    no listener is required to start & access any local Oracle database.

    no listener.ora file is required to start or utilize any Oracle listener.

     

    I suspect that any database named "mydb" is running & online now.

    post the last 50 or so lines from alert_mydb.log file

  • 35. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    sb92075 Guru
    Currently Being Moderated

    Realize that if you just COPY & PASTE completely & exactly what you do & how Oracle responds, then we can see for ourselves what you did.

     

     

    no listener is required to start & access any local Oracle database.

    no listener.ora file is required to start or utilize any Oracle listener.

     

     

    I suspect that no database named "mydb" is running & online now.

    post the last 50 or so lines from alert_mydb.log file

  • 36. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    c66e295b-e424-45df-a343-9e2219231da4 Newbie
    Currently Being Moderated

    Almost the end of alert_mydb.log file

     

    Starting up:

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options.

    Using parameter settings in server-side spfile G:\ORACLEDB\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEMYDB.ORA

    System parameters with non-default values:

      processes                = 150

      memory_target            = 816M

      control_files            = "G:\ORACLEDB\ORADATA\MYDB\CONTROL01.CTL"

      control_files            = "G:\ORACLEDB\FLASH_RECOVERY_AREA\MYDB\CONTROL02.CTL"

      db_block_size            = 8192

      compatible               = "11.2.0.0.0"

      db_recovery_file_dest    = "G:\oracledb\flash_recovery_area"

      db_recovery_file_dest_size= 3852M

      undo_tablespace          = "UNDOTBS1"

      remote_login_passwordfile= "EXCLUSIVE"

      db_domain                = "orcl"

      dispatchers              = "(PROTOCOL=TCP) (SERVICE=mydbXDB)"

      local_listener           = "LISTENER_MYDB"

      audit_file_dest          = "G:\ORACLEDB\ADMIN\MYDB\ADUMP"

      audit_trail              = "DB"

      db_name                  = "mydb"

      open_cursors             = 300

      diagnostic_dest          = "G:\ORACLEDB"

    Mon Sep 16 00:44:37 2013

    PMON started with pid=2, OS id=1984

    Mon Sep 16 00:44:37 2013

    VKTM started with pid=3, OS id=3032 at elevated priority

    Mon Sep 16 00:44:37 2013

    GEN0 started with pid=4, OS id=2492

    Mon Sep 16 00:44:37 2013

    DIAG started with pid=5, OS id=2308

    VKTM running at (10)millisec precision with DBRM quantum (100)ms

    Mon Sep 16 00:44:37 2013

    DBRM started with pid=6, OS id=2888

    Mon Sep 16 00:44:37 2013

    PSP0 started with pid=7, OS id=3716

    Mon Sep 16 00:44:37 2013

    DIA0 started with pid=8, OS id=4060

    Mon Sep 16 00:44:37 2013

    MMAN started with pid=9, OS id=1200

    Mon Sep 16 00:44:37 2013

    DBW0 started with pid=10, OS id=2684

    Mon Sep 16 00:44:37 2013

    LGWR started with pid=11, OS id=2908

    Mon Sep 16 00:44:37 2013

    CKPT started with pid=12, OS id=2856

    Mon Sep 16 00:44:37 2013

    SMON started with pid=13, OS id=3684

    Mon Sep 16 00:44:37 2013

    RECO started with pid=14, OS id=3504

    Mon Sep 16 00:44:37 2013

    MMON started with pid=15, OS id=2144

    starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

    starting up 1 shared server(s) ...

    ORACLE_BASE from environment = G:\oracledb

    Mon Sep 16 00:44:38 2013

    AUD: OS Error = 1502 encountered while writing audit record

    Mon Sep 16 00:44:37 2013

    MMNL started with pid=16, OS id=1644

    Mon Sep 16 00:44:58 2013

    AUD: OS Error = 1502 encountered while writing audit record

    AUD: OS Error = 1502 encountered while writing audit record

    Mon Sep 16 00:45:49 2013

    AUD: OS Error = 1502 encountered while writing audit record

    AUD: OS Error = 1502 encountered while writing audit record

    AUD: OS Error = 1502 encountered while writing audit record

    AUD: OS Error = 1502 encountered while writing audit record

    Tue Sep 17 12:27:50 2013

    Starting ORACLE instance (normal)

    LICENSE_MAX_SESSION = 0

    LICENSE_SESSIONS_WARNING = 0

    Picked latch-free SCN scheme 2

    Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

    Autotune of undo retention is turned on.

    IMODE=BR

    ILAT =27

    LICENSE_MAX_USERS = 0

    SYS auditing is disabled

    Tue Sep 17 12:28:02 2013

    Starting up:

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options.

    Using parameter settings in server-side spfile G:\ORACLEDB\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEMYDB.ORA

    System parameters with non-default values:

      processes                = 150

      memory_target            = 816M

      control_files            = "G:\ORACLEDB\ORADATA\MYDB\CONTROL01.CTL"

      control_files            = "G:\ORACLEDB\FLASH_RECOVERY_AREA\MYDB\CONTROL02.CTL"

      db_block_size            = 8192

      compatible               = "11.2.0.0.0"

      db_recovery_file_dest    = "G:\oracledb\flash_recovery_area"

      db_recovery_file_dest_size= 3852M

      undo_tablespace          = "UNDOTBS1"

      remote_login_passwordfile= "EXCLUSIVE"

      db_domain                = "orcl"

      dispatchers              = "(PROTOCOL=TCP) (SERVICE=mydbXDB)"

      local_listener           = "LISTENER_MYDB"

      audit_file_dest          = "G:\ORACLEDB\ADMIN\MYDB\ADUMP"

      audit_trail              = "DB"

      db_name                  = "mydb"

      open_cursors             = 300

      diagnostic_dest          = "G:\ORACLEDB"

    Tue Sep 17 12:28:03 2013

    ksuapc : ORA-1033 foreground process starts before PMON

  • 37. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    c66e295b-e424-45df-a343-9e2219231da4 Newbie
    Currently Being Moderated

    I got "mydb" database working !! Funny though, i just increased the log sizes of application and system events (clearing doesn't solve problem) , restarted listener and that solved my problem..

  • 38. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Now, after (only!) 37 posts, you realize that your "all of sudden" problem had a little to do with Oracle, aren't you ? Time to investigate why those logs need such a space.  Nicolas.
  • 39. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    c66e295b-e424-45df-a343-9e2219231da4 Newbie
    Currently Being Moderated

    Well it had something to do with Oracle after all i added some description of "SID" inside "SID_LIST_LISTENER" of listener.ora file.. I thank you all experts here to solve my problem!

  • 40. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    sb92075 Guru
    Currently Being Moderated

    c66e295b-e424-45df-a343-9e2219231da4 wrote:

     

    Well it had something to do with Oracle after all i added some description of "SID" inside "SID_LIST_LISTENER" of listener.ora file.. I thank you all experts here to solve my problem!

     

     

    no listener is required to start & access any local Oracle database.

    no listener.ora file is required to start or utilize any Oracle listener.

  • 41. Re: Suddenly getting ORA-12514 error Oracle 11gr2
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    >Well it had something to do with Oracle after all i added some description of "SID" inside "SID_LIST_LISTENER" of listener.ora file

    Hardly true. At a first glance, the file did not change suddenly to break your connection and require to be modified.

    Then a listener.ora file is not mandatory to have a working listener on 1521. So probably your modifications are meaningless.

     

    Nicolas.

1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points