Pro Oracle Database 11g Administration (74 page)

SQL> recover datafile '/ora02/dbfile/O11R2/users01.dbf';

Oracle inspects the SCN in the datafile header and determines which archive-redo log or online-redo log to use to start applying redo. If all redo required is in the online-redo logs, you see this message: Media recovery complete.

If the starting point for redo is contained only in an archive-redo log file, Oracle suggests which file to start with:

ORA-00279: change 17809451 generated at 08/21/2010 10:10:56 needed for thread 1

ORA-00289: suggestion : /ora02/oraarch/O11R2/1_45_726314508.arc

ORA-00280: change 17809451 for thread 1 is in sequence #45

Specify log: {=suggested | filename | AUTO | CANCEL}

You can type AUTO to have Oracle apply all required redo in archive-redo log files and online-redo log files:

AUTO

You should see this message if successful:

Log applied.

Media recovery complete.

You can now bring the datafile back online:

SQL> alter database datafile '/ora02/dbfile/O11R2/users01.dbf' online; Restoring Control Files

When you’re dealing with user-managed backups, you usually restore the control file in one of these situations:

• One control file is damaged, and the control file is multiplexed.

• All control files are damaged.

These two situations are covered in this section.

445

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Restoring One Damaged Control File When Multiplexed

If you configure your database with more than one control file, you can shut down the database and use an OS command to copy an existing control file to the location of the missing control file. For example, from the initialization file, you know that two control files are used for this database: SQL> show parameter control_files

NAME TYPE VALUE

---------------------------- ----------- ------------------------------

control_files string /ora01/dbfile/O11R2/control01.ctl,

/ora01/dbfile/O11R2/control02.ctl

Suppose the control02.ctl file has become damaged. Oracle throws this error when querying the data dictionary:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/ora01/dbfile/O11R2/control02.ctl'

When a good control file is available, you can shut down the database, and copy the existing good control file to the name and location of the bad control file:

SQL> shutdown abort;

$ cp /ora01/dbfile/O11R2/control01.ctl /ora01/dbfile/O11R2/control02.ctl Now, restart the database:

SQL> startup;

In this manner, you can restore a control file from an existing control file.

Restoring When Al Control Files Are Damaged

If you lose all of your control files, you can restore one from a backup or you can re-create the control file. As long as you have all your datafiles and any required redo (archive redo and online redo), you should be able to completely recover your database. The steps for this scenario are as follows: 1.

Shut down the database.

2.

Restore a control file from the backup.

3.

Restore all datafiles from the backup.

4.

Start the database in mount mode, and initiate database recovery using the RECOVER DATABASE USING BACKUP CONTROLFILE clause.

5.

For a complete recovery, manually apply the redo contained in the online-redo logs.

6.

Open the database with the RESETLOGS clause.

In this example, all control files for the database were accidentally deleted, and Oracle subsequently reports this error:

ORA-00202: control file: '/ora01/dbfile/O11R2/control01.ctl'

ORA-27041: unable to open file

446

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Step 1: Shut Down the Database

In this case, you have to shut down your database and restore all datafiles and the control file from the backup location to the live database locations. First, shut down the database: SQL> shutdown abort;

Step 2: Restore the Control File from the Backup

This database was configured with just one control file, which you copy back from the backup location as shown:

$ cp /oradump/hbackup/O11R2/controlbk.ctl /ora01/dbfile/O11R2/control01.ctl If more than one control file is being used, you have to copy the backup control file to each control file and location name listed in the CONTROL_FILES initialization parameter.

Step 3: Restore All Datafiles from the Backup

As part of this recovery procedure, all datafiles must be restored from the backup. In this example, all the backup files are in the /oradump/hbackup/O11R2 directory:

$ cp /oradump/hbackup/O11R2/*.dbf /ora01/dbfile/O11R2

In this type of recovery scenario, you need to copy all the datafiles back from the last hot backup. If you didn’t, the SCN information in the datafile headers would be more current than the SCN in the control file. There is no way for Oracle to apply redo to a control file to catch it up to a datafile (it works just the opposite: redo must be applied to the datafiles).

Step 4: Start the Database in Mount Mode, and Initiate Database Recovery Next, start the database in mount mode:

SQL> startup mount;

After the control file(s) and datafiles have been copied back, you can perform a recovery. Oracle knows that the control file was from a backup (because it was created with the ALTER DATABASE BACKUP

CONTROLFILE statement), so the recovery must be performed with the USING BACKUP CONTROLFILE clause: SQL> recover database using backup controlfile;

At this point, you’re prompted for the application of archive-redo log files: ORA-00279: change 17779631 generated at 08/21/2010 04:51:22 needed for thread 1

ORA-00289: suggestion : /ora02/oraarch/O11R2/1_33_726314508.arc

ORA-00280: change 17779631 for thread 1 is in sequence #33

Specify log: {=suggested | filename | AUTO | CANCEL}

Type AUTO to instruct the recovery process to automatically apply all archive-redo logs: AUTO

After all archive-redo logs have been applied, it’s instructional to think about what would happen if you tried to open your database at this point:

SQL> alter database open;

447

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Oracle throws the following error in this situation:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open In this scenario, the online-redo logs are still intact, so a complete recovery is possible by applying the redo that exists in the online-redo logs (and doesn’t exist in the archive-redo logs). To apply what’s in the online-redo logs, first determine the locations and names of the online-redo log files: select

a.first_change#

,b.member

from v$log a

,v$logfile b

where a.group# = b.group#;

Here’s the partial output for this example:

FIRST_CHANGE# MEMBER

------------- -----------------------------------

17779560 /ora01/oraredo/O11R2/redo01a.rdo

17779678 /ora02/oraredo/O11R2/redo02a.rdo

Now, re-initiate the recover process:

SQL> recover database using backup controlfile;

The last archive-redo log generated for this database was sequence 51. The recovery process prompts for an archive-redo log that doesn’t exist:

ORA-00279: change 17815760 generated at 08/21/2010 13:21:58 needed for thread 1

ORA-00289: suggestion : /ora02/oraarch/O11R2/1_52_726314508.arc

ORA-00280: change 17815760 for thread 1 is in sequence #52

Instead of supplying the recovery process with an archive-redo log file, you type in the name of an online-redo log file. This instructs the recovery process to apply any redo in the online-redo log:

/ora01/oraredo/O11R2/redo02a.rdo

You should see this message when the correct online-redo log is applied: Log applied.

Media recovery complete.

The database is completely recovered at this point. However, because a backup control file was used for the recovery process, the database must be opened with the RESETLOGS clause: SQL> alter database open resetlogs;

Upon success, you should see this:

Database altered.

448

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Performing an Incomplete Recovery of an Archivelog-Mode

Database

Incomplete recovery
means you don’t restore all transactions that were committed before the failure.

With this type of recovery, you’re recovering to a point in time in the past, and transactions are lost. This is why incomplete recovery is also referred to as
database point-in-time recovery
(DBPITR).

Incomplete recovery doesn’t mean you’re restoring and recovering only a subset of datafiles. In fact, with most incomplete scenarios, you have to restore all datafiles from the backup as part of the procedure. If you don’t restore all datafiles, you first need to take offline any datafiles you don’t intend to participate in the incomplete-recovery process. Any datafiles taken offline can’t be later restored and recovered.

You may want to perform an incomplete recovery for many different reasons:

• You attempt to perform a complete recovery but are missing the required archive-redo logs or unarchived online-redo log information.

• You want to restore the database back to a point in time in the past just prior to an erroneous user error (such as deleted data, dropped table, and so on).

• You have a testing environment in which you have a baseline copy of the database. After the testing is finished, you want to reset the database back to baseline for another round of new testing.

If you’re using user-managed incomplete recovery for any of these reasons, you should consider using the Flashback Table or Flashback Database feature. These features are discussed in detail later in the chapter.

You can perform user-managed incomplete recovery three ways:

• Cancel based

• SCN based

• Time based

Cancel based
allows you to apply archive redo and halt the process at the boundary based on an archive-redo log file. For example, say you’re attempting to restore and recover your database, and you realize that you’re missing an archive-redo log. You have to stop the recover process at the point of your last good archive-redo log. You initiate cancel-based incomplete recovery with the CANCEL clause of the RECOVER DATABASE statement:

SQL> recover database until cancel;

If you want to recover up to and including a certain SCN number, use
SCN-based
incomplete recovery. You may know from the alert log or from the output of LogMiner the point to which you want to restore to a certain SCN. Use the UNTIL CHANGE clause to perform this type of incomplete recovery: SQL> recover database until change 12345;

If you know the time at which you want to stop the recovery process, use
time-based
incomplete recovery. For example, you may know that a table was dropped at a certain time and want to restore and recover the database up to the specified time. The format for a time-based recovery is always as follows:

'YYYY-MM-DD:HH24:MI:SS'. Here’s an example:

SQL> recover database until time '2010-10-21:02:00:00';

449

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

When you perform an incomplete recovery, you have to restore all datafiles that you plan to have online when the incomplete restoration is finished. Here are the steps for an incomplete recovery: 1. Shut down the database.

2. Restore all the datafiles from the backup.

3. Start the database in mount mode.

4. Apply redo (roll forward) to the desired point, and halt the recovery process (use cancel-, SCN-, or time-based recovery).

5. Open the database with the RESETLOGS clause.

The following example performs a cancel-based incomplete recovery. If the database is open, shut it down:

$ sqlplus / as sysdba

SQL> shutdown abort;

Next, copy
all
datafiles from the backup (either a cold or hot backup). This example restores all datafiles from a hot backup. For this example, the current control file is intact and doesn’t need to be restored. Here’s a partial snippet of the OS copy commands for the database being restored: $ cp /oradump/cbackup/O11R2/system01.dbf /ora01/dbfile/O11R2/system01.dbf $ cp /oradump/cbackup/O11R2/sysaux01.dbf /ora01/dbfile/O11R2/sysaux01.dbf $ cp /oradump/cbackup/O11R2/undotbs01.dbf /ora02/dbfile/O11R2/undotbs01.dbf $ cp /oradump/cbackup/O11R2/users01.dbf /ora02/dbfile/O11R2/users01.dbf $ cp /oradump/cbackup/O11R2/appdata.dbf /ora01/dbfile/O11R2/appdata.dbf After the datafiles have been copied back, you can initiate the recovery process. This example performs a cancel-based incomplete recovery:

$ sqlplus / as sysdba

SQL> startup mount;

SQL> recover database until cancel;

At this point, the Oracle recovery process suggests an archive-redo log to apply: ORA-00279: change 17851736 generated at 08/22/2010 07:33:42 needed for thread 1

ORA-00289: suggestion : /ora02/oraarch/O11R2/1_3_727625398.arc

ORA-00280: change 17851736 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}

In this example, you know that the last good archive-redo log that you have is sequence 7, so you apply redo to that point and then type CANCEL:

CANCEL

This stops the recovery process. Now you can open the database with the RESETLOGS clause: SQL> alter database open resetlogs;

The database has been opened to a point in time in the past. The recovery is deemed incomplete because not all redo was applied.

450

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Flashing Back a Table

In older versions of the database, if a table was accidentally dropped, you had to do the following to restore the table:

1. Restore a backup of the database to a test database.

2. Perform an incomplete recovery up to the point in time when the table was dropped.