The Database Configuration Assistant - Oracle

As an author who admits to hating GUIs, I rather like the Database Configuration Assistant (DBCA). Creation of a small database is a great way to test your new Oracle software install. Oracle Database 10g has added new functionality to the DBCA, including the ability to perform Real Application Cluster database installs and configuration of Automatic Storage Management (a new feature that is described later in this chapter).

The SYSAUX Tablespace

In the previous section you might have noticed that when you upgrade your Oracle database to Oracle Database 10g, you need to create a new tablespace called SYSAUX. Also, when you create your first Oracle Database 10g database, Oracle will create SYSAUX as well. The SYSAUX tablespace is a new tablespace component in Oracle Database 10g. This section first discusses the SYSAUX tablespace and then reviews some Oracle-supplied procedures that allow you to perform maintenance tasks on the SYSAUX tablespace.

Introducing the SYSAUX Tablespace

The SYSAUX tablespace is a new tablespace that is required in Oracle Database 10g. SYSAUX is a secondary tablespace for storage of a number of database components that were previously stored in the SYSTEM tablespace. The SYSAUX tablespace is created as a locally managed tablespace using automatic segment space management.

Previously, many Oracle features required their own separate tablespaces (such as the RMAN recovery catalog, Ultra Search, Data Mining, XDP, and OLAP). This increases the management responsibility of the DBA. The SYSAUX tablespace consolidates these tablespaces into one location, which becomes the default tablespace for these Oracle features.

When you create an Oracle database, Oracle creates the SYSAUX tablespace for you by default. If you are using Oracle Managed Files (OMF), then the tablespace is created in the appropriate OMF directory. If you use the sysaux datafile clause in the create database statement, then the SYSAUX tablespace datafile(s) will be created in the location you define. Finally, if no sysaux datafile clause is included and OMF is not configured, Oracle creates the SYSAUX tablespace in a default location that is OS- specific. Here is an example of a create database statement with the sysaux datafile clause in it:

CREATE DATABASE my_db
DATAFILE 'c:\oracle\oradata\my_db\my_db_system_01.dbf' SIZE 300m
SYSAUX DATAFILE 'c:\oracle\my_db\my_db_sysaux_01.dbf' SIZE 100m
DEFAULT TEMPORARY TABLESPACE dtemp_tbs tempfile
'c:\oracle\my_db\my_db_temp_01.dbf' SIZE 100m
UNDO TABLESPACE undo_tbs_one DATAFILE
'c:\oracle\my_db\my_db_undo_tbs_one_01.dbf' SIZE 100m;

As stated earlier in this chapter, when you migrate to Oracle Database 10g, you need to create the SYSAUX tablespace as a part of that migration. You do this after mounting the database under the new Oracle Database 10g database software. Once you have mounted it, you should open the database in migrate mode with the startup migrate command. Once the database is open, you can create the SYSAUX tablespace. Here is the create tablespace statement that you would use to perform this operation:

CREATE TABLESPACE sysaux
DATAFILE 'c:\oracle\oradata\my_db\my_db_sysaux_01.dbf' SIZE 300m
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

The SYSAUX tablespace must be created with the attributes shown in the preceding example. The following restrictions apply to the usage of the SYSAUX tablespace in Oracle Database 10g:

• When migrating to Oracle Database 10g, you can create the SYSAUX tablespace only when the database is open in migrate mode.
  
• Also, when migrating to Oracle Database 10g, if a tablespace is already named SYSAUX, you need to remove it or rename it while you are in migrate mode.
  
• Once you have opened your Oracle Database 10g database, you cannot drop the SYSAUX tablespace. If you try, an error will be returned.
  
• You cannot rename the SYSAUX tablespace during normal database operations.
  
• The SYSAUX tablespace cannot be transported to other databases via Oracle's transportable tablespace feature.

Once the SYSAUX tablespace is in place and the database has been upgraded, you can add or resize datafiles associated with a SYSAUX tablespace just as you would any other tablespace through the alter tablespace command, as shown in this example:

ALTER TABLESPACE sysaux ADD DATAFILE
 'd:\oracle\oradata\my_db\my_db_sysaux_01.dbf' SIZE 200M;

Managing Occupants of the SYSAUX Tablespace

Each set of application tables within the SYSAUX tablespace is known as an occupant. Oracle provides some new views to help you monitor space usage of occupants within the SYSAUX tablespace and some new procedures you can use to move the occupant objects in and out of the SYSAUX tablespace.

First, Oracle provides a new view, V$SYSAUX_OCCUPANTS, to manage occupants in the SYSAUX tablespace. This view allows you to monitor the space usage of occupant application objects in the SYSAUX tablespace, as shown in this example:

SELECT occupant_name, space_usage_kbytes FROM v$sysaux_occupants;

In this case, Oracle will display the space usage for the occupants, such as the RMAN recovery catalog.

If you determine that you need to move the occupants out of the SYSAUX tablespace, then the MOVE_PROCEDURE column of the V$SYSAUX_OCCUPANTS view will indicate the procedure that you should use to move the related occupant from the SYSAUX tablespace to another tablespace. This can also be a method of “reorganizing” your component object tables, should that be required.

NOTE: The loss of the SYSAUX tablespace is not fatal to your database. In our testing it appears that the only real impact is that certain functionality related to the occupants of the SYSAUX tablespace is lost.