Oracle Database Hot Backup Practices

An open (hot) database backup is made with database running in ARCHIVELOG mode. This kind of backup can be performed while the database is open and the users are working with the database, thus it is also called online backup. Online backups are done by placing a tablespace in backup mode, copying it somewhere else on disk or tape, and then taking the tablespace out of backup mode. After backing up tablespaces, you can backup the control file. Upon completing this lab exercise, you will know how to take a hot backup of your database, how to restore and recover your database after a simulated media failure.
IMPORTANT: Be sure you have done the Hands-On exercise before doing this lab to or you will not be able to complete the lab. The exercise sets up the environment by putting the database in ARCHIVELOG mode.
Creating a SPOOL session: Be sure that you start a spool session before you start executing your SQL code when working on the lab. Your SPOOL file name should be DATABASE_lab7.

L A B   S T E P S

Step 1: Taking a Hot Backup of the Database

A hot backup consists of datafiles, controlfiles, and archive redo logs. An online backup never contains online redo logs. First you need to backup the datafiles by putting each tablespace in backup mode. To do this you will use following command:

ALTER TABLESPACE tablespace_name BEGIN BACKUP;

You want to backup all of your available tablespaces with the exception of the temporary tablespace. When you are finished you should have five alter statements and have all five tablespaces in backup mode.
To verify that each tablespace is in backup mode query the V$BACKUP dictionary view to get the file number and status for each tablespace. Your result set should look similar to the following.

FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE
5 ACTIVE

Next you need to take care of backing up the actual data files. Since you are in a Windows environment you can simply copy/paste the actual data files from their current location in the directories under the ORADATA directory in your OFA structure to the directory named BACKUP. When finished you should have all five of the datafiles in the directory call BACKUP.
Now you need to take the tablespaces out of backup mode. To do this you will need to use an ALTER TABLESPACE statement again, only this time you will need to specify END BACKUP after the tablespace name. When finished then verify none of the tablespace are still in backup mode by querying the V$BACKUP view again. Your result set output should look similar to the following.

FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE
5 NOT ACTIVE

The final step in this process will be to issue an ALTER SYSTEM SWITCH LOGFILE command several times to create some archived log files for the actions you just took the database through.

Step 2: Backing up the Control files and Archiving the Redo logs

Now you need to get backups of the control file and another set of archived log files. To back up the control file you will need to issue a ALTER DATABASE command specifying to BACKUP CONTROLFILE and then give the complete directory path to the backup folder. Your complete command should look similar to the following (remember to use your database instance name, file path and a file name for the backup).

ALTER DATABASE BACKUP CONTROLFILE TO 'F:\DBM\FALLA\DB104\ORACLE\ADMIN\BACKUP\filename.bkp';

Go to your BACKUP folder in the OFA structure for your database instance and verify that the file is there.
Now issue a logfile switch command enough times to cycle through all three log groups.

Step 3: Simulating a Media Failure

Now you are going to simulate a media failure. This type of failure can happen when a hard drive crashes or becomes corrupt, or a file becomes corrupt and unusable.
a. First shut down the database. To save time you can issue a SHUTDOWN ABORT.
b. Now go to your OFA structure and rename the USERS01.dbf file to USERS01.dbf.old. If you get an error message stating that the file is in use by another application and access is denied then exit out of SQL*Plus to stop the session and try to rename the file again.
c. Next, attempt to start your database instance up again using your initdb###.ora file. You should get an error at this point so shutdown the instance again.
d. Go to your bdump directory of your OFA structure and open your alert log file. Find the place within the log listing that addresses the error caused by not being able to find the file you changed the name of. Copy that listing into notepad and hold on to if for the end of the lab. Your listing should look similar to the following.

Errors in file f:\dbm\springb\db1000\oracle\admin\bdump\db1000_dbw0_5700.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\USERS01DV001.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Step 4: Restore and Recover the Database

Now we are going to do some magic and get the database back up and running using the backups that you created earlier.
a. Make sure that you have shutdown the database instance as instructed in Step 4. Copy just the data files (the five files with the .dbf extension) from the backup directory to the correct disk folder in your OFA structure. Be sure that you get them back in the disk folder that they belong in.
b. Now attempt to start your database instance back up using your initdb###.ora file. There still seems to be a problem so shutdown the instance again.
c. Now attempt to start your database instance in MOUNT mode using your initdb###.ora file. Query the V$RECOVER_FILE dictionary view to find out which file has a problem and what the error is. Your result set should look similar to the following.

FILE# ERROR
---------- -------
1
2
4

d. Now we need to recover the database and get back to where we want to be. Issue a RECOVER DATABASE command. When prompted for archived redo, hit the return until Oracle cannot find any more archived redo logs to apply. At this point you should see a message stating "Media recovery complete".
e. Now open the database using an ALTER DATABASE OPEN command. Congratulations, you have just brought your database back from the dead.
This concludes the Lab for Week 7. Be sure to enter a SPOOL OFF command to end your spool session and save your output file.