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:

1
2
3
4
5
6
var numtransact varchar2(100)
 
begin
:numtransact:='5.31.1099960';
end;
/

use mos note to force commit transaction:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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

1
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

1
2
3
4
5
6
7
8
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:

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

create asm directory:

1
ASMCMD [+] > mkdir +DATA/u41testdb

*startup dummy instance:

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

*connect auxiliary instance +connect target instance

1
2
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";
1
2
3
4
5
6
7
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

check :

1
2
3
4
5
6
7
8
9
10
11
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[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

    1
    [grid@oel6-2 admin]$ lsnrctl reload

    check one more time:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     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:

    1
    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:

    1
    2
    3
    4
    5
    6
    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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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:

    1
    alter table my_table_redef drop column CUSTOMDESCRIPTION_TMP;

    check:

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

    and dont forget to reset default value

    1
    alter table MY_TABLE modify CUSTOMDESCRIPTION default null;

    segment grown trend

    segment grown trend published on Комментариев к записи segment grown trend нет
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    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

    1
    2
    3
    4
    5
    6
    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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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

    1
    2
    3
    4
    5
    6
    7
    8
    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