Saturday, November 27, 2010

How to Configure UTL_MAIL in Oracle DB

To install UTL_MAIL:

sqlplus / as SYSDBA

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

When you run the above mentioned two scripts, Oracle creates UTL_MAIL package. Oracle also creates a public synonym on UTL_MAIL package, but doesn't grant privileges to any user, DBA role or PUBLIC. You need to explicitly grant EXECUTE privilege on this package.


Grant succeeded.

In addition, define int.ora parameter as shown below.

SQL> alter system set smtp_out_server= '' scope both;

If you are in Oracle11g, configure network access control lists.

That's all you need to setup UTL_MAIL package. Send a test  email

exec UTL_MAIL.SEND (sender=>'', recipients=>'', subject=>'Test Message', Message=>'test'); 

If you are receiving the following error, then double-check that SMTP server that you have defined is accessible.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671

Oracle11g Upgrade - Don't Let New Features Bite You!

Oracle11g introduces several new features that are quite useful. Most of the features are available as soon as you upgrade to 11g, but few of them would require additional configuration in 11g. If you are not prepared, then these specific new features, that I will discuss in this blog, could break your applications. Fortunately, these new 11g features are not bugs! They are just features!

For smooth and flawless migration, you need to know the new features, you need to know the impact of these new features on your application, if any, and then you need to take appropriate mitigating steps. Otherwise, they could cause havoc the day-after 11g upgrade. In this blog, I will shed light on two such features that could break your application. One of them is Network Access Control List and the second one is case sensitive passwords.

Network Access Control List


Oracle database comes with several PL/SQL packages such as UTL_TCP, UTL_HTTP, UTL_MAIL, UTL_SMTP and UTL_INADDR that access network services through back-end Oracle database. UTL_MAIL is widely used to send emails from PL/SQL code. UTL_SMTP and UTL_TCP packages allow you to access back-end network resources over TCP/IP protocol. UTL_INADDR package resolves server names and IP addresses. These PL/SQL Network packages are quite useful when properly used, but outright dangerous if misused.

Oracle's Network PL/SQL packages provide direct access to network resources which are behind firewall and protected by several layers of security controls. In fact, these packages are so powerful that security auditors would closely review access granted on Network PL/SQL packages. Database security vulnerability scanners would raise a red-flag if you have granted access on Network PL/SQL packages to users or roles. By default, Oracle grants EXECUTE privilege on Network PL/SQL packages to PUBLIC. Not a good security practice at all. Therefore, DBAs would revoke PUBLIC access on Network PL/SQL packages and grant EXECUTE privilege to those who need it.

Sure, with controlled grants on Network PL/SQL packages, Oracle DBAs would reduce the security risk level, but the risk still lingers for potential security breaches. If you have access to Network PL/SQL packages, then you can access network resources that you are supposed to, and beyond. From a browser or a client outside the firewall, you potentially can access resources behind firewall.

To further tighten security on PL/SQL Network packages, Oracle11g introduces fine-grained access controls to these PL/SQL packages. With fine-grained access controls through Network Access Control Lists, you define what network resources should be accessed by the users. You can't use any SMTP server available on the back-end network. You can only use the SMTP server that you have been specifically granted access to. Oracle Network Access Control Lists define what network resources are available and accessible. An vastly improved security posture.

Oracle provides DBMS_NETWORK_ACL_ADMIN package to manage Network ACLs. A brief description on DBMS_NETWORK_ACL_ADMIN is in order as this package is little bit confusing. As explained below, a Network ACL is comprised of a list of network resources that you want to grant access to. For each network resource, you include server names and port range through upper and lower port numbers. A Network ACL is assigned to a user. A user can have connect or resolve privilege. Connect privilege includes resolve privilege. Resolve privilege is needed for UTL_INADDR package, but grant connect privilege for other Network PL/SQL packages.


Configuration of Network Access Control List is relatively simple. There are two configuration steps:
  • Configure XML DB
  • Configure Network Access Control Lists

Configure XML DB

exec UTL_MAIL.SEND (sender=>'', recipients=>'', subject=>'Test Message', Message=>'test'); 

In response to the above command, if you get the following error, then you need to configure XML DB.

ORA-24248: XMLDB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Let's verify that XML DB has not been installed.


COMP_NAME                                STATUS
---------------------------------------- ---------------------------
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID

Login / as sysdba to install XML DB.

sqlplus / as SYSDBA

Rem      Creates the tables and views needed to run the XDB system
Rem      Run this script like this:
Rem          -- XDB_PASSWD: password for XDB user
Rem          -- TABLESPACE: tablespace for XDB
Rem          -- TEMP_TABLESPACE: temporary tablespace for XDB
Rem          -- SECURE_FILES_REPO: if YES and compatibility is at least 11.2,
Rem               then XDB repository will be stored as secure files;
Rem               otherwise, old LOBS are used. There is no default value for
Rem               this parameter, the caller must pass either YES or NO.
Rem    NOTES
Rem      Must be run connected as SYS

