PRVF-4037 : CRS is not installed on any of the nodes

During the health check, we observed that has service status is showing as down in EM12C

Error as shown in the Incident Manager :

PRVF-4037 : CRS is not installed on any of the nodes,Verification cannot proceed,,,CRS integrity check failed,,Verification of CRS integrity was unsuccessful on all the specified nodes. ,NODE_STATUS::<Host>:EFAIL,OVERALL_STATUS::EFAIL

Logged in to the host as Grid owner :

[oracle@<host> ~]$  cluvfy comp ocr

Verifying OCR integrity

Checking OCR integrity...

ERROR:
PRVF-4037 : CRS is not installed on any of the nodes
Verification cannot proceed

OCR integrity check failed

Verification of OCR integrity was unsuccessful on all the specified nodes.
[oracle@<host> ~]$

Cause :

/etc/oraInst.loc was pointing to incorrect Inventory..

Solution :

Corrected the /etc/oraInst.loc and pointed it to expected ( Grid ) Inventory

[oracle@<host> ~]$  cluvfy comp ocr

Verifying OCR integrity

Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations


ASM Running check passed. ASM is running on all specified nodes

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful


Disk group for ocr location "+DA_XX" available on all the nodes


NOTE:
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Verification of OCR integrity was successful.
[oracle@<host> ~]$

Create an Alias for [ sqlplus "/as sysdba" ]

We can use this OS utility to create an alias of the commands we use very frequently:

Here, for example :

-bash-3.00$ cat .bash_profile

alias sqllogin='sqlplus "/as sysdba"'

-bash-3.00$

-bash-3.00$ sqllogin

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 5 07:24:46 2013

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


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

SQL>

Hope this helps !!

Upgrading Application [ 11i CU1 to 11i CU2 ] and Database [ 9.2.0.6 to 11.2.0.1 ]

Hello All,


Recently I was involved in the upgrade [ application and database ]
Here is the walkthrough of the project.


::::::::::::::::::::: Overview :::::::::::::::::::::


: Existing System :
Application version : 11.5.10.1 [ 11i CU1 ]
Database version    : 9.2.0.6   [ 9iR2    ]




: Target System :
Application version : 11.5.10.2 [ 11i CU2 ]
Database version    : 11.2.0.1  [ 11gR2   ]




:Steps Involved :
1] Review of the System to be upgraded.
2] Clone for the iterations.
3] Phase - 1 : Upgrading Application from 11iCU1 to 11iCU2.
4] Phase - 2 : Upgrading Database from 9iR2 to 10gR2
5] Phase - 3 : Upgrading the required Family Packs 
6] Phase - 4 : Upgrading Database from 10gR2 to 11gR2.
7] Phase - 5 : Patches for the issues faced after upgrade.






Now , Lets get into the details of each step.


1] Review of the System to be upgraded >>
As we all aware , that 11i premium support ended in Nov 2010 , so we referred Metalink Note-ID 
"883202.1 for Minimum Baseline Patch Requirements for Extended Support"  to make our review easier.


To check the existing patchset level of the system :


Execute below sql :
select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME ;


OR


Download the latest patchset.sh from the Metalink Note-ID :
Oracle Applications Current Patchset Comparison Utility - patchsets.sh [ID 139684.1]


This shell script will provide you the current patchset level and also the latest patchset available on the metalink.


This will ease our review at module level as well.


Very Important Note : Prepare a detailed sheet of the steps to be executed.




2] Clone for the iterations >>
For Cloning we used Rapid clone method .


Importantly , Always check UAT system configuration to avoid surprise performance issues after Production Upgrade.
Keep the configuration at optimum for benchmarking testing.




3] Phase - 1 : Upgrading Application from 11iCU1 to 11iCU2 >>


Phase - 1 is three segments step.


A - Applying the CU2 patch.
B - Applying the latest TXK AUTOCONFIG AND TEMPLATES ROLLUP PATCH.
C - Applying 11.5.10 INTEROP PATCH FOR 10GR2.


A] Applying the CU2 patch >>


The Read me suggests to apply latest Oracle Applications DBA Minipack 11i.AD.I :: i.e AD.I.7 plus few pre-requisites :


