Skip Headers
Oracle® Database Express Edition 2 Day DBA
11g Release 2 (11.2)

E18804-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Managing Database Storage

This section describes the storage structures of your database, and explains how to monitor and manage the amount of storage that is in use and available for the database and its backups. It contains the following topics:

About the Database Storage Structures

Oracle Database Express Edition (Oracle Database XE) is composed of the following storage structures:

  • Logical structures such as tablespaces are created and recognized by the database only, and are not known to the operating system.

  • Physical structures are those that can be seen and operated on from the operating system, such as the physical files that store data on disk.

  • Recovery-related structures such as redo logs and database backups are used to recover the database after an operating system failure, Oracle instance failure, or media (disk) failure. Recovery-related structures are stored in an automatically managed disk storage area called the flash recovery area.

Oracle Database XE completely automates the management of its logical and physical structures and flash recovery area storage. You use the Oracle Database XE graphical user interface to monitor these structures, mostly to understand how much storage your applications have used so far, how much free storage remains, and whether more space is needed for backups.

The following sections provide a closer look at the database and its storage structures. Refer to Figure 6-1 as you review these sections.

Figure 6-1 Database Storage Structure

Description of Figure 6-1 follows
Description of "Figure 6-1 Database Storage Structure"

Database

The database is the collection of logical and physical structures that together contain all the data and metadata for your applications. The database also contains control structures (such as control files) that it needs for startup and operation. All of these structures are described in subsequent sections, and are summarized in Figure 6-1.

The Oracle Database XE instance (which consists of the Oracle Database XE background processes and allocated memory) works with a single database only. Rather than enabling you to create multiple databases to accommodate different applications, Oracle Database XE uses a single database, and accommodates multiple applications by enabling you to separate data into different schemas. See "About User Accounts" for more information about schemas.

The maximum database size in Oracle Database XE is 5 gigabytes (GB). This includes between 0.5 and 0.9 GB for the data dictionary, internal schemas, and temporary space, which leaves just over 4.0 GB for user data.

See Also:

See "Internal User Accounts" for information about internal schemas, and "Tablespaces" for information about temporary space.

Tablespaces

A database consists of one or more tablespaces. A tablespace is a logical grouping of one or more physical datafiles or tempfiles, and is the primary structure by which the database manages storage.

There are various types of tablespaces, including the following:

  • Permanent tablespaces

    These tablespaces are used to store system and user data. Permanent tablespaces consist of one or more datafiles. In Oracle Database XE, all your application data is by default stored in the tablespace named USERS. This tablespace consists of a single datafile that automatically grows (autoextends) as your applications store more data.

  • Temporary tablespaces

    Temporary tablespaces improve the concurrency of multiple sort operations, and reduce their overhead. Temporary tablespaces are the most efficient tablespaces for disk sorts. Temporary tablespaces consist of one or more tempfiles. Oracle Database XE automatically manages storage for temporary tablespaces.

  • Undo tablespace

    Oracle Database XE transparently creates and automatically manages undo data in this tablespace.

    When a transaction modifies the database, Oracle Database XE makes a copy of the original data before modifying it. The original copy of the modified data is called undo data. This information is necessary for the following reasons:

    • To undo any uncommitted changes made to the database in the event that a rollback operation is necessary. A rollback operation can be the result of a user specifically issuing a ROLLBACK statement to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.

    • To provide read consistency, which means that each user can get a consistent view of data, even while other uncommitted changes may be occurring against the data. For example, if a user issues a query at 10:00 a.m. and the query runs for 15 minutes, then the query results should reflect the entire state of the data at 10:00 a.m., regardless of updates or inserts by other users during the query.

      See Oracle Database Concepts for a discussion of read consistency.

    • To support the Flashback Query feature, which enables you to view or recover older versions of data. See "Viewing and Restoring Historical Data with Flashback Query" for more information.

Table 6-1 describes the tablespaces included in Oracle Database XE.

Table 6-1 Tablespaces and Descriptions

Tablespace Description

SYSTEM

This tablespace is automatically created when Oracle Database XE is installed. It contains the data dictionary, which is the central set of tables and views used as a read-only reference for the database. It also contains various tables and views that contain administrative information about the database. These are all contained in the SYS schema, and can be accessed only by user SYS or other administrative users with the required privilege.

SYSAUX

This is an auxiliary tablespace to the SYSTEM tablespace, and is also automatically created upon installation. Some database components and products use this tablespace. The HR sample schema is also stored in the SYSAUX tablespace.

TEMP

This tablespace stores temporary data generated when processing SQL statements. For example, this tablespace is used for sort work space. The TEMP tablespace is specified as the default temporary tablespace for every user.