SQL> @$ORACLE_HOME/rdbms/admin/Catqm xdb xdb temp NO

Let's double-check that XML DB has been installed.


COMP_NAME                                STATUS
---------------------------------------- ---------------------------
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID
Oracle XML Database                      VALID

exec UTL_MAIL.SEND (sender=>'', recipients=>'', subject=>'Test Message', Message=>'test'); 

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 1

As you could see from the error message, we need to configure Network Access Control List (ACL). The steps are described below.

There are few important that little details that you should keep in mind.

Don't forget to COMMIIT after you execute DBMS_NETWORK_ACL_ADMIN package.
Parameters to DBMS_NETWORK_ACL_ADMIN are case sensitive.
You can grant access on network resources to users or roles. If you are planning to execute network packages within the context of PL/SQL packages, then you must grant privileges on network resources directly to users, not roles.

-- let's configure network ACL to fix ORA-24247 errors
-- Create a network ACL called Email.xml and grant this list to user JMEHTA with connect privilege
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl=>'Email.xml', description=>'Email Access', principal=>'JMEHTA', is_grant=>TRUE, privilege=> 'connect');
-- Grant Email.xml ACL to JMEHTA1 user with connect privilege
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'Email.xml', principal=>'JMEHTA1', is_grant=>TRUE, privilege=> 'connect');
-- Add server to ACL with port range
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl=>'Email.xml', host=>'', lower_port=>25, upper_port=>25);

-- Data Dictionary View
select * from dba_network_acls ;
Few additional useful commands:
-- Use wildcards to grant access on all network resources within a domain
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl=>'Email.xml', host=>'*');
-- Unassign servers
-- Drop a Network ACL
exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl=>'Email.xml');

Now, test email should work.

Case-sensitive Network Access Control Lists

Passwords in Oracle11g are case-sensitive. You may need to disable password case sensitivity by disabling the following parameter.


Wednesday, November 24, 2010

Oracle11g Upgrade - Start Here!

Many organizations are running their production applications on Oracle10g R2. The latest Oracle database is 11g R2. A thousand dollar question is whether to upgrade or not to upgrade to 11g.

Many organizations don’t see any business justification to upgrade to 11g at the moment. Many organization defers database upgrade task as long as possible simply because upgrade process could be long and resource-intensive. Not to mention Oracle10g database just works fine. We all have different priorities.

Business drivers for an Oracle11g upgrade may vary from organization to organization. New administrative features, new developmental features, improved performance, regulatory compliance, compatibility with other products are just a few drivers that I can think of for an Oracle11g upgrade project. The criticality and importance of these drivers may vary from an organization to organization, but regulatory compliance is one such driver that forces many organizations to upgrade.

Regulatory compliance mandates that all software must be patched with the latest security patches. Oracle security patches are available only to the customers with Oracle Support. Oracle10g R2 premier support ends next year in July 2011. What this means that Oracle security patches won’t be available since July 2011 onwards unless you purchase extended support at an additional cost. So most organizations may not have a choice but upgrade to 11g R2 by July 2011. Otherwise they jeopardize regulatory compliance. For this reason, we are expecting a spike in database upgrade activities over the next six months. In fact, we have just embarked on an upgrade project that is expected to be completed by early next year.

In this blog, I will share my thoughts and experience on Oracle11g upgrade project. Hopefully, you will find them useful.

Start Here!

Oracle11g upgrade must be treated as a project. There should not be any issues whatsoever during the upgrade and more importantly after the upgrade. Our applications must not break with Oracle11g. Upgrade must be 110% successful. Upgrade must be transparent, etc, etc, etc.

Proper upgrade planning is the only solution. Don’t underestimate the importance of proper planning. The following documents should help you get started:

  • Jay Mehta Blog (Just kidding!)
  • Oracle Database Upgrade Guide (Included in Oracle11g Documentation Library. Downloadable from Oracle Technet)
  • Oracle Database Operating System specific Installation Guide
  • Oracle 11gR2 Upgrade Companion [MetaLink ID 785351.1]

Pre-Upgrade Information Tool and Upgrade Diagnostic Tool
  • Install Oracle11g R2 software and get hold of$ORACLE_HOME/rdbms/admin/utlu112i.sql script. Run this script against the database you are planning to upgrade to understand what you need to fix before the upgrade.
  • In addition, download dbupgdiag.sql script from metalink and run against the database that you want to upgrade. 

Manual Upgrade

