Aug 18 2010
This is a good Metalink document for troubleshooting issues with creation or refresh or maintenance of materialized views.
MVIEW ‘ORA-’ error diagnosis. For Materialized View CREATE or REFRESH (Doc ID 1087507.1)
The diagnostic is listed by the MView’s ORA errors.
May 16 2010
Last week we’ve encountered the archived-log-gap-missing issue on Oracle 10.2.0.3 physical standby. Looking from V$MANAGED_STANDBY, it showed waiting for gap of the archived log sequence #53713.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CONNECTED 0 0 0 0 ARCH CLOSING 2 58067 102401 1360 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 2 58068 2 1823 RFS IDLE 0 0 0 0 MRP0 WAIT_FOR_GAP 1 53713 0 0
The alert log showed similarly with additional information saying that it was trying to open the missing log and the Fetch Archive Log (FAL) was requesting the gap sequence from primary, but all failed.
Media Recovery Log /opt/oracle/admin/PROD/standby/arch_1_53713_645984751 Error opening /opt/oracle/admin/PROD/standby/arch_1_53713_645984751 Attempting refetch Media Recovery Waiting for thread 1 sequence 53713 Fetching gap sequence in thread 1, gap sequence 53713-53713 FAL[client]: Error fetching gap sequence Wed May 12 15:26:44 2010 FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 53713-53713 DBID 2748812654 branch 645984751 FAL[client]: All defined FAL servers have been attempted.
Interestingly, the specific archived log file (arch_1_53713_645984751) existed already on the standby server with proper permission. Not only that, Data Guard was aware of it when querying the v$archived_log.
select sequence#, name, archived, applied from v$archived_log where sequence# like '%53713%'; SEQUENCE# NAME ARC APP ---------- ------------------------------------------------------- --- --- 53713 /opt/oracle/admin/PROD/standby/arch_3_53713_645984751 YES NO
An attempt to register it again obviously failed.
SQL> ALTER DATABASE REGISTER LOGFILE '/opt/oracle/admin/PROD/standby/arch_1_53713_645984751'; ALTER DATABASE REGISTER LOGFILE '/opt/oracle/admin/PROD/standby/arch_1_53713_645984751' * ERROR at line 1: ORA-16089: archive log has already been registered
At this point, it did not seem like anything we could do. Even trying to copy the archived file to another location and re-register with a different path did not help either.
Reading blog post by Jason, this might be a bug as described in the document ID 5576816.8 – “Bug 5576816 FAL gap resolution does not work with max_connection set in some scenario.” The bug’s description says that “Gap resolution appears to hang after exhausting disk space on the standby system. The hang persists even after additional disk space is made available.” This seems to fit our incident because before having this issue, the disk space on the standby was filled up. And even after cleanup, the gap resolution appeared to hang.
The suggested workaround from the document is to disable parallel archival and bounce the primary instance. Um…
The restart of the primary instance was something we’re hesitant to do especially when without a clear description of what it will do. Even though a suggestion of using “kill -9″ on the archiver (ARC) OS processes in the post’s comments with no downtime was very temping, it was still obviously too risky to do in the production environment.
Fortunately, while looking into other options, we’ve found this blog from Andy. It’s interesting that once we tried “alter database register” again but now with “or replace” keyword, it immediately fixed this issue.
ALTER DATABASE REGISTER OR REPLACE PHYSICAL LOGFILE '/opt/oracle/admin/BMCPD/standby/arch_1_53713_645984751';
Apr 26 2010
Oracle 11g Release 2 for Windows was just released this month. With the availability of the grid infrastructure in this version, I plan to install it on my Windows 7 desktop to see what it can do even if it is just on stand alone environment.
In order for database to use Automatic Storage Management (ASM), it requires the Grid Infrastructure. In addition to ASM, Grid Infrastructure will also provide Oracle Restart to manage the Oracle processes (database, listener, and ASM).
One of the first issues I’ve encountered is the new requirement that the clusterware files (OCR & Voting) must be on ASM. I have to admit even though I’ve done ASM on Solaris and Linux before, but never on Windows. Since this is mandatory, I will give it a try. And since I will use ASM for clusterware files, I plan to use it for database data files as well.
In order to use ASM, I’m required to provide the unformatted (raw) basic disks. I plan to use the existing disks without adding new physical ones. Fortunately in Windows 7, I can use the disk management (diskmgmt.msc) tool to shrink volume and create a new logical disk from claimed space. Note that you may have multiple physical disks on your machine, but ASM supports and recognizes only logical drives on the Basic disk (not Dynamic disk). Click here if you’re interested in differences between Basic and Dynamic disks.
Once data volume is shrunk, I can create a new volume and then a logical drive. The new drive must not be formatted or having a drive letter assigned to it. Here is the guidelines from Oracle document on “create disk partitions”.
To use ASM with direct attached storage or SAN, the disks must be stamped with a header. This can be accomplished by using either asmtool (command-line version) or asmtoolg (GUI version). Since we will install Oracle grid infrastructure in interactive mode, the asmtoolg will be called during the configuration. Somehow, if I tried to launch the asmtoolg outside Oracle grid infrastructure installation, I always encountered error with no disks found. However, within the Oracle grid infrastructure installation, there is no issue.
In general, the installation went well. I’ve encountered few issues which I’ve documented them in the documents below. The snapshots of steps here are for educational purpose only.
Windows 7 – Disk Preparation for ASM
Oracle 11g R2 Grid Infrastructure for Standalone Server Installation on Windows
Oracle 11g R2 Software Installation for Single Instance Database on Windows
Apr 16 2010
I found this tip from Oracle document while looking for a solution for session hung at the SQL prompt after issuing “alter tablespace read only”. Usually “alter tablespace read only” executes very quickly. However, the likelihood cause of waiting for tablespace to become read-only is due to existing in-flight transactions started before “alter tablespace read only” are still running.
To identify these transactions that are preventing the read-only tablespace is to first identify the “alter tablespace read only” session.
SELECT SQL_TEXT, SADDR FROM V$SQLAREA,V$SESSION WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS AND SQL_TEXT LIKE 'alter tablespace%'; SQL_TEXT SADDR ---------------------------------------- ---------------- alter tablespace tbs_tts1 read only 0000040634C0D8B8
Based on the identified session address and start SCN number, we can find the earlier executions before the read-only statement by querying the v$TRANSACTION order by ascending start SCN.
SELECT SES_ADDR, START_SCNB FROM V$TRANSACTION ORDER BY START_SCNB; SES_ADDR START_SCNB ---------------- ---------- 0000040634C254F8 2976616884 --> Waiting on this transaction 0000040634C0D8B8 2980274305 --> alter tablespace read only 0000040634C53858 2980283454
From the session address of the blocking transaction, we then can find information about that session.
SELECT T.SES_ADDR, S.USERNAME, S.MACHINE FROM V$SESSION S, V$TRANSACTION T WHERE T.SES_ADDR = S.SADDR ORDER BY T.SES_ADDR SES_ADDR USERNAME MACHINE ---------------- -------------------- ------------------ 0000040634C254F8 RT_ADMIN isdweb1
Once the session is identified, an appropriate action whether to terminate this session can be decided. In our case, it was just a run-away session. Once terminated, alter tablespace read only completed right away.
Reference: Oracle Database Administrator’s Guide 11g Release 1 (11.1) - Making a Tablespace Read-Only
Feb 20 2010
I wrote in my previous post about the Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g. However, it did not cover another PL/SQL network utility package named UTL_INADDR which retrieves host names and IP addresses of local and remote hosts.
You can read some usage samples of the UTL_INADDR from Eddie Awad’s blog.
Similar to those UTL_ packages, in 11g, you will be required to configure the access control list in order to use the UTL_INADDR. Otherwise, by default, you will receive errors as follows:
TEST_USER @DB11> SELECT utl_inaddr.get_host_name FROM dual; SELECT utl_inaddr.get_host_name FROM dual * ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_INADDR", line 4 ORA-06512: at "SYS.UTL_INADDR", line 35 ORA-06512: at line 1
Two simple steps to configure are:
1. Create an access control list and its privilege definition.
SQL> connect / as sysdba
begin
dbms_network_acl_admin.create_acl (
acl => 'Resolve_Access.xml', -- Name of the access control list XML file
description => 'Resolve Network Access using UTL_INADDR', -- Brief description
principal => 'TEST_USER', -- First user account or role being granted or denied permission
-- this is case sensitive,
-- but typically user names and roles are stored in upper-case letters
is_grant => TRUE, -- TRUE = granted, FALSE = denied
privilege => 'resolve', -- connect or resolve, this setting is case sensitive,
-- so always enter it in lowercase
-- connect if user uses the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL
-- resolve if user uses the UTL_INADDR
start_date => null, -- optional, null is the default
-- in format of timestamp_with_time_zone (YYYY-MM-DD HH:MI:SS.FF TZR)
-- for example, '2008-02-28 06:30:00.00 US/Pacific'
end_date => null -- optional, null is the default
);
commit;
end;
/
Note that the privilege used for UTL_INADDR is resolve in lowecase.
You can add more users or roles using DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE.
To verify a newly-created ACL.
SQL> SELECT any_path FROM resource_view WHERE any_path like '/sys/acls/Resolve%.xml'; ANY_PATH -------------------------------------------------------------------------------- /sys/acls/Resolve_Access.xml
2. Assign the the access control list to one or more network hosts.
begin
dbms_network_acl_admin.assign_acl (
acl => 'Resolve_Access.xml', -- Name of the access control list XML file to be modified
host => '*', -- Network host to which this access control list will be assigned
-- This a host name or IP address or wild card name
lower_port => null, -- (optional)
upper_port => null); -- (optional)
commit;
end;
/
TEST_USER @DB11> SELECT utl_inaddr.get_host_name FROM dual; GET_HOST_NAME -------------------------------------------------------------------------------- hostname1
Reference: Oracle document on Managing Fine-Grained Access to External Network Services
Nov 12 2009
Oracle Database 11g Release 2 (11.2.0.1.0) for Solaris (SPARC) (64-bit) platform is now available for download.
Nov 10 2009
Our organization requires a regular password change on some database accounts for security compliance. If this account is used in the database link in other database, that database link has to be dropped and recreated with an updated password.
This changes in 11gR2 because it now offers the alter database link to change password. No more drop and recreate database link!
Sample here is on the database where database link is located:
The password of the database link’s account has just been changed.
db11gr2 SQL> select count(*) from tb_test@DL_TEST; select count(*) from tb_test@DL_TEST * ERROR at line 1: ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from DL_TEST db11gr2 SQL> alter database link DL_TEST connect to dblink_test identified by dblink_test; Database link altered. db11gr2 SQL> select count(1) from tb_test@DL_TEST; COUNT(1) ---------- 6304
This option is not available in the pre-11gR2.
db11gr1 SQL > alter database link DL_TEST connect to dblink_test identified by dblink_test; alter database link DL_TEST connect to dblink_test identified by dblink_test * ERROR at line 1: ORA-02231: missing or invalid option to ALTER DATABASE
Oct 19 2009
The File Watcher is a scheduler object that starts a job whenever files whose attributes met the defined criteria arrived on a system. These criteria include the name, location, and other properties of a file. When the file watcher detects the arrival of the designated file, it raises a file-arrival event. The event message, which has all information on the newly-arrived file, can then be used to process the file.
This new feature simplifies the configurations of the most common triggering event in the data load/batch processing which is to detect the arrival of files.
File Watcher configuration
Setup a new database account to manage the file watcher.
SQL> create user watcher_user identified by watcher_pwd quota unlimited on users; User created SQL> grant connect to watcher_user; Grant succeeded. SQL> grant EXECUTE on SYS.SCHEDULER_FILEWATCHER_RESULT to watcher_user; Grant succeeded.
Other grants needed to complete the tests:
grant create table, create procedure, create job to watcher_user; grant execute on dbms_lock to watcher_user; grant execute on dbms_system to watcher_user; grant manage scheduler to watcher_user; SQL> create or replace directory STAGING_DIR as '/home/oracle/staging'; Directory created. SQL> grant read, write on directory staging_dir to watcher_user; Grant succeeded.
Now as a new watcher_user, we will configure the File Watcher.
1. Create a credential using the OS privilege for file access.
begin dbms_scheduler.create_credential( credential_name => 'watch_credential', username => 'oracle', password => 'oracle'); end; /
2. Create a table to store data processed from file.
create table t_staging_files( upload_timestamp timestamp, file_name varchar2(100), file_size number, contents clob );
3. The procedure will process file data and put into a database table.
create or replace procedure process_files
(payload IN sys.scheduler_filewatcher_result)
is
l_clob clob;
l_bfile bfile;
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
src_csid NUMBER := NLS_CHARSET_ID ('AL32UTF8');
lang_context INTEGER := dbms_lob.default_lang_ctx;
warning INTEGER;
begin
insert into t_staging_files (
upload_timestamp , file_name, file_size, contents)
values(
payload.file_timestamp,
payload.directory_path || '/' || payload.actual_file_name,
payload.file_size,
empty_clob()
) returning contents into l_clob;
l_bfile := bfilename('STAGING_DIR', payload.actual_file_name);
dbms_lob.fileopen(l_bfile);
dbms_lob.loadclobfromfile (
l_clob,
l_bfile,
dbms_lob.getlength(l_bfile),
dest_offset,
src_offset,
src_csid,
lang_context,
warning
);
dbms_lob.fileclose(l_bfile);
end;
/
4. Create a Program object with a Metadata argument.
begin
dbms_scheduler.create_program (
program_name => 'file_watcher',
program_type => 'stored_procedure',
program_action => 'process_files',
number_of_arguments => 1,
enabled => false);
dbms_scheduler.define_metadata_argument (
program_name => 'file_watcher',
metadata_attribute => 'event_message',
argument_position => 1);
dbms_scheduler.enable('file_watcher');
end;
/
PL/SQL procedure successfully completed.
5. Create a File Watcher
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'my_file_watcher',
directory_path => '/home/oracle/staging',
file_name => '*',
credential_name => 'watch_credential',
destination => null,
enabled => false);
end;
/
PL/SQL procedure successfully completed.
6. Create an Event-Based Job that references the File Watcher.
begin
dbms_scheduler.create_job(
job_name => 'staging_file_job',
program_name => 'file_watcher',
event_condition => 'tab.user_data.file_size > 10',
queue_spec => 'my_file_watcher',
auto_drop => false,
enabled => false);
dbms_scheduler.set_attribute('staging_file_job','parallel_instances',true);
end;
/
7. Enable all objects
begin
dbms_scheduler.enable('my_file_watcher,staging_file_job');
end;
/
8. Perform validation
$ echo "Hello World Hello World" > /home/oracle/staging/test_file.txt
After waiting for about 10-15 minutes,
col UPLOAD_TIMESTAMP format a20 col FILE_NAME format a20 col CONTENTS format a20 select * from t_staging_files; UPLOAD_TIMESTAMP FILE_NAME FILE_SIZE CONTENTS -------------------- -------------------- ---------- ----------------------- 13-OCT-09 01.42.04.0 /home/oracle/staging 23 Hello World Hello World 00000 PM /test_file.txt
By default, the file watcher checks for the arrival of files every 10 minutes. You can adjust this interval as follows:
as SYS user
begin
DBMS_SCHEDULER.SET_ATTRIBUTE('FILE_WATCHER_SCHEDULE','REPEAT_INTERVAL','FREQ=MINUTELY;INTERVAL=2');
end;
/
You can view information about file watchers by querying the views *_SCHEDULER_FILE_WATCHERS.
col FILE_WATCHER_NAME format a20 col DIRECTORY_PATH format a20 col FILE_NAME format a5 col CREDENTIAL_NAME format a17 SELECT file_watcher_name, directory_path, file_name, credential_name FROM dba_scheduler_file_watchers; FILE_WATCHER_NAME DIRECTORY_PATH FILE_ CREDENTIAL_NAME -------------------- -------------------- ----- ----------------- MY_FILE_WATCHER /home/oracle/staging * WATCH_CREDENTIAL
References:
Oracle 11gR2 document: Starting a Job When a File Arrives on a System
Oct 18 2009
The DBFS (Database File System), one of the new 11gR2 features, takes advantage of the SecureFiles feature (which is new in 11gR1). The SecureFiles provide powerful file storage features (including de-duplication, compression, etc.) which removes performance barrier to storing files in the database. This stands in contrast to LOB (now called the BasicFiles). Not only does the DBFS provide the standard file system interface (path names, directories and links) to store and access files in the database, its benefits also include security and performance from using SecureFiles.
DBFS configuration
1. Follow the DBFS prerequisites and installation instructions here.
2. Install the FUSE package.
Since oracle user will do sudo, add it into /etc/sudoers.
$ tar -xzvf fuse-2.7.3.tar.gz $ cd fuse-2.7.3 $ ./configure --prefix=/usr/src/kernels/`uname -r`-`uname -p` $ make $ sudo su # make install # /sbin/depmod # /sbin/modprobe fuse # chmod 666 /dev/fuse # echo "/sbin/modprobe fuse" >> /etc/rc.modules
3. Create a database user and a tablespace to store data.
SQL> create user dbfs_admin identified by dbfs_admin;
SQL> grant create session, resource, create view to dbfs_admin;
SQL> grant dbfs_role to dbfs_admin;
SQL> create tablespace tbs_dbfs datafile '/u1/oradata/DB11LNX/dbfs.dbs' size 100m;
The tablespace must be the Automatic Segment Space Management (ASSM) in order to use the SecureFiles. This is the default setting in 11g.
3. Create a file system using dbfs_create_filesystem.sql (located at $ORACLE_HOME/rdbms/admin).
The dbfs_create_filesystem.sql creates a partitioned file system which stores data in the multiple physical segments. The files will be distributed randomly in these partitions. This way it gives the best performance and scalability.
$ cd $ORACLE_HOME/rdbms/admin
SQL> conn dbfs_admin/dbfs_admin
SQL> @dbfs_create_filesystem.sql tbs_dbfs staging_area
Note that the last argument (e.g., staging_area) will be visible as the name of the file system.
When creating a new file system, a new partitioned table will be created having its name from the file system’s name with T_ prefix.
SQL> select table_name, partition_name from user_tab_partitions where table_name like '%STAGING_AREA'; TABLE_NAME PARTITION_NAME ----------------------------- ------------------------------ T_STAGING_AREA SYS_P141 T_STAGING_AREA SYS_P142 T_STAGING_AREA SYS_P143 T_STAGING_AREA SYS_P144 T_STAGING_AREA SYS_P145 T_STAGING_AREA SYS_P146 T_STAGING_AREA SYS_P147 T_STAGING_AREA SYS_P148 T_STAGING_AREA SYS_P149 T_STAGING_AREA SYS_P150 T_STAGING_AREA SYS_P151 T_STAGING_AREA SYS_P152 T_STAGING_AREA SYS_P153 T_STAGING_AREA SYS_P154 T_STAGING_AREA SYS_P155 T_STAGING_AREA SYS_P156 16 rows selected.
4. Verify by copying files into the exposed file system.
SQL> conn dbfs_admin/dbfs_admin SQL> !cat ~/dbfs_show_content.sql col pathname format a40 col pathtype format a10 col contents format a20
select pathname, pathtype, utl_raw.cast_to_varchar2(filedata) as contents from dbfs_content order by std_creation_time;
This shows the default directories currently in database.
SQL> @~/dbfs_show_content.sql PATHNAME PATHTYPE CONTENTS ------------------------------ ---------- ------------------------------ /staging_area directory /staging_area/.sfs directory /staging_area/.sfs/RECYCLE directory /staging_area/.sfs/attributes directory /staging_area/.sfs/content directory /staging_area/.sfs/snapshots directory /staging_area/.sfs/tools directory 7 rows selected.
Let’s create a directory named test_dir. Please note that base directory name staing_area is from the file system created previously. The dbfs_client can be executed from any systems which meet the prerequisite requirements mentioned in the step 1.
{client}$ dbfs_client dbfs_admin@DB11LNX --command mkdir dbfs:/staging_area/test_dir
Password: dbfs_admin
A new directory test_dir is now visible as a new record.
SQL> @~/dbfs_show_content.sql PATHNAME PATHTYPE CONTENTS ------------------------------ ---------- ------------------------------ /staging_area directory /staging_area/.sfs directory /staging_area/.sfs/RECYCLE directory /staging_area/.sfs/attributes directory /staging_area/.sfs/content directory /staging_area/.sfs/snapshots directory /staging_area/.sfs/tools directory /staging_area/test_dir directory 8 rows selected.
Copy a file into it.
{client}$ echo "hello world" > /tmp/dbfs_file
{client}$ dbfs_client dbfs_admin@DB11LNX --command cp /tmp/dbfs_file dbfs:/staging_area/test_dir
Password: dbfs_admin
/tmp/dbfs_file -> dbfs:/staging_area/test_dir/dbfs_file
SQL> @~/dbfs_show_content.sql PATHNAME PATHTYPE CONTENTS ---------------------------------------- ---------- ------------------------------ /staging_area directory /staging_area/.sfs directory /staging_area/.sfs/attributes directory /staging_area/.sfs/tools directory /staging_area/.sfs/snapshots directory /staging_area/.sfs/RECYCLE directory /staging_area/.sfs/content directory /staging_area/test_dir directory /staging_area/test_dir/dbfs_file file hello world
9 rows selected.
Optionally you can also mount this file system on the client, so the file operations can be done without invoking dbfs_client every time.
Create a mount point. (*)
{client}# mkdir /mnt/dbfs
{client}# chown oracle:dba /mnt/dbfs
{client}# chmod 755 /mnt/dbfs
$ dbfs_client dbfs_admin@DB11LNX /mnt/dbfs password: dbfs_admin :
Somehow on my test system, the prompt never returned even though the file system is mounted successfully. So in order to test it, I left this window open, and execute the remaining of commands in a new window.
The prompt will never return using above syntax. However, the following example will read the password from a file, mount a file system, and then free the terminal. (Thanks Simon for pointing this out.)
$ nohup dbfs_client dfs_admin@DB11LNX /mnt/dbfs < passwordfile.f &
Note that now I can perform all standard Unix file/directory syntaxes to this mount point.
{client}$ echo "hello world 2" > /tmp/dbfs_file2
{client}$ cp /tmp/dbfs_file2 /mnt/dbfs/staging_area/test_dir
SQL> @~/dbfs_show_content.sql PATHNAME PATHTYPE CONTENTS ---------------------------------------- ---------- ------------------------------ /staging_area directory /staging_area/.sfs directory /staging_area/.sfs/attributes directory /staging_area/.sfs/tools directory /staging_area/.sfs/snapshots directory /staging_area/.sfs/RECYCLE directory /staging_area/.sfs/content directory /staging_area/test_dir directory /staging_area/test_dir/dbfs_file file hello world /staging_area/test_dir/dbfs_file2 file hello world2
10 rows selected.
There are a lot of administrative options you can do from here including using Oracle wallet so no password will be prompted when mounting a DBFS store. You can also mount DBFS through the fstab. See instructions in the Oracle 11gR2′s DBFS File System Client.
One of the claimed benefits of using DBFS is I/O throughput performance in a range of 5-7 GB/sec. I plan to perform performance tests and report the results in the next post. Stay tune!.
(*) Initially, I received this error when trying to run dbfs_client.
dbfs_client: error while loading shared libraries: libfuse.so.2: cannot open shared object file: No such file or directory
By creating a softlink to the /lib folder, it’s solved the problem.
# ln -s /usr/src/kernels/2.6.18-92.el5-i686/lib/libfuse.so.2 /lib/libfuse.so.2
Oct 06 2009
This is one of the 11g features I read it once when it was first released but did not see its significance until now. Last week we just migrated an application from 9i to 11g. During a test of the send mail package using UTL_SMTP, we got this error, “ORA-24247: network access denied by access control list (ACL).” After a quick search, I’m in luck because I found a lot of articles written about this new 11g feature. However, I particularly find these two well-written concepts and samples from Arup Nanda’s Access Control Lists for UTL_TCP/HTTP/SMTP and Oracle-Base’s Fine-Grained Access to Network Services in Oracle Database 11g Release 1 very helpful.
My sample here is from our test case:
1. The send mail package which executes the UTL_SMTP failed.
TEST_USER SQL> exec pkg_LoadStatus.SendMail('user@company.com', 'Test Subject', 'Hello World');
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "pkg_LoadStatus", line 283
ORA-06512: at line 3
2. To fix it, an ACL has to be created.
The principal is the user or role to be added into this ACL. In this case, the TEST_USER account is added during the ACL creation. This field is case sensitive.
SQL> connect / as sysdba begin dbms_network_acl_admin.create_acl ( acl => 'Mail_UTL_Access.xml', description => 'Mail UTL Network Access', principal => 'TEST_USER', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null ); commit; end; /
The description of each variable is clearly described in the Oracle-Base’s article.
3. Verify a newly-created ACL.
SQL> SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/%.xml';
ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/Mail_UTL_Access.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3684e24e04403ba6c65c6_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf36e4e24e04403ba6c65c6_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3724e24e04403ba6c65c6_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3764e24e04403ba6c65c6_acl.xml
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/acls/ro_anonymous_acl.xml
4. Optionally you can add more users or roles into this ACL by using the add_privilege procedure. This is similar to the create_acl procedure except no description. Sample shown here is to add ADMIN_ADMIN_ROLE role.
begin dbms_network_acl_admin.add_privilege ( acl => 'Mail_UTL_Access.xml', principal => 'APP_ADMIN_ROLE', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null); commit; end; /
5. Add a host and port range allowed.
begin dbms_network_acl_admin.assign_acl ( acl => 'Mail_UTL_Access.xml', host => 'smtp.company.com', lower_port => 1, upper_port => 1024); commit; end; /
6. Test the send mail package again. This time there is no error, and the recipient receives email.
TEST_USER SQL> exec pkg_LoadStatus.SendMail('user@company.com', 'Test Subject', 'Hello World');
PL/SQL procedure successfully completed
|
|
|
|
|
|
