Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, July 31, 2012

Oracle Dynamic sampling and impact on optimizer

what is dynamic sampling:

Dynamic sampling (DS) was introduced in Oracle Database 9i Release 2 to improve the optimizer's ability to generate good execution plans. The most common misconception is that DS can be used as a substitute for optimizer statistics. The goal of DS is to augment the optimizer statistics; it is used when regular statistics are not sufficient to get good quality cardinality estimates.

So how and when will DS be use? During the compilation of a SQL statement, the optimizer decides whether to use DS or not by considering whether the available statistics are sufficient to generate a good execution plan. If the available statistics are not enough, dynamic sampling will be used. It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan. For the case where one or more of the tables in the query does not have statistics, DS is used by the optimizer to gather basic statistics on these tables before optimizing the statement. The statistics gathered in this case are not as high a quality or as complete as the statistics gathered using the DBMS_STATS package. This trade off is made to limit the impact on the compile time of the statement.

The second scenario where DS is used is when the statement contains a complex predicate expression and extended statistics are not available. Extended statistics were introduced in Oracle Database 11g Release 1 with the goal to help the optimizer get good quality cardinality estimates for complex predicate expressions. For example, if you had a simple query that has where clause predicates on two correlated columns, standard statistics would not be sufficient.

source:
https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer
 

Saturday, July 28, 2012

Exachk Error RC-002- Unable to read driver files. Please report this error to your Oracle representative for action.

>sh exachk -a

Error RC-002- Unable to read driver files. Please report this error to your Oracle representative for action.

Exiting .....



  While running exacheck I got the above error and there is no document as such that explains why I got this error message.

I gave another shot at the exacheck user guide and it says it is preferable to copy/use the exacheck script from Database node-1 $HOME directory of the oracle software installation owner (oracle user) i.e /home/oracle  ( remember it is mentioned in the doc as a preference and not mandatory )  anyway I copied back the exacheck.zip from dbfs mount to home directory unzipped it and ran the exacheck from there..bingo it picked up the driver files this time...


exa-host1(DBSID1)  /home/oracle
>exachk -a

CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /u01/app/11.2.0.3/grid?[y/n][y]




Checking ssh user equivalency settings on all nodes in cluster

Node ex01host2 is configured for ssh user equivalency for oracle user

Node ex01host3 is configured for ssh user equivalency for oracle user

Node ex01host4 is configured for ssh user equivalency for oracle user



Searching for running databases . . . . .

. . . . .
List of running databases registered in OCR
1. DBAINST
2. DBABNST
3. DBCINST
4. All of above
5. None of above

Select databases from list for checking best practices. For multiple databases, select 4 for All or comma separated number like 1,2 etc [1-5][4].4



Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
-------------------------------------------------------------------------------------------------------
                                                 Oracle Stack Status
-------------------------------------------------------------------------------------------------------
Host Name  CRS Installed  ASM HOME       RDBMS Installed  CRS UP    ASM UP    RDBMS UP  DB Instance Name
-------------------------------------------------------------------------------------------------------
ex01host1      Yes             Yes             Yes             Yes        Yes      Yes      DBAINST1 DBBINST1 DBCINST1
ex01host2      Yes             Yes             Yes             Yes        Yes      Yes      DBAINST2 DBBINST2 DBCINST2
ex01host3      Yes             Yes             Yes             Yes        Yes      Yes      DBAINST3 DBBINST3 DBCINST3
ex01host4      Yes             Yes             Yes             Yes        Yes      Yes      DBAINST4 DBBINST4 DBCINST4
-------------------------------------------------------------------------------------------------------

root user equivalence is not setup between ex01host1 and STORAGE SERVER cellhost1.

1. Enter 1 if you will enter root password for each STORAGE SERVER when prompted.

2. Enter 2 to exit and configure root user equivalence manually and re-run exachk.

3. Enter 3 to skip checking best practices on STORAGE SERVER.

Please indicate your selection from one of the above options[1-3][1]:-


Please indicate your selection from one of the above options[1-3][1]:- 1


Is root password same on all STORAGE SERVER?[y/n][y]


Enter root password for STORAGE SERVER :-


86 of the included audit checks require root privileged data collection on DATABASE SERVER. If sudo is not configured or the root password is not available, audit checks which  require root privileged data collection can be skipped.


1. Enter 1 if you will enter root password for each on DATABASE SERVER host when prompted

2. Enter 2 if you have sudo configured for oracle user to execute root_exachk.sh script on DATABASE SERVER

3. Enter 3 to skip the root privileged collections on DATABASE SERVER

