'; zhtm += '

' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 13 - Selecting and Implementing a Backup Strategy

Using Oracle8

Chapter 13 Selecting and Implementing a Backup Strategy

Selecting a Backup Strategy
Consider Using Recovery Manager
- Recovery Manager Scripts
Performing an Offline (Cold) Backup
- Taking an Offline (Cold) Backup of a Tablespace
- Making a Full Cold Backup
Performing an Online (Hot) Backup
- What Is a Block Split?
- Hot Backup Script
Creating a Standby Database
- Maintaining a Standby Database
- Activating a Standby Database
Using Replication as a Failover
- Building a Failover Database with Replication

Using Recovery Manager for backup and recovery operations
Scripts that perform offline and online backups
Implementing a hot standby database
Using replication for failover

Selecting a Backup Strategy

Oracle offers very rich and comprehensive backup and recovery options to suit every application's demand. The key is to implement a suitable strategy to meet the desired service levels between end users and the database operations group. A backup strategy suitable for a database depends on several factors, based on the nature of the application and the availability requirements. These factors will also decide whether your database is running in ARCHIVE LOG mode or in NOARCHIVE LOG mode. Table 13.1 lists the available database options that depend on the database's ARCHIVELOG mode.

The backup option in NOARCHIVELOG mode

Full cold backup is the only backup option available when the database is in NOARCHIVELOG mode.

Table 13.1 Database mode and the available backup options

Backup Type:	ARCHIVELOG	NOARCHIVELOG
Full cold backup	Yes	Yes
Partial cold backup	Yes	No
Hot backup	Yes	No

Recovery Manager and OS-level backup rely on making a copy of the users' data on a backup media-most commonly tape and disk. These methods preserve the data in a safe, inactive form. The data stored by these methods isn't used unless there's a loss of the current data for some reason. You can also copy the data stored in an Oracle database by using any of the following methods:

Recovery Manager

Recovery Manager is Oracle's new backup and recovery tool. Chapter 15, "Using Recovery Manager for Backup and Recovery," gives the details.

Export. Export enables you to make a logical backup of the data. You can export the full database, specific users, or specific tables. It provides a copy of the data stored in Oracle's proprietary format independent of the OS. Incremental backups are supported but aren't incremental in a strict sense, as any table is fully exported even if only one row has been modified. Export is used generally to transfer data from one database to another.
Consider using Export for preserving contents of small lookup tables. However, the use of Export as a backup method is very limited in large databases due to its slower performance.
Hot standby database. A hot standby database is normally used as part of the disaster recovery plan in mission-critical environments. A hot standby database needs machine resources equivalent to the primary database. Data can be made available to users at very short notice in a hot standby database, even if no other resource from the primary database is available.
A hot standby database is used only when the cost of downtime for the database is very high and the downtime cost justifies the cost to devote redundant machine resources required to build the standby database.
Replication. With this technique, you can maintain object-level replicas of user data. The data is asynchronously available to all users at all locations after a predefined replication interval. Use this method when the business requires that the same data be available from multiple databases. It uses a trigger-based technique to propagate changes among the database and is a resource-intensive process. "Using Replication as a Failover," later in this chapter, provides more detail on this subject.

In summary, all these methods offer unique advantages and need to be used depending on your business requirements. However, they can also be used to complement each other.

Consider Using Recovery Manager

Oracle8's Recovery Manager is an integrated backup and recovery tool. Consider the following salient features before deciding your backup strategy:

Media management libraries

The Media Management library contains the vendor-supplied software programs that supply the backup Media Manager. If you don't have any backup media installed on the machine, disks can be used to take back-ups.

Recovery Manager is integrated with Oracle. It performs backup operations with spawned Oracle processes. These processes use Media Management libraries, which interface with backup media. You don't have to specify an OS utility such as tar, Backup, dd, and so on to copy files.
Recovery Manager greatly simplifies administrative tasks associated with backup and recovery. Several tasks-defining backup configurations, keeping a log of backup and recovery operations, automatically parallelizing the backup and restore with the resources available-can be easily automated with Recovery Manager.
Recovery Manager detects any Oracle block splits and rereads these blocks to get a consistent view. Therefore, it isn't necessary to keep the tablespace in backup mode while performing a backup. However, you may perform a consistent cold backup of the database by using Recovery Manager. (See "What Is a Block Split?" later in this chapter for more information on block splits.)
Recovery Manager doesn't back up Oracle blocks that have never been used, thus saving considerable backup time and space.
Recovery Manager stores backed-up data in an Oracle internal format that can't be read by any other utility. Therefore, files backed up by Recovery Manager can be restored and recovered only with Recovery Manager.

If you aren't using Recovery Manager to perform backup and recovery, you can use OS-level physical backups for making copies of the data to protect against loss. This has so far been the most widely used method for backup operations. There are important considerations to make while implementing a backup strategy:

