Utility Belt

Lock

Lock by DBLink – Which is te session in the remote database?

matheusdba

“Holy Remote Session, DBAtman!”
Who is this in the remote DB?

Important, right? What 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);
/

Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!

SQPlus

Sqlplus: Connect without configure TNSNAMES

matheusdba

“Holy easy stuff, DBAtman!”

Okey, you must to know, but is always useful to remember that… If you don’t want to configure your TNSNAMES, you can connect directly to description of your database.

You can do it in two ways: First one is using Easy Connect. But some tools simply don’t support it. Here is the second way, just like using TNSNAMES but without TNSNAMES:

sqlplus> conn matheus_boesing@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1521)))(CONNECT_DATA=(service_name=mydb)))
Enter password: ********
Connected.
sqlplus>

Based on this, I made two scripts, to connect with the sid (c.sql) or with the service_name (s.sql) and make my life easier. Here the scripts:

sqlplus>get c
1 DEFINE VHOST = &1.
2 DEFINE VPORT = &2.
3 DEFINE VSID = &3.
4 DEFINE VDESC='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&VHOST)(PORT=&VPORT)))(CONNECT_DATA=(SID=&VSID)(server=dedicated)))'
5 disconnect
6 connect dbatman@&&VDESC
7 set linesize 1000
8 set sqlprom '&&VSID> '
9 select instance_name, host_name
10 from v$instance;
11 exec dbms_application_info.SET_MODULE('MATHEUS_BOESING','DBA');
12 alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
13 UNDEFINE VDESC
14 UNDEFINE 1
15 UNDEFINE 2
16* UNDEFINE 3
sqlplus>get s
1 DEFINE VHOST = &1.
2 DEFINE VPORT = &2.
3 DEFINE VSID = &3.
4 DEFINE VDESC='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&VHOST)(PORT=&VPORT)))(CONNECT_DATA=(SERVICE_NAME=&VSID)(server=dedicated)))'
5 prompt &VDESC
6 disconnect
7 connect dbatman@&&VDESC
8 set linesize 1000
9 set sqlprom '&&VSID> '
10 select instance_name, host_name
11 from v$instance;
12 exec dbms_application_info.SET_MODULE('MATHEUS_BOESING','DBA');
13 alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
14 UNDEFINE VDESC
15 UNDEFINE 1
16 UNDEFINE 2
17* UNDEFINE 3
sqlplus>

It can be used like this:

sqlplus>@s mydb.domain.net 1521 mydb
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=mydb)(server=dedicated)))
Enter password: ********
Connected.

Ok, but, let’s suppose you are working in a cluster and wants to connect directly to the another instance. I made the script below (ci.sql). It’s not beautiful, but is a lot hopeful:

sqlplus> get ci
1 DEFINE VINT = &1.
2 undefine VHOST
3 undefine VSID
4 VARIABLE VCONN varchar2(100)
5 PRINT ret_val
6 BEGIN
7 SELECT '@c '||host_name||' 1521 '||INSTANCE_NAME
8 INTO :VCONN
9 FROM gv$instance where INSTANCE_NUMBER=&VINT;
10 END;
11 /
12 set head off;
13 spool auxcon.sql
14 prompt set head on;
15 print :VCONN
16 prompt set head on;
17 spool off;
18* @auxcon
sqlplus>

As you see, you inform the inst_id you want to connect. It can be used like:

mydb> @instance
INSTANCE_NAME
------------------------------
mydb_2
mydb> @instances
INST_NUMBER INST_NAME
----------- ---------------------------------------
1 db2srvr2p.grepora.net:mydb_1
2 db1srvr1p.grepora.net:mydb_2
mydb> @ci 1
@c db2srvr2p.grepora.net 1521 mydb_1
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Enter password: ********
Connected.
mydb_1> @instance
INSTANCE_NAME
------------------------------
mydb_1

Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!

ASM

ASM: Disk Size Imbalance Query

matheusdba

“Holy Imbalance, DBAtman!”
Is that right?

It can be useful if you work frequently with OEM metrics…

# OEM’s 12c Query

SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count)
min_disk_extents
, MAX(extent_count) - MIN(extent_count) disk_extents_imbalance
FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp)
extent_count
FROM x$kffxp
WHERE group_kffxp = 1
AND disk_kffxp != 65534
GROUP BY number_kffxp, disk_kffxp
ORDER BY number_kffxp, disk_kffxp)
GROUP BY file_num
HAVING MAX(extent_count) - MIN(extent_count) > 5
ORDER BY disk_extents_imbalance DESC;

# DBAtman’s Query

select max(free_mb) biggest, min(free_mb) lowest, avg(free_mb) AVG,
trunc(GREATEST ((avg(free_mb)*100/max(free_mb)),(min(free_mb)*100/avg(free_mb))),2)||'%' as balanced,
trunc(100-(GREATEST ((avg(free_mb)*100/max(free_mb)),(min(free_mb)*100/avg(free_mb)))),2)||'%' as inbalanced
from v$asm_disk
where group_number in
(select group_number from v$asm_diskgroup where name = upper('&DG'));

I made my own query for two reasons:
1) I didn’t have the OEM query in the time i made it.
2) My query measures the imbalance with the avg of the disks (if every disk would balanced, how would be the difference), rather than the real/present difference between the disk with the maximum and the minimum usage…