Next step would be to check whether our Application-Database Tier is ready to be upgraded. So, we can run TXKScript.pl on both the tiers.


The script TXKScript.pl is placed under FND_TOP on Application Tier and ORACLE_HOME on Database Tier.


Pre-requisites >>


# Run the admkappsutil.pl under AD_TOP to create the appsutil.zip file
# Copy the successfully generated zip file to DB Tier and unzip it [ unzip -o appsutil.zip ]




Next step would be to run the scripts on both the Tiers :


Sample Script :


>> Run the Technology Validation Utility using the below script on Application Tier:
$ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl
        -script=$FND_TOP/patch/115/bin/txkVal11510MP_AP.pl
        -txktop=$APPLTMP
        -appspass=<apps_password>
        -outfile=$APPLTMP/txkVal11510MP.html




>>  Run the Technology Validation Utility using the below script on Database Tier:
$ADPERLPRG $ORACLE_HOME/appsutil/bin/TXKScript.pl
        -script=$ORACLE_HOME/appsutil/bin/txkVal11510MP.pl
        -txktop=$ORACLE_HOME/appsutil
        -appspass=<apps_password>
        -outfile=$ORACLE_HOME/appsutil/temp/txkVal11510MP_DB.html






IMPORTANT :: Make sure the output of the script is "ALL PASS " on both the Tiers.


Next step would be to apply the CU2 patch on the application :
# Bounce the database and listener to ensure that there are no locks.
# Use the option "nocompilejsp" to save the time of upgrade.


Note : Check for the successful completion.




B] Applying the latest TXK AUTOCONFIG AND TEMPLATES ROLLUP PATCH >>


Pre-Steps :
We have to check whether the application is ready to techstack upgrade :


Run the Technology Stack Validation script as follows:
           NOTE: Ensure that your current working directory is
           [patch unzipped location]/fnd/patch/115/bin


          ./txkprepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=<apps database password>




Once we pass the validation , we can apply the TXK patch on the application Tier.




C] Applying 11.5.10 INTEROP PATCH FOR 10GR2 >>
This patch will prepare the application tier for the database upgrade to 10gR2.


Once the Phase - 1 is completed , we are sure that our Application is upgraded from 11iCU1 to 11iCU2 and also is ready to database upgrade from 9iR2 to 10gR2.




4] Phase - 2 : Upgrading Database from 9iR2 to 10gR2 >>


A] Installation of Oracle 10gR2 Base Release [ 10.2.0.1 ]


# Create a directory on the server to install the Oracle Binaries onto the server.


# Download the stage from OTN and upload & unzip the database 10gR2 base release stage to the server.


# Install the Base Release to directory. [ with companion ]


# Now download the 10gR2 patchset 3 patch and apply it on 10g Home.


# Apply the pre-req optaches on the new home. [ Refer upgrade metalink note-id ]


# Check whether the Existing database is ready for the upgrade :


Goto 10.2.0/rdbms/admin
ls –ltr  utlu112i.sql
Sqlplus “/as sysdba”


SQL> spool <filename>.log
SQL>@utlu112i.sql


B] Upgrade the database from 9iR2 to 10gR2:


# Unset the existing 9i environment:


# Set new ORACLE_HOME and ORACLE_SID 


cd $ORACLE_HIME/bin


./dbua




Follow the screens and start the upgrade.


Make sure the upgrade finishes successfully.


Check the dba_registry for successful upgrade of the components.




Post Steps :


Re-create 9idata directory
Run changeperm.sh from $ORACLE_HOME/install
Fix Korean lexers
Run adgrants.sql
Grant create Procedure on CTXSYS
Implement Autoconfig
Gather Stats for SYS schema


Note : Set new environment and check the application.




5] Phase - 3 : Upgrading the required Family Packs >>


Review required family packs ( In our case )


A] 11i.ATG_PF.H.delta.7 (RUP 7)
B] 11I.HR_PF.K delta 5
C] 11i.FIN.PF.G
D] MINI-PACK 11I.AP.O
E] MINI-PACK 11I:PN.M


Post Steps :
Generate message files. 
Compile APPS schema(s). 
Compile flexfield data in AOL tables. 
Compile menu information. 




