iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 10

"The mistakes are all waiting to be made." - chessmaster Savielly Grigorievitch Tartakower (1887-1956) - on the game's opening position

 

Read first then play the video:

   DBA-VIDEO -Multiplexing and Maintaining the Online Redo Log files

   

 

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@school 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.

 

"Reality is merely an illusion, albeit a very persistent one." - Albert Einstein (1879-1955)

 

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:'
/