Tuesday, November 29, 2011

Oracle SOA essential for SOA Administrators and Developers

Hi,
Oracle SOA 11g essential which can ge useful for SOA Administrators and Developers
can be listed as given below:

Understanding web services:

Useful links are:

http://www.w3schools.com/wsdl/wsdl_intro.asp
http://www.w3schools.com/wsdl/wsdl_uddi.asp
http://www.oasis-open.org/committees/download.php/3343/oasis-200304-wsrp-specification-1.0.pdf
http://www.oracle.com/technetwork/middleware/bpa/overview/index.html
http://www.w3schools.com/xsl/

Understand ESB:

An "Enterprise Service Bus" (ESB) is a system to which all services are connected.
Through the enterprise service bus all connected services can also be accessed.

Here is an illustration of an enterprise service bus which acts as central "bridge" or "gateway" to all applications exposing services
underneath it.


Understand BPEL:

BPEL (Business Process Execution Language) for Web services is an XML-based language designed to enable task-sharing
for a distributed computing or grid computing environment which can be use evenly across multiple organizations and using a combination of Web service.
BPEL is the most feature rich component in SOA suite.
BPEL is use in large extent to orchestrate the web services.

Understanding synchronous and Asynchronous BPEL process:

Link which will be useful.

http://suryaveer-chauhan.blogspot.com/2009/06/asynchronous-process-vs.html

Understand BAM:

Oracle Business Activity Monitoring (Oracle BAM) gives business executives the ability to monitor their business services and processes in the enterprise, to correlate KPIs down to the actual business process themselves, and most important,
to change business processes quickly or to take corrective action if the business environment changes.

Note: Oracle BAM is a component of both SOA Suite and BPM Suite.

when we install Oracle SOA suite 11g,we can install BAM component if we have enough memory in our Operating system(Usully required 8 GB of RAM
for good functioning) as I implemented in RHEL 5.3 (64-bit) machine.It is good practise to involve the latest version of Oracle SOA suite
always.I've installed 11.1.1.5.0 on Windows and Unix-based machines.

Understand OWSM:

Oracle Web Services Manager(OWSM) offers a comprehensive and easy-to-use solution for policy management and security of service infrastructure. It provides visibility and control of the policies through a centralized administration
interface offered by Oracle Enterprise Manager.

We will get OEM console link when we configure the weblogic domain.When we see the Home page after login after login with Oracle Fusion Middleware
11g OEM console.The admin console is also useful for lot of SOA admin tasks as well as OEM console.

Understanding ODI:
*ODI stands for Oracle Data Integrator.This application is useful in processing large amount of data.
*It works on the principle of Estraction,Loading and Transformation(ELT) unlike ETL tools where we have extract transform and load.
*In short ODI can be called as 'ELT tool of SOA'

Understanding OBR:
*OBR stands for Oracle Business Rules is a tool for writing the Business rules seperately from the code.
*This becomes very vital when you want to understand the Business rules and code of development separately.
*We can see Business rules with the help of User Interface provided by this tool.

Understanding SR:
*SR stands for Service Registry.It is used to keep track of all the services that are present in my portfolio(Which consists of past,present and future services).

Oracle JDeveloper:
*As the name says it stands for Oracle Java Developer where you integrate Oracle,java,web,web services and XML services.
*Oracle JDeveloper integrates development features for Java, SOA, Web 2.0,Database, XML and Web services into a single development tool.
*Oracle JDeveloper integrates the full set of features needed for developing composite applications with specific features that address the technologies like Database,java,web,web services and XML.
*Oracle JDeveloper integrates the full set of features needed for developing composite applications with specific features that address the technologies like Database,java,web,web services and XML
*Oracle JDeveloper is the development environment for Oracle SOA Suite and the Oracle WebCenter Suite.

Understanding Oracle WebLogic Server:

*An enterprise-ready Java application server.
*Supports the deployment of mission-critical applications in a robust,secure,highly available and scalable environment.
*Oracle WebLogic Server is an ideal foundation for building
applications based on service-oriented architecture (SOA).

Understanding Domain,Admin server and Managed servers in Weblogic Server:

Domain:
*Logically related group of Java components.