UNDO

This is the tablespace used by the database to store undo information.

USERS

This tablespace is used to store permanent user objects and data. In Oracle Database XE, USERS is the assigned default tablespace for all users except the SYS user, which has the default permanent tablespace of SYSTEM.


Note:

You can create additional permanent tablespaces in Oracle Database XE, although typically there is no need to do so. One situation where you may have to create new permanent tablespaces is if you are importing objects from another Oracle database and the import file specifies tablespace names. See the CREATE TABLESPACE command in Oracle Database SQL Language Reference, and "Exporting and Importing Data" for more information.

Datafiles and Tempfiles

Datafiles are the operating system files that hold database data. The data is written to these files in an Oracle-proprietary format that cannot be read by programs other than an Oracle database. Tempfiles are a special class of datafiles that are associated only with temporary tablespaces. Temporary tablespaces provide workspaces to help process queries.

Control File

The control file is a binary file that tracks the names and locations of the physical components of the database, and that maintains other control information, including records of all database backup-related files. It is essential to the functioning of the database.

Server Parameter File

The server parameter file (SPFILE) contains initialization parameters that Oracle Database XE uses at startup to determine the settings and run-time resources for the database. Do not attempt to edit this file with a text editor, as it is a binary file. You can change initialization parameter values by submitting ALTER SYSTEM commands with the SQL Command Line. See Oracle Database SQL Language Reference for more information.

You can view current initialization parameter settings with SQL Developer: in the Reports navigator, expand Data Dictionary Reports and then Database Administration, and see the reports under Database Parameters. Reports are available for All Parameters and Non-Default Parameters (the latter identified as "Modified" in the Parameters display available from the XE Database Home Page), and each report indicates whether each parameter is Session Modifiable and System Modifiable.

Password File

Oracle Database XE uses a password file to authenticate a user who is logging in remotely as user SYS. The SYS user can then perform administrative functions from a remote workstation. The password file contains the SYS password (encrypted). Whenever you change the password for SYS, the password file is automatically updated.

The password file is automatically created when you install Oracle Database XE. Unlike the other physical structures of the database, the password file is not backed up to the flash recovery area.

Note:

Under typical circumstances, you should never log in to Oracle Database XE as user SYS.

Recovery-Related Structures in the Flash Recovery Area

The flash recovery area is essential for data protection in Oracle Database XE. It is a directory, separate from the database itself, where recovery-related structures are stored. These recovery-related structures include:

  • Backups of the physical files that make up the database (datafiles, the control file, and the server parameter file (SPFILE))

    Note:

    Oracle database backup and recovery is based on protecting the physical files of the database, rather than individual database objects such as tables.

    Backups are stored in collections called backup sets. A backup set consists of one or more backup pieces, which are files in a proprietary archival format that can be accessed only by an Oracle database. See "About Backing Up and Restoring the Database" for more information.

  • Online redo logs

    The online redo log files record all changes made to the database. They can be used to reconstruct data in the event of a failure. See "Online Redo Log Files" for more information.

  • Archived redo logs

    If you enable log archiving, filled redo log files are archived (copied) in the flash recovery area before being reused. The online and archived logs together constitute a record of all changes committed to the database since the last backup was taken. See "Archived Redo Log Files" for more information.

The database automatically manages all contents of the flash recovery area. You must not directly manipulate files within the flash recovery area using operating system commands.

Table 6-2 lists the default location for the flash recovery area on each platform.

Table 6-2 Flash Recovery Area Default Locations

Platform Location

Linux

/usr/lib/oracle/xe/app/oracle/flash_recovery_area/

Windows

c:\oraclexe\app\oracle\flash_recovery_area\


Caution:

The default configuration of Oracle Database XE stores the flash recovery area on the same disk as your database files. In this configuration, if a media (disk) failure occurs, you can lose both your database and your backups. For any database where data protection is essential, change the location of the flash recovery area so that it is stored on a different disk. See "Setting Flash Recovery Area Location and Size" for instructions.

Online Redo Log Files

The most crucial structure for database recovery is a set of redo log files. This set of files is collectively known as the redo log for the database. A redo log is made up of redo entries, which are also called redo records.

The primary function of the redo log is to record all changes made to data in the database. If an Oracle instance failure or operating system failure prevents modified data from being permanently written to the datafiles, the changes can be recovered from the redo log so that committed data updates are not lost.

The database writes to the redo log files in a circular fashion. When the current redo log file fills, the database begins writing to the next available redo log file. (The redo log files that are not current are called inactive.) When the last available redo log file is filled, the database returns to the first redo log file and writes to it (overwriting previous redo entries), starting the cycle again.

