* Rafi ORACLE DBA & APPS DBA Blog*

Changing Oracle Application user password for Oracle E-Business Suite R12 Application

2012-02-11T11:27:00.000-08:00

As a part of system administrator's task,Apps DBA do lot of tasks
One such task is changing or resetting Oracle Application user password.
We can follow the below steps to do this task:

Step 1:Login with 'sysadmin' user into Oracle E-Business Suite front
end page.

Step 2:In the Oracle Applications Home Page go to Navigator section,select the User Management Responsibility

Step 3:Click the User Management Responsibility,In the User Management,click Users

Step 4:In the Oracle User Management of page,we find lot of search options available for use,we can use % also to find the name of the Oracle Application user
If you now the employee id than you can directly enter here.

Let us say we want to change password for Employee id 'E0111' ,than I will enter 'EO111' in the place where User Name is present

Step 5:Seach the user
After entering ther User Name,click Go

We can see the details as specified below:
Last Name First Name Email User Name Status Create User Reset Password Update

Step 6:Reset the password
Click the Reset Password tab and Choose Enter Manually option of resetting the password.Now,we can enter the password and confirm the password and
Click Submit.

Step 7:Verify and inform the User
After resetting or changing the password,we can verify once ourself by logging from that user from the Oracle E-Business Suite front end page

Let us say we have reset the password of 'E0111' to welcome123,than we can login successfully and inform the user for the change.

Hope it helps....

Happy oracle Apps DBA learning..

Best regards,

Rafi.

Creating Soft link or Symbolic link for Concurrent Manager program files

2012-02-07T15:48:00.000-08:00

Soft Link or Symbolic Link is required to read a shell script (.prog file) through a concurrent program.

We can create soft links or symbolic links for Concurrent Manager program files as follows:

Step 1:Go to the custom top as below:

>cd $xxtest_TOP/bin
testapp01(APPDEV) /product/appl/apps/apps_st/appl/xxtest/12.0.0/bin

Step 2: Use 'fndcpesr' command to create soft link

Lets say we have to create two symbolic links for two .prog files xxtest_techtest_ets_loader.prog and xxtest_techtest_points_loader.prog respectively,than we can proceed as follows:

$pwd
testapp01(APPDEV) /product/appl/apps/apps_st/appl/xxtest/12.0.0/bin
$ln -s $FND_TOP/bin/fndcpesr xxtest_techtest_ets_loader

$ln -s $FND_TOP/bin/fndcpesr xxtest_techtest_points_loader

Note:prog_name should be without .prog extension while creating symbolic link for .prog files.

Step 3:Verify the two created symbolic links:

$ls -altr xxtest_techtest*
lrwxrwxrwx 1 applmgr oinstall 55 Feb 7 14:29 xxtest_techtest_ets_loader -> /product/appl/apps/apps_st/appl/fnd/12.0.0/bin/fndcpesr
lrwxrwxrwx 1 applmgr oinstall 55 Feb 7 14:29 xxtest_techtest_points_loader -> /product/appl/apps/apps_st/appl/fnd/12.0.0/bin/fndcpesr
As we see above,after creating symbolic link the new attribute 'l' is added that is nothing but symbolic link.

Note:
1)'fndcpesr' is a standard utility available in $FND_TOP/bin directory.
2) Its mainly used by the application to parse arguments for shell scripts.
3)It is located in $FND_TOP/bin path along with other FND Utilities.
4)'fndcpesr' is an executable and read only file.

Hope it helps.

Happy Apps DBA learning...

Reference summary of asmcmd commands

2012-02-06T07:21:00.000-08:00

asmcmd is a various nice feature of ASM.It helps us understand and do things from command line itself.I find this utility very useful.Thanks to Oracle again for making things easy.

Reference summary of asmcmd commands:

cd Changes the current directory to the specified directory.

du Displays the total disk space occupied by ASM files in the
specified ASM directory and all its subdirectories, recursively.

exit Exits ASMCMD.

find Lists the paths of all occurrences of the specified name (with
wildcards) under the specified directory.

help Displays the syntax and description of ASMCMD commands.

ls Lists the contents of an ASM directory, the attributes of the
specified file, or the names and attributes of all disk groups.

lsct Lists information about current ASM clients.

lsdg Lists all disk groups and their attributes.

mkalias Creates an alias for a system-generated filename.

mkdir Creates ASM directory.

pwd Displays the path of the current ASM directory.

rm Deletes the specified ASM files or directories.

rmalias Deletes the specified alias, retaining the file that the alias points to.

asm.sh, a Linux shell script, that demonstrates some of the asmcmd functionality.

lsct :list all connected Oracle instances

ASMCMD> lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
+ASM CONNECTED 11.2.0.1.0 11.2.0.1.0 +ASM1 GRID_DATA_01
+ASM CONNECTED 11.2.0.1.0 11.2.0.1.0 +ASM1 TESTDB_DATA_01
TESTDB CONNECTED 11.2.0.1.0 11.2.0.0.0 TESTDB1 TESTDB_FRA_01
TESTDB CONNECTED 11.2.0.1.0 11.2.0.0.0 TESTDB1 TESTDB_DATA_01
ICMQB CONNECTED 11.2.0.1.0 11.2.0.0.0 ICMQB1 TESTDB_FRA_01
ICMQB CONNECTED 11.2.0.1.0 11.2.0.0.0 ICMQB1 TESTDB_REDO_01
ICMQB CONNECTED 11.2.0.1.0 11.2.0.0.0 ICMQB1 TESTDB_REDO_02

lsdg:List Diskgroup status and type.

ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 5934 5002 173 2414 0 N GRID_DATA_01/
MOUNTED EXTERN N 512 4096 1048576 2161538 112220 0 112220 0 N TESTDB_DATA_01/
MOUNTED EXTERN N 512 4096 1048576 654684 560350 0 560350 0 N TESTDB_FRA_01/
MOUNTED EXTERN N 512 4096 1048576 32652 11922 0 11922 0 N TESTDB_REDO_01/
MOUNTED EXTERN N 512 4096 1048576 32652 11954 0 11954 0 N TESTDB_REDO_02/

ASMCMD> iostat
Group_Name Dsk_Name Reads Writes
GRID_DATA_01 GRID_DATA_01_0000 3121340416 0
GRID_DATA_01 GRID_DATA_01_0001 3616768 0
GRID_DATA_01 GRID_DATA_01_0002 4354048 4096
GRID_DATA_01 GRID_DATA_01_0003 2061918208 4096
GRID_DATA_01 GRID_DATA_01_0004 3616768 0
GRID_DATA_01 GRID_DATA_01_0005 3731456 4096
TESTDB_DATA_01 TESTDB_DATA_01_0000 1868196700160 205746507776
TESTDB_DATA_01 TESTDB_DATA_01_0001 1860077395968 203464622080
TESTDB_DATA_01 TESTDB_DATA_01_0002 1860528062464 204175556608

Reference :MY ORACLE SUPPORT and Oracle Documentations

Hope is helps...

Happy ASM learning...

Changing the profile value in Oracle EBS applications

2012-02-06T07:11:00.000-08:00

How do we change the profile value in Oracle EBS applications?

Ans: Below are the steps for changing profile value in Oracle EBS applications:

Step 1:
Login to EBS home page with 'SYSADMIN' or privileged application user.

Step 2:

In the Navigator,Go to System Administrator Tab and On the right side Go to Profile=>System

Step 3:

Click System you will see Oracle Application Manager Form Page is opened.

Step 4:

Double Click Profile Tab,go to System,Click Open Tab at the bottom.

Step 5:

We will see form page Find System Profile Values

Enter the Profile Name at the Bottom where Profile Tab is there and Click Find.

Where Profile:Name_of_profile whose value we want to set.

Step 6:
Now we will see Form Page where
Profile Option Name:Profile_name
Site:Value you want to set

Site=0,1...

Step 7:
Save the Changes.

File=>Save

And Close the Form.

File=>Close Form or Exit Oracle Applications.

Hope it helps Beginners Apps DBA

HAPPY NEW YEAR 2012

2012-01-01T02:11:00.000-08:00

Hi All,
Thanks for visiting,reading and giving suggestion regarding my blog,I wish you
all very HAPPY AND PROSPEROUS NEW YEAR 2012.

Last year(2011) was interesting for me,as I learn new technologies Oracle SOA(Service Oriented Architecture) and ODI(Oracle Data Integrator).Apart from these
I stepped forward to learn Oracle 11g RAC,Dataguard and Oracle EBS 12.1.1 and later versions.
Going forward this year(2012) I'm interested in extending my skillsets and implement
these skillsets more in my work to gain perfection.It won't be easy,but I will be
trying my best to maintain these skillsets by reading and practise.

MY GOALS FOR THE YEAR 2012::

1)Practise and Master the skillsets Oracle 11g RAC,Dataguard.

2)Practise and Master the skillsets Oracle EBS R12.1.1 & later versions.

3)Practise and Master the skillsets Oracle SOA 11g.

4)Practise and Master the skillsets Oracle Data Integrator 11g.

5)In order to get more perfection I need to take Oracle 11g RAC,Dataguard training
from Oracle which will also act as base for my prepartion of OCM Exam.

6)Practise and Complete PMP Certification.

7)Bring Excellence to my work by adopting best work practises in terms of technology
and work practises.

Oracle and DBA Market Survey as per me:

DBA Market Demanded the following major skillsets:
1)RAC: RAC skillset I found ruled the DBA market in the year 2011(last year).It makes sense as it is used by lot of financial and highly critical Databases.

2)Dataguard: Dataguard skillset was the second major skillset which I found ruled the DBA market.As many companies grow their data,they worry for protecting it from Disaster.
ASM,RMAN,Performance tuning were not behind and were also in demand as always.

Apps DBA Market Survery:

Apps DBA market Demanded the follwing skillset:

1)Clonning and Patching:
The most repeated task of the Apps DBA again ruled the last year(2011).It can be called as refreshing the Database and Application and Clonning
in other sense.Patching is again the repeated task for the Apps DBA

2)SHARED APPL_TOP ,LOAD BALANCING AND PCP:
As experience grows the expectation of companies for you will also.I found few companies specifically asking the combination of RAC and Apps for
senior Apps consultant/Consultant positions on a large scale.

DMZ configuration,Apps DBA Administraion(AD Utilities) and Workflow configuration skillsets were also not behind.

3)Oracle SOA market survey:
Oracle SOA 11g market was hot last year.I found many companies
asking for Oracle SOA Developer/Admins.Along with these,Oracle Weblogic server skillsets were in demand.

4)ODI market survey:
Oracle Data Integrator 11g(ODI 11g) was the hot ETL tool last year as
many companies were moving Data from one source to many targets.

5)Oracle Fusion Apps DBA(Fusion DBA) market survey:
I found a new job role emerged in 2011,It is the combination of Oracle Apps DBA and Fusion Middleware Administration(SOA Administration + Weblogic Administration).It is a kind of profile where company is asking too much from one candidate,considering future demand of Oracle Weblogic(BEA product).Some companies
were specific on hiring Oracle Fusion Middleware Administrators.

Note:The above survey is just based on my personal experience and should not be considered for any publicity or stratergy.

I will be writing my next survey in next year beginning.All the best.Have a bright and skillfull year 2012.

Enjoy and Happy Oracle learning.

Manually Installing Oracle Data Mining in Oracle 11g

2011-12-26T00:00:00.001-08:00

Hi,
If you compare Oracle 11g and previous versions there are lot of changes in terms of installing Data Mining component,
For installing check the MY ORACLE SUPPORT note :
How To Manually Install Data Mining In Oracle 11g? [ID 818314.1].

Information about data mining:
The brief information about Oracle Data mining can be obtained from the below link:

http://docs.oracle.com/html/B14339_01/1intro.htm

In short,If you do the Oracle installation with Enterprise Edition installation type ,it selects Oracle Data Mining option by default.In Oracle Database 11g,the Data Mining metadata is created with 'SYS' metadata when you select the Create Database option.

For verifying the installation of Oracle Data Mining:
Check if the parameter DATA MINING is set to TRUE,if it is than Oracle Data Mining component is already installed in the Database.The V$OPTION is very useful for checking the various functionality exist in your Database or not as seen from the below results:

SQL>SET LINESIZE 1000
SQL>SELECT *FROM V$OPTION;

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Materialized view warehouse refresh TRUE
Database resource manager TRUE
Spatial TRUE
Automatic Storage Management FALSE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java TRUE
OLAP Window Functions TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Block Media Recovery TRUE
Fine-grained Auditing TRUE
Application Role TRUE
Enterprise User Security TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
OLAP TRUE
Basic Compression TRUE
Join index TRUE
Trial Recovery TRUE
Data Mining TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Online Redefinition TRUE
Streams Capture TRUE
File Mapping TRUE
Block Change Tracking TRUE
Flashback Table TRUE
Flashback Database TRUE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
Oracle Database Vault FALSE
Result Cache TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
SQL Plan Management TRUE
SecureFiles Encryption TRUE
Real Application Testing TRUE
Flashback Data Archive TRUE
DICOM TRUE
Active Data Guard TRUE
Server Flash Cache TRUE
Advanced Compression TRUE
XStream TRUE
Deferred Segment Creation TRUE

PRIVILEGE REQUIRED FOR DATA MINING SCHEMA:

Let us say,If you want to use Data Mining feature to a specific user,than we have to grant CREATE MINING MODEL privilege to that user.

SQL> conn dmuser
Enter password:
Connected.
SQL> select *from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
CREATE MINING MODEL

11 rows selected.

Happy Oracle DBA tasks...

Best regards,

Rafi.

Step by steps Oracle 10g Application Server installation steps

2011-12-21T11:28:00.001-08:00

Hi,
Below are the steps for Deinstallation,Installation of Oracle 10g Application Server:

Application server Deinstallation process:

Go to
cd D:\Apps_soft\application_server\Disk1

Launch setup.exe

Click Deinstall products

As seen in above screenshot,click all the products we need to deinstall
And then Click Remove

=>Application server 10g Database
=>Application server Infrastructure
=>Application server Middle Tier

Click Yes and than click OK each time it prompts ,which will all the products of the Oracle Application Server and hence Application server will be deinstall successfully.

Installing Oracle 10g Application Server:

Oracle Application Server (10g Release 2) is installed as follows:

Operating System:Windows XP(32-bit)

Version: 10.1.2.0.2
Size on disk required:20 GB

Step 1:

Browse to :D:\Apps_soft\application_server\Disk1

Step 2:
Specify the name of the Application server and Home location where we will be keeping Infra and Middle Tier home components

Step 3:

Gives 3 options:

1)Installation of Middle Tier
2)Installation of Infra Tier
3)Installation of Developer kits 10g

Select option 2 for Installation of Infrastructure Tier.

<span style="font-weight:bold;">Step 4:
Select option 1:It will install Identity Management and Metadata Repository

Step 5:
We will get message as this software is not certified ignored it and proceed by clicking ok and Check the Administrator privileges which will confirm you have administrative rights to install this software.

Click next

Click next

Click Install

Exit and Come out

II)Middle tier Installation:

Select option 1 For installation of Application Server Middle Tier

Click Next,

Select option 3 For installation of BI and Forms.

Click OK and proceed.

Click Next,

Go to D:\Apps_server_10g_home\install.

And open portlist.ini file to see the port number for Oracle Internet Directory(OID).

Enter your machine name and port for OID. Usually 389 for windows as in our case.

Specify: Instance name:Middle Tier instance and ias_admin password
Click Install

Exit and Come out:

Verification:
U:\>cd D:\Apps_server_10g_home\opmn\bin

U:\>d:

D:\Apps_server_10g_home\opmn\bin>opmnctl status

Processes in Instance: APPS_INFRA_INSTANCE.node1.abc.comp.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | 3740 | Alive
OC4J | OC4J_SECURITY | 4324 | Alive
HTTP_Server | HTTP_Server | 4184 | Alive
OID | OID | 1992 | Alive

D:\Apps_server_10g_home\opmn\bin>cd D:\Apps_server_10g_home\MidTier\opmn\b

D:\Apps_server_10g_home\MidTier\opmn\bin>opmmctl status
'opmmctl' is not recognized as an internal or external command,
operable program or batch file.

D:\Apps_server_10g_home\MidTier\opmn\bin>opmnctl startall
opmnctl: starting opmn and all managed processes...

D:\Apps_server_10g_home\MidTier\opmn\bin>opmnctl status

Processes in Instance: Middle_Tier_instance_node1.abc.comp.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | 7272 | Alive
OC4J | home | 6572 | Alive
OC4J | OC4J_BI_Forms | 7920 | Stop
WebCache | WebCache | 6384 | Alive
WebCache | WebCacheAdmin | 1996 | Alive
HTTP_Server | HTTP_Server | 6360 | Alive

Happy Oracle Apps learning...

Best regards,

Rafi.

Completed Oracle SOA Suite 11g Certification

2011-12-14T01:21:00.000-08:00

Hi,
I added one more credential to my profile portfolio,this time it is my this year favourite concept Oracle SOA 11g.I've completed Oracle SOA suite 11g foundation practitioner certificate last Saturday.Now,I'm Oracle SOA certified expert.Working on the competency servers and giving tech talk on Oracle SOA in my organization were key factors which helped me achieving this credentials.
In addition to this I installed and configured Oracle SOA suite 11g(version 11.1.0.5.0) in my personal laptop also which is having Windows 7 Operating system helped me a lot.

I passed my Oracle SOA Foundation Practitioner Certification by following below tips:

Practise needed:
Practise On Developing and Deploying composite application by starting with basic Hello BPEL task can be very much useful,We need to be aware of each and every service components like their functioning and usage.Work with Adapters and Integration practises will also comes handy.For getting Started with Oracle SOA below link helped lot:

http://docs.oracle.com/cd/E21764_01/integration.1111/e10223/toc.htm

Once this is done you can develop and deploy various composite application for your practise which will be advantageous.

Books you can follow:

Oracle SOA Suite 11g Development guide:http://docs.oracle.com/cd/E21764_01/integration.1111/e10224/toc.htm
Oracle SOA Suite 11g Handbook written by Lucas Jellema.

Links you can follow:

http://docs.oracle.com/cd/E21764_01/soa.htm
Blog from Ahmed :http://blog.ipnweb.com/
Blog from Abhishek:http://abhishek-soablog.blogspot.com/
Oracle SOA Suite Forum:https://forums.oracle.com/forums/forum.jspa?forumID=320

Details about Oracle SOA Foundation Practitioner certification:

Check the below link for Oracle SOA Suite 11g foundation practitioner Certificate syllabus,training and Exam Details:

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1080544&lang=US&p_exam_id=1Z0_451

All the best for Oracle SOA 11g exam...

Happy Oracle SOA learning...

Best regards,

Rafi.

My Favourite Book for Oracle SOA suite 11g

2011-12-12T09:53:00.000-08:00

Hi,
This year I devoted myself in learning and practicing Oracle SOA suite 11g.I started learning the concepts of Oracle SOA 11g and various Components involved.
For initial learning of mine,I focused on the below link:

http://docs.oracle.com/cd/E21764_01/integration.1111/e10223/toc.htm
http://rafioracledba.blogspot.com/2011/11/oracle-soa-essential-for-soa.html

After clearing my concepts I went with installing and Configuring Oracle Suite 11g on Windows and Linux Operating systems which led the foundation of my SOA learning.
For installation and Configuration refer my below links:

http://rafioracledba.blogspot.com/2011/08/step-by-step-oracle-soa-suite-11g.html

But,One book that inspired me a lot is Oracle SOA Suite 11g Handbook written by Lucas Jellema.

Brief about Author: Lucas Jellema is an Oracle ACE Director for Oracle Fusion Middleware.He is the SOA specialist based in Nieuwegein,The Netherlands.He is an author at the AMIS Technology Blog for Oracle Technology Network,and for international magazines.

Brief about Book:
This books covers various the following topics which will help you work in Oracle SOA suite 11g:

1)Introduction to SOA with an Example of how it is adopted.
2)Developing Composite Applications which covers the Components of Oracle SOA Suite 11g and how this components work together by using SCA
(Service Component Architecture which is new from Oracle SOA 11g).
3)How we can Administer,Secure and Govern Oracle SOA components.
4)Use of CEP(Complex Event Processing) and BAM (Business Activity Monitoring)
5)How we can migrate from Oracle SOA Suite 10g to 11g.

What else,A SOA learner and expert can expect,really love to read this book several times to understand more better and Work in Oracle SOA suite 11g.All the best SOA learners.

Happy Oracle SOA learning...

Best regards,

Rafi.

Happy Oracle SOA

Oracle SOA essential for SOA Administrators and Developers

2011-11-29T10:35:00.000-08:00

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.

How to tune oracle Database?

2011-11-25T02:57:00.000-08:00

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

2011-11-25T01:13:00.000-08:00

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.

DBA and APPS DBA advanced interview questions

2011-11-21T03:25:00.000-08:00

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

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

2011-11-01T03:54:00.000-07:00

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.

Resolving ORA-01652

2011-10-25T05:15:00.000-07:00

Hi,

Steps we can follow to resolve the error ORA-01652 is as follows:

Solution 1:

We can check for held TEMP segments with this query:
SQL> set linesize 1000
SQL>select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;

TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 201 169216 70912 15 5 TEST1 oracle ACTIVE
TEMP 201 178688 128 9 1450 TEST2 INACTIVE
TEMP 201 240128 128 150 299 APEX_PUBLIC_USER apex_admin INACTIVE

This command may remove a TEMP segment, try:

alter tablespace xxxxx coalesce;

alter tablespace TEMP coalesce;

(OR)

Solution 2:

We can also use a drop segments event to remove temporary space from a tablespace:

ALTER SESSION SET EVENTS 'immediate trace name drop_segments level &x';

where:
x is the value for file# from Tablespace.

(OR)

Solution 3:Add the temp file for Temporary tablespace

SQL> SELECT FILE_NAME||' '||TABLESPACE_NAME||' '||BYTES/1024/1024
2 FROM DBA_TEMP_FILES;

FILE_NAME||''||TABLESPACE_NAME||''||BYTES/1024/1024
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u04/TESTDB/TESTDB_temp_01.dbf TEMP 1900

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/TESTDB/TESTDB_temp_02.dbf' SIZE 200M;

Tablespace altered.

SQL> SELECT TABLESPACE_SIZE/1024/1024||' '||ALLOCATED_SPACE/1024/1024||' '||FREE_SPACE/1024/1024
FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_SIZE/1024/1024||''||ALLOCATED_SPACE/1024/1024||''||FREE_SPACE/1024/1024
--------------------------------------------------------------------------------------------------------------------------
2100 1898 1519

SQL> select tablespace_name, total_blocks, used_blocks, free_blocks
2 FROM v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP 242688 74112 168576

SQL> set linesize 1000

SQL> SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024
FROM V$temp_space_header
GROUP BY tablespace_name;

TABLESPACE_NAME SUM(BYTES_USED)/1024/1024 SUM(BYTES_FREE)/1024/1024
------------------------------ ------------------------- -------------------------
TEMP 1898 202

Then,resize or add the datafile related to a Tablespace if require or add a new datafile to current tablespace

Addind Datafile to existing Tablespace:
ALTER TABLESPACE RMRTM ADD DATAFILE '/u05/TESTDB/RMRTM_DATA_01.DBF' SIZE 200M;

SQL> SELECT *FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE '%RM%';

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/u04/TESTDB/RMRTM_DATA_01.DBF 39 RMRTM 3221225472 393216 AVAILABLE 39 YES 3221225472 393216 12800 3220176896 393088 ONLINE

SQL> SET LINESIZE 1000
SQL> SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACEF.FREE_SPACE),'999,999')"USEDMB",
TO_CHAR (F.FREE_SPACE, '999,999') "FREEMB",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTALMB",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;

TABLESPACE_NAME USEDMB FREEMB TOTALMB FREE
------------------------------ -------- -------- -------- ------

RMRTM 2,748 324 3,072 11 %

SQL>ALTER TABLESPACE RMRTM ADD DATAFILE '/u05/TESTDB/RMRTM_DATA_01.DBF' SIZE 200M;

Tablespace altered

(OR)

