Tag: lock

Database, Errors and Bugs

ORA-02056: 2PC: k2lcom: bad two-phase command number rdonly from coord

 , ,

Hi all,

So, you found this error?

Well, I received this in a client environment. After checking MOS ORA-02056: 2PC: K2gCheckGlobalCommit: Bad Two-phase Command Number 1 From Coord (Doc ID 2498134.1), which points to a known issue when dealing with committing transactions as per Bug 22016049 I noticed my problem could be different…

My real root cause is my transaction being done on a remote object and when checking on remote database I found pending distributed transactions.

For more informations about distributed transactions, please check: https://docs.oracle.com/database/121/ADMIN/ds_txns.htm#ADMIN12211

GUID-D521B4E9-E916-4B02-8B0E-4FAF4DC61423-default

So, if you getting this situation, before planning and applying the patch I suggest you:

  • Check if your process is not working on remote databases.
  • Check for any possible distributed remote transactions.

How? Easy check (on remote db):

select * from dba_2pc_pending;

I hope it helps!

PS NOTE: I just found this very good and complete post by Rodrigo Mufalani on the same topic: ORA-01591: lock held by in-doubt distributed transaction

Have a look for an Action Plan!
Cheers!

Database, SQL | PLSQL

Oracle: Explicitly Lock a Table

 ,

Hello all,
So I client had a process that kept need big chunks locks on a table. Problem is that due application use this lock occasionally failed (lock timeout) and the whole process fail. So how to guarantee this?

Using explicit locks for the table before starting the procedure, with command below:

LOCK TABLE GREPORA.GREP_TABLE IN SHARE MODE;

This lock is released as soon as the session commit or rollback transaction in the current session. Killing session means an implicit rollback, as usual.

The SHARE permits concurrent queries but prohibits updates to the locked table.

Nice right?
Hope it helps you too!

Database, Performance, RAC | Data Guard, SQL | PLSQL

Oracle: “Invisible” Locks!

 , ,

Have you ever heard:
“My session was killed. I’m trying again. There is no locks. But My SQL doesn’t run!”

By default, when a session is disconnected from Oracle, the SQLs uncommitted are undone (rollback). So, in case of a long routing there is a probably a rollback in place.

How Oracle rollback take that long? Well, it’s Oracle mechanism. The RDBMS basically assume you know what your are doing and start writing the new blocks so the commit will be very quick. Which, however, is bad in case of rollbacks.

How to check if this is your case?

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD'
4 /

KTUXEUSN Time KTUXESIZ KTUXESTA
---------- -------------------- ---------- ----------------
31 07-APR-2018 16:48:53 17705 ACTIVE
48 07-APR-2018 16:48:53 108856 ACTIVE

Hm.. What can I do? Not much really, we need to wait the rollback to complete.
One thing that can be done to increase the rollback is changing the fast_start_parallel_rollback parameter, as per:

SQL> show parameters roll

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> alter system set fast_start_parallel_rollback=HIGH;

System altered.

Hope it helps!
Cheers!

Database, Errors and Bugs, MySQL

MySQL: ERROR 1356 (HY000): View ‘sys.innodb_lock_waits’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

 , , , ,

Hello all!
So, I was messing around with a Dev environment to simulate some strategies, doing some tests, and after a mysqldump exporting, dropping, and reimporting my whole database got this error.

If you arrived here from Google, Is this your case?

Well, this happens for a simple reason. The routines are not exported by mysqldump by default. Why? I don’t know either, this is an abomination to me. This would be very cheap to be the default right?

Happens that even for a new database, when importing a dump generated with –all-databases the sys/information_schema routines are deleted by the restore process. Well, at least this is recognized as a Bug (Bug 83259).

I noticed that when trying to query a sys view to get locks info, as per:

root@localhost-(none)-13:46:26>SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id,
-> blocking_pid, blocking_query FROM sys.innodb_lock_waits;
ERROR 1356 (HY000): View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Hm.. Weird message, let’s check for information_schema objects:

root@localhost-(none)-13:46:39>select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

Ok, so, if you are already in this mess, how to quick recover?
Well, just run the mysql_upgrade (assuming you are in the top version/repository you have available on server):

root@localhost-(none)-13:47:34>exit
Bye
[root@greporasrv ~]# mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.1).
Found 0 sys functions, but expected 22. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
mysqlslap.t1 OK
[... my other databases...]
sys.sys_config OK
world.city OK
world.country OK
world.countrylanguage OK
Upgrade process completed successfully.
Checking if update is needed.

Fine, lets test it:

[root@greporasrv ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost-(none)-13:49:44>select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys';
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.00 sec)

root@localhost-(none)-13:49:53>

Hope it helps.
Cheers!

Database, Enterprise Manager

OEM: The number of hanging transactions are hang_trans is %

 , ,

