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.
Wednesday, September 23, 2009
Tuesday, September 22, 2009
[ 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
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
Friday, September 18, 2009
[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 ]
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 ]
Monday, September 14, 2009
[ 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
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
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
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.
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.
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.
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.
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.
Wednesday, September 9, 2009
[ 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
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
Subscribe to:
Posts (Atom)