Database Architecture Exercises - Control file Redo logfile Examples

The Oracle database uses two very important files for database support, stability, and recovery.  The Control file is one of the most important files in the database, as it is the file that keeps track of where everything is and where it should be at any given time.  Since the database itself only needs one Control File to operate with, multiplexing the Control File is a very important part of the Oracle Architecture.  In this lab, you will create a third Control File for the database.
Another essential file structure for successful operation of an Oracle database is the Redo Log file.  This file contains listings of every transaction that takes place in the database, and it is essential in database recovery and restoration.  Log Files exist in Log Groups, and each Log Group must have at least one file--although it is recommended that there be a minimum of two per group and that they exist on separate disks.  In this lab, you will also create a new Log Group containing two files.
The following steps will outline the processes needed to complete this lab.  Keep in mind that the file paths shown in the lab specs may vary slightly from your file paths, depending on the database instance you have.  The expected results examples given in each step are provided as a guide only.  The specific results of your work may vary slightly, but should be similar enough to determine whether or not you have successfully completed the step.

VERY IMPORTANT – Setting up for your lab
Creating a SPOOL session: Be sure that you start a spool session before you start executing your SQL code when working on the lab.  Your SPOOL file name should be DATABASE_lab3. If you do the lab in several sessions, be sure that you use a different file name so as not to overwrite the existing file (for example DATABASE_lab3, DATABASE_lab3a, DATABASE_lab3b etc.)

L A B   S T E P S

Part 1 -- The Control File: Adding a new Control File Step 1: Locating the existing control files

First, from the Oracle Desktop you will want to access the SQL*Plus editor and connect to your database instance as SYS AS SYSDBA, using the appropriate password and host string.  It is important to do this from the Oracle Desktop, as you are going to need to be able to access your database directory folders and files later in the lab, and this can only be done from the desktop.
Where is the existing control file located and what is the name?

Hint: Query the dynamic performance view V$CONTROLFILE or V$PARAMETER, or execute the SHOW PARAMETER command to display the name and location of the control file.

Your response will look similar to this:

NAME
-------------------------------------------------------------------------------
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK1\ctl1001.ctl
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\ctl1002.ctl
2 rows selected.

Step 2: Add a new control file

You need to add a new control file to your existing control file, using the directory disk3.  Name the new control file ctltest3.ctl.  You also need to make sure that the Oracle server is able to find and write to the new control file.

Hints:

• In your current SQL*Plus session, issue the command to shut down the database.
• On your mapped network drive, go to the DISK1 directory and physically copy the existing control file in that directory to the directory DISK3. Rename the file to CTLtest3.ctl
• Next, go to your initol###.ora file found in the PFILE  directory of your mapped drive.  Modify the “Control files” parameter to include the new file name, so that your parameter listing looks similar to the one below.  (You will be adding a third control file path in the parameter listing for the control file).  Your new listing should look similar to the one below.

Control_files=(‘F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK1\ctl1001.ctl’,
                    ‘F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\ctl1002.ctl',
                    ‘F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\ctltest3.ctl’)

• Save the control file.
• Now go back to yrou SQL*Plus session and issue the STARTUP command to restart the database, using your init.ora parameter file with the new setting.  Your startup command should look similar to the following:

startup pfile= O:\oracle\admin\pfile\init###.ora

Step 3: Verify the new control file

Query the dynamic performance view v$controlfile to verify that all three control files are being used. 
Your response will look similar to this:

NAME
-----------------------------------------------------------------------
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK1\ctl1001.ctl
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\ctl1002.ctl
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\ctltest3.ctl
2 rows selected.

Step 4: Recovering from a corrupted Control file

Sometimes stuff happens, and when it does, you need to be able to recover from it with as little delay in operations as possible.  It is not a common occurrence, but it is possible for a Control File to become corrupt.  There is really no repair tool for this when it happens, and often it requires replacing the corrupt file or, at a minimum, getting rid of it.  Since Control Files, like some other Oracle files, are automatically mirrored by the system, getting rid of a Control File will not hurt the operation of the database, as long as there is one good file for the system to use.  For this part of the lab, you are going to corrupt the Control File you just added and then get rid of it.

