The Blog from the DBA Classroom

By: Joel Goodman

Archive for November, 2009

Database Links Masterclass Part 2

Posted by Joel Goodman on 30/11/2009

Today I presented the second in my Database Links Masterclass series on Distributed transactions at the UKOUG Annual Conference. You can see details on my “Articles and Presentations” Page

Posted in Oracle | Leave a Comment »

RMAN is Piping Hot

Posted by Joel Goodman on 24/11/2009

The RMAN “PIPE” Interface

Tips and Tricks for the Oracle DBA #1

RMAN is traditionally managed using the command line interface RMAN executable or by using Enterprise Manager. But there is a “third way” known as the “pipes” interface to RMAN, which uses the “DBMS_PIPE” Oracle, supplied package to provide PL/SQL programmes with an interface to RMAN.

The Oracle Documentation contains an example bit of code, which I have wrapped inside a demo to demonstrate the pipes interface. Essentially the RMAN command line executable must be started with the PIPE option and must specify the pipe name. Optionally a timeout value may be specified to terminate the RMAN executable if no command is sent down the named pipe within the timeout period. In my SQL*PLUS demo script RMAN is invoked automatically by shelling out to the host and running RMAN disconnected from the main script so that control returns to the SQL*PLUS script. One could of course drive this all from a shell script as well by running RMAN in the background and then running SQL*PLUS.

Here is an example of invoking RMAN to accept input from a pipe:

SQL> Host rman pipe demo_pipe timeout 600 target / \&

Two pipes will be created called ORA$RMAN_DEMO_PIPE_IN and ORA$RMAN_DEMO_PIPE_OUT and used for inbound commands to RMAN and output from the commands.

The demo is commented and does the following:

  1. Starts the RMAN Session in the background by hosting out
  2. Checks that a session is waiting on a “pipe get” event
  3. Uses DBMS_PIPE.PURGE to clean the two pipes
  4. Uses the code from the documentation to create a stored procedure called RMAN_CMD
  5. Checks for compilation errors
  6. Uses the V$DB_PIPES view to show the two pipes’ attributes
  7. Invokes the RMAN_CMD procedure several times with various RMAN commands
  8. Invokes the RMAN_CMD procedure, passing EXIT as the message to terminate the RMAN executable that is running in the background.

Possible used for the pipes interface are:

  1. Applications coded to automatically invoke one or more backup and recovery functions to achieve their design objectives
  2. Third party tools which whish to provide their own interface to RMAN
  3. DBA tools that wish to incorporate some RMAN functionality

The demo and spooled output are:

  1. rman_pipes_sql – this contains the script
  2. rman_pipes_spool – this is the spooled output of the script running on Linux.

Posted in Oracle | 5 Comments »

How Good is your “Local” Management?

Posted by Joel Goodman on 23/11/2009

Whilst delivering a course recently, a delegate asked for my views about tablespace management and the ensuing discussion covered Locally managed tablespaces, bigfile tablespaces, and Automatic Segment Space Management (ASSM)  for tablespaces. During the discussion I surprised the class when I stated “there are actually three types of locally managed tablespaces” as only two were known to anyone in attendance.  It then occurred to me that this was one of those cases where things may have “fallen into the cracks” and that it should be clarified.

Locally managed tablespaces may be created by a DBA with one of the following two options:

  • Uniform Size
  • Autoallocate

When using the “Uniform Size” option, all free and allocated extents for the tablespace are of the specified size and each bit in the bitmap used to track storage represents an area of that size. This option provides the following advantages:

  1. Extent tracking is local, being done in a bitmap metadata area within one of the tablespace files. This is the primary and well known advantage for local management and eliminates the use of the Data Dictionary for tracking free and allocated extents in base tables UET$ and FET$. In doing so there is also a reduction in contention for the Space Transaction enqueue (ST enqueue) which is used to single thread updates to space management tables in the data dictionary.
  2. External Fragmentation in the tablespace is totally eliminated since all extents are the same size. Therefore, the first free extent found by reference to the bitmaps is chosen for allocation when space is required for a segment creation or extension.

There are also some important implications for the “Uniform Size” option.

  1. Large uniform sizes may waste storage is small segments are created in the tablespace.
  2. The DBA may require several different tablespaces, each using a different “Uniform Size” if there is a great variety in segment sizes.

When using the “Autoallocate” option, each bit in the bitmap used to track storage represents an area of 64K. This option provides the following advantages:

  1. Extent tracking is local as in the “Uniform Size” option providing the same benefit in reducing ST enqueue overhead.
  2. Extents may vary in size on multiples of 64K boundaries, providing for heterogeneous segment sizes in the same tablespace with little or no wasted storage.
  3. Although all extents are multiples of 64K, when an extent is dropped the storage it occupies may be reallocated as several separate extents if required. External fragmentation, although possible, may not be as common as for dictionary managed tablespaces where extents are allocated on five block boundaries.

The third option which is not well known is the “Converted” tablespace. DBAs who wish to convert dictionary managed tablespaces to locally managed ones, use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure.

Argument Name                  Type                    In/Out Default?

TABLESPACE_NAME                VARCHAR2                IN
UNIT_SIZE                      BINARY_INTEGER          IN     DEFAULT
RFNO                           BINARY_INTEGER          IN     DEFAULT

The Parameters passed to this procedure are :

  1. The name of the tablespace to convert which is a mandatory parameter.
  2. The unit size that will be represented by each bit in the bitmap. This defaults to the highest common factor for all the existing allocated and free extents in the tablespace at the time the conversion is done.
  3. The tablespace relative file number in which the bitmap should be created. This defaults to the lowest file number.

When using a “converted” tablespace the only real benefit accrued is the local management as in the two cases listed above. But unlike “Uniform Size” and “Autoallocate”, the fragmentation propensity for a converted tablespace remains as high as it is for Dictionary managed tablespaces. This is caused by the default unit_size calculation being set to the highest common factor of existing extents. Since the extent boundaries in dictionary manages tablespaces are on five block boundaries, it is usually the case that some or many extents in such a tablespace are only five blocks long. The highest common factor therefore is often a size of five blocks. If the tablespace was in a database with 4k blocksize, then the highest common factor will often be 20k unless the DBA had arranged to have larger minimum extent lengths before the conversion using the minimum extent length option available since Oracle 8.0.

It is for this reason that I do not recommend converting tablespaces but instead, prefer to create new ones and then move the contents over. Do do so I recommend the following:

  1. Index Tablespaces – Create a new tablespace and then do “online index rebuilds” into the new tablespace and drop the old one when finished.
  2. “Table” Tablespaces – Create a new tablespace and then use “online table redefinition” with the DBMS_REDEFINITION package, solely for changing the tablespace without changing the table’s logical or physical properties.

Finally, to determine if your database has any “converted” locally managed tablespace consult the DBA_TABLESPACES data dictionary view:

SQL> select tablespace_name,extent_management, allocation_type from dba_tablespaces;


SYSTEM                         LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
UNDOTBS2                       LOCAL      SYSTEM
EXAMPLE                        LOCAL      USER
FRA                            LOCAL      SYSTEM

If the EXTENT_MANAGEMENT is LOCAL and the ALLOCATION_TYPE is USER then the tablespace has been converted. Creating new ones and moving segments into them can reduce fragmentation in those tablespaces and provide you with proper “Local” management.

Posted in Oracle | 5 Comments »