Chapter 14 *

Backup and Recovery Options with Archiving *

Supporting 24-Hour Operations *

Online vs. Offline Backups *

Exercises *

Online Backup Methods *

Exercises *

Online Backups of the Control File *

Exercise *

Complete Recovery with Archiving *

Complete Recovery Methodology *

Complete Recovery with Disk Replacement *

Complete Recovery Without Disk Replacement *

Tablespace Recovery *

Datafile Recovery *

Exercises *

Complete Recovery Scenarios *

Scenario 1: Full Offline Backups *

Scenario 2: Online Tablespace Backups *

Exercises *

Incomplete Recovery with Archiving *

When to Use Incomplete Recovery *

Exercises *

Why Incomplete Recovery May Be Necessary *

Exercises *

Incomplete Recovery Process *

Change-Based Recovery *

Time-Based Recovery *

Cancel-Based Recovery *

Steps for Incomplete Recovery *

Exercises *

Incomplete Recovery and Re-Creating the Control File *

Database Recovery Using a Backup Control File *

Exercises *

Chapter Summary *

Two-Minute Drill *

Chapter 14

Backup and Recovery Options with Archiving

In this chapter, you will understand and demonstrate knowledge in the following areas:

There are several areas that this chapter focuses on. First, an in-depth discussion will be presented on the methods used for backing up the database that must be available 24 hours a day. The methodology presented for backups on 24-hour operations is only available when archiving is used. After covering the concepts and functions involved in backup on 24-hour databases, the discussion will focus on methods for complete recovery of all committed transactions to the time the database experienced media failure when archiving is enabled. The steps involved for this task, along with explanations and examples, will be presented. Finally, the potential for incomplete recovery with archiving will also be presented. These three main context areas comprise approximately 35 percent of the test, making understanding this chapter most critical for you passing the OCP DBA Exam 3.

Supporting 24-Hour Operations

In this chapter, you will cover the following topics related to supporting 24-hour operations:

Today’s corporations need more database availability for a global user base and/or a 24-hour workforce. They need databases that are available on a 24-hour basis as well. Supporting organizations with high availability requirements puts intense pressure on the DBA in two ways. First, many of the options presented, such as database export or offline full backups, are not viable strategies for database recovery. The only viable option is the one that takes advantage of archiving--online hot backups. The second factor is the additional pressure on the DBA to recover the database as quickly as possible. Since the database has users at all hours of the day, the DBA has pressure to restore the 24-hour database at all times, day or night.

Online vs. Offline Backups

A live database is always in flux. Data is stored to disk at times determined by the background processes DBWR and LGWR. Data from transactions that have been committed may not have actually been stored to database files. In order to ensure that all data is captured in a cold backup, it is required that the DBA close the database using shutdown normal from the Server Manager prompt. After the database has been shut down, all database file components are then copied and archived using operating system commands. The following bullet points list the five types of files that must be archived in order to restore the database:

Allocating enough space for a copy of all database files may be difficult, especially for large databases. Ideally, enough space can be allocated to store all files on disk first to complete the backup before transferring files to offline storage. This method not only minimizes downtime for the offline backup, but also improves recovery time if the DBA can keep some of the backups on disk in case of media failure. Offline backups by themselves offer coverage for minimal point-in-time recovery on database systems. With archived redo logs, the recovery options for offline backups expand to include recovery to any point in time up to point of disk failure. It is important for the DBA to use archiving with offline database backups for systems requiring more options than point-in-time recovery.

In globally deployed systems that require a range of availability for all hours of night and day corresponding to users in other areas of the world, offline backup may not be possible. The organization simply may not be able to allocate enough time per week to allow DBAs to take the database down in normal mode. As an alternative to be used in support of global operations that require round-the-clock online access, the DBA can use Oracle’s online backup method. Executing an online backup is an iterative process that creates backed-up copies of the database, tablespace by tablespace. In order to execute a hot backup, the DBA must execute the alter tablespace name begin backup command in Server Manager. An example of the command syntax in action appears in the following code block:

After issuing this command, the DBA makes a copy of all datafiles associated with the tablespace using operating system copy commands. The DBA issues the alter tablespace name end backup command from Server Manager.

TIP: It is highly recommended by Oracle that the tablespaces be backed up with the iterative approach, placed into backup mode with the alter tablespace begin backup statement one at a time, rather than setting all tablespaces into backup mode at once. Using the iterative approach minimizes the chance that a tablespace will be in backup mode at a time when the disk drive crashes or when the entire instance stops running for some reason.

Exercises

1. Identify the two areas DBAs are typically pressured to perform or deliver with respect to backup and recovery support for 24-hour database operations.
2. Identify differences between online and offline backups. Determine the appropriateness of using archiving for recovery with both online and offline backups. Which backup option requires archiving?

Online Backup Methods

In order to guarantee that the changes made to the tablespace while the online backup took place are kept, it is required that the DBA archive redo logs that were taken during the operation of online backups. Prior to taking the online backup, the DBA should issue the archive log list command from Server Manager in order to determine the oldest online redo log sequence that should be saved in conjunction with the online backups being taken. Once the tablespace backups are complete, the archive log list command should be issued again, followed by a log switch to ensure that the current redo log entries made for the tablespaces backed up are archived properly for use by the backups should recovery be necessary. The steps to the process are listed below:

1. Execute archive log list from Server Manager. Note value for "Oldest online log sequence." This is the oldest redo log required for using the online backup.

SVRMGR> ARCHIVE LOG LIST

Database log mode ARCHIVELOG
automatic archival ENABLED
Archive destination /u01/oracle/home/arch
Oldest online log sequence 21
Next log sequence to archive 25
Current log sequence 25

2. Execute alter tablespace name begin backup from Server Manager. This step prepares the tablespace for online backup.

SVRMGR> ALTER TABLESPACE users_01 BEGIN BACKUP;

3. Copy the datafiles for that tablespace using operating system commands or third party products. Be sure the copy resides on another disk than the production datafiles themselves.
4. Execute alter tablespace name end backup from Server Manager. This step completes the online backup process for that tablespace.

SVRMGR> ALTER TABLESPACE users_02 BEGIN BACKUP;

5. Repeat steps 2–4 for all tablespaces to be backed up.
6. Execute archive log list again from Server Manager. Note value for "Current log sequence" this time. This is the last redo log required for using the online backup.

SVRMGR> ARCHIVE LOG LIST