6] Phase - 4 : Upgrading Database from 10gR2 to 11gR2


Pre-Steps :


exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSMAN'); [ if the user exist ]


A] Installation of Oracle 11gR2 Base Release [ 11.2.0.1 ]


# Create a directory on the server to install the Oracle Binaries onto the server.


# Download the stage from OTN and upload & unzip the database 11gR2 base release stage to the server.


# Install the Base Release to directory. [ with companion ]


# Apply the pre-req optaches on the new home. [ Refer upgrade metalink note-id ]


# Check whether the Existing database is ready for the upgrade :


Login as Database user 
Goto 11.2.0/rdbms/admin
ls –ltr  utlu112i.sql
Sqlplus “/as sysdba”


SQL> spool <filename>.log
SQL>@utlu112i.sql
....
SQL> spool off;


# Unset the existing 10g environment:


# Set new ORACLE_HOME and ORACLE_SID 


cd $ORACLE_HIME/bin


./dbua




Follow the screens and start the upgrade.


Make sure the upgrade finishes successfully.


Check the dba_registry for successful upgrade of the components.




Post Steps :


Re-create 9idata directory
Run adgrants.sql  
Grant create procedure privilege on CTXSYS  
Set CTXSYS parameter  
Implement Autoconfig
Gather statistics for SYS schema 


Note : Set 11g environment and check the application health.



7] Phase - 5 : Patches for the issues faced after upgrade >>


This phase is very important phase of the upgrade.


Once we release the instance to the users , they will report list of issues.


Some issues wil resolved using patches, so log a ticket and apply all the required patches.


Important Note : To avoid surprise issues after upgrade , we have to make sure that most of the issues are resolved in the iteration phase.


This will reduce the downtime of the instance after upgrade.


I have tried my best to summarize the upgrade ; I hope this will help !!




Best Of luck for the upgrade.

Metalink Note-IDs related to Concurrent Managers

1) Concurrent Processing Tables and Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) [ID 104282.1]

2) Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance [ID 822368.1]

3) Best Practices for Performance for Concurrent Managers in E-Business Suite [ID 1057802.1]

4) Using the Output Post Processor (OPP) in Oracle Applications Concurrent Processing [ID 291792.1]

5) Cannot View Concurrent Manager Log And Out Files in R12 (APP-FND-01634 ) [ID 555394.1]

6) Deadlock Error During Concurrent Request Termination [ID 1060736.1]

7) Concurrent Processing (CP) / APPS Reporting Scripts [ID 213021.1]

8) How to Activate Parallel Concurrent Processing - More Facts [ID 602899.1]

9) How to Configure Concurrent Managers Applications for Parallel Concurrent Processing [ID 157352.1]10) Performance: Purge Obsolete Workflow Runtime Data ( FNDWFPR ) Concurrent Request is Slow [ID 985332.1]

11) Concurrent Requests Errors Out With ORA-20100: Error: FND_FILE Failure [ID 460643.1]

12) In R12 Get APP-FND-01564 ORA-01116 ORA-01110 ORA-27041 Errors When Running Concurrent Requests [ID 577291.1]

13) Performance Degradation When Submitting a Concurrent Request [ID 563434.1]

14) Concurrent Mangagers Down Cause Of Output Post Process Failing To Start [ID
798362.1]

15) How To Create a Java Concurrent Program? [ID 827563.1]

16) How to Troubleshoot Concurrent Manager Start Up Issue Related to Service Manager [ID 735148.1]

17) How To Submit A Concurrent Request Set Using Fnd_Request.Submit_Request [ID 382791.1]

18) How to Solve Concurrent Request Database Performance Issues [ID 863780.1]

19) How To Get Level 12 Trace And FND Debug File For Concurrent Programs [ID 726039.1]

20) How To Print Concurrent Requests in PDF Format [ID 333504.1]

21) When Submitting Create Accounting Program It Completes With Warning "Setting the Profile Option Concurrent: OPP Response Timeout to a Higher Value May Be Necessary" [ID 553320.1]

22) Troubleshooting AutoInvoice: Concurrent Request Failures (Request Status = Error) [ID 1082238.1]

