Dumping redo log file information – Oracle Database 10g

Redo log files are written by redo log writter process. The files are written in Oracle proprietary format and cannot be read directly. However there are simple command for reading the logfile. I had a situation where I wanted to read the blocks of online redo log files. I was knowing the commands to read datafile blocks by giving the file number and block numbers, but it took me lot of time to actually search the command for redo log files.
Anyway after speding some time, I come to know some of the ways we can dump the content of redo log files.

We basically dump the output of redo log files in a trace and then read the trace file to understand the content. Below are some of the useful command.

The following ways of dumping a redo log file are covered

1. To dump records based in DBA (Data Block Address)
2. To dump records based on RBA (Redo Block Address)
3. To dump records based on SCN
4. To dump records based on time
5. Dump the file header information
6. Dump an entire log file

1. To dump records based on DBA  (Data Block Address)

Connect to database using sysdba and execute the below command
ALTER SYSTEM DUMP LOGFILE ‘filename’  DBA MIN (fileno) (blockno) DBA MAX (fileno) (blockno);

Example:

ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’ DBA MIN 5 . 31125 DBA MAX 5 . 31150;
This will cause all the changes to the specified range of data blocks to be dumped to the trace file.  In the example given, all redo records for file #5, blocks 31125 thru 31150 are dumped.

2. To dump records based on RBA (Redo Block Address)

This will dump all redo records for the range of redo addresses specified for the given sequence number and block number.

Syntax:
ALTER SYSTEM DUMP LOGFILE ‘filename’ RBA MIN seqno blockno RBA MAX seqno blockno;

Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’ RBA MIN 2050 13255 RBA MAX 2255 15555;

3. To dump records based on SCN

Using this option will cause redo records owning changes within the SCN range
specified to be dumped to the trace file.

ALTER SYSTEM DUMP LOGFILE ‘filename’ SCN MIN minscn SCN MAX maxscn;

Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’ SCN MIN 103243  SCN MAX 103294;

4. To dump records based on time

Using this option will cause redo records created within the time range specified to be dumped to the trace file.
ALTER SYSTEM DUMP LOGFILE ‘filename’ TIME MIN value TIME MAX value;

Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’ TIME MIN 299425687 TIME MAX 299458800;

Please Note: the time value is given in REDO DUMP TIME

5. Dump the file header information

This will dump file header information for every online redo log file.

alter session set events ‘immediate trace name redohdr level 10’;

6. Dump an entire log file:

ALTER SYSTEM DUMP LOGFILE ‘filename’;

Please note: Fully qualify the filename, and include the single quotes.

Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’;

References:
http://yumianfeilong.com/2007/04/02/how-to-dump-redo-log-file-information/

Advertisements

Oracle Hot backup under microscope

I was reading one of the post for oracle hotbackup and also recovery using the hotbackups. There was quite a lot of information in the post and it took me 3 readings to understand the contents. The post was written excellently well, its just that the concept is bit complex. I thought of expressing the same in my words. At the end of this post, I have given the reference for original post as well.

This post is about few details about what happens during hot backup and how the Oracle database recovers from the hot backup.

When we want to take a hot backup for the database, the neccessary condition for the same is that, your database should be archivelog enabled. You can take a backup of one tablespace at a time by putting the tablespace in “Begin backup” mode.

What happens when we do begin backup:

You can start hot backup for a tablespace by issuing following command.

SQL> alter tablespace APPS_TS_SEED begin backup;

Tablespace altered.

This tablespace contains 2 datafiles.

SQL> select file_name from dba_data_files
2 where tablespace_name = ‘APPS_TS_SEED’;

FILE_NAME
——————————————————————————–
/dy/oracle/product/apps11i/scmidcdata/a_ref01.dbf
/dy/oracle/product/apps11i/scmidcdata/a_ref02.dbf

You can copy these datafiles now to backup location. When you issue a begin backup command the section of headers in these files will get freezed and cannot be updated. When I say section, that mean that some part of header is still updatable. The hot backup SCN and checkpoint counter is still moving in accordance to database activities. Note then this hot backup SCN is not the same as master checkpoint SCN of the datafile. Master checkpoint SCN is the one which will get updated normally when checkpoint happens, where as “hot backup SCN” is the one which gets incremented when the datafile is in backup mode and redos are getting generated. So obviously “hot backup SCN” should be greater then master checkpoint SCN. Also there is begin backup SCN, this is the SCN # when we put the tablespace in “Begin Backup” mode. This information is also updated in the datafile and gets freezed till the time tablespace is in backup mode.

So when the file is in begin backup mode, no data will get updated in the datafile, but in the header, hot backup SCN and checkpoint counter will get updated when user activities happens.

When a user does “end backup”, following activities happens.

1) Oracle will unfreeze the frozen section of the header

2) master checkpoint SCN in datafile and control file will get updated with hot backup SCN

3) The status in the file header goes from 0x1 (hot backup mode) to 0x4 (normal online mode).

4) A redo record for each file in the tablespace is created with the file number and begin backup SCN.

Note here that when we are issuing “end backup” command, actually in redo log files the SCN at the time of “Begin backup” will be updated.

5) During recovery after the end backup redo record is encountered the file in question has its status updated from 0x1 (hot backup mode) to 0x0 (consistent mode).

We saw above that tablespace APPS_TS_SEED is having 2 datafiles, so when we issue a command “alter tablespace APPS_TS_SEED end backup;” redo file will have a end backup marker for both of these files. This is shown as given below.

==============================================================
REDO RECORD – Thread:1 RBA: 0x000a17.00000242.0010 LEN: 0x0038 VLD: 0x01
SCN: 0x0000.0496800c SUBSCN: 1 01/01/2008 22:54:53
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:17.1
End backup marker – file:19 scn: 0x0000.04967e28

REDO RECORD – Thread:1 RBA: 0x000a17.00000242.0048 LEN: 0x0038 VLD: 0x01
SCN: 0x0000.0496800c SUBSCN: 1 01/01/2008 22:54:53
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:17.1
End backup marker – file:20 scn: 0x0000.04967e28

==============================================================


The SCN mentioned in “End backup marker” lines is the SCN at the time of “Begin backup” command.

OK, the important thing is, why Oracle is having such a behaviour. Why its putting a begin backup SCN number along with a file number when we are issuing a command “End Backup”. Lets take a deep look into that.

Lets say that we have a recent backup at ‘y’ time (say 10-Nov-2007) and before taking this hot backup we have another hot backup taken previous to that at y-n (where n = 1 week, so actual date will be 3-Nov-2007). So when we took a backup on y-n time, redo files where having the end backup marker and the begin backup SCN number at the time this backup was initiated. Also when we are taking a backup now on y time, the redo log will again have a end backup marker along with begin backup SCN number.

Now assume that we are restoring our backup taken at y time. We have restored all the files from the backup taken at y time except 1 datafile. One of the datafile was corrupted in our latest hot backup, so we are restoring that one file from the 1 week old hot backup (y-n).

After restore when we start recovering the database, it will start applying the redos from y-n time (since that is the oldest file in our restoration). For the file, which we restored from the y-n backup, the redo records will start getting applied. The recovery process for this file will continue until the begin backup SCN # in datafile header is equal to the end backup marker SCN # in redo log file. When the begin backup SCN # in the datafile header is same as end backup marker SCN # in redo log file, oracle will mark the file headers to 0x0 (file is consistent and needs no further “hot-backup-necessary” redo). This does not mean that this file does not need any recovery. This only means that it does not need any hot-backup-neccessary redo. This file can further be recovered from the further redos. Do not confuse this consistancy of the file with database consistancy.

What will happen if the redo log is not having this begin backup SCN number and just the file number.

In this case when it start applying the redos from y-n backup and the recovery would stop after all redo records containing the end backup marker from the y-n backup had been encountered. It wont even apply the redos from y backup.
Thats the reason Oracle Provide another level of protection by specifying the begin backup SCN. In this case oracle will compare the begin backup SCN # in datafile header(backup taken at y time) with the end backup marker SCN # present in redo log file (at y-n time) and if the begin backup SCN # in datafile header is equal to end backup marker SCN # in redo log file, then it will mark datafile headers to 0x0 (file is consistent and needs no further “hot-backup-necessary” redo). Again after making the file consistant, further transactions after end backup will be applied from redo log files.

From the previous scenario, with the begin backup SCN embedded in the redo record for each file, Oracle would continue to recover through the necessary redo. It would consider a file consistent only when it found the redo record for the end backup statement that contained the begin backup SCN that corresponds with the begin backup SCN stored in the file header. The begin backup SCN is a portion of the frozen section of the file header when the BEGIN BACKUP command was issued.

I hope this helps !!

References:

Thanks to Eric providing such details.
http://esemrick.blogspot.com/2006/02/pleasure-of-finding-oracle-things-out.html