cmis565_lab4.htm; updated June 6, 2013


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. 



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


New Redo Log Files



Same as existing Redo Log Files

Control File #3


Same as existing Control Files.



Tasks for this Lab


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


2.  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;


3.  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. 

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


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



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


4.  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 4M 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.

5.  Resize Redo Log Files (2 points).

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

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


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.  Turn Off Automatic Archive Logging (1 point). 

a.    Take the steps necessary to turn OFF automatic archive logging.

b.    Display the results of this query to demonstrate automatic archive logging is off. 


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.