• To begin, issue a SHUTDOWN IMMEDIATE (make sure you are logged in as the SYS user).
• Next, go into your mapped directory structure and find the ctltest3.ctl file in the Disk3 folder.  Right-click on the file and select Open With => Notepad from the options list.  When the file opens, press the space bar once and then close and save the file.  You have just corrupted your database!
• Now, once again, start up your database using the init.ora file for your instance, as you did in step 2.  What happened?  Did the database start?  Go find your alert log (Hint: bdump folder in your OFA structure), copy the part of the entry that references the problem (look down at the bottom of the file), and save it to a text file to be turned in with the lab work.
• Ok, so now we need to fix this problem.  Execute the following steps to recover your database from this problem.
  
  
  • First, shut down the database by issuing a SHUTDOWN IMMEDIATE.
  • Next, go back into your init.ora file in your mapped directories and remove the line that you just added that has the path to the ctltest3.ctl file.  Make sure that you have both original files in single quotes with a comma separating them, and that there is not a comma at the end before the closing parenthesis.  Close the file and save it.
  • Now go back to the SQL*Plus editor and start up your database using the init.ora file, as you have done before.
  
  
  

Your database should open this time and be ready for business.  If this is not the case, then retrace your steps; check the Alert log and, if need be, email your instructor.  Once you have your database back up and running, you can delete the ctltest3.ctl file in DISK3, as it is no longer needed.

Part 2 -- The Redo Logs: Working with Redo Logs Step 5: Find the location of existing Redo Log files and Groups

Make sure you are still connected to your database as the SYS AS SYSDBA user.
List the number and location of existing log files and display the number of redo log groups and members your database has:

Hints: 

• Query the dynamic performance views V$LOGFILE to display the location.
• Use the dynamic performance view V$LOG to display the number of redo log file groups and members.

Your response will look similar to this for A.

MEMBER
--------------------------------------------------------------------
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\LOG1A01.RDO
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\LOG2A01.RDO
2 rows selected.

Your response will look similar to this for B.

GROUP#  MEMBERS
----------    ------------
     1                   1
     2                   1
2 rows selected.

Step 6: Determine current Archive Mode

In which archive mode is your database configured?

Hint:  Query the dynamic performance views V$DATABASE to show the database mode.

Your response should look similar to this:

LOG_MODE
---------------
NOARCHIVELOG
1 row selected.

Step7: Determine if Archiving is enabled

Is archiving enabled?

Hint: Query the dynamic performance views V$INSTANCE to verify if the archiving is enabled.

Your response should look similar to this:

ARCHIVE
---------
STOPPED
1 row selected.

Step 8: Adding Redo Log Members

Add a redo log member to each group in your database located in the same directory, using the following name conventions:  For group 1, add a new member called log1b01.rdo and place it in disk 3.  For group 2, add a new member named log2b01.rdo and place it in disk 2 

Hint: Execute the ALTER DATABASE ADD LOGFILE MEMBER command to add a redo log member to each group. Then query the dynamic performance views V$LOGFILE to verify your result.

Your response should look similar to this:

Database altered.
Database altered.
   GROUP# STATUS  MEMBER
---------  -------- ----------------------------------------------------------------
            1 STALE    F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\LOG1A01.RDO
            2              F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\LOG2A01.RDO
            1 INVALID  F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\LOG1B01.RDO
            2 INVALID  F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\LOG2B01.RDO
 4 rows selected.

Step 9: Add a new Log Group

Create a new redo log group (it will automatically assume the number 3) that will contain to files, each one 4M in size.  Place the first log file named LOG3A04.RDO in directory disk4.  Place the second log file named LOG3B01.RDO in directory disk 5.  Finally, verify that the new log group and files are there, by querying the appropriate data dictionary view.

Hint: Execute the ALTER DATABASE ADD LOGFILE GROUP command to create a new group.  Remember that you can add both files to the new group at one time.  To verify your work, query the performance view V$LOG.  

Your response should look similar to this:

Database altered.

Now, to verify your work, query the performance view V$LOG.

Your response should look similar to this:

       GROUP#    MEMBERS
   ----------   ----------
               1               2
               2               2
               3               2

3 rows selected.

This concludes the Lab for Week 3. Be sure to enter a SPOOL OFF command to end your spool session, to save your output file.