Aug 15 2011

Book Review: Oracle 11g R1/R2 Real Application Clusters Essentials by Ben Prusinski and Syed Jaffer Hussain (Packt Publishing)

Category: 11g,Database — ittichai @ 9:31 pm

Oracle 11g R1/R2 Real Application Clusters EssentialsOracle Real Application Cluster (RAC)  becomes the technology of choice for highly available and highly scalable deployment of enterprise Oracle database environment. With its innovative technology it comes with no surprise its complexity. The complexity is not only with the underlying database technology itself, but also how well you design and implement it to work with other components including operating system, storage subsystem, etc. In order to setup and maintain a successful RAC environment, it not only requires technical knowledge of database administrator (DBA), but also well collaboration and extended planning between all IT and business partners.

Few years back, there has been a movement of DBA 2.0 which says that, in addition to database knowledge,  an extended understanding of operating system, storage and/or networking is encouraged due to more tightly integrated of Oracle database technologies such as Clusterware, RAC, ASM with the underlying subsystems. I agree with this notion but not to the extent that DBAs should perform all works in those areas. (Yes, I’m aware that some DBAs do it all.) However, I think at least they should have enough understandings of all-related technologies to collaborate with those who are responsible for in order to achieve the desired setup. In many cases especially in the large companies where traditional organization division of the job description of administrators is clear cut and no overlapping, being well-rounded with all-related technologies will allow DBA to better discuss the requirements and designs and even assist with the implementation details.

Most Oracle RAC books in the market today tend to focus only on the detailed technical aspects of this database technology. This mainly is good for those Oracle professionals who at least have worked with RAC before. Few books really provide a good foundation for those who are new to this technology. One of those few which I’ve just read recently is Oracle 11g R1/R2 Real Application Clusters Essentials authored by Ben Prusinski and Syed Jaffer Hussain published by Packt Publishing.

What I really like about this book is the fact that it does not jump into the nuts and bolts of this Oracle technology right away but instead it is trying to build up good foundation starting from concept (especially high availability), architecture (of all components not just of database), design, implementation and then even some of the real-world samples. This stepping-stone approach helps readers to understand what is involved and how they should deploy this technology for the high available database.

In the chapter 1, the emphasis of the high availability concept is crucial. The authors precisely points out that high availability should be looked from users’ perspective. Many DBAs or system administrators especially novice ones have a tendency to focus solely on their respective areas. The provided sample of the availability percentage in the “high availability interpretations” section is a good reminder for a need to have an end-to-end high availability design to avoid a single point of failure (SPOF). The chapter goes on to give the definitions of many acronyms (BIA, BCP, DRP, etc.) commonly-used during the planning phase. It is quite useful to be aware of them especially when discussing with business end-users. The chapter ends with providing a whole picture of the high availability solutions (RAC, Data Guard, Streams, Application Server Clustering) from Oracle as a part of the Maximum Availability Architecture (MAA). It is worth to point out that RAC is just one (not the only) of key components for a complete high availability solution.

Chapter 2 looks into the hardware components of Oracle RAC architecture including network, storage, etc. Again, I think the authors reinforce a need to understand all these underlying technologies (at least at high level) even though DBAs might not involve in the actual implementation. But it is crucial to have this knowledge under their belts in order to have the productive discussions of the requirements and design with system administrators.

Chapter 3 goes into the clusterware installation of 11g R1/R2. Even though readers may to be able to find similar information on many web sites and blogs, this book has some extra information which makes an attempt to explain what each action does, for example, what happen when the orainstRoot.sh and root.sh run?

Chapter 4 dedicates to the Automatic Storage Management (ASM) which becomes the only clear choice for storage management in the 11g deployment. The chapter covers all aspects of ASM in both R1 and R2 including new features, configurations, management, etc.

Chapter 5 covers Clusterware management and (some) troubleshooting samples. Instead of showing all possible troubleshooting scenarios (which is impossible to include everything in a chapter or even in a book), the authors did an excellent job of giving an overview of all necessary Clusterware utilities in order to troubleshoot the issues.

Chapter 6 turns our attention to RAC database administration. The structure is similar to that of Clusterware in the chapter 3. The latter part of the chapter also covers the automatic workload management including the topic of the service which is highly recommended to utilize it (not using the default database service.)

Chapter 7 covers all aspects of backup and recovery of Oracle database including the best practices. In addition, new features of RMAN in Oracle 11g R1 and 11g R2 along with OCR and voting disk backup and recovery are discussed.

Chapter 8 tries to give an overview of performance tuning. The intention here seems to just give an overview of the concept and methods of performance tuning as well as new 11g performance features, for example, Real Application Testing (RAT) which has the Database Replay and SQL Performance Analyzer (SPA). This high-level overview focus is understandable because the scope of performance tuning is generally vast. Some of tuning metrics especially relate to the wait events are explained here.

Chapter 9 focuses on the upgrade scenarios. As we all know it is impossible to cover all possible scenarios. But the authors gives sufficient information about overall upgrade processes from 10g R2 to 11g R1 and then to 11g R2 in this chapter. Definitely consulting with Oracle support for more details on individual upgrade case is still highly recommended.

Chapter 10 is very informative on the node addition and removal. The chapter steam-lines the steps pretty well (comparing to the official Oracle document which is very confusing at the first read with the flow of information). (We just went through adding two nodes into the existing two nodes of Oracle 11g R1 two months ago. That’s why I can say that.)

Chapter 11 shows the readers the importance of the end-to-end high availability implementation. The high availability should not stop at the Oracle RAC database, but it must extend to application layer on top of it. In addition to an overview of Oracle EBS (E-Business Suite), this chapter shows the readers how to take full advantage of high availability and performance features in Oracle RAC database.

The last chapter wraps up nicely with information about other tools in the MAA tool suite namely Streams and Data Guard. It covers all various topics of thes tools including concepts, new features, best practices, and overview configuration.

The last section of this book on the additional resources and tools is a nice read and very informative. It is just like a nice dessert at the end of a good meal.

In conclusion, if you’re new to Oracle RAC technology, this is THE book for you. It should give the readers enough understanding to jump on Oracle RAC bandwagon. Advanced DBAs may find that most of the contents in the book are too familiar, but they might be attracted to the new features, comparisons between R1 and R1 as well as some of real-world samples mentioned in this book. Overall I appreciate a very well effort of Ben ad Syed for one of the most complete informative books on Oracle RAC in the market today. Well-deserved kudos to them.

Thanks to Packt Publishing for the opportunity to review this book.

Tags: , , , , , , , , , , ,

Nov 12 2009

11.2.0.1 on Solaris is now available

Category: 11g,Database — ittichai @ 10:35 am

Oracle Database 11g Release 2 (11.2.0.1.0) for Solaris (SPARC) (64-bit) platform is now available for download.

11gr2 solaris

Tags: , ,

Nov 10 2009

11gR2 New Feature – Alter Database Link to Change Password

Category: 11g,Database — ittichai @ 7:26 pm

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

 

Related Topics:

 

Tags: , , ,

Oct 19 2009

11gR2 New Feature – File Watchers

Category: 11g,Database — ittichai @ 9:19 pm

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

Tags: , , , , ,

Oct 18 2009

11gR2 New Feature – DBFS Database File System

Category: 11g,Database — ittichai @ 8:15 pm

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

Tags: , , ,

Oct 13 2009

OOW09 Day 2

Category: Events,OOW — ittichai @ 12:04 am

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.

ASM Development Team

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

Tags: , , , ,