Pro Oracle Database 11g Administration (72 page)

hardware (RAID disks, multiple controllers, and so on) to ensure high availability and recoverability.

Step 3: Determine Which Files Need to Be Backed Up

For this step, you only need to know the locations of the datafiles:

SQL> select name from v$datafile;

When you get to step 5, you may want to consider altering tablespaces one at a time into backup mode. If you take that approach, you need to know which datafiles are associated with which tablespace: select

tablespace_name

,file_name

from dba_data_files

order by 1,2;

Step 4: Note the Maximum Sequence Number of the Online-Redo Logs

To recover successfully using a hot backup, you require at minimum all the archive-redo logs that were generated during the backup. For this reason, you need to note the archivelog sequence before starting the hot backup:

431

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

select

thread#

,max(sequence#)

from v$log

group by thread#

order by thread#;

Step 5: Alter the Database/Tablespaces into Backup Mode

You can put all your tablespaces into backup mode at the same time using the ALTER DATABASE BEGIN

BACKUP statement:

SQL> alter database begin backup;

If it’s an active OLTP database, doing this can greatly degrade performance. This is because when a tablespace is in backup mode, Oracle copies a full image of any block (when it’s first modified) to the redo stream. This is discussed in detail later in the chapter.

The alternative is to alter only one tablespace at a time into backup mode. After the tablespace has been altered into backup mode, you can copy the associated datafiles (step 6) and then alter the tablespace out of backup mode (step 7). You have to do this for each tablespace: SQL> alter tablespace begin backup;

Step 6: Copy the Datafiles with an OS Utility

Use an OS utility (Linux/Unix cp command) to copy the datafiles to the backup location. In this example, all the datafiles are in one directory, and they’re all copied to the same backup directory: $ cp /ora01/dbfile/O11R2/*.dbf /oradump/hbackup/O11R2

Step 7: Alter the Database/Tablespaces Out of Backup Mode

After you’re finished copying all your datafiles to the backup directory, you need to alter the tablespaces out of backup mode. This example alters all tablespaces out of backup mode at the same time: SQL> alter database end backup;

If you’re altering your tablespaces into backup mode one at a time, you need to alter each tablespace out of backup mode after its datafiles have been copied:

SQL> alter tablespace end backup;

If you don’t take the tablespaces out of backup mode, you can seriously degrade performance and compromise the ability to recover your database.

432

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Step 8: Archive the Current Online-Redo Log and Note the Maximum Sequence Number of the Online-Redo Logs

The following statement instructs Oracle to archive any unarchived online-redo logs and also to initiate a log switch. This ensures that an end-of-backup marker is written to the archive-redo logs: SQL> alter system archive log current;

Also note the maximum online-redo log sequence number. If a failure occurs immediately after the hot backup, you need any archive-redo logs generated during the hot backup to fully recover your database:

select

thread#

,max(sequence#)

from v$log

group by thread#

order by thread#;

Step 9: Back Up the Control File

For a hot backup, you can’t use an OS copy command to make a backup of control file. Oracle’s hot-backup procedure specifies that you must use the ALTER DATABASE BACKUP CONTROLFILE statement. This example makes a backup of the control file and places it in the same location as the database backup files:

SQL> alter database backup controlfile

to '/oradump/hbackup/O11R2/controlbk.ctl' reuse;

The REUSE clause instructs Oracle to overwrite the file if it already exists in the backup location.

Step 10: Back Up Any Archive-Redo Logs Generated During the Backup

Back up the archive-redo logs that were generated during the hot backup. You can back up the archive-redo logs with an OS copy command:

$ cp This guarantees that you have the logs in the event of a failure that occurs soon after the hot backup finishes. Be sure you don’t back up an archive-redo log that is currently being written to by the archiver process—doing so results in an incomplete copy of that file. Sometimes, DBAs script this process by checking the maximum SEQUENCE# with the maximum RESETLOGS_ID in the V$ARCHIVED_LOG view. Oracle updates that view when it’s finished copying the archive-redo log to disk. Therefore, any archive-redo log file that appears in the V$ARCHIVED_LOG view should be safe to copy.

433

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Scripting Hot Backups

The script in this section covers the minimal tasks associated with a hot backup. For a production environment, a hot backup script can be quite complex. The script listed here provides you with a baseline of what you should include in a hot-backup script.

You need to modify these variables in the script for it to work in your environment:

• hbdir

• dbname

The SQL*Plus variable hbdir points to the base directory for the hot backups. The script also needs the dbname to be set to the Oracle system identifier (SID) of your database. The script copies files to a directory that is the concatenated string of the hbdir and dbname variables. For example, if you set hbdir to /oradump/hbackup and set dbname to O11R2, the backup files are copied to the /oradump/hbackup/O11R2

directory:

#!/bin/bash

# Either hardcode or source the OS variables via a script,

# see chapter 2 for more details on the oraset script.

. /var/opt/oracle/oraset $1

#