Pro Oracle Database 11g Administration (69 page)

411

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

discusses hot backups. You also investigate several common restore and recover scenarios. These examples build your base knowledge of Oracle B&R internals. Finally, this chapter covers Oracle’s flashback technology and how that complements many user-managed recovery scenarios.

Implementing a Cold-Backup Strategy for a Noarchivelog-

Mode Database

You perform a user-managed cold backup by copying files after the database has been shut down. This type of backup is also known as an offline backup. Your database can be in either noarchivelog mode or archivelog mode when you make a cold backup.

For some reason, DBAs tend to think of a cold backup as being synonymous with a backup of a database in noarchivelog mode. That isn’t correct. You can make a cold backup of a database in archivelog mode, and that’s a backup strategy that many shops employ. The differences between a cold backup with the database in noarchivelog and in archivelog mode are detailed in the following sections.

Making a Cold Backup of a Noarchivelog-Mode Database

One main reason to make a cold backup of a database in noarchivelog mode is to give you a way to restore a database back to a point in time in the past. You should use this type of backup only if you don’t need to recover transactions that occurred after the backup. This type of B&R strategy is acceptable only if your business requirements allow for the loss of data and downtime. Rarely would you ever implement this type of B&R solution for a production business database.

Having said that, there are some good reasons to implement this type of backup. One common use is to make a cold backup of a development/test/training database and periodically reset the database back to the baseline. This gives you a way to restart a performance test or a training session with the same point-in-time snapshot of the database.

■
Tip
Consider using the Flashback Database feature to set your database back to a point in time in the past.

Flashback Database is discussed later in this chapter.

The example in this section shows you how to make a backup of every critical file in your database: all control files, datafiles, temporary datafiles, and online-redo log files. With this type of backup, you can easily restore your database back to the point in time when the backup was made. The main advantages of this approach are that it’s conceptually simple and easy to implement. Here are the steps required for a cold backup of a database in noarchivelog mode:

1. Determine where to copy the backup files and how much space is required.

2. Determine the locations and names of the database files to copy.

3. Shut down the database with the IMMEDIATE, TRANSACTIONAL, or NORMAL clause.

4. Copy the files (identified in step 2) to the backup location (determined in step 1).

5. Restart your database.

414

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

The following sections elaborate on these steps.

Step 1: Determine Where to Copy the Backup Files and How Much Space Is Required

Ideally, the backup location should be on a separate set of disks from your live datafiles location.

However, in many shops you may not have a choice and are told which mount points are to be used by the database. Often it’s not up to you architect the underlying subdisk system.

For this example, the backup location is the directory /oradump/cbackup/O11R2. To get a rough idea of how much space you need to store one copy of the backups, you can run this query: select sum(sum_bytes)/1024/1024 m_bytes

from(

select sum(bytes) sum_bytes from v$datafile

union

select sum(bytes) sum_bytes from v$tempfile

union

select (sum(bytes) * members) sum_bytes from v$log

group by members);

Step 2: Determine the Locations and Names of the Files to Copy

Run this query to list the names (and paths) of files that are included in a cold backup of a noarchivelog-mode database:

select name from v$datafile

union

select name from v$controlfile

union

select name from v$tempfile

union

select member from v$logfile;

Do you need to back up the online-redo logs? No; you never need to back up the online-redo logs as part of any type of backup. Then why do DBAs back up the online-redo logs as part of a cold backup?

One reason is that it makes the restore process for the noarchivelog mode scenario slightly easier. The online-redo logs are required to normally open the database. If you back up all files (including the online-redo logs), then to get your database back to the state it was in at the time of the backup, you restore all files (including the online-redo logs) and start up your database.

■
Tip
See the later sections in this chapter on scripting for examples of SQL-generating commands you can use to automate B&R steps.

415

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Step 3: Shut Down the Database

Connect to your database as the SYS (or as a SYSDBA privileged user), and shut down your database using IMMEDIATE, TRANSACTIONAL, or NORMAL In almost every situation, using IMMEDIATE is the preferred method.

This mode disconnects users, rolls back uncompleted transactions, and shuts down the database: $ sqlplus / as sysdba

SQL> shutdown immediate;

Step 4: Create Backup Copies of the Files

For every file identified in step 2, use an OS utility to copy the files to a backup directory (identified in step 1). In this simple example, all the datafiles, control files, temporary database files, and online-redo logs are in the same directory. In production environments, you’ll most likely have files spread out in several different directories. This example uses the Linux/Unix cp command to copy the database files from /ora01/dbfile/O11R2 to the /oradump/cbackup/O11R2 directory:

