Oracle Database Administration – Lab #4
Redo Log and Control File Multiplexing, Archive Redo Log Files,SPFile
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.
System Object File Name Size
New Redo Log Files USERXXXredo01b.log Same as existing Redo Log Files USERXXXredo02b.log
Control File #3 USERXXXcontrol03.ctl Same as existing Control Files.
STEPS:
1. Startup your instance. Connect as the DBA user for your database.
2. Multiplex the Redo Log Files for your database
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.
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
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.
Requery V$LOG and V$LOGFILE as shown above.
4. Resize Redo Log Files
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.
Requery V$LOG and V$LOGFILE as shown above.
5. Drop a Log Group
Drop Redo Log Group 2 from your database.
Query V$LOG as shown above to verify the result.
Remove the operating system files for Redo Log Group 2.
6. Add a new control file
Add a third control file (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. 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).
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
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.
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
Locate this file in the same location as your initUSERXXX.ora file.
9. Shut down the database