Database log mode ARCHIVELOG
automatic archival ENABLED
Archive destination /u01/oracle/home/arch
Oldest online log sequence 21
Next log sequence to archive 33
Current log sequence 33

7. Issue an alter system switch logfile to cause Oracle to create an archive of the current redo log. This archive should then be stored in the LOG_ARCHIVE_DEST area. If desired, copy the archives associated with the backup to tape.

SVRMGR> ALTER SYSTEM SWITCH LOGFILE;

8. Create a copy of the control file. This is done with the alter database backup controlfile statement.

TIP: A control file must be backed up whenever a change is made to the structure of a database. For example, after the creation of a new tablespace, or an addition or removal of a datafile, the control file for the database must be backed up.

It is possible to perform parallel online backups of the tablespaces of the database. The code block following this paragraph will illustrate parallel online tablespace backup. However, this method is not recommended. Taking tablespace backups iteratively allows less time between the beginning and the end of a tablespace backup. Minimizing backup time is important because less time for backup means there is less time when the database is exposed to the problems caused by database failure while a backup is taking place. The second reason for taking online tablespace backups iteratively is that the amount of redo information written is larger and more extensive during online backups than during normal operation of the database.

TIP: Two reasons not to take online tablespaces in parallel are that during the period of time a backup is occurring, the database is vulnerable to a system crash. To avoid leaving the system vulnerable for extended periods of time, the DBA should run online backups iteratively. The second reason for performing backups in this way is that redo log information is more substantial for transactions on tablespaces when the tablespace is in backup mode.

Exercises

1. What are the steps required for taking an online backup?
2. What is the difference between taking online backups in an iterative and a parallel fashion?
3. What are the advantages and drawbacks for parallel tablespace backups?

Online Backups of the Control File

Taking an online backup of the control file is accomplished in two different ways. The first way provides the DBA with a backup of the actual database control file, which is then used if there is a media failure that causes damage to a control file on the database. The method used to handle backup of this control file is the alter database backup controlfile statement. An example of usage for this statement appears in the following code block:

There is another method available for database backup of control files as well, using the same syntax with the addition of a special keyword called trace to use in place of name. In this situation, Oracle creates the script required to create the control file. This file is created in the trace file directory specified by the BACKGROUND_DUMP_DEST parameter of the init.ora file. An example for usage of the trace option is described in the following code block:

Exercise

1. What are the two options for backups of control files?

Complete Recovery with Archiving

In this section, you will cover the following topics related to complete recovery with archiving:

Complete recovery can be accomplished in two different ways: with offline backups or with online tablespace backups. Either way, however, the DBA must have all redo logs written by Oracle after the database was restarted for full offline backups, or all redo logs written during the database backup and after the backup to the point in time of the failure in the case of online backups. If the criteria for either scenario are met, the DBA can run a complete recovery of the database. This section will describe the process of making complete recovery happen to the point in time of a database failure using both online and offline backups, and the application of archived redo logs. It will also illustrate those processes with examples for each.

Complete Recovery Methodology

Recovery to the point of media failure on the Oracle database requires two things. First, the DBA requires some sort of database backup for the files that were lost in the media failure. This backup can either be online tablespace backups or offline database backup. The other component is a complete set of archived redo logs for the period of time following the backup, and in the case of hot online backups, redo information that was taken during the period of the backup. The requirements for backups and archived redo logs for complete recovery are illustrated in Figure 14-1.

Figure 1: Requirements for complete recovery

After restoring the database from backup components, the recovery takes place. Oracle looks for archived redo logs in the location specified by the LOG_ARCHIVE_DEST parameter of the init.ora file. An alternate location for the archived redo logs can be specified manually by the DBA using the from clause in the alter database recover statement. An example for usage of alter database recover in which an alternate location for archived redo log files is specified appears in the following code block:

The database cannot be open for use during a full recovery. The DBA can shut it down using shutdown abort. Using this option forces Oracle to require media recovery the next time the database is opened.

Complete Recovery with Disk Replacement

After making any hardware repairs or replacements necessary, the following steps are necessary make complete recovery. These steps are accomplished only when the disk media that failed is replaced before recovery begins.

1. Restore all files from the complete backup taken, including datafiles, redo log files, and control files. All files should be restored--not just damaged ones--in order for the entire database to be read consistent to a single point in time, unless complete recovery using archive logs will be performed. In this case, only lost files should be recovered. Operating system copy commands are used to handle this step.
2. Recover the database using archived redo log information. The command syntax for this process is the alter database recover database statement. This statement will begin the interactive process of applying redo log information:

ALTER DATABASE RECOVER DATABASE;

3. Take a full backup of the database. Use operating system copy commands for this step.
4. Open the database for use with the resetlogs option.

ALTER DATABASE OPEN RESETLOGS;

Complete Recovery Without Disk Replacement

It may not be possible to secure a repair or replacement for the disk that failed. In these situations, the DBA may need to recover the database by placing files that were on the damaged disk onto other disks. The database must be closed for this process, and the other steps are listed.

1. Restore all files from the complete backup taken, including datafiles, redo log files, and control files. It is important that all files be restored--not just damaged ones--in order for the entire database to be read consistent to a single point in time (the point in time of the last database backup), unless complete recovery using archive logs will be performed. In this case, only lost files should be recovered. Operating system copy commands are used to handle this step for database recovery from a full backup.
2. Move control files to other disks if they were lost in media failure. Change the location of control files as specified in the CONTROL_FILES parameter of the init.ora file to reflect a new location for the control file of the Oracle database.
3. Mount the database in exclusive mode.
4. Move datafiles and redo log files onto other disks with the alter database rename file statement from Server Manager. The full pathnames for the datafile at the old and the new locations should be specified in the statement.

ALTER DATABASE orgdb01
RENAME FILE ‘/u01/oracle/data/data_301a.dbf’
TO ‘/u02/oracle/data/data_301b.dbf’;

ALTER DATABASE orgdb01
RENAME FILE ‘/u01/oracle/ctl/rdorgdb01’
TO ‘/u02/oracle/ctl/rdorgdb01’;

5. Back up the database in the same manner detailed for offline backups in Chapter 11.

6. Recover the database using archived redo log information. The command syntax for this process is the alter database recover database statement. This statement will begin the interactive process of applying redo log information:

ALTER DATABASE RECOVER DATABASE;

7. Back up the database.
8. Open the database using the resetlogs option.

ALTER DATABASE OPEN RESETLOGS;

Tablespace Recovery

