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!

Advertisements

ORA-29278 When Trying to Use SMTP with GMail

Hi all,
Some time ago I got reached by a client with this error to send SMTP emails using GMail service with a wallet.
This is a generic error, in general the string identified the error returned from email service layer.

# Generic:

ORA-29278: SMTP transient error: string

# Error I was facing:

ORA-29278: SMTP transient error: 421 Service not available

Basically it was related to some intermittence on GMail’s service and got back to normal after a while. This is also well documented in this post by Hâvard Kristiansen.
However, I couldn’t use scripts there because of my wallet. Then I found the very useful commands below, that is what I want to share with you:

# Checking for SSL Certificate:

[oracle@grepora-srvr]$ openssl s_client -crlf -quiet -connect smtp.gmail.com:587 -starttls smtp
depth=3 C = US, O = Equifax, OU = Equifax Secure Certificate Authority
verify return:1
depth=2 C = US, O = GeoTrust Inc., CN = GeoTrust Global CA
verify return:1
depth=1 C = US, O = Google Inc, CN = Google Internet Authority G2
verify return:1
depth=0 C = US, ST = California, L = Mountain View, O = Google Inc, CN = smtp.gmail.com
verify return:1
250 SMTPUTF8
^C
(return:1 means "passed".)

# Testing SMTP on Database:
DECLARE
  k_host            CONSTANT VARCHAR2(100) := 'smtp.gmail.com';
  k_port            CONSTANT INTEGER       := 587;
  k_wallet_path     CONSTANT VARCHAR2(100) := 'file:/u01/app/oracle/admin/grepora/wallet/email';
  k_wallet_password CONSTANT VARCHAR2(100) := '****';
  k_domain          CONSTANT VARCHAR2(100) := 'grepora.com'; 
  k_username        CONSTANT VARCHAR2(100) := 'suporte@grepora.com';
  k_password        CONSTANT VARCHAR2(100) := 'mysecurepass';
  k_sender          CONSTANT VARCHAR2(100) := 'suporte@grepora.com';
  k_recipient       CONSTANT VARCHAR2(100) := 'suporte@grepora.com';
  k_subject         CONSTANT VARCHAR2(100) := 'Test TLS mail';
  k_body            CONSTANT VARCHAR2(100) := 'Message body';
  l_conn    utl_smtp.connection;
  l_reply   utl_smtp.reply;
  l_replies utl_smtp.replies;
  BEGIN
    dbms_output.put_line('utl_smtp.open_connection');

    l_reply := utl_smtp.open_connection
               ( host                          => k_host
               , port                          => k_port
               , c                             => l_conn
               , wallet_path                   => k_wallet_path
               , wallet_password               => k_wallet_password
               , secure_connection_before_smtp => FALSE
               );

    IF l_reply.code != 220
    THEN
      raise_application_error(-20000, 'utl_smtp.open_connection: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.ehlo');

    l_replies := utl_smtp.ehlo(l_conn, k_domain);

    FOR ri IN 1..l_replies.COUNT
    LOOP
      dbms_output.put_line(l_replies(ri).code||' - '||l_replies(ri).text);
    END LOOP;

    dbms_output.put_line('utl_smtp.starttls');

    l_reply := utl_smtp.starttls(l_conn);

    IF l_reply.code != 220
    THEN
      raise_application_error(-20000, 'utl_smtp.starttls: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.ehlo');

    l_replies := utl_smtp.ehlo(l_conn, k_domain);

    FOR ri IN 1..l_replies.COUNT
    LOOP
      dbms_output.put_line(l_replies(ri).code||' - '||l_replies(ri).text);
    END LOOP;

    dbms_output.put_line('utl_smtp.auth');

    l_reply := utl_smtp.auth(l_conn, k_username, k_password, 'PLAIN');

    IF l_reply.code != 235
    THEN
      raise_application_error(-20000, 'utl_smtp.auth: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.mail');

    l_reply := utl_smtp.mail(l_conn, k_sender);

    IF l_reply.code != 250
    THEN
      raise_application_error(-20000, 'utl_smtp.mail: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.rcpt');

    l_reply := utl_smtp.rcpt(l_conn, k_recipient);

    IF l_reply.code NOT IN (250, 251)
    THEN
      raise_application_error(-20000, 'utl_smtp.rcpt: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.open_data');

    l_reply := utl_smtp.open_data(l_conn);

    IF l_reply.code != 354
    THEN
      raise_application_error(-20000, 'utl_smtp.open_data: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.write_data');

    utl_smtp.write_data(l_conn, 'From: '||k_sender||utl_tcp.crlf);
    utl_smtp.write_data(l_conn, 'To: '||k_recipient||utl_tcp.crlf);
    utl_smtp.write_data(l_conn, 'Subject: '||k_subject||utl_tcp.crlf);
    utl_smtp.write_data(l_conn, utl_tcp.crlf||k_body);

    dbms_output.put_line('utl_smtp.close_data');

    l_reply := utl_smtp.close_data(l_conn);

    IF l_reply.code != 250
    THEN
      raise_application_error(-20000, 'utl_smtp.close_data: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.quit');

    l_reply := utl_smtp.quit(l_conn);

    IF l_reply.code != 221
    THEN
      raise_application_error(-20000, 'utl_smtp.quit: '||l_reply.code||' - '||l_reply.text);
    END IF;

  EXCEPTION
    WHEN    utl_smtp.transient_error
         OR utl_smtp.permanent_error
    THEN
      BEGIN
        utl_smtp.quit(l_conn);
      EXCEPTION
        WHEN    utl_smtp.transient_error
             OR utl_smtp.permanent_error
        THEN
          NULL;
      END;

      raise_application_error(-20000, 'Failed to send mail due to the following error: '||SQLERRM);

  END;
  /

