This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Feb 6, 2013 11:27 PM by 936666 Go to original post RSS
  • 15. Re: Error: ORA-03114: not connected to ORACLE
    936666 Newbie
    Currently Being Moderated
    >
    I didn't ask you to provide me with listener.ora, I asked why you couldn't provide it to your client along with everything else you are doing for him.

    And just to note ... with this in you listener.ora
    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1522))
    your listener will never get a request from any other machine ....

    Edited by: EdStevens on Feb 1, 2013 7:29 AM
    i am connecting my machine only i am running in port 1522
  • 16. Re: Error: ORA-03114: not connected to ORACLE
    EdStevens Guru
    Currently Being Moderated
    933663 wrote:
    in my lsnrctl i am unable to see the database
    H:\lsnrctl status
    
    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-FEB-2013 19:10:31
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
    Start Date                01-FEB-2013 19:01:09
    Uptime                    0 days 0 hr. 9 min. 22 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   C:\oracle\listener.ora
    Listener Log File         e:\app\username\diag\tnslsnr\mysystem\listener\alert\log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
    Services Summary...
    Service "orcl" has 1 instance(s).
    Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    Service "First" has 1 instance(s).
    Instance "First", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    i am able to see the all the database which running under this instances
    Obviously, your database isntance 'last' is not registered with the listener.

    >
    After that i pingged the database
    H:\>tnsping First
    
    TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-FEB-2013 19:13:47
    
    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
    
    Used parameter files:
    C:\Oracle\sqlnet.ora
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost
    )(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FIRST)))
    OK (30 msec)
    where as in tnsping Last i am able to see the database
    H:\>tnsping Last
    
    TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-FEB-2013 19:13:47
    
    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
    
    Used parameter files:
    C:\Oracle\sqlnet.ora
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost
    )(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = LAST)))
    OK (30 msec)
    tnsping doesn't do what you think it does. It only confirms that the name resolution -- the connect string - points to a reachable listener. It says absolutley nothing about the state - or even the existence - of the named instance.

    see: [url http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/] tnsping-101

    Last database is in oracleserviceLast

    What is wrong with the process.
    read the links I've provided.
  • 17. Re: Error: ORA-03114: not connected to ORACLE
    936666 Newbie
    Currently Being Moderated
    i agree but while running the wizard it is not saving any entry in Listener why?
    why i need to give at when i run a database creation via sql script
    What is missing over the script process.
    how it is able to do with no data at LISTENER.ora in wizard , how it is referred.

    Thanks!
  • 18. Re: Error: ORA-03114: not connected to ORACLE
    EdStevens Guru
    Currently Being Moderated
    933663 wrote:
    i agree but while running the wizard it is not saving any entry in Listener why?
    What wizard? What options?

    why i need to give at when i run a database creation via sql script
    I don't understand that sentence at all.
    What is missing over the script process.
    how it is able to do with no data at LISTENER.ora in wizard , how it is referred.
    I don't understand that sentence at all.

    >
    Thanks!
  • 19. Re: Error: ORA-03114: not connected to ORACLE
    936666 Newbie
    Currently Being Moderated
    i agree but while running the wizard it is not saving any entry in Listener why?
    What wizard? What options?

    Wizard -->DBCA
    While running the DBCA to create the database we will not choose any option to save in listener i have created about 10 database via DBCA there is no entry in the listener
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = orcl)
          (ORACLE_HOME = E:\app\username\product\11.2.0\dbhome_1)
          (PROGRAM = extproc)
          (ENVS = "EXTPROC_DLLS=ONLY:E:\app\username\product\11.2.0\dbhome_1\bin\oraclr11.dll")
        )
      )
     
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
          (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1522))
        )
      )
    there is no other entry is there i have created nearly more than 10 databases.
    There is only change in tnsnames.ora it have an entry for the newly created database
    why i need to give at when i run a database creation via sql script
    I mean to say while running the scripts why i need to do the below step
    2) Before running the database creation script, modify the SID_LIST section of listner.ora, adding an entry for the yet to be created database. Then reload the listener.
    where as in DBCA there is no such entry for listener.

    What is missing over the script process.how it is able to do with no data at LISTENER.ora in wizard , how it is referred.
    While running the scripts to create database why there is an manual entry is required in listener.
  • 20. Re: Error: ORA-03114: not connected to ORACLE
    sb92075 Guru
    Currently Being Moderated
    933663 wrote:
    While running the scripts to create database why there is an manual entry is required in listener.
    NO manual entry is required because NO listener.ora file is required to start or use the listener.
  • 21. Re: Error: ORA-03114: not connected to ORACLE
    936666 Newbie
    Currently Being Moderated
    hi sb92075,
    Did you look at the whole post conversation for this problem.
    why we need an entry in listener.ora if we run via script.

    Thanks!
  • 22. Re: Error: ORA-03114: not connected to ORACLE
    936666 Newbie
    Currently Being Moderated
    Any suggestions..!!
  • 23. Re: Error: ORA-03114: not connected to ORACLE
    EdStevens Guru
    Currently Being Moderated
    933663 wrote:
    i agree but while running the wizard it is not saving any entry in Listener why?
    What wizard? What options?

    Wizard -->DBCA
    While running the DBCA to create the database we will not choose any option to save in listener i have created about 10 database via DBCA there is no entry in the listener
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = orcl)
    (ORACLE_HOME = E:\app\username\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:E:\app\username\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    )
    
    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1522))
    )
    )
    there is no other entry is there i have created nearly more than 10 databases.
    There is only change in tnsnames.ora it have an entry for the newly created database
    why i need to give at when i run a database creation via sql script
    I mean to say while running the scripts why i need to do the below step
    2) Before running the database creation script, modify the SID_LIST section of listner.ora, adding an entry for the yet to be created database. Then reload the listener.
    You only have to do that IF you want dbca to create a database AND configure dbcontrol both in one operation. You have to do that because the creation and operation of dbcontrol requires a listener connection. And the script for creating a database and configuring dbcontrol will get to the part about dbcontrol before the newly created database has had time to register itself with the listener - hence we need the listener to be "pre-informed" about the as yet non-existent database.
    >
    where as in DBCA there is no such entry for listener.

    What is missing over the script process.how it is able to do with no data at LISTENER.ora in wizard , how it is referred.
    While running the scripts to create database why there is an manual entry is required in listener.
    dbca does not create an entry in the listener.ora file. As to "why" it doesn't, you'd have to ask the guys that wrote dbca. I' sure it has to do with the fact the the database itself doesn't care if there is a listener or not.

    Yes, it would be nice if Oracle would work out this Catch-22 situation, but to my knowledge they haven't. So you have two options:
    1) manually create an entry in listener.or for the new database before running dbca to create the db and configure dbcontrol, OR
    2) Do NOT select to configure dbcontrol when creating the database. Instead, create the database without it, then come back and create dbcontrol as a second operation either through dbca or using emca at the command line.
  • 24. Re: Error: ORA-03114: not connected to ORACLE
    936666 Newbie
    Currently Being Moderated
    EdStevens wrote:
    2) Do NOT select to configure dbcontrol when creating the database. Instead, create the database without it, then come back and create dbcontrol as a second operation either through dbca or using emca at the command line.
    I am using Win 2003 and Oracle 11g , during the DBCA setup i am not able to see the DBcontrol configuration
    There are 11 steps involved in DBCA to create the database
    at 9th step there is a option called All initialization parameters there at no 3 there is a option called Control file under this value i found
    *("{ORACLE_BASE}\oradata\{DB_UNIQUE_NAME}\control01.ctl", "{ORACLE_BASE}\flash_recovery_area\{DB_UNIQUE_NAME}\control02.ctl")*
    Override default is enabled and in category it is mentioned as File configuration
    Do i need to disable the Override Default here.
    Is there are any articles to see how to create the database with these steps.

    Thanks!
  • 25. Re: Error: ORA-03114: not connected to ORACLE
    EdStevens Guru
    Currently Being Moderated
    933663 wrote:
    EdStevens wrote:
    2) Do NOT select to configure dbcontrol when creating the database. Instead, create the database without it, then come back and create dbcontrol as a second operation either through dbca or using emca at the command line.
    I am using Win 2003 and Oracle 11g , during the DBCA setup i am not able to see the DBcontrol configuration
    There are 11 steps involved in DBCA to create the database
    at 9th step there is a option called All initialization parameters there at no 3 there is a option called Control file under this value i found
    *("{ORACLE_BASE}\oradata\{DB_UNIQUE_NAME}\control01.ctl", "{ORACLE_BASE}\flash_recovery_area\{DB_UNIQUE_NAME}\control02.ctl")*
    Override default is enabled and in category it is mentioned as File configuration
    Do i need to disable the Override Default here.
    Is there are any articles to see how to create the database with these steps.

    Thanks!
    dbca -->
    Database Configuration Assistant: Welcome ... Next --->
    Database Configuration Assistant: Step 1 of 12:Operations ....
    Options are:
    1 - Create a Database
    2 - Configure Database Options <<<<< chose this one
    3 - Delete a Database
    4 - Manage Templates.
    Database Configuration Assistant: Step 2 of 4:Database
    "Select the database that you want to configure" ... Next --->
    Database Configuration Assistant: Step 2 of 6:Management Options
    - Configure Enterprise Manager <<<<< select this
    - Configure Database Control for local management <<<<< select this
    NEXT

    Surely you can take it from here ...
  • 26. Re: Error: ORA-03114: not connected to ORACLE
    936666 Newbie
    Currently Being Moderated
    EdStevens wrote:
    1) Regenerate your script/template by going through the wizard and NOT selecting to create the dbcontrol. Then, after you have run the script and created the database (without the dbcontrol) go back and run dbca and select to create dbcontrol on the new, now existing, database.

    2) Before running the database creation script, modify the SID_LIST section of listner.ora, adding an entry for the yet to be created database. Then reload the listener.
    For Step:1
    is there any link is there to follow since iam not able to find any solution any where i am keep on posting here.
    I need a script to create the database with proper configuration in it

    i followed the http://www.orafaq.com/node/2015 but still missing SID in listener.ora so i thought of going for Templet

    If i follow step 2:
    I will add an entry in listener.ora file for a new database(yet to come) and restart the listener
    then what should i do?
  • 27. Re: Error: ORA-03114: not connected to ORACLE
    EdStevens Guru
    Currently Being Moderated
    933663 wrote:
    EdStevens wrote:
    1) Regenerate your script/template by going through the wizard and NOT selecting to create the dbcontrol. Then, after you have run the script and created the database (without the dbcontrol) go back and run dbca and select to create dbcontrol on the new, now existing, database.

    2) Before running the database creation script, modify the SID_LIST section of listner.ora, adding an entry for the yet to be created database. Then reload the listener.
    For Step:1
    That's not step 1, step 2. That's Option 1 and Option 2. Either/or. Not both.
    is there any link is there to follow since iam not able to find any solution any where i am keep on posting here.
    I need a script to create the database with proper configuration in it
    I don't know how to make it any more clear.
    At this point I'm assuming you have run dbca. I'm assuming you know how to make choices as you go through it. I'm assuming you know how to have it generate the CREATE DATABASE scripts at the end.

    Ok so you need to make a choice.
    Do you want to fix the listener.ora so that the scripts will work the first time?


    If so, then fix the listener, then as you are walking through dbca, choose to create dbcontrol.
    ************ OR ******************

    Do you want to leave listener.ora untouched, and run emca scripts after the database creation scripts?

    If so, don't touch the listener.ora, and as you are walking through dbca, choose to NOT create dbcontrol.


    i followed the http://www.orafaq.com/node/2015 but still missing SID in listener.ora so i thought of going for Templet
    I can't take responsibility for someone else's web site.
    >
    If i follow step 2:
    I will add an entry in listener.ora file for a new database(yet to come) and restart the listener
    then what should i do?
    Run your database creation script ....
  • 28. Re: Error: ORA-03114: not connected to ORACLE
    936666 Newbie
    Currently Being Moderated
    Thanks a lot EdStevens,

    I think i have to opt for option 1 since i may not in a position to run the dbca wizard
    where as i can generate the script and run those script and modify the listener.ora file option 2 is ok for me since there is no need to run the DBCA.

    But i'm wondering that if i run DBCA there is no entry in listener.ora only in tnsnames.ora will have changes.

    Ways followed by me

    I have generated the script with DBCA wizard and save the scripts
    added a new entry in the listener.ora
    and finally run the bat file in scripts folder

    is there any thing needed apart from it.

    Once again thanks a lot for your valuable time and information on this.

    Thanks!
  • 29. Re: Error: ORA-03114: not connected to ORACLE
    EdStevens Guru
    Currently Being Moderated
    933663 wrote:
    Thanks a lot EdStevens,

    I think i have to opt for option 1 since i may not in a position to run the dbca wizard
    where as i can generate the script and run those script and modify the listener.ora file option 2 is ok for me since there is no need to run the DBCA.

    But i'm wondering that if i run DBCA there is no entry in listener.ora only in tnsnames.ora will have changes.

    Ways followed by me

    I have generated the script with DBCA wizard and save the scripts
    added a new entry in the listener.ora
    and finally run the bat file in scripts folder

    is there any thing needed apart from it.
    Did that procedure produce the desired results?
    Once again thanks a lot for your valuable time and information on this.

    Thanks!

Legend

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