Recovery can be performed on individual tablespaces in addition to the entire database. The DBA can execute a tablespace recovery using the alter database recover tablespace statement. The database must be open in order to accomplish a tablespace recovery, so that Oracle can view the contents of the database while the tablespace recovery occurs. However, the tablespace itself must be offline. A benefit to tablespace recovery is that the DBA can allow users to access other tablespaces while the offline tablespace is restored. In this example, assume that the USERS01 tablespace needs to be recovered.

1. Open the database if it is not already open.

ALTER DATABASE OPEN;

2. Take the tablespace on the disk that failed offline.

ALTER TABLESPACE users01 OFFLINE;

3. Restore damaged datafiles with their respective backup copies using operating system commands, assuming hardware problems have been rectified. If the hardware problem has not been rectified, or if the DBA decides to move the files of the tablespace to another disk, the DBA should issue the appropriate alter database rename file command.

ALTER DATABASE
RENAME FILE ‘/u01/oracle/home/data01.dbf’
TO ‘/u02/oracle/home/data01.dbf’;

4. Recover the tablespace. To minimize the amount of interaction required between DBA and Oracle, the DBA can include the automatic keyword in the alter database statement, allowing Oracle to automatically apply its own suggestions for redo logs.

ALTER DATABASE RECOVER AUTOMATIC TABLESPACE users01;

5. Back up the database.
6. Bring the tablespace online using the alter tablespace online statement.

ALTER TABLESPACE users01 ONLINE;

Datafile Recovery

Recovery can be performed on individual datafiles in the tablespace with the alter database recover datafile statement. The database and tablespace must be open in order to accomplish datafile recovery so that Oracle can view the contents of the database while the datafile recovery occurs. However, the datafile itself must be offline. A benefit to datafile recovery is that the DBA can allow users to access other tablespaces while the offline datafile is restored. In this example, assume that the users01a.dbf datafile needs to be recovered.

1. Open the database if it is not already open.

ALTER DATABASE OPEN;

2. Take the datafile on the disk that failed offline.

ALTER DATABASE DATAFILE ‘users01a.dbf’ OFFLINE;

3. Restore damaged datafiles with their respective backup copies using operating system commands, assuming hardware problems have been rectified. If the hardware problem has not been rectified, or if the DBA decides to move the file to another disk, the DBA should issue the appropriate alter database rename file command.

ALTER DATABASE
RENAME FILE ‘/u01/oracle/home/data01a.dbf’
TO ‘/u02/oracle/home/data01a.dbf’;

4. Recover the datafile. To minimize the amount of interaction required between DBA and Oracle, the DBA can include the automatic keyword in the alter database statement, allowing Oracle to automatically apply its own suggestions for redo logs.

ALTER DATABASE RECOVER AUTOMATIC DATAFILE ‘users01a.dbf’;

5. Back up the database.
6. Bring the tablespace online using the alter database datafile online statement.

ALTER DATABASE DATAFILE ‘users01a.dbf’ ONLINE;

Exercises

1. What database recovery procedure is performed when damaged disks are replaced? What database recovery procedure is performed when datafiles of other database files must be moved? Can the database be available?
2. What is the procedure for tablespace recovery? Can the database be available?
3. What is the procedure for datafile recovery? Can the database be available?

Complete Recovery Scenarios

The following discussion will present an example of a crisis situation that requires database recovery. A permanent media failure has occurred on two disks, which destroyed several datafiles for two different tablespaces, DATA01 and INDEX01. Hardware support has installed and formatted replacement drives for the disks that failed, thereby eliminating the DBA’s need to move the tablespaces to different disks. For this example, the DBA should assume that all the backups are available, along with all archived redo logs.

Scenario 1: Full Offline Backups

The first step required in this situation is to restore the appropriate files from backup to the disks that were replaced. There are three datafiles corresponding to one of the tablespaces that failed, data01a.dbf, data01b.dbf, and datao1c.dbf for DATA01. The other tablespace, INDEX01, consists of only one datafile, index01a.dbf. The DBA must first restore all copies of the database datafiles to the empty disks. However, since the DBA uses full offline backups as the cornerstone of the database recovery strategy, the DBA must restore all datafiles to all appropriate disks in order to maintain a read-consistent database. This step is accomplished manually, as illustrated in Figure 14-2.

Figure 2: Recovering all datafiles using full offline backups

Once all datafiles are copied into their proper locations, the DBA can then initiate recovery of the entire database. This step allows Oracle to rewrite all database changes made after the full backup was taken. This step is accomplished with the use of the alter database recover database statement from SQL*Plus, or simply the recover database command from Server Manager. The DBA can issue this statement from either tool according to the syntactic requirements illustrated in the following code block:

Oracle will prompt the DBA to specify archived redo logs to apply in order to facilitate the complete recovery. To ease the effort required from the DBA, Oracle will provide suggested names of archived redo logs to apply based on the values for the LOG_ARCHIVE_DEST, LOG_ARCHIVE_FORMAT, and the V$LOG_HISTORY dynamic performance views. If the archived redo log Oracle suggests the DBA apply is appropriate, the DBA need only press the enter key. Otherwise, the DBA can manually key in the appropriate redo log to apply. The database recovery may run for a long time, depending on how many redo logs there are to apply to the database. Another factor that increases the time recovery will take in this situation is the fact that Oracle has to apply all data in the archived redo logs to all tablespaces. The recovery from backup in this case causes all database changes made after the backup was taken to be discarded, only to be reentered from archived redo entries.

When the database recovery is complete, the DBA should take a full backup of the recovered database. This step will allow a full recovery at least to the moment in time the database was restored. The steps required for a full backup are detailed in Chapter 11. After taking the full backup, the DBA will be able to open the database. This step can be done with the alter database open statement. It should be noted that the DBA should reset the redo logs, thereby discarding previously applied archives at this stage, and thereby underscoring the importance of taking a backup of the database before making it available to the users after the data lost in a disaster has been restored.

Scenario 2: Online Tablespace Backups

This scenario requires several things from the DBA: a backup copy of the datafiles for each tablespace lost and all archived redo logs taken during the online tablespace backup and the archived redo information taken after the backup was completed. With these items available, the recovery can begin. The first thing required of the DBA is to put the datafiles onto the new disks that replaced the disks that failed. This step, like the complete recovery of a full offline backup, is accomplished using the commands and/or utilities available from the operating system.

After this step is complete, the DBA must recover the tablespaces to the same point in time as other tablespaces in the database. To do this, recall that the database must be open. If the database is not open already, the following statement can be issued:

Recall, however, that the tablespace that is being recovered cannot be available for regular usage. In this case, there are two tablespaces that cannot be available. As a result, the DBA must take these tablespaces offline. The command syntax for doing so lies in the alter tablespace statement in the following code block:

Now that the prerequisites for completing recovery specifically for the tablespaces lost in the database failure are complete, the DBA can pursue the recovery of those tablespaces lost with the recover tablespace command run from Server Manager, or the alter database recover tablespace statement run from SQL*Plus. The statement in the following code block will initiate the interactive process between Oracle and the DBA to recover the database to the point where media failure damaged the tablespaces irreparably:

Overall, the recovery of the individual tablespaces is faster than the time required for a database recovery, even though both recovery strategies use the same archived redo information. The reason for this brevity in producing a recovered database with the recover tablespace option is due to the fact that Oracle will not apply as much redo from the archived redo log to recover a tablespace as it would in recovery of a full database.

After tablespace recovery is complete for DATA01 and INDEX01, the DBA can bring the tablespaces back online with the alter tablespace name online statement. Also, the DBA may want to take an online backup of the tablespaces that were damaged as part of the media failure. This additional precaution allows the DBA to recover the tablespaces just recovered more easily in the event of some emergency. In fact, the DBA may want to take online backups for all tablespaces at this time.

In general, online backups offer the DBA a great deal of flexibility for backup strategies that must work around 24-hour database availability requirements. When the need arises to recover the database, the DBA has many options to reduce recovery time, and leave undamaged parts of the database untouched by the recovery effort and available to the users of the database. However, one option that online backups of tablespaces do not grant is incomplete recovery. The reason incomplete recovery is impossible is because it would leave the recovered portion of the database in an inconsistent state with the rest of the database, which was undamaged by the database media failure and therefore untouched by the database recovery.

In order to provide the users with incomplete recovery, the DBA must employ other options. One strategy the DBA may want to use is a periodic complete backup with the database offline. Usually, all databases but the ones to which 24-hour availability is most mission-critical should be able to sustain downtime at some point in a weekly or monthly cycle. However, for those databases that cannot, the DBA may have to employ a strategy whereby he or she recovers the database to another machine with the use of online backups, then exports the objects that must be recovered to a point in time in the past. This strategy is also useful in situations where user error requires the DBA to correct an improper data change.

Exercises

1. Which recovery scenarios in this discussion minimize downtime?
2. What initialization parameters does Oracle use to formulate the recommendations for applied archived redo logs?

Incomplete Recovery with Archiving

In this section, you will cover the following topics related to incomplete recovery with archiving:

The DBA may have to recover the database to a point in time in the past, at which time a backup may or may not have been taken. Archiving allows the DBA to make incomplete recovery. This discussion will present several aspects of incomplete recovery, from understanding what situations will require the DBA to execute incomplete recovery to the processes required for executing incomplete recovery. Also, the implications of incomplete recovery when the control file must be re-created will be a topic of presentation as well.

When to Use Incomplete Recovery

Incomplete recovery is recovery to a point in time in the past, before a media failure occurred. By definition, any database recovery that does not involve the application of archived redo information is an incomplete recovery. This fact is true because it is the application of archived redo information that brings the database to a state of complete recovery. Logical backup and recovery methods using EXPORT and IMPORT do not permit the possibility of complete recovery, for although database exports can be imported to provide recovery to a point in time, the DBA cannot necessarily pick an arbitrary point in time to recover the database to. Rather, that point in time is determined by when the backup completes.

TIP: The status of the datafiles in a database that may need to be recovered can be determined with use of the V$RECOVERY_FILE_STATUS dynamic performance view. The status of a database recovery overall may be determined using the V$RECOVERY_STATUS view.

Just as archived redo log information allows the DBA a great deal of flexibility in allowing complete recovery to the exact point in time the database media failure occurred, archived redo information also allows the DBA to perform recovery to just about any point in between. The point, then, can be specified in a variety of ways, allowing the DBA to perform both complete recovery to the point in time of the database failure as well as incomplete recovery to any point in time desired.

There are three other types of database recovery that have already been introduced. These three types of recovery are change-based recovery, cancel-based recovery, and time-based recovery. These types of recovery are based entirely on the availability of archived redo logs for the purpose of restoring changes made. These categories for database recovery are named based on the mechanisms that Oracle and the DBA will use to end the recovery. These three types of recovery will generally take place only when the database has been mounted in exclusive mode and otherwise left closed.

Change-based recovery is the first type considered. In change-based recovery, Oracle will restore database changes made up to the change that is specified by the DBA as part of the command that initiates the recovery. The DBA may in some cases use the change-based recovery option to identify the last known good transaction to have taken place, for the purpose of recovering the database to the point just after that transaction committed. The change that indicates the end of the recovery to Oracle is defined with the use of a system change number, or SCN. The SCN is a number that Oracle assigns to every transaction that occurs in the database. This "transaction ID number" is then attached to every statement that executes as part of the transaction. The change information can then be tracked both in the rollback segments and the online redo logs, gathering all statements that make changes together as one transaction. The SCN is specified as an integer. To find the last SCN archived by Oracle or what the SCN that was written at the last checkpoint was, the DBA can query the V$DATABASE dynamic performance view. The following statement illustrates how the DBA may use the change-based recovery. The number 4043 is the SCN for a transaction in the online redo log. When the recovery procedure reaches transaction number 4043, Oracle will apply the database changes that were committed as part of that transaction. After that point, Oracle will terminate the recovery automatically.

TIP: The SCN of the last transaction conducted at a checkpoint can be found in the V$DATABASE dynamic performance view. To find the SCN of a particular transaction in an archived redo log, the V$LOG_HISTORY performance view can be used.

Rather than restoring to a system change number, which may be hard for the DBA to ascertain, the incomplete database recovery may be conducted to a certain point in time instead. This type of recovery is considered to be a time-based recovery. Time-based and change-based recoveries are similar in that Oracle will restore data to the database to some point in the past. The big difference is that the DBA can identify a point in time rather than dig through the database to identify the system change number for the last transaction committed at a point in time. Once the recovery has applied redo information for all committed transactions through the time named, the recovery will end automatically. If uncommitted data was written in order to supply the database with all committed information to the time named, then the uncommitted transaction data will be rolled back before the recovery ends. The final type of incomplete recovery considered is the cancel-based recovery, which allows the DBA to run a database recovery for an indefinite period, defined on the fly by the DBA as the recovery executes. During the course of the recovery, the DBA may choose to issue a cancel command, and the recovery will stop. The cancel-based recovery offers the DBA unmatched control over the execution of database recovery; however, it carries with that control the responsibility of the DBA to monitor the recovery process. More details about these types of database recovery are discussed a little later in this chapter.

