0538-0540

Previous Table of Contents Next

Page 538

The steps in a cold restore are

1. Make sure that the current Oracle database instance is shut down.
2. Replace all the existing database files, control files, and redo logs with earlier versions from tape or disk.
3. From Oracle Server*Manager, do connect internal and issue a startup command. The database is now ready for use.

NOTE

The time and date stamps on all of the files from the recovery should be for the same period of time. If they are not, the database will be out of sync and will not open properly.

Full Database Recovery

In a full database recovery, also called a complete recovery, data changed since the last backup can be restored. One or more database files are restored from backup. Archive logs are then applied to them until they are in sync with the rest of the database.

The steps in a full database recovery are

1. Make sure that the database instance is shut down.
2. Restore the data file from tape or disk.
3. From Oracle Server*Manager, do connect internal and perform startup mount on the database instance.
4. Issue the recover database automatic command from within Oracle Server*Manager. Oracle Server*Manager responds by applying all the required changes to the database instance. Depending on the length of time since the last backup and the size and number of the archive logs, this wait can take a few seconds or several hours.
5. After the SVRMGR> prompt returns, issue the alter database open noresetlogs; command. The database is now completely recovered and available for use.

NOTE

There are several variations of the recover database command, including recover datafile and recover tablespace.

Time-Based Recovery

Sometimes a recovery is required, but not everything in the archive logs is necessary. Suppose, for example, that an overzealous developer deploys a job that deletes every other row in a

Page 539

transaction processing table. In this case, a full recovery will not work. Because the transactions that corrupted the table are in the archive logs, a full recovery simply restores from the last backup and processes all the transactions, including the haphazard delete. If you know that the job ran at 2:30 p.m., you can use time-based recovery to recover until 2:29 p.m. That way, the table is exactly as it appeared before the job ran. This is also called an incomplete recovery or a point-in-time recovery.

A time-based recovery is performed exactly like a full recovery, with the exception of the recover database command. The steps are

1. Make sure that the database instance is shut down.
   
2. Restore the data file from tape or disk.
3. From Oracle Server*Manager, do connect internal and perform startup mount on the database instance.
4. Issue the recover database until time `YYYY-MM-DD:HH24:MI:SS' command from within Oracle Server*Manager. This is a mask for the time and day on which the recovery should stop. Oracle Server*Manager responds by applying all the required changes to the database instance. Depending on the length of time since the last backup and the size and number of the archive logs, this wait can take a few seconds or several hours.
5. After the SVRMGR> prompt returns, issue the alter database open resetlogs; command. The database is now completely recovered and available for use.

Cancel-Based Recovery

Even if you do not know the exact time an error occurred, you might feel reasonably certain that you can isolate when to terminate the recovery based on the thread/sequence number. Perhaps there was a break in the archive logs because you had the database out of ARCHIVELOG mode for a short time, or perhaps you want more control over what archive logs are applied as part of the recovery. The solution is cancel-based recovery.

Under cancel-based recovery, you are prompted after each archive log is applied. The recovery process continues until either the recovery is complete or you enter cancel at the prompt. The prompt appears within Oracle Server*Manager as

 Specify log: [<RET> for suggested  AUTO  FROM logsource  CANCEL] 

After you enter cancel at the prompt, the recovery stops.

The steps in a cancel-based recovery are

1. Make sure that the database instance is shut down.
2. Restore the data file from tape or disk.
3. From Oracle Server*Manager, do connect internal and perform startup mount on the database instance.

Page 540

4. Issue the recover database until cancel command from within Oracle Server*Manager. Oracle Server*Manager responds by prompting you before each archive log is applied. The recovery ends when the database encounters the final archive log or when you enter cancel.
5. The SVRMGR> prompt will return. If the recovery ran until completion, issue the alter database open noresetlogs; command. If you entered cancel to end the recovery, issue the alter database open resetlogs; command. The database will be recovered until the point of completion or cancellation.

Change-Based Recovery

The change-based recovery method can be used to recovery the database to a point in time based on a specific change to the database. This method can be used if one of the archive logs are lost (maybe you forgot to back up your archive logs). In order to initial a partial or incomplete recovery using this method, you have to supply the database with the last change for the last available archive log, known as the system change number, or SCN. Each change to the database is associated with such a number. But how do you find that number? By querying the V$LOG_HISTORY view in the data dictionary, you can at least find the first number associated with the next archive log.

Suppose that you lost file #20, meaning that you can now only recover up to file #19. In order to get the last SCN in file #19 (to recover that entire archive log), you can query V$LOG_HISTORY for file #20. After you have the first SCN for file #20, subtract 1, and you would have the SCN for the last change in file #19, which we will say is 1234.

Then you could recover up to file #19 as follows :

 svrmgrl SVRMGR> connect internal Connected to an idle instance SVRMGR> startup mount ORACLE instance started Total System Global Area        95243632 bytes Fixed Size                         46384 bytes Variable Size                   70588480 bytes Database Buffers                24576000 bytes Redo Buffers                       32768 bytes Database mounted. SVRMGRL> recover database until change 1234; 

At this point, verifying that the required archive logs are in the designated archiving destination, you would enter auto when Oracle prompts for the name of the first archive log.

Sample Database Backup Scripts

The code examples in the following sections show you how to set up and execute hot and cold backup schemes. These are not highly intensive processing modules. There are certainly ways

Previous Table of Contents Next