Hope it helps you!

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

11g Feature: Skip Locked Syntax in SELECT FOR UPDATE

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:

Skip Locked Syntax in SELECT FOR UPDATE

This is an 11g feature, and it’s a bit controversial. Why?
The SELECT FOR UPDATE statement is well known and responsible by several problematic operations, mainly in transactional databases. It’s not rare to face issues like errors below when trying to perform large updates on database:

ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-00054 resource busy and NOWAIT specified

Worse than this, if a select for update task aborts, a zombie process may hold the row locks long term, requiring DBA intervention.

In 11g, the clause SKIP LOCKED has been released, allowing to skip-over any rows that are already locked. Check below for a simple example:

select COLUMN1, COLUMN2 from MYTABLE 
where COLUMN1='DESIRED_VALUE' for update skipped locked;

This is very useful in transaction environments, specially when facing errors mentioned above, however can cause logical corruption.
The reason is obvious, if some rows be in lock, those are not being updated. In this case, if the table has 100 entries where COLUMN1=’DESIRED_VALUE’ but 10 of them are in lock, only 90 will be actually selected, making statement invalid in some circumstances.

This is very useful in transaction environments, specially when facing errors mentioned above, however can cause logical corruption.
The reason is obvious, if some rows be in lock, those are not being updated.

Additional Note: In some cases, increasing the table’s initrans allows more buckets for locking:

alter table MYTABLE move initrans xxxx;

Here is a very interesting post by Jonathan Lewis about it.

See you next week!

Orphan AWR Data Consuming SYSAUX Space

Hi all!
Some time ago I faced the this situation in a client. SYSAUX was simply growing with no reason, but retention and everything has not changed.

After some research, could map issue to Bug 14084247: STBH: ORA-01555 DUE TO WRH$_ACTIVE_SESSION_HISTORY NOT PURGED.

There is an one-ooff patch, but needs also extra steps to make it work, as stated on MOS: Excessive AWR Growth From Partitioned Objects Such as SYS.WRH$_EVENT_HISTOGRAM Causing Sysaux to Grow (Doc ID 1912201.1):

However, even after applying bug patch 14084247, this fix will not have an immediate effect and may take some time to "catch up".

The good new is that this can also be applied in on-line patching mode. I did another post documenting this patch applying: Online Data Patch Apply with multiple Databases on same Oracle Home: OPatch failed with error code 26

Some other relevant MOS Documents under this subject are:
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)
Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)

Ok, but waht can I do right now?
You can purge the top AWR tables, as described on MOS: WRH$_LATCH, WRH$_SYSSTAT, and WRH$_PARAMETER Consume the Majority of Space within SYSAUX (Doc ID 2099998.1).

Check steps below on how it can be done:

Continue reading

Downstream database with ORA-00317: file type 0 in header is not log file

You missed some Downstream archived log?

Is this archived log on Downstream area either it’s not read by Logminer.

Try to run to the hills or read GrepOra posts.

In alert log is tracing like these:

ORA-00317: file type 0 in header is not log file
ORA-00334: archived log: '/oracle/dowstream-archive/2_136361_87643997.dbf'
LOGMINER: Error 317 encountered, failed to read corrupt logfile /oracle/dowstream-archive/2_136361_87643997.dbf
LOGMINER: Encountered error 1291 while adding logfile /oracle/dowstream-archive/2_136361_87643997.dbf to session 1

Copy the archived log from RMAN to Downstream, register logical logfile. Wait for while to Logimer start to provide LCR GoldenGate Integrated Extracts to Logminer new registered archived log.

Try register achived log on Dowstream database as below:

ALTER DATABASE REGISTER LOGFILE '/oracle/dowstream-archive/2_136361_87643997.dbf' FOR 'OGG$CAP_EXT_1';
ALTER DATABASE REGISTER LOGFILE '/oracle/dowstream-archive/2_136361_87643997.dbf' FOR 'OGG$CAP_EXT_2';

 

integratedcapture[1]