Oracle Database Administration – Lab #8
Backup and Recovery
Situation: You have been asked to demonstrate your proficiency as a new DBA working at Bock Incorporated in the area of backup and recovery procedures. You may elect to complete the laboratory individually, or you may team up with 1 to 2 laboratory partners. If you team up with partners, you will select your own partners and you will be graded as a group and you will submit a single document as evidence of your team's work.
STEPS
1. Perform a database export
Begin by exporting your entire database. Create a subdirectory off of your /a05/student subdirectory named exports. Set the permission level for the exports subdirectory (use chmod 775 exports) – example:
mkdir /a05/student/dbockstd/exports
Ensure your database is open, then exit SQLPlus. Use the Data Pump export utility to complete the export.
Print out a copy of the contents of the parameter file used for the export.
IMPORTANT: Confirm that the export.dmp file exists on your exports subdirectory and that you received the successful termination of the export message.
Use the exp (Export) utility to export the Invoice and Invoice_Details tables (that you created in an earlier lab) to a dump file named MyTest.dmp.
If you used a parameter file in performing the Export, print out a copy of the contents of the parameter file used for the export, or show the parameters used as part of the exp command.
2. Complete full cold backup . Normally this is accomplished via the use of a system backup utility such as tar or cpio to backup to tape or to backup hard disk space. We will simulate backup of the complete system by backing up to disk.
You have disk drive /a05 available. You can use it for a backup disk drive, OR you can use another disk drive as directed by your instructor during class. Create a subdirectory named backup. Set the permission level for the exports subdirectory (use chmod 775 backup) – example:
mkdir /a05/student/dbockstd/backup
Inside your backup directory create five directories, each named a01, a02, a03, a04, and a05 and set the permission level to 775 for each of these directories.
Make a listing of all files that comprise your database including the disk drive and directory location for each file – this listing is ABSOLUTELY CRITICAL. The listing should NOT include scripts, dumps, etc, only the control files, datafiles (tablespaces), redo log files, and parameter files. Use the information in the data dictionary to create your listing (an example would be to SELECT from the V$DATAFILE view) – do NOT use the operating system level ls command.
Next, shutdown the database.
Copy all required and recommended files to the appropriate directory within the backup subdirectory by using the UNIX cp (short for copy) command. Example command for one of the files that is normally located on disk drive /a01 – note the file is copied to backup/a01 so that if you need to restore the file, you will know where the file is supposed to be located.
cp /a01/student/dbockstd/oradata/DBOCKSTDdata01.dbf
/a05/student/dbockstd/backup/a01/DBOCKSTDdata01.dbf
Confirm that the backup took place. You might also note any differences in ownership of files between your original subdirectory (or subdirectories) where your database files are located and the backup subdirectory. The backup subdirectory files all belong to you as your login account name where your database files on your original subdirectories belong to the owner named Oracle. Since both you and Oracle are members of the group named dba, you must ensure that the group dba has read and write privileges for the database files if you use the backup.
3. Performing online partial database backup . This enables a partial backup while the database is available to users. Backup the tablespace named DATA01. Note that this tablespace may include one or more data files in your database.
Exit the database to the operating system (do not use host). Create a subdirectory to hold partial database backups in disk drive /a05 OR thedisk driveyou are using for backup. Create a subdirectory named partialbackup. Set the permission level for the exports subdirectory (use chmod 775 partialbackup) – example:
mkdir /a05/student/dbockstd/partialbackup
Connect as SYS or as the DBA of your database and startup the database if it is not already running. Verify the ARCHIVELOG mode by querying the v$databasetable as above. You MUST be in ARCHIVELOG mode to continue. Begin the partial database backup with the ALTER TABLESPACE command.
ALTER TABLESPACE DATA01 BEGIN BACKUP;
Host to the operating system and copy the datafile(s) that comprises the DATA01 tablespace to your partialbackup subdirectory. You may need to set permission access privileges for this new subdirectory. Verify that the file(s) was copied satisfactorily.
Exit back to SQL*Plus and issue the command to end the backup.
ALTER TABLESPACE DATA01 END BACKUP;
Backup the control files to protect against loss of all copies of the control file. This is necessitated by any command that changes the database configuration. Backup your control files to a file named backup.ctl on the partialbackup subdirectory with the ALTER DATABASE command.
Note: To recover a control file: (1) shut down the instance in either normal or immediate mode, (2) copy the good control file using a new name, (3) edit the p-file to delete the name of the damaged control file and add the filename of the copy, and (4) restart the instance. You will not recover a control file during this laboratory.
4. Perform a Database Import . Now you will assume that part of the database has been damaged, specifically the Invoice and Invoice_Details tables. You will drop these damaged tables and recover by importing the tables from the database export that was created earlier.
Connect as the DBA for your database because the DBA user created and therefore owns the tables that are damaged. To demonstrate that the tables are restored, first alter the tables as they currently exist by inserting some new rows. Insert a new test row in the Invoice table and a corresponding row in the Invoice_Details table. Query these two tables to display the data you entered.
b. Now you will assume that the tables are damaged - execute commands to drop the Invoice and Invoice_Details tables. This will also drop any indexes that exist for these two tables, but it will not drop the cluster index. Drop the tables in the order shown here because of referential integrity constraints.
drop table Invoice_Details;
drop table Invoice;
Exit to the LINUX prompt and use the imp (Import – NOT the Data Pump Import) command to import the tables. Note the connection to Oracle using the user account system/manager. You need to use the password for the user named system that is correct for your database (If you don't remember the password for SYSTEM, then connect as SYS and alter the user SYSTEM). You will import the Invoice and Invoice_Details tables.
Connect to the database as the DBA user. Query the two tables – the rows you added earlier in part 4a above should not be present – this demonstrates that you have recovered to a specific point in time.
5. Destroy and restore the database. We will simulate a catastrophic failure, such as a fire.
a. Take in a deep breath and destroy your database by deleting all of the files that are part of the database in your original subdirectories – this includes the following: all control files, all tablespace files, all redo.log files (not the archived ones), and the init.ora file.
b. Restore the backup files to their original subdirectories. Example command (this is typed on one line):
cp /a05/student/dbockstd/backup/a01/DBOCKSTDdata01.dbf /a01/student/dbockstd/oradata/DBOCKSTDdata01.dbf
c. Set permissions for the restored files on your original subdirectories to 775 to enable Oracle to access them if this is necessary.
chmod 775 *
d. Connect to your database as SYS and startup the database in open mode. Show the output of the startup command to demonstrate the database is restored.
6. Shut down the database
