1 2 3 4 5 6 Previous Next 79 Replies Latest reply: Mar 13, 2013 10:36 AM by EdStevens Go to original post RSS
      • 60. Re: Cant connect my database using net service_name
        Mark D Powell
        You always have to specifiy when you want a sysdba connection. The following is a valid UNIX command entry for a server where I configured remote sysdba connection for the purpose of this demo. I have changed the displayed information to be generic. Normally I think allowing remote sysdba connections are a security issue. If you cannot log onto the database server as the Oracle software owner you do should not be allowed a sysdba connection. But if you want to remote administrator your database via EM or GRID then you allow this feature.

        sqlplus "username@tns_alias as sysdba" and then provide the password on the prompt otherwise the password would be provided as "username/pswd@tns"


        HTH -- Mark D Powell --
        • 61. Re: Cant connect my database using net service_name
          972355
          Good Morning to all ;

          I can connect my database.

          *[oracle@linuxserver dbs]$ orapwd file=orapworcltest password=asdf force=y*

          SQL> conn sys/asdf@ORCLTEST as sysdba
          Connected.

          SQL> conn sys@ORCLTEST as sysdba
          Enter password:
          Connected.
          I am giving wrong password here ..
          SQL> conn sys/1234@ORCLTEST as sysdba
          ERROR:
          ORA-01017: invalid username/password; logon denied
          Warning: You are no longer connected to ORACLE.

          SQL> conn sys/asdf@ORCLTEST as sysdba
          Connected.

          SQL> startup
          ORA-01081: cannot start already-running ORACLE - shut it down first

          SQL> select name from v$database;
          NAME
          ORCLTEST


          Thanks to all ; who has helped me to solve my problem.
          • 62. Re: Cant connect my database using net service_name
            972355
            Good Morning sb;
            It bothers me that the tnsnames.ora file specifies SERVICE_NAME while listener.ora specifies SID.
            IMO, both should use the same; either both use SID or both use SERVICE_NAME.
            I made some changes in my tnsnames.ora and listener.ora

            i.e. ( i deleted both files and i created new files with new listener name (listener)

            My new tnsnames.ora file  entry i

            # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
            # Generated by Oracle configuration tools.

            ORCLPROD =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = orclprod)
            )
            )
            ORCLTEST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = orcltest)
            )
            )

            My LISTENER.ORA  Entry

            SID_LIST_LISTENER =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME =orcltest)
            (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
            (GLOBAL_DBNAME=orcltest)
            )
            (SID_DESC =
            (SID_NAME = orclprod)
            (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
            (GLOBAL_DBNAME=orclprod)
            )
            )
            LISTENER =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
            )
            )

            Once again please clear my doubt sb
            My problem is solved.  but i can't understand clearly why  first time  i can't connect ?

            I made some changes ( listener name modified , service_name modified , even both database also newly created just now)

            Thanks  sb  and  Mark .Powell and sharma
            • 63. Re: Cant connect my database using net service_name
              972355
              Some additional information  about my new listener
              *$ lsnrctl status*

              LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-MAR-2013 13:03:24

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

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxserver)(PORT=1521)))
              STATUS of the LISTENER
              ------------------------
              Alias LISTENER
              Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
              Start Date 12-MAR-2013 11:39:09
              Uptime 0 days 1 hr. 24 min. 15 sec
              Trace Level off
              Security ON: Local OS Authentication
              SNMP OFF
              Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
              Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
              Listening Endpoints Summary...
              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=1521)))
              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
              Services Summary...
              Service "orclprod" has 2 instance(s).
              Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...
              Instance "orclprod", status READY, has 1 handler(s) for this service...
              Service "orclprodXDB" has 1 instance(s).
              Instance "orclprod", status READY, has 1 handler(s) for this service...
              Service "orclprod_XPT" has 1 instance(s).
              Instance "orclprod", status READY, has 1 handler(s) for this service...
              Service "orcltest" has 2 instance(s).
              Instance "orcltest", status UNKNOWN, has 1 handler(s) for this service...
              Instance "orcltest", status READY, has 1 handler(s) for this service...
              Service "orcltestXDB" has 1 instance(s).
              Instance "orcltest", status READY, has 1 handler(s) for this service...
              Service "orcltest_XPT" has 1 instance(s).
              Instance "orcltest", status READY, has 1 handler(s) for this service...
              The command completed successfully
              • 64. Re: Cant connect my database using net service_name
                EdStevens
                969352 wrote:
                Good Morning sb;
                <snip>
                My problem is solved.  but i can't understand clearly why  first time  i can't connect ?
                see: [url http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/] Help! I can’t connect to my database
                see: [url http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/] Help! I can’t connect to my database (part duex)
                I made some changes ( listener name modified , service_name modified , even both database also newly created just now)

                Thanks  sb  and  Mark .Powell and sharma
                • 65. Re: Cant connect my database using net service_name
                  972355
                  Hello EdStevens ;
                  Yesterday i saw following thread; What can go wrong? => Really i wonder when reading ..
                  Re: TNS:listener does not currently know of service requested in connect

                  Is this reason  why first time i can't connect ?

                  1st time : In tns file , I mentioned service name like *(database global name)*
                  2nd time : In tns file , I mentioned service name as oracle SID_NAME
                  My doubt is ( in your blogs also . service name is not like database global_name)
                  http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/
                  http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/
                  - service name mentioned as curely

                  Always I am keeping touch with OTN. Good Supporters *(sb, edstevens , rp, justin and some others )*

                  Really i feel good. Thanks EdStevens ..
                  • 66. Re: Cant connect my database using net service_name
                    972355
                    what i believe
                    If we make service name is database globalname we can't connect database"

                    This is not workable ( if service_name is database global_name)
                    SQL> conn sys/password@net_service_name as sysdba
                    Thanks ..
                    • 67. Re: Cant connect my database using net service_name
                      sb92075
                      969352 wrote:
                      what i believe
                      If we make service name is database globalname we can't connect database"

                      This is not workable ( if service_name is database global_name)
                      "not workable" is 100% & totally devoid of any actionable detail.
                      We don't know what you did.
                      We don't know how Oracle responded.

                      I don't know what you have.
                      I don't know what you do.
                      I don't know what you see.
                      It is really, Really, REALLY difficult to fix a problem that can not be seen.
                      use COPY & PASTE so we can see what you do & how Oracle responds.

                      >
                      SQL> conn sys/password@net_service_name as sysdba
                      You should *NOT* be connecting "as sysdba" across the network.

                      my car won't go
                      tell me how to make my car go.
                      • 68. Re: Cant connect my database using net service_name
                        TSharma-Oracle
                        I think you guys are going to make a record with maximum number of posts in a single thread ;)
                        • 69. Re: Cant connect my database using net service_name
                          972355
                          Now i entirely changed my database , tnsnames.ora ,listener.ora including listener name. (old files were deleted)
                          It is really, Really, REALLY difficult to fix a problem that can not be seen.
                          But my problem was  solved. Thanks sb for your all replies ..
                          • 70. Re: Cant connect my database using net service_name
                            jgarry
                            TSharma wrote:
                            I think you guys are going to make a record with maximum number of posts in a single thread ;)
                            Not even close ;)

                            I'm still wondering if this a typo from not using cut and paste:
                            oracle 11696 1 0 16:31 ? 00:00:00 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTener_ORcl -inherit
                            • 71. Re: Cant connect my database using net service_name
                              972355
                              I am so sorry .. again disturbing forum members ..
                              i red following thread ..
                              Listener status shows 2 instances when only 1 exists
                              Uwe Hesse said
                              READY means listener knows this because PMON of that instance told him so (dynamic registration)
                              UNKNOWN means listener knows this because of SID_LIST entry in the listener.ora (static registration)

                              when i am checking my listener
                              $ lsnrctl status

                              LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-MAR-2013 00:20:41

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

                              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxserver)(PORT=1521)))
                              STATUS of the LISTENER
                              Alias LISTENER
                              Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                              Start Date 12-MAR-2013 11:39:09
                              Uptime 0 days 12 hr. 41 min. 31 sec
                              Trace Level off
                              Security ON: Local OS Authentication
                              SNMP OFF
                              Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
                              Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
                              Listening Endpoints Summary...
                              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=1521)))
                              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
                              Services Summary...
                              Service "orclprod" has 2 instance(s).
                              Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...
                              Instance "orclprod", status READY, has 1 handler(s) for this service...
                              Service "orclprodXDB" has 1 instance(s).
                              Instance "orclprod", status READY, has 1 handler(s) for this service...
                              Service "orclprod_XPT" has 1 instance(s).
                              Instance "orclprod", status READY, has 1 handler(s) for this service...
                              Service "orcltest" has 2 instance(s).
                              Instance "orcltest", status UNKNOWN, has 1 handler(s) for this service...
                              Instance "orcltest", status READY, has 1 handler(s) for this service...
                              Service "orcltestXDB" has 1 instance(s).
                              Instance "orcltest", status READY, has 1 handler(s) for this service...
                              Service "orcltest_XPT" has 1 instance(s).
                              Instance "orcltest", status READY, has 1 handler(s) for this service...
                              The command completed successfully

                              Please Note : My db (orcltest , orclprod) having single instance only.
                              but listener showing 2 instances.
                              when static registration required and dynamic registration required (listener) ?
                              I feel panic  continuously  asking  here ..

                              Thanks ..
                              • 72. Re: Cant connect my database using net service_name
                                sb92075
                                stop looking for problems where none exist.

                                stop acting like Chicken Little!
                                http://en.wikipedia.org/wiki/Chicken_little

                                post here only when you have error code & message; like ORA-01555 Snapshot Too Old.
                                • 73. Re: Cant connect my database using net service_name
                                  972355
                                  I think and i am sure , my question was not related to Snapshot Too Old.
                                  stop acting like Chicken Little!
                                  - I am sorry sb. Am i getting angry reply ?

                                  I am confused. I am extremely sorry to disturbed you.

                                  Thanks ..
                                  • 74. Re: Cant connect my database using net service_name
                                    507581
                                    do not confused with rac .

                                    it says

                                    Services Summary...




                                    Service "orclprod" has 2 instance(s).
                                    its coming because of listener you have entry with this name GLOBAL_DBNAME
                                    (SID_DESC =
                                          (GLOBAL_DBNAME=orclprod)
                                          (ORACLE_HOME = / / / / /db_1)
                                          (SID_NAME = orclprod)
                                        )
                                    Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...
                                    its coming because of listener you have entry with this name SID_NAME
                                    Instance "orclprod", status READY, has 1 handler(s) for this service...
                                    due to pmon register based on db_name,service_names plus db_domain parameter in spfile and pmon registers in every 60 seconds
                                    or manually you may have tried alter system register;
                                    UNKNOWN

                                    Further, notice that its status is UNKNOWN.
                                    This status of UNKNOWN is the indication that this registration came from the SID_LIST section of listener.ora.
                                    It is unknown because the listener doesn’t make a check to see if there is an instance named “orclprod” broadcasting a service name of “orclprod”.


                                    the READY instance comes from the database having registered with the listener (dynamic registration).



                                    thanks to stevens for nice post
                                    http://edstevensdba.wordpress.com/2011/03/19/ora-12514/