Hi all!
So, today is quickie one, just to make the links. Seems this message from OEM is not clear enough for some people, specially regarding non-specialists in Oracle: This means something is in lock in your database!

If this is the case, contact a DBA.

If you ARE a DBA, you may want to read this post about easy locating and solving locks: Solving Simple Locks Through @lock2s and @killlocker.

Also, if the session if from DBLink, is always useful to read this: Lock by DBLink – How to locate the remote session?

There is also some additional/specific material about some issues and bugs in this regard here: Tag: LOCK.

I hope it helps!
Cheers!

Backup and Restore, Database, Errors and Bugs, Performance, RAC | Data Guard, Tools

DFS Lock Handle During RMAN Backup on Standby Database

 , , , ,

Hi all!
Some time ago I faced some session on wait event DFS Lock Handle during a RMAN Backup on Standby Database. Btw, running backup on a Standby is a very interesting approach to avoid running backups on primary, so all nodes can be fully dedicated to application ends.

Turn that in my situation I noticed there was a lock with my apply process. The fix? Quite simple: Cancel apply process, run backup, and restart apply again. In my case, using DG Broker:

dgmgrl
connect / 
show configuration
show database 
edit database  set state = 'apply-off';

— Run Backup

edit database set state = 'apply-on';

 

Hope it helps!
Cheers!

Database, Errors and Bugs, Performance

Oracle Database Lock Performance / False Locks

 , ,

Hello all!
This is a very interesing one… what about database taking too long or even showing locks that don’t really exists?
I faced some weird situations related to the size of DBMS_LOCK_ALLOCATED.

After some research, seems root cause is descripted in MOS DBMS_LOCK_ALLOCATED Table Keeps Growing Eventually Depleting Tablespace (Doc ID 1054343.6).

The issue is:
– DBMS_LOCK_ALLOCATED is the table that keep all locks on database.
– This table keeps growing even though many of the locks are probably no longer used.
– Upon checking the EXPIRED column, it reveals that the locks’ expiration date is a year or more in the future.

Besides the mentioned “storage space issue” in MOS Doc, there is also the situation that we have actually 1073741823 “lockhandlers” available. And what if this ‘not releasing’ cause we run out of handlers? Unlike, but possible.
Having a big table can also cause bad performance on DBMS_LOCK.REQUEST, that is the basic mechanism for locks (passing the handler, lockmode, timeout and release instructions).

More“Oracle Database Lock Performance / False Locks”

Database, SQL | PLSQL

Oracle Database 9i: Where is my SQL_ID? Which SQL is in lock?

 , , ,

Struggling with that, right?
As you know, in Oracle Database 9i the view V$SESSION doesn’t have SQL_ID column…
So how to map SQLs in my database? And, for example, how to get the SQLs causing a lock?

In the end of the day, the SQL_ID is only a representation of the hash_value of an SQL. You can even make the translation from SQL_ID to Hash Value as you can check on this post by Tanel Poder.

Ok, but I have to map which sql is causing the lock in my 9i database, how can I do that?

Here it goes:

If session status is ACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.SQL_ADDRESS = s1.ADDRESS

If session status is INACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.prev_sql_addr = s1.ADDRESS

You’re welcome! 😉

See you next week!

Database, Performance, SQL | PLSQL

Solving Simple Locks Through @lock2s and @killlocker

 , ,

Hi guys!
This post is to show the most simple and most common kind of locks for objects and the simpliest way to solve it (killing the locker). 🙂
It’s so common that I scripted it. Take a look:

greporadb> @lock2s
 Inst        SID SERIAL# UserName  STATUS   LOGON_TIME          LMODE  REQUEST LC_ET TY       ID1        ID2       CTIME LOCKWAIT         EVENT                                                                                           
----- ---------- ------- --------- -------- ------------------- ------ ------- ----- -- ---------- ---------- ---------- ---------------- -----------------------------------                                                             
    1        354   18145 MATHEUS   ACTIVE   17/06/2016 14:25:19 X      NONE    4032  TX     393238     424490        715 00000000DB0DF900 enq: TX - row lock contention                                                                   
    1        169   25571 GREPORA   ACTIVE   17/06/2016 14:22:48 NONE   X        714  TX     393238     424490        714 00000000DB0D5ED8 enq: TX - row lock contention                                                                   
    1        252   63517 MATHEUS   INACTIVE 17/06/2016 14:17:49 X      NONE     714  TX     655363    1550347       4195                  SQL*Net message from client                                                                     
    1        846   65011 GREPORA   ACTIVE   17/06/2016 14:20:18 NONE   X       4075  TX     655363    1550347        715 00000000DB0ECB88 enq: TX - row lock contention                                                                   
    1        354   18145 GREPORA   ACTIVE   17/06/2016 14:25:19 NONE   S       4032  TX     655363    1550347        715 00000000DB0DF900 enq: TX - row lock contention                                                                   
