Number of users logged into Oracle E-Business Suite

You can check the number of active users currently logged into the application using the following query.

select to_char(START_TIME,’DD-MON-YYYY’) Login_Time, count(*) cnt
from fnd_logins where START_TIME > (select to_date(’25-JAN-2008 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) from dual)
and login_type is not null
and end_time is null
group by to_char(START_TIME,’DD-MON-YYYY’);


LOGIN_TIME               CNT
—————– ———-
26-JAN-2008               26
25-JAN-2008              132
28-JAN-2008               13
27-JAN-2008               34

Also you can check the number of user session for the application using ICX_SESSIONS table. Use below query for checking the number of user sessions.

select ((select sysdate from dual)),(select  ‘ user sessions : ‘ || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != ‘Y’
and PSEUDO_FLAG = ‘N’
and (last_connect + decode(FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’)/60)/24) > sysdate
and counter < limit_connects) from dual

((SELECTSYSDATE (SELECT’USERSESSIONS:’||COUNT(DISTINCTSESSION_ID)HOW_MANY
————— ———————————————————
28-JAN-08        user sessions : 9

Advertisements

R12 New Techstack and Directory Structure Changes

R12 New Techstack

There has been a specific directory structure and file structure that is followed in Oracle application. In the previous release till 11iCU2, there was some standard naming convention that is following. Each directory whether it’s a product top or form directory was having an organized path.

In the new oracle application release, E-Business suite R12, the file directory structure is changed. This doesn’t mean that there is no specific organization of files and directory. Off course the pattern for directories and file system is organized well, but it is different then the directory structure for the previous releases.

In this post we will check the changes in the directory structure organization and also the techstack level changes.

Why new structure

Lets us first answer to the question, as to why the new directory structure and file system was introduced, if at all, old file system and directory structure was flaw less and been working fine. And the answer to this is the need to separate E-business suite into three parts

  • Data
  • Code
  • Configuration

In many cases, configuration changes more frequently than code and data. So keeping it separately would result in easy maintenance.

Also, in a shared file system, MUTEX files, related to Apache, cause access related problems as IAS_ORACLE_HOME is mounted on NFS. With the new file structure, this issue is also resolved as all the configuration files can be stored on the local server instead of NFS.

Directory Structure Changes

In R12 the directory structure is as given below. Just after HOME, we have APPS directory and INST directory. APPS directory contains the APPL_TOP and also the installation of forms OH and application server 10g. The version for forms that is used in E-Business suits is 10g and also the application server used is 10g. The directory 10.1.2 belongs to form oracle home and directory 10.1.3 belongs to application server oracle home. Since these are the underlying technology they are grouped under TECH_ST. APPS_ST contains the APPL_TOP and COMMON_TOP. In apps11i the APPL_TOP directory used to have format <SID>appl, where as in R12 this has been changed as simply appl directory

110.jpg
Other then this there is one more directory at this level (INST). This is instance directory. We have a new environment variable in R12 called as INST_TOP, which points in this directory.

Instance top contains all the config files, log files, ssl certificates, document root etc.   This is a new directory introduced in R12TS. Addition of this directory makes the middle-tier more organized, since data is kept separate from config/log files. Another advantage is that, multiple instances can easily share the same middle tier / DB. To create a new instance that shares an existing middle-tier, just create a new instance_top with proper config files and nfs mount the middle tier in the server

The directory structure for apps11i is also shown. Here we have 3 top directory under HOME directory. These are <SID>appl (APPL_TOP as discussed previously), <SID>comn (COMMON_TOP) and <SID>ora (IAS_ORACLE_HOME for 9i Application server and forms ORACLE_HOME). Inside <SID>ora directory we have 8.0.6 directory, which is the ORACLE_HOME for forms, and iAS directory which is ORACLE_HOME for 9i Application server.

22.jpg

For the new E-Business suite, the structure on the database side is as shown in the fig

31.jpg

The database side consists of the base oracle directory, similar to appmgr directory. Inside oracle directory again we have the distinction of technology directory with data directory. We have APPS_ST and TECH_ST directories. APPS_ST contains data files for the database and TECH_ST is the ORACLE_HOME for oracle database 10g.

File System Changes

Here we will be comparing the major directory structure changes that happened between the old and new release.

91.jpg

JAVA_TOP changes
11i:

  • $JAVA_TOP = $COMMON_TOP/java
    • Class
    • Archives

R12:

  • $JAVA_TOP = $COMMON_TOP/java/classes
    • Classes
  • $AF_JLIB = $COMMON_TOP/java/lib
    • Archives

In case of 11i release the JAVA_TOP was pointing to COMMON_TOP / java directory, which consist of class and archive directories. The class directory consist of all the .class files where as archive directory consist of all jar files.

In R12 this has been changed and new JAVA_TOP directly points to COMMON_TOP / java / classes directory, which contains all .class files. For jar files we have another directory called lib. This contains JAR files. For pointing to this directory, there is another environment variable that is defined called AF_JLIB.

Techstack Level Changes

For Oracle Apps 11i we have following components version.

41.jpg

In R12 the components and versions are as given below.

51.jpg

Component Level Comparison

In case of new techstack some of the components got changed. Following is the brief comparison.

101.jpg

In case of 11i, Jserv engine was part of HTTP server and was used for JSP and Java requests. This has been replaced by OACORE (for handling JSP). Instead of Application server 9i used in 11i, in R12 we have Application server 10g.

INST_TOP Structure

Instance top contains all the config files, log files, ssl certificates, document root etc.   This is a new directory introduced in R12TS.    Addition of this directory makes the middle-tier more organized, since data is kept separate from config/log files.   Another advantage is that, multiple instances can easily share the same middle tier / DB.   To create a new instance that shares an existing middle-tier, just create a new instance_top with proper config files and nfs mount the middle tier in the server.

In 11i release all the config/log files was in COMMON_TOP. This has been shifted to INST_TOP. This is just to organize the application middle tire and to make a distinction between the core files and config/log files. Before checking the contents of INST_TOP, lets have a brief comparison of what was the content of COMMON_TOP in 11i release and R12. COMMON_TOP contains following directories.

61.jpg

So if we see the above comparison for the COMMON_TOP for 11i and R12, we see that there is only one directory, which is missing. This directory is admin directory. This directory is been shifted to INST_TOP. Also the log directory, which is still shown in COMMON_TOP, is no more used. There is another log directory in INST_TOP. This represents the logs for all the components of middle tier. Lets now see the structure of INST_TOP.

73.jpg

Here we see that we have some of the directories, which are same as directories in COMMON_TOP. So lets clear the confusion here as to which directory in which TOP is used for what purpose.

Actually the LOG, PORTAL and RGF directory in COMMON_TOP is not used. Instead these directories in INST_TOP are actually used. They are just the copies stored in COMMON_TOP. Also the admin directory is clearly moved to INST_TOP.

LOG directory contains the log files for all the components of mid tier like apache, oacore, opmn, and concurrent manager and also the logs for the AD-scripts used to manager various services.

CERTS directory is used for SSL certificates. In case of secured environment where secure certificates are used, the certificates should be downloaded in this directory.

PID directory contains the PID (process ID) for middle tier processes like apache.

APPL directory contains the 2 directories admin and fnd. ADMIN directory contains ojspCompile.conf and ojspCompile.properties. These files are important for JSP compilation and settings. FND directory contains the DBC file, which is required by the middle tier to connect to database. Actually the DBC file which used to be in $FND_TOP/secure directory location in the 11i release has been shifted to this location. This location is directly referred by an environment variable FND_SECURE. This was pointing to $FND_TOP/secure directory in 11i release. In R12 its pointing to $INST_TOP/appl/fnd/secure directory.

ORA directory indicated the techstack components. But an ORA directory in INST_TOP contains the configuration information of the techstack components. Lets look into the details of this directory.

81.jpg

ADMIN directory contains all the AD-scripts used to manage the middle tier components. ADMIN directory contains the SCRIPT directory, which has all the script. Actually these are the wrapper scripts over the main binary. We will see the details of these scripts when we explain each component. Following are the different AD-scripts and there function.

111.jpg

New Environment Variables in R12

121.jpg

Changes to existing environment Variables in R12

131.jpg

Hope this information will be helpful !! Thanks.

Cloning Oracle Application 11i Instance

Introduction

This section will cover the detailed steps required for cloning an 11i instance. Cloning an instance is creating a duplicate of the instance. But this definition is not exactly true, because during cloning we change various details like hostname, SID etc and configure the system as per these new values. But if we see the application data and other techstack versions are exactly same as source instance.

Need for Cloning

Some time situations arise where we have to clone our application system. For example, if we have a production instance and we want to create a test instance or a debug instance, then in that case we can just make a duplicate of production instance by cloning. Test instance is usually used when we want to test some patches before applying into production so as to avoid any unknown downtime for production system. Also some times we face issues in production and we want to reproduce the same error in test and get it fixed in test. Such kind of situations demands test instance.

This post is based on Metalink note ID 230672.1

Environment Information

My environment is 11.5.10.CU2. Database version is 9.2.0.6.
Further details about OA Framework version etc can be seen using following URL
http://<HOSTNAME&gt;:<PORT>/OA_HTML/OAInfo.jsp

OA Framework Version InformationOA Framework Version 11.5.10.2CU.
MDS Version 9.0.5.4.81 (build 481)
UIX Version 2.2.18
BC4J Version 9.0.3.13.51

The process

There are 2 major methods for cloning an application system.

1) Cloning Oracle application without rapid clone.

Cloning Oracle Applications Release 11i was originally published in conjunction with Release 11.5.5 and is applicable for all 11i releases up to 11.5.5 that are not AutoConfig enabled.

2) Cloning Oracle application using Rapidclone technology

Cloning Oracle Applications Release 11i with Rapid Clone is applicable for all 11i systems that have migrated to AutoConfig and enabled Rapid Clone. This method contains steps to install AutoConfig and Rapid Clone.

Rapid Clone is the new cloning utility introduced in Release 11.5.8. Rapid Clone leverages the new installation and configuration technology utilized by Rapid Install. See OracleMetaLink Note 230672.1 (Cloning Oracle Applications 11i with Rapid Clone) for instructions on installing and enabling Rapid Clone.

Also there is one more method for cloning Oracle application with autoconfig, but this has been fully replaced by Cloning Oracle application using Rapidclone technology and is no longer supported by Oracle.

We will first discuss the method for cloning an Oracle Application with rapidclone.

With the flexible and sophisticated architecture of Oracle Applications Release 1i, simply copying all of the components will not provide you with a working applications system. For instance, there are numerous configuration files in your file system that must be modified based upon the physical topology. In addition, the Rapid Install installation process utilizes Oracle Universal Installer (OUI), which writes key information about the installation to a binary registry file. When you copy the system to a target location, you invalidate the binary registry file. Consequently, you will not be able to apply patches to the OUI-based
components.

Cloning the application system using rapid clone involves 3 major steps.

  1. Preparing the Source System
  2. Copy the file System to target location
  3. Configuring the target system

Preparing The source file system

Preparing The source file system actually contains 2 sub steps.

  1. Checking the pre-requisites patches
  2. Preparing the source system for cloning.

We will start with checking the pre-requisites for cloning.

1) Make sure that patch 2115451 is applied to the application system. This will create the neccessary perl script. In our case the version of application is 11.5.10CU2, so this patch was already applied.

