12 Replies Latest reply: Aug 19, 2010 5:38 PM by 792658 RSS

    ORA-12505

    780248
      When I try to connect to oracle 11g, there appears such a problem: ORA-12505 : listner does not currently know of SID given in connection description.
      The details in the tnsnames.ora are as follows:

      # tnsnames.ora Network Configuration File: D:\app\xiaofan\product\11.1.0\db_1\NETWORK\ADMIN\tnsnames.ora
      # Generated by Oracle configuration tools.

      ORCL =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SID = orcl)
      )
      )

      It is said one can use lsnrctl services to know the details of servcice. Unfornunately, the listner in my computer does not support services.
        • 1. Re: ORA-12505
          sb92075
          This could be cause by the fact that the DB is not up & online

          open command window & issue the following commands

          set
          lsnrctl status
          lsnrctl service
          • 2. Re: ORA-12505
            630404
            replace "localhost" with your machine name

            regards,
            • 3. Re: ORA-12505
              sb92075
              replace "localhost" with your machine name
              why?
              oracle@bcm-laptop:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin$ cat tnsnames.ora 
              # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
              # Generated by Oracle configuration tools.
              
              V112 =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                  (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = v112)
                  )
                )
              
              LISTENER_V112 =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
              
              
              oracle@bcm-laptop:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin$ lsnrctl start
              
              LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 18-JUN-2010 15:36:36
              
              Copyright (c) 1991, 2009, Oracle.  All rights reserved.
              
              Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
              
              TNSLSNR for Linux: Version 11.2.0.1.0 - Production
              System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
              Log messages written to /u01/app/oracle/diag/tnslsnr/bcm-laptop/listener/alert/log.xml
              Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
              Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
              
              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
              STATUS of the LISTENER
              ------------------------
              Alias                     LISTENER
              Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
              Start Date                18-JUN-2010 15:36:38
              Uptime                    0 days 0 hr. 0 min. 0 sec
              Trace Level               off
              Security                  ON: Local OS Authentication
              SNMP                      OFF
              Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
              Listener Log File         /u01/app/oracle/diag/tnslsnr/bcm-laptop/listener/alert/log.xml
              Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
              The listener supports no services
              The command completed successfully
              oracle@bcm-laptop:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin$ sqlplus hr/oracle@v112
              
              SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 18 15:36:57 2010
              
              Copyright (c) 1982, 2009, Oracle.  All rights reserved.
              
              
              Connected to:
              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              SQL> 
              • 4. Re: ORA-12505
                780248
                Thanks a lot! I have changed localhost to my computer name and this works! But i meet a new problem. I don't know the usename and password. i remember i set the password of the database as 1234 when i was installing oralcle 11 on windows XP.
                • 5. Re: ORA-12505
                  Fahd.Mirza
                  Do this:

                  c:\ sqlplus /nolog

                  sqlplus> conn / as sysdba
                  sqlplus> alter user sys identified by test;

                  Now your username is sys and the password is test.

                  But you should create some other user and use it for day to day tasks.

                  you may create new user by doing:

                  create user foo identified by bar;
                  grant connect to foo;

                  regards
                  • 6. Re: ORA-12505
                    780248
                    Thanks!

                    I typed in following command:

                    c:\ sqlplus /nolog

                    sqlplus> conn / as sysdba
                    sqlplus> alter user sys identified by test;


                    it returns: user has been changed

                    However, i still can not use sql developer to login on the database. I use: conn, sys, test. And it returns: connection as sys should be as SYSDBA or SYSOPER
                    • 7. Re: ORA-12505
                      780248
                      I did it!

                      I just create another user!
                      • 8. Re: ORA-12505
                        Fahd.Mirza
                        As I said earlier, dont connect with sys. Connect with some other user. Create a new user like this.


                        create user foo identified by bar;
                        grant connect, create session to foo;

                        Now we have a user foo with password bar. Connect with this user to the sql developer.


                        If you want to make this user more powerful than grant it dba right.

                        regards
                        • 9. Re: ORA-12505
                          Fahd.Mirza
                          If your problem has resolved, then mark the question as answered.

                          regards
                          • 10. Re: ORA-12505
                            780248
                            meet the same problem again
                            • 11. Re: ORA-12505
                              780248
                              I meet this problem ORA-12505 again just because I bring my laptop to my lab and plugin a cable.

                              I changed the host name in tnsnames.ora from my computer name to localhost to 127.0.0.1. It still does not work!

                              How could this happen!
                              • 12. Re: ORA-12505
                                792658
                                My problem was solved by this answer