Monday, 31 January 2011

ASM Command-Line Interface (ASMCMD) utility

Introduced in Oracle 10g release 2, the ASM Command-Line Interface (ASMCMD) utility provides an easy way to manipulate files within Automatic Storage Management (ASM) diskgroups. Its major functionality is to present an ASM file system in a user-friendly directory-tree structure. ASMCMD provides short commands for accessing the files and directories within ASM diskgroups.
The interface provides both interactive and noninteractive modes. The interactive mode enters a shell-like environment where the user is prompted to issue the commands. The noninteractive mode executes a single command and exits the utility. The latter is made available for scripting and batch-processing purposes.
You can invoke the ASMCMD tool with a -p parameter to always display the present directory inside the prompt itself.
Here is a brief description of ASMCMD commands:
pwd displays the absolute path of the current directory.
cd changes the current directory to the specify directory.
find finds under a specified directory all paths that match a given pattern.
ls lists aliases or its contents alphabetically by name if the alias is a directory.
mkdir creates directories.
rm removes the specified file as well as its system alias. If it is an empty directory, then rm removes it.
mkalias creates the specified user alias for the specified system alias.
rmalias deletes the specified user aliases, while preserving the files and their system aliases.
du displays the total space used for files located recursively under the specified directory.
lsdg lists all diskgroups and their attributes.
lsct lists all clients and their attributes.
help
displays list of commands

Oracle Apps R12 - Get the current Application release from sqlplus

select release_name from fnd_product_groups

RELEASE_NAME
----------------------------
12.0.6

Thursday, 27 January 2011

Steps to recover Applications context file if it is corrupted or deleted accidentally

The Applications context file can be retrieved by running the adclonectx.pl script.
To retrieve the Applications tier context file,
  • perl /clone/bin/adclonectx.pl retrieve
  • On being prompted for the context file to be retrieved, select the option of retrieving the
          Applications tier context file that has been lost and retrieve it to the default location specified
          by the script.
The above command can be used only when INST_TOP the is still intact. In case that has also been lost
accidentally, the Applications tier context file may be retrieved as follows:
  • Execute the following command on the Database tier: perl /appsutil/clone/bin/adclonectx.pl retrieve
  • On being prompted for the context file to be retrieved, select the option of retrieving the
          Applications tier context file that has been lost.
  • While confirming the location for the context file, set it to any existing directory with write permission.
  • Once the context file has been generated in the specified location, move it to the location specified
          for the context file in the context variable 's_contextfile'.
To retrieve the Database tier context file,
  • Execute the following command on the Database tier: perl /appsutil/clone/bin/adclonectx.pl retrieve
  • On being prompted for the context file to be retrieved, select the Database tier context file and
          retrieve it to the default location specified by the script.

Pinning Oracle Applications Objects into the shared pool

      Oracle Applications DBAs who want to improve database performance by pinning packages into shared pool.

Why pinning objects into the shared pool?
Oracle Applications requires space in the ORACLE System Global Area (SGA) for stored packages and functions. If SGA space is fragmented, there may not be enough space to load a package or function. You should pre-allocate space in the SGA shared pool for packages, functions, and sequences by "pinning" them.
Pinning objects in the shared pool can provide a tremendous increase in database performance, if it is done correctly. Since pinned objects reside in the SQL and PL/PLSQL memory areas, they do not need to be loaded and parsed from the database, which saves considerable time.
What objects to be pinned into the shared pool?
Most performance improvement can be gained from pinning large, frequently used packages. Pinned objects are expensive in terms of memory space, since other not-pinned objects need this memory space, too. In general do not pin all objects or rarely used objects - this could even decrease database performance.
As a general rule, you should always pin the following packages which are owned by SYS:
(see Note 61623.1 SHARED POOL TUNING)
STANDARD
DBMS_STANDARD
DBMS_UTILITY
DBMS_DESCRIBE
DBMS_OUTPUT
and maybe other SYS packages that are often used (DBMS_LOCK, DBMS_ALERT, etc.).
The Applications objects that should be pinned are harder to identify and will vary from site to site, depending on what the users are doing. To identify good candidates for pinning, you need to know which objects are being executed the most. To do this, let the system run long enough to reach a steady state (several days to a week). Then initiate a SQL*Plus session as system (or sys or apps) and run the following script $AD_TOP/sql/ADXCKPIN.sql. This will spool object execution and reload statistics into the output file ADXCKPIN.lst.
Example output:
OBJECT TYPE SPACE(K) LOADS EXECS KEPT
---------------------------------- ------------ -------- ------ ------- ----
APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE 15.2 1 9 NO
APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE BODY 13.7 1 8 NO
APPS.FND_CLIENT_INFO PACKAGE 2.7 1 206 NO
APPS.FND_CLIENT_INFO PACKAGE BODY 13.0 1 206 NO
APPS.FND_CONCURRENT PACKAGE 15.2 1 199 NO
APPS.FND_CONCURRENT PACKAGE BODY 24.2 1 197 NO