So, you can chose the one you need… 🙂

Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!

Database, Instance

Getting Oracle Parameters: Hidden and Unhidden

matheusdba

“Holly Secret Stuff, DBAtman!”
What other secrets do you have there?

Connected as sys with sysdba:

select x.ksppinm name,ksppdesc description,
y.kspftctxvl value,
y.kspftctxdf isdefault,
decode(bitand(y.kspftctxvf, 7), 1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.kspftctxvf, 2), 2, 'TRUE', 'FALSE') isadj
from sys.x$ksppi x, sys.x$ksppcv2 y
where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance')
and x.indx + 1 = y.kspftctxpn

order by name;Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!

Temp

Scripts for Temp Space Checking and Troubleshooting

matheusdba

“Holy Temp Usage, DBAtman!!”

What do we do?
There are some Scripts for Temp Space Checking and Troubleshooting:

The classing and his output:

select TABLESPACE_NAME, round(TABLESPACE_SIZE/1024/1024/1024) SIZE_GB,
round(ALLOCATED_SPACE/1024/1024/1024) ALLOCATED_GB, round(FREE_SPACE/1024/1024/1024) FREE_GB
from dba_temp_free_space;

TABLESPACE_NAME                   SIZE_GB ALLOCATED_GB    FREE_GB
------------------------------ ---------- ------------ ----------
TMP_TBS                                52           52         52
TEMPJOB                               208          208        207

Anybody using more than 70% of a TBS?

select sid,
inst_id,
username, "%Used",TABLESPACE
  from (select s.inst_id,
               s.username,
               s.SQL_ADDRESS,
               s.SID,
               TABLESPACE,
               sum(blocks * blk_size) / 1024 / 1024 Size_Mb,
               trunc((sum(blocks * tf.blk_size) / max(tf.sz)) * 100, 2) "%Used"
          from gv$sort_usage SU,
               gv$session s,
               (select tablespace_name,
                       max(bytes / blocks) blk_size,
                       sum(bytes) sz
                  from dba_temp_files
                 group by tablespace_name) tf
         where su.inst_id = s.inst_id(+)
           and su.SESSION_ADDR = s.SADDR(+)
           and su.TABLESPACE(+) = tf.tablespace_name
           and TABLESPACE = nvl(upper('&TBS'), TABLESPACE)
           group by s.inst_id, s.username, s.SQL_ADDRESS, s.SID, TABLESPACE
           order by sum(blocks * blk_size) / 1024 / 1024)
where "%Used"> 70
order by 4 desc;

Here is another one to do the same, but based on used size:

col sid format 99999
col tbs format a20
SELECT /*+ rule*/
       s.sql_id, s.sid, s.username, u.tablespace TBS, u.segtype, u.extents,
       (u.blocks * (select value from v$parameter where name = 'db_block_size')) /1024/1024 MB_used,
       (select sum(f.bytes) from dba_temp_files f where f.tablespace_name = u.tablespace) /1024/1024 Total_temp_mb
FROM v$sort_usage u, v$session s
WHERE s.saddr      = u.session_addr
and ((u.blocks * (select value from v$parameter where name = 'db_block_size')) /1024/1024) > 1000
order by S.sid
order by 6 asc

Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!

ASM, Files - TBS|DTFs|DBFiles

Shared Diskgroup for Several Databases: How much each Database is really using?

matheusdba

“Holy space usage, DBAtman!”

OK, So now you have a lot of databases on same DG, and don’t know how much of DG is being used by each database?

Here is something that may help you:

@asm_usage.sql

-- aka asm-db.sql
set pages 350 timing on
col gname form a10
col dbname form a10
col file_type form a16
break on gname skip 2 on dbname skip 1
compute sum label total_db of gb on dbname
compute sum label total_diskg of gb on gname  

SELECT
    gname,
    dbname,
    file_type,
    round(SUM(space)/1024/1024) mb,
    round(SUM(space)/1024/1024/1024) gb,
    COUNT(*) "#FILES"
FROM
    (
        SELECT
            gname,
            regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
            file_type,
            space,
            aname,
            system_created,
            alias_directory
        FROM
            (
                SELECT
                    concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
                    system_created,
                    alias_directory,
                    file_type,
                    space,
                    level,
                    gname,
                    aname
                FROM
                    (
                        SELECT
                            b.name            gname,
                            a.parent_index    pindex,
                            a.name            aname,
                            a.reference_index rindex ,
                            a.system_created,
                            a.alias_directory,
                            c.type file_type,
                            c.space
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b,
                            v$asm_file c
                        WHERE
                            a.group_number = b.group_number
                        AND a.group_number = c.group_number(+)
                        AND a.file_number = c.file_number(+)
                        AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0
                AND rindex IN
                    (
                        SELECT
                            a.reference_index
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b
                        WHERE
                            a.group_number = b.group_number
                        AND (
                                mod(a.parent_index, power(2, 24))) = 0
                    ) CONNECT BY prior rindex = pindex )
        WHERE
            NOT file_type IS NULL
            and system_created = 'Y' )
GROUP BY
    gname,
    dbname,
    file_type
ORDER BY
    gname,
    dbname,
    file_type
/

Continue reading “Shared Diskgroup for Several Databases: How much each Database is really using?” →