Multiplexed Redo Log

To protect against a failure involving the redo log itself, Oracle Database XE allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations. For the most benefit, these locations should be on separate disks. Even if all copies of the redo log are on the same disk, however, the redundancy can help protect against I/O errors, file corruption, and so on.

Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. When the database writes to the current log file, all members in that log file's group are updated so that they remain identical. Each redo log group is defined by a number, such as group 1, group 2, and so on.

The current and inactive redo log files—that is, the current and inactive log groups and all their members—taken together, are called the online redo log files, to distinguish them from archived redo log files, which are described later in this section.

The default installation of Oracle Database XE configures two redo log groups of one member each. Thus, the default configuration for the redo logs does not use multiplexing. As shown in Figure 6-1, both single-member redo log groups are stored in the flash recovery area.

You may want to multiplex the redo logs to protect against failures. Again, the ideal configuration is to separate members of the same log group onto different disks to protect against disk failure. Assuming that you decided to configure two members per group, the best practice for Oracle Database XE would be the following:

  1. Move the flash recovery area to a different disk.

    See "Setting Flash Recovery Area Location and Size" for instructions.

  2. Create the second member of each redo log group in the same location as the datafiles.

    See "Creating Redo Log Members" in Oracle Database Administrator's Guide for instructions. Table 6-3 shows the location of the datafiles on each platform.

Table 6-3 Datafile Locations

Platform Datafile Location

Linux

/usr/lib/oracle/xe/oradata/XE/

Windows

C:\oraclexe\oradata\XE\


Moving the flash recovery area to a different disk is preferred over leaving the flash recovery where it is and creating the second group member on a different disk. This is because the flash recovery area also contains database backups, and backups are best placed on a disk other than the disk that contains the datafiles.

Note:

When you multiplex the redo log, the database must increase the amount of I/O that it performs. Depending on your configuration, this may affect overall database performance.

See Also:

Archived Redo Log Files

Oracle Database XE can be configured so that a background archiving process makes copies of filled, inactive redo log files in the flash recovery area before they are reused. Redo log files copied in this way are called archived redo log files.

Note:

When the redo log is multiplexed, the database selects one member of that log file's group to archive. If a member is damaged or unavailable, the database attempts to archive another member.

A database configured to archive redo logs is said to be in ARCHIVELOG mode. (A database not configured to archive redo logs is said to be in NOARCHIVELOG mode.)

The advantages of running in ARCHIVELOG mode are the following:

  • After a media failure causing the loss of some or all database files, the database can be reconstructed with all committed transactions intact if you have backups of the control file and datafiles, and a complete set of all archived and online redo log files created since the last backup.

    The online and archived redo log files contain a complete record of all database changes since the last backup. This reconstruction process is called media recovery.

  • A database in ARCHIVELOG mode can be backed up while it is online.

    A NOARCHIVELOG mode database can be backed up only while it is in the mounted (but not open) state after a successful SHUTDOWN or SHUTDOWN IMMEDIATE operation. Your applications are unavailable during the backup of a NOARCHIVELOG database.

In ARCHIVELOG mode, the archived redo log files require disk space in the flash recovery area, and the flash recovery area requires monitoring to ensure that it does not fill completely.

Log archiving is disabled by default, to simplify the management of your database. Thus, the default configuration of Oracle Database XE protects your database from instance failure or operating system failure, but does not protect your database from media failure. Oracle therefore recommends that you do the following for complete data protection:

  • Enable ARCHIVELOG mode.

  • Back up the database frequently.

Note:

If you enable ARCHIVELOG mode, you must perform regular backups of the database to avoid completely filling the flash recovery area. A completely filled flash recovery area can lead to database failure.

See Also:

Monitoring Storage Space Usage

Because Oracle Database Express Edition (Oracle Database XE) is limited to just over four gigabytes (GB) of user data, your most important storage management task is monitoring the amount of free storage space and storage space used for any tablespaces used for storing user data.

To check this usage information:

  1. In SQL Developer, click the Reports navigator tab, and expand the hierarchy as follows: All Reports, then Data Dictionary Reports, then Database Administration, then Storage.

  2. Under Storage, click Free Space, then select a database connection for a privileged user such as SYSTEM, as shown in Figure 6-2.

    Figure 6-2 Selecting a Connection for the Free Space Report

    Description of Figure 6-2 follows
    Description of "Figure 6-2 Selecting a Connection for the Free Space Report"

  3. Click OK in the Select Connection dialog box to display the Free Space report, which is shown in Figure 6-3.

    Figure 6-3 Free Space Report

    Description of Figure 6-3 follows
    Description of "Figure 6-3 Free Space Report"

    You are interested in the USERS tablespace because it is typically used only for user data, not for Oracle-maintained system data. In Figure 6-3, only about 2.6 percent (0.025625) of the available allocated space for the USERS tablespace is actually being used.

