Category Archives: storage

Oracle 12c – Secure file LOBs

In Oracle 12c SecureFiles is now the default storage of LOB when the COMPATIBLE parameter is set to 12.1 or higher.  SecureFiles gives better performance than BasicFiles for unstructured objects.  The following enhancements have been made to SecureFiles:


  1. PDML Operations – parallel DML operations for non-partitioned tables containing SecureFile LOB columns, table cannot contain BasicFile LOB columns. Operations which can use parallelism include:
    1. insert
    2. insert as select
    3. create table as select
    4. delete
    5. update
    6. merger (conditional update and insert)
    7. multi-table insert
    8. SQL*Loader
    9. Import/Export
  2. LogMiner – LogMiner now fully supports SecureFiles LOBs (deduplication of SecureFile LOB SecureFile Database File System (DBFS). Only SQL_REDO columns are filled in SecureFile LOB, SQL_UNDO is not used.



Larry Catt


Oracle 12c – Manage VLDB

Very Large Databases use partitioning to improve the ability to support large data sets.  Breaking large tables into smaller manageable chunks.  This allows for partition pruning, which prevents access to partitions which will not have data queried.  Partitioning and indexing work together to provide parallel processing to search multiple partitions at same time as in data ware housing.


Partitioning Methods: 

You can partition data into a single level or two level for a composite partition.  How the data is accessed determines the best implementation method.   Regardless of partitioning method, you have three ways to control distributing of partitions:

  1. Range
  2. Hash
  3. List


Single Level Partitioning

One or more of the columns acts as the partition key spitting the table into multiple sections.

  1. Range Partitioning – maps data to a range of values in the partition key. Each row is identified with a mapping to partition key.   This is the most common partitioning method and is commonly used with dates.  Partition is created with a VALUES LESS THEN clause which show the upper bound of a date column.  The highest partition can use a MAXVALUE to provide an upper limit.
  2. Hash Partitioning – This method uses a hashing algorithm to map data to partitioning key. Used to evenly spread data among partitions. It is easier to implement then range.  Good to use if no date column exists and no obvious partitioning key exists.
  3. List Partitioning – Allows you to provide a list of values for partitioning key column and divides data base on list. You control exactly how rows are mapped to partitions.  You list a set of values to be placed in separate partitions and use the DEFAULT clause to specify a partition for any value not in the list.


Composite Partitions

This combines the basic partitioning methods into multiple sub-partitions of the same method or different methods.  Provides for a finer level of sub-division of data.   Possible composite partitions are:

  • Range-Range
  • Range-Hash
  • Range-List
  • List-Range
  • List-Hash
  • List-List
  • Hash-Hash



Larry Catt


Oracle 12c – Manage database Structures

Management of Oracle database storage structures provides the ability to persistently store data for long term retrieval and recovery processes.   The management of this structures can be done both from the command line, via EM Express and EM cloud control.    EM Express and EM Cloud control gives the administrator the ability to view, create, and alter various storage structures in the database in a Graphical User Interface through the STORAGE page.   Command line option requires that the administrator knows the syntax of DDL statements to perform the same tasks.  Oracle data structure of concern of storage are:

  • Tablespaces: create, drop, add datafiles, and change status of tablespace
  • Undo Management – shows current status of undo tablespaces and gives validation that it is sized correctly.
  • Redo Log Groups – allows you to create, drop, add members to redo log groups.
  • Archive Logs – Allows you view info about archive logs.
  • Control Files: – see content of control files and allows you to backup control files to trace.

OMF – Oracle Managed Files are intends to simplify the management of files associated with database.  When OMF is in use, the administrator specifies a directory for different file types.  OMF cannot be used with RAW devices.    OMF can be used for the following storage structures:

  • Tablespaces
  • Redo log files
  • control files
  • archive logs
  • block change tracking files
  • Flashback logs
  • RMAN backups.

OMF does nothing with administrative file like trace files, audit files, alert logs, and core files.   OMF requires three initialization parameter to be set

  • DB_CREATE_FILE_DEST – defines directory where data files and temp files are stored, Also used or redo logs and control files if DB_CREATE_ONLINE_LOG_DEST_n is not defined.
  • DB_CREATE_ONLINE_LOG_DEST_n – Defines directory for redo log files and control files.   You can specify up to 5 multiplex locations.
  • DB_RECOVERY_FILE_DEST – Define Fast Recovery Area. , Also used or redo logs and control files if DB_CREATE_ONLINE_LOG_DEST_n is not defined.


Larry Catt







Oracle 12c – Administer ASM

Oracle Automatic Storage Management (ASM) is a storage solution for Oracle that acts as a volume manager providing file system for exclusive use of the database.  When using ASM partitioned disks are assigned to ASM for striping, mirroring, management and ASM implements OMF.   ASM has its own database instance which is specifically designed to manage the disks and distribute I/O for performance.   The benefits of using ASM are:

  1. Simplifies operations by creating database and managing disk space.
  2. Distributes data across physical disks to provide uniform performance.
  3. Rebalance data automatically after storage change.

An ASM instance uses same basic instance as Oracle database instance with a few exceptions.  SGA of ASM is much smaller because it performs fewer activities.   Only function of ASM is to mount disk groups and make associate files available to database instances.  The logical storage elements of an Oracle ASM instance:

  • ASM Disks – Storage devices can be physical disks or LUN (Logical Unit Number) from array or network attached file.
  • ASM Disk Group – Group of ASM Disks managed as a logical unit.
  • ASM Files – File stored in an Oracle ASM disk group can be data files, control files, online redo logs, other types of files.
  • ASM Extents – Raw storage used to hold contents of Oracle ASM file. ASM file is one or more file extents and ASM extent is one or more ASM allocation units.
  • ASM Allocation Units – smallest unit of allocation within a disk group.
  • ASM Instance – special oracle instance to manage ASM disks. Manages metadata and provides access to database instances.

The files that an ASM instance support are:   NOTE ASM does not support trace files, audit files, alert logs, export files, or core files.

  1. Control Files.
  2. Data files
  3. Redo log files
  4. Archive log files.
  5. Temporary files.
  6. Data file backup pieces
  7. Archive log backup pieces.
  8. Persistent initialization parameter files (SPFILE)
  9. Flashback logs
  10. Data Pump dumpset

The file naming conventions in an ASM instance include Fully Qualified Name, Individual Element Names and Alias Names.   A Fully qualified file name is created when a new ASM file is created and gives a complete path name in the ASM file system.   You use the filly qualified file name for existing Oracle ASM files, except for disk group creation.    They have the following format:




An individual element Name is the name of a single file in the ASM instance.   The breakdown of Individual elements of file name includes:


  • +diskgroup – the disk group name preceded by a plus sign. plus sign(+) means root directory of ASM instance.
  • dbname – DB_UNIQUE_NAME of database owning file.
  • filetype – Oracle file type.
  • filetypetag – Type specific info about the file.
  • incarnation – file/incarnation pair ensure uniqueness.


Example of ASM filename.




File creation request do not give full name, it gives alias or disk group name.   If Alias is given, the ASM instance will create it and point at full name.  ASM can take single or multiple file create requests at the same time.

Aliases can be used to reference existing ASM file or create new ones.  Alias name consist of a Disk Group name proceeded by a plus sign (+), a slash (/), and a sting of characters.  Aliases must include the Disk Group name.  They cannot exist in root (+) level.  When a file is created with alias, both are recorded by ASM and you can access by both.  Alias names cannot end in dotted pair of numbers.  Example of Alias names


  • +data/orcldb/control_file_main
  • +data/orcldb/control_file_backup
  • +flashback/recover/backup1.dbf


Example of tablespace datafile creation with alias name:


Create tablespace data_one   datafile ‘+data/orcldb/data_01.dbf’ size 100m;


If an alias is used, it is not an OMF file and will not be automatically deleted when tablespace is dropped.    You must manually delete it as shown below.


Alter diskgroup data drop file ‘+data/orcldb/data_01.dbf’;




Larry Catt


Oracle 12c – Use Direct NFS

In Oracle 11g a native NFS client has been placed in Oracle Database kernel.  It improves performance when Network File Systems are in use.  Oracle NFS kernel optimizes and eliminates overhead of NFS, eliminating the need to tune NFS parameters at OS layer.


To use Direct NFS, the NFS file system must be mount and available by OS kernel .  The Oracle Direct NFS Client manages Oracles access to drives, by a configuration file call orafstab or mount tab file /etc/mtab on linux.  The file $ORACLE_HOME/dbs/orafstab would specify the direct NFS client settings for a single database.  Oracle looks for settings in /etc/oranfstab then Oracle reads /etc/mtab to identify available NFS mounts.


To use Direct NFS mount setting you have to replace standard Oracle Disk Manager (ODM) library with one supporting Direct NFS Client.




[oracle@linux2 ~]$ $ORACLE_HOME/dbs/orafstab

server: MyNFSServer1





export: /vol/ oradata1

mount: /mnt/ oradata1


Once the file exists, the following commands will enable the Direct NFS Client ODM Library


[oracle@linux2 ~]$ cd $ ORACLE_HOME/ lib

[oracle@linux2 ~]$ cp libodm11. so libodm11. so_stub

[oracle@linux2 ~]$ ln –s libnfsodm11. so libodm11. so




Larry Catt


Oracle 12c Moving or renaming Datafile online

In oracle 12c you can now move a datafile while it is online.   This procedure will list the rules of moving or renaming a datafile online and show some examples.



  1. If a file of the same name already exists the ALTER DATABASE statement will fail with an error.
  2. If the REUSE clause is used with the ALTER DATABASE statement and the file already exists, the existing file will be overwritten.
  3. When the ALTER DATABASE MOVE DATAFILE statement is used a copy of the file is made during the process and adequate space must be available for both the original and the copy for operation to succeed.
  4. At the end of the operation, the original copy is deleted. Unless the KEEP clause is used.




  1. Logon as the oracle software owner and logon to sqlplus.


  1. Display all datafiles with command: select file_name from dba_data_files;


SQL> select file_name from dba_data_files;









  1. Move the sysaux tablespace datafile from sysaux01.dbf to sysaux01_test.dbf.


SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’

TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’;  2

Database altered.



  1. Display all datafiles with command:  select file_name from dba_data_files;   NOTE:  dbf has been replaced with sysaux01_test.dbf.


SQL> select file_name from dba_data_files;









  1. To overwrite an existing file, add the REUSE clause.


ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’

TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’  REUSE; 



  1. To keep the old file, add the KEEP clause.


ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’

TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’ KEEP; 


  1. This completes movment of online datafiles.



Larry Catt


Oracle 12c – Implement Space Management

Oracle provides a large amount of storage options to reduce the space required for data storage and increase the speed of retrieval.   This article provides a brief overview of space management in an Oracle 12c database with some general suggestions to increase flexibility and performance.


Local Managed Tablespaces:

You should always use locally managed tablespaces in Oracle, this is where bitmaps in the tablespace to manage all extents and not external in the data dictionary.  Any tablespace can be locally managed, including SYSTEM and SYSAUX.   Advantage of local managed tablespaces:

  1. Allocation and deallocation of space is local managed, resulting in higher performance.
  2. Local managed temporary tablespaces do not generate undo or redo.
  3. The AUTOALLOCATE clause allows for automatically extent sizing.
  4. Reduce contention on data dictionary resulting from no access by tablespace.
  5. No need to coalesce free extents.

DBMS_SPACE_ADMIN is a package containing procedures specific to locally managed tablespaces.

Use the ‘EXTENT MANAGEMENT LOCAL AUTOALLOCATE’ is the clause to specify local management with auto select of extent sizes which is the default.  To have uniformed extent sizes you must specify, ‘UNIFORM’




DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’




CREATE TABLESPACE data1 DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’ SIZE 50M;


Two methods you can use for segment space in locally managed tablespace.  Manual and Automatic:  Manual uses a free lists to manage free space in segments.   Automatic is the default method and it uses bitmaps to manage free space and is more efficient.   You can explicitly specify clause:  ‘SEGMENT SPACE MANAGEMENT AUTO’




DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’







Is the lowest level compression and does not compress data in a table from INSERT/UPDATE.   Data is only compressed during bulk loads.   Basic compression is available in EE license and its format is the same as Advance Compression.  It is possible to convert from Basic to Advance, by simply changing the storage definition of the table or partition.


Advance Row Compression

Originally called OLTP Table compression in 11g, Advance Row compression compresses data regardless how it arrived.  It also minimizes overhead in write operations by eliminating duplicates in data blocks even across rows.  Advance Compression is an additional licensing option.


Hybrid Columnar Compression

Hybrid Columnar Compression (HCC) is a method of organizing data within a set of database blocks.   The underlying OS must support HCC and requires the use of Oracle Storage Exadata, Pillar Axiom or Sun ZFS Storage Appliance.  It stores multiple rows by columns, in what is call compression units.  Columns of like data are stored together similar data types, thus can achieve compression of x6 x15.


Tables created in tablespace with compression option will be compressed unless you specify no compression or another compression method in the CREATE TABLE clause.


Segment Shrink

Online segment shrink allows you to reclaim fragmented free space below high watermark in a segment.  With the following benefits:

  1. Compacting provides better cache use.
  2. Compact data has less data blocks to be scanned.

Segment shrink is performed online without DML operations interference.    Concurrent DML operations are blocked at end of shrink for space deallocation.  Indexes are maintained and usable after shrink.   Unused disk space is reclaimed above and below high water mark.  No additional space is needed during shrink.  By default shrink operation compact data, move the high water mark and release unused space.

Segment shrink requires row movement and thus requires you to enable row movement and disable rowed triggers.  Shrink operations can only happen on segments using locally managed tablespace with ASSM Automatic segment space management.  In ASSM all segment types are shrinkable except:

  1. IOT mapping tables
  2. Tables with rowed based materialized views.
  3. Tables with function-based indexes.
  5. Compressed tables.


Invoking Online Segment Shrink – SHRINK SPACE

You can shrink space in the following by use of SHRINK SPACE clause

  1. Table – with ALTER TABLE..
  2. Index-Organized Table – with ALTER TABLE
  3. Index – with ALTER INDEX
  4. Partition – with ALTER TABLE
  5. Sub-partition – with ALTER TABLE
  6. Materialized view – with ALTER MATERIALIZED VIEW
  7. Materialized view log – with ALTER MATERIALIZED VIEW LOG


Two operations with SHRINK SPACE clause.

  1. COMPACT – shrink operation divided into two sections. First phase:  defragments and compacts rows, does not reset high water mark or release space.  You can re-issue shrink operation without the COMPACT operation during off hours to complete deallocation of space and movement of high water mark.
  2. CASCADE – This extends shrink operation to all dependent objects.


Example shrink a table and all of its dependent segments:




Example shrink a single partition of a partitioned table:






Larry Catt