23) How To Create A Custom Concurrent Program With Host Method and Pass Parameters To The Shell Script [ID 266268.1]

24) How to Submit a Concurrent Request With CONCSUB Using a User Other Than APPS [ID 1050415.1]

25) How To Gather Statistics On Oracle Applications 11.5.10(and above) - Concurrent Process,Temp Tables, Manually [ID 419728.1]

26) Where Is 'Delete Data From Temporary Table' Concurrent Program - ICXDLTMP.SQL [ID 397118.1]

27) Error ORA-20100 .tmp Creation For FND_FILE Failed In FA Concurrent Requests [ID 392635.1]

28) Concurrent Requests Fail with FDPSTP failed due to ORA-20100: File o0002712.tmp creation for FND_FILE [ID 148262.1]

29) Concurrent Requests Complete with ORACLE Error 20100 in FDPSTP [ID 157756.1]

30) Ora-01555 Caused By SELECT Statements With Low Query Duration and no Concurrent DML [ID 361992.1]

31) ORA-20100 & ORA-6512 When Running a Concurrent Process [ID 123977.1]

32) How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running. [ID 735119.1]

33) How to Solve Concurrent Request Database Performance Issues [ID 863780.1]

34) How to move Concurrent Processing Server from one node to another node [ID 373611.1]

35) A closer examination of the Concurrent Program Purge Obsolete Workflow Runtime Data [ID 337923.1]

36) Concurrent Managers Will Only Start on One Node in PCP/RAC Environment [ID 264296.1]

37) How to Solve Concurrent Request Database Performance Issues [ID 863780.1]

38) How to Submit a Concurrent Request With CONCSUB Using a User Other Than APPS [ID 1050415.1]

39) How To Create A Custom Concurrent Program With Host Method and Pass Parameters To The Shell Script [ID 266268.1]

40) Troubleshooting AutoInvoice: Concurrent Request Failures (Request Status = Error) [ID 1082238.1]

41) When Submitting Create Accounting Program It Completes With Warning "Setting the Profile Option Concurrent: OPP Response Timeout to a Higher Value May Be Necessary" [ID 553320.1]

Hope this helps !!!!

Notification Mailer Issue - Unable to approve/reject leave/PO using Outlook

Hello All,

Few days back , HRMS Team enabled the functionality to Approve/Reject the Employee's Leave through Mail ; But the User started complaining that the functionality is not working for few employees.

So after analysing the issue, we found out that the employees/managers who approve/reject the leave through "Webmail" were able to approve/reject the leave ; The employees/manager who use Outlook were able to view the Approve / Reject buttons but functionality was not working.

So after drilling down the issue , we have found that the one of the "Template" was not supporting the Outlook Header.
To fix the issue , we changed the Template "Open Notification (Direct Response Parsing)" to "Workflow Open Mail (Direct)" and bounced the Notification Mailer.

Navigation :
Login as Sysadmin > System Administration > OAM > Notification Mailer > Edit > Advanced > Step 4 of 8 > Templates section.

Thanks to metalink... :)

Unable to login into ERP through SSO -- 500 Internal Server Error

Hi All,

Few days back , Users started complaining that they are unable to login into ERP.

Our Current Setup :
When the user enters the credentials , it is authenticated by IDM [with Sun LDAP ] and generates the token and once IDM authenticates , the request is forwarded to OID with the token , OID accepts the token and checks the user accounts in OID.

Error :
When the User enters the login details in SSO page, we used to get token , but also the error "500 Internal Server Error".

Error in ssoServer.log :
[ERROR] AJPRequestHandler-ApplicationServerThread-5 Audit log insertion exception: ORA-01536: space quota exceeded for tablespace 'IAS_META'
ORA-06512: at "ORASSO.WWSSO_AUDIT_LOG", line 248
ORA-06512: at "ORASSO.WWSSO_AUDIT_LOG", line 203
ORA-06512: at line 1



Cause :
The default schema , ORASSO allocated quota on tablespace was full , which didnt allow users to login into ERP.
Unable to execute/trigger the procedure "ORASSO.WWSSO_AUDIT_LOG" .

