[20120112]機器同名問題.txt

昨天,同僚發現克隆的機器沒有修改機器名,出現同名現象.需要找到這些機器修改機器名.理論講可以通過檢視dhcp伺服器定位這些機器.不過因為我們的伺服器都是linux,可以通過oracle也可以找到到.

從toad檢視會話跟蹤得到的腳本,再過濾一些資訊可以獲得哪些機器出現重名:

SELECT ROUND (BITAND (s.ownerid, 65535)) parent_session_sid, ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,

       s.inst_id, s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username, s.command, s.ownerid, s.taddr, s.lockwait,

       s.status, s.server, s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal, UPPER (s.program) program,

       s.TYPE, s.sql_address, s.sql_hash_value, s.sql_id, s.sql_child_number, s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,

       s.prev_child_number, s.plsql_entry_object_id, s.plsql_entry_subprogram_id, s.plsql_object_id, s.plsql_subprogram_id,

       s.module, s.module_hash, s.action, s.action_hash, s.client_info, s.fixed_table_sequence, s.row_wait_obj#,

       s.row_wait_file#, s.row_wait_block#, s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled, s.failover_type,

       s.failover_method, s.failed_over, s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,

       s.current_queue_duration, s.client_identifier, s.blocking_session_status, s.blocking_instance, s.blocking_session,

       s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id,

       s.wait_class#, s.wait_class, s.wait_time, s.seconds_in_wait, s.state, s.service_name, s.sql_trace, s.sql_trace_waits,

       s.sql_trace_binds, stat.cpu - stat.cpu_this_call_start cpu_this_call, stat.cpu, stat.uga_memory, stat.pga_memory,

       stat.commits, stat.rollbacks, p.program "OSProgram", p.spid, p.pid

  FROM gv$session s,

       gv$process p,

       (SELECT   ss.inst_id stat_inst_id, ss.SID stat_sid,

                 SUM (DECODE (sn.NAME, 'CPU used when call started', ss.VALUE, 0)) cpu_this_call_start,

                 SUM (DECODE (sn.NAME, 'CPU used by this session', ss.VALUE, 0)) cpu,

                 SUM (DECODE (sn.NAME, 'session uga memory', ss.VALUE, 0)) uga_memory,

                 SUM (DECODE (sn.NAME, 'session pga memory', ss.VALUE, 0)) pga_memory,

                 SUM (DECODE (sn.NAME, 'user commits', ss.VALUE, 0)) commits,

                 SUM (DECODE (sn.NAME, 'user rollbacks', ss.VALUE, 0)) rollbacks

            FROM gv$sesstat ss, gv$statname sn

           WHERE ss.statistic# = sn.statistic#

             AND ss.inst_id = sn.inst_id

             AND (   sn.NAME = 'CPU used when call started'

                  OR sn.NAME = 'CPU used by this session'

                  OR sn.NAME = 'session uga memory'

                  OR sn.NAME = 'session pga memory'

                  OR sn.NAME = 'user commits'

                  OR sn.NAME = 'user rollbacks'

                 )

        GROUP BY ss.inst_id, ss.SID) stat

 WHERE ((s.username IS NOT NULL) AND (NVL (s.osuser, 'x') 'SYSTEM') AND (s.TYPE 'BACKGROUND'))

   AND (s.ownerid = 2147483644)

   AND ((p.addr(+) = s.paddr) AND (p.inst_id(+) = s.inst_id))

   AND ((stat.stat_inst_id = s.inst_id) AND (stat.stat_sid = s.SID))

獲得SPID也就是程序号如何知道網絡位址呢?想起lsof指令有個-i選項檢視網絡連接配接.

# lsof -i TCP:1521 -nnn | grep   18820

# lsof -i :1521 -nnn | grep   18820

oracle    18820  oracle   14u  IPv4 916466713       TCP 192.168.XXX.XXX:1521->192.168.YYY.YYY:socks (ESTABLISHED)

這樣可以定位是那台機器重名.