Oracle RMAN 11g Backup and Recovery (101 page)

Chapter 12: RMAN Restore and Recovery
291

Note that in this case, we are accepting that the control file backup set pieces will be created in the default location.

Step 2.
Because this is an online backup, there is no need to shut down and then mount the database. Complete an online backup of your system. In this case, we will back up the database and the archived redo logs. Once the archived redo logs are backed up, we will remove them. In this workshop, we will assume that the backup is to a configured default device.

backup database plus archivelog delete input;

Step 3.
Shut down your database:

shutdown immediate;

Step 4.
Rename all database datafiles. Also rename the control files. Do not rename your online redo logs for this exercise. (Optionally, you can remove these files if you don’t have the space to rename them and if you really can afford to lose your database, should something go wrong.)
Step 5.
Startup nomount your database and restore your control file: startup nomount;

set DBID ;

restore controlfile from autobackup;

alter database mount;

Step 6.
Recover your database with RMAN using the backup you took in Step 2: restore database;

recover database;

alter database open resetlogs;

Step 7.
Complete the recovery by backing up the database again:

shutdown immediate;

startup mount;

backup database;

Tablespace Recoveries

Perhaps you have just lost datafiles specific to a given tablespace. In this event, you can opt to recover just a tablespace rather than the entire database. One nice thing about tablespace recoveries is that they can occur while the rest of the database is humming along. For example, suppose you lose your accounts payable tablespace, but your accounts receivable tablespace is just fine. As long as your application doesn’t need to access the accounts payable tablespace, you can be recovering that tablespace while the accounts receivable tablespace remains accessible.

Here is an example of the code required to recover a tablespace:

sql "alter tablespace users offline";

restore tablespace users;

recover tablespace users;

sql "alter tablespace users online";

292
Part II: Setup Principles and Practices

As you can see, the recovery process is pretty simple. First, we need to take the tablespace offline. We use a new command,
sql
, to perform this action. Enclosed in quotes after the
sql
command is specific SQL that we want the database to execute; in this case, we are taking the USERS tablespace offline with the command
alter tablespace users offline
. Next, we restore the datafiles associated with the tablespace, and then we recover the tablespace. Finally, we use the
sql
command again to issue the
alter tablespace users online
command, and the recovery of the USERS tablespace is complete.

NOTE

You cannot recover an individual tablespace or datafile to a point in
time different from that of the rest of the database.

You can also recover multiple tablespaces in the same command set, as shown in this code snippet:

sql "alter tablespace users offline";

sql "alter tablespace data offline";

restore tablespace users, data;

recover tablespace users, data;

sql "alter tablespace users online";

sql "alter tablespace data online";

Datafile Recoveries

Second cousin to a tablespace recovery is a datafile recovery, which is a very granular approach to database recovery. Here, we can replace lost database datafiles individually, while the rest of the tablespace remains online. Datafile recovery allows the DBA to recover specific datafiles while allowing the rest of the tablespace to remain online for users to access. This feature is particularly nice if the datafile was empty or sparsely populated, as opposed to recovering the entire tablespace. Here is some sample code required to recover a datafile: sql "alter database datafile 3 offline ";

sql "alter database datafile 'd:\oracle\oradata\users01.dbf' offline "; restore datafile 3;

restore datafile 'd:\oracle\oradata\users01.dbf';

recover datafile 3;

recover datafile 'd:\oracle\oradata\users01.dbf';

sql "alter database datafile 3 online";

sql "alter database datafile 'd:\oracle\oradata\users01.dbf' online "; We recovered a couple of datafiles in this example, by using two methods of defining which datafile we were recovering. First, we used the
sql
command again and took the offending datafiles offline with an
alter database datafile offline
command (they may be already offline in some cases, but we want to make sure). We then restore the datafiles with the
restore datafile
command.

The first command restores the datafile by number, and the second restores the datafile by name.

Next we recover the datafiles. Again we recover the first datafile by number and then we recover the datafile by name. Finally we use the SQL command to bring the datafiles back online.

Again we used the datafile number in the first SQL command, and the datafile name on the second.

Chapter 12: RMAN Restore and Recovery
293

Before we move on, let’s look more closely at one component of the
alter database
command: how we reference datafiles. There are two different ways to reference datafiles. The first is to reference the datafile by number, and that’s what we did with datafile 3 in the preceding example.

The second is to reference a datafile by name, 'd:\oracle\oradata\ users01.dbf'.

Either method is acceptable, but we often find using the datafile number is easier. Generally, when a datafile is missing or corrupt, Oracle gives you both the datafile name and number in the associated error message, as shown in this example:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'D:\ORACLE\ORADATA\RECOVER\TOOLS01.DBF'

Notice in this listing that datafile 4 is associated with the tools01.dbf datafile. Often, it’s much easier to just indicate that you want to restore datafile 4 than to indicate you want to restore d:\oracle\oradata\recover\tools01.dbf.

Once we have taken our datafiles offline, we will restore them (again using either the file number or the filename) and then recover them. Finally, we will bring the datafiles online again, which will complete the recovery process.

What If I Use Incremental Backups?

Oracle will determine automatically if you are using an incremental backup strategy when you restore your datafiles and will automatically apply the required incremental backup sets as required. You do not need to do anything different to recover in these cases.

During a restore using an incremental backup, the
restore
command restores only the base backup. Once that restore is complete, you issue the
recover
command, which causes the incremental backups to be applied to the database, and then the archived redo logs will be applied. Once that is complete, then you can open the database as usual. In all cases, Oracle attempts to restore the base backup and incremental backup that is the most recent. This reduces the amount of redo that has to be applied to fully recover the database and thus reduces the overall restore time.

Note that since the database will likely be applying multiple backup sets during the recover process, your recovery will likely take longer than you might expect. However, depending on a number of factors (data change velocity being a large factor), applying incremental backup sets can be faster than the application of a generous amount of redo, and thus, the incremental backup solution can be a faster one. Therefore, the ultimate benefit of incremental backups is a quicker backup strategy (and a smaller overall space requirement for the backup set pieces) at the expense of a potentially slower recovery timeline.

Recovering from Online Redo Log Loss

One recovery situation you might experience is the loss of the database online redo logs. You will need to contend with four different situations if you lose the online redo logs:

■ Loss of a redo log file group member

■ Loss of an inactive online redo log group

■ Loss of an active but not current online redo log group

■ Loss of the current online redo log group