Section 16.2. Oracle Architecture


16.2. Oracle Architecture

As mentioned in Chapter 15, it is important to understand the design of the database that is being backed up. Therefore, this chapter starts with a discussion of Oracle architecture. Similar information is provided in Chapter 15, but this chapter concentrates on information specific to Oracle. Just as in Chapter 15, we start with the power user's view of the database, then continue with that of the database administrator. This chapter uses Oracle-specific terms. To see how a particular term relates to one used in DB2, SQL Server, or Sybase, consult Chapter 15. As much as possible, these architectural elements are presented in a "building block" order. Elements that are used to explain other elements are presented first. For example, we explain what a tablespaceis before explaining what the backup tablespace command does.

16.2.1. The Power User's View

Unless a power user wants to start doing the DBA's job of putting a database together, the following terms should be all she needs to know. This view also could be called the "logical" view, because many of the elements described in this view don't exist in a physical sense.

16.2.1.1. Instance

An instance is a set of processes through which the Oracle database talks to shared memory and the shared memory that Oracle allocates for its use. Often, there is more than one instance on a single system. When an Oracle instance is opened, the database connected to it becomes available.

On a Unix/Linux system, an instance can be identified by a set of processes with the pattern ora_ function_SID, where function is a string indicating which Oracle function the process applies to and SID is the instance name. On Windows, each Oracle SID has its own service named OracleService SID, where SID is the instance name.

On a Unix/Linux system, an instance is automatically started with the dbstart script and shut down with the dbshut script. You can use these scripts or SQL*Plus commands to manually stop and start Oracle. In Unix or Linux, you have to place this script into the appropriate directory and add the appropriate symbolic links to have it run automatically after a reboot. To automatically start and stop instances in Windows, you should enable automatic start in the Control Panel for the appropriate OracleService. (If you cannot find OracleService SID in the Control Panel, you can create it using the oradim utility.) You then need to tell Oracle to automatically start the database when the service is started. You can do this by right-clicking on the SID in the Administrative Assistant; choosing Startup/Shutdown Options; choosing the Oracle Instance tab; and selecting "Start up instance when service is started," "Shut down instance when service is stopped," or both.

16.2.1.2. Database

The database is what most people think about when they are using Oracle. That's because the database contains the data! A database is a collection of files that contain tables, indexes, and other important database objects. Unless you're using Oracle Real Application Clusters (RAC), there is a one-to-one relationship between instances and databases. Without Oracle RAC, a database connects to only one instance, and an instance mounts only one database. RACs run multiple instances (most likely on multiple servers) that share a single database.

That is why Oracle DBAs often use the two terms interchangeably. Technically, though, the instance is a set of processes through which the database talks to Oracle's shared memory segments whereas the database is the collection of files that contain the data.

16.2.1.3. Table

A table is a collection of related rows that all have the same attributes. There are three types of tables in Oracle: relational, object, and XML.

16.2.1.4. Index

A database index is analogous to an index in a book: it allows Oracle to find data quickly. Again, an Oracle index is the same as anyone else's index, and it presents no unique backup requirements. An index is a derived object; it is created based on the attributes in another table so that it can be recreated during a restore, and it's usually faster to recreate an index than to restore it. Oracle has several types of indexes, including normal, bitmap, partitioned, function-based, and domain indexes.

16.2.1.5. Large object datatypes

Oracle 8 has special datatypes called BLOB, CLOB, and BFILE for storing large objects such as text or graphics. The BLOB and CLOB datatypes present no special backup requirements because they are stored within the database itself. (A BLOB typically contains image files, and a CLOB normally contains text data.) However, the BFILE datatype stores only a pointer inside the database to a file that actually resides somewhere in a filesystem on the database server. This aspect requires some special attention during backups. See the section "LOB space" in Chapter 15 for more information.

16.2.1.6. Object

