'; zhtm += '

' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 12 - Understanding Oracle8 Backup Options

Using Oracle8

Chapter 12 Understanding Oracle8 Backup Options

Types of Failure
Archiving Your Database
Backup Options

What can happen to a database
Backing up with the database shutdown
Backing up with the database open and available to users
How to recover to the last commit
Windows NT-based recovery tools
Running more than one copy of the database

Types of Failure

When you're planning a backup strategy, it's useful to consider the day-to-day hazards that eventually cause any database system to fail. No matter how many UPSs or mirrored disks you have, no matter how regulated your computing environment is, every database system will experience an unexpected failure.

Database failures can generally be divided into two categories:

Instance failure is generally the result of an Oracle internal exception, operating system failure, or other software-related database failure. Instance failure will be the diagnosis when the necessary Oracle processes (PMON, SMON, DB Writer, Log Writer) are no longer running and the database wasn't shut down normally. Although instance failures can directly or indirectly lead to database corruption, they're generally nondamaging in nature. Often, simply restarting the database allows operations to continue normally.
Media failure, by contrast, is usually far more sinister. Media failure will usually manifest itself by the database being unable to read data it has previously written. Leading causes of media failure include disk drive failure, bad disk block, deleted data files, and damaged file system(s).

Media failure, unlike instance failure, almost always results in damage to your database that must be repaired before the database can resume normal operations. Fortunately, Oracle8 provides many methods for recovering from data loss.

Archiving Your Database

Because databases often contain mission-critical information, an organization may not be able to tolerate the loss of any transactions whatsoever. If a database were backed up every night and suffered a disk failure a few minutes before backups were scheduled to begin, you could lose up to around 23 hours' worth of transactions. Oracle provides an elegant solution to this problem in the form of archive logs.

Impact of ARCHIVELOG mode on disk requirements

Running a database in ARCHIVELOG mode can seriously affect your database's disk needs, depending on how much activity your database has. Because a copy of every write operation is kept, you may need tens or even hundreds of megabytes of disk space to store all the archived redo logs for one day.

Oracle keeps a record of most every operation in its redo logs in order to guard against loss of database buffers should the database instance fail. Because these logs, in aggregate, contain everything needed to reconstruct a database from any time in the past, they can be used to recover from media failure. By default, Oracle overwrites the redo log groups in a round-robin fashion. This is sufficient for instance recovery because a log switch forces a checkpoint that, in turn, forces all dirty database buffers to be written to disk.

To guard against media failure, however, it's necessary to keep the redo logs archived since at least the last physical database backup (and in practice, you'll want to keep them much longer). Oracle refers to this as running the database in ARCHIVELOG mode; it will archive every redo log file that's filled up.

Keep archive logs on disk

Although Oracle allows you to spool your archived redo logs directly to tape on many system architectures, you're strongly advised not to do so. Archiving directly to tape is much slower and requires much more effort and testing than does archiving to disk. Disk space is very cheap these days.

Starting Archiving

By default, Oracle doesn't create a database in ARCHIVELOG mode. You have to manually place a database into ARCHIVELOG mode as soon as it's created, but when you do so, Oracle will stay in ARCHIVELOG mode until you return it to NOACHIVELOG mode (at which time your database again becomes more vulnerable to media failure).

To check whether a database is running in ARCHIVELOG mode, check the LOG_MODE column in the V$DATABASE table. This is shown in an example:

SQL> select * from v$database;

NAME CREATED   LOG_MODE   CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
---- --------- ---------- ------------------ ---------------
TEST 02/14/98 08:03:01 NOARCHIVELOG 12964              12951

SQL>

In this example, the database TEST isn't running in ARCHIVELOG mode, as indicated by NOARCHIVELOG in the LOG_MODE column.

Enable ARCHIVELOG mode (general steps)

Modify the init.ora file.
Shut down the database.
Start the database in MOUNT EXCLUSIVE mode.
Enable ARCHIVELOG mode.
Perform a cold backup.
Restart the database normally.

Step 1: Modify the init.ora File

You must decide a couple of things before editing the init.ora file:

In what directory you will store archive logs
What format you want the filenames of the archived logs to follow

Determining what directory to store the archive logs in is very important. If you exhaust all the space available, Oracle will stop virtually all activity until space becomes available again.

When Oracle freezes

If Oracle suddenly freezes and doesn't respond to the most basic SQL statements, first check to make sure that you haven't used up all the space available to your archive logs. If you move some archive logs to another directory, Oracle will automatically resume database operations. If Oracle must wait for space to become available in the archive log directory, it will log this event in the applicable alert.log file.

