|
Multiplexing and Maintaining the Online Redo
Log files
Introduction
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
In
this exercise you will learn how to maintain
and multiplex the online redo log files
using Oracle-Managed Files (OMF).
Connect to a
database
First, connect to SQL*Plus as the
system/manager user.
SQL> CONNECT
system/manager@yourhost AS SYSDBA
Oracle-Managed
Files
Check to see that the database is using
Oracle-Managed Files.
SQL> 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.
Create a
directory
Create a
directory called c:.
SQL> HOST MKDIR c:
Now, the folder was created.
Define Oracle Managed folder
Alter the
system so that the database will manage the
Online Redo Log files in the Online Redo Log
destination 4.
SQL> ALTER
SYSTEM SET db_create_online_log_dest_4='c:'
/
Display the DB_CREATE_ONLINE_LOG_DEST
parameter again.
SQL> SHOW PARAMETER
db_create_online_log_dest
Notice that there is a value for
the log destination 4.
Add a group
file using Oracle-Managed file
Alter the database to add one
more group to the online redo log files.
SQL> ALTER DATABASE ADD LOGFILE GROUP 4
/
Query the V$LOGFILE view.
SQL> SELECT * FROM
v$logfile
/
Notice the Oracle naming
convention. The 4 indicates the group
number.
Add a log file
member
Add
a member to the Online Redo Log files group
number 4, using Oracle-Managed Files (OMF).
SQL> ALTER
DATABASE
ADD LOGFILE
MEMBER 'redo04b.log' TO GROUP 4
/
Query the V$LOGFILE view.
SQL> 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.
Clear a log
file group
Clear Online Redo Log file group number 1.
SQL> 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.
Drop a log
file group
Drop the group
number 4 of the Online Redo Log file.
SQL> ALTER DATABASE
DROP LOGFILE GROUP 4
/
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.
Questions:
Q: Why do you
multiplex an online redo log file?
Q: How do you
multiplex an online redo log file?
Q: Describe
the Oracle-Managed files.
Q: Describe
the User-Managed files.
Q: What are
the differences between an Oracle-Managed
and User-Managed files?
Q: How do you
create an online redo log member?
Q: What does
the following SQL statement?
SQL> ALTER
SYSTEM SET db_create_online_log_dest_4='c:'
/ |