$ cp /ora01/dbfile/O11R2/*.* /oradump/cbackup/O11R2

Step 5: Restart Your Database

After all the files are copied, you can start up your database:

$ sqlplus / as sysdba

SQL> startup;

Restoring a Cold Backup in Noarchivelog Mode with Online-Redo

Logs

The next example explains how to restore from a cold backup of a database in noarchivelog mode. If you included the online-redo logs as part of the cold backup, you can include the online-redo logs when you restore the files. Here are the steps involved with this procedure:

1.

Shut down the instance.

2.

Copy the datafiles, online-redo logs, temporary files, and control files back from the backup.

3.

Start up your database.

4.

These steps are detailed in the following sections.

Step 1: Shut Down the Instance

Shut down the instance, if it’s running. In this scenario, it doesn’t matter how you shut down the database, because you’re restoring back to a point in time. Any files in the live database directory locations are overwritten when the backup files are copied back. If your instance is running, you can abruptly abort it. As a SYSDBA privileged user, do the following:

$ sqlplus / as sysdba

SQL> shutdown abort;

416

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Step 2: Copy the Files Back from the Backup

This step does the reverse of the backup: you’re copying files from the backup location to the live database file locations. In this example, all the backup files are located in the /oradump/cbackup/O11R2

directory and all files are being copied to the /ora01/dbfile/O11R2 directory: $ cp /oradump/cbackup/O11R2/*.* /ora01/dbfile/O11R2

Step 3: Start Up the database

Connect to your database as SYS (or a user that has SYSDBA privileges), and start up your database: $ sqlplus / as sysdba

SQL> startup;

After you finish these steps, you should have an exact copy of your database as it was when you made the cold backup. It’s as if you set your database back to the point in time when you made the backup.

Restoring a Cold Backup in Noarchivelog Mode Without Online-

Redo Logs

As mentioned earlier, you don’t ever need the online-redo logs when restoring from a cold backup. If you made a cold backup of your database in noarchivelog mode and didn’t include the online-redo logs as part of the backup, the steps to restore are nearly identical to the steps in the previous section. The main difference is that the last step requires you to open your database using the OPEN RESETLOGS clause.

Here are the steps:

1. Shut down the instance.

2. Copy the control files and datafiles back from the backup.

3. Start up the database in mount mode.

4. Open the database with the OPEN RESETLOGS clause.

Step 1: Shut Down the Instance

Shut down the instance, if it’s running. In this scenario, it doesn’t matter how you shut down the database because you’re restoring back to a point in time. Any files in the live database directory locations are overwritten when the backups are copied. In this scenario, if your instance is running, you can abruptly abort it. As a SYSDBA privileged user, do the following: $ sqlplus / as sysdba

SQL> shutdown abort;

Step 2: Copy the Files Back from the Backup

Copy the control files and datafiles from the backup location to the live datafile locations: $ cp /*.* 417

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Step 3: Start Up the Database in Mount Mode

Connect to your database as SYS or a user with SYSDBA privileges, and start the database in mount mode: $ sqlplus / as sysdba

SQL> startup mount

Step 4: Open the Database with the OPEN RESETLOGS Clause

Open your database for use with the OPEN RESETLOGS clause:

SQL> alter database open resetlogs;

If you see the “Database altered” message, the command was successful. However, you may see this error:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery In this case, issue the following:

SQL> recover database until cancel;

You should see this message:

Media recovery complete.

Now, attempt to open your database with the OPEN RESETLOGS clause:

SQL> alter database open resetlogs;

This statement instructs Oracle to re-create the online-redo logs. Oracle uses information in the control file for the placement, name, and size of the redo logs. If there are old online-redo log files in those locations, they’re overwritten.

If you’re monitoring your alert.log throughout this process, you may see ORA-00312 and ORA-00313.

This means Oracle can’t find the online-redo log files; this is okay because these files aren’t physically available until they’re re-created by the OPEN RESETLOGS command.

Scripting a Cold Backup and Restore

It’s instructional to view how you script a cold backup. The basic idea is to dynamically query the data dictionary to determine the locations and names of the files to be backed up. This is preferable to hardcoding the directory locations and file names in a script. The dynamic generation of a script is less prone to errors and surprises (for example, when new datafiles are added to a database but not added to an old hard-coded backup script).

■
Note
The scripts in this section aren’t meant to be production-strength B&R scripts. Rather, they illustrate the basic concepts of scripting a cold backup and subsequent restore.

The first script in this section makes a cold backup of a database. Before you use the cold-backup script, you need to modify these variables contained in the script to match your database environment: 418

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

• ORACLE_SID

• ORACLE_HOME

• cbdir

The cbdir variable specifies the name of the backup-directory location. The script creates a file named coldback.sql, which is executed from SQL*Plus to initiate a cold backup of the database:

#!/bin/bash

ORACLE_SID=O11R2

ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1

PATH=$PATH:$ORACLE_HOME/bin

#