Wednesday 2 October 2013

Sql Tuning and Performance

As salamo alaikum wa rahmatullah

SQL Performance Analyzer in Oracle Database 11g Release - 1

The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manupulate them, was introduced in Oracle 10g as port of the Automatic Sql Tunning functionality. Oracle 11g makes futher user of SQL tunning set with the SQL performance anlyzer, which compares the performance of the statements in tunning set before and after a database change. The database change can be as major as you like, such as:
Database, operating system, or hardware upgrades.
Database,  operating system, or hardware configuration changes.
Database initialization parameter changes.
Schema changes, such as adding indexes or materialized views.
Refreshing optimizer statistics.
Creating or changing SQL profiles.

Unlike Database Replay, the SQL Performance Analyzer does not try and replicate the workload on the system. It just plugs through each statement gathering performance statistics.

The SQL Performance Analyzer can be run manually using the DBMS_SQLPA package or using Enterprise Manager. This article gives an overview of both methods.

Setting Up the Test
Creating SQL Tuning Sets using the DBMS_SQLTUNE Package
Running the SQL Performance Analyzer using the DBMS_SQLPA Package
Creating SQL Tuning Sets using Enterprise Manager
Running the SQL Performance Analyzer using Enterprise Manager
Optimizer Upgrade Simulation
Parameter Change
Transferring SQL Tuning Sets

Setting Up the Test

The Sql Performance analyzer requires SQL tunning sets, and SQL tuning sets are pointless unless they contain SQL, so the first task should be to issue some SQL statements. We are only trying to demonstrate the technology, so the examples can be really simple. The following code creates a test user called SPA_TEST_USER.

CONNECT AS SYSDBA
Create user spa_test_user IDENTIFIED BY spa_test_user QUOTA UNLIMITED ON users;

GRANT CONNECT,CREATE TABLE TO spa_test_user;

Next, connect to the test user and create a test table called 'MY_OBJECTS' using a query from the ALL_OBJECTS view.

CONNECT spa_test_user/spa_test_user
CREATE TABLE MY_OBJECTS AS SELECT * FROM ALL_OBJECTS;
Exec DBMS_STATS.gather_table_Stats(USER,'MY_OBJECTS',cascade=>TRUE);
or EXEC DBMS_STATS.gather_table_stats('spa_test_user','MY_OBJECTS',cascade=>TRUE);

This schema represents our "before" state. Still logged in as the test user, issue the following statemtns.

SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID <=100;
SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID =100;
SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID <=1000;
SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID =1000;
SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID BETWEEN 100 AND 1OOO;

Notice all statements make reference to the currently unidexed OBJECT_ID column. Later we will be indexing this column to create our changed "after" state.

The select statements are now in the shared pool, so we can start creating an SQL Tunning set.

Creating SQL Tunning Sets using DNMS_SQLTUNE Package

The DBMS_SQLTUNE package contains procedure and functions that allow us to create, manipulate and drop the SQL tunning sets. The first step is to create an SQL tunning set called spa_test_sqlset using the CREATE_SQLSET procedure.

Sql> Conn / as sysdba
Sql> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name=>'spa_test_sqlset');

Next, the SELECT_CURSOR_CACHE table function is used to retrieve a cursor containing all SQL statements that were parsed by the SPA_TEST_USER schema and contain the word "MY_OBJECTS". The resulting cursor is loaded into the the tuning test using the LOAD_SQLSET procedure.

DECLARE 
L_CURSOR DBMPS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN L_CURSOR FOR
SELECT VALUE(a)
FROM  TABLE (
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
BASIC_FILTER => 'SQL_TEXT LIKE ''%MY_OBJECTS'' AND PARSING_SCHEMA_NAME=''SPA_TEST_USER'', ATTRIBUTE_LIST => 'ALL')
) a;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME => 'SPA_TEST_SQLSET', POPULATE_CURSOR => L_CURSOR);
END;


The DBA_SQLSET_STATEMENTS view allows us to see which statements have been associated with the tuning set.

Select sql_text From dba_select_statements where sqlset_name ='spa_test_sqlset'

out put 
------------------


-----------------

Now we have an SQL tuning set, we can start using the SQL performance analyzer.

Running the SQL Performance Analyzer using the DBMS_SQLPA Package

The DBMS_SQLPA package is the PL/SQL API used to manage the SQL performance analyzer. The first is to create an analysis task using the CREATE_ANALYSIS_TASK function, passing in the SQL tunning set name and making a not of the resulting task name.

Conn / AS SYSDBA

VARIABLE V_TASK VARCHAR(64);
EXEC :V_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(SQLSET_NAEM=>'SPA_TEST_SQLSET');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> PRINT :V_TASK


Next, user the EXECUTE_ANALYSIS_TASK procedure to execute the contents of the SQL tunning set against the current state of the database to gather information about the performance before any modifications are made. This analysis run is named before_change.

BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :v_task,
execution_type =>'test execute',
execution_name => 'before_change');
END;
/

Now we have the "Before" performance information, we need to make a change so we can test the "after" performance. For this example we will simply add an index to the test table on the OBJECT_ID column.
In a new SQL*PLUS session create the index using the following statements.

Conn spa_test_user/spa_test_user

CREATE INDEX my_objects_index_01 ON my_objects(object_id);

EXEC DBMS_STATS.gather_table_stats(USER,'MY_OBJECTS',cascade => TRUE);

Now, we can return to our original session and test the performance after the database change. Once again use the EXECUTE_ANALYSIS_TASK procedure, naming the analysis task "after_change".

BEGIN
DBMS_SQLPA.execute_analysis_task (
task_name => :v_task,
execution_type => 'test execute',
execution_name => 'after_change');
END;
/

Once the before and after analysis task completed, we must run a comparison analysis task. The following code explicitly names the analysis tasks to compare using name-value pairs in the EXECUTION_PARAMS parameter. If this is ommited, the latest two analysis runs are compared.

BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :v_task,
execution_type =>'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'before_change',
'execution_name2',
'after_change')
);
END;
/

With this final analysis run complete, we can check out the comparison report using REPORT_ANALYSIS_TASK function. The function returns a CLOB containing the report in TEXT, XML or HTML format.
Its usage is shown below

Note :- Oracle 11gR2 also includes an 'ACTIVE' format that looks more like the Enterprise Manager output.

SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON

SPOOL /tmp/execute_comparison_report.htm

SELECT DBMS_SQLPA.report_analysis_task(:v_task,'HTML','ALL') From dual;

SPOOL OFF

---

Mohammad Shahnawaz
Oracle's Passion

Privileges on Tablespace

As salamo alaikum wa rahmatullah,

Provide the Privileges on Tablespace.

Situation :- I have created a user by name test_user and try to create a table with big data an error is thrown by the oracle like "no privileges on tablespace system", here is step by step solution of this.

[Windows Operating System]
C:\windows\system>Sqlplus "/ as sysdba"
Sql> show user
"SYS"

sql> create user test_user identified by test_user Quota unlimited on users tablespace system;

sql> grant connect, create table to test_user;

sql>connect test_user/test_user
sql> Create table My_Table As select * from all_objects;

Oracle error -> no privileges on tablespace system.

sql> connect sys as sysdba
password : *******
sql> Alter user test_user Quota 100M On system;
sql>Grant unlimited tablespace to test_user;
sql> connect test_user/test_user
sql>Create table My_Tables AS Select * from all_objects;
Table created

--
Mohammad Shahnawaz
Oracle's Passion

Monday 19 August 2013

Database Object for Database space usage

As salamo alaikum wa rahmatullah

DBA_TABLESPACES
DBA_SEGMENTS
DBA_EXTENTS
DBA_FREE_SPACE
DBA_LMT_FREE_SPACE
DBA_THRESHOLDS
DBA_OUTSTANDING_ALERTS
DBA_ALERT_HISTORY
V$ALERT_TYPES

-> select reason_id, object_type, scope, internal_metric_category,
internal_metric_name from v$alert_types
where group_name = 'Space';


Sample select queries.

-> select tablespace_name, block_size, contents, extent_management from dba_tablespaces;

-> select tablespace_name, count(*) NUM_OBJECTS,sum(bytes), sum(blocks), sum(extents) from dba_segments group by rollup (tablespace_name);

-> select owner, segment_name, tablespace_name,extent_id, file_id, block_id, bytes from dba_extents where segment_name = 'XXX$';

-> select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name;


--
Thanks and Regards
Mohammad Shahanwaz
oracle's passion

Tuesday 13 August 2013

Definition of AD utility

As salamo alaikum wa rahmatullah

This top containin Utilities & program code which is very frequently used by DBA’s . So where are these utilities or program in file system ? (I think you all know but for begineers its in AD_TOP/bin )

Lets see one line definition of each

AD Utility ?
AD Utilities are a group of tools designed to install, upgrade, maintain, and patch applications.

ADPATCH ?
is a utility that is used to apply individual patches, mini-packs, or maintenance packs to an Oracle Applications instance.

ADADMIN ?
AD Administration (adadmin) is used performs maintenance/administration tasks on an Oracle Appls Instance. Task associated with adadmin are divided into two broad categories database and file system task.

ADCTRL ?
Utility to determine status of worker (started by adpatch , adadmin or other ad utility) and to control ( restart/drop/skip) workers.

ADRELINK ?
Used to relink apps executables with product libraries , if some library defination is changed or executable is corrupted.

ADMRGPCH ?
To merge different patches & create single patch . This is helpful when you have too many patches , you can merge them & apply in one shot.

ADCLONE ?
Utility to clone Insatnce from Test to Prod , Vice Versa

ADCONFIG ?
To configure different components in Aplications like changing port number or domainname or incresing number of JVM’s

ADIDENT ?
utility to find version of a file in oracle applications (Identification )

ADLICMGR ?
To license a product in applications , you might have license to use only 10 products initially then later decided to use 5 more so license them first by this utility

ADSPLICE ?
You add a product in application, by default you might have 90-105 ( may be more ) products like AP, GL, AR and now oracle released one more product so add this product using this utility .

This is overview & usages of each application DBA Utility , now in next topic I will start with patching & how these utilities are used & options available with them . If you are amazed/confused whats this patching donot worry I will explain in deatil but if you are a DBA you might already be knowing patching & might have applied coupel of database patches using opatch now lets use ADPATCH to apply application or apps patches.


--
Thanking you
Mohammad Shahnawaz
oracle's passion

Monday 12 August 2013

What are re-start file?

As salamo alaikum wa rahmatullah,


The restart files are files used by the managers or workers to restart a failed job. When a job complete the restart files are deleted, but backup files still are saved on the system with the extensions .bak, .bk2 or bk3.

The restart files are located in the $APPL_TOP/admin/<SID>/restart directory.

--
Thanks and Best Regards
Mohammad Shahnawaz
oracle's passion

Command to check the Trigger at Logon time

As salamo alaikum wa rahmatullah,
conenct / as sysdba

select owner, trigger_name from DBA_TRIGGERS

where trigerring_event like 'LOGON%'

--
Thank's and Best Regards
Mohammad Shahnawaz
Oracle's Passion

Schema registered with E-Business Suite

As salamo alaikum wa rahmatullah

You can run the following statement to see which schemas are currently registered with the Ebusiness Suite:

select distinct(upper(oracle_username)) sname
from fnd_oracle_userid a, fnd_product_installations b
where a.oracle_id = b.oracle_id
order by sname;


--
Thanks and Best Regards
Mohammad Shahnawaz
Oralce's Passion

Tuesday 30 July 2013

How to Implement VPD on column level

As salamo alaikum wa rahmatullah


You can implement the VPD at column level. If a column with secure data is referenced by a query, you can use column-level VPD to apply a security policy or display the column with NULL values. This approach is different from the row-based VPD. In column-level VPD all rows may be displayed, but the columns may be shielded from the user. You can apply column-level VPD to a table or a view.
To us column-level VPD, specify a value for the sec_relevant_cols input parameters for the ADD_POLICY procedure of DBMS_RLS. For creating policy use the following command:

begin
DBMS_RLS.ADD_POLICY
 ( object_schema=>'PRACTICE',
   object_name=>'Emp',
   policy_name=>'EMP_SELECT_POLICY',
   function_schema=>'PRACTICE',
   policy_function=>'SECURITY_PACKAGE.EMP_SELECT_SECURITY',
   sec_relevant_cols=>'Price');
end;
/
Here  PRACTICE is name of my local schema, Emp is the name of table, and EMP_SELECT_SECURITY is customized policy function and the Price is the name of Column of EMP table.
You have to create a Package as i created by the name of 'SECURITY_PACKAGE'  and i call the Function EMP_SELECT_SECURITY.
  --
Mohammad Shahnawaz
Oracle's Passion

Monday 29 July 2013

Viewing Privilege and Role Information

As salamo alaikum wa rahmatullah

To access information about grants of privileges and roles, you can query the following data dictionary views:


View
Description
DBA_COL_PRIVS
ALL_COL_PRIVS
USER_COL_PRIVS
DBA view describes all column object grants in the database. ALL view describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee. USER view describes column object grants for which the current user is the object owner, grantor, or grantee.
ALL_COL_PRIVS_MADE
USER_COL_PRIVS_MADE
ALL view lists column object grants for which the current user is object owner or grantor. USER view describes column object grants for which the current user is the grantor.
ALL_COL_PRIVS_RECD
USER_COL_PRIVS_RECD
ALL view describes column object grants for which the current user or PUBLIC is the grantee. USER view describes column object grants for which the current user is the grantee.
DBA_TAB_PRIVS
ALL_TAB_PRIVS
USER_TAB_PRIVS
DBA view lists all grants on all objects in the database. ALL view lists the grants on objects where the user or PUBLIC is the grantee. USER view lists grants on all objects where the current user is the grantee.
ALL_TAB_PRIVS_MADE
USER_TAB_PRIVS_MADE
ALL view lists the all object grants made by the current user or made on the objects owned by the current user. USER view lists grants on all objects owned by the current user.
ALL_TAB_PRIVS_RECD
USER_TAB_PRIVS_RECD
ALL view lists object grants for which the user or PUBLIC is the grantee. USER view lists object grants for which the current user is the grantee.
DBA_ROLES
This view lists all roles that exist in the database.
DBA_ROLE_PRIVS
USER_ROLE_PRIVS
DBA view lists roles granted to users and roles. USER view lists roles granted to the current user.
DBA_SYS_PRIVS
USER_SYS_PRIVS
DBA view lists system privileges granted to users and roles. USER view lists system privileges granted to the current user.
ROLE_ROLE_PRIVS
This view describes roles granted to other roles. Information is provided only about roles to which the user has access.
ROLE_SYS_PRIVS
This view contains information about system privileges granted to roles. Information is provided only about roles to which the user has access.
ROLE_TAB_PRIVS
This view contains information about object privileges granted to roles. Information is provided only about roles to which the user has access.
SESSION_PRIVS
This view lists the privileges that are currently enabled for the user.
SESSION_ROLES
This view lists the roles that are currently enabled to the user.

--
Thanking you
Mohammad Shahnawaz
Oracle's Passion


Thursday 11 July 2013

Oracle Apps Question [Installation Part - 2]

As salamo alaikum wa rahmatullah,
Ramdan kareem - 1434 hzr.

What is Load balancing?
ans)Laod balancing distributes process for Oracle Applications across multiple nodes.This distribution of the workload improves the performance and enhances scalability. If the load balancing then even if the component is down,the application system continues to work. For example, if Apache load balancing is enabled across two nodes then in case one of the node is down, all the incoming connections will automatically be rediricted to the other node.

In which servers load balancing can be enabled?
ans) The load balancing can be enabled in the forms server, Web server as well as the concurrent processing server.

Which products are installed by default along with RapidInstall?
ans) RapidInstall installs all the products automatically regardless of their licensed status. However, you must register products you have licensed so that they are flagged in the system as active.
An active flag marks products for inclusion in patching and other tasks that you will perform to update and maintain your system after the initial installation.

How can i tell what is installed and licensed after finishing an install?
ans) The script AD_TOP/sql/adutconf.sql against the APPS user schemma will generate a detailed file (adutconf.lst) with information about the database configuration and what products are installed and licensed after the rapid installation process completes.

I have already done the installation but forgot to license a product I want to use Can I license it after the installation?
ans) Yes, licensing can be done after the installation. You can do in two ways.
1. Go to AD_TOP and run the script adlicmgr.sh. This will prompt for the additional products to be licensed.
2. From Oracle Applications Manager go to OAM>License>Manager>License additional products.

I have already done the installation.Now i want to add an additional language. Is it possible?
ans) Yes,It's possible to add a separate language after the installation. For this, you need to make the language as active from Oracle Application Manager and need to download and install the NLS software.

Thanking you
---
Mohammad Shahnawaz
Oracle's Passion
kuwait

Monday 8 July 2013

ORACLE APPS Installation Question

