|
Maintaining and Relocating the Redo Log
files
Introduction
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
In this
exercise you will learn how to maintain and
relocate the Redo Log files.
Connect to a
database
Let's first, connect to SQL*Plus as the
system/manager user.
SQL> CONNECT
system/manager@yourhost AS SYSDBA
View Log
information
Query the V$LOG directory view to display
the Online Redo Log information..
SQL> SELECT *
FROM v$log
/
View log files
location
Query the V$LOGFILE directory view to
display the Online Redo Log files location.
SQL> SELECT * FROM
v$logfile
/
Notice that the database has only
three Online Redo log groups.
Add
a log file group
Add a group
number 4's Online Redo Log file to the
database.
SQL> ALTER
DATABASE ADD LOGFILE GROUP 4
'c:.log' size
500k
/
Query the
V$LOG dictionary view to display the Online
Redo Log information again.
SQL> SELECT * FROM
v$log
/
Relocate a log
file
Relocate or rename the Online Redo Log file
from redo04.log to redo04a.log.
First, shutdown the database.
SQL> SHUTDOWN
IMMEDIATE
Then, copy the Online Redo Log file to a new
location and delete the previous old Online
Redo Log file.
SQL> HOST COPY
-
C:.LOG
-C:a.log
SQL> HOST
ERASE C:.LOG
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.
SQL> CONNECT
system/manager@school AS SYSDBA
SQL> STARTUP MOUNT
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.
SQL> ALTER
DATABASE RENAME FILE
'C:.LOG' TO
'C:a.log'
/
SQL> ALTER DATABASE OPEN
/
Query the V$LOGFILE dictionary view again.
SQL> SELECT * FROM
v$logfile
/
Notice that your Online Redo Log
file was relocated.
Drop a log
file group
Drop group number 4's Online Redo Log file.
SQL> ALTER
DATABASE DROP LOGFILE GROUP 4
/
Query the V$LOGFILE directory view again.
SQL> SELECT *
FROM v$logfile
/
Notice that
your Online Redo Log file was deleted.
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.
SQL> HOST
ERASE c:a.log
Questions:
Q: How do you
maintain and relocate a redo log file?
Q: Describe
the V$LOG view.
Q: Describe
the V$LOGFILE view.
Q: How do you
add an online redo log group?
Q: How do you
add an online redo log file member?
Q: How do you
relocate or rename an online redo log file?
Q: How do you
drop an online redo log file?
Q: How do you
drop an online redo log group?
Q: How do you
delete a physical online redo log file?
Q: What does
the following SQL statement?
SQL> ALTER
DATABASE ADD LOGFILE GROUP 4
'c:.log' size
500k
/
Q:
What does the following SQL statement?
SQL> ALTER
DATABASE RENAME FILE
'C:.LOG' TO 'C:a.log'
/ |