The ORASSO.WWSSO_AUDIT_LOG_TABLE_T table keeps track of SSO transactions in the Oracle Portal.
The WWSSO_AUDIT_LOG_TABLE_T table is very useful and is owned by the ORASSO user. It contains very important information about the Single Sign On transactions.


Solution:
We checked the Apache log file , it showed us the message of received token , but no error.
So , we changed the quota to unlimited , which resolved the issue.

-Avadhut

Forms runaway processes used to consume the CPU 100%

Hi All,

Here we have Two Application nodes and user requests are controlled by hardware load balancer.

Few days back , the OS team complained us about the CPU 100% utilization by frmweb processes on both the servers.

Issue :
frmweb process are consuming 100%CPU utilization on the Server thereby increasing the load .

viz: On Oracle Applications R12 when checking the top processes on the OS level for the middle tier, you find that forms process (frmweb) almost consumes 100% of the CPU.


Cause :
The root cause of the issue is that returning rows from LOVs in core forms causes the forms process to grow up into memory depending on the number of rows returned.


When an end user login to forms and start working with LOV within core forms sometimes and according to the search criteria that the user will provide to filter the results in LOV, it may fetch huge numbers of records in which causes the frmweb process to grow very large, and in extreme cases this can even lock up the current process or even the whole machine.

So when executing a LOV query, every row is fetched into memory on the middle tier, the frmweb process can get extremely large, and the larger it gets the more likely it is to start paging.


Eventually it starts consuming excessive CPU just paging the process in and out of memory, which is probably what you can see here in this case as the amount of memory consumed when the LOV records are fetched into memory obviously depends on the amount of data in each record.


viz : The forms runaway processes used to consume the CPU %.


Solution:


IMP :: Bring down all the application services.

1- For Forms Variable "FORMS_CATCHTERM" the context vairable name is: "s_forms_catchterm" and you can update the context file located in ($INST_TOP/appl/admin/<SID_HOSTNAME.xml>)

2- For other forms variable "FORMS_RECORD_GROUP_MAX" there is no variable defined in Autoconfig for that one and have to customize the autoconfig for the forms variables to set that environment as following:


a- Go to the autoconfig Template folder:


$cd $AD_TOP/admin/template


b- Create new directory named (custom)


$ mkdir custom


c- Make sure that new directory has same file permissions as ($AD_TOP/admin/template)


d- Copy the following autoconfig template to the new custom directory:


$cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/admin/template/custom/APPLSYS_ux.env


e- Edit the file copied file under custom directory and add the following 2 lines at the end of section:

####################################
# Oracle Forms environment variables
####################################

FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX

f- Save and exit from the file.


g- Next time autoconfig run, it will read the custom directory and check for any customizations there.


3. Run Autoconfig on the middle tier and make sure it is completed successfully.


4. Startup all services.

Monitor the CPU utilization on the server.

In our case ; we performed above mentioned Action plan on one server at a time; So no downtime is required ....... HURRAY !!! ;)






Refer : Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12 [ID 745711.1]

[ Issue ] FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file

Error :
**Starts**14-SEP-2009 16:45:36
ORACLE error 20100 in FDPSTP
FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, oklslagl.tmp in the directory, [/usr/tmp].
You will find more information in the request log.
ORA-06512: at "APPS.FND_FILE", line 417

Cause :
Unable to create the [filename].tmp in [/usr/tmp] directory.

Solution :
>> Login into the Database Node.
>> Check for the init[SID].ora file
>> Check whether we have  [/usr/tmp] listed in the utl_file_dir parameter
>> OR check for the permissions on [/usr/tmp]
>> Check whether the file named "oklslagl.tmp" already exist in [ /usr/tmp ]
If yes , Then either remove them or move them to some other temporary directory.

[ Read Me ] Concurrent Processing / System Administration Scripts

213021.1 Concurrent Processing (CP) / APPS Reporting Scripts