An object is any type of database object, such as a table or an index; it's really a generic term rather than an Oracle-specific term. Unfortunately, Oracle also uses the term "object" to refer to reusable components created by object-oriented SQL programming. Here, it is used simply as a generic way to refer to any type of table, index, or other entity within Oracle.

16.2.1.7. Row

A row is a collection of related attributes, such as all the information about a specific customer. Oracle also may refer to this as a record.

16.2.1.8. Attribute

An attribute is any specific value (also known as a "column" or "field") within a row.

16.2.2. The DBA's View

Now that we have covered the logical structure of an Oracle database, let's concentrate on the physical structure. Since only the DBA should need to know this information, we will call it "the DBA's view."

16.2.2.1. Blocks

A block is the smallest piece of data that can be moved within the database. Oracle allows a custom block size for each instance; the size can range from 2 K to 32 K, and each tablespace (defined later) can have its own block size. A block is what is referred to as a page in other RDBMSs.

16.2.2.2. Extents

An extent is a collection of contiguous Oracle blocks that are treated as one unit. The size of an extent varies based on a combination of factors, the most important of which is the tablespace storage allocation method defined at tablespace creation.

16.2.2.3. Segment

A segment is the collection of extents dedicated to a database object. (An object is a table, index, etc.) Depending on the type of object, extents may be allocated or taken away to meet the storage needs of a given table. Oracle8 had the rollback segment, but this has been replaced in later versions with undo segments, which are stored in an undo tablespace. (See the later sections "Tablespace" and "Undo tablespace.")

16.2.2.4. Datafile

An Oracle datafile can be stored on either a raw (disk device) or cooked (filesystem) file. Once they are created, the syntax to work with raw and cooked datafiles is the same. However, if rman is not used to back up the Oracle datafiles, backup scripts do have to take the type of datafile into account. If the backup script is going to support datafiles on raw partitions, it will need to use dd or some other command that can back up a raw partition. Using cp or tar will not work because they support only filesystem files.

Each Oracle datafile contains a special header block that holds that datafile's system change number (SCN). Each transaction is assigned an SCN, the SCN in each datafile is updated every time a change is made to that datafile, and the control file keeps track of the current SCN. When an instance is started, the current SCN is checked against the SCN markers in each datafile. (See the section "Control file " later in this chapter.)

Please see an important explanation of the role that the SCN plays during hot backup in the section "Debunking Hot-Backup Myths" later in this chapter.


16.2.2.5. Tablespace

The tablespace is the virtual area into which a DBA creates tables and other objects. It consists of one or more datafiles and is created by the create tablespace tablespace_name datafile device command. A tablespace may contain several tables. The space that each object (e.g., table) occupies within that tablespace is a segment (see the earlier definition of segment).

As of 10g, every Oracle database has at least three tablespaces: system, sysaux, and undo. The system tablespace stores the data dictionary, PL/SQL programs, view definitions, and other types of instance-wide information. The sysaux tablespace stores non-system-related tables and indexes that traditionally were placed in the system tablespace. Its name implies that it is the auxiliary system tablespace. The undo tablespace contains the undo segments, which replaced rollback segments. When it comes to backup and recovery, the main difference between these tablespaces and the rest of the tablespaces is that they must be recovered offline. That is because the instance cannot be brought online without these tablespaces. Other tablespaces can be recovered after the instance has been brought online.

16.2.2.6. Partition

A table or index can be divided into chunks called partitions and spread across multiple tablespaces for performance and availability reasons. As long as you are backing up all tablespaces, partitioned tables do not present any challenges to backup and recovery.

16.2.2.7. Control file

The control file is a database (of sorts) that keeps track of the status of all the files that comprise a given database and maintains rman backup metadata. It knows about all tablespaces, datafiles, archive logs, and redo logs within the database. It also knows the current state of each of these files by tracking each object's SCN. Every transaction is assigned an SCN, and every time a change is made, the SCN gets updated both in the control file and in each datafile. (See the earlier section "Datafile.") That way, when the instance is starting up, the control file has a record of what SCN the file should be at, and it checks that against the SCN that the file has. This is how it "notices" that a file is older than the control file and in need of media recovery. Also, if an older control file is put in place, Oracle will see that the SCN of the datafiles is higher than those that it has recorded in the control file. That's when Oracle displays the datafile is more recent than the controlfile error.