2) Apply the latest AD Minipack (Apply patch 4712852 (AD.I.4) or higher).
Check the current AD level of your application.

SQL> select PATCH_LEVEL from fnd_product_installations
2  where PATCH_LEVEL like ‘%AD%’;

PATCH_LEVEL
——————————
11i.AD.I.2

The latest version of this AD patch is now 11i.AD.I.5. But atleast make the version to 11i.AD.I.4 as mentioned in metalink note ID 230672.1

3) Apply the latest Autoconfig patch to the application. As per the note ID 165195.1 the latest patch for autoconfig is 5985992(JUL/AUG 2007). Please carry out the post install steps properly as mentioned in the README.txt present in the patch directory.

4) Next step is to check if the latest rapid clone patches are applied or not. As per the metalink note ID 230672.1 the latest rapid clone patches are

* 3453499 (11i.ADX.F)
* 5225940 (Post ADX.F Fixes)
* 5972212 (For SLES 10)

SQL> select count(*) from ad_bugs
2  where bug_number = ‘3453499’;

  COUNT(*)
———-
1

SQL> select count(*) from ad_bugs
2  where bug_number = ‘5225940’;


COUNT(*)
———-
1

Third patch is required only if the application is on SLES linux.

5) Run Autoconfig on Application tier as well as on database tier. Make sure you did not encounter any error.