Exercises

1. What are the three types of incomplete recovery?
2. Which type of incomplete recovery cannot be run in conjunction with automatic recovery?
3. Can the database be available during incomplete recovery? Why or why not?

Why Incomplete Recovery May Be Necessary

The DBA may use incomplete recovery for several reasons. In a system fed daily with valid value or core data, an incorrect feed of information may cause transactions to be conducted based on incorrect information. However, there are other situations where the database may require incomplete recovery. For example, in the situation of a user error, a database might require recovery to the point where a database object existed in one state such that the DBA can export that singular database object. The DBA may restore the information to a duplicate database and populate the production version of the database with the object as it appeared in the past, thus correcting a user error.

Incomplete recovery may be the result of a lack of choice on the part of the DBA. Somewhere along the line, an archived redo log may get lost or damaged. If a situation arises where the redo information for a database on a specific redo log sequence is lost, then the database cannot be recovered beyond that point via the application of archived redo logs. Thus, unless there is a baseline backup to work with that was taken after the missing archived redo log, the database cannot be recovered past the point in time representing the gap in redo log information. So, if an archived redo log is lost, the DBA may have no choice but to perform incomplete recovery.

Exercises

1. What are some reasons incomplete recovery with archiving might be used by the DBA?
2. Why is incomplete recovery with archiving better than recovery without archiving for point-in-time recovery?

Incomplete Recovery Process

The command used to perform database recovery is the alter database recover statement issued from SQL*Plus, or the recover command in Server Manager. At the time one of these commands is issued, the database cannot be opened or available for use, and must be mounted by only one database instance. The DBA may want to issue the following statements to prepare the database for incomplete recovery, executed from within Server Manager:

Incomplete recovery should be executed on the entire database. An incomplete recovery cannot be performed on only one tablespace in Oracle7, because the database would then be left in a read-inconsistent state. By performing the incomplete recovery on the entire database, the DBA keeps the database read consistent with a moment in time in the past. The next step in incomplete recovery is to restore the database from a backup copy. This step must be done with a full backup, to create a benchmark copy of the Oracle database at a point in time. Alternately, several tablespace backups can be used, as long as all the backups were taken at a point in time prior to the point to which the DBA wants to recover.

In case of media failure associated with the need for incomplete recovery, the DBA may have to place datafiles on alternate disk media. If this is the case, the DBA may want to issue some alter database rename file statements to update the control file with the location changes. For the purposes of this discussion, assume that all datafiles will stay in the same location. After recovering the datafiles from the operating system perspective, the DBA can then move onto complete the recovery from the perspective of Oracle by applying the archived redo log information. Provided that Oracle has handled archiving automatically, the location from which Oracle will pull archived redo log files is specified by the pathname in the LOG_ARCHIVE_DEST parameter in the init.ora file. If automatic archiving has not been used, the LOG_ARCHIVE_DEST parameter may or may not be specified. To specify a location from which to draw archived redo information, the DBA can include a from clause. Consider the following code block:

As stated before, the incomplete recovery must be a database-wide recovery involving the application of archived redo information to all datafiles of all tablespaces. This process requires the use of the database clause in the alter database statement. The following code block demonstrates the proper use of the database clause:

The three incomplete recovery processes begin to differentiate after the until keyword. The specifications for each of the incomplete recovery processes appear after this keyword, and tell Oracle when and how to identify the moment recovery is over. To refresh the discussion, recovery can be stopped on the application of a specific system change number (SCN). Alternately, database recovery can cease at a point in time in the past, or via an explicit cancel issued by the DBA in the interactive process between Oracle and the DBA that marks the application of archived redo information during database recovery.

Change-Based Recovery

Oracle will restore database changes made up to the change that is specified by the DBA as part of the command that initiates the recovery. The change that indicates the end of the recovery to Oracle is defined with the use of a system change number, or SCN. The SCN is a number that Oracle assigns to every transaction that occurs in the database. The SCN is specified as an integer. To find the last SCN archived by Oracle or what the SCN that was written at the last checkpoint was, the DBA can query the V$DATABASE dynamic performance view. The following statement illustrates how the DBA may use the change-based recovery. The number 39983 is the SCN for a transaction in the online redo log. When the recovery procedure reaches transaction number 39983, Oracle will apply the database changes that were committed as part of that transaction. After that point, Oracle will terminate the recovery automatically.

In the database recovery statement listed in the code block above, several things are happening. First, the database will be recovered automatically, meaning that interaction between Oracle and the DBA will be minimized during the database recovery. Instead of prompting the DBA to supply names of archived redo logs while simultaneously offering suggestions for redo to apply, Oracle will simply apply its own redo log suggestions and inform the DBA if there is a problem. Once transaction 39983 has been applied in its committed entirety, the database recovery is complete and Oracle concludes the recovery automatically.

Time-Based Recovery

The incomplete database recovery may also be conducted to a certain point in time This type of recovery is considered to be a time-based recovery. The point in time should be identified to a precision in seconds. The appropriate format for the time a DBA can specify is a four-digit year, followed by a two-digit month, followed by two-digit date. These three items are separated by a hyphen. Immediately following the date should be a colon, followed by the hour of the day in 24-hour format, followed by the minute, followed by the second. The time components should be separated by colons. A sample alter database statement with recovery to a point in time identified is listed in the code block following the paragraph. Note also that in addition to the formatting constraints described above, the date must be enclosed in single quotes. Once the recovery has applied redo information for all committed transactions through the time named, the recovery will end automatically. If uncommitted data was written in order to supply the database with all committed information to the time named, then the uncommitted transaction data will be rolled back before the recovery ends.

In the database recovery statement listed in the code block above, several things are happening. First, the database will be recovered automatically, meaning that interaction between Oracle and the DBA will be minimized during the database recovery. Instead of prompting the DBA to supply names of archived redo logs while simultaneously offering suggestions for redo to apply, Oracle will simply apply its own redo log suggestions and inform the DBA if there is a problem. Once transaction information has been applied , the database recovery is complete and Oracle concludes the recovery automatically.

Cancel-Based Recovery