The following parameters must be set in the database's appropriate init.ora file:

log_archive_start indicates whether Oracle should automatically archive filled redo logs. You should always set this to true.
log_archive_dest is the directory to which you want archive log files to be written. This directory should hold exclusively archived redo logs and have plenty of free space.
log_archive_format defines the format of the archived redo log filenames. Use %s to denote the sequence number of an archived redo log file. A good naming convention is to use the database SID followed by %s (to denote each sequence number), and then an .ARC to denote an archive log.
Here's an example of these settings on a UNIX system:

log_archive_start = true # if you want automatic archiving
log_archive_dest = /opt/oracle803/archive/test
log_archive_format = TEST%s.arc

A typical Windows NT init.ora setting might look like this:

log_archive_start = true
log_archive_dest = %ORACLE_HOME%\database\archive
log_archive_format = "TEST%S.ARC"

Monitoring the archive destination

Ideally, you should have a monitoring system in place to constantly keep watch for an archive destination directory that's quickly filling up. By being warned before your destination directory is actually full, you can take corrective measures before database operations are affected. BMC's Patrol product offers this capability, although many other excellent products in the market-place serve this need.

Step 2: Shut Down the Database

A normal (or immediate) shutdown is required to continue. The following example shows how to shut down the database in a UNIX environment. This example will close any open sessions on the database and roll back any transactions in progress.

Who's using the database?

By querying the V$SESSION view, you can see who's logged in to the database. V$SESSION can help you identify whether the database is in use and who's using it.

oreo:~$ svrmgrl
Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production

SVRMGR> CONNECT INTERNAL;
Connected.
SVRMGR> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR>

Step 3: Start the Database in MOUNT EXCLUSIVE Mode

Continuing the example from Step 2, this example shows how to use the STARTUP command with the MOUNT and EXCLUSIVE options:

SVRMGR> STARTUP MOUNT EXCLUSIVE;
ORACLE instance started.
Database mounted.
SVRMGR>

Step 4: Enable ARCHIVELOG Mode

The following ALTER DATABASE ARCHIVELOG command will place the database into ARCHIVELOG mode:

SVRMGR> ALTER DATABASE ARCHIVELOG;
Statement processed.
SVRMGR>

Step 5: Perform a Cold Backup

Cold backups and changing ARCHIVELOG mode

You may want to alternate between ARCHIVELOG and NOARCHIVELOG modes. It's absolutely essential that you perform a cold backup after you re-enable ARCHIVELOG mode. Failure to do so may render your archive logs useless.

By following the procedures outlined in Chapter 13, "Selecting and Implementing a Backup Strategy," you must perform a cold backup of the database before continuing. This is necessary because the archived redo logs are useful only when they can be applied to a database backup made since ARCHIVELOG mode was enabled.

Step 6: Restart the Database Normally

In Server Manager, restart the database normally to allow users back onto the database. The V$DATABASE view will now reflect the switch to ARCHIVELOG mode in the LOG_MODE column.

Stopping Archiving

From time to time, it will be beneficial to stop archiving on your databases. For instance, during a maintenance period, you may be importing or deleting large amounts of data that would generate an excessive number of archive logs.

Stopping archiving will still provide recovery in the event of instance failure, but should a media error occur, it will be necessary to restore from the last cold backup.

Stop archiving (general steps)

Shut down the database.
Start the database in MOUNT EXCLUSIVE mode.
Enable NOARCHIVELOG mode.
Open the database.

Step 1: Shut Down the Database

Just as when ARCHIVELOG was enabled, the database must first be shut down. The following example shows how to shut down the database in a Windows NT environment. This example will close any open sessions on the database and roll back any transactions in progress:

D:\ORANT\BIN\SVRMGR30
Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production

SVRMGR> CONNECT INTERNAL;
Connected.
SVRMGR> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR>

Step 2: Start the Database in MOUNT EXCLUSIVE Mode

The database must be in MOUNT EXCLUSIVE mode to change between NOARCHIVELOG and ARCHIVELOG mode:

SVRMGR> STARTUP MOUNT EXCLUSIVE;
ORACLE instance started.
Database mounted.
SVRMGR>

Step 3: Enable NOARCHIVELOG Mode

Backing up after NOARCHIVELOG

Although a cold backup isn't required when switching to NOARCHIVELOG mode, it's recommended that you do so anyway. By making a cold back-up at this time, you'll have a known fallback point to restore to should you experience media failure or data corruption.

The ALTER DATABASE NOARCHIVELOG command places the database into NOARCHIVELOG mode:

SVRMGR> ALTER DATABASE NOARCHIVELOG;
Statement processed.
SVRMGR>