Completing till this step concludes that this environment can be now rapid cloned. We have all latest AD patches and autoconfig patches applied to the environment and also we have all latest rapid clone patches applied to the environment. Now next step is the actual preparation step for cloning the instance. Stay tuned !!

Preparing the source system for cloning

In this step we will prepare the source file system for cloning. This involves gathering the required configuration information. In a broad way this step will create the staged clone directory which will be having the driver files and configuration file of the source.

6) According to metalink note ID 230672.1, preparing the source system consists of preparing the database side and preparing the appltop side. We need to run adpreclone.pl script on both database side and appltop side. Lets check the details.

6a) Running adpreclone on database side.

[oracle@ocvmrh2122 oracle]$ cd $ORACLE_HOME
[oracle@ocvmrh2122 9.2.0]$ cd appsutil/
[oracle@ocvmrh2122 appsutil]$ cd scripts/
[oracle@ocvmrh2122 scripts]$ cd PROD_ocvmrh2122/
[oracle@ocvmrh2122 PROD_ocvmrh2122]$ ls
adautocfg.sh  addbctl.sh   adexecsql.pl  adpreclone.pl  adstrtdb.sql
adchknls.pl   addlnctl.sh  adlsnodes.sh  adstopdb.sql
bash-2.05$ ./adpreclone.pl  dbTier pwd=apps

When we run this command on the database side following things happens.

Techstack:

It will create following directories in the ORACLE_HOME/appsutil/clone