Administration Server:
*Special WebLogic Server instance which is the central point from which you
configure and manage all resources in the domain.

Managed Servers:

*Additional WebLogic Server instances where we deploy Java components,
such as Web applications, EJBs, and Web services.

*For production environments that require increased application performance,throughput, or high availability, you can configure
two or more Managed Servers to operate as a cluster.

*Oracle instance contains one or more system components

Understanding Oracle SOA Suite:

*Complete set of service infrastructure components for designing,
deploying, and managing composite applications.
*Enables services to be created, managed, and orchestrated into
composite applications and business processes.
*Composites enable you to easily assemble multiple technology
components into one SOA composite application.

Understanding SOA Development Challenge:

*There are various standards such as BPEL, WSDL, JCA,
Human workflow, Rules etc in the SOA solution
*As number of technologies increase, so does number of tools a
and skills required to build, deploy and manage them.
*Because each tool throws its own data, it can be
frustrating trying to make all of them work together.

Understanding What is needed?:

*There should be a “standardized way” by which disparate SOA components like BPEL, OSB, Adapters, Human workflow, Rules etc can work together.

*They should be managed from a single console.

*Logically related components should be deployable together,if possible.

*This “standardized way” is a framework called SCA.

Understanding SCA:

*Stands for Service Component Architecture

*SCA proposes the concept of “Composite”

*SCA is actually the platform on which the components come together,
and create a composite.It is technology and vendor neutral.

*SCA Programming Model provides the following
->Creating service components written with a wide range of technologies,
including programming languages such as Java, BPEL, C++, and declarative languages such as XSLT.
->The use of specific programming languages and technologies (including web services)is not required with SCA.
->Assembling the service components into a SOA composite application.
In the SCA environment,service components are the building blocks of applications.

Note: Oracle SOA Suite 11g leverages SCA

Understanding How Oracle SOA Suite 11g leverages SCA:

SCA simplified the entire lifecycle:

1)Development
->JDeveloper can be used to develop all components.

2)Deployment
->All components are grouped together in a composite and
during compilation,we get a single file called SAR.
->SAR can be versioned as one, deployed as one and managed as one

3)Monitoring
->EM allows to trace the entire flow,giving ability to drill down into
components of a composite.

Understanding Service Components:

*Service components are the building blocks that you use to
construct a SOA composite application.

The following service components are available:

*BPEL- provide process orchestration.

*Human Task =>provide workflow modeling.

*Business Rules =>enable designing a business decision based on rules.

*Mediators =>route events (messages) between different components.

*Spring =>enables you to integrate Java interfaces into SOA composite applications

->There is a corresponding service engine of the same name for each service component.

->All service engines can interact in a single composite.

Understanding Binding Components:

*Binding components establish a connection between a SOA composite and the external world.

There are two types of binding components
The following service components are available:

1)Services provide the outside world with an entry point to the SOA composite application.
2)References enable messages to be sent from the SOA composite application to
external services in the outside world

*There is a corresponding service engine of the same name for each service component.

*All service engines can interact in a single composite.

Examples of Binding Components are:


1)Web service SOAP over Http:Use for connecting to standards-based services using SOAP

2)JCA Adapters:Use for integrationg services and references with technologies(like databases,file systems ftp servers)

3)B2B Binding component:Use for browsing B2B metadata in the MDS repository and selecting document defintions

4)ADF -BC Service:Use forconnecting Oracle Application Development Framework(ADF) applications using SDO(Service Data Object) with SOA platform.

5)Oracle Applications:Use for integrating Oracle Application Adapter with Oracle Applications.

Like these se have lot of Binding components.We have to explore each and every component in SOA Suite 11g,Design various composite applications,
Deploy them on weblogic by using Oracle JDeveloper or Weblogic server.
SR(Service Registry) can be useful to keep track of all the web services and orchestrating this web services using BPEL.



Happy SOA learning...

Best regards,

Rafi.

Friday, November 25, 2011

How to tune oracle Database?

Collecting and analyzing the AWR reports for Oracle Database:


