Февраль 2016 – bushmelev-aa.ru

CVE-2012-1675 tns poison fix for oracle (rac) >=11.2.0.4

29.02.2016 Bushmelev Anton 1 комментарий

for check i use nmap and
Nmap script to test Oracle DB for “TNS poison vulnerability”

oracle-tns-poison.nse

[code language=”bash”]
local bin = require "bin"
local io = require "io"
local nmap = require "nmap"
local shortport = require "shortport"
local stdnse = require "stdnse"
local table = require "table"

description = [[
Simple module to test Oracle DB server for TNS Poison vulnerability.
Module sends to server a packet with command to register new TNS Listener and check response
To more details about this bug see http://seclists.org/fulldisclosure/2012/Apr/204
]]

—
— @usage
— nmap –script=oracle-tns-poison -p 1521 <host>
—
— @output
— PORT STATE SERVICE REASON
— 1521/tcp open oracle syn-ack
— | oracle-tns-poison: Host is vulnerable!
—
—
— This module is based on sid-brute script. Thanks to author: Patrik Karlsson.
—

author = "Ivan Chalykin"
license = "Same as Nmap–See http://nmap.org/book/man-legal.html"
categories = {"vuln"}

portrule = shortport.port_or_service(1521, ‘oracle-tns’)

local tns_type = {CONNECT=1, REFUSE=4, REDIRECT=5, RESEND=11}

local function create_tns_header(packetType, packetLength)

local request = bin.pack( ">SSCCS",
packetLength + 34, — Packet Length
0, — Packet Checksum
tns_type[packetType], — Packet Type
0, — Reserved Byte
0 — Header Checksum
)

return request

end

local function create_connect_packet()

local connect_data = "(CONNECT_DATA=(COMMAND=service_register_NSGR))"

local data = bin.pack(">SSSSSSSSSSICCA",
308, — Version
300, — Version (Compatibility)
0, — Service Options
2048, — Session Data Unit Size
32767, — Maximum Transmission Data Unit Size
20376, — NT Protocol Characteristics
0, — Line Turnaround Value
1, — Value of 1 in Hardware
connect_data:len(), — Length of connect data
34, — Offset to connect data
0, — Maximum Receivable Connect Data
1, — Connect Flags 0
1, — Connect Flags 1
connect_data
)

local header = create_tns_header("CONNECT", connect_data:len() )

return header .. data

end

action = function(host, port)

local socket = nmap.new_socket()
local catch = function() socket:close() end
local try = nmap.new_try(catch)
local request, response, tns_packet
local indicator

socket:set_timeout(2000)

try(socket:connect(host, port))
request = create_connect_packet( host.ip, port.number)
try(socket:send(request))
response = try(socket:receive_bytes(1))

if response:match("ERROR_STACK") then
indicator="Not Vulnerable"
else indicator="Host is vulnerable!"
end

return indicator
end
[/code]

[свернуть]

check :

nmap --script=oracle-tns-poison.nse -p 1521 oel6-2

Starting Nmap 6.47 ( http://nmap.org ) at 2016-02-29 10:54 MSK
Nmap scan report for oel6-2 (10.0.0.62)
Host is up (0.00018s latency).
rDNS record for 10.0.0.62: oel6-2.djeday.lan
PORT     STATE SERVICE
1521/tcp open  oracle
|_oracle-tns-poison: Host is vulnerable!

Nmap done: 1 IP address (1 host up) scanned in 0.04 seconds

solution is described by MOS: Valid Node Checking For Registration (VNCR) (Doc ID 1600630.1)
How to Enable VNCR on RAC Database to Register only Local Instances (Doc ID 1914282.1)
fixup:
add to grid listener.ora

[grid@oel6-2 admin]$ cat listener.ora
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))		# line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))		# line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))		# line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON		# line added by Agent

VALID_NODE_CHECKING_REGISTRATION_LISTENER=1

VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=1
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(oel6-1.djeday.lan,oel6-2.djeday.lan,oel6-3.djeday.lan)
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=1
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(oel6-1.djeday.lan,oel6-2.djeday.lan,oel6-3.djeday.lan)
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=1
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(oel6-1.djeday.lan,oel6-2.djeday.lan,oel6-3.djeday.lan)

REGISTRATION_INVITED_NODES_LISTENER_SCAN*=( list of public ip’s of all nodes)

reload listener conf

[grid@oel6-2 admin]$ lsnrctl reload

check one more time:

 nmap --script=oracle-tns-poison.nse -p 1521 oel6-2

Starting Nmap 6.47 ( http://nmap.org ) at 2016-02-29 10:55 MSK
Nmap scan report for oel6-2 (10.0.0.62)
Host is up (0.00021s latency).
rDNS record for 10.0.0.62: oel6-2.djeday.lan
PORT     STATE SERVICE
1521/tcp open  oracle
|_oracle-tns-poison: Not Vulnerable

Nmap done: 1 IP address (1 host up) scanned in 0.04 seconds

Job done =)

quick way to update column online with no locks

25.02.2016 Bushmelev Anton Комментариев нет

we can update whole column of big table with no downtime with dbms_redefinition and col_mapping
there is an example:
find table with lots of rows for test and make a copy:

