Friday, December 31, 2010

Difference between 11i and R12

Whats new in Oracle Apps R12

Key points to Note:

1)Oracle Applications Release 12 is the latest release in the chain of E-Business Suite Releases by Oracle.

2)This release came up with the new file system model

Socket to Servlet conversion - Oracle Apps 11i

A)How to identify the Forms connect mode:

grep -i connectMode $COMMON_TOP/html/bin/appsweb_$CONTEXT_NAME.cfg

If the above command returns connect mode as http/https -> The forms are running in Servlet Mode:

If the above command returns connect mode as socket -> The forms are running in Socket Mode:

Socket to Servlet conversion (vice - versa) - Oracle Apps 11i

A)In Servlet mode the context file values looks like the following:

<server_url oa_var="s_forms_servlet_serverurl">/forms/formservlet</server_url>

<servlet_comment oa_var="s_forms_servlet_comment"/>

<forms_connect oa_var="s_frmConnectMode">https</forms_connect>

B)In Socket mode the context file values looks like the following:


<server_url oa_var="s_forms_servlet_serverurl"/>

<servlet_comment oa_var="s_forms_servlet_comment">#</servlet_comment>

<forms_connect oa_var="s_frmConnectMode">socket</forms_connect>

C)Edit the context file as required (servlet or socket) and run the autoconfig to reflect the change

D)Verify the Forms connect mode and start the services

grep -i connectMode $COMMON_TOP/html/bin/appsweb_$CONTEXT_NAME.cfg

check the connectMode socket or http(s)

For more information on socket or servlet mode please go to

Forms Servlet or Socket Mode - Which is better ?

Forms Servlet or Socket Mode - Which is better ?
A good article from Steve Chan Blog (

Many products within the Oracle E-Business Suite have screens that are built with Oracle Forms. Oracle Forms can be run in either servlet mode or socket mode. Apps 11i is based on Forms 6i and is configured to run in socket mode by default. Apps 12 is based on Forms 10g and is configured to run in servlet mode by default. What are these modes, and which is better?

What is Forms Servlet Mode?
The Forms Listener Servlet is a Java servlet that delivers the ability to run Oracle Forms applications over HTTP and HTTPS connections. It manages the creation of a Forms Server Runtime process for each client, as well as network communications between the client and its associated Forms Server Runtime process.
The desktop client sends HTTP requests and receives HTTP responses from the web server. The HTTP Listener on the web server acts as the network endpoint for the client, keeping other servers and ports from being exposed at the firewall.

What is Forms Socket Mode?
Initial releases of the Oracle Forms Server product used a simple method for connecting the client to the server. The connection from the desktop client to the Forms Listener process was accomplished using a direct socket connection. The direct socket connection mode was suitable for companies providing thin client access to Forms applications within their corporate local area networks. For the direct socket connection mode, the client had to be able to see the server and had to have permission to establish a direct network connection.

Although the direct socket connection mode is perfectly suited for deployments within a company’s internal network, it's not the best choice for application deployment via unsecured network paths via the Internet. A company connected to the Internet typically employs a strict policy defining the types of network connections that can be made by Internet clients to secure corporate networks. Permitting a direct socket connection from an external client exposes the company to potential risk because the true identity of the client can be hard to determine.

Servlet Mode Advantages
1. HTTP and HTTPS traffic is easily recognizable by routers, while socket mode communications is generally considered suspect and treated on an exception basis.
2. Existing networking hardware can be used to support basic functions such as load-balancing and packet encryption for network transit.
3. More resilient to network and firewall reconfigurations.
4. More robust: servlet connections can be reestablished if network connections drop unexpectedly for Forms, Framework, and JSP-based pages.
5. Is the only supported method for generic Oracle Forms customers, and therefore is more thoroughly tested by the Forms and E-Business Suite product groups.
6. Performance traffic can be monitored via tools like
Oracle Real User Experience Insight (RUEI).
7. Socket mode is not supported on Windows-based server platforms.

Socket Mode Advantages
1. Uses up to 40% less bandwidth than Forms servlet mode. This may be perceived by Wide Area Network (WAN) users as causing slower responsiveness, depending upon network latency.
2. Uses fewer application-tier JVM resources than servlet mode, due to fewer TCP turns and lack of overhead associated with HTTP POST handling.
Switching Apps Deployments Between Modes
Due to its numerous advantages, Forms servlet mode is the preferred and recommended deployment model for Forms on the web.
There may be circumstances where you need to switch between the default Forms modes. You might wish to switch your Oracle E-Business Suite Release 12 environment to socket mode to improve performance or reduce network load. You might wish to switch your Apps 11i environment to servlet mode as part of your rollout to external web-based end-users outside of your organization.
If you're running Apps 11i and would like to switch to servlet mode, see:
Using Forms Listener Servlet with Oracle Applications 11i (Note 201340.1)
If you're running Apps 12 and would like to switch to socket mode, see:
Using Forms Socket Mode in Oracle Applications Release 12 (Note 384241.1)

Enable Multi Language - NLS with Oracle Applications 11i

How to enable Mutilple languages (NLS) with Oracle Applications 11i instance

The below process guides through the step by step process of enable multi language in Oracle Ebiz - 11i instance.
Have taken French as example language to enable in the below steps, You can choose the language you wish to enable in the oracle applications.

Follow the below process to enable the NLS, Please note the base language will be English only.

1)Install the required Language (French) from OAM.