1] CMCLEAN.sql >> Non Destructive Script to Clean Concurrent Manager Tables
2] How To Re-create Concurrent Manager Views
3] CCM.sql >> Concurrent Manager Check Script
4] FNDCCMDiagnostic115.sh >> Concurrent Manager Check Script
5] ICMLOG.sql >> Internal Manager Log Script
6] REQCHECK.sql >> Concurrent Request Check Script
7] ANALYZEPENDING.sql >> Analyze Pending Requests Script
8] ANALYZEREQ.sql >> Analyze Request Script
9] WHOCANRUN.sql >> Who Can Run which Requests
10] FNDCCMDiagnostic115.sh >> Concurrent Request Diagnostic Script
11] bde_request.sql >> Concurrent Request Diagnostic Script
12] FNDPrinterValidation115.sh >> Printing Script
13] FNDValidateFNDFS115.sh >> Report Review Agent Script
14] CMLOGS.txt >> Concurrent Request Log Script


214088.1 Oracle Applications System Administration Scripts

1] Performance Troubleshooting Chart
2] Locks for given Session ID >> bde_session_locks.sql
3] Validates and Rebuilds Fragmentated Indexes >> bde_rebuild.sql
4] Analyze Indexes to determine Fragmentation >> bde_analyze_indexes.sql
5] Current, Required and Recommended Apps 11i init.ora params >> bde_chk_cbo.sql
6] Simple Explain Plan for given SQL Statement >> bde_x.sql
7] Expensive SQL and Resources Utilization for a Given Session ID >> bde_session.sql
8] Verifies Statistics for all Installed Apps Modules 11.5 >> bde_last_analyzed.sql
9] Changes CBO Stats Selectivity for a Given Index and Column >> bde_chg_stats.sql
10] Clone Views Across Instances for SQL Tuning Exercises >> coe_view.sql
11] Trace Apps Online Transactions with Event 10046 >> coe_trace.sql
12] Automate CBO Stats Gathering >> coe_stats.sql
13] Top 10 Expensive SQL from SQL Area >> coe_sqlarea.sql
14] SQL Tracing online transactions using Event 10046 >> coe_event_10046.sql
15] Session and Serial# for Locked Rows >> coe_locks.sql
16] Import CBO Stats from COE_STATTAB_XYZ >> coe_import_stattab.sql
17] Enhanced Explain Plan for given SQL Statement >> SQLTXPLAIN.sql
18] Active Users executing FORMs or Conc Programs >> FNDFindActiveUsers115.sql
19] Enhanced Explain Plan for given SQL Statement >> bde_system_event_10046.sql , TRCANLZR.sql
 
Please refer the mentioned Metalink note-ids and download the scripts

[Issue] ora-00600 : internal error code, arguments: [kkdogty01], [], []

Hello All,

During our daily health check , we found ora-00600 error in our alert.log

Error:

Errors in file /m02/oracle/db/tech_st/10.2.0/admin/PROD_server1/udump/prod_ora_8960.trc:
ORA-00600: internal error code, arguments: [kkdogty01], [], [], [], [], [], [], []


Cause:

The error means that the function kkdogty got a null pointer instead of a proper pointer to the object OID.
If a Third Party tool is being used, bad connection can cause the error.
In our case, TOAD was used to run a SQL which produced the error.


Solution:

If a Third Party tool is used, stop all third party connections and
check if problem is reproducible from a SQL*Plus session.

If this error can be reproduced using SQLPLUS, then please log a Service Request
and upload the related trace files to Oracle Support Service for further analysis.
Otherwise, check configuration and usage of third party application.

Refer : 279869.1 [ Metalink Note-ID ]

[ How To ] Change The Password For The SYSADMIN User -- 11.5.10.2

1. Keep all services running.
2. Login via the OS level by way of the applmgr user.
3. Run your environment scripts
a. cd $APPL_TOP
b. run APPSORA.env
c. the above should also run _.env, but you can verify by running it.
d. cd admin
e. run adovars.env
4. cd $FND_TOP/bin
5. Run FNDCPASS to change the SYSADMIN password (IMPORTANT: Change ONLY the SYSADMIN password)
FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN
6. Restart the Apache, Forms, Reports, 8.0.6 listener and concurrent manager services.
7. To test, login into the applications as the SYSADMIN user and the new password.
8. Verify that a concurrent program such as Active Users runs.

Refer : 423274.1 -- Metalink Note-ID

[Read Me] What happens when we execute COMMIT statement

