HAVE THE DATABSE IN ARCHIVELOG MODE TO WORK ON THIS LAB.
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, and how to restore and recover your database after a simulated media failure.
IMPORTANT: Be sure you have the database in ARCHIVELOG mode before starting.
Creating a SPOOL session: Be sure that you start a spool session before you start executing your SQL code when working on the lab. This will be the only way to capture the results of your work and will be required for grading. Because of the nature of this lab, it is best to do the lab in one sitting. This will minimize the possibility of causing a problem with the database.
iLAB STEPS
Step 1: Take a Hot Backup of the Database
Back to Top
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 the 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, verify that 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: Back Up the Control Files and Archive the Redo Logs
Back to Top
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:DBMFALLADB104ORACLEADMINBACKUPfilename.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: Simulate a Media Failure
Back to Top
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 it for the end of the lab. Your listing should look similar to the following.
Errors in file f:dbmspringbdb1000oracleadminbdumpdb1000_dbw0_5700.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'F:DBMSPRINGBDB1000ORACLEADMINORADATADISK1USERS01DV001.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
Back to Top
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
3
4
5
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 enter key (you do not need to type anything in) 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.