Oracle Database Administration Lab 4

cmis565_lab4.htm; updated May 16, 2011

Oracle Database Administration – Lab #4

Redo Log and Control File Multiplexing, Archive Redo Log Files, SPFile (10 points)

Situation: In this laboratory you multiplex your database’s redo log files and control files, work with archiving redo log files, and create a SPFile. You may elect to complete the laboratory individually, or continue to work in your laboratory team. Teams only need to submit one copy of their laboratory report.

Use Notepad, Wordpad, or Microsoft Word to document your solution to the tasks in the laboratory.

Label your answer to each clearly by typing the number/letter (example 2a) in bold print.
Show each command you execute and the computer's response (example response: Database Altered, Tablespace Created, etc.). If you don't show the commands you execute, then your answer will be counted as wrong.
Print your output using 10 point Courier or Courier New font (so the output lines up and is easy to read).

· Format your output as necessary to make it easy to read. Do not submit for grading anything you would not submit to your boss for review in an actual work environment, i.e. in other words, do not turn in sloppy work that is difficult for me to grade.

Database File Locations: The modifications that you make to a database should comply with the optimal flexible architecture guidelines. You must decide where to store the new redo log files and control file to be added to your database.

System Object

File Name

Size

New Redo Log Files

USERXXXredo01b.log

USERXXXredo02b.log

Same as existing Redo Log Files

Control File #3

USERXXXcontrol03.ctl

Same as existing Control Files.

Tasks for this Lab

1. Startup your instance. Connect as the DBA user for your database.

2. Multiplex the Redo Log Files for your database (2 points).

a. Add an additional Redo Log File) to each of the two existing Redo Log Groups for your database (see the above table for the file names; use the same file size as the existing Redo Log Files) so that you will end up with two Redo Log Files that are identical within two Redo Log Groups.

You must determine where to locate the files for each group.
In order to develop an OFA-compliant Redo Log File Grouping, you may need to both add Redo Log Files and to relocate the existing Redo Log Files.
NOTE: When you add new members to the groups, one or more of the new members may be listed as INVALID—switching log file groups 2 or 3 times will CLEAR the INVALID status.

b. Query both V$LOG and V$LOGFILE to verify the result as follows:

SELECT group#, members, status, bytes FROM v$log;

COLUMN member FORMAT A50;

SELECT group#, member, type FROM v$logfile;

3. Add a Log Group (2 points).

a. Add an additional Redo Log Group with two Redo Log Files in the group. Number this Group 3 as shown in the table above. Make the files 5M in size in this group. You must decide where to locate the Redo Log Files in this new Redo Log Group (use disk drives that are available to you as appropriate). You must also select appropriate file names for the new Redo Log Files.

b. Requery V$LOG and V$LOGFILE as shown above.

4. Resize Redo Log Files (2 points).

a. Resize the Redo Log Groups such that each Redo Log File is 5M in size. Show the steps and all commands that you use accomplish this task.

b. Requery V$LOG and V$LOGFILE as shown above.

5. Drop a Log Group (1 point).

a. Drop Redo Log Group 2 from your database.

b. Query V$LOG as shown above to verify the result.

c. Remove the operating system files for Redo Log Group 2.

6. Add a new control file (1 point).

a. Add a third control file (for example, USER350control03.ctl). Before you do this, check to see if you’ve already created 3 control files. If you have, then you will be adding a fourth control file instead of adding a third one (since you only have three disk drives to use, there is no practical place to store a fourth control file—so select any of the disk drives if this applies to you). This is to give you practice creating a new control file and adding it to the database. Show the steps through that you have to complete to accomplish this task. Multiplex the control files by spreading them across the disk drives that are available to you. Check the permission settings on the new control file—since you will be the owner, the DBA group that includes the Oracle user will need permission to access the file – a setting of 660 should be sufficient (you are allowed to try other permission settings as long as the new control file is accessible when the database is restarted).

b. Startup the database. Query the V$CONTROLFILE view to display the names/locations of your control files as follows:

SELECT name FROM v$controlfile;

7. Archive Log Files (1 point).

a. Take the steps necessary to turn on automatic archive logging (ARCHIVELOG mode). You must decide where to store archive log files. List the location you selected here in your report. List all of the commands that you execute in order to turn on automatic archive logging.

b. After automating archive logging, cause the system to execute a Redo Log File switch. Query the V$INSTANCE view to determine the status of archived logs as follows:

SELECT instance_name, archiver, status FROM v$instance;

c. After you see how Archive Log mode works, then take the steps necessary to turn OFF automatic archive logging.

8. Create an SPFILE for your database (1 point). Locate this file in the same location as your initUSERXXX.ora file.

9. Shut down the database (-1 point if you forget to shutdown).

Do not leave your database running between laboratory sessions.

END OF ASSIGNMENT