1] When we say commit; all that happens physically is that LGWR flushes the log buffer to Disk.
DBWR does absolutely nothing.
2] To make a transaction durable, all that is necessary the changes that make up the transaction be on disk; there is no need whatsoever for the actual data to be on the Disk.
3] LGWR writes in very nearly real time, virtually all the transactions changes are on disk already.
4] When LGWR process is ON, the session hangs till it completes the process.
5] After the process is completed , session is then free to continue and from then on all other sessions will no longer be directed to the undo blocks when they address the changed table, unless the principle of consistency requires it.


Note : In normal running , DBWR writes only a few dirty only a few dirty buffers to disk ; when a check point is signaled it writes all dirty buffers to disk.

[Read Me] What happens when we execute ROLLBACK statement

1] If the session that initiated the transaction fails {n/w down , user process reboots} – PMON will rollback.
2] If the Server reboots then on startup SMON will detect and rollback.
3] Mechanism:
Update: The pre-update version is the columns are copied from the undo block to table blocks.
4] Insert: Oracle retrieves the rowid of the inserted row from the undo block and uses it as the key for a delete on a table.
5] Delete: Oracle constructs a complete insert statement from the data in the undo block.
Note: A Rollback will itself generate more redo as it executes , perhaps rather more than the original statement.

[Read Me] What happens when we execute INSERT and DELETE statements

1] Insert and Delete are managed in the same fashion as an Update. Redo generation is exactly the same: all the changes to be made to data and undo blocks are first written out to the log buffer.
2] The difference is in the amount of Undo generated. When a row is inserted, the only Undo generated consists of writing out of the new rowid to the undo block. This is because to rollback an Insert the only information Oracle requires is the rowid.
For DELETE: The whole row is written to the undo block, so that the deletion can be rolled back if need by inserting the complete row back into the table.

[Read Me] What happens when we execute UPDATE statement

Hello All,
I was just curious about what exactly happens when we execute "Update" statement.... I found the answer in one of the Oracle Fundamentals ...
Here we go...
1] For any DML operation ; it is necessary to work on both data blocks and undo blocks and also regenerate redo A , C & I of the ACID test require generation of UNDO ; the UNDO segment is just another segment.
2] First step is same as the execution of select command {only diff is empty block of UNDO segment is required}
3] First , locks must be placed on any rows and associated index keys that will be affected by the operation.
4] Then the redo is regenerated: the server process writes to the log buffer the changes that are going to be applied to the data blocks.
5] This generation of redo Is applied to both table block changes and undo block changes : if a column is to be updated the new value of the column is written to the log buffer { which is the change that will be applied to the table block } and also the old value { which is the change that will be applied to the undo block}.
6] If the column I part of an index key , then the changes to be applied to the index are also written to the log buffer , together with an undo block change to protect the index changes.
7] Having generated the redo, the update is carried out in the DB buffer cache : the block of the table is updated with the new version of the changed column and the old version of the changed column is written to the block of an undo segment.
8] From this point until the update is committed, all queries from other sessions addressing the changed row will be redirected to the undo data.
9] Only the session this is doing the update will see the actual current version of the row in the table block. The same principle applies to any associated index changes.

[ How To ] Reset / Unlock the orcladmin password

Recently while upgrading our Discoverer to 10.1.2.3 [ +CP4 ] , we encountered / or got stuck in one of the step.
One of the step asks for orcladmin password which was not known to us.
So , we need to reset that password :
Here are the steps which we followed ;
Error:
orcladmin password lost or account locked
Cause:
orcladmin password not known to us.
Solution :
Step 1] Login as OAS user [ here testbi ] and
sqlplus "/as sysdba"
SQL>> alter user ODS identified by [new password]
Step 2] Execute $ORACLE_HOME/bin/oidpasswd create_wallet=true
Step 3] oidpasswd connect=[connect string] change_oiddb_pwd=true
This will prompt us for old password and new password.
old password you can give the same as ODS user and unique new password.
Step 4] To unlock the orcladmin account :
oidpasswd connect=[connect string] unlock_su_acct=trueThis will prompt for password ; enter the ODS password.Which will inturn unlock your orcladmin account.
Step 5] To reset the password :
oidpasswd connect=[connect string] reset_su_password=true
This will prompt for new password. [keep the password same as ODS user.]
and your are done.
You have successfully changed the orcladmin password.
Now, to test the password :
cd $ORACLE_HOME/bin/oidadmin
The window will prompt for orcladmin password.
Refer : http://eldapo.blogspot.com/2007/08/forcing-orcladmin-password.html

