7 Maintaining Redo Log Files Objectives Explaining the

7 Maintaining Redo Log Files

Objectives • Explaining the use of online redo log files • Obtaining log and archive information • Controlling log switches and checkpoints • Multiplexing and maintaining online redo log files • Planning online redo log files • Troubleshooting common redo log file problems 7 -2

Using Redo Log Files • Record all Control files changes made to the database Data files Database 7 -3 • Used only for Redo log files recovery

Introduction to Online Redo Log Files Components: • Redo log group • Online redo log file or member • Archived redo log file • ARCn 7 -4

Introduction to Online Redo Log Files Components: • Redo log buffer • LGWR • CKPT 7 -5

Introduction to Online Redo Log Files File 1 is written to File A by the ARCn process after the log switch is complete 7 -6

Introduction to Online Redo Log Files File 1 and File 3 are written to by the LGWR process simultaneously until both are full 7 -7

Redo Log Groups and Members Group 1 Group 2 Member Group 3 Member Min. 2 groups Min. 1 and up to 5 members/group 7 -8 Disk 1 Disk 2

Redo Log Groups and Members • LGWR concurrently writes to all members of a group • Log Sequence numbers are written to the group • Log Sequence number is assigned to a group by Oracle each time: unique id • Current log number is stored in control file 7 -9

Oracle Architecture Instance SGA Database buffer cache DBWR Redo log buffer CKPT LGWR ARCH Control files Parameter file Password file 7 -10 Data files Redo log files Database Archived log files

LGWR, Log Switches and Checkpoints LGWR writes from buffer to file when: • A transaction commits • Redo buffer is one-third full • >1 MB of changed records • Timeout occurs (every 3 secs) • Before DBWR writes buffer to data files 7 -11

LGWR, Log Switches and Checkpoints • Log switch: event when LGWR switches to a new group; Oracle then assigns a new sequence number • DBA can force a switch Checkpoint: • Dirty DB buffers written to data files • Updates headers of data and control files • Occurs when • Every log switch • Instance shuts down • Manually by DBA 7 -12

Without Archiving Backup Data files 50 7 -13 Control files 51 t 1 Disk failure 100 t 2 101

With Archiving Backup Archived redo logs Disk failure 50 Data files Control files 50 51 t 1 7 -14 99 100 t 2 101

To Archive Or Not? Oracle can be configured to operate in: • ARCHIVELOG or NOARCHIVELOG mode Two ways to archive redo log files: • Manually or Automatically LOG_ARCHIVE_START parameter indicates if manual or automatic 7 -15

Obtaining Information About Archiving • Server Manager command: SQL>ARCHIVE LOG LIST; • V$DATABASE: – NAME (select name, log_mode from v$databse) – LOG_MODE • V$INSTANCE – ARCHIVER(select archiver from v$instance) 7 -16

Obtaining Information About Groups V$THREAD: • GROUPS • CURRENT_GROUP# • SEQUENCE# select groups, current_group#, sequence# from v$thread; 7 -17

Obtaining Information About Groups and Members V$LOG: • GROUP# • MEMBERS • STATUS • SEQUENCE# • BYTES select group#, sequence#, bytes, members, status from v$log; 7 -18

Obtaining Information About Groups and Members V$LOGFILE: • GROUP# • STATUS • MEMBER select * from v$logfile; STATUS for groups(G) and members (M): UNUSED(G): never been written to CURRENT(G): current group ACTIVE(G): active but not current; needed for recovery INACTIVE(G): no longer needed for recovery INVALID(M): file is inaccessible 7 -19 BLANK(M): file is in use

Log Switches and Checkpoints • Force log switches with the command: SQL> ALTER SYSTEM SWITCH LOGFILE; • Control checkpoints with the initialization parameters: – LOG_CHECKPOINT_INTERVAL – LOG_CHECKPOINT_TIMEOUT SQL> ALTER SYSTEM CHECKPOINT 7 -20

Adding Online Redo Log Groups ALTER DATABASE ADD LOGFILE (‘/DISK 3/log 3 a. rdo’, ‘/DISK 4/log 3 b. rdo’) size 1 M; log 1 a. rdo log 1 b. rdo Group 1 7 -21 log 2 b. rdo Group 2 log 3 a. rdo log 3 b. rdo Group 3

Adding Online Redo Log Members ALTER DATABASE ADD LOGFILE MEMBER ‘/DISK 4/log 1 b. rdo’ TO GROUP 1, ‘/DISK 4/log 2 b. rdo’ TO GROUP 2; log 1 a. rdo log 1 b. rdo Group 1 7 -22 log 2 a. rdo log 2 b. rdo Group 2

How to Relocate Online Redo Log Files 1. Shut down the database. 2. Copy the online redo log files to the new location. 3. Mount the database. 4. Execute the ALTER DATABASE RENAME FILE command. ALTER DATBASE RENAME FILE ‘fname’ TO ‘fname’ 5. Open the database. 7 -23

Dropping Online Redo Log Groups ALTER DATABASE DROP LOGFILE GROUP 3; log 1 a. rdo log 1 b. rdo Group 1 7 -24 log 2 a. rdo log 2 b. rdo Group 2 log 3 a. rdo log 3 b. rdo Group 3

Dropping Online Redo Log Members ALTER DATABASE DROP LOGFILE MEMBER ‘/DISK 4/log 2 b. dbf’; log 1 a. rdo log 1 b. rdo Group 1 7 -25 log 2 a. rdo log 2 b. rdo Group 2

Clearing Online Redo Log Files If it gets corrupted. Example ALTER DATABASE CLEAR LOGFILE ‘/DISK 3/log 2 a. rdo’; 7 -26

Online Redo Log Configuration Group 1 Member Group 2 Member Group 3 Member Disk 1 7 -27 Disk 2 Disk 3

Possible LGWR Errors • One member of a group of two or more is not available. • All members of the next group are not available. • All members of the current group are not available. 7 -28

OMF and Log Files • You can create as many redo log groups as you need, bounded by MAXLOGFILES setting (when database was created). • You can multiplex each of those groups with up to five additional OMF members (bounded by the MAXLOGMEMBERS). D • Different redo log group members are created in different locations, as defined by parameters such as db_create_online_log_dest_n 7 -29

OMF and Log Files (cont. ) Examples • ALTER DATABASE ADD LOGFILE; or • ALTER DATABASE ADD LOGFILE GROUP 3 SIZE 300 M; - to add a log group • ALTER DATABASE DROP LOGFILE GROUP 1; to drop a log group (may not be current log group) • Not possible to add an additional log group member that is an OMF • Can drop an OMF redo log member: ALTER DATABASE DROP LOGFILE MEMBER. . Oracle will remove the dropped redo log member. 7 -30

OMF and Log Files (cont. ) Example Create Database Command: CREATE DATABASE mydb DATAFILE SIZE 500 M LOGFILE GROUP 1 SIZE 10 M , GROUP 2 SIZE 10 M DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 100 M UNDO TABLESPACE undotbs 1 DATAFILE SIZE 50 M MAXLOGFILES = 5 MAXLOGMEMBERS = 5 MAXDATAFILES = 600 NOARCHIVELOG; 7 -31

Summary • Controlling log switches and checkpoints • Administering online redo log files 7 -32