9 DBFS Hierarchical Store

Constants for DBMS_DBFS_HS Package

DBMS_DBFS_HS.CREATESTORE

This method enables users to create a new DBFS HS store named store_name of type store_type (STORETYPE_TAPE or STORETYPE_AMAZONS3) in schema schema_name (defaulting to the current schema) under the ownership of invoking session user.tbl_name in tablespace tbs_space that will hold store entries in the database.cache_size amount of space to be used to cache the content.

The method has parameter properties, which is a table of (name, value, typecode) tuples. This parameter will not be used now but can be used in future versions to configure initial properties of the store.

Store names must be unique for an owner. But the same store names can be used for different stores owned by different owners.

Currently DBMS_DBFS_HS.CREATESTORE will set certain properties of the store to default values. The user can use the methods DBMS_DBFS_HS.SETSTOREPROPERTY and DBMS_DBFS_HS.RECONFIGCACHE to appropriately change the property values and to set other properties of the store.

DBMS_DBFS_HS.DROPSTORE

This method will delete a previously created DBFS HS store with the name store_name and owned by the invoking session_user.

This method will unregister the store from the DBFS Content API (DBMS_DBFS_CONTENT package). All files in the given store will be deleted from the store (tape or Amazon S3 web service). The database table holding the store's entries in the database will also be dropped by this method.

This method will execute like a DDL (auto-commit before and after its execution).

DBMS_DBFS_HS.RECONFIGCACHE

This procedure reconfigures the parameters of the database cache being used by the store.

The Hierarchical Store uses a level 1 cache and a level 2 cache. The level 1 cache subsumes most of the working set and the level 2 cache is used to perform bulk writes to the backend device.

The DBMS_DBFS_HS package optimistically tries to allocate more than one tarball's worth of size for level 2 cache to facilitate concurrency, though a minimum of one tarball size is necessary for level 2 cache.

The values for cumulative cache size and LOB cache quota decide allocation of space for the two caches. If values are not provided, a user might see an INSUFFICIENT CACHE exception. In that case, it is better to revise the cache parameters in order to have a working store.

If this subprogram successfully executes, its actions cannot be rolled back by the user. In that case, the user should call RECONFIGCACHE again with new or modified parameters.

DBMS_DBFS_HS.SETSTOREPROPERTY

This method will associate properties with a store registered with the Hierarchical Store. Each property will be a name value pair.

Wallet Management

The command-line utility mkstore is used to create wallets and to add aliases for secret store. Use the following commands to create and manage wallets:

Creating wallet:

mkstore -wrl <wallet location> -create

Adding KEY alias. Specify the access_key and secret_key by enclosing it within single quotes.

mkstore -wrl <wallet location> -createCredential <alias> '<access_key>'
 '<secret_key>'

For example:

mkstore -wrl /home/user1/mywallet -createCredential mykey 'abc' 'xyz'

Deleting KEY alias:

mkstore -wrl <wallet location> -deleteCredential <alias>

For example:

mkstore -wrl /home/user1/mywallet -deleteCredential mykey

DBMS_DBFS_HS.GETSTOREPROPERTY

This method retrieves the values of a property, identified by PropertyName, of a store in the database.

DBMS_DBFS_HS.CREATEBUCKET

The AWS bucket, associated with a store of type STORETYPE_AMAZONS3, should already exist when the DBFS HS tries to move content into that bucket.

One way of creating the S3 bucket is to use the DBMS_DBFS_HS.CREATEBUCKET method. The PROPNAME_BUCKET property of the store should be set before this method is called.

DBMS_DBFS_HS.STOREPUSH

This pushes locally cached data to archive store, identified by storename.

DBMS_DBFS_HS.CLEANUPUNUSEDBACKUPFILES

This method removes files created on the external storage device that have no currently used data (content) in them. This method can be executed periodically (perhaps once a week) to clear space on the external storage device. Asynchronously deleting content from the external storage device is useful because it has minimal impact on the OLTP performance. The periodic scheduling can be accomplished using the DBMS_SCHEDULER package.

DBMS_DBFS_HS.REGISTERSTORECOMMAND

A client will use this method to register commands (messages) for a store with the DBFS HS to be sent to the Media Manager for the external storage device associated with the store. These commands are sent before the next read or write of content. When the DBFS HS wants to push or get data to or from the storage device, it will begin an API session to talk to the device. After beginning the session, it will send all registered commands, for that particular device, to the device before writing or getting any data.