If you're performing user-managed backups, control files should be backed up using both the backup controlfile to filename and backup controlfile to TRace commands. If you're using rman, you can use the backup controlfile command, or you can have rman automatically back up the control file every time you run a backup by setting the controlfile autobackup parameter to on. You should also issue the backup controlfile to trace command within sqlplus. Additionally, it's a good practice to copy the results of this command to a known location to make it easy to find during recovery. There is a scenario that we will cover in the "Recovering Oracle" section where this trace output will come in quite handy. Restoring control files is a bit tricky. The mechanics of restoring control files are covered later in the section "Recovering Oracle."

Back up the control file to a file or within rman, and back it up to trace, too. Both backups can be very useful.


It is best to avoid having to restore or rebuild a control file. The best way to do this is to mirror your control files within Oracle via a function Oracle calls multiplexing. This allows you to create two or three copies of the control file, each of which is written to every time the control file is updated.

Please don't confuse the Oracle term multiplexing with how this term is used everywhere else in the backup and recovery space (the sending of simultaneous backup streams to a single tape drive). To minimize this confusion, this chapter refers to multiplexing in Oracle as multiplexing/mirroring or multiplexed/mirrored.


Multiplexing/mirroring is slightly different from disk-level mirroring and offers an additional level of protection. With disk-level mirroring, you have one control file that's mirrored to multiple disks via RAID 1. This protects you from disk failure, but not human error. If someone accidentally deleted the control file, it would instantly be deleted on each disk it was mirrored on. Storing multiplexed/mirrored control files on separate disks protects you from disk failure as well as accidental deletion. If a single control file gets deleted or corrupted, the instance becomes inoperable. However, the deleted/corrupted control file can easily be restored via one of the multiplexed copies, and the instance can be returned to proper operation.

Make sure you are multiplexing/mirroring your control files to separate disks. They take up very little space and provide an incredible amount of recovery flexibility.


16.2.2.8. Transaction

A transaction is any activity by a user or a DBA that changes one or more attributes in an Oracle database. (A set of commands that ends with a commit statement is treated as one transaction.) Logically, a transaction modifies one or more attributes, but what eventually occurs physically is a modification to one or more blocks within the Oracle database.

16.2.2.9. Undo tablespace

Undo data is now used for three purposes, the first of which is to undo an aborted transaction or a transaction that was not yet committed when the database crashed. Undo information also provides a consistent read for long-running queries. (See the section "ACID Compliance" in Chapter 15.) Finally, undo information can be used by Oracle's flashback feature, which allows you to manually undo transactions. For these reasons, it's important to keep undo information as long as possible.

Prior to 9i, undo was managed using rollback segments. The biggest challenge with rollback segments was trying to figure out how large they should be. If you made them too large, you wasted space. If you made them too small, you would get the dreaded ORA-01555 snapshot too old error, indicating that a long-running query was not able to obtain the consistent read it needed. If you had a very active database with a lot of long transactions or long running queries, this could prove to be a real problem.

Oracle 9i introduced automatic undo management, or AUM. You can continue to use manual undo management using rollback segments, but once you understand the value of AUM, it's hard to understand why you wouldn't use it. When configuring an Oracle database, you now specify an undo tablespace. Oracle automatically creates undo segments in this tablespace instead of requiring you to manually create rollback segments. AUM eliminates the complexities of managing rollback segment space and lets you exert control over how long undo is retained before being overwritten. Oracle strongly recommends that you use undo tablespaces to manage undo rather than rollback segments.