drwxr-xr-x   4 oracle01 oinstall    1024 Dec 31 02:00 jlib
drwxr-xr-x   5 oracle01 oinstall      96 Dec 31 02:00 db
drwxr-xr-x   5 oracle01 oinstall      96 Dec 31 02:00 data

“db” will contain the techstack information, “data” will contain the information related to datafiles and required for cloning.

Creates driver files at ORACLE_HOME/appsutil/driver/instconf.drv
Converts inventory from binary to xml, the xml file is located at $ORACLE_HOME/appsutil/clone/context/db/SCMIDC_ap101fam.xml

Prepare database for cloning:

This includes creating datbase control file script and datafile location information file at

$ORACLE_HOME/appsutil/template
-rwxr-xr-x   1 oracle01 oinstall    4826 Dec 31 02:00 adcrdbclone.sql
-rwxr-xr-x   1 oracle01 oinstall    4508 Dec 31 02:00 dbfinfo.lst
Generates database creation driver file at ORACLE_HOME/appsutil/clone/data/driver

bash-2.05$ ls -rlt
-rw-r–r–   1 oracle01 oinstall     794 Dec 31 02:00 data.drv

Copy JDBC Libraries at ORACLE_HOME/appsutil/clone/jlib/classes12.jar and appsoui

6b) Running adpreclone on appltop side

[applmgr@ocvmrh2122 prodappl]$ cd $COMMON_TOP
[applmgr@ocvmrh2122 prodcomn]$ cd admin/scripts/PROD_ocvmrh2122/
[applmgr@ocvmrh2122 PROD_ocvmrh2122]$ ./adpreclone.pl appsTier pwd=apps

This will create stage directory at $COMMON_TOP/clone. This goes into two stages

Techstack:

Creates template files for
Oracle_iAS_Home/appsutil/template
Oracle_806_Home/appsutil/template

Creates Techstack driver files for

Oracle_iAS_Home/appsutil/driver/instconf.drv
Oracle_806_Home/appsutil/driver/instconf.drv

APPL_TOP preparation:

-It will create application top driver file
$COMMON_TOP/clone/appl/driver/appl.drv

-Copy JDBC libraries
$COMMON_TOP/clone/jlib/classes111.zip

Copy the file System to target location

Once the application system is prepared for cloning. Next thing that you have to do is to bring down database and listener as well (complete application system is down) and copy the whole application to the location, where you want to create a clone.
You need to copy the following components.

On Application side:
<APPL_TOP>
<OA_HTML>
<OA_JAVA>
<OA_JRE_TOP>
<COMMON_TOP>/util
<COMMON_TOP>/clone
<COMMON_TOP>/_pages  (when this directory exists)
<806 ORACLE_HOME>
<iAS ORACLE_HOME>

On Database side (Do a proper shutdown of database (immediate)):
copy DBF files
copy ORACLE_HOME

Configuring the target system

Once all the copy is done on the target system, you can start your source application system for normal use.
Target system can be configured as given below.

On database side:

cd $ORACLE_HOME/appsutils/clone/bin
perl adcfgclone.pl dbTier pwd=apps

This will use the templates and driver files those where created while running adpreclone.pl on source system and has been copied to target system.

Following scripts are run by adcfgclone.pl for configuring techstack

  • adchkutl.sh — This will check the system for ld, ar, cc, and make versions.
  • adclonectx.pl — This will clone the context file. Because when we copy the application files from source system to target system, we also copy the context file. But this context file wont be of use here. So we need to create a new context file as per the details of this instance. Here hostname, ports and other details will change.
  • runInstallConfigDriver — located in $Oracle_Home/appsutil/driver/instconf.drv
  • Relinking $Oracle_Home/appsutil/install/adlnkoh.sh — This will relink ORACLE_HOME

For data on database side, following scripts are run

  • Driver file $Oracle_Home/appsutil/clone/context/data/driver/data.drv
  • Create database adcrdb.zip
  • Autoconfig is run
  •  Control file creation adcrdbclone.sql

On Application Side:

COMMON_TOP/clone/bin/perl adcfgclone.pl appsTier pwd=apps

Following scripts are run by adcfgclone.pl

Creates context file for target adclonectx.pl

Run driver files
$ORACLE_HOME/appsutil/driver/instconf.drv
$IAS_ORACLE_HOME/appsutil/driver/instconf.drv

Relinking of Oracle Home
$ORACLE_HOME/bin/adlnk806.sh
$IAS_ORACLE_HOME/bin/adlnkiAS.sh

At the end it will run the driver file $COMMON_TOP/clone/appl/driver/appl.drv and then runs autoconfig.

References:

Metalink Note ID: 216664.1 – Frequently Asked Questions Cloning Oracle Applications 11i

Metalink Note ID: 230672.1 – Cloning Oracle Applications Release 11i with Rapid Clone

http://techblogspace.com/appsdba/

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/

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