Step 4: Open the Database

Use the ALTER DATABASE OPEN command to open the database for normal activity:

SVRMGR> ALTER DATABASE OPEN;
Statement processed.

At this time, you may want to query the V$DATABASE view to confirm the change in log mode.

The Automatic Archive Process

In the procedure for enabling ARCHIVELOG mode, recall that the following line was added to the init.ora file to allow automatic archiving of the filled redo logs:

log_archive_start = true

By starting the automatic archiver, the database will copy redo logs to the designated destination directory as each redo log is filled. This asynchronous process won't necessarily begin copying at the instant of a logfile switch, but it usually can keep up with your database's transaction load.

Confirming that the archive process is running

On UNIX systems, you can confirm that the archive process is running by looking for a process named ora_arch_YourSID.

Backup Options

Document backup and recovery

Backup systems often run for quite some time without any DBA intervention required. Unfortunately, memories fade and staff turnover can lead to confusion when the time comes to restore a database. It's essential that backup and recovery procedures be tested and documented. Chances are that during a high stress database recovery, not everyone is going to remember the subtle details that can make or break a recovery effort.

The need to back up databases is certainly obvious enough. However, databases-because of their highly structured and transaction-centric nature-have special backup and recovery needs. By carefully reading and understanding the backup strategies Oracle provides, you can implement a reliable backup strategy that meets your organization's needs.

The most important aspect of any backup plan is to thoroughly test database restores in a test environment. Backups can often appear to have run properly but be incorrect for recovery situations. It's absolutely imperative that all DBAs have first-hand experience with backup and recovery plans.

Understanding Cold Backups

The simplest and most straightforward backup in Oracle is known as a cold backup. Essentially, a cold backup involves nothing more than shutting down the Oracle database instance and backing up all the relevant database files, including

Backing up redo logs

Oracle doesn't technically need redo logs backed up. Because the database isn't running, however, they can be safely backed up and will prevent you from having to recreate redo logs should you need to restore the database.

Data files
Control files
Redo logs
Archived redo logs
init.ora and config.ora (if applicable)

The key to cold backups is that you must have the database instance shut down before beginning. Although the backup process may very well appear to work with the database running, it's very possible that the backup will be corrupted and unusable.

When backing up the database, be sure to also back up all the Oracle program files. All these files are typically found under the ORACLE_HOME directory. This directory tree often contains additional configuration files, such as Net8 files and any applied patches.

Unlike many database systems, Oracle doesn't provide a backup and restore system per se. Oracle instead relies on operating system utilities, such as tar in UNIX. Although this may seem to be a weakness at first, it's actually something of a feature. Many organizations spend a great deal of money on complex and robust backup systems far superior to anything any database maker now bundles with their product. Oracle, in keeping with its history of flexibility, lets you use the best backup tools available for your environment to back up the operating system and the database.

Oracle's Enterprise Manager

Oracle's Enterprise Manager includes a backup and restore utility for Windows NT environments. While this utility is functional, most DBAs find that operating system and third-party backup tools work much better for them.

The advantages of cold backups are numerous:

Quick and easy
Fairly trouble-free implementation; most sites simply back up the database files as part of a full system backup
Simple restores
Very little site-specific customization required for implementation

The disadvantage of cold backups is that the database must be shut down. If you can afford to shut down a database for backups, cold database backups usually offer the best and easiest backup strategy.

Effects of taking down a database

Shutting down an Oracle data-base can have lasting effects beyond the actual backup period. When a database is shut down and restarted, the data dictionary cache is blank and there is no data in the database block buffers. The morning data-base activity following a backup cycle could be slowed down, as Oracle must reload the data dictionary and the working set of database blocks into the SGA.

SEE ALSO
More details on performing a cold backup,

Understanding Hot Backups

Oracle uses the term hot backups to describe the process of backing up the database while it's open and available to users. Hot backups are an essential component of 24-hour, 7-days-a-week operations, as it allows for backups necessary to data security without interrupting mission-critical operations.

Don't back up redo logs

Redo logs aren't backed up during hot backups. In fact, doing so can cause serious database corruption.

Hot backups will back up these components in a manner similar to a cold backup system:

Archived redo logs
init.ora and config.ora (if applicable)

However, special consideration must be given to data files and control files.

The advantage of hot backups is that the database can continue normal operations while the database is being backed up. On the other hand, hot backups have several disadvantages:

Testing hot backups

The need to test hot backup/recovery systems can't be overemphasized. It's absolutely essential that backups be periodically restored to test machines in order to verify that hot backups are being taken properly and to ensure that the skills needed to restore are kept sharp.