You must run the database in ARCHIVE LOG mode in an online transaction system, where each and every transaction is important and it's necessary to recover up to the last committed transaction.
When the database is running in ARCHIVELOG mode, you can perform a full or partial cold or hot backup.
A full cold backup is the simplest backup to implement and should be the preferred method, unless availability requirements don't allow enough downtime to perform a full cold backup. A full cold backup can also be integrated with other non-Oracle files at the OS level.
Recovery Manager is a very flexible and powerful backup and recovery tool. Consider using the Recovery Manager for backup and recovery operations. Refer to Chapter 15 for more details on using the Recovery Manager.
Recovery Manager enables you to take true incremental backup of the database. You can back up only the modified blocks since the last backup was performed at the same or lower level.

Recovery Manager Scripts

Recovery Manager is an easy-to-use tool available in command-line interface (CLI) and graphical user interface (GUI) mode. (The GUI mode of the Recovery Manager is available through Enterprise Manager on the Windows NT platform.) Oracle also provides comprehensive scripts to start with. The scripts case1.rcv and case4.rcv provide comprehensive information about using the Recovery Manager to perform backup. These scripts are located in the $ORACLE_HOME/rdbms/demo directory on a UNIX system and the \ORANT\rdbms\demo folder on a Windows NT system.

case1.rcv

This script contains code for the following backup, restore, and recovery operations using Recovery Manager:

Print the script case1.rcv while reading this part

case1.rcv is a long script that shows how to write Recovery Manager scripts. Keep a printout of it handy while reading this section.

Archive the current log
Specify maximum corruption tolerance
Mount the database
Back up archived logs, back up the control file, and back up the control file with the copy command
Back up system tablespace
Full, whole database backup
Incremental level 0, whole database backup
Incremental level 1, whole database backup
Incremental level 2, whole database backup
Back up read-only tablespace
Script to verify backup set isn't corrupt
Restore and recovery
Restore control file
Restore all data files
Recover all data files
Restore and recover data files and control file
Restore and recover data files
Restore and recover a single tablespace (database open)

Keep the following in mind when you perform these functions:

Use the setlimit, filesperset clause to enforce the following restrictions:

No single backup piece is greater than 2GB.

Puts a maximum of 20 archived logs in any one backup set.

No more than 200 buffers are allowed to be read per file per second, which limits the effect the backup file scan has on online users and batch jobs.

A channel can have a maximum of only 32 files open at any one time.

Includes, at the most, six files in one backup set.

Continue even if any of the data files is inaccessible, leaving unavailable data files.
Skip data files that belong to read-only tablespaces and are offline.
Back up the tablespace that has just been made read-only; it should be backed up once before being omitted from future backups by the skip readonly clause.

Making backups on disk with Recovery Manager

To make backup sets to disk with Recovery Manager, change the channel allocation commands to be type disk and modify the format clause to include the full path name the backup is to be written to. Otherwise, the backup files will typically be written to the dbs directory in Oracle home.

case4.rcv

This script has the code for taking a consistent backup of a database using the Recovery Manager. It performs the backup operations with the following considerations:

Backs up database by using up to two tape drives.
The Media Manager has a maximum supported file size limit of 2GB, so any backup piece should be no larger than that size.
Each backup set should include a maximum of five files.
Includes offline data files and read-only tablespaces in the backup.
Terminates the backup if any files aren't accessible.
Opens the database after the backup completes.

Performing an Offline (Cold) Backup

Cold backup or consistent backup of a file is the backup taken when the file is offline (not being accessed by the database instance, for instance). Thus, a file's cold backup is its image copy as it existed when the file was taken offline. You can take a partial or full cold backup off the database. The following factors determine whether a full or a partial cold backup is taken:

Enabling ARCHIVELOG mode

By default, the database is created in the NOARCHIVELOG mode. Refer to Chapter 12, "Understanding Oracle8 Backup Options," for steps required to alter the database to ARCHIVELOG mode.

A partial cold backup can't be taken if the database is in NOARCHIVE LOG mode; you can't take the files offline for cold backup.
Size of the database, speed of the backup media, and the time allowed for the database to be unavailable. If the database is in ARCHIVE LOG mode, you can offline tablespaces to back them up while the rest of the database is available to the users.

Taking an Offline (Cold) Backup of a Tablespace

To make a cold backup of a tablespace (other than system tablespace data files), take the corresponding tablespace offline by issuing the following command from Server Manager:

You can't take the SYSTEM tablespace offline

To make an offline tablespace backup, the database needs to be shut down. You can't perform a cold backup of the SYSTEM tablespace while the database is running because Oracle requires that it always be available.

SQL> alter tablespace tablespace name offline ;

Use any of the OS utilities tar, dd, and cpio on UNIX and Backup on Windows NT to copy the corresponding data files. When the backup is completed, use the following command to put the tablespace back online:

SQL> alter tablespace tablespace_name online ;

Listing 13.1 lists a sample script that performs an offline backup of a tablespace on a UNIX system. This script does the following:

Download this code

You can download the TS_BACK-UP_ OFF.SH script from the Web at http://www.mcp.com/ info. You'll be asked to enter an ISBN; enter 0789716534 and then click the Search button to go to the Book Info page for Using Oracle8.

Lists the data files belonging to the tablespace (lines 9-23)
Takes the tablespace offline (lines 28-30)
Backs up the files with the UNIX tar command (lines 38-49)
Puts the tablespace back online (lines 51-56)

Listing 13.1 TS_BACKUP_OFF.SH-Performing an offline backup of a tablespace

     01:     # Script to perform offline backup of a tablespace
     02:     # on a UNIX system
     03:     #
     04:     #  Takes two input arguments
     05:     #            1. Name of the tar file
     06:     #            2. Name of the tablespace to be backed up.
     07:     #
     08:     #
     09:     # Listing database files belonging to the tablespace
     10:     #
     11:     #
     12:     sqlplus -s sys/<password> > tsfile_name.dat <<!
     13:     set heading off;
     14:     set pagesize 0
     15:     set linesize 2048
     16:     set feedback off;
     17:     REM
     18:     REM  Listing datafiles
     19:     REM
     20:     select file_name
     21:     from DBA_DATA_FILES
     22:     where tablespace_name='$2';
     23:     exit;
     24:     !
     25:     #
     26:     # Offline the tablespace to be backed up
     27:     #
     28:     sqlplus -s sys/change_on_install <<!
     29:     alter tablespace $2 offline ;
     30:     exit;
     31:     !
     32:     #
     33:     #
     34:     # Creating the tar file and
     35:     # backing up the datafiles
     36:     #
     37:     #
     38:     First_file='Yes'
     39:     for i in `cat tsfile_name.dat`
     40:     do
     41:             echo "----Backing up the datafile $i"
     42:        if [ $First_file = Yes ]
     43:        then
     44:         tar -cvf  $1 $i
     45:         First_file='No'
     46:        else
     47:         tar -rvf $1 $i
     48:        fi
     49:     done
     50:     #
     51:     sqlplus -s sys/change_on_install <<!
     52:     REM
     53:     REM Bringing the tablespace back online
     54:     REM
     55:     REM
     56:     alter tablespace $2 online ;
     57:     !
     58:     # end of the script tablespace backup script

Contents of offline tablespaces aren't available to users

When a tablespace is made offline, user objects residing in it aren't available for use until it's put back online. Applications trying to use these objects will signal errors while the tablespace remains offline.

Taking a tablespace offline

You can't take offline a table-space that contains an active rollback segment. First take the rollback segment offline, then the tablespace.

Making a Full Cold Backup

To perform a full offline database backup, the database must be shut down. Listing 13.2 shows a script that performs a cold backup of a database on a UNIX system. This script performs the following sequence of operations:

Generates a list of all the database's data files, control files, and online redo log files (lines 36-53)
Shuts down the database (lines 59-62)
Backs up the default parameter file (line 68)
Backs up the files with the UNIX tar command (lines 72-76)
Starts the database after the backup (lines 81-84)