we can resize the existing Datafile if the mount point have enough space

Note:
Resizing Datafile can be done as givne below:

ALTER DATABASE DATAFILE 'D:\ORACLEXE\ORADATA\XE\USERS.DBF' RESIZE 200M;

(OR)

Solution 4:Create a new Temporary Tablespace and make this temporary tablespace default for our Database.

Step 1:Create the new temporary Tablespace

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select status from v$instance;

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

SQL> CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE '/u05/TESTDB/TEMP02.dbf' SIZE 256M
REUSE AUTOEXTEND ON NEXT 256M MAXSIZE 8192M EXTENT MANAGEMENT LOCAL;

Tablespace created.

Step 2:Make this temporary tablespace default at Database level

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

Database altered.

Where:
'TEMP02' is the DEFAULT TEMPORARY TABLESPACE name in the above case.

Step 3:Bounce our Database so that actual temporary space is release from 'temp' Tablespace and Drop tablespace 'temp' including content

we have to Make sure We delete on temp data files and the actual space is recovered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 855641944 bytes
Database Buffers 205520896 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.
SQL> Drop tablespace temp including contents;

Tablespace dropped.

SQL> select *from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/u05/TESTDB/TEMP02.dbf
3 TEMP02 268435456 32768 ONLINE
1 YES 8589934592 1048576 32768 267386880 32640

Now,we can see the new Temporary tablespace is 'TEMP02' with enough free space.

Note:We can adopt either of one solution

Hope it helps...

Happy troubleshooting...

Best regards,

Rafi.

Installation and Configuring of Oracle Data Integrator on Linux

2011-10-25T02:11:00.000-07:00

Hi,
There are lot of ETL tools available in the market like Informatica,Datastage...Here is one from Oracle,This tool is called ODI(Oracle Data Integrator) which is the ELT(Estraction Loading and Transformation) tool as per Oracle concepts.

Below are few steps for Installation and Configuration of ODI:

Below I'm describing the steps which I've followed to installed the Oracle Data Integrator(ODI)11g version 11.0.1.5.0 on linux platform,The Detailed step along with the installation Roadmap is given below:

Installation Roadmap:
The following tasks provide a brief description of the Oracle Data Integrator Installation Procedure, including a description of the installable components, pre and post-installation tasks, and process flow. This tasks are can be said as the Installation Roadmap.

Task1: Complete the installation planning requirements
Prior to installation you must prepare your system environment for installation. Review the general installation requirements for Oracle Fusion Middleware, as well as any specific configuration requirements for Oracle Data Integrator.

Task2:Pre-requistics Software should be installed.

1)Install and configure an Oracle Database
Refer previous posts of mine:

2)Install Oracle WebLogic Server:

Weblogic pre-installation steps:
Check the below Certification matrix wls.xls to check the supported Weblogic server for
Various Operating system.For our operating system RHEL 5.3 the supported
Weblogic server software is 10.3.

SQL> select name from v$database;

NAME
---------
FMWDB

SQL> select status from v$instance;

STATUS
------------
OPEN
SQL> show parameter processes
SQL> alter system set processes=1000 scope=spfile;
SQL> shutdown immediate
SQL> startup
SQL> show parameter processes

export FMW_HOME=$ORACLE_BASE/product/FMW

[root@testnode1FMW /]# mkdir -p Middleware/SOASuite11gR1
[root@testnode1FMW /]# chown oracle:oinstall /Middleware/SOASuite11gR1/
[root@testnode1FMW /]# chmod -R 755 /Middleware/SOASuite11gR1/

We have to create Middleware home directory for installation FUSION MIDDLEWARE COMPONENTS in this particular by granting the ownership to the ‘oracle’ user with all the required privileges.

Once your database is ready, we can install the next step, the WebLogic server.
When you are ready to install, complete the following.

I granted all the ownership to the oracle user and permission where the executable of weblogic software is present.I launched the installation on vm server

#xserver +
#xclock
=>This shoud display time,Use GUI mode for installing weblogic so that you can see the dispaly cleary..

,We have entered the Fusion Middlware Home path which has all the ownership and permission so that ‘oracle’ user can install weblogic server here.

Enter the common path like: /Middleware/

In step5 Make uninformed to ‘yes’ and proceed..

Choose Typical installation and proceed...

click next...
proceed...

3) Install Oracle Repository Creation Utility (RCU)

Note: The Oracle Repository Creation Utility (RCU) allows us to create and load a Master Repository and a Work Repository in a single database schema. RCU supports only the Oracle, Microsoft SQL Server and DB2 Databases. RCU only supports a single schema containing both the Master Repository and one Work Repository. Other technologies and configurations are supported by manually creating the repositories using the Oracle Data Integrator Studio after the installation phase.

Useful pre-requistics steps:

[oracle@testnode1FMW admin]$ hostname
testnode1FMW.apac.bosch.com
[oracle@testnode1FMW admin]$ tnsping fmwdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:23

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/app//product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fmwdb.apac.bosch.com)))
TNS-12541: TNS:no listener
[oracle@testnode1FMW admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:30

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /u01/app//product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app//product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/diag/tnslsnr/testnode1FMW/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testnode1FMW.apac.bosch.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 29-JUL-2011 18:26:32
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app//product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/diag/tnslsnr/testnode1FMW/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testnode1FMW.apac.bosch.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@testnode1FMW admin]$ tnsping fmwdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:40

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/app//product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fmwdb.apac.bosch.com)))
OK (30 msec)

2) Please do below step in Database side,If you are installing all the Oracle Fusion Middleware products,For SOA installation alone setting open_cursors=300,but as we are installating all the Oracle Fusion middleware products we need to set open_cursors=1000.

Conn “/as sysdba” in Database.

SQL> alter system set open_cursors=1000;

System altered.

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 1000

Now it is time to set up the database schema.
Note: If we have installed SOA 11g before and you are not upgrading, you must drop your existing database schema before reinstalling or create a second schema for this installation. We cannot reuse an existing schema.

Configure Schema using RCU
Now create the new schema.

Give the ownership and permission to ‘oracle’ user to run the repository creation utility.

[root@testnode1FMW /]# chown oracle:oinstall /Reposit_create_soft/
[root@testnode1FMW /]# chmod -R 755 /Reposit_create_soft/

[root@testnode1FMW Reposit_create_soft]# su - oracle
[oracle@testnode1FMW ~]$ pwd
/home/oracle
[oracle@testnode1FMW ~]$ cd /Reposit_create_soft/
[oracle@testnode1FMW Reposit_create_soft]$ ls -altr
total 389240
-rwxr-xr-x 1 root root 398173821 Jul 26 18:53 ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 2 oracle oinstall 4096 Jul 27 10:36 .
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
[oracle@testnode1FMW Reposit_create_soft]$ unzip ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip

[oracle@testnode1FMW /]$ cd Reposit_create_soft/
[oracle@testnode1FMW Reposit_create_soft]$ ls -altr
total 389244
drwxr-xr-x 32 oracle oinstall 4096 Apr 26 05:04 rcuHome
-rwxr-xr-x 1 root root 398173821 Jul 26 18:53 ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
drwxr-xr-x 3 oracle oinstall 4096 Jul 28 11:41 .
[oracle@testnode1FMW Reposit_create_soft]$ cd rcuHome/
[oracle@testnode1FMW rcuHome]$ ls
assistants common inventory jdk lib nls oraInst.loc plsql rcu relnotes sqlplus xdk
BC4J css javavm jlib modules oracleRoot.sh owm portal rdbms root.sh srvm
bin instantclient jdbc ldap network oracore perl precomp readme.htm sqlj util
[oracle@testnode1FMW rcuHome]$ cd bin

Go to path ‘/Reposit_create_soft_/rcuHome/bin’ as seen in the screenshot.

$ export Display

On Linux(UNIX):
./rcu

Step 4: Install Oracle Data Integrator 11g
Run Oracle Universal Installer (OUI) to install Oracle Data Integrator 11g. The Oracle Universal Installer automates many of the ODI installation and configuration tasks.

:Create ODI_HOME directory and grant ‘oracle’ user ownership
Create the ‘ODI_HOME’ directory.and Grant the ownership and permission to ‘oracle’ user for accessing ‘ODI_HOME’ directory.
[root@testnode1FMW Middleware]# cd /Middleware/
[root@testnode1FMW Middleware]# ls
JDev11g SOASuite11gR1
[root@testnode1FMW Middleware]# mkdir ODI_HOME
[root@testnode1FMW Middleware]# chown oracle:oinstall /Middleware/ODI_HOME/
[root@testnode1FMW Middleware]# chmod -R 755 /Middleware/ODI_HOME/
[root@testnode1FMW Middleware]# ls
JDev11g ODI_HOME SOASuite11gR1
[root@testnode1FMW Middleware]# ls -altr
total 24
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
drwxr-x--- 13 oracle oinstall 4096 Aug 1 14:45 SOASuite11gR1
drwxr-xr-x 2 oracle oinstall 4096 Aug 2 18:24 JDev11g
drwxr-xr-x 2 oracle oinstall 4096 Aug 3 12:56 ODI_HOME
drwxr-xr-x 5 root root 4096 Aug 3 12:56 .
[oracle@testnode1FMW common]$ cd
[oracle@testnode1FMW ~]$ cd /ODI_11gsoft/
[oracle@testnode1FMW ODI_11gsoft]$ ls -altr
total 1887820
drwxrwxr-x 3 root root 4096 Apr 23 09:27 Disk2
drwxr-xr-x 5 root root 4096 Apr 30 03:14 Disk1
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
-rw-r--r-- 1 root root 1931212170 Aug 4 11:44 ofm_odi_generic_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 4 oracle oinstall 4096 Aug 4 14:29 .
[oracle@testnode1FMW ODI_11gsoft]$ su
Password:
[root@testnode1FMW ODI_11gsoft]# unzip ofm_odi_linux_11.1.1.5.0_32_disk1_1of1.zip

Verify the JDK home before launching the installation:

Our JDK home = /Middleware/SOASuite11gR1/jdk160_24

Granting ‘oracle’ user ownership and permission:

[root@testnode1FMW ~]# cd /ODI_11gsoft/
[root@testnode1FMW ODI_11gsoft]# ls -altr
total 1887820
drwxrwxr-x 3 root root 4096 Apr 23 09:27 Disk2
drwxr-xr-x 5 root root 4096 Apr 30 03:14 Disk1
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
-rw-r--r-- 1 root root 1931212170 Aug 4 11:44 ofm_odi_generic_11.1.1.5.0 _disk1_1of1.zip
drwxr-xr-x 4 oracle oinstall 4096 Aug 4 14:29 .
[root@testnode1FMW ODI_11gsoft]# chown -R oracle:oinstall /ODI_11gsoft/Disk1
[root@testnode1FMW ODI_11gsoft]# chown -R oracle:oinstall /ODI_11gsoft/Disk2
[root@testnode1FMW ODI_11gsoft]# ls -altr
total 1887820
drwxrwxr-x 3 oracle oinstall 4096 Apr 23 09:27 Disk2
drwxr-xr-x 5 oracle oinstall 4096 Apr 30 03:14 Disk1

Login as ‘oracle’ user for launching the ODI 11g installation.

- Download the Oracle Data Integrator 11g and start the installer.
- Note: JDK home = /Middleware/SOASuite11gR1/jdk160_24

Click Next

- ODI step 2 : Select the ODI components to install. ODI Studio is the main component.

Select the components which you want…Select All for complete installations..
Click Next

Click Next

Note: An Application server should already be installed,We had installed Weblogic server in the same path.

Click Next

Select the Configure Repositories option, to configure the ODI Studio and the Stand Alone Agent with an existing Master and Work repositories pair.

Provide the Database Connection information
Enter User:DEV_ODI_REPO =>User used for odi metadata repository in Database ‘FMWDB’

Pass:welcome123

Click Next

Create the SUPERVISOR password
ODI Pass:welcome123

Click Next

Note: If we are installing agent we can provide the installation information as Agent=ODI_AGENT and Port:20910(Default port)=>This is the port which Agent will be listening…

Select the WorkRepository created with RCU

I created one response file in /home/oracle directory to review installation steps later

Click Install to install ODI 11g

Our ODI 11g installation and Configuration is successful

Click Next

I created the configuration file to refer later.

Click OK
Click Finish….

Hope it helps...

Happy ODI learning

HOW TO RESOLVE ROW-CHAINING

2011-10-18T10:40:00.000-07:00

We know the row-chaining happen,when a row that is too large cannot fit on a single block.

Eg: If we have a 2k blocksize but our row size is 4k, we will use 3 blocks to store that row in pieces.

Row-chaining can occur because of below reasons:

1)A table have a long/long raw Data types in some cases
2) A table's rowsize exceeds the blocksize and
3)Any table with more then 255 columns

We can resolve ROW-CHAINING by following below 2 steps:

Step:1.FIND OUT IF ROW-CHAINING IS OCCURING.
$sqlplus scott/tiger
SQL>exec dbms_stats.gather_schema_stat(‘scott’)
OR
SQL>exec dbms_stat.gather_table_stat('scott','emp')
SQL>select chaint_cnt from user_tables where TABLE_NAME='EMP';

NOTE: Any non-zero value means there is row-chaining.

Step 2:RESOLVE THE CHAINING

 RUN THE FOLLOWING COMMAND TO CREATE chained_rows table.

$sqlplus "/as sysdba"

SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql

SQL>ANALYZE TABLE EMP LIST CHAINED ROWS INTO CHAINED_ROWS;

SQL>create table temp_emp as
Select * from emp
Where rowid in (select HEAD_ROWID from chained_rows
where TABLE_NAME= ‘EMP’;

SQL>DELETE FROM EMP WHERE ROWID IN(
select HEAD_ROWID from chained_rows
where TABLE_NAME= ‘EMP’);

SQL>INSERT INTO EMP SELECT * FROM TEMP_EMP;

SQL>DROP TABLE TEM-_EMP;
SQL>DROP TABLE CHAINED_ROWS;

Note:Remember,ROW CHAINING occurs because of UPDATE operations and not because of INSERT operations.

Hope it helps...

Happy Oracle DBA learning

Best regards,

Rafi.

.

Refreshing schemas in oracle Databases

2011-10-14T03:41:00.000-07:00

Hi,
Schema refresh task might be regular for DBA's working on Database migration project.Schema refresh is done to make our production Database data in sync with developmnent,test and performance environment.

Below I'm describing one such task.Lot of time we might need to do a set of schemas so it is very important we make a document or plan for doing this task effectively.In the below task we have 2 environments .PRODDB(production) and TESTDB(test).I'm refreshing TESTDB by taking Data from PRODB,here only one schema is refreshed.

Source side:

Preparatory Steps:

Create directory or use an exiting directory by giving read and write permission for 'system' Database user to use that direcotry(TEST_MIG).

SQL> grant read,write on directory TEST_MIG to system;

Grant succeeded.

SQL> alter user system identified by TESTDBdba account unlock;

PRODDB:

Step 1:Exporting the Data from the source Database(PRODDB in our case)

vi expdp_refresh_schema_sep27.sh

$ expdp system/PRODDB@PRODDB DUMPFILE=REFRESH_SCHEMA.DMP DIRECTORY=DATA_PUMP_DIR SCHEMAS=REFRESH_SCHEMA LOGFILE=REFRESH_SCHEMA.log

$ nohup sh expdp_refresh_schema_sep27.sh>refresh_schema.out &

Step 2:Copying the dump file(Source Data) to Target Database server

We can use 'winscp' tool(A graphical utility for copying files from windows to linux or viceversa) or ftp or scp or tar or rsync for coping Data from source server to target server.

Step 3:Moving Data into the target Database.

$ impdp system/TESTDBdba@TESTDB DUMPFILE=REFRESH_SCHEMA.DMP DIRECTORY=TEST_MIG REMAP_SCHEMA=REFRESH_SCHEMA:REFRESH_SCHEMA LOGFILE=REFRESH_SCHEMA.log

Step 4:Verify the Data in Source and Target Databases.

Note:
In oracle 11g rel2,version:11.2.0.1.0 there are about 44 Distinct object_types comparing to previous versions this number is huge.

SQL> select *from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select distinct object_type from dba_objects;

OBJECT_TYPE
-------------------
EDITION
INDEX PARTITION
CONSUMER GROUP
SEQUENCE
TABLE PARTITION
SCHEDULE
QUEUE
RULE
JAVA DATA
PROCEDURE
OPERATOR

OBJECT_TYPE
-------------------
LOB PARTITION
DESTINATION
WINDOW
SCHEDULER GROUP
DATABASE LINK
LOB
PACKAGE
PACKAGE BODY
LIBRARY
PROGRAM
RULE SET

OBJECT_TYPE
-------------------
CONTEXT
TYPE BODY
JAVA RESOURCE
XML SCHEMA
TRIGGER
JOB CLASS
UNDEFINED
DIRECTORY
MATERIALIZED VIEW
TABLE
INDEX

OBJECT_TYPE
-------------------
SYNONYM
VIEW
FUNCTION
JAVA CLASS
JAVA SOURCE
INDEXTYPE
CLUSTER
TYPE
RESOURCE PLAN
JOB
EVALUATION CONTEXT

44 rows selected.

Source Database:

PRODDB:
---------

SQL> select count(*) from dba_objects
where owner='REFRESH_SCHEMA';

COUNT(*)
----------
132

SQL> select count(*) from dba_tables
where owner='REFRESH_SCHEMA';

COUNT(*)
----------
34

SELECT COUNT(*) FROM DBA_OBJECTS
WHERE OWNER='REFRESH_SCHEMA'
AND OBJECT_TYPE IN('TABLE','JOB','VIEW','PACKAGE','TRIGGER','SYNONYM','FUNCTION','PROCEDURE','TYPE')
ORDER BY OBJECT_TYPE;
SQL> SELECT COUNT(*) FROM DBA_OBJECTS
WHERE OWNER='REFRESH_SCHEMA'
AND OBJECT_TYPE IN('TABLE','JOB','VIEW','PACKAGE','TRIGGER','SYNONYM','FUNCTION','PROCEDURE','TYPE')
ORDER BY OBJECT_TYPE;
2 3 4
COUNT(*)
----------
62

SELECT COUNT(*) FROM DBA_OBJECTS
WHERE OWNER='REFRESH_SCHEMA'
AND OBJECT_TYPE IN

TARGET DATABASE:

TESTDB:
-------------
SQL> select count(*) from dba_objects
where owner='REFRESH_SCHE'; 2

COUNT(*)
----------
131

SQL> select count(*) from dba_tables
where owner='APEX4_DEV'; 2

COUNT(*)
----------
34

SQL> SELECT COUNT(*) FROM DBA_OBJECTS
WHERE OWNER='REFRESH_SCHEMA'
AND OBJECT_TYPE IN('TABLE','JOB','VIEW','PACKAGE','TRIGGER','SYNONYM','FUNCTION','PROCEDURE','TYPE')
ORDER BY OBJECT_TYPE;

COUNT(*)
----------
62

Hope it helps.Happy refreshing our Database environments

Best regards,

Rafi.

Master DBA and Apps DBA interview questions

2011-09-28T02:12:00.000-07:00

Hi,
Below are some of the Master DBA and Apps DBA:

1)Explain the installation and configuration of Oracle SOA?
What steps you followed for building Oracle SOA setup?
What were the major issues faced by you during Oracle SOA installation and configuration?
(Oracle Fusion DBA interview question)
Ans: Check the below link for the installation and configuration process:
http://rafioracledba.blogspot.com/search/label/Oracle%20SOA%20tasks

When installing and configuring SOA.The Repository creation utility(RCU) and SOA version should be same.
Eg: If we are using Oracle RCU version 11.1.1.5.0 than we have to SOA version also 11.1.1.5.0.
I Installed the Oracle RCU version 11.1.1.5.0 and SOA version 11.1.1.2.0 so was facing the issue like SOA infra was not coming up so I upgraded the Oracle SOA version to 11.1.1.5.0 by using upgrade option,while using Oracle SOA version 11.1.1.5.0 than after upgradation,I started SOA managed server it worked.

For Starting the SOA(Service-Oriented Architecture) managed server:

Set the SERVER_NAME and ADMIN_URL environment variables, then run the script with no arguments.

[oracle@nodefmw bin]$ pwd
/Middleware/SOASuite11gR1/wlserver_10.3/common/bin
Usage: startManagedWebLogic [SERVER_NAME] [ADMIN_URL]

for example:

startManagedWebLogic managedserver1 http://localhost:7001
./startManagedWegLogic nodefmw.apac.abc.com http:// nodefmw.apac.abc.com:7001
(OR)
#./startManagedWeblogic.sh soa_server1
User:weblogic
Pass:welcome123

For Starting the BAM(Business Authentication Module) managed server:

#./startManagedWeblogic.sh bam_server1
User:weblogic
Pass:welcome123

2)Have you done DMZ setup?What are the steps followed in doing DMZ setup?
(Oracle Apps DBA interview question)
Ans: Thanks Navdeep for explaing the things beautifully and clearly.For detail steps please
check the Navdeep's link.
Reference:
http://practicalappsdba.wordpress.com/2007/03/26/dmz-configuration-for-irecruitment/

For implementing DMS check the MY ORACLE SUPPORT note:
Oracle E-Business Suite R12 Configuration in a DMZ [ID 380490.1]

A DMZ (De-Militarized Zone) is a separate part of an organization’s network which is a shielded and 'cut off' from the main corporate network and its systems.The DMZ contains technical equipment to prevent access from external parties(say on the Internet) from gaining access to your main systems.

In the IT industry,a demilitarized zone is a single or multi-segment perimeter network that demarks the portion of the corporate network that lies between the intranet and outside networks.Corporate DMZ borders are enforced by firewalls and other dedicated networking devices.

When configuring Oracle E-Business Suite in a DMZ configuration,
firewalls are deployed at various levels to ensure that only the traffic that the architecture expects is allowed to cross the firewall boundaries.
The firewalls ensure that if intrusion attempts against machines in the DMZ are successful,the intrusion is contained within the DMZ and the machines in the intranet are not affected.

The steps followed in doing DMZ setup are as follows:

To make Oracle E-Business Suite modules as secure as possible, the following tasks may need to be performed.

* Use of separate web node for external usage
* Setting of server level profile values
* Associate trust levels to application middle tier nodes
* Mark a subset of responsibilities as available on an external web node
* Deploy a Reverse proxy in front of the external web node
* Configuring a URL firewall and mod security in the reverse proxy
* Run only the required Oracle E-Business Suite Application services on the external web tier

3)What are the background processes in Oracle ASM explain?(oracle DBA interview question)
Ans: The following background processes are an integral part of Automatic Storage Management:

1)ARBn(Actual Rebalance)
* ARBn(Actual Rebalance) performs the actual rebalance data extent movements in an Automatic Storage Management instance.
* There can be many of these processes running at a time, named ARB0, ARB1, and so on.

2)ASMB(ASM Balance)
* ASMB(ASM Balance) runs in a database instance that is using an ASM disk group.
* ASMB communicates with the ASM instance, managing storage and providing statistics.
* ASMB can also run in the ASM instance. ASMB runs in ASM instances when the ASMCMD cp command runs or
* when the database instance first starts if the SPFILE is stored in ASM.

3)GMON(Global monitor)
* GMON(Global monitor) maintains disk membership in ASM disk groups.

4)MARK(Marking)
* MARK marks ASM allocation units as stale following a missed write to an offline disk.
* This essentially tracks which extents require resync for offline disks and

5)RBAL(Rebalance)
* RBAL runs in both database and ASM instances.
* In the database instance,it does a global open of ASM disks.
* In an ASM instance, it also coordinates rebalance activity for disk groups.

4)What are the parameters specific to Oracle ASM explain?(oracle DBA interview question)
Ans: The following parameters for specific to ASM instance They are:

1)ASM_DISKGROUPS
2)ASM_DISKSTRING
3)ASM_POWER_LIMIT
4)ASM_PREFERRED_READ_FAILURE_GROUPS
5)INSTANCE_TYPE
Ofcourse,there are other parameters also like:
DB_CACHE_SIZE,DIAGNOSTIC_DEST,LARGE_POOL_SIZE,PROCESSES,REMOTE_LOGIN_PASSWORDFILE,SHARED_POOL_SIZE