create table my_table nologging as select * from big_table;

create interim table for redefinition and add column to remap data need to be updated, also set default value for column we need to update:

create table my_table_redef as select * from my_table where 1=2;

alter table my_table_redef add ( CUSTOMDESCRIPTION_TMP varchar2(255));
alter table my_table_redef MODIFY (CUSTOMDESCRIPTION default 'A');

select count (*) from my_table; 40947444

do redefinition with remap of CUSTOMDESCRIPTION to CUSTOMDESCRIPTION_TMP:

EXEC DBMS_REDEFINITION.can_redef_table(USER, 'MY_TABLE');


begin
DBMS_REDEFINITION.start_redef_table(
     uname        => USER, 
     orig_table   => 'MY_TABLE',
     int_table    => 'MY_TABLE_REDEF',
     col_mapping =>'ID ID,FIELDNAME FIELDNAME,NEWVALUE NEWVALUE,OLDVALUE OLDVALUE,HISTORY_ID HISTORY_ID,CUSTOMDESCRIPTION CUSTOMDESCRIPTION_TMP, INTERNALVALUE INTERNALVALUE',
     options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
end;   

EXEC DBMS_REDEFINITION.sync_interim_table(USER, 'MY_TABLE', 'MY_TABLE_REDEF'); 

EXEC DBMS_REDEFINITION.finish_redef_table(USER, 'MY_TABLE', 'MY_TABLE_REDEF');

drop old column:

alter table my_table_redef drop column CUSTOMDESCRIPTION_TMP;

check:

select count (*),CUSTOMDESCRIPTION from MY_TABLE group by CUSTOMDESCRIPTION;
40947444	A

and dont forget to reset default value

alter table MY_TABLE modify CUSTOMDESCRIPTION default null;

segment grown trend

16.02.2016 Bushmelev Anton Комментариев нет

column owner format a10
column object_name format a30
column "tablespace name" format a15
select s.s_id ,s.e_id,o.owner , o.object_name , o.subobject_name , o.object_type , 
t.name "tablespace name", s.growth/(1024*1024) "growth in mb", 
(select sum(bytes)/(1024*1024) 
   from dba_segments 
   where segment_name=o.object_name
) "total size(mb)" 
from dba_objects o 
    join 
    (select min(ss.begin_interval_time) as s_id,max(ss.begin_interval_time) as e_id,ts#,obj#, sum(space_used_delta) growth 
      from dba_hist_seg_stat st 
        join dba_hist_snapshot ss on st.snap_id=ss.snap_id
      group by ts#,obj# 
      having sum(space_used_delta) > 0 
      order by 2 desc
    ) s on s.obj#=o.object_id 
    join v$tablespace t on s.ts#=t.ts# 
where rownum < 11 
order by 8 desc;

found at community.oracle.com

show what stored in sql_plan_baseline

11.02.2016 Bushmelev Anton Комментариев нет

SELECT s.sql_handle,s.* FROM dba_sql_plan_baselines s;

SELECT * FROM TABLE (dbms_xplan.display_sql_plan_baseline(‘SQL_0c408547ef15f69d’));

Calculating TPS from ASH via SQL_EXEC_ID or from DBA_HIST_SQLSTAT

08.02.2016 Bushmelev Anton 1 комментарий

save to my blog ( original )

to calculate tps by sql_id we can use sql_exec_id , ash and exel

SELECT TO_CHAR (sample_time, 'HH24:MI'),inst_id,
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_MINUTE
  FROM gv$active_Session_history
 WHERE    sql_id = 'b6asbgkghps5h' and sample_time > sysdate-5/24
group by TO_CHAR (sample_time, 'HH24:MI'),inst_id
order by 1 asc;

or to split results it frame by 10 minutes:

select date#,"'1'" as first_node,"'2'" as second_node from (
SELECT TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60) as date#,instance_number,
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_10_MINUTE
  FROM gv$active_Session_history
 WHERE    sql_id = '77qx41mkwcm92' 
group by TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60),instance_number
order by 1 asc )
pivot 
(
   sum(EXECUTIONS_PER_10_MINUTE)
   for instance_number in ('1'  ,'2' )
) order by date# asc;

one more type of view

select * from (
select s.INSTANCE_NUMBER,s.sql_id,
sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
and st.sql_id=s.sql_id
and s.sql_id='8xjwqbfwwppuf'
-- and st.sql_text not like '/* SQL Analyze%'
--and s.sql_id in ( select p.sql_id from dba_hist_sql_plan p where p.object_name=’OPN_HIS’)
and ss.begin_interval_time > sysdate-14
group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id,s.INSTANCE_NUMBER )
pivot ( sum(execs) for instance_number in (1,2 )
) order by 1;

and result

or even that way

select * from (
select s.sql_id,
sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
and st.sql_id=s.sql_id
-- and st.sql_text not like '/* SQL Analyze%'-- and s.sql_id in ( select p.sql_id from dba_hist_sql_plan p where p.object_name='OPN_HIS')
and ss.begin_interval_time > sysdate-7
group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id )
pivot ( sum(execs) for sql_id in (
'8xjwqbfwwppuf' ,'14crnjtpxh9aa')
) order by 1;

Primary Sidebar