If you are planning to upgrade to 11g R2 manually, as we have decided to, the following metalink notes are indispensable:
  • Complete Checklist for Manual Upgrades to 11gR2 [MetaLink ID 837570.1]
  • RMAN Restore of Backups as Part of a Database Upgrade [Metalink ID 790559.1]

Customize SQL*Plus

SQL*Plus is one the most commonly used tool to access Oracle databases. Although I do use Oracle Grid/Database Controls and few other tools extensively, SQL*Plus is the tool of choice on the servers to perform variety of database administration activities such as ad-hoc queries, monitoring, upgrades, database maintenance, user management, space management, etc. etc. etc. Given its wide-spread usage on day-to-day basis, I have customized SQL*Plus to improve and enhance my user experience!

In this blog, I will show you few productivity boosting tips on how to customize SQL*Plus. Its quite easy. These tips are quite useful to me, and I believe will be useful to you as wll.

SQLPATH Variable

SQLPATH environment variable is equivalent to PATH variable. What PATH variable is to executables, SQLPATH is to SQL scripts. As a DBA, I have my own repository of scripts that I execute quite often, and I am sure you do as well. Simply add your script folders to SQLPATH. SQL*Plus searches for SQL scripts in all directories specified by SQLPATH variable. Quite useful. You don't need to specify the full path of the script. Or you don't need to change directory to the folder where your scripts are stored.

On Windows platform, you also have a choice to add SQLPATH registry variable. Use either environment variable or registry variable.


LOGIN.SQL is equivalent to AUTOEXEC.BAT on Windows or .profile on Unix platforms. SQL*Plus executes login.sql at the startup. At startup, SQL*Plus starts a search for login.sql in your current directory and then it moves on to directories specified by SQLPATH variable. Create login.sql and include it in a directory that is pointed to by SQLPATH variable. You can add any SQL*Plus commands that you like. Another useful customization. The following is my truncated login.sql file.

set termout off
-- formatiing
set pagesize 1000
set linesize 132
alter session set nls_date_foramt = 'MM/DD/YYYY:HH24:MI:SS' ;
-- SQL Prompt
column new_dbid new_value old_dbid noprint
column usr new_value ousr noprint
select upper (substr (global_name, 1, (instr (global_name, '.') -1))) new_dbid
from global_name ;
select user usr from dual ;
set sqlprompt '&ousr &old_dbid> '
column new_dbid print
column usr print
-- Automatic Spool
column tstamp new_value otstamp noprint
select to_char(sysdate,'MMDDYYYYHHMISS') tstamp from dual ;
spool 'E:\MyDoc\Spool\&ousr&otstamp'
column tstamp print
set termout on

Please be careful if you have scheduled unattended batch jobs using SQL*Plus. If database is not up and running, login.sql script will prompt for values for Username and Global Name variables used in the above script, and wait forever.

SQL Prompt

Another useful customization is to customize the SQL*Plus prompt. Login.sql displayed above sets up SQL prompt that comprises of Username and a Database Name.

Automatic Spool File

This customization, included as part of above mentioned login.sql, has been quite useful to me on number of occasions. It automatically creates a spool file for every SQL*Plus session at startup. Your SQL*Plus session history is saved in the spool files that you can reference anytime. Quite useful when you need to reconstruct what happened during that late night session!

Spool filename comprises of username and timestamp. Spool file is created in the folder specified as part of spool command in the above mentioned SQL*Plus.

Update Start in Property on MS Windows

Update Start-in property on Windows so all your spool files will be saved into this folder.

If you have recently installed Oracle11g R2 and are getting the following errors, then you need to update Start in SQL*Plus properties.

SP2-0110: Cannot create save file "afiedt.buf"

JMEHTA> save temp.lst
SP2-0110: Cannot create save file "temp.lst"

Saturday, November 20, 2010

Flashback is not just for humans - Oracle Flashback Features