DBMS_DBFS_HS.DEREGSTORECOMMAND

This method removes a command (message) that had been previously associated with a store through the REGISTERSTORECOMMAND.

DBMS_DBFS_HS.SENDCOMMAND

This sends a command (message) to be executed on the Media Manager of the external storage device.

USER_DBFS_HS_FILES

This view shows the files archived by this user, and their location on the back end device.

Setting up the Store

Step 1: Call createStore:

Dbms_dbfs_hs.createStore( store_name, store_type, tbl_name, tbs_name, cache_size, lob_cache_quota, optimal_tarball_size, schema_name);

Step 2: Set mandatory and optional properties using the following interface:

Dbms_dbfs_hs.setStoreProperty(StoreName, PropertyName, PropertyValue);

For store_type = STORETYPE_TAPE, mandatory properties are:

PROPVAL_DEVICELIBRARY, PROPVAL_MEDIAPOOL, PROPVAL_CACHESIZE.
 
PROPVAL_CACHESIZE is already set by createStore. 

You can change the value of PROPVAL_CACHESIZE using setStoreProperty.

Optional properties are:

PROPVAL_OPTTARBALLSIZE, PROPVAL_READCHUNKSIZE, PROPVAL_WRITECHUNKSIZE, PROPVAL_STREAMABLE.

For store_type = STORETYPE_AMAZONS3 mandatory properties are:

PROPVAL_DEVICELIBRARY, PROPVAL_CACHESIZE, PROPVAL_S3HOST,PROPVAL_BUCKET, PROPVAL_LICENSEID, PROPVAL_WALLET.

PROPVAL_CACHESIZE is already set by createStore. You can change the value of PROPVAL_CACHESIZE using setStoreProperty.

Optional properties are:

PROPVAL_OPTTARBALLSIZE, PROPVAL_READCHUNKSIZE, PROPVAL_WRITECHUNKSIZE, PROPVAL_STREAMABLE, PROPVAL_HTTPPROXY.

Step 3: Register the store with DBFS Content API using:

Dbms_dbfs_content.registerStore(store_name, provider_name, provider_package);

Step 4: Mount the stores for access using:

Dbms_dbfs_content.mountStore(store_name, store_mount,
 singleton,principal, owner, acl, asof, read_only);

Using the Hierarchical Store

The Hierarchical Store can be used as an independent file system or as an archive solution for SecureFiles.

DBMS_DBFS_HS.storePush(store_name);

DBMS_DBFS_HS.dropStore(store_name, opt_flags);

Example: Using Amazon S3

The following example program configures and uses an Amazon S3 store.

Valid values need to be substituted in some places, indicated by < .. >, for the program to run successfully.

Please refer to DBMS_DBFS_HS documentation for complete details about the methods and their parameters.

Rem Example to configure and use an Amazon S3 store.
Rem
Rem hsuser should be a valid database user who has been granted
Rem the role dbfs_role.
 
connect hsuser/hsuser 
 
Rem The following block sets up a STORETYPE_AMAZONS3 store with
Rem DBMS_DBFS_HS acting as the store provider.
 
declare 
storename varchar2(32) ; 
tblname varchar2(30) ; 
tbsname varchar2(30) ; 
lob_cache_quota number := 0.8 ; 
cachesz number ; 
ots number ; 
begin 
cachesz := 50 * 1048576 ; 
ots := 1048576 ; 
storename := 's3store10' ; 
tblname := 's3tbl10' ; 
tbsname := '<TBS_3>' ; -- Substitute a valid tablespace name
 
-- Create the store.
-- Here tbsname is the tablespace used for the store,
-- tblname is the table holding all the store entities,
-- cachesz is the space used by the store to cache content
--   in the tablespace,
-- lob_cache_quota is the fraction of cachesz allocated
--   to level-1 cache and
-- ots is minimum amount of content that will be accumulated
--   in level-2 cache before being stored in AmazonS3
dbms_dbfs_hs.createStore(
  storename,  
  dbms_dbfs_hs.STORETYPE_AMAZONS3,
  tblname, tbsname, cachesz,
  lob_cache_quota, ots) ; 
 
