Hands-On 05 (Maintaining Redo Log files)

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 05 (Maintaining Redo Log files)
-- Preparation
SET ECHO ON
SET linesize 1000 pagesize 55
COL name FORMAT a40
col description format a30
col type format a7
pause

--Start

CLEAR SCR
-- 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.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- To resize the online redo log files, first query
-- the V$LOG dictionay view.

pause


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.

pause

CLEAR SCR
-- Archive the current online redo log file.

pause


ALTER SYSTEM ARCHIVE LOG CURRENT
/

pause

CLEAR SCR
-- Query the V$LOG dictionary view again.

pause

SELECT * FROM v$log
/

-- Notice that the current status was set to the
-- next Online Redo Log file.

pause

CLEAR SCR
-- Achive the current online redo log file again.

pause


ALTER SYSTEM ARCHIVE LOG CURRENT
/

pause

CLEAR SCR
-- Query the V$LOG dictionary view again.

pause


SELECT * FROM v$log
/

-- Notice that the status column for the one we archived 
-- indicates the INACTIVE status.

-- Note it's number.

pause


CLEAR SCR
-- Now, you can drop the online redo log groups 
-- that bear the status of INACTIVE.

pause


ALTER DATABASE DROP LOGFILE GROUP &Enter_Group_number
/

pause

CLEAR SCR
-- 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 demostrate 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.

pause


HOST MKDIR c:\mylogs

ALTER SYSTEM SET db_create_online_log_dest_1='c:\mylogs'
/

pause

CLEAR SCR
-- 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.

pause


ALTER DATABASE ADD LOGFILE GROUP &Enter_group_number SIZE &Enter_log_size
/

pause

CLEAR SCR
-- Query the V$LOG dictionary view one more time.

pause


SELECT * FROM v$log
/

-- Now, you have been able to resize the Online Redo
-- log file.

pause

CLEAR SCR
-- Query the V$LOGFILE dictionary view.

pause


SELECT * FROM v$logfile
/

-- Notice that Oracle has it's own naming convension and
-- also the default destination is Oracle-Managed Files 
-- distination.

pause

CLEAR SCR
-- Check the last 10 archived logs.

pause


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

-- Note the last record with it's SEQUENCE#.

pause


CLEAR SCR
-- Let's now write a procedue 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.

-- Please wait ...

pause

BEGIN
-- This procedure does nothing. It inserts records into 
-- the table and then it will delete them.
-- The purpose of this procedure is to create logs.
FOR i IN 1..25 LOOP
FOR j IN 1..100 LOOP
INSERT INTO iself.emp
VALUES (j,'AJ','CLERK',7788,'23-MAR-86',1000,null,30);
COMMIT;
END LOOP;

FOR j IN 1..100 LOOP
DELETE FROM iself.emp
WHERE empno = j;
COMMIT;
END LOOP; 

END LOOP;
END;
/

pause

CLEAR SCR
-- Check the last 10 archived logs.

pause

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.


pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause