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 09

"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:

   DBA-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:\xxxxx\.LOG C:\yyyyyy\.log

SQL> HOST ERASE C:\xxxxx\.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:\xxxxx\.LOG' TO

                'C:\yyyyyy\.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:\yyyyyy\.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?