Listing 13.2 COLD_BACKUP.SH-Performing a full cold backup of a database

     01:     # Script to perform cold backup of a database
     02:     # on a UNIX system
     03:     #
     04:     # Uses environment variable ORACLE_SID to decide
     05:     #   which database to backup
     06:     # The database should be up and running.
     07:     #
     08:     # Connects to the database using sys account,
     09:     # to list name of all the
     10:     # Control files,
     11:     # online redo log files and
     12:     # datafiles.
     13:     #
     14:     # It also backs up the default INIT.ORA
     15:     #  file from $ORACLE_HOME/dbs directory
     16:     #
     17:     # The script does not backup archived redo logs.
     18:     # If you are running in the ARCHIVELOG mode
     19:     # then please be sure to back them up.
     20:     #
     21:     # Modify this script to shutdown all the instances
     22:     # accessing the database in an Oracle Parallel Server
     23:     # environment. It creates the tar file with the
     24:     # following naming convent|ion
     25:     #       bkp<ORACLE_SID><mmddhh>.tar (month, data, hour)
     26:     # Creates tar file in the directory specified
     27:     # by BACKUP_DIR as defined in the
     28:     # following line
     29:     ##
     30:     BACKUP_DIR=/home/oracle/backup ; export BACKUP_DIR
     31:     BKPTIME=`date '+%m%d%H'`
     32:     BKPFILE=$BACKUP_DIR/bkp$ORACLE_SID$BKPTIME.tar
     33:     #
     34:     # Creating database file list in the file file_name.dat
     35:     #
     36:     sqlplus -s sys/change_on_install > file_name.dat <<!
     37:     set heading off;
     38:     set pagesize 0
     39:     set linesize 2000
     40:     set feedback off;
     41:     REM
     42:     REM  Listing datafiles
     43:     REM
     44:     select name from V\$DATAFILE ;
     45:     REM
     46:     REM  Listing control files
     47:     REM
     48:     select name from V\$CONTROLFILE;
     49:     REM
     50:     REM  Listing online redo log files
     51:     REM
     52:     select member from V\$LOGFILE;
     53:     exit;
     54:     !
     55:     #
     56:     # Shutting down the database
     57:     # Using Shutdown normal.
     58:     #
     59:     svrmgrl <<!
     60:     connect internal ;
     61:     shutdown;
     62:     exit;
     63:     !
     64:     #
     65:     #
     66:     # Backing up the initialization parameter file
     67:     #
     68:     tar cvf $BKPFILE $ORACLE_HOME/dbs/init$ORACLE_SID.ora
     69:     #Backing up the files using the tar command
     70:     # to the file BKPFILE as defined above
     71:     #
     72:     for i in `cat file_name.dat`
     73:     do
     74:     echo "Backing up the datafile $i"
     75:         tar -rvf $BKPFILE $i
     76:     done
     77:     #
     78:     # Starting the database
     79:     # after backup
     80:     #
     81:     svrmgrl <<!
     82:     connect internal ;
     83:     startup;
     84:     exit;
     85:     !
     86:     #
     87:     exit ;
     88:     # end of the script cold_backup.sh

Shut down all instances in an OPS environment

In order to perform a cold backup of a database in an Oracle Parallel Server environment, you must shut down all instances accessing the database before copying the data files at the OS level.

Shutdown immediate disconnects all active users

Shutdown immediate disconnects users and rolls back all current uncommitted transactions. The data in uncommitted transactions is lost. If you don't want to forcefully disconnect the users for performing backup, use shutdown normal.

Pay attention to the following points when performing a cold backup:

Use Shutdown immediate or Shutdown normal. Before copying the files, ensure that the database has been shut down properly by looking at the messages in the alertsid.log file.

Don't use Shutdown abort before a cold backup

If you shut down the database with the ABORT option, you should restart the database in RESTRICT mode and use Shutdown normal before copying the database files.

The sample script shown in Listing 13.2 backs up online redo log files. Be careful while restoring them, however; if you plan to perform recovery by using the archived redo logs after the restore, don't restore the online redo logs from the backup. If you do so, they will overwrite the current online redo logs on the system and you won't be able to perform a complete recovery.
Always back up archived redo logs at regular intervals.
Use an automated method to get a list of the files that are part of the database similar to the one used in the sample script cold_backup.sh in Listing 13.2. This will minimize the administrative work and human errors. If you decide to list the files manually for backup purposes, remember to modify the script after you add and delete data files, control files, and online redo log files to and from the database.
An automated script that picks up the names of the data files from the database doesn't depend on the underlying database file architecture (such as OFA, Optimal Flexible Architecture) to discover the name of the files to back up. Thus, it doesn't restrict you to placing the database files in a predefined manner.
If the database contains READ ONLY tablespaces, it's not necessary to back them up each time during a cold backup. A one-time backup of these tablespaces is necessary, however, after making them READ ONLY.
You don't need to back up tablespaces that don't contain any permanent objects. Thus, you may not back up tablespaces that contain only rollback segments and are used as temporary tablespace. I recommend, however, that you include them in the cold backup for ease of operation during the recovery. If you have to perform recovery after a full restore and haven't backed up the temporary tablespace and the tablespace containing rollback segments, you might have to perform extra steps before completing the recovery.
Always look at the log generated during execution of the backup script to ensure that the backup process completed properly. The sample script in Listing 13.2 is provided for a quick start; it doesn't include a comprehensive error-checking mechanism. You might want to enhance it for checking the status of the database prescript, the status of the backup device, the space available at the backup destination, the errors during backup, and so on.
The initialization parameter file is an ASCII file and can be created with any editor. It isn't necessary to back this up with every cold backup. I recommend backing it up, however, as it involves resources of little significance and can save valuable time in case you lose it.

Performing an Online (Hot) Backup

A hot or online backup refers to the backup of a database's data files taken while the database is still open. Use online backup when business requirements don't permit the database to be unavailable for an interval long enough to perform the offline data files backup. To perform online backup of a data file, you need to put the corresponding tablespace in backup mode by using the following command:

SQL>alter tablespace tablespace_name begin backup;

