everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 31

Next >>


 

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:
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?

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?

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.