Enabling the Arabic from OAM, folllow the below steps

Login to OAM as a sysadmin -> sitemap -> License Manager -> License -> Languages And license the following language

Enable the "French"

Make sure you should not change the base language, it should be english.

2)Validate the language activated

Login to application with apps user and execute the below command.


Make sure you get below result.

2. French

Here base lang is American

3)Stop the Oracle application services

cd $OAD_TOP/admin/scripts
./ apps/apps

4)Run Maintain Multi-lingual Tables

AD Administration Main Menu > Maintain Applications Database Entities Menu using adadmin.

5)Run Update current view snapshot from adadmin

adadmin -> Maintain Applications Files -> Maintain snapshot information -> Update current view snapshot)

6)Run the Translation Synchronization Patch utility (

Login to applmgr user in application tier, make sure enviorment is sourced properly and execute the following command

perl $AD_TOP/bin/

Above command will genertate the manifest file which you need to upload to support. Menifist file will be on following location:


7)Upload the manifest file to oracle support, use the below steps.

Upload the adgennls.txt file to make sure you give your exact email id.
Once you upload the file, select the option Get Latest Translstions and click on next it will provide you the patch number which support is going to generate for NLS syncronization.

A Translation synchronization patch will be generated specifically based on your manifest for each of your active language. The patch will synchronize the language file versions and American English file versions in your Applications instance.
Note : it is suggested option: If you select the option to get latest translations, the patch will also bring your translations up-to-date.

If you do not receive any email regarding your request within 48 hours, you may contact Oracle Support for assistance.

this patch will generate NLS media patck, it will take some time to generate

8)Apply the Translation Synchronization patch

Follow the instructions in the README file to apply the patch.

note: Before Applying patch make sure enough space in appl top directory, patch initially copy all required files to appl top create new lang patch Choose a directory that contains enough space for the NLS updates (about 10 GB for each language Minimum).

9)AD Administration utility to generate message files from the Generate Applications Files menu.

10)Compile the invalid objects using utlrp.

11)Complie Forms and Reports

12)Complile Apps schema and validate apps schema

13)Bring up the applications services

Wednesday, December 29, 2010

Database Interview Questions - Basics I


Q 1: What are the steps involved in database startup?
Ans: Start an Instance, Mount the database and Open the Database.

Q2: What are the steps involved in database shutdown?
Ans : Close the database ; Dismount the database and Shutdown the instance.

Q3: What is Archived Redo log?
Ans: Acrhived Redo Log consists of Redo Log files that have archived before being reused

Q4: What is a temporary segment?
Ans: Temporary segments are created by ORACLE when a sql statement needs a temporary work area to complete execution. When the statement finishes execution , the temporary segment extents are released to the system for future use.

Q5: What does a control file contain?
Ans: A control file records the physical structure of the database, database name,time stamp of database creation,max datafile limit,max redo log group,max log file members, database archive information,backup information..

Q6:What is a Tablespace?
Ans: A database is divided into Logical storage unit called tablespace. A tablespace used to grouped related logical structures together

Q7: Explain the relation ship among the database,tablespace and datafiles?
Ans: Each database logically divided in to one or more tablespaces. One or more datafiles are explicitly created for each tablespace.

Q8:What is Logical structure of the database?
Ans: Database àTablespacesàsegmentsàExtentsàOracle Blocks

Q9: What is the physical structure of the database?
Ans: Datafiles,Control File and redo log files.

Q10: What are Different types of segments?
Ans: Temp Segement, Undo Segment, Table Segment and Index Segment.

Q11: Can you name few DBMS Package names and their use?

Q12: How Do you find whether the instance was started with PFILE or SPFILE?
Ans: Sho parameter spfile or query from v$parameter.

Q13: What Column differentiates the v$views to the gv$views and how?
Ans: The INST_ID Column which indicates the instance in a RAC environment the information came from ..

Q14: Explain an Ora -1555
Ans: You get this error when you get a snap shot too old within roolback.It can usually be solved by increasing the undo retention or increasing the size of rollbacks

Q15:What is Diffeernce between a temporary tablespace and a permanent tablespace?
Ans: A temporary tablespace is used for Sort Oprations of no sufficient space found in PGA. Permanent tablespace stores the true objects of dataase ..

Q16:How Do you add a datafile to a tablespace?
Ans: alter tablespace add datafile size ;

Q17: How Do you resize a datafile?
Ans: alter database datafile resize ;

Q18: What view Would you use to look at th size of a datafile?
Ans: dba_data_files,V$datafile

Q19: What view Would you use to determine free space in a tablespace?
Ans: dba_free_space

Q20: How can you gather statistics on a table?
Ans: The analyze command or using DBMS_STATS package.

Q21: List out the views names we can use to find about undo usage?
Ans: V$undostat,dba_undo_segs

Q22: What is view we can use to find out database locks?
Ans: v$lock

Q23: Using which view will check for session and process details?
Ans: V$session,v$process

Q24: Using which view will you find out , how much % Operation or job completed sofar it is running in database?
Ans: v$session_longops

Q25: How will you find out running job names from database?
Ans: Querying from dba_jobs,Dba_jobs_running views

Q26:How will find out Database creation time details? What is the column name?
Ans: v$database; created is column

Q27: How will you find out instance startup time?
Ans: v$instance,startup_time

Q28: How do you find archive is enabled for database?
Ans: archive log list or v$database ,log_mode Cloumn

Q29: How will you find active/inactive session count from database?
Ans: select status,count(*) from v$session group by status;

Q30: explain about temporary tablespace groups?
Ans: It is 10g new feature . it is a group of temporary tablespaces. We can get more details from dba_tablespace_groups if it is enabled.

Q31: What is undo_retention?
Ans : Time specification for undo segments hold the data ..and these reusable are reusable..

Q32: How will you find undo management is auto or manual?
Ans : Sho parameter undo_management

Q33: If your database contain 4 undo will you find out which undo tablespace is used by database?
Ans: Sho parameter undo_tablespace

Q34: If you database contain 5 temporary tablespaces how will you find out what is default temporary tablespace to databse?
Ans: By querying database_properties

Q35. What is alert log file and how will you find out the location of file from database?
Ans : Sho parameter dump


Q36: How can you determine if an Oracle Instance is up from the Operating system level?
Ans: ps –efgrep smon/pmon

Q37: How can u find dead processes?
Ans: ps –efgrep zombie or who –d or ps –efgrep defunc

Q38: Give the command to display space usage on the UNIX File system?
Ans: df –lk or df –lh or df –k/h

Q39: Explain sar and vmstat?
Ans: sar used for to get system activity report and Vmstat reports on virtuval memory statistics for processes .

Q40: How do you find the Load average of a server?
Ans: using uptime command.

Q41: How do you find out swap usage on a server?
Ans: free –g

Q42: How will you kill a process id at OS level ?
Ans: kill -9

