Oracle Application DBA Portal

(Re)Securing Oracle Application Server Control

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

If you’re running an Oracle Application Server 10g instance you are probably familiar with Oracle Enterprise Manager Application Server Control. If not, go back to the manual. This is not a how-to on setting it up or using it. If you want to know how to secure it and refresh the certificate when it expires, read on.

Application Server Control is installed with Application Server 10g and typically runs on a port like 1810. By default it uses the non-secure http protocol. Since your whole application server is controlled through this interface, you probably want to secure it. The instructions below will generate a self signed certificate and get your Application Server Control up and running with https.

As usual this post is written for Oracle Application Server 10g on UNIX. Always review the documentation for your release before trying any of these steps.

Securing Application Server Control

Oracle has provided a simple way to secure Application Server Control.

Note: If $ORACLE_HOME/bin is not in your path you will need to provide this path to emctl.

1. Connect to the command line on the application server and set all the appropriate environment variables for your application instance.

2. Run the command emctl stop iasconsole to stop Application Server Control.

3. Run the command emctl secure em to secure Application Server control. This will perform a few steps including generating a self-signed secure certificate.

4. Run emctl start iasconsole to start Application Server Control.

If all goes well you will now be able to connect to your Application Server Control instance on the same port as before but now with the https protocol. In most browsers you will need to specify ‘https://’ in the URL.

Depending on your browser settings you may get a warning when accessing the site that the secure certificate was not issued by a trusted company. That is normal with a self-signed certificate. You can either tell your browser to trust the certificate or simply disregard the warning when it appears.

Renewing the Certificate

By default the certificate created in the steps above will only be good for six months. Once the cert goes stale you will probably get a warning that the certificate date is invalid. You may additionally get some java errors like below.

When this happens you can simply re-secure Application Server Control with the same steps above. This will create a new certificate which will be valid for another six months.

Some Potential Problems

If the certificate has expired you will likely get a java error like this:

IOException in sending Request :: javax.net.ssl.SSLException: SSL handshake failed: X509CertExpiredErr

If this happens simply re-secure Application Server Control with the instructions above.

Sometimes Application Server Control will not shut down properly and you may get an error like this:

IOException in sending Request :: javax.net.ssl.SSLException: SSL handshake failed: SSLIOClosedOverrideGoodbyeKiss

If this happens you will probably have to kill the enterprise manager process (look for a process called emagent) and re-secure again.

oracle, oracle application server, oracle security

Happy Learning
--Sreene

Workflow Notification Mailer Setup

2010-01-29T01:26:00.000-08:00

Workflow Notification Mailer Setup
NOTIFICATION MAILER ARCHITECTURE

The main component of the Oracle Workflow Notification Mailer is the executable
WFMAIL. This is a server side program that queries the database for any pending
notifications. It then dispatches these notifications by calling sendmail for UNIX and the MAPI APIs for Microsoft Windows NT. The notification mailer also queries the local inbox for incoming messages. These messages are validated and then passed to the database for response processing. To configure Workflow Notification Mailer we have to do OS level setup (Sendmail) and Application level setup.

1. OS Level Setup
2. Application level Configuration

1. OS (Oracle Enterprise Linux 4 update 7) level Setup
At Os level we need to do bellow setups before configuring Application level
Sendmail should be installed by default when you install RedHat Linux. If it is not then you need to install the Sendmail RPM’s with the Red Hat distribution
Sendmail RPM’s (In Oracle Enterprise Linux 4 update 7)

Note: By default Oracle Enterprise Linux 4 update 7 have above all RPM’s

A. Configuring Sendmail