At first, it's hard to differentiate between a tablespace dedicated to rollback segments and a tablespace dedicated to undo segments. Perhaps an explanation of how Oracle manages the automatic deletion of older undo data will help.

You can specify a minimum amount of time that undo information is to be kept using the undo_retention parameter. When Oracle needs space for more undo data, Oracle does its best to honor that value by deleting the oldest expired undo data first. However, if the undo tablespace is out of space, Oracle may begin deleting unexpired undo information, which unfortunately can result in the same snapshot too old error that you could experience with rollback segments. However, if you enable autoextend on the undo tablespace, the tablespace automatically extends to the maximum size you have specified. This automatic space management is what makes AUM so popular with those who have used it.

16.2.2.10. Checkpoint

A checkpoint is the point at which all changed data that is kept in memory is flushed to disk. In Oracle, a DBA can force a checkpoint with the alter system checkpoint command, but a checkpoint also is done automatically every time the database switches to a different online redo log group.

16.2.2.11. Flash recovery area

The components that create different backup and recovery-related files (e.g., rman, alter database backup controlfile, alter system switch logfile) have no knowledge of each other or of the space available in the filesystem in which they're storing data.

One of the big reasons to upgrade to Oracle 10g is its flash recovery area, which solves this problem by automating management of backup-related files. Choose a location on disk and an upper limit for storage space, and set a retention policy that governs how long backup files are needed for recovery, and the database automatically manages the storage used for backups, archived redo logs, and other recovery-related files for your database within that space. To create a flash recovery area, specify a value for the db_recovery_file_dest and db_recovery_file_size parameters in your startup file. To have archive logs sent to the flash recovery area, specify location = use_db_recovery_file_dest as the value for one of your log_archive_dest_ n parameters.

16.2.2.12. Redo log

If the undo tablespace or rollback segments contain the information to roll back a transaction, the redo log contains the information to "roll forward" transactions. Every time that Oracle needs to change a block on disk, it records the change vector in the redo log; that is, it records how it changed the block, not the value it changed it to. A mathematical explanation may be helpful here. Suppose that you had a variable with a value of 100 and added 1 to it. To record the change vector, you would record +1; to record the changed value, you would record 101. This is how Oracle records information to the redo logs during normal operation. As explained in the later section "Debunking Hot-Backup Myths," it switches to recording the changed value when a tablespace is in hot-backup mode.

In times of recovery, the redo log is used to redo (or replay) transactions that have occurred since the last checkpoint or since the backup that is being used for a restore. Oracle can be configured to use both online redo logs and offline (archived) redo logs.

The online and archived redo logs are essential to recovering from a crash or disk failure. Learn everything you can about how they work and protect them as if they were gold!


Originally, the online redo logs were a few (typically three) files to which Oracle wrote the logs of each transaction. The problem with this approach is that the log to which Oracle was currently writing always contained the only copy of the most recent transaction logs. If the disk that this log was stored on were to crash, Oracle would not be able to recover up to the point of failure. This is why, in addition to multiplexing/mirroring the control file, Oracle also supports multiplexing/mirroring redo logs as well. Instead of using individual redo logs, you can now write redo information to a log group. A log group is a set of one or more files that are written to simultaneously by Oracleessentially a mirror for the redo logs. Believe it or not, you're only required to have one member of each log group. Obviously, if you don't have multiple members of the log group, though, you're not helping yourself much. The usual practice is to put three members of each log group on three separate disksoften the same disks that you're multiplexing your control files to. The separate files within a log group are referred to as members. Each log group is treated as a single logfile, and all transaction records are simultaneously written to all disks within the currently active log group.

Now, instead of three or more separate files, any one of which could render the database useless if damaged, there are three or more separate log groups of multiplexed/mirrored files. If each log group is assigned more than one member, every transaction is being recorded in more than one place. After a crash, Oracle can read any one of these members to perform crash recovery.