dbms_dbfs_hs.setstoreproperty(storename,
  dbms_dbfs_hs.PROPNAME_SBTLIBRARY,
  '<ORACLE_HOME/work/libosbws11.so>');
  -- Substitute your ORACLE_HOME path
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_S3HOST,
  's3.amazonaws.com') ; 
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_BUCKET,
  'oras3bucket10') ; 
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_WALLET,
  'LOCATION=file:<ORACLE_HOME>/work/wlt CREDENTIAL_ALIAS=a_key') ;
  -- Substitute your ORACLE_HOME path
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_LICENSEID,
  '<xxxxxxxxxxxxxxxx>') ; -- Substitute a valid SBT license id
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_HTTPPROXY,
  '<http://www-proxy.mycompany.com:80/>') ;
  -- Substitute valid value. If a proxy is not used,
  -- then this property does not need to be set.
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_COMPRESSLEVEL,
  'NONE') ; 
 
dbms_dbfs_hs.createbucket(storename) ; 
 
-- Please refer to DBMS_DBFS_CONTENT documentation
-- for details about this method
dbms_dbfs_content.registerstore(
  storename,
  's3prvder10',
  'dbms_dbfs_hs') ; 
 
-- Please refer to DBMS_DBFS_CONTENT documentation
-- for details about this method
dbms_dbfs_content.mountstore(
  storename,
  's3mnt10') ; 
end ; 
/ 
 
Rem The following code block does file operations
Rem using DBMS_DBFS_CONTENT on the store configured
Rem in the previous code block
 
connect hsuser/hsuser 
 
declare 
path varchar2(256) ; 
path_pre varchar2(256) ; 
mount_point varchar2(32) ; 
store_name varchar2(32) ; 
prop1 dbms_dbfs_content_properties_t ; 
prop2 dbms_dbfs_content_properties_t ; 
mycontent blob := empty_blob() ; 
buffer varchar2(1050) ; 
rawbuf raw(1050) ; 
outcontent blob := empty_blob() ; 
itemtype integer ; 
pflag integer ; 
filecnt integer ; 
iter integer ; 
offset integer ; 
rawlen integer ; 
begin 
 
  mount_point := '/s3mnt10' ; 
  store_name := 's3store10' ; 
  path_pre := mount_point ||'/file' ; 
 
  -- We create 10 empty files in the following loop
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10 ; 
    path := path_pre || to_char(filecnt) ; 
    mycontent := empty_blob() ; 
    prop1 := null ; 
 
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.createFile(
      path, prop1, mycontent) ; -- Create the file
 
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- We populate the newly created files with content
  -- in the following loop
  pflag := dbms_dbfs_content.prop_data +
           dbms_dbfs_content.prop_std  +
           dbms_dbfs_content.prop_opt  ; 
 
  buffer := 'Oracle provides an integrated management '  ||
            'solution for managing Oracle database with '||
            'a unique top-down application management '  ||
            'approach. With new self-managing '          ||
            'capabilities, Oracle eliminates time-'      ||
            'consuming, error-prone administrative '     ||
            'tasks, so database administrators can '     ||
            'focus on strategic business objectives '    ||
            'instead of performance and availability '   ||
            'fire drills. Oracle Management Packs for '  ||
            'Database provide signifiCant cost and time-'||
            'saving capabilities for managing Oracle '   ||
            'Databases. Independent studies demonstrate '||
            'that Oracle Database is 40 percent easier ' ||
            'to manage over DB2 and 38 percent over '    ||
            'SQL Server.'; 
 
  rawbuf := utl_raw.cast_to_raw(buffer) ; 
  rawlen := utl_raw.length(rawbuf) ; 
  offset := 1 ; 
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10 ; 
    path := path_pre || to_char(filecnt) ; 
    prop1 := null;
  
    -- Append buffer to file
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.putpath(
      path, prop1, rawlen,
      offset, rawbuf) ;
 
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- Clear out level 1 cache
  dbms_dbfs_hs.flushCache(store_name) ; 
  commit ; 
 
  -- Do write operation on even-numbered files.
  -- Do read operation on odd-numbered files.
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10; 
    path := path_pre || to_char(filecnt) ; 
    if mod(filecnt, 2) = 0 then 
      -- Get writable file
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_dbfs_content.getPath(
        path, prop2, outcontent, itemtype,
        pflag, null, true) ;
  
      buffer := 'Agile businesses want to be able to '    ||
                'quickly adopt new technologies, whether '||
                'operating systems, servers, or '         ||
                'software, to help them stay ahead of '   ||
                'the competition. However, change often ' ||
                'introduces a period of instability into '||
                'mission-critical IT systems. Oracle '    ||
                'Real Application Testing-with Oracle '   ||
                'Database 11g Enterprise Edition-allows ' ||
                'businesses to quickly adopt new '        ||
                'technologies while eliminating the '     ||
                'risks associated with change. Oracle '   ||
                'Real Application Testing combines a '    ||
                'workload capture and replay feature '    ||
                'with an SQL performance analyzer to '    ||
                'help you test changes against real-life '||
                'workloads, and then helps you fine-tune '||
                'the changes before putting them into'    ||
                'production. Oracle Real Application '    ||
                'Testing supports older versions of '     ||
                'Oracle Database, so customers running '  ||
                'Oracle Database 9i and Oracle Database ' ||
                '10g can use it to accelerate their '     ||
                'database upgrades. '; 
 
      rawbuf := utl_raw.cast_to_raw(buffer) ; 
      rawlen := utl_raw.length(rawbuf) ; 
 
      -- Modify file content
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_lob.write(outcontent, rawlen, 10, rawbuf);
      commit ; 
    else 
      -- Read the file
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_dbfs_content.getPath(
        path, prop2, outcontent, itemtype, pflag) ;
    end if ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- Delete the first 2 files
  filecnt := 0; 
 
  loop 
    exit when filecnt = 2 ; 
    path := path_pre || to_char(filecnt) ; 
    -- Delete file
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.deleteFile(path) ;
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
 
  -- Move content staged in database to Amazon S3 store
  dbms_dbfs_hs.storePush(store_name) ; 
  commit ; 
 
