everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 09

Next >>


 

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@yourhost 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
 

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

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.