Hi,
Tuning the Database is very much essential not only for better execution of SQL statement but also for applications running well using that Database.AWR(Automatic workload repository) report gives us clear picture in Deciding and tuning Database as well as SQL statement.As Enterprise Manager we can see it easily but Enterprise manager needs license(additional cost) so some companies does not want to use Oracle Enterprise Manager.Oracle gives us flexible option in which we can generated awr report in html and plain format.
It is better to generate in html format for seeing it clearly by using any web browser.Since performance tuning is a deep ocean I will be updating this thread based on my problems faced and the method which work fine for tuning purpose.


Collecting awr report from SQL prompt:

Login to the Database as 'sys' user(SYSDBA) and make sure Database is up & running and Oracle Environment for the particular Database
is set.we can gather the awr report using 'awrrpt.sql'.

Note:If we want the awr report in Oracle RAC environment than we have to use 'awrgrpt.sql' script,as there you have to gather the report for multiple instances running on various nodes.

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3628069655 TESTDB 1 TESTDB


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified: html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 3628069655 1 TESTDB TESTDB TESTNODE1.comp.com



Using 3628069655 for database Id
Using 1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
TESTDB TESTDB 5590 24 Nov 2011 00:30 1
5591 24 Nov 2011 01:30 1
5592 24 Nov 2011 02:30 1
5593 24 Nov 2011 03:30 1
5594 24 Nov 2011 04:30 1
5595 24 Nov 2011 05:30 1
5596 24 Nov 2011 06:30 1
5597 24 Nov 2011 07:30 1
5598 24 Nov 2011 08:30 1
5599 24 Nov 2011 09:30 1
5600 24 Nov 2011 10:30 1
5601 24 Nov 2011 11:30 1
5602 24 Nov 2011 12:30 1
5603 24 Nov 2011 13:30 1
5604 24 Nov 2011 14:30 1
5605 24 Nov 2011 15:30 1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 5604
Begin Snapshot Id specified: 5604

Enter value for end_snap: 5605
End Snapshot Id specified: 5605


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_5604_5605.html. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: awrrpt_NOV24_2011_2_30_3_30_PM.html


SQL> exit
We will see the html format of the awr report in the current operating system path.

[oracle@TESTNODE1 ~]$ ls -altr awr*
-rw-r--r-- 1 oracle dba 458371 Nov 24 14:02 awrrpt_1_5590_5603.html
-rw-r--r-- 1 oracle dba 390564 Nov 24 16:31 awrrpt_NOV24_2011_2_30_3_30_PM.html

We can copy this html file using copying tool(winscp or ftp) to our machine and review using web browsers(mozilla or IE supported
versions).


Analzing the awr report and suggesting possible recommendations:
Once we obtain the awr report our main motive is to analyze the awr report and come up with possible recommendations.Depending
on the size of our Production Database we can come up with possible recommendations.This recommendation should be first implemented in
test environment and after successful results should be adopted in production environments.

1) Redo logs:

We need to make sure our redo logs are large enough.Check the number of log switches, one every twenty minutes is ideal, more than this is too high and you should make them larger to reduce the number of switches.

We can find the log switches in the Instance Activity Stats part of the awr report.

Example:

Instance Activity Stats - Thread Activity

* Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic Total per Hour
log switches (derived) 2 2.00

We can see in this system there are 2 log swtiches per hourly basis,which is good.So this tells us the redo logs are large enough.

2)Parsing:

Check the hard parsing amount.It should be zero.If it is not, this indicates that our SGA is probably too small,increase the size of SGA and test again.
Hard parsing is caused by use of literals in SQL (as opposed to bind variables).If the queries in question are our own,we should change them to use bind variables.

We can find this information on the first page.


Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
...
Parses: 33.9 7.2
Hard parses: 0.5 0.1
...

We can see in this system the hard parses is almost zero, which is good.

Now coming to the SGA we can focus on the below considerations:

3)Buffer hit and Library hit percentages:

Check the buffer hit and library hit percentages. We want them to be 100%, if not we should increase the size of SGA.This is also on the first page:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.82 Redo NoWait %: 100.00
Buffer Hit %: 99.52 In-memory Sort %: 100.00
Library Hit %: 98.63 Soft Parse %: 98.60
Execute to Parse %: 50.96 Latch Hit %: 98.16
Parse CPU to Parse Elapsd %: 66.67 % Non-Parse CPU: 97.75

