iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals II

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 | Lesson 27 | Lesson 28 | Lesson 29 |

 

Lesson 04

“What if nothing exists and we're all in somebody's dream? Or what's worse, what if only that fat guy in the third row exists?” Woody Allen (1935 - ), "Without Feathers"

 

Read first then play the video:

   DBA-VIDEO -Maintaining Redo Log Files (Advanced)

   

Maintaining Redo Log files

 

Introduction

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:

 

Redo Log Files

Using the V$LOG view

Archiving a current Online Redo log file

Checking a Online Redo log file’s Status

The INACTIVE status

The ACTIVE status

The CURRENT status

Dropping a Log file Group

Resizing a Redo Log file

Adding a Redo Log file

Managing an Online Redo log file with (OMF)

Using the DB_CREATE_ONLINE_LOG_DEST parameter

Using the V$LOGFILE dictionary view

Checking the SEQUENCE# column

Using the V$ARCHIVED_LOG view

Commands:

ALTER SYSTEM SET

ALTER SYSTEM ARCHIVE LOG CURRENT

ALTER DATABASE DROP LOGFILE GROUP

ALTER DATABASE ADD LOGFILE GROUP

 

Hands-on

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:\your-location
SQL> ALTER SYSTEM SET db_create_online_log_dest_1='c:\your-location'
/

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

 

“Put more trust in nobility of character than in an oath.” Solon (638 BC - 559 BC)

 

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?

SQL> SELECT * FROM v$archived_log
              WHERE recid >
                            (SELECT MAX(recid) - 10 FROM v$archived_log)
/

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?