Reprinted with Permission by Quest Software June  2002
 

Online Database Block Size Rebuilds in Oracle9I
Kevin Loney, TUSC

ABSTRACT

Oracle9i offers support for multiple database block sizes in the same tablespace. DBAs can use this feature to support rebuilding their databases to a higher database block size while the database remains online. This article will provide a demonstration of the steps required to increase the database block size for almost all tablespaces in an Oracle9i environment. Topics covered include database quiescing, supporting multiple block caches, using the Oracle-managed files feature, and online object reorganization.

INTRODUCTION

Oracle9i offers a set of capabilities that, taken together, allow you to change the database block size for application tables and indexes while those tables are accessible by users. Oracle9i supports multiple database block sizes, on a tablespace-by-tablespace basis. Each block size has a corresponding cache area in the data block buffer cache area of the SGA.

In terms of init.ora parameters, the buffer cache area parameters include DB_BLOCK_SIZE and the DB_nK_CACHE_SIZE parameters, where nK is a supported database block size (typically 2K, 4K, 8K, and 16K). As of Oracle9i, the DB_BLOCK_BUFFERS init.ora parameter is deprecated. To see the DB_nK_CACHE_SIZE values, query V$PARAMETER:

select Name, Value
from V$PARAMETER
where Name like '%cache_size';

NAME                  VALUE
--------------------- -----------
db_keep_cache_size    0
db_recycle_cache_size 0
db_2k_cache_size      0
db_4k_cache_size      0
db_8k_cache_size      0
db_16k_cache_size     0
db_32k_cache_size     0
db_cache_size         20971520

In this database, the DB_BLOCK_SIZE is 4096 (4K). The corresponding DB_4K_CACHE_SIZE value must be 0. The DB_CACHE_SIZE value of 20M defines the size of the 4K block cache. You can modify buffer cache sizes (and the shared pool size) in increments called granules. Granules are 4 M in size if the SGA is less than 128 M in size, or 16 M if the SGA size exceeds 128 M. The SGA’s maximum size is defined by the SGA_MAX_SIZE init.ora parameter. The cache sizes can be dynamically altered while the database is running, but changing SGA_MAX_SIZE requires shutting down and restarting the instance.

STEP 1. CREATING THE 8K CACHE

In this example, the SCOTT.EMP table will be moved from a 4K tablespace to an 8K tablespace in a series of sequential steps. To support an 8K tablespace, you must first create an 8K cache:

alter system set db_8k_cache_size=4M;

The space allocated for the new cache must be available under the SGA_MAX_SIZE. If there is not memory available for the new cache, you may need to decrease the 4K cache size (in this example, set via DB_CACHE_SIZE).

Throughout this example, the emphasis is on ensuring the database availability during the processes. If availability was not a concern, you could use the QUIESCE RESTRICTED option of the ALTER DATABASE command. When a database is quiesced, no transactions by users other than SYS and SYSTEM are permitted; all other transactions and login appear to hang. Those hung transactions and logins are released when you execute the ALTER DATABASE UNQUIESCE command. You can query the quiesced state (NORMAL, QUIESCING, or QUIESCED) from the V$INSTANCE.Active_State column. Because quiescing impacts availability and forces SYS and SYSTEM to execute all commands (impacting ownership of indexes and management of grants), the examples in this paper instead use the Oracle9i online redefinition options. First, you must create a tablespace to hold the redefined SCOTT.EMP table, as shown in the following section.

STEP 2. CREATING THE 8K TABLESPACE WITH ORACLE-MANAGED FILES

To simplify the management of database files, Oracle9i introduces a feature called Oracle-managed files (OMFs). If you create a tablespace with OMFs, Oracle will create the datafile for the tablespace; when the tablespace is dropped, the OMFs will be deleted at the operating system level.

The location for OMF datafiles is specified via the DB_CREATE_FILE_DEST init.ora parameter, as shown in the following listing:

alter system set
DB_CREATE_FILE_DEST='/u01/oracle/CC1/oradataomf';

You can also use OMFs to manage online redo log files and controlfiles; both will be created in the directories specified via the DB_CREATE_ONLINE_LOG_DEST_n init.ora parameters, where n is a value between 1 and 5, as shown in the following listing:

DB_CREATE_ONLINE_LOG_DEST_1='/u02/oracle/CC1'
DB_CREATE_ONLINE_LOG_DEST_2='/u03/oracle/CC1'

With OMFs, you can thus create a new database via a single command:

create database dbnm;

By default, OMFs create all datafiles autoextensible, and all datafiles will be 100MB except for a 10 MB systemmanaged undo tablespace.

To create an 8K tablespace, specify the BLOCKSIZE parameter of the CREATE TABLESPACE command, as shown in the following listing. In this example, the OMF feature is used to bypass the need to specify a datafile name.

create tablespace USERS_DEST
datafile size 5M blocksize 8k;

You can verify the block size for the tablespace by querying the Block_Size column value from the DBA_TABLESPACES view. Since an 8K tablespace now exists in the database, you cannot change DB_8K_CACHE_SIZE to 0.

