Month: August 2015

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.

Database, Performance, SQL | PLSQL

DBA_TAB_MODIFICATIONS

 , , ,

Do you know the view “dba_tab_modifications”?
It’s very useful to know what has changed since the last stats gathering of a table and all decision/information that comes with… See the example below..

The only need is to run “dbms_stats.flush_database_monitoring_info” before cheking… take a look:

mydb> create TABLE matheus_boesing.test (nro number);
Table created.
mydb> begin
2 for i in 1..1000 loop
3 insert into matheus_boesing.test values (i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
no rows selected
mydb> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES
---------------------- -------------- ---------- ---------- ----------
MATHEUS_BOESING test 1000 0 0

mydb> EXEC DBMS_STATS.GATHER_TABLE_STATS('MATHEUS_BOESING','test');
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
no rows selected

For more information: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4149.htm

Have a nice day! 😀
Matheus.

Backup and Restore, Database, Shell Scripts, Tools

Date Format in RMAN: Making better!

 , , ,

I know…
The date format on RMAN it’s not good, but it’s to make it better. Take a look:

db-server>rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 12 11:00:59 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=1286311368)
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
541 Incr 1 17.80M DISK 00:00:01 12-AUG-15
BP Key: 541 Status: AVAILABLE Compressed: NO Tag: BKPINCR_LV1_20150812_0923
Piece Name: +DGFRA/MYDB/backupset/2015_08_12/ncnnn1_bkpincr_lv1_20150812_0923_0.4613.887534683
Control File Included: Ckp SCN: 7301745 Ckp time: 12-AUG-15
RMAN> exit
Recovery Manager complete.

I’ts a simple NLS export on SO before access RMAN:

db-server>export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss';
db-server>rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 12 11:05:57 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=1286311368)
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
541 Incr 1 17.80M DISK 00:00:01 2015/08/12 09:24:42
BP Key: 541 Status: AVAILABLE Compressed: NO Tag: BKPINCR_LV1_20150812_0923
Piece Name: +DGFRA/MYDB/backupset/2015_08_12/ncnnn1_bkpincr_lv1_20150812_0923_0.4613.887534683
Control File Included: Ckp SCN: 7301745 Ckp time: 2015/08/12 09:24:41

Matheus.

ASM, Database, Errors and Bugs, Linux

ORA-15186: ASMLIB error function

 , , , ,

Almost a month away… My bad!
Here I go again, with a quick tip, that a passed today. Our kernel was ‘changed’ without advise and this began to happen:

ORA-15186: ASMLIB error function = [asm_init], error = [18446744073709551611], mesg = [Driver not installed]
ERROR: error ORA-15186 caught in ASM I/O path

The solution was is basically update the asmlibs, that is based on kernel version. For RHEL, the solution is well decribed here:

http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html
https://access.redhat.com/solutions/315643

Just to remember: After the kernel change, a relink of your Oracle Home is higly recommended.

Have a nice day!
Matheus.

Community, Events

GrepOra.com at GUOB 2015!

 , ,

Hi all!
It was a great pleasure to me beeing with the starts in GUOB Tech Day 2015 – LA OTN Tour, event organized by GUOB (Grupo de Usuários Oracle – Brasil). This year we had the opportunity to get in touch with Kerry Osborne, Tim Hall, Alex Gorbachev, Mike Dietrich, Francisco Alvarez and others well known in Oracle world. We had also a speech by Alex Zaballa, our fresh and first Brazilian Oracle ACE Director.

One of the bests speeches, by Kerry Osborne, is also available on his site, here. Take a look!

I know, we are a little bit ugly, but is valid for the moment:

guob3(Rafael Nolio – GrepOra, William Fagundes, Rodrigo Jorge and Matheus Boesing – GrepOra)

Recently, GUOB organization team released some videos from the best presentations. Below:

Kerry Osborne
Tim Hall
Mike Dietrich

So, this is it!
See you on GUOB Tech Day 2016!