Chapter 8. Managing Database and Database Storage

A man is but the product of his thoughts what he thinks, he becomes. - Mahatma Gandhi

This chapter provides background information on memory management in Oracle Database 11g XE and managing physical files related to the database. Oracle memory is of two types, namely System Global Area (SGA) and Process Global Area (PGA). SGA is a memory that is shared by all Oracle processes, while PGA is a private memory allocated to individual processes. These topics will be covered in this chapter in more detail. We will also explore the physical database structure along with the logical database structure. The topics covered in this chapter are as follows:

• Memory structure

  • System Global Area

  • Process Global Area

• Automatic Memory Management

• Important background processes

• Physical and logical database structures

  • Creating a tablespace

  • Adding datafiles to a tablespace

  • Dropping a tablespace

  • Viewing tablespace usage

• Managing Flash Recovery Area

Memory structure

Oracle...

Memory structure

Oracle is available on almost every platform. For this reason, the physical architecture of Oracle is different on different operating systems. For example, on a Linux operating system, Oracle is implemented as multiple operating system processes whereas on Windows, Oracle is implemented as a single-threaded process.

Oracle uses memory to store information such as cached data, shared SQL and PL/SQL code, information about a session, and cursor pointers.

As mentioned earlier, Oracle memory structure is composed of two types of memory System Global Area and Process Global Area. The following sections will cover more on these topics.

System Global Area

System Global Area (SGA) is a large shared memory area that all server and background processes access. The SGA is broken into various pools as shown in the following diagram:

These pools are explained as follows:

• Database buffer cache: This is where Oracle stores database blocks before writing them to the disk and after reading...

Automatic Memory Management

Oracle Database 11g XE uses the Automatic Memory Management (AMM) feature that simplifies SGA and PGA memory management significantly. The MEMORY_TARGET initialization parameter is used to automatically manage memory needs. When the MEMORY_TARGET parameter is set, Oracle Database 11g XE automatically sizes various memory components such as database buffer cache, Java pool, shared pool, streams pool, large pool, and process global area.

You cannot disable AMM in Oracle Database XE configuration. The MEMORY_TARGET parameter by default is set to 1 GB when you install Oracle Database XE. Oracle Database 11g XE includes four new views to support automatic memory management:

• V$MEMORY_CURRENT_RESIZE_OPS

• V$MEMORY_DYNAMIC_COMPONENTS

• V$MEMORY_RESIZE_OPS

• V$MEMORY_TARGET_ADVICE

The amount of memory allocated to each dynamic component is displayed using the V$MEMORY_DYNAMIC_COMPONENTS view, as shown in the following command:

SQL> SELECT * FROM v$memory_dynamic_components...

Important background processes

There are three types of process in Oracle Database 11g XE:

• Background processes: These are the processes that start and stop the database. They are also responsible for writing blocks to the disk, maintaining the online redo log, and performing other background jobs.

• Server processes: These processes perform work based on the client's request. Tasks performed by these processes include loading data from the disk to SGA, modifying blocks in memory, and so on.

• Slave processes: These processes perform jobs on behalf of background or server processes.

We will look at the important background processes:

• Database Block Writer (DBWn):  This is responsible for writing dirty (modified) blocks to the disk, thus making buffer space available. More than one DBWn process can be configured to enhance the writing of dirty blocks to the disk.

• Log Writer (LGWR): This is responsible for flushing the contents of the redo log buffer to online redo logfiles. It flushes...

Physical and logical database structures

A database is a collection of physical operating system files. The files that make up a database are redo logfiles, datafiles, control files, and temporary files. A logical database structure consists of tablespaces.

A tablespace is a logical storage unit within Oracle Database 11g XE. It is considered logical because a tablespace is not visible in the filesystem. A tablespace is a collection of one or more datafiles. A datafile belongs to one and only one tablespace. There are three types of tablespace in Oracle Database 11g XE — permanent, temporary, and undo. All our tables, indexes, and stored subprograms reside in a permanent tablespace. A temporary tablespace is used to process temporary data such as storing intermediate sorting results. All information related to undo (roll back) is stored in the undo tablespace. Undo records are used to roll back transactions when a ROLLBACK statement is issued or during recovery of the database.

Datafiles...

Managing the Flash Recovery Area

Oracle Database 11g XE stores database backups, redo logfiles, and archive redo logfiles in Flash Recovery Area (FRA). Optionally you can place a member of the multiplexed control file in FRA. The V$RECOVERY_FILE_DEST view provides details of FRA location and usage. Run the following query to determine the FRA details:

SQL> set line 100
SQL> column name format a45
SQL>
SQL> SELECT name,
2 ROUND(space_limit/1024/1024) space_limit,
3 ROUND(space_used/1024/1024) space_used,
4 ROUND(space_reclaimable/1024/1024) space_reclaimable
5 FROM v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE
--------------------------------------------- ----------- ---------- -----------------
C:\oraclexe\app\oracle\ fast_recovery_area 10240 100 0
SQL>

Using the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters we can define the FRA location and size. To set the flash recovery area to 50 GB, enter the following commands...

Summary

In this chapter we explored all the components that make up an SGA. Also, we learned about the PGA. We also explored the important types of files that make an Oracle database such as the parameter file, password file, control file, datafile, and redo logfiles. We reviewed how to create and manage tablespaces and control the Flash Recovery Area.

The next chapter is dedicated to data loading and unloading. In this chapter, we will discuss different methods and options available within Oracle Database to load and unload the data using the Oracle proprietary tools.