Another handy script:
SELECT substr(owner,1,10)||'.'||substr(name,1,35) "Object Name",
' Type: '||substr(type,1,12)||
' size: '||sharable_mem ||
' execs: '||executions||
' loads: '||loads||
' Kept: '||kept
FROM v$db_object_cache
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
AND executions > 0
ORDER BY executions desc,
loads desc,
sharable_mem desc;

Choose the objects with a high number of executions (EXECS) or very large (SPACE(K)), frequently used objects. If the decision is between two objects that have been executed the same number of times, then preference should be given to the larger object. From experience, very good results have been achieved with having pinned only about 10 packages.

How to pin object into shared pool?
The pl/sql scripts $AD_TOP/sql/ADXGNPIN.sql (packages, functions) and ADXSPPNS.sql (sequences) generate pinning scripts, which can be executed in Sql*Plus. Do not run them without having edited them, otherwise the scripts would try to pin all objects. Create your own script to pin the packages and pin them in a descending order according to their size.
The pl/sql command to pin a package (i.e. FNDCP_TMSRV) manually is:
SQL> execute dbms_shared_pool.keep('APPS.FNDCP_TMSRV');

Oracle DBA Interview questions

1)  How to Check the memory of the system while the time of Linux Installation ?
2)  How to assign hostname to a node ?
3)  How to assign IP to a node ?
4)  which are the files where kernel settings and security limits are stored ?
5)  How to check the swap space and physical memory ?
6)  How to do ftp from one node to another node ? Give Eg
7)  How to do copy between two machines ? Give Eg
8)  Give steps to enable ftp and telnet services ?
9)  How to do confiugre nfs server and do a nfs mount from the client machine ?
10) what's the use of TOP, ps commands ?
11) How to create user and groups in Linux ?
12) How to see if user and group already exists ?
13) How to check the release version of OS ?
14) How to check the kernel version of OS ?
15) How to check OS is 32bit/64bit?
16) How will you check whether required os level software has installed?
17) what is the command to check required rpms are installed?
18) How will install and upgrade rpms in linux?
19) What is oraInventory?default location for aix , Linux ?
20) what is oratab ?
21) How can you check the groups to a user belong ?
22) What is configuration file?(config.txt) and where is the default location
23) What is rapid install?
24) Explain Single Node and Multi Node installation ? ( Exp preinstall and postinstall tasks )
25) Explain single user and multi user installation ?
26) What is difference between express install and advanced install ?
27) What is difference between fresh database and vision database install types ?
28) What is minimum /  approx. disk requirement for 11.5.10 for  vis and fresh and stage ?  
29) What is staging area ? How you set up staging area ?
30) Is it possible to install apps without staging area ?
31) How will check perl, java versions?
32) where is location of staging logs ?
33) How can you clear the system after an incomplete installation ?
34) What's the difference between ORACLE_BASE and ORACLE_HOME
35) what is default port pool? what is default range and till what range it support?
36) If some of pre-install test has failed what will you do?
37) If the installtion has terminated before completion What will you do?
38) What are post installtion tests done by rapidwiz?
39) How will you check rapidwiz version?
40) Describe how will you do multinode installtion?
41) What are things will be checked during post installation tests?
42) What are required in post installtion steps additionally?
43) Where is the location of the Rapidwiz logs?
44) How could you Install technology stack alone?
45) What are the software require to maintain oracle application on windows ?
46) What are the Technology Stack Components?
47) What are the software required for Install Oracle Application on unix or linux machines ?
48) How can you check the version of Oracle Apps ?
49) What is Oracle Apps ?
50) Explain three tier architecture in Oracle Apps ( Including login workflow and middle tier services )
51) What is OATM ?
52) What is Oracle Jinitiator ? How can you check the jinitiator version ?
53) Where is location of Oracle Apache Cache and modplsql Cache ?
54) How many oracle Homes are there in 11i and what are they?
55) What is oracle applications technology layer ? what and why are these compnonents used for ?
56) what is Oracle Application Object Library ? How it differs for end user, developer and system admin ?
57) what is OAM ? What is OAM Login URL ?
58) What is Oracle Applications URL and Ebusiness Suite Apps Login URL ?
59) Which is the script location is Oracle apps for middle tier and db tier ?
60) where is the concurrent manger log and out location ?
61) what are environment files ? what are the different types of env files, and locations on both tiers ?
62) what are context files and locations for both tiers
63) what is APPLPTMP and APPLTMP  ?
64) what are they key environment file parameters ?
65) which is the script used to start and stop apache and concurrent manager ? Tell the steps
66) Brief out the file system for Oracle Applications Ebusiness Suite ( and explain each directory structure,
      its contents )
