DBA Fundamentals

You, as a DBA, are responsible to maintain and relocate the Redo Log files in order to distribute data among multiple hard disks to increase I/O performance. Your job�s responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

In this exercise we will learn how to resize the online redo log files. We will learn how to archive the Online Redo Log files manually. Also, we'll learn to understand how the checkpoint, Archive, Log Writer, and DB Writer processes work.

Connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
 

Resize online redo log files
The online redo log files are used to store Oracle user�s entries; and once it is full, the file will be archived to an assigned destination in the Oracle database. The log writer process writes those users entries from the redo log buffer.

The online redo log files will be used for the database recovery in a case of any hardware or software failures.

To resize the online redo log files, first query the V$LOG dictionay view,
SQL> SELECT * FROM v$log
/
Note the current online redo log file group. Assume that the current online redo log file is the one we would like to resize.

Archive the current online redo log file.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT
/

Query the V$LOG dictionary view again.
SQL> SELECT * FROM v$log
/
Notice that the current status was set to the next Online Redo Log file.

Archive the current online redo log file again.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT
/

Query the V$LOG dictionary view again.
SQL> SELECT * FROM v$log
/
Notice that the status column for the one we archived indicates the INACTIVE status. Note it's number.

Now, you can drop the online redo log groups that bear the status of INACTIVE.
SQL> ALTER DATABASE DROP LOGFILE

GROUP &Enter_Group_number
/

Now, add a group with a different size. Notice that the size of the online redo log files depend on the database transactions you have. In this tutorial, we make the size of the online redo log files very small so that we can demonstrate the case study for you. We are also going to manage the Online Redo Log files with Oracle-Managed Files (OMF).

Let's fist specify the DB_CREATE_ONLINE_LOG_DEST parameter. Make sure to make a directory before specifying the parameter.
SQL> HOST MKDIR c:
SQL> ALTER SYSTEM SET db_create_online_log_dest_1='c:'
/

Now, create an online redo log group with the same number of the group which you dropped earlier in this exercise. Set the size of the Redo Log file to be no less than 2,000,000.
SQL> ALTER DATABASE ADD LOGFILE GROUP

&Enter_group_number SIZE &Enter_log_size
/

Query the V$LOG dictionary view one more time.

SQL> SELECT * FROM v$log
/
Now, you have been able to resize the Online Redo log file.

Note that the V$LOG dictionary view contains information such as its group number, size, member, status, archived, and the first change number in the log.

Query the V$LOGFILE dictionary view.
SQL> SELECT * FROM v$logfile
/
The V$LOGFILE dictionary view contains the location of logs and their status.

Notice that Oracle has its own naming convention and also the default destination is Oracle-Managed Files destination.
 

How online redo log file works
Check the last 10 archived logs.
SQL> SELECT * FROM v$archived_log
WHERE recid >
(SELECT MAX(recid) - 10 FROM v$archived_log)
/
-- Note the last record with it's SEQUENCE#.
 

Let's now write a procedure which will generate some logs to test how the online redo log file works. We assume that you know how to write a procedure at this level.
SQL> BEGIN
SQL> -- This procedure does nothing. It inserts records into
SQL> -- the table and then it will delete them in order to generate logs.
SQL> -- The purpose of this procedure is to create logs.
SQL> FOR i IN 1..25 LOOP
SQL> FOR j IN 1..100 LOOP
SQL> INSERT INTO oracle.emp
SQL> VALUES (j,'AJ','CLERK',7788,'23-MAR-86',1000,null,30);
SQL> COMMIT;
SQL> END LOOP;
SQL> FOR j IN 1..100 LOOP
SQL> DELETE FROM oracle.emp
SQL> WHERE empno = j;
SQL> COMMIT;
SQL> END LOOP;
SQL> END LOOP;
SQL> END;
SQL> /

Check the last 10 archived logs.
SQL> SELECT * FROM v$archived_log
WHERE recid > (SELECT MAX(recid) - 10 FROM v$archived_log)
/
Notice that the sequence archive number was changed. It shows that the transaction created so many entries, the automatic archive was done, the Online Redo Log file was switched, and the checkpoint process wrote all of the transactions into the database.
 

Questions:

Q: Describe an online redo log file in a database?

Q: How do you add a redo log file group 3 to a database structure?

Q: How do you resize a redo log file?

Q: How do you drop a redo log file 3?

Q: Describe the V$LOG and V$LOGFILE views.

Q: What does the following SQL statement?

Q: You, as a DBA, are responsible to maintain and relocate the Redo Log files in order to distribute data among multiple hard disks to increase I/O performance. Your task is to relocate only of the redo log file from it original location c:_location to c:sub-directory. What are the steps?