Much more complex to implement
Custom site-specific backup scripts must usually be written
Extensive testing required to prove viability

Despite the obvious advantage of hot backups, they typically require considerably more time and effort to successfully implement. While running a 24/7 operation may be the trendy thing to do these days, make sure that your business needs require this availability before incurring the time and expense of hot backups.

Although database operations can continue during a hot backup without interruption, it's still important for you to schedule backups during the least amount of database activity (UPDATE, INSERT, and DELETE operations, in particular). Hot backups will cause a database to incur additional overhead in terms of CPU, I/O, and higher production of archived redo logs.

If your organization truly needs to run 24/7, hot backups will provide a proven and robust solution to keep your business running and your database safe and secure.

SEE ALSO
More details on performing a hot backup,

Recovery Manager for Windows NT Databases

Preparing to use Recovery Manager

Always make a backup of the damaged database-even if parts of the database are missing- before using Recovery Manager (or any recovery effort, for that matter). That way you have something to fall back on if anything goes wrong during recovery and the database becomes more badly damaged. An excellent rule of thumb is to back up the database and try to recover it yourself once. If your first attempt fails, restore from the backup you made before trying to restore and call Oracle Worldwide Support for guidance.

Oracle8 DBAs supporting Windows NT-based databases have a convenient tool for recovering damaged databases: Recovery Manager (see Figure 12.1). Recovery Manager is a GUI-based tool that simplifies database recovery in these cases:

Figure 12.1 : This is the opening window for Oracle's Recovery Manager on Windows NT 4.0.

Recovering from lost or damaged data file(s)
Replacing lost or damaged control file(s)
Performing complete restores from a database backup

Recovery Manager also has an automatic recovery option that may be able to automatically recover a database with little or no DBA intervention. This option isn't a silver bullet for solving all database recovery problems, however. Automatic recovery can't work correctly unless the proper up-front work has been done to ensure that database backups are performing regularly and correctly.

Using Database Exports as a Backup Strategy

The backup methods discussed here have been physical in nature. That's to say that the backup methods all copy a database's physical data files verbatim to a backup device. These backups provide a fast backup and are relatively easy to use in a restore process should media failure occur.

Backing up just a database's data through Oracle's Export utility is known as a logical backup. In many instances it's useful (and necessary) to use logical backups:

Object or row recovery. If someone inadvertently drops a table or deletes rows from a table, it's exceedingly difficult to restore just a particular table or a few rows from a physical backup.
Major release upgrade of Oracle. When Oracle6 was replaced by Oracle7, Oracle changed the underlying database structures. This necessitated that the data contained in Oracle6 databases be exported and then imported into a new Oracle7 system. Oracle will advise when this operation is necessary.
Migrating an Oracle database between significantly different database servers. The physical data structures between different platforms (MVS, VMS, Windows NT, and UNIX) aren't the same. In order to move data between different Oracle databases on different platforms, it's necessary to export data from the source platform and then import the data into the target system.
Moving data between two instances on the same physical machine. Because a physical backup/restore on the same machine would normally overwrite the source instance, it's necessary to export the data from the source instance and then import it into the target instance.

Export is sometimes needed with similar platforms

Using exports is advised sometimes even when moving data between machines of the same platform. If the data, redo, and control files won't be placed in the same location, it's advisable to export data from the source machine and then import it into a target system configured to receive the data.

Exports can be performed with the database up and running. Oracle's built-in read consistency allows it to have a read-consistent view of each table in the database. However, referential integrity can't be guaranteed because Oracle's read consistency will be applied only on a table-by-table basis. If at all possible, exports should be run with the database running in restricted mode.

Regular exports should complement hot or cold backups in any backup strategy. Export is a necessary component primarily to provide recovery from the loss of data due to dropped tables or deleted rows. Hot and cold backups are generally useful only for recovery from media failure. User or application errors are often recoverable only from logical backups.

Understanding Incremental Backups

Incremental exports work at the table level

When performing an incremental export, be aware that the full table is exported-not just the changed row(s)-if anything at all changes in a table.

Keeping an up-to-date export backup on hand doesn't always require that the complete database be exported. Oracle8 provides an incremental-type export that may reduce the time and space required for an export backup by exporting only tables that have changed since the last full or incremental export.

In many database systems, only a few tables are actually updated on a day-to-day basis. Many more tables are relatively static in nature and don't need to be exported during each export cycle to maintain a complete export backup. Many DBAs avoid making export backups regularly because of the time and space required to do so. You may be able to overcome this obstacle by making several incremental export backups between full export backups.