Copy the file at the OS level after issuing this command. When the copy operation is completed, the tablespace should be returned to normal mode with the following command:

SQL>alter tablespace tablespace_name end backup;

Oracle copies entire block images of the modified data blocks on the first change to the blocks; this is done while the tablespace is in the backup mode. This increases the size of the redo log generated while the tablespace is in the backup mode. Follow these guidelines to minimize its impact on the performance:

Perform the online backup of a tablespace while its DML activity is minimal.
Don't keep the tablespace in backup mode longer than what's required to copy the appropriate data files. Start copying the files immediately after issuing the begin backup command, and issue the corresponding end backup command as soon as copying is done.

View V$BACKUP

Use the dynamic performance view V$BACKUP to determine the back-up status of the data files at any time. The ACTIVE value in this column indicates that the file is marked as now being backed up.

Ensure that a tablespace isn't left in backup mode for a very long period after a failure in backup process during the middle of the backup.

What Is a Block Split?

An Oracle data block consists of multiple OS disk blocks. The Oracle DBWR process writes modified blocks to the disk through OS write calls, which write to the disk in chunks that are multiples of OS blocks. The same Oracle block may be read and written to the storage media at the same time. This simultaneous read/write operation causes what's known as an "Oracle block split."

Figure 13.1 shows three Oracle blocks. Each Oracle block consists of four OS blocks-a total of 12 OS blocks. Assume that time t1, t2, and t3 are in increasing order, and consider the following situation:

Figure 13.1 : Oracle blocks splits/fractures as DBWR writes the block while it's being copied at the OS level.

The backup process writes the first six OS blocks to the backup file at time t1.
The DBWR process writes Oracle Block 2 at time t2.
The backup process writes the next six OS blocks to the backup file at time t3.

Clearly, the backup file contains the first half of Oracle Block 2 before the update, and the second half after the update. Thus, an inconsistent copy of the Oracle block is present in the backup file. To handle this problem, Oracle writes the complete image of the modified blocks in its redo log while the tablespace is in backup mode. During the recovery process before applying the changes to a block, Oracle copies the block image from its redo log to the disk-making the block consistent-and applies the remaining redo for the block.

Hot Backup Script

You can use the HOT_BACKUP.SH script shown in Listing 13.3 to perform a hot backup of a database on a UNIX system. This script backs up all data files in online mode. It also appends a backup copy of the control file and all archived redo logs present in the archive destination directory. Tailor it to suit your environment. This script calls another script, bkp_ts.sh (shown in Listing 13.4), which issues the necessary begin and end backup commands and copies all the data files belonging to a tablespace.

Listing 13.3 HOT_BACKUP.SH-Performing a hot backup of a database

     01:     # Script to perform hot backup of a database
     02:     # on a UNIX system.
     03:     #
     04:     # Uses environment variable ORACLE_SID to decide
     05:     #   which database to backup
     06:     # The database should be up and running.
     07:     #
     08:     # Connects to the database using sys account,
     09:     # to list name of all the
     10:     # tablespace and the corresponding datafiles.
     11:     #
     12:     # It also backs up the default INIT.ORA
     13:     #  file from $ORACLE_HOME/dbs directory
     14:     #
     15:     # The script  performs a log switch at the end and
     16:     # performs backup of all the archive logs present in
     17:     # the directory. It creates the tar file with the
     18:     # following naming convention
     19:     #       bkp<ORACLE_SID><mmddhh>.tar (month, data, hour)
     20:     # Creates tar file in the directory specified
     21:     # by BACKUP_DIR as defined in the
     22:     # following line
     23:     #
     24:     BACKUP_DIR=/home/oracle/backup ; export BACKUP_DIR
     25:     BKPTIME=`date '+%m%d%H'`
     26:     BKPFILE=$BACKUP_DIR/bkp$ORACLE_SID$BKPTIME.tar
     27:     #
     28:     # Listing the tablspaces to backup
     29:     #
     30:     sqlplus -s sys/<password> > tablespace_name.dat |<<!
     31:     set heading off;
     32:     set pagesize 0
     33:     set linesize 2048
     34:     set feedback off;
     35:     REM
     36:     REM  Listing all the tablespaces
     37:     REM
     38:     select tablespace_name from DBA_TABLESPACES
           where tablespace_name='SYSTEM';
     39:     exit;
     40:     !
     41:     #
     42:     #
     43:     #
     44:     # Backing up the initialization parameter file
     45:     #
     46:     tar cvf $BKPFILE $ORACLE_HOME/dbs/init$ORACLE_SID.ora
     46:     #Backing up the files using the tar command
     47:     # to the file BKPFILE as defined above
     48:     #
     49:     for ts in `cat tablespace_name.dat`
     50:     do
     51:             echo "Beginning backup of the tablespace $ts"
     52:             bkp_ts.sh  $BKPFILE $ts
     53:     done
     54:     #
     55:     # Backing up the control file to a backup file
     56:     #
     57:     sqlplus -s sys/change_on_install <<!
     58:     set pagesize 0
     59:     set linesize 2048
     60:     set feedback off;
     61:     REM
     62:     REM Backing up control file
     63:     REM
     64:     alter database backup controlfile to '$PWD/control.bkp';
     65:     exit;
     66:     !
     67:     #
     68:     #Appending the control file to tar file
     69:     #
     70:     tar -rvf $BKPFILE control.bkp
     71:     rm -f control.bkp
     72:     #
     73:     # Performing a log switch to archiving all the
     74:     # logs
     75:     sqlplus -s sys/change_on_install <<!
     76:     set linesize 2048
     77:     set feedback off;
     78:     alter system switch logfile;
     79:     alter system archive log all;
     80:     exit;
     81:     !
     82:     #
     83:     # Backing up the archive redo logs.
     84:     # This script assumes that the archive log destination
     85:     # is set to its default value of $ORACLE_HOME/dbs
     86:     # This might have to be changed to suit
     87:     # your environment
     88:     #
     89:     FILES=`ls $ORACLE_HOME/dbs/arch*`; export FILES
     90:     tar -rvf $BKPFILE $FILES
     91:     rm -f $FILES
     92:     #
     93:     # end of the script hot_backup.sh