The final type of incomplete recovery considered is the cancel-based recovery. This type of allows the DBA to run a database recovery for an indefinite period, defined on the fly by the DBA as the recovery executes. During the course of the recovery, the DBA may choose to issue a cancel command, and the recovery will stop. The cancel-based recovery offers the DBA unmatched control over the execution of database recovery; however, it carries with that control the responsibility of the DBA to monitor the recovery process. In order to demonstrate the usage of cancel-based recovery, the following code block illustrates an alter database statement that specifies cancel-based recovery. In order to cancel recovery, the DBA can issue the cancel command as Oracle interactively requests the DBA to confirm the application of suggested redo logs.

In the database recovery statement listed in the code block above, several things are happening of note. First, the database cannot be recovered automatically, meaning that interaction between Oracle and the DBA cannot be minimized during the database recovery. Because cancel-based recovery requires the DBA to specifically tell Oracle when to stop applying redo information, the DBA cannot use the automatic option in conjunction with cancel-based recovery. Once transaction information written to the database as of the moment has been applied in its committed entirety, the database recovery is complete and Oracle concludes the recovery when the DBA issues the cancel command at an interactive prompt.

After the execution of whichever incomplete recovery option the DBA must use in order to obtain the desired recovery result, the DBA may want to back up the database before allowing the users access to it. The DBA should conduct a full offline backup of the database before allowing the database to be accessed by the users. After obtaining the full offline physical backup using means identified in Chapter 11, the DBA can now allow the users access to the database while simultaneously discarding the archived redo log information with the use of the resetlogs option. Discarding archived redo log information underscores the importance of taking a full backup of the database after recovery is complete, and is done with the alter database open resetlogs statement.

Steps for Incomplete Recovery

Here is a list of the steps required for incomplete recovery:

1. Prepare the database for incomplete recovery by disallowing user access and mounting the database to only one instance.

SHUTDOWN ABORT;
ALTER DATABASE orgdb01 MOUNT EXCLUSIVE;

2. Recover all datafiles for all tablespaces using commands or utilities provided by the operating system.
3. Execute the incomplete recovery with the alter database recover database statement, specifying the appropriate incomplete recovery method, depending on the needs of the DBA in the until clause of that statement.

ALTER DATABASE orgdb01
RECOVER AUTOMATIC
DATABASE UNTIL scn;

ALTER DATABASE orgdb01
RECOVER AUTOMATIC
DATABASE UNTIL ‘yyyy-mm-dd:hh24:mi:ss’;

ALTER DATABASE orgdb01
RECOVER AUTOMATIC
DATABASE UNTIL CANCEL;

4. After recovery is complete, the DBA should perform a full backup of the database using operating system methods.
5. Finally, the DBA can make the database available to the users with the alter database open resetlogs statement, simultaneously opening the database and discarding the online redo information. This step underscores the importance of the full backup taken in step 4.

ALTER DATABASE OPEN RESETLOGS;

Exercises

1. Describe in detail each type of incomplete recovery process. What are the similarities between each incomplete recovery process? How are they different?
2. How does the DBA open the database while simultaneously discarding redo information?

Incomplete Recovery and Re-Creating the Control File

Executing database recovery can be done with a control file other than the "live" one currently being used. If the DBA should need to perform incomplete recovery of the database without the control file that existed on the database, the DBA can create a new one using the create controlfile statement. In order to create a new control file to replace one that was lost in the media failure, the DBA should issue the create controlfile statement and specify all online redo logs and all datafiles that are part of the Oracle database in the create controlfile statement. There are two special options that should also be included. The first is resetlogs, used to reset the online redo log sequence number in order to make the control file current and therefore usable with the recovery effort, and the other is the archivelog option to inform Oracle that archiving is enabled. An example create controlfile statement may look similar to the statement in the following code block:

Once this control file is created, the incomplete database recovery can commence using the steps outlined in the prior discussion. For refreshing, the list of steps required, including the creation of a control file, are listed as follows:

1. Re-create the control file for the database using the create controlfile resetlogs archivelog statement.
2. Prepare the database for incomplete recovery by disallowing user access and mounting the database to only one instance.

SHUTDOWN ABORT;
ALTER DATABASE orgdb01 MOUNT EXCLUSIVE;

3. Recover all datafiles for all tablespaces using commands or utilities provided by the operating system.
4. Execute the incomplete recovery with the alter database recover database statement, specifying the appropriate incomplete recovery method, depending on the needs of the DBA in the until clause of that statement.

ALTER DATABASE orgdb01
RECOVER AUTOMATIC
DATABASE UNTIL scn;

ALTER DATABASE orgdb01
RECOVER AUTOMATIC
DATABASE UNTIL ‘yyyy-mm-dd:hh24:mi:ss’;

ALTER DATABASE orgdb01
RECOVER AUTOMATIC
DATABASE UNTIL CANCEL;

5. After recovery is complete, the DBA should perform a full backup of the database using operating system methods.
6. Finally, the DBA can make the database available to the users with the alter database open resetlogs statement, simultaneously opening the database and discarding the online redo information. This step underscores the importance of the full backup taken in step 4.

ALTER DATABASE OPEN RESETLOGS;

Database Recovery Using a Backup Control File

In some cases, the DBA may instead need to perform a database recovery using a backup copy of the control file. The syntax for this option is to add the using backup controlfile clause to the alter database recover database statement used when the statement commencing incomplete recovery is issued. Also, the step for re-creating the control file should be eliminated. The steps for this process are listed as follows:

1. Prepare the database for incomplete recovery by disallowing user access and mounting the database to only one instance.

SHUTDOWN ABORT;
ALTER DATABASE orgdb01 MOUNT EXCLUSIVE;

2. Recover all datafiles for all tablespaces using commands or utilities provided by the operating system.
3. Execute the incomplete recovery with the alter database recover database statement, specifying the appropriate incomplete recovery method, depending on the needs of the DBA in the until clause of that statement.

ALTER DATABASE orgdb01
RECOVER AUTOMATIC
DATABASE UNTIL scn
USING BACKUP CONTROLFILE ‘/u01/oracle/bkp/backup.ctl’;

ALTER DATABASE orgdb01
RECOVER AUTOMATIC
DATABASE UNTIL ‘yyyy-mm-dd:hh24:mi:ss’
USING BACKUP CONTROLFILE ‘/u01/oracle/bkp/backup.ctl’;

ALTER DATABASE orgdb01
RECOVER AUTOMATIC
DATABASE UNTIL CANCEL
USING BACKUP CONTROLFILE ‘/u01/oracle/bkp/backup.ctl’;