Understanding Standby Databases

Pitfalls with standby databases

Standby databases can work only if the delivery of every archive log from the production machine can be guaranteed. If an archive log is lost, it is necessary to resynchronize the stand-by database machine with a fresh hot or cold backup. For this reason, you need to implement an automated delivery system of archive logs from the production database to the standby database.

Oracle version 7.7.3 introduced the concept of a standby database, which allows you to configure a database that's close to being up-to-date with an online production database instance. In case of a production instance/machine failure, the standby database can be opened, which allows normal database activity to continue.

A standby database is, first, an identical copy of the production database (usually this is done by restoring from a cold backup). From this synchronization point, all archive logs generated by the production database machine will be copied to the standby database machine and applied to the database. The standby database is, essentially, always running in recovery mode because it's actively applying archive logs any time the production database is in operation.

Standby databases don't eliminate the need for normal backups on the production database machine. A dropped table or deleted row will also be dropped or deleted on the standby machine.

Standby databases have several advantages:

Fairly easy to implement.
Will work with all datatypes on Oracle7 databases.
Most database changes will be copied automatically to the standby database.
Replication has a negligible impact on the production system.

Standby databases also have several disadvantages:

Standby databases can't be used for load balancing

Because the standby database is in recovery mode and not open, it's not available for use by any users. You can't use a standby database to help with load balancing on the production machine.

Almost never completely up-to-date
Can't be used for load balancing
Only the entire database can be duplicated; no provision for duplicating just a subset of the production database

Standby databases are usually best suited for disaster-recovery database machines.

SEE ALSO
Learn how to create a standby database,

Understanding Replication Strategies

Oracle8 provides two technologies for replicating all or part of a database between two or more instances:

Snapshot replication
Symmetric replication

Snapshot Replication

Limitations in Oracle7

If you're working with some Oracle7 databases, be aware that snapshots can't replicate LONG or LONG RAW datatypes. If a snapshot's base query includes a LONG or LONG RAW column, the result will be NULL values in the target system. All other columns will transfer normally.

Snapshot replication produces a copy of database tables on a target instance based on a query to the source database. At the time a snapshot is initially taken, the specified query (maybe an entire table) is run and the resulting data is loaded into the target snapshot table. Oracle provides a fairly sophisticated facility for updating snapshots based on time or update activity.

Snapshot replication has the following advantages:

Very easy to implement
Excellent for producing a static set of data from OLTP systems to DSS systems
Can be used for some limited load balancing

On the other hand, snapshot replication has the following disadvantages:

Snapshots may become out-of-date immediately.
Excessive refreshing can heavily tax system and Internet-working resources.
Updates can occur only on the master database.

Updating rows in snapshots

It's technically possible to allow updates to a snapshot. However, any changes made won't be sent back to the master database and may be over-written during the next snapshot refresh.

Typical applications can include the following:

Transferring OLTP system data to a DSS or data warehousing system for thorough analysis
Transferring data to a dedicated instance to avoid long running batch jobs from adversely affecting the production system
Disaster recovery
Creating a test database environment from production systems

SEE ALSO
More information on snapshots in relation to setting up a read-only failover database,

Symmetric Replication

Symmetric replication offers a mission-critical and robust means of keeping two or more instances synchronized. Symmetric replication can ensure that a transaction isn't fully committed until all systems being replicated have committed the transaction locally. Alternatively, it can replicate asynchronously, allowing each database node to run at full speed without holding up local updates because of remote database speed issues.

Limitations in Oracle7

If you're working with some Oracle7 databases, be aware that symmetric replication can't replicate LONG or LONG RAW datatypes.

Symmetric replication is one of the most complicated units of Oracle and any other relational database. Issues such as network reliability, resolving conflicting updates, and transaction volumes are major design issues that must be planned for and dealt with.

The advantages to symmetric replication are as follows:

Updates done on any system can automatically be posted on all other replicated and master systems.
Replicated systems can be configured to be kept completely up-to-date.
It's ideal for load-balancing most systems.

Symmetric replication isn't without its disadvantages:

It's more difficult to set up and administer than other replication options.
A high update transaction volume on one machine may stress other systems' resources.
Network outages may bring all update-database activity to a halt.
Potentially very high network resource requirements.

Typical applications may include the following:

High availability and disaster-recovery systems
Using many database instances for load-balancing purposes

Impact on network resources

Symmetric replication will transfer each update transaction from any master database to all other machines that subscribe to database updates. Depending on the volume of updates, this can easily saturate wide area networks. Even high-speed local area networks can become bottlenecks during batch update or load cycles.