Chapter 7. Techniques for Creating a Multimedia Database

This chapter covers the technical aspects of setting up a database to use Oracle Multimedia. It contains tips, codes, and useful techniques for setting up and managing an Oracle Database and covers the core storage capabilities of the Oracle Database. The goal is to enable the database administrator to make correctly informed decisions about the physical structure of database storage objects, in particular tablespaces, datafiles, and database capabilities.

Tier architecture

The rules surrounding technology are constantly changing. Decisions and architectures based on current technology might easily become out of date with hardware changes. To best understand how multimedia and unstructured data fit and can adapt to the changing technology, it's important to understand how and why we arrived at our different current architectural positions. In some cases we have come full circle and reinvented concepts that were in use 20 years ago. Only by learning from the lessons of the past can we see how to move forward to deal with this complex environment.

In the past 20 years a variety of architectures have come about in an attempt to satisfy some core requirements:

• Allow as many users as possible to access the system

• Ensure those users had good performance for accessing the data

• Enable those users to perform DML (insert/update/delete) safely and securely (safely implies ability to restore data in the event of failure)

The goal of a database management system...

Basic database configuration concepts

When a database administrator first creates a database that they know will contain digital objects, they will be confronted with some basic database configuration questions covering key sizing features of the database.

When looking at the Oracle Database there are a number of physical and logical structures built inside the database. To avoid confusion with other database management systems, it's important to note that an Oracle Database is a collection of schemas, whereas in other database management the terminology for a database equates to exactly one schema. This confusion has caused a lot of issues in the past. An Oracle Database administrator will say it can take 30 minutes to an hour to create a database, whereas a SQL Server administrator will say it takes seconds to create a database. In Oracle to create a schema (the same as a SQL Server database) also takes seconds to perform.

For the physical storage of tables, the Oracle Database is composed...

Oracle Securefile architecture

In Oracle 11g, Oracle rearchitected the management of lobs. This new structure is called SECUREFILES. The storage structure also included a number of new optimizations and security features. It is anticipated that the older storage method (now called BASICFILES) will be made obsolete in the next major release.

The storage architecture also introduced a new method for indexing the lobs, enabling faster access to parts of it. Reading and writing were also improved.

The following sections describe some of the key storage options a database administrator might want to consider when setting up tables that hold unstructured data. Example table creation statements are included at the end of the chapter.

Enabling storage in row

This option is useful for small lobs. These are ones that are less than 4000 bytes. When this option is enabled, Oracle stores the first 4000 bytes in the row just like if the column was a RAW (4000) one. If the lob is larger than 4000 bytes, the...

Where does Oracle Multimedia fit in?

The Oracle Database supports three types of binary data types:

• Binary Large Object (BLOB): This is a data type that contains binary data. Two structures exist in Oracle. The traditional one is called BASIC and a newer faster one, which was introduced in Oracle 11, is called SECUREFILES. The BASIC format will be desupported in a later release. The BLOB replaces the LONG data type which was the standard data type for any binary data. The LONG data type had a number of key limitations including only one being allowed per table and minimal support for controlling its storage. As of Oracle11g a BLOB does not have a maximum size.

• Character Large Object (CLOB): This is a lob designed for text data. It is very similar in behavior to a VARCHAR of unlimited length. In Oracle 11g the difference is minor and text functions can be applied to a CLOB. A CLOB is useful for storing XML data. A CLOB is controlled by the database character set, whereas if an NCLOB is...

Understanding the ORDSYS data types

This section covers methods for creating and working with tables using the Oracle Multimedia data types. Further information on each of the types can be found in Appendix E, Loading and Reading.

Creating a table

As the ORDSYS Multimedia types reference ORDSYS.ORDSOURCE, which in turn references a BLOB, the database administrator has the option of specifying all the storage parameters for each of the columns. For example, the table created looks like the following:

create table myimage(
 pk number,
 myphoto ORDSYS.ORDIMAGE,
 myvideo ORDSYS.ORDVIDEO
)

The resulting storage parameters would like like the following:

create table myimage
(
 pk number,
 myphoto ORDSYS.ORDIMAGE,
 myvideo ORDSYS.ORDVIDEO
)
tablespace relational_tbls pctfree 5 storage( pctincrease 0 maxextents unlimited)
   LOB (myphoto.source.localdata) 
    STORE AS SECUREFILE l_myphoto
      (TABLESPACE image_tbls
       disable storage in row
       RETENTION AUTO
       NOCOMPRESS
       KEEP_DUPLICATES...

Creating a schema

The following section goes through an example schema creation configuration:

create user multimedia identified by mypassword;
grant create session, create table, create procedure to multimedia;
grant create view, create role, create trigger to multimedia;

The following statements enable the schema to access the temporary tablespaces (in this case it's assumed to be called TEMP) and be able to have storage rights in the tablespace called USERS:

alter user multimedia temporary tablespace temp;
alter user multimedia quota unlimited on users;
alter user multimedia default tablespace users;

The following two permissions are optional but can prove to be useful. They allow the schema to access the dbms_lock.sleep command (useful for asynchronous processing) and the dbms_file_transfer command, allowing the schema to manipulate external multimedia files.

grant execute on dbms_file_transfer to multimedia;
grant execute on dbms_lock to multimedia;

Oracle HTTP servers

There are two HTTP gateway servers available in Oracle. One uses the Oracle listener to act as an HTTP server and it can deliver data from the database using PL/SQL stored procedures. The second involves using Apache and the Mod PL/SQL gateway. Each have advantages and disadvantages in using them.

The Apache HTTP server offers additional capabilities such as virtual directory configuration, SSL, URL rewrites, Single Sign On, and a whole range of open source plugins. The embedded gateway has limitations but is simple to configure and is designed to work with Mod PL/SQL (and Oracle Apex). It's lightweight and is embedded in the Oracle listener, requiring one less process to manage.

Configuring the Oracle embedded gateway

The goal of this section is to show you how to configure Oracle Database for the embedded PL/SQL gateway. For Oracle XE, the embedded gateway comes configured for Apex by default.

All configuration is done via SQL*Plus. SQL*Plus represents the lowest common interface available across all Oracle versions. The command-line interface when used with a text editor enables quick and easy modification of most Oracle features.

Tools like Oracle SQL Developer and Enterprise Manager can also be used, but it is best to have a good understanding of the core capabilities before using GUI development tools:

1. Use SQL*Plus to connect to the database as SYS:

        sqlplus / as sysdba

2. Check to make sure the database parameter dispatchers are correctly configured to allow the database to talk to the listener:

   SQL> show parameter dispatchers

   The output should look like the following:

   NAME          TYPE        VALUE
   ------------------------------------ ----------- ----------...

Configuring Apache

Apache offers an open source multithreaded HTTP Server that can integrate with Oracle.

The Oracle Database can work with either Apache 1.3 or Apache 2.0. The integration is done via Mod PL/SQL. The Mod PL/SQL with Apache 1.3 superseded the previous integration Oracle provided when it provided its own web server.

The multi-threaded nature of the connection means that Apache establishes a number of fixed connections to the database (client/server). Multiple HTTP users then perform requests on a round robin type basis across these fixed connections. This means that there is no guarantee that if a user performs multiple HTTP requests, they will be using the same physical connection to the database. This is a key issue that has to be considered in the development. It means that the PL/SQL program (or Java program) cannot rely on session variables or the content of temporary tables to be there from call to call. Apache manages the load balancing and based on the usage can increase...

HTTPD.CONF file

The master parameter configuration file for Apache is called httpd.conf. It's found in the directory (if the install shown previously is used):

C:\oracle\apache2\instances\instance1\config\OHS\ohs1

There are a lot of parameters in the file and it is self documenting, but for an administrator who just wants to modify the basic values, the following are some of the core parameters in the file based on an Apache install on Windows (keep in mind for Unix, root needs to start the Apache Server if it's going to listen on any port less than 1024). Windows was chosen as all documentation one finds on Apache configuration always covers Unix.

• ServerName: This is the DNS name of the server. On installation the identified name of the server is used. Multiple DNS can use the one HTTP file using the concept of a virtual server.

  ServerName: www.site1.com

• ServerRoot: This is the top level directory, where all the HTTP server configuration and log files are kept.

  ServerRoot: C:\config

• DocumentRoot...

External locations and security

When working with digital objects, it is inevitable that the database administrator will need to configure some type of external access to load external files into the database server. There are four available methods that can be used, each one using very different loading methods.

Oracle directory

A number of external access points can be used when accessing external files. The directory is one that Oracle Multimedia uses. Others include utl_file and it is also possible to use Java to access directories directly. But let's first define an external directory that the multimedia schema can access.

The following is an example on Windows:

create or replace directory LOADING_DIR as
    'C:\multimedia_files';

The following is an example on Unix:

create directory LOADING_DIR as '/u01/multimedia_files';

Granting access to a directory

Once created, access to the directory can be given using Oracle grants:

grant read on directory LOADING_DIR to multimedia;

Access is also implicitly...

Discussing Raid, SSD, SANs, and NAS

When working with unstructured data there are two key considerations that need to be taken into account. The first is that there will likely to be a large amount of data (possibly tens of terabytes) to be loaded in. This requires an infrastructure that can handle large contiguous writes. The hardware used must be able to support high speed storage of hundreds of megabytes of data in bursts.

The other is that reading in data will result in large reads, potentially concurrently, at possibly random locations across any part of the storage. If it's not random and well known, an option to cache the well-known data items to improve performance must be considered. The I/O (or network) channel can become a bottleneck. The best solution for performance is to support multiple channels.

Solid State Disk

A Solid State Disk (SSD) is the equivalent of persistent memory, in that if power is lost the contents are not lost. It is a form of flash memory with the provision...

Setting up Oracle XE to run Oracle Multimedia

Oracle XE has two major limitations that most database administrators would deem to be showstopper issues and not consider XE for use with multimedia. The first issue is the 11 GB database storage limit. If the decision is made to store all digital images outside the database and just use a BFILE or HTTP pointer to reference them, the database storage remaining that can be used for relational data is quite a large amount. An application with auditing can manage tens of thousands of images of any size. The administrator has to now manage backups of external files and deal with the potential for orphaned images, but can still manage a healthy volume of digital images using the remaining powerful features found in the database as well as PL/SQL.

The second major issue is that Oracle XE doesn't come with Java in the database and all the Oracle Multimedia methods are written in Java. This means there is no image processing, metadata extractions, watermarking...

Summary

There are numerous ways to configure an Oracle Database to store large volumes of unstructured data. Some of the key areas to review include the database block size, the size of the UNDO tablespace, the placement and sizes of the redo logs, and the configuration and extent size of the tablespaces used to store the data. Different architectural configurations are available based on the number of disks, CPUs, and available memory on the server. The use of solid state drives can improve load time performance.

Even though the Oracle XE database has no built in support for Java and Oracle multimedia, its usage should not be dismissed outright as a database for storing multimedia as its core architecture offers features and performance characteristics that still make it ideal to use.

Chapter 8, Tuning, will provide an introduction to database tuning for the novice administrator before covering in depth a number of multimedia tuning issues that the database administrator needs to consider...

Exercises

1. Design an architecture to house a set of Oracle Databases having the following server features:

   • 1 x SSD Drive (64 GB)

   • 1 x mirrored drive x 1 TB

   • 1 x 3 TB drive

   • 1 x external 2 TB drive

   • 1 Server CPU with 8 cores

   The server has to use the following:

   • To run vSphere (or equivalent virtualization kernel)

   • Be able to run three Linux operating systems with one Oracle 11g installation on each

   • Each Linux install is to run Oracle and provide full redundancy

   • Be able to store 2 TB of multimedia digital objects across all the three Oracle installs, with one install storing a max of 500 GB of multimedia.

2. You are a software architect and have been tasked to design a new multimedia centric database from scratch. Your goal is to design a storage structure for a database factoring in the following conditions:

   • There can be no restriction on the length of a row stored or the number of columns in a table. It should be possible to create a table with more than 100 columns, with each one of unlimited length.

   • The length...