Log switch after a hot backup

Always back up the redo logs generated during a hot backup by forcing a log switch and then copying the generated archived redo logs.

Listing 13.4 BKP_TS.SH-Performing a hot backup of a tablespace

     01:     # Script to perform hot backup of a tablespace
     02:     #
     03:     #
     04:     #  Takes two input arguments
     05:     #            1. Name of the tar file
     06:     #            2. Name of the tablespace to be backed up.
     07:     #
     08:     #
     09:     # Listing database files belonging to the tablespace
     10:     #
     11:     #
     12:     sqlplus -s sys/<password> > tsfile_name.dat <<!
     13:     set heading off;
     14:     set pagesize 0
     15:     set linesize 2048
     16:     set feedback off;
     17:     REM
     18:     REM  Listing datafiles
     19:     REM
     20:     select file_name
     21:     from DBA_DATA_FILES
     22:     where tablespace_name='$2';
     23:     exit;
     24:     !
     25:     #
     26:     # Putting the tablespace in hot backup mode
     27:     #
     28:     sqlplus -s sys/change_on_install <<!
     29:     alter tablespace $2 begin backup ;
     30:     exit;
     31:     !
     32:     #
     33:     #
     34:     # Appending the files to the file
     35:     # BKPFILE as defined above
     36:     #
     37:     for i in `cat tsfile_name.dat`
     38:     do
     39:             echo "----Backing up the datafile $i"
     40:         tar rvf $1 $i
     41:     done
     42:     #
     43:     sqlplus -s sys/change_on_install <<!
     44:     set heading off;
     45:     set pagesize 0
     46:     set linesize 2048
     47:     set feedback off;
     48:     REM
     49:     REM Changing the tablespace from hot backup mode
     50:     REM to normal mode
     51:     REM
     52:     alter tablespace $2 end backup ;
     53:     !
     54:     # end of the script tablespace backup script

Creating a Standby Database

If a database is used for mission-critical applications, you want to avoid downtime at any cost. Modern hardware and software techniques enable you to increase the availability of systems approaching 100 percent. If for any reason the primary production database can't be made available to the end users, however, maintaining a standby may fulfill a business's availability requirements.

The standby database can't be queried

A standby database is constantly in recovery mode and not available for querying the data unless you activate it. Once a standby database is activated, it's no longer available as a standby unless you recreate it as another standby database.

A standby database is a copy of the primary database that can be brought online with minimum delay when needed. This feature is part of Oracle's disaster recovery procedures. With this feature, you can maintain a hot standby copy of the production database, preferably at another remote site, which can be activated on short notice. When it's activated, this database will serve as the production database. You might need to start from the beginning to create a standby database for this database.

Create a standby database

Perform an online or offline backup of the production database using the proper procedures. (If your system is mission-critical, however, you would most likely perform an online backup.) It's good practice to perform an online backup while setting up the standby database for the first time; this will give invaluable experience in recreating the standby database whenever the standby database is put into production use.
Create a control file for the standby database by using the following command:

SQL>alter database create standby
controlfile as control_file_name;

The following is a sample command session executing this command. Notice that the filename is included in quotation marks. Because the full path name isn't given for the file, the standby control file is created in the default location-the $ORACLE_HOME/dbs directory.

SVRMGR> alter database create standby
     2> controlfile as 'control.std';
