[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)

这样可以定位是那台机器重名.