4. Enter 4 to exit and work with the SA to configure sudo on DATABASE SERVER or to arrange for root access and run the tool later.

Please indicate your selection from one of the above options[1-4][1]:-


Please indicate your selection from one of the above options[1-4][1]:- 1


Is root password same on all compute nodes?[y/n][y]


Enter root password on DATABASE SERVER:-


9 of the included audit checks require root privileged data collection on INFINIBAND SWITCH .


1. Enter 1 if you will enter root password for each INFINIBAND SWITCH when prompted

2. Enter 2 to exit and to arrange for root access and run the exachk later.

3. Enter 3 to skip checking best practices on INFINIBAND SWITCH

Please indicate your selection from one of the above options[1-3][1]:-


Is root password same on all INFINIBAND SWITCH ?[y/n][y]


Enter root password for INFINIBAND SWITCH :-


*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) ***


Log file for collections and audit checks are at
/home/oracle/exachk_072712_160651/exachk.log



Thursday, July 26, 2012

ORA-15306: ASM password file update failed on at least one node

Error:
ORA-15306: ASM password file update failed on at least one node

Error by itself is very misleading someone would think the password might have messed up or got corrupted or missing.

In my case none of the above were true.

When I tried this sql i.e trying to alter the password of a user on the ASM instance.

SQL> alter user dbsnmp identified by test;
alter user dbsnmp identified by test.
                                *
ERROR at line 1:
ORA-15306: ASM password file update failed on at least one node




SQL> show parameter remote_login_passwordfile

NAME                                 VALUE
-------------------------         ------------
remote_login_passwordfile            EXCLUSIVE


password file parameter is set properly

My password file exists on all nodes and in fact I can connect  as sysasm to the instance so I know it is not the password file issue then what?

Here my problem was the user doesn't exists in the ASM to begin with..
SQL> select * from v$pwfile_users;

USERNAME      SYSDBA          SYSOPER         SYSASM
--------------------------------------------------------------------------------
SYS              TRUE            TRUE            FALSE

I don't have the user in the database for which  I am trying to change the password for.

Now go ahead and create the user in the ASM database.


SQL> create user dbsnmp identified by test;

User created.


SQL>grant sysdba to dbsnmp;



SQL> select * from v$pwfile_users;

USERNAME      SYSDBA          SYSOPER         SYSASM
--------------------------------------------------------------------------------
SYS                  TRUE            TRUE            FALSE
DBSNMP           TRUE            TRUE            TRUE


Monday, July 23, 2012

ORA-20100: Error: FND_FILE failure. Unable to create file




I was getting the below error during the dashboard collection.

ORA-20100: Error: FND_FILE failure. Unable to create file


 

APPLTMP and APPLPTMP are sourced and referenced to the correct locations with all the write  permissions and no files with the same name exists.


When I try to create the log file manually from the database:

SQL> conn apps/
Connected.
SQL> exec FND_FILE.PUT(FND_FILE.LOG,'shaik.log');
BEGIN FND_FILE.PUT(FND_FILE.LOG,'shaik.log'); END;

*
ERROR at line 1:
ORA-20100: Error: FND_FILE failure. Unable to create file, o0169751.tmp in the
directory, /database_tempfile/appltmp.
You will find more information in the request log.
ORA-06512: at "APPS.FND_FILE", line 417
ORA-06512: at "APPS.FND_FILE", line 456
ORA-06512: at line 1





Solution:
UTL_FILE was not set properly i.e it was defined as a single path location hence was not working.

SQL> alter system set utl_file_dir='/database_tempfile/appltmp','/usr/tmp'  scope=spfile sid='*';

System altered.

Tried again:

SQL> conn apps/
Connected.
SQL> exec FND_FILE.PUT(FND_FILE.LOG,'shaik.log');

PL/SQL procedure successfully completed.

SQL> exit





460643.1

System Hold Fix Manager before resetting counters


After migrating the environment to Exadata I was getting the below errors for the CM's


System Hold, Fix Manager before resetting counters



Solution:

1) Shutdown the Apps Tier cleanly make sure there are no orphan sessions.
2) go to cd $FND_TOP/bin
run the below commands:
$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDFS"
$ adrelink.sh force=y link_debug=y "fnd FNDCRM"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"


Check relink logs to make sure the relinks completes suuscessfully.
Ex:
adrelink is exiting with status 0

End of adrelink session
Date/time is  Mon Jul 23 10:50:24 EDT 2012
**********************************************************

Line-wrapping log file for readability ...
Done line-wrapping log file.

Original copy is  /apps/apps_st/appl/admin/log/adrelink.lsv
New copy is /apps/apps_st/appl/admin/log/adrelink.log



3) After running the relinks run the cmclean.sql to flush out the status of the CM's..

SQL> conn apps/
Connected.
SQL> @cmclean.sql
DOC>

-----------------------------------------------------------------------
Updates complete.
Type commit now to commit these updates, or rollback to cancel.
-----------------------------------------------------------------------

SQL> commit;

Commit complete.

4) Now Restart the Apps teir:

Now CM's came back online without any issue.







Friday, July 20, 2012

APP-FND-0156 Cause: AFPCOA failed due to ORA-28000: the account is locked


APP-FND-01564: ORACLE error 28000 in AFPCOA

Cause: AFPCOA failed due to ORA-28000: the account is locked

Solution:
Apps account is locked please unlock the apps account and if needed increase the profile option FAILED_LOGIN_ATTEMPTS in the database if/to appropriate.

conn system/passwd
select username,status from dba_users where username='APPS'

APPS  locked

SQL> Alter username apps account unlock;

change failed_login_attempts:

SQL> alter profile default limit failed_login_attempts 10;

Profile altered.

SQL> exit

R12 FNDCPASS change APPS SYSADMIN passwords

Change the APPS password:

changing APPLSYS password changes the APPS password:

applmgr@[unixhost]-> FNDCPASS apps/apps 0 Y system/PASS11445 SYSTEM APPLSYS PASS12011
Log filename : L3306801.log


Report filename : O3306801.out
applmgr@[unixhost]->



Change the SYSADMIN password:


applmgr@[unixhost]-> FNDCPASS apps/PASS12011 0 Y system/PASS11445 USER SYSADMIN PASSs54lpRd
Log filename : L3306805.log


Report filename : O3306805.out


Run the autoconfig on all the nodes:

applmgr@[unixhost]-> adautocfg.sh
Enter the APPS user password:

The log file for this session is located at: /product/app/ENV_NAME/inst/apps/ENV_NAMEX1_unixhost/admin/log/07201546/adconfig.log

AutoConfig is configuring the Applications environment...

Thursday, July 19, 2012

R12 recompile jsp

R12 webpage redirects to a blank page :

Ex:
http://myvip.domain.com
redirects to the below blank page
http://myvip.domain.com/OA_HTML/AppsLocalLogin.jsp

Issue missing _RF pages

Solution:
Recopile jsps:

 cd $FND_TOP/patch/115/bin
HOSTNAME> perl ojspCompile.pl --compile --flush -p 2

logfile set: PATH/logs/appl/rgf/ojsp/ojspc_error.log
starting...(compiling all)
using 10i internal ojsp ver: 10
synchronizing dependency file:
  loading deplist...8073
  enumerating jsps...8073
  updating dependency...0
initializing compilation:
  eliminating children...6004 (-2069)
translating and compiling:
  translating jsps...6004/6004 in 2m20s
  compiling jsps... 56% complete: 3400/6004 ETA: 4m41s

Reference:
Blank Page Accessing R12 - 'Missing class: _RF' in OACore application.log [ID 467562.1]

Wednesday, July 18, 2012

Putty CM putty cm error object reference not set to an instance of an object

Where ever you click on the Putty CM you will get the error as :
 object reference not set to an instance of an object
and in the log file no useful info,

Was very annoyed bu these errors, tried recopying, deleting the current version so on...
Finally ---
Fix:
Under the options --> select the "show tray icon " --> Select "Hide when minimized" 


Tuesday, July 10, 2012

view / show Compilation error messages

What if you too many invalid objects how do get the errors messages for these objects




 
run the query against dba_errors and the text column holds the error message.






run the utlrp.sql to compile all the objects


SQL> @?/rdbms/admin/utlrp.sql

see it made any difference  if not then view the error messages from above and start troubleshooting..

Monday, July 9, 2012

OBIEE --Presentation services No Log Found

User was not able to view the contents of the Log from OBIEE presentation services.
i.e

Re producing the issue:In OBIEE.



under administrattion:

go to Mange Sessions:




 click on Manage sessions Then click on view log.



You got the error message:



Solution:

Make sure logging is turned on for the user. i.e >2 or more.





Now bounce the OBIEE services and you should be able to view the logs now.






2)
If you still have the issue then verify the session variable " Log Level"  is not set with any default value like "0"

see below:




If yes then
Remove this default value and try again by bouncing the OBIEE services.

3) If you still have issues make sure there are not custom initialization blocks defined to block the logging.

see example  below:





Remove this and bounce the OBIEE. You should be all set ..


Reference:
MOS:817010.1