Q43: How will you find out the count of Oracle processes running on server?
Ans: ps –efgrep wc –l

Q44: How will you check if any Rman backup is running?
Ans: ps –efgrep rman

Q45: How will check cron schedule job details?
Ans: Crontab –l

Q46 : How will find ASM instance is running on server?
Ans : ps –efgrep +ASM

Q47 : I have a very big file at OS and I want see last 50 lines of the file only..Which command will you use?
Ans : tail -50

Q48: How can you find , file last accessed ( modified )date?
Ans : ls –l Q49: How will you find out howmany instances are running on a server?
Ans : ps –efgrep smon (gives all instance names which are running)

Q50 : What is the command we can use to find trace of process at OS level?(means what process is doing)
And : strace –p

Tuesday, December 28, 2010

Restore RMAN backup to different server - Database Clone from RMAN Hot backup

How to restore RMAN backup to different server and Recover the database

The situation here is we have taken an RMAN backup on PROD (Source) and we need to restore those backup pieces on different server as TEST (target) database.

Its actually cloning the database from PROD(SOURCE) to TEST(target) using RMAN backup piecies taken on source.

Please follow the following steps outlined below.

Please note source (PROD) is where the backup piecies are available, we need to clone and create a database with another name TEST (target) on different server.

In target Database (TEST) perform the below

Modify the db_name in the $ORACLE_HOME/dbs/init.ora file to reflect the source database name

Start database in nomount mode and restore controlfile (this controlfile should be the RMAN backup controlfile after the RMAN backup on source)

Copy the control file backup piece from source to target and restore the controlfile.

rman target /

RMAN>startup nomount;

RMAN>restore controlfile from '/u01/backup/ccf_c-1250176543-20081031-01';

--Mount the database

RMAN>alter database mount;

Copy all the backup piecies of source(PROD) database to target(TEST) server.

Copy all the backup piecies of source(PROD) database to target(TEST) server using scp.

Once copy completes,Run the following script to list the backup pieces available:

Register all the copied backup piecies in the controlfile. (/u01/backup is the location of copied backup piecies in the target)

RMAN target /

RMAN>catalog backuppiece '/u01/backup/LEVEL0_1-10-2010_dxxthtcn_1_1';

--Run the above command for all the backup piecies copied from the source (PROD) to target (TEST)

List the archive logs. Note the last sequence number

The last sequence number should be used in the set until clause in the rman restore script.
It is referred to $SEQ

RMAN>list backup of archivelog all;

In a separate sqlplus session, rename the log files

sqlplus ‘/ as sysdba’

alter database rename file '/prod/oradata/log01.dbf' to '/test/oradata/log01.dbf';

alter database rename file '/prod/oradata/log02.dbf' to '/test/oradata/log02.dbf';

Run the following SQL to create the SQL to create the temp files for the temporary tablespace:

sqlplus ‘/ as sysdba’

select 'alter database rename file '''name''' to '''replace(name,'','')''' ;' from v$tempfile;

Run the generated SQL

alter database rename file '/prod/oradata/temp01.dbf' to '/test/oradata/temp01.dbf' ;

alter database rename file '/prod/oradata/temp02.dbf' to '/test/oradata/temp02.dbf' ;

Use the following SQL to generate the SQL to create the set new name commands to go into the rman command file for the restore

Sql> select 'set newname for datafile ' file# ' to ''' replace(name,'','') ''';' from v$datafile order by file# ;

Restore the database & Recover it:

Run the following rman restore command, pasting in the result of the above SQL where indicated.

Replace the SEQ+1 value with the sequence number + 1 obtained in step Note the last sequence number.

RMAN target /
run { set until sequence ;

-- Paste in set newname commands from previous SQL here restore database;

switch datafile all;

recover database;


Open the database and configure the database

Once Successful recovery of the database Recreate the controlfile:

Sql> alter database backup control file to trace;

Modify the trace file generated in the udump as required (file locations..etc) and SET the DB Name to TARGET SID.

Bring down the database

Modify the db_name parameter in the init.ora to target


Recreate the controlfile:

Open the database:

Sql> alter database open resetlogs;