5 rows selected.

You can identify the Locker by LMODE column. And all his Waiters by REQUEST column marked by not ‘NONE’, below each Locker…

So, let’s kill the lockers:

greporadb> @killlocker
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'                                                                                                                                                                                                                                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                       
alter system kill session '252,63517' immediate;                                                                                                                                                                                                                                                                                                                                                       
alter system kill session '354,18145' immediate;                                                                                                                                                                                                                                                                                                                                                       
2 rows selected.
greporadb> alter system kill session '252,63517' immediate;      
System altered.
greporadb> alter system kill session '354,18145' immediate;      
System altered.
greporadb> @lock2s
no rows selected

Solved!
My magic scripts? Here it goes:

get lock2s.sql:

set lines 10000
set trimspool on
col serial# for 999999
col lc_et for 999999
col l1name for a50
col lmode for a6
col username for a25
select /*+ rule */
distinct b.inst_id,a.sid,b.serial#,b.username,b.status,
--b.audsid,
--b.module,
--b.machine,b.osuser,
b.logon_time,
decode(lmode,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',lmode) lmode,
decode(request,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',request) request,
b.last_call_et LC_ET,a.type TY,a.id1,a.id2,
d.name||'.'||c.name l1name,a.ctime,b.lockwait,b.event
--distinct b.inst_id,a.sid,b.username,a.type,d.name||'.'||c.name l1name,a.id1,a.id2,
--decode(lmode,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',lmode) lmode,
--decode(request,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',request) request,a.ctime,b.lockwait,b.last_call_et
from gv$lock a, gv$session b,sys.obj$ c,sys.user$ d,(select a.id1 from gv$lock a where a.request  0) lock1
where a.id1    = c.OBJ# (+)
and a.sid      = b.sid
and c.owner#   = d.user# (+)
and a.inst_id=b.inst_id
and b.username is not null
and a.id1 = lock1.id1
order by id1,id2, lmode desc
/

get killlocker.sql:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' 
from v$session where sid in (select BLOCKING_SESSION from v$session where BLOCKING_SESSION is not null);

Now you can put in your Linkedin you are a JR DBA… 😛
haha

Matheus.

Database, Errors and Bugs, Performance, SQL | PLSQL

Lock by DBLink – How to locate the remote session?

 , , , ,

And if you identify a lock or other unwanted operation by a DBLink session, how to identify the original session in remote database (origin dabatase)?
The one million answer is simple: by process of v$session. By the way, looks like is easier than find the local process (spid)… Take a look in my example (scripts in the end of post):

dest> @sid
Sid:10035
Inst:1
SEQ# EVENT MODULE STATUS SID SERIAL# INST_ID
----- --------- ---------- ---------- ---------- ---------- ----------
29912 SQL*Net message from client oracle@origin2(TNS V1-V3) INACTIVE 10035 35 1
dest> @spid
SPID SID PID PROCESS_FOR_DB_LINK MACHINE LOGON_TIME
------ ---------- ---------- ----------- ----------- -----------
16188960 10035 882 17302472 origin2 24/08/2015 07:43:40

Now I know the sid 10035 refers to local process 16188960 and the process on origin database is 17302472. What I do what I want if this process:

root@origin2:/oracle/diag/rdbms/origin/origin2/trace>ps -ef |grep 17302472
grid 17302472 1 97 07:42:42 - 5:58 oracleorigin2 (LOCAL=NO)
root 24445782 36700580 0 08:05:45 pts/3 0:00 grep 17302472

What include to locae the session in the database by spid, see the sql, and etecetera:

origin> @spid2
Enter value for process: 17302472
SID SERIAL# USERNAME OSUSER PROGRAM STATUS
------- ---------- ----------- ----------- --------------- ----------
7951 41323 USER_XPTO scheduler_user sqlplus@scheduler_app.domain.net (TNS V1-V3) ACTIVE
database2> @sid
Sid:7951
Inst: 2
SQL_ID SEQ# EVENT MODULE STATUS SID SERIAL# INST_ID
---------- ----- --------- ------- --------- ----- ------ ----------
1w1wz2mdunya1 56778 db file sequential read REMOTE_LOAD ACTIVE 7951 41323 2

That’s OK?
Simple isn’t?

The used Scripts (except the “sid”, that is a simple SQL on gv$session):

Get SPID and PROCESS FOR DBLINK from a SID:

# spid:
col machine format a30
col process format 999999
select p.spid,b.sid, p.pid, b.process as process_for_db_link, machine, logon_time
from v$session b, v$process p
where b.paddr=p.addr
and sid=&sid
/

Get SID from SPID:

#spid2:
SELECT s.sid, s.serial#, s.username,
s.osuser, s.program, s.status,
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (&process);
/

See ya!
Matheus.