67) Whats the difference between APPS,APPLSYS,APPLSYSPUB users ?
68) what are the diff types of users available is oracle apps ?
69) What is Multiple Organization ? How can you check if MO is enabled ?
70) How can you check if multiple languages are installed with oracle apps ?
71) How can you check if an oracle apps installations in multi node or single node and in which
      node each services are running ?
72) How can you check how many database are up and running in both linux and windows machines
73) How can you check if concurrent manager and apache server is up and running
74) Where is apache access log and error log location
75) Where is the location of Oracle Alert logfiles and Trace files ( Give complete path )

Multi Org difference between 11i and R12

The difference in multi-org in 11i and R12 is "MOAC" concept(Multi-Org Access 
Control)
 
In R12 we have a feature call MOAC(Multi-Org Access Control) which 
allows the user to submit requests, enter, view and access data of 
different Operating Unit without having to switch between 
responsibilities. 

Profile Options:

MO: Operating Unit - It will restrict the access through responsibility to only one Operating Unit

MO: Security Profile - It enables the user to get access on more than one Operating Unit by defining a security profile with Multiple Operating Unit and assigning it to the responsibility (If MO: Security Profile is set, then MO: Operating Unit is ignored by the system)

MO: Default Operating Unit - It is used when we have more than one OU attached to a responsibility through security profile, among whihc you want a particular OU to be defaulted during transaction entry stages. When the responsibility is assigned with only one OU then that will become the default OU, without setting the profile option MO: Default Operating Unit

Tuesday, 25 January 2011

Oracle Apps R12 Change sysadmin password from sqlplus

Two methods to change the sysadmin password:
1- CPASS tool: This is an Oracle tool that allow change system and database user password's
like SYSADMIN, APPLSYSPUB, APPS, GL, AR.

FNDCPASS apps/appspwd 0 Y system/manager USER SYSADMIN newspassword

2- fnd_user_pkg tool: This package maintains oracle application user accounts
To change the sysadmin password execute the following script:

declare
   l_flag  boolean;
begin
  l_flag = fnd_user_pkg.change_password('sysadmin','newpassword');
end;

To check if the new password take place run the following:
select   fnd_web_sec.validate_login('sysadmin','newpassword')    from dual;

The expected result would be Y.

Complie oracle forms in Oracle Apps R12

Execute the following comman as applmgr user:

frmcmp_batch.sh module=$AU_TOP/forms/US/xxxx.fmb   userid=apps/password@SID
module_type=form Output_File=$PROD_TOP/forms/US/xxx.fmx

Monday, 24 January 2011

Oracle Apps R12 AutoConfig

Autoconfig is an Oracle Apps tool used to configure the application and database tiers.
Autoconfig maintains the environment specific values in an XML file called Oracle Application context file located under  $INST_TOP/appl/admin.
Autoconfig is run by executing a shell script called adautocfg.sh which in turn called another script called adconfig.sh under $AD_TOP/bin.
During the autoconfig process a new configuration files will be generated and replaces the old ones based on the context file and the template file. Template files generally located under $PROD_TOP/admin/template.

adautocfg.sh is located under:
For the database: $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
For the application: $INST_TOP/admin/scripts or $ADMIN_SCRIPTS_HOME

We need to run the autoconfig when we instructed by oracle support or when we change an application configuration.  

The last thing to mention is that the autoconfig must be run while the Application tier is down.

Sunday, 23 January 2011

Oracle Apps R12 - FNDLOAD

FNDLOAD can download data from an application into a portable, editable text file.
This file can then be uploaded into any other database to copy the data.

Conversion between database store and file format is specified by a configuration file that is read by the loader.
It downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). The Generic Loader can then upload this data to another database using a configuration file.

The loader operates in one of two modes: download or upload.
In the download mode, data is downloaded from the database to a text file;
in the upload mode, data is uploaded from a text file to the database.

The same configuration file may be used for both uploading and downloading.

When downloading, the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading. The data file has a standard syntax for representing the data that has been downloaded. When uploading, the Generic Loader reads a data file to get the data that it is to upload. In most cases, the data file was produced by a previous download, but may have come from another source. The data file cannot be interpreted without the corresponding configuration file available.

These are the extensive list which can be done through FNDLOAD
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Alerts
· Concurrent Manager Schedules

Oracle Apps R12 - Remote Diagnostic Agent RDA

RDA is a command-line diagnostic tool that is executed by an engine written in the Perl programming language. RDA provides a unified package of support diagnostics tools and preventive solutions .

Data captured provides Oracle Support with a comprehensive picture of the customer's environment which aids in problem diagnosis.

314422.1 is the Metalink Note where you can get every Information about RDA including Installation Instructions for Different Version of Operating Systems,

Oracle Apps R12 - Output Post Processor OPP

