11g Feature: Fine Grained Dependency Tracking (FGDT)

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

11g Feature: Fine Grained Dependency Tracking (FGDT)

In previous versions, object dependencies were managed at the object level. This way, altering an object automatically invalidated all dependent objects.
Oracle 11g has more granular dependency management, so only changes that directly affect an object will cause an invalidation.

This is not a Feature under control of Dev or DBA, but I judged important to mention here, as per has important impact to development and deployment processes.

More details can be found here: http://www.orafaq.com/node/2683

Advertisements

11g Feature: Fine-Grained Access Control (FGAC) on Network Services

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Fine-Grained Access Control (FGAC) on Network Services

Oracle supplies PL/SQL utility packages such as UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR to access to network services. In 11g Oracle have enhanced security available.
Rather than public being granted execute privileges on these packages, now it’s needed to create an ACCESS CONTROL LIST (ACL) in order to use these packages. Some ACL Related Data Dictionary VIEWS are DBA_NETWORK_ACLS and [DBA/USER]_NETWORK_ACL_PRIVILEGES.

> To create ACL:

SQL> begin
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'example.xml',
description=>'EXEMPLE ACL',
principal=>'EXAMPLE',                        
is_grant=>TRUE,
privilege=>'connect');
End;
/

> Once the ACL is created, additional user or privileges can be added using the DBMS_NETWORK_ACL_ADMIN.add_privileges procedure:

SQL> BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege ( 
    acl         =>  'example.xml', 
    principal   => 'SCOTT',
    is_grant    => FALSE, 
    privilege   => 'connect', 
    position    => NULL, 
    start_date  => NULL,
    end_date    => NULL);
  COMMIT;
END;
/

* DBMS_NETWORK_ACL_ADMIN.delete_privileges can be usedto drop privileges and DBMS_NETWORK_ACL_ADMIN.drop_acl to drop ACL.

> To assign ACL to a Network Host:

SQL> begin
DBMS_NETWORK_ACL_ADMIN.assign_acl(
acl => 'example.xml',
host=>'grepora');
End;
/

See you next week!

11g Feature: PLS-00436 Restriction in FORALL Statements Removed

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

11g Feature: PLS-00436 Restriction in FORALL Statements Removed

In 11g, the PLS-00436 restriction has been removed, meaning individual elements of a collection can be referenced with SET and WHERE clauses in a FORALL construction.

Please check the following example, setting text of my_table to ‘Line x’ where x is the line number (also ID column):

DECLARE
  TYPE t_test IS TABLE OF my_table%ROWTYPE;
  l_test t_test;
BEGIN
  SELECT * BULK COLLECT INTO l_test FROM my_table;
  
  FOR i IN l_test.first .. l_test.last LOOP
    l_test(i).text := 'Line ' || i;
  END LOOP;

  FORALL i IN l_test.first .. l_test.last
    UPDATE my_table SET text = l_test(i).text WHERE id = l_test(i).id;
  COMMIT;
END;
/

How to Setup Automatic Startup and Shutdown of an Oracle Database on Linux (Not Using Oracle-Restart)

Ok, we all have done it several times. I, myself, made some scripts to do it in past. However, do you know there is an official way/script for that?
You can accomplish this with dbstart and dbshut scripts, which are located in the $ORACLE_HOME/bin directory.
This is documented for 12.1 in Stopping and Starting Oracle Software .

Of course that, if you have Oracle Clusterware configured, you can use Oracle Restart and SRVCTL tool, and Clusterware automatically starts and stops the Oracle database instances and listeners. Which is way better.
This post refers to official procedure in case you haven’t Clusterware configured.

Quick Guide:

Continue reading

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).

Continue reading

Long running jobs AQ$_PLSQL_NTFN_%

Hello all!

Some time ago I had an issue with some jobs running for 64 days. Cool han?
All jobs names were AQ$_PLSQL_NTFN_%. All activity of jobs were related to SQLID f7zggdz9p7bhk in wait event “Streams AQ: waiting for messages in the queue”.

# SQLID f7zggdz9p7bhk:
begin SYS.SCHEDULER$_JOB_EVENT_HANDLER(context => :1,reginfo => sys.aq$_reg_info(:2, :3, :4, :5, :6, :7),descr => sys.aq$_descriptor(:8, :9, :10, sys.msg_prop_t(:11, :12, :13, :14, :15, :16, :17, :18, sys.aq$_agent(:19, :20, :21), :22, :23), sys.aq$_ntfn_descriptor(:24), :25, :26), payload => :27, payloadl => :28); end;

Reviewing on MOS, found a match to Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8).

The root cause is procedure SYS.SCHEDULER$_JOB_EVENT_HANDLER keep waiting AQ PL/SQL Notification callbacks associated with scheduler job email notifications for messages in “SYS”.”SCHEDULER$_EVENT_QUEUE” which no longer exist.

From MOS, this can be easily solved with fix for Bug 16623661 – No email notification is sent by scheduler, wich is included in 12.1.0.1 (base) and affect versions 11.2.0.2 and later.
Also from MOS, and which I did, is drop these jobs. The problem is these jobs just restart and have the same problem again after automated recreation.

So, apply the fix or upgrade your database. 😉

Some related references:
Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8)
AQ$_PLSQL_NTFN Scheduler Jobs Executed in large numbers affecting Database performance (Doc ID 2001165.1)
Bug 21665897: FIX FOR BUG 14712567 CAUSES LARGE NUMBERS OF AQ$_PLSQL_NTFN_% TO BE SPAWNED

Hope it helps!
Cheers!

Purge Recycle Bin Older than…

Hello all!

Have your recyclebin being full/big but don’t want to make a complete “PURGE RECYCLE BIN;”?
Want remove only older than x days (like older than 30 days)?

Please don’t:

DELETE from DBA_RECYCLEBIN where droptime<sysdate-30;
PURGE DBA_RECYCLEBIN where droptime<sysdate-30;

A good way to perform that is to execute output from:

# 30 Days
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' 
and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-30;

# 30 minutes
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' and 
to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-(30/(24*60));

There is some reference here too:
How To Drop / Delete / Purge Recyclebin or DBA_RECYCLBIN For Objects Older Than x Days/minutes or selective purge of objects from recycle bin (Doc ID 1596147.1)

And here you can see an awesome procedure in case you want to automate that in a job or similar:
http://dbspecialists.com/enhanced-purging-of-the-oracle-recyclebin/

Hope it help you!
Cheers!