Hands-On 09 (Multiplexing and Maintaining the Online Redo Log files)

As a DBA, you are responsible for maintaining multiple Online Redo log files to protect your organization of a possible loss of data due to media failure. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Maintaining the Online Redo Log files

Multiplexing the Online Redo Log files

Using Oracle-Managed Files

Using User-Managed Files

Using the DB_CREATE_ONLINE_LOG_DEST parameter

Creating an Online Redo Log group

Creating an Online Redo Log member

Clearing an Online Redo Log group

Dropping an Online Redo Log group

Using the V$LOGFILE view

Commands:

SHOW PARAMETER

HOST MKDIR

ALTER SYSTEM SET

ALTER DATABASE ADD LOGFILE GROUP

ALTER DATABASE ADD LOGFILE MEMBER

ALTER DATABASE CLEAR LOGFILE GROUP

-- Hands-On 09 (Multiplexing and Maintaining the Online Redo Log files)
-- Preparation
set echo on
connect system/manager@school as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a50
col parameter format a40
col username format a10
col member format a50

pause

--Start

CLEAR SCR
-- In this exercise you will learn how to maintain 
-- and multiplex the online redo log files using 
-- Oracle-Managed Files (OMF).

-- First, connect to SQL*Plus as the system/manager user.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- Check to see that the database is using Oracle-Managed
-- Files.

pause


SHOW PARAMETER db_create_online_log_dest

-- If the value of all of the DB_CREATE_ONLINE_LOG_DEST column
-- were null, it means that you cannot take advantage of 
-- Oracle-Managed Files.

pause

CLEAR SCR
-- Create a directory called c:\newfolder.

pause

HOST MKDIR c:\newfolder

-- The folder was created.

pause

CLEAR SCR
-- Alter the system so that the database will manage the Online Redo
-- Log files in the Online Redo Log destination 4.

pause


ALTER SYSTEM SET db_create_online_log_dest_4='c:\newfolder'
/

pause

CLEAR SCR
-- Display the DB_CREATE_ONLINE_LOG_DEST parameter again.

pause


SHOW PARAMETER db_create_online_log_dest

-- Notice that there is a value for the log destination 4.

pause

CLEAR SCR
-- Alter the database to add one more group to 
-- the online redo log files.

pause

ALTER DATABASE ADD LOGFILE GROUP 4
/

pause

CLEAR SCR
-- Query the V$LOGFILE view.

pause

SELECT * FROM v$logfile
/

-- Notice the Oracle naming convention. 
-- The 4 indicates the group number. 

pause

CLEAR SCR
-- Add a member to the Online Redo Log files group number 4,
-- using Oracle-Managed Files (OMF).

pause


ALTER DATABASE ADD LOGFILE MEMBER 'redo04b.log' TO GROUP 4
/

pause

CLEAR SCR
-- Query the V$LOGFILE view.

pause

SELECT * FROM v$logfile
/

-- Notice the new member status is set to invalid. That's okay.
-- Once we start using it, the status will change.

pause

CLEAR SCR
-- Clear Online Redo Log file group number 1.

pause

ALTER DATABASE CLEAR LOGFILE GROUP 1
/

-- You should never execute this command unless you have to.
-- By executing this command you may loose some important
-- information. Do this command only if your group file 
-- has a corrupted member. 

pause

CLEAR SCR
-- Drop the group number 4 of the Online Redo Log file.

pause


ALTER DATABASE DROP LOGFILE GROUP 4
/

pause

-- Notice that when you use Oracle-Managed Files,
-- you do not need to delete the physical datafile
-- from the system using the Operating System command.
-- Oracle does it for you.

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