Statement processed.

The standby control file

Standby control file contents are different from those in the original control file. You should not use a backup copy of the original control file generated by the backup control file command instead of this file.

Archive the production database's current online log files with this command:

SQL> alter system archive log current ;

This command forces a log switch and then archives the current redo log file group. The database must be open to issue this command. This command may not be required if you've performed an offline backup of the database in Step 1, as data files are checkpointed (synchronized) before an offline backup. If the database is open, issuing this command is important because it ensures consistency among the data files in Step 1 and the control file in Step 2.

Transfer all files generated in Steps 1 through 3 to the system where you want to build the standby database.

The standby should be built on a similar system

The standby database must be built on the same hardware platform as that of the primary database. It's recommended there be similar architecture and software on both machines.

Create the initialization parameter file for the standby database. It's highly desirable and also recommended to have the standby database's parameters similar to the primary database parameters, because the standby database will be used as the primary database after the failover. Keeping most parameters the same will help you avoid any surprises during its operation.

Table 13.2 lists important initialization parameters related to the standby database configuration.

Table 13.2 Parameters related to standby database

Parameter Name:	Description:
`COMPATIBLE`	This parameter must be the same on the primary and standby databases.
`DB_FILES`	This parameter and the `MAXDATAFILES` clause of the `CREATE DATABASE` or `CREATE CONTROLFILE` commands specifies the maximum number of data files for the database. Keep it the same, as the number of data files allowed/needed will be identical at both places.
`CONTROL_FILES`	This parameter specifies the name of the control files used for the database. Both databases should point to different files. Their names can be the same, as they're located on different machines.
`DB_FILE_NAME_CONVERT`	This parameter is set only on the standby database. Use this parameter only if the directory paths to the data files are different at both sites.
`LOG_FILE_NAME_CONVERT`	This parameter is the same as `DB_FILE_NAME_ CONVERT`, except that it applies to the online redo log members.

Maintaining a Standby Database

After transferring all data from the primary database to the host machine for the standby database, you need to keep the standby database closely synchronized with the primary database.

Keep the standby synchronized with the primary database

Start the standby database instance in the nomount state with the following command:

svrmgrl> startup nomount ;

Automate the archive log transfer to the standby database

Archived redo logs generated at the primary site need to be applied to the hot standby on a regular basis. In a production environment, it's desirable to automate the process of transferring and applying the archived redo log to the standby database. Use the operating system's or Oracle's job-scheduling features to implement this.

Mount the standby database in the standby exclusive mode by using the following command:

svrmgrl> alter database mount standby database;

Prepare the standby database for recovery to apply the archived redo logs received from the primary database:

svrmgrl> recover [from location] standby database until cancel;

Oracle will prompt the next log to be applied. Apply all the logs that have been generated so far and then cancel the recovery. If the archived redo logs to be applied are being made available in the directory specified by LOG_ARCHIVE_DEST, the FROM location clause doesn't need to be specified. Because it's a recurring process, you might want to automate it by using Oracle job queues or operating system job-scheduling features.

Using the Unrecoverable Option on the Primary Database

Unrecoverable data loads and the no-logging feature associated with tables and indexes that don't generate redo logs aren't propagated to the standby database. If you activate the standby database without paying attention to them, the affected target objects will be marked as corrupted. You can drop the affected tablespace at the standby database after the activation, or recreate it by using a fresh backup of the concerned tablespace at the primary site and transferring it over to the standby database before activation.

Adding and Deleting Data Files

If you add a data file at the primary database, you should make a copy of the added data file and add it to the standby database before applying the redo log that was generated while the file was added. This adds the new file to the standby control file and will allow the standby database's recovery to proceed as normal. A dropped data file at the primary site is also removed from the standby database control file when the corresponding redo log is applied.

Adding, Removing, and Clearing Redo Log Files

The standby database's online redo log files

Online redo log files for a standby database are initialized when the standby database is activated. You might consider using the alter database clear logfile command to initialize the online redo logs in advance; this saves time while activating the standby database.

Adding and removing online redo logs doesn't have any impact on the configuration of the standby database's redo log con-figuration.

Performing an incomplete recovery at the primary database and opening it with the resetlogs option will invalidate the standby database, as the resetlogs option initializes all the online redo logs and resets the log sequence number at the primary site.

Activating a Standby Database

The standby database is mounted and is in recovery mode. Issue the following command to activate it and make it available to the users:

svrmgr> alter database activate standby database;

Standby databases become normal databases after activation

As soon as you activate the standby database, it no longer remains a standby database. It becomes a normal database totally independent of its primary database. If needed, use it to recreate another standby data-base.

Be careful to apply all the archived and unarchived redo logs from the primary database before activating the standby database. Once you activate the standby database, all the transactions from the primary database that are present in the unapplied redo logs are lost.

Using Replication as a Failover

Oracle offers the following solutions for implementing highly available systems:

Oracle Parallel Server
Hot standby database (discussed earlier in this chapter)
Oracle Failsafe option
Replication

For information on the Oracle Failsafe option, available on Windows NT cluster systems, refer to the Oracle Fail Safe Concepts and Administration Guide from Oracle Corporation.

SEE ALSO
An overview of Oracle Parallel Server options,

Oracle's Replication option enables you to maintain the same data in multiple databases. By using replication, you can design applications that are available to end users all the time. If one database fails for any reason-including the unavailability of its host site due to natural catastrophe-the sites having the replicated database can be used by the application. If you're maintaining data at more than two locations, the application can access the data as long as one replicated site is functioning. The nice thing about this scheme is that it can be completely transparent to end users. If the database instance they were working on goes down, the application can connect the users to another replicated site immediately without the users knowing that. Look at this option's important features with respect to a fail-over strategy:

The Replication option lets you choose the objects to be replicated at the table level. Thus, if only part of the data is critical for the business application, only that data can be replicated.
There are no restrictions on the hardware and software architecture of the machines hosting the database's participating in a replicated environment. One database could be on a UNIX machine, another could be on a Windows NT box, and so on. You can replicate databases among platforms as long as the Oracle versions residing on them are compatible with each other.
The systems having replicated databases need not be clustered or part of an MPP (massively parallel platform) system as required while implementing the Oracle Parallel Server or Failsafe option. Common network communication is used for data transfers among the machines having a replicated database.
Due to the hardware requirements, the machines used for OPS and Failsafe are located in physical proximity; therefore, these two options don't provide protection against site failure. The Oracle Replication option and the hot standby database can have the remote database at different geographical locations and provide protection against site failure.
The Replication option involves significant overhead in terms of network and other system resources; as a result, it's generally not suitable for a very high volume transaction system.
Data is propagated to other sites asynchronously. This implies that when a database participating in an replicated environment fails, some transactions committed in it may not be propagated at the other site until the failed database comes back up. Replication shouldn't be used for failover if the application can't tolerate this type of inconsistency.

SEE ALSO
Information on advanced replication options,

Building a Failover Database with Replication

To use replication as a means providing increased availability, you can choose from the following options:

The read-only snapshots should be used if the backup site is to be used to provide read-only access to the data. In this situation, DML operations can't be performed until the primary site comes back up.
Provide full access to the data by using multimaster replication. The data is available for read as well as for DML access from all the sites all the time.

Setting Up Read-Only Failover Database with Snapshots

Set up snapshots to provide read-only access to data with the master site

To set up snapshot replication, you need to decide which tables need to be replicated. If the application doesn't require all the tables at the failover site, you can save resources by not configuring snapshots for tables that aren't needed.

Refer to Oracle8's Server Replication Manual for complete details

Detailed instructions on setting up snapshots and replication is beyond the scope of this book. An overview of the tasks to be performed is provided; please refer to the Oracle8 Server Replication manual for more details and command syntax.

Create the snapshot schema at the snapshot site and then establish database links to the master site.
Create a snapshot log at the master site to allow fast refresh of the snapshots. If you don't create a snapshot log, every refresh will be a complete refresh (which may not be acceptable in most cases).
Create a snapshot at the snapshot site. During this process, all the data from the master site is propagated to the snapshot site.
Create snapshot groups and assign snapshots to them. This is the process where you define the snapshot update interval.

Setting Up Failover Database with Multimaster Replication

The steps required to set up multimaster replication are very similar to setting up snapshots.

Set up multimaster replication

Set up the replication environment at the master definition site. This site includes creating the replication owner and granting necessary privileges to it, defining a database link to be used for replication, and scheduling jobs to replicate changes from one site to another at regular intervals.
Decide which tables need to be replicated and who owns them at the master definition site. Create the schema and the tables to be replicated at the master definition site.
Repeat the functions from Step 1 on the remote (master) site.
Repeat the functions from Step 2 on the remote (master) site.
Create a master replication group at the master definition site.
Add replication objects to the replication group and generate the replication support for these objects.
Start the replication process by enabling changes in the replication group from QUIESCED mode to NORMAL mode.

After the replication is set up, Oracle maintains the data at all participating sites. No additional steps are required to maintain the replicated data, except to resolve error conditions that might result from what's called the CONFLICTS. Refer to the Oracle8 Server Replication Manual for more details.

No single backup piece is greater than 2GB.	Puts a maximum of 20 archived logs in any one backup set.
No more than 200 buffers are allowed to be read per file per second, which limits the effect the backup file scan has on online users and batch jobs.	A channel can have a maximum of only 32 files open at any one time. Includes, at the most, six files in one backup set.