Edit the /etc/mail/sendmal.mc file
Look for this bellow line in this file
DAEMON_OPTIONS (`Port=smtp,Addr=127.0.0.1, Name=MTA')dn

Add 1 line with SMTP port Number and Server IP to this above line

DAEMON_OPTIONS (`Port=25,Addr=192.168.1.61, Name=MTA')dnl

Here 25 is SMTP Port Number, 192.168.1.61 is Server IP

After adding his like file look like this

DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')dnl
DAEMON_OPTIONS(`Port=25,Addr=192.168.1.61, Name=MTA')dnl

Save the file

Rebuild /etc/sendmail.cf from the revised /etc/mail/sendmail.mc
$m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf

Restart the sendmail services
$ /etc/init.d/sendmail restart
B.Starting Sendmail
You can use the chkconfig command to get sendmail configured to start at boot time
$chkconfig sendmail on
The services should now be set at the appropriate run levels and then checked to ensure they are correct.

#chkconfig --level 2345 sendmail on

To check run level
#chkconfig --list sendmail
C. Check the sendmail services
$telnet
$telnet 192.168.1.61 25

Now sendmail is working

D. Dovecot IMAP Server
Now that the sendmail server has been setup to allow the sending of emails, we need to configure a means for the user to retrieve any emails that are waiting for them on the server. One of the packages that do this is dovecot, which handles POP and IMAP mailboxes in clear text or with link encryption (POPS and IMAPS); IMAPS is the preferred mail protocol for MUAs.
Install Dovecot RPM if not installed

E: Start the Dovecot services
Start the services from Command

$ /etc/init.d/dovecot restart
Or
Start Dovecot services from Server

Start the dovecot services then save

F.Check the Dovecot services
$telnet 192.168.1.61 143

G. Check the mails are going to your mail ID from Linux command line
$ mail –s “Hi this is from WF Server “ yourID@gmail.com
If u gets this mail to your mail Id It then send mail is working fine
H. Create DISCARD & PROCESS folders
Login to the Application User (testappl)
Under testappl user home it will be one mail folder. Under mail folder you create DISCARD & PROCESS folders

2. Application level Configuration

1. We can configure workflow notification mailer from Oracle Application Manager (OAM)

2. There are two kinds of Notification Outbound & Inbound in Workflow Mailer

3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.

4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.

5. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)

Enable Workflow Agent Listener and Notification Mailer concurrent

programs

Schedule the Workflow Background process, Notification Mailer concurrent requests

Workflow Mailer Service, Workflow Agent Listener Service should be started

Configure Workflow Notification Mailer
Login to Oracle Application Manager
Go to Site Map

1. Click on Next

2. Click on Next

3. Here give Application User name, password and Test Mail Id
Give PROCESS and DISCARD folders (which we have created in application server) name in EMail Processing

4. Click on Next

5. Give Submit Date and time then click on Next

6. Click on Next

7. Here give user name and click on Send Test Message

Click on Next

8. Click on finish

B. STARTUP NOTIFICATION MAILER

The mailer program can be started from command line or through a concurrent
Program called "Notification Mailer". When the notification mailer is started as a concurrent request in Oracle Applications, the OS process is run by the Applications Manager (APPLMGR) user.

To start it from the application, submit the "Notification Mailer" Concurrent request.

To start it from command line on UNIX, execute the following command:

Go to FND_TOP/bin/

$WFMAIL apps/Pass 0 Y $FND_TOP/resource/wfmail.cfg

Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/

Happy Learning
G.Venugopal Achari

Data Dictionary Info

2009-08-03T23:37:00.000-07:00

Data Dictionary Info
Here are some scripts related to Data Dictionary Info .

Dictionary Cache
DATA DICTIONARY CACHE NOTES:

Parameter - Name of the parameter that determines the number of entries in the data dictionary cache.
Gets - Total number of requests for information on the data object.
Getmisses - Number of data requests resulting in cache misses.
% Cache Misses - Miss Ratio
Count - Total number of entries in the cache
Usage - Number of cache entries that contain valid data.

This report shows the DC cache statistics (a part of the shared pool). Whenever the database parses a SQL statement, it scans the text for syntax and semantic correctness. The semantic check requires cross-referencing of the information in the SQL statement and the data dictionary, including the table names, columns-to-date relationships, column names, data types, and security access privileges. To resolve the relationships, Oracle uses the data dictionary cache in the SGA. When the data sought in not in the cache, Oracle executes SQL statements to retrieve the data dictionary information from the SYSTEM tablespace. These statements for data dictionary information represent one type of recursive SQL statement. To increase the size available to the dictionary cache, increase the size of the shared pool area (via the SHARED_POOL_SIZE init.ora parameter.) Missing a get on the data dictionary or shared pool area of the SGA is more costly than missing a get on a data buffer or waiting for a redo buffer. If these parameters look familiar - you are probably recalling them from Oracle v6 (when you tuned these in the init.ora file)

select PARAMETER,
GETS,
GETMISSES,
round(GETMISSES/GETS,2)*100 "% Cache Misses",
COUNT,
USAGE
from v$rowcache
where GETS > 0
order by (GETMISSES/GETS)*100 desc

Latch Gets/Misses
LATCH GET AND MISS NOTES:

Latch Name - Name of the latch
Gets - Number of times obtained wait
Misses - Number of time obtained with wait but failed first try
Gets / Misses % - Ratio of misses to gets
Immediate Gets - Number of times obtained with no wait
Immediate Misses - Number of times failed to get with no wait

select NAME,
GETS,
MISSES,
round(((GETS-MISSES)*100) / GETS , 2) "Gets/Misses %",
IMMEDIATE_GETS,
IMMEDIATE_MISSES
from v$latch
where GETS != 0
or IMMEDIATE_MISSES > 0
order by ((GETS-MISSES) / GETS) desc

Redo Log Buffer

2009-08-03T23:24:00.001-07:00

Redo Log Buffer
Here are some scripts related to Redo Log Buffer .

Contention
REDO LOG CONTENTION NOTES:

Redo Latch Name - Name of the latch
Gets - Number of times gotten wait
Misses - Number of times gotten wait but failed first try
Sleeps - Number of times slept when wanted wait
Immediate Gets - Number of times gotten without wait
Immediate Misses - Number of times failed to get without wait

Heavy access to the redo log buffer can result in contention for redo log prompt buffer latches.

select NAME,
GETS,
MISSES,
SLEEPS,
IMMEDIATE_GETS,
IMMEDIATE_MISSES
from v$latch
where NAME in ('redo allocation','redo copy')

Statistics
REDO BUFFER NOTES:

Parameter - Name of the parameter
Value - Current value for the parameter

select NAME,
VALUE
from v$sysstat
where NAME like 'redo%'
and VALUE > 0

Contention/Locking

2009-08-03T23:20:00.000-07:00

Contention/Locking
Here are some scripts related to Contention/Locking .

Lock Info
LOCK INFORMATION NOTES:

OS User - Name of operating system user
OS PID - Operating system process id
Oracle User - Name of Oracle user
Oracle ID - Oracle session id
Lock Type - Type of lock
Lock Held - Current lock held
Lock Requested - Type of lock requested
Status - Status of object (Blocking, Not Blocking, Global)
Object Owner - Owner of the object
Object Name - Name of the object

select OS_USER_NAME os_user,
PROCESS os_pid,
ORACLE_USERNAME oracle_user,
l.SID oracle_id,
decode(TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', type) lock_type,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', lmode) lock_held,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', request) lock_requested,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) status,
OWNER,
OBJECT_NAME
from v$locked_object lo,
dba_objects do,
v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND l.SID = lo.SESSION_ID

SQL Lock Info
SQL ASSOCIATED WITH LOCK NOTES:

Oracle User - Name of the oracle user
SID - Oracle session id
Serial# - Serial# of the process
Type - Resource type (RW - Row wait enqueue lock, TM - DML enqueue lock, TX - Transaction enqueue lock, UL - User supplied lock)
Held - Type of lock held
Requested - Type of lock requested
ID1 - Resource identifier #1
ID2 - Resource identifier #2
SQL - SQL statement

select sn.USERNAME,
m.SID,
sn.SERIAL#,
m.TYPE,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_type,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_requested,
m.ID1,
m.ID2,
t.SQL_TEXT
from v$session sn,
v$lock m ,
v$sqltext t
where t.ADDRESS = sn.SQL_ADDRESS
and t.HASH_VALUE = sn.SQL_HASH_VALUE
and ((sn.SID = m.SID and m.REQUEST != 0)
or (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in
(select s.ID1, s.ID2
from v$lock S
where REQUEST != 0
and s.ID1 = m.ID1
and s.ID2 = m.ID2)))
order by sn.USERNAME, sn.SID, t.PIECE

Full Table Scans

2009-08-03T23:15:00.000-07:00

Full Table Scans
Here are some scripts related to Full Table Scans .

System Statistics (Table)SYSTEM STATISTICS (TABLE) NOTES:

Statistic Name - Name of the statistic
Bytes - Size

This query provides information on the full table scan activity. If your application is OLTP only, having long full table scans can be an indicator of having missing or incorrect indexes or untuned SQL.
Table fetch by rowid reflect the cumulative number of rows fetched from tables using a TABLE ACCESS BY ROWID operation.
Table fetch continued row reflect the cumulative number of continued rows fetched. This value is incremented when accessing a row that is longer than a block in length and when accessing "migrated" rows. Migrated rows are rows that were relocated from their original location to a new location because of an update that increased their size to the point where they could no longer be accommodated inside their original block. Access to migrated rows will cause this statistic's value to increment only if the access is performed by ROWID. Full table scans of tables that contain migrated rows do not cause this counter to increment.
Table scan blocks gotten reflect the cumulative number of blocks read for full table scans.
Table scans rows gotten reflect the cumulative number of rows read for full table scans.
Table scans (cache partitions) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
Table scans (long scans) indicate a full scan of a table that has > 5 database blocks.
Table scans (rowid ranges) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
Table scans (short scans) indicate a full scan of a table that has <= 5 database blocks.

select NAME,
VALUE
from v$sysstat
where NAME like '%table'

Process Table Scans
PROCESS TABLE SCAN NOTES:

User Process - Name of user process
Long Scans - Full scan of a table that has > 5 database blocks.
Short Scans - Full scan of a table that has <= 5 database blocks.
Row Retrieved - Cumulative number of rows read for full table scans.

select ss.username||'('||se.sid||') ' "User Process",
sum(decode(name,'table scans (short tables)',value)) "Short Scans",
sum(decode(name,'table scans (long tables)', value)) "Long Scans",
sum(decode(name,'table scan rows gotten',value)) "Rows Retreived"
from v$session ss,
v$sesstat se,
v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%')
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '

Process Table Scans (Avg)
PROCESS TABLE SCAN (AVERAGE) NOTES:

User Process - Name of user process
Short Scans - Number of short scans (<= 5 blocks)
Long Scans - Number of long scans (> 5 blocks)
Rows Retrieved - Number of rows retrieved
Long Scans Length - Average long scan length (i.e. full table scan of > 5 blocks)

select ss.username||'('||se.sid||') ' "User Process",
sum(decode(name,'table scans (short tables)',value)) "Short Scans",
sum(decode(name,'table scans (long tables)', value)) "Long Scans",
sum(decode(name,'table scan rows gotten',value)) "Rows Retreived",
round((sum(decode(name,'table scan rows gotten',value)) - (sum(decode(name,'table scans (short tables)',value)) * 5)) / (sum(decode(name,'table scans (long tables)', value))),2) "Long Scans Length"
from v$session ss,
v$sesstat se,
v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%')
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '
having sum(decode(name,'table scans (long tables)', value)) != 0
order by 3 desc

SENDMAIL configuration

2009-06-25T06:03:00.000-07:00

SENDMAIL Configuration tested on RedHat 4 update 5
-------------------------------------------------------------------
Follow the below link:

http://www.owlriver.com/tips/sendmail-tip/

1> Apply the patch sendmail-cf-8.13.1-3.2.el4.i386.rpm which is available in 3rd cd
rpm -Uvh sendmail-cf-8.13.1-3.2.el4.i386.rpm

2> To be able to hand email TO it (on port 25), you will need packages:
rpm -q sendmail-cf m4

3> If not present, use your install media and install them

4> Edit /etc/mail/sendmail.mc with your favorite text editor. Toward the bottom of that
file is this stanza:
dnl This changes sendmail to only listen on the loopback device 127.0.0.1
dnl and not on any other network devices. Comment this out if you want
dnl to accept email over the network.
DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')
... Note that the string "dnl " at the left margin is how comments are added to
sendmail.mc -- this is an artifact of the ancient origins of sendmail and the m4 macro
processor
Make it read as the text says "to accept email over the network":
dnl DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')
(this is the last line quoted in the first stanza, now commented out.)

4. rebuild /etc/sendmail.cf from the revised /etc/mail/sendmail.mc thus:
m4 /etc/mail/sendmail.mc > /etc/sendmail.cf
(This is considered Unix deep magic -- but all it does is have the m4 macro processor use
the sendmail-cf recipies, and make a new well-formed sendmail.cf. Note that lower in the
sendmail.mc file is an option to specify what hostname your mail server uses -- useful for
pretending to be a server with a resolvable name.)
(Note also that /etc/sendmail.cf moves to become /etc/mail/sendmail.cf with sendmail-
8.12.x and so that command will become m4 /etc/mail/sendmail.mc >
/etc/mail/sendmail.cf in later versions. But there are many other changes, including a
separate non-privleged child process.)

5. Tell sendmail to listen all the time (in so-called daemon mode) instead of delivering
just its own local mail queue just once an hour.
Edit /etc/sysconfig/sendmail
and change:
DAEMON=no
to:
DAEMON=yes

6. And restart sendmail
cd /etc/mail
make
service sendmail restart
(Note: The make step is needed in some variants of Red Hat, as the automatic execution
of the make is missing in some releases of the initscript package.)

7. Confirm it locally:
netstat -an | grep 25 | grep tcp
yields:
tcp 0 0 0.0.0.0:25 0.0.0.0:* LISTEN
(which says the host is listening on ALL interfaces for connections ...)
[I undid step 5 and 6 -- and reran this test -- it yields -- nothing -- that it is NOT able to be
connected to externally on port 25 -- and so THAT port is safe from a remote direct
attack.]

8. Test locally (I list YOUR part OUT of []):
telnet localhost 25
[... banner]
HELO LOCALHOST
[... response]
QUIT
It went:
[root@couch herrold]# telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 couch.basement.net ESMTP Sendmail 8.11.6/8.11.6; Sat, 17
Nov 2001 17:10:39 -0500
HELO localhost
250 couch.basement.net Hello
IDENT:+yf0DRAJjMc1ZxKCEIv/Y7hCZQtbC55G@couch.basement.net
[127.0.0.1], pleased to meet you
QUIT
221 2.0.0 couch.basement.net closing connection
Connection closed by foreign host.
$

9. The acid test -- From another host test it (we'll restate the conversation, and hide the
remote end's responses initially for clarity):
telnet couch.basement.net 25
HELO localhost
QUIT
Or more elaborately (I highlight your input in red):
[herrold@router herrold]$ telnet couch.basement.net 25
Trying 172.16.33.101...
Connected to 172.16.33.101.
Escape character is '^]'.
220 couch.basement.net ESMTP Sendmail 8.11.6/8.11.6; Sat, 17
Nov 2001 17:36:05 -0500
HELO localhost
250 couch.basement.net Hello router.basement.net
[172.16.33.2], pleased to meet you
MAIL FROM:
553 5.5.4 godzilla@localhost... Real domain name required for sender address
MAIL FROM:
250 2.1.0 godzilla@microsoft.com... Sender ok
RCPT TO:
250 2.1.5 root@localhost... Recipient ok
DATA
354 Enter mail, end with "." on a line by itself
Linux rules.
.
250 2.0.0 fAHMajY07056 Message accepted for delivery
QUIT
221 2.0.0 couch.basement.net closing connection
Connection closed by foreign host.
[herrold@router herrold]$
.... that was successful -- note that the 'domain must resolve' rule for senders kicked in on
the first part of the "MAIL FROM" offer -- this is an anti-spam feature. [Note the name
look up's working -- I have working residence DHCP and DNS services on Linux ... ].
The X- header is added by procmail.
There is a forward rule in place for root, as recommended in an earlier post; Out at the
end recipient host, we find:
Return-Path:
Received: from couch.basement.net
(dhcp065-024-xx-xx.columbus.rr.com [65.24.xx.xx])
by swampfox.owlriver.com (8.11.6/8.11.6) with ESMTP id
fAHMbGu07271 for ; Sat, 17 Nov
2001 17:37:16 -0500
Received: from localhost (router.basement.net [172.16.33.2])
by couch.basement.net (8.11.6/8.11.6) with SMTP id
fAHMajY07056
for root@localhost; Sat, 17 Nov 2001 17:37:06 -0500
Date: Sat, 17 Nov 2001 17:37:06 -0500
From: godzilla@microsoft.com
Message-Id: <200111172237.fAHMajY07056@couch.basement.net>
X-Loop: herrold@owlriver.com
Linux rules.
Read the received lines from the bottom up --
We forged the sender as "godzilla@microsoft.com" -- spammers do this all the time,
nothing new there; and offered to root@localhost -- The forward rule handed it for
delivery to herrold@owlriver.com. Seems to work fine
As this demonstrated, this is probably not enough to suffice in production. Do NOT
connect this to the outside world -- a bit more needs to be done --- As set, it will receive
mail only with a final (as to the originator's perspective) destination at the localhost --
/etc/mail/access and /etc/mail/local-host-names need attention to receive mail for
handling for others. But again, this is beyond the scope of this presentation.

10> To send a mail
sendmail –v sriasan@gmail.com – use gmail, I had spam issues with yahoo.com
this is to test sendmail
ctrl –d or .
check gmail for the mail

Rollback Segments

2009-06-22T01:59:00.000-07:00

Rollback Segments
Here are some scripts related to Rollback Segments .

Segments
ROLLBACK INFORMATION NOTES:

Segment Name - Name of the rollback segment.
Owner - Owner of the rollback segment.
Tablespace - Name of the tablespace containing the rollback segment.
Segment ID - ID number of the rollback segment.
File ID - ID number of the block containing the segment header.
Block ID - Starting block number of the extent.
Initial Extent - Initial extent size in bytes.
Next Extent - Secondary extent size in bytes.
Min Extents - Minimum number of extents.
Max Extents - Maximum number of extents.
PCT Increase - Percent increase for extent size.
Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.
Instance - Instance this rollback segment belongs to (Parallel Server), or NULL for a single-instance system .

select SEGMENT_NAME,
OWNER,
TABLESPACE_NAME,
SEGMENT_ID,
FILE_ID,
BLOCK_ID,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
INSTANCE_NUM
from dba_rollback_segs
order by SEGMENT_NAME

Transactions
ROLLBACK STATISTIC (TRANSACTION TABLES) NOTES:

Statistic Name - Name of the statistic
Value - Current value

The name of the consistent changes statistic is misleading. It does not indicate the number of updates (or changes), but rather, the number of times a consistent get had to retrieve and "old" version of a block because of updates that occurred after the cursor had been opened. As of Oracle7.3, a more accurate statistic was added. Named data blocks consistent reads - undo records applied; the new statistic gives the actual number of data records applied.
The consistent gets statistic reflects the number of accesses made to the block buffer to retrieve data in a consistent mode. Most accesses to the buffer are done with the consistent get mechanism, which uses the SCN (System Change Number) to make sure the data being read has not changed sine the query was started.
The data blocks consistent reads - undo records applied statistic reflects the number of updates (or changes) applied.

select NAME,
VALUE
from v$sysstat
where name in (
'consistent gets',
'consistent changes',
'transaction tables consistent reads - undo records applied',
'transaction tables consistent read rollbacks',
'data blocks consistent reads - undo records applied',
'no work - consistent read gets',
'cleanouts only - consistent read gets',
'rollbacks only - consistent read gets',
'cleanouts and rollbacks - consistent read gets')
order by NAME

Contention
ROLLBACK CONTENTION NOTES:

Segment Name - Name of the rollback segment.
Seg# - Rollback segment number.
Gets - Number of header gets.
Waits - Number of header waits.
Hit Ratio - Ratio of gets to waits. This should be >= 99%.
Active Transactions - Number of active transactions.
Writes - Number of bytes written to rollback segment.

Hit Ratio should be >= 99% - if not, consider adding additional rollback segments.
Check the system undo header, system undo block, undo header, undo block statistics under "Wait Statistics" for additional information on rollback contention.

select b.NAME,
a.USN seg#,
GETS,
WAITS,
round(((GETS-WAITS)*100)/GETS,2) hit_ratio,
XACTS active_transactions,
WRITES
from v$rollstat a,
v$rollname b
where a.USN = b.USN

Growth
ROLLBACK EXTENDING AND SHRINKAGE NOTES:

Rollback Segment - Name of rollback segment.
Seg# - Rollback segment number.
Size - Size in bytes of the rollback segment.
OptSize - Optimal size of rollback segment.
HWM - High Water Mark of rollback segment size.
Extends - Number of times rollback segment was extended to have a new extent.
Wraps - Number of times rollback segment wraps from one extent to another.
Shrinks - Number of times rollback segment shrank, eliminating one or more additional extents each time.
Average Shrink - Total size of freed extents divided by number of shrinks.
Average Active - Current average size of active extents, where "active" extents have uncommitted transaction data.
Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.

select NAME,
a.USN,
RSSIZE,
OPTSIZE,
HWMSIZE,
EXTENDS,
WRAPS,
SHRINKS,
AVESHRINK,
AVEACTIVE,
STATUS
from v$rollstat a ,
v$rollname b
where a.USN=b.USN
order by NAME

User Information

2009-06-22T01:20:00.000-07:00

User Information
Here are some scripts related to User Information .

User Objects
USER OBJECT NOTES:

Username - Owner of the object(s)
Tabs - Table(s)
Inds - Index(es)
Syns - Synonym(s)
Views - Views(s)
Seqs - Sequence(s)
Procs - Procedure(s)
Funcs - Function(s)
Pkgs - Packages(s)
Trigs - Trigger(s)
Deps - Dependencies

select USERNAME,
count(decode(o.TYPE#, 2,o.OBJ#,'')) Tabs,
count(decode(o.TYPE#, 1,o.OBJ#,'')) Inds,
count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,
count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,
count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,
count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,
count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,
count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,
count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,
count(decode(o.TYPE#,10,o.OBJ#,'')) Deps
from obj$ o,
dba_users u
where u.USER_ID = o.OWNER# (+)
group by USERNAME
order by USERNAME

Invalid Objects
INVALID OBJECT NOTES:

Owner - Owner of the object
Object Type - Type of object
Object Name - Name of the object
Status - Status of the object

select OWNER,
OBJECT_TYPE,
OBJECT_NAME,
STATUS
from dba_objects
where STATUS = 'INVALID'
order by OWNER, OBJECT_TYPE, OBJECT_NAME

Object Modification
OBJECT MODIFICATION NOTES: (Modified in last 7 days)

Owner - Owner of the object
Object Name - Name of the object
Object Type - Type of the object
Last Modified - Last modification date/time
Created - Object creation date/time
Status - Status of the object

select OWNER,
OBJECT_NAME,
OBJECT_TYPE,
to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created,
STATUS
from dba_objects
where (SYSDATE - LAST_DDL_TIME) < 7
order by LAST_DDL_TIME DESC

User Privileges
USER PRIVILEGES NOTES:

Grantee - Grantee name, user or role receiving the grant
Granted Role - Granted role name
Admin - Grant was with the ADMIN option
Default - Role is designated as a DEFAULT ROLE for the user
Privilege - System privilege

select rp.GRANTEE,
GRANTED_ROLE,
rp.ADMIN_OPTION,
DEFAULT_ROLE,
PRIVILEGE
from dba_role_privs rp, dba_sys_privs sp
where rp.GRANTEE = sp.GRANTEE
and rp.GRANTEE not in ('SYS','SYSTEM','DBA')
order by rp.GRANTEE, GRANTED_ROLE, PRIVILEGE

Hit/Miss Ratios

2009-06-22T01:12:00.000-07:00

Hit/Miss Ratios
Here are some scripts related to Hit/Miss Ratios .

Buffer Hit Ratio
BUFFER HIT RATIO NOTES:

Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from v$sysstat

Data Dict Hit Ratio
DATA DICTIONARY HIT RATIO NOTES:

Gets - Total number of requests for information on the data object.
Cache Misses - Number of data requests resulting in cache misses

Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select sum(GETS),
sum(GETMISSES),
round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from v$rowcache

SQL Cache Hit Ratio
SQL CACHE HIT RATIO NOTES:

Pins - The number of times a pin was requested for objects of this namespace.
Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be > 85%

select sum(PINS) Pins,
sum(RELOADS) Reloads,
round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from v$librarycache

Library Cache Miss Ratio
LIBRARY CACHE MISS RATIO NOTES:

Executions - The number of times a pin was requested for objects of this namespace.
Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select sum(PINS) Executions,
sum(RELOADS) cache_misses,
sum(RELOADS) / sum(PINS) miss_ratio
from v$librarycache

Session Statistics

2009-06-19T01:56:00.000-07:00

Session Statistics
Here are some scripts related to Session Statistics .

Session I/O By User
SESSION I/O BY USER NOTES:

Username - Name of the Oracle process user
OS User - Name of the operating system user
PID - Process ID of the session
SID - Session ID of the session
Serial# - Serial# of the session
Physical Reads - Physical reads for the session
Block Gets - Block gets for the session
Consistent Gets - Consistent gets for the session
Block Changes - Block changes for the session
Consistent Changes - Consistent changes for the session

select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME

CPU Usage By Session
CPU USAGE BY SESSION NOTES:

Username - Name of the user
SID - Session id
CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)

select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
VALUE cpu_usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
order by VALUE desc

Resource Usage By User
RESOURCE USAGE BY USER NOTES:

SID - Session ID
Username - Name of the user
Statistic - Name of the statistic
Value - Current value

select ses.SID,
nvl(ses.USERNAME,'ORACLE PROC') username,
sn.NAME statistic,
sest.VALUE
from v$session ses,
v$statname sn,
v$sesstat sest
where ses.SID = sest.SID
and sn.STATISTIC# = sest.STATISTIC#
and sest.VALUE is not null
and sest.VALUE != 0
order by ses.USERNAME, ses.SID, sn.NAME

Session Stats By Session
SESSION STAT NOTES:

Username - Name of the user
SID - Session ID
Statistic - Name of the statistic
Usage - Usage according to Oracle

select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
sn.NAME stastic,
VALUE usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and se.SID = ss.SID
and se.VALUE > 0
order by sn.NAME, se.SID, se.VALUE desc

Cursor Usage By Session
CURSOR USAGE BY SESSION NOTES:

Username - Name of the user
Recursive Calls - Total number of recursive calls
Opened Cursors - Total number of opened cursors
Current Cursors - Number of cursor currently in use

select user_process username,
"Recursive Calls",
"Opened Cursors",
"Current Cursors"
from (
select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and (NAME like '%opened cursors current%'
or NAME like '%recursive calls%'
or NAME like '%opened cursors cumulative%')
and se.SID = ss.SID
and ss.USERNAME is not null
group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order by USER_PROCESS,"Recursive Calls"

User Hit Ratios
USER HIT RATIO NOTES:

Username - Name of the user
Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit ratio should be > 90%

select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
from v$session, v$sess_io
where v$session.SID = v$sess_io.SID
and (CONSISTENT_GETS+BLOCK_GETS) > 0
and USERNAME is not null
order by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS))

Tablespace Information

2009-06-19T00:54:00.000-07:00

Tablespace Information
Here are some scripts related to Tablespace Information .

Information
TABLESPACE INFORMATION NOTES:

Tablespace Name - Name of the tablespace
Initial Extent - Default initial extent size
Next Extent - Default incremental extent size
Min Extents - Default minimum number of extents
Max Extents - Default maximum number of extents
PCT Increase - Default percent increase for extent size
Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)
Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.

select TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
from dba_tablespaces
order by TABLESPACE_NAME

Coalesced Exts
WAIT STATISTIC NOTES:

Tablespace Name - Name of tablespace
Total Extents - Total number of free extents in tablespace
Extents Coalesced - Total number of coalesced free extents in tablespace
% Extents Coalesced - Percentage of coalesced free extents in tablespace
Total Bytes - Total number of free bytes in tablespace
Bytes Coalesced - Total number of coalesced free bytes in tablespace
Total Blocks - Total number of free oracle blocks in tablespace
Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace
% Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace

select TABLESPACE_NAME,
TOTAL_EXTENTS,
EXTENTS_COALESCED,
PERCENT_EXTENTS_COALESCED,
TOTAL_BYTES,
BYTES_COALESCED,
TOTAL_BLOCKS,
BLOCKS_COALESCED,
PERCENT_BLOCKS_COALESCED
from dba_free_space_coalesced
order by TABLESPACE_NAME

Usage
TABLESPACE USAGE NOTES:

Tablespace Name - Name of the tablespace
Bytes Used - Size of the file in bytes
Bytes Free - Size of free space in bytes
Largest - Largest free space in bytes
Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc

Users Default (SYSTEM)
SYSTEM TABLESPACE USAGE NOTES:

Username - Name of the user
Created - User creation date
Profile - Name of resource profile assigned to the user
Default Tablespace - Default tablespace for data objects
Temporary Tablespace - Default tablespace for temporary objects

Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.

select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME

Objects in SYSTEM TS
OBJECTS IN SYSTEM TABLESPACE NOTES:

Owner - Owner of the object
Object Name - Name of object
Object Type - Type of object
Tablespace - Tablespace name
Size - Size (bytes) of object

Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace

select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME

Freespace/Largest Ext
FREE, LARGEST, & INITIAL NOTES:

Tablespace - Name of the tablespace
Total Free Space - Total amount (bytes) of freespace in the tablespace
Largest Free Extent - Largest free extent (bytes) in the tablespace

select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME

Disk I/O, Events, Waits

2009-06-19T00:50:00.000-07:00

Disk I/O, Events, Waits
Here are some scripts related to Disk I/O, Events, Waits .

Datafile I/O
DATAFILE I/O NOTES:

File Name - Datafile name
Physical Reads - Number of physical reads
Reads % - Percentage of physical reads
Physical Writes - Number of physical writes
Writes % - Percentage of physical writes
Total Block I/O's - Number of I/O blocks

Use this report to identify any "hot spots" or I/O contention

select NAME,
PHYRDS "Physical Reads",
round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
PHYWRTS "Physical Writes",
round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
from (
select sum(PHYRDS) PHYS_READS,
sum(PHYWRTS) PHYS_WRTS
from v$filestat
) pd,
v$datafile df,
v$filestat fs
where df.FILE# = fs.FILE#
order by fs.PHYBLKRD+fs.PHYBLKWRT desc

SGA Stats
SGA STAT NOTES:

Statistic Name - Name of the statistic
Bytes - Size

select NAME,
BYTES
from v$sgastat
order by NAME

Sort Stats
SORT NOTES:

Sort Parameter - Name of the sort parameter
Value - Number of sorts

sorts (memory) - The number of sorts small enough to be performed entirely in sort areas without using temporary segments.
sorts (disk) - The number of sorts that were large enough to require the use of temporary segments for sorting.
sorts (rows) - Number of sorted rows

The memory area available for sorting is set via the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE init.ora parameters.

select NAME,
VALUE
from v$sysstat
where NAME like 'sort%'

All Events
SYSTEM EVENT (ALL) NOTES:

Event Name - Name of the event
Total Waits - Total number of waits for the event
Total Timeouts - Total number of timeouts for the event
Time Waited - The total amount of time waited for this event, in hundredths of a second
Average Wait - The average amount of time waited for this event, in hundredths of a second

select EVENT,
TOTAL_WAITS,
TOTAL_TIMEOUTS,
TIME_WAITED,
round(AVERAGE_WAIT,2) "Average Wait"
from v$system_event
order by TOTAL_WAITS

All Statistics
SYSTEM STATISTICS (ALL) NOTES:

Stat# - Number of the statistic
Name - Name of the statistic
Class - Statistic class: 1 (User), 2 (Redo), 4 (Enqueue), 8 (Cache), 16 (OS), 32 (Parallel Server), 64 (SQL), 128 (Debug)
Value - Value of the statistic

select STATISTIC#,
NAME,
CLASS,
VALUE
from v$sysstat

Wait Stats
WAIT STATISTIC NOTES:

Class - Class of block subject to contention
Count - Number of waits by this OPERATION for this CLASS of block
Time -Sum of all wait times for all the waits by this OPERATION for this CLASS of block

Data Blocks - Usually occurs when there are too many modified blocks in the buffer cache; reduce contention by adding DBWR processes.
Free List - May occur if multiple data loading programs run simultaneously.
Segment Header - May occur when may full table scans execute simultaneously with data loading processes; aggravated by the parallel options. Reschedule data loading jobs to reduce contention;
Sort Block - Rarely seen except when the Parallel Query option is used; reduce contention by reducing the degree of parallelism or decreasing the SORT_AREA_SIZE init.ora parameter setting.
Undo Block - Very rarely occurs; may be caused by multiple users updating records in the same data block at a very fast rate; contention can usually be resolved by increasing the PCTFREE of the tables being modified.
Undo Header - May occur if there are not enough rollback segments to support the number of concurrent transactions.

select CLASS,
COUNT,
TIME
from v$waitstat
order by CLASS

General Info

2009-06-19T00:03:00.000-07:00

General Info
Here are some scripts related to general database information .

Database Information
DATABASE INFORMATION NOTES:

Database Name - Name of the database
Created - Date/time the database was created
Log Mode - Archive log mode: NOARCHIVELOG or ARCHIVELOG
Checkpoint Change# - Last SCN checkpointed
Archive Change# - Last SCN archived

select NAME,
CREATED,
LOG_MODE,
CHECKPOINT_CHANGE#,
ARCHIVE_CHANGE#
from v$database

Size
DATAFILE NOTES:

File Name - Name of the datafile
Tablespace - Name of the tablespace
Datafile Size - Size of datafile (bytes)
Bytes Used - Amount of datafile used
Percent Used - Percent of datafile used
Bytes Free - Amount of datafile free

select FILE_NAME,
d.TABLESPACE_NAME,
d.BYTES datafile_size,
nvl(sum(e.BYTES),0) bytes_used,
round(nvl(sum(e.BYTES),0) / (d.BYTES), 4) * 100 percent_used,
d.BYTES - nvl(sum(e.BYTES),0) bytes_free
from DBA_EXTENTS e,
DBA_DATA_FILES d
where d.FILE_ID = e.FILE_ID (+)
group by FILE_NAME,d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS
order by d.TABLESPACE_NAME,d.FILE_ID

Files
DATABASE FILE NOTES:

Filename - Name of the file(s)
Location - Location of the file(s)

select 'Archived Log Directory' "Filename",
value "Location"
from v$parameter
where name = 'log_archive_dest'
UNION
select 'Control Files' "Filename",
value "Location"
from v$parameter
where name = 'control_files'
UNION
select 'Datafile' "Filename",
name "Location"
from v$datafile
UNION
select 'LogFile Member' "Filename",
member "Location"
from v$logfile

init.ora
INIT.ORA PARAMETER NOTES:

Parameter - Init.ora parameter name
Value - Current value
Is Default - Whether the parameter value is the default
Session Modifiable - TRUE=the parameter can be changed with ALTER SESSION / FALSE=the parameter cannot be changed with ALTER SESSION
System Modifiable - IMMEDIATE=the parameter can be changed with ALTER SYSTEM / DEFERRED=the parameter cannot be changed until the next session / FALSE=the parameter cannot be changed with ALTER SYSTEM
Is Modified - Indicates how the parameter was modified. If an ALTER SESSION was performed, the value will be MODIFIED. If an ALTER SYSTEM (which will cause all the currently logged in sessions' values to be modified) was performed the value will be SYS_MODIFIED.

ALWAYS_ANTI_JOIN - Set the parameter to HASH to allow parallel processing of the NOT IN clause (a real resource hog). If you set the parameter to NESTED_LOOPS, the NOT IN is evaluated the same way it was as Oracle7.2 and earlier (which will not please you). MERGE uses the SORT_MERGE algorithm to process the NOT IN, which is faster than NESTED_LOOPS but not as fast as HASH.
DEFAULT: NULL
VALUES: NESTED_LOOPS, MERGE, HASH
COMPATIBLE - This parameter specifies the release with which the Oracle Server must maintain compatibility. Set to the release of Oracle RDBMS that you are currently running to take full advantage of any new features. Allows you to use the current version while at the same time guaranteeing backward compatibility with an earlier release. This is in case it becomes necessary to revert to the earlier release. Some features of the current release may be restricted. Oracle 7.1.5 introduced the ability to bypass the buffer cache (direct reads) for table scans and sorts (COMPATIBLE=7.1.5). Oracle 7.3 introduced temporary tablespaces that improve the performance of sorts and hash joins (COMPATIBLE=7.3.1)
DEFAULT: NULL
VALUES: Any Oracle RDBMS (e.g., Oracle7.2.3)
CONTROL_FILES - Specifies one or more names of control files, separated by commas. Oracle Corporation recommends using multiple files on different devices or mirroring the file at the OS level.
DEFAULT: OS Dependent
VALUES: 1 - 8 filenames
CURSOR_SPACE_FOR_TIME - Turns waiting for application cursors on (TRUE) or off (FALSE). If it is set to TRUE, the database uses more space for cursors to save time. It affects both the shared SQL area and the client's private SQL area. Shared SQL areas are kept pinned in the shared pool when this parameter's value is TRUE. As a result, shared SQL areas are not aged out of the pool as long as there is an open cursor that references them. Because each active cursor's SQL area is present in memory, execution is faster. Because the shared SQL areas never leave memory while they are in use, however, you should set this parameter to TRUE only when the shared pool is large enough to hold all open cursors simultaneously. Setting this parameter to TRUE also retains the private SQL area allocated for each cursor between executes instead of discarding it after cursor execution. This saves cursor allocation and initialization time.
DEFAULT: FALSE
VALUES: TRUE/FALSE
DB_BLOCK_BUFFERS - Sets the size, in database blocks, of the data block buffer cache in the SGA. The larger the data block buffer cache is, the more memory will be available for sharing data already in memory amoung users - reducing the need for physical reads. You can determine the effectiveness of the data block buffer cache by measuring the hit ratio of the database.
DEFAULT: 32 Buffers
VALUES: 4 to unlimited
DB_BLOCK_SIZE - Defined when the database is created; it determines the size of each block within the database. You CANNOT change the block size of an existing database; the only method available for increasing the block size is to perform a full database Export, recreate the database with a different DB_BLOCK_SIZE value, and Import the database. In most environments, the default value for DB_BLOCK_SIZE is 2048 bytes (2KB). If you operating environment permits, you should increase the value to 4KB, 8KB, or higher. The performance gains obtained by using a larger block size are significant for both OLTP and batch applications. In general, each doubling of the database block size will reduce the time required for I/O-intensive batch operations be around 40 percent. As the database block size increases, your overall memory requirements may increase.
DEFAULT: OS Dependent
VALUES: 1024-32768
DB_FILE_MULTIBLOCK_READ_COUNT parameter helps determine how many blocks are read at a time by the database during full table scans. You should set the DB_BLOCK_MULTIBLOCK_READ_COUNT parameter to a value that takes the greatest advantage of the operating system's buffer during reads. For example, suppose the OS buffer available for reads is 64KB in size. If your database block size is 2KB, you should set DB_FILE_MULTIBLOCK_READ_COUNT to 32; if the block size is 4KB, set DB_FILE_MULTIBLOCK_READ_COUNT to 16. In some operating systems, the available buffer size is configurable.
DEFAULT: OS Dependent but usually a function of DB_BLOCK_BUFFERS and PROCESSES
VALUES: OS Dependent (normally 1 to either the DB_BLOCK_BUFFERS / 4 or the OS maximum I/O size / DB_BLOCK_SIZE)
DB_FILE_SIMULTANEOUS_WRITES - Number of write batches written by the database writers. If you are experiencing problems getting buffers written to your disks during checkpoints, try increasing the value. It is applicable only to the operation systems that support more than one write to your disk devices.
DEFAULT: 4
VALUES: 1 to 24
DB_WRITERS - Once the database block size and memory areas are properly established, tune the way in which Oracle writes data from memory. If your OS permits using multiple DBWR processes, set a value greater than '1' for the DB_WRITERS parameter. If you start more than on DBWR process, you may be able to reduce contention for blocks within the database block buffer cache. If there is only one DBWR process available, it becomes a possible bottleneck during I/O operations, even if the data is distributed among multiple devices.
ENQUEUE_RESOURCES - An enqueue is a sophisticated locking mechanism which permits several concurrent processes to share known resources to varying degrees. Any object which can be used concurrently can be protected with enqueues. For example, Oracle allows varying levels of sharing on tables: two processes can lock a table in share mode or in share update mode.
DEFAULT: Derived
VALUES: 10 - 65535
HASH_AREA_SIZE - This parameter specifies the maximum amount of memory, in bytes, to be used for the hash join. If this parameter is not set, its value defaults to twice the value of the SORT_AREA_SIZE parameter. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: 2 times the value of SORT_AREA_SIZE
VALUES: Any integer
HASH_JOIN_ENABLED - This parameter enables or disables the hash join feature. Set this parameter to TRUE to use hash joins. Set this parameter to FALSE to disable hash joins. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: TRUE
VALUES: TRUE/FALSE
HASH_MULTIBLOCK_IO_COUNT - This parameter specifies how many blocks a hash join reads and writes at once. When operating in multi-threaded server mode, however, this parameter is ignored (that is, the default value of 1 is used even if you set the parameter to another value). The value of DB_BLOCK_SIZE multiplied by the value of HASH_MULTIBLOCK_IO_COUNT should be less than 64 KB. This parameter strongly affects performance because it controls the number of partitions into which the input is divided.
DEFAULT: 1
VALUES: 1 - (65,536/DB_BLOCK_SIZE)
LOG_ARCHIVE_BUFFER_SIZE - The size of each archival buffer, in redo log blocks (operating system blocks). The default should be adequate for most applications. This parameter, with LOG_ARCHIVE_BUFFERS, can tune archiving so that it runs as fast as necessary, but not so fast that it reduces system performance.
DEFAULT: OS Dependent
VALUES: 1 - OS Dependent (in O/S blocks)
LOG_ARCHIVE_DEST - Applicable only if using the redo log in ARCHIVELOG mode. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported on all operating systems.) The value cannot be a raw partition. To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the Server Manager command ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination.
DEFAULT: OS Dependent
VALUES: Any valid path or device name, except raw partitions
LOG_ARCHIVE_START - Applicable only when you use the redo log in ARCHIVELOG mode, LOG_ARCHIVE_START indicates whether archiving should be automatic or manual when the instance starts up. TRUE indicates that archiving is automatic. FALSE indicates that the DBA will archive filled redo log files manually. (The Server Manager command ARCHIVE LOG START or STOP overrides this parameter.) In ARCHIVELOG mode, if all online redo log files fill without being archived, an error message is issued, and instance operations are suspended until the necessary archiving is performed. This delay is more likely if you use manual archiving. You can reduce its likelihood by increasing the number of online redo log files.
DEFAULT: FALSE
VALUES: TRUE/FALSE
LOG_BUFFER Sets the size, in bytes, of the redo log buffer area in the SGA. The default is set to four times the maximum database block size for the OS. For an OLTP application in which many users perform transactions, the LOG_BUFFER parameter needs to be increased beyond its default value. If the 'redo log space requests' statistic in V$SYSSTAT is non-zero, you should increase LOG BUFFER to support the transaction load without forcing transactions to wait for accesses to the redo log buffer.
DEFAULT: OS Dependent
VALUES: OS Dependent
LOG_CHECKPOINT_INTERVAL - The number of newly filled redo log file blocks needed to trigger a checkpoint. Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. If the value exceeds the actual redo log file size, checkpoints occur only when switching logs. The number of times DBWR has been notified to do a checkpoint for a given instance is shown in the cache statistic dbwr checkpoints, which is displayed in the System Statistics Monitor of the Server Manager. Additional cache statistics include background checkpoints started and background checkpoints completed.
DEFAULT: OS Dependent
VALUES: Unlimited (OS blocks, not database blocks)
LOG_SIMULTANEOUS_COPIES - The maximum number of redo buffer copy latches available to write log entries simultaneously. For good performance, you can have up to twice as many redo copy latches as CPUs. For a single-processor system, set to zero so that all log entries are copied on the redo allocation latch. If this parameter is set to 0, redo copy latches are turned off, and the parameters LOG_ENTRY_PREBUILD_THRESHOLD and LOG_SMALL_ENTRY_MAX_SIZE are ignored. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: CPU_COUNT
VALUES: 0 - unlimited
OPTIMIZER_MODE - When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.) You can set the goal for cost-based optimization by setting this parameter to FIRST_ROWS or ALL_ROWS. FIRST_ROWS causes the optimizer to choose execution plans that minimize response time. ALL_ROWS causes the optimizer to choose execution plans that minimize total execution time. The goal of cost-based optimization can also be set within a session by using ALTER SESSION SET OPTIMIZER_MODE.
DEFAULT: CHOOSE
VALUES: RULE/CHOOSE/FIRST_ROWS/ALL_ROWS
PARALLEL_MAX_SERVERS - Each instance must either have a value of zero or the same value as the other instances. Maximum number of query servers or parallel recovery processes for an instance.
DEFAULT: OS Dependent
VALUES: 0 - 256
PARALLEL_MIN_SERVERS - Minimum number of query server processes for an instance. This is also the number of query server processes Oracle creates when the instance is started.
DEFAULT: 0
General Info: 0 - PARALLEL_MAX_SERVERS
General Info - For a multiple-process operation, this parameter specifies the maximum number of operating system user processes that can simultaneously connect to an Oracle7 Server. Should include up to 6 for the background processes (or more if GC_LCK_PROCS is non-zero or if you use the dispatcher configuration) plus one for login; so a value of 20 would permit 13 or 14 concurrent users. The default values of DB_FILE_MULTIBLOCK_READ_COUNT and SESSIONS are derived from PROCESSES. If you alter the value of PROCESSES, you may want to adjust the values of these derived parameters.
DEFAULT: 25
General Info: 6 - OS Dependent
ROLLBACK_SEGMENTS - One or more rollback segments to allocate by name to this instance. If ROLLBACK_SEGMENTS is not overridden, an instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated from the ratio TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT).
DEFAULT: NULL (the instance uses public rollback segments by default if you do not specify this parameter
VALUES: Any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM
ROW_CACHE_CURSORS - The number of cached recursive cursors used by the row cache manager for selecting rows from the data dictionary. The default value is sufficient for most systems.
DEFAULT: 10
VALUES: 10 - 3300
SHARED_POOL_RESERVED_MIN_ALLOC - This parameter controls allocation of reserved memory. Memory allocations larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle Server will allow fewer allocations from the reserved list and will request more memory from the shared pool list.
DEFAULT: 5000
VALUES: 5000 - SHARED_POOL_RESERVED_SIZE (in bytes)
SHARED_POOL_RESERVED_SIZE - This parameter controls the amount of SHARED_POOL_SIZE reserved for large allocations. SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC to create a reserved list. The default value of 0 represents no reserved shared pool area. Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. The amount of operating system memory, however, may constrain the size of the shared pool. In general, you should set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. For most systems, this value will be sufficient if you have already tuned the shared pool.
DEFAULT: 0
VALUES: From SHARED_POOL_RESERVED_MIN_ALLOC to one half of SHARED_POOL_SIZE (in bytes)
SHARED_POOL_SIZE - Sets the size, in bytes, of the shared pool in the SGA. If your application is OLTP-oriented, and you use packages and other procedural objects, you'll need a large shared SQL area. In environments using a large number of procedural objects, the size of your shared pool may exceed your data block buffer cache. If you have many users, you should increase the SHARED_POOL_SIZE parameter everytime you increase the DB_BLOCK_BUFFERS parameter.
DEFAULT: 3,500,000
VALUES: 300 Kbytes - OS Dependent
SORT_AREA_RETAINED_SIZE - This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory retained after a sort. This memory is released back to the PGA, not to the operating system, after the last row is fetched from the sort space. If a sort requires more memory, a temporary segment is allocated and the sort becomes an external (disk) sort. The maximum amount of memory to use for the sort is then specified by SORT_AREA_SIZE instead of by this parameter. Larger values permit more sorts to be performed in memory. However, multiple sort spaces of this size may be allocated. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, though, additional concurrent sorts are required. Each sort occurs in its own memory area, as specified by SORT_AREA_RETAINED_SIZE.
DEFAULT: The value of SORT_AREA_SIZE
VALUES: From the value equivalent to one database block to the value of SORT_AREA_SIZE
SORT_AREA_SIZE - This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory to use for a sort. After the sort is complete and all that remains to do is to fetch the rows out, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is fetched out, all memory is freed. The memory is released back to the PGA, not to the operating system. Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never exist; there is only one memory area of SORT_AREA_SIZE for each user process at any time. The default is usually adequate for most database operations. Only if very large indexes are created might you want to adjust this parameter. For example, if one process is doing all database access, as in a full database import, then an increased value for this parameter may speed the import, particularly the CREATE INDEX statements.
DEFAULT: OS Dependent
VALUES: The value equivalent to two database blocks (minimum)
SORT_DIRECT_WRITES - SORT_DIRECT_WRITES can improve sort performance if memory and temporary space are abundant on your system. When set to the default value of AUTO, and if the value of SORT_AREA_SIZE is greater than ten times the buffer size, SORT_DIRECT_WRITES automatically configures the SORT_WRITE_BUFFER_SIZE and SORT_WRITE_BUFFERS parameters. When SORT_DIRECT_WRITES is in AUTO mode, SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE have no effect. When SORT_DIRECT_WRITES is set to TRUE, each sort allocates additional buffers in memory to write directly to disk. When SORT_DIRECT_WRITES is set to FALSE, the sorts that write to disk write through the buffer cache.
DEFAULT: AUTO
VALUES: AUTO/TRUE/FALSE
SORT_READ_FAC - SORT_READ_FAC is a unitless ratio that describes the amount of time to read a single database block divided by the block transfer rate.
DEFAULT: OS Dependent
SORT_WRITE_BUFFER_SIZE - This parameter sets the size of the sort buffer when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
DEFAULT: 32768
VALUES: Any integer
SORT_WRITE_BUFFERS - This parameter sets the number of sort buffers when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
DEFAULT: 2
VALUES: Any integer
SQL_TRACE - Disables or enables the SQL trace facility. Setting this parameter to TRUE provides information on tuning that you can use to improve performance. Because the SQL trace facility causes system overhead, you should run the database with the value TRUE only for the purpose of collecting statistics. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: FALSE
VALUES: TRUE/FALSE
TIMED_STATISTICS - By default (when set to FALSE), the Server Manager statistics related to time (from the buffer manager) always are zero and the Server can avoid the overhead of requesting the time from the operating system. To turn on statistics, set the value to TRUE. Should normally be set to FALSE.
DEFAULT: FALSE
VALUES: TRUE/FALSE
USER_DUMP_DEST - The pathname for a directory where the server will write debugging trace files on behalf of a user process.
DEFAULT: OS Dependent
VALUES: Valid local pathname, directory, or disk

select NAME,
VALUE,
ISDEFAULT,
ISSES_MODIFIABLE,
ISMODIFIED
from v$parameter
order by NAME

License
LICENSE INFORMATION NOTES:

Sessions Max - Maximum number of concurrent user sessions allowed for the instance.
Sessions Warn - Warning limit for concurrent user sessions for the instance.
Sessions Current - Current number of concurrent user sessions.
Sessions Highwater - Highest number of concurrent user sessions sine the instance started.
Users Max - Maximum number of named users allowed for the database.

select SESSIONS_MAX,
SESSIONS_WARNING,
SESSIONS_CURRENT,
SESSIONS_HIGHWATER,
USERS_MAX
from v$license

Versions
DATABASE PRODUCT AND VERSION NOTES:

Version number of core library components in the Oracle Server

select BANNER product_versions
from v$version

Migrating Oracle10g DB to ASM

2009-05-27T01:17:00.000-07:00

Step By Step Instructions on Migrating Oracle10g Database to Automatic Storage management (ASM)

Disable Block change tracking:

SQL> select * from v$block_change_tracking;

STATUS
----------
FILENAME
--------------------------------------------------------------------------------
BYTES
----------

DISABLED

If not disabled then, disble using this command.

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL>

Shutdown Database Cleanly:

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

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Create pfile and add/modify the below parameters:

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 12:17:50 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create pfile from spfile;

File created.

Modify pfile with these parameters:

I have already created 2 ASM diskgroups DATA and FLASH.

*.control_files=(+DATA, +FLASH)
*.db_recovery_file_dest=+FLASH
*.db_recovery_file_dest_size=2147483648
*.db_create_file_dest=+DATA
*.db_create_online_log_dest_1=+FLASH
*.db_create_online_log_dest_2=+DATA -- optional if you want another online redo logs dest.

Create spfile back from modified pfile:

PS: take a copy of original spfile before you overwrite spfile using below command.

SQL> create spfile from pfile;

File created.

SQL> exit

Disconnected

Copy Database to ASM diskgroups using rman:

(1) start the instance on NOMOUNT state
(2) copy the controlfile from old location to ASM usin "resrore" rman command
(3) mount the database
(4) copy the datafiles to ASM disk group using rman "BACKUP AS COPY DATABASE" command
(5) Switch database to COPY and open the database.

[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 10:03:10 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes

RMAN> restore controlfile from '/home/oracle/oradata/db10g/control01.ctl';

Starting restore at 21-JUL-06

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=+DATA/db10g/controlfile/backup.256.596369129

output filename=+FLASH/db10g/controlfile/backup.256.596369131

Finished restore at 21-JUL-06

RMAN> startup mount

database is already started

database mounted

released channel: ORA_DISK_1

RMAN> configure device type disk parallelism 4;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 21-JUL-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=151 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=150 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=149 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/home/oracle/oradata/db10g/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile fno=00003 name=/home/oracle/oradata/db10g/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile fno=00002 name=/home/oracle/oradata/db10g/undotbs01.dbf
channel ORA_DISK_4: starting datafile copy
input datafile fno=00004 name=/home/oracle/oradata/db10g/users01.dbf
output filename=+DATA/db10g/datafile/undotbs1.259.596369341 tag=TAG20060721T100858 recid=2 stamp=596369352
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_3: starting datafile copy
copying current control file
output filename=+DATA/db10g/datafile/users.260.596369341 tag=TAG20060721T100858 recid=1 stamp=596369350
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:20
channel ORA_DISK_4: starting full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
output filename=+DATA/db10g/controlfile/backup.261.596369361 tag=TAG20060721T100858 recid=3 stamp=596369364
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:06
including current SPFILE in backupset
channel ORA_DISK_4: starting piece 1 at 21-JUL-06
channel ORA_DISK_4: finished piece 1 at 21-JUL-06
piece handle=+DATA/db10g/backupset/2006_07_21/nnsnf0_tag20060721t100858_0.262.596369369 tag=TAG20060721T100858 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:10
output filename=+DATA/db10g/datafile/sysaux.258.596369341 tag=TAG20060721T100858 recid=4 stamp=596369390
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:05
output filename=+DATA/db10g/datafile/system.257.596369339 tag=TAG20060721T100858 recid=5 stamp=596369414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:21
Finished backup at 21-JUL-06

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/db10g/datafile/system.257.596369339"
datafile 2 switched to datafile copy "+DATA/db10g/datafile/undotbs1.259.596369341"
datafile 3 switched to datafile copy "+DATA/db10g/datafile/sysaux.258.596369341"
datafile 4 switched to datafile copy "+DATA/db10g/datafile/users.260.596369341"
RMAN> alter database open;

database opened

RMAN> exit

Recovery Manager complete.

Migrate tempfile to ASM:

RMAN does not migrate the tempfile as part of the BACKUP AS COPY and SWITCH command becuase the tempfile is not listed in controlfile.
The tempfile has to be manually migrated to ASM.

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 10:12:42 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.

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

SQL> select name, bytes from v$tempfile;

NAME
--------------------------------------------------------------------------------
BYTES
----------

/home/oracle/oradata/db10g/temp01.dbf
20971520

SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace temp1 including contents;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------

+DATA/db10g/tempfile/temp.264.596370217

Migrate and drop the old Online Redo Logs to ASM:

Run the below procedure to migrate the redo logs to ASM. This program is taken from OTN/metalink.

SQL> declare
cursor orlc is
select lf.member, l.bytes
from v$log l, v$logfile lf
where l.group# = lf.group# and
lf.type = 'ONLINE'
order by l.thread#, l.sequence#;
type numTab_t is table of number index by binary_integer;
type charTab_t is table of varchar2(1024) index by binary_integer;
byteslist numTab_t; namelist charTab_t;
procedure migrateorlfile(name IN varchar2, bytes IN number) is
retry number;
stmt varchar2(1024);
als varchar2(1024) := 'alter system switch logfile';
begin
select count(*) into retry from v$logfile;
stmt := 'alter database add logfile size ' || bytes;
execute immediate stmt;
stmt := 'alter database drop logfile ''' || name || '''';
for i in 1..retry loop
begin execute immediate stmt;
exit;
exception
when others then
if i > retry then raise;
end if;
execute immediate als;
end;
end loop;
end;
begin
open orlc;
fetch orlc bulk collect into namelist, byteslist;
close orlc;
for i in 1..namelist.count loop migrateorlfile(namelist(i), byteslist(i));
end loop;
end;

/

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

PL/SQL procedure successfully completed.
SQL> SQL>

SQL>

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------

+FLASH/db10g/onlinelog/group_3.259.596373299
+FLASH/db10g/onlinelog/group_2.258.596373295
/home/oracle/oradata/db10g/redo01.log
+FLASH/db10g/onlinelog/group_4.257.596373293

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

Re-Execute the same script again in order to migrate the remaining ones.

SQL> declare
cursor orlc is
select lf.member, l.bytes
from v$log l, v$logfile lf
where l.group# = lf.group# and
lf.type = 'ONLINE'
order by l.thread#, l.sequence#;
type numTab_t is table of number index by binary_integer;
type charTab_t is table of varchar2(1024) index by binary_integer;
byteslist numTab_t; namelist charTab_t;
procedure migrateorlfile(name IN varchar2, bytes IN number) is
retry number;
stmt varchar2(1024);
als varchar2(1024) := 'alter system switch logfile';
begin
select count(*) into retry from v$logfile;
stmt := 'alter database add logfile size ' || bytes;
execute immediate stmt;
stmt := 'alter database drop logfile ''' || name || '''';
for i in 1..retry loop
begin execute immediate stmt;
exit;
exception
when others then
if i > retry then raise;
end if;
execute immediate als;
end;
end loop;
end;
begin
open orlc;
fetch orlc bulk collect into namelist, byteslist;
close orlc;
for i in 1..namelist.count loop migrateorlfile(namelist(i), byteslist(i));
end loop;
end;

/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

PL/SQL procedure successfully completed.

SQL> select member from v$logfile;

MEMBER

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

+FLASH/db10g/onlinelog/group_3.259.596373619
+FLASH/db10g/onlinelog/group_2.258.596373615
+FLASH/db10g/onlinelog/group_1.261.596373613
+FLASH/db10g/onlinelog/group_4.257.596373293
+FLASH/db10g/onlinelog/group_5.260.596373609

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

DELETE THE OLD DATAFILES USING RMAN.

This way, it will also clear out the datafiles entry from controlfile.

[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 11:22:33 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target

connected to target database: DB10G (DBID=4283639931)

RMAN> run {

2> DELETE COPY OF DATABASE;

3> }

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=151 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=138 devtype=DISK

List of Datafile Copies

Key File S Completion Time Ckp SCN Ckp Time Name

------- ---- - --------------- ---------- --------------- ----
6 1 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/system01.dbf
7 2 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/undotbs01.dbf
8 3 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/sysaux01.dbf
9 4 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/users01.dbf

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/system01.dbf recid=6 stamp=596369439
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/undotbs01.dbf recid=7 stamp=596369439
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/sysaux01.dbf recid=8 stamp=596369440
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/users01.dbf recid=9 stamp=596369440
Deleted 4 objects

RMAN> exit
Recovery Manager complete.

REMOVE THE OLD ONLINE REDO LOGS FILES PHYSICALLY:

[oracle@node1-pub oracle]$ rm /home/oracle/oradata/db10g/redo*.log
[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 11:29:56 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Enable the block change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Database altered.
SQL>

Install Oracle 11g RAC On Linux

2009-05-26T06:33:00.000-07:00

Step By Step Instructions on Installing Oracle 11g Clusterware Software (11.1.0.6) 32-bit on CentOS EL 4 Update 5 x86.

This document explains the step by step process of installing Oracle 11g R (11.1.0.6) Clusterware Software.

Installing Oracle11g (11.1.0.6) Clusterware Software:
Task List:

Setting Up oracle user Environment
Running OUI (oracle Universal Installer) to install 10g RAC Clusterware
Verify CRS status.
Verify Nodeapps status

Setting Up Oracle Environment:

Add the below lines into the .bash_profile under the oracle home directory to set the CRS_HOME in the session.

export CRS_HOME=/u01/app/crs

Running OUI (Oracle Universal Installer) to install Oracle Clusterware:
Complete the following steps to install Oracle Clusterware on your cluster.
You need to run the runInstaller from ONLY ONE node (any single node in the cluster).

Start the runInstaller command as oracle user from any one node When OUI displays the Welcome page, click Next

Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified

Can't connect to X11 window server using ':0.0' as the value of the DISPLAY variable.

If you get the above error, please execute the below command as root and then start the runInstaller by connecting as oracle.

[root@node1-pub ~]# xhost +
access control disabled, clients can connect from any host
[root@node1-pub ~]# su - oracle
[oracle@node1-pub ~]$ /mnt/cdrom/runInstaller

CLICK Next

CLICK Next

CLICK Next

At this step, you shuld not receive any error. If you have configured the Pre-Installation
steps correctly, then you will not get any errors. I get one warning here as you can see which
is complaining about the low memory than required. I had only 512 MB ram and the required memory
is 1GB but I would not worry about this warning and will check the status box.

CLICK Next

I have entered the fully qualified name for the public hostname and vip hostname. If you do not have registerd domain (like hingu.org)
then, you can simply enter the nodename without having domain name appended to that.
For example node1-pub instead of node1-pub.hingu.net

CLICK Next

Check whether the interface has correct subnetmask and type associated to it. If you have configured the
network for all the nodes correctly as explained in Pre-Installation task, then you would not get any
error message at this step.

CLICK Next

Enter the filename and location (mount point) for the OCR file. In the Pre-Instalation steps, I have configured
ocfs for this file to store. I have used the same mount point (/u02/oradata/ocr) to store them.
I have chosen the External redundancy just for exteriment purpose. On production server, You make sure that
you have one extra mountpoint created on separate physical device to store the morror file
to avoid SPF (Single Point Of Failure).

CLICK Next

Use the same mount point as OCR file and enter the filename you want for Voting Disk file.
If you choose the External Redundancy, then you need to mention only one location.
CLICK Next

CLICK Next

When you execute the above scripts on all the nodes, you should get the below output.

CLICK Next

At the below step, the Oracle Cluster Verification Utility gets failed becuase CentOS is not a certified
Linux OS for the 11g RAC installation. Simply Ignore this error and continue.

CLICK Exit

Verifying CRS status:

The below commands can be used to verify the CRS status.

crsctl check crs <<-- for the local node
crsctl check cluster <<-- for remote nodes in the cluster

[root@node1-pub ~]# crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
[root@node1-pub ~]#

crsctl check cluster <<-- for remote nodes in the cluster

For this command to run, CSS needs to be running on the local node.
The "ONLINE" status for remote node says that CSS is running on that node.
When CSS is down on the remote node, the status of "OFFLINE" is displayed for that node.

[root@node1-pub ~]# crsctl check cluster
node1-pub ONLINE
node2-pub ONLINE

Verifying Nodeapps Status:

Now, verify that the nodeapps are configured and running by executing the below command.

[oracle@node2-pub dbs]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....pub.gsd application ONLINE ONLINE node1-pub
ora....pub.ons application ONLINE ONLINE node1-pub
ora....pub.vip application ONLINE ONLINE node1-pub
ora....pub.gsd application ONLINE ONLINE node2-pub
ora....pub.ons application ONLINE ONLINE node2-pub
ora....pub.vip application ONLINE ONLINE node2-pub

I can also get the same info using below series of command.

srvctl config nodeapps -n node1-pub
srvctl config nodeapps -n node2-pub
srvctl status nodeapps -n node1-pub
srvctl status nodeapps -n node2-pub

[root@node1-pub ~]# srvctl config nodeapps -n node1-pub
VIP exists.: /node1-vip/216.160.37.153/255.255.255.248/eth0
GSD exists.
ONS daemon exists.
Listener exists.
[root@node1-pub ~]# srvctl config nodeapps -n node2-pub
VIP exists.: /node2-vip/216.160.37.157/255.255.255.248/eth0
GSD exists.
ONS daemon exists.
Listener exists.
[root@node1-pub ~]# srvctl status nodeapps -n node2-pub
VIP is running on node: node2-pub
GSD is running on node: node2-pub
Listener is running on node: node2-pub
ONS daemon is running on node: node2-pub
[root@node1-pub ~]# srvctl status nodeapps -n node1-pub
VIP is running on node: node1-pub
GSD is running on node: node1-pub
Listener is running on node: node1-pub
ONS daemon is running on node: node1-pub
[root@node1-pub ~]#

Sessions RAC

2009-05-19T03:24:00.000-07:00

Hello Everyone!!!

Displays information on all database sessions for whole RAC.
-- ----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
s.inst_id,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM gv$session s,
gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
ORDER BY s.username, s.osuser;

SET PAGESIZE 14
-- ----------------------------------------------------------------------------------
Happy Learning,
--Sreene

Monitor Memory RAC

2009-05-19T03:19:00.000-07:00

Hello Everyone!!!

Displays memory allocations for the current database sessions for the whole RAC.
-- ----------------------------------------------------------------------------------
SET LINESIZE 200

COLUMN username FORMAT A20
COLUMN module FORMAT A20

SELECT a.inst_id,
NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM gv$session a,
gv$sesstat b,
gv$statname c
WHERE a.sid = b.sid
AND a.inst_id = b.inst_id
AND b.statistic# = c.statistic#
AND b.inst_id = c.inst_id
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC;

-- ----------------------------------------------------------------------------------
Happy Learning,
--Sreene

Lists all locked objects for whole RAC

2009-05-19T02:01:00.000-07:00

Hello Everyone!!!

Lists all locked objects for whole RAC.
-- ---------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT b.inst_id,
b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14
SET VERIFY ON
-- ----------------------------------------------------------------------------------
Happy Learning,
--Sreene

Concurrent Managers

2009-05-06T00:48:00.000-07:00

Hello Everyone!!!

Concurrent Managers

Concurrent Manager:
It is a component of your applications concurrent processing facility that monitor and run time consuming tasks without tying up your terminal. Whenever you submit a request, such as running a report, a concurrent manager does the work for you, letting you perform many tasks simultaneously. It is a daemon program running continuously in the background for e.g. a Unix shell script

Concurrent Process:
A task in the process of completing. Each time you submit a task, a new concurrent process is created. A concurrent process runs simultaneously with other concurrent processes (and other activities on your computer) to help you complete multiple tasks at once with no interruptions to your terminal.

Concurrent Queue:
A list of concurrent requests awaiting completion by a concurrent manager. Each concurrent manager has a queue of requests waiting in line.

Concurrent Request:
A request to complete a task for you. You issue a request whenever you submit a task, such as running a report. Once you submit a task, the concurrent manager automatically takes over for you, completing your request without further involvement from you, or interruption to your work. Concurrent managers process your request according to when you submit the request and the priority you assign to your request. If you do not assign a priority to your request, the application automatically prioritizes the request for you.

Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one them has a failure.

Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.

Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.

Identifying Oracle Process ID and Dictionaries

The main tables or the data dictionaries of Oracle Apps are the following, using which we can query to find the status of any of the Concurrent Requests or Programs.
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.

In order to identify a Request that is running in the Oracle Application side, we can query the FND_CONCURRENT_REQUESTS table or the V$SESSION and the V$PROCESS table. While doing this, we need the Process Id at the OS level, the Process ID at the Oracle database tier and also the Process ID at the Application tier.

Identifying a Running Process
Session ID :
Session ID is the Identification number of the Process at the Database Side. The value for this field can be obtained by querying the V$SESSION view. This is given by the field SID.

Process ID and SPID:
The Process ID or the PID is the number of the Process at the OS level and the SPID is the identification number for the Process at the Application level. These values can be obtained by querying the V$PROCESS view.

Request ID:
Request ID is the ID of the Concurrent request or the Program that has been submitted at the Application tier. This field is denoted by the REQUEST_ID field and can be obtained by querying the FND_CONCURRENT_REQUESTS view.

PHASE_CODE and STATUS_CODE :
These two columns gives the value of the Phase and the Status Code of the concurrent request that is running in the Application end. Some of the different Status Code and Phase code and their meanings are as follows:
Phase code
C - Completed
C - Completed
P - pending
R - Running

Status code
D - Cancelled
C - Normal
Q - standby
W - paused

We can get the Oracle Process ID and the OS Process ID for a particular request that is running in the Application end by querying the FND_CONCURRENT_REQUESTS View also. The fields ORACLE_PROCESS_ID , and OS_PROCESS_ID represent the same in the FND_CONCURRENT_REQUESTS dictionary view.

E.g. In order to kill a particular request that is running, we can obtain the Process ID and other details using the below mentioned SQL query .. :

SQL> select Phase_code, status_code,REQUEST_ID,ORACLE_ID, ORACLE_PROCESS_ID, OS_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=;

After identifying the Process ID and the OS ID, we can kill the particular request or the Process using the normal OS command. E.g.. KILL .

Happy Learning
--Chandru

Backup Recovery

2009-04-29T04:46:00.000-07:00

Hello Everyone!!!

Backup Recovery

Closed Database Recovery Steps

Restore the damaged files from backup.

SQL> startup mount
SQL> recover database
SQL> alter database open

Offline Tablespace Recovery Steps

Restore the damaged files from the backup.

SQL> startup mount
SQL> alter datafile '/u01/oradata/users01.dbf' offline;
SQL> alter tablespace users offline;
SQL> recover tablespace users;
SQL> alter tablespace users online;

Offline Datafile Recovery Steps

Restore the damaged files from the backup.

SQL> Startup mount
SQL> alter datafile '/u01/oradata/users01.dbf' offline;
SQL> alter database open;
SQL> alter tablespace users offline;
SQL> recover datafile '/u01/oradata/users01.dbf';
SQL> alter tablespace users online;

Cancel-Based Recovery Steps

Restore the damaged files from the backup.

SQL> startup mount
SQL> recover database until cancel using backup controlfile ;
SQL> alter database open resetlogs
Perform a full backup of database.

Change-Based Recovery Steps

Restore the damaged files from the backup.

sql> startup mount
sql> recover database until change 2315 using backup controlfile
sql> alter database open resetlogs

Time-Based Recovery Steps

Restore the damaged files from the backup.

SQL> startup mount
SQL> recover database until time using backup controlfile ;
SQL> recover database until time '2009-01-01:12:00:00' using backup controlfile
SQL> alter database open resetlogs
Perform a full backup of the database

TIPS
System Tablespace Versus a Non-System Tablespace Recovery
-----------------------------------------------------------------------------

When a system data file is lost or damaged, the only way to recover
the database is by doing a closed database recovery using RECOVER
DATABASE command.

Checking for Files Needing Recovery
----------------------------------------------------------
select b.name, a.error from v$recover_file a, v$datafile b
where a.file# = b.file# ;

Happy Learning
--Sreene

RMAN Setup

2009-04-29T04:23:00.000-07:00

Hello Everyone!!!

Setup RMAN User, Catalog & Register
-----------------------------------------
MAKE SURE ALL DATABASE have PASSWORD FILE

sqlplus system/manager@rman <<__1

drop user rman_TEST cascade;

create user rman_TEST identified by rman_TEST
default tablespace users
temporary tablespace ts_temp
quota unlimited on users
quota unlimited on ts_temp ;
grant connect , resource, recovery_catalog_owner to rman_TEST;
exit
__1
# create the catalog
rman catalog rman_TEST/rman_TEST@rman << __2
create catalog;
exit
__2
rman target \"sys/pass9999@TEST as sysdba\" \
catalog rman_TEST/rman_TEST@rman << __3
register database ;
EXIT
__3

Full Backup Script – Hot Backup – Target DB in Archivelog Mode
-------------------------------------------------------------------
run {
allocate channel c1 type disk;
set limit channel c1 kbytes 500000;
backup full
filesperset 2
(database format '/rman_backup/BACKUP/TEST_data_%U');
sql "alter system archive log current";
backup filesperset 10
format '/rman_backup/BACKUP/TEST_arch%U'
(archivelog all );
release channel c1;
}
exit;

Incremental Backup Script
------------------------------
resync catalog;
change archivelog all validate;
run{
allocate channel c1 type 'SBT_TAPE';
allocate channel c2 type 'SBT_TAPE';
backup
format '/intr/cf_t%t_s%s_p%p'
(current controlfile);
backup
incremental level 0
tag db_intr_0
filesperset 5
format '/intr/df_t%t_s%s_p%p'
(database);
sql 'alter system archive log current';
backup
filesperset 20
format '/intr/rl_t%t_s%s_p%p'
(archivelog all delete input);
release channel c1;
release channel c2;
}

Full Backup Script – To Tape
--------------------------------

run {
allocate channel t1 type 'SBT_TAPE';
backup
format 'db_%d_t%t_s%s_p%p'
(database);
sql 'alter system archive log current';
backup
format 'arch_%d_t%t_s%s_p%p'
(archivelog all delete input);
}

Recover Specific Datafile
-----------------------------
run
{
allocate channel c1 type disk;
sql 'alter database datafile 2 offline drop';
restore datafile 2;
recover datafile 2;
release channel c1;
}

COMPLETE RECOVERY script using current controlfile
-------------------------------------------------------
run {
allocate channel d1 type disk;
restore database;
sql 'alter database mount';
recover database;
}

INCOMPLETE RECOVERY script using backup controlfile
--------------------------------------------------------
-- Required if there are gaps in log sequence

run {
SET UNTIL logseq = 6 thread = 1;
allocate channel d1 type disk ;
restore controlfile;
restore database;
sql 'alter database mount';
recover database;
sql 'alter database open resetlogs';
}

COMPLETE RECOVERY script using backup controlfile
------------------------------------------------------
run {
allocate channel d1 type disk;
restore controlfile;
restore database;
sql 'alter database mount';
recover database;
sql 'alter database open resetlogs';
}

INCOMPLETE RECOVERY script using backup controlfile
--------------------------------------------------------
-- Required if there are gaps in log sequence

run {
# recover database until specific time
allocate channel dev1 type 'SBT_TAPE';
set until time 'Apr 20 2003 12:15:00';
sql "alter database mount" ;
restore database;
switch datafile all;
recover database;
sql "alter database open resetlogs";
release channel dev1;
}

Happy Learning
--Sreene

Change hostname in Linux

2009-04-28T05:30:00.000-07:00

Hello Everyone!!!

First you need to find out your hostname, you can do this with

$ hostname
localhost.localdomain
$

Edit /etc/hosts
You need to edit /etc/hosts and add a line for your host name

$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
$

My new server IP is 72.232.196.90, i need to assign it hostname server12.hosthat.com, to do this, i have edited /etc/hosts as follows.

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
72.232.196.90 server12.hosthat.com server12

Edit /etc/sysconfig/network
First lets see what is in the file

$ cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=localhost.localdomain
$

To change servers hostname to server12.hosthat.com, change the file as follows.

$ cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=server12.hosthat.com
$

Now you need to reboot the server to change the hostname.

Happy Learning
--Sreene

R12:Major changes from 11i

2009-04-22T04:57:00.000-07:00

Technology Stack components

Hello Everyone!!

Major changes from 11i

Release 11i – 11.5.10.2

Release 12

Client Tier

1)R12 Replaces Jinitiator (oajinit.exe)
2)Sun’s 1.5 Java Runtime Environment (JRE)
3)Repacked into oaj2se.exe
4)Netscape support for Applications and ADI

Database Tier
1)10g Release 2 ORACLE_HOME
2)Oracle Database (Enterprise Edition)
3)XDK -Database
4)XDB - Database
5)Workflow
6)InterMedia option
7)Spatial option
8)Data Mining option
9)JDK 5.0 to support Autoconfig

Application Tier
1)OracleAS 10g 10.1.2 for Forms & Reports Services
Replaces the 8.0.6-based Oracle_Home

2)OracleAS 10g 10.1.3 for Oracle Containers for Java (OC4J)
Replaces the 8.1.7-based Oracle_Home provided by iAS 1.0.2.2 in 11i

3)JDBC 10.2

4)JDK 1.5 for web & concurrent processing

5)Servlet Container: JServ -> OC4J(OC4J is Oracles J2EE container)

6)Apache: 1.3.19 -> 1.3.31

7)Forms/Reports: 6i -> 10g

Application Server 10.1.3

1)The Java Oracle Home
2)HTTP Listener
3)Oracle Containers for J2EE (OC4J)
4)Oracle Process Manager and Notification Server
5)Application Server Control (ASControl)
6)JDK 5.0

Application Server 10.1.2

1)The Forms or ‘C’ Oracle Home
2)Forms and Reports
3)Delivers native runtime executables for
Forms (frmweb)
Reports (rwrun)
4)No relinking of native executables. User exists (Applications C Code) is now dynamically linked at runtime.
5)10.1 Required Support Files (RSF’s)

Happy Learning
--Sreene
Oracle Apps DBA

Clone Apps 11i Instance

2009-04-20T11:29:00.000-07:00

11i Cloning

Prerequisites
STEP :1
Oracle Universal Installer 2.2.0.19
Perl 5.005
JRE 1.1.8
JRE 1.3.1
JDK 1.3.1
Zip 2.3
OS utilities
OS utilities
(see 11i Install Manual) must be in
the the PATH when running adcfgclone.pl
(Unix: make,ld,cc,ar - Windows: gnumake,link,cc,cmd)

STEP: 2
Apply the Rapid Clone patch
3453499 in the Application Tier Administration node (as the APPLMGR user):
o Log in to the APPL_TOP environment (source the environment file)
o Create appsutil.zip file
perl AD_TOP/bin/admkappsutil.pl
o This will create appsutil.zip in $APPL_TOP/admin/out

* On the Database Tier (as the ORACLE user):

o Copy or FTP the appsutil.zip file to the
o cd RDBMS ORACLE_HOME
unzip -o appsutil.zip

STEP :3
Clone Oracle Applications 11i

Prepare the source system database tier for cloning
Log on to the source system as the ORACLE user and run the following commands:
cd RDBMS ORACLE_HOME/appsutil/scripts/CONTEXT_NAME
perl adpreclone.pl dbTier

Prepare the source system application tier for cloning
Log on to the source system as the APPLMGR user and run the following commands on each node that contains an APPL_TOP:
cd COMMON_TOP/admin/scripts/CONTEXT_NAME
perl adpreclone.pl appsTier

STEP :4
Copy the Source System to the Target System
Copy the application tier file system

APPL_TOP
OA_HTML
OA_JAVA
OA_JRE_TOP
COMMON_TOP>/util
COMMON_TOP>/clone
COMMON_TOP>/_pages (when this directory exists)
806 ORACLE_HOME
iAS ORACLE_HOME

Copy the database tier file system

# Copy the database (DBF) files from the source to the target system
# Copy the source database ORACLE_HOME to the target system

STEP : 5
Configure the Target System

Configure the target system database server
Log on to the target system as the ORACLE user and type the following commands to configure and start the database:
cd RDBMS ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbTier

Configure the target system application tier server nodes
Log on to the target system as the APPLMGR user and type the following commands:
cd COMMON_TOP/clone/bin
perl adcfgclone.pl appsTier

STEP :6
Finishing Task
1. Update profile options
2. Update printer settings (If printers are not configured or you don't want to use printer you can skip this step)
3. Update workflow configuration settings (Important)

For detailed steps check Metalink Note # 230672.1

Happy Learning
--Sreene
Oracle Apps DBA

Upgrading 9i to 10g with 11i

2009-04-17T05:09:00.000-07:00

Upgrading 9i (9.2.0.6) to 10g (10.1.0.5) with 11i (11.5.10.2) Applications

Installation Steps

Before Upgrade

1. Take a Cold backup of Oracle Home 9.2.0.6 and Oracle Database after shutting down all Processes and Database.

2. Apply Interoperobility patch for 11.5.10 4335078.
2. Apply Auto Config patch (5478710)
3. Gather Optimizer Statistics Before the Upgrade using the scripts below.

Note: This script must be run connected AS SYSDBA using SQL*Plus.

SCRIPT #1
---------
spool gdict

grant analyze any to sys;

exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

spool off
----------------------- End of script #1 -----------------------

4. SCRIPT #2
--
This script creates the stats table, 'dictstattab' and exports the stats for the RDBMS component schemas into it. The export will give error if a particular component schema does not exist in the database. This can happen if a component is not installed or if it is invalid.
--
-- This will be useful incase you want to import the stats back
-- example:
-- Following stmt imports the stats for SYS schema after
-- deleting the existing stats.
-- exec dbms_stats.delete_schema_stats('SYS');
-- exec dbms_stats.import_schema_stats('SYS','dictstattab');

spool sdict
grant analyze any to sys;
exec dbms_stats.create_stat_table('SYS','dictstattab');

exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');

spool off

----------------------- End of script #2 -----------------------

Note: You can ignore the following errors:
*
ERROR at line 1:
ORA-20000: Schema WMSYS does not exist or insufficient privileges to analyze an
ORA-20000: Schema XDB does not exist or insufficient privileges to analyze an
ORA-20000: Schema WKSYS does not exist or insufficient privileges to analyze an
ORA-20000: Schema LBACSYS does not exist or insufficient privileges to analyze
ORA-20000: Schema DMSYS does not exist or insufficient privileges to analyze an
ORA-20000: Schema SI_INFORMTN_SCHEMA does not exist or insufficient privileges

Database Installation
1. Log in as the Oracle user

2. Prepare to create the 10.2.0 Oracle Home.

Decide where you want to install the 10.2.0 Oracle Home on the database server node. It must be in a different directory than the current Oracle Home.

Example :
$ cd /ebis/proddb
$ mkdir 10.2.0

3. Install Oracle10G Release 10.2.0.1 Database Server. Do not run any migrate scripts at this time

The environment variables DISPLAY, TEMP, TMPDIR, TMP should be set before installing and any reference to the 8.0.6 or 9.2.0 Oracle Homes in the path or environment variables should be removed. OR Remove .env entry from the .profile from oracle user.

(Remove sourcing of environment file from Oracle user profile and login again. Run xhost + as root user)

Install Server Version 10.1.0.2

Note: This may differ slightly from your prompts depending on the installation media.

Steps:
$ . runInstaller
Check "Advanced Installation" checkbox on the “Welcome” screen
Click the [Next] button
Choose a new ORACLE_HOME Name (i.e. APPS10G_)
Verify the location of the Oracle Home
Click the [Next] button
Click the [Next] button to accept the “Enterprise Edition” installation
Review the success of "Product Specific Pre-requisites"
Click the [Next] button

Note: You will not be upgrading an existing Database at this time
Choose the “Do not create a starter Database” radio button
Click the [next] button
Click the [Install] button at the “Summary” screen
Run the “root.sh” scripts when prompted
Click [Ok] to continue after running the script
Click ‘Exit’ to acknowledge the end of installation
Click the (yes] button leave the installer
THEN INSTALL THE COMPANIAN CD
./runinstaller
SELECT THE SAME ORACLE HOME (10g oracle home)
Select 10g products à next
Run root.sh
Finish the installations.

PATCH SET 10.1.0.5 INSTALLATION
a) Set the environment
b) Set .profile OR

export ORACLE_SID=PROD
export ORACLE_HOME=<10.1.0 ORACLE_HOME>
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
c) Unzip the patch in $ORACLE_HOME
d) CD Disk1
e) ./runinstaller
f) run root.sh
g) Create nls/data/9idata directory

On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you perform steps involving the 10g Oracle home.

Steps:
export ORACLE_SID=PROD
export ORACLE_HOME=<10.1.0 ORACLE_HOME>
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

h)
i) cd $ORACLE_HOME/nls/data/old
perl cr9idata.pl

The following will message appear:
Please reset environment variable ORA_NLS10 to /ebis/proddb/10.2.0/nls/data/9idata!

Prepare to Upgrade
1.Check the free space on the SYSTEM tablespace. There should be at least 500MB of free space. Temporary tablespace 2GB

$ sqlplus system/manager

select nvl(sum(bytes)/1024,0) as "System Tablespace Avail(KB)"
from dba_free_space
where tablespace_name like '%SYSTEM%';

Note: If it is necessary to add a Datafile use a statement similar to the following:
ALTER TABLESPACE system
ADD DATAFILE '/apps2/oracle/proddata/system07.dbf'
SIZE 750M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

Apply OPATCH 2617419
Create soft link
Ln –s /usr/sbin/fuser /sbin/fuser
Unzip the patch into $ORACLE_HOME

APPLY PATCH 4605877
DON’T PERFORM SPECIAL INSTRUCTION IN SPECIFIED IN THE PATCH README.

SHUTDOWN THE OLD (9I)LISTENER
NOTE: Database should be up and running.
CREATE 10G LISTENER
Netca
Give the same instance name as PROD and port as same port.
Upgrade the Database
1.Shutdown all processes running out of the 9.2.0.6 ORACLE_HOME
2.only the database shold be UP and Running.

2.Set your environment variables
export ORACLE_SID=VIS
export ORACLE_HOME=/apps/visdb/10.1.0
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

3.Start the Database Upgrade Assistant

Steps:
dbua
Click the [next) button
Select the Database to upgrade
Click the [next) button
Choose the size for the SYSAUX tablespace
[ We recommend that you use 500 MB as the SYSAUX tablespace size. Set autoextend on for the SYSAUX tablespace.] after upgrade it will take around 7-8 GB depends on your DB size. Please make sure you have sufficient space in that directory.
Click the [next) button
Remove the check for the option to “Recompile Invalid Objects”. This will be during at a later step. If you want the dbua compiles your invalid objects then check the check box(but it will take time to10hr)
Click the [next) button
Decide on you “Backup” strategy
Click the [next) button
Decide on your Management Options
Click the [next) button
Review the “Summary” screen
Click the (Finish) button
You may disable the compilation of objects as that is done at a later step.
Click the (Ok) button to acknowledge completion of the upgrade
Review the upgrade results
Decide on your option to configure “Database Passwords”
Decide on your option to “Restore the Database” if you are not satisfied with your upgrade results.
Click the (Close) button to end the upgrade

Note: The following changes are noted in the summary:

A spfile.ora is created
Large_pool_size = 8388608
Shared_pool_size was changed from 301989888 to 335544320
The following parameters were removed:
- optimizer_max_permutations
- row_locking
- undo_supress_errors
- max_enabled_roles

Adjust the Database parameters

1. Alter Database parameters as suggested in Note: 216205.1
New initPROD.ora file will be available in 10.2.0.1 ORACLE_HOME/dbs directory.
Make the below changes in the initPROD.ora and restart the 10.2.0.1 database.
2.Modify the location of the DUMP files

a) Create the new locations for the dump files

b) Alter the dump locations

3.Modify the location of the directory for UTL_FILE_DIR

a) Create the new locations for UTL_FILE_DIR secondary directory

cd $ORACLE_HOME/appsutil
mkdir outbound
mkdir

b) Alter the UTL_FILE_DIR secondary location

4.Run utlrp to compile Invalid Objects

The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types and so on. These actions are optional; however, they ensure that the cost of recompilation is incurred during installation rather that in the future.

To run the utlrp.sql script, complete the following steps:
a) Change to the ORACLE_HOME/rdbms/admin directory.
b) Login as a user with SYSDBA privileges.
c) Run the utlrp.sql

Note: This scripts will take several hours to run

7. Check for Installation of Oracle XML DB
select count(1) from dba_registry where comp_id='XDB';
After the Database upgrade
Gather statistics for SYS schema for this the free temporary tablespace should be more give 6 to 8gb

Steps:
$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;
Start the New Database listener.

Migrate the 9.2.0.6 CONTEXT_NAME directory to the 10.2.0 network/admin directory
Modify the listener.ora, tnsnames.ora and sqlnet.ora to replace the 9.2.0 references with 10.2.0

Setup the environment

export TNS_ADMIN=/apps/visdb/10.1.0/network/admin/
export ORACLE_SID=PROD
export ORACLE_HOME=/ebis/proddb/10.1.0
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
lsnrctl start

Run adgrants.sql (Conditional)
If you have at least AD.I or Applications release 11.5.10 installed on your system, copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:

sqlplus "/ as sysdba" @adgrants.sql

Grant create procedure privilege on CTXSYS

From the administration server node, use SQL*Plus to connect to the database as APPS and run the following command:

sqlplus apps/ @$AD_TOP/patch/115/sql/adctxprv.sql \
CTXSYS

Manually migrate files for the 10.2.0 environment
Note:-- carefully change all the environment whichever showing the 9i oracle home to 10g

Migrate the 9.2.0.6 .env to the 10.2.0 ORACLE_HOME and modify the environment file changing the 9.2.0 references to 10.2.0

Note: Global replace command for vi editor
:g/9.2.0/s//10.1.0/g

Implement and run Autoconfig on the new Database home
1. Copy AutoConfig to the RDBMS ORACLE_HOME

Update the RDBMS ORACLE_HOME file system with the AutoConfig files by performing the following steps:

Steps:
* On the Application Tier (as the APPLMGR user):
a) Log in to the APPL_TOP environment and source the APPSORA.env file
b) Create appsutil.zip file. This will create appsutil.zip in $APPL_TOP/admin/out
perl $AD_TOP/bin/admkappsutil.pl
c) Copy or FTP the appsutil.zip file to the
* On the Database Tier (as the APPLMGR or ORACLE user):
d) cd
e) Source <10.2.0 CONTEXT_NAME.env> file
f) unzip -o appsutil.zip

2. Generate your Database Context File. Execute the following commands to create your Database Context File:

Steps:

a) cd
b) . .env
c) cd <10.2.0 ORACLE_HOME>/appsutil/bin
d) perl adbldxml.pl tier=db appsuser= appspasswd=

Attention: adbldxml uses your current environment settings to generate the context file. Therefore ensure that your environment is correctly sourced.

3. Generate and Apply AutoConfig Configuration files
Attention: This step performs the conversion to AutoConfig. Once completed, the previous configuration will not be available.

Attention: The database server and the database listener must remain available during the AutoConfig run. All the other database tier services should be shut down.
Execute the following commands:

Steps:
a) cd /appsutil/bin
b) adconfig.sh contextfile= appspass=
Note: Running AutoConfig might change your existing environment files. After running AutoConfig, you should always set the environment before you run any Applications utilities in order to apply the changed environment variables.

Recreate Grants and Synonyms
Steps:
cd $APPL_TOP
. APPSORA.env
adadmin
Choose Menu Item #4 -Maintain Applications Database Entities menu
Choose Sub Menu Item #2 - Re-create grants and synonyms for APPS schema
Choose the number of workers

Restart Applications
Steps:
cd $APPL_TOP
. APPSORA.env
cd $APPLCSF/scripts/$CONTEXT_NAME
. adstrtal.sh apps/

Metalink Documents referred:
1.282038.1 Oracle Applications Release 11i with Oracle Database 10g Release 1 (10.1.0)
2.362202.1 Interoperability Notes Oracle Applications Release 11i with Oracle Database 10g Release 1 (10.1.0)
3.216205.1 - Database Initialization Parameters and Configuration for Oracle Applications 11i.
4.165195.1 - Using Autoconfig to Manage System Configurations with Oracle Applications 11i
5. Note:359483.1-- Upgrading Oracle Server 9.2.0.6 to 10.1.0.4 in an 11.5.10.2 Applications Environment

Upgrade from 11.5.10.2 to R12

2009-04-17T04:06:00.000-07:00

Upgrade Oracle Applications from 11.5.10.2 to R12
System Overview

Version 11.5.10.2
Oracle Applications Vision Instance
OS: Redhat Linux update 5
Type: Single Node instance
Applications OS User: applmgrupd
Installed directory: /d01/oracle/singlenode
Database 9i OS User : oracleupd
9i Database Home = /d01/oracle/singlenode/testdb/9.2.0
10g Database Home = /d01/oracle/singlenode/testdb/10.2.0
Instance SID: TEST
Host: linux1

Task – To Upgrade Oracle Applications 11.5.10.2 to R12
Note: Please use vision instance as it is already Multi-Org, Multi-Org is mandatory in R12

Note: The below upgrade is only for testing purposes, not to be implemented in production servers, please do refer metalink for more information.

Upgrade path
• Applications R11.5.6 and below requires to first upgrade to R11.5.10.2/10gR2
• Applications R11.5.7 and up can be directly upgraded to R12
• Database in all releases must have been upgraded to 10gR2 (11.5.9.2 / 11.5.10.2) or should be done during upgrade to R12 (11.5.9.1/11.5.10.1 and below

Here are steps to upgrade Oracle Application 11.5.10.2 to R12, let’s split the activity into 6 steps
1> Please do upgrade the database from 9.2.0.6 to 10.2.0.3, the steps are available in the below link

2> Apply the below patches in your existing 11.5.10.2 environment

Shut down the entire E-Business Suite environment and perform a full backup
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12

3> Run rapidwiz to create R12 code tree

4> Apply the below patches in R12 environment
4502962 – R12 Minipack

5> Configuration Phase – R12

6> Apply the online help

2> Apply the below patches in your existing 11.5.10.2 environment
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
*5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12

Enable the maintenance mode

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=adadmin.log
menu_option=ENABLE_MAINT_MODE workers=4

-- Apply Patch 4712852
unzip p4712852_11i_LINUX.zip
cd 4712852

-- Takes 5 minutes
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=4712852.log
patchtop=/software/upgrades/11.5.10.2toR12/r11/4712852 driver=u4712852.drv workers=4
-- Apply patch u5753359.drv
unzip p5753359_11i_LINUX.zip

-- Takes 2 minutes
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=5753359.log
patchtop=/software/upgrades/11.5.10.2toR12/r11/5753359 driver=u5753359.drv workers=4
-- Apply patch u5467526.drv

unzip p5467526_11i_LINUX.zip
cd 5467526
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=5467526.log
patchtop=/software/upgrades/11.5.10.2toR12/r11/5467526 driver=u5467526.drv workers=4

-- Apply patch u5120936.drv
unzip p5120936_11i_GENERIC.zip
cd 5120936
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=5120936.log
patchtop=/software/upgrades/11.5.10.2toR12/r12/5120936 driver=u5120936.drv workers=4
cd $AD_TOP/patch/115/sql

sqlplus apps/apps @adtums.sql /usr/tmp

-- Review TUMS report in /usr/tmp
-- Apply patch 5726010
unzip p5726010_11i_GENERIC.zip

cd 5726010/

sqlplus apps/apps @adgncons.sql apps apps
# modify the adcrtbsp.sql to specify the path for new tablespaces

sqlplus system/manager @adcrtbsp.sql

su - oracleupd

mkdir -p $ORACLE_HOME/appsutil/admin
cd $ORACLE_HOME/appsutil/admin
cp /software/upgrades/11.5.10.2toR12/r12/5726010/adgrants.sql .

sqlplus '/ as sysdba' @adgrants.sql applsys
-- Run rapidwiz to create R12 code tree
The installation will install R12 applications components and the database home
Select upgrade to Oracle Applications R12
Select upgrade actions screen
select create upgrade file system

enter the parameters required to set up your new environment run rapid install Here are the screen shots for the same

The Installation creates APPL_TOP, COMMON_TOP, INST_TOP and 10g Oracle Home
Move the new environment file into .bash_profile
su - applmgrupd
cd /d01/oracle/apps/apps_st/appl
mv .bash_profile .bash_profile_11.5.10
cat APPSVIS_linux1.env >> /home/applmgrupd/.bash_profile
Now onwards, you are connected to R12 application
-- Apply patch AD.A 4502962
-- takes 6 minutes
unzip p4502962_R12_LINUX.zip
cd 4502962/
adpatch

Please enter the name of your AutoPatch driver file: u4502962.drv
-- Run the American English upgrade patch driver
-- Takes 40 hours
cd $AU_TOP/patch/115/driver

adpatch options=nocopyportion,nogenerateportion

Please enter the name of your AutoPatch driver file: u4440000.drv
Run the NLS upgrade patch driver (conditional)
• Download the NLS Release 12 patch (4440000) for each active language in the system.
• Run each driver (u4440000.drv) with adpatch
• NLS patch driver has the same name as the American English patch driver.
Synchronize NLS and American English product patches (conditional)
• Generate manifest using perl $AD_TOP/bin/adgennls.pl
• Backup manifest file $APPL_TOP/admin/$TWO_TASK/out/adgennls.txt
• http://updates.oracle.com/TransSync
• Don’t check Translation Level Updates
• Upload manifest
• Download and apply patch
-- Configuration Phase
Disable maintenance mode
Run autoconfig

Run rapidwiz to configure and start processes
Sign on to R12 applications check concurrent managers

Run autoconfig
su - applmgrupd
perl $AD_TOP/bin/admkappsutil.pl
su - oracleupd
cd $ORACLE_HOME
cp /d01/oracle/inst/apps/TEST_linux1/admin/out/appsutil.zip .
unzip -o appsutil.zip
cd $ORACLE_HOME/appsutil/scripts/TEST_linux1
sh adautocfg.sh

Run rapidwiz to configure and start processes
-----------------------------------------
rapidwiz - Upgrade to Oracle Applications Release 12 -> Upgrade Action -> Select Configure Upgraded

Release 12 instance
$INST_TOP/admin/VIS_linux1.xml
/d01/oracle/inst/apps/TEST_linux1/appl/admin/TEST_linux1.xml

Note: I got the below error, but all services and the application is running.

Sign on to R12 applications check concurrent managers

Connect as sysadmin/sysadmin

-- Apply online help
cd $AU_TOP/patch/115/driver/
adpatch options=hotpatch,nocopyportion,nogenerateportion
Please enter the name of your AutoPatch driver file : u5051400.drv
-- Gather schema statistics for CBO
Release 12 employs cost-based optimization, which examines FND table statistics to determine the most
efficient access paths and join
methods for executing SQL statements. These statistics are gathered by the FND_STATS process, which
you initiate by running the
Gather Schema Statistics concurrent program.
From your Release 11i APPL_TOP, complete the following steps:
1. Log in to Oracle Applications with the System Administrator responsibility.
2. Navigate to the Submit Request window (Request > Run).
3. Submit the Gather Statistics program. (Schema)

References
1. Upgrading Oracle Applications Note# 289788.1
2. Oracle 10gR2 Database Preparation Guidelines for an E-Business Suite R12 Note#403339.1
3. Oracle applications R11.5.10.2 Maintenance pack Installation Note#316365.1
4. Oracle Applications Upgrade Guide: Release 11i to Release 12 Part# B31566-01
5. Database Initialization Parameters for Oracle Applications Release 12 Note#396009.1
6. Oracle Applications Release Notes Release 12 Note# 405293.1
7. E-Business Suite Release 12 Upgrade Sizing and Best Practices Note# 399362.1
8. Upgrading to R12 Note# 414710.1
9. Maintenance Wizard Note# 215527.1

How to access EBS in mozile borwser in linux

2009-04-17T02:58:00.000-07:00

To enable the JAVA plugin in the browers...

1. install required JAVA in /usr/java

2.Go the plugin dir of your browser
ex : 1. cd /usr/lib/mozilla/plugins/
2. cd /home/oracle/firefox/plugins/

3.create a link for the file libjavaplugin_oji.so.
ex : ln -s /usr/java/j2sdk1.4.2_04/jre/plugin/i386/ns610/libjavaplugin_oji.so .
or ln -s /usr/java/j2sdk1.4.2_04/jre/plugin/i386/ns610-gcc32/libjavaplugin_oji.so .

4. Try again with the Browser.
these are the steps to access 11i in linux mozilla borwser..

Linux, Mozilla And Oracle Applications 11i

For the benefits of those who has been hunting around for a solution to running Oracle Applications on Linux, here's the steps on how to get it to run on a Redhat based distro :

1. Download the latest Java SDK/JRE from Sun. The latest version is J2SE 1.4.2_01
URL: http://java.sun.com/j2se/1.4.2/download.html

2. Save the downloaded SDK file, j2sdk-1_4_2_01-linux-i586-rpm.bin (approx. 40MB), in a directory.

3. As root, extract the file:
root@linux# ./j2sdk-1_4_2_01-linux-i586-rpm.bin
This will extract the j2sdk-1_4_2_01- linux-i586.rpm file.

4. If you are installing the JDK for the 1st time:
root@linux# rpm -iv j2sdk-1_4_2_01-linux-i586.rpm OR, if upgrading from previous version:root@linux# rpm -Uv j2sdk-1_4_2_01-linux-i586.rpm

5. The installation process will drop the SDK into the /usr/java/j2sdk directory

6. Go to your mozilla plugins directory:root@linux# cd /usr/lib/mozilla/plugins

7. Create a symbolic link to the libjavaplugin_oji.so library. NOTE if your mozilla is 1.3.X, then:root@linux# ln -s /usr/java/j2sdk/jre/plugin/i386/ns610/libjavaplugin_oji.so libjavaplugin_oji.so

If your mozilla is 1.4.X and above: root@linux# ln -s /usr/java/j2sdk/jre/plugin/i386/ns610-gcc32/libjavaplugin_oji.so libjavaplugin_oji.so

8. If your home directory also contain a .mozilla directory, then:
root@linux# cd $HOME/.mozilla/plugins Create another symbolic link to the library as in step 7.

9. Launch Mozilla and type the url to access your Oracle Applications:
http://:/dev60cgi/f60cgi?jinit_mimetype=application/x-java-vm

10. Now sit back and enjoy (or suffer?) Oracle.
Warning: The above steps has not been fully tested and I believe Oracle will not support it. So, use it at your own risk. Cheers and say bye to Microsoft(and BG)

Change hostname for E-Business Suite

2009-04-17T01:13:00.000-07:00

Run AutoConfig to change hostname for E-Business Suite

Oracle E-Business Suite is dependent on hostname. User can easily change the IP of the machine, but user cannot anyhow change the hostname of the machine. To change the hostname, we need to run AutoConfig on E-Business Suite.

First, startup dbTier. AppTier is not required at all in the whole process.

FOR DATABASE TIER:
Note:338003.1 How to change the hostname and/or port of the Database Tier using AutoConfig

1. Login as oracle user.

2. De-register the current database server.
perl $ORACLE_HOME/appsutil/bin/adgentns.pl appspass=apps contextfile=$CONTEXT_FILE -removeserver

3. Update AutoConfig Context file with new host information. Edit $CONTEXT_FILE directly.
* Copy $CONTEXT_FILE to a new context file.
cp /opt/oracle/product/10.1.0/db/appsutil/PROD_.xml
/opt/oracle/product/10.1.0/db/appsutil/PROD_.xml
* Edit /opt/oracle/product/10.1.0/db/appsutil/PROD_.xml directly.
- Replace all to
- Replace all to < newdomain.com>

4. Change hostname.
- Execute " system-config-network" to change the new hostname for the machine.
- Edit /etc/hosts with " newhost.newdomain.com ip newhost".

(After changing hostname, you'll not be able to startup any graphical application such as xclock due to "Xlib: connection to :0.0 refused by server". You can press "ctrl + alt + backspace" to kill x server.)

5. Execute AutoConfig for dbTier.
cd $ORACLE_HOME/appsutil/bin/
./adconfig.sh contextfile=$ORACLE_HOME/appsutil/PROD_.xml

FOR APPS TIER:
Note:341322.1 How to change the hostname of an Applications Tier using AutoConfig

1. Login as applmgr user.

2. De-register the current apps server.
perl $AD_TOP/bin/adgentns.pl appspass=apps contextfile=$CONTEXT_FILE -removeserver

3. Update AutoConfig Context file with new host information. It is easier to edit $CONTEXT_FILE directly.
* Copy $CONTEXT_FILE to a new context file.
cp /opt/oracle/apps/prodappl/admin/PROD_.xml
/opt/oracle/apps/prodappl/admin/PROD_.xml
* Edit /opt/oracle/apps/prodappl/admin/PROD_.xml directly.
- Replace all to
- Replace all < olddomain.com> to < newdomain.com>

4. Execute AutoConfig for appTier.
cd $AD_TOP/bin
./adconfig.sh contextfile=/opt/oracle/apps/prodappl/admin/PROD_.xml appspass=apps

FINISHING TASK:

AutoConfig doesn't convert E-Business Suite completely. Some settings may remain unchanged or reset to default. So, it is required to go check the configuration as described in Cloning Guide.

Identified steps are:

1. As applmgr, vi /opt/oracle/apps/prodappl/admin/adgetlnxver.sh to ensure ctxfile is pointing to correct PROD_.xml file.
ctxfile="/opt/oracle/apps/prodappl/admin/PROD_htb.xml"

2. As applmgr user, vi $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties to ensure:
wrapper.bin.parameters=-Djava.awt.headless=true
wrapper.bin.parameters=-DAdaptorCatalog=/opt/oracle/apps/prodappl/au/11.5.0/java/catalog.xml
wrapper.bin.parameters=-DClientMode=local

3. As oracle user, do the following:
$ sqlplus apps/apps
sql> update icx_parameters set session_cookie_domain=' newdomain.com';
sql> conn applsys/apps
sql> select profile_option_value from fnd_profile_option_values where profile_option_value like '%oldhost%';
sql> update fnd_profile_option_values set profile_option_value='/opt/oracle/db/proddb/9.2.0/appsutil/outbound/PROD_newhost' where profile_option_value like '%oldhost%';
3 rows will be updated.
sql> exit;

Done. You can startup AppTier now and access E-Business Suite login page at http://newhost.newdomain.com:8000.

Discoverer Troubleshooting

2009-04-16T05:10:00.000-07:00

Generic Discoverer Issues

Make sure Jserv is working fine.
For all other discoverer issues that comes while accessing viewer page or start_ie page or discolaunch.htm, try the following :

1. cd $ORACLE_HOME/discwb4/util
2. ./stopall.sh
3. ls –la .*id => there should not be any .*id file once the components are down. If any .*id file gets listed, do
a. more .oadid ( good for any .*id file present)
b. ps –eaf | grep nnnnn ( nnnnn is the value listed in step a )
c. If this is owned by the wrong user, remove the file.
4. Make sure ./startgatekeeper.sh is in startall.sh
5. ./startall.sh
6. Check locator.log, gatekeeper.log and oad.log for error messages
7. Check if the discoverer works fine. If not …
a. stopall.sh
b. startoad.sh
c. startosagent.sh
d. unregistercollector.sh
e. unregistersession.sh
f. unregisterpreference.sh
g. check for correct ip in pref.txt
h. applypreferences.sh
i. registerpreferences.sh
j. registersession.sh
k. registercollector.sh
l. stoposagent.sh
m. stopoad.sh
n. stopall.sh
o. startall.sh

Discoverer Viewer
Problem Description:
Discoverer Viewer fails with the following error...
Wide area network connection failed.
Unable to read locator.ior file at location file:/discwb4/applet/locator.ior. - Local area network connection failed. Failed to connect to locator .oracleoutsourcing.com_OracleDiscovererLocator4 using OSAgent. -
Gatekeeper connection failed. -
Hint: An administrator can further diagnose connection problems by running the "checkdiscoverer" script under /806/discwb4/util.

Resolution :

This happens when there is a discrepancy in the discoverer xml file.
1. To locate the filename and its location go to : $APACHE_TOP/Jserv/etc/disco4iviewer.properties
2. Look for "servlet.Viewer.initArgs" and note down the path it points to. Eg : "servlet.Viewer.initArgs=config=file:/tbmidi/product/iAS/Apache/Apache/htdocs/disco4iv/html/disco4iv.xml"
3. Now grep for "path" in the disco4iv.xml file. The path should ideally look like, for eg : path="/tbmidi/product/iAS/Apache/Apache/htdocs/discwb4/applet/"
If the full path is not mentioned, viewer fails.
In this case, the path would have looked like........path="/discwb4/applet/"
Change the path variable to display the full path as given in the eg.
4. Restart Apache Server.

Problem Description: Discoverer Viewer fails with the following error...

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, apdmrkmi@oracleoutsourcing.com and inform them of the time the error occurred, and anything you might have done that may have caused the error. More information about this error may be available in the server error log.

“mod_jserv.log” display the following error……..

[10/03/2005 17:08:39:120] (ERROR) ajp12: Servlet Error: Failed to set state

Resolution :

Update $APACHE_TOP/Jserv/etc/viewer.properties by moving the following lines above the line with "apps.zip" reference.

#-----------------------
wrapper.classpath=/dmrkmi/product/806/vbroker/lib/vbjorb.jar
wrapper.classpath=/dmrkmi/product/806/vbroker/lib/vbjapp.jar
wrapper.classpath=/dmrkmi/product/iAS/Apache/Apache/htdocs/disco4iv/disco4res.jar
#-----------------------

Problem Description: Discoverer Viewer fails with the following error...

Could not locate or parse the .dbc file corresponding to the given Oracle Applications instance.

Resolution :

Edit the $APPL_TOP/admin/_.xml context file and look for “s_appserverid_authentication”
Set this to OFF and rerun the adconfig

In 11510 default value is SECURE.

Discoverer Launcher

Problem Description: Discoverer Launcher fails with the following error...

Wide area network connection failed.
Unable to read locator.ior file at location http://:/discoshare/locator.ior - Local area network connection failed. Failed to connect to locator .oracleoutsourcing.com_OracleDiscovererLocator4 using OSAgent. -
Gatekeeper connection failed. -
Hint: An administrator can further diagnose connection problems by running the "checkdiscoverer" script under /806/discwb4/util.

Resolution :
This happens when the URL is wrong. It can be either the hostname or the port.
1.Ensure the “fnd_profile_option_values” has the correct URL set.
2.Grep for the URL in $APACHE_TOP/Apache/htdocs.
a.For eg, a big-up url is given a wrong port no. ie https://appsrolltest.oracleoutsourcing.com:10515
b.grep -ir appsrolltest * discwb4/scripts/htmlvars_apps.js:var ORAWEBDISCO_IORCODEBASE = https://appsrolltest.oracleoutsourcing.com:10515/discoshare/";
c.Change the URL to reflect the correct port.

Gatekeeper

Problem Description: Gatekeeper fails to start

Resolution:
1.Go to $APACHE_TOP/Apache/htdocs/discwb4/applet directory
2.Check the gatekeeper.log file for errors like ‘unable to bind to port’ or other errors.
3.Fix the error and restart gatekeeper.

Registering Components

Problem Description:

Registering Discoverer Preference Repository

oadutil reg: Unexpected SystemException org.omg.
CORBA.NO_IMPLEMENT[completed=MAYBE]

Resolution:

1.The issue is with TMPDIR in $ORACLE_HOME/discwb4/discwb.sh.
2.Make sure that the entry for TMPDIR exists in discwb.sh.
For eg; TMPDIR=/lhypei/applmgr/product/806/discwb4/tmp

Problem Description:

//applmgr/product/806/vbroker/bin/vbj[200]:
-Dorg.omg.CORBA.ORBClass=com.visigenic.vbroker.orb.ORB: unknown test
operator

Resolution:

Goto the line no. specified in brackets and put the correct syntax as follows :

if [ -d "${J_HOME}" ] && [ "${javavm}" = "java" ]; then

(Sometimes the line no. in the error will not match with the above line, in that case you need to search for the above line in the vbj file and correct it)

Problem Description:

//product/806/discwb4/bin/dis4pr: error while loading shared
libraries:
libclntsh.so.1.0: cannot open shared object file: No such file or directory Registering Discoverer Preference Repository.

Resolution:

1. ldd /tohagi/product/806/discwb4/bin/dis4pr
You will get libclntsh.so.1.0 => not found
2. Executed the above command in the source instance, to get the correct path of libclntsh.so.1.0.
3. Copy libclntsh.so.1.0 from source to target.

OAD

Problem Description: Failed to Start OAD. Possibly the OAD is already running in the same port.
Resolution:
1. Check for IP_ADDR in $ORACLE_HOME/discwb4/util/discwb.sh.
Correct the IP Address.

Note : Along with the above error, if oad.log shows : error while loading shared libraries: liborb.so: cannot open shared object file: No such file or directory.

2. Copy liborb.so from source MT where discoverer is running to target MT.
For eg : $ORACLE_HOME/vbroker/lib/liborb.so to target.

Problem Description: Failed to bind to OAD

The following errors can be found in locator.log :
VisiBroker: Unable to contact agent in local node or from list provided.
Will try to locate using broadcast.
oadutil reg: Unexpected SystemException org.omg.CORBA.NO_IMPLEMENT[completed=MAYBE]
Locator: No IP address given. Bind directly to server name
Unable to bind to or retrieve information from preference object auohscabo02.oracleoutsourcing.com_10715OracleDiscovererPreferences4
Could not locate the following object: repository id : IDL:DCICORBAInterface:1.0

(OR)

OAD: Fatal Error [Cross-device link].
Unable to create repository file: /tcaboi/applmgr/product/806/vbroker/adm/impl_dir/impl_rep. Make sure VBROKER_ADM environmentis set up correctly
Unable to create repository file: /tcaboi/applmgr/product/806/vbroker/adm/impl_dir/impl_rep

Resolution:

Create a directory by name “tmp” in $ORACLE_HOME/vbroker/adm and put the following entry in $ORACLE_HOME/discwb4/discwb.sh

TMPDIR=$ORACLE_HOME/vbroker/adm/tmp
export TMPDIR

Error:ORA-01631

2009-04-16T04:54:00.000-07:00

Overview Of ORA-01631: Max # Extents (%s) Reached in Table %s.%s

Text: max # extents (%s) reached in table %s.%s
---------------------------------------------------------------
Cause: A table tried to extend past maxextents
Action: If maxextents is less than the system maximum, raise it.Otherwise, you must recreate with larger initial, next or pctincrease params.

Explanation:
------------
The max extents error occurs when the current number of extents equals the maximum number of extents in the max_extents parameter for the object, or the maximum number of extents allowable for the db_block_size, whichever is smaller, and an attempt is made to add another extent. Max extents can be set for an object using the MAXEXTENTS option of the storage clause. These kind of problems could be avoided by pro-actively monitoring the object sizes that may reach their max_extents.

Diagnostic Steps:
-----------------
1. Run the following script to identify the tables with extent problems.

SQL> select segment_name, owner, extents, max_extents
2 from dba_segments
3 where segment_type = 'TABLE'
4 and (extents +1) >= max_extents;

2. Follow up the scripts in: Note 1019721.6 SCRIPT: SCRIPT TO REPORT TABLES APPROACHING MAXEXTENTS.

Possible solutions:
-------------------
1. If the value of max_extents for the affected object is less than the limit of maximum extents for the db_block_size of the database (refer to Note 1015356.4), then more extents could be allocated to the object.

Alter the STORAGE clause of the object to increase MAXEXTENTS.
In databases version 7.3.X or higher one could specify maxextents
UNLIMITED (i.e. 2147483645), but use this with caution.

ALTER TABLE .table STORAGE ( MAXEXTENTS x);
where x is greater than max_extents and lesser than unlimited (2147483645);

ALTER TABLE .table STORAGE ( MAXEXTENTS UNLIMITED);

Remark: Note 50380.1 ALERT: Using UNLIMITED Extent Format

2. It is not advisable to have a lot (hundreds of thousands, etc.) of extents that could result in large amounts of of space management operations in dictionary managed tablespase. In that case, or when the number of max_extents for the affected object reached the limit of maximum extents for the db_block_size,recreate the object with more efficient storage.Export the table and precreate the table before import, using larger storage parameters (initial, next, pctincrease) - to result in fewer extents, or export using COMPRESS=Y - to minimize number of extents after import.

Multi-Node Installation

2009-04-13T03:06:00.000-07:00

Multi-node Installation
With the multi-node installation option, the configuration possibilities are endless. Any server can be placed on any node. You can also use this option to install multiple forms servers to balance the load of forms usage.

Benefits of a Multi-node Installation
The multi-node installation provides many benefits, including those listed on this slide. If you decide to have your forms servers in a Linux environment and your database servers in a UNIX environment, Rapid Install helps you to configure this multi-platform environment.
Note: If you install multiple forms servers, they must be configured identically (including the operating system), otherwise load balancing will not function properly.

Setting Up a Multi-node Installation
In a multi-node installation, you can set up your servers on more than one node. That means you can install any type of server, on any number of nodes, in any combination. Regardless of the number of nodes you plan to use, a multi-node installation requires that you run Rapid
Install on your database node first, before you install the other nodes. Then, using the same configuration file you created while setting up your database node, you run Rapid Install on each of the other nodes in your installation.

Setting Up a Multi-node Installation
Running the Rapid Install Wizard
Step 1: Choose an installation operation
Step 2: Choose a configuration type
Step 3: Choose environment
Step 4: Register Applications products and components
Step 5: Select country-specific functionality
Step 6: Select NLS settings
Step 7: Select host names
Step 8: Specify main settings information
Step 9: Specify derived settings information
Step 10: Review instance settings information
Step 11: Save the configuration file
Step 12: Review pre-install test results
Step 13: Begin the installation
Step 14: Complete the installation on the other nodes

Step 1: Choose an installation operation

Choose an installation operation
Click the Install Oracle Applications button. Pressing the Tab key moves the cursor between options. Moving the cursor by pressing the Up or Down Arrow keys selects the option as well.
Click Next to continue.

Step 2: Choose a configuration type

Select Multi-node to set up a configuration that installs the servers on two or more nodes. Besure that the first server you install and set up is the database. After you complete that installation, you will run Rapid Install on each of the other nodes in your system. Click Next to
continue.

Step 3: Choose environment

Choose environment: Database name
In order to identify the Oracle Applications environment, give it a name and indicate the type of database you want to install.
Rapid Install uses default names that suggest possible uses for the environment:
• A production environment is a fully configured Release 11i installation that can be used for live production purposes.
• A test environment is a mirror image of a production Release 11i installation. You can use it to test your installation before you go live.
• A Vision Demo environment can be used to set up a Release 11i installation for
demonstration purposes.
Note: If you want to install another instance, for example, to use as a test environment, run the Rapid Install wizard again and identify the new environment on this screen.

After you type the name, choose the type of database that you want to install. The database
options are:
• Fresh install database: An empty Oracle database, typically used for production or
testing.
• Vision Demo database: An Oracle Vision Demonstration database used to set up demos
or for evaluation purposes.
• Do not install a database: This option assumes an existing database. It creates an
Applications instance and configures it using your existing database. It is typically used for Windows clusters.
Click Next to continue.

Step 4: Register Applications products and components

Rapid Install installs all products regardless of their licensed status. You use this screen to register the products that you have licensed for use in your system. Your Oracle Corporation license agreement specifies the type of license that applies to your installation. Click Use
Component Applications Licensing or Use E-Business Suite Licensing, according to the
agreement. Rapid Install automatically installs and registers shared and dependent products for you, so they are selected by default.
Choosing the Component Applications Licensing option registers the products for the
Applications component(s) specified in your license agreement. When you choose to register an Applications component, you register all the products that it includes. You can also register the products in a component individually. To view individual products, click the Prod. Detail
button. The Available Products screen appears.

All individual Oracle products are listed on this screen, including shared or dependent products. Notice that Rapid Install places a check mark beside the products you indicated on the register products screen. Shared or dependent products are shaded, so you cannot select them. Rapid Install automatically installs and registers these shared and dependent products for
you, so they are selected by default.
Review your selections on this screen. If you selected a component on the previous screen, you cannot deselect any of its individual products on this screen. If you want to install individual products instead of all the products in a component, press Cancel to return to the product registration screen. Uncheck Use Component Applications Licensing, and do not check any component applications. Click Prod. Detail and make individual product selections on the
Available Products screen. Click OK to return to the product registration screen.

Choosing the E-Business Suite Licensing option causes Rapid Install to automatically register all the products included in the E-Business Suite price bundle.
Note that some of the modules are grayed out. The ones that are not must be installed
separately as Add-on products — they are not part of the E-Business Suite price bundle. Click the Prod. Detail button to display the Available Products screen.

Most of the individual Add-on products are checked, indicating that they will be installed and registered automatically. If you want to select individual products, select them now.
If you have chosen to install a product that is controlled, Rapid Install displays an alert screen.
Follow the instructions on the screen as they apply to your installation. Click OK to return to the product registration screen. Click Next to continue.
Note: Once a product is registered, it cannot be unregistered.

Step 5: Select country-specific functionality

Select country-specific functionality
If you will use country-specific features, select one or more country-specific functionalities to install. All countries that Oracle supports are listed on this screen. Click the appropriate check box to make your selections. Click Next to continue.
Note: The Globalizations products, JA, JE, JG, and JL are associated with country-specific functionalities. They cannot be registered from the product registering screens. For example, when you select BR-Brazil, the JL product will be set to licensed.

Step 6: Select NLS settings

The fresh install database comes with American English installed as the default base language, AMERICA as the default territory, and US7ASCII as the default character set. You can select additional languages and modify the default base language and the default territory settings on this screen.
If you select additional languages, you may also need to select an alternative character set for the database and the APPL_TOP. Note that the conversion to a different character set will be automatic, but it may take several hours to complete.
The default territory is used as the NLS territory setting for all users of the Applications installation. The base language is used as the default NLS language setting. Choose a territory from the list of NLS territories if you want to change the default territory setting. Click Select Languages to see a list of available languages.
Note: Release 11i does not support changing NLS settings or the character set for the Vision Demonstration database.

In prior versions of Release 11i, the Rapid Install wizard only registered additional languages — it did not actually install them. In this release, the language files will be installed in the APPL_TOP file system during the installation. If you choose to add a language(s) on this screen, Rapid Install prompts you for the location of the language CD(s) later in the
installation. You must have the Oracle Applications Release 11i NLS CD pack available at that time.
Warning: If you do not have the language CDs available when Rapid Install prompts you for their location, the installation will stop and you will not be able to continue.
Double-click a language to move it into the Installed Languages list or highlight it and click the right arrow key. Highlighting a language in the Installed Languages box and clicking the left arrow removes it. You cannot remove American English from this list. Click OK to continue.

The Base Language list box now shows multiple language choices and the Database Character Set and APPL_TOP Character Set list boxes have changed to a character set that is common to all the selected languages.

If you want to change the database and APPL_TOP character set from the values shown, select a new value from the dropdown box. If you need to use a character set that is not shown in the dropdown box, enter the APPL_TOP character set name directly in the derived settings screen
(see Step 9), or the database character set name on the instance settings screen (see Step 10).
Click Next to continue.
Note: The languages you install must be compatible with the character set you choose.
After Rapid Install processing is complete, you must perform additional tasks to complete the
language installation. See the Oracle Applications NLS Release Notes for more information.

Step 7: Select host names

In a multi-node installation, the Rapid Install wizard asks for the names of the hosts where you will install the components for your installation. You can choose to install servers on any number of nodes, each running a different operating system. In the example, Rapid Install will set up the database server on a host named OASERV1 on a machine running on HP-UX. The remaining servers (concurrent processing server, admin server, forms server, and web server)
will be set up on separate hosts (OASERV2, OASERV3, and so on), each running on Linux.
Click Next to continue.

Step 8: Specify main settings information

Specify the main settings for the various top-level directories and user accounts for your installation.
The labels on the tabs are derived from the host names you specified on the previous screen. The parameters listed under each tab are determined by the platform you specified for the associated host. For example, if you set up an admin server node called OASERV3 that will run on Linux, the list includes Linux-specific settings such as APPS OS User and APPS OS
Group. If you indicated that the admin server node runs on Windows, the settings include NT Password and NT User, but not APPS OS User or APPS OS Group.
There is a separate tab for each node. Click on the tab to activate the settings list. Use the vertical scroll bar or the Up and Down Arrow keys to scroll through the main settings fields and default values. Change the values or accept the current settings.

If there are more than three hosts to be set up, click the arrow button in the top right corner to access the tabs that are not shown on the screen.
If you are configuring a number of application (middle) tiers to share a single APPL_TOP, the APPL_TOP base and COMMON_TOP base should be the same for all application tiers and should be set to the shared disk resource.

Main Settings
APPL_TOP base: This default directory mount setting appears on subsequent Rapid Install screens for convenience. This is the base directory for the APPL_TOP.
COMMON_TOP base: This is the base directory for the COMMON_TOP. It contains other
directories for files that are used across products or in conjunction with third-party products. This default directory mount setting appears on subsequent Rapid Install screens for convenience.
ORA_TOP base: The base directory for the RDBMS installation.
DATA_TOP (sys): The directory on the database node that contains the data dictionary for the entire RDBMS.
DATA_TOP (logs): The directory on the database node that contains the redo log files used by the RDBMS.
DATA_TOP (data): The directory on the database node that contains the data tablespaces.
Each product has its own data tablespace within this directory.
Apps OS User (UNIX): Owner of the Oracle Applications file system and Applications
technology stack.
Apps OS Group (UNIX): Default OS (operating system) group for the Applications OS user.
Oracle OS User (UNIX): Owner of the Oracle database file system.
Oracle OS Group (UNIX): Default OS (operating system) group for the Oracle OS user.
NT User (Windows): If you choose the Windows platform for running the concurrent
manager service, this is the user that runs the service.
NT Password (Windows): If you choose the Windows platform for running the concurrent
manager service, this is the password of the user that runs the service.
DNS Domain Name: A valid domain name used when configuring Oracle Applications for the network. This is a required field. You must enter a domain name that, when combined with a host name, produces a fully qualified domain name.
X DISPLAY (UNIX): Used by the reports server, forms server, Apache server, and the
concurrent manager, this display must always be accessible during runtime. Should be set to an active X Windows display, and should point to a machine that is always available to the Oracle Applications instance.

Step 9: Specify derived settings information

The following screen shows detailed settings for each host you set up on the main setting screen in the previous step. Click on a tab to activate the settings list. Use the vertical scroll bar or the Up and Down Arrow keys to scroll through the main settings fields and default values.
Click Next to continue.
Note: If you change a recommended character set (by overwriting the default) in this screen, be sure the languages you install are compatible with the character set you choose.

Derived Settings

APPL_TOP Mount Point, APPL_TOP aux 1, APPL_TOP aux 2, APPL_TOP aux 3: The
primary APPL_TOP mount point directory, and the auxiliary mount point directories used to distribute APPL_TOP among multiple directories or partitions. If you plan to install the entire file system on one mount point or disk, all APPL_TOP auxiliary directories should have the same mount point or disk name.
APPL_TOP Char. Set: The character set of the files within the file systems installed on the application tier. The character set is carried over from your selection in the NLS Settings of the Language screen.
COMMON_TOP: The common top directory holds directories for files that are used across
products or in conjunction with third-party products. It contains the:
• Rapid Install admin directory with subdirectories for concurrent manager log and out
directories, the install subdirectory (contains scripts used only during an install), and the
scripts subdirectory (contains scripts used for daily maintenance of the Oracle
Applications instance).
• html directory, which contains files used by html-based products such as JSP files, Java scripts, xml files, and style sheets.
• java directory where Rapid Install installs all Oracle Applications JAR files. It also holds 3rd-party Java files.
• portal directory, which contains Rapid Install Portal files.
• temp directory, which is used for caching by some processes such as Oracle Reports.
• util directory, which contains 3rd-party utilities such as JDK, JRE, and UnZip.
Database ORACLE_HOME: The ORACLE_HOME directory installed on the database tier.
It hosts the Oracle database server.
8.0.6 ORACLE_HOME: The ORACLE_HOME directory installed on each node of the
application tier. This ORACLE_HOME hosts the Oracle Developer components of the
Applications technology stack. The application tier file system is linked to this 8.0.6 ORACLE_ HOME.
Tool ORACLE_HOME: The ORACLE_HOME directory installed on each node of the
application tier that is used for the iAS components, including the Oracle HTTP server.

DATA_TOP (sys): The directory on the database node that contains the data dictionary for the entire RDBMS.
DATA_TOP (logs): The directory on the database node that contains the redo log files used by the RDBMS.
DATA_TOP (data): The directory on the database node that contains the data tablespaces. Each product has its own data tablespace within this directory.
DATA_TOP (index): The directory on the database node that contains the index tablespaces. Each product has its own index tablespace within this directory.
JAVA_TOP: Contains the Java files used by all Oracle Applications products. This directory is in the COMMON_TOP directory.
PORTAL_TOP: Contains the HTML used to review and complete the installation after Rapid Install. These include the post-install steps and the configuration files, written as HTML files.
JRE_TOP: Contains the Java Runtime Engine files used by all Oracle Applications products.These include the Java platform core classes and supporting files.
Temp Directory: Contains temporary files. This directory is not used during installation.

Apps OS User (UNIX): Owner of the Oracle Applications file system and Applications
technology stack.
Apps OS Group (UNIX): Default OS (operating system) group for the Applications OS user.
Oracle OS User (UNIX): Owner of the database file system.
Oracle OS Group (UNIX): Default OS (operating system) group for the Oracle OS user.
NT User (Windows): If you choose the Windows platform for running the concurrent
manager service, this is the user that runs the service.
NT Password (Windows): If you choose the Windows platform for running the concurrent
manager service, this is the password of the user that runs the service.
DNS Domain Name: The domain the machine is assigned to on the network.

MKS Directory (Windows): MKS tools must be added in the PATH so that executables can
be used to relink Applications executables and .DLLs.
MSDEV Directory (Windows): Location of the Visual C/C++ executables and .DLL files.
Used for linking Applications executables or .DLLs.
DBA Group Name (UNIX): The user that owns the Oracle database must belong to this
group.
X DISPLAY (UNIX): Used by the reports server, forms server, apache server, and the
concurrent manager, this display must always be accessible during runtime. Should be set to an active X Windows display, and should point to a machine that is always available to the Oracle Applications instance.
External JDK: Points to location of the JDK installation. Required by Apache to successfully start the JSERV engine. Must be the same as the location where you downloaded JDK.
OUI Inventory Path (Windows): Path used by the Oracle Universal Installer (OUI) for
installation. The Inventory contains components installed by Rapid Install and is used for patching in certain circumstances.

Step 10: Review instance settings information

Rapid Install uses the values specified on this screen to configure server processes, such as those on the forms and web servers, as well as listener processes.

The starting number of the port value is pre-set, but you can set the incremental number by using the dropdown list in the Port Pool field. The Port Pool list provides a way to use a predefined set of server ports. There are 100 port pool selections.

For example, if you select 3, the default Database Port number (1521) becomes 1524
(increment of 3). If you do not want to use the port pool feature, you can specify ports manually by adjusting individual server port values where appropriate.
Use the vertical scroll bar or the Up and Down Arrow keys to move through the instance settings. You can change the values or accept the current settings. Click Next to continue.

Instance Settings
SID: Name of the database instance.
Global Name: Global database name.
DB Character Set: Character set of the database.
Default Territory: Defines the NLS territory.
Database Port: Net Services Listener port that receives requests from the various servers for processing on the RDBMS.
RPC Port: Oracle Applications uses the Report Review Agent (an RPC server process). The RPC port is the TCP/IP port on the concurrent processing server node that receives incoming Report Review Agent requests.
Reports Port: TCP/IP port on the reports server that receives incoming requests from
browsers or other servers.
Web Listener Port: HTTP port on the web server that receives incoming requests from
browsers or other servers.
OProcMgr Port: Port for process that monitors the Apache JVM state. Routes requests for the JVM.

Instance Settings
Web PLSQL Port:. Port that receives PL/SQL data and HTML content from the database and re-directs it to the PL/SQL Apache listener.
Servlet Port: Port on the web server that browsers connect to when invoking Java servlets.
Forms Listener Port: TCP/IP port on the forms server that receives incoming requests from browsers or other servers.
Metrics Server Data Port: TCP/IP port on which the Metrics server receives load data from
Metrics clients running on other machines.
Metrics Server Req. Port: TCP/IP port on which the Metrics server receives the “least-loaded
host” requests from forms clients.
JTF Fulfillment Server Port: TCP/IP port on which the Fulfillment server receives requests from a remote process.
Map Viewer Servlet Port: Dedicated TCP/IP servlet port on the web server that receives requests for the Map Viewer.
OEM Web Utility Port: Dedicated TCP/IP port on the web server that receives requests for the Oracle Enterprise Manager.
VisiBroker OrbServer Agent Port: Dedicated TCP/IP port on the web server that receives requests for the VisiBroker OrbServer agent, used by Oracle Discoverer.
MSCA Server Port: Port used by the MSCA (Mobile Supply Chain Applications) server.
MSCA Dispatcher Port: Port used by the MSCA Dispatcher.
OACORE Servlet Port Range: Range of ports used by the OACORE servlets (for the Self-
Service framework).
Discoverer Servlet Port Range: Range of ports used by the Discoverer servlets.
Forms Servlet Port Range: Range of ports used for the Forms Servlet JServ processes.
XMLSVCS Servlet Port Range: Range of ports used by the XML service.

Step 11: Save the configuration file

You have now completed all the information Rapid Install needs to install your Oracle
Applications products. The next screen asks you to save your installation settings in a configuration file. This file (config.txt) stores the configuration parameters that you entered on the settings screens.

The default is to store the configuration file in the system temporary directory. It’s a good idea to choose a permanent directory location because you will use this file to complete the installation on the other nodes. Enter a directory path or click Browse. Select a location and click Next to continue.

Step 12: Review pre-install test results

Rapid Install begins to perform a series of system checks to validate the configuration described by your configuration file. As the parameters are tested, the results of the validation
checks are recorded in the system test dialog box. When the tests are complete, Rapid Install provides a check list of the tests that it performed and an indication of whether the tests succeeded or failed.

Parameter Validation
The parameters that Rapid Install validates include:
• Port Availability validates the ports you selected are available for use.
• Operating System Check validates the patches and/or version levels of your operating system.
• Port Uniqueness validates that there are no duplicate defined ports for server processes.
• File System Check validates that file system mount points exist and have correct privileges.
• File Space Check validates that file system mount points have sufficient space.
• Host/Domain Check validates that host and domain names are verified by the DNS
server.
• System Utilities Check validates that linking utilities (make, ld, and cc) are available.
• JDK Availability Check validates that JDK exists in the location you supplied.
The results of each test are displayed using check list icons. Scroll down the list to see the
results. There are three result types:
• Check mark: The test succeeded.
• Exclamation mark (!): The configuration requires review. Click the ! to get information about the system test review. Click Yes to continue, and No if you are going to resolve the issues. Rapid Install alerts you if you continue without resolving the issues.
• An x mark: All issues marked x must be resolved before you continue with the
installation. Click the x to see the errors. If you can resolve an issue by fixing the values provided on the settings screen(s), click Back until you reach the appropriate screen, and re-enter the values. Some tests must be resolved in the operating system. In that case, you may have to restart the Rapid Install wizard after the problem has been fixed. When there are no issues to resolve, click Next to continue.

Step 13: Begin the installation (on the database node)

Rapid Install lists the actions it will take during the installation process. The list varies based on your installation. Click Next to continue.
Rapid Install displays another alert screen asking you to verify that you are ready to begin the installation. Click Yes to continue. Rapid Install begins the installation. When the installation is complete, you have installed Oracle Applications on the database node.

Step 14: Complete the installation on the
other nodes

Completion of the database node installation is the first step in a multi-node installation. Next, you must set up the additional nodes. You can set up additional nodes in any order.
1. Copy the configuration file (config.txt) you saved in Step 11 to each of the other nodes in your installation.
2. Start Rapid Install, and choose the Install Oracle Applications option. Click Next to continue.
3. On the next screen (configuration type), choose the Read Configuration from File option.
Click Next to continue.
4. Rapid Install prompts you for the location of the config.txt file on this node.Enter the directory path, or click Browse. Then click Next to continue.
5. Because you set up the parameters for all nodes when you first created the configuration file, Rapid Install automatically moves past the main settings, derived settings, and instance settings screens. It goes directly to the pre-install test and begins the validation process. See Step 12 for details.
6. Review or resolve any issues flagged with ! or x on the pre-install test results screen.
Then, click Back to return to the screen where you saved the configuration file. Click Next to re-run the pre-install test. If there are no issues listed on the summary screen, click Next to continue.
7. Click Next when each alert screen is displayed. Rapid Install begins the installation on the second node.
8. Repeat all the steps in this section for each of the other nodes in your installation.

UNIX / LINUX COMMAND

2009-04-08T11:49:00.000-07:00

UNIX / LINUX OVERVIEW
Below is a listing of each of the Unix / Linux commands currently listed on Computer Hope and a brief explanation of what each of the commands do. The following commands are all the Unix / Linux commands, which means not all the below commands will work in your distribution of Unix / Linux and may also not work because of your privileges.

COMMAND : DESCRIPTION

a2p
Creates a Perl script from an awk script.

alias
Create a name for another command or long command string.

ac
Prints statistics about users' connect time.

ar
Maintain portable archive or library.

arch
Display the architecture of the current host.

as
An assembler.

at
Command scheduler.

awk
Awk script processing program.

basename
Deletes any specified prefix from a string.

bash
Command Bourne interpreter
bc
Calculator.

bdiff
Compare large files.

bfs
Editor for large files.

bg
Continues a program running in the background.

biff
Enable / disable incoming mail notifications.

break
Break out of while, for, foreach, or until loop.

bs
Battleship game.

bye
Alias often used for the exit command.

cal
Calendar

calendar
Display appointments and reminders.

cancel
Cancels a print job.

cat
View and/or modify a file.

cc
C compiler.

cd
Change directory.

chdir
Change directory.

checkeq
Language processors to assist in describing equations.

checknr
Check nroff and troff files for any errors.

chfn
Modify your own information or if super user or root modify another users information.

chgrp
Change a groups access to a file or directory.

chkey
Change the secure RPC key pair.

chmod
Change the permission of a file.

chown
Change the ownership of a file.

chsh
Change login shell.

cksum
Display and calculate a CRC for files.

clear
Clears screen.

cls
Alias often used to clear a screen.

cmp
Compare files.

col
Reverse line-feeds filter.

comm
Compare files and select or reject lines that are common.

compress
Compress files on a computer.

continue
Break out of while, for, foreach, or until loop.

copy
Copy files.

cp
Copy files.

cpio
Creates archived CPIO files.

crontab
Create and list files that you wish to run on a regular schedule.

csh
Execute the C shell command interpreter

csplit
Split files based on context.

ctags
Create a tag file for use with ex and vi.

cu
Calls or connects to another UNIX system, terminal or non-UNIX system.

cut
Cut out selected fields of each line of a file.

date
Tells you the date and time in UNIX.

dc
An arbitrary precision arithmetic package.

df
Display the available disk space for each mount.

deroff
Removes nroff/troff, tbl, and eqn constructs.

diff
Displays two files and prints the lines that are different.

dircmp
Lists the different files when comparing directories.

dpost
Translates files created by troff into PostScript.

du
Tells you how much space a file occupies.

echo
Displays text after echo to the terminal.

ed
Line oriented file editor.

edit
Text editor.

egrep
Search a file for a pattern using full regular expressions.

elm
Program command used to send and receive e-mail.

emacs
Text editor.

enable
Enables / Disables LP printers.

env
Displays environment variables.

eqn
Language processors to assist in describing equations.

ex
Line-editor mode of the vi text editor.

exit
Exit from a program, shell or log you out of a UNIX network.

expand
Expand copies of file s.

expr
Evaluate arguments as an expression.

fc
The FC utility lists or edits and re-executes, commands previously entered to an interactive sh.

fg
Continues a stopped job by running it in the foreground

fgrep
Search a file for a fixed-character string.

file
Tells you if the object you are looking at is a file or if it is a directory.

find
Finds one or more files assuming that you know their approximate filenames.

finger
Lists information about the user.

fmt
Simple text formatters.

fold
Filter for folding lines.

for
Shell built-in functions to repeatedly execute action(s) for a selected number of times.

foreach
Shell built-in functions to repeatedly execute action(s) for a selected number of times

ftp
Enables ftp access to another terminal.

getfacl
Display discretionary file information.

gprof
The gprof utility produces an execution profile of a program.

grep
Finds text within a file.

groupadd
Creates a new group account.

groupdel
Enables a super user or root to remove a group.

groupmod
Enables a super user or root to modify a group.

gunzip
Expand compressed files.

gview
A programmers text editor.

gvim
A programmers text editor.

gzip
Compress files.

halt
Stop the computer.

hash
Remove internal hash table.

hashstat
Display the hash stats.

head
Displays the first ten lines of a file, unless otherwise stated.

help
If computer has online help documentation installed this command will display it.

history
Display the history of commands typed.

hostname
Set or print name of current host system.

id
Shows you the numeric user and group ID on BSD.

ifconfig
Sets up network interfaces.

isalist
Display the native instruction sets executable on this platform.

jobs
List the jobs currently running in the background.

join
Joins command forms together.

keylogin
Decrypt the user's secret key.

kill
Cancels a job.

ksh
Korn shell command interpreter.

ld
Link-editor for object files.

ldd
List dynamic dependencies of executable files or shared objects.

less
Opposite of the more command.

lex
Generate programs for lexical tasks.

ln
Creates a link to a file.

lo
Allows you to exit from a program, shell or log you out of a UNIX network.

locate
List files in databases that match a pattern.

login
Signs into a new system.

logname
Returns users login name.

logout
Logs out of a system.

lp
Prints a file on System V systems.

lpadmin
Configure the LP print service.

lpc
Line printer control program.

lpq
Lists the status of all the available printers.

lpr
Submits print requests.

lprm
Removes print requests from the print queue.

lpstat
Lists status of the LP print services.

ls
Lists the contents of a directory.

mach
Display the processor type of the current host.

mail
One of the ways that allows you to read/send E-Mail.

mailcompat
Provide SunOS 4.x compatibility for the Solaris mailbox format.

mailx
Mail interactive message processing system.

make
Executes a list of shell commands associated with each target.

man
Display a manual of a command.

mesg
Control if non-root users can send text messages to you.

mkdir
Create a directory.

more
Displays text one screen at a time.

mount
Disconnects a file systems and remote resources.

mt
Magnetic tape control.

mv
Renames a file or moves it from one directory to another directory.

neqn
Language processors to assist in describing equations.

netstat
Shows network status.

newalias
Install new elm aliases for user and/or system.

newform
Change the format of a text file.

newgrp
Log into a new group.

nice
Invokes a command with an altered scheduling priority.

niscat
Display NIS+ tables and objects.

nischmod
Change access rights on a NIS+ object.

nischown
Change the owner of a NIS+ object.

nischttl
Change the time to live value of a NIS+ object.

nisdefaults
Display NIS+ default values.

nisgrep
Utilities for searching NIS+ tables.

nismatch
Utilities for searching NIS+ tables.

nispasswd
Change NIS+ password information.

nistbladm
NIS+ table administration command.

nohup
Runs a command even if the session is disconnected or the user logs out.

nroff
Formats documents for display or line-printer.

nslookup
Queries a name server for a host or domain lookup.

on
Execute a command on a remote system, but with the local environment.

onintr
Shell built-in functions to respond to (hardware) signals.

optisa
Determine which variant instruction set is optimal to use.

pack
Shrinks file into a compressed file.

pagesize
Display the size of a page of memory in bytes, as returned by getpagesize.

passwd
Allows you to change your password.

paste
Merge corresponding or subsequent lines of files.

pax
Read / write and writes lists of the members of archive files and copy directory hierarchies.

pcat
Compresses file.

perl
Perl is a programming language optimized for scanning arbitrary text files, extracting information from those text files.

pg
Files perusal filters for CRTs.

pgrep
Examine the active processes on the system and reports the process IDs of the processes

pico
Simple and very easy to use text editor in the style of the Pine Composer.

ping
Sends ICMP ECHO_REQUEST packets to network hosts.

pkill
Examine the active processes on the system and reports the process IDs of the processes

poweroff
Stop the computer.

pr
Formats a file to make it look better when printed.

priocntl
Display's or set scheduling parameters of specified process(es)

printf
Write formatted output.

ps
Reports the process status.

pvs
Display the internal version information of dynamic objects within an ELF file.

pwd
Print the current working directory.

quit
Allows you to exit from a program, shell or log you out of a UNIX network.

rcp
Copies files from one computer to another computer.

reboot
Stop the computer.

red
Line oriented file editor.

rehash
Recomputes the internal hash table of the contents of directories listed in the path.

remsh
Runs a command on another computer.

repeat
Shell built-in functions to repeatedly execute action(s) for a selected number of times.

rgview
A programmers text editor.

rgvim
A programmers text editor.

rlogin
Establish a remote connection from your terminal to a remote machine.

rm
Deletes a file without confirmation (by default).

rmail
One of the ways that allows you to read/send E-Mail.

rmdir
Deletes a directory.
rn
Reads newsgroups.

rpcinfo
Report RPC information.

rsh
Runs a command on another computer.

rview
A programmers text editor.
rvim
A programmers text editor.

s2p
Convert a sed script into a Perl script.

sag
Graphically displays the system activity data stored in a binary data file by a previous sar run.

sar
Displays the activity for the CPU.

script
Records everything printed on your screen.

sdiff
Compares two files, side-by-side.

sed
Allows you to use pre-recorded commands to make changes to text.

sendmail
Sends mail over the internet.

set
Set the value of an environment variable.

setenv
Set the value of an environment variable.

setfacl
Modify the Access Control List (ACL) for a file or files.

settime
Change file access and modification time.

sh
Runs or processes jobs through the Bourne shell.

shutdown
Turn off the computer immediately or at a specified time.

sleep
Waits a x amount of seconds.

sort
Sorts the lines in a text file.

spell
Looks through a text file and reports any words that it finds in the text file that are not in the dictionary.

split
Split a file into pieces.

stop
Control process execution.

stty
Sets options for your terminal.

su
Become super user or another user.

sysinfo
Get and set system information strings.

tabs
Set tabs on a terminal.

tail
Delivers the last part of the file.

talk
Talk with other logged in users.

tar
Create tape archives and add or extract files.

tbl
Preprocessor for formatting tables for nroff or troff.

tcopy
Copy a magnetic tape.

tee
Read from an input and write to a standard output or file.

telnet
Uses the telnet protocol to connect to another remote computer.

time
Used to time a simple command.

timex
The timex command times a command; reports process data and system activity

touch
Change file access and modification time.

tput
Initialize a terminal or query terminfo database.

tr
Translate characters.

traceroute
Print the route packets take to network host.

troff
Typeset or format documents.

ul
Reads the named filenames or terminal and does underlining.

umask
Get or set the file mode creation mask.

unalias
Remove an alias.

unhash
Remove internal hash table.

uname
Print name of current system.

uncompress
Uncompressed compressed files.

uniq
Report or filter out repeated lines in a file.

unmount
Crates a file systems and remote resources.

unpack
Expands a compressed file.

untar
Create tape archives and add or extract files.

until
Execute a set of actions while/until conditions are evaluated TRUE.

useradd
Create a new user or updates default new user information.

userdel
Remove a users account.

usermod
Modify a users account.

vacation
Reply to mail automatically.

vedit
Screen-oriented (visual) display editor based on ex.

vgrind
Grind nice program listings

vi
Screen-oriented (visual) display editor based on ex.

vim
A programmers text editor.

view
A programmers text editor.

wait
Await process completion.

wc
Displays a count of lines, words, and characters in a file

whereis
Locate a binary, source, and manual page files for a command.

while
Repetitively execute a set of actions while/until conditions are evaluated TRUE.

which
Locate a command.

who
Displays who is on the system.

whois
Internet user name directory service.

write
Send a message to another user.

X
Execute the X windows system.

xfd
Display all the characters in an X font.

xlsfonts
Server font list displayer for X.

xset
User preference utility for X.

xterm
Terminal emulator for X.

xrdb
X server resource database utility.

yacc
Short for yet another compiler-compiler, yacc is a compiler.

yppasswd
Changes network password in the NIS database.

zcat
Compress files.

Upgrade 11.5.9 to 11.5.10.2

2009-04-08T11:39:00.000-07:00

Procedure followed to Upgrade 11.5.9 to 11.5.10.2

Documents followed
Metalink Oracle Document ID : 316365.1 – Oracle Applications Release 11.5.10.2 Main. Pack Instlation Instructions
Instructions
Metalink Oracle Document ID : 259484.1 - Release 11.5.10 Maintenance Pack Installation

Reference
216205 – Database Initilization Parameters and Conf for Oracle Apps 11i

Basic Maintenance

1. Check the Disk space – Minimum space required 3 – 4 GB Free space.
2. Add additional table space for this tablespace BISD, IGFD,ARD,AHLX,OZFX,OKSX,OKCD,IGSX,PNX

ALTER DATABASE DATAFILE ‘path/okcd01.dbf’ RESIZE 150M; / EXTEND ON etc.

3. Check the Invalid Objects
4. Check the Missing files
5. Recompile Apps Schema, Forms, Reports etc
6. Regenerate Env, Jar Files etc.
7. Check hostname, domainname
8. Check file-max, Limit Processors etc.
9. Check the kernel parameters

1. Apply latest AD utility Patch – 4712852

2. Apply patch 4238286 – TUMS MP

3. Apply necessary patches as reported by TUMS

4. Apply 4709948 – Technology Stack Validation Report

5. Apply 3072811 – Oracle HTTP server patch [Stop the apache server and apply, before applying
a. To stop apache $COMMON_TOP/admin/scripts/sid_host/adapcctl.sh stop
a. export ORACLE_HOME = IAS_ORACLE_HOME path
b. ./patch.sh install
c. Unzip oprocmgr – check
d. Start apache -$COMMON_TOP/admin/scripts/sid_hos/adappctl.sh start

6. In Application Tier – In context file – SID_host.xml in $APPL_TOP/admin dir
Symbolic link pointing to $COMMON_TOP, $JAVA_TOP, PORTAL to be replaced with direct link not through symbolic link for ex.

/erpp – is s a symbolic link to /data/erpp & is available in / file system

In Context file it might have mentioned as /erpp/erppcomn/html to be replaced with /data/erpp/erppcomn

7. s_html, s_javatop, s_pt,s_oemcomment,s_toplevel domain - comment these lines in SID_host.xml – Application Context file.

8. Apply patch 4318672 – Technology Stack Validation Utility

9. _system_trig_enabled=false – initSID.ora

10. REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE –initSID.ora

11. orapwd file = /erpp/erppdb/9.2.0/dbs/orapw password=admin

12. Grant sysdba to apps

13. perl Txkrun.pl –script –InstSSLFix

14. Apply 4948577 – Release 6i patch 18

15. Apply 3966092 – one off patch 8.1.7.4 – IAS_ORACLE_HOME

16. Apply 4968700 – Additional Developer 6i patch

17. Apply 4261542 -

18. Apply 4888294 - Application Interoperability patch for Developer 6i patch 18

19. Apply 2376472 - 8.1.7.4 patchset for Oracle Data Server [Application].
[8.0.6 Oracle Database used in 11.5.9 is obsolete].

20. Apply 4029076 - Delivery of http client.jar [1022A],OJSP 11352 provider jar and SSL patch for IAS Technology Stack.

21. Copy perl directory from SIDora/Apache to /SIDdb/9.2.0/Apache [Problems faced in perl installation available in SIDdb directory].

22. DIRECTORY ACCESSBILITY=FALSE – initSID.ora

23. _B_TREE_BITMAP_PLANS=FALSE – initSID.ora

24. ALTER TABLESPACE SYSTEM ADD datafile ‘path/system12.dbf’ size 1024M;

25. Apply patch 5035661 – Oracle Universal Installer 2.2

26. Apply patch 4163445 - Database upgrade 9.2.0.3 to 9.2.0.7

27. Apply 2617419 – O Patch

28. Apply 4483951 – Query gives wrong results in 9.2.0.7 version

29. Ensure 750 MB Free rollback segment space is available

30. Ensure 512 Max Extents ??

31. Apply patch 3180164 – Migrate to New table space system followed by 11.5.10.2

32. Apply patch 3348000 – Maintenace Pack 11.5.10.2 – CU2 patch. [After applying the patch, patch itself will take care of
a. Compiling Invalid Objects
b. Running Auto Config
c. Compile Flexfield
d. Recreate grants and synonyms
e. Main. Multiple Reporting Currencies etc.

33. In Application Context file of apps tier and change the following
"s_options_symlinks" from "Options -FollowSymLinks"
"s_options_symlinks" to "Options FollowSymLinks"

34. Start the Application Services

Single-node Installation

2009-04-08T04:54:00.000-07:00

Single-node Installation
For a single-node installation, everything is installed on one node. All Oracle Applications components, Applications technology stack components, and the database are installed on a single machine. It’s a rather seamless installation in that all components are pre-certified.
This environment is suitable for situations where a large volume of transactions is notexpected, such as a development or training environment. A large production environment with heavy transaction volume is more likely to require a multi-node installation.

Setting Up a Single-node Installation
Running the Rapid Install Wizard
Step 1: Choose an installation operation
Step 2: Choose a configuration type
Step 3: Choose environment
Step 4: Register Applications products and
components
Step 5: Select country-specific functionality
Step 6: Select NLS settings
Step 7: Specify main settings information
Step 8: Specify derived settings information
Step 9: Review instance settings information
Step 10: Save the configuration file
Step 11: Review pre-install test results
Step 12: Begin the installation

Step 1: Choose an installation operation
Choose an installation operation
Upon starting Rapid Install with the rapidwiz command, the first screen appears.
Click the Install Oracle Applications button. Pressing the Tab key moves the cursor between options. Moving the cursor by pressing the Up or Down Arrow keys selects the option as well.

Click Next to continue.
Note: The Upgrade Actions are used when upgrading a 10.7 or 11.0 Oracle Applications
system to Release 11i.

Step 2: Choose a configuration type
Choose a configuration type
Choose the Single Node option to set up a system with the database, admin, concurrent
processing, forms, and web servers on a single node. Click Next to continue

Step 3: Choose environment
Choose environment: Database name
In order to identify the Oracle Applications system, give it a name and indicate the type of database you want to install.
Rapid Install uses default names that suggest possible uses for the environment:
• A production environment is a fully configured Release 11i installation that could be
used for live production purposes.
• A test environment is a mirror image of a production Release 11i installation. You should
use it to test your installation before you go live.
• A Vision Demo environment can be used to set up a Release 11i installation for
demonstration purposes.
Note: If you want to install another instance, for example, to use as a test environment, run the
Rapid Install wizard again and identify the new environment on this screen.

Step 3: Choose environment
Choose environment: Database type
After you type the name, choose the type of database that you want to install. The database
options are:
• Fresh install database: An empty Oracle database, typically used for production or
testing.
• Vision Demo database: An Oracle Vision Demonstration database used to set up demos
or for evaluation purposes.
• Do not install a database: This option assumes an existing database. It creates an
Applications instance and configures it using your existing database. It is typically used
for Windows clusters.
Click Next to continue.

Step 4: Register Applications products and components
Register Applications products and components: Component Applications
licensing Rapid Install installs all products regardless of their licensed status. You use this screen to register the products that you have licensed for use in your system. Your Oracle Corporation license agreement specifies the type of license that applies to your installation. Click Use
Component Applications Licensing or Use E-Business Suite Licensing, according to the
agreement. Rapid Install automatically installs and registers shared and dependent products for
you, so they are selected by default.
Choosing the Component Applications Licensing option registers the products for the
Applications component(s) specified in your license agreement. When you choose to register
an Applications component, you register all the products that it includes. You can also register the products in a component individually. To view individual products, click the Prod. Detail button. The Available Products screen appears.

Step 4: Register Applications products and components
Register Applications products and components: Component Applications
licensing
All individual Oracle Applications products are listed on this screen, including shared or dependent products. Notice that Rapid Install places a check mark beside the products you indicated on the register products screen. Shared or dependent products are shaded, so you cannot select them. Rapid Install automatically installs and registers these shared and dependent products for you, so they are selected by default.
Review your selections on this screen. If you selected a component on the previous screen, you cannot deselect any of its individual products on this screen. If you want to install individual products instead of all the products in a component, press Cancel to return to the product registration screen. Uncheck all component applications, then click Prod. Detail and make individual product selections on the Available Products screen. Click OK to return to the product registration screen.

Register Applications products and components: E-Business Suite Licensing
Choosing the E-Business Suite Licensing option causes Rapid Install to automatically register all the products included in the E-Business Suite price bundle.
Note that some of the modules are grayed out. The ones that are not must be installed
separately as Add-on products — they are not part of the E-Business Suite price bundle. Click the Prod. Detail button to display the Available Products screen.
Register Applications products and components: E-Business Suite Licensing
Most of the individual Add-on products are checked, indicating that they will be installed and registered automatically. If you want to select additional individual products, select them at this time.
If you have chosen to install a product that is controlled, Rapid Install displays an alert screen.
Follow the instructions on the screen as they apply to your installation. Click OK to return to the product registration screen. Click Next to continue.

Step 5: Select country-specific functionality

Select country-specific functionality
If you will use country-specific features, register them on this screen. All countries that Oracle supports are listed on this screen. Click the appropriate check box to make your selections.
Click Next to continue.
Note: The Globalizations products, JA, JE, JG, and JL are associated with country-specific functionalities. They cannot be registered from the product registering screens. For example, when you select BR-Brazil, the JL product will be set to licensed.

Step 6: Select NLS settings
Select NLS settings
The fresh install database comes with American English installed as the default base language, AMERICA as the default territory, and US7ASCII as the default character set. You can select additional languages and modify the default base language and the default territory settings on this screen.
If you select additional languages, you may also need to select an alternative character set for the database and the APPL_TOP. Note that the conversion to a different character set will be automatic, but it may take several hours to complete.
The default territory is used as the NLS territory setting for all users of the Applications installation. The base language is used as the default NLS language setting. Choose a territory from the list of NLS territories if you want to change the default territory setting. Click Select Languages to see a list of available languages.
Note: Release 11i does not support changing NLS settings or the character set for the Vision Demonstration database.

Step 6: Select NLS settings
Select NLS settings: Languages
In prior versions of Release 11i, the Rapid Install wizard only registered additional languages — it did not actually install them. In this release, the language files will be installed in the APPL_TOP file system during the installation. If you choose to add a language(s) on this screen, Rapid Install prompts you for the location of the language CD(s) later in the
installation. You must have the Oracle Applications Release 11i NLS CD pack available at that time.
Warning: If you do not have the language CDs available when Rapid Install prompts you for their location, the installation will stop and you will not be able to continue.
Double-click a language to move it into the Installed Languages list or highlight it and click the right arrow key. Highlighting a language in the Installed Languages box and clicking the left arrow removes it. You cannot remove American English from this list. Click OK to continue.

Step 6: Select NLS settings: Base Language
The Base Language list box now shows multiple language choices and the Database Character Set and APPL_TOP Character Set list boxes have changed to a character set that is common to all the selected languages.

Step 6: Select NLS settings: Character Set
If you want to change the database and APPL_TOP character set from the values shown, select a new value from the dropdown box. If you need to use a character set that is not shown in the dropdown box, enter the APPL_TOP character set name directly in the derived settings screen
(see Step 8), or the database character set name on the instance settings screen (see Step 9).
Click Next to continue.
Note: The languages you install must be compatible with the character set you choose.
After Rapid Install processing is complete, you must perform additional tasks to complete the language installation. See the Oracle Applications NLS Release Notes for more information.

Step 7: Specify main settings information
Specify the main settings for the various top-level directories and user accounts for your installation. The values you define here are used to calculate the settings for the system and are carried over to subsequent screens where you define the detailed directory and instance settings.
The tab label is derived from the name of the machine you are using to run the Rapid Install wizard. The default directories are created using the appropriate operating system syntax for that machine. Windows syntax is shown in the example.
Use the vertical scroll bar or the Up and Down Arrow keys to move the main settings fields and default values into view. Click Next to continue.

Main Settings
APPL_TOP base: This default directory mount setting appears on subsequent Rapid Install screens for convenience. This is the base directory for the APPL_TOP.
COMMON_TOP base: This is the base directory for the COMMON_TOP. It contains other
directories for files that are used across products or in conjunction with third-party products. This default directory mount setting appears on subsequent Rapid Install screens for convenience.
ORA_TOP base: The base directory for the RDBMS installation.
DATA_TOP (sys): The directory on the database node that contains the data dictionary for the entire RDBMS.
DATA_TOP (logs): The directory on the database node that contains the redo log files used by the RDBMS.
DATA_TOP (data): The directory on the database node that contains the data tablespaces.
Each product has its own data tablespace within this directory.
Main Settings
Apps OS User (UNIX): Owner of the Oracle Applications file system and technology stack.
Apps OS Group (UNIX): Default OS (operating system) group for the Applications OS user.
Oracle OS User (UNIX): Owner of the Oracle database file system.
Oracle OS Group (UNIX): Default OS (operating system) group for the Oracle OS user.
NT User (Windows): If you choose the Windows platform for running the concurrent
manager service, this is the user that runs the service.
NT Password (Windows): If you choose the Windows platform for running the concurrent
manager service, this is the password of the user that runs the service.
DNS Domain Name: A valid domain name used when configuring Oracle Applications for
the network. This is a required field. You must enter a domain name that, when combined with a host name, produces a fully qualified domain name. For example, in the fully qualified domain name myhost.in.appsdbaportal.com, the domain name is in.appsdbaportal.com.
X DISPLAY (UNIX): Used by the reports server, forms server, Apache server, and the
concurrent manager, this display must always be accessible during runtime. Should be set to an active X Windows display, and point to a machine that is always available to the Oracle Applications instance.

Step 8: Specify derived settings
information

Step 8: Specify derived settings information
This screen shows detailed settings for the environment. The values are derived from those entered on the main settings screen in the previous step. Use the vertical scroll bar or the Up and Down Arrow keys to move the derived settings fields and default values into view. Click
Next to continue.
Note: If you change a recommended character set (by overwriting the default) in this screen,
be sure the languages you install are compatible with the character set you choose.

Derived Settings
Derived Settings
APPL_TOP Mount Point, APPL_TOP aux 1, APPL_TOP aux 2, APPL_TOP aux 3: The
primary APPL_TOP mount point directory and the auxiliary mount point directories used to distribute APPL_TOP among multiple directories or partitions.
If you plan to install the entire file system on one mount point or disk, all APPL_TOP auxiliary directories should have the same mount point or disk name. In this case, your APPL_TOP disk space requirements will be 12 - 14 GB, depending on your platform.
APPL_TOP Char. Set: The character set of the files within the file systems installed on the application tier. The character set is carried over from your selection in the NLS Settings of the Language screen.
COMMON_TOP: The common top directory holds directories for files that are used across
products or in conjunction with third-party products. It contains the:
• Rapid Install admin directory with subdirectories for concurrent manager log and out directories, the install subdirectory (contains scripts used only during an install), and the scripts subdirectory (contains scripts used for daily maintenance of the Oracle Applications instance).
• html directory, which contains files used by html-based products such as JSP files, Java scripts, xml files, and style sheets.
• java directory where Rapid Install installs all Oracle Applications JAR files. It also holds 3rd-party Java files.
• portal directory, which contains Rapid Install Portal files.
• temp directory, which is used for caching by some processes such as Oracle Reports.
• util directory, which contains 3rd-party utilities such as JDK, JRE, and UnZip.
Database ORACLE_HOME: The ORACLE_HOME directory installed on the database tier.It hosts the Oracle database server.
8.0.6 ORACLE_HOME: The ORACLE_HOME directory installed on each node of the
application tier. This ORACLE_HOME hosts the various servers of the Applications
technology stack. The application tier file system is linked to this 8.0.6 ORACLE_ HOME.
Tool ORACLE_HOME: The ORACLE_HOME directory installed on each node of the
application tier. It is used for the Oracle iAS components including the Oracle HTTP server.
DATA_TOP (sys): The directory on the database node that contains the data dictionary for the entire RDBMS.
DATA_TOP (logs): The directory on the database node that contains the redo log files used by the RDBMS.
DATA_TOP (data): The directory on the database node that contains the data tablespaces. Each product has its own data tablespace within this directory.
DATA_TOP (index): The directory on the database node that contains the index tablespaces. Each product has its own index tablespace within this directory.
JAVA_TOP: Contains the Java files used by all Oracle Applications products. This directory is in the COMMON_TOP directory.
PORTAL_TOP: Contains the HTML used to review and complete the installation after Rapid Install. These include the post-install steps and the configuration files, written as HTML files.
JRE_TOP: Contains the Java Runtime Engine files used by all Oracle Applications products. These include the Java platform core classes and supporting files.
Temp Directory: Contains temporary files. This directory is not used during installation.
Apps OS User (UNIX): Owner of the Oracle Applications file system and the Applications technology stack.
Apps OS Group (UNIX): Default OS (operating system) group for the Applications OS user.
Oracle OS User (UNIX): Owner of the database file system.
Oracle OS Group (UNIX): Default OS (operating system) group for the Oracle OS user.
NT User (Windows): If you choose the Windows platform for running the concurrent
manager service, this is the user that runs the service.
NT Password (Windows): If you choose the Windows platform for running the concurrent
manager service, this is the password of the user that runs the service.
DNS Domain Name: The domain the machine is assigned to on the network.

MKS Directory (Windows): MKS tools must be added in the PATH so that executables can
be used to relink Applications executables and .DLLs.
MSDEV Directory (Windows): Location of the Visual C/C++ executables and .DLL files.
Used for linking Applications executables or .DLLs.
DBA Group Name (UNIX): The user that owns the Oracle database must belong to this
group.
X DISPLAY (UNIX): Used by the reports server, forms server, apache server, and the
concurrent manager, this display must always be accessible during runtime. Should be set to an
active X Windows display, and should point to a machine that is always available to the Applications instance.
External JDK: Points to location of the JDK installation. Required by Apache to successfully start the JSERV engine. Must be the same as the location where you downloaded JDK.
OUI Inventory Path (Windows): Path used by the Oracle Universal Installer (OUI) for
installation. The Inventory contains components installed by Rapid Install and is used for patching in certain circumstances.

Step 9: Review instance settings information
Rapid Install uses the values specified on this screen to configure server processes, such as those on the forms and web servers, as well as listener processes.

Step 9: Review instance settings information: Port Pool
The starting number of the port value is pre-set, but you can set the incremental number by using the dropdown list in the Port Pool field. The Port Pool list provides a way to use a predefined set of server ports. There are 100 port pool selections.

Step 9: Review instance settings information
For example, if you select 3, the default Database Port number (1521) becomes 1524
(increment of 3). If you do not want to use the port pool feature, you can specify ports manually by adjusting individual server port values where appropriate.
Use the vertical scroll bar or the Up and Down Arrow keys to move through the instance settings. You can change the values or accept the current settings. Click Next to continue.

Instance Settings

Instance Settings
SID: Name of the database instance.
Global Name: Global database name.
DB Character Set: Character set of the database.
Default Territory: Defines the NLS territory.
Database Port: Net Services Listener port that receives requests from the various servers for processing on the RDBMS.
RPC Port: Oracle Applications uses the Report Review Agent (an RPC server process). The RPC port is the TCP/IP port on the concurrent processing server node that receives incoming Report Review Agent requests.
Reports Port: TCP/IP port on the reports server that receives incoming requests from
browsers or other servers.
Web Listener Port: HTTP port on the web server that receives incoming requests from
browsers or other servers.
OProcMgr Port: Port for process that monitors the Apache JVM state. Routes requests for the JVM.
Web PLSQL Port: Port that receives PL/SQL data and HTML content from the database and
re-directs it to the PL/SQL Apache listener.
Servlet Port: Port on the web server that browsers connect to when invoking Java servlets.
Forms Listener Port: TCP/IP port on the forms server that receives incoming requests from
browsers or other servers.
Metrics Server Data Port: TCP/IP port on which the Metrics server receives load data from Metrics clients running on other machines.
Metrics Server Req. Port: TCP/IP port on which the Metrics server receives the “least-loaded host” requests from forms clients.
JTF Fulfillment Server Port: TCP/IP port on which the Fulfillment server receives requests from a remote process.
Map Viewer Servlet Port: Dedicated TCP/IP servlet port on the web server that receives requests for the Map Viewer.
OEM Web Utility Port: Dedicated TCP/IP port on the web server that receives requests for the Oracle Enterprise Manager.
VisiBroker OrbServer Agent Port: Dedicated TCP/IP port on the web server that receives requests for the VisiBroker OrbServer agent, used by Oracle Discoverer.
MSCA Server Port: Port used by the MSCA (Mobile Supply Chain Applications) server.
MSCA Dispatcher Port: Port used by the MSCA Dispatcher.
OACORE Servlet Port Range: Range of ports used by the OACORE servlets (for the Self-
Service framework).
Discoverer Servlet Port Range: Range of ports used by the Discoverer servlets.
Forms Servlet Port Range: Range of ports used for the Forms Servlet JServ processes.
XMLSVCS Servlet Port Range: Range of ports used by the XML service.

Step 10: Save the configuration file
You have now completed all the information Rapid Install needs to install your Oracle
Applications products. The next screen asks you to save your installation settings in a configuration file. This file (config.txt) stores the configuration parameters that you entered on the settings screens.

Step 11: Review pre-install test results
Rapid Install begins to perform a series of system checks to validate the configuration described by your configuration file. As the parameters are tested, the results of the validation
checks are recorded in the system test dialog box. When the tests are complete, Rapid Install
provides a check list of the tests that it performed and an indication of whether the tests succeeded or failed.

Parameter Validation
The parameters that Rapid Install validates include:
• Port Availability validates the ports you selected are available for use.
• Operating System Check validates the patches and/or version levels of your operating
system.
• Port Uniqueness validates that there are no duplicate defined ports for server processes.
• File System Check validates that file system mount points exist and have correct
privileges.
• File Space Check validates that file system mount points have sufficient space.
• Host/Domain Check validates that host and domain names are verified by the DNS
server.
• System Utilities Check validates that linking utilities (make, ld, and cc) are available.
• JDK Availability Check validates that JDK exists in the location you specified.

The results of each test are displayed using check list icons. Scroll down the list to see the results. There are three result types:
• Check mark: The test succeeded.
• Exclamation mark (!): The configuration requires review. Click the ! to get information about the system test review. Click Yes to continue, and No if you are going to resolve the issues. Rapid Install alerts you if you continue without resolving the issues.
• An x mark: All issues marked x must be resolved before you continue with the
installation. Click the x to see the errors. If you can resolve an issue by fixing the values provided on the settings screen(s), click Back until you reach the appropriate screen, and re-enter the values. Some tests must be resolved in the operating system. In that case, you may have to restart the Rapid Install wizard after the problem has been fixed.
When there are no issues to resolve, click Next to continue.

Step 12: Begin the installation
Rapid Install lists the actions it will take during the installation process. The list varies based on your installation. Click Next to continue.
Rapid Install displays another alert screen asking you to verify that you are ready to begin the installation. Click Yes to continue. Rapid Install begins the installation.

Upgrade to Oracle Database 10g

2009-04-07T05:19:00.000-07:00

Supported Upgrade Paths to Oracle 10g

You can migrate directly to the Oracle Database 10g version only if your database is one of the following versions: 8.0.6, 8.1.7, 9.0.1, or 9.2.
You can upgrade to Oracle Database 10g in two ways:

• the traditional manual mode
• by using the Database Upgrade Assistant (DBUA)

Note: The DBUA is a GUI tool, but you can also run it in the silent mode, by using the following command at the operating system level: dbua
Using New Utility to Perform Pre-Upgrade Validation Checks
Oracle now includes a brand-new tool, called the Upgrade Information Tool, to help you collect various pieces of critical information before you start the upgrade process.

The Upgrade Information Tool provides important information and actions you should do before upgrading the existing database.
If you are performing a manual upgrade, you need to invoke the tool by running the SQL script utlu10*i.sql. The DBCA automatically runs it as part of the pre-upgrade check.

Note: In Oracle 10g Release 2, the Pre-Upgrade Information Utility (utlu102i.sql) has been enhanced to provide improved resource estimations for tablespace space usage and elapsed upgrade runtime.
The Post-Upgrade Status Tool
Oracle Database 10g also provides a Post-Upgrade Status Tool (utlu10*s.sql), which gives you an accurate summary of the upgrade process and any necessary corrective steps to be taken.

You can restart a failed database upgrade job from the point where you failed.
If you use the DBUA to upgrade, the script runs automatically. If you are performing a manual upgrade, you need to run the script yourself, after the upgrade process is finished.

Using the Simplified Upgrade Process
Oracle provides the DBUA to facilitate the database upgrade process. You can use the DBUA to upgrade any database configuration, including RAC and standby databases.
The DBUA takes care of the following tasks for you:
• Deletes all obsolete initialization parameters
• Changes the ORACLE_HOME settings automatically
• Runs the appropriate upgrade scripts for your current release
• Configures your listener.ora file

Starting DBUA
On Windows: Programs | Oracle | Configuration and Migration Tools | Database Upgrade Assistant.
On a UNIX system: simply type dbua
Silent startup: dbua -silent –dbName nina
Manual Upgrade Process
Steps in the Manual Upgrade Process

1. Start a Spool File
SQL> spool upgrade.log
2. Run the Upgrade Information Tool
SQL> @$ORACLE_HOME/rdbms/admin/utlu101i.sql
SQL> spool off
3. Back Up Your Database
At this point, shut down and back up your current database, by using either the RMAN or by using user-managed backup techniques.
4. Copy Your init.ora File
Copy your present init.ora file to the new Oracle Database 10g default location:
o %ORACLE_HOME%\database on Windows with the name: init%ORACLE_SID%.ora
o $ORACLE_HOME/dbs under UNIX with the name: init$ORACLE_SID.ora
Make all the necessary changes in your init.ora parameter file, as per the Upgrade Information Tool’s recommendations.
5. If you are upgrading a cluster database and your initdb_name.ora file resides within the old environment's Oracle home, then move or copy the initdb_name.ora file to the new Oracle home. Make modifications in the file in the same way as made in the init.ora file.
6. If you are upgrading a cluster database, then set the CLUSTER_DATABASE initialization parameter to false. After the upgrade, you must set this initialization parameter back to true.
7. Shut down the instance:
SQL> SHUTDOWN IMMEDIATE
8. Completely remove any Windows-Based Oracle Instances
C:\>net stop oracleservicefinance
C:\>oradim -delete -sid finance
C:\>oradim -new -sid finance -intpwd finance1 -startmode auto –pfile c:\oracle\product\10.1.0\Db_1\database\initfinance.ora
9. If your operating system is UNIX, then make sure that your ORACLE_SID is set correctly and that the following variables point to the new release directories: ORACLE_HOME,PATH,ORA_NLS10,LD_LIBRARY_PATH
10. Log in to the system as the owner of the Oracle home directory of the new Oracle Database 10g release.
11. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
12. Start Up the New Database
sqlplus /nolog
SQL> connect / as sysdba
SQL> startup upgrade

Using the startup upgrade command tells Oracle to automatically modify certain parameters, including initialization parameters that cause errors otherwise
13. If you are upgrading from a release other than 10.1, create the SYSAUX Tablespace. The Pre-Upgrade Information Tool provides an estimate of the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace section.
CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE
14. If you upgrading to release 1, run the Upgrade Script. Run the Upgrade Script corresponding to the Oracle version you would like to upgrade:
o 8.0.6: u0800060.sql
o 8.1.7: u0801070.sql
o 9.0.1: u0900010.sql
o 9.2: u0902000.sql
15. If you upgrading to Oracle Database 10g Release 2, only one common SQL script has to be invoked when performing a database upgrade. Oracle automatically determines what version is being upgraded and runs the appropriate upgrade scripts for that database and all of its included components:
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
16. Depending of the release you are upgrading to, run utlu10*s.sql (Post-Upgrade Status Tool) to display the results of the upgrade:
SQL> @utlu101s.sql TEXT
SQL> @utlu102s.sql
SQL> SPOOL OFF
Note that the utlu101s.sql script is followed by the word TEXT, to enable the printing of the script output.
The tool simply queries the DBA_SERVER_REGISTRY table to determine the upgrade status of each individual component.
17. Check the spool file and verify that the packages and procedures compiled successfully. Rerun the catupgrd.sql script, if necessary.
18. Restart the instance
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
19. If Oracle Label Security is in your database:
SQL> @olstrig.sql
20. Run utlrp.sql to recompile any remaining invalid stored PL/SQL and Java code.
SQL> @utlrp.sql
21. Verify that all expected packages and classes are valid:
SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';
22. Exit SQL*Plus

Flash Recovery Area

2009-04-06T07:15:00.000-07:00

Using the Flash Recovery Area

The flash recovery area serves as the default storage area for all files related to backup and restore operations.

The flash recovery area provides the following benefits:
• Single storage location for all recovery-related files.
• Automatic management of recovery-related disk space.
• Faster backup and restore operations, since you don’t need to restore tape backups.
• Increased reliability of backups, since disks are generally safer storage devices than tapes.

What’s in the Flash Recovery Area?
The flash recovery area may contain the following files:
• Datafile copies: The new RMAN command BACKUP AS COPY can be used to create image copies of all datafiles and automatically store in the flash recovery area.
• Control file autobackups: The database places any control file backups it generates in the flash recovery area.
• Archived redo log files: If you store Archived redo log files in the flash recovery area, Oracle will automatically delete the files.
• Online redo log files: Oracle recommends that you save a multiplexed copy of your online redo log files in the flash recovery area. The following statements can create online redo logs in the flash recovery area: CREATE DATABASE, ALTER DATABASE ADD LOGFILE, ALTER DATABASE ADD STANDBY LOGFILE, and ALTER DATABASE OPEN RESETLOGS.
• Current control files: Oracle also recommends that you store a multiplexed current control file in the flash recovery area.
• RMAN files
• Flashback logs: If you enable the flashback database feature, Oracle copies images of each altered block in every datafile into flashback logs stored in the flash recovery area.

Note: Oracle calls the multiplexed redo log files and control files in the flash recovery area permanent files, since they should never be deleted and are part of the live database. Oracle terms all the other files in the flash recovery area (recovery related files) transient files, since Oracle will delete them eventually after they have become obsolete or have already been copied to tape.
Creating a Flash Recovery Area

You use the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters to configure a flash recovery area in your database.
When you use the DB_RECOVERY_FILE_DEST parameter to specify the destination of your flash recovery area, you can use a directory, file system, or ASM disk group as your destination.

Dynamically Defining the Flash Recovery Area

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G SCOPE=BOTH
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = 'C:\ORACLE\RECOVERY_AREA' SCOPE=BOTH

You must always specify the size parameter before specifying the location parameter.
Disabling the Current Flash Recovery Area

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ''

Note: even after you disable the flash recovery area, the RMAN will continue to access the files located in the flash recovery area for backup and recovery purposes.

Default File Location and the Flash Recovery Area
The initialization parameters DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n determine the location of all OMF files.
Control Files
If you haven’t set the CONTROL_FILES parameter, Oracle will create the control files in various default locations, according to the following rules:

• If you specify the DB_CREATE_ONLINE_LOG_DEST_n parameter, Oracle will create an OMF-based control file in n number of locations, with the first directory holding the primary control file.
• If you specify the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST parameters, Oracle will create an OMF based control file in both of these locations.
• If you just specify the DB_RECOVERY_FILE_DEST parameter, Oracle will create an OMF-based control file in the flash recovery area only.
• If you omit all three of the initialization parameters, Oracle will create a non-OMF-based control file in the system-specific default location.
Note: If the database creates an OMF control file, and it is using a server parameter file, then the database sets the CONTROL_FILES initialization parameter in the server parameter file.
Redo Log Files

If you omit the LOGFILE clause during database creation, Oracle will create the redo log files according to the same rules as mentioned above.
Backing Up the Flash Recovery Area
In order to back up the flash recovery area itself using RMAN, you must set CONFIGURE BACKUP OPTIMIZATION to ON.
You can back up the flash recovery area only to a tape device using these backup commands:

BACKUP RECOVERY AREA

o This command backs up all flash recovery files in the current or previous flash recovery area destinations.
o It backs up only those files that have never been backed up to tape before.
o The files that the command will back up include full backups, incremental backups, control file autobackups, archive logs, and datafile copies.

BACKUP RECOVERY FILES

This command backs up all the files that the BACKUP RECOVERY AREA command does, but from all areas on your file system, not just from the flash recovery area.

BACKUP RECOVERY FILE DESTINATION

Use this command to move disk backups created in the flash recovery area to tape.
Note: Neither of the two commands, BACKUP RECOVERY AREA or BACKUP RECOVERY FILES, will back up any permanent files or the flashback logs in the flash recovery area.

Managing the Flash Recovery Area
Space Management

If you ever receive the out-of-space warning (85) and critical alerts (97) because of space pressure in you flash recovery area, you have the following options:
o Consider changing your backup retention and archive log retention policies.
o Increase the size of the DB_RECOVERY_FILE_DEST_SIZE parameter to allocate more space to your current flash recovery area.
o Use the BACKUP RECOVERY AREA command in the RMAN to back up the contents of the flash recovery area to a tape device.
o Use the RMAN to delete unnecessary backup files. The RMAN commands CROSSCHECK and DELETE EXPIRED come in handy during this deletion process.

Data Dictionary Views

V$RECOVERY_FILE_DEST
This view is the main source and contains the following columns:

SPACE_LIMIT :how much space has been allocated to the flash recovery area

SPACE_USED :space occupied

SPACE_RECLAIMABLE :how much space you can reclaim by getting rid of obsolete and redundant files in the flash recovery area.
NUMBER_OF_FILES :number of files

V$FLASH_RECOVERY_AREA_USAGE

This view provides information about the flash recovery area disk space usage. Following is its main columns:

FILE_TYPE :the type of the file and can have any of the following values: controlfile, onlinelog, archivelog, backuppiece, imagecopy, flashbacklog

PERCENT_SPACE_USED :This represents the disk space used by the file type, in percentage.

PERCENT_SPACE_RECLAIMABLE :this represents the percentage of disk space reclaimable from the file type after deleting any obsolete or redundant files, and files backed up to a tertiary device.

Flash Recovery Area Columns in Other Views

The Yes/No column IS_RECOVERY_DEST_FILE is added to some dictionary views to indicate whether the file was created in the flash recovery area. It exists in V$CONTROLFILE, V$LOGFILE, V$ARCHIVED_LOG, V$DATAFILE_COPY, V$BACKUP_PIECE.
Moving the Flash Recovery Area

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/oracle/new_area' SCOPE=BOTH

Eventually, Oracle will delete all the transient files from the previous flash recovery area location, when each of them become eligible for deletion. However, if you want to move your current permanent files, transient files, or flashback logs to the new flash recovery area, you can do so by using the standard file-moving procedures.

Database Patching

2009-04-06T04:40:00.000-07:00

Database Patching

Database patching consists of either upgrades or interim fixes. Database upgrades are typically complex in nature and require installation of new software when upgrading from one point release to another. Obsolete and new initialization parameters must be reviewed when upgrading to a new release of the database.

Database upgrades can be accomplished manually or by using dbmig,the database migration utility. Since the method for upgrading the database is version and platform dependent, the associated readme file for the upgrade must be reviewed, and the steps required to perform the upgrade should be documented.

Interim patch fixes for the database are applied as the owner of the database install with the opatch utility or by running an operating system script. Details on how to apply database patches are outlined in the patch’s readme.Before upgrading or applying a patch to the database, the oraInst.loc file must point to the correct Oracle inventory location for the database ORACLE_HOME. It is also important to cleanly shut down the database before proceeding, and to perform a cold database backup.

The opatch utility is downloaded from MetaLink as patch number 2617419. The opatch utility requires Perl and JDK to function, and they must be installed and specified in the path and library environment variables. Once the opatch utility has been downloaded and unbundled, the Opatch directory of the opatch unbundled patch should be added to the PATH, as in the following example:

$export PATH=$PATH:/[path_of_2617419]/Opatch

The library path of Perl must also be specified with the following PERL5LIB environment variable, as in the following example:

$export PERL5LIB=[path_of_PERL]/lib

To validate that opatch is functioning properly, execute the following command with the lsinventory option:

$opatch lsinventory

Once opatch has been successfully set up, the database interim patch fix may be applied. To do this, first review the readme file for the patch. Make certain that all prerequisites have been met. Document any post-patching steps that are required. Download the patch and unbundle it. Change to the directory where the patch has been unbundled. Verify that the database has been shut down. Apply the patch by executing opatch as the database owner with the apply parameter, as in the following example:

$opatch apply

To verify that a patch has successfully been applied, the lsinventory option can again be executed. This will display all patches that have been applied to the database.

■Note If the opatch fails, there may be a patch_locked file located under the hidden directory $ORACLE_HOME/.patch_storage. The opatch utility may not be executed until the patch_locked file is removed.

AD Control

2009-04-06T04:28:00.000-07:00

Using AD Control

The administrative tool used to manage patch workers is AD Control, or adctrl. Frequently workers will fail or hang, which will require the Oracle Applications DBA to interface with adctrl.

AD Control menu options will vary depending upon the AD patch version applied to the instance. When logged in as the application owner on the Admin Node, execute adctrl to display the menu options shown in

AD Controller Menu

To execute an adctrl menu option, simply type the menu option and press Enter. If options 2–6 are chosen, either specify the number of the worker that requires action, or press Enter for the action to be executed for
all workers.
The “Skip Worker” menu option is a hidden adctrl menu option. If a worker needs to be skipped, start adctrl, enter 8, and then enter the worker number. Only use this option if advised by Oracle Support.

■Oracle Aplication DBA Portal Tip:With AD.I, adctrl may be used in noninteractive mode. Using adctrl noninteractively can expedite patch problem resolution.

Resolving AD Patch Worker Failure
If a worker has failed, the adpatch session will normally display a failedworker
message. The status of the worker may also be determined using adctrl. If a worker has failed, the worker error can be obtained by viewing the worker log file. Once the worker issue has been resolved, use adctrl to
restart the worker.

If a worker has failed, and it is determined that the step the worker was
trying to execute may be skipped, the hidden option 8 of the adctrl menu, “Skip Worker,” may be used to skip the worker. It is only advisable to do this
if the step is not critical to the environment being patched.

■Oracle Aplication DBA Portal Tip:It may be necessary to research MetaLink or open an SR to resolve issues with failed workers. For additional information on MetaLink and the SR process, see
The following are common worker failures that will be seen by the Applications DBA during patching. The error messages will be displayed by the adpatch session or in the worker log file:
Error message: ORA-01013: user requested cancel of current operation Resolution to error: If this error occurs, simply use adctrl to restart the worker on the current machine.

Error message: Patch not applied successfully, adpatch did not cleanup its restart files (*rf9).

Resolution to error: If this error occurs, execute the following as the
instance owner:
$cd $APPL_TOP/admin/$CONTEXT_NAME
$mv restart restart_old
$mkdir restart

After cleaning up the restart files, you may then restart the adpatch session using adpatch.

Then, use adctrl to restart the failed worker.

Error message: No error message is displayed; rather the worker log file states that the worker is complete, yet adctrl indicates that the worker is still running.

Resolution to error: This patching problem occurs when the worker is complete, but did not update patching tables correctly to notify the adpatch session that it has finished. In this scenario, the adpatch session is still waiting for the finish return code from the worker. When this occurs, use adctrl to fail the worker, then restart the worker.

■Oracle Aplication DBA Portal Tip:Any form, library, or report that fails to generate during the patch process can be regenerated manually after all patching and post-patching steps have completed. If theobject still fails to compile, open an SR.

Using AD Patch

2009-04-06T03:49:00.000-07:00

Using AD Patch
The administrative tool used to apply application patches is AD Patch, also referred to by its command line executable, adpatch. Basic information about the AD Patch utility can be found in Oracle MetaLink Note 181665.1, “REL11i ADPATCH BASICS.” The AD Patch utility will spawn off a user-determined number of worker threads or patch worker processes to update application code by copying later versions of programs to the application tier; updating database objects, regeneration application forms, and reports; and performing some post-patching steps. The code changes are determined by the patch’s driver files.

■Oracle Aplication DBA Portal Tip: Prior to executing adpatch, disable any password constraints defined within the application or database. These constraints could cause errors if the patch creates a new user account. You may also need to reset some standard Oracle passwords for accounts such as CTXSYS.

An Oracle Applications DBA needs to be familiar with the options available for the AD Patch utility. The parameters supported by the adpatch command differ depending upon the AD Minipack level applied with Oracle Applications. When upgrading the AD product group, be sure to identify and understand any new features of the AD Patch utility. Study the adpatch command parameters for any possible benefits. Use the command adpatch help=y to see the available options.

In order to enable an option with adpatch, use the following syntax:

adpatch options=[option1, option2..]

The adpatch options shown are commonly used to accelerate the patching process.

Option Purpose
Novalidate :Prevents adpatch from validating all schema connections
Noprereq :Prevents adpatch from checking the existence of prerequisite patches
Nocompiledb :Prevents adpatch from compiling database objects
Nocompilejsp :Prevents adpatch from compiling JSP objects
Noautoconfig :Prevents adpatch from running autoconfig after the patch has completed
Nogenerateportion :Prevents adpatch from compiling forms, menus,and plls
Hidepw :Prevents passwords from being displayed in log files Hotpatch Allows adpatch to be run when the instance is not in maintenance mode

■Oracle Aplication DBA Portal Tip: If you are applying a large number of patches, use the options nocompilejsp,nocompiledb, noprereq, and novalidate to speed up the application of the patches. Recompiling Java Server Pages (JSP) pages and database objects can be performed at the end of the patching process. In this situation, placing the database in noarchivelog mode may also improve patching performance.

Having changed directory to the location where the patch driver(s) is located, you can then start the patching session as the instance owner by executing adpatch with the desired options:

adpatch options=nocompiledb,novalidate

When adpatch is started, the user must respond to several questions. These questions serve to verify application file settings, database connectivity, and patch driver options. For example, the user may set adpatch to send an email upon failure. The following questions from adpatch require additional
explanation:

Question: Filename for AutoPatch Log
Recommended response: Rather than use the default name of adpatch.log, use a more descriptive name, such as [c|d|g|u][patch#].
log. For multi-node or multi-language patching, you should consider including the server name and language in the filename. Additional descriptions may also be added depending on your environment.

Question: The default directory is [current working directory]
Recommended response: It is advised that you run the adpatch utility from the directory where the patch has been unbundled. By doing this, the default value for this question can be chosen. Otherwise, enter the directory where the patch was unbundled.

Question: Please enter the name of your AutoPatch driver file
Recommended response: This depends upon the patch being applied. Most patches from Oracle now contain a single u driver. If the patch contains c, d, and/or g drivers, adpatch needs to be run for each driver. The drivers need to be applied in alphabetical order.

Question: Number of parallel workers
Recommended response: This value is environment-specific and
should be adjusted accordingly.

Using a defaultsfile will allow for noninteractive patching by providing responses to the adpatch questions. The defaultsfile option of adpatch must point to a file in the $APPL_TOP/admin/$CONTEXT_NAME directory. This option is typically used with the mode interactive=no.

Performing Manual Steps
If any part of the patching process requires numerous manual steps, it is useful
to create scripts to automate the manual commands. This can not only
help reduce the chances of human error during the patching process, but
scripting can also speed up the process. For ease of management, you should create a special directory to be the home directory for such scripts. During the creation of the scripts, be sure to include logging commands as well as parameters for values such as instance name. When you parameterize such variables, the scripts can be executed with each patching effort without requiring modifications.

For example, you may build a script of manual steps that looks similar to
the following template:

SCRIPT_TOP=/patch/scripts/project_a
INSTANCE_NAME=$1
LOG_TOP=$SCRIPT_TOP/logs
script_1.sh > $LOG_TOP/post_steps.log
command_1 >>$LOG_TOP/post_steps.log
script_2.sh $INSTANCE_NAME >> $LOG_TOP/post_steps.log
exit

In the preceding example, script_1.sh may be a script that performs a backup of library files, and script_2.sh could be a script that relinks several
forms.

When the script has executed, thoroughly review the log files generated by the script. New failures may be encountered on some instances that had not occurred during past patch applications. Resolve any errors before proceeding to the next steps. Scripts created for such steps should contain error handling, such as checking the number and types of parameters Custom scripts should also contain documentation to describe the purpose of
the script.

The scripts you create should be included in the spreadsheet as part of the process for applying the patch. Part of the documentation process for the patching effort involves using descriptive script and variable names. Writing scripts is a useful skill set for Applications DBAs. We recommend you practice coding scripts on test servers while connected as a user with a low level of permissions until you become more comfortable with scripting. Always test your scripts thoroughly before running them on production systems.

Patching

2009-04-06T03:22:00.000-07:00

Patching

One of the most important and time-consuming aspects of an Oracle Applications DBA’s job is applying patches to the E-Business Suite. Patches may be required to resolve problems with the application code, to fix production issues, to install new features, or to upgrade components of the technology stack. Patching is not a simple one-step process, but rather requires careful research in order to determine all of the prerequisite steps, patching steps, and post-patching steps required.

Oracle E-Business Suite patching can be divided into two categories:
• Oracle Applications patching: This includes all patching that changes
the underlying Oracle Applications code.
• Technology stack components patching: This includes all upgrades and fixes for the Oracle Database software, JDK, Oracle Developer 6i (Oracle Forms and Reports), Developer 6i Client library files, Oracle Discoverer, JDBC, Oracle Java Server Page (OJSP), Oracle Application Server (iAS),and iAS Client library files (Required Support Files or RSF).

The focus of this chapter will be on Oracle Applications patching, and a brief overview of Oracle Database software patching will also be provided. Patching the Applications Technology Stack will not be covered, as this type of patching effort has numerous operating system dependencies.

Applications Patching
There are several steps involved in patching Oracle Applications. In this section
we’ll discuss each of these stages:

• Preparing to patch: Before patching, it is important to document the requirements and determine what steps and patches are needed. This section will explain how to document and manage the overall process of applying patches, and discuss patch reporting, where you investigate which version, if any, of a patch is currently installed.
• Applying patches: Applying a patch involves several steps, such as unbundling the patch, enabling maintenance mode, applying the patch with adpatch, and implementing manual steps. This section will discuss each of the steps involved.
• Monitoring and resolving patching issues: Sometimes there are problems applying patches. This section will explain how to review log files and use the AD Control utility to monitor patch worker processes.
• Post-patching steps and cleaning up: There are often steps that should be performed after the patching is complete. This section will explain how you can efficiently perform post-patching steps and clean up files no longer required after patching.

Types of Application Patches
There are several different types of Oracle Applications patches. These are
the more common patches:

• One-off patch: This is the simplest type of patch. It is created to resolve a
specific bug.
• Minipack patch: This is a collection of one-off patches and enhancements related to a particular module. Alphabetic characters denote the Minipack version for the module; for example, the product code for the Application DBA utilities is AD, and version Minipack I of this product would be called AD.I.
• Family Pack patch: This is a collection of Minipack patches for a particular family group of application modules. Alphabetic characters denote the Family Pack version; for example, the J version of the Human Resources Suite Product Family would be HR_PF.J.
• Maintenance Pack patch: This is a collection of Family Packs that serves as a point-level release upgrade; Oracle Applications Release 11.5.10 is an example of a Maintenance Pack.

There are also other special types of patches:
• Consolidated patch: This is a collection of one-off fixes for a Family Pack
or Maintenance Pack; Oracle Applications 11.5.10 Consolidated Update
2 (CU2) is an example of a consolidated patch.
• Interoperability patch: This is a patch that is required for Oracle Applications
to function with a newer version of a technology stack component; for example, you would apply an interoperability patch when upgrading the database to version 10g.
• NLS patch: This is a patch that updates language-specific information
for multi-language installations.
• Rollup patch: This is a collection of one-off patches that update code
levels for particular products.
• Legislative patch: This is a special patch for HR Payroll customers; it
contains legislative data for multiple countries.

As the patch group size increases from one-off patches to Maintenance Packs, the complexity of the patch application process also increases. More research is required for Family Packs than is required for a Minipack. Due to the increased complexity, there is more planning required for Maintenance Packs and Family Packs than other patches.

Preparing to Patch
Before applying a patch, carefully examine the readme file provided with the
patch. This document will list all steps required by the patch.

■Oracle Application DBA Portal Tip: Before applying a patch, make certain that the readme file has been carefully reviewed.

The readme file will contain prerequisites, installation steps, postinstallation
steps, and other information vital to the successful installation of the patch. The prerequisites may consist of other patches or manual steps.

Here is an example of the readme file contents:

---------------------------------------------------------------------
README CONTENTS:
---------------------------------------------------------------------

A. Prerequisites
B. Best Practices
C. Installation Steps
D. Post-Installation Steps
E. HRGLOBAL - SPECIAL NOTES AND CHANGE HISTORY
F. Other Information Sources
---------------------------------------------------------------------
A. PREREQUISITES:
---------------------------------------------------------------------
Apply this patch if you have HR (Product code PER) fully installed.
Before applying this patch you must have each of these prerequisites:
1. Oracle Applications Server 11i
2. Oracle 11i.PER.G, patch 1988754, or later. . . .
If prerequisites have not been met, you must add these steps or patches to the overall process of applying the patch. Become familiar with all steps required before attempting to apply the patch.

■Caution Removing a patch from Oracle Applications after it has been applied is not
usually a feasible option; therefore, a full system backup should be taken before applying patches to an instance.

Documenting the Patching Process
It is recommended that you maintain a spreadsheet detailing all prerequisite
steps, patching steps, and post-installation steps required for patch application.
By creating such a document, you can eliminate operator error, such as missed steps or steps completed out of order.

The columns in the spreadsheet should be customized to match your needs. These columns can include information about the node being patched, details about the patch being applied, or the rationale for the patch. At a minimum, it is useful to have columns for patch number, description, and comments, but it is often also useful to include the actual time required to complete each step based upon trial runs in a sandbox instance. Tracking timings allows for an accurate prediction of production maintenance downtime.

shows an example of a spreadsheet for patches required by Project A that will require 6 hours and 25 minutes to apply.

Sample patch documentation spreadsheet

If timings are included for every step, the Applications DBA can generate a schedule for applying the patches to production by using time functions in the spreadsheet software. This corresponds to the Shift Start Time column in This process is highly recommended for extended patching efforts that will require multiple shifts. Otherwise, a simple summation of the time required for each step should provide an accurate schedule. The times required for applying patches is also tracked by adpatch and can be found in the $APPL_TOP/admin/$CONTEXT_NAME.out/adt*.lst files.

■Oracle Application DBA portal Tip: When documenting the patching process for multiple patches, post-installation

steps like recompiling invalid objects, regenerating JAR files, and running the autoconfig utility can be consolidated and executed at the end of the patching process. This helps to streamline the patch process and reduce downtime.

Patch Reporting
Patch reporting is used to determine whether or not a specific patch has already been applied to the instance, or what version of a Family Pack or Minipack is currently installed. The following sections will discuss four methods for determining patching levels:
• Using the adphrept.sql script
• Executing the patchsets.sh utility
• Querying the database
• Using Oracle Application Manager (OAM)

Using adphrept.sql
The $AD_TOP/patch/115/sql/adphrept.sql file is an Oracle-provided script for generating a patch report for an instance. This script provides an easily searchable list of all patches that have been applied to an environment. Keep in mind that the script can take a long time to execute. Additional details regarding adphrept.sql and a description of all parameters can be obtained by viewing MetaLink Note 162498.1.

Using patchsets.sh
The Oracle-provided patch-comparison utility, patchsets.sh, is a handy tool for reviewing patchset levels. Family Pack versions, fully installed products, and shared installed products, along with the latest version available, are displayed in the output. Information about the latest version of this utility can be reviewed in MetaLink Note 139684.1.

Querying the Database for Patches
In order to determine whether a specific patch has been applied, a query can be executed against the bug_number table. The following SQL will return results if the patches included in the IN clause have been applied to the
instance:
SELECT bug_number
FROM ad_bugs
WHERE bug_number IN ('patch_number', 'patch_number', . . .)
ORDER BY bug_number DESC;

Using OAM
Oracle Application Manager (OAM) may also be used to query the instance for applied patches.

Enterprise Manager 10g

2009-04-05T12:32:00.000-07:00

Oracle10g Enterprise Manager

Finally, a tool that serves as one-stop-shop for Oracle administration and management—whether by novices or experts
What tool do you use in your day-to-day DBA-related activities? It's a question I asked recently in a user group meeting.

The answers varied depending on the DBA's work experience. Most senior administrators expressed a preference for simple command-line
SQL*Plus (my personal favorite), with the rest dividing their allegiances among a handful of third-party products. The same question, however,
yielded a different response from entry-level DBAs: among that group, Enterprise Manager (EM) was clearly the tool of choice.

It's not hard to understand these preferences. Oracle Enterprise Manager has been steadily perfected since its introduction several years ago,
beginning as the character-mode display SQL*DBA, evolving into a client OS-based tool, and finally taking on a Java flavor. The information
presented by EM was sufficiently detailed for most DBA tasks, serving as a solution for users who were either too reluctant or too busy to learn a
new syntax and wanted a GUI tool for managing common database chores such as adding users, modifying datafiles, and checking on rollback
segments. The diagnostic pack supplied much-needed GUI support for performance tuning.

However, one of the major issues hampering EM's widespread adoption was its inability to keep pace with the development of the database server
itself. For example, the Oracle9i Database version of EM doesn't support subpartitioning, a feature first introduced in Oracle8i.
The new version of EM in Oracle Database 10g changes that equation. It has a new architecture, a new interface, and most important, a very
powerful and complete toolbox catering to all DBA skillsets—from novices to advanced users. And best of all, it's part of the installation itself without
any additional cost. If you are evaluating third-party tools, you can certainly throw EM into the mix to light a fire under the competition. Even if you
are an "in-command-line-we-trust" kind of DBA (like me), you will greatly appreciate how EM can help you in several situations.

In this installment I will introduce you to the new EM. Because the tool is so vast in scope, it will be impossible to cover the entire spectrum of
features; instead, I will explain a few basics and offer pointers to additional material. Keeping in the spirit of this series, I will provide practical
examples that demonstrate the use of the tool to solve real-life problems.
Architecture

EM 10g is installed by default when you install the 10g software. Conceptually, it differs from previous versions in that instead of being a clientinstalled
tool, it's actually an HTTP server (called DB Console) sitting on the database server itself. (See Figure 1.) You can use any browser to see
the EM interface.

Figure 1: EM Architecture
The port number for DB Console is found in $ORACLE_HOME/install/portlist.ini. Here is an example of a file; ports in your case may be different.
Ultra Search HTTP port number = 5620
iSQL*Plus HTTP port number = 5560
Enterprise Manager Agent Port =
Enterprise Manager Console HTTP Port (starz10) = 5500
Enterprise Manager Agent Port (starz10) = 1830
From this file we know that the Agent for the database starz10 listens on the port 1830 and the EM console listens on 5500. We can invoke the EM

logon screen by entering the following URL:
http://starz/em/console/logon/logon

This URL brings up a logon screen where you can log on as a DBA user. For our example, we will log in as SYS.
Main Database Home Page
After logon, the main database home page comes up. The top portion of the home page enables a quick glance at important details. (See Figure 2.)

Figure 2: Main Database Home Page (Top)
Some of the most important points in the above figure have been circled and annotated with numbered references in this article. First, note the
section labeled "General" (1); this section shows some most rudimentary details about the database, such as the fact that the database has been
up since March 20 as well as the instance name. The Oracle Home is shown as a hyperlink, which, when clicked, shows all the products and all
other Oracle databases sharing that home. The hyperlink for Listeners shows all the databases and instances registered with the listener, whose
name is shown immediately below. Finally, it shows the host name (starz).
In section named "Host CPU" (2), the CPU details are shown at a glance. Section "Active Sessions" (3) shows the active sessions and what they
are doing at the moment (4). We see from the above that 99% of the time spent by the sessions is in waiting. (We will find the cause of these waits
later.) The section on "High Availability" (5) shows availability-related information. For example, the value of "Instance Recovery Time," which is the
value of MTTR Target for the instance, determines how much time may be required for instance crash recovery.

The section on "Space Usage" (6) is interesting: it shows warnings associated with 23 segments. (Again, more on these warnings later.) The
section "Diagnostic Summary" (7) provides a synopsis of database well being. The number of performance findings indicates how many issues
were proactively identified by the Automatic Database Diagnostic Monitor (ADDM), the new self-diagnostic engine in 10g. EM also automatically
analyzes your environment to determine if any recommended best practices are being violated; the result of this analysis is presented in the "Policy
Violation" section. Finally, EM scans the alert log and shows any recent ORA errors. This information is invaluable—automatic scanning of Oracle
errors in the alert log saves you the considerable trouble of manually searching for them.

The bottom part of the database home page, shown in Figure 3, we see some of these messages in more detail. The section "Alerts" (1) shows all
the relevant alerts that require your attention, each of which can be easily configured. Take the first one (2), for example, which shows that the
Archiver process is hanging for some reason. Of course, the next course of action is to determine why. To find out, just click on it. You will be
shown more details from the alert.log file containing the error. In this case, the culprit was a filled-up flashback recovery area; we just need to clear
it up so the Archiver can start working again.

Figure 3: Main Database Home Page (Bottom)
Another alert (3) is about a wait: the database is waiting 69% of the time for a wait related to the wait class "Application." Remember how the top
part of the home page indicates that a session is waiting? This alert shows us what it is waiting on. Clicking on the hyperlink will immediately show
you the actual waits.

The next alert (4) shows an audit entry, that the user SYS connected to the database from a certain client machine. Again, by clicking on the
hyperlink you can reveal all the details about the connection. The last alert (5) shows that some objects are invalid. Clicking on the hyperlink will get
you to the screen where the invalid objects are validated.
As you can see, the database home page serves as a dashboard for everything that needs your attention. Instead of cluttering the screen with
detailed information, the interface has been made quite succinct with those details just a click away. You could compile all this information
manually, but it would take a lot of time and effort. EM 10g provides an out-of-the-box solution.

General Usage
Let's see how some of the more common tasks are accomplished through the new EM.
One common task is to alter a table and its corresponding indexes. From the Database home page, choose the "Administration" tab as shown in
Figure 3 and reference the item marked 6. From this page you can administer the database to configure undo segments, create tablespaces and
schema objects, set up resource manager, use the new Scheduler (to be covered in a future installment), and more. Choose "Tables" from there,
which brings up a screen as shown in Figure 4.

Figure 4: Table Management
Note the flashlight symbol highlighted inside a red circle; this is the button for bringing up a list of values. In the screen shown in the figure, you can
click on the LOV symbol to bring up a list of users in the database and select one from the list. Clicking on the button "Go" brings up a list of tables
for that user. You can also specify a wildcard with the "%" sign—for example, by using %TRANS% to bring up all the tables with the word TRANS in
the name.
Let's see an example. Choose the table TRANS to modify a column there. Clicking on the hyperlink brings up the "Edit Table" screen as shown in

Figure 5.
Figure 5: Table Administration
If you want to modify the column ACTUAL_RATE from NUMBER(10) to NUMBER(11), you can modify the number (Ref 1) and click "Apply." To see
the actual SQL statement used to accomplish this task, can click the button "Show SQL."
Another important piece of information is available in the same screen: the growth trend. As you will learn in a future installment on segment
management, it is possible to observe object growth over a period of time. This screen offers that same information but in a graphical manner. To
see the screen, click on the tab "Segments" (Figure 5 Ref 2). This brings up the segment screen as shown in Figure 6.

Figure 6: Segment Screen
Note the item marked inside the red circles. The screen shows how much space is allocated to the segment (2), how much is actually used (1), and
how much is wasted (3). On the bottom part of the screen (4), you can see a graph of the space used and allocated for the object. In this example,
the pattern of the table usage has been steady—hence the straight line.
You can perform other administrative operations on the table using the tabs for that purpose, such as "Constraints" for managing constraints.
Performance Tuning Using EM

As you've learned up to this point, although EM's look-and-feel has changed, it offers at least as much functionality as the previous Java version.
However, unlike the latter, EM now also supports newer Oracle Database functionality. For example, EM can now handle subpartitions.
However, experienced DBAs will want more from the tool—especially for troubleshooting problems or proactive performance tuning. Let's use an
example. Recall from the previous section that our database is waiting on the "Application" wait class as shown in the database home page (Figure
3 Ref 3) and that we need to diagnose the cause. One of the key things to understand in any tuning process is how various components such as
CPU, disk, and host subsystems interact, so it helps if all these variables are viewed together in context. To do that, choose the "Performance" tab
from the Database home page. This brings up the screen as shown in Figure 7.

Figure 7: Performance Tab
Note how all the metrics have been aligned on the same timeline, which makes viewing their interdependencies easier. Note the spike (3), which
corresponds to the Scheduler task. It shows that some seven sessions were waiting for Scheduler-related waits at that time. So, what was the
impact? Note the CPU metrics located in the same place (the green area)—they indicate the maximum CPU ever used, as shown in the graph by
the broken line (4). Before and after that point, we don't see the CPU spikes occurring, which provides one clue. Note the spike in CPU run queue
length (1), which is a direct consequence of the Scheduler, which might have generated an excessive memory requirement, having caused the
increased paging activity (2). As you can see, all the symptoms fall in line to enable a better understanding of the database load "profile."
Note the spikes at the end of the timeline—increases in Run Queue Length (5) and Paging Rate (6)—which correlate to another spike in Physical

Reads (7). What is the cause?
By comparing the graph "Sessions: Waiting and Working" with the time the spikes were occurring, we can see that most of the sessions were
waiting on the "Application" wait class. But we need to find out exactly what it was waiting on during that time period. Click on the area at that time,
which brings up the Active Sessions screen as shown in Figure 8.

Figure 8: Active Sessions Waits
The screen shows that the sessions were waiting for the wait event enq: TX – row lock contention. So what was the SQL statement that
caused it? Simple: The SQL ID of the statement 8rkquk6u9fmd0 is shown on the screen itself (inside the red circle). Click on the SQL ID to bring
up the SQL screen as shown in Figure 9.

Figure 9: SQL Details
On this screen you can see the SQL statement and relevant details about it, including the execution plan. It turns out that this SQL was causing row
lock contention, so application design may be a source of the problem.
Latch Contention
Suppose that clicking on the "Performance" tab takes you to a screen similar to that shown in Figure 10.

Figure 10: Performance Tab, Example 2
In the figure, note the metrics highlighted inside the red rectangle. You can see a lot of CPU-related waits around 12:20AM, which resulted in a
large run queue in the CPU. We need to diagnose this wait.
First, click on the graph on the area shown for CPU contention (marked with "Click Here" on the figure) to see that particular wait in detail, shown in

Figure 11.
Figure 11: Active Session Waits
Note the shaded box in the "Active Sessions Working: CPU Used" graph (1). You can drag it using the mouse to place the focus. This operation
causes the pie charts below (2 and 3) to be calculated only within the timeframe contained in that box. From there we see that a specific SQL with
id 8ggw94h7mvxd7 is working extra hard (2). We also see that the user session with username ARUP and SID 265 is a top worker (3). Click on the
session to see the details. This operation brings up a "Session Details" screen. Click on the tab "Wait Events" to bring up the details of the wait
events experienced by the session, similar to what you see in Figure 12.

Figure 12: Wait Event Details
In this screen, note the longest wait of 118 centiseconds, highlighted inside a red circle, which is waiting for a library cache. When you click on the
hyperlink for "Latch: Library Cache," you will see a screen similar to that in

Figure 13.
Figure 13: Wait Histogram
This screen provides some unique information not available in pre-10g databases. While diagnosing our latch contention issue, how do you know
whether the 118 centi-second wait comprises many small waits in several sessions or just one large wait in only one session, thereby skewing the
data?
The histograms come to our rescue here. From the figure, you know that some 250 times sessions had a wait of 1 millisecond (highlighted inside a
circle). Sessions waited some 180 times somewhere between 4 and 8 milliseconds. This screen shows that the waits are typically for small
durations, making them insignificant symptoms of latch contention.
From the database home page you can access ADDM, SQL Access Advisor, and other Advisors by clicking on the tab marked "Advisor Central."
ADDM runs automatically as metrics are collected and the results are posted immediately on the Advisor Central page, which when clicked shows
the recommendations made by ADDM. The SQL Tuning Advisor also examines these metrics and communicates its recommendations on this
page. (We'll examine ADDM and SQL Tuning Advisor in much more detail in a future installment.)

Materialized Views

2009-04-05T12:25:00.000-07:00

Materialized Views

Managing materialized views is much easier in 10g with compulsory query rewrite and the introduction of powerful new tuning advisors
that take guesswork out of the picture
Materialized views (MVs), also known as snapshots, have been around for quite some time now. MVs store the result of a query in a segment and
can return that result to the user when the query is submitted, eliminating the need to re-execute the query—an advantage when the query is issued
several times, as is typical in data warehouse environments. MVs can be refreshed from base tables either completely or incrementally using a fast
refresh mechanism.

Assume you have defined an MV as follows:

create materialized view mv_hotel_resv
refresh fast
enable query rewrite
as
select distinct city, resv_id, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id';

How would you know that all the necessary objects have been created for this MV to work perfectly? Prior to Oracle Database 10g, this
determination was performed with the procedures EXPLAIN_MVIEW and EXPLAIN_REWRITE in the package DBMS_MVIEW. These procedures,
which are still available in 10g, explain very succinctly whether a specific capability—such as fast refreshability or query rewritability—are possible
with the said MV but don't offer any recommendations to make those capabilities possible. Instead, a visual inspection of the structure of each MV
is required, which is quite impractical.

In 10g, a procedure called TUNE_MVIEW in the new package DBMS_ADVISOR makes that job very easy: You call the package with the IN
parameter, which constitutes the whole text of the MV creation script. The procedure creates an Advisor Task, which has a specific name passed
back to you using only the OUT parameter.

Here's an example. Because the first parameter is an OUT parameter, you need to define a variable to hold it in SQL*Plus.

SQL> -- first define a variable to hold the OUT parameter
SQL> var adv_name varchar2(20)
SQL> begin
2 dbms_advisor.tune_mview
3 (
4 :adv_name,
5 'create materialized view mv_hotel_resv refresh fast enable query rewrite as
select distinct city, resv_id, cust_name from hotels h,
reservations r where r.hotel_id = h.hotel_id');
6* end;

Now you can find out the name of the Advisor from the variable.
SQL> print adv_name
ADV_NAME
-----------------------
TASK_117
Next, get the advice provided by this Advisor by querying a new DBA_TUNE_MVIEW. Make sure you execute SET LONG 999999 before running
this command because the column statement in this view is a CLOB and by default only 80 characters are displayed.
select script_type, statement
from dba_tune_mview
where task_name = 'TASK_117'
order by script_type, action_id;
Here is the output:
SCRIPT_TYPE STATEMENT
-------------- ------------------------------------------------------------
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID,
SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD
ROWID, SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH
ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV REFRESH FAST
WITH ROWID ENABLE QUERY REWRITE AS SELECT
ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
ARUP.RESERVATIONS.HOTEL_ID GROUP BY
ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY
UNDO DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV

The column SCRIPT_TYPE shows the nature of the recommendation. Most of the lines are to be implemented, hence the name
IMPLEMENTATION. The recommended actions, if accepted, should be followed in a specific sequence indicated by the ACTION_ID column.
If you review these automatically-generated recommendations carefully, you'll note that they are similar to what you would have produced yourself
via visual analysis. The recommendations are logical; the presence of fast refresh needs to have a MATERIALIZED VIEW LOG on the base tables
with appropriate clauses such as those including new values. The STATEMENT column even provides the exact SQL statements for implementing
these recommendations.

In the final step of the implementation, the Advisor suggests changes in the way the MV is created. Note the difference in our example: a count
(*) has been added to the MV. Because we defined this MV as fast refreshable, the count(*) has to be there, so the Advisor corrected the
omission.

The procedure TUNE_MVIEW goes beyond what was available in EXPLAIN_MVIEW and EXPLAIN_REWRITE not just in its recommendations, but
also by identifying easier and more efficient paths for creating the same MV. Sometimes the Advisor can actually recommend more than a single
MV to make the query more efficient.

How is that useful, you may ask, when any seasoned DBA can find out what was missing in the MV creation script and then adjust it themselves?
Well, the Advisor does exactly that: it is a seasoned, highly motivated, robotic DBA that can make recommendations comparable to a human but
with a very important difference: it works for free and doesn't ask for vacations or raises. This benefit frees senior DBAs to offload routine tasks to
less senior ones, allowing them to apply their expertise to more strategic goals.
You can also pass an Advisor name as the value to the parameter in the TUNE_MVIEW procedure, which generates an Advisor with that name
instead of a system-generated one.

Easier Implementation
Now that you can see the recommendations, you may want to implement them. One way is to select the column STATEMENT, spool to a file, and
execute that script file. An easier alternative is to call a supplied packaged procedure:

begin
dbms_advisor.create_file (
dbms_advisor.get_task_script ('TASK_117'),
'MVTUNE_OUTDIR',
'mvtune_script.sql'
);
end;
/

This procedure call assumes that you have defined a directory object, such as:
create directory mvtune_outdir as '/home/oracle/mvtune_outdir';
The call to dbms_advisor will create a file called mvtune_script.sql in the directory /home/oracle/mvtune_outdir. If you take a look at this file, you will
see:

Rem SQL Access Advisor: Version 10.1.0.1 - Production
Rem
Rem Username: ARUP
Rem Task: TASK_117
Rem Execution date:
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."HOTELS"
WITH ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."HOTELS"
ADD ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."RESERVATIONS"
WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID C2, ARUP.HOTELS.CITY
C3, COUNT(*) M1 FROM ARUP.RESERVATIONS, ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID
= ARUP.RESERVATIONS.HOTEL_ID GROUP BY ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.
RESV_ID,
ARUP.HOTELS.CITY;
whenever sqlerror EXIT SQL.SQLCODE
begin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;
/

This file contains everything you need to implement the recommendations, saving you considerable trouble in creating a file by hand. Once again,
the robotic DBA can do your job for you.

Rewrite or Die!
By now you must have realized how important and useful the Query Rewrite feature is. It significantly reduces I/O and processing and returns
results faster.
Let's imagine a situation based on the above example. The user issues the following query:
Select city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
The execution stats show the following:
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)

Note the value of consistent gets, which is 6—a very low value. This result is based on the fact that the query was rewritten to use the two MVs
created on the three tables. The selection was not from the tables, but from the MVs, thereby consuming fewer resources such as I/O and CPU.
But what if the query rewrite had failed? It could fail for several reasons: If the value of the initialization parameter query_rewrite_integrity is
set to TRUSTED and the MV status is STALE, the query will not be rewritten. You could simulate this process by setting the value in the session
before the query.

alter session set query_rewrite_enabled = false;

After this command, the explain plan shows the selection from all three tables and not from the MVs. The execution stats now show:
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)

Note the value of consistent gets: it jumps to 16 from 6. In a real-life situation, this result may be unacceptable because the additional required
resources are unavailable, and thus you may want to rewrite the query yourself. In that case, you can ensure that the query should be allowed if
and only if it is rewritten.
In Oracle9i Database and below, the decision is one-way: you can disable Query Rewrite but not the base table access. Oracle Database 10g,
however, provides a mechanism to do that via a special hint, REWRITE_OR_ERROR. The above query would be written with the hint like this:

select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
Note the error message now.
from hotels h, reservations r, trans t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite
ORA-30393 is a special type of error that indicates the statement could not be rewritten to make use of the MVs; hence, the statement failed. This
failsafe will prevent potentially long running queries from hogging system resources. Beware of one potential pitfall, however: the query will be
successful if one, not all, of the MVs could be used in rewriting the query. So if MV_ACTUAL_SALES but not MV_HOTEL_RESV can be used, the
query will be rewritten and the error will not occur. In this case the execution plan will look like:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=6 Bytes=156)
1 0 SORT (GROUP BY) (Cost=11 Card=6 Bytes=156)
2 1 HASH JOIN (Cost=10 Card=80 Bytes=2080)
3 2 MERGE JOIN (Cost=6 Card=80 Bytes=1520)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'HOTELS' (TABLE) (Cost=2 Card=8 Bytes=104)
5 4 INDEX (FULL SCAN) OF 'PK_HOTELS' (INDEX (UNIQUE)) (Cost=1 Card=8)
6 3 SORT (JOIN) (Cost=4 Card=80 Bytes=480)
7 6 TABLE ACCESS (FULL) OF 'RESERVATIONS' (TABLE) (Cost=3 Card=80 Bytes=480)
8 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) (Cost=3
Card=80 Bytes=560)

The query did use MV_ACTUAL_SALES but not MV_HOTEL_RESV; thus, the tables HOTELS and RESERVATIONS are accessed. This
approach, especially the full table scan of the latter, will definitely use more resources—a situation you would note while designing queries and
MVs.

Although you can always control resource utilization using Resource Manager, using the hint will prevent the issuance of queries even before the
Resource Manager is called. Resource Manager estimates required resources based on optimizer statistics, so the presence or absence of
reasonably accurate statistics will affect that process. The rewrite or error feature, however, will stop table access regardless of statistics.
Explain Plan Explains Better

In the previous example, note the line in the explain plan output:
MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE)
This method of access—MAT_VIEW REWRITE—is new; it shows that the MV is being accessed, not the table or segment. This procedure
immediately tells you if the table or MV is used, even if the names don't imply the nature of the segment.

RMAN

2009-04-04T14:28:00.000-07:00

RMAN

RMAN becomes more powerful with a redesigned incremental backup scheme, offline recovery of incremental backups, previewing
restore, recovering through resetlogs, file compression, and much more
Most people would agree that RMAN is the de facto tool of choice for Oracle database backup. But as powerful as they were, early versions of
RMAN left something to be desired. Like many DBAs, I had pet peeves about the absence of what I consider to be must-have features.
Fortunately, Oracle Database 10g addresses many of these issues by incorporating many desirable features, making RMAN an even more
powerful and useful tool. Let's take a look.

Incremental Backups Revisited
RMAN includes an option for incremental backups. But truthfully, how often do you use it? Probably occasionally, or possibly even never.
This option instructs the tool to back up blocks that have changed since the last incremental backup at the same level or below. For instance, a full
backup (level_0) is taken on day 1 and two incrementals of level_1 are taken on days 2 and 3. The latter two merely back up the changed blocks
between days 1 and 2 and days 2 and 3, not across the entire backup time. This strategy reduces backup size, requiring less space, and narrows
the backup window, reducing the amount of data moving across the network.
The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in
NOLOGGING mode and data changes do not go to the archived log files—hence, no media recovery is possible. Considering the massive size of
data warehouses today, and the fact that most of the data in them does not change, full backups are neither desirable nor practical. Rather, doing
incremental backups in RMAN is an ideal alternative.
So why do many DBAs do incremental backups only rarely? One reason is that in Oracle9i and below, RMAN scans all the data blocks to identify
candidates for backup. This process puts so much stress on the system that doing incrementals becomes impractical.

Oracle Database 10g RMAN implements incremental backups in a manner that disposes of that objection. It uses a file, analogous to journals in
filesystems, to track the blocks that have changed since the last backup. RMAN reads this file to determine which blocks are to be backed up.
You can enable this tracking mechanism by issuing the following command:

SQL> alter database enable block change tracking using file '/rman_bkups/change.log';
This command creates a binary file called /rman_bkups/change.log for tracking purposes. Conversely, you can disable tracking with
SQL> alter database disable block change tracking;
To see whether change tracking is currently enabled, you can query:
SQL> select filename, status from v$block_change_tracking;
Flash Recovery Area

Flashback queries, introduced in Oracle9i, depend on undo tablespace to flash-back to a prior version, thereby limiting its ability go too far into the
past. Flash recovery provided an alternative solution by creating flashback logs, which are similar to redo logs, to revert the database to a prior
state. In summary, you create a flash recovery area for the database, specify its size, and place the database in flash recovery mode with the
following SQL commands:

alter system set db_recovery_file_dest = '/ora_flash_area';
alter system set db_recovery_file_dest_size = 2g;
alter system set db_flashback_retention_target = 1440;
alter database flashback on;

The database must be in archive log mode to be flashback-enabled. That process creates Oracle Managed Files in the directory /ora_flash_area,
with a total size of up to 2GB. The database changes are written to these files and can be used to quickly recover the database to a point in the
past.

By default, RMAN also uses /ora_flash_area to store backup files; thus, RMAN backups are stored on disk, not tape. For that reason, you have the
ability to specify how many days you need to keep backups. After that period, the files are automatically deleted if more space is required.

The flash recovery area needn't be a filesystem or a directory, however—alternatively, it could be an Automatic Storage Management (ASM)
diskgroup. In that case, the flash recovery area is specified by:

alter system set db_recovery_file_dest = '+dskgrp1';

Consequently, using ASM and RMAN in combination, you can build a highly scaleable, fault-tolerant storage system using cheap disks such as
Serial ATA or SCSI drives, with no additional software required. (For more details about ASM, see the Week 8 installment in this series.) This
approach not only makes the backup process much faster but also cheap enough to compete with the tape-based approach.

An additional benefit is protection against user errors. Because ASM files are not true filesystems, they are less likely to be corrupted accidentally
by DBAs and sysadmins.

Incremental Merge

Let's say you have the following backup schedule:
Sunday - Level 0 (full), with tag level_0
Monday - Level 1 (incremental) with tag level_1_mon
Tuesday - Level 1 (incremental) with tag level_1_tue

and so on. If the database fails on Saturday, prior to 10g you would have had to restore the tag level_0 and then apply all six incrementals. It would
have taken a long time, which is another reason many DBAs shun incremental backups.
Oracle Database 10g RMAN radically changes that equation. Now, your incremental backup command looks like this:

RMAN> backup incremental level_1 for recover of copy with tag level_0 database;
Here we have instructed RMAN to make an incremental level_1 backup and merge that with the full backup copy with the tag level_0. After this
command, level_0 becomes a full backup of that day.

So, on Tuesday, the backup with tag level_0, when merged with incremental level_1 backup, becomes identical to the full Tuesday backup.

Similarly, the incremental taken on Saturday, when applied to the backup on disk, will be equivalent to a full level_0 Saturday backup. If the
database fails on Saturday, you just need to restore the level_0 backup plus a few archive logs to bring the database into a consistent state; there is
no need to apply additional incrementals. This approach cuts down recovery time dramatically, speeds backup, and eliminates the need to make a
full database backup again.

Compressed Files

With disk-based backups in the flash recovery area, you still have a big limitation: disk space. Especially when going across a network—as is
usually the case—it's advisable to create as small a backup set as possible. In Oracle Database 10g RMAN, you can compress files inside the
backup command itself:

RMAN> backup as compressed backupset incremental level 1 database;
Note the use of the clause COMPRESSED. It compresses backup files with an important difference: while restoring, RMAN can read the files without
uncompressing. To confirm compression, check for the following message in the output:
channel ORA_DISK_1: starting compressed incremental level 1 datafile backupset
Furthermore, you can verify that the backup was compressed by checking the RMAN list output:

RMAN> list output;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Incr 1 2M DISK 00:00:00 26-FEB-04
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20040226T100154
Piece Name: /ora_flash_area/SMILEY10/backupset/2004_02_26/
o1_mf_ncsn1_TAG20040226T100154_03w2m3lr_.bkp
Controlfile Included: Ckp SCN: 318556 Ckp time: 26-FEB-04
SPFILE Included: Modification time: 26-FEB-04
As with any compression process, this approach puts pressure on CPUs. As a tradeoff, you can keep more RMAN backups on disk that are readily
available for restore-and-recover operations. Alternatively, you can make RMAN backups at the Physical Standby Database that can be used to
recover the primary database. That approach will offload backup resourses to another host.

Look Before You Leap: Recovery Preview

In Oracle Database 10g, RMAN has gone one more step ahead by providing the ability to preview the backups required to perform a restore
operation.

RMAN> restore database preview;
Listing 1 shows the output of this operation. You can also preview specific restore operations; for example:

restore tablespace users preview;

Preview allows you to ensure the recovery readiness of your backup infrastructure by making periodic and regular checks.

Resetlogs and Recovery

Let's imagine that you have lost the current online redo log files and you have to perform an incomplete database recovery—a rare but not unheard
of situation. The biggest problem is resetlogs; after incomplete recovery you must open the database with the resetlogs clause, which sets the
sequence number of the log threads to 1, making your earlier backups obsolete in RMAN and making the recovery operation more of a challenge.
In Oracle9i and below, if you need to restore the database to a version prior to resetlogs, you have to restore to a different incarnation. In Oracle
Database 10g, you don't have to do that. Thanks to additional infrastructure in the control file, RMAN can now readily use all backups, before and
after a resetlogs operation, to recover the Oracle database. There is no need to shut down the database to make a backup. This new capability
means that the database can be re-opened immediately for the user community after a resetlogs operation.

Ready for RMAN

The enhancements in Oracle Database 10g RMAN make it an even more compelling tool in your backup strategy. The improvements to the
incremental backup process alone make RMAN tough to ignore.

Automatic Storage Management

2009-04-04T14:19:00.000-07:00

Automatic Storage Management

You just received a brand-new server and storage subsystem for a new Oracle database. Aside from operating system configuration, what is your
most important before you can create the database? Obviously, it's creating the storage system layout—or more specifically, choosing a level of
protection and then building the necessary Redundant Array of Inexpensive Disks (RAID) sets.

Setting up storage takes a significant amount of time during most database installations. Zeroing on a specific disk configuration from among the
multiple possibilities requires careful planning and analysis, and, most important, intimate knowledge of storage technology, volume managers, and
filesystems. The design tasks at this stage can be loosely described as follows (note that this list is merely representative; tasks will vary by
configuration):

1. Confirm that storage is recognized at the OS level and determine the level of redundancy protection that might already be provided
(hardware RAID).
2. Assemble and build logical volume groups and determine if striping or mirroring is also necessary.
3. Build a file system on the logical volumes created by the logical volume manager.
4. Set the ownership and privileges so that the Oracle process can open, read, and write to the devices.
5. Create a database on that filesystem while taking care to create special files such as redo logs, temporary tablespaces, and undo
tablespaces in non-RAID locations, if possible.
In most shops, the majority of these steps are executed by someone with lots of knowledge about the storage system. That "someone" is usually
not the DBA.
Notice, however, that all these tasks—striping, mirroring, logical filesystem building—are done to serve only one type of server, our Oracle
Database. So, wouldn't it make sense for Oracle to offer some techniques of its own to simplify or enhance the process?
Oracle Database 10g does exactly that. A new and exciting feature, Automatic Storage Management (ASM), lets DBAs execute many of the above
tasks completely within the Oracle framework. Using ASM you can transform a bunch of disks to a highly scalable (and the stress is on the word
scalable) and performant filesystem/volume manager using nothing more than what comes with Oracle Database 10g software at no extra cost.
And, no, you don't need to be an expert in disk, volume managers, or file system management.
In this installment, you will learn enough about ASM basics to start using it in real-world applications. As you might guess, this powerful feature
warrants a comprehensive discussion that would go far beyond our current word count, so if you want to learn more, I've listed some excellent
sources of information at the conclusion.

What is ASM?
Let's say that you have 10 disks to be used in the database. With ASM, you don't have to create anything on the OS side; the feature will group a
set of physical disks to a logical entity known as a diskgroup. A diskgroup is analogous to a striped (and optionally mirrored) filesystem, with
important differences: it's not a general-purpose filesystem for storing user files and it's not buffered. Because of the latter, a diskgroup offers the
advantage of direct access to this space as a raw device yet provides the convenience and flexibility of a filesystem.
Logical volume managers typically use a function, such as hashing to map the logical address of the blocks to the physical blocks. This computation
uses CPU cycles. Furthermore, when a new disk (or RAID-5 set of disks) is added, this typical striping function requires each bit of the entire data
set to be relocated.
In contrast, ASM uses a special Oracle Instance to address the mapping of the file extents to the physical disk blocks. This design, in addition to
being fast in locating the file extents, helps while adding or removing disks because the locations of file extents need not be coordinated. This
special ASM instance is similar to other filesystems in that it must be running for ASM to work and can't be modified by the user. One ASM instance
can service a number of Oracle databases instances on the same server.
This special instance is just that: an instance, not a database where users can create objects. All the metadata about the disks are stored in the
diskgroups themselves, making them as self-describing as possible.
So in a nutshell, what are the advantages of ASM?

l Disk Addition—Adding a disk becomes very easy. No downtime is required and file extents are redistributed automatically.
l I/O Distribution—I/O is spread over all the available disks automatically, without manual intervention, reducing chances of a hot spot.
l Stripe Width—Striping can be fine grained as in Redo Log Files (128K for faster transfer rate) and coarse for datafiles (1MB for transfer of a
large number of blocks at one time).
l Buffering—The ASM filesystem is not buffered, making it direct I/O capable by design.
l Kernelized Asynch I/O—There is no special setup necessary to enable kernelized asynchronous I/O, without using raw or third-party
filesystems such as Veritas Quick I/O.
l Mirroring—Software mirroring can be set up easily, if hardware mirroring is not available.

Creating an ASM-enabled Database, Step by Step
Here's a concrete example of how you would create an ASM-enabled database:
1. Set up an ASM Instance
You create an ASM instance via the Database Creation Assistant by specifying the following initialization parameter:
INSTANCE_TYPE = ASM
You should start the instance up when the server is booted, and it should be one of the last things stopped when the server is shut down.
By default the value of this parameter is RDBMS, for regular databases.
2. Set up a Disk Group
After starting the ASM instance, create a disk group with the available disks.
CREATE DISKGROUP dskgrp1
EXTERNAL REDUNDANCY
DISK
'/dev/d1',
'/dev/d2',
'/dev/d3',
'/dev/d4',
... and so on for all the specific disks ...
;
In the above command, we have instructed the database to create a diskgroup named dksgrp1 with the physical disks named /dev/d1, /dev/d2, and
so on. Instead of giving disks separately, you can also specify disk names in wildcards in the DISK clause as follows.
DISK '/dev/d*'

In the above command, we have specified a clause EXTERNAL REDUNDANCY, which indicates that the failure of a disk will bring down the
diskgroup. This is usually the case when the redundancy is provided by the hardware, such as mirroring. If there is no hardware based redundancy,
the ASM can be set up to create a special set of disks called failgroup in the diskgroup to provide that redundancy.
CREATE DISKGROUP dskgrp1
NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK
'/dev/d1',
'/dev/d2',
FAILGROUP failgrp2 DISK
'/dev/d3',
'/dev/d4';

Although it may appear as such, d3 and d4 are not mirrors of d1 and d2. Rather, ASM uses all the disks to create a fault-tolerant system. For
instance, a file on the diskgroup might be created in d1 with a copy maintained on d4. A second file may be created on d3 with copy on d2, and so
on. Failure of a specific disk allows a copy on another disk so that the operation can continue. For example, you could lose the controller for both
disks d1 and d2 and ASM would mirror copies of the extents across the failure group to maintain data integrity.
3. Create Tablespace
Now create a tablespace in the main database using a datafile in the ASM-enabled storage.
CREATE TABLESPACE USER_DATA DATAFILE '+dskgrp1/user_data_01'
SIZE 1024M
/
That's it! The setup process is complete.
Note how the diskgroup is used as a virtual filesystem. This approach is useful not only in data files, but in other types of Oracle files as well. For
instance, you could create online redo log files as
LOGFILE GROUP 1 (
'+dskgrp1/redo/group_1.258.3',
'+dskgrp2/redo/group_1.258.3'
) SIZE 50M,
...

Even archived log destinations can also be set to a diskgroup. Pretty much everything related to
Oracle Database can be created in an ASM-based diskgroup. For example, backup is another great
use of ASM. You can set up a bunch of inexpensive disks to create the recovery area of a database,
which can be used by RMAN to create backup datafiles and archived log files. (In the next
installment about RMAN in Oracle Database 10g, you'll learn in detail how to use that capability to
your advantage.)
Please bear in mind however that ASM supports files created by and read by the Oracle Database
only; it is not a replacement for a general-purpose filesystem and cannot store binaries or flat files.
Maintenance
Let's examine some typical tasks needed to maintain the diskgroups. From time to time, you may
have to add additional disks into the diskgroup dskgrp1 to accommodate growing demand. You
would issue:
alter diskgroup dskgrp1 add disk '/dev/d5';
To find out what disks are in what diskgroup, you would issue:
select * from v$asm_disk;
This command shows all the disks managed by the ASM instance for all the client databases. Of
these disks, you may decide to remove a disk with:
alter diskgroup dskgrp1 drop disk diskb23;