Oracle writes to the log groups in a cyclical fashion. It writes to one log group until that log group is full. It then performs a log switch and starts writing to the next log group. As soon as this happens, the log group that was just filled is then copied to an archived redo logfile, if running in archivelogmode and automatic archiving is enabled. If archivelogmode is not enabled, this file is not copied and is simply overwritten the next time that Oracle needs to write to that log. If automatic archiving is not enabled, the instance hangs the next time Oracle needs to write to an unarchived redo log.

For nonenterprise customers, each of the online redo logs is copied to the filename pattern specified by the value in one or more log_archive_dest_ n parameters in the parameter file, followed by an incremented string specified by the log_archive_format parameter in the parameter file. For example, assume that log_archive_dest_0 is set to /archivelogs/arch and log_archive_format is set to %s .log, where %s is Oracle's variable for the current sequence number. If the current sequence number is 293, a listing of the archivelogs directory might show the following:

# cd /archivelogs/arch # ls -l arch* arch291.log arch292.log arch293.log

The log_archive_dest_ n parameter is an enhanced version of the log_archive_dest parameter. Where log_archive_dest could be set to only one destination, you can have multiple log_archive_dest_ n parameters, each of which can be set to a directory or to the flash recovery area by specifying db_recovery_file_dest. (See the section "Flash recovery area" earlier in this chapter.)

Depending on how much activity a database has, the archive log destination directory may have hundreds of files over time. If you send archive logs directly to a directory, Oracle does not manage this area; however, if you're sending archive logs to the flash recovery area, Oracle manages the space for you. If you're managing the space, a cron job must be set up to clean up archive log destinations. As long as these files are being backed up to some kind of backup media, they can be removed after a few days. However, the more logs there are on disk, the better off the database will be because it may sometimes be necessary to restore from a backup that is not the most current. (For example, this could happen if the current backup volume is damaged.) If all the archive logs since the time the old backup was taken are online, it's a breeze. If they aren't, they have to be restored as well. That can create an available-space problem for many environments. This is why I recommend having enough space to store enough archive logs to span two backup cycles. For example, if the system does a full database backup once a night, there should be enough space to have at least two days' worth of redo logs online. If it backs up once a week, there should be enough storage for two weeks' worth of transaction logs. (This is yet another reason for backing up every night.)

In summary, the online redo logs are usually three or more log groups that Oracle cycles through to write the current transaction log data. A log group is a set of one or more logs that Oracle treats as one redo log. Log groups should have more than one member; more than one member means there is little chance for data corruption in case of disk failure. Once Oracle fills up one online redo log group, it copies that redo log to the archive log destination as a separate file with a sequence number contained in the filename. It makes this copy only if you are running in archivelog mode and automatic archiving is enabled.

16.2.2.13. Initialization parameters

There are a number of initialization parameters in Oracle that are important to know during recovery, so it's vital to learn where those parameters are stored both in and outside of the database.

Historically, these initialization parameters were stored in a text file named init<SID>.ora. You could change most of the parameters inside the database, but if you wanted them to survive a reboot, you also had to make the changes in the init<SID>.ora file.

Oracle 9i introduced the concept of a server parameter file, or spfile for short. An spfile is a binary file named spfile<SID>.ora that is usually stored in the ORACLE_HOME/dbs directory. It cannot be edited, but it can be directly controlled by Oracle, allowing dynamic configuration changes to be written to the spfile automatically so that they will survive a reboot. For those who are used to init<SID>.ora files, an spfile can be a bit of an adjustment because there's nothing for you to edit. There are actually a few ways to change initialization parameters.

If the database is running, simply set the appropriate parameter via the alter system set parameter_name = value command. This action immediately changes the parameter in the running database and also stores it in the spfile. This is certainly easier than the old method of changing it in the database and the init.ora file. Some parameters, such as log_archive_start, cannot be changed this way. You must change them in the spfile and then restart.

You can query both the currently used parameters and the parameters stored in the parameter file using v$parameter and v$sparameter tables, respectively.