In this case they are also good.

4)Top 5 Timed Foreground Events:

Check the average wait times.Anything over 5ms indicates a problem.If we see database CPU events in the Top 5, this indicates that SGA is too small.We may also be missing indexes.Check the optimizer statistics.

Here are the Top 5 from my environment:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 15 59.9
log file sync 1,592 8 5 32.3 Commit
sort segment request 1 1 1001 4.0 Configurat
db file sequential read 216 1 4 3.6 User I/O
db file scattered read 64 0 6 1.5 User I/O

we can see here that the major issue is DB CPU, which generally indicates SGA is too small. However, in this case it is high because this report was run on a VM with the database and BPM sharing the CPU and disk.

Database file sequential/scattered read

These indicate time spent doing table scans and index scans (respectively).If these are high (over 5ms),We should consider moving your data files to reduce disk I/O contention, or move them to faster disks.

5)Enqueue high watermark:

This indicates hardware contention that occurs when there are multiple users inserting into LOB segments at once while the database is trying to reclaim unused space.
We should consider enabling secure files to improve LOB performance (SECURE_FILES=ALWAYS).

We cannot see these in my example report, because this was not a problem in my environment, so it did not make it into the Top 5.If it did, you would see an event called:enq: HW - contention

Other things to be aware of…

We will also check our database configuration.

6)MEMORY_TARGET:

Do not use this setting.We should have our DBA tune the memory manually instead.This will result in a better tuned database.We should start with 60% of physical memory allocated to SGA and 20% to PGA.


7)AUDIT_TRAIL:

Usually we do not use this setting much for tuning.But auditing on Database level
can be overhead to the Database.




Happy Tuning Oracle Databases ...


Best regards,

Rafi.

Useful Queries for Apps DBA and possible solutions

Hi,
Apps DBAs use lot of queries to find the useful information from the Database.I will try to list as much as I can in the below thread.
This thread I will update continously so that It can help one and all.

1)How to find the E-business suite login URL?
Ans:
SQL> conn apps
Enter password:
Connected.
SQL> select home_url from icx_parameters;

HOME_URL
--------------------------------------------------------------------------------
http://testnode1.comp.com:8000/OA_HTML/AppsLogin


2)How to find the release of Apps installed or version installed in our machine?
Ans:conn apps
Enter password:
Connected.

SQL> select release_name from fnd_product_groups;

RELEASE_NAME
--------------------------------------------------
12.1.1

3)What is Yellow Bar Warning in Apps?

Ans: Oracle Applications Release 11.5.1 (11i) requires that its code run in a trusted mode and uses J-Initiator to run Java applets on a desktop client. If an applet is “trusted,” however, Java will extend the privileges of the applet.The Yellow Warning Bar is a warning that your applet is not running in a trusted mode.To indicate that an applet is trusted, it must be digitally signed using a digital Certificate,so Oracle Applications requires that all Java archive files must be digitally signed.

4)How to check the custom top installled?

Ans:

SQL> Select BASEPATH,PRODUCT_CODE,APPLICATION_SHORT_NAME
From fnd_application
Where application_Short_name like '%CUST_TOP_name%';


5)How to check multi-org is enabled in Oracle applications?

Ans:

SQL> select multi_org_flag from fnd_product_groups;

M
-
Y
Note:For enabling multi-org check the MY ORACLE SUPPORT notes 396351.1 and 220601.1

6)How to compile invalid objects in Oracle Applications?

Ans: Check the below link for all possible ways to compile the invalid objects in Oracle Application.Usually 'adadmin' utility provides us the option to do this task.

http://onlineappsdba.blogspot.com/2008/05/how-to-compile-invalid-objects-in-apps.html


7)Can we install Apps Tier and Database Tier on different Operating system while installing Oracle EBS 11i/R12?
Ans: Yes it is possible.We can do this by following below MY ORACLE SUPPORT notes:

Oracle Apps 11i --> Using Oracle EBS with a Split Configuration Database Tier on 11gR2 [ID 946413.1]

Oracle Apps R12 --> Oracle EBS R12 with Database Tier Only Platform on Oracle Database 11.2.0 [ID 456347.1]