One of the most critical responsibilities of an Oracle DBA is to protect data against disasters. That includes maintaining data integrity of the database. FIPS Publication 199 “Standards for Security Categorization of Federal Information and Information Systems” defines loss of integrity as unauthorized modification and/or destruction of information. Loss of integrity may be a result of hardware failures, malicious attacks or simply unintentional accidents.
A recent study that cites human errors as the leading cause of loss of data integrity is no surprise. Human errors and accidental mistakes cause more system downtime than all other events combined. We heard the phrase “We are human” quite often. You can’t blame anybody. Accidents do happen. Mistakes do take place. An UPDATE without a WHERE clause runs. DELETE statement may have a bug. Application Code misbehaves. A support staff simply fires the wrong statement in the wrong database. Remember running a script in production which was meant for development database. Ouch! Whatever the cause maybe, an Oracle DBAs are called upon to fix data integrity issues.
It’s Oracle DBA’s responsibility to plan and implement mechanisms to recover from such accidents and disasters. A well-planned and tested backup strategy forms the primary mechanism to protect data. Oracle Flashback is one more weapon in Oracle DBA’s arsenal against such disasters. Oracle Flashback may come to your rescue in some specific scenarios. Oracle Flashback Features may have restrictions, constraints and limitations, but it’s Oracle DBA’s job to maximize the benefits that may be accrued from Oracle Flashback features.
With Oracle Flashback, you can view data as of previous point-in-time, get data update history along with metadata, recover tables to a previous point-in-time, undo transactions, etc.
From an Oracle DBA perspective, Oracle Flashback features are based on three different components:
  • Flashback Features that are based on Undo Segments – Flashback Query, Flashback Version Query and Flashback Transaction fall in this category. These features rely upon data stored in undo segments.
  • Flashback Features that are based on Recycle Bin – Flashback Drop feature falls in this category.
  • Flashback Features that are based on Flashback Archive Logs – Flashback Database feature falls in this category. This feature relies upon flashback logs.
In this blog, I will explain what you need to do to setup flashback features that are based on undo segments.
Init.ora Parameters
Automatic Undo Management (AUM) is a pre-requisite for Oracle Flashback. With AUM, the database manages undo segments in an undo tablespace. Beginning with Release 11g, automatic undo management is the default mode for a newly installed database.
Set Undo_management init.ora parameter to AUTO to enable Automatic Undo Management.
UNDO Tablespace
This parameter is optional. If you have only one undo tablespace in your database, then you don’t need to specify this parameter. Oracle picks up the first available undo tablespace at the database startup. If you have more than one undo tablespaces, then specify the name of the undo tablespace that you want Oracle to use. If you don’t specify undo tablespace parameter and you haven’t pre-created any undo tablespace, then Oracle would start using SYSTEM tablespace as undo tablespace.
Size of Undo Tablespace
Size of undo tablespace is an important parameter that has direct impact on read consistency and flashback. The two primary objectives of the undo segments are (a) to rollback transactions and (2) to provide read consistent view of the database to the users. In addition, undo segments are used during instance recovery to rollback uncommitted transactions. You need to size undo tablespace keeping in mind these objectives. If undo tablespace is too small, then you may get dreaded “snapshot too old” errors. If undo tablespace is too large, then you may waste space.
Proper sizing of undo tablespace is not trivial. You need to know maximum undo generation rate and timings of long running queries. In addition, you need to factor in flashback requirements. Many DBAs don’t have such statistics, and hence start with reasonable undo tablespace size, and then monitor the statistics to fine tune the size if required.
If you have enough disk space, then I would recommend creating an undo tablespace as large as reasonably possible. With larger undo tablespace, you are likely to retain undo data for longer duration, and hence flashback further into past.
For NON-AUTOEXTEND undo datafiles, you don’t need to specify undo_retention. If you specify, then Oracle ignores it. The database automatically tunes undo_retention parameter for the best possible retention based on system activities and undo tablespace size.
For AUTOEXTEND undo datafiles, the database attempts to maintain undo information for the period specified by this parameter. If necessary, Oracle extends datafiles to ensure that undo information is retained for the duration specified here. Please note that Oracle doesn’t automatically shrink the datafiles when undo tablespace extends and becomes too large.
Enable Row Movement
Enable Row Movement is a pre-requisite for FLASHBACK TABLE <table> TO SCN/TIMESTAMP/RESTORE POINT statement. You must execute ALTER TABLE <table> ENABLE ROW MOVEMENT to enable row movement. This clause grants Oracle permission to move rows within the table. Row movement changes row ids of the rows. FLASHBACK TABLE <table> TO SCN/TIMESTATMP/RESTORE POINT statement deletes and then inserts rows, causing row ids to change from their original values.
Please don’t confuse Row Movement with Row Migration and Row Chaining. Row Chaining occurs when Oracle cannot fit a row in one block and the row is spread across more than one Oracle data blocks. Row migration occurs when Oracle migrates a row to a new block without changing its row id.
By default, row movement is disabled. You need to explicitly enable row movement with CREATE TABLE or ALTER TABLE clauses. Please note that Oracle10g invalidates objects when you enable row movement for a table. I don’t see any reasons why row movement should not be enabled unless
  • Your application stores ROWIDs for future queries and DML operations.
  • Your application relies upon Oracle exceptions to prevent partition key updates.
Please note that FLASHBACK TABLE <table> TO BEFORE DROP doesn’t require row movement enabled. In addition, ALTER TABLE <table> SHRINK and Partition Key Update operations require row movement to be enabled.