If you want to change a parameter in the spfile without using the database to do it, you need to create a text version of the file, edit it, then import the text file into a new spfile. The following SQL command creates a pfile called pfilename from the current spfile:

SQL> create pfile='pfilename' from spfile

Once you've edited pfilename and made the appropriate changes, you can make a new spfile from the pfile using this SQL command:

SQL> create spfile from pfile='pfilename'

Although you can query the v$parameter or v$sparameter tables even if a database is down, it might be helpful to occasionally make a text export of your spfile to allow you to look at it during a recovery.


16.2.2.14. Restore versus recover

Oracle distinguishes between a restore and a recovery. A restore is meant in the traditional sense of the term; it returns the datafile to the way it looked when you last backed it up by reading that backup from tape or disk. You then initiate a recovery, which applies redo against that datafile to bring it up to the current point in time. This is also referred to as media recovery.

16.2.3. Finding All Instances

All procedures in this chapter assume you know what the instances are on your server or that you're able to determine what they are. Unfortunately, there is no foolproof way to determine all instances on all machines. For example, I could tell you to use the oratab file on Unix, but not everybody uses it, and it can be out of date. For Windows, I could tell you to list the registry keys matching a certain pattern, but like the oratab file, these keys do not always represent real instances. Another method would be to list the instances that are actually running on the machine. This finds active instances, of course, but it won't find instances that were not running when the backup ran.

The best you can do is to tell everybody the method you're using to determine the list of instances, then stick with it. Tell them that if something's in oratab or listed in the Windows Registry, it gets backed up. If it's not there, it won't get backed up. Tell them that you're going to back up any running instances and will not back up instances that were manually shut down. Just make a choice, and publish it well.

You can enforce the use of the oratab file on Unix/Linux or the registry keys in Windows by automatically starting only databases found there and taking note any time someone complains about an instance that didn't start after a reboot. Explain to them that it needs to be in the registry or oratab. If an instance is not in the oratab, it is not automatically started, and it doesn't get backed up!

The oratab file is usually located in /etc/oratab or /var/oracle/oratab. In Windows, you want to look at the following registry tree:

\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEn

Usually n is 0, but there can be additional registry trees with other digits if there are additional ORACLE_HOMEs configured on this machine. Underneath this registry tree, you should find one or more of the following values:

  • ORA_ SID _AUTOSTART

  • ORA_ SID _PFILE

  • ORA_ SID _SHUTDOWN

You can then parse that list to determine a list of Oracle SIDs.

If you want to use the oratab/registry method but also want to double check that you're getting everything, you can also use the process list method discussed in the following paragraphs to see if any instances are running that aren't in the oratab or registry.


One way to do this is just to list the instances that are running on the machine. This, of course, lists only instances that are running. To get a list of all instances running on a Unix/Linux system, use a variation of this command:

$ ps -ef|grep "ora_.*pmon" | awk '{print $6}' \ | awk -F_ '{print $2}'

The output of the ps command can obviously be different in different versions of Unix. You may have to change the column that this command prints from $6 to something else to get this command to work for you.


On Windows, each Oracle SID has its own service named OracleService SID, where SID is the instance name. You can get a list of running Windows instances using this command:

C:> net start | find "OracleService"    OracleServiceXYZ    OracleServiceABC

If you would like to make this list even better, download the sed command from the GnuWin32 project (http://gnuwin32.sourceforge.net), and add it to the list of commands. The following sed command tells sed to strip off all characters up to the string OracleService. This leaves you with a list of instance names.

C:> net start | find "OracleService" \ | sed "s/.*OracleService//" XYZ ABC

Again, the best thing you can do is pick one of these methods (preferably the oratab/ registry method), then publish it and enforce it.





Backup & Recovery
Backup & Recovery: Inexpensive Backup Solutions for Open Systems
ISBN: 0596102461
EAN: 2147483647
Year: 2006
Pages: 237

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net