"What if nothing
exists and we're all in somebody's dream? Or what's worse, what
if only that fat guy in the third row exists?" Woody Allen
(1935 - ), "Without Feathers" |
Read
first then play the video:
DBA-VIDEO -Maintaining
Redo Log Files (Advanced)
Maintaining Redo Log files
Introduction
You, as a DBA, are
responsible to maintain and relocate the Redo Log files in order to
distribute data among multiple hard disks to increase I/O performance.
Your job"s responsibilities dictate that you should be at least
informed of the following basic fundamental subjects:
Redo Log Files
Using the V$LOG view
Archiving a current Online
Redo log file
Checking a Online Redo log
file"s Status
The INACTIVE status
The ACTIVE status
The CURRENT status
Dropping a Log file Group
Resizing a Redo Log file
Adding a Redo Log file
Managing an Online Redo log
file with (OMF)
Using the
DB_CREATE_ONLINE_LOG_DEST parameter
Using the V$LOGFILE
dictionary view
Checking the SEQUENCE#
column
Using the V$ARCHIVED_LOG
view
Commands:
ALTER SYSTEM SET
ALTER SYSTEM ARCHIVE LOG
CURRENT
ALTER DATABASE DROP LOGFILE
GROUP
ALTER DATABASE ADD LOGFILE
GROUP
Hands-on
In this exercise we will
learn how to resize the online redo log files. We will learn how to
archive the Online Redo Log files manually. Also, we'll learn to
understand how the checkpoint, Archive, Log Writer, and DB Writer
processes work.
Connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
Resize online redo log
files
The online redo log files are used to store Oracle user"s
entries; and once it is full, the file will be archived to an assigned
destination in the Oracle database. The log writer process writes
those users entries from the redo log buffer.
The online redo log files
will be used for the database recovery in a case of any hardware or
software failures.
To resize the online redo
log files, first query the V$LOG dictionay view,
SQL> SELECT * FROM v$log
/
Note the current online redo log file group. Assume that
the current online redo log file is the one we would like to resize.
Archive the current online redo log file.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT
/
Query the V$LOG dictionary view again.
SQL> SELECT * FROM v$log
/
Notice that the current status was set to the next Online
Redo Log file.
Archive the current online redo log file again.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT
/
Query the V$LOG dictionary view again.
SQL> SELECT * FROM v$log
/
Notice that the status column for the one we archived
indicates the INACTIVE status. Note it's number.
Now, you can drop the online redo log groups that bear the status of
INACTIVE.
SQL> ALTER DATABASE DROP LOGFILE
GROUP &Enter_Group_number
/
Now, add a group with a different size. Notice that the size of the
online redo log files depend on the database transactions you have. In
this tutorial, we make the size of the online redo log files very
small so that we can demonstrate the case study for you. We are also
going to manage the Online Redo Log files with Oracle-Managed Files (OMF).
Let's fist specify the DB_CREATE_ONLINE_LOG_DEST parameter. Make
sure to make a directory before specifying the parameter.
SQL> HOST MKDIR c:\your-location
SQL> ALTER SYSTEM SET db_create_online_log_dest_1='c:\your-location'
/
Now, create an online redo log group with the same number of the group
which you dropped earlier in this exercise. Set the size of the Redo
Log file to be no less than 2,000,000.
SQL> ALTER DATABASE ADD LOGFILE GROUP
&Enter_group_number
SIZE &Enter_log_size
/
Query the V$LOG dictionary view one more time.
SQL> SELECT * FROM v$log
/
Now, you have been able to resize the Online Redo log file.
Note that the V$LOG
dictionary view contains information such as its group number, size,
member, status, archived, and the first change number in the log.
Query the V$LOGFILE dictionary view.
SQL> SELECT * FROM v$logfile
/
The V$LOGFILE dictionary view contains the location of logs
and their status.
Notice that Oracle has its own naming convention and also the default
destination is Oracle-Managed Files destination.
How online redo log file
works
Check the last 10 archived logs.
SQL> SELECT * FROM v$archived_log
WHERE recid >
(SELECT MAX(recid) - 10 FROM v$archived_log)
/
-- Note the last record with it's SEQUENCE#.
Let's now write a procedure
which will generate some logs to test how the online redo log file
works. We assume that you know how to write a procedure at this level.
SQL> BEGIN
SQL> -- This procedure does nothing. It inserts records into
SQL> -- the table and then it will delete them in order to generate
logs.
SQL> -- The purpose of this procedure is to create logs.
SQL>
FOR i IN 1..25 LOOP
SQL>
FOR j IN 1..100 LOOP
SQL>
INSERT INTO iself.emp
SQL>
VALUES (j,'AJ','CLERK',7788,'23-MAR-86',1000,null,30);
SQL>
COMMIT;
SQL>
END LOOP;
SQL>
FOR j IN 1..100 LOOP
SQL>
DELETE FROM iself.emp
SQL>
WHERE empno = j;
SQL>
COMMIT;
SQL>
END LOOP;
SQL>
END LOOP;
SQL> END;
SQL> /
Check the last 10 archived logs.
SQL> SELECT * FROM v$archived_log
WHERE recid > (SELECT MAX(recid) - 10 FROM v$archived_log)
/
Notice that the sequence archive number was changed. It
shows that the transaction created so many entries, the automatic
archive was done, the Online Redo Log file was switched, and the
checkpoint process wrote all of the transactions into the database.
"Put more trust in
nobility of character than in an oath." Solon (638 BC - 559
BC) |
Questions:
Q: Describe an online redo
log file in a database?
Q: How do you add a redo
log file group 3 to a database structure?
Q: How do you resize a redo
log file?
Q: How do you drop a redo
log file 3?
Q: Describe the V$LOG and
V$LOGFILE views.
Q: What does the following
SQL statement?
SQL> SELECT * FROM
v$archived_log
WHERE recid >
(SELECT MAX(recid) - 10 FROM v$archived_log)
/
Q: You, as a DBA, are
responsible to maintain and relocate the Redo Log files in order to
distribute data among multiple hard disks to increase I/O performance.
Your task is to relocate only of the redo log file from it original
location c:_location to c:sub-directory. What are the steps?
|