If you notice that space remaining is becoming low, you can attempt to free some space by doing the following:

  1. For each schema:

    1. Log in as the schema owner.

    2. Drop (delete) unused database objects.

    3. Purge the recycle bin.

      See "Purging the Recycle Bin" for instructions.

  2. Compact storage.

    See "Compacting Storage" for instructions.

Note:

If you log in to the database and connect as SYSDBA, you can purge the entire recycle bin (all schemas simultaneously). See Oracle Database Administrator's Guide for details.

If this procedure does not free a significant amount of space and you expect space requirements to continue to grow, you must consider upgrading to Oracle Database Standard Edition or Enterprise Edition.

Compacting Storage

If you notice that space remaining in the database is becoming low, you can compact storage. Compacting storage attempts to recover unused fragmented free space in the database. Depending on the state of the database, compacting storage may or may not recover unused space.

Compacting and other options for reclaiming space are explained in the chapter about reclaiming waster space in Oracle Database Administrator's Guide.

Viewing Tablespaces

You can use the Oracle Database XE graphical user interface to view a list of tablespaces in the database, view tablespace properties, and view datafile properties.

To view Oracle Database XE tablespaces:

  1. From the Oracle Database 11g Express Edition menu, select Get Started.

  2. Click Storage.

    If prompted for administrator credentials, enter the SYSTEM user name and password or another administrator user name and password, and then click Login.

    The Storage page appears.

    Description of xe_tablespaces.jpg follows
    Description of the illustration xe_tablespaces.jpg

    Depending on datafile settings, a tablespace can grow beyond its currently allocated size. For example, the USERS tablespace may start with one datafile with an initially allocated size of 100 megabytes (MB), but the datafile can autoextend as needed, 10 MB at a time.

  3. (Optional) Click a tablespace name to view information on that tablespace's segments.

Viewing Redo Log Files

You can use the DBA navigator in SQL Developer to view location and status information for the online redo log files.

  1. In SQL Developer, if the DBA navigator is not visible, click View, then DBA.

  2. If the DBA navigator does not already include a connection to the SYSTEM user, right-click Connections in the DBA navigator, select Add Connection, and select the connection for SYSTEM. (This does not create a new database connection; it just makes the SYSTEM connection visible in the DBA navigator.)

  3. Expand the SYSTEM connection in the DBA navigator; then expand Storage and select Redo Log Groups.

    Description of redo_log_file.jpg follows
    Description of the illustration redo_log_file.jpg

  4. You can select any redo log group to see, for each redo log file in the group, its location, type, and whether it was created in the flash recovery area.

See "Online Redo Log Files" for more information on redo log groups and log group members.

Note:

The display does not include information on archived redo log files. In addition, the default configuration of Oracle Database XE does not include redo log multiplexing.

Managing the Flash Recovery Area

Oracle Database Express Edition (Oracle Database XE) stores database backups, online redo log files, and archived redo log files in the flash recovery area. The primary management tasks related to the flash recovery area are the following:

  • Monitoring flash recovery area available space

  • Changing the flash recovery area location and size

The flash recovery area is a fixed-size storage area. The default size is 10 gigabytes (GB). Because Oracle Database XE storage is limited to 5 GB, two backups of the database are expected to fit in the flash recovery area. However, when running in ARCHIVELOG mode, you may need to allocate extra space for archived redo log files, and you must monitor flash recovery area available space more closely. (See "Archived Redo Log Files" for information about ARCHIVELOG mode.)

This section contains the following topics:

Monitoring Space in the Flash Recovery Area

You must run a SQL query to determine the current available space in the flash recovery area.

To view current available space in the flash recovery area:

  1. Using the SQL Command Line, log in and connect to the database as SYSTEM or SYSDBA.

  2. Enter the following query:

    SELECT 
        NAME, 
        TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
        TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999')
           AS SPACE_AVAILABLE,
        ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
           AS PERCENT_FULL
        FROM V$RECOVERY_FILE_DEST;
    

The query results should look something like this:

---------------------------------------------------------------------------------------------
NAME                                        SPACE_LIMIT      SPACE_AVAILABLE   PERCENT_FULL
C:\oraclexe\app\oracle\flash_recovery_area  21,474,836,480   20,050,844,672    6.6
---------------------------------------------------------------------------------------------