4. After recovery is complete, the DBA should perform a full backup of the database using operating system methods.
5. Finally, the DBA can make the database available to the users with the alter database open resetlogs, simultaneously opening the database and discarding the online redo information. This step underscores the importance of the full backup taken in step 4.

ALTER DATABASE OPEN RESETLOGS;

Exercises

1. How is a control file created? When is it useful to do so in conjunction with incomplete recovery? What is the syntax for recovery using a control file backup?
2. What dynamic performance views may be used to determine the status of database recovery for the database and for the datafiles?

Chapter Summary

This chapter covers several aspects of database recovery related to archiving and backup methods on a 24-hour database. The three topics covered include complete recovery with archiving, incomplete recovery with archiving, and online database backups for databases that require 24-hour availability. This chapter covers about 30 percent of the material tested in OCP Exam 3.

The first area of discussion is online database backup facts and methods for databases that must be available 24 hours a day. This section presents methods for distinguishing online backups from offline ones. As a point of fact, an offline database backup is usually just that--a backup of the database. Thus, when the DBA is examining a full database backup, it is usually one that contains all datafiles, redo log files, control files, and parameter files. A full backup is usually taken while the database is offline so that the backup is read consistent to a single point in time. Online database backups, however, are slightly different. An online backup is usually taken tablespace by tablespace. As such, the online backup usually only consists of the datafiles for each tablespace, rather than all datafiles for all tablespaces. Of course, there is nothing that says the DBA cannot take a backup of all tablespaces one at a time or in parallel on each occasion that online backups are performed.

In order to take online backups of the database, archiving of redo logs must be used. The database is open, available, and accepting changes during the entire time a hot backup is taking place. In order to guarantee that the changes made to the tablespace while the backup took place are kept, it is required that the DBA archive redo logs that were taken during the operation of hot backups. Prior to taking the hot backup, the DBA should issue the archive log list command from Server Manager in order to determine the oldest online redo log sequence that should be saved in conjunction with the online backups being taken. Once the tablespace backups are complete, the archive log list command should be issued again, followed by a log switch to ensure that the current redo log entries made for the tablespaces backed up are archived properly for use by the backups should recovery be necessary.

The steps to the process are as follows. Execute archive log list from Server Manager. Note the value for "Oldest online log sequence." This is the oldest redo log required for using the online backup. Execute alter tablespace name begin backup from Server Manager. This step prepares the tablespace for online backup. Copy the datafiles for that tablespace using operating system commands or third-party products. Be sure the copy resides on a disk other than the production datafiles themselves. Execute alter tablespace name end backup from Server Manager. This step completes the online backup process for that tablespace. Repeat for all tablespaces to be backed up. Execute archive log list again from Server Manager. Note the value for "Current log sequence" this time. This is the last redo log required for using the online backup. Issue an alter system switch logfile to cause Oracle to create an archive of the current redo log. This archive should then be stored in the LOG_ARCHIVE_DEST area. If desired, copy the archives associated with the backup to tape. Create a copy of the control file. This is done with the alter database backup controlfile statement.

The final step in the online backup of a database is an important one that merits special consideration. There are two different types of backups of database control files. The first is a backup created of the actual control file, ready for use on the Oracle database. Should the production version of the control file be destroyed, the DBA can simply put the backup in place and move forward. The alter database backup controlfile to filename statement can be used in this case to ensure that a usable backup copy of the control file is available in the event of an emergency. The other option is to use a special keyword in place of filename in the alter database backup controlfile statement. This keyword is called trace. The use of this keyword allows the DBA to obtain a special backup of the control file. Rather than backing up the control file itself, this statement backs up a copy of the script that can be used to create the control file. If the DBA has space considerations or wishes to create the control file on a database running under a different operating system, this option will provide the backup required.

The next section of discussion in this chapter is complete recovery with database archiving. To perform complete recovery, there are two things required. The first thing required is the backup. The discussion takes into account that there are two different types of database backups that a DBA might use to execute a database recovery: the offline backup and the online backup. Complete recovery also requires a complete set of archived redo logs from the period of time the backup was taken to the present. In addition, the DBA may require the archived redo log information taken while the online backup was taking place if online backups are used on the database.

The first step required in complete database recovery using offline backups and archived redo logs is to make the database unavailable to the users. This step can be accomplished with the shutdown abort statement executed within Server Manager. The database cannot be available to the users during recovery from offline backups because a full database recovery is required to bring the contents of the database up to the point in time the media failure occurred, which requires Oracle to write a lot of redo information. The same condition is not required for database recovery from online backups, however, because database recovery from online backups need only consist of tablespace recovery, which means the rest of the database can be available for use during the period of time the recovery is taking place.

Once the database is offline (if it needs to be), the DBA needs to handle the restoration of the database files from backup. This step requires the DBA to replace the files lost in the database media failure with backed-up copies using operating system commands and/or utilities. If need be, the DBA may also need to replace the failed media device, although this is not always required. In some cases, the DBA instead might simply move the files onto other disks.

When the task of restoring backed-up copies of lost files to their proper places on replaced disks is complete, the DBA can then set forth with the task of mounting (but not opening) the database if offline backups were used, or simply leaving the database available for use if online backups were used. If the DBA does move the files to another disk, it is at this point that the DBA should issue the alter database rename file statement to change the Oracle control file so that Oracle will know where all the physical database components are.

At this point, the task of replacing lost or damaged files with backup copies is complete. The second part of complete recovery can now begin. This is the part where archived redo is applied to the backups to make the database consistent to the point in time that the database failed. This step requires the DBA to issue an alter database recover database command, in the case of complete recovery from offline database backups, or the alter database recover tablespace command in the case of recovery from online backups. Oracle will expect that the archived redo information will be located in the place on the machine specified by the parameter called LOG_ARCHIVE_DEST in the init.ora file. If automatic archiving is not used, or if another location contains the archives, the DBA should instruct Oracle to use another location by specifying the pathname containing the archived redo information with the from clause of the alter database recover statement.

Application of archived redo logs is handled interactively by Oracle. The database prompts the DBA to supply the name of the next archive to apply, while also issuing a suggestion for which redo log to apply. This suggestion is based on the V$LOG_HISTORY dynamic performance view, the LOG_ARCHIVE_FORMAT parameter, and the LOG_ARCHIVE_DEST parameter. If the DBA desires, he or she can specify Oracle to apply its own suggestions automatically during database recovery by including the automatic keyword in the alter database recover statement.

