Oracle Database 11g Release 2 (11.2.0.1.0) for Solaris (SPARC) (64-bit) platform is now available for download.
Nov 10 2009
11gR2 New Feature – Alter Database Link to Change Password
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
11gR2 New Feature – File Watchers
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
Starting a Job When a File Arrives on a System
Oct 18 2009
11gR2 New Feature – DBFS Database File System
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 13 2009
OOW09 Day 2
Today all my sessions are pretty much centered around Oracle ASM. Even though all sessions started with basic information about ASM (what is, why, and so on), I found the Q&A time at the end more interesting. One of the sessions – “Extending Oracle ASM in Release 11.2 to Manage All Data” has the panel of the ASM development team for Q & A. It is awesome to see these people who are responsible for this cool product face-to-face.
Oracle ASM Development Team
A common question I’ve heard over and over is that how many diskgroups Oracle recommends in a deployment. The panel said that regardless of the number of databases, still only two diskgroups are recommended as the best practice – one diskgroup for work area (e.g., data files), other for recovery (e.g., flash recovery). The exception of more-than-two diskgroups is if organization has ILM strategy – putting disks of different tiers into different diskgroups based on requirement and strategy.
A quick note from the ASM development team while they’re talking about the ACFS snapshot is that they’re working on more of data services around ACFS such as storage replication. We should hear about it soon. 
