1 2 Previous Next 16 Replies Latest reply: Nov 15, 2009 1:39 PM by 661723 RSS

    serial# and SID

    661723
      hi!!!

      I am a DBA funa-1 9i WDP student.I need some help:

      (If i am not wrong)v$session is dynamic view which store some information about oracle.but my question is why serial# and SID is required to kill the session??why not just only SID to kill the session??

      another thing is that a user can use multiple session(as oracle does not restrict to do so).we observed there is a unique-ness for Serial# then why do we need to mention SID to kill the session?
      And my final question is, how the SID and Serial# is generated?
        • 1. Re: serial# and SID
          Pavan Kumar
          Hi,

          You are correct V$session and there is chance that, if you close one particular session and some open new session, then worst case same session might be allocated for next coming session based on the allocation. So, in order to find the object relavent to Session , looking into case of above statement which I said, we need "Session serial number" to identify a session's objects Distinctly or Uniquely.

          So combination of Session ID and Serial I will give you or provide unqiue records or Obejcts.

          I think i have answered your 2 questions. come to SID and Serial Generation it based on the Client connecting to DB and type of Service connected to Dedicted or Shared - AFAIK.

          - Pavan Kumar N
          • 2. Re: serial# and SID
            661723
            hi!!!

            if i am not wrong, a killing session may require to take cold backup from db(may not be the only case although).but the using it i can restrict user to interact with the db or may be to a particular tablespace.and i can take a cold/hot backup.As Serial# is unique why we need session id to kill the session??

            can you explain a bit more(if possible with example)??

            can you show a scenario where serial# is the same but not SID??
            • 3. Re: serial# and SID
              Anand...
              Hi..

              Whenever a user tries to connect to the database, a session is created inside the oracle and is given a particular unique sid and serial# number.These numbers are automatically generated by oracle AFAIK.I have never seen two sessions having same the serial# or sid or both.

              Killing a session doesn't require a cold or any sort of db backup.The moment you kill the session either it gets killed immediately or the session is marked to be killed.The users that can connect to the database can be seen using dba_users view.Each users can have different privileges and roles on db objects and accordingly they are allowed to do dml,ddl ,select on them, and those objects can be on different tablespaces or a single tablespace.


              Anand
              • 4. Re: serial# and SID
                661723
                Anand... wrote:
                Hi..

                Whenever a user tries to connect to the database, a session is created inside the oracle and is given a particular unique sid and serial# number.These numbers are automatically generated by oracle AFAIK.I have never seen two sessions having same the serial# or sid or both.
                (if i am not wrong)suppose hr is an user who created two session say one from isqlplus and another from sql*plus gui(in win xp) .
                i found they are provided a same SID by oracle but not same serial#.so in this case why we need two field as serial# is generating more uniqueness.....
                Killing a session doesn't require a cold or any sort of db backup.
                Actually i want to mean (ie. what my faculty said actually) during backup,killing of session may require.According to him,at first we need to restrict user to interact in db(so that during backup phase they can not do anything transaction with db).then we need to kill the session for the user who are logged in already to db then take the backup.
                >
                >
                Anand
                regards
                • 5. Re: serial# and SID
                  Anand...
                  HI..
                  i found they are provided a same SID by oracle but not same serial#.
                  Well, can you paste the output from v$session.I am not very sure that they would have same sid.
                  ,at first we need to restrict user to interact in db(so that during backup phase they can not do anything transaction with db).then we need to kill the session for the user who are logged in already to db then take the backup.
                  You can put the database in archivelog mode and take the db backup using the RMAN utility of an online database.No need to kill the user sessions and no need to shutdown the database.You can also take hot backup.Although it is best said to take the backup of the databases during non-peak hours.

                  If you want to take the cold backup of the databases, you will need to kill the user sessions,shutdown the database and then take the backup.But then why to do so when oracle has provided utilities to take online backup.

                  RMAN backup can also be taken for the dbs in no-archivelog mode but then the db must be in mount mode for maintaining the consistency.

                  Anand
                  • 6. Re: serial# and SID
                    Aman....
                    >
                    (if i am not wrong)suppose hr is an user who created two session say one from isqlplus and another from sql*plus gui(in win xp) .
                    i found they are provided a same SID by oracle but not same serial#.so in this case why we need two field as serial# is generating more uniqueness.....
                    Show us the output, I logged in SH user from sql*plus in 11106 in RHEL4, I couldn't reproduce. You are wrong IMO,
                     SID    SERIAL# USERNAME
                    ---------- ---------- ------------------------------
                           124         54 SH
                           125         56 SYS
                           126         29 DBSNMP
                           127        203 SYSMAN
                           129         32 DBSNMP
                           130         66 SH
                    >

                    >
                    Actually i want to mean (ie. what my faculty said actually) during backup,killing of session may require.According to him,at first we need to restrict user to interact in db(so that during backup phase they can not do anything transaction with db).then we need to kill the session for the user who are logged in already to db then take the backup.
                    I am not able to get what's teh purpose of killing sessions before taking a backup. Not sure what your faculty was trying to tell or may be you just are confused. For ahot backup, users would keep on working on the database and there is no need to kill their sessions before taking a backup provided you can actually do a hot backup. Why on earth you would like to do this just for taking a backup? Imagine Yahoo, Amazon killing your transactions and sessions in between some time when they are going to take backup, would you accept this ? I guess not! May be its better to quote exactly what your faculty was trying to convey, ask for the clarification of the statement that he has made.

                    HTH
                    Aman....
                    • 7. Re: serial# and SID
                      Pavan Kumar
                      Hi,

                      Please check with faculty clearly and what he is saying to do so. It's not at all required to kill a session. Check with him and let us know.

                      - Pavan Kumar N
                      • 8. Re: serial# and SID
                        722357
                        A test case that you can easily see with same sid, but different serial# is in a RAC environment

                        In the following example, we are in 10.1.0.4 RAC system.

                        Without serial#, how you could kill session with sid 120 or 124?
                        select sid,serial# from gv$session order by sid;   
                        
                        SID                                    SERIAL#                                
                        111                                    5941                                   
                        113                                    26615                                  
                        114                                    20569                                  
                        115                                    8716                                   
                        116                                    2698                                   
                        120                                    8238                                   
                        120                                    64971                                  
                        121                                    29721                                  
                        124                                    61589                                  
                        124                                    13134                                  
                        125                                    2237   
                        Bye
                        • 9. Re: serial# and SID
                          661723
                          1)ok i agree with you.my faculty may be talking about cold backup.may be it may not require for backup but for some instance killing a session may require.isn't it?


                          2)one more thing is that wdp 9i fundamental 1 doesn't contain RMAN tool.

                          3)if i am not wrong,the serial# and SID both are generated randomly by DB.i did not spool and spool off in that class but i got two same number in that class.as ottocolori says:
                          SID                                    SERIAL#  
                          120                                    8238                                   
                          120                                    64971                                  
                                                       
                          124                                    61589                                  
                          124                                    13134                                  
                          yes i can't kill a session without serial#.but can't i do it without SID?


                          regards
                          • 10. Re: serial# and SID
                            722357
                            Hi,

                            my example comes from a RAC system where there are two "collaborating" instances on the same database, so you can easilly see two sessions with same sid, as sid

                            is reassigned continuously, and so if a session quits, next connecting session might have the same sid assigned, while serial# is a sort of "counter, which is

                            incremented each time a sid id reused by anotehr session" (from Oracle9i Database Performance Tuning Guide and Reference), so it is like a "measure" of how many

                            times a sid is reused. Moreover v$session.audsid, too, can be used to identify a session
                            SQL> select sid, serial# from v$session where program like '%Pl%';
                             
                                   SID    SERIAL#
                            ---------- ----------
                                   139      37758
                                   142      41671
                             
                            SQL> select sid, serial# from v$session where program like '%Pl%';
                             
                            select sid, serial# from v$session where program like '%Pl%'
                             
                            Not logged on                                 <---------- Here I Logged off
                             
                            SQL> select sid, serial# from v$session where program like '%Pl%';
                             
                                   SID    SERIAL#
                            ---------- ----------
                                   139      37768
                                   142      41683                          <----------- System regave me same sid, but SERIAL# incremented
                            I can only guess that the couple sid, serial# gives you more "precision" in getting the right session, and of course it is not, in absolute, the only method to

                            identify a session, but it is the method oracle developers implemented. They are just assuring us that we will not kill wrong sessions!!


                            Bye
                            • 11. Re: serial# and SID
                              UweHesse
                              Question:
                              Why do I have to provide both, SID and SERIAL# in order to kill a session although SID is itself unique for the currently connected sessions (at one instance)?

                              Answer:
                              Imagine you want to kill a particular session for whatever reason. You spot the SID of that session. Then you kill it. In the meantime (during your determination of the right SID), the user disconnected an another user connected, getting the same SID.
                              You would incidentally kill his session, would the SID be sufficient to do that. But the new session as another SERIAL#. You have to provide both SID and SERIAL#, so you won't kill the new session in this scenario (and you would not annoy your end users by killing their sessions without a cause).

                              Kind regards
                              Uwe

                              http://uhesse.wordpress.com
                              • 12. Re: serial# and SID
                                661723
                                yes i can't kill a session without serial#.------------>its nice.i got it.

                                so why on earth i need SID???

                                as you said serial# is unique and get incremented each time,so why on earth i need SID??

                                Edited by: mango_boy on Nov 15, 2009 9:58 AM
                                • 13. Re: serial# and SID
                                  UweHesse
                                  >
                                  .. as you said serial# is unique and get incremented each time ...
                                  >

                                  Where did I say that? Take my above argumentation and substitute SID with SERIAL# and vice versa, in order to understand why it would not be a good idea if you were able to kill a session by just providing a SERIAL#.

                                  Kind regards
                                  Uwe

                                  http://uhesse.wordpress.com
                                  • 14. Re: serial# and SID
                                    Hans Forbrich
                                    mango_boy wrote:
                                    yes i can't kill a session without serial#.------------>its nice.i got it.

                                    so why on earth i need SID???
                                    Please note that you are looking at one (or very few) specific configuration of Oracle session interaction and probably one specific operating system.

                                    WHen you explore ALL possibilities (Windows, Linux, *Nnix, VMS, mainframe, RAC, dedicated server, shared server, IPC, Networking), you will find that both SID and SERIAL can be reused over time.  Even the combination may be reused, but the probablilty of hitting that seems to be very low.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                    1 2 Previous Next