[ Issue ] Concurrent Manager startup errors out

Hello All,
Few days back , we encountered an error while starting Concurrent Managers ;
Here is the log file [segment]
Error :
=========================================================
Check that your system has enough resources to start a concurrent manager process. Contact your system administ : 18-AUG-2009 13:44:33
Starting PASMGR Concurrent Manager : 18-AUG-2009 13:44:33
CONC-SM TNS FAIL
Routine AFPEIM encountered an error while starting concurrent manager PASMGR with library /m03/DEV/apps/apps_st/appl/pa/12.0.0/bin/PALIBR.
Check that your system has enough resources to start a concurrent manager process. Contact your system administrato : 18-AUG-2009 13:44:33
Process monitor session ended : 18-AUG-2009 13:44:33
Process monitor session started : 18-AUG-2009 13:46:33
Could not contact Service Manager FNDSM_APPSDBA1_DEV. The TNS alias could not be located, the listener process on APPSDBA1 could not be contacted, or the listener failed to spawn the Service Manager process.
Could not contact Service Manager FNDSM_APPSDBA_DEV. The TNS alias could not be located, the listener process on APPSDBA could not be contacted, or the listener failed to spawn the Service Manager process.
Starting RCVOLTM Concurrent Manager : 18-AUG-2009 13:46:33CONC-SM TNS FAILRoutine AFPEIM encountered an error while starting concurrent manager RCVOLTM with library /u03/DEV/apps/apps_st/appl/po/12.0.0/bin/RCVOLTM.
Check that your system has enough resources to start a concurrent manager process.
Contact your system administra : 18-AUG-2009 13:46:33
Starting PODAMGR Concurrent Manager : 18-AUG-2009 13:46:33CONC-SM TNS FAIL
=========================================================

Cause :
Some of the libraries of FNDSM/FNDLIBR must be missing or corrupted.

Solution :
1] Shutdown all the services.
2] Log in as applmgrcd to $FND_TOP/patch/115/sql
Run the script: afdcm037.sql
3] Relink FNDSM and FNDLIBR executables as mentioned below:
$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"
4] Run cmclean.sql
5] Start up the Services and retest.

Refer :
Metalink note-id : 460578.1 , 555081.1

[ How To ] Validate DBC file in Oracle Applications

This command will validate your DBC file , incase you encounter issues like " Database Connection Fails with Java Exception "
Cmd :
[appldev@appsdba install]$ $IAS_ORACLE_HOME/appsutil/jdk/jre/bin/java oracle.apps.fnd.security.AdminAppServer apps/apps STATUS DBC=$FND_SECURE/DEV.dbc
Database Server
---------------
DATABASE_ID: DEV
AUTHENTICATION: OFF

Application Server
------------------
APPL_SERVER_STATUS: VALID
APPL_SERVER_ID: 6D8F64B74A528627E040E00A887E308773406321633472009781846406505215
[appldev@appsdba install]$


Other metalink note-ids which can be referred for DBC:
150475.1 Database Connection Fails with Java Exception when Running adgendbc.sh.
160291.1 How to Use the AdminAppServer Utility To Delete a DBC File in
Oracle Application Object Library 11.0 & 10.7N for Windows.
144581.1 How to Use the AdminAppServer Utility To Update a DBC File in Oracle Application Object Library 11.0 & 10.7N for Unix
160295.1
146402.1 How to Create a Database Connection (DBC) File From the template.dbc File
in Release 10.7N & 11
821037.1 reventing access via "/forms/frmservlet/" in Release 12.
270802.1 adgendbc.sh not updating Fnd_application_servers
168228.1 java.sql.SQLException Running adgendbc.sh
458282.1 Context Variables
353605.1 DBC File Naming Conventions for Discoverer Connections to Oracle Applications 11i and 12i