The SCOTT.EMP table presently resides in the 4K USERS tablespace. During this example, that table will be moved to the new 8K USERS_DEST tablespace. The user SCOTT is now granted a quota on the new tablespace.

alter user SCOTT quota 5M
on USERS_DEST;

STEP 3. REORGANIZE THE TABLE ONLINE

Now that the destination tablespace is ready, you can begin the process of moving the EMP table from USERS to USERS_DEST. To accomplish this while the database is being accessed, we’ll use the DBMS_REDEFINITION package provided with Oracle9i. There are some significant restrictions on this process (from the Oracle9i Administrator’s Guide):

If your tables meet those criteria, you can now start the redefinition process.

The redefinition process has five steps:

3.1. Verify table can be rebuilt online
3.2. Create interim table
         - and its indexes, grants, constraints, triggers
3.3. Start the redefinition
3.4. Optionally sync the source/destination tables
3.5. Abort or finish the redefinition

The following steps show the redefinition of the SCOTT.EMP table from the USERS to the USERS_DEST tablespace. In the process, we’ll also partition the EMP table on the fly.

3.1 VERIFY THE TABLE CAN BE REBUILT ONLINE

To verify the table can be rebuilt online, execute the CAN_REDEF_TABLE procedure, providing the schema owner and table name as input:

execute DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','EMP');

If an error stack is reported, the first error listed will tell you what the problem is; later errors concerning the DBMS_REDEFINITION package can be ignored.

3.2 CREATE THE INTERIM TABLE

What do you want EMP to look like in the new tablespace? Create an interim table that will be used during the redefinition process. To simplify matters, keep the column names and order the same as the source EMP table.

create table EMP_DEST
(EMPNO NUMBER(4) PRIMARY KEY,
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2))
partition by range (DeptNo)
(partition PART1
      values less than ('30'),
partition PART2
      values less than (MAXVALUE))
tablespace USERS_DEST
storage (freelists 5);

In this example, the EMP_DEST table will serve as the interim table during the redefinition. Once the process is complete, EMP_DEST will replace the old EMP table – and the only indexes, constraints, triggers, and grants available for EMP then will be those that you create on EMP_DEST now. You can optionally create the indexes, triggers, grants, and constraints after the redefinition process completes, but creating them now avoids the need for DDL locks on active tables later.

Because the EMP_DEST table is created in USERS_DEST, it will use 8K database blocks. To support larger block sizes, increase the FREELISTS settings to avoid conflicts during concurrent inserts.

3.3 START THE REDEFINITION

The START_REDEF_TABLE procedure begins the redefinition. The EMP_DEST table will now be populated with the committed data in the EMP table, so the time and undo requirements for this step depend on the size of the EMP table. As parameters, pass the schema owner, old table name, and the interim table name:

execute DBMS_REDEFINITION.START_REDEF_TABLE -
 ('SCOTT','EMP','EMP_DEST');

If EMP_DEST had a different column list than EMP, that column list would be passed as the fourth parameter to the START_REDEF_TABLE procedure. Once this procedure has completed, you can query EMP_DEST to verify its data contents.

3.4 – OPTIONAL – ABORT THE PROCESS

If you need to abort the redefinition process at this point, use the ABORT_REDEF_TABLE procedure, with the schema owner, source table, and interim table as input parameters.

execute DBMS_REDEFINITION.ABORT_REDEF_TABLE -
 ('SCOTT','EMP','EMP_DEST');

After aborting the redefinition, you should consider truncating the interim (EMP_DEST) table as well.

3.4 (B) – OPTIONAL – SYNC THE TABLES

During the conclusion of the redefinition process, Oracle will sync the data between the source and interim tables. To shorten the time required by that part of the process, you can sync the tables prior to the final step. This optional step allows you to instantiate the latest production data in the interim table, minimizing later impact on your online users.

To sync the source and interim tables, use the SYNC_INTERIM_TABLE procedure, with the schema owner, source table, and interim table as input parameters.

execute DBMS_REDEFINITION.SYNC_INTERIM_TABLE -
 ('SCOTT','EMP','EMP_DEST');

3.5 FINISH THE REDEFINITION PROCESS

To complete the redefinition process, execute the FINISH_REDEF_TABLE procedure, as shown in the following examples. The input parameters are the owner, source table, and interim table names.

execute DBMS_REDEFINITION.FINISH_REDEF_TABLE -
 ('SCOTT','EMP','EMP_DEST');

During this step, the EMP table will take over the characteristics of the EMP_DEST table – in this case, it will be relocated to the USERS_DEST tablespace, will use a database block size of 8 KB, and will be partitioned.

4. VERIFY THE REDEFINITION

Since the EMP table should now be partitioned and moved to USERS_DEST, you can verify its redefinition by querying DBA_TAB_PARTITIONS:

select Table_Name, Tablespace_Name, High_Value
  from DBA_TAB_PARTITIONS
 where Owner = 'SCOTT';

TABLE_NAME   TABLESPACE_NAME HIGH_VALUE
------------ --------------- ----------
EMP          USERS_DEST      MAXVALUE
EMP          USERS_DEST      '30'

