?

Log in

No account? Create an account
StarWoofy's lair
 
[Most Recent Entries] [Calendar View] [Friends]

Below are the 20 most recent journal entries recorded in starwoofy's LiveJournal:

[ << Previous 20 ]
Wednesday, October 17th, 2018
6:14 am
Боролся с последствиями на 12.2+ - запилил памятку на рисоваче


How to Restore ASM Based OCR After Complete Loss of the CRS Diskgroup on Linux/Unix Systems (Doc ID 1062983.1)
и бекапов ocr не было. Совсем.

ps. Оригинальный GI разворачивал не я. Моя - не ломать. Моя - чинить.

pps. Список ресурсов, vip, ip, "и прочая и прочая", вытащил из предыдущей версии gi 11.2 дампом.
Saturday, October 13th, 2018
1:26 pm
Особенности кулинарии
Что бы я ни начинал варить - получается лагман
Friday, October 12th, 2018
9:05 pm
DBMS_TRANSACTION
Если распределенную транзакцию нельзя отменить, надо её подменить и отменить!


In-doubt Transaction in Prepared State in DBA_2PC View – ORA-30019, ORA-06510
20
Sunday
May 2012
Posted by shrikant rao in Troubleshooting ≈ 6 Comments
1 Vote

DB Version: 9.2.0.8

Recently I encounter an issue with in-doubt distributed transaction. A user had executed a process from application end which connects to another Database to fetch records. For some reason the session was killed. There was no process running at background but still when user executed the same for second time, he received error pointing to the same LOCAL_TRAN_ID ‘10.19.6462883’.

ORA-01591: lock held by in-doubt distributed transaction 10.19.6462883

I checked for entry with the local tran id and it was present.

SQL> column database format a22
SQL> column global_tran_id format a25
SQL> column global_name format a22
SQL> SELECT * from global_name;

GLOBAL_NAME
----------------------
DBSIT

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID GLOBAL_TRAN_ID TO_CHAR(FAIL_TIME,'DD-MON-YYYY STATE MIX
---------------------- ----------------------------- ------------------------------ ---------------- ---
22.38.4444334 DBSIT.e9487d1c.22.38.4444334 11-feb-2012 11:04:03 collecting no
10.19.6462883 DBSIT.e9487d1c.10.19.6462883 30-apr-2012 00:00:00 collecting no
1.44.3809621 DBSIT.e9487d1c.1.44.3809621 07-oct-2011 16:39:17 collecting no
3.47.5102063 DBSIT.e9487d1c.3.47.5102063 05-aug-2011 17:41:10 collecting no

SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID IN_ I DATABASE
---------------------- --- - ----------------------
22.38.4444334 in N
10.19.6462883 in N DBSIT
1.44.3809621 in N
1.44.3809621 in N DBSIT
1.44.3809621 in N X
3.47.5102063 in N
3.47.5102063 in N DBSIT
3.47.5102063 out N X
3.47.5102063 out N X
3.47.5102063 out N X
3.47.5102063 out N DBS
3.47.5102063 out N X
3.47.5102063 out N X
3.47.5102063 out C X
3.47.5102063 out N X
1.44.3809621 out N X
1.44.3809621 out N X
1.44.3809621 out N X
1.44.3809621 out N X
1.44.3809621 out N X
1.44.3809621 out N DBS
1.44.3809621 out N X
1.44.3809621 out N X
10.19.6462883 out C X
22.38.4444334 out N X
22.38.4444334 out N X
22.38.4444334 out N X
22.38.4444334 out N DBS
10.19.6462883 out N X

29 rows selected.
Tried executing rollback force for the specific transaction ID. But that didn’t help, it got hang.

rollback force '10.19.6462883';
Then tried to purge the lost entry, but encounter with ORA-30019.

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883'); END;

*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY is not supported when using AUM. This is due to fact that “set transaction use rollback segment…” cannot be done in AUM.

This can only be resolved with following process,

1.) alter session set “_smu_debug_mode” = 4;
2.) commit; — so that the call to DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY is the first
— step of the transaction
3.) execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘local_tran_id’);” — Oracle Doc

NOTE:”_smu_debug_mode” can not set with alter session on 10g.
If you try you will get error ORA-02096
Thus in 10g onwards use ALTER SYSTEM for setting _smu_debug_mode
NOTE:
In 9.2 alter session works
alter session set “_smu_debug_mode” = 4;

