Skip to content

free global transaction table entry \ Unable To Purge Distributed Transaction

free global transaction table entry \ Unable To Purge Distributed Transaction published on 1 комментарий к записи free global transaction table entry \ Unable To Purge Distributed Transaction

while trying to commit distibuted transaction faced with «free global transaction table entry»
solution foud at mos 549307.1
define local_tran_id:

var numtransact varchar2(100)

begin
:numtransact:='5.31.1099960';
end;
/

use mos note to force commit transaction:

set transaction use rollback segment SYSTEM;

delete from sys.pending_trans$
where local_tran_id = :numtransact;
delete from sys.pending_sessions$ where local_tran_id = :numtransact;
delete from sys.pending_sub_sessions$ where local_tran_id = :numtransact;

commit;

insert into pending_trans$ (
LOCAL_TRAN_ID,
GLOBAL_TRAN_FMT,
GLOBAL_ORACLE_ID,
STATE,
STATUS,
SESSION_VECTOR,
RECO_VECTOR,
TYPE#,
FAIL_TIME,
RECO_TIME)
values( :numtransact, 
306206,
'XXXXXXX.12345.1.2.3', 
'prepared','P', 
hextoraw( '00000001' ),
hextoraw( '00000000' ), 
0, sysdate, sysdate );

insert into pending_sessions$
values( :numtransact,
1, hextoraw('05004F003A1500000104'),
'C', 0, 30258592, '',
146
);
commit;

begin
DBMS_TRANSACTION.COMMIT_FORCE (:numtransact);
commit;
end;
/

transaction state changed to forced commit ( or commit forced =) ), after that purge transcation entry

exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.31.1099960'); --local tran id

find query running more than 5 sec

find query running more than 5 sec published on 1 комментарий к записи find query running more than 5 sec 
 select t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
 round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > 5
group by  sql_id, plan_hash_value
 )t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs desc;

Screen Shot 2016-03-28 at 10.16.48

create standby database

create standby database published on 1 комментарий к записи create standby database

*copy passwd file from primary
scp $ORACLE_HOME/dbs/passwd$ORACLE_SID{instance_number}
cp [pwd file from primary] orapw$ORACLE_SID

*create static listener

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = u41testdb.rt.ru)
     (ORACLE_HOME = /oracle/app/base/db/12.1.0.2)
     (SID_NAME = s41testdb)
    )
   )

create audit directory:

mkdir -p /oracle/app/base/admin/s41testdb

create asm directory:

ASMCMD [+] > mkdir +DATA/u41testdb

*startup dummy instance:


echo db_name='db_name' > pfile.ora
startup nomount pfile='pfile.ora'

*connect auxiliary instance +connect target instance

rman target sys/xxxxx@u41testdb auxiliary sys/xxxxx@s41testdb
RMAN> duplicate target database for standby from active database spfile set "db_unique_name"="s41testdb" set "cluster_database"="false" set "audit_file_dest"="/oracle/app/base/admin/s41testdb";


DGMGRL> create configuration u41dg as primary database is u41testdb connect identifier is u41testdb	;
Configuration "u41dg" created with primary database "u41testdb"
DGMGRL> add database s41testdb as connect identifier is s41testdb maintained as physical;
Database "s41testdb" added
DGMGRL> enable configuration;
Enabled.
DGMGRL>

set correct permissions on oracle binary for asm ORA-15025, KFSG-00312, ORA-15081

set correct permissions on oracle binary for asm ORA-15025, KFSG-00312, ORA-15081 published on Комментариев к записи set correct permissions on oracle binary for asm ORA-15025, KFSG-00312, ORA-15081 нет

ORA-15025, KFSG-00312, ORA-15081
Database Creation on 11.2 Grid Infrastructure with Role Separation ( ORA-15025, KFSG-00312, ORA-15081 ) (Doc ID 1084186.1)
su — grid
$ORACLE_HOME/bin/setasmgidwrap o=/oracle/app/base/db/12.1.0.2/bin/oracle

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

CVE-2012-1675 tns poison fix for oracle (rac) >=11.2.0.4 published on 1 комментарий к записи CVE-2012-1675 tns poison fix for oracle (rac) >=11.2.0.4

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

    quick way to update column online with no locks published on Комментариев к записи quick way to update column online with no locks нет

    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

    segment grown trend published on Комментариев к записи segment grown trend нет 
    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

    Calculating TPS from ASH via SQL_EXEC_ID or from DBA_HIST_SQLSTAT

    Calculating TPS from ASH via SQL_EXEC_ID or from DBA_HIST_SQLSTAT published on 1 комментарий к записи Calculating TPS from ASH via SQL_EXEC_ID or from DBA_HIST_SQLSTAT

    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;

    Screen Shot 2016-02-08 at 15.50.27

    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;

    ORA-27300 ORA-27301 ORA-27302 ORA-27157 Database Crash

    ORA-27300 ORA-27301 ORA-27302 ORA-27157 Database Crash published on Комментариев к записи ORA-27300 ORA-27301 ORA-27302 ORA-27157 Database Crash нет

    test upgrade to 12c on RedHat 7.2 faced with 

    Errors in file /oracle/app/base/diag/asm/+asm/+ASM1/trace/+ASM1_smon_11227.trc:
ORA-27300: OS system dependent operation:semctl failed with status: 22
ORA-27301: OS failure message: Invalid argument
ORA-27302: failure occurred at: sskgpwrm1
ORA-27157: OS post/wait facility removed
ORA-27300: OS system dependent operation:semop failed with status: 43
ORA-27301: OS failure message: Identifier removed
ORA-27302: failure occurred at: sskgpwwait1

    WA is to set RemoveIPC=no in /etc/systemd/logind.conf
    ps ( MOS Doc ID 438205.1)

    Primary Sidebar