|
"My advice to
you is get married: if you find a good wife you'll be happy; if
not, you'll become a philosopher." - Socrates (470-399
B.C.) |
Read
first then play the video:
DBA008(VIDEO)-Maintainiing
and Relocating the Redo Log files
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@school 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
|
"The opposite of
a correct statement is a false statement. The opposite of a
profound truth may well be another profound truth." - Niels
Bohr (1885-1962) |
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'
/
|