Managing RedoLog Files
Consistency is a key requirement and factor of a database. “Shadow Copy” and “Write-Ahead Logging” (WAL) methods are used in order to ensure consistency, WAL being more popular.
Open Source databases use WAL, without needing to use built-in postgreSQL, to rename.
The Oracle DBMS does this differently, it uses a redolog to rename.
Properties of Oracle Redologs:
• Save files to the RedoLog before making any changes.
• RedoLogs are used in Oracle recovery mechanisms.
• RedoLogs are organized into groups, with a minimum requirement of 2 groups.
• To maintain error-tolerance, group members need to be on separate disks, if opting to keep all group members on the same disk, then error-tolerant disk architecture should be used such as RAID 1, 5, 10, etc.
• All group members share the same properties.
• Log Sequences and their sizes are all the same.
• The number of RedoLog files and the no. of members are set using the Create database command.
• The MAXLOGFILES parameter is used to set the maximum amount of redo log file groups.
• MAXLOGMEMBERS defines the maximum number of members per group.
• Redo logs work cyclically, for example in a situation where there are 3 groups LOGWR writes sequentially first to group 1 > group 2 > group 3 > group 1 (writing on group 1 again would mean writing over previous group 1 data).
•The number of groups is determined by the level of activity on a database and the number of transactions.
•If there aren’t enough groups in a database then errors such as “threat cannot allocate new log” are produced.
Explanations on how to add groups and member as well as a few database failure scenarios have also been provided below. An example of these is restoring a database where the RedoLog has failed.
• A Redo Log can have different statuses
– Current: The redo log currently in use.
– Active: The status that precedes Current. Information is waiting to be written to data files. (In archive mode, would show that the archiving has still not been completed).
– Inactive: A redo log that is not in use. Inactive redo logs cannot be used for Instance Recovery.
– Unused: A redo log that has never been used.
– Inactive: An inaccessible or corrupted redo log.
– Stale: A log with incomplete contents.
– Deleted: A redo log that will not be used.
– Clearing: A redo log that’s had its contents cleared and recreated using the “alter database clear logfile” command. Once recreated the redo log has an Unused status.
• To return a database to its last stable state, just before a disaster, the database should be placed in archive mode. This way all of the data that’s been committed can be restored.
One of the advantages of being in Archive mode is being able to make a backup of a database without shutting it down. (By default a database is not in Archive mode.)
Log Switch: This is the term given to a Log Writer, LGWR, switching from one group to another. A checkpoint transaction is executed along with a Log Switch.
|
1 |
alter system switch logfile; |
spfile fast_start_mttr_target = 600
If the spfile is closed in an inconsistent state, a value of 600 indicates that it should be opened within 600 seconds. The Log switches are therefore timed accordingly.
Making check points frequently would decrease the database’s performance, on the other hand infrequent updates would also have a negative impact in that it would take longer to recover a database that has shutdown in an inconsistent state.
This code creates a checkpoint.
|
1 |
alter system checkpoint ; |
Redo Log file information can be viewed using the v$log and v$logfile commands.
v$logv$logfile
The current state of Redo Log transactions
|
1 2 3 4 5 |
select group#, member from v$logfile;GROUP# MEMBER ----------------------------------------------------------------------------- 3 /export/home/oracle/oradata/suntest/redo03a.log 2 /export/home/oracle/oradata/suntest/redo02a.log 1 /export/home/oracle/oradata/suntest/redo01a.log |
Adding members
|
1 2 3 |
alter database add logfile member '/export/home/oracle/oradata/suntest/redo01b.log' to group 1 alter database add logfile member '/export/home/oracle/oradata/suntest/redo02b.log' to group 2 alter database add logfile member '/export/home/oracle/oradata/suntest/redo03b.log' to group 3 |
Note: As this is a test environment we have placed members in the same folder, under normal circumstances members would be in created in separate physical disks.
It’s possible to create members on the same disk, but by adding members in separate physical disks we increase a database’s fault tolerance in case of a disk error. Having members on the same physical disk would also have a negative impact on performance.
A guide to adding disks in a Linux environment can be found at the end of this page.
The entry made in AlertLog
|
1 2 3 |
$ tail -f alert_suntest.logalter database add logfile member '/export/home/oracle/oradata/suntest/redo01b.log' to group 1 Wed Jun 2 15:45:30 2010 Completed: alter database add logfile member '/export/home/oracle/oradata/suntest/redo01b.log' to group 1 …… |
Repeating the query.
|
1 2 3 4 5 6 7 8 |
select group#, member from v$logfile;GROUP# MEMBER -------------------------------------------------------- 3 /export/home/oracle/oradata/suntest/redo03a.log 2 /export/home/oracle/oradata/suntest/redo02a.log 1 /export/home/oracle/oradata/suntest/redo01a.log 1 /export/home/oracle/oradata/suntest/redo01b.log 3 /export/home/oracle/oradata/suntest/redo03b.log 2 /export/home/oracle/oradata/suntest/redo02b.log |
Adding a group.
|
1 |
alter database add logfile group 4 ('/export/home/oracle/oradata/suntest/redo04a.log','/export/home/oracle/oradata/suntest/redo04b.log') size 50M |
|
1 2 3 4 5 6 7 8 9 10 |
select group#, member from v$logfile;GROUP# MEMBER -------------------------------------------------------- 3 /export/home/oracle/oradata/suntest/redo03a.log 2 /export/home/oracle/oradata/suntest/redo02a.log 1 /export/home/oracle/oradata/suntest/redo01a.log 1 /export/home/oracle/oradata/suntest/redo01b.log 4 /export/home/oracle/oradata/suntest/redo04a.log 4 /export/home/oracle/oradata/suntest/redo04b.log 3 /export/home/oracle/oradata/suntest/redo03b.log 2 /export/home/oracle/oradata/suntest/redo02b.log |
Moving or renaming a RedoLog
There are 2 methods of doing this:
A. By using the Rename command
B. By creating a member in the location where it is to be moved and deleting (drop) the old ones.
A. Using the Rename command
First put the database in Mount mode
|
1 2 3 |
shutdown immediate startup mount alter database rename file '/export/home/oracle/oradata/suntest/redo03.log' to '/export/home/oracle/oradata/suntest/redo03a.log' ; |
B. Moving the redolog is done in the same way. Once the database has been shutdown redo03.log is copied to its new location and an “Alter Database Rename” command is executed.
Dropping Groups
• The main thing to remember is that the system requires at least 2 groups.
• Groups that are in a current or active status cannot be deleted.
• Files belonging to deleted groups cannot be deleted on the operating system.
|
1 |
alter database drop logfile group 4; |
Dropping Members
• The last working member cannot be deleted.
• If a member’s status is current, the status first needs to be switched.
• A database that’s in archive mode and has a pending archive cannot be deleted.
• If the operating system isn’t using the OMF (Oracle Managed File) property, the file will remain within the operating system.
|
1 |
alter database drop logfile member'/export/home/oracle/oradata/suntest/redo01a.log'; |
If a Redo Log file has been corrupted
Test 1
Whilst in group 3 was in a Current status, oracle and oinstall groups had all privileges assigned based on the group 3 redolog.
|
1 2 |
chmod wrx-a redolog03.rdo alter system switch logfile ; |
A switch has been made to Group 1 without any problems.
Using “alter system switch logfile” again will result in the database hanging.
Alert Log denies permission and a gives a “ARC0: Failed to archive thread 1 sequence 11 (0)” error.
The system can be accessed using a sys user.
Query executions are attempted.
Using a sys user it is possible to create a test table and commit it.
|
1 2 3 4 5 6 7 8 9 10 |
conn hr/hr@orclvm ERROR: ORA-00257: archiver error. Connect internal only, until freed. conn scott/1@orclvm ERROR: ORA-00257: archiver error. Connect internal only, until freed. alter database clear logfile group 3; alter database clear logfile group 3 ERROR at line 1: ORA-00350: log 3 of instance orcl (thread 1) needs to be archived ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log' alter database clear unarchived logfile group 3; Database altered, |
The log entry in the Alert file:
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN BEFORE 01/24/2010 20:51:55 (CHANGE 635601) CANNOT BE USED FOR RECOVERY.
This part is important: rman backups made before the above date are of no use, but is it possible return the database to to the point mentioned in the archive log instead of the mentioned date & time ?
Clearing online log 3 of thread 1 sequence number 11 Sun Jan 24 21:13:56 2010 Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6815.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: ‘/u01/app/oracle/oradata/orcl/redo03.log’ ORA-27041: unable to open file Linux-x86_64 Error: 13: Permission denied Additional information: 2 Sun Jan 24 21:14:09 2010 Completed: alter database clear unarchived logfile group 3 Sun Jan 24 21:14:14 2010 Archiver process freed from errors. No longer stopped
Logged into the system as Scott.
Excluding the errors in group 1, the database is working as normal.
|
1 |
alter system switch logfile; |
After entering the previous command, Group 3’s status was changed to current, the privileges previously granted by the system are restored and the system functions normally again. A backup of the database is made straight away and the database is later shutdown.
Opening Oracle:
|
1 2 |
startup ORACLE instance started. |
Total System Global Area 369098752 bytes Fixed Size 2020896 bytes Variable Size 159386080 bytes Database Buffers 201326592 bytes Redo Buffers 6365184 bytes Database mounted. ORA-00314: log 3 of thread 1, expected sequence# doesn’t match ORA-00312: online log 3 thread 1: ‘/u01/app/oracle/oradata/orcl/redo03.log’
Following the previous error, the database is in mount mode.
|
1 2 |
alter database open; ORA-03113: end-of-file on communication channel |
Recover database until cancel
Media recovery complete.
The following syntax opens the database.
|
1 |
alter database open resetlogs; |
Test 2
|
1 |
$rm -rf redo01.rdo This command deletes the redolog file |
In the alert.log file
Shutting down archive processes Tue Jan 26 20:30:06 2010 ARCH shutting down ARC2: Archival stopped
The logs have been written. The current sessions can continue their read & write transactions.
|
1 |
select * from v$log; |
truncating (as requested) before column FIRST_CHANGE#
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
———- ———- ———- ———- ———- — —————- —-
1 1 8 52428800 1 NO CURRENT 26-J 2 1 7 52428800 1 YES ACTIVE 25-J 3 1 6 52428800 1 YES INACTIVE 25-J
[/crayon]
The alert log
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_arc0_7628.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: ‘/u01/app/oracle/oradata/orcl/redo01.log’ ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory
starts giving error messages.
After entering an “alter system switch logfile” command 3 times (when wanting to place the group that is currently in a transaction, first to 2 followed by 3 and finally 1.
The Alert Log starts giving the following errors. The command line cursor doesn’t respond to the “alter system switch logfile” command.
Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Tue Jan 26 20:42:12 2010 ARC1: Failed to archive thread 1 sequence 8 (0)
|
1 2 |
alter database clear unarchived logfile group 1; Database altered. |
The following information is entered into the alert log.
Tue Jan 26 20:45:31 2010 Thread 1 advanced to log sequence 11 Current log# 1 seq# 11 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log Tue Jan 26 20:45:31 2010 Completed: alter database clear unarchived logfile group 1 Tue Jan 26 20:45:31 2010 Archiver process freed from errors. No longer stopped
|
1 |
alter system switch logfile; |
The session that was hanging due to the command is now working. Writing and deleting can continue.
|
1 |
select * from v$log; |
truncating (as requested) before column FIRST_CHANGE#
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
———- ———- ———- ———- ———- — —————- —-
1 1 11 52428800 1 NO CURRENT 26-J 2 1 10 52428800 1 YES ACTIVE 26-J 3 1 9 52428800 1 YES INACTIVE 26-J
When checked from the operating system we find that redo01.rdo or group 1 has been recreated. To make sure that this is the case, we should shut down and restart the db.
|
1 2 3 4 |
shutdown immediate startup ORACLE instance started. Total System Global Area 369098752 bytes Fixed Size 2020896 bytes Variable Size 121637344 bytes Database Buffers 239075328 bytes Redo Buffers 6365184 bytes Database mounted. ORA-00314: log 1 of thread 1, expected sequence# doesn't match ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log |
Recover database until cancel alter database open resetlogs;
These commands were able to restart the database successfully, indicating that there are no problems.
To check:
|
1 2 |
FDISK –L and MOUNT Changes made to /etc/fstab will ensure that volumes (disks) will be mounted automatically when the operating system boots. |
|
1 2 |
column member format a50 select f.member,l.bytes/1024/1024 as "Size in MB" from v$log l,v$logfile f where l.group#=f.group#; |
|
1 2 |
column MEMBERS format 99 select STATUS, group#, THREAD#, members, bytes/1024/1024 MB, ARCHIVED, SEQUENCE# from v$log; |
|
1 2 |
column member format a50 select group#, member from v$logfile; |
|
1 2 |
SELECT group#, dbid, thread#, sequence#, status FROM v$standby_log; |
|
1 2 3 4 |
SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG UNION SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG ORDER BY 1; |
|
1 2 3 4 5 6 7 8 9 10 |
set linesize 1200 column group# format 99 column THREAD# format 99 column sequence# format 999 column bytes/1024/1024 format 9999 column group format 99 column USED format 99999999999 column ARCHIVED format a5 column STATUS format a10 select group#, THREAD#, sequence#, bytes/1024/1024 MB, USED, ARCHIVED, STATUS from v$standby_log ; |
Adding a RedoLog in the file system
Adding a group.
|
1 2 |
alter database add logfile group 4 ('/export/home/oracle/oradata/suntest/redo04a.log','/export/home/oracle/oradata/suntest/redo04b.log') size 50M |
Adding a member.
|
1 2 3 |
alter database add logfile member '/oracle/oradata/suntest/redo01b.log' to group 1 alter database add logfile member '/oracle/oradata/suntest/redo02b.log' to group 1 alter database add logfile member '/oracle/oradata/suntest/redo03b.log' to group 1 |
Adding a StandbyLog to the file system.
|
1 2 |
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log01_1.log') SIZE 100M ; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log02_1.log') SIZE 100M ; |
Adding a RedoLog to Automatic Storage Management (ASM)
|
1 |
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ( '+DATA') SIZE 100M; |
|
1 |
show parameter db_create_online_log_dest_ |
|
1 2 3 |
alter system set db_create_online_log_dest_1 = '+DATA' scope=both sid='*' alter system set db_create_online_log_dest_2 = '+FRA' scope=both sid='*' alter database add logfile ; |
alternatively
|
1 |
alter database add logfile group xxx; |
This method autmatically adds a member to +FRA
Adding a Standby Log
|
1 2 3 4 5 6 7 8 |
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 SIZE 100M, GROUP 6 SIZE 100M, GROUP 7 SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 SIZE 100M, GROUP 9 SIZE 100M, GROUP 10 SIZE 100M; |
Drop (Deleting)
|
1 |
ALTER DISKGROUP FRA DROP FILE '+FRA/mx/onlinelog/redo01b-tmp.log'; |
The FRA is the Flash or Fast Recovery Area
|
1 |
ALTER DATABASE DROP LOGFILE GROUP 3; |
|
1 |
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo'; |
|
1 |
ALTER DATABASE CLEAR LOGFILE GROUP 3; |
|
1 |
ALTER SYSTEM CHECKPOINT GLOBAL; |
If the corrupt redo log file has not been archived, use the UNARCHIVED parameter.
|
1 |
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3; |
Dropping an Alias Name for an Oracle ASM filename
|
1 |
ALTER DISKGROUP data DROP ALIAS '+data/orcl/erp.dbf'; |