Concurrent processing uses the Output Post Processor (OPP) to enforce post-processing actions for concurrent requests.

Post-processing actions are actions taken on concurrent request output. An
example of a post-processing action is that used in publishing concurrent
requests with XML Publisher.

For example, say a request is submitted with an XML Publisher template
specified as a layout for the concurrent request output. After the oncurrent
manager finishes running the concurrent program, it will contact the OPP
to apply the XML Publisher template and create the final output.

A concurrent manager contacts an available OPP process when
a running concurrent request needs an OPP processing action.
A concurrent manager uses a local OPP process (that, is, on the same node) by default, but will choose a remote OPP if no local OPP process is available.


There should always be at least one OPP process active in the system.
If no OPP service is available, completed requests that require OPP processing will complete with a status of Warning.

An OPP service is multi-threaded and will start a new thread for each concurrent request it processes. You can control the number of simultaneous threads for an OPP service instance by adjusting the Threads per Process parameter for the instance.

If all the OPP services have reached their respective maximum number of threads, the requests waiting to be processed remain in a queue to be processed as soon as threads become available.

If request throughput has become slow, you may want to increase the number of threads per process for the OPP. It is recommended that you keep the number of threads per process between 1 and 20.

Saturday, 22 January 2011

Check version of Oracle E-Business suite from SQLPLUS

SQL> SELECT RELEASE_NAME FROM FND_PRODUCT_GROUPS;

RELEASE_NAME
--------------------------------------------------
12.0.6

Differences between Oracle Apps 11i and R12

At Database tier:
Database in 11.5.x was 9.2 and became 10.2 in R12.

At Application tier:
Tech stack on application tier in 11i consists of iAS 8.0.6 and Oracle Developer 6i (Forms and Reports)
On R12 its builds on fusion middleware which is iAS 10.1.3 and Developer 10.1.2

Directory structure on 11i:










Directory structure on R12:















Notice that a new top added called INST_TOP which contains the configuration, script and log files.

Speedup Clone Process in R12 and 11i

The normal way to do the clone process is by archiving  the Source node then copy the archived files to the Target node then extract the archived file back. The following will speedup the cloning process. In the following will avoid the two fold steps and do the operation in a single step.

eg. One of the clone steps is to move the ORACLE_HOME form the database node; To do this:
We have the Source node called PROD and the Target node called TEST:

On the Target node create the ORACLE_HOME directory if its not exists:
$ mkdir -p /u01/app/oracle/product/10.2.0./db

On the Source node:
$ cd  $ORACLE_HOME
$ tar cf - * | ssh TEST '(cd /u01/app/oracle/product/10.2.0./db; tar xf - )'

Now on the Target node check if the moving and extracting process is started:
$ cd /u01/app/oracle/product/10.2.0./db
$ ls -ltr

total 240
drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 jre
drwxr-x--- 7 oracle dba 4096 Oct 25 20:38 javavm
drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 has
drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 diagnostics
drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 demo
drwxr-x--- 6 oracle dba 4096 Oct 25 20:38 crs
drwxr-x--- 4 oracle dba 4096 Oct 25 20:38 clone
drwxr-x--- 7 oracle dba 4096 Oct 25 20:38 assistants
drwxr-x--- 4 oracle dba 4096 Oct 25 20:49 jdbc

In this way we:
1- Avoid the two fold process.
2- No need for extra disk space on the Target node nor Source node.  


Thursday, 20 January 2011

Oracle RAC summary

- Oracle Clusterware is the software that enables a database to be opened by more than one instance.
- In 9i was named Oracle Cluster Manager oracm; renamed in 10.1 to Oracle Cluster Ready Services and finally renamed to Oracle Clusterware(OC) in 10.2
- In 10.2 and above OC installation enhanced by adding the CLUVFY (Cluster Verification Utility) which can be used to check each OC installation stage requirements are met.
- OC must be installed on a separate home other than oracle database home.
- OC consists of three main components:
1- CSS (Cluster Synchronization Services): Used to manage cluster node membership.
2- CRS (Cluster Ready Services): Manage high availability operations.
3- EVM (Event Manager): Manage event notifications and callouts.
On UNIX those components implemented as daemons and entries added to the /etc/inittab to spawns those daemons in case of node reboot.
Before 10.2 the only way to start the CRS was by reboot the node. Starting form 10.2 CRSCTL used to start and stop the CRS.

- OC uses Two additional files that must be accessible by all nodes:
1- OCR disk: Used to store details of the cluster configurations including names and statuses of databases, instances, services and node applications such as listeners and VIP's.

OCR is used by OC and managed by client tools such as DBCA, SRVCTL and OEM.
 2- Voting(quorum) disk: Usedto determine the nodes that currently available in the cluster.