As salamo alaikum wa rahmatullah,
There are following questions which are asked from oracle apps installation. For the installation it is good.

What is the minimum disk space requirement for installing Oracle Applications?
ans)The approximate file size in a single-node installation are:
-> Application tier file system - 26GB (including iAS/8.0.6 ORACLE_HOME COMMON_TOP and APPL_TOP)
-> Database tier file system (fresh install with a production database) -31 GB
-> Database tier file system (fresh install with a Version Demo database) - 65 GB
-> Total space for single node system, not including stage area, is 57 

GB for a fresh install with production database, and 91 GB for fresh install with a Version Demo database.

How much STAGE area is required for RapidInstall?
ans)To run RapidInstall from a stage area, you need at least 24 GB to accommodate the files.

How much space an additional language needs?
ans) To install an additional language, it needs approximately around 10 GB space.

Di i need to install any other software before starting the RapidInstall?
ans)Befoer installing Oracle Applications, the JDK needs to be installed. The version of JDK which needs to be installed depends on which version of Oracle Applications yuo want to install. The latest 11.5.0 release of Oracle Applications need JDK1.4.2. Apart from JDK, you must have perl 5.0053 installed and which should be there in your PATH.

Can I start the RapidInstall with any user?
ans)RapidInstall needs to b started only with the root user. It can also be started with any other user as well, but that is not recommended as you have two different users - one for database and other for application file system.

How many operating system users I need for installing Oracle Application?
ans)Oracle recommends that you should have two Unix users for Installing Oracle Application - One Oracle user (oracle) who owns the file system of the database and one application (applmgr) user who owns the application file system.

What are the individual disks included in the release 11i Software boundle?
ans)The 11.5.10 software comes in DVD format. The individual disks included in the Release 11i software bundle are labeled as follows:
-> Start Here - Disk1
-> APPL_TOP - Disk n
-> Tools - Disk n
-> Database - Disk n

Is the NLS software included with the RapidInstallDVD bundle?
ans)No, the NLS software is not included with the RapidInstall bundle. You need to order the NLS supplement software separately for each additional language which you want to install along with Oracle 
Applications.

How do i create the stage area of the RapidInstall?
ans)For creating a stage area, the script adautostg.pl needs to be run as follows:
$cd /mn/cdrom/Disk1/rapidwiz
$perl adautstg.pl

How many directories are there in the stage area of Oracle Application?
ans)The following Six directoruis are there in the stage area of  Oracle Applications.
-> StartCD
-> oraApps
-> oraDB
-> oraiAS
-> oraAppDB
-> oraNLS (Optional only if you have a NLS Software)

What pre-install checks should I make to ensure a successful installation?
ans) -> Verify the correct users and groups have been created, as documented in installing Oracle Applications.
-> Verify the required disk space is available, as documented in installing Oracle Applications.
-> Verify the file system base install directories have write access granted to the user that will own the software (on UNIX the RapidInstall Wizard may be run as root but the install runs as the user that will on the software, therefore this user must have write access to the base install directories).
-> Verify the required ports are available for the installation.
-> Verify system parameters are sufficient for the Oracle Software (especially the database) to run.
-> Verify that all the OS patches have been applied as per Oracle documentation.

What is a Single node installation?
ans)Single node installation is the one in which all the servers (Concurrent Processing,Forms,Web,Reports), the database and all product directories are installed on a single node. In other words, in single node installation the entire Oracle Applications are installed on a single server.

Where is a single node installation generally used?
ans) The single node installation is generally used for smaller installations and also used for demonstration purpose.

What is an express configuration?
ans)Express configuration installs a fully configured single node system with either a fresh database or Vision Demo database. Only a few basic parameters such as database name, top level install directories and port settings needs to be specified in this and express configuration take care of installing Oracle Applications 
without any user intervention.

What is configuration file and why is it used?
ans)During installation RapidInstall asks many questions from the user. It saves all the configuration parameters you enter in a new configuration file (config.txt) which is uses to configure the system for the new installation. In case the installation fails the same configuration file can be used for restarting the installation without answering all the question again from the scratch. For multi-node installation, the configuration file is used for the installation in other nodes.

I have started RapidInstall but nothing is coming in the screen.What could be the reason?
ans)The display might not be set properly.Set the display and start RapidInstall again.

What is a multi-node installation?
ans)A multi node installation is the one in which the database tier and the application tier are installed across two or more nodes.