8)How to find the node details in Oracle Applications?
Ans: FND_NODES tables in 'apps' schema helps in finding node details after installation,clonning and migration of applications.
SQL> SELECT NODE_NAME||' '||STATUS ||' '||NODE_ID||' '||HOST
FROM FND_NODES;


9)How to see the products installed and their versions in Oracle Applications?
Ans:

SQL> SELECT APPLICATION_ID||''||ORACLE_ID||''||PRODUCT_VERSION||''||STATUS||''||PATCH_LEVEL
FROM FND_PRODUCT_INSTALLATIONS;


O/P looks like below:

172 172 12.0.0 I R12.CCT.B.1
191 191 12.0.0 I R12.BIS.B.1
602 602 12.0.0 I R12.XLA.B.1
805 805 12.0.0 I R12.BEN.B.1
8302 800 12.0.0 I R12.PQH.B.1
8303 800 12.0.0 I R12.PQP.B.1
809 809 12.0.0 I 11i.HXC.C
662 662 12.0.0 I R12.RLM.B.1
663 663 12.0.0 I R12.VEA.B.1
298 298 12.0.0 N R12.POM.B.1
185 185 12.0.0 I R12.XTR.B.1

10)How to see the concurrent Requests and jobs in Oracle Applications?
Ans: FND_CONCURRENT_REQUESTS can be used to see the concurrent requests and job details.These details are useful
in troubleshooting concurrent manager related issues.

SQL>SELECT REQUEST_ID||' '||REQUEST_DATE||' '||REQUESTED_BY||' '||PHASE_CODE||' '||STATUS_CODE
FROM FND_CONCURRENT_REQUESTS;


O/P will be as given below:
REQUEST_ID||''||REQUEST_DATE||''||REQUESTED_BY||''||PHASE_CODE||''||STATUS_CODE
--------------------------------------------------------------------------------------------------------
6088454 24-NOV-11 1318 P I
6088455 24-NOV-11 1318 P Q
6088403 24-NOV-11 0 C C
6088410 24-NOV-11 0 C C


Where:

PHASE_CODE column can have values:
C Completed
I Inactive
P Pending
R Running

STATUS_CODE Column can have values:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting


11)What is the significance of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables?
Ans: FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables are created and Dropped during the 'adadmin' and 'adpatch' sessions.
Both AD utilities (adpatch/adadmin) access the same tables to store the workers details, so both FND_INSTALL_PROCESSES and
AD_DEFERRED_JOBS tables need to be dropped from the failed adpatch session ,so that adadmin/adpatch session can run successfully next time.


Happy Apps DBA learning


Best regards,

Rafi.

Monday, November 21, 2011

DBA and APPS DBA advanced interview questions

1)what is FNDLOAD?(APPS DBA interview)
(OR)
How we can migrate concurrent programs of one application?(Apps DBA)

Ans:FNDLOAD is a an Oracle utility to migrate data from one instance to another.It can be used with several different
entities within Oracle 11i,R12 applications.

Usage: FNDLOAD logon 0 Y mode configfile datafile [ entity [ param ... ] ]

where:
logon is username/password[@connect]
mode is either UPLOAD or DOWNLOAD
configfile is the configuration file
datafile is the data file
entity is an entity name, or - to specify all values in an upload
param is a NAME=VALUE string used for parameter substitution


FNDLOAD can be used for lot of migration/transfering task across Oracle Applications.They are

1)FNDLOAD can be used to transfer Request Groups
2)FNDLOAD can be used for moving Concurrent Programs.
3)FNDLOAD can be used to download and upload forms personalizations.
4)FNDLOAD can be used to migrate Key FlexFields, Descriptive Flexfields, Responsibilities and
almost every other FND entity

Eg:

##To FNDLOAD Concurrent Programs
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct
XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt PROGRAM APPLICATION_SHORT_NAME="XXGMS"
CONCURRENT_PROGRAM_NAME="XX_CUSTOM_ORACLE_INTERFACE_PROG"


2)what is SCAN with respect to RAC?(DBA interview)

