Blocking session, find root blockers and kill

By the following script you are able to find root cause of the locks. Script do not display BACKGROUND processes that are locking others (for example, LGWR, DBWR), because killing them causes database crush.

In front of the kill immediate statement there is written additional information such as : username[machine]:sql_id[prev_sql_id]:program and then comes kill immediate statement.

with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
 from gv$session where blocking_instance is not null and blocking_session is not null and username is not null)
 select lpad(' ',2*(level-1))||waiter lock_tree from
 (select * from lk
 union all
 select distinct 'root', blocker from lk
 where blocker not in (select waiter from lk))
 connect by prior waiter=blocker start with blocker='root';
--Generate SQLs to kill top-level blockers
set serverout on
 declare
 sess varchar2(20);
 sessinfo varchar2(100);
 begin
 for i in
 (with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
 from gv$session where blocking_instance is not null and blocking_session is not null and username is not null)
 select distinct blocker from lk where blocker not in (select waiter from lk)
 )
 loop
 begin
 select regexp_substr(i.blocker,'[0-9]+$')||','||serial# ||',@' || regexp_substr(i.blocker,'[0-9]+'),
 substr(username||'['||machine||']:'||sql_id||'['||prev_sql_id||']:'||program,1,100) into sess, sessinfo
 from gv$session where inst_id = regexp_substr(i.blocker,'[0-9]+') and sid = regexp_substr(i.blocker,'[0-9]+$') and type='USER';
 dbms_output.put_line(sessinfo || ' ' || 'alter system kill session ''' || sess || ''' immediate;');
 exception when no_data_found
 then continue;
 end;
 end loop;
 end;
 /

DDL Trigger to audit schema changes

  1. Create sequence for assigning numbers to the events:
    -- Create sequence
    create sequence SYS.DSQ_DDLEVENTS
    minvalue 1
    maxvalue 9999999999999999999999999999
    start with 1
    increment by 1
    cache 100;
  2. Create ddl events table
    -- Create table
    create table SYS.DDL_EVENTS
    (
     eventid NUMBER,
     inst_id   NUMBER,
     eventdate DATE,
     oraloginuser VARCHAR2(100),
     oradictobjname VARCHAR2(100),
     oradictobjowner VARCHAR2(100),
     oradictobjtype VARCHAR2(100),
     orasysevent VARCHAR2(100),
     machine VARCHAR2(100),
     program VARCHAR2(100),
     osuser VARCHAR2(100)
    )
    tablespace USERS;
  3. Create table for saving SQL statements(this is necessary because triggered sql statements may have several lines)
    -- Create table
    create table SYS.DDL_EVENTS_SQL
    ( eventid NUMBER,
     sqlline NUMBER,
     sqltext VARCHAR2(4000)
    )
    tablespace USERS;
  4. Create DDL trigger(it doesn’t degrade performance at all, by my experience)
    CREATE OR REPLACE TRIGGER sys.audit_ddl_trg
    AFTER DDL ON DATABASE
    DECLARE
    l_eventId NUMBER;
     l_sqlText ORA_NAME_LIST_T;
    BEGIN
    if ORA_SYSEVENT!='TRUNCATE' then
    
     SELECT dsq_ddlEvents.NEXTVAL INTO l_eventId FROM SYS.DUAL;
    INSERT INTO ddl_events (eventId,
     inst_id,
     EVENTDATE,
     ORALOGINUSER,
     ORADICTOBJNAME,
     ORADICTOBJOWNER,
     ORADICTOBJTYPE,
     ORASYSEVENT,
     machine,
     program,
     osuser
     )
     ( SELECT l_eventId,
     inst_id,
     SYSDATE,
     ORA_LOGIN_USER,
     ORA_DICT_OBJ_NAME,
     ORA_DICT_OBJ_OWNER,
     ORA_DICT_OBJ_TYPE,
     ORA_SYSEVENT,
     machine,
     program,
     osuser
     FROM SYS.DUAL
     right outer join
     SYS.GV$SESSION s on (1=1)
     WHERE s.sid=SYS_CONTEXT('USERENV','SID')
     and SYS_CONTEXT('USERENV','INSTANCE')=s.inst_id);
     FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
     INSERT INTO ddl_events_sql
     ( eventId, sqlLine, sqlText )
     VALUES
     ( l_eventId, l, l_sqlText(l) );
     END LOOP;
     end if;
    
     exception when others then
     null;
    END;

Oracle: Audit DMLs by specific user

Ordinary auditing do not have option to indicate audit some activities done by specific user.

I mean, you cannot write the following:

audit insert on my_schema.my_table by my_user;  <<—-not possible. The right statement is:
audit insert on my_schema.my_table by access;
or
audit insert on my_schema.my_table by session;

If I want to audit only activities done by my_user, one of the way is to create audit policy like the following;

begin
dbms_fga.add_policy(
object_schema=>’my_schema‘,
object_name=> ‘my_table‘,
policy_name=> ‘my_policy’,
audit_condition => ‘sys_context(”USERENV”,”CURRENT_USER”)=”MY_USER”’,
enable => TRUE,
statement_types => ‘INSERT, UPDATE, DELETE’,
audit_column_opts => dbms_fga.all_columns);
END;

So audit_condition gives the opportunity to check something and in this case we are checking user that is running statements indicated in statement_types option.

–Logs will be located here

SELECT * FROM dba_fga_audit_trail

–To see what policies we have

SELECT * FROM dba_audit_policies

HOW TO CATALOG TAPE BACKUP PIECES

You can catalog TAPE backup piece only using automatic channel:

RMAN> configure channel device type 'SBT_TAPE' parms <mml parameters>

Example:

configure channel device type 'SBT_TAPE' parms='ENV=(NB_ORA_CLIENT=ClientHostName,NB_ORA_SERV=backupServerHostName)';
RMAN> catalog device type 'SBT_TAPE' backuppiece 'arch_dEYC_ub4qtfud9_s20836_p1_t903346601';

Linker error while installing Oracle 11g /u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk

During installing 11g on OEL7 I got error when linking libraries.

To solve you should change syntax in the following file:

vi $ORACLE_HOME/sysman/lib/ins_emagent.mk

Change the following line from

$(MK_EMAGENT_NMECTL)

to:

$(MK_EMAGENT_NMECTL) -lnnz11

and click retry on error window to continue?

Good Luck!

CURSOR_SHARING effect on database performance, latch: shared pool

I have upgraded our database from 11g to 12c and after that query performance degraded significantly.

I have generated ADDM report during the problematic period and found that the main problem was hard parses :

1  Hard Parse Due to Literal Usage           10.38 | 43.55%        1

Finding 1: Hard Parse Due to Literal Usage
Impact is 10.38 active sessions, 43.55% of total activity.
———————————————————-
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.

For us it is not new that our developers are not using bind variables so shared pool was/is growing and growing to retain all of the parsed SQLs and their execution plans.

But the same codes were running on 11g and were working fine. Just after upgrade database started to feel that bad:)

The reason is that 12c has major change in optimize behavior. So if bind variables are not used in existing application then you need to use CURSOR_SHARING=FORCE option, old value of this parameter was CURSOR_SHARING=EXACT.

alter system set cursor_sharing=FORCE;

After that database started to feel better but in any case I cleared shared pool(for clearing old,not necessary parses):

alter system flush shared_pool;

Database started to feel better!

Good Luck!

scsi_id not returning any output in a VM on VMware ESX

  1. scsi_id not returning result:

    scsi_id -g -u -d /dev/sdb
    No Result

  2. Start the vSphere Client, and log in to a vCenter Server.
  3.  Select Virtual Machines for which you want to retrieve disk unique id. I need this attribute for udev rules to prepare disks for ASM, for example.
  4.  Right-click the virtual machine for which you are enabling the disk UUID attribute, and select Power > Power Off.
    The virtual machine powers off.
  5. Right-click the virtual machine, and click Edit Settings.
  6. Click the Options tab, and select the General entry in the settings column.
  7. Click Configuration Parameters.
    The Configuration Paramters window appears.
  8. Click Add Row.
  9. In the Name column, enter disk.EnableUUID
  10. In the Value column, enter TRUE.
  11. Click OK and click Save.
  12. Power on the virtual machine.
  13. Now, it returns id:

    scsi_id -g -u -d /dev/sdb
    36000c292dfddac7b8934d3293313098e

Follow

Get every new post delivered to your Inbox.