Interpret the results as follows:

  • NAME indicates the current flash recovery area location.

  • SPACE_LIMIT indicates the current flash recovery area maximum size.

  • SPACE_AVAILABLE indicates the space available for storing new backups and archived redo logs, including space that can be reclaimed by deleting files that are no longer needed to meet the retention policy.

  • PERCENT_FULL indicates the current percentage of flash recovery area space used for backups and archived redo logs that are within the backup retention policy.

If the PERCENT_FULL value is approaching 100% (for example, is 85% or more), and log archiving is enabled (the database is in ARCHIVELOG mode), it may be time to back up the database. Backing up the database deletes archived log files and frees space in the flash recovery area.

If the PERCENT_FULL value is frequently close to 100% after several recent backups, consider allocating more space for your flash recovery area as described in "Setting Flash Recovery Area Location and Size", or, if in ARCHIVELOG mode, taking backups more frequently to reduce the size of the retained archived log files.

See Also:

Setting Flash Recovery Area Location and Size

This section explains the procedures for setting the flash recovery area location and for changing the flash recovery area size. The flash recovery area location and size are specified by the initialization parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE.

Setting the Flash Recovery Area Location

Table 6-2 shows the default flash recovery area locations on both platforms.

To change the flash recovery area location:

  1. Using the SQL Command Line, log in and connect to the database as SYSDBA.

    See "Logging In and Connecting to the Database as SYSDBA" for instructions.

  2. At the SQL Command Line prompt, enter the following command:

    ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = 'new_path';
    

    where new_path is an absolute path to the new directory for the flash recovery area. The path must exist. (The ALTER SYSTEM command cannot create directories.)

    For example, in Windows, to set the flash recovery location to the FRA directory on the E: drive, enter the following command:

    ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = 'E:\FRA';
    
  3. Enter the following command, which runs a PL/SQL script that moves the online redo log files to the new flash recovery area location and drops the log files from the old location:

    @?/sqlplus/admin/movelogs
    

    Note that the command must be entered in lower case. The '@' symbol is an abbreviation for the START command, which runs the named SQL script. The '?' symbol, when used in a SQL Command Line command, is an abbreviation for the Oracle home directory. This command therefore runs the script named movelogs.sql, which is located in the path Oracle_home/sqlplus/admin.

    If the script is successful, SQL Command Line displays the following message:

    PL/SQL procedure successfully completed.
    

    A listing of the movelogs.sql script appears later in this section.

  4. Enter the following command to exit the SQL Command Line:

    EXIT
    

Note:

After you change the location of the flash recovery area, Recovery Manager (RMAN) can still use the backups and archived logs in the old location until they become obsolete. The old backups cannot be moved to the new flash recovery area location.

Do not manually delete the contents of the old flash recovery area using operating system utilities. Instead, make the backups in the old flash recovery area location obsolete by backing up your database twice after you change the location of the flash recovery area.

Each time that you back up the database as described in "Backing Up the Database", obsolete backups and archived logs are deleted so that only the two most recent backups and accompanying archived redo logs are retained. Thus, after the new flash recovery area contains two recent backups, all files are deleted from the old location.

See Also:

Changing the Flash Recovery Area Size

To change the flash recovery area size:

  1. Using the SQL Command Line, log in and connect to the database as SYSDBA.

    See "Logging In and Connecting to the Database as SYSDBA" for instructions.

  2. Enter the following command at the SQL Command Line prompt:

    ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = new_size;
    

    where new_size can be of the format nK (kilobytes), nM (megabytes) or nG (gigabytes).

    For example, to set the flash recovery area size to 20 gigabytes, enter the following command:

    ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G;
    
  3. Enter the following command to exit the SQL Command Line:

    EXIT
    

See Also:

movelogs.sql Script

The following is a listing of the movelogs.sql script, which you must run after changing the location of the flash recovery area. The script moves the online redo log files to the new flash recovery area location and drops the log files from the old location.

declare
   cursor rlc is
      select group# grp, thread# thr, bytes/1024 bytes_k
        from v$log
      order by 1;
   stmt     varchar2(2048);
   swtstmt  varchar2(1024) := 'alter system switch logfile';
   ckpstmt  varchar2(1024) := 'alter system checkpoint global';
begin
   for rlcRec in rlc loop
        stmt := 'alter database add logfile thread ' ||
               rlcRec.thr || ' size ' ||
               rlcRec.bytes_k || 'K';
      execute immediate stmt;
      begin
         stmt := 'alter database drop logfile group ' || rlcRec.grp;
         execute immediate stmt;
      exception
         when others then
            execute immediate swtstmt;
            execute immediate ckpstmt;
            execute immediate stmt;
      end;
      execute immediate swtstmt;
   end loop;
end;
/