In a multi-node installation how do i find what services are running from which node?
ans)The context file sid_hostname.xml in $APPL_TOP/admin will have the  information about the same. It can also be requeired from the table FND_NODES. You can query the following columns SUPPORT_CP for concurrent manager, SUPPORT_FORMS for froms server, SUPPORT_WEB for the apache host and SUPPORT_ADMIN to know the admin tier.

What benefits do i get with multi-node installations?
ans)Multi-node installation distributes teh server process across different servers. For example, in a typical example of multiple node, the database is hosted in one physical server the apache is hosted from some other server, the forms are hosted in other server, the current manager and reports server is hosted from one other server. This helps in distributing the load across various servers and as a result the overall performance of the application system increases.

In case of multi-node installation i will have multiple APPL_TOP's. How will manage all the different APPL_TOP's?
ans)In earlier releases of the Oracle Application, this problem was there. In case of patching, patching needs to be done from all the different nodes of the APPL_TOP. But with the introduction of the concept of shared APPL_TOP, this problem has been resolved.

What is Shared APPL_TOP and how does it help in case of multi-node installation?
ans)In case of multi-node installation, various components of the middle tiers are hosted across different physical servers. Shared APPL_TOP means the APPL_TOP will be installed only in one of the physical servers and all other servers of the application will share the file system of the APPL_TOP. Any changes made in the shared APPL_TOP file system are immediately visible on all nodes.This helps a lot in managing the application system.In case of patching, it needs to be done only once as all the servers share the same APPL_TOP. With 11.5.10 release, RapidInstall creates a shared APPL_TOP by default for multi-node installation.

What is shared application tier file system?
ans)In a shared application tier file system installation, the APPL_TOP, the COMMON_TOP, and the applications technology stack (ORACLE_HOME) are installed on a shared disk resource mounted to each 
node in the system. These nodes can be used to provide standard application tier services, such as forms, Web and concurrent processing. Any changes made in the shared application tier file system are immediately visible on all nodes.

What operating systems can use the shared APPL_TOP?
ans)All RapidInstall platforms except Windows support a shared application tier infrastructure.

Can i shared the APPL_TOP across different platforms of operating system?
ans)No, sharing of the APPL_TOP is not possible across different platforms because the binaries and libraries of the application file system are platform specific.

As of now i am using two different APPL_TOP. Can i merge the existing APPL_TOP to go for the shared APPL_TOP model?
ans)Yes, you can merge APPL_TOPs which are spread across different nodes. The metalink docuemt 233428.1. mentions in details about doing the same.

Thanking you
--
Mohammad Shahnawaz
Oracle's Passion

Wednesday 3 July 2013

Oracle Application Administration Question and Answer

As salmao alaikum wa rahmatullah,

What is load balancing?
ans)Load balancing is a server process that monitors loading on all of the forms servers. Each of the forms servers runs a load balancing client which keeps the load balancing server apprised of its load.

How can you find how many forms users are connected to the application system from the operating system level?
ans) You can do the same by querying the f60webmx process and counting the same. You can use the following command to check this.
pe-ef|grep f60webmx|wc -l

What is reports server?
ans)Reports server is also a component of the middle tier and is hosted in the same node of the concurrent processing server. Reports server is used to produce business intelligence reports. Reports server is started by executable rwmts60 which is located at $ORACLE_HOME/bin 

How can you check from the operating system whether the reports server is up and running?
ans)Reports server can be checked from operating system by querying for the process rwmts60. you can check the same using the following command 
ps -ef|grep rwmts60

How can you compile a report manually?
ans) You can do the same uisng the adrepgen utility as shown below. 
adrepgen apps/<appspasswd> source=$PRODUCT_TOP/srw/filename.rdf dest=$PRODUCT_TOP/srw/filename.rdf stype=rdffile dtype=rdffile  logfile=<path_of_log> overwrite=yes batch=yes dunit=character

What is discoverer and why it is used?
ans)Discoverer is an intuitive adhoc query, reporting, analysis and Web-publishing tool that empowers business users at all levels of the organization to gain immediate access to information from data marts, data warehouses, online transaction processing system and oracle e-Business suite. The discoverer server comprises Oracle Discoverer 4i, a key component of the Oracle 9i Application Server(9iAS). Discoverer 
4i is tightly integrated with Oracle Applications which allow users to employ Discoverer to analyze data from selected business areas in human resources, purchasing, process manufacturing, financial and other products. The Discoverer server complements the reports server by allowing performance of adhoc queries and analysis of the resulting query output. It also allows users to perform projections based on possible changes to the business environment or other strategic factors.

How can you start discoverer server?
ans)Diescoverer start script addisctl.sh is available in the $OAD_TOP/admin/scripts/$CONTEXT_NAME location.Alternatively, you can also use the startall.sh script located at $ORACLE_HOME/discwb4/util directory.

What is the product directory in the APPL_TOP and what is the importance of the same?
ans) For each product there is a separate directory in the APPL_TOP. There are more than two hudnred products in the 11.5.10 release. The product directories are names with product's standard abbreviation 
like bis for Business Intelligence System ec for e-commerce.The product files are stored in the product directories.
<Prod_TOP> refers to <APPL_TOP>/prod/Version.
for example - $FND_TOP=$APPL_TOP/fnd/11.5.0
under each product top there are a large number of dirctories. If we go to FND_TOP directory we will see the following directories.
(appmgr01)emstestappl - bash $cd $FND_TOP
(appmgr01)11.5.0 -bash $pwd
/slot01/appmgr/emstestappl/fnd/11.5.0
(appmgr01)11.5.0 -bash $ls
3rd party fndenv.env html lib media patch secure xml admin froms include log mesg reports sql drivr bin help java mds out resource usrxit
(appmgr01)11.5.0 -bash $

What are the important configuration files available in APPLTOP?
ans)Following are the important configuration files available in the 
APPL_TOP.
APPLSYS.env/APPSORA.env
Adovars.env
SID.xml
Adconfig.txt
Adjareas.txt
Topfile.txt
Appsweb.cfg
Hostname_SID.dbc
Adpltfrm.txt
Adjborg.txt
Adjborg2.txt

What is the significance of the appsweb.cfg file and where is it located?
ans)This file defines the parameter values used by forms Web CGI. This is the main configuration file used by the forms. This file contains the following details:

-> Forms Server Name, ServerPort, Domain Name
-> Database Connection Parameters
-> JInitiator Version
The file is located at $OA_HTML/bin 

What is the significance of the DBC file and where is it located?
ans) The DBC stands for database connection.This is the file which is responsible for establishing a connection between the database and the APPL_TOP. The DBC file stores all the information for successfull connection to the database. The DBC file contains the value of GWYUID, FNDNAM and TWO_TASK & GUEST_USER_PWD.GWYUID stands for Gateway User ID and should have APPLSYSPUB/PUB as User ID/Password.The default User Id/Password for Oracle Application is guest/guest, guest/oracle, oracle/guest. This User ID/Password should match with the record available in the fnd_profile_option table.The location of this file is $FND_TOP/secure.

What is the significance of GWYUID?
ans)It is used to connect to database by thick clients.

what is the difference between GWYUID and GUEST_USER_PWD?
ans)GWYUID is used by thick clients to connect to the database. For example, Forms uses the GWYUID to get connected. Whenever a new forms connection is established, it uses APPLSYSPUB/PUB to authenticate the session,where as GUEST_USER_PWD (Guest/Oracle) is used by JDBC thin client.

If you go to the FND_TOP/secure directory, yo can see lots of DBC files located there. How do you find which one is used by the application system?
ans) To find out which DBC file is used by the application system you  can query for the porfile Application Database ID. if the profile name is SID then SID.dbc is used by the application.

What is the significance of the admin directory in the APPL_TOP?
ans) The $APPL_TOP/admin directory contains the scripts and the file which are used by the AD Utilities. The directory and running of ad utilities.
 Following are the important files in the $APPL_TOP/admin  directory.
-> <sid>.xml - This is the context file which is used by the Oracle Applications.
-> adpvars.env - This is an important configuration file about which we have already discussed.
-> <sid>/log - This directory contains all the logfiles which are generating during patching or by the running of Ad utilities
-> <sid>/out - This directory contains all the output files
-> Text files - This directory contains a large number of text files, which contains various information about the application system and are referred during autopatch

How can you change the password of the application users?
ans)The password of the application users as well as the password of all the schemas includding apps can be changed using the FNDCPASS utility. For running the FNDCPASS, you need to have the system and the 
apps password. FNDCPASS is run in the following manner:
FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME

What is '0' and 'Y' in flag in FND executables like FNDCPASS,FNDLOAD?
ans)'0' means the request id.Since the request is not submitted via the Concurrent Request submission forms, request id zeor is assigned to it. 'Y' indicates the method of invocation.It's invoked directly from the operating system and not through the concurrent request.

What are the tables which store the information about the various application users and their passwords?
ans)Two tables - FND_USERS and FND_ORACLE_USERID store the information about the application users and their passwords.

How can you delete an application user?
ans)You can't delete an application user but you can put the end date the application user making the user inactive.

In case of a multi-node installation, how can you check which service is being run from which node?
ans) There are two ways to find the same information.You can open the CONTEXT_FILE in the APPL_TOP/admin and check the information.
You can check for the FND_NODES table and check the columns, SUPPORT_CP (for Concurrent Manager) SUPPORT_FORMS(for Forms server), SUPPORT_WEB (Web Server), SUPPORT_ADMIN (Admin Server), and SUPPORT_DB for database tier.

What is OATM and what is significance?
ans)OATM refers to the Oracle Applications tablespace model. In the previous releases of Oracle Applications, there were two tablespaces for each product. One was for data and the other was for the index and there use to be a lot of overhead in managing all the tablespaces. The new tablespace model replaces the old tablespace model by 12 tablespaces making it lt easier to manage the tablespaces.

Is apps password stored in any flat file outside database?
ans)Yes the apps password file is stored in the file called wbdbr.app located at $IAS_ORACLE_HOME/Apache/modplsql/cfg.

Where are all the middle tier start/stop scripts located?
ans)The scripts for managing the middle tiers are located in COMMON_TOP/admin/scripts/<SID> directory.For running these scripts, login to the application tier as the owner of the application file system and source the environment using the environment <SID>.env located in the $APPL_TOP. All the scripts create a log file which shows the status of the server. The log file is written in the directory $COMMON_TOP/admin/log/<SID>. Each component of the middle tier has a separate log file.

What is the script for the start/stop of Apache?
ans)The Apache server can be started with the script adapcctl.sh. The parameters that accepts is start,stop and status.
adapcctl.sh {start|stop|status}
The Apache startup script is customized for the Oracle Applications in such a way that it takes of starting the Jserv,modplsql and the TCF socket server automatically once the apache is started.The log file which is created by the script is adapcctl.txt and it is located at the location of the log file.

What is the Script for starting/stopping the forms server?
ans)The forms server can be controlled with script adfrmctl.sh which is located at the common locations of all the scripts viz 
$COMMON_TOP/admin/scripts/<SID>. The froms server can be started/stopped in the following way.
adfrmctl.sh {Stop|Start|Status}
The log file which is created by the script is f60svrm.txt available at the common location of the log files.
Alternatively, the forms server can also be started manually without using the script with the f60ctl executable which is located at $ORACLE_HOME/bin. This is 8.0.6 Oracle Home and should not be confused with the Oracle Home of the database server.
The forms server can be started manually in the following way. 
f60ctl start port=<port name> mode=socket exe=f60webmx  logfile=/location of logfile.

How you can start the reports server?
ans) Reports server can be controlled with the script adrepctl.sh. It uses the executable FNDSVGRG which is located at $FND_TOP/bin. The defualt name of the reports server log file is rep60_<SID>.txt and is located at same place along with the log files of other components of  the middle tier.Reports server can be controlled by
adrepctl.sh {start|stop|status}

How you can start/stop all the middle tier components at one go?
ans)For starting and stopping all the midlle tiers, Oracle provides two different scripts which take care of the starting and stopping all the middle tiers at one go.For starting all the middle tiers, the script is adstrtal.sh. It takes the apps user id and apps password as parameters.
adstrtal.sh <appsusername>/<appspassword>
Similarly, for stopping all the middle tiers at one go, the script is adstpal.sh. This aslo takes the apps user id and apps password as parameters.
adstpall.sh <appsusername>/<appspassword>
Both the scripts create a log file in which it contains detailed information. The logfile name is in the following format <Month><Date><Hour><Minutes>.log It gives a formatted report in the log file with the details of which components are started, which are already running, which are disabled and which are not running.


Thanking you
--
Regards
Mohammad Shahnawaz
Oracle's Passion