Ans: Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides
a single name for clients to access Oracle Databases running in a cluster. The benefit is that the client’s connect information does not need to change if you add or remove nodes in the cluster. Having a single name to access the cluster allows clients to use the EZConnect client and the simple JDBC thin URL to access any database running in the cluster, independently of which server(s) in the cluster the database is active. SCAN provides load balancing and failover for client connections to the database. The SCAN works as a cluster alias for databases in the cluster.

There are 2 options for defining the SCAN:
1. Define the SCAN in your corporate DNS (Domain Name Service)
2. Use the Grid Naming Service (GNS)

In case, a 2-node-cluster is used (for which 3 IPs are still recommended for simplification reasons), one server in the cluster will host two sets of SCAN resources under normal operations. If the node where a SCAN VIP is running fails,
the SCAN VIP and its associated listener will failover to another node in the cluster. If by means of such a failure the number of available servers in the cluster becomes less than three, one server would again host two sets of SCAN
resources. If a node becomes available in the cluster again, the formerly mentioned dispersion will take effect and
relocate one set accordingly.

Configuring SCAN:

#srvctl config scan_listener
#srvctl config scan

For Oracle Database 11g Release 2, SCAN is an essential part of the configuration and therefore the
REMOTE_LISTENER parameter is set to the SCAN per default, assuming that the database is created using standard Oracle tools (e.g. the formerly mentioned DBCA).This allows the instances to register with the SCAN Listeners as remote listeners to provide information on what services are being provided by the instance, the current load, and a recommendation on how many incoming connections should be directed to the instance.
In this context, the LOCAL_LISTENER parameter must be considered. The LOCAL_LISTENER parameter should
be set to the node-VIP.

Eg:

local_listener=>(DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=133.
22.67.111)(PORT=1521))))
remote_listener=>sales1-scan.example.com:1521

Note: ThE example Oracle 11g Release 2 client using a default TNSNAMES. ora will be as follows:

ORCLservice =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=sales1-scan.example.com)(PORT=1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =MYORCLservice)
))


3)How in R12 Jinitiator is placed?(APPS DBA interview)

Ans:
In App 11i, in context file we see s_sun_plugin_type set to jinit

Where as,In App R12 it is set to jdk

In Context.xml file we will find the line as given below in Apps R12

1.6.0_07
jdk


4)What are the background process in Dataguard physical standby setup and Logical standby setup?(DBA interview)
Ans: In additional to Mandatory Oracle background processes,the background processes playing key role in Dataguard physical standby and Logical Standby Setup are:

1)ARC0 Archiver Process on Local Server
2)ARCH Archiver
3)ASYNC Asynchronous
4)DMON Data Guard Monitor
5)FAL Fetch Archive Log =>For Pysical Standby setup
6)LCR Logical Change Record =>For Logical Standby setup
7)LGWR Logwriter
8)LNS Network Server Process
9)LSP Logical Standby Process (SQL Apply for Logical DG)=>For Logical Standby setup
10)MRP Managed Recovery Process (Redo Apply for Physical DG)=>For Pysical Standby setup
11)RFS Remote File Server
12)SYNC Synchronous

5)Which background process is responsible for recovery processes in physical standby database?(DBA interview)
Ans: MRP(Managed recovery process) is responsible for recovery process in physical standby Database in a Dataguard Configuration.

6)which background process is responsible for recovery in logical standby database?(DBA interview)
Ans:LSP(Logical standby process) is responsible for recovery in logical standby database in a Dataguard configuration.


7)What we should do if when applying patch the adpatch utility fails and Now you want to use adadmin/adpatch utility again? (Apps DBA)
Ans: Both AD utilities (adpatch/adadmin) access the same tables to store the workers details, so both FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables need to be dropped from the failed adpatch session ,so that adadmin/adpatch session can run successfully next time.


Happy Interviews

Best regards,

Rafi

Tuesday, November 1, 2011

MDS-01370: MetadataStore configuration for metadata-store-usage "soa-infra-store" is invalid

Hi,
In Windows 7 OS we might face an issue where SOA_INFRA is down even after starting SOA managed server,In this case we need to be aware of the below error.This error can be resolved as given below.

MDS-01370: MetadataStore configuration for metadata-store-usage "soa-infra-store" is invalid

Error Message:

Caused By: oracle.mds.exception.MDSExceptionList: MDS-01329: unable to load element "persistence-config"
MDS-01370: MetadataStore configuration for metadata-store-usage "soa-infra-store" is invalid.
ORA-04063: package body "DEV_MDS.MDS_INTERNAL_SHREDDED" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DEV_MDS.MDS_INTERNAL_SHREDDED"
ORA-06512: at line 1

Resolution:

Step 1: Uninstall the XE Database by launching Oracle XE graphical utility

Step 2:Install the XE Database

Step 3:Set the Oracle Environment variables 'RCU_JDBC_TRIM_BLOCKS=TRUE'
and Go to the repository creation utility Home(rcuHome) and BIN path and execute the rcu.bat batch file


C:\Users\RafiAlvi>set RCU_JDBC_TRIM_BLOCKS=TRUE

C:\Users\RafiAlvi>cd D:\Rafi_docs\Oracle_SOA_INSTALL\ofm_rcu_win_11.1.1.4.0_disk1_1of1\rcuHome\BIN

C:\Users\RafiAlvi>D:

D:\Rafi_docs\Oracle_SOA_INSTALL\ofm_rcu_win_11.1.1.4.0_disk1_1of1\rcuHome\BIN>rcu.bat

Step 4:Drop all the Schema owner and Tablespaces and
then run RCU again to Re-create the SOA Suite schemas and tablespaces.

The list of Component,Schema Owner and Tablespaces are given below:

Component Schema Owner Tablespaces
SOA Infrasture DEV_SOAINFRA DEV_SOAINFRA
BAM DEV_ORABAM DEV_ORABAM
UserMessaging service DEV_ORASDPM DEV_IAS_ORASDPM

Step 5:Find and compile the Invalid objects in the XE Database

SQL> COLUMN object_name FORMAT A30
SQL> SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

OWNER OBJECT_TYPE
------------------------------ -------------------
OBJECT_NAME STATUS
------------------------------ -------
DEV_ESS PACKAGE BODY
ESSPURGE INVALID

DEV_ESS PROCEDURE
CLOSE_FILE INVALID

DEV_ESS PROCEDURE
DEBUG_PURGE INVALID


OWNER OBJECT_TYPE
------------------------------ -------------------
OBJECT_NAME STATUS
------------------------------ -------
DEV_ESS PROCEDURE
LOG_ERROR INVALID

DEV_ESS PROCEDURE
LOG_MESSAGE INVALID

DEV_ESS PROCEDURE
WRITE_LINE INVALID


OWNER OBJECT_TYPE
------------------------------ -------------------
OBJECT_NAME STATUS
------------------------------ -------
DEV_SOAINFRA PROCEDURE
PURGE_B2B_INSTANCES_DOWN INVALID

DEV_SOAINFRA PROCEDURE
PURGE_RUNTIME_WITH_TIMEOUT INVALID


8 rows selected.

SQL> conn DEV_SOAINFRA
Enter password:
Connected.
SQL> ALTER PROCEDURE PURGE_B2B_INSTANCES_DOWN COMPILE;

Procedure altered.

SQL> ALTER PROCEDURE PURGE_RUNTIME_WITH_TIMEOUT COMPILE;

Procedure altered.


SQL> CONN DEV_MDS
Enter password:
Connected.
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'DEV_MDS');

PL/SQL procedure successfully completed.

Step 6:Now Bounce the Weblogic admin server and SOA Managed Server


weblogic_server_stop.bat:

cd D:\oracle_middleware\user_projects\domains\Soa_suitedev_domain\bin
D:
stopWebLogic.cmd

weblogic_server_start.bat:

cd D:\oracle_middleware\user_projects\domains\Soa_suitedev_domain\bin
D:
startWebLogic.cmd

soa_server_stop.bat:

cd D:\oracle_middleware\user_projects\domains\Soa_suitedev_domain\bin
D:
stopManagedWeblogic.cmd soa_server1

soa_serve_start.bat:


soa_server_start.bat:

cd D:\oracle_middleware\user_projects\domains\Soa_suitedev_domain\bin
D:
startManagedWeblogic.cmd soa_server1


Happy SOA learning...

Best regards,

Rafi.