All archived redo information will be applied by Oracle to execute a complete recovery. When Oracle is complete, the DBA can open the database for usage by issuing the alter database open resetlogs statement. The resetlogs option indicates that the DBA wishes to reset the log sequence number for the database online redo logs to 1. It is generally recommended that prior to opening a newly recovered database, the DBA take a full backup of the recovered database in the event of an emergency.

As a point of reference, to minimize downtime during database recovery, it is faster to use online tablespace backups. This is because tablespace recovery runs faster than full database recovery required for offline backups, and also allows the DBA to leave the unaffected parts of the database available to users while fixing the damaged portion.

The final subject for discussion in this chapter is facts and methods for incomplete recovery using archived redo information. There are several situations that may force the DBA to recover the database to a point in time in the past using incomplete database recovery methods. Some of these reasons include an errant batch process or feed that contains bad data, which causes some improper information to enter the database. Another situation where incomplete recovery may occur simply involves bad luck, where the DBA loses an archived redo log file or finds that the file has been damaged. In any event, incomplete recovery is the recovery of a database to some point in the past.

One of the things that makes incomplete recovery involving archived redo logs better than incomplete recovery involving backups that do not work in conjunction with archiving, or backups on a database that does not use archiving, is choice. The DBA can choose the point in time to which incomplete recovery will occur (barring loss of archived redo logs) when archiving is used. In contrast, the only "point in time" to which a database can be restored is the point in time when the last backup completed.

To accomplish incomplete recovery, the DBA requires two things. The first is a complete backup of the system. Incomplete recovery is a complete database recovery operation. Incomplete recovery from online tablespace information, and the tablespace recovery operation, is not allowed. This is because incomplete recovery of an individual tablespace would put the entire database into a read-inconsistent state. The second item required is archived redo log information. As stated, incomplete recovery may be the result of lost archived redo logs. If, for example, consider that there are three archived redo logs for a database, numbered 1, 2, and 3, and each archive contains information for 30 transactions, whose SCNs are from 0–9, 10–19, and 20–29. If archive sequence 3 is lost, the DBA can only recover the database through SCN 19, or archive sequence 2. If 2 is lost, then the DBA can only recover the database through SCN 9, and if archive sequence 1 is lost, then no archived redo log information can be applied.

There are three types of incomplete recovery. Those three types are change-based, time-based, and cancel-based. The change-based incomplete recovery allows the DBA to specify a system change number (SCN) of a transaction at which, once its full set of committed operations are applied, Oracle will stop running the database recovery automatically. The time-based incomplete recovery allows the DBA to specify a date and time later in the past than the database backup, to which the database will be recovered. When Oracle applies redo information to that point in time specified by the time-based recovery, Oracle will automatically stop the recovery process. The final type of database incomplete recovery is the cancel-based recovery. This recovery allows the DBA to arbitrarily decide when to end the recovery by issuing a cancel command when Oracle prompts the DBA to enter the name of the next archived redo log entry to be applied. This option gives the DBA complete control over when to end a database backup, but requires the DBA to interact with Oracle during the recovery at all times.

As with complete recovery using offline backups, the DBA cannot allow users to have access to the database while the incomplete recovery is taking place. As such, the database should be mounted in exclusive mode to prevent other instances from opening it, but not opened by that instance either. The DBA can then place all datafiles in place on their respective disks from backup and issue the alter database recover database until statement. Following the until clause, the DBA can specify the exact form of incomplete recovery that will be employed, either using change followed by the SCN, a date and time, or simply the cancel keyword. If the DBA wants to limit the interactive portion of recovery where Oracle prompts the DBA for names of archived redo log files to apply, the DBA can use the automatic keyword in the alter database recover statement, except in the case of using the cancel-based recovery option. When using cancel-based recovery, the DBA must interact with Oracle during application of redo logs in order to issue the cancel command.

After recovery is complete, it is recommended that the DBA perform a full database backup before allowing the users to access the database. After that, the DBA can allow the users access to the database by executing the alter database open resetlogs statement. Using the resetlogs keyword means that Oracle will reset the log sequence number to 1, effectively discarding all archived redo information taken before the recovery and underscoring the importance of taking a backup before allowing user access to the database again.

Of particular importance in running incomplete database recovery is the manipulation or creation of a new control file in the event that a new one is required. If the control file is damaged, and there is no backup to it that will allow the DBA to perform complete recovery on the Oracle database, the DBA may be forced to create a new control file as part of the recovery process. This process is accomplished with the use of the create controlfile statement. In it, the DBA will identify the location of all redo logs and datafiles, as well as specifying the resetlogs option to discard any online redo information (also to make the control file usable for recovery). The DBA should also remember to specify the archivelog option because, after all, the database is archiving redo. If the DBA ever used the alter database backup controlfile to trace option for control file backup, the script backed up could be modified (if necessary) and executed in lieu of formulating a new one.

Finally, in the event that the DBA is using an actual backup of the control file and needs to execute recovery from it, the DBA can issue a the alter database recover statement using a special clause that tells Oracle to use the backup control file. The full statement would be alter database dbname recover database using backup controlfile ctlname.

Two-Minute Drill

1. Prepare the database for backup using the alter tablespace begin backup statement.
2. Make backups of the tablespace datafiles using operating system commands.
3. End the tablespace backup using the alter tablespace end backup statement.

1. Have the database mounted in exclusive mode but not opened.
2. Restore all backup copies of datafiles.
3. Specify new locations of datafiles if any were moved.
4. Execute the recover database operation, applying appropriate archived redo logs.
5. Take a complete backup of database.
6. Open the database using the resetlogs option to discard archives and reset sequence number.

1. The database can be open for use, but the damaged tablespace must be offline.
2. Restore all backup copies of datafiles.
3. Specify new locations of datafiles if any were moved.
4. Execute the recover tablespace operation, applying appropriate archived redo logs.
5. Bring the tablespace online.
6. Take an online backup of tablespace.

1. Have the database mounted in exclusive mode but not opened.
2. Restore all backup copies of datafiles.
3. Specify new locations of datafiles if any were moved.
4. Execute recover database operation, applying appropriate archived redo logs. Use the appropriate incomplete recovery option: Cancel-based uses until cancel, change-based uses until change scn, time-based uses until ‘yyyy-mm-dd:hh24:mi:ss’.
5. Take complete backup of database.
6. Open the database using the resetlogs option to discard archives and reset sequence number.