But in my case this didn’t help me. Still received the error but this time ORA-06510.

SQL> alter session set "_smu_debug_mode" = 4;

Session altered.

SQL> commit;

Commit complete.

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883'); END;

*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_TRANSACTION", line 94
ORA-06512: at line 1
Later I came across Oracle MOS ID “Rollback Force In-doubt Transaction in Prepared State Fails With ORA-02075 [ID 1413375.1]”

Checked out the lost entry transaction details.

SQL> SELECT * FROM sys.x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;/* <== This value is the txn undo seg# that is displayed in the first part of the transaction ID */

ADDR INDX INST_ID KTUXEUSN KTUXESLT KTUXESQN
---------------- ---------- ---------- ---------- ---------- ----------
KTUXERDBF KTUXERDBB KTUXESCNB KTUXESCNW KTUXESTA
---------- ---------- ---------- ---------- ----------------
KTUXECFL KTUXEUEL KTUXEDDBF KTUXEDDBB KTUXEPUSN KTUXEPSLT
------------------------ ---------- ---------- ---------- ---------- ----------
KTUXEPSQN KTUXESIZ
---------- ----------
00000001103944EC 19 1 10 19 6462883
71 132239 3672859349 49 PREPARED
SCO|COL|REV|DEAD 4 71 132239 0 0
0 1

SQL> select * from sys.pending_trans$ where local_tran_id = '10.19.6462883';

SQL> select * from sys.pending_sessions$ where local_tran_id = '10.19.6462883';

LOCAL_TRAN_ID SESSION_ID BRANCH_ID I TYPE# PARENT_DBID PARENT_DB DB_USERID
---------------------- ---------- --------------- - ---------- ---------------- ---------------------- ----------
10.19.6462883 1 0000 N 0 DBSIT DBSIT 39

SQL> select * from sys.pending_sub_sessions$ where local_tran_id ='10.19.6462883';

LOCAL_TRAN_ID SESSION_ID SUB_SESSION_ID I DBID LINK_OWNER DBLINK BRANCH_ID SPARE
---------------------- ---------- -------------- - ---------------- ---------- --------------- --------------- ------
10.19.6462883 1 15 N 878b3161 39 INDB 0A001300A39D620 0010F
10.19.6462883 1 14 C 878b3161 39 IBM 0A001300A39D620 0010E

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA Status,KTUXECFL Flags FROM sys.x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ---------------- ------------------------
10 19 6462883 PREPARED SCO|COL|REV|DEAD
Now when the purging process doesn’t provide any solution. We need to proceed with below steps to resolve the issue. Only thing we need to do is delete the record from below 3 tables and then try to remove the lost ID.
But before deleting the records, I will suggest to take a backup of record, by backup I mean record details for that particular id.

Getting the details:

select * from sys.pending_trans$ where local_tran_id = ‘10.19.6462883’;
select * from sys.pending_sessions$ where local_tran_id = ‘10.19.6462883’;
select * from sys.pending_sub_sessions$ where local_tran_id =’10.19.6462883′;

SQL> alter system disable distributed recovery;

SQL> delete from sys.pending_trans$ where local_tran_id = '10.19.6462883';

1 row deleted.

SQL> delete from sys.pending_sessions$ where local_tran_id = '10.19.6462883';

1 row deleted.

SQL> delete from sys.pending_sub_sessions$ where local_tran_id ='10.19.6462883';

2 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system enable distributed recovery;
Now we can cross verify whether the ID still exist in any of the 3 tables. This shows the trans ID is not present in the Database. But If we check the sys.x$ktuxe table the entry will still be available.

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA Status,KTUXECFL Flags FROM sys.x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ---------------- ------------------------
10 19 6462883 PREPARED SCO|COL|REV|DEAD
Lets try to purge the trans ID.

SQL> Commit force '10.19.6462883';
Commit force '10.19.6462883'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 10.19.6462883

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883'); END;

*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
By googling in more deep, I came across a blog. It was said in such kind of scenario, we need to insert dummy record in all the 3 tables and try to purge the in-doubt transaction.
Well I even tried that. But unfortunately it made my instance terminate. I inserted dummy value in the tables and try to purge which didn’t help. So we tried with a Database bounce.
So Database stared, but it was hardy 30 seconds and the instance terminated. Inserting dummy value in the tables.