Used by the OCSSD to detect nodes that join or departs the cluster. For that its also called css voting disk.
- Both OCR and Voting must be stored on cluster file system or raw devices but not ASM.
- Both disks can be mirrored to eliminate the potential of SPOF.
- One mirror for the OCR and two for the voting.
- Initial size for the OCR is 100 MB and 20 MB for the voting.

Node Applications:
- Those applications are automatically configured on each node during the OC installation.
- They will be automatically started if they enabled when the node is booted. They also can be started and stopped by SRVCTL utility.
1- Listener:
Run on each node to listent to incoming connection requests from the clients.
Can be managed by LSNRCTL and SRVCTL.
In RAC environment its configured on the OCR to run as a node application.
2- ONS (Oracle Notification Service):
Used by OC to propagate messages both within RAC cluster and clients and application-tier systems.
Uses publish-and-subscribe method to genearte and deliver event messages.
ONS provide the foundations for FAN which in turn provides the basis for FCF.
3- FAN ( Fast Application Notification):
Is a mechanism by by which RAC notifies other processes about changes in configuration and service level.
Also used to notify applications about service status changes such as start/stop of instances and services.
FAN publish also load-balancing advisory events. Application can take advantage of those events to direct work requests to the instances that is providing the best level of service.
4- FCF ( Fast Connection Failover):
Its used to prevent new connections to be directed to the failed nodes or instances.


It works with integrated connection pools in the application servers and clients.
Oracle clients that provide FCF are JDBC, OCI and ODP.NET.
5- VIP (Virtual IP):
Alternate IP to the public IP addresses.
have the same subnet as the public IP addresses.
Configured using the VIPCA utility.  If a node failed then its VIP address fails over to another another surviving node.
Used to redirect the client connection request to a failed node to another surviving node.
This eliminates the TCP timeout issue which it takes a longer time.
6- GSD (Global Services Daemon):
Allows clients (srvctl,dbca,..) to execute administrative commands such as instance startup and shutdown.
In 9i it was implemented as a java program, managed by GSDCTL and run as a daemon.
In 10.1 and above its implemeted as a node application and can be only managed by SRVCTL.






Oracle EBS - Configure Parallel Concurrent Processing PCP R12

Parallel Concurrent Processing Configuration:

1. Execute AutoConfig by running $INST_TOP/admin/scripts/adautocfg.sh on all concurrent manager nodes.2. Source the application environment.

3. Check the configuration files tnsnames.ora and listener.ora located at
$INST_TOP/ora/10.1.2/network/admin.
Ensure that you have information of all the other concurrent nodes for FNDSM and FNDFS entries.

4. Log on to Application as SYSADMIN, Then choose the System Administrator Responsibility.
Navigate to Install > Nodes screen and ensure that each node in the cluster is registered.

5. Verify whether the Internal Monitor for each node is defined properly with correct primary and secondary nodes specification and work shift details. Also ensure that the Internal Monitor manager is activated by going to Concurrent > Manager > Administrator and activating the manager.
Also the work shifts were not present. Set them as 24 hours and the processes was set to 1.Go to the Concurrent > Manager > Administrator screen and activate these managers.

6. Set the $APPLCSF environment variable on all the concurrent processing nodes pointing to a log directory on a shared file system. To do this edit the context file as follow:
$ grep -i applcsf $CONTEXT_FILE
/u01/app/PROD/inst/apps/$CONTEXT_NAME/logs/appl/conc
Changed it to :
/u01/app/PROD/inst/apps/DBNAME/logs/appl/conc
Do not run autoconfig yet, you have to make more changes in the below step.

7. Set the $APPLPTMP environment variable on all the concurrent processing nodes to the value of the UTL_FILE_DIR entry in init.ora on the database nodes. This value should be pointing to a directory on a shared file system. We need this to be on a file system, that is shared across all the concurrent manager nodes and is also writable by the owner of the oracle db.
If the UTL_FILE_DIR need to be changed; then change it and restart the DB.
Same APPLPTMP the APPLTMP needs to be point to a shared file system.

8. After those modifications Run the autoconfig.

9. Set profile option ' Concurrent: PCP Instance Check' to OFF if database instance-sensitive failover is not required. By setting it to 'ON', Concurrent Managers will fail over to a secondary Application tier node if the database instance they are connected to fails.

Set Up Transaction Managers
1. Shut down the application services (servers) on all nodes.

2. In the Oracle RAC environment, shutdown all the database instances cleanly with the command:
srvctl stop database -d PROD

3. Edit $ORACLE_HOME/dbs/_ifile.ora. Add the following parameters:
_lm_global_posts=TRUE
_immediate_commit_propagation=TRUE

4. Start the instances on all database nodes, one by one.

5. Start up the application on all application nodes.

