Topics: Maintaining and Relocating the Redo Log
files
|
More Resources by
Google: |
|
|
|
|
Hands-On 08
(Maintaining and Relocating the Redo Log files)
As a DBA, you are
responsible for maintaining and relocating the Redo Log file in order to
distribute data among multiple hard disks to increase I/O performance. Your
job’s responsibilities dictate that you should at least be informed of the
following basic fundamental subjects:
Maintaining and
relocating the Redo Log files
Using the V$LOG
directory view
Using the V$LOGFILE
directory view
Adding an Online
Redo Log Group
Relocating or
Renaming the Online Redo Log file
Copying the Online
Redo Log file
Dropping
an Online Redo Log Group
Deleting the Online
Redo Log file's physical file
Commands:
ALTER
DATABASE ADD
SHUTDOWN
IMMEDIATE
HOST
COPY
HOST
ERASE
STARTUP
MOUNT
ALTER
DATABASE RENAME FILE
ALTER
DATABASE OPEN
ALTER
DATABASE DROP LOGFILE
-- Hands-On 08 ( Maintaining and Relocating the Redo Log files)
-- Preparation
set echo on
connect system/manager@school as sysdba
ALTER DATABASE DROP LOGFILE GROUP &Enter_group_number
/
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
-- relocate the Redo Log files.
-- Let's first, connect to SQL*Plus as the system/manager user.
pause
CONNECT system/manager@school AS SYSDBA
pause
CLEAR SCR
-- Query the V$LOG directory view to display the Online Redo
-- Log information..
pause
SELECT * FROM v$log
/
pause
CLEAR SCR
-- Query the V$LOGFILE directory view to display the Online
-- Redo Log files location.
pause
SELECT * FROM v$logfile
/
-- Notice that the database has only three online
-- redo log groups.
pause
CLEAR SCR
-- Add a group number 4's Online Redo Log file to
-- the database.
pause
ALTER DATABASE ADD LOGFILE GROUP 4
'c:\oracle\oradata\school\redo04.log' size 500k
/
pause
CLEAR SCR
-- Query the V$LOG dictionary view to display the Online Redo
-- Log information again.
pause
SELECT * FROM v$log
/
pause
CLEAR SCR
-- Relocate or rename the Online Redo Log file from
-- redo04.log to redo04a.log.
-- First, shutdown the database.
pause
SHUTDOWN IMMEDIATE
pause
CLEAR SCR
-- Then, copy the Online Redo Log file to a new location and
-- delete the previous old Online Redo Log file.
pause
HOST COPY C:\ORACLE\ORADATA\SCHOOL\REDO04.LOG C:\ORACLE\ORADATA\redo04a.log
HOST ERASE C:\ORACLE\ORADATA\SCHOOL\REDO04.LOG
pause
CLEAR SCR
-- Connect to the database as the SYSDBA and start the
-- database with the MOUNT option.
-- The MOUNT option starts the instance, reads the control file,
-- and attaches the database, but does not open it.
pause
CONNECT system/manager@school AS SYSDBA
STARTUP MOUNT
pause
CLEAR SCR
-- Alter the database to rename the original online
-- redo log file to the new location of the online redo
-- log file. This alter command will change the structure
-- of the database by updating the controlfiles.
-- Then open the database. The database needs to be
-- opened since the database was started with the
-- MOUNT option.
pause
ALTER DATABASE RENAME FILE
'C:\ORACLE\ORADATA\SCHOOL\REDO04.LOG' TO
'C:\ORACLE\ORADATA\redo04a.log'
/
ALTER DATABASE OPEN
/
pause
CLEAR SCR
-- Query the V$LOGFILE dictionary view again.
pause
SELECT * FROM v$logfile
/
-- Notice that your Online Redo Log file was relocated.
pause
CLEAR SCR
-- Drop group number 4's Online Redo Log file.
pause
ALTER DATABASE DROP LOGFILE GROUP 4
/
pause
CLEAR SCR
-- Query the V$LOGFILE directory view again.
pause
SELECT * FROM v$logfile
/
-- Notice that your Online Redo Log file was deleted.
pause
CLEAR SCR
-- Delete the Online Redo Log file's physical file.
-- Notice that when we drop the Online Redo Log file, we should
-- delete the file using the Operating System command.
pause
HOST ERASE c:\oracle\oradata\school\redo04a.log
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
|