SQL> insert into sys.pending_trans$ (local_tran_id,GLOBAL_TRAN_FMT,state,status,SESSION_VECTOR,RECO_VECTOR,FAIL_TIME,RECO_TIME) values ('10.19.6462883',1,'A','A','A','A','30-APR-2012','30-APR-2012');

1 row created.

SQL> insert into sys.pending_sessions$ (local_tran_id,SESSION_ID,BRANCH_ID,INTERFACE,DB_USERID) values ('10.19.6462883',1,'A','A',1);

1 row created.

SQL> insert into sys.pending_sub_sessions$ (local_tran_id,SESSION_ID,SUB_SESSION_ID,INTERFACE,DBID,LINK_OWNER,DBLINK) values ('10.19.6462883',1,1,'A',1,1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> Commit force '10.19.6462883';
Commit force '10.19.6462883'
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [18124], [], [], [], [], [], [], []
That is why I stated to keep previous record before going ahead with the deletion. Now just insert the actual records in the tables and try to purge the trans ID.

insert into sys.pending_trans$ values ('10.19.6462883',306206,'DBSIT.e9487d1c.10.19.6462883','','','prepared','P','null','00000001','00000001',0,'4-30-2012','','30-04-2012','BIF','ASPNET','BAN107693','KBDT07693','214126256853',,'',,'');

insert into sys.pending_sessions$ values ('10.19.6462883',1,'0000','N',0,'DBSIT','DBSIT',39);

insert into sys.pending_sub_sessions$ values ('10.19.6462883',1,15,'N','878b3161',39,'INDB','0A001300A39D6200010F','');
insert into sys.pending_sub_sessions$ values ('10.19.6462883',1,14,'C','878b3161',39,'IBM','0A001300A39D6200010E','');
Now lets try the whole process for purging the in-doubt transaction.

SQL> alter session set "_smu_debug_mode" = 4;

Session altered.

SQL> commit;

Commit complete.

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883');

PL/SQL procedure successfully completed.

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID GLOBAL_TRAN_ID TO_CHAR(FAIL_TIME,'DD-MON-YYYY STATE MIX
---------------------- ----------------------------- ------------------------------- ---------------- ---
22.38.4444334 DBSIT.e9487d1c.22.38.4444334 11-feb-2012 11:04:03 collecting no
1.44.3809621 DBSIT.e9487d1c.1.44.3809621 07-oct-2011 16:39:17 collecting no
3.47.5102063 DBSIT.e9487d1c.3.47.5102063 05-aug-2011 17:41:10 collecting no

SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID IN_ I DATABASE
---------------------- --- - ----------------------
22.38.4444334 in N
1.44.3809621 in N
1.44.3809621 in N DBSIT
1.44.3809621 in N X
3.47.5102063 in N
3.47.5102063 in N DBSIT
3.47.5102063 out N X
3.47.5102063 out N X
3.47.5102063 out N X
3.47.5102063 out N DBS
3.47.5102063 out N X
3.47.5102063 out N X
3.47.5102063 out C X
3.47.5102063 out N X
1.44.3809621 out N X
1.44.3809621 out N X
1.44.3809621 out N X
1.44.3809621 out N X
1.44.3809621 out N X
1.44.3809621 out N DBS
1.44.3809621 out N X
1.44.3809621 out N X
22.38.4444334 out N X
22.38.4444334 out N X
22.38.4444334 out N X
22.38.4444334 out N DBS

26 rows selected.

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA Status,KTUXECFL Flags FROM sys.x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;

no rows selected

SQL>
Well at last, the distributed transaction ‘10.19.6462883’ has been purged.

In normal scenario, DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY works fine.
How to Purge a Distributed Transaction from a Database [ID 159377.1]

But if it doesn’t help then this is the way to make it work.

For more reference can go through below MOS ID

Manually Resolving In-Doubt Transactions: Different Scenarios [ID 126069.1]
Rollback Force In-doubt Transaction in Prepared State Fails With ORA-02075 [ID 1413375.1]
ORA-02062: DISTRIBUTED RECOVERY RECEIVED DBID 01010101, EXPECTED DB21020 [ID 1077215.1]
Sunday, October 7th, 2018
1:23 pm
Saturday, October 6th, 2018
12:19 pm
Thursday, October 4th, 2018
10:02 pm
Инфлюэнцу подцепил
Либо в опен спейсе, либо в метро (более вероятно).

Лечусь "традиционно" - римантадин, фталазол, акт. уголь, чай с мёдом и вареньем.

https://en.wikipedia.org/wiki/Influenza
Wednesday, October 3rd, 2018
8:23 pm
Sunday, September 30th, 2018
5:58 pm
Похоже ТЦ Коламбус сделает всю уличную парковку - платной
http://www.trc-columbus.ru/contacts/parking/
http://www.trc-columbus.ru/news/news/khoroshie-novosti-parkovka-stanet-eshche-udobnee/

Территория на ремонте - а ведь там круглосуточно месяцами стояли/хранились машины из области. Скорее всего будет как обычно после 2х бесплатный часов - 100р час.
5:52 pm
Обновил зимний беговой комплект
Почти весь.
Недорогие бренды Glissade и Termit - в спортмастере - для зимнего бега использую горнолыжный костюм - уделывается за 1-2 сезона.
Ещё 2*кроссовки и новое термобельё надо, но это не к спеху  - месяц ещё точно не потребуются.

5:23 pm
8:35 am
Интересно, что Лавров скажет на такое
https://tvzvezda.ru/news/vstrane_i_mire/content/201809300217-qufn.htm
США могут устроить морскую блокаду России, чтобы воспрепятствовать поставкам ее нефти и газа на Ближний Восток. Об этом заявил министр внутренних дел США Райан Зинке, сообщает Washington Examiner.
7:31 am
Отопление
Гы. В четверг вечером "подразнили" - включили. В пятницу утром уже не было.
В субботу в обед оставил заявку "нет отопления" в диспетчерском центре (после 15:00 уже было не прозвониться).

Wednesday, September 26th, 2018
9:54 pm
Индия - родина слонов!
Наши добрые индийские друзья напилили девелоперский патч под 12.2.
Всего-то с месяц неспешной переписки - и готово.
Архитекторы будут счастливы. Clonedb экономит нервы и дисковое пространство.
:-)


к заметке:
oracle 12.2 psu от января и до апреля включительно - clonedb на hpux/linux
Saturday, September 22nd, 2018
7:00 pm
1:34 pm
ВВС
Съездил в музей в Монино
http://moninomuseum.ru

Пробки на МКАД и вокруг БалашИхи не помешали после поездки,  с удовольствием погулять по лётному полю и посмотреть экспонаты в ангарах.
Погода отличная. Нащёлкал зеркалкой эффектных ракурсов!

В ТУ-144 не пускают - рестраврация.

Магнитиков полный карман привёз.
:-)
Friday, September 21st, 2018
9:09 pm
Saturday, September 15th, 2018
12:56 pm
Чуть не получилось


ps. Утиный бульон спас. Успел, до того, как он в угольно-прожаренное второе блюдо не превратился.
12:36 pm
Благоустройство района
Сразу после чемпионата - усиленно начали ремонтировать дороги.
У нас делают узенький тротуар между "большим тортуаром" + газоном и парковочными местами+дорогой вокруг квартала.
Давно пора - зимой там замучаешься сугробы перелезать.
Ещё хорошо бы сделали шлагбаум на въезд в квартал и платные парковки.
А то у нас там "автобаза" из минифур и транспортеров (таблички к знаку парковки "только легковые автомобили" текстовые висят - но грузовики оттуда редко эвакуируют).
В общем есть поле для работы (несколько раз вызывал эвакуаторы).
12:20 pm
12:08 pm
В дополнение про "гиппопотама"
https://starwoofy.livejournal.com/743488.html
https://www.amazon.com/gp/product/B00CRQUKQ4/ref=oh_aui_detailpage_o00_s00?ie=UTF8&psc=1

проверил пока в городе

1. На торпеде машины не лепится никуда, хоть переобтирайся спиртовой салфеткой.
2. Под собственным весом пока не падал - особо не "ездит" (липучая силиконовая площадка не даёт скатываться, "мешочек" с песком в основании - тяжеленький)
3. Клеешняяя открывается под 6" с трудом, надо привыкнуть.
4. Угол обзора при засветке безобразный (ну, строго говоря при засветке оно всегда безобразно)

Продолжаю тестирование.
[ << Previous 20 ]
About LiveJournal.com