Oracle Apps DBA & SYSADMIN

How to Resolve ORA-00600:[kwqmfidliot:ins] Internal Error

2010-10-26T13:45:00.000-07:00

In our production database we got this error ORA-00600: internal error code, arguments: [kwqmfidliot:ins], [], [], [], [], [], [], [] during OATM implementation.

In one of the trace file we found that our Workflow background queue "WF_DEFERRED_TABLE_M" got corrupted.

Trace file Contents

Dump event group for SYSTEM

kwqicaclcur: Error 600

Cursor Session Number : 7921

Cursor Session Serial : 2

Cursor Pin Number : 9

Error 600 in Queue Table "APPLSYS"."WF_DEFERRED_TABLE_M"

error 600 encountered during serving 37461

*** 2010-08-15 13:22:30.111

ORA-00600: internal error code, arguments: [kwqitnmptme:wait/done/exp], [0], [], [], [], [], [], []

In oracle metalink any bugs were reported for this specially during OATM implementation which can cause workflow background queue to corrupt due to intransit data in the queue tables.

In this case we had to rebuild our workflow background queue based on the following steps.

1. Drop the corrupted workflow queue. In this case make sure we need to use uppercase for the APPLSYS APPS:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfqued.sql APPLSYS

2. Now it's time to Recreate the corrupted workflow queue

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfquec2.sql APPS APPLSYS

3. Recreate WF_DEFERRED_TABLE_M_N1 index on the workflow queue table.

sqlplus apps/apps @FND_TOP/patch/115/sql/wfqidxc.sql APPLSYS APPS

4. Re-populate Workflow Queue table WF_DEFERRED_TABLE_M with data. We need to make sure all the components of workflow are down like background engine, listeners etc.

sqlplus apps/apps @$FND_TOP/sql/wfbkgbld.sql APPLSYS

5. Recreate following indexes if they are mssing after queue creation.

WF_INBOUND_TABLE_PK
WF_OUTBOUND_TABLE_PK

6. Gather stats for Workflow tables to recreate missing histograms if any.

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfhistc.sql APPLSYS

7. Recreate the Subscribers for queues

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfmqsubc2.sql applsys apps ( 11.5.10.2)

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfmqsubc.sql applsys apps ( 11.5.10)

wfmqsubc.sql is changed to wfmqsubc2.sql that creates subscribers for
WF_NOTIFICATION_OUT and WF_NOTIFICATION_IN. (after 11.5.10.2)

8. Recreate the Grant & Synonyms

sqlplus apps/apps @FND_TOP/patch/115/sql/afwfqgnt.sql apps apps applsys apps

After implementing all steps mentioned above restart all the workflow engine related components.

How to Install Oracle Applications R12 E-Business Suite

2009-09-27T00:21:00.000-07:00

In this post i will discuss steps to install Oracle Applications R12 e-Business Suite.

First we need to create a staging area by unzipping all the zip files to create folders as given below.

docs
startCD
oraDB
oraAPPS
oraAS
oraAPPDB

This screen lists the components that are included in, or supported by R12 Oracle Applications. Click Next to continue.

Use the Select Wizard Operation screen to select first option ‘install oracle applications release 12.1.1. This screen can be used for both new installations and upgrades. Based on the selection the Rapid Install wizard continues with the appropriate screen flow.

In this screen Provide your Metalink userID and password.

Configuration Choice screen, this indicate whether you will be using an existing Configuration file.In this screen you can specify Configuration file to use in Multi node Architecture, but if this is the first server in multinode architecture then select 'Create a new configuration'.

In this screen please specify Port Pool. Based on Port pool number oracle will assign port numbers to different Application services like http, forms,db etc..

In this screen please specify the database type,SID, Node name, Domain Name and Operating system for Database Server node.

In this screen you need to specify server Name, Domain Name, Operating system, Application Owner and Group etc.

In this screen please specify what services can be run on this Application server node based on your requirement.

In this screen you can add additional servers to cater different services based on your requirement.

In this screen you need to specify server Name, Domain Name, Operating system, Application Owner and Group etc for additional Application server.

In this screen please specify what services can be run on this additional Application server node based on your requirement.

At this point oracle will verify your configuration and if something is not correct it will give you an error and you may need to correct it before you start instllation.

This is the final screen before installation for your review.

In this screen click on Yes to start Installation.

This is installation screen where oracle will show you the installation progress.

Once Installation is finished Oracle will verify the system for Post installation steps.

And and Hurray you are done with the installation and now start exploring Release 12...

How to install Oracle Applications 11i e-Business Suite

2009-09-14T23:30:00.000-07:00

In this post i will discuss steps to install Oracle Applications 11i e-Business Suite version 11.5.10.2.

First we need to create a staging area by unzipping all the zip files to create folders as given below.

docs
startCD
oraDB
oraAppDB
oraiAS
oraApps

  Next thing we need to make sure that all the OS related pre-requisite steps have been completed and all the required OS patches have been applied.

  Now start rapidwiz from startCD/Disk1/rapidwiz>./rapidwiz location ( don't forget to export DISPLAY parameter to valid x-server)

  First screen of installer will show you the list of components Rapidwiz will install as a part of Oracle applications 11i Installation. Click next to go the next screen.

In the second screen you will have a choice to install a fresh Oracle Applications E-Business 11i or Upgrade to E-Business 11i from previous versions.

In this screen you can specify Configuration file to use in Multi node Architecture, but if this is the first server in multinode architecture then select 'No'.

In this screen you will get the option to select single node installation or Multinode installation. Based on your requirement please select the option.

In this screen you can select Load balancing option if you have multiple Application tier like two or more Web tiers and two or more Admin Tier servers.

In this screen please specify server name or Node name and Operating system for each Service like database, admin, concurrent and forms.
If you are planning to use Shared File system please configure shared file system option in the screen only by clicking details button otherwise uncheck shared file system option.

In this screen you need to specify the purpose of this instance. You can select Vision Demo Instance or you can select custom instance for your production,development or test purpose.

In this screen you need to specify Oracle Database owner, group and file system (Mount points) architecture for binary files and database files.

In this screen you need to specify Application Owner like applmgr and File System Architecture for all Application services. You can select single owner for database and Application if you are planning to install Vision instance only.

In this screen please specify Domain Name, X-server Address and Port Pool. Based on Port pool number oracle will assign port numbers to different Application services like http, forms,db etc..

If you are planning to use Multinode Architecture then you should save your configuration in some file which you will be using during other node installation.

At this point oracle will verify your configuration and if something is not correct it will give you an error and you may need to correct it before you start instllation.

This is the final screen before installation for your review.

In this screen click on Yes to start Installation.

This is installation screen where oracle will show you the installation progress.

Once Installation is finished Oracle will verify the system for Post installation steps.

And and Hurray you are done with the installation and now start exploring...

How to Configure Oracle Java Workflow Notification Mailer in Oracle apps 11i

2009-09-02T01:23:00.000-07:00

Use following simple steps to configure Oracle Java Workflow Notification mailer in Oracle applications 11i

• Run $FND_TOP/sql/wfver.sql and review the output
• Check for invalid objects
• Generic Service Management (GSM) should be enabled in the system. Workflow mailer program runs as a service with in GSM.
• Verify if GSM DEBUG service run successfully without exceptions in the log from OAM.
• JDK1.3 or higher must be installed and configured on Concurrent processing node.

JDK1.3 Home/bin/java -version

• Verify following variables for JDK version

AF_JRE_TOP = $JDK1.3 Home
AFJVAPRG = $JDK1.3 Home/bin/java
AF_CLASSPATH = $JDK1.3 Home/lib/dt.jar:$JDK1.3 Home/lib/tools.jar:$JDK1.3 Home/jre/lib/rt.jar:$JDK1.3 Home/jre/lib/i18.jar: $JAVA_TOP/appsborg2.zip:$ORACLE HOME 8.0.6/forms60/java:$JAVA_TOP

• Verify if JMS libraries exist in AF_CLASSPATH.

grep jar $APPL_TOP/admin/adjborg2.txt

Sample output should look like:

$ORACLE HOME 8.0.6/owm/jlib/jssl-1_1.jar
$ORACLE HOME 8.0.6/owm/jlib/javax-ssl-1_1.jar
$iAS HOME/rdbms/jlib/aqapi.jar
$iAS HOME/rdbms/jlib/jmscommon.jar
$iAS HOME/Apache/xsu/xsu12.jar
$iAS HOME/Apache/xsu/oraclexmlsql.jar
$iAS HOME/rdbms/jlib/xsu12.jar
$iAS HOME/lib/http_client.jar

If the output is 0 (zero), it means that appsborg2.zip does not contain the JMS classes

• Create a IMAP Account in Lotus Notes with following information

User ID : WFMAILER
Password : WFMAILER
INBOX folder
PROCESS folder
DISCARD folder

Test your IMAP account

telnet testorcl.com 143
Trying 112.126.180.67...
Connected to testorcl.com.
Escape character is '^]'.
* OK Domino IMAP4 Server Release 5.0.11 ready Sun, 23 Nov 2003 10:26:49 -0800
1 login WFMAILER WFMAILER
1 OK LOGIN completed
1 select "INBOX"
* 23 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 0] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
1 select "DISCARD"
* 54 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 6] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
1 select "PROCESSED"
* 58 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 4] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
1 logout
* BYE testorcl.com IMAP4rev1 server terminating connection
1 OK LOGOUT completed
Connection closed by foreign host.

Verify SMTP server Configuration

telnet mailhost 25
MAIL FROM: wfmailer@orcl.com
RCPT TO: kapil@orcl.com
DATA
Subject: Test message from workflow (mailhost)
Hello Kapil
.
Verify that kapil@orcl.com receives an email from wfmailer@orcl.com . If not, then the SMTP server is not configured properly.

Configure Workflow Notification Mailer using OAM

Log on to Oracle Application manager

Navigate to Workflow manager from pull down menu

Click the Notification Mailer status icon to navigate to the Service Components page for notification mailers

In the Service Components page, select the Workflow Notification Mailer service component and click the Edit button

Leave all settings as a default in the first page

Click on the next button to go to second step. If you are using inbound mails then define Inbound Thread Count more than 0 or else 0. For Outbound Mail define Outbound Thread Count to 1

In the Inbound eMail Account region, enter the inbound mail server and the username and password for the IMAP e-mail account. Please remember userid and password is case sensitive. In the Outbound eMail Account region, enter the outbound mail server that the Workflow Notification Mailer will use.

In the Send region of the Edit Notification Mailer: Message Generation page, enter the address of the reply-to e-mail account that receives incoming messages and the base URL that identifies the HTML web agent that handles HTML notification responses.

All other configuration parameters for the Workflow Notification Mailer are set to default values.
After you finish setting the configuration parameters, go to service component page and click on Workflow mailer Service.

In the Service Instances for Generic Service Component Container page select start all from pull down menu and click on go button.

Go to Service component page once again and verify that the status of the Workflow Notification Mailer service component is now running.

In the next Post i will discuss Workflow mailer troubleshooting.

How to Find out Undocumented Paramaters in Oracle Database

2009-09-01T17:53:00.000-07:00

Use following SQL Query to find out Undocumented paramater and their value.

SELECT xpi.ksppinm "Parameter Name",
xpi.ksppdesc "Description",
xcv.ksppstvl "Session Value",
xsv.ksppstvl "Instance Value"
FROM x$ksppi xpi,
x$ksppcv xcv,
x$ksppsv xsv
WHERE xpi.indx = xcv.indx
AND xpi.indx = xsv.indx
AND SUBSTR (xpi.ksppinm, 1, 1) ='_'
ORDER BY xpi.ksppinm;

How to Monitor Long Running Concurrent Requests in Oracle 11i

2009-09-01T16:44:00.000-07:00

Use following Script to monitor long running concurrent requests in Oracle 11i

------------Start of Script-------------------------------------
#!/bin/sh
# Long Running Concurrent Requests
#

SPOOL_FILE=long_runn_conc.html
FINAL_FILE=Long_Running_Concurrent.html

pchk1=`ps -ef | grep ora_smon_$ORACLE_SID | grep -v grep | wc -l`

if [ "$pchk1" -eq 0 ]; then
     echo "WARNING: Possible database shutdown problem"
     exit 0
fi

echo "Content-Type: text/html" >> ${FINAL_FILE}
sqlplus -s apps/apps EOF
set echo off
set pagesize 120
set markup html on spool on
spool ${SPOOL_FILE}

TTITLE CENTER 'Concurrent Requests running for more than 20 minutes'

SELECT gv.inst_id "Instance Number",
gv.sid "Sid",
gv.serial# "Serial#",
fcr.request_id "Request ID",
substr(fcr.program,1,40) "Program" ,
fcr.phase "Phase",
fcr.status "Status",
to_char(fcr.actual_start_date,'DD-MON-RR HH24:MI:SS') "Start",
to_char(fcr.actual_completion_date,'DD-MON-RR HH24:MI:SS') "End",
ROUND( ( NVL( fcr.actual_completion_date, sysdate ) - fcr.actual_start_date ) *60*24, 2 ) "Time(Min)",
fcqtl.user_concurrent_queue_name "Concurrent Manager",
fcr.user_name "User Name",
substr(gvw.event,1,30) "Event"
FROM apps.fnd_amp_requests_v fcr,
gv$session gv,
gv$session_wait gvw,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_processes fcproc
WHERE Phase_code ='R'
and fcr.oracle_session_id=gv.audsid(+)
and gv.sid=gvw.sid(+)
and gv.inst_id=gvw.inst_id(+)
and fcr.controlling_manager = fcproc.concurrent_process_id
and fcproc.queue_application_id = fcqtl.application_id
and fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
and fcqtl.language='US'
and ROUND( ( NVL( fcr.actual_completion_date, sysdate ) - fcr.actual_start_date ) *60*24, 2 ) > 20
order by 9 desc;

spool off
set markup html off spool off
EOF

if [ `grep -c 'no rows selected' ${SPOOL_FILE}` -eq 1 ]
then
   rm ${FINAL_FILE}
   rm ${SPOOL_FILE}
exit 0
fi

cat ${SPOOL_FILE} | grep -v 'rows selected' >> ${FINAL_FILE}

(echo "Importance: High"; echo "Subject: VGOP: Long Running Concurrent Requests"; cat ${FINAL_FILE}) | /usr/sbin/sendmail -F VGOP dbadmin@activision.com

rm ${FINAL_FILE}
rm ${SPOOL_FILE}

-------------End of Script-------------------------------------

Use following SQL to get more information.

SELECT gv.inst_id "Instance Number", gv.SID "Sid", gv.serial# "Serial#", fcr.request_id "Request ID", SUBSTR (fcr.program, 1, 40) "Program", fcr.phase "Phase", fcr.status "Status",
   TO_CHAR (NEW_TIME (fcr.actual_start_date, 'GMT', 'PDT'), 'DD-MON-RR HH24:MI:SS' ) "Start",
   TO_CHAR (NEW_TIME (fcr.actual_completion_date, 'GMT', 'PDT'), 'DD-MON-RR HH24:MI:SS' ) "End",
   ROUND ( ( NVL (fcr.actual_completion_date, SYSDATE)
   - fcr.actual_start_date )* 60* 24,2) "Time(Min)",
   fcqtl.user_concurrent_queue_name "Concurrent Manager",
   fcr.user_name "User Name",
   (CASE
   WHEN gvw.event = 'latch free'
   THEN (SELECT vl.NAME || ': Latch Name'
   FROM v$latch vl
   WHERE vl.latch# = gvw.p2)
   ELSE SUBSTR (gvw.event, 1, 30)
   END
   ) "Event",
   (CASE
   WHEN (gvw.event = 'db file sequential read' or gvw.event ='gc buffer busy')
   AND dbo.object_name IS NULL
   THEN 'Rollback Segment'
   ELSE dbo.object_name
   END
   ) "Database Object",
   fcr.argument_text, gvw.p1, gvw.p2, gv.sql_id,gp.spid
   FROM apps.fnd_amp_requests_v fcr,
   gv$session gv,
   gv$process gp,
   gv$session_wait gvw,
   dba_objects dbo,
   fnd_concurrent_queues_tl fcqtl,
   fnd_concurrent_processes fcproc
   WHERE phase_code = 'R'
   AND gv.paddr = gp.addr(+)
   and gv.inst_id=gp.inst_id(+)
   AND fcr.oracle_session_id = gv.audsid(+)
   AND gv.SID = gvw.SID(+)
   AND gv.inst_id = gvw.inst_id(+)
   AND gv.row_wait_obj# = dbo.object_id(+)
   AND fcr.controlling_manager = fcproc.concurrent_process_id
   AND fcproc.queue_application_id = fcqtl.application_id
   AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
   AND fcqtl.LANGUAGE = 'US'
ORDER BY 10 DESC;

How to Monitor Temp Tablespace usage and Sort Operations

2009-09-01T16:27:00.000-07:00

Use following Script to monitor Temp tablespace and Sort Operations in Oracle Database.

------------Start of Script-------------------------------------

#!/bin/sh
# Temp Tablespace & Sort segments Usage
#

SPOOL_FILE=temp_seg.html
FINAL_FILE=Temp_Segments.html
echo "Content-Type: text/html" >> ${FINAL_FILE}
sqlplus -s "/ as sysdba" EOF
set echo off
set pagesize 220
set markup html on spool on
spool ${SPOOL_FILE}
TTITLE CENTER 'Temp Tablespace and Sort Segments Usage'

SELECT gvs.inst_id "Instance",
gvs.SID,
gvs.username "User Name",
NVL (gvs.status, ' ') "Status",
NVL (gvs.osuser, ' ') "OS User",
NVL (gvs.program, ' ') "Program",
NVL (gvs.module, ' ') "Module",
gsu.TABLESPACE "Tablespace Name",
SUM (gsu.extents) "# of Ext",
SUM (gsu.blocks) "# of Blocks"
FROM gv$session gvs,
gv$sort_usage gsu
WHERE gvs.saddr = gsu.session_addr
AND gvs.inst_id = gsu.inst_id
GROUP BY gvs.inst_id,
gvs.SID,
gvs.username,
gvs.status,
gvs.osuser,
gvs.program,
gvs.module,
gsu.TABLESPACE
ORDER BY 9 DESC;

spool off

set markup html off spool off
EOF

if [ `grep -c 'no rows selected' ${SPOOL_FILE}` -eq 1 ]
then
rm ${FINAL_FILE}
rm ${SPOOL_FILE}
exit 0
fi

cat ${SPOOL_FILE} | grep -v 'rows selected' >> ${FINAL_FILE}

(echo "Importance: High"; echo "Subject: $ORACLE_SID: Temp Tablespace & Segment Usage"; cat ${FINAL_FILE}) | /usr/sbin/sendmail -F $ORACLE_SID test@testorcl.com

rm ${FINAL_FILE}
rm ${SPOOL_FILE}

------------End of Script--------------------------------------

How to Monitor Undo Tablespace and Segment Usage

2009-08-31T17:27:00.000-07:00

Starting from release 9i oracle introduced Automatic Undo Management to help DBAs and Developers to cope with ORA-01555: snapshot too old error. Ofcourse you can't completely avoid ORA-01555 error but you can tweak some parameters with Automatic Undo Management along with application code tuning to reduce it, Parameters like UNDO_RETENTION parameter, which defines time in minutes how long oracle will keep the committed transactions in UNDO segments.
One should use UNDO advisor in order to calculate optimum UNDO tablespace Size and UNDO_RETENTION parameter value.
Use following SQL script to monitor Undo tablespace and Undo Segments or Rollback Segments usage in Oracle Database.

------------Start of Script-------------------------------------
#!/bin/sh
# Undo Segment Usage
#

SPOOL_FILE=undo_seg.html
FINAL_FILE=Undo_Segments.html
echo "Content-Type: text/html" >> ${FINAL_FILE}
sqlplus -s "/ as sysdba" EOF
set echo off
set pagesize 220
set markup html on spool on
spool ${SPOOL_FILE}
TTITLE CENTER 'Undo Segments Usage'
SELECT gvs.inst_id "Instance",
gvs.SID,
glo.os_user_name "OS User",
glo.oracle_username "DB User",
dbo.owner "Schema",
SUBSTR (dbo.object_name, 1, 30) "Object Name",
SUBSTR (dbo.object_type, 1, 10) "Object Type",
SUBSTR (drs.segment_name, 1, 15) "RBS Name",
gvt.used_urec "# of Records",
gvt.used_ublk "# of Blocks",
drs.tablespace_name "Tablespace"
FROM
gv$locked_object glo,
dba_objects dbo,
dba_rollback_segs drs,
gv$transaction gvt,
gv$session gvs
WHERE glo.object_id = dbo.object_id
AND glo.xidusn = drs.segment_id
AND glo.xidusn = gvt.xidusn
AND glo.xidslot = gvt.xidslot
AND gvt.addr = gvs.taddr
AND gvt.used_ublk > 1000
ORDER BY gvt.used_ublk DESC;
spool off
set markup html off spool off
EOF

if [ `grep -c 'no rows selected' ${SPOOL_FILE}` -eq 1 ]
then
rm ${FINAL_FILE}
rm ${SPOOL_FILE}
exit 0
fi

cat ${SPOOL_FILE} | grep -v 'rows selected' >> ${FINAL_FILE}

(echo "Importance: High"; echo "Subject: $ORACLE_SID: Undo Segment Usage"; cat ${FINAL_FILE}) | /usr/sbin/sendmail -F $ORACLE_SID test@testorcl.com
rm ${FINAL_FILE}
rm ${SPOOL_FILE}

------------Start of Script--------------------------------------

How to Resolve ORA-27063 ORA-01114 ORA-01110 Errors

2009-08-31T11:57:00.001-07:00

During hardware failure we got following error while starting one of our instances.

ALTER DATABASE OPEN
Thu Aug 6 23:21:00 2009
Errors in file /u01_orcltest/db/10.2.0/admin/ORCLTEST_orcltestdb01/udump/orcltest_ora_8866.trc:
ORA-01110: data file 407: '/u02_orcltest/data/apps_ts_queues_001.dbf'
ORA-01114: IO error writing block to file 407 (block # 1)
ORA-27063: number of bytes read/written is incorrect
Additional information: 8192
Thu Aug 6 23:21:00 2009
ORA-01201: file 407 header failed to write correctly
ORA-1113 signalled during: ALTER DATABASE OPEN...

We thought that the above file got currepted and we need to do media recovery and when we tried to recover database we got the same error again.

Thu Aug 6 23:48:28 2009
ALTER DATABASE RECOVER database
Thu Aug 6 23:48:28 2009
Media Recovery Start
Thu Aug 6 23:48:28 2009
Errors in file /u01_orcltest/db/10.2.0/admin/ORCLTEST_orcltestdb01/udump/orcltest_ora_13199.trc:
ORA-01110: data file 407: '/u02_orcltest/data/apps_ts_queues_001.dbf'
ORA-01114: IO error writing block to file 407 (block # 1)
ORA-27063: number of bytes read/written is incorrect
Additional information: 8192
Thu Aug 6 23:48:28 2009
Media Recovery failed with error 1201
ORA-283 signalled during: ALTER DATABASE RECOVER database ...

At this point we asked our Unix team to unmount and mount file system again for this instance. Once file system remounted we tried to open database but failed with ORA error again. In order to save our database at this point we recovered database using online redo log files.

ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ...
Fri Aug 7 01:33:12 2009
ALTER DATABASE RECOVER LOGFILE '/u02_orcltest/data/log01a.log'
Fri Aug 7 01:33:12 2009
Media Recovery Log /u02_orcltest/data/log01a.log
Fri Aug 7 01:33:14 2009
Incomplete recovery applied all redo ever generated.
Recovery completed through change 1136061258397
Fri Aug 7 01:33:14 2009
Media Recovery Complete (ORCLTEST)
Completed: ALTER DATABASE RECOVER LOGFILE '/u02_orcltest/data/log01a.log'
Fri Aug 7 01:33:26 2009
alter database open resetlogs
Fri Aug 7 01:33:27 2009
RESETLOGS after complete recovery through change 1136061258397
Resetting resetlogs activation ID 2533705982 (0x970548fe)

How to Resolve ORA-00600: [kkoarl1]

2009-08-25T13:32:00.000-07:00

Here you go again with ORA-00600: internal error code, arguments: [kkoarl1], [90139], [46226], [], [], [], [], [] error in our production database followed by "Memory Notification: Library Cache Object loaded into SGA" warning message. Luckily this error is not serious and we have a workaround also for ORA-00600 error and warning message.

As per Oracle one can see this error with very large and complex query. In our case we got this error from very complex and very large Discoverer report.

ORA-00600: internal error code, arguments: [kkoarl1], [90139], [46226], [], [], [], [], []Current SQL statement for this session:SELECT CASE WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '500000' THEN '220270' WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '500001' THEN '220270' WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '505000' THEN '220250' WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '505001' THEN '220250' WHEN ( SUBSTR ----------------
----------------
----------------
FROM POFG_PURCHASE_ORDERS o102467,en') as C_2,NVL(o102467.AUTHORIZATION_STATUS,'Incomplete') as C_1,ROUND(o102468.ORDERED_Q POFG_PO_DISTRIBUTIONS o102468,_5,ROUND(o102468.DELIVERED_QUANTITY,2)*o102470.ACTUAL_PRICE as C_6,ROUND(o102468.BILLED_ POFG_PO_LINES o102469,RICE as C_7
-------------------
-------------------
-------------------

This ORA-00600 error can be resolved by using "_optimizer_cost_based_transformation" parameter and set it false.

"_optimizer_cost_based_transformation"=false

During 11i upgrade also we had many standard forms failing with different errors and we end up setting this parameter to false in 11i e-Business suite. Although some experts says not to change this parameter because of performance issue and work on the the sql statment but we didn't have any performance issue after making changes to this parameter.

Memory Notification: Library Cache Object loaded into SGA

Memory Notification: Library Cache Object loaded into SGA
Heap size 109275K exceeds notification threshold (51200K)Details in trace file ORCLTEST_ora_19128.trc
KGL object name :EXPLAIN PLAN SET STATEMENT_ID = '117984602' INTO EUL4_US.EUL5_PLAN_TABLE FORSELECT CASE WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) IN ('785500','803500','810000','810100','810500','810510','810520','810530',
'810540','810550','810570','811010','823250','823500','823501','830500',
'831000','831100','831300','831400','840000','840001','840002','840003',
'840050','840070','840100','840120','840150','840200','840250','840300',
'840350','840400','840450','840500','840550','840600','840650','840700',
-----------
-----------

This is a simple warning message for KGL heap size warning threshold as some process is waiting for a long time in finding free memory extents. This warning message can be supress by using _kgl_large_heap_warning_threshold (hidden parameter) and set it to relatively high value in bytes, default value of this parameter is 50M in 10.2.0.4 .0. You may also try setting this parameter to zero to supress this warning.

How to Resolve ORA-00600: [sorsikbeg_1]

2009-08-17T15:02:00.000-07:00

Now we got another ORA-00600 Error in our production database and this time error was ORA-00600: internal error code, arguments: [sorsikbeg_1], [5], [0], [], [], [], []

This error is generally caused by "CONNECT BY PRIOR" in SQL statement. In our trace file we could see the following connect by query:

SELECT COUNT(DISTINCT PAAF.PERSON_ID)
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERSON_TYPES PPT,
PER_PERSON_TYPE_USAGES_F PPTU,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE 1 = 1 AND PAPF.PERSON_ID = PPTU.PERSON_ID
AND PPTU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE = 'EMP'
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID AND
PAST.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND PAAF.ASSIGNMENT_TYPE = 'E'
CONNECT BY PRIOR PAAF.PERSON_ID = PAAF.SUPERVISOR_ID
AND TRUNC(SYSDATE)
BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND
TRUNC(SYSDATE) BETWEEN PPTU.EFFECTIVE_START_DATE AND PPTU.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND
PAAF.EFFECTIVE_END_DATE START WITH 1 = 1 AND PAAF.PERSON_ID = :b1;

In order to resolve this error we temporary used following work around (Oracle Hint) but be careful as this hint can have serious performance issue.

SELECT /*+ NO_CONNECT_BY_FILTERING*/ COUNT(DISTINCT PAAF.PERSON_ID) FROM
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERSON_TYPES PPT, PER_PERSON_TYPE_USAGES_F PPTU, PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE 1 = 1
AND PAPF.PERSON_ID =PPTU.PERSON_ID
AND PPTU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE = 'EMP'
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
AND PAST.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND PAAF.ASSIGNMENT_TYPE = 'E'
CONNECT BY PRIOR
PAAF.PERSON_ID = PAAF.SUPERVISOR_ID
AND TRUNC(SYSDATE)BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE ANDTRUNC(SYSDATE) BETWEEN PPTU.EFFECTIVE_START_DATE AND PPTU.EFFECTIVE_END_DATEAND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE ANDPAAF.EFFECTIVE_END_DATE START WITH 1 = 1
AND PAAF.PERSON_ID = :b1;

another way to resolve this is to set the parameter "_optimizer_connect_by_cost_based" to false in the session itself using
alter session set "_optimizer_connect_by_cost_based"=FALSE by default value of this hidden parameter is TRUE, but one should be careful with hidden parameters and should always get blessing from oracle before using them otherwise you might get unpredictable results.

How to find out Patch Information and Duration in Oracle Applications 11i

2009-08-10T23:52:00.000-07:00

Use following SQL Query to find out Installed Patch Information and duration.

SELECT AAP.PATCH_NAME "Patch Name",
NVL
(DECODE
(MERGED_DRIVER_FLAG,
'Y', AD_PA_VALIDATE_CRITERIASET.GET_CONCAT_MERGEPATCHES
(APD.PATCH_DRIVER_ID), '' ), 'Single Patch' ) "Merged Patches",
APR.START_DATE "Start Date", APR.END_DATE "End Date",
APD.DRIVER_FILE_NAME "Driver File",
APR.PATCH_ACTION_OPTIONS "Patch Options",
DECODE (APR.SERVER_TYPE_ADMIN_FLAG,
'Y', 'Admin,',
NULL
)
|| DECODE (APR.SERVER_TYPE_FORMS_FLAG, 'Y', 'Forms,', NULL)
|| DECODE (APR.SERVER_TYPE_NODE_FLAG, 'Y', 'Node,', NULL)
|| DECODE (APR.SERVER_TYPE_WEB_FLAG, 'Y', 'Web,', NULL)
"Server Type",
APD.PLATFORM "Platform",

SUBSTR (APR.PATCH_TOP, 1, 20) "Patch Top",
AD_CORE.GET_FORMATTED_ELAPSED_TIME
((APR.END_DATE - APR.START_DATE), 2 ) "Elapsed Time"
FROM AD_PATCH_RUNS APR,
AD_PATCH_DRIVER_LANGS APDL,
AD_PATCH_DRIVERS APD,
AD_APPLIED_PATCHES AAP
WHERE APD.PATCH_DRIVER_ID = APR.PATCH_DRIVER_ID
AND APD.PATCH_DRIVER_ID = APDL.PATCH_DRIVER_ID
AND APD.APPLIED_PATCH_ID = AAP.APPLIED_PATCH_ID
AND AAP.PATCH_NAME = &PATCH_NAME
ORDER BY END_DATE DESC;

Use following SQL query to find out Patch Job Details and Timings.

SELECT PROGRAM "Program", AAP.PATCH_NAME "Patch Name",
APRT.JOB_NAME "Job Name", APRT.PHASE_NAME "Phase",
APRT.START_TIME "Job Start Time",
APRT.END_TIME "Job End Time",
ROUND ((NVL
(APRT.END_TIME, SYSDATE) - APRT.START_TIME)
* 60 * 24, 2) "Elapsed Time"
FROM AD_PROGRAM_RUN_TASK_JOBS APRT,
AD_PATCH_RUNS APR,
AD_PATCH_DRIVERS APD,
AD_APPLIED_PATCHES AAP
WHERE APRT.SESSION_ID = APR.SESSION_ID
AND APR.PATCH_DRIVER_ID = APD.PATCH_DRIVER_ID
AND APD.APPLIED_PATCH_ID = AAP.APPLIED_PATCH_ID
AND AAP.PATCH_NAME = &PATCH_NAME
ORDER BY "Elapsed Time" DESC

How to Find Out Pending Concurrent Requests in Oracle Applications 11i

2009-08-07T18:58:00.000-07:00

   Use following script to monitor Pending concurrent requests status in the oracle applications 11i. You can schedule this using crontab or use only sql script to get the results. This script will help you to understand pending concurrent requests status and to resolve any issue with pending concurrent requests.

------------Start of Script-------------------------------------

#!/bin/sh
# Long Running Concurrent Requests
#

SPOOL_FILE=/home/oracle/scripts/pending_conc.html
FINAL_FILE=/home/oracle/scripts/Pending_Concurrent.html

echo "Content-Type: text/html" >> ${FINAL_FILE}
sqlplus -s apps/apps EOF
set echo off
set pagesize 120
set markup html on spool on
spool ${SPOOL_FILE}
TTITLE CENTER 'Pending Concurrent Requests '

SELECT DISTINCT far.request_id, SUBSTR (program, 1, 30), far.user_name,
   far.phase_code, far.status_code, phase, status,
   (CASE
   WHEN far.phase_code = 'P' AND far.hold_flag = 'Y'
   THEN 'Job is on Hold by user'
   WHEN far.phase_code = 'P'
   AND (far.status_code = 'I' OR far.status_code = 'Q')
   AND far.requested_start_date > SYSDATE
   THEN 'Job is scheduled to run at '
   || TO_CHAR (far.requested_start_date,
   'DD-MON-RR HH24:MI:SS'
   )
   WHEN far.phase_code = 'P'
   AND (far.status_code = 'I' OR far.status_code = 'Q')
   AND fcp.queue_control_flag = 'Y'
   THEN 'ICM will run ths request on its next sleep cycle'
   WHEN far.phase_code = 'P' AND far.status_code = 'P'
   THEN 'Scheduled to be run by the Advanced Scheduler'
   WHEN far.queue_method_code NOT IN ('I', 'B')
   THEN 'Bad queue_method_code of: '
   || far.queue_method_code
   WHEN far.run_alone_flag = 'Y'
   THEN 'Waiting on a run alone request'
   WHEN far.queue_method_code = 'B'
   AND far.status_code = 'Q'
   AND EXISTS (
   SELECT 1
   FROM fnd_amp_requests_v farv
   WHERE phase_code = 'P'
   AND program_application_id =
   fcps.to_run_application_id
   AND concurrent_program_id =
   fcps.to_run_concurrent_program_id)
   THEN 'Incompatible request '
   || (SELECT DISTINCT farv.request_id
   || ': '
   || farv.program
   || ' is Ruuning by : '
   || farv.user_name
   FROM fnd_amp_requests_v farv,fnd_concurrent_program_serial fcps1
   WHERE phase_code = 'R'
   AND program_application_id =fcps1.running_application_id
   AND concurrent_program_id =fcps1.running_concurrent_program_id
   AND fcps.to_run_application_id=fcps1.to_run_application_id
   AND fcps.to_run_concurrent_program_id=fcps1.to_run_concurrent_program_id)
   WHEN fcp.enabled_flag = 'N'
   THEN 'Concurrent program is disabled'
   WHEN far.queue_method_code = 'I' AND far.status_code = 'Q'
   THEN 'This Standby request might not run'
   WHEN far.queue_method_code = 'I' AND far.status_code = 'I'
   THEN 'Waiting for next available '
   || fcqt.user_concurrent_queue_name
   || ' process to run the job. Estimate Wait time '
   || fcq.sleep_seconds
   || ' Seconds'
   WHEN far.queue_method_code = 'I'
   AND far.status_code IN ('A', 'Z')
   THEN 'Waiting for Parent Request: '
   || NVL (far.parent_request_id,
   'Could not locate Parent Request ID'
   )
   WHEN far.queue_method_code = 'B' AND far.status_code = 'Q'
   THEN 'Waiting on the Conflict Resolution Manager'
   WHEN far.queue_method_code = 'B' AND far.status_code = 'I'
   THEN 'Waiting for next available '
   || fcqt.user_concurrent_queue_name
   || ' process to run the job. Estimate Wait time '
   || fcq.sleep_seconds
   || ' Seconds'
   WHEN far.phase_code = 'P' AND far.single_thread_flag = 'Y'
   THEN 'Single-threaded request. Waiting on other requests for this user.'
   WHEN far.phase_code = 'P' AND far.request_limit = 'Y'
   THEN 'Concurrent: Active Request Limit is set. Waiting on other requests for this user.'
   END
   ) reason
   FROM fnd_amp_requests_v far,
   fnd_concurrent_programs fcp,
   fnd_conflicts_domain fcd,
   fnd_concurrent_program_serial fcps,
   fnd_concurrent_queues fcq,
   fnd_concurrent_queue_content fcqc,
   fnd_concurrent_queues_tl fcqt
   WHERE far.phase_code = 'P'
   AND far.concurrent_program_id = fcp.concurrent_program_id
   AND fcd.cd_id = far.cd_id
   AND fcps.running_application_id(+) = far.program_application_id
   AND fcps.running_concurrent_program_id(+) = far.concurrent_program_id
   AND far.program_application_id = fcps.to_run_application_id(+)
   AND far.concurrent_program_id = fcps.to_run_concurrent_program_id(+)
   AND far.concurrent_program_id = fcqc.type_id(+)
   AND far.program_application_id = fcqc.type_application_id(+)
   AND fcq.concurrent_queue_id(+) = fcqc.concurrent_queue_id
   AND fcq.application_id(+) = fcqc.queue_application_id
   AND fcqt.concurrent_queue_id(+) = fcq.concurrent_queue_id
   AND fcqt.application_id(+) = fcq.application_id
   ORDER BY far.request_id DESC
/
spool off
set markup html off spool off
EOF

if [ `grep -c 'no rows selected' ${SPOOL_FILE}` -eq 1 ]
then
rm ${FINAL_FILE}
rm ${SPOOL_FILE}
exit 0
fi
cat ${SPOOL_FILE} | grep -v 'rows selected' >> ${FINAL_FILE}
(echo "Importance: High"; echo "Subject: ORCL: Pending Concurrent Requests"; cat ${FINAL_FILE}) | /usr/sbin/sendmail -F ORCL
test@testorcl.com

rm ${FINAL_FILE}
rm ${SPOOL_FILE}

-------------End of Script-------------------------------------

Hourly Concurrent Managers/Requests Performance Report in Oracle Applications 11i

2009-08-07T13:42:00.001-07:00

In order to fine tune your Concurrent Managers performance you may need to collect hourly data for Concurrent Managers and Concurrent Requests. Please use following SQL report to generate this data.

COLUMN "Queue Time" format a15
COLUMN "Concurrent Manager" format a20
COLUMN "Program" format a60 heading "Concurrent Program"
set linesize 2000
set echo off
BREAK on "Queue Time" SKIP 1 ON "Concurrent Manager"
SELECT
TO_CHAR (actual_start_date, 'DD-MON-YY : HH24') "Queue Time",
fcqtl.user_concurrent_queue_name "Concurrent Manager",
fcptl.user_concurrent_program_name "Program",
ROUND
( SUM (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Total Duration (min)",
ROUND
( AVG (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Avg Duration (min)",
ROUND
( MIN (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Min Duration (min)",
ROUND
( MAX (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Max Duration (min)",
COUNT (*) "Times Run", fcq.target_processes "Total Processes"
FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl,
fnd_concurrent_processes fcproc,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_queues fcq,
fnd_concurrent_requests fcr
WHERE fcr.phase_code = 'C'
AND fcr.actual_completion_date IS NOT NULL
AND actual_start_date IS NOT NULL
AND fcq.concurrent_queue_id = fcproc.concurrent_queue_id
AND fcq.application_id = fcproc.queue_application_id
AND fcq.manager_type = 1
AND fcr.controlling_manager = fcproc.concurrent_process_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcp.concurrent_program_name NOT IN
('ACTIVATE', 'ABORT', 'DEACTIVATE', 'VERIFY')
AND fcr.concurrent_program_id = fcptl.concurrent_program_id
AND fcr.program_application_id = fcptl.application_id
AND fcptl.LANGUAGE = 'US'
AND fcproc.queue_application_id = fcqtl.application_id
AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
AND fcqtl.LANGUAGE = 'US'
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YY : HH24'),
fcqtl.user_concurrent_queue_name,
fcptl.user_concurrent_program_name,
fcq.target_processes
ORDER BY "Queue Time" ASC,
"Concurrent Manager" ASC,
"Times Run" DESC,
"Max Duration (min)" DESC,
"Total Duration (min)" DESC

Generate Graph for you presentation using MS Excel

How to List Responsibility And Request Group in Oracle Applications 11i

2009-08-06T16:56:00.000-07:00

Use following SQL Report to list responsibilities and attached request groups. In oracle apps Users submit concurrent request based on the Request group assigned to thier responsibility.

COLUMN responsibility_name format a40 heading "Responsibility Name"
COLUMN request_group_name format a40 heading "Request Group Name"
COLUMN description format a60 heading "Request Group Detail"
BREAK on responsibility_name SKIP 1 ON request_group_name SKIP 1
SET feedback off
SET linesize 1000
SELECT 'Report Date: ' SYSDATE FROM DUAL;
SELECT frv.responsibility_name,
frg.request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Use Following SQL report to list Responsibilities, Request Group and Concurrent requests for each Request Group.

COLUMN responsibility_name format a40 heading "Responsibility Name"
COLUMN request_group_name format a40 heading "Request Group Name"
COLUMN DESCR format a60 heading "Request Group Detail"
COLUMN user_concurrent_program_name format a60 heading "Concurrent Program"
COLUMN Description format a60 heading "Concurrent Program Detail"
BREAK on responsibility_name SKIP 1 ON request_group_name SKIP 1 on DESCR
SET feedback off
SET linesize 1000
SELECT 'Report Date: ' SYSDATE FROM DUAL;
SELECT frv.responsibility_name, frg.request_group_name,
frg.description descr, fcpv.user_concurrent_program_name,
fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name, frg.request_group_name, frg.description

How to List Reponsibilities for an User in Oracle Applications 11i

2009-08-06T16:42:00.000-07:00

You can use following SQL Statments to list responsibities assigned to all users or to a particuler user instead of going to System Administrator Screen.

For All Users

SELECT UNIQUE fu.user_id "User ID",
fu.user_name "User Name",
frt.responsibility_name "Responsibility Name",
frv.description "Responsibility Detail",
fat.application_name "Application Name"
FROM fnd_user fu,
fnd_user_resp_groups furg,
fnd_application_tl fat,
fnd_responsibility_tl frt,
fnd_responsibility_vl frv
WHERE furg.user_id(+) = fu.user_id
AND furg.responsibility_application_id = fat.application_id
AND fat.application_id = frt.application_id
AND furg.responsibility_id = frt.responsibility_id
AND furg.responsibility_application_id = frv.application_id
AND furg.responsibility_id = frv.responsibility_id
ORDER BY fu.user_name,
fat.application_name,
frt.responsibility_name

For particuler User
SELECT UNIQUE fu.user_id "User ID",
fu.user_name "User Name",
frt.responsibility_name "Responsibility Name",
frv.description "Responsibility Detail",
fat.application_name "Application Name"
FROM fnd_user fu,
fnd_user_resp_groups furg,
fnd_application_tl fat,
fnd_responsibility_tl frt,
fnd_responsibility_vl frv
WHERE furg.user_id(+) = fu.user_id
AND furg.responsibility_application_id = fat.application_id
AND fat.application_id = frt.application_id
AND furg.responsibility_id = frt.responsibility_id
AND furg.responsibility_application_id = frv.application_id
AND furg.responsibility_id = frv.responsibility_id
And fu.USER_NAME = &Username

How to Resolve ORA-04061: Existing State Of Package Body has been invalidated

2009-08-05T18:23:00.000-07:00

In Oracle application Workflow related errors are very common specially related to PO Approval or Requisition approval. Recently we got ORA-04061: Existing State Of Package Body has been invalidated for APPS.PO_GA_PVT and APPS.PO_WF_PO_NOTIFICATION. All PO related Workflow notifications were erroring out and users were not able to approve PO from emails and from the system.

Error Message:

[WF_ERROR] ERROR_MESSAGE=3835: Error '-20002 - ORA-20002: 2018: Unable to generate the notification XML. Caused by: 2020: Error when getting notification content. Caused by: ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" has been invalidated ORA-04065: not executed, altered or dropped package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" ORA-06508: PL/SQL: could not find program unit being called: "APPS.XXUS_PO_WF_PO_NOTIFICATION" wf_notification.GetAttrDoc2(3618679, PO_LINES_DE' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK= WF_MAIL.GetLOBMessage3(3618679, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" has been invalidated ORA-04065: not executed, altered or dropped package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" ORA-06508: PL/SQL: could not find program unit being called: "APPS.XXUS_PO_WF_PO_NOTIFICATION" wf_notification.GetAttrDoc2(3618679, PO_LINES_DETAILS, text/plain) Wf_Notification.GetAttrDoc(3618679, PO_LINES_DETAILS, text/plain) Wf_Notification.GetText(3618679, text/plain) Wf_Notification.GetBody(3618679, text/plain) WF_NOTIFICATION.GetFullBody(nid => 3618679, disptype => text/plain) WF_MAIL.GetLOBMessage3(nid => 3618679, r_ntf_pref => MAILATTH), Step -> Getting text/plain body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 3618679) WF_XML.Generate(oracle.apps.wf.notification.send, 3618679) WF_XML.Generate(oracle.apps.wf.notification.send, 3618679) Wf_Event.setMessage(oracle.apps.wf.notification.send, 3618679, WF_XML.Generate) Wf_Event.dispatch_internal()

In the database both the package and body were in valid state. We completed following steps as per metalink note to resolve this issue but couldn't resolve this issue.

1. Shutdown the Notification Mailer and agent listener
2. Recompile the APPS schema through ADADMIN (even when there were no invalids)
3. Start the Notification Mailer and agent listener

Now to resolve this issue we need to do following steps in addition to the above steps.

1. Shutdown the Notification Mailer and agent listener
2. Recompile the APPS schema through ADADMIN
3. connect / as sysdba

SQL>alter system set aq_tm_processes=0 scope=memory;

You need to wait until the q00* and qmnc* processeses are no longer running. You can check via

ps -ef grep q00 and ps -ef grep qmn

SQL> alter system flush shared_pool;

SQL> alter system set aq_tm_processes=1 scope=memory;

4. Start the Notification Mailer and agent listener

Once we did all the above steps users were able to receive mails related to PO and Requisition and they were also able to approve PO from Workflow Notification Emails.

How to find out blocking locks & sessions.

2009-08-03T14:15:00.001-07:00

There are many ways to find out blocking session in Oracle or blocking locks in Oracle.You can use following SQL query to find out Blocking locks, User ID, Concurrent Request Name and Object name in oracle applications environment.

SELECT s.inst_id,
NVL (s.username, 'Internal') "Database User",
m.SID,
s.serial#,
p.spid "DB OS Process",
m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server",
s.process "Apps OS process",
m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;

You can also schedule using a crontab entry. Use following shell script and schedule it using crontab. This sheel script will create a html file and mail it to the users.

#------------------Start of script----------------------- #

#!/bin/sh
# Check Blocking Locks
#
. /u01/oracle/ORCL/orcldb/10.2.0/ORCL1_orcl-1.env
echo "Content-Type: text/html" >> Blocking_Session.html

sqlplus -s apps/apps EOF
SELECT s.inst_id, NVL (s.username, 'Internal') "Database User", m.SID,
s.serial#, p.spid "DB OS Process", m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server", s.process "Apps OS process", m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name", SQL.sql_text "Statement"
FROM gv\$session s,
gv\$lock m,
gv\$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv\$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
spool off
set markup html off spool off
EOF

cat blocking_session.html >> Blocking_Session.html
(echo "Importance: High"; echo "Subject: ORCL: Blocking Session Locks"; cat Blocking_Session.html)
| /usr/sbin/sendmail -F Oracle abc@test.com

rm Blocking_Session.html
rm blocking_session.html

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

Use following SQL to get more detail data.

SELECT s.inst_id, NVL (s.username, 'Internal') database_user, s.SID,
   s.event, s.p1, s.serial#, p.spid, m.TYPE,
   DECODE (m.lmode,
   0, 'None',
   1, 'Null',
   2, 'Row Share',
   3, 'Row Excl.',
   4, 'Share',
   5, 'S/Row Excl.',
   6, 'Exclusive',
   lmode, LTRIM (TO_CHAR (lmode, '990'))
   ) lock_type,
   m.TYPE
   || ' - '
   || DECODE (m.TYPE,
   'BL', 'Buffer hash table instance lock',
   'CF', ' Control file schema global enqueue lock',
   'CI', 'Cross-instance function invocation instance lock',
   'CS', 'Control file schema global enqueue lock',
   'CU', 'Cursor bind lock',
   'DF', 'Data file instance lock',
   'DL', 'Direct loader parallel index create',
   'DM', 'Mount/startup db primary/secondary instance lock',
   'DR', 'Distributed recovery process lock',
   'DX', 'Distributed transaction entry lock',
   'FI', 'SGA open-file information lock',
   'FS', 'File set lock',
   'HW', 'Space management on a specific segment lock',
   'IN', 'Instance number lock',
   'IR', 'Instance recovery serialization global enqueue lock',
   'IS', 'Instance state lock',
   'IV', 'Library cache invalidation instance lock',
   'JQ', 'Job queue lock',
   'KK', 'Thread kick lock',
   'MB', 'Master buffer hash table instance lock',
   'MM', 'Mount definition gloabal enqueue lock',
   'MR', 'Media recovery lock',
   'PF', 'Password file lock',
   'PI', 'Parallel operation lock',
   'PR', 'Process startup lock',
   'PS', 'Parallel operation lock',
   'RE', 'USE_ROW_ENQUEUE enforcement lock',
   'RT', 'Redo thread global enqueue lock',
   'RW', 'Row wait enqueue lock',
   'SC', 'System commit number instance lock',
   'SH', 'System commit high water mark enqueue lock',
   'SM', 'SMON lock',
   'SN', 'Sequence number instance lock',
   'SQ', 'Sequence number enqueue lock',
   'SS', 'Sort segment lock',
   'ST', 'Space transaction enqueue lock',
   'SV', 'Sequence number value lock',
   'TA', 'Generic enqueue lock',
   'TD', 'DDL enqueue lock',
   'TE', 'Extend-segment enqueue lock',
   'TM', 'DML enqueue lock',
   'TO', 'Temporary Table Object Enqueue',
   'TT', 'Temporary table enqueue lock',
   'TX', 'Transaction enqueue lock',
   'UL', 'User supplied lock',
   'UN', 'User name lock',
   'US', 'Undo segment DDL lock',
   'WL', 'Being-written redo log instance lock',
   'WS', 'Write-atomic-log-switch global enqueue lock',
   'TS', DECODE (m.id2,
   0, 'Temporary segment enqueue lock (ID2=0)',
   'New block allocation enqueue lock (ID2=1)'
   ),
   'LA', 'Library cache lock instance lock (A=namespace)',
   'LB', 'Library cache lock instance lock (B=namespace)',
   'LC', 'Library cache lock instance lock (C=namespace)',
   'LD', 'Library cache lock instance lock (D=namespace)',
   'LE', 'Library cache lock instance lock (E=namespace)',
   'LF', 'Library cache lock instance lock (F=namespace)',
   'LG', 'Library cache lock instance lock (G=namespace)',
   'LH', 'Library cache lock instance lock (H=namespace)',
   'LI', 'Library cache lock instance lock (I=namespace)',
   'LJ', 'Library cache lock instance lock (J=namespace)',
   'LK', 'Library cache lock instance lock (K=namespace)',
   'LL', 'Library cache lock instance lock (L=namespace)',
   'LM', 'Library cache lock instance lock (M=namespace)',
   'LN', 'Library cache lock instance lock (N=namespace)',
   'LO', 'Library cache lock instance lock (O=namespace)',
   'LP', 'Library cache lock instance lock (P=namespace)',
   'LS', 'Log start/log switch enqueue lock',
   'PA', 'Library cache pin instance lock (A=namespace)',
   'PB', 'Library cache pin instance lock (B=namespace)',
   'PC', 'Library cache pin instance lock (C=namespace)',
   'PD', 'Library cache pin instance lock (D=namespace)',
   'PE', 'Library cache pin instance lock (E=namespace)',
   'PF', 'Library cache pin instance lock (F=namespace)',
   'PG', 'Library cache pin instance lock (G=namespace)',
   'PH', 'Library cache pin instance lock (H=namespace)',
   'PI', 'Library cache pin instance lock (I=namespace)',
   'PJ', 'Library cache pin instance lock (J=namespace)',
   'PL', 'Library cache pin instance lock (K=namespace)',
   'PK', 'Library cache pin instance lock (L=namespace)',
   'PM', 'Library cache pin instance lock (M=namespace)',
   'PN', 'Library cache pin instance lock (N=namespace)',
   'PO', 'Library cache pin instance lock (O=namespace)',
   'PP', 'Library cache pin instance lock (P=namespace)',
   'PQ', 'Library cache pin instance lock (Q=namespace)',
   'PR', 'Library cache pin instance lock (R=namespace)',
   'PS', 'Library cache pin instance lock (S=namespace)',
   'PT', 'Library cache pin instance lock (T=namespace)',
   'PU', 'Library cache pin instance lock (U=namespace)',
   'PV', 'Library cache pin instance lock (V=namespace)',
   'PW', 'Library cache pin instance lock (W=namespace)',
   'PX', 'Library cache pin instance lock (X=namespace)',
   'PY', 'Library cache pin instance lock (Y=namespace)',
   'PZ', 'Library cache pin instance lock (Z=namespace)',
   'QA', 'Row cache instance lock (A=cache)',
   'QB', 'Row cache instance lock (B=cache)',
   'QC', 'Row cache instance lock (C=cache)',
   'QD', 'Row cache instance lock (D=cache)',
   'QE', 'Row cache instance lock (E=cache)',
   'QF', 'Row cache instance lock (F=cache)',
   'QG', 'Row cache instance lock (G=cache)',
   'QH', 'Row cache instance lock (H=cache)',
   'QI', 'Row cache instance lock (I=cache)',
   'QJ', 'Row cache instance lock (J=cache)',
   'QL', 'Row cache instance lock (K=cache)',
   'QK', 'Row cache instance lock (L=cache)',
   'QM', 'Row cache instance lock (M=cache)',
   'QN', 'Row cache instance lock (N=cache)',
   'QO', 'Row cache instance lock (O=cache)',
   'QP', 'Row cache instance lock (P=cache)',
   'QQ', 'Row cache instance lock (Q=cache)',
   'QR', 'Row cache instance lock (R=cache)',
   'QS', 'Row cache instance lock (S=cache)',
   'QT', 'Row cache instance lock (T=cache)',
   'QU', 'Row cache instance lock (U=cache)',
   'QV', 'Row cache instance lock (V=cache)',
   'QW', 'Row cache instance lock (W=cache)',
   'QX', 'Row cache instance lock (X=cache)',
   'QY', 'Row cache instance lock (Y=cache)',
   'QZ', 'Row cache instance lock (Z=cache)',
   '????'
   ) lock_type_detail,
   DECODE (m.request,
   0, 'None',
   1, 'Null',
   2, 'Row Share',
   3, 'Row Excl.',
   4, 'Share',
   5, 'S/Row Excl.',
   6, 'Exclusive',
   request, LTRIM (TO_CHAR (m.request, '990'))
   ) lock_request,
   DECODE (command,
   0, 'BACKGROUND',
   1, 'Create Table',
   2, 'INSERT',
   3, 'SELECT',
   4, 'CREATE CLUSTER',
   5, 'ALTER CLUSTER',
   6, 'UPDATE',
   7, 'DELETE',
   8, 'DROP',
   9, 'CREATE INDEX',
   10, 'DROP INDEX',
   11, 'ALTER INDEX',
   12, 'DROP TABLE',
   13, 'CREATE SEQUENCE',
   14, 'ALTER SEQUENCE',
   15, 'ALTER TABLE',
   16, 'DROP SEQUENCE',
   17, 'GRANT',
   18, 'REVOKE',
   19, 'CREATE SYNONYM',
   20, 'DROP SYNONYM',
   21, 'CREATE VIEW',
   22, 'DROP VIEW',
   23, 'VALIDATE INDEX',
   24, 'CREATE PROCEDURE',
   25, 'ALTER PROCEDURE',
   26, 'LOCK TABLE',
   27, 'NO OPERATION',
   28, 'RENAME',
   29, 'COMMENT',
   30, 'AUDIT',
   31, 'NOAUDIT',
   32, 'CREATE EXTERNAL DATABASE',
   33, 'DROP EXTERNAL DATABASE',
   34, 'CREATE DATABASE',
   35, 'ALTER DATABASE',
   36, 'CREATE ROLLBACK SEGMENT',
   37, 'ALTER ROLLBACK SEGMENT',
   38, 'DROP ROLLBACK SEGMENT',
   39, 'CREATE TABLESPACE',
   40, 'ALTER TABLESPACE',
   41, 'DROP TABLESPACE',
   42, 'ALTER SESSION',
   43, 'ALTER USER',
   44, 'COMMIT',
   45, 'ROLLBACK',
   46, 'SAVEPOINT',
   47, 'PL/SQL EXECUTE',
   48, 'SET TRANSACTION',
   49, 'ALTER SYSTEM SWITCH LOG',
   50, 'EXPLAIN',
   51, 'CREATE USER',
   52, 'CREATE ROLE',
   53, 'DROP USER',
   54, 'DROP ROLE',
   55, 'SET ROLE',
   56, 'CREATE SCHEMA',
   57, 'CREATE CONTROL FILE',
   58, 'ALTER TRACING',
   59, 'CREATE TRIGGER',
   60, 'ALTER TRIGGER',
   61, 'DROP TRIGGER',
   62, 'ANALYZE TABLE',
   63, 'ANALYZE INDEX',
   64, 'ANALYZE CLUSTER',
   65, 'CREATE PROFILE',
   66, 'DROP PROFILE',
   67, 'ALTER PROFILE',
   68, 'DROP PROCEDURE',
   69, 'DROP PROCEDURE',
   70, 'ALTER RESOURCE COST',
   71, 'CREATE SNAPSHOT LOG',
   72, 'ALTER SNAPSHOT LOG',
   73, 'DROP SNAPSHOT LOG',
   74, 'CREATE SNAPSHOT',
   75, 'ALTER SNAPSHOT',
   76, 'DROP SNAPSHOT',
   79, 'ALTER ROLE',
   85, 'TRUNCATE TABLE',
   86, 'TRUNCATE CLUSTER',
   87, '-',
   88, 'ALTER VIEW',
   89, '-',
   90, '-',
   91, 'CREATE FUNCTION',
   92, 'ALTER FUNCTION',
   93, 'DROP FUNCTION',
   94, 'CREATE PACKAGE',
   95, 'ALTER PACKAGE',
   96, 'DROP PACKAGE',
   97, 'CREATE PACKAGE BODY',
   98, 'ALTER PACKAGE BODY',
   99, 'DROP PACKAGE BODY',
   command || ' - ???'
   ) command,
   (CASE
   WHEN m.TYPE = 'UL'
   THEN 'None '
   ELSE DECODE (command,
   0, 'None',
   DECODE (m.id2,
   0, dusr.username
   || '.'
   || SUBSTR (dobj.NAME, 1, 30),
   'Rollback Segment'
   )
   )
   END
   ) OBJECT,
   s.machine, s.process, m.ctime, s.program, SQL.sql_text, s.sql_id,
   (SELECT 'select * from '
   || owner
   || '.'
   || object_name
   || ' where rowid=dbms_rowid.rowid_create( 1, '
   || row_wait_obj#
   || ','
   || row_wait_file#
   || ','
   || row_wait_block#
   || ','
   || row_wait_row#
   || ' );'
   FROM dba_objects db
   WHERE db.object_id = s.row_wait_obj# AND db.object_type = 'TABLE') row_wait
   FROM gv$session s,
   gv$lock m,
   gv$process p,
   gv$sqlarea SQL,
   dba_users dusr,
   SYS.obj$ dobj
   WHERE m.id1 IN (SELECT il.id1
   FROM gv$lock il
   WHERE il.request <> 0)
   AND m.SID = s.SID
   AND s.paddr = p.addr
   AND s.inst_id = p.inst_id
   AND SQL.inst_id(+) = s.inst_id
   AND SQL.address(+) = s.sql_address
   AND SQL.hash_value(+) = s.sql_hash_value
   AND m.inst_id = s.inst_id
   AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
   AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;

How to resolve ORA-00600: [ksnpost:ksnigb]

2009-07-31T16:17:00.000-07:00

Today i got this error ORA-00600: internal error code, arguments: [ksnpost:ksnigb], [], [], [], [], [], [], [] in one of our shakedown instance (pre go live instance).

In alert log there were many errors like

Errors in file /fsd1/oracle/OROD/oroddb/10.2.0/admin/OROD_admin/udump/orod_ora_6911.trc: ORA-00600: internal error code, arguments: [ksnpost:ksnigb], [], [], [], [], [], [], []
ORA-00609: could not attach to incoming connection
ORA-06403: Unable to allocate memory.
Fri Jul 31 13:55:37 2009
Incremental checkpoint up to RBA [0x1a.21bf9.0], current log tail at RBA [0x1a.234ae.0]
Fri Jul 31 14:00:23 2009
Process startup failed, error stack:
Fri Jul 31 14:00:23 2009
Errors in file /fsd1/oracle/OROD/oroddb/10.2.0/admin/OROD_admin/udump/orod_psp0_18212.trc: ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn5
ORA-27303: additional information: skgpspawn5
Fri Jul 31 14:00:24 2009

trace file orod_psp0_18212.trc had error messages

Process startup failed, error stack:
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn5
ORA-27303: additional information: skgpspawn5
*** 2009-07-31 14:01:24.304

In orod.log (listener log file) there were many error messages like

TNS-12518: TNS:listener could not hand off client connection
TNS-12549: TNS:operating system resource quota exceeded
TNS-12560: TNS:protocol adapter error
TNS-00519: Operating system resource quota exceeded
HPUX Error: 12: Not enough space

as you can see above messages clearly states that system is running out of resources. OS was totally out of physical memory as per swapinfo -tam command

problem was we had only 3GB memory allocated to SGA but processes parameter was set to 1000 and this cause an issue in the instance and to solve this we had to reduce Processes parameter to lower value around 400 to make it work.

How to Find out Product Installations and Patch Set level in Oracle Applications 11i

2009-07-24T16:44:00.001-07:00

Use Following Query to find out Product Installation

SELECT application_name "Application Name",
SUBSTR (application_short_name, 1, 10) "Short Name",
RPAD (DECODE (fpi.status, 'I', 'Installed', 'S', 'Shared Install', 'N', 'Not Installed' ), 14, ' ' ) "Install Status", SUBSTR (patch_level, 1, 12) "Patch Level", fa.BASEPATH "Basepath"
FROM fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fat
WHERE fa.application_id = fpi.application_id
AND fa.application_id = fat.application_id
ORDER BY fpi.application_id;

How to Find out File version in Oracle Applications 11i E-Business Suite

2009-07-24T13:55:00.000-07:00

Some times you need to find out the file version in oracle application for some TAR or for some development work. You can use SQL command to find out file version, package version or you can use OS commands to find out the version.

Use following SQL command to find out file version, patch number.

SELECT af.app_short_name "Application", af.filename "File Name",
afv.VERSION "File Version",
NVL (aap.patch_name, 'Default Installation') "Patch Number",
'$' || fa.basepath || '/' || af.subdir "Location"
FROM apps.ad_files af,
apps.ad_patch_run_bug_actions apa,
apps.ad_patch_run_bugs aprb,
apps.ad_patch_runs apr,
apps.ad_patch_drivers apd,
apps.ad_applied_patches aap,
apps.ad_file_versions afv,
apps.fnd_application fa
WHERE af.filename = 'IGSEN010.fmb'
AND af.file_id = apa.file_id(+)
AND apa.common_action_id(+) = 4042
AND apa.patch_run_bug_id = aprb.patch_run_bug_id(+)
AND aprb.patch_run_id = apr.patch_run_id(+)
AND apr.patch_driver_id = apd.patch_driver_id(+)
AND apd.applied_patch_id = aap.applied_patch_id(+)
AND af.file_id = afv.file_id
AND af.app_short_name = fa.application_short_name
GROUP BY aap.patch_name,
af.filename,
afv.VERSION,
af.app_short_name,
af.file_id,
af.subdir,
fa.basepath

At OS level go to $Product_TOP/patch/115/Object_Type

Example
$pwd
/u01/oracle/TESTORCL/testorclappl/igs/11.5.0/forms/US

$ident IGSEN010.fmx | grep IGSEN010.fmb | grep Header
$Header: IGSEN010.fmb 115.24 2003/10/06 06:32 svanukur ship

If you know DB object name you can use following SQL Statement to find out Package,Procedure or Function version.

select text from dba_source where name='FND_OAM_DSCRAM_DMLS_PKG' and line=2

Useful Oracle Apps DBA related SQL Scripts

2009-07-14T12:54:00.000-07:00

How to find out blocking locks,sessions.

SELECT s.inst_id, NVL (s.username, 'Internal') "Database User", m.SID,
s.serial#, p.spid "DB OS Process", m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username '.' SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment' ) ) "Object",
s.machine "Application Server", s.process "Apps OS process", m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session' ) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name", SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID

How to find out long running Concurrent Requests.

SELECT gv.inst_id, gv.SID, fcr.request_id, fcr.program, fcr.phase,
fcr.status, fcr.actual_start_date "start",
fcr.actual_completion_date "end",
ROUND ( ( NVL (fcr.actual_completion_date, SYSDATE) - fcr.actual_start_date )
* 60 * 24, 2 ) TIME, fcqtl.user_concurrent_queue_name "Concurrent Manager", fcr.user_name, fcr.argument_text text, fcr.concurrent_program_id, gvw.event
FROM apps.fnd_amp_requests_v fcr,
gv$session gv,
gv$session_wait gvw,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_processes fcproc
WHERE phase_code = 'R'
AND fcr.oracle_session_id = gv.audsid(+)
AND gv.SID = gvw.SID(+)
AND gv.inst_id = gvw.inst_id(+)
AND fcr.controlling_manager = fcproc.concurrent_process_id
AND fcproc.queue_application_id = fcqtl.application_id
AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
AND fcqtl.LANGUAGE = 'US'
ORDER BY 9 DESC

How to find out Concurrent Manager Process SID and SPID

SELECT fcq.concurrent_queue_name "Concurrent manager", fcp.os_process_id "Manager PID", LOWER (SUBSTR (fcp.node_name, 1)) "CM Node", ses.SID, proc.spid, SUBSTR (ins.host_name, 1) "DB Node", ins.instance_name "Instance"
FROM apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_processes fcp,
gv$process proc,
gv$session ses,
gv$instance ins
WHERE fcp.process_status_code = 'A'
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcp.oracle_process_id = proc.pid
AND ses.paddr = proc.addr
AND proc.inst_id = ses.inst_id
AND ins.instance_number = ses.inst_id
AND ins.instance_number = proc.inst_id
AND fcq.instance_number = ins.instance_number
AND fcp.instance_number = ins.instance_number
ORDER BY fcq.concurrent_queue_name

How to resolve ORA-00600: [qertbFetchByRowID]

2009-07-08T13:44:00.000-07:00

In this post i am going to discuss how to resolve ORA-00600: internal error code, arguments: [qertbFetchByRowID]
ORA-00600: internal error code, arguments: [qertbFetchByRowID] error represents that an index and a table entries are out of sync or underlying index is corrupt.

In this case just check the trace file for current SQL statement for that session. Once we get the SQL statement and table name from the session trace file we can easily find out the index name as shown in this example.

Try to validate the table structure using command.

SQL> analyze table gl.gl_interface validate structure cascade;
analyze table gl.gl_interface validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

Check the trace file create by above statement in udump folder and search for following text

Table/Index row count mismatch
table 48068 : index 239744, 191029
Index root = tsn: 4 rdba: 0x0d00438b

In above example rdba value is in HEXADECIMAL, Convert rdba: 0x0d00438b to decimal value like 0x0d00438b is equivalent to 218121099.

Now use statement given below to find out Header_file and Header_block.

SQL> select dbms_utility.data_block_address_file(218121099) "Rfile#"
,dbms_utility.data_block_address_block(218121099) "Block#"
from dual;

Rfile# Block#
---------- ----------
52 17291

Once you get the Header_file and Header_Block use the SQL statement to find out the segment name and segment type.

SQL> select owner, segment_name, segment_type
from dba_segments
where header_file = 52
and header_block = 17291;

OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------------------------------------------
GL GL_INTERFACE_N1 INDEX

Once you get the index name, you can rebuild index or drop it and recreate it.

SQL> drop index GL.GL_INTERFACE_N1;

CREATE INDEX GL.GL_INTERFACE_N1 ON GL.GL_INTERFACE
(USER_JE_SOURCE_NAME, SET_OF_BOOKS_ID, GROUP_ID)
NOLOGGING
TABLESPACE APPS_TS_INTERFACE
PCTFREE 0
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

SQL> analyze table gl.gl_interface validate structure cascade;

Table analyzed.

Now you wouldn't get the ORA-00600 error anymore.

Database upgrade from 9i to 10g on Linux ( Oracle 11i)

2009-04-23T18:13:00.003-07:00

Linux Box Preparation Steps
O/s level settings for oracle

Modify number of open file descriptors

Open /etc/security/limits.conf and change the existing values for "hard" and "soft" parameters as follows:
oratest soft nofile 8192
oratest hard nofile 63536
oratest soft nproc 4096
oratest hard nproc 16384
oratest soft memlock 15728640
oratest hard memlock 15728640

Modify port range value
Open /etc/sysctl.conf and change the port range value as follows. Reboot the system after you make this change.
net.ipv4.ip_local_port_range = 1024 65000
If you don’t change the same, there will be error during software installation pre-check, which needs to be ignored.

Check if Asyn I/o is enabled at Linux Database box
output with async io enabled.
$ cat /proc/slabinfo grep kio
kioctx 3060 3060 128 102 102 1 : 4032 1008
kiocb 83648 89820 128 2864 2994 1 : 4032 1008
kiobuf 36444 38460 128 1280 1282 1 : 4032 1008

output with async io disabled.
$ cat /proc/slabinfo grep kio
kioctx 0 0 128 0 0 1 : 252 126
kiocb 0 0 96 0 0 1 : 252 126
kiobuf 0 0 64 0 0 1 : 252 126

If the same is not enabled, please enable the same.

Check if hangcheck timer is set
/sbin/lsmod grep hangcheck

Check Network Requirements
Configure all nodes with two network adapters with TCP/IP support: one for the public network interface and one for the private network interface (interconnect).

In RAC Environment interconnect (Private Network) must support the UDP.

Each public network interface should be assigned an IP address and associated host name, registered in DNS. One virtual IP address (VIP) and an associated virtual host name registered in DNS, or resolved in the hostfile, or both. Each Private interface must be assigned a private IP address.

You can configure database to use either the virtual host name or virtual IP address.

Check Kernel Parameters and Linux Packages

Check for O/S Version in DB node.
$cat /etc/issue Red Hat Enterprise Linux
or
$cat /etc/*release*
AS release 3 (Taroon Update 6 Kernel Version – uname –r 2.4.21-37.ELsmp

Verify that the cat /etc/sysconfig/network file contains an entry that is similar to the following: HOSTNAME=.. The same needs to be run on DB node as root user or sudo.

NETWORKING=yes
HOSTNAME=testoracledb02.net
GATEWAY=10.36.130.10

Check the current O/s packages installed in DB
use following command.

rpm -q glib openmotif setarch
rpm -qa grep compat

The following patches (Minimum) must be installed:
compat-db-4.0.14.5
compat-gcc-7.3-2.96.122
compat-gcc-c++-7.3.2.96.122
compat-libstdc++-devel-7.3-2.96.122
openmotif21-2.1.30-8.i386.rpm
setarch-1.3-1

Ensure to Install Red Hat packages and apply patches rpm -qa --queryformat "%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n" grep libaio
+ compat-db-4.1.25-9
+ compat-gcc-32-3.2.3-47.3
+ compat-gcc-32-c++-3.2.3-47.3
+ compat-oracle-rhel4-1.0-3
+ compat-libcwait-2.0-1
+ compat-libgcc-296-2.96-132.7.2
+ compat-libstdc++-296-2.96-132.7.2
+ compat-libstdc++-33-3.2.3-47.3
+ xorg-x11-deprecated-libs-devel-6.8.1-23.EL
+ xorg-x11-deprecated-libs-6.8.1-23.EL

In addition, install the following rpm's (available on Disk3 of the RHEL4.0 media)
+ openmotif21-2.1.30-11
+ libaio-0.3.102-1
+ libaio-devel-0.3.102-1

Check following Kernel parameters
kernel.sem=250 32000 500 150
kernel.shmmax=34359738368
kernel.shmmni=4096
kernel.shmall=3279547
fs.file-max=23278304
kernel.msgmni=2878
vm.nr_hugepages=65
net.core.rmem_default=1048576
net.core.wmem_default=262144
net.core.rmem_max=1048576
net.core.wmem_max=262144

Cluster Verification Utility (CVU)

Install cvuqdisk Package for Linux using following command

rpm -iv cvuqdisk-1.0.1-1.rpm

This cvuqdisk RPM package is located under clusterware/rpm directoryon the installation media.

Check rsh, ssh, rcp, scp and host equivalence

a. Verify that you have rsh (remote shell) package installed on all your hosts, by executing the command: rpm -qa grep -i rsh

b. Check host has Public and private rac entries.

$cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
10.36.130.108 testoracledb01.net testoracledb01
10.36.130.245 testoracledb01.net testoracledb02
192.168.0.10 testoracledb01rac
192.168.0.11 testoracledb02rac

c. Check ssh is enabled between both Cluster nodes doing scp, from one node a test file to another node. If it does not ask for password then ssh is enabled. If it asks for password, then its not enabled.

d. Configure Secure Shell (SSH) on all cluster nodes as follows: Log in as the oracle user.
$ cd .ssh ( if not present make directory .ssh under /home/oracle/ )
$ chmod 755 ~/.ssh
e. Generate a RSA key using the following commands
$ /usr/bin/ssh-keygen -t rsa (Accept the default for all the prompt)
f. Generate a DSA key for the SSH protocol:
$ /usr/bin/ssh-keygen -t dsa (Accept the default location for the key file at the prompt )
g. SCP the id_rsa.pub and id_dsa_pub generated to other node.
h. Copy the contents of the id_rsa.pub and id_dsa.pub files to the /.ssh/authorized_keys file on this node, and to the same file on all other cluster nodes.
$ cat home/oracle/.ssh/id_rsa.pub >> /home/orac/.ssh/authorized_keys
i. Change the permissions on the home/oracle/.ssh/authorized_keys file on all cluster nodes: $ chmod 644 ~/.ssh/authorized_keys
j. After all done very again, Connect at ssh to each other node, it should not ask for password. From testoracledb01
$ssh testoracledb02
From testoracledb02
$ssh testoracledb01
It should not ask for password.

k. Use the following syntax to determine pre-installation steps status

//clusterware/cluvfy/runcluvfy.sh stage -pre crsinst -n testoracledb01, testoracledb02 nodes in your cluster must be separated by commas. for network

//clusterware/cluvfy/runcluvfy.sh comp nodecon -n testoracledb01, testoracledb02 -verbose for operating system requirement using CUV

//clusterware/cluvfy/runcluvfy.sh comp sys -n testoracledb01, testoracledb02 -p {crsdatabase}

example
$. ./runcluvfy.sh comp sys -n testoracledb01, testoracledb02 -p crs

Pre Upgrade Patches – (On your current 9i Database)

You may need to apply some Application interoperability patches before you upgrade your database from 9i to 10g.

Note: - If the LINUX BOX is already having CRS Installed perform the below mentioned steps:

1). Clean the .bash_profile on both the nodes
2). Move /home/oracle/oraInventory to /home/oracle/oraInventory.old
3). Move /var/opt/oracle to /var/opt/oracle.old
4). Execute the following:
rm /etc/oracle/*
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs
cp /etc/inittab /etc/inittab.orig

Install Oracle Clusterware 10.2.0.1

Start runInstaller from the Oracle Clusterware 10.2.0.1 staging area. Ensure we create directory structure for CRS OH /u01/ora/product/10.2.0 Ensure you have correct Java version installaed in the system.

[oracle@testoracledb01 bin]$ /u01/ora/product/jdk142/jdk/bin/java -version
java version "1.4.2_11" Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_11-b06) Java HotSpot(TM) 64-Bit Server VM (build 1.4.2_11-b06, mixed mode)

Start installation from staging area

$./runInstaller -jreLoc /u01/ora/product/jdk142/jdk
In the Specify Home details Window, enter the name and path of CRS ORACLE_HOME and click Next.

In the Cluster Configuration Window, enter the name of the Cluster Configuration. Specify public node, corresponding private node names for the public nodes and corresponding virtual host names for the public host names.

In next screen specify network interface with the interface type
Enter the location for Oracle Cluster Registry (OCR) and click Next.

In case of upgrade of RAC DB, this OCS screen does not appear. Oracle 9i server manager file identified in /var/opt/oracle/srvConfig.loc (in our case, srvm.dbf) that exists on shared storage will become the new OCR file required for 10g.
Enter the location and the name for Voting Disk (e.g. /ocfs01_msa/ora10g/voting.dbf). Click Next.
Please create a directory for the voting disk in shared file system beforehand.

NOTE: The OCR and Voting Disks must be located on a shared file system that is accessible by all nodes.

Verify the installation Summary Window and Click Install.
Exexcute root.sh from all nodes at the end of the installation. Execute root.sh from the CRS ORACLE_HOME specified after logging in as root in a different terminal window. Root.sh will also start CRS services on both the cluster nodes.

Before Running root.sh please create softlink for JDK in CRS_ORACLE_HOME to avoid following error

# # An unexpected error has been detected by HotSpot Virtual Machine:
# # SIGILL (0x4) at pc=0x2000000003aa4070, pid=12563, tid=16384
# # Java VM: Java HotSpot(TM) 64-Bit Server VM (1.4.2_10-b03 mixed mode)
# Problematic frame: # /u01/ora/product/10.2.0/crs/install/rootconfig: line 1046: 12563 Segmentation fault $CH/bin/vipca -silent -nodelist

$CRS_NODELIST -nodevips $CRS_NODEVIPS
$ sudo ./root.sh
WARNING: directory '/u01/ora/product/10.2.0' is not owned by root
WARNING: directory '/u01/ora/product' is not owned by root
WARNING: directory '/u01/ora' is not owned by root
Checking to see if Oracle CRS stack is already configured /etc/oracle does not exist. Creating it now.
Setting the permissions on OCR backup directory
Setting up NS directories Oracle Cluster Registry configuration upgraded successfully WARNING: directory '/u01/ora/product/10.2.0' is not owned by root.
WARNING: directory '/u01/ora/product' is not owned by root
WARNING: directory '/u01/ora' is not owned by root clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node : node 1: testoracledb01 testoracledb01rac testoracledb01
node 2: testoracledb02 testoracledb02rac testoracledb02
clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override. -force is destructive and will destroy any previous cluster configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
testoracledb01
testoracledb02
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps

#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
# SIGILL (0x4) at pc=0x2000000003aa4070, pid=12563, tid=16384
#
# Java VM: Java HotSpot(TM) 64-Bit Server VM (1.4.2_10-b03 mixed mode)
# Problematic frame:
# /u01/ora/product/10.2.0/crs/install/rootconfig: line 1046: 12563 Segmentation fault $CH/bin/vipca -silent -nodelist $CRS_NODELIST -nodevips $CRS_NODEVIPS

As discussed earlier, root.sh in the last node will throw error like:
Oracle CRS stack installed and running under init(1M) Running vipca(silent) for configuring nodeapps. The given interface(s), "eth0" is not public. Public interfaces should be used to configure virtual IPs.

Solution for this is to run vipca manually after completing the installation Run VIPCA manually from CRS_ORACLE_HOME/Bin as root user. It will open another window to do VIP configuration

$ sudo ./vipca

Install Oracle Software 10.2.0.1

From the staging area, where u have downloade 10.2.0.1 software and unzipped the same. Go to that same location and run its installer.

$./runInstaller -jreLoc /u01/ora/product/jdk142/jdk

A GUI first screen comes, the same ask for Select Installation Method. Check the button Advanced Installation option and Click Next.

The next screen asks for Select Installation Type. Check the button Oracle Enterprise Edition 10.2.0.1 from the list of options, and click Next.

The next screen asks for Specify Home Details. Check under Path option the Oracle Home it is showing is correct one and is pointing to new 10g location. Else change the same to the new 10g Oracle Home location then Click on Next Button

In Cluster Nodes Window, verify the cluster nodes shown for the installation. Select all nodes for installation, and click Next.

Then comes the Product specific pre-requisites checks. The same will be performed and it will pass all. Then click Next. If you see any error or warning need to correct and start the same again.

Then the Screen come Upgrade an Existing Database. Select NO on the same, and click Next.

The Next screen asks for Select Configuration Options. Select the checkbox Install Database Software Only option, and then click on Next Button.

On the database creation Window, click Install Database Software only, and click Next.

Next comes the Summary Screen. Check all is correct and then Click on Install Button.

Then the Installation starts and you will see the progress of it.
During the course of the installation, the Installer will prompt for executing root.sh. Log on to another terminal of same Database box and go to ORACLE_HOME location. Either you log on as root or your own login and do sudo to run as root.

$export $ORACLE_HOME=/u01/ora/product/oracle10g
$cd $ORACLE_HOME
$sudo ./root.sh

During running ./root.sh it is promt for various overwrite of files. Type y for each time it asks for.Once the same is done, return to Graphical Interface Installation screen and Click on OK button for the promt.
Final screen comes End of Installation.
Click on Exit Button once its done.

Install: “Oracle DB 10g (10.2.0.1)Products” from Companion CD

Go to the Staging home location and execute the installer as below.
$ ./runInstaller –jreLoc /u01/ora/product/jdk142/jdk
First screen comes Select the Product Install. Check on box to select Oracle Database 10g Products 10.2.0.1.0 and then click on Next button.

Next comes sceen Specify Oracle Home Details. Check and ensure its showing correct 10g Oracle Home location. Click on Next button.

Next screen is Product Specific pre-requisites check. Will check all pre req and give report of Success at the end. After which click on Install Button.

Then comes the Summary Screen. Crosscheck if all are correct and then press on Install Button. Installation starts with window showing its progress.

Final screen is End of Installation, showing the url details. Click on Exit button on the same. You are done.

Apply 10.2.0.3.0 or 10.2.0.4.0 patch to cluster and oracle home

Set the ORACLE_HOME variable to the 10.2.0.1 CRS ORACLE_HOME installed earlier in CRS 10.2.0.1 install.
Shut down all the CRS services on all the nodes in the cluster.
$10g CRS ORACLE_HOME/bin/crsctl stop crs

Execute runInstaller from the 10.2.0.2 staging area and upgrade the CRS software to 10.2.0.3.0 At the end of the installation, the Installer will prompt for executing root.sh from all the nodes. Log in as root in a different terminal window, and execute root.sh.

$ export ORACLE_HOME=/u01/ora/product/10.2.0/crs
$ ./runInstaller -jreLoc /u01/oracle/OraHome_1/jdk/

Unset the existing ORACLE_HOME, and set the same to point to 10.2.0.1 ORACLE_HOME. Execute Installer, under 10.2.0.3 patchset staging location.

$./runInstaller -jreLoc /u01/oracle/OraHome_1/jdk/

Screen gives you the summary on Installation, Click on Install Button.
Installation starts and you see its progress. Then comes End of Installation screen, here you will click on Exit button. You are done with software upgrade work. This will upgrade the 10g Software from 10.2.0.1 to 10.2.0.3 (Binaries)

Apply RDBMS Patch or CPU

Once binary upgrade is done you can apply any RDBMS patch or CPU patch to CRS or ORACLE_HOME.

Pre – Database Upgrade 9i to 10g

Run the pre-upgrade information tool using following steps. (DB1) Open another terminal session connecting to Database as oracle user.

$echo $ORACLE_HOME (This should point to 9i ORACLE HOME) $cp /u01/ora/product/oracle10g/rdbms/admin/utlu102i.sql .
SQL>spool instance_preinfo102.log
mailto:SQL@utlu102i.sql

Check for the deprecated CONNECT Role (DB1) After upgrading to 10gR2, the CONNECT role will only have the CREATE SESSION privilege. The other privileges granted to the CONNECT role in earlier releases will be revoked during the upgrade. To identify which users and roles in your database are granted. In Oracle 9i Database session SQLPLUS run the following query.

$echo $ORACLE_HOME (check it should point to 9i OH)
$sqlplus Username: / as sysdba
SQL>spool connect_dep.log
SQL> SELECT grantee FROM dba_role_privs WHERE granted_role = 'CONNECT' and grantee NOT IN ( 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT'
SQL> exit

In Oracle 10.2 the CONNECT role only includes CREATE SESSION privilege.

$ sqlplus '/as sysdba'
SQL> create role connect_sub;
SQL> grant CREATE VIEW ,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SESSION,CREATE SYNONYM,CREATE SEQUENCE,CREATE DATABASE LINK to connect_sub;
SQL> Spool assign_role.sql
SELECT 'grant connect_sub to ' grantee ';' FROM dba_role_privs WHERE granted_role = 'CONNECT' and grantee NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS','ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER','OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

Startup the 10g database to upgrade.

$echo $ORACLE_HOME (should point to 10g OH)
$sqlplus "/ as sysdba"
SQL>STARTUP UPGRADE

Create Mandatory SYSAUX tablespace.

SQL> CREATE TABLESPACE sysaux DATAFILE '/u03_iscsi/ora/testdata/ocfsd19/sysaux01.dbf' SIZE 2000M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

Run the upgrade script. Spool the output so you can take a look at possible errors after the upgrade:

SQL> spool upgrade.log
SQL> @/u01/ora/product/oracle10g/rdbms/admin/catupgrd.sql
SQL> spool off

This takes a long time in my case took 1 and ½ hours.

Verify the upgrade, Run utlu102s.sql, specifying the TEXT option:

$echo $ORACLE_HOME (ensure its 10g home)
$cd /home/oracle/10g_upgrade
$sqlplus "/ as sysdba"
SQL>spool verify_upg.log
SQL>@ /u01/ora/product/oracle10g/rdbms/admin/utlu102s.sql TEXT
SQL>spool off;

Check the output if all product has been successfully updated.
Restart the database. (10g Database)

$sqlplus "/ as sysdba"
SQL>SHUTDOWN IMMEDIATE; (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)
SQL>STARTUP

Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

$sqlplus "/ as sysdba"
SQL>@ /u01/ora/product/oracle10g/rdbms/admin/utlrp.sql
This will take long time a hour or more.

Post Database Upgrade 10.2.0.3

Update Environment Variables after the Upgrade. Make sure that the following environment variables point to the new release 10.2 directories: (To be done on both Database node.) ORACLE_HOME, PATH , ORA_NLS10 , LD_LIBRARY_PATH.

Gather sys schema statistics.
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node.
Use SQL*Plus to connect to the database as SYSDBA and restart the database in restricted mode, run adstats.sql, and restart the database in normal mode.

Execute assign_role.sql which we generated in earlier step.

Unlock schema and table statistics using unlock_statistics.sql

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

execute dbms_stats.unlock_schema_stats('APPLSYS');
execute dbms_stats.unlock_schema_stats('XNP');
execute dbms_stats.unlock_schema_stats('XXHL');
execute dbms_stats.unlock_schema_stats('AR');
execute dbms_stats.unlock_schema_stats('IEO');
execute dbms_stats.unlock_schema_stats('CCT');
execute dbms_stats.unlock_schema_stats('SYSTEM');
execute dbms_stats.unlock_schema_stats('AMV');
execute dbms_stats.unlock_schema_stats('ASO');
execute dbms_stats.unlock_schema_stats('IEM');
execute dbms_stats.unlock_schema_stats('XDP');
execute dbms_stats.unlock_schema_stats('APPS');
execute dbms_stats.unlock_schema_stats('IBU');
execute dbms_stats.unlock_schema_stats('SYS');
execute dbms_stats.unlock_schema_stats('JTF');
execute dbms_stats.unlock_schema_stats('OKC');

TUNING STEPS for 10g NEW FEATURE

SGA memory automatic tuning, new parameter SGA_TARGET and other needs to be calculated and set.
sga_target = 1G
shared_pool_size = 400M
shared_pool_reserved_size = 40M
log_buffer = 10M
Remove or comment the following entry from init.ora file –
db_cache_size
sga_max_size
java_pool_size
large_pool_size

The following parameters will be auto tuned new database feature, on spfile. Change STATISTICS_LEVEL=TYPICAL for AWR to collect statistics.

Enter 10g specific entry in init parameter files.
olap_page_pool_size = 4194304
plsql_optimize_level = 2 #MP
plsql_code_type = native #MP
plsql_native_library_dir = /prod11i/plsqlnativelib
plsql_native_library_subdir_count = 149
_b_tree_bitmap_plans = FALSE #MP
_kks_use_mutex_pin=TRUE
optimizer_secure_view_merging = FALSE
optimizer_dynamic_sampling = 2
disk_asynch_io = TRUE
_optimizer_cost_based_transformation=off ##put only for 11.5.9

How to Configure Oracle Streams in Oracle environment

2009-04-23T18:12:00.001-07:00

In this post i am going to discuss steps to configure Oracle Streams in Oracle environment. We wouldn't be discussing about streams in details but steps only. If you need more details please refer the oracle documentation.

1. Streams Configuration Setup – Source System

Add following parameters to source and destination instances
log_archive_format='ORCLPRODA_%t_%s_%r.arc'
log_archive_dest_1='LOCATION=/ocfsd106/ora/arch/ORCLPRODA'
STREAMS_POOL_SIZE =500M
_job_queue_interval=1
Create tablespace to hold stream data Source
CREATE TABLESPACE STREAMS_DATA DATAFILE '/ocfsd105/ora/ORCLPRODdata/ocfsd106/streamd01.dbf' SIZE 1000 M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;
Create/Setup Stream User to hold stream data

CREATE USER strmadmin
IDENTIFIED BY strmadmin
DEFAULT TABLESPACE STREAMS_DATA
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON STREAMS_DATA;

GRANT CONNECT, RESOURCE, DBA TO strmadmin;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;
GRANT AQ_ADMINISTRATOR_ROLE to STRMADMIN;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;
GRANT SELECT_CATALOG_ROLE TO strmadmin;

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/
begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/

begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/
begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/

begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/

begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/

begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,
object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
grantee => 'STRMADMIN',
grant_option => FALSE );
END;

/
Create database Link from Source to Target
update global_name set global_name='ORCLPROD.WORLD';

CREATE DATABASE LINK "ORCLREP.WORLD" CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING 'ORCLREP';
Create Streams queue for each schema

sqlplus strmadmin/strmadmin

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STRMADMIN.STREAMS_SQUEUE',
queue_table =>' STRMADMIN.STREAMS_SQUEUE_TABLE',
queue_user => 'STRMADMIN');
END;

It's a good idea to create sepereta queue for each schema you are planning to replicate.
Create capture process and Rules for the tables at the source database
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES (
table_name => 'APPLSYS.FND_TERRITORIES_TL',
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE_APPLSYS', queue_name => 'STRMADMIN.STREAMS_APPSQUEUE', include_dml => true,
include_ddl => true,
source_database => 'ORCLPROD');
END;
/

Add propagation rules for the table at the source database. This step will also create a propagation job to the destination database.

Example:

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'APPLSYS.FND_TERRITORIES_TL',
streams_name => 'STRMADMIN_PROPAGATE',
source_queue_name => 'STRMADMIN.STREAMS_SQUEUE',
destination_queue_name => 'STRMADMIN.STREAMS_TQUEUE@ORCLREP',
include_dml => true,
include_ddl => true,
source_database => 'ORCLPROD');
END;

2. Streams Configuration Setup – Target System

Create tablespace to hold stream data

CREATE TABLESPACE STREAMS_DATA DATAFILE '/ocfsr101/ora/ORCLREPdata/streamd01.dbf' SIZE 1000 M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;
Create/Setup Stream User to hold stream data

CREATE USER strmadmin
IDENTIFIED BY strmadmin
DEFAULT TABLESPACE STREAMS_DATA
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON STREAMS_DATA;

GRANT CONNECT, RESOURCE, DBA TO strmadmin;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;
GRANT AQ_ADMINISTRATOR_ROLE to STRMADMIN;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;
GRANT SELECT_CATALOG_ROLE TO strmadmin;

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/
begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/

begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/
begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/

begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/

begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
end;
/

begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,
object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
grantee => 'STRMADMIN',
grant_option => FALSE );
END;

/
Create Tablespaces and Users at Destination database

CREATE TABLESPACE ONTD DATAFILE '/ocfsr101/ora/ORCLREPdata/ontd01.dbf' SIZE 8000 M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER ONT
IDENTIFIED BY ONT
DEFAULT TABLESPACE ONTD
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON ONTD;

GRANT CONNECT, RESOURCE TO ONT;

alter user ONT QUOTA UNLIMITED ON ONTX;
Export tables from source database to target database

Source

exp ont/ont tables=(OE_ORDER_LINES_ALL) file=('ONT_ORCLPROD01.dmp') log=ONT_ORCLPROD.log FILESIZE=4G DIRECT=YES

Target

imp ont/ont file=('ONT_ORCLPROD01.dmp') log=ONT_ORCLREP.log FULL=Y
Create database Link from Target to Source
update global_name set global_name='ORCLREP.WORLD';

CREATE DATABASE LINK "ORCLPROD.WORLD"
CONNECT TO STRMADMIN
IDENTIFIED BY STRMADMIN
USING 'ORCLPROD';
Create Streams queue for each schema

sqlplus strmadmin/strmadmin

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STRMADMIN.STREAMS_TQUEUE',
queue_table =>' STRMADMIN.STREAMS_TQUEUE_TABLE',
queue_user => 'STRMADMIN');
END;
Create Apply process and Rules for the tables at the destination database

Example:

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'APPLSYS.FND_TERRITORIES_TL',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY',
queue_name => 'STRMADMIN.STREAMS_TQUEUE',
include_dml => true,
include_ddl => true,
source_database => 'ORCLPROD');
END;
/
Grant Full permission on all objects to stream admin user

Example:

grant all on APPLSYS.FND_TERRITORIES_TL to STRMADMIN;
Activate stream admin user to apply all DML and DDL changes

The user specified in the APPLY_USER parameter must have the necessary privileges to perform DML and DDL changes on the apply objects.

BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'STRMADMIN_APPLY',
apply_user => 'STRMADMIN');
END;

3. Instantiate Table level Replication

Find the SCN number in the source

set serveroutput on
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE (iscn);
END;
/

DECLARE
source_scn NUMBER;
BEGIN
source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'APPLSYS.FND_TERRITORIES_TL',
source_database_name => 'ORCLPROD',
instantiation_scn => source_scn);
END;
/

Instantiate the objects in the target. The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are to be applied by the apply process. If the commit SCN of an LCR from the source database is less than or equal to this instantiation SCN, then the apply process discards the LCR else, the apply process applies the LCR.

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'APPLSYS.FND_TERRITORIES_TL',
source_database_name => 'ORCLPROD',
instantiation_scn => 8221381383942);
END;
DECLARE source_scn NUMBER; BEGIN
source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@ORCLREP( source_object_name => 'APPLSYS.FND_TERRITORIES_TL', source_database_name => 'ORCLPROD, instantiation_scn => source_scn); END;/
Set performance tuning parameters in source and target database
begin
dbms_capture_adm.set_parameter('STRMADMIN_CAPTURE','_checkpoint_frequency','400');
end;

begin
dbms_capture_adm.set_parameter(capture_name=>'STRMADMIN_CAPTURE',checkpoint_retention_time=>2); end; Begin dbms_capture_adm.set_parameter('CAPTURE_NAME','_CHECKPOINT_FORCE','Y'); END;

begin
dbms_aqadm.alter_propagation_schedule('STRMADMIN.STREAMS_SQUEUE','ORCLREP',latency=>1);
end;
Start the capture process in the source

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STRMADMIN_CAPTURE');
END;
/
Start the apply process in the target

BEGIN
DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRMADMIN_APPLY');
END;
/
Set parameters for Apply Process at target site

begin
dbms_apply_adm.set_parameter('STRMADMIN_APPLY','disable_on_error','n');
end;

Begin
dbms_apply_adm.set_parameter('STRMADMIN_APPLY','_TXN_BUFFER_SIZE',2);
end;
Begin
dbms_apply_adm.set_parameter('STRMADMIN_APPLY','_DYNAMIC_STMTS','Y');
end;
Monitor the apply process in the target

SELECT (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Event Creation",
TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
HWM_MESSAGE_NUMBER "Applied Message Number"
FROM V$STREAMS_APPLY_COORDINATOR
WHERE APPLY_NAME = 'STRMADMIN_APPLY';

SELECT
(APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Event Creation",
TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
APPLIED_MESSAGE_NUMBER "Applied Message Number"
FROM DBA_APPLY_PROGRESS
WHERE APPLY_NAME like 'STRMADMIN_APPLY';

SELECT DECODE(ap.APPLY_CAPTURED,
'YES','Captured LCRS',
'NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED,
SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
r.STATE,
r.TOTAL_ASSIGNED,
r.TOTAL_MESSAGES_APPLIED
FROM V$STREAMS_APPLY_SERVER R, V$SESSION S, DBA_APPLY AP
WHERE r.APPLY_NAME like 'STRMADMIN_APPLY' AND
r.SID = s.SID AND
r.SERIAL# = s.SERIAL# AND
r.APPLY_NAME = ap.APPLY_NAME
ORDER BY r.SERVER_ID;

SELECT q.owner, q.name, t.queue_table, t.owner_instance
FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
WHERE t.object_type = 'SYS.ANYDATA' AND
q.queue_table = t.queue_table AND
q.owner = t.owner;
Flow Control Query COLUMN queue_schema FORMAT A12 COLUMN queue_name FORMAT A20 SELECT queue_schema, queue_name, unbrowsed_msgs, overspilled_msgs, memory_usage, publisher_stateFROM V$BUFFERED_PUBLISHERS;

SELECT
subscriber_name, cnum_msgs, total_dequeued_msg, total_spilled_msg
FROM V$BUFFERED_SUBSCRIBERS;
Dynamic Streams views V$STREAMS_CAPTURE GV$STREAMS_CAPTURE V$STREAMS_APPLY_COORDINATOR GV$STREAMS_APPLY_COORDINATOR V$STREAMS_APPLY_READER GV$STREAMS_APPLY_READER V$STREAMS_APPLY_SERVER GV$STREAMS_APPLY_SERVER V$STREAMS_POOL_ADVICE GV$STREAMS_POOL_ADVICE V$STREAMS_TRANSACTION GV$STREAMS_TRANSACTION V$BUFFERED_PUBLISHERS GV$BUFFERED_PUBLISHERS V$BUFFERED_QUEUES GV$BUFFERED_QUEUES V$BUFFERED_SUBSCRIBERS GV$BUFFERED_SUBSCRIBERS V$PROPAGATION_RECEIVER GV$PROPAGATION_RECEIVER V$PROPAGATION_SENDER GV$PROPAGATION_SENDER V$RULE GV$RULE V$RULE_SET GV$RULE_SET V$RULE_SET_AGGREGATE_STATS GV$RULE_SET_AGGREGATE_STATS
Static Streams Views Capture Views DBA_CAPTURE DBA_CAPTURE_EXTRA_ATTRIBUTES DBA_CAPTURE_PARAMETERS DBA_CAPTURE_PREPARED_DATABASE DBA_CAPTURE_PREPARED_SCHEMAS DBA_CAPTURE_PREPARED_TABLES
Apply Views DBA_APPLY DBA_APPLY_CONFLICT_COLUMNS DBA_APPLY_DML_HANDLERS DBA_APPLY_ENQUEUE DBA_APPLY_ERROR DBA_APPLY_EXECUTE DBA_APPLY_INSTANTIATED_GLOBAL DBA_APPLY_INSTANTIATED_OBJECTS DBA_APPLY_INSTANTIATED_SCHEMAS DBA_APPLY_KEY_COLUMNS DBA_APPLY_OBJECT_DEPENDENCIES DBA_APPLY_PARAMETERS DBA_APPLY_PROGRESS DBA_APPLY_SPILL_TXN DBA_APPLY_TABLE_COLUMNS DBA_APPLY_VALUE_DEPENDENCIES
Propagation & Queue Views Streams Views DBA_PROPAGATION DBA_QUEUE_SCHEDULES DBA_QUEUE_SUBSCRIBERS DBA_QUEUE_TABLES DBA_QUEUES
Streams views DBA_REGISTERED_ARCHIVED_LOG DBA_RECOVERABLE_SCRIPT DBA_RECOVERABLE_SCRIPT_BLOCKS DBA_RECOVERABLE_SCRIPT_ERRORS DBA_RECOVERABLE_SCRIPT_PARAMS DBA_STREAMS_ADD_COLUMN DBA_STREAMS_ADMINISTRATOR DBA_STREAMS_DELETE_COLUMN DBA_STREAMS_GLOBAL_RULES DBA_STREAMS_MESSAGE_CONSUMERS DBA_STREAMS_MESSAGE_RULES DBA_STREAMS_NEWLY_SUPPORTED DBA_STREAMS_RENAME_COLUMN DBA_STREAMS_RENAME_SCHEMA DBA_STREAMS_RENAME_TABLE DBA_STREAMS_RULES DBA_STREAMS_SCHEMA_RULES DBA_STREAMS_TABLE_RULES DBA_STREAMS_TRANSFORM_FUNCTION DBA_STREAMS_TRANSFORMATIONS DBA_STREAMS_UNSUPPORTED DBA_RULE_SET_RULES DBA_RULE_SETS DBA_RULES DBA_HIST_BUFFERED_QUEUES DBA_HIST_BUFFERED_SUBSCRIBERS DBA_HIST_RULE_SET DBA_HIST_STREAMS_APPLY_SUM DBA_HIST_STREAMS_CAPTURE DBA_HIST_STREAMS_POOL_ADVICE
Apply process parameter _HASH_TABLE_SIZE: set this relatively high (such as 10000000), to minimize “wait dependency” bottlenecks.
Apply process commit frequency
Propagate
• Set the propagate parameter LATENCY to 0
• Set the propagate parameter QUEUE_TO_QUEUE to TRUE in case of RAC(10.2 only).
Capture Process

streams$_capture_process: lists all defined capture processes
dba_capture: basic status, error info
v$streams_capture: detailed status info
dba_capture_parameters: configuration information
Propagate Process

streams$_propagation_process: Lists all defined propagate processes
dba_propagation: Basic status, error info
v$propagation_sender: Detailed status
v$propagation_receiver: Detailed status
Apply Process

streams$_apply_process: Lists all defined apply processes
dba_apply: Basic status, error info
v$streams_apply_reader: Status of the apply reader
v$streams_apply_server: Status of apply server(s)
v$streams_apply_coordinator: Overall status, latency info
dba_apply_parameters: Configuration information
“Miscellaneous” Tables and Views

v$buffered_queues: View that displays the current and cumulative number of messages enqueued and spilled, for each buffered queue.
sys.streams$_apply_spill_msgs_part: Table that the apply process uses, to “spill” messages from large transactions to disk.
system.logmnr_restart_ckpt$: Table that holds capture process “checkpoint” information.

In the Next post i will discuss Downstream Oracle Streams Configuration.