end ; 
/

Example: Using Tape

The following example program configures and uses a tape store.

Valid values need to be substituted in some places, indicated by < .. >, for the program to run successfully.

Please refer to DBMS_DBFS_HS documentation for complete details about the methods and their parameters.

Rem Example to configure and use a Tape store.
Rem
Rem hsuser should be a valid database user who has been granted
Rem the role dbfs_role.
 
connect hsuser/hsuser 
 
Rem The following block sets up a STORETYPE_TAPE store with
Rem DBMS_DBFS_HS acting as the store provider.
 
declare 
storename varchar2(32) ; 
tblname varchar2(30) ; 
tbsname varchar2(30) ; 
lob_cache_quota number := 0.8 ; 
cachesz number ; 
ots number ; 
begin 
cachesz := 50 * 1048576 ; 
ots := 1048576 ; 
storename := 'tapestore10' ; 
tblname := 'tapetbl10' ; 
tbsname := '<TBS_3>' ; -- Substitute a valid tablespace name
 
-- Create the store.
-- Here tbsname is the tablespace used for the store,
-- tblname is the table holding all the store entities,
-- cachesz is the space used by the store to cache content
--   in the tablespace,
-- lob_cache_quota is the fraction of cachesz allocated
--   to level-1 cache and
-- ots is minimum amount of content that will be accumulated
--   in level-2 cache before being stored in AmazonS3
dbms_dbfs_hs.createStore(
  storename,
  dbms_dbfs_hs.STORETYPE_TAPE,
  tblname, tbsname, cachesz,
  lob_cache_quota, ots) ; 
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_SBTLIBRARY,
  '<ORACLE_HOME/work/libobkuniq.so>') ;
  -- Substitute your ORACLE_HOME path
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_MEDIAPOOL,
  '<0>') ;  -- Substitute valid value
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_COMPRESSLEVEL,
  'NONE') ; 
 
-- Please refer to DBMS_DBFS_CONTENT documentation
-- for details about this method
dbms_dbfs_content.registerstore(
  storename,
  'tapeprvder10',
  'dbms_dbfs_hs') ; 
 
-- Please refer to DBMS_DBFS_CONTENT documentation
-- for details about this method
dbms_dbfs_content.mountstore(storename, 'tapemnt10') ; 
end ; 
/ 
 
Rem The following code block does file operations
Rem using DBMS_DBFS_CONTENT on the store configured
Rem in the previous code block
 
connect hsuser/hsuser 
 