6. Log on to Oracle E-Business Suite Release 12 using SYSADMIN login and
System Administrator Responsibility.Navigate to Profile => System and change the
profile option Concurrent: TM Transport Type to 'QUEUE' and verify the transaction manager works across the RAC instance.

7. Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.

8. Restart the concurrent managers.

9. If any of the transaction managers have a deactivated status, activate them using
Concurrent > Manager > Administrator.

Wednesday, 19 January 2011

Differences between 9i and 10g that affects RAC

1- Oracle Clusterware: In 9i was called oracm cluster manager; renamed to Oracle Cluster Ready Services     10.1. Finally in 10.2 called Oracle Clusterware.

2- Virtual IP: In 9i it was take a long time to recover clients connection after node failure.
Introduced in 10.1 VIP used instead of Public IP. When a node failure happens the client connection    
transferred from the failed node to another node using the VIP.

3- ASM ( Automatic Storage Management ): This feature introduced in 10.1 that is an alternative to the volume manager. It also provide double or triple mirroring. The only exception in ASM that it allow to store Oracle Database files  and associated files like archive redo logs and flash recover area files.

4- AWR (Automatic Workload Repository): Introduced in 10.1; Records an extended set of database usage statistics in the database for subsequent analysis and performance tuning. Its an evolution of STATSPACK.

5- ASH (Active Session History): Introduced in 10.1; Records wait events for each session. This information is initially stored in the SGA and subsequantaly summerized and flushed to the database to be kept for time-based performance tuning.


6- FAN(Fast Application Notification): Introduced in 10.1;allows databases, listeners, application servers and clients to receive rapid notifications of database events such as start/stop database, instances or services.
Well-written application can be also utilize this to reconnect to the instance without end user aware.

7- ADDM(Automatic Database Diagnostics Monitor):Introduced in 10.1; It provides the same STATSPACK reports functionality plus detecting problems and provide resolutions.

7- Database services: Introduced in 8i and enhanced in 10.1; It's logical grouping for sessions doing the same kind of work. Service later assigned to a Preferred instance(s) and Available(backup) instance(s) 

8- Database scheduler: Introduced in 10.1 and replaced DBMS_JOB package. Its more felxable by allowing jobs to be grouped in classes, job chains and windows to be created and external jobs such as shell scripts to be executed.
 
9- CVU (Cluster Verification Utility): Introduced in 10.2 to provide a method for verifying each stage of RAC installation process.

10- OEM (Oracle Enterprise Manager): This tool is converted from Java application to HTML web application and extended greatly. Two types of OEM available:
   A- Grid Control: Allow managing multiple nodes, database, instances and more.
   B- Database Control: This allow managing single database node.



    

Monday, 17 January 2011

Find concurrent manager nodes on Oracle Apps

1- Use the following query:
   sqlplus>  select node_name from apps.fnd_nodes where support_cp='Y';

2- Check the variable < TIER_NODE oa_var="s_isConc" >YES< / TIER_NODE> on the context file:
    $ grep s_isConc $CONTEXT_FILE
    YES means that this node is a concurrent manager node.

3- Check for the foundation library (FNDLIBR) processes
     $ ps -ef  | grep FNDLIBR

4- Check the report server processes:
      $ ps -ef | grep ora_rw

Sunday, 16 January 2011

R12: Change Application user password and end date from sqlplus

DECLARE
  v_user_name     VARCHAR2(80) := 'GFMISDBA';
  v_new_password  VARCHAR2(80) := 'oracle123';
  v_hard_password VARCHAR2(1) := fnd_profile.VALUE('SIGNON_PASSWORD_HARD_TO_GUESS');
BEGIN
  fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
                 ,val  => 'N');
  -- Call the procedure
  fnd_user_pkg.updateuser(x_user_name            => v_user_name
                         ,x_owner                => 'CUST'
                         ,x_unencrypted_password => v_new_password
                         ,x_end_date             => SYSDATE + 10000);
  fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
                 ,val  => v_hard_password);
COMMIT ;
EXCEPTION
  WHEN OTHERS THEN
    fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
                   ,val  => v_hard_password);
    RAISE;
END;

Basic Unix commands

cat           Sends file contents to standard output. This is a way to list the contents of short files to the screen. It works well with piping.
       cat .bashrc   Sends the contents of the ".bashrc" file to the screen.


cd            Change directory
       cd /home      Change the current working directory to /home. The '/' indicates relative to root, and no matter what directory you are in when you execute this command, the directory will be changed to "/home".
      

cd httpd      Change the current working directory to httpd, relative to the current location which is "/home". The full path of the new working directory is "/home/httpd".
      

cd ..  Move to the parent directory of the current directory. This command will make the current working directory "/home.
      

cd ~   Move to the user's home directory which is "/home/username". The '~' indicates the users home directory.
cp            Copy files
       cp myfile yourfile   Copy the files "myfile" to the file "yourfile" in the current working directory. This command will create the file "yourfile" if it doesn't exist. It will normally overwrite it without warning if it exists.
      

cp -i myfile yourfile      With the "-i" option, if the file "yourfile" exists, you will be prompted before it is overwritten.
      
cp -i /data/myfile . Copy the file "/data/myfile" to the current working directory and name it "myfile". Prompt before overwriting the file.
      
cp -dpr srcdir destdir     Copy all files from the directory "srcdir" to the directory "destdir" preserving links (-p option), file attributes (-p option), and copy recursively (-r option). With these options, a directory and all it contents can be copied to another directory.


dd     dd if=/dev/hdb1 of=/backup/       Disk duplicate. The man page says this command is to "Convert and copy a file", but although used by more advanced users, it can be a very handy command. The "if" means input file, "of" means output file.


df            Show the amount of disk space used on each mounted filesystem.
less   less textfile Similar to the more command, but the user can page up and down through the file. The example displays the contents of textfile.

ln            Creates a symbolic link to a file.
       ln -s test symlink   Creates a symbolic link named symlink that points to the file test Typing "ls -i test symlink" will show the two files are different with different inodes. Typing "ls -l test symlink" will show that symlink points to the file test.

locate        A fast database driven file locator.
       slocate -u    This command builds the slocate database. It will take several minutes to complete this command. This command must be used before searching for files, however cron runs this command periodically on most systems.
       locate whereis       Lists all files whose names contain the string "whereis".

logout        Logs the current user off the system.

ls            List files
       ls     List files in the current working directory except those starting with . and only show the file name.
       ls -al List all files in the current working directory in long listing format showing permissions, ownership, size, and time and date stamp

more          Allows file contents or piped output to be sent to the screen one page at a time.
       more /etc/profile    Lists the contents of the "/etc/profile" file to the screen one page at a time.
      
ls -al |more  Performs a directory listing of all files and pipes the output of the listing through more. If the directory listing is longer than a page, it will be listed one page at a time.

mv            Move or rename files
       mv -i myfile yourfile      Move the file from "myfile" to "yourfile". This effectively changes the name of "myfile" to "yourfile".
      
mv -i /data/myfile . Move the file from "myfile" from the directory "/data" to the current working directory.

pwd           Show the name of the current working directory
       more /etc/profile    Lists the contents of the "/etc/profile" file to the screen one page at a time.

shutdown             Shuts the system down.
       shutdown -h now      Shuts the system down to halt immediately.
       shutdown -r now      Shuts the system down immediately and the system reboots.

whereis              Show where the binary, source and manual page files are for a command
       whereis ls    Locates binaries and manual pages for the ls command.
      

Create a context file on Oracle Apps R12

For the dbTier Under $ORACLE_HOME/appsutil/bin
./adbldxml.pl tier=db appsuser=apps appspass=apps

For the appTier Under $AD_TOP/bin

perl adbldxml.pl tier=apps appsuser= appspasswd= (apps password )

Monday, 10 January 2011

Apply Application patch on Oracle Applications r12

We will use the Autopatch utility called adpatch:

1- Download the patch from Metalink.

2- Extract the patch:
$ cd /u01/patches
$ unzip patchname.xp
This will create a directory with the same patchname. Change the owner of this directory
to be the application user:
$ chown -R applprod:oradba /u01/patches/patchname


3- Stop the application tier:

$ su - applprod
$ cd $ADMIN_SCRIPTS_HOME
$ adstpall.sh apps/apps

4- Enable the maintenance mode:
$ adadmin
Select [5] Chnge maitinance mode
Select [1] Enable maitinance mode

5- Move to the patch directory
$ cd /u01/patches/patchname

6- run the Autopatch utility
$ adpatch

7- After successful patching Disable the maintenance mode.
$ adadmin
Select [5] Change maintenance mode
Select [2] Disable maintenance mode

8- run the application tier back.
$ cd $ADMIN_SCRIPTS_HOME
$ adstrtal.sh apps/apps

Sunday, 9 January 2011

Count the concurrent users in apps r12

select count(distinct d.user_name) from
apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

Create Application User with sqlplus in Apps R12

-- Create a Oracle Application user with the system administrator
-- and application developer responsibilities. Just the "UserName"
-- and "Your complete name" to

BEGIN
fnd_user_pkg.CreateUser(x_user_name => 'UserName'
,x_owner => 'CUST'
,x_unencrypted_password => '12345'
,x_description => 'Your complete name'
);

fnd_user_pkg.AddResp(username => 'UserName'
,resp_app => 'FND'
,resp_key => 'APPLICATION_DEVELOPER'
,security_group => 'STANDARD'
,description => 'Applicatie Developer'
,start_date => SYSDATE
,end_date => NULL
);

fnd_user_pkg.AddResp(username => 'UserName'
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'System admin'
,start_date => SYSDATE
,end_date => NULL
);
COMMIT;
-- The commit here is very important
END;

Friday, 7 January 2011

Oracle installation: Oracle Binary Cloning


Oracle installation: Oracle Binary Cloning

Clone the oracle software between two server.

Please check the OS compatibility are same between source & destination server.


Source Server:
****************

steps 1:

ORACLE_HOME: /apps/oracle/product/10.2.0.1

Tar the oracle home 10.2.0.1

$cd /apps/oracle/product

$tar -cvf 10.2.0.1.tar 10.2.0.1


Destination server:
******************

Steps 2:

Copy the oracle binary tar file from source server to Destination server.

$mkdir -p /apps/oracle/product

$cd /apps/oracle/product
$sftp oracle@source
$cd /apps/oracle/product
$get 10.2.0.1.tar
$bye

$tar -cvf 10.2.0.1.tar


Steps 3:


Create the oratab file in appropriate path.

Enter the detail first in oratab: Eg..

Cat Oratab
cd /var/opt/oracle/oratab
testdb:/apps/oracle/product/10.2.0.1:N

Steps 4:

Invoke the oraenv ( Optional method)

I need to create the testdb instance in dest server.

cd $ORACLE_HOME/bin
export ORACLE_HOME=/apps/oracle/product/10.2.0.1

Source the SID ----- Here it is testdb
. oraenv
testdb

Steps 5:

Create the oraInst.loc in appropriate path.

Edit the oraInst.loc
/var/opt/oracle/oraInst.loc
inventory_loc=/apps/oracle/product/10.2.0.1/oraInventory

Steps 6:

./runInstaller -clone -silent -ignoreSysPrereqs ORACLE_HOME="/apps/oracle/product/10.2.0.1" ORACLE_HOME_NAME="OHOME10"


Steps 7:

Execute the relink all

$Cd $ORACLE_HOME/bin

$ls -lart relink*

$relink all

Note:
changePerm.sh is a script that allows users that do not belong to the "dba" group to access and execute files in the ORACLE_HOME directory.

you need to run the changeperm.sh file in $ORACLE_HOME/install

Oracle RAC Background Processes

RAC Background Processes:

1. Lock Monitor Processes ( LMON)
2. Lock Monitor Services (LMS)
3. Lock Monitor Daemon Process ( LMD)
4. LCKn ( Lock Process)
5. DIAG (Diagnostic Daemon)

1. Lock Monitor Processes ( LMON)

It Maintains GCS memory structures.
Handles the abnormal termination of processes and instances.
Reconfiguration of locks & resources when an instance joins or leaves the cluster are handled by LMON ( During reconfiguration LMON generate the trace files)
It responsible for executing dynamic lock remastering every 10 mins ( Only in 10g R2 & later versions).
LMON Processes manages the global locks & resources.
It monitors all instances in cluster, primary for dictionary cache locks,library cache locks & deadlocks on deadlock sensitive on enqueue & resources.
LMON also provides cluster group services.
Also called Global enqueue service monitor.

2. Lock Monitor Services (LMS)


LMS is most very active background processes.
Consuming significant amount of CPU time. ( 10g R2 - ensure that LMS process does not encounter the CPU starvation).
Its primary job is to transport blocks across the nodes for cache-fusion requests.
If there is a consistent-read request, the LMS process rolls back the block, makes a Consistent-Read image of the block and then ship this block across the HSI (High Speed Interconnect) to the process requesting from a remote node.
LMS must also check constantly with the LMD background process (or our GES process) to get the lock requests placed by the LMD process.
Each node have 2 or more LMS processes.

GCS_SERVER_PROCESSES --> no of LMS processes specified in init. ora parameter.
Above parameter value set based on number of cpu's ( MIN(CPU_COUNT/2,2))
10gR2, single CPU instance,only one LMS processes started.
Increasing the parameter value,if global cache activity is very high.
Also called the GCS (Global Cache Services) processes.

Internal View: X$KJMSDP

3. Lock Monitor Daemon Process ( LMDn)

LMD process performs global lock deadlock detection.
Also monitors for lock conversion timeouts.
Also sometimes referred to as the GES (Global Enqueue Service) daemon since its job is to manage the global enqueue and global resource access.
LMD process also handles deadlock detection and remote enqueue requests.
Remote resource requests are the requests originating from another instance.
Internal View: X$KJMDDP

4. LCKn ( Lock Process)

Manages instance resource requests & cross instance calls for shared resources.
During instance recovery,it builds a list of invalid lock elements and validates lock elements.

5. DIAG (Diagnostic Daemon)

Oracle 10g - this one new background processes ( New enhanced diagnosability framework).
Regularly monitors the health of the instance.
Also checks instance hangs & deadlocks.
It captures the vital diagnostics data for instance & process failures.

Number of Visitors