As shown in the preceding listing, the EMP table partitions both reside in the USERS_DEST tablespace. Blocks read from EMP will now use the DB_8K_CACHE_SIZE memory area in the SGA.

At this point, you should verify that the foreign keys on EMP are enabled; that all necessary grants are in place; that the indexes are all in place; and that all triggers are enabled.

5. CLEAN UP THE INTERIM TABLE

Although EMP has taken over the EMP_DEST definitions, the EMP_DEST table is still out there! EMP and EMP_DEST have switched places – EMP_DEST is now a non-partitioned table in the USERS tablespace. Once you have verified the EMP table is correct, you should clean up the EMP_DEST table – either by truncating it or dropping it to free the space it had allocated.

6. ADDITIONAL OPTIONS

Now that you can upgrade the database block size of a table online, you can quickly expand this process to cover multiple tables at once. With proper planning, you can move a set of related tables to a higher block size without sacrificing the referential integrity across the tables or the availability of the application. You will need to have enough space to store the source and interim tables concurrently. Note that if the tables are already partitioned, you do not need to use this redefinition process – you can use the ALTER TABLE options already available to change the storage locations for existing partitions.

As of Oracle8i, you can use the REBUILD ONLINE option of the ALTER INDEX command to move an index while it is being rebuilt online. However, there are data corruption bugs with this option in Oracle 8.1.5 (see Metalink), and until Oracle9i it is very limited in the types of indexes supported.

7. MONITOR THE USE OF THE CACHES

You should monitor the cache areas to verify that the EMP blocks are using the 8K cache and that the cache sizes are adequate. Oracle9i records all cache entries for all block sizes in the X$BH table. If there is only one block size in effect, you can determine the names of the objects in the buffer cache, and the number of buffers used, by joining the Obj column of X$BH to the Object_ID column of DBA_OBJECTS. The following query eliminates the objects owned by SYS and SYSTEM from the output (execute from the SYS account):

select Object_Name,
           Object_Type ,
           COUNT(*) Num_Buff
  from X$BH A, SYS.DBA_OBJECTS B
 where A.Obj = B.Object_ID
  and Owner not in ('SYS','SYSTEM')
 group by Object_Name, Object_Type;

Oracle9i supports multiple block sizes, so a new column, BlSiz, has been added to the X$BH table (its values include 4096, 8192, etc). For block sizes other than the database’s default block size (as defined by DB_BLOCK_SIZE), the X$BH.Obj values may not match Object_ID values in DBA_OBJECTS. For example, following queries of the new EMP table the X$BH table was queried and the Obj values were compared to DBA_OBJECTS:

select Obj, COUNT(*)
  from X$BH
 where BlSiz=8192 group by Obj;

       OBJ COUNT(*)
---------- ----------
         0 496
     31540 3
     31541 2

select MAX(Object_ID) from DBA_OBJECTS;

MAX(OBJECT_ID)
--------------
        31519

As shown in the preceding listing, the X$BH.Obj values are higher than the highest Object_ID value in DBA_OBJECTS for non-default size caches. To join X$BH to DBA_OBJECTS in databases with non-default block sizes, you will need to use the File ID and Block ID for the block in the cache, and compare that to the File ID and Block ID values in DBA_EXTENTS. The following query performs this join (for the sake of this example, only the 8192 BlSiz values are selected):

select Segment_Name, Segment_Type,
      COUNT(*) Num_Buff
from X$BH a, SYS.DBA_EXTENTS B
where a.Dbarfil = b.File_id
and a.Dbablk >= b.Block_id
and a.Dbablk <= (b.Block_id + b.Blocks)
and Owner not in ('SYS','SYSTEM')
and a.Blsiz=8192
group by Segment_Name, Segment_Type;

Sample output is shown in the following listing:

SEGMENT_NAM SEGMENT_TYPE       NUM_BUFF
----------- ------------------ ----------
        EMP TABLE PARTITION             7
SYS_C002451 INDEX                       1

As shown in this output, the 8K cache is storing table partition blocks from the EMP table and an index block from its primary key index.

If you want to see all caches, you can add BlSiz and Segment_Owner to the preceding query, as shown in the following listing. To further reduce the output, you may wish to not show objects owned by other system accounts such as WKSYS, RMAN, OUTLN, SH, and others.

select Blsiz, Owner, Segment_Name,
                  Segment_Type, COUNT(*) Num_Buff
from X$BH a, SYS.DBA_EXTENTS B
where a.Dbarfil = b.File_id
and a.Dbablk >= b.Block_id
and a.Dbablk < (b.Block_id + b.Blocks)
and Owner not in ('SYS','SYSTEM')
group by Blsiz, Owner, Segment_Name, Segment_Type;

You can now create and monitor multiple block size caches in the database; create tablespaces using Oracle-managed files; and move objects into the larger block size caches online. Be sure to manage the size of the caches so they properly support the volume of data assigned to their respective tablespaces.

ABOUT THE AUTHOR:
Kevin Loney is a Senior Technical Management Consultant with TUSC. See http://www.tusc.com for further technical tips. The author can be reached at www.loneyk@tusc.com.