declare 
  path varchar2(256) ; 
  path_pre varchar2(256) ; 
  mount_point varchar2(32) ; 
  store_name varchar2(32) ; 
  prop1 dbms_dbfs_content_properties_t ; 
  prop2 dbms_dbfs_content_properties_t ; 
  mycontent blob := empty_blob() ; 
  buffer varchar2(1050) ; 
  rawbuf raw(1050) ; 
  outcontent blob := empty_blob() ; 
  itemtype integer ; 
  pflag integer ; 
  filecnt integer ; 
  iter integer ; 
  offset integer ; 
  rawlen integer ; 
begin 
 
  mount_point := '/tapemnt10' ; 
  store_name := 'tapestore10' ; 
  path_pre := mount_point ||'/file' ; 
 
 
-- We create 10 empty files in the following loop
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10 ; 
    path := path_pre || to_char(filecnt) ; 
    mycontent := empty_blob() ; 
    prop1 := null ; 
 
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.createFile(
      path, prop1, mycontent) ; -- Create the file
 
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- We populate the newly created files with content
  -- in the following loop
  pflag := dbms_dbfs_content.prop_data +
           dbms_dbfs_content.prop_std  +
           dbms_dbfs_content.prop_opt  ; 
 
  buffer := 'Oracle provides an integrated management '  ||
            'solution for managing Oracle database with '||
            'a unique top-down application management '  ||
            'approach. With new self-managing '          ||
            'capabilities, Oracle eliminates time-'      ||
            'consuming, error-prone administrative '     ||
            'tasks, so database administrators can '     ||
            'focus on strategic business objectives '    ||
            'instead of performance and availability '   ||
            'fire drills. Oracle Management Packs for '  ||
            'Database provide signifiCant cost and time-'||
            'saving capabilities for managing Oracle '   ||
            'Databases. Independent studies demonstrate '||
            'that Oracle Database is 40 percent easier ' ||
            'to manage over DB2 and 38 percent over '    ||
            'SQL Server.'; 
 
  rawbuf := utl_raw.cast_to_raw(buffer) ; 
  rawlen := utl_raw.length(rawbuf) ; 
  offset := 1 ; 
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10 ; 
    path := path_pre || to_char(filecnt) ; 
    prop1 := null;
  
    -- Append buffer to file
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.putpath(
      path, prop1, rawlen,
      offset, rawbuf) ;
 
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- Clear out level 1 cache
  dbms_dbfs_hs.flushCache(store_name) ; 
  commit ; 
 
  -- Do write operation on even-numbered files.
  -- Do read operation on odd-numbered files.
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10; 
    path := path_pre || to_char(filecnt) ; 
    if mod(filecnt, 2) = 0 then 
      -- Get writable file
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_dbfs_content.getPath(
        path, prop2, outcontent, itemtype,
        pflag, null, true) ;
  
      buffer := 'Agile businesses want to be able to '    ||
                'quickly adopt new technologies, whether '||
                'operating systems, servers, or '         ||
                'software, to help them stay ahead of '   ||
                'the competition. However, change often ' ||
                'introduces a period of instability into '||
                'mission-critical IT systems. Oracle '    ||
                'Real Application Testing-with Oracle '   ||
                'Database 11g Enterprise Edition-allows ' ||
                'businesses to quickly adopt new '        ||
                'technologies while eliminating the '     ||
                'risks associated with change. Oracle '   ||
                'Real Application Testing combines a '    ||
                'workload capture and replay feature '    ||
                'with an SQL performance analyzer to '    ||
                'help you test changes against real-life '||
                'workloads, and then helps you fine-tune '||
                'the changes before putting them into'    ||
                'production. Oracle Real Application '    ||
                'Testing supports older versions of '     ||
                'Oracle Database, so customers running '  ||
                'Oracle Database 9i and Oracle Database ' ||
                '10g can use it to accelerate their '     ||
                'database upgrades. '; 
 
      rawbuf := utl_raw.cast_to_raw(buffer) ; 
      rawlen := utl_raw.length(rawbuf) ; 
 
      -- Modify file content
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_lob.write(outcontent, rawlen, 10, rawbuf);
      commit ; 
    else 
      -- Read the file
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_dbfs_content.getPath(
        path, prop2, outcontent, itemtype, pflag) ;
    end if ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- Delete the first 2 files
  filecnt := 0; 
 
  loop 
    exit when filecnt = 2 ; 
    path := path_pre || to_char(filecnt) ; 
    -- Delete file
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.deleteFile(path) ;
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
 
  -- Move content staged in database to Amazon S3 store
  dbms_dbfs_hs.storePush(store_name) ; 
  commit ; 
 
end ; 
/