Let us briefly discuss them,
1)ASM_DISKGROUPS:

The ASM_DISKGROUPS initialization parameter specifies a list of the names of disk groups that an ASM instance mounts at startup. Oracle ignores the value that you set for ASM_DISKGROUPS when you specify the NOMOUNT option at startup or when you issue the ALTER DISKGROUP ALL MOUNT statement. The default value of the ASM_DISKGROUPS parameter is a NULL string. If the parameter value is NULL or is not specified, then ASM does not mount any disk groups.

The ASM_DISKGROUPS parameter is dynamic. If you are using a server parameter file (SPFILE), then you should not need to manually alter the value of ASM_DISKGROUPS. ASM automatically adds a disk group to this parameter when the disk group is successfully created or mounted. ASM also automatically removes a disk group from this parameter when the disk group is dropped or dismounted. The following is an example of setting the ASM_DISKGROUPS parameter dynamically:

SQL> ALTER SYSTEM SET ASM_DISKGROUPS = 'CONTROLFILE, DATAFILE, LOGFILE, STANDBY'

2)ASM_DISKSTRING:

The ASM_DISKSTRING initialization parameter specifies a comma-delimited list of strings that limits the set of disks that an ASM instance discovers. The discovery strings can include wildcard characters. Only disks that match one of the strings are discovered. The same disk cannot be discovered twice.

The discovery string format depends on the ASM library and the operating system that are in use. Pattern matching is supported; refer to your operating system-specific installation guide for information about the default pattern matching. For example, on a Linux server that does not use ASMLIB, to limit the discovery process to only include disks that are in the /dev/rdsk/ directory, set ASM_DISKSTRING to:

/dev/rdsk/*

The asterisk is required. To limit the discovery process to only include disks that have a name that ends in disk3 or disk4, set ASM_DISKSTRING to:

/dev/rdsk/*disk3, /dev/rdsk/*disk4

3)ASM_POWER_LIMIT:

The ASM_POWER_LIMIT initialization parameter specifies the default power for disk rebalancing.
The default value is 1 and the range of allowable values is 0 to 11 inclusive. A value of 0 disables rebalancing.
Higher numeric values enable the rebalancing operation to complete more quickly, but might result in higher I/O overhead.

4)ASM_PREFERRED_READ_FAILURE_GROUPS:

The ASM_PREFERRED_READ_FAILURE_GROUPS initialization parameter value is a comma-delimited list of strings that specifies the failure groups that should be preferentially read by the given instance. This parameter is generally used only for clustered ASM instances and its value can be different on different nodes. For example:

diskgroup_name1.failure_group_name1, ...

The ASM_PREFERRED_READ_FAILURE_GROUPS parameter setting is instance specific.
This parameter is only valid for clustered ASM instances and the default value is NULL.
Note:
The ASM_PREFERRED_READ_FAILURE_GROUPS parameter is valid only in Oracle RAC environments.

5)INSTANCE_TYPE:

The INSTANCE_TYPE initialization parameter must be set to ASM for an ASM instance.
This is a required parameter and cannot be modified. The following is an example of the INSTANCE_TYPE parameter
in the initialization file:

INSTANCE_TYPE=ASM

6)DB_CACHE_SIZE

You do not need to set a value for the DB_CACHE_SIZE initialization parameter if you use automatic memory management.

The setting for the DB_CACHE_SIZE parameter determines the size of the buffer cache.
This buffer cache is used to store metadata blocks. The default value for this parameter is suitable for most environments.

7)DIAGNOSTIC_DEST

The DIAGNOSTIC_DEST initialization parameter specifies the directory where diagnostics for an instance are located.
The value for an ASM instance is of the form:

diagnostic_dest/diag/asm/db_name/instance_name

For an ASM instance, db_name defaults to +asm.

8)LARGE_POOL_SIZE

You do not need to set a value for the LARGE_POOL_SIZE initialization parameter if you use automatic memory management.

The setting for the LARGE_POOL_SIZE parameter is used for large allocations.
The default value for this parameter is suitable for most environments.

5)What all the possible ways to convert/copy/transfer non-asm filesystem to asm or viceversa?
What all the conversions possible in asm?(oracle DBA interview question)
Ans: In 10g the possible ways to migrate - DBMS_FILE_TRANSFER, rman (copy vs.
backup), or XMLDB FTP
In 11g, we introduced the asmcmd copy command. Key point here is that copy files
out is great for:
1. archive logs
2. Controlfiles
3. Datafiles for debugging
4. Dumpsets (can be done across platforms)
Copying files in:
TTS
Copy in only supported files.
28
ASMCMD Copy
ASMCMD> ls
+fra/dumpsets/expdp_5_5.dat
ASMCMD> cp expdp_5_5.dat sys@rac1.orcl1:+DATA/dumpsets/ex
pdp_5_5.dat
source +fra/dumpsets/expdp_5_5.dat
target +DATA/dumpsets/expdp_5_5.dat
copying file(s)...
file, +DATA/dumpsets/expdp_5_5.dat,
copy committed.

6)What is ACFS?what are the advantages of ACFS?(oracle DBA interview question)
Ans: The new feature added in Oracle11g release2 for ASM is ACFS(ASM cluster file system)
ACFS is available in 11gR2 for LINUX and few unix platforms and Windows operating system
also have a similar solution is available.

ACFS will work with Single Instance Installations as well as Cluster Databases.
It is strongly integrated with the Oracle Clusterware 11g release2.
One more point to note is - Supported protocols for NAS are NFS(Network file system and CIFS(Common Internet File System).

This new feature is also named Unified Storage Management and allows for storing the following data in ASM:

1)Application File Data
2)Oracle Cluster Registry (OCR)
3)Voting Disk and
4)Oracle Binaries

Major advantages of ACFS are:

1)We will have all the trace files, alert.log,reports in ASM which makes ASM a complete storage management system .Because of this reason both database and non-database files are stored ins ASM and it completely eliminates the need for any third party cluster file systems like veritas and sun clusters.

2) ACFS uses a journaling mechanism and uses endian independent metadata to support large files and file systems to an extent up to exabytes.
A special metadata log structure is maintained for integrity checks and fast recovery through a metadata transaction engine and

3)File systems in ASM can dynamically be expended and contracted with no downtime.
High availability is fully supported through mirroring and striping capabilities for all files now with ASM.

7)what is interconnect(Cache fusion) in Oracle rac?(oracle DBA interview question)
Ans: Starting with Oracle 9i, RAC can transfer blocks from one instance to another this can be called as interconnect (cache fusion).

8)Have you done workflow configuration?(oracle Apps DBA interview question)
Ans: Best link with explaination I found:
http://onlineappsdba.com/index.php/2008/08/01/workflow-notification-mailer-configuration-using-autoconfig-for-outbound-processing/

9)Explain the importance of autoconfig file?(oracle Apps DBA interview question)
Ans:
AutoConfig uses a file called context file, which is used to configure changes.Context file is a XML file having all the parameters of application system.
These parameters are part of some configuration file for some services or it may be part of some environment file.When we create an application system (using Rapid install),context file gets created automatically will all the parameters
and there values. Most of the parameters take default value,where as some parameters takes the value that is supplied
at the time of installation.

All the information required to configure Oracle Apps 11i/R12 are stored in files called as Context file.So there are two context file, one for Apps Tier(Middle Tier) and one for DB Tier.

Oracle apps 11i Context file location is as follows:

Apps Tier:$APPL_TOP/admin/$TWO_TASK.xml
DB Tier:$ORACLE_HOME/appsutil/$TWO_TASK.xml

Oracle apps R12 Context file location is as follows:

Apps Tier:$INST_TOP/appl/admin/$TWO_TASK_.xml
DB Tier:$ORACLE_HOME/appsutil/$TWO_TASK_.xml

Where: TWO_TASK is the SID set for EBS Database.

More details about autoconfig can be extracted from the below mentioned link with nice explaination:

http://onlineappsdba.com/index.php/2008/01/28/autoconfig-in-oracle-apps-11i-r12-12i/

Context file is repository for configuration stored in xml format.
Its xml file and file name format is _.xml in Oracle Apps 11i

10)What all the techniques to improve oracle application patching mechanism?
(oracle Apps DBA interview question)
Ans:
To improve the oracle application patching mechanism in terms of performance and other criteria we can apply
patches as given below:

1)Merge Patches using admrgpch

admrgpch utility is used to merge two or more patches in oracle applications.
The advantage of merging patches is that it reduces downtime and thus very much useful.

Note 1: The syntax for using admrgpch utility is as given below:

admrgpch -s -d -merge_name

For eg:

admrgpch -s -d -merge_name

We have to make sure the the merge path log file "admrgpch.log" does not contain any error,once the two patches
are merged.

Note 2:'admrgpch' utility will not merge patches of different Oracle applications releases and operating systems platforms and with different parallel modes and we cannot use 'admrgpch' utility to merge AD and Non-AD patches.

2)To improve the performance while applying patch using 'adpatch'(auto patch utility) has lot of options like:
options=nocopyportion noautoconfig nogenerateportion hotpatch ... We can use as per our convenience.

Eg:
$adpatch options=noautoconfig
$

3)If you cannot afford downtime than you can apply patch using options=hotpatch,very much useful for production environment here it is not required to keep the application in maintainance mode.

Eg:
$adpatch options=hotpatch

11)How we register new products in Oracle applications?(oracle Apps DBA interview question)
Ans: 'adsplice' is an ad utility which is used to add off-cycle/new product to oracle apps.
i.e. products released between maintenance packs.

Download the product patch which contains information about the new product,'adsplice' control file and other associated files. Go through the readme and apply pre-reqs if any.Now for using 'adsplice' there is a control file newprods.txt. A typical newprods.txt looks like as given below:

product=zsa
base_product_top=*APPL_TOP*
oracle_schema=zsa
sizing_factor=100
main_tspace=ZSAD
index_tspace=ZSAX
temp_tspace=TEMP
default_tspace=ZSAD

If we are using OFA tablespace model then we need to change the last 4 parameters else for OATM(Oracle Application tablespace Model) it is optional.

We have to copy the new-product in $APPL_TOP/admin location and run 'adsplice' utility from 'applmgr' OS user and follow the prompts.Then we can verify that product is correctly spliced or not using adutconf.sql (AD Configuration report).
We need to logout and login again so that the new environment is set.
We can then verify that new _TOP environment variable is set and need to apply the patches for newly added product.

12)What is SHARED APPL_TOP?What are the major advantages and disadvantages of SHARED APPL_TOP?
(oracle Apps DBA interview question)
Ans: Basically Shared APPL_TOP's means sharing APPL_TOP and
COMMON_TOP across the Application Servers.

Benefits:

1)Administration tasks only need to be performed on any one node.
2)Application patches only need to be applied to any one node in the system.
3)Allows the use of "Distributed AD" to reduce downtime (check MetaLink 236469.1).
4)Reduces overall disk space requirements.
5)Makes it easier to add additional nodes to our Applications system.
6)Create single node clones from a shared APPL_TOP multi node system and
7)Merge APPL_TOP(Merge TOPs) from different application tiers into a single application tier APPL_TOP.

Disadvantages:

1)Single point of failure.
2)Performance might be an issue,if shared APPL_TOP's filesystem will be a 'nfs'(network file system) mount.

13)Explain the architecture of Oracle Golden Gate?(Oracle DBA interview question)
Ans: The Oracle GoldenGate software architecture is comprised of three primary components:
1)Capture,
2)Trail Files and
3)Delivery.
As shown in the figure,
This modular approach allows each component to perform its tasks independently of the others,
accelerating data replication and ensuring data integrity.

1)Capture:
Oracle GoldenGate’s Capture module resides on the source database and looks for new transactional activity.The Capture module reads the result of insert, update, and delete operations by directly accessing the database transaction (redo) logs, and then immediately captures new and changed data for distribution.
The Capture module only moves committed transactions—filtering out intermediate activities and rolled-back operations—which not only reduces infrastructure load but also eliminates potential data inconsistencies.Further optimization is achieved through transaction grouping and optional compression features.

2)Trail Files:
Oracle GoldenGate’s Trail Files contain the database operations for the changed data in a transportable,platform-independent data format.
Trail Files are a critical component within Oracle GoldenGate’s optimized queuing mechanism.
They reside on the source and/or target server but exist outside of the database to ensure heterogeneity,improved reliability, and minimal data loss.
This architecture minimizes impact to the source system because no additional tables or queries to the database are required to support the data capture process. The Capture module reads once, and then immediately moves the captured data to the external Trail File for delivery to the target(s).

In the event of an outage at the source and/or target, the Trail Files contain the most-recent
changed data up to the point of the outage, and the data is applied once the systems are back online again.

3)Delivery:
Oracle GoldenGate’s Delivery module takes the changed data from the latest Trail File and applies it to the target database using native SQL for the appropriate relational database management system.Delivery can be made to any open database connectivity–compliant database.
The Delivery module applies each transaction in the same order as it was committed and within the same transactional context as at the source, enabling consistency and referential integrity at the target.
To enhance IT flexibility, captured data can also be delivered to a Java Message Service destination or as a flat file using Oracle GoldenGate Application Adapters.

14)What is difference between Socket & Servlet Mode in Apps Forms?(oracle Apps DBA interview question)
Ans: When forms run SOCKET Mode these are dedicated connection between Client Machine & Form Server (Started by adfrmctl.sh).When Forms run in servlet mode the forms requests are fulfilled by Jserv in Apache .
There will be additional JVM for Forms Request in that case and we won't start form via adfrmctl.sh.

15)How to compile Forms in 11i and R12?(oracle Apps DBA interview question)

Ans: We can Compile Forms in 11i using 'f60gen' utility as follows:

$f60gen module=.fmb userid=apps/ output_file=$XX_TOP/forms/US/.fmx

Where as,In R12 we can Compile Forms using 'frmcmp_batch' utility as follows:

$frmcmp_batch .fmb userid=apps/ output_file=$XX_TOP/12.0.0/form/US/.fmx module_type=form compile_all=special

16)If a patch fail how to proceed in Oracle Application?
Ans: I like the below link for this:

http://oracleappsdba1.blogspot.com/2009/02/oracle-apps-patch-fails.html

17)Describe the steps involved in the upgradation from Oracle 11i to R12 Appplications?
(oracle Apps DBA interview question)
Ans: Atul again described these upgradation steps beautifully in the below mentioned link:

http://onlineappsdba.com/index.php/2008/07/09/upgrade-oracle-apps-11i-to-r1212i-1204-key-points/
http://onlineappsdba.com/index.php/2007/10/13/upgrade-oracle-applications-to-release-1202/
http://onlineappsdba.com/index.php/2008/03/26/upgrade-oracle-applications-to-1204/

We usually prepare the below documents for doing upgrade of Oracle Applications:
1)xls document for preparing a plan and to record the steps of upgrade.
2)Word document for the actual implemented steps in details the steps.

Best regards,

Rafi.

Cost based optimizer approach

2011-09-26T04:45:00.000-07:00

Hi,
Performance tuning of Database and queries is very important from DBA's point of view it is no-doubt very vast and many people say it is a deep ocean and tuning can
be done to unlimited extent when we really know our data very well but many times
developers and designers will be having details of data in a better way so tuning as per me is a combined effort where DBA's,developers and Database Designers work together to make a well tune system.In the below example I'm starting with the basics
but in my future posts I will surely put some real time tuning techniques in a detailed way.
We all know how important is the 'optimizer' as depending on setting
of the optimizer and statistics the query will execute better.We all know there are two types of optimizers
1)Rule-based optimizer
2)cost-based optimizer

Let us start with the below example:

For checking the optimizer mode we can use the below approach:

SELECT * FROM V$PARAMETER WHERE NAME = 'optimizer_mode';
(OR)
SQL> show parameter %OPTIMIZER%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS

This will tell us the mode for the database. If the value is 'CHOOSE' than the
database is in Cost-Based.However,if there are no statistics on the tables
that a query is based upon, then it is in RULE mode.

To tell for a particular query,we will need to do an EXPLAIN PLAN on the
query. If we see costs associated with the query, then the query is in
COST-BASED mode. Otherwise it is in RULE-BASED mode.

Note 1: we can use the RULE hint to force rule-based mode on any query.
Note 2:
1) Possible values for optimizer_mode = choose/ all_rows/ first_rows/ first_rows[n]
2) The ALL_ROWS optimizer mode uses a cost-based approach to determine the best access path to access your tables.

I'm not focusing on rule-based much as it is not suggested much to use.

Eg:

SQL> conn schema_test
Enter password:
Connected.
SQL> select *from TTTT;

no rows selected

SQL> SET AUTOTRACE TRACEONLY;
SQL> select *from TTTT;

no rows selected

Execution Plan
----------------------------------------------------------

----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 |
| 1 | TABLE ACCESS FULL| TTTT | 1 | 13 | 2 |
----------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
330 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Hope it helps...

Happy Oracle Performance tuning learning...

Best regards,

Rafi.

Challenging DBA and Apps DBA interview questions

2011-09-23T05:15:00.000-07:00

Hi,
I find some time to write on the challenging Apps DBA interview questions.I found these questions challenging as these questions will surely need some time tolearn,practise along with practical experience on the tasks and concepts of Oracle Apps,RAC.

1)Explain Database migration steps in details?(DBA interview question)
Ans: Database migration or Data migration involves a team effort of DBAs,Developers,Data Modelors/Database Designers
Applications Administrator if the other applications servers like JBOSS/tomcat is involved.When we are working
in team the best way to complete this task is to prepare a plan of tasks along with time and table well in advance on an excell sheet and intimate each of the team members to communicate effectively.
From DBA's point below is an example of one such task,Check the below link for the details:

http://rafioracledba.blogspot.com/2011/04/step-by-step-migration-of-schemas-from.html

2)What are the 3 ips used in RAC configuration,explain them in brief?(DBA interview question)
Ans: 3 IPs used in RAC configuration are public,private and virtual IP.
In brief we can describe them as follows:

1.Public IP address:Public IP adress is the normal IP address typically used to manage storage,system and database.
2.private IP address:Private IP address is used only for internal clustering processing (i.e Cache Fusion or as interconnect).
3.Virtual IP address:VIP is used by database applications to enable fail over when one cluster node fails.

3)What is cache fusion?(DBA interview question)
Ans: Oracle RAC uses Cache Fusion to synchronize the data stored in the buffer cache of each database instance. Cache Fusion moves current data blocks (which reside in memory) between database instances, rather than having one database instance write the data blocks to disk and requiring another database instance to reread the data blocks from disk. When a data block located in the buffer cache of one instance is required by another instance, Cache Fusion transfers the data block directly between the instances using the interconnect, enabling the Oracle RAC database to access and modify data as if the data resided in a single buffer cache.

4)What is voting Disk,what is the major use of it?(DBA interview question)
Ans:Voting disk Manages cluster membership and arbitrates cluster ownership
between the nodes in case of network failures.The voting disk is a file that
resides on shared storage.

5)What is OCR,what is the major use of it?(DBA interview question)
Ans:Oracle Cluster Registry (OCR)–Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR contains information such as which database instances run on which nodes and which services run on which databases. The OCR also stores information about processes that Oracle Clusterware controls. The OCR
resides on shared storage that is accessible by all the nodes in your cluster.

6)In RAC 11g as we all know ASM can be used for storing OCR and voting disk,if that is the case how the clusterware start?(DBA interview question)
Ans: The clusterware starts and works properly because of the new feature added in Oracle11g release2 for ASM i.e ACFS(ASM cluster file system).ACFS is available in 11gR2 for LINUX and few unix platforms and Windows operating system
also have a similar solution available.

* ACFS will work with Single Instance Installations as well as Cluster Databases.
It is strongly integrated with the Oracle Clusterware 11g release2.
One more point to note is - Supported protocols for NAS are NFS(Network file system and CIFS(Common Internet File System).

This new feature is also named Unified Storage Management and allows for storing the following data in ASM:

1)Application File Data
2)Oracle Cluster Registry (OCR)
3)Voting Disk and
4)Oracle Binaries

Major advantages of ACFS are:

1)We will have all the trace files, alert.log,reports in ASM which makes ASM a complete storage management system .Because of this reason both database and non-database files are stored ins ASM and it completely eliminates the need for any third party cluster file systems like veritas and sun clusters.

2) ACFS uses a journaling mechanism and uses endian independent metadata to support large files and file systems to an extent up to exabytes.A special metadata log structure is maintained for integrity checks and fast recovery through a metadata transaction engine and

3)File systems in ASM can dynamically be expended and contracted with no downtime.
High availability is fully supported through mirroring and striping capabilities for all files now with ASM.

7)How you change apps password?Explain the syntax of FNDCPASS?(Apps DBA interview question)
Ans: FNDCPASS utility is used to change the password of apps/applsys and all other application products users like gl,ar.FNDCPASS utilliy is supplied with various parameters and options,details are given below along
with one task for complete understanding.

Note 1: We use FNDCPASS to change apps password,as the APPLSYS (APPS) password became corrupted using ALTER USER because an applications session was not maintained at the same time. This apps session is necessary to change the APPLSYS password in: 'Security> Oracle> Register' WHILE being in SQL*PLUS as the SYSTEM user thas why we use The supported method FNDCPASS to change the password.

Note 2:Backup FND_ORACLE_USERID and FND_USER tables before using FNDCPASS utility for safety purpose.

To implement the solution, please execute the following steps:

1. Restore the FND_ORACLE_USERID and FND_USER tables from a backup.

2. Then run FNDCPASS to change the APPLSYS password. Ex.

FNDCPASS apps/ 0 Y system/ SYSTEM APPLSYS WELCOME

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS apps

Eg:
-bash-3.2$ FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS apps
Log filename : L6021164.log

Report filename : O6021164.out
-bash-3.2$ less L6021164.log

+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

module:
+---------------------------------------------------------------------------+

Current system time is 23-SEP-2011 03:53:32

+---------------------------------------------------------------------------+

Working...

+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 23-SEP-2011 03:53:38

+---------------------------------------------------------------------------+

For example,the following command changes the GL user password to 'gl'.

FNDCPASS apps/apps 0 Y system/manager ORACLE GL gl

-bash-3.2$ FNDCPASS apps/apps 0 Y system/manager ORACLE GL gl
Log filename : L6021170.log

Report filename : O6021170.out

-bash-3.2$ less L6021164.log

+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

module:
+---------------------------------------------------------------------------+

Current system time is 23-SEP-2011 03:59:01

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 23-SEP-2011 03:59:01

+---------------------------------------------------------------------------+

Note 1: Passwords for APPLSYS and the APPS schemas -- including the MRC schema -- must be the same.
If you change the password for one, FNDCPASS automatically changes the others

Note 2:After changing the password as the golden rule for apps dba check the log file if it gives
error than password is unchaged.Error details in log file can be as give below:

ERRORs:
APP-FND-01496 Results From FNDCPASS Chaning The APPLSYS password
APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password

Note 3:When changing 'apps' password it is important to run autoconfig to propagate the change across the application or else we will need change the 'apps' password in following files manually which can be a tedious task.

1. $IAS_ORACLE_HOME\Apache\modplsql\cfg\wdbsvr.app file
2. $FND_TOP/resource/wfmail.cfg
3. $COMMON_TOP/admin/scripts//adcmctl.ch
4. $OA_HTML/bin/appsweb.cfg
5. $AD_TOP/admin/template/CGIcmd.dat

Q 8) What is wdbsvr.app file used for? What's full path of this file? What's significance of this file ?(Apps DBA inteview question)
Ans: If we lost the 'apps' user password we can see this password in wdbsvr.app file.
The file wdbsvr.app file is located under $IAS_ORACLE_HOME/Apache/modplsql/cfg

Based on file location I am sure you can say this is related to Apache, & looking into modplsql/cfg , I am sure you will say its related to mod_pls (mod plsql component of Apache/Oracle 11i WebServer) configuration file. This file is used by mod_plsql component of Apache to connect to database. So when you type url http://hostname:port/pls/SID , whenever Apache(11i Web Server) finds that request is for /pls/ then Apache delegates this request to mod_pls component which in turn pick this file & check if there is any DAD with name SID (in our example its VISION11I) &
Sample entry in wdbsvr.app
[DAD_VISION11I]
connect_string = VISION11I
password = apps
username = APPS
default_page = fnd_web.ping

On typing http://hostname:port/pls/VISION11I ,
it will connect to database using apps schema & will return you page fnd_web.ping ( where fnd_web is package & ping is procedure or vise versa).So story about this file doesn't stop here,this URL which I mentioned about is quite useful in troubleshooting so you can check if database connection is working fine
or not. Another thing you want to check about this file is since it stores APPS password you need to change here whenever you change apps password .

Note:In Apps 11i we have apps password without encrypted form in the below mentioned locations:

• iAS_TOP/Apache/modplsql/cfg/wdbsvr.app
• ORACLE_HOME/reports60/server/CGIcmd.dat

In R12 you won't see these files anymore because Modplsql module got removed and we won't be seeing the files with apps password.

9)Explain the process when we login with 'sysadmin' what happens at the backend?(Apps DBA interview question)
Which table is involved?
Ans: When we login using 'sysadmin' user and password there is a validation takes place,once the user gets validated than E-business suite home page is displayed.

8)How you troubleshoot apps login issue?(Apps DBA interview question)
Ans:Atul's link explain this in best way,please go through it even for troubleshooting at our work environment also.

http://becomeappsdba.blogspot.com/2006/10/troubleshoot-oracle-apps-web-server.html

9)Explain the major troubling which you had done in Oracle Apps?
Ans:We have to explain the recent troubeshooting which we had done recently.Whenever we are doing troubleshooting in Oracle Applications,it is very important to get familiar with the log file locations,depending
on release of Apps.In Apps 11i and R12 the log file locations have changed,so best link I found to remember this locations is given below:

http://easyoracleapps.wordpress.com/category/r12/

10)What do we have in FND_NODES?when do we run FND_CONC_CLONE.SETUP_CLEAN ?(Apps DBA interview question)

Ans:FND_NODES have the information about node_name,node_mode, support_cp.
we use FND_CONC_CLONE.SETUP_CLEAN to cleanup FND_NODES table to clear corrupted setup after performing
clonning of Applications.

##
## before cleanup
##

SQL>select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES

NODE_NAME N S S S S
------------------------------ - - - - -
HOST01 O Y N Y N
MIDTIER01 O N Y N Y
APPSERVER21 N Y N Y <-- bogust host
APPSERVER22 N Y N Y <-- bogust host
APPSERVER23 N Y N Y <-- bogust host
AUTHENTICATION O N N N N

6 rows selected.

##
## Cleanup
##
login as 'applmgr'(application filesystem owner),set the application environment and run
FND_CONC_CLONE script to remove bogust host entries.Now login with 'apps' user in Database.

SQL> sho user
USER is "APPS"
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

##
## After cleanup
##

SQL> select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES
/

##
## Run AutoConfig
##
Note:Context file location
In Apps 11i:$APPL_TOP/admin/$TWO_TASK.xml
In Apps R12:$INST_TOP/appl/admin/TWO_TASK_.xml

## Host01
In Apps 11i:
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/ appspass=

In Apps R12:
$AD_TOP/bin/adconfig.sh contextfile=$INST_TOP/appl/admin/TWO_TASK_.xml
appspass=apps

## Host
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/XTPR_host02.xml appspass=

##
## After AutoConfig runs
##

select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES

NODE_NAME N S S S S
------------------------------ - - - - -
MIDTIER01 O N Y N Y
AUTHENTICATION O N N N N
HOST01 O Y N Y N

11)Where do we find the details of all the ports in Oracle Apps?
Ans: Details of all the ports for various severs are present in portlist.ini file.
This file is located inside technology stack in the below mentioned path:

/oraAPP/apps/tech_st/10.1.2/install

The portlist.ini file have the details of all the ports information of all the servers like HTTP,Java,Oracle Notification
Server,Oracle Developer suite,DATABASE LISTENER,Web Cache,Reports Services,DCM Discovery,Log Loader which can be
seen below.

-bash-3.2$ view portlist.ini

;OracleAS Components reserve the following ports at install time.
;As a post-installation step, you can reconfigure a component to use a different port.
;Those changes will not be visible in this file.

[System]
Host Name = TESTNODEebs.in.apps.com

[Ports]
Oracle HTTP Server port = 7778
Oracle HTTP Server Listen port = 7779
Oracle HTTP Server SSL port = 8250
Oracle HTTP Server Listen (SSL) port = 4444
Java Object Cache port = 7000
Oracle Notification Server Request port = 6003
Oracle Notification Server Local port = 6102
Oracle Notification Server Remote port = 6201
Oracle Developer Suite HTTP port = 8888
Oracle Developer Suite JMS port = 9240
Oracle Developer Suite RMI port = 23910
ASG port = 7891
Oracle HTTP Server Diagnostic port = 7200
Log Loader port = 44000
Application Server Control RMI port = 1850
DCM Discovery port = 7100
Oracle Net Listener = 1521
Oracle Management Agent Port = 1157
Application Server Control port = 1156
Web Cache HTTP Listen port = 7778
Web Cache HTTP Listen (SSL) port = 8250
Web Cache Administration port = 9400
Web Cache Invalidation port = 9401
Web Cache Statistics port = 9402
Reports Services bridge port = 14011
Reports Services discoveryService port = 14021
Reports Services SQL*Net port = 1950

If you don't know port number of your web server you can check it in file
$IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf with directive Listen & Port

12)How we check the sysadmin password which we are using to login Oracle E-business suite home page is correct?(Apps DBA interview question)
Ans:We Use Following query to check whether 'sysadmin' password that we are
using is correct or incorrect.

select fnd_web_sec.validate_login('SYSADMIN','Passord_for_sysadmin')
from dual;

If it returns Y then sysadmin password is correct
If it returns N then sysadmin password that we are using
is wrong. We have to change sysadmin password using 'FNDCPASS' utility.

Eg:

SQL> select fnd_web_sec.validate_login('SYSADMIN','Passord_for_sysadmin')
from dual;

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','sysadmin')
--------------------------------------------------------------------------------
N

SQL> select fnd_web_sec.validate_login('SYSADMIN','welcome')
from dual; 2

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','WELCOME')
--------------------------------------------------------------------------------
Y

13)How to check the Standby Database is in sync with production Database?(Apps DBA interview question)
Ans: Check the below link for useful details:

http://www.oraclemasters.in/?p=1255

RMAN COMMANDS

2011-09-21T09:42:00.000-07:00

Hi,
We use RMAN COMMANDS for doing lot of tasks like backup,cloning,conversion of filesystems from NON-ASM to ASM and vice-versa.The commands of RMAN are many so I decided to make a note of few crucial ones,hope it can be useful.

There are two types of RMAN commands:
1)Stand-alone:Executed at the RMAN prompt and are generally self-contained. CHANGE, CONNECT,CREATE CATALOG, RESYNC CATALOG. CREATE SCRIPT, DELETE SCRIPT, REPLACE SCRIPT etc.
2)JOB:These are usually grouped and RMAN executes this commands inside of RUN command block sequentially. If any command fails, RMAN ceases processing, no further commands within the block are executed.

•THE CONFIGURE COMMAND :
 CONFIGURE AUTOMATIC CHANNEL :
RMAN > CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/db01/backup/%U’;

 IMPLEMENT RETENTION POLICY BY SPECIFYING RECOVERY WINDOW :
RMAN >CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 days ;
- Recovery window: a period of time that begins with the current time and extends backward in time to the point of recoverability. In above example, the command ensures that for each data file, one back up that is older than the point of recoverability (7 days) must be retained.

 IMPLEMENT RETENTION POLICY BY SPECIFYING REDUNDANCY:
RMAN >CONFIGURE REDUNDANCY POLICY TO REDUNDANCY 2;
- Redundancy value indicates that any number of backups or copies beyond a specified number need not be retained. The default is 1 day.

 CONFIGURE DUPLEXED BACKUP SETS:
RMAN >CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE disk TO 2;

- We can create up to 4 copies (in above example 2) of each back up piece in a backup set for all backup commands that use automatic channels. This applies only for datafiles and archived redo log files.

 CONFIGURE BACKUP OPTIMIZATION:
RMAN >CONFIGURE BACKUP OPTIMIZATION ON;
- With this setting on, the BACKUP command does not backup files to a device type if the identical file has already been backed up to the device type. For two files to be identical, there contents must be exactly the same. Default value is off.

 USE THE CLEAR OPTION TO RETURN TO THE DEFAULT VALUE:
RMAN >CONFIGURE RETENTION POLICY CLEAR;
RMAN >CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;

•THE SHOW COMMAND :
- This command displays persistent configuration settings.
 AUTOMATIC CHANNEL CONFIGURATION SETTINGS:
RMAN>SHOW CHANNEL;
RMAN>SHOW DEVICE TYPE;
RMAN>SHOW DEFAULT DEVICE TYPE;

 RMAN RETENTION POLICY CONFIGURATION SETTINGS:
RMAN>SHOW RETENTION POLICY;

 NUMBER OF BACKUP COPIES:
SHOW DATAFILE BACKUP COPIES;

 MAXIMUM SIZE OF BACKUP SETS:
SHOW MAXSETSIZE;

 TABLESPACES EXCLUDED FROM WHOLE DATABASE BACKUP:
SHOW EXCLUDE;

 STATUS OF BACKUP OPTIMIZATON:
SHOW BACKUP OPTIMIZATION;

•LIST COMMAND:
- LIST command is used to produce a detailed report listing all information for the following.

 LIST BACKUPS OF ALL FILES IN THE DATABASE:
RMAN >LIST BACKUP OF DATABASE;

 LIST ALL BACKUP SETS CONTAINING THE uses1.dbf DATAFILE:
RMAN >LIST BACKUP OF DATAFILE ”/db1/oradata/u03/users1.dbf”;

 LIST ALL COPIES OF DATAFILES IN THE SYSTEM TABLESPACE:
RMAN >LIST COPY OF TABLESPACE “SYSTEM”;

•THE REPORT COMMAND:
- This command helps to analyze information in the RMAN repository in more detail.

 WHAT IS THE STRUCTURE OF THE DATABASE?
RMAN >REPORT SCHEMA;

 WHICH FILES NEED TO BE BACKED UP?
RMAN >REPORT NEED BACKUP …;

WHICH BACKUPS CAN BE DELETED?
RMAN >REPORT OBSOLETE;

 WHICH FILES ARE NOT RECOVERABLE B’COS OF UNRECOVERABLE OPERATIONS:
RMAN >REPORT UNRECOVERABLE …;

•THE REPORT NEED BACKUP COMMAND :
- This command is used to identify all data files that need a backup. There are three options with this command.

 INCREMENTAL: An integer specifies the maximum number of incremental backups that should be restored during recovery. If this number or more is required then the data file needs a new full backup.

RMAN >REPORT NEED BACKUP incremental 3 database;
This example will report files needing three or more incremental backups for recovery.

 DAYS: An integer specifies max. number of days since the last full or incremental backup of file. The file needs a backup if the most recent backup is equal to or greater than this number.
RMAN >REPORT NEED BACKUP days 3 tablespace system;
To report what system files have not been backed up for three days, use above command.

 REDUNDANCY: An integer specifies the min. level of redundancy considered necessary. For example, redundancy level two requires a backup if there are not two or more backups.
RMAN >REPORT NEED BACKUP redundancy 3;

•RECOVERY MANAGER PACKAGES:
- Two packages named DBMS_RCVCAT AND DBMS_RCVMAN are used by RMAN to perform its tasks.
-DBMS_RCVMAN is created in target database. It queries the control file or recovery catalog.
-DBMS_RCVCAT is used by recovery manager to maintain the information in the recovery catalog.
-DBMS_BACKUP_RESTORE package is created by dbmsbkrs.sql and prvtbkrs.plb script called by catproc.sql. it is used to interface with oracle and os to create,restore and recover bkups of datafiles and archived redo log files.

Hope it helps...I will increase this thread continously as Oracle 11g has lot of new commands.

Happy Oracle learning...

CLONING OF APPLICATIONS IN APPS 11i

2011-09-19T07:29:00.000-07:00

Hi,
Lot of times Apps DBAs have to do the task of Cloning,It is one of the frequently doing task for Apps DBA.Below I'm describing one such task.

Concept of Cloning:
* Cloning is the act of creating an identical copy of an existing Oracle applications system.
* The new system, including component versions, operating system and platform type, is identical to the existing system.

Steps for cloning with E-business suite version 11.5.10.2 on linux are as follows:

Why to clone:
- Create a copy of a production system for testing updates.
- Migrate an existing system to new hardware.
- Create a stage area to reduce patching downtime.

* PREREQUISITE TASKS:

1) PREPARE THE SOOURCE SYSTEM:
LOG ON TO SOURCE SYSTEM AS ORACLE USER, RUN THE FOLLOWING COMMANDS.

a)Preparing source system for dbTier:
$cd /apps11i/proddb/9.2.0/appsutil/scripts/PROD_node1
$perl adpreclone.pl dbTier

b)Preparing source system for applicaton tier (appsTier):

$cd /apps11i/prodcomn/admin/scripts/PROD_node1
$perl adpreclone.pl appsTier

2)COPY THE SOURCE SYSTEM TO THE TARGET SYSTEM:

- Properly shutdown the database from the database terminal i.e. the terminal where database environment is set and shutdown the application from application terminal where application environment is set.
- Copy base directory including all its sub-directory (prodappl, prodcomn, proddb, prodora, proddata) manually using OS command.
- The directory should be same on the target machine as on the source machine for easiness.
- Copy the database configuration file and application configuration file i.e. .db and .app from source to target machine.

3)Edit the .db file, change the machine name i.e. from source to target m/c name.

. /apps11i/proddb/9.2.0/PROD_node2.env
PATH=$PATH:/apps11i/proddb/9.2.0/appsutil/scripts/PROD_node2
PATH=$PATH:/apps11i/prodcomn/admin/scripts/PROD_node2
export PATH

4)Now run the post script for dbtier:

$cd /apps11i/proddb/9.2.0/appsutil/clone/bin
$perl adcfgclone.pl dbTier

and supply the required values prompt by script like:

enter APPS password : apps
virtual hostname for tgt node: y
Tgt hostbane ; node2.server.com
Tgt rac instance: n
Tgt. System databse name: PROD
Tgt. System RDBMS oracle_home dir: choose the default (just press enter)
System util_file accessible dirs list:
/apps11i/proddb/9.2.0/appsutil/outbound/PROD_node2
(how to find this path:
On target machine,
Sho parameter %util% )

No of DATA_TOPS on the target system : 1
Tgt system DATA_TOP: select the default
Do u want to preserve the display set to node1:0.0: yes
Do u want to preserve the port values from the src system on the tgt. System: yes

5)Now edit the .app file i.e. change the source name to target name:

. /apps11i/prodappl/APPSPROD_node2.env
PATH=$PATH:/apps11i/prodcomn/admin/scripts/PROD_node2
PATH=$PATH:/apps11i/proddb/9.2.0/appsutil/scripts/PROD_node2
export PATH

6)Now run the .app file and the environment for application:

$cd /apps11i/prodcomn/clone/bin
$perl adcfgclone.pl appsTier

provide the required values prompt by the script.
Do you want to use the virtual host name for the target node: y
Tgt host name: node2.server.com
Tgt system dabase sid: PROD
Tgt. System database server node: node2.server.com
Tgt. System database domain name: server.com
Does the TGT. System has more then one application Tier: no
Is the tgt. System APPL_TOP devided in to multiple mount points: no
Tgt system APPL_TOP mount point:
/apps11i/prodcomn/
Tgt. System 8.0.6 ORACLE_HOME DIR: choose dfault
Tgt system iAS ORACLE_HOME DIR : choose default\
Preserve the display: yes\
Preserve the port value: yes
Web listner port is : 8000
UTL_FILE_DIR on database tier consist of the following dirs.
1. . /usr/tmp
2. /apps11i/prod2.0/appsutil/util/outbound/PROD_node2
3. /apps11i/proddb/9.2.0/appsutil/outbound/PROD_node2
4. /usr/tmp
Choose a value which will be set as APPLTMP value on the target node.

Important Scripts used for Apps DBA:

-Run the .app profile to set the environment for applications
1) addbctl.sh start  To start the database
2) addlnctl.sh start PROD  To start the corresponding listener
3) adstrtal.sh apps/apps  To start all the application services
4) adstpall.sh stop  To stop all services

Hope it helps.

Happy Apps DBA learning

Proxy user in Oracle

2011-09-13T02:02:00.000-07:00

Proxy user - A proxy user is a user who can assume the identity of other user/users.It is basically created for the application user to access the target user data without knowing its password.

Creating Proxy User:

SQL> create user proxy1 identified by proxy1;

User created.

SQL> alter user target grant connect through proxy1;

User altered.

SQL> select * from dba_proxies;

PROXY CLIENT AUT
------------------------------ ------------------------------ ---
AUTHORIZATION_CONSTRAINT ROLE PROXY_AUT
----------------------------------- ------------------------------ ---------
proxy1 target NO
PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE

Connecting with Proxy User:

C:\WINDOWS\system32>sqlplus sys@TESTDB AS SYSDBA

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 25 11:33:45 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn proxy1[target]
Enter password:
Connected.
SQL> SHOW USER
USER is "target"

Hope it helps...

Happy Oracle learning.

Best regards,

Rafi.

Completed MBA successfully

2011-09-03T00:02:00.000-07:00

Hi,
I completed my MBA in Information Technology from Sikkim Manipal University.This MBA is a second attempt to combine the Business and technology.ITIL v3 completion was the first step.Database management,Financial Accounting,Strategic Management & Business policies,Enterprise Resource planning,Statistics are some of the subjects which I found interesting,rest all were Ok.It was some time difficult to manage job and this study together but some how by almighty's grace and excellent support from people around me,had help me completing this MBA degree on time.Let's hope this can be useful to upgrade my carrier and gives me an idea of understanding business and technology together along with managerial point of view of the technology(Oracle),which I'm working currently.Oracle and MBA seems to be different concepts all together but I believe the main purpose of technology is Business as the final end product.I hope this journey will be more interesting now.

Best regards,

Rafi.

Cloning of ORACLE_HOME in Oracle 11g

2011-08-30T01:55:00.000-07:00

Cloning of ORACLE_HOME in Oracle 11g:

We have to clone ORACLE_HOME for few of the reasons like
1)Whenever a mount point disk space is insufficient.
2)Copy the software from one machine to other withour intstalling.

When we are doing Cloning of the ORACLE_HOME(i.e software) in Oracle11g we need to specify ORACLE_BASE by executing clone.pl perl script below is the experiment to do this ORACLE_HOME cloning for Oracle 10g ORACLE_HOME the steps remain the same only difference is ORACLE_BASE environment variable is not required.We have to request for the downtime of 30 minutes for performing this task because after cloning ORACLE_HOME

we need to verify and change the below things:

1).bash_profile(.profile on solaris,hp/ux,aix) file on linux.

2)/etc/oratab file(/var/opt/oracle/oratab on solaris) on linux.

3)listener.ora file in $TNS_ADMIN($ORACLE_HOME/network/admin) path.

4)expdp(export job script) and RMAN scripts or other scripts using ORACLE_HOME and ORACLE_BASE

Step 1:Collect the environment variable of your current ORACLE_HOME from the .bash_profile (.profile in any other unix environment):

Below are the current settings:

# .bash_profile

export ORACLE_BASE=/u01/oracle11g

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

Step 2:Set the Display environmet variable:

DISPLAY=":0.0" export DISPLAY
(or)
xclock -display machine_name:0.0

Step 3:Now,zipped and extract using tar in a mount point where you have sufficient diskspace:

$ export OLD_ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
$ cd $OLD_ORACLE_HOME/..
$ tar -cvf /u05/TAR_ORACLE_HOME1/OH.tar /u01/oracle11g/product/11.2.0/dbhome_1

Note:
tar -cvf destination_location source_location
Where:Destination_location=u05/TAR_ORACLE_HOME1/OH.tar
Source_location=/u01/oracle11g/product/11.2.0/dbhome_1

Step 4:Destination ORACLE_HOME Creation:

[oracle@testnode1 u03]$ cd /u05
[oracle@testnode1 u05]$ mkdir app
[oracle@testnode1 u05]$ cd app/
[oracle@testnode1 app]$ ls
[oracle@testnode1 app]$ mkdir db11g2
[oracle@testnode1 app]$ cd db11g2/
[oracle@testnode1 db11g2]$ mkdir product
[oracle@testnode1 db11g2]$ cd product/
[oracle@testnode1 product]$ pwd
/u05/app/db11g2/product
[oracle@testnode1 product]$ mkdir 11.2
[oracle@testnode1 product]$ cd 11.2/
[oracle@testnode1 11.2]$ pwd
/u05/app/db11g2/product/11.2

export ORACLE_BASE=/u05/app/db11g2
export ORACLE_HOME=/u05/app/db11g2/product/11.2

[oracle@testnode1 11.2]$ pwd
/u05/app/db11g2/product/11.2
$cd /u05/app/db11g2/product/11.2
$ tar -xvf /u05/TAR_ORACLE_HOME1/OH.tar

=>This will create the ORACLE_HOME as present is /u01 mount point in /u05 mount point
Next,

$export PATH=$ORACLE_HOME/bin:$PATH
$export ORACLE_HOME=/u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1
$echo $ORACLE_HOME
/u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1
$cd /u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1
$perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_HOME=/u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1 ORACLE_BASE=/u05/app/db11g2/product/11.2/u01/oracle11g ORACLE_HOME_NAME=OraDb11g_home2

Result:
--------------------------------------
WARNING:
The following configuration scripts need to be executed as the "root" user.
/u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts

The cloning of OraDb11g_home_2 was successful.
Please check '/u01/oraInventory/logs/cloneActions2011-08-29_04-23-59PM.log' for more details.
[oracle@testnode1 dbhome_1]$
[oracle@testnode1 dbhome_1]$
[oracle@testnode1 dbhome_1]$

Step 5:Execute root.sh script:
Log in as "root"
[oracle@testnode1 dbhome_1]$ su
Password:
[root@testnode1 dbhome_1]# cd /u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1
[root@testnode1 dbhome_1]#pwd
/u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1
[root@testnode1 dbhome_1]# sh root.sh
Check /u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1/install/root_testnode1.in.bosch.com_2011-08-29_16-32-46.log
for the output of root script

Step 6:Copy the spfiles and password files:

Copy the spfiles and password files of Databases from previous $ORACLE_HOME/dbs( $ORACLE_HOME/database in windows)
to new $ORACLE_HOME/dbs path

Step 7:Post ORACLE_HOME clonning steps are as given below:

1)Modify .bash_profile file:

If we have created separate profile for each databases environment than we have update there and
check we are able to login the Database successfully

vi .bash_profile

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u05/app/db11g2/product/11.2/u01/oracle11g
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export sq='sqlplus "/as sysdba"'
export ORACLE_SID=TESTDB1

:wq =>save and quit.

2)Modify /etc/oratab file.

Modify the /etc/oratab entry in linux with the neW ORACLE_HOME path

# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
#TESTDB2:/u01/oracle11g/product/11.2.0/dbhome_1:Y
#TESTDB1:/u01/oracle11g/product/11.2.0/dbhome_1:Y

#New ORACLE_HOME
TESTDB2:/u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1:Y
TESTDB1:/u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1:Y

3)Modify listener.ora file

If we are not able to connect from other machine or client machine after ORACLE_HOME cloning don't get shocked as we have to update listener.ora with the new ORACLE_HOME path.Before modifying listener.ora file create a backup file
if it does not exists.Now you cand set the new ORACLE_HOME path here as given below.

[oracle@testnode1 oracle11g]$ cd $TNS_ADMIN
[oracle@testnode1 admin]$ pwd
/u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1/network/admin
[oracle@testnode1 admin]$ ls -altr
-rw-r--r-- 1 oracle dba 431 May 13 15:36 sqlnet.ora
-rw-r--r-- 1 oracle dba 959 Aug 29 17:15 tnsnames.ora
-rw-r--r-- 1 oracle dba 405 Aug 29 19:32 listener.ora_bkp29082011
-rw-r--r-- 1 oracle dba 461 Aug 29 19:34 listener.ora
drwxr-xr-x 3 oracle dba 4096 Aug 30 11:04 .
[oracle@testnode1 admin]$ vi listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =testnode1)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = TESTDB1)
(ORACLE_HOME = /u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1)
)
(SID_DESC =
(SID_NAME = TESTDB2)
(ORACLE_HOME = /u05/app/db11g2/product/11.2/u01/oracle11g/product/11.2.0/dbhome_1)
)
)

:wq =>save and quit.

4)Modify expdp(export job script) and RMAN scripts or other scripts using ORACLE_HOME and ORACLE_BASE for their executions.
If we don't modify the Backup scripts or other scripts using ORACLE_HOME and ORACLE_BASE than next morning we might see backups or jobs getting failed so please make sure you modify the ORACLE_HOME and ORACLE_BASE.

Now,we can test ourself in our local system whether we are able to connect to the Databases with the new ORACLE_HOME and than request the end users to use the system.Thus our task can be successfully completed.

Hope it helps.

Enjoy DBA tasks.

Best regards,

Rafi.

Compiling JSP files automatically and manually in R12

2011-08-24T22:25:00.000-07:00

Hi,
In R12 Application we can compile JSP files
1)Automatically or
2)Manually
Below are some useful steps to do the compilation of JSP files:

1)Automatic JSP files compilation:

Command to complie jsp files automatically has some changes compared to 11i (See metalink notes 458338.1):

2)Manual JSP files compilation:
Preparatory Step: Set the Oracle Application environment.
# su - applmgr
$ cd /oraAPP/apps/apps_st
$TEST_node1.env

We can follow below steps to compile a single jsp file (seeded or custom) in Oracle Applications 11i/R12.

1)Copy the jsp file to $COMMON_TOP/webapps/oacore/html (this location translates to $OA_HTML):

Note:We can use 'winscp' to copy the jsp file from windows to linux in a frequent manner each time.

Placing the file under $OA_HTML enables end users to access the file using the url http://hostname.domain:port/OA_HTML/[jspfile.jsp]

2)Give ownership and permission for 'applmgr'(Application file system owner) to compile jsp file.

#chown applmgr:dba /oraAPP/apps/apps_st/comn/webapps/oacore/html/test1.jsp
#chmod -R 755 /oraAPP/apps/apps_st/comn/webapps/oacore/html/test1.jsp

3) Flush and Compile the jsp file using 'applmgr' user:
# su - applmgr
$ cd $OAH_TOP/html =>location of all jsp files

-bash-3.2$ /oraAPP/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/ojspCompile.pl --compile --flush -p 2
logfile set: /oraAPP/inst/apps/VIS_kor300949v20ebs/logs/appl/rgf/ojsp/ojspc_error.log
starting...(compiling all)
using 10i internal ojsp ver: 10
synchronizing dependency file:
loading deplist...8052
enumerating jsps...8054
updating dependency...2
parsing jsp...2
writing deplist...8053
initializing compilation:
eliminating children...5985 (-2069)
translating and compiling:
translating jsps...5985/5985 in 6m51s
compiling jsps...5985/5985 [failed: 1] in 17m11s
Finished!

Note:
Linux/Unix:
cd $FND_TOP/patch/115/bin
perl ojspCompile.pl --compile --flush -p 2

Windows:
cd %FND_TOP%\patch\115\bin
perl -x ojspCompile.pl –compile –flush

4)Restart the Middle Tier(applications) services:

For Stopping the Middle-Tier services in R12:

-bash-3.2$ cd $ADMIN_SCRIPTS_HOME
-bash-3.2$ ./adstpall.sh
Enter the APPS username: apps

Enter the APPS password:
The logfile for this session is located at /oraAPP/inst/apps/TESTDB_TESTNODEebs/logs/appl/admin/log/adstpall.log

For Starting the Middle-Tier Services in R12:

-bash-3.2$ ./adstrtal.sh

You are running adstrtal.sh version 120.15

Enter the APPS username: apps

Enter the APPS password:
The logfile for this session is located at /oraAPP/inst/apps/TESTDB_TESTNODEebs/logs/appl/admin/log/adstrtal.log

Once the Middle-Tier services are started successfully,We have to check the URL for JSP file.

5)Verify the URL:

The URL format will be as given below:
http://hostname.domain:port/OA_HTML/[jspfile.jsp]

Hope it helps...

Happy Apps DBA learning.

Best regards,

Rafi.

Setting the Oracle Environment for various Oracle Database versions

2011-08-20T12:15:00.000-07:00

Hi,
When in we install different Oracle Software in our machine.It is very important we set the correct PATH and ORACLE_HOME environment variables in order to connect them always.Below is one such way which will be helpful.

Error messages:

C:\Windows\system32>sqlplus
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Pre-requistic step:

On Windows OS:

Delete the environment variable 'PATH' by going to computer=>properties=>Advanced
system properties=>Advanced=>Environment variable=>path and delete this Click OK.

On linux OS:
We have to remove the 'PATH' variable from .bash_profile or we can set separate .bash_profile for each Oracle Database versions and we have to use export in place of set command.

C:\>set ORACLE_HOME=
C:\>set PATH=%ORACLE_HOME%\bin;%PATH%
C:\>set ORACLE_SID=
C:\>sqlplus "/ as sysdba"

Oracle 11g:

Pre-requistics step:Make sure you started the services for Oracle 11g listener
and Database in your machine(services.msc at run prompt and start Oracle 11g listener and Database services).

set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=ORCL
sqlplus "/as sysdba"

Oracle 10g:

Pre-requistics step: Make sure you are started services for Oracle 10g listener and Database in your machine.

set ORACLE_HOME=D:\oracle\product\10.2.0\db_1
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=TESTDB
sqlplus "/as sysdba"

Hope it helps.

Best regards,

Rafi.

Apex Deployment on Weblogic Server

2011-08-18T03:47:00.000-07:00

Hi,
I started my Oracle fusion middleware journey with some good experiments.
Below are the steps which I performed for deploying Apex on Weblogic Server.
(Installation and configuration of Weblogic Server I will cover in separate post and Apex installation and configuration you can get from Apex_tasks Label link)

Apex can be integrated with weblogic by follwing below experiment:

I)
Case study:

WebLogic Server and APEX:
When a WebLogic Server (WLS) is the primary Web server, accessing APEX pages though the WLS requires a proxy. The configuration of APEX generally follows one of two configurations – Apache with mod_plsql or the Embedded PL/SQL gateway. When WebLogic (without Apache) is the main HTTP server, getting APEX to surface on the same port as WebLogic requires some form of proxy.

Overall Approach:
This solution creates a very simple Web Application that invokes a Java Proxy Servlet when a user tries to access APEX pages. Wrapping this Web Application around the Java Proxy Servlet lets the WLS serve APEX without any port conflicts. The WLS Proxy Servlet is a fully documented out of the box tool.
To create and deploy the Web Application simply build the files outlined in this document, deploy the application and then access APEX.

Exploded deployment:

For convenience, this solution takes advantage of the exploded deployment feature of the WLS. In addition to the ability to deploy jar, war and ear files, the WLS can deploy Web Applications as an exploded directory that contains the same contents of the archive file. An exploded archive directory contains the same files and directories as a jar archive. However, the files and directories reside directly in the file system instead of as a single archive file (JAR). This example uses the exploded deployment style to create the Web Application for this example.
Default WebLogic application
The default Web Application is what the server sends to browser clients who do not specify a recognized URI (or specify only “/” as the URI). Each server has only one default Web Application and for this solution to work, this application must be set as the default. If there is already a default, this servlet could be added to the existing application by using an exploded deployment of the default with modification to the web.xml to register the APEX proxy.

II)

Pre-requisites:

1)An Oracle database successfully serving APEX pages. The APEX instance may be on the same or different machine and served from either Apache or the Embedded PL/SQL gateway. In this example, APEX uses the xdb feature of Database which is included in Oracle 11g Database and database is running on the same machine as the WebLogic server and natively appears at the http://10.47.33.10:8080/apex URL.
Database side task:
Open the Apex http port from the Database side
SQL> begin
dbms_xdb.sethttpport('8080');
end;
/

PL/SQL procedure successfully completed.

SQL> select dbms_xdb.gethttpport as "HTTP-Port"
from dual;

HTTP-Port
----------
8080

2) An Oracle WebLogic Server 10.3(11g) running a Node Manger, the Administration Console and a Managed server. This example uses a domain created specifically for this exercise named APEXDemo. The WLS Administration console uses port 7001 and the SOA Managed Server uses port 8001.We are using SOA Managed Server for Deploying Apex

3) There is no other “default” WebLogic application for the Managed Server.

III)Experiment:

Create the APEX Proxy Servlet
1)Create the following directory structure somewhere on disk. This experment assumes the / root disk is used. Note: the apexproxy.war directory name mimics the normal J2EE naming convention for Web Application archive (WAR).

Create the directory structure as given below in linux OS:

#mkdir APEX_PROXY
#cd /APEX_PROXY
#mkdir apexproxy.war
#cd /APEX_PROXY/apexproxy.war

Rest of the steps with screen shots very well documented in the below link:

http://christopherbeck.wordpress.com/2008/09/15/weblogic-server-and-apex/

Thanks Christopherbeck.

Enjoy Oracle Fusion Middleware learning

Best regards,

Rafi.

Apex listener starting issue

2011-08-16T03:28:00.000-07:00

Hi,
Usually we start the apex listener as given below,But when you see the error messages as given below don't get shock as it is because the apex war file is not able to pick the right jdk version(jdk6) while starting apex listener.

Error message:-bash-3.2$ java -Dapex.images=/apex_4.0.2/apex/images -Dapex.port=8080 -jar apex.war
INFO: Starting: /apex_4.0.2/apex.war
See: 'java -jar apex.war --help' for full range of configuration options
INFO: Extracting to: /tmp/apex
SEVERE: method java.lang.System.console with signature ()Ljava.io.Console; was not found.
java.lang.NoSuchMethodError: method java.lang.System.console with signature ()Ljava.io.Console; was not found.
at ____embedded.____EntryPoint.apexImages(____EntryPoint.java:104)
at ____embedded.____EntryPoint.main(____EntryPoint.java:91)

Solution: Installed the jdk6 update version latest one and execute the .war file directly as given below.

# rpm -Uvh jdk-6u26-linux-i586-rpm.bin

Go to the path where we unzip the apex_listener.zip file:

In an EBS R12 environment don't set the environment for oracle or apps and execute the 'apex.war' file with 'oracle' user.

-bash-3.2$ ./apex.war
INFO: Starting: /apex_listener_status/apex.war
See: 'java -jar apex.war --help' for full range of configuration options
INFO: Extracting to: /tmp/apex
Enter the path to the directory containing the APEX static resources
Example: /Users/myuser/apex/images
or press Enter to skip: /apex_4.0.2/apex/images/
INFO: Using classpath: file:/tmp/apex/apex/____embedded/start.jar:file:/tmp/apex/apex/WEB-INF/lib/xmlparserv2-11.2.0.jar:file:/tmp/apex/apex/WEB-INF/lib/ojmisc.jar:file:/tmp/apex/apex/WEB-INF/lib/commons-fileupload-1.2.1.jar:file:/tmp/apex/apex/WEB-INF/lib/poi-3.6-20091214.jar:file:/tmp/apex/apex/WEB-INF/lib/ucp.jar:file:/tmp/apex/apex/WEB-INF/lib/xdb-11.2.0.jar:file:/tmp/apex/apex/WEB-INF/lib/ojdbc6.jar:file:/tmp/apex/apex/WEB-INF/lib/apex.jar:file:/tmp/apex/apex/WEB-INF/lib/je-4.0.103.jar:
INFO: Starting Embedded Web Container in: /tmp/apex
Enter a username for the APEX Listener Administrator [adminlistener]: admin
Enter a password for admin:
Confirm password for admin:
Enter a username for the APEX Listener Manager [managerlistener]: apex
Enter a password for apex:
Confirm password for apex:
Aug 17, 2011 3:22:06 AM ____bootstrap.Deployer deploy
INFO: Will deploy application path=/tmp/apex/apex/WEB-INF/web.xml
Aug 17, 2011 3:22:10 AM ____bootstrap.Deployer deploy
INFO: deployed application path=/tmp/apex/apex/WEB-INF/web.xml
Using config file: /tmp/apex/apex-config.xml

In Database side:

alter user apex_public_user identified by apex_public_user;

Listener configuration:
URL: http://:/apex/listenerConfigure

where:host=ipadrress/machine name,port:8080(default)

Now,
We are ready to configure the apex listener and carry the remaining workspace administration tasks or leave to the apex team to do rest of the task.

Happy APEX learning.

Best regards,

Rafi.

Step by step Oracle SOA suite 11g installation on RHEL 5.3

2011-08-09T07:03:00.000-07:00

Hi,
Below are the lists of steps I have followed for installing Oracle SOA Suite 11g,I have not put screenshots but tried to put explain without screenshots.

Oracle SOA Suite 11g Installation plan:

Downloads:
1)DATABASE :11.2.0.1.0(x86 32-bit LINUX):
2)WEBLOGICSERVER
3)RCU(REPOSITORY CREATION UTILITY
4) SOA
5) JDEVELOPER
6)SOA EXTENSION

Preparatory step:

[oracle@fmwtestnode /]$ mkdir Web_logic_soft
[oracle@fmwtestnode /]$ su
Password:
[root@fmwtestnode /]# mkdir Oracle11gR2soft
[root@fmwtestnode /]# chown oracle:oinstall /Oracle11gR2soft/
[root@fmwtestnode /]# chmod -R 755 /Oracle11gR2soft/
[root@fmwtestnode /]# mkdir Web_logic_soft
[root@fmwtestnode /]# mkdir Jdev_soft
[root@fmwtestnode /]# mkdir SOA_SUITE_11g_soft
[root@fmwtestnode /]# chown -R oracle:oinstall /Web_logic_soft/
[root@fmwtestnode /]# mkdir Reposit_create_soft
[root@fmwtestnode /]# chown -R oracle:oinstall /Jdev_soft/
[root@fmwtestnode /]# chown -R oracle:oinstall /SOA_SUITE_11g_soft/
[root@fmwtestnode /]# chown -R oracle:oinstall Reposit_create_soft/
[root@fmwtestnode /]# chmod -R 755 /Oracle10gsoft/
[root@fmwtestnode /]# chmod -R 755 /Web_logic_soft/
[root@fmwtestnode /]# chmod -R 755 /Reposit_create_soft/
[root@fmwtestnode /]# chmod -R 755 /Jdev_soft/
[root@fmwtestnode /]# chmod -R 755 /SOA_SUITE_11g_soft/

Check your browser:
Enterprise Manager requires Firefox 3, Safari 4+ or IE 7+.
BAM requires IE 7 or 8

Check your JDK:
The WebLogic server installation exe is packaged with a JDK. The JDeveloper exe also comes with a JDK.
If you are going to install WebLogic server and JDeveloper on the same machine,we use the JDK from WebLogic for JDeveloper too. However, if you are going to install on separate machines, you need to have Oracle Sun Java 1.6 update 21+ JDK for JDeveloper. You can get it from the Oracle Sun downloads page. Be sure to get the JDK not the JRE: http://java.sun.com/products/archive/

Installations:

1)Install the database:
Check my previous post for Oracle Database 11g rel2 installation on linux.

2) Install WebLogic server:

Weblogic pre-installation steps:
Check the Certification matrix to check the supported Weblogic server for
Various Operating system.For our operating system RHEL 5.3 the supported
Weblogic server software is 10.3.

SQL> select name from v$database;

NAME
---------
FMWDB

SQL> select status from v$instance;

STATUS
------------
OPEN
SQL> show parameter processes
SQL> alter system set processes=1000 scope=spfile;
SQL> shutdown immediate
SQL> startup
SQL> show parameter processes

[root@fmwtestnode /]# mkdir -p Middleware/SOASuite11gR1
[root@fmwtestnode /]# chown oracle:oinstall /Middleware/SOASuite11gR1/
[root@fmwtestnode /]# chmod -R 755 /Middleware/SOASuite11gR1/

We have to create Middleware home directory for installation FUSION MIDDLEWARE COMPONENTS in this particular by granting the ownership to the ‘oracle’ user with all the required privileges

1)DATABASE
2)WEBLOGIC SERVER
3)RCU
4)SOA
5)JDeveloper
6)SOA Extension

Once your database is ready, we can install the next step, the WebLogic server.
When you are ready to install, complete the following:

I granted all the ownership to the oracle user and permission where the executable of weblogic software is present.I launched the installation on vm server as follows:

Here ,I have entered the Fusion Middlware Home path which has all the ownership and permission so that ‘oracle’ user can install weblogic server here.

Choose 'Graphical' method to install Weblogic server to have pictorial representation:

Press ‘Enter’ and proceed……..

Press ‘Enter’……..

Make uninformed to ‘yes’ as shown below in screenshot and proceed:

Select Typical, click Next

Press ‘Enter’ and proceed……..

Here,As we see above Along with Weblogic platform 10.3.5.0,JDKs and other JDBC ,weblogic server plugins and other products get installed…

Press ‘Enter’ proceed…….

That’s it.Weblogic installation is proceeding as see in the above screen shot…………..

First JDK files are installed,OCM,domains…….

As we see from the screenshot Weblogic installation Completed successfully….
Press ‘Enter’ and it is done……….

Once the Weblogic installation is done we need to Configure Domain,And we have to make sure weblogic server is up & running.

What is a weblogic server domain?>
A domain is the basic administration unit for Weblogic Server instances. A domain consists of an Administration Server and Managed application servers and resources. The main configuration file in the domain is config.xml, which is in the domain directory(where you install your weblogic server domain) under config directory.
We can use Administration Server to do the configuration tasks. A web based console comes with the domain installation, where an administrator can login and perform the administration tasks.
Weblogic domain can have multiple servers in a cluster or multiple clusters. A domain can have an Administration Server which can act as an Administration Server and managed server. This architecture is mostly used by the developers in developing and deploying applications in there local environments.
Production and Development Modes

Weblogic domain can be configured in two different modes:
1.Development Mode
2.Production Mode

1. Development Mode: In the development mode of configuration,Weblogic server domain has minimum security and is used for the development of application purposes. In this mode, you can also auto-deploy the application.
2. Productions Mode: This is the final mode of the domain configuration, where you deploy the application which will be directly used by the customers. This is a very important environment, with maximum security.
Now, as we got a general idea on what a weblogic server domain is, we will now configure the weblogic server domain on Linux operating system can be done as follows:

Step 1: Go to /Middleware/SOASuite11gR1/wlserver_10.3/common/bin and look for file name "config.sh".

[oracle@fmwtestnode bin]$ cd /Middleware/SOASuite11gR1/wlserver_10.3/common/bin
[oracle@fmwtestnode bin]$ ls -altr
total 128
-rwxr-x--- 1 oracle oinstall 13740 Jul 28 11:10 wlsifconfig.sh
drwxr-x--- 11 oracle oinstall 4096 Jul 28 11:10 ..
-rwxr-x--- 1 oracle oinstall 761 Jul 28 11:11 wlst.sh
-rwxr-x--- 1 oracle oinstall 31126 Jul 28 11:11 wlscontrol.sh
-rwxr-x--- 1 oracle oinstall 3298 Jul 28 11:11 upgrade.sh
-rwxr-x--- 1 oracle oinstall 2087 Jul 28 11:11 unpack.sh
-rwxr-x--- 1 oracle oinstall 1448 Jul 28 11:11 stopDerby.sh
-rwxr-x--- 1 oracle oinstall 4082 Jul 28 11:11 startDerby.sh
-rwxr-x--- 1 oracle oinstall 2579 Jul 28 11:11 pack.sh
-rwxr-x--- 1 oracle oinstall 2274 Jul 28 11:11 config.sh
-rwxr-x--- 1 oracle oinstall 2122 Jul 28 11:11 config_builder.sh
-rwxr-x--- 1 oracle oinstall 22511 Jul 28 11:11 commEnv.sh
-rwxr-x--- 1 oracle oinstall 2746 Jul 28 11:11 setPatchEnv.sh
-rwxr-x--- 1 oracle oinstall 4156 Jul 28 16:36 startManagedWebLogic.sh
drwxr-xr-x 3 oracle oinstall 4096 Jul 28 16:36 servers

Step 2: Run the 'config.sh' file for the domain configuration.
‘Config.sh’ will do the Domain Configure.There are 3 ways you can do this configuration
1)Graphical
2)Console
3)Silent
We are using ‘Graphical’ method as it display all the information to our screen…
For doing this we need to set the ‘DISPLAY’ as follows:
Check ‘xclock’ command is working and showing the current time.If it shows than we just need to export ‘DISPLAY’ environment Variable.

$xclock
$ export DISPLAY
$./config.sh

Proceed with the configuration,supply 'weblogic' user with credential.Choose
Development or Production depending on our requirement and obtain the
final URL.After completion of Domain configuration,we will obtain:

Domain Location:/Middleware/SOASuite11gR1/user_projects/domain/base_domain
Admin Server URL:http://fmwtestnode.apac.xyz.com:7001/console

3)Install database schema using RCU (REPOSITORY CREATING UTILITY)

Useful pre-requistics steps:

[oracle@fmwtestnode admin]$ hostname
fmwtestnode.apac.bosch.com
[oracle@fmwtestnode admin]$ tnsping fmwdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:23

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/app//product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fmwdb.apac.bosch.com)))
TNS-12541: TNS:no listener
[oracle@fmwtestnode admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:30

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /u01/app//product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app//product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/diag/tnslsnr/fmwtestnode/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fmwtestnode.apac.bosch.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 29-JUL-2011 18:26:32
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app//product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/diag/tnslsnr/fmwtestnode/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fmwtestnode.apac.bosch.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@fmwtestnode admin]$ tnsping fmwdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:40

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/app//product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fmwdb.apac.bosch.com)))
OK (30 msec)

2) Please do below step in Database side,If you are installing all the Oracle Fusion Middleware products,For SOA installation alone setting open_cursors=300,but as we are installating all the Oracle Fusion middleware products we need to set open_cursors=1000.

Conn “/as sysdba” in Database.

SQL> alter system set open_cursors=1000;

System altered.

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 1000

Now it is time to set up the database schema.
Note: If we have installed SOA 11g before and you are not upgrading, you must drop your existing database schema before reinstalling or create a second schema for this installation. We cannot reuse an existing schema.

Configure Schema using RCU
Now create the new schema.

Give the ownership and permission to ‘oracle’ user to run the repository creation utility.

[root@fmwtestnode /]# chown oracle:oinstall /Reposit_create_soft/
[root@fmwtestnode /]# chmod -R 755 /Reposit_create_soft/

[root@fmwtestnode Reposit_create_soft]# su - oracle
[oracle@fmwtestnode ~]$ pwd
/home/oracle
[oracle@fmwtestnode ~]$ cd /Reposit_create_soft/
[oracle@fmwtestnode Reposit_create_soft]$ ls -altr
total 389240
-rwxr-xr-x 1 root root 398173821 Jul 26 18:53 ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 2 oracle oinstall 4096 Jul 27 10:36 .
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
[oracle@fmwtestnode Reposit_create_soft]$ unzip ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip

[oracle@fmwtestnode /]$ cd Reposit_create_soft/
[oracle@fmwtestnode Reposit_create_soft]$ ls -altr
total 389244
drwxr-xr-x 32 oracle oinstall 4096 Apr 26 05:04 rcuHome
-rwxr-xr-x 1 root root 398173821 Jul 26 18:53 ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
drwxr-xr-x 3 oracle oinstall 4096 Jul 28 11:41 .
[oracle@fmwtestnode Reposit_create_soft]$ cd rcuHome/
[oracle@fmwtestnode rcuHome]$ ls
assistants common inventory jdk lib nls oraInst.loc plsql rcu relnotes sqlplus xdk
BC4J css javavm jlib modules oracleRoot.sh owm portal rdbms root.sh srvm
bin instantclient jdbc ldap network oracore perl precomp readme.htm sqlj util
[oracle@fmwtestnode rcuHome]$ cd bin

Go to path ‘/Reposit_create_soft_/rcuHome/bin’ as seen in the screenshot.

$ export Display

On Linux(UNIX):
./rcu

Give all the permission to the ‘rcu’ file to launch the installation:

Note: During installation, give the details of the Oracle database created in step 2, select all the SOA schemas to install and select default settings for all other options during the installtion.

Login as ‘root’ user:
#xhost +
To set display correctly and for avoiding java related errors:

$./rcu =>For installing repository Creation Utility.

On the welcome screen, click Next
Select Create

Click Next
Enter the database information
Before that:
[oracle@fmwtestnode admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 29 18:19:44 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user sys identified by fmwsysdba;

User altered.

Enter the information as above.

Click Next

When complete, click OK. The utility moves to the next page – with a slight delay, just wait for it.

On the Select Components screen, enter DEV in the field for creating a new prefix.
Select the component SOA,DATA INTEGRATOR and BPM Infrastructure and all the components for our Oracle Fusion Middleware setup..
Dependent schemas are selected automatically.

Click Next
The pre-requisites for this step are checked.

Please do below step in Database side and click ok:

Conn “/as sysdba” in Database.

SQL> alter system set open_cursors=1000;

System altered.

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 1000

When completed, click OK

Select the radio button to Use the same password for all schemas.
Enter a schema password. The password welcome123 is assumed in this document, but we should choose your own secure password or a different one for each schema and be sure to record your passwords as you will need them later

Click Next

Accepting the defaults, click Next

When the pre-requisites for this step are completed, click OK

Click Create to create the tables. This takes about 60 minutes,as we are creating the repository
For all the fusion middleware products. ‘Portal’ repository creation take more than 30 minutes.

I took screenshots of this step:

COMPLETION SUMMARY :

When completed, click Close
Our Repository Creation for All fusion Middleware product is Completed…

4)Install SOA Suite 11g:

1. Download the software from here:
http://download.oracle.com/otn/nt/middleware/11g/ofm_soa_generic_11.1.1.5.0_disk1_1of1.zip
Also present in our shard drive:
\\bmh2fs01\bmhapp01_projects\Ebs1_Vss\DB_services\FMW\FMW_SOFTWARES\ORACLE_SOA_SUITE_INSTALL_SOFT

Once the schema has been created, you are ready to install the SOA server.
Login as ‘root’ user:

#xserver +
#xclock

Perform the following:
unzip ofm_soa_generic_11.1.1.5.0_disk1_1of2.zip

[root@fmwtestnode /]# cd SOA_SUITE_11g_soft/
[root@fmwtestnode SOA_SUITE_11g_soft]# unzip ofm_soa_generic_11.1.1.5.0_disk1_2of2.zip
[root@fmwtestnode SOA_SUITE_11g_soft]# ls
Disk1 Disk2 Disk3 Disk4 Disk5

Give 'Oracle' user ownership and permission to install Oracle SOA Suite software.
root@fmwtestnode /]#chown Oracle:ointall /SOA_SUITE_11g_soft/Disk*
Login as ‘oracle’ user and do the Oracle SOA software installation:

[oracle@fmwtestnode Disk1]# cd /SOA_SUITE_11g_soft/Disk1/
[oracle@fmwtestnode Disk1]# ls -altr
total 108
-rwxrwxr-x 1 root root 73728 Aug 21 2009 setup.exe
-rwxrwxr-x 1 root root 11460 Oct 28 2009 runInstaller
drwxrwxr-x 10 root root 4096 Nov 4 2009 install
drwxr-xr-x 5 root root 4096 Nov 4 2009 .
drwxrwxr-x 9 root root 4096 Nov 4 2009 doc
drwxr-xr-x 13 root root 4096 Nov 4 2009 stage
drwxr-xr-x 5 oracle oinstall 4096 Aug 1 13:59 ..

Login as ‘root’ user:
#xhost +
To set display correctly and for avoiding java related errors:

$./runInstaller -jreLoc /Middleware/SOASuite11gR1/jdk160_24

When the install wizard welcome screen comes up, click Next

Now ,All Prerequistics checks are passed…With no errors……..

Click Next

On the Specify Installation Location screen, select the Middleware home:
/Middleware/SOASuite11gR1/
Accept the default Oracle home: Oracle_SOA1

Click Next

I created one response file to see the installation flow,Before going for install.

Click Install

Thats it SOA Installation started Successfully,

click Next…
Very much happy to see below screen.Our Oracle SOA suite 11g installation finished successfully.

Click Finish………

Create the SOA domain:

1. Perform the following:
$cd /Middleware/SOASuite11gR1/Oracle_SOA1/common/bin
$./config.sh

Choose Extend to existing Weblogic domain
Click 'Next'

Select Generate a domain configured automatically to support the following products:
Check all to select all the products…
Domains

Click 'Next'

Name:weblogic
Pass:weblogic123

Click ‘Next’

Select the JRocket SDK as it will yield beter performance for Oracle SOA and leave Development Mode checked

Check 'Oracle SOA Suite - 11.1.5.0 [Oracle_SOA1]'
Check 'Oracle Enterprise Manager - 11.1.1.0 [oracle_common]'
Check 'Oracle Business Activity Monitoring - 11.1.5.0 [Oracle_SOA1]'
Click 'Next'
Click 'Next'
User password: welcome123
Confirm user password: welcome123
Click 'Next'
Click 'Next'
Check 'BAM Schema', 'SOA Infrastructure', 'User Messaging Service', 'OWSM MDS Schema', 'SOA MDS Schema'
DBMS/Service: orcl.ipnweb.com
Host Name: oradev.ipnweb.com
Schema Password: welcome1
Click 'Next'
Click 'Next'
Click 'Next'

Click 'Create'

If all are successful, click Next, otherwise click Previous and correct any errors.

Now you must choose whether to have your server run as a managed server or in a single server configuration.
As we have chosen the developer’s configuration, there will be one admin server and one managed server for BAM.
Don’t check any of the boxes here and click Next, unless you want to make changes to the default configuration

Click Next

This is the summary page for the Configuration. Click Extend to extend to the domain.
Wait for the create to finish – takes just a minute

Check the box to start the admin server and Click Done.

Add the machine entry:
We have to add the host entry in windows 'hosts' file.

Internet explorer settings:
Go to Tools=>Internet Options=>Connections=>LAN Settings and Uncheck Use automatic configuration script =>click ok =>click ok

Now access the Admin Server URL:

Admin Server URL:
http://fmwtestnode.apac.abc.com:7001

Note:We have to use compatible version of Oracle Repository creation utility and Oracle SOA i.e If rcu is 11.1.1.5.0 than use SOA also with the same version
11.1.1.5.0

Happy Oracle SOA learning...

Best regards,

Rafi.

Integrating APEX 4.0.2 with E-business Suite Release 12.1.1

2011-08-09T06:43:00.000-07:00

Hi,
Apex can be integrated with E-business Suite release R12.In the below example my Database vesion is 11.1.0.7.0,I'm using APEX LISTENER for doing this task which is very secure and convenient way.Below are the steps I used for this purpose.

Step 1:Create one Tablespace for holding the Apex users Data

create tablespace APEX402 datafile '/oraDB/db/apps_st/data/apex402.dbf' size 1G autoextend on;

Step 2:Upgrade Apex to 4.0.2

E-business suite Database 11.1.0.7.0 comes with Apex 3.0.2 installed,For upgrading to the latest apex component follow the below steps:

For upgrading apex 4.0.2:
----------------------------

Go to apex 4.0.2 path:

sqlplus "/as sysdba"
@apexins.sql apex402 apex402 temp /i/

Message while upgrading coming to end:

Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the APEX_040000 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

Message while validating APEX componnent:

.Compiled 676 out of 2720 objects considered, 0 failed compilation 05:22:43
...234 packages
...227 package bodies
...426 tables
...12 functions
...19 procedures
...3 sequences
...439 triggers
...1177 indexes
...175 views
...0 libraries
...4 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 05:22:43
...Completed key object existence check 05:22:43
...Setting DBMS Registry 05:22:43
...Setting DBMS Registry Complete 05:22:43
...Exiting validate 05:22:43
timing for: Validate Installation
Elapsed: 00:44:26.45
timing for: Development Installation
Elapsed: 01:06:23.02
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

JOB_QUEUE_PROCESSES: 2

Check after validation in Database:

SQL> SELECT COMP_NAME||' '||VERSION||' '||STATUS FROM DBA_REGISTRY;

COMP_NAME||''||VERSION||''||STATUS
--------------------------------------------------------------------------------
Oracle Application Express 4.0.2.00.07 VALID
Oracle Ultra Search 11.1.0.7.0 VALID
Oracle Label Security 10.2.0.3.0 OPTION OFF
Oracle XML Database 11.1.0.7.0 VALID
Oracle Application Server Syndication Services 10.1.2.0.2 VALID
Oracle Application Server Single Sign-On 10.1.2.0.2 VALID
Oracle Workflow 10.1.2.0.2 VALID
Oracle Data Mining 11.1.0.7.0 VALID
OLAP Catalog 11.1.0.7.0 VALID
Oracle Text 11.1.0.7.0 VALID
Spatial 11.1.0.7.0 VALID

COMP_NAME||''||VERSION||''||STATUS
--------------------------------------------------------------------------------
Oracle Multimedia 11.1.0.7.0 VALID
Oracle Database Catalog Views 11.1.0.7.0 VALID
Oracle Database Packages and Types 11.1.0.7.0 VALID
JServer JAVA Virtual Machine 11.1.0.7.0 VALID
Oracle Database Java Packages 11.1.0.7.0 VALID
Oracle XDK 11.1.0.7.0 VALID
Oracle Real Application Clusters 11.1.0.7.0 INVALID
OLAP Analytic Workspace 11.1.0.7.0 VALID
Oracle OLAP API 11.1.0.7.0 VALID
Oracle Application Server Integration B2B 10.1.2.0.2 VALID
Oracle Application Server Integration BAM 10.1.2.0.2 VALID

COMP_NAME||''||VERSION||''||STATUS
--------------------------------------------------------------------------------
Oracle Application Server Certificate Authority 10.1.2.0.2 VALID
Oracle Internet Directory 10.1.2.0.2 VALID
Oracle Application Server Metadata Repository Version-R 10.1.2.0.2 VALID
Oracle Application Server Distributed Configuration Management 10.1.2.0.2 VALID
Oracle Business Intelligence Discoverer 10.1.2.0.2 VALID
Oracle Application Server UDDI Registry 10.1.2.0.2 VALID
Oracle Application Server Web Clipping 10.1.2.0.2 VALID
Oracle Application Server Wireless 10.1.2.0.2 VALID

30 rows selected.
Now,We have successfully upgraded APEX to 4.0.2.

Step 3:Start the http services using the ‘root’ user

#/etc/init.d/httpd start
[root@testnode1ebs apex]# /etc/init.d/httpd status
httpd (pid 13319 13318 13317 13316 13315 13314 13313 13312 13310) is running...

Step 4: Open the Apex http port from the Database side
SQL> begin
dbms_xdb.sethttpport('8080');
end;
/

PL/SQL procedure successfully completed.

SQL> select dbms_xdb.gethttpport as "HTTP-Port"
from dual;

HTTP-Port
----------
8080

Step 5:Download JDK6 from oracle and unzip it in the E-business suite server.

Step 6:Download and Install the Apex Listener

Launch Oracle Application Express Listener in standalone mode:

1. Download and unzip Oracle Application Express Listener.

2. Execute the following command:

bash-3.2$ java -jar /apex_4.0.2/apex.war
INFO: Starting: /apex_4.0.2/apex.war
See: 'java -jar apex.war --help' for full range of configuration options
INFO: Extracting to: /tmp/apex
Enter the path to the directory containing the APEX static resources
Example: /Users/myuser/apex/images
or press Enter to skip: /oraAPP/apps/tech_st/10.1.3/images
INFO: Using classpath: file:/tmp/apex/apex/____embedded/start.jar:file:/tmp/apex/apex/WEB-INF/lib/xmlparserv2-11.2.0.jar:file:/tmp/apex/apex/WEB-INF/lib/ojmisc.jar:file:/tmp/apex/apex/WEB-INF/lib/commons-fileupload-1.2.1.jar:file:/tmp/apex/apex/WEB-INF/lib/poi-3.6-20091214.jar:file:/tmp/apex/apex/WEB-INF/lib/ucp.jar:file:/tmp/apex/apex/WEB-INF/lib/xdb-11.2.0.jar:file:/tmp/apex/apex/WEB-INF/lib/ojdbc6.jar:file:/tmp/apex/apex/WEB-INF/lib/apex.jar:file:/tmp/apex/apex/WEB-INF/lib/je-4.0.103.jar:
INFO: Starting Embedded Web Container in: /tmp/apex
Enter a username for the APEX Listener Administrator [adminlistener]: apex
Enter a password for apex:welcome
Confirm password for apex:

Enter a username for the APEX Listener Manager [managerlistener]: apex
Enter a password for apex:welcome
Confirm password for apex:
Aug 10, 2011 2:32:41 AM ____bootstrap.Deployer deploy
INFO: Will deploy application path=/tmp/apex/apex/WEB-INF/web.xml
Aug 10, 2011 2:32:48 AM ____bootstrap.Deployer deploy
INFO: deployed application path=/tmp/apex/apex/WEB-INF/web.xml
Using config file: /tmp/apex/apex-config.xml
APEX Listener version : 1.1.2.131.15.23
APEX Listener server info: Grizzly/1.9.18-o
INFO: http://localhost:8080/apex started

Step 7:Configure the Apex listener.
http://testnode1ebs:8080/apex/listenerConfigure

To configure the Apex listener Open IE 8 browser or Mozilla 3.0….
And provide the information and password as mentioned :

SID:VIS
Host:10.47.33.56
Password: welcome

Step 8:Request Apex team to create WORKSPACE by giving ‘admin’ user password.

Note: ‘admin’ user should be entered in Lower case letters.

User:admin
Password:welcome

Step 9:If You are getting ADMIN/ADMIN_PASSWORD as "Invalid Login Credential Error", then run

Connect as sys user at the EBS Database and execute this procedure:
begin

SQL> begin
wwv_flow_api.set_security_group_id(p_security_group_id=>10);
wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'ADMIN',
p_email_address => 'xxx@xxxdomain.com',
p_web_password => 'welcome') ;
end;
/

PL/SQL procedure successfully completed.

Step 10:Verify now with the APEX team and ask them to change the password for security reason..

Username:workspace
Pass:welcome

Happy Apex learning job is done...

Best regards,

Rafi.

RMAN backup types and Usage

2011-07-28T10:07:00.000-07:00

Hi,
Here I'm descrbing RMAN backups Types,Commands and Usage in Brief in Oracle 11g Database.Hope it will help one and all.

RMAN BACKUPS:
----------------

1)RMAN HOT BACKUP =>database up & running
2)RMAN COLD BACKUP =>database is not up(shutdown =>mount(now take backup).

1)RMAN HOT BACKUP: OPEN STATE (ARCHIVE_LOG MODE)
2)RMAN COLD BACKUP:MOUNT STATE (NO ARCHIVE LOG MODE)

INCREMENTAL BACKUP:
1)DIFFERENTIAL BACKUP =>BY DEFAULT INCREMENTAL BACKUP IS DIFFERENTIAL.
=>BACKUP FROM SAME OR LOWER LEVEL.
=>LEVEL 0,LEVEL 1,LEVEL 2.....
2)CUMMULATIVE BACKUP =>BACKUP FROM LEVEL 0(LOWEST LEVEL)
=>LEVEL 0,LEVEL 1,LEVEL 2.....

WHERE:
LEVEL 0=FULL BACKUP
LEVEL 1=CHANGE DATA FROM LEVEL 0
LEVEL 2=CHANGE DATA FROM LEVEL 1...

BACKUP STRATERGY:
--------------------

SUNDAY =LEVEL 0 =>INCREMENTAL (DIFFERENTIAL)=>BACKUP LEVEL 0(LOWEST LEVEL)
MONDAY =LEVEL 1 =>INCREMENTAL
TUESDAY=LEVEL 2 =>INCREMENTAL
WED =LEVEL 0 =>INCREMENTAL (CUMMULATIVE) =>BACKUP LEVEL 0(LOWEST LEVEL)
THUR =LEVEL 1 =>INCREMENTAL
FRI =LEVEL 2 =>INCREMENTAL
SAT= =LEVEL 1 =>CUMMULATIVE =>BACKUP LEVEL 1(CHANGE DATA FROM LEVEL 0)

TIMESTAMP SYMBOLS:
-------------------

%U=UNIQUE BACKUPSET NAME
%T= TIMESTAMP OF TIME OF BACKUPSET

RMAN COMMANDS:
----------------

INCREMENTAL BACKUP:
---------------------

1)DIFFERENTIAL BACKUP
2)CUMULATIVE BACKUP

1)DIFFERENTIAL BACKUP:
-- INCREMENTAL LEVEL 0
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=0 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

-- INCREMENTAL LEVEL 1
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=1 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

-- INCREMENTAL LEVEL 2
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=2 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

2)CUMULATIVE BACKUP:
----------------------

-- CUMMULATIVE BACKUP
RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> Backup incremental level=0 CUMULATIVE database tag='complete_backup';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }

released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK

Starting backup at 24-JUL-11
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:15
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11

released channel: ch1

-- CUMULATIVE BACKUP LEVEL LOWEST(0)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=0 CUMULATIVE DATABASE FILESPERSET 4 tag='cumulative';
tag='complete_cummulative_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

-- CUMULATIVE BACKUP LEVEL LOWEST(1)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }

released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK

Starting backup at 24-JUL-11
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:25
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:35
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11

released channel: ch1

allocated channel: c1
channel c1: SID=73 device type=DISK

Starting backup at 24-JUL-11
channel c1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_BACKUP2_CF_D-ORCL_ID-1280115002_1QMI7FDK_20110724 tag=TAG20110724T055044 R
D=31 STAMP=757317045
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11

released channel: c1

ARCHIVE LOG DELETION POLICY FOR A DATABASE:
----------------------------------------------

run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -5';
}

I do backups from the primary database to a local drive and have been puzzled
how to delete the standby archive logs after they ship.
The ‘obvious’ solution is the documented feature in RMAN:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

COMPRESSION LEVEL ORACLE 11g:
----------------------------------

basic =DEFAULT
none =Not recomended
medium = license
high =license

Drawbacks:
------------

medium =>consume hight system resource
high =>consume high system resource

II)RMAN COLD BACKUP:
----------------------

=>NOARCHIVE LOG MODE
=>MOUNT STATE
=> DEVELOPMENT => MUCH DISK SPACE IS NOT THERE
=>DOWNTIME TOLERABLE..

run_orcl.txt:
-----------------------------------------------------

*SNAPSHOT CONTROLFILE:RMAN USES FOR BACKUP OF CONTROL FILE.

Configure setting in RMAN :
----------------------------
We can use configure command to change any setting in RMAN.I would definetly like to outside my script like below.

$rman target / catalog rman/rman@catdb
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';

rman_cold.txt:
--------------

run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}

rman_orcl.bat:
---------------

-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_24july_cum.log

Note: In Unix environment use export for setting the enviromnmet and Write shell script 'rman_orcl.sh',rest all script is almost same and can fit as per our path in Unix environment.

RMAN BACKUP DETAILS Views:
---------------------------------

We can use Dictionary Views for checking the status of rman backups.

1)USE AFTER SCHEDULING RMAN BACKUP:
SELECT START_TIME||' '||END_TIME||' '||STATUS FROM V$RMAN_BACKUP_JOB_DETAILS;

2)USE TO CHECK BYYES PROCESSED IN RMAN BACKUP:
SELECT SID||' '||STATUS||' '||MBYTES_PROCESSED||' '||START_TIME||' '||END_TIME
FROM V$RMAN_STATUS;

3)USE TO CHECK SESSION AND RMAN BACKUP OUTPUT:
SELECT SID||' '||RECID||' '||OUTPUT||' '||SESSION_STAMP
FROM V$RMAN_OUTPUT;

Note:On Unix terminal,It will be very much useful to check the process in order to check the status by using 'ps' command.
$ps -eaf|grep rman

Hope it helps...

Enjoy Oracle learning.

Best regards,

Rafi.

RMAN Recovery Catalog Database Creation and Configuration

2011-07-25T11:48:00.000-07:00

Hi,
RMAN Recovery Catalog is an excellent way of keeping our backup safe.Since backup is the medicine of Database in case of failure or Data loss,so we don't want to keep the medicine and poison in the same bottle(which will be the case when target Database 'Controlfile' will be using for keeping the information of Backup.If the 'Controlfile' is corrupted or deleted(usually it is multiplexed),but still 'Controlfile' will be acting as poison and medicine.Hence Best option will be to use the 'Recovery Catalog'(Other Database to keep the RMAN metadata) where our backup can be safe.

Below are some steps for Creating and Configuring Recovery Catalog for our Database which might be production,test or Development:

Step 1:Create a new database for RMAN – Recovery catalog database->'CATDB' Database created using dbca.

Note: We can create a small database with minimal sizes of tablespaces and others, and we can name the database as CATDB for naming convention and to avoid the confusion between our production and rman databases.

Step 2:Create a new tablespace in the new database (CATDB)

$ sqlplus /nolog

CONNECT SYS/welcome@catdb AS SYSDBA;

CREATE TABLESPACE rman
DATAFILE 'D:\rafi\catdb\rman\rman01.dbf' size 200m;

Step 3:Create the Recovery Catalog Owner in the new database (CATDB)

CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;

Step 4:Grant the necessary privileges to the schema owner

SQL> GRANT recovery_catalog_owner TO rman;

Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog.RECOVERY_CATALOG_OWNER role has the CREATE_SESSION privileges so need to grant separately.

Step 5:Creating the Recovery Catalog

Connect to the database which will contain the catalog as the catalog owner.
For example:'rman' user is catalog owner in our example.

On Linux(UNIX):
------------------
Run the 'CREATE CATALOG' command to create the catalog

$ rman target / catalog rman/rman@catdb
RMAN> CREATE CATALOG;

recovery catalog created

On Windows:
--------------------

C:\Windows\system32>rman target / catalog rman/rman@catdb

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 00:44:02 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

Step 6:Registering a Database in the Recovery Catalog
Connect to the target database and recovery catalog database.

$ export ORACLE_SID=ORCL
$ rman target / catalog rman/rman@catdb

Recovery Manager: Release 11.2.0.1.0 - Production on Sun July 1 14:25:30 2007
Copyright (c) 1982, 2010, Oracle. All rights reserved.
connected to target database: ORCL (DBID=3677528376)
connected to recovery catalog database

windows:
-------------
C:\Windows\system32>set ORACLE_SID=ORCL

C:\Windows\system32> rman target / catalog rman/rman@catdb

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 01:03:19 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1280115002)
connected to recovery catalog database

Step 7:Register and Verify after connection

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

On Windows (OR) Linux Environment:
-------------------------------

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Register the target Database using 'REGISTER DATABASE' command as seen above.

Make sure that the registration is successful by running REPORT SCHEMA:

RMAN> REPORT SCHEMA;

Report of database schema

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM YES D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
2 570 SYSAUX NO D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
3 55 UNDOTBS1 YES D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
4 6144 USERS NO D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
5 100 EXAMPLE NO D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 D:\APP\RAFIALVI\ORADATA\ORCL\TEMP01.DBF

Note:

In Windows:
---------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
C:\Windows\system32>set ORACLE_SID=ORCL
C:\Windows\system32>rman target / CATALOG rman/rman@CATDB

In Linux:
-----------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
$export ORACLE_SID=ORCL
$rman target / CATALOG rman/rman@CATDB

Rest all steps are prety much similar in both environment.....

Hope it helps.

Best regards,

Rafi.

Recover Database from rman cold backup

2011-07-25T11:29:00.000-07:00

Hi,
If we have the rman cold backup we can recover the Database upto that point we have the backup.Below is our experiment to prove this point.

Recovery of Database from complete loss of all files:
---------------------------------------------------------
Our Database is in noarchive log mode as verified below:

SQL> ARCHIVE LOG LIST
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination D:\app\RafiAlvi\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence 1
Current log sequence 2
k
I'm having rman cold backup of all database files and image copy of controlfile.
Below are our scripts which I used for taking cold backup:

run_ORCL.txt:
-----------------
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\orcl_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}

rman_coldb_backup.bat:
------------------------
-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_25july_cold.log

rman_cold_orcl_25july_cold.log:
--------------------------------

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 25 09:04:41 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1280115002, not open)

RMAN> run{
2> shutdown immediate;
3> startup mount;
4> allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
5> allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
6> backup database TAG='ORCL_BACKUP_WEEKLY';
7> allocate channel c1 type disk;
8> copy current controlfile to 'D:\RMAN_BACKUP\orcl_ctrl_%U_%T';
9> Release channel c1;
10> configure retention policy to recovery window of 7 days;
11> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
12> CROSSCHECK BACKUP;
13> release channel ch1;
14> release channel ch2;
15> alter database open;
16> }
17>
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 217157632 bytes

Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes

allocated channel: ch1
channel ch1: SID=134 device type=DISK

allocated channel: ch2
channel ch2: SID=5 device type=DISK

Starting backup at 25-JUL-11
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 25-JUL-11
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
channel ch2: starting piece 1 at 25-JUL-11
channel ch1: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_21MIAF62_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:11
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 25-JUL-11
channel ch1: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_23MIAF6E_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:08
channel ch2: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_22MIAF62_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:21
Finished backup at 25-JUL-11

allocated channel: c1
channel c1: SID=48 device type=DISK

Starting backup at 25-JUL-11
channel ch1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_CF_D-ORCL_ID-1280115002_24MIAF8O_20110725 tag=TAG20110725T090632 RECID=32 STAMP=757415193
channel ch1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JUL-11

released channel: c1

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\SNAPCF_ORCL.F';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
new RMAN configuration parameters are successfully stored

crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_04\O1_MF_NCSNF_TAG20110604T133433_6YO5PVD8_.BKP RECID=1 STAMP=752938475
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNNDF_TAG20110621T011243_700NMVS9_.BKP RECID=8 STAMP=754362763
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSNF_TAG20110621T011243_700NO02P_.BKP RECID=9 STAMP=754362800
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNND0_TAG20110621T013343_700OV861_.BKP RECID=10 STAMP=754364024
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSN0_TAG20110621T013343_700OYC20_.BKP RECID=11 STAMP=754364123
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNND1_TAG20110621T013544_700OZ19R_.BKP RECID=12 STAMP=754364145
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSN1_TAG20110621T013544_700P3CKP_.BKP RECID=13 STAMP=754364283
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 RECID=20 STAMP=757316409
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 RECID=21 STAMP=757316410
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 RECID=22 STAMP=757316486
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 RECID=23 STAMP=757316919
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 RECID=24 STAMP=757317004
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 RECID=25 STAMP=757317040
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724 RECID=26 STAMP=757318341
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1TMI7GMD_1_1_20110724 RECID=27 STAMP=757318368
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1UMI7GN7_1_1_20110724 RECID=28 STAMP=757318377
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724 RECID=29 STAMP=757318347
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_21MIAF62_1_1_20110725 RECID=30 STAMP=757415106
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_23MIAF6E_1_1_20110725 RECID=31 STAMP=757415136
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_22MIAF62_1_1_20110725 RECID=32 STAMP=757415110
Crosschecked 20 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_20110618_04MF7PSK_1_1 RECID=2 STAMP=754182037
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_20110618_05MF7PVA_1_1 RECID=3 STAMP=754182123
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_07MF7TM1_1_1_20110619 RECID=4 STAMP=754185922
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_09MF7TM4_1_1_20110619 RECID=5 STAMP=754185933
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_0AMF7TML_1_1_20110619 RECID=6 STAMP=754185943
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_08MF7TM2_1_1_20110619 RECID=7 STAMP=754185924
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0LMI4OJ5_1_1 RECID=14 STAMP=757228134
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0MMI4OM5_1_1 RECID=15 STAMP=757228232
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0OMI4PES_1_1 RECID=16 STAMP=757229020
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0PMI4PJ3_1_1 RECID=17 STAMP=757229156
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0RMI4QET_1_1 RECID=18 STAMP=757230046
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0SMI4QJ5_1_1 RECID=19 STAMP=757230182
Crosschecked 12 objects

released channel: ch1

released channel: ch2

database opened

Recovery Manager complete.

We are dropping our Database and assuming that all our Database files are lost
or corrupted including the controlfile.I'm including that step also.

Dropping Database:
---------------------
We have to drop Database in mount restrict mode as given below:

To make sure we are dropping the correct Database,please make sure we do the below steps:

C:\Users\RafiAlvi>set ORACLE_SID=ORCL

C:\Users\RafiAlvi>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 25 09:14:41 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select status from v$instance;

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

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
Database mounted.
SQL> drop database;

Database dropped.

Now I will try to connect the Database,

C:\Users\RafiAlvi>set ORACLE_SID=ORCL

C:\Users\RafiAlvi>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 25 09:18:12 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

Now,Restore and recovery of Database can be done by following below steps:

Step 1: Startup Database with pfile:
Create one pfile in location where previous pfile was existing:

INITORCL.ora:

db_name=ORCL

Step 2:Restore the controlfile with 'RMAN':

RMAN> restore controlfile;

Starting restore at 24-JUL-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: copied control file copy
input file name=D:\RMAN_BACKUP\ORCL_CTRL_CF_D-ORCL_ID-1280115002_1VMI7GP2_20110724
output file name=D:\APP\RAFIALVI\PRODUCT\11.2.0\DBHOME_1\DATABASE\CTL1ORCL.ORA
Finished restore at 24-JUL-11

Step 3: Mount the Database.

C:\Users\RafiAlvi>set ORACLE_SID=ORCL

C:\Users\RafiAlvi>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 08:31:24 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='D:\APP\RAFIALVI\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITORCL.ORA'
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

Step 4: Restore ,recover and open the Database.

RMAN> restore Database;

Starting restore at 24-JUL-11
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724
channel ORA_DISK_1: piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724 tag=ORCL_BACKUP_WEEKLY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724
channel ORA_DISK_1: piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724 tag=ORCL_BACKUP_WEEKLY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 24-JUL-11

RMAN> recover database;

Starting recover at 24-JUL-11
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/24/2011 08:55:16
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database

Step 5:0RA-38760 is because flashback Database is turned ON.
We have to turn OFF and try now,It will work.

CASE 1:IN ARCHIVE LOG MODE:
------------------------------

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

SQL> alter database flashback off;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

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

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

CASE 2:IN NOARCHIVELOG MODE
-------------------------------

RMAN> recover database;

Starting recover at 25-JUL-11
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/25/2011 10:29:40
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-00275: media recovery has already been started

(OR)

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00275: media recovery has already been started

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

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

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG
Oracle is amazing :) BECAUSE my Database was in NOARCHIVELOG mode after recovery as I resetlogs it is converted to ARCHIVELOG MODE.

Hope we enjoyed recovery.

Best regards,

Rafi.

Resolving ORA-31626,ORA-31633 while running Datapump job

2011-07-17T21:51:00.000-07:00

Resolving ORA-31626,ORA-31633 while running Datapump job:
-------------------------------------------------------------
When I check my expdp logs(Datapump backup logs) and backup for one of my development Database I found both the logs and backup for the previous day missing,than I checked the cron job log and found the job actually ran the previous day.After some thinking I decided to run the expdp job(Datapump job) once again manually with the help of one my favourite command 'nohup' as follows:
$nohup sh expdp_FULL_DB_DEVDB.sh>a.out&

In the a.out(log file for nohup job used for running expdp job) I found the below error:

ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.FULL_EXPDP_DEVDB"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-00955: name is already used by an existing object

After some research,I found the cause of this error as given below:

Cause: This error occured because Job creation failed because a Master Table(A table created when a Datapump job is started and dropped when a Datapump job is completed) and its indexes could not be created,most commonly due to the pre-existance of a table with the same name (job name) in the user schema

Solution: I decided to Change the JOB_NAME in the expdp script and run the expdp script once again with the help of the 'nohup' command again as given below:

$vi expdp_FULL_DB_DEVDB.sh
JOB_NAME=EXPD_JOB_NEW_NAME
:wq

$nohup sh expdp_FULL_DB_DEVDB.sh>a.out&

This time the 'nohup' command ran successfully,I checked the 'expdp' job it started successfully and the log file also looks fine.

$ps -eaf|grep expdp
=>Datapump(expdp) job found running

$tail -f expdp-18072011_095042.log
=>show running log file and I've verified here that expdp job is running fine and completed successfully.

After some time expdp job completed successfully,My only concern is next time this issue should not occur for this I'm not getting any clue if you have any please reply to this post.

Hope it helps...

Best regards,

Rafi.

Resolving DRG-10502 and ORA-06512

2011-07-13T02:42:00.000-07:00

Hi,
When I checked my Datapump export backup(logical backup) log file,I saw the below error message,In the below post I'm explaining the cause and the posible solution to get rid of this error in our next Datapump export backup.Remember,we need
to check our log files regularly for resolving such errors and to make sure our backup is complete error free as this is very much vital for DBA to avoid any future issues.

Error message:

ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('WWV_FLOW_OH_IDX','APEX_040000','TEXTINDEXMETHODS','CTXSYS',11.02.00.00.00,newblock,0)
ORA-20000: Oracle Text error:
DRG-10502: index APEX_040000"."WWV_FLOW_OH_IDX does not exist
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 6300

Cause:

We got this error because,In my 11g rel2 database Because of the below reason:

The errors are thrown because the domain index has status FAILED, visible in USER_/DBA_INDEXES view, and no entry exists in Text data dictionary, ctxsys schema.Only metadata of valid domain indexes are exported.
I tried to rebuild the index but still the same error thrown.So finally decided to
drop it.

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_type = 'DOMAIN'
and domidx_opstatus = 'FAILED'; 2 3 4

INDEX_NAME STATUS DOMIDX_STATU DOMIDX
------------------------------ -------- ------------ ------
WWV_FLOW_OH_IDX VALID VALID FAILED

SQL> ALTER INDEX APEX_040000.WWV_FLOW_OH_IDX REBUILD ONLINE;
ALTER INDEX APEX_040000.WWV_FLOW_OH_IDX REBUILD ONLINE
*
ERROR at line 1:
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-20000: Oracle Text error:
DRG-10561: index WWV_FLOW_OH_IDX is not valid for requested operation
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 614

Solution:
To implement the solution, please execute the following steps:

* if the indexes are required in your application drop and re-create them
* if the indexes are not used by your application then drop them

Resolution:
I have drop the index as I know it is not used by our application and one valid reason i.e ctxsys:The owner of Oracle text (formerly: interMedia text) and not used by our application.

Note: Before dropping WWV_FLOW_OH_IDX,please make sure it is not used by your application or else drop and recreate.

SQL> DROP INDEX APEX_040000.WWV_FLOW_OH_IDX;

Index dropped.

SQL> select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_type = 'DOMAIN'
and domidx_opstatus = 'FAILED'; 2 3 4

no rows selected

References:MY ORACLE SUPPORT,ORACLE FORUM

Hope it helps...

Best regards,

Rafi.

Resolving UDE-12545 and ORA-12545 error

2011-07-06T04:20:00.000-07:00

Hi,
Oracle networking errors can test our patience some times.Below is one such error.Changing the host or domain name of a server can lead to UDE-12545 and
ORA-12545 error.Below I'm discussing the cause of this error,possible ways to diagnose and resolve this error in a very simple manner.

Error message:

UDE-12545: operation generated ORACLE error 12545
ORA-12545: Connect failed because target host or object does not exist

Diagnosis:
We can solve this error as follows:

1)Check the listener is up & running

[oracle@node1 Backup_Scipts]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-JUL-2011 10:55:48

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.in.company.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 02-JUL-2011 11:00:36
Uptime 3 days 23 hr. 55 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle11g/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/oracle11g/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.in.company.com)(PORT=1521)))
Services Summary...
Service "TESTDB" has 1 instance(s).
Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service...
Service "TESTDB2" has 1 instance(s).
Instance "TESTDB2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

2)Use the tnsping to check the connection string(TNS entry) for Database is working fine or not,for checking network connectivity:

Here we will get the error as given below:

[oracle@node1 Backup_Scipts]$ tnsping TESTDB

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-JUL-2011 10:55:56

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/oracle11g/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.apac.company.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB)))
TNS-12545: Connect failed because target host or object does not exist

Note:Here,TESTDB is connection string(tns entry) present in tnsnames.ora file.

Resolving:
Now,We have to check our hostname and verify it with the connection string present in the tnsnames.ora file.

Verify the host details and connection string details and validate:

[oracle@node1 Backup_Scipts]$ hostname
node1.in.company.com
As we noticed we need to change the domain name from 'apac' to 'in' in the connetion string present
in tnsnames.ora file and save it and than try 'tnsping'

[oracle@node1 admin]$ tnsping TESTDB

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-JUL-2011 10:57:58

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/oracle11g/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.in.company.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB)))
OK (10 msec)

Now,we can perform the tasks related to rman,datapump or other tasks dealing with the connection string it will work fine.

Hope it helps.

Best regards,

Rafi.

APEX Installation,Uninstallation and Administration in Oracle Database

2011-06-24T06:47:00.000-07:00

Hi,
As a DBA we might come across doing the Below task for APEX Component existing
in our Database from Oracle 11g onwards.

Before Uninstalling any component(apex) from Oracle Database(Oracle 11g rel2) discuss with team and inform clearly and once this is confirmed,than follow the below steps,By default in Oracle 11g rel2 Database APEX Component is installed (Apex version 3.2.1.00.10).

SQL> select *from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL>SELECT COMP_NAME||' '||VERSION||' '||STATUS FROM DBA_REGISTRY;

COMP_NAME||''||VERSION||''||STATUS
--------------------------------------------------------------------------------
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.1.0 VALID
OLAP Catalog 11.2.0.1.0 VALID
Spatial 11.2.0.1.0 VALID
Oracle Multimedia 11.2.0.1.0 VALID
Oracle XML Database 11.2.0.1.0 VALID
Oracle Text 11.2.0.1.0 VALID
Oracle Expression Filter 11.2.0.1.0 VALID
Oracle Rules Manager 11.2.0.1.0 VALID
Oracle Workspace Manager 11.2.0.1.0 VALID
Oracle Database Catalog Views 11.2.0.1.0 VALID
Oracle Database Packages and Types 11.2.0.1.0 VALID
JServer JAVA Virtual Machine 11.2.0.1.0 VALID
Oracle XDK 11.2.0.1.0 VALID
Oracle Database Java Packages 11.2.0.1.0 VALID
OLAP Analytic Workspace 11.2.0.1.0 VALID
Oracle OLAP API 11.2.0.1.0 VALID

Apex Uninstallation:
Apex uninstallation can be done as follows:
[oracle@node1 apex]$ cd $ORACLE_HOME/apex
SQL> @apxremov.sql
...Removing Application Express
old 1: alter session set current_schema = &APPUN
new 1: alter session set current_schema = APEX_030200

Session altered.
begin
*
ERROR at line 1:
ORA-04063: package body "APEX_030200.WWV_FLOW_UPGRADE" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"APEX_030200.WWV_FLOW_UPGRADE"
ORA-06512: at line 2
no rows selected
old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
begin
*
ERROR at line 1:
ORA-04063: package body "APEX_030200.WWV_FLOW_UPGRADE" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"APEX_030200.WWV_FLOW_UPGRADE"
ORA-06512: at line 3

Session altered.

PL/SQL procedure successfully completed.

old 1: drop user &APPUN cascade
new 1: drop user APEX_030200 cascade

User dropped.

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
begin
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
ORA-06512: at line 4

old 5: if '&UPGRADE' = '1' then
new 5: if '1' = '1' then
declare
*
ERROR at line 1:
ORA-24231: database access descriptor (DAD) APEX not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_EPG", line 67
ORA-06512: at "SYS.DBMS_EPG", line 261
ORA-06512: at line 15

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
begin
*
ERROR at line 1:
ORA-04043: object VALIDATE_APEX does not exist
ORA-06512: at line 3

...Application Express Removed

After running the above script if some APEX Schemas and synonyms still exist in our Database than we have to drop them manually.Below is the query to find public synonym and drop them .Spool into some sql file,for making this task simpler.

SQL)Spool Drop_pub_synonym.sql

SQL>select 'drop public synonym ' || synonym_name || CHR(10) || '/'
from sys.dba_synonyms
where table_owner in ('FLOWS_010500','FLOWS_010600','FLOWS_020000',
'FLOWS_020100','FLOWS_020200','FLOWS_030000','FLOWS_030100','APEX_030200',
'APEX_040000','FLOWS_FILES');

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
drop public synonym APEX_ACTIVITY_LOG
/

drop public synonym APEX_APPLICATION
/

drop public synonym APEX_APPLICATION_FILES
/

drop public synonym APEX_APPLICATION_GLOBAL
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------

drop public synonym APEX_APPLICATION_INSTALL
/

drop public synonym APEX_COLLECTION
/

drop public synonym APEX_COLLECTIONS
/

drop public synonym APEX_CSS

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
/

drop public synonym APEX_CUSTOM_AUTH
/

drop public synonym APEX_DEBUG_MESSAGE
/

drop public synonym APEX_FEEDBACK_TYPES
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
drop public synonym APEX_INSTANCE_ADMIN
/

drop public synonym APEX_ITEM
/

drop public synonym APEX_JAVASCRIPT
/

drop public synonym APEX_LANG
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------

drop public synonym APEX_LDAP
/

drop public synonym APEX_LOGIN
/

drop public synonym APEX_MAIL
/

drop public synonym APEX_MAIL_ATTACHMENTS

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
/

drop public synonym APEX_MAIL_LOG
/

drop public synonym APEX_MAIL_QUEUE
/

drop public synonym APEX_PLSQL_JOB
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
drop public synonym APEX_PLSQL_JOBS
/

drop public synonym APEX_PLUGIN
/

drop public synonym APEX_PLUGIN_UTIL
/

drop public synonym APEX_REST
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------

drop public synonym APEX_SITE_ADMIN_PRIVS
/

drop public synonym APEX_UI_DEFAULT
/

drop public synonym APEX_USER_ACCESS_LOG
/

drop public synonym APEX_UTIL

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
/

drop public synonym APEX_WEB_SERVICE
/

drop public synonym HTMLDB_ACTIVITY_LOG
/

drop public synonym HTMLDB_APPLICATION
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
drop public synonym HTMLDB_APPLICATION_FILES
/

drop public synonym HTMLDB_APPLICATION_GLOBAL
/

drop public synonym HTMLDB_COLLECTION
/

drop public synonym HTMLDB_COLLECTIONS
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------

drop public synonym HTMLDB_LDAP
/

drop public synonym HTMLDB_MAIL
/

drop public synonym HTMLDB_MAIL_LOG
/

drop public synonym HTMLDB_MAIL_QUEUE

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
/

drop public synonym HTMLDB_PLSQL_JOB
/

drop public synonym HTMLDB_PLSQL_JOBS
/

all synonyms are dropped for apex users:
---------------------------------=------------

SQL> select synonym_name from sys.dba_synonyms where owner = 'FLOWS_FILES';
no rows selected

SQL>SPOOL OFF
SQL>@Drop_pub_synonym.sql
All the synonyms are dropped.
We have to make sure the below all the APEX related schemas are dropped or else drop them manually,In my case I upgraded to Apex 4.x so additional user APEX_040000 is also present:
SQL>DROP USER APEX_040000 CASCADE;
SQL>DROP USER APEX_PUBLIC_USER CASCADE;
SQLDROP USER FLOW_FILES CASCADE;
SQLDROP USER APEX_03000 CASCADE;
Now our Apex Uninstallation is successful.

Apex Installation:

1) Go to $ORACLE_HOME/apex path: For Oracle 11g rel2 Database apex component is already present when we install the software in our Operating system in '$ORACLE_HOME/apex' path.

$cd $ORACLE_HOME/apex
$pwd
/u01/oracle11g/product/11.2.0/dbhome_1/apex

2)Connect to SQL-PLUS and execute apexins for installing apex
SQL>@apexins SYSAUX SYSAUX TEMP /i/

Apply any upgraded Apex Component if required by following same process as above.

Apex Patching:
For patching the apex component,Run apxpatch.sql.
For example:
SQL>@apxpatch.sql

Apex ADMIN user password change:

We might get request from our APEX team to change the APEX ADMIN user password after Apex installation,We can change the APEX ADMIN user password by executing 'apxchpwd.sql'.

$cd $ORACLE_HOME/apex
SQL>@apxchpwd.sql
Enter a value below for the password for the Application Express ADMIN user.

Enter a password for the ADMIN user []

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.

Hope it helps in doing APEX administration.If I come across some other Apex
related task than I will be posting the same.

Best regards,

Rafi.

Automate Startup and Shutdown of Oracle Database,Listener and HTTP Serivices on Linux

2011-06-20T07:29:00.000-07:00

Hi,
Automating Regular Database tasks is the key priority of DBA.In the below script we can automate Databases,Listerners,HTTP services start and stop.

Step 1: Please make sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /u01/app/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:[Y|N]
Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.

If in my database there is two database named testdb1 and testdb2 then my oratab file will contain the entry like,
testdb1:/u01/app/oracle/product/10.2.0/db_1:Y
testdb2:/u01/app/oracle/product/10.2.0/db_1:Y
where :/u01/app/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.

Step 2: Create a script to call dbstart and dbshut.
In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.

a) Login as 'root'.
b) Change directories to /etc/init.d
$cd /etc/init.d
c) Create an empty file called 'dbora' with 'touch' command and Grant permission with chmod as 750.

# touch dbora
# chmod 750 dbora
d)Edit the dbora file and make the contents of it like below.

dbora:

#!/bin/bash
#
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
#
ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

case "$1" in
'start')

# Start the TNS Listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
# Start the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
# Start the Intelligent Agent
if [ -f $ORA_HOME/bin/emctl ];
then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start agent"
elif [ -f $ORA_HOME/bin/agentctl ]; then

su - $ORA_OWNER -c "$ORA_HOME/bin/agentctl start"
else
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl dbsnmp_start"
fi
# Start Management Server
if [ -f $ORA_HOME/bin/emctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
elif [ -f $ORA_HOME/bin/oemctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/oemctl start oms"
fi
# Start HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl]; then
su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl start"
fi
touch /var/lock/subsys/dbora
;;
'stop')
# Stop HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl ]; then

su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl stop"
fi
# Stop the TNS Listener

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# Stop the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
rm -f /var/lock/subsys/dbora
;;
esac
# End of script dbora

Step 3:As 'root' user perform the following to create symbolic links:

# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle

Alternatively we can register the Service using
/sbin/chkconfig --add dbora

This action registers the service to the Linux service mechanism.

Step 4: Test the script to make sure it works.

The real test is to reboot unix box and then see whether oracle is started up automatically or not.

However to test the script created in step 2, without rebooting, do the following:

Login as root and then,
# /etc/init.d/dbora start (for startup)
# /etc/init.d/dbora stop (for shutdown)

If we restart,start and stop oracle database is successful then we are almost done.The real Beauty of the above shell script is It will start and stop all the listener,Databases,HTTP Services whenever server is rebooted for start and stop.

Note:The above Shell Script we can modify as per our convenience.Suppose if we want to Start only Databases and Listener Sevices than comment the portion of the shell script for Starting HTTP services.

Hope it helps.

Best regards,

Rafi

Scheduling RMAN cold backup and copy controlfile

2011-06-14T06:18:00.000-07:00

Hi,
RMAN cold backup has its own advantage compare to the traditional cold backup:
1)RMAN as we all know backup only used blocks so it is fast.Here entire Database I'm taking backup.
2)In my below script I'm copying the control file to have the latest copy with me in case of recovery.

This is because of below factors:
-------------------------------------------------------------------------

"copy current controlfile"
Personally I like and use this,Because of below reasons:

*restore after total loss.

I run this command as the *final* command of my database,
run {allocate channel c1 type disk; copy current controlfile to '/some OS location'.

Effectively,I'm backing up my "Backup Meta Data" at the end of each RMAN backup.
My RMAN shell scripts look like this:
--------------------------------------------

1)Backup database in mount State because rman expect Database to be in mount state for taking cold backup

2)copy current controlfile to some location
I'm not saying do not use these other options, just be sure we know ,What we are getting and whether we are able to recover the Database.Test your backups in competency servers and be sure that you can do restore & recovery in case of failure.

3)In my rman scripts,I've used
%U=To have unique backupset number
%T=Date of backup

4)Snapshot Controlfile:
Snapshot controlfile is used by rman to backup control file.Personal speaking not required to do so.If we have copy of current controlfile.Here I have used to save one in the current location of rman backup location.

5)Retention policy:
It is that period of time,till when you want to keep the backup in rman repository.Usually decide this policy well in advance and inform the team.In my case till 7 days I can obtain the backup for restore and recovery purpose.

Below are my scripts:
------------------------
1)run_TESTDB.txt:
---------------------
run_TESTDB.txt is for the setting I'm going to configure for RMAN.We have to create this file first than the actual shell script for taking rman backup.

-- RMAN Configured
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_BK_SET2_%U_%T';
backup database TAG='TESTDB_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/snapcf_TESTDB.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}

2)rman_TESTDB.sh:
--------------------
rman_TESTDB.sh consist of setting Oracle Environment Variables and calling the run_TESTDB.txt file for taking the rman cold backup.

-- TESTDB RMAN COLD BACKUP
export ORACLE_SID=TESTDB
export ORACLE_HOME=/u01/oracle11g/product/11.2.0/dbhome_1
export ORACLE_BASE=/u01/oracle11g
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
rman target sys/TESTDBdba @/u05/DB_BACKUP_TESTDB/Backup_Scipts/run_TESTDB.txt log=/u05/DB_BACKUP_TESTDB/logs/rman_cold_weekly_backup_logs/rman_TESTDB.log

Scheduling RMAN Backup job:
-------------------------------
If I want to schedule this backup every sunday at 9pm.The cronjob script will be:
$vi crontab.oracle

#Script for RMAN COLD BACKUP WEEKLY TESTDB DATABASE
################################################################
00 21 * * 0 /u05/DB_BACKUP_TESTDB/SCRIPTS/rman_TESTDB.sh 2>&1 >/u05/DB_BACKUP_TESTDB/logs/CRONJOBS_LOGS/testdb_rman_cron.log

$crontab crontab.oracle =>scheduling job using 'crontab' command at 9pm every sundays.

Verifying cronjob:
-----------------------
$crontab -l =>List the jobs that are scheduled using Cronjob.Verify the rman job also if we have schedule as above.

Hope it helps.

Best regards,

Rafi.

Moving Database files from one location to other location in a Planned way

2011-06-10T03:53:00.000-07:00

Hi,
Whenever we get issue like some mount point is not getting sufficient in terms of disk space,we decide to move the Database files to a separate mount point.While doing this task all the end users or affected users should be informed well in advance and we have to ask for suitable downtime and duration considering the size of Data we are moving from one mount point to other and if possible we have to do such task at less peak hour of Database usage.Planning and creation of Scripts should be done well in advance,so that the actual process can be completed on time or much before time.
Step 1 and step2 can be planned and implemented well before the actual process can start.I'm doing this small test environment,Remember when we are doing such task in a very large size Database that time planning and implementing this small task on
time is very crucial.

1)Check the source location where Database files reside:
-------------------------------------------------------------------

SQL> select file_name||' '||tablespace_name||' '||bytes/1024/1024 as MB from dba_data_files;

MB
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TESTDB/users01.dbf USERS 203.75
/u01/app/oracle/oradata/TESTDB/undotbs01.dbf UNDOTBS1 235
/u01/app/oracle/oradata/TESTDB/sysaux01.dbf SYSAUX 970
/u01/app/oracle/oradata/TESTDB/system01.dbf SYSTEM 940
/u01/app/oracle/oradata/TESTDB/example01.dbf EXAMPLE 102.5
/u01/app/oracle/oradata/TESTDB/apex01.dbf APEX 100

6 rows selected.

SQL> select file_name||' '||tablespace_name||' '||bytes/1024/1024 as MB from dba_temp_files;

MB
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TESTDB/temp01.dbf TEMP 52

[oracle@host1 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
18G 14G 2.6G 85% /
/dev/sda1 99M 20M 75M 21% /boot
tmpfs 940M 0 940M 0% /dev/shm
/dev/sdb1 20G 3.0G 16G 17% /u02

Step 2:Create the directory structure in /u02 where we can move database files.
-------
$cd /u02
$mkdir -p app/oracle/oradata/TESTDB
$cd app/oracle/oradata/TESTDB/
$pwd
/u02/app/oracle/oradata/TESTDB

Step 3: Shutdown the Database
---------

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Step 4: Using 'mv' command move all database files(.dbf) files.
-------

$ cd /u01/app/oracle/oradata/TESTDB/
$ pwd
/u01/app/oracle/oradata/TESTDB
$ ls -altr
total 3016160
-rw-r----- 1 oracle oinstall 10043392 Jun 3 2010 control03.ctl
-rw-r----- 1 oracle oinstall 10493964 Sep 23 2010 test_01.dfb__old
-rw-r----- 1 oracle oinstall 10493874 Sep 23 2010 test_01.dfb
drwxr-x--- 4 oracle oinstall 4096 Mar 17 14:19 ..
drwxr-x--- 2 oracle oinstall 4096 Jun 2 19:51 .
-rw-r----- 1 oracle oinstall 52429312 Jun 9 19:56 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 19:56 redo02a.log
-rw-r----- 1 oracle oinstall 52429312 Jun 10 08:00 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jun 10 08:00 redo03a.log
-rw-r----- 1 oracle oinstall 54534144 Jun 10 15:30 temp01.dbf
-rw-r----- 1 oracle oinstall 213655552 Jun 10 15:43 users01.dbf
-rw-r----- 1 oracle oinstall 246423552 Jun 10 15:43 undotbs01.dbf
-rw-r----- 1 oracle oinstall 985669632 Jun 10 15:43 system01.dbf
-rw-r----- 1 oracle oinstall 1017126912 Jun 10 15:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jun 10 15:43 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 10 15:43 redo01a.log
-rw-r----- 1 oracle oinstall 107487232 Jun 10 15:43 example01.dbf
-rw-r----- 1 oracle oinstall 104865792 Jun 10 15:43 apex01.dbf
-rw-r----- 1 oracle oinstall 10043392 Jun 10 15:43 control01.ctl
$ mv *.dbf /u02/app/oracle/oradata/TESTDB/

Step 5:Verify at the target location(/u02)
-------
[oracle@host1 TESTDB]$ cd /u02/app/oracle/oradata/TESTDB/
[oracle@host1 TESTDB]$ pwd
/u02/app/oracle/oradata/TESTDB
[oracle@host1 TESTDB]$ ls -altr
total 2668428
-rw-r----- 1 oracle oinstall 54534144 Jun 10 15:30 temp01.dbf
drwxr-xr-x 3 oracle oinstall 4096 Jun 10 15:31 ..
-rw-r----- 1 oracle oinstall 213655552 Jun 10 15:43 users01.dbf
-rw-r----- 1 oracle oinstall 246423552 Jun 10 15:43 undotbs01.dbf
-rw-r----- 1 oracle oinstall 985669632 Jun 10 15:43 system01.dbf
-rw-r----- 1 oracle oinstall 1017126912 Jun 10 15:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 107487232 Jun 10 15:43 example01.dbf
-rw-r----- 1 oracle oinstall 104865792 Jun 10 15:43 apex01.dbf

Step 6:Use 'Alter database' commmand to rename or change the location in mount state
--------

$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 10 15:54:05 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 764157952 bytes
Fixed Size 2217184 bytes
Variable Size 335547168 bytes
Database Buffers 423624704 bytes
Redo Buffers 2768896 bytes
Database mounted.

-- For single file The below command can be used.
SQL>alter database rename file '/u01/app/oracle/oradata/TESTDB/users01.dbf' to '/u02/app/oracle/oradata/TESTDB/users01.dbf';

Rename_datafile_TESTDB.sql:
---------------------------

-- Renaming Database files for TESTDB Database
alter database rename file '/u01/app/oracle/oradata/TESTDB/system01.dbf' to '/u02/app/oracle/oradata/TESTDB/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/sysaux01.dbf' to '/u02/app/oracle/oradata/TESTDB/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/undotbs01.dbf' to '/u02/app/oracle/oradata/TESTDB/undotbs01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/users01.dbf' to '/u02/app/oracle/oradata/TESTDB/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/example01.dbf' to '/u02/app/oracle/oradata/TESTDB/example01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/apex01.dbf' to '/u02/app/oracle/oradata/TESTDB/apex01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/temp01.dbf' to '/u02/app/oracle/oradata/TESTDB/temp01.dbf';

[oracle@host1 SCRIPTS]$ chmod -R 775 /u02/SCRIPTS/

Make sure Rename_datafile_TESTDB.sql is having the execute privileges.

[oracle@host1 SCRIPTS]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 10 16:08:21 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> @Rename_datafile_TESTDB.sql

Database altered.

Database altered.

Database altered.

Database altered.

Database altered.

Database altered.

Database altered.

Step 7:Open the Database and verify the new location for your Database files
-------
SQL> alter database open;

Database altered.

SQL> select tablespace_name||' '||file_name||' '||bytes/1024/1024 as MB from dba_data_files;

MB
--------------------------------------------------------------------------------
USERS /u02/app/oracle/oradata/TESTDB/users01.dbf 203.75
UNDOTBS1 /u02/app/oracle/oradata/TESTDB/undotbs01.dbf 235
SYSAUX /u02/app/oracle/oradata/TESTDB/sysaux01.dbf 970
SYSTEM /u02/app/oracle/oradata/TESTDB/system01.dbf 940
EXAMPLE /u02/app/oracle/oradata/TESTDB/example01.dbf 102.5
APEX /u02/app/oracle/oradata/TESTDB/apex01.dbf 100

6 rows selected.

SQL> select tablespace_name||' '||file_name||' '||bytes/1024/1024 as MB from dba_temp_files;

MB
--------------------------------------------------------------------------------
TEMP /u02/app/oracle/oradata/TESTDB/temp01.dbf 52

[oracle@host1 TESTDB]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
18G 12G 5.2G 69% /
/dev/sda1 99M 20M 75M 21% /boot
tmpfs 940M 0 940M 0% /dev/shm
/dev/sdb1 20G 5.6G 14G 30% /u02

Note: Moving or renaming a datafile while the database is in the 'OPEN' stage is also possible,But we have to make tablespace offline,When no downtime is accepted than we can go for the below steps.

The steps involved are:
-------------------------

1)Take the tablespace OFFLINE.
2)Use operating system commands to move or rename the files.
3)Use the ALTER TABLESPACE command to rename the file within the database.
4)Bring the tablespace back ONLINE.

sqlplus "/ as sysdba"

SQL> alter tablespace TEST_TB offline;

SQL> !mv /u05/app/oradata/TESTDB/user_data_01.dbf /u06/app/oradata/TESTDB/user_data_01.dbf

SQL> alter tablespace TEST_TB
rename datafile '/u05/app/oradata/TESTDB/user_data_01.dbf' to '/u06/app/oradata/TESTDB/user_data_01.dbf';

Bring the tablespace back online.

SQL> alter tablespace TEST_TB online;

SQL> exit

Hope it helps.

Best regards,

Rafi.

Scheduling expdp and impdp backup jobs using cronjob

2011-06-08T23:14:00.000-07:00

Hi,
We can schedule expdp and impdp using cronjob to have logical backup of our Databases on daily basis or depending on the requirement..In the below article
I'm taking two Databases TESTDB_1 and TESTDB_2.In the below shell scripts,I'm doing tasks for scheduling expdp job using
cronjob. In the below article we are doing below tasks for 'expdp',However similary we can do for 'impdp' by changing impdp scripts:

1)I'm setting the Oracle Environment,
2)Creating expdp script for taking export Datapump backup(Logical Database backup).
3)Moving log files to a separate Directory on the server.
4)Giving all the required permission for the dumpfile backup.
5)Zipping the dumpfiles using gunzip utility.
6)Deleting the dumpfiles which are more than 6 days(7days) old. &
7)Using Cronjob to execute and schedule this task Daily at 9pm & 11pm.

Pre-requistics steps:
-----------------------

1)TESTDB_1: Before executing and scheduling this script perform the below task in the Database side.Create a directory
for taking the datapump export backup and grant the required permissions to the user which you are going to use for
doing this export backup.In my case I took 'system' user.

Creating Directory:
--------------------

CREATE DIRECTORY expdp_TESTDB_2_dir as '/u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily';
GRANT READ,WRITE ON DIRECTORY expdp_TESTDB_2_dir TO SYSTEM;

Note: If you a creating a separate user or plannning to use separate user for taking the datapump export backup than,
Grant the required privileges.
Eg: Grant connect,exp_full_database,imp_full_database to Test_user; or Grant DBA to Test_user;

Shell script for TESTDB_1 Database is below:
--------------------------------------------

backup TESTDB_1.sh:
--------------------------

#!/bin/bash
#Script to Perform Datapump Export backup Every Day
################################################################
#Change History
#================
#DATE AUTHOR http://rafioracledba.blogspot.com LAST-CHANGE
#--------- ----------------------- --------------------------------- -----------
#26-MAY-2011 RAFI ALVI SCRIPT FOR FULL EXPORT TESTDB_1 01-JUN-2011
#
#
#
################################################################
export ORACLE_BASE=/u01/oracle11g
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=TESTDB_1
export PATH=$PATH:$ORACLE_HOME/bin
expdp system/TESTDB_1dba@TESTDB_1 dumpfile=expdp-`date '+%d%m%Y_%H%M%S'`.dmp directory=expdp_TESTDB_1_dir logfile=expdp-`date '+%d%m%Y_%H%M%S'`.log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_TESTDB_1
############################################################################
#Moving log files
mv /u05/DB_BACKUP_TESTDB_1/expdp_fulldb_backup/expdp*.log /u05/DB_BACKUP_TESTDB_1/logs/expdp_fulldb_backup_logs
###############################################################################3
#Granting permissions for Dump files
chmod -R 775 /u05/DB_BACKUP_TESTDB_1/expdp_fulldb_backup/*
#################################################################################
#Compressing files
gzip /u05/DB_BACKUP_TESTDB_1/expdp_fulldb_backup/*.*
#################################################################################33
#Removing 7 days old dump files
find /u05/DB_BACKUP_TESTDB_1/expdp_fulldb_backup -mtime +6 -exec rm {} \;
######################################################################################

cronjob script:
----------------------
##############################################################
#Script for full export backup TESTDB_1 DATABASE
###############################################################
00 21 * * * /u05/DB_BACKUP_TESTDB_1/Backup_Scipts/expdp_FULL_BACKUP_TESTDB_1.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/UGal_cron.log

Similary you can do for any Database.In the below case I'm doing it for the 2nd Database 'TESTDB_2'.

2)TESTDB_2:
------------

SQL>CREATE DIRECTORY expdp_TESTDB_2_dir as '/u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily';
SQL>GRANT READ,WRITE ON DIRECTORY expdp_TESTDB_2_dir TO SYSTEM;

backup TESTDB_2.sh:
--------------------------------

#!/bin/bash
#Script to Perform Datapump Export Every Day
################################################################
#Change History
#================
#DATE AUTHOR http://rafioracledba.blogspot.com LAST-CHANGE
#--------- ----------------------- --------------------------------- -------------
#26-MAY-2011 RAFI ALVI SCRIPT FOR FULL EXPORT TESTDB_2 01-JUN-2011
#
#
#
################################################################
export ORACLE_BASE=/u01/oracle11g
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=TESTDB_2
export PATH=$PATH:$ORACLE_HOME/bin
expdp system/TESTDB_2dba@TESTDB_2 dumpfile=expdp-`date '+%d%m%Y_%H%M%S'`.dmp directory=expdp_TESTDB_2_dir logfile=expdp-`date '+%d%m%Y_%H%M%S'`.log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_TESTDB_2
###################################################################
#Moving log files
mv /u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily/expdp*.log /u05/DB_BACKUP_TESTDB_2/logs/expdp_fulldb_backup_logs
#####################################################################
#Granting Permission to dump files
chmod -R 775 /u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily/*
########################################################################
#Compressing the files
gzip /u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily/*.*
###########################################################################
#Removing 7 days old dump files
find /u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily -mtime +6 -exec rm {} \;

cronjob script:
--------------------------

##############################################################
#Script for full export backup TESTDB_2 DATABASE
###############################################################
00 23 * * * /u05/DB_BACKUP_TESTDB_2/SCRIPTS/expdp_FULL_DB_TESTDB_2.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/wgal_cron.log

Scheduling task using Cronjob:
----------------------------------------

We have to schedule the 2 export Datapump job using cronjob for doing this task perform the steps described below:

1)Create one separate directory for all your DBA/Cronjob tasks and grant all the permissions so that scripts can execute.
$mkdir DBA_TASKS
$chmod -R 775 /u04/DBA_TASKS

2)All the shell scripts and Cronjob script you can keep in /u04/DBA_TASKS.

3)We can create a separate file or edit the existing crontab file.

In my example I'm creating a separate file 'crontab.oracle' where I will put the below cronjob scripts:

vi crontab.oracle

##############################################################
#Script for full export backup TESTDB_1 DATABASE
###############################################################
00 21 * * * /u05/DB_BACKUP_TESTDB_1/Backup_Scipts/expdp_FULL_BACKUP_TESTDB_1.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/UGal_cron.log
##############################################################
#Script for full export backup TESTDB_2 DATABASE
###############################################################
00 23 * * * /u05/DB_BACKUP_TESTDB_2/SCRIPTS/expdp_FULL_DB_TESTDB_2.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/wgal_cron.log

:wq
Save and quit

(or) crontab -e
##############################################################
#Script for full export backup TESTDB_1 DATABASE
###############################################################
00 21 * * * /u05/DB_BACKUP_TESTDB_1/Backup_Scipts/expdp_FULL_BACKUP_TESTDB_1.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/UGal_cron.log
##############################################################
#Script for full export backup TESTDB_2 DATABASE
###############################################################
00 23 * * * /u05/DB_BACKUP_TESTDB_2/SCRIPTS/expdp_FULL_DB_TESTDB_2.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/wgal_cron.log

4)Schedule the job for execution using cronjob:
--------------------------------------------------

$crontab crontab.oracle
=>This command will schedule the job for execution,In the above case at 9pm and 11pm daily.

For more details regarding Cronjob Check my link Label 'UNIX FOR DBA'.

5)For verifying the cronjobs scheduled for execution:
$crontab -l
This command will list all jobs schedule for execution.

Hope it helps.

Best regards,

Rafi.

How to recover Database when accidentally deleted datafile without backup

2011-06-06T22:15:00.000-07:00

How to recover Database when accidentally deleted datafile without backup:
------------------------------------------------------------------------------
If you have accidentally deleted a datafile from a physical storage without backup you can recover database by dropping that particular datafile and Tablespace as follows,However if that datafile is very much crucial you can try the below link to hit and try:

http://gmdba.blogspot.com/2009/12/oracle-recover-deleted-datafile-enen.html

Step 1:Bring the tablespace offline so that you can shutdown the database.
-------

SQL>alter tablespace test11 offline immediate;

OR

$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 7 10:26:54 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1248484971)

RMAN> run
{
sql 'alter tablespace test11 offline immediate';
}

using target database control file instead of recovery catalog
sql statement: alter tablespace test11 offline immediate

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Step 2: In the mount state drop the datafile and than Drop the tablespace including contents.
-------

SQL> startup mount
ORACLE instance started.

Total System Global Area 764157952 bytes
Fixed Size 2217184 bytes
Variable Size 335547168 bytes
Database Buffers 423624704 bytes
Redo Buffers 2768896 bytes
Database mounted.
SQL> ALTER DATABASE DATAFILE '/u02/SCRIPTS/test11_data_01.dbf' OFFLINE DROP;

Database altered.

SQL> select file_name||' '||tablespace_name from dba_data_files;

FILE_NAME||''||TABLESPACE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf USERS
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE
/u01/app/oracle/oradata/orcl/apex01.dbf APEX
/u02/SCRIPTS/test11_data_01.dbf TEST11

7 rows selected.

SQL> DROP TABLESPACE TEST11 INCLUDING CONTENTS;

Tablespace dropped.

SQL> select file_name||' '||tablespace_name from dba_data_files;

FILE_NAME||''||TABLESPACE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf USERS
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE
/u01/app/oracle/oradata/orcl/apex01.dbf APEX

Note 1: If the datafile is drop accidentally or taken offline than RMAN we cannot take full database rman backup,As RMAN will be searching for that
file for taking physical backup.
Note 2: I tried this experiment in my competancy server,It is always advisable to have one backup for complete recovery and to avoid data loss and never try such experiment in any development or real time systems.

Hope it helps.

Best regards,

Rafi.

ORA-00845: MEMORY_TARGET not supported on this system

2011-06-06T04:47:00.001-07:00

Hi,
As we know MEMORY_TARGET is a new dynamic parameter added in Oracle 11g for automatic SGA and PGA management.Working with LINUX OS you might face error
ORA-00845.

Error:
ORA-00845: MEMORY_TARGET not supported on this system

The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

For me the first case had happen i.e MEMORY_TARGET=1G,So I decided to increase to 2G,This is because of the below factor:
•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process so we need extra memory or size of this parameter must be high,If you have 2 Databases running in your machine you can keep MEMORY_TARGET=2G.For 3 or 4 Database MEMORY_TARGET=2.5g should
be fine.

Resolving ORA-00845:
As a root user
mount -t tmpfs shmfs -o size=2g /dev/shm

In order to make the settings persistence so that it will affect after
restarting machine add an entry in /etc/fstab similar to the following:
vi /etc/fstab

shmfs /dev/shm tmpfs size=12g 0

[root@node1 /]# mount -t tmpfs shmfs -o size=2g /dev/shm

:wq
Save and quit

Note 1:In the above example I've created the shmfs(shared memory file system) with a size of 2G as that is the size of the buffer cache I am planning to use.
The other elements of the SGA are placed in regular memory,not this shared memory file system, so they should not be included when deciding on the size of the shmfs.
It is recommended to size this slightly bigger than the actual size needed,
Here I've used a 2G shmfs for a 2G buffer cache.

Note 2: The size of memory_target parameter we can grow upto memory_max_target and not beyond memory_max_target.

SQL> show parameter %MEMORY%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1G
memory_target big integer 1G

[root@node1 /]# reboot

Broadcast message from root (pts/3) (Thu May 19 19:28:02 2011):

The system is going down for reboot NOW

Check after reboot:
-------------------

[root@node1 ~]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda6 5952252 4117872 1527140 73% /
/dev/sda7 5080796 4292008 526532 90% /u01
/dev/sda3 6605824 1807356 4457492 29% /u03
/dev/sda2 35550448 26956160 6759264 80% /u02
/dev/sda1 497829 16695 455432 4% /boot
tmpfs 2097152 0 2097152 0% /dev/shm
/dev/sdb1 39381744 35555696 1825556 96% /u04
/dev/sdc1 51605436 1233976 47750056 3% /u05
shmfs 2097152 0 2097152 0% /dev/shm

Check after starting database:
-------------------------------
[oracle@node1 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda6 5.7G 4.0G 1.5G 73% /
/dev/sda7 4.9G 4.1G 515M 90% /u01
/dev/sda3 6.3G 1.8G 4.3G 29% /u03
/dev/sda2 34G 26G 6.5G 80% /u02
/dev/sda1 487M 17M 445M 4% /boot
tmpfs 2.0G 1.4G 714M 66% /dev/shm
/dev/sdb1 38G 34G 1.8G 96% /u04
/dev/sdc1 50G 1.2G 46G 3% /u05
shmfs 2.0G 1.4G 714M 66% /dev/shm

Hope it helps.

Best regards,

Rafi.

******* Rafi ORACLE DBA & APPS DBA Blog*******