Tuesday, April 30, 2013

Unable to boot Oracle Linux 5 on HP DL380/DL360 Gen8


Problem: After installation of oel 5.8 64 bit linux, while booting for the first time following occur.
setuproot: error mounting /proc: No such file or directory
setuproot: error mounting /sys: No such file or directory
Analysis:This problem looks just like the problem described in the following document:
HP ProLiant DL360 G7 Server Does Not Boot With UEK Kernel 2.6.32-100.28.9.El5 [ID 1310016.1])


For displaying the detailed log:
1) Select the 2.6.32-300.10.1.el5uek entry and enter "e" to edit it
2) Highlight the line beginning "kernel vmlinuz-2.6.32...." and enter "e" again to edit it
3) Move the cursor to the end of the line and remove the parameters "rhgb quiet" and press <enter>
4) Press "b" to boot
5) Capture the error log if it is any more verbose than you already provided.
(Note That:
rhgb = redhat graphical boot - This is a GUI mode booting screen with most of the information hidden.
quiet = hides the majority of boot messages before rhgb starts.)


Problem Description : Bug
Workaround : Using base kernel.. (Oracle Linux contains two kernels , uek and base. There is no booting problem with base kernel.)

"Solution: Kernel Upgrade -> to 2.6.32-300.39.2.el5uek"
Boot with base kernel, upgrade uek with rpm, check and configure(if necessary) the grub.conf , and reboot.

Sunday, April 28, 2013

Oracle E-Business Suite R12 on Exadata V2 Sun Oracle Database Machine -- Implemented case study

Scope:
EBS installation using rapidwiz  11G 11.1.0.7 single database with EBS 12.1.1
Upgrade 11.1.0.7 to 11.2.0.2 ( scan supported release)
11.2.0.2 database patches for Ebs
Asm/Rac conversion single instance to Exadata Asm.
Ebusiness Suite Patches to support scan.
Ebusiness Suite config for Rac and scan.
Paralel Concurrent Processing configuration.
Technical Testing
Data Load
Functional Testing
Benchmarks

Installation Process

Target Specs

Target Specifications:
Oracle E-Business Suite R12 (12.1) running on Oracle Database 11gR2 2 node ASM/RAC (Exadata) .
Detailed Specification
DB servers(x2) : 
Oracle Enterprise Linux 5.5 64 bit96 Gb Physical Ram30 Gb Sga, 10 Gb Pga
Scan configured.
Application Servers(x2) : 
Oracle Enterprise Linux 5.5 64 bitGb Physical Ram 
Node 1: AdminConcurrent, Web, Forms
Node 2: Concurrent,Web,Forms

EBS Architecture on Exadata


Monday, April 22, 2013

OPTIMIZER_USE_PENDING_STATISTICS -- 11g Feature


In the previous releases, gathered statistics are immediately placed into dictionary tables, and started to be used by the optimizer.
Pending_statistics option can be used to place those statistics into pending tables. Thus new statistics can be tested by the sessions which has optimizer_use_pending_statistics set to TRUE.
If the new statistics are found useful, they can be placed on the dictionary tables.
By this method, there is an opportunity to prevent plan regressions that result from incomplete or incorrect statistics.

Pending statistics can be viewed from the USER_%_PENDING_STATS dictionary views.

Pending statistics can be collected by the dbms_stats.set_table_prefs as follows;

--First, set publish option for the table to FALSE;
DBMS_STATS.SET_TABLE_PREFS(‘SCHEMA NAME’,’TABLE_NAME’,‘PUBLISH’,’FALSE’);
--and then collect statistics;
DBMS_STATS.GATHER_TABLE_STATS(‘SCHEMA_NAME’,’TABLE_NAME’);

By this way, collected statistics for the TABLE_NAME will be not published.

Pending statistics can be published by the dbms_stats.publish_pending_stats  as follows;

DBMS_STATS.PUBLISH_PENDING_STATS(‘SCHEMA NAME’,’TABLE_NAME’);

I found this feature very useful, because sometimes you can't anticipate the results of  gathering statistics..

Described above, also shows the new capabilities of DBMS_STATS package on 11g.
DBMS_STATS package has been the most efficient way for gathering statistics since Oracle 8i . Oracle 11g introduces DBMS_STATS.SET_*_PREFS procedure which offers much granularity of control. (DBMS_STATS.SET_PARAM has deprecated..)



Sunday, April 21, 2013

Self-Developed Applications -> Db Sniper, Limiter and Steady Backup

Added Self Developed Applications... 

I developed several applications in my career. In this page you will find some applications I developed. Actually these are my favorites, as they have been used  in Production Systems.

As a dba and system engineer, sometimes application development was a part of my job. But most of the time, I have been developed the applications (system applications) for extending my job and gaining a developer perspective...

Note that , I have a great interest in Operating System kernel, but so far I couldnt have much time to get into it...

Thursday, April 18, 2013

Override address in R12 Notification Mailer

Override address is set to sent all the notification mails to a certain mail address..
If override address is set to a valid email, notification mailer will send all the mails to the address specified in override address.

To set override address and put it into use; a certain process should be followed.

First; overried address is set through the following steps,

*Log into Oracle Applications Manager
*Navigate: Site Map / Administration tab / Workflow section / Notification Mailer / View Details / Set      Override Address Button
*Give a valid Email Address / Submit

When pressed submit button; notification mailer will send a verification mail to the override address set in Noticatin Mailer properties.  This is for verification..
The verification code in the verification email must be entered in order to activate override address functionality.

Tip: In my opinion; override address can be used in test,dev,lab,preprod,uat environments for generally test purposes.

EBS R12 frmcmp > FRM-91500: Unable to start/complete the build

This error is produced even with command frmcmp help=y..
This is normal as a X server is needed for running frmcmp..
Use Vnc or run frmcmp directly from console..

Wednesday, April 17, 2013

EBS R12 FORMS error-- Internet Explorer has modified this page to prevent cross-site scripting--

Internet Explorer user can encounter this error, when launching Forms in EBS environments.
This error seems related with a deployment problem with Forms.(bug: 8608695) Redeploying the Forms 10.1.2.3 formsapp.ear file fixes the issue.

Bug 8608695 details:


Whenever you apply a patch set over the basic version (10.1.2.0.2), the form 
servlet is not re-deployed.
This causes a major problem in case some corrections are done at the servlet 
side. Not re-deploying the form servlet, the correction won't be taken into 
account.


Here are the steps for the solution: (reference : Oracle Support)

1)Make a backup copy of the original Forms system-jazn-data.xml.
This file is found in $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml

2=Change oc4jadmin password if the password is unknown [ per Note 397174.1 ]

3)Execute $FND_TOP/bin/txkrun.pl -script=CfgOC4JApp

         Enter Application name for re-deployment ? forms
         Enter Oc4j Instance password for re-deployment ? welcome (or current password)
         Run Autoconfig <Yes/No> ? No 

4)Run AutoConfig on the instance by running the command: $ADMIN_SCRIPTS_HOME/adautocfg.sh 

ORA-12516 and ORA-12520

ORA-12516 -> check lsnrctl status and services to make sure the instances are registered..

lsnrctl status;
lsnrctl services;

Make sure the service you want to connect (for exampla PROD1) is not a blocked state (state:blocked)


ORA-12520 -> Process limit reached.
Check the process values with the following sql;

select * from v$resource_limit where resource_name in ('processes','sessions');



For RAC.

Check lsnrctl services , and lsnrctl status and also check process limit..
If found any problems in service registration, check local listener of the rac nodes.(sqlplus > show parameter local_listener)
Local listener should be set to vip addresses..
For instance 1 Local_listener Should be like;

local_listener       string      (ADDRESS=(PROTOCOL=tcp)(HOST=erm1-vip)(PORT=1521))




Tuesday, April 16, 2013

Direct Path insert -- Reused Space and Referential Integrity

1) With Insert append, free space is not reused.  Data will be inserted in append mode. (above the HWM) After the commit; HWM is updated to the new value .. -->  requires more space than conventional-path 
2) Refrential integrity constraint can not be maintained..

It seems these 2 things makes direct load inserts generate less redo and undo than conventional path inserts..

Ref: docs.oracle

During direct-path INSERT, you can disable the logging of redo and undo entries. Conventional insert operations, in contrast, must always log such entries, because those operations reuse free space and maintain referential integrity.

In addition, It seems "insert /*+append*/ " can be ignored by oracle , if the underlying table has referential integrity or a trigger.

Ref: Ask Tom.


what it seems to neglect to say in that old documentation is that.... 
insert /*+ append */ will ignore the append hint and use conventional path loading when the table has referential integrity or a trigger...

sqlldr can and does ignore triggers/referential integrity and even uniqueness during a direct path load, insert /*+ append */ does not - you are not using direct path when they (constraints) exist. 

parallel is always a direct path, if you go parallel, you will be appending. 





DBMS_SHARED_POOL.PURGE

Whitout purging the entire shared pool, single object can be purged from shared pool using DBMS_SHARED_POOL.PURGE.

DBMS_SHARED_POOL.PURGE take 3 arguments.. Name, Flag and Heaps.

Name => select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '%SQL ID...%'

Flag=>

(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.
Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.
Set to 'T' or 't' to specify that the input is the name of a type.
Set to 'R' or 'r' to specify that the input is the name of a trigger.
Set to 'Q' or 'q' to specify that the input is the name of a sequence.
In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.

Heaps => default seems suitable..

Default is 1, that is, heap 0 which means the whole object would be purged

Monday, April 15, 2013

EBS LISTENER PORT INFORMATION STORED IN FND_TNS_LISTENER_PORTS

FND_TNS_LISTENER_PORTS table stores the listener information.
I didnt trace it , but in my opinion, apps tier autoconfig queries this table, and retrieves the listener port.
Especially s_apps_jdbc_connect_descriptor in CONTEXT_FILE is updated by autoconfig using FND_TNS_LISTENER_PORTS table .
s_apps_jdbc_connect_descriptor variable is not related with port pool defined in apps tier CONTEXT_FILE.
This explains s_apps_jdbc_connect_descriptor related issues like "this parameter is not updated even after apps tier autoconfig and etc..), and the solutions of this issues; run db tier autoconfig first.

In brief ( In my opinion);

Db tier autoconfig updates FND_TNS_LISTENER_PORTS with the listener port (from db tier context file).
Apps tier autoconfig read this table and updates its context file(s_apps_jdbc_connect_descriptor), and make configurations..



EBS - Update Context File command line

When logged in as application owner, and set the environment.

Context file can be updated with  oracle.apps.ad.context.UpdateContext class.

Usage:

java  oracle.apps.ad.context.UpdateContext {CONTEXT_FILE_FULL_PATH} {CONTEXT_VARIABLE_NAME} {DESIRED VALUE}


Example

applmgr>  java oracle.apps.ad.context.UpdateContext $CONTEXT_FILE s_dbport 1522

Investigating Query Rewrite Materialized View issues


Why Oracle does not used a Materialized view altough Query rewrite is enabled?
To answer above question following DBMS_MVIEW.EXPLAIN_REWRITE should be used.
There several options for using DBMS_MVIEW.EXPLAIN_REWRITE , I will mention about the following method to use DBMS_MVIEW.EXPLAIN_REWRITE.

First of all; the table named rewrite_table should be created by using $ORACLE_HOME/rdbms/admin/utlxrw.sql.
Rewrite_table is the table that  DBMS_MVIEW.EXPLAIN_REWRITE writes its output. If the table was already created in previous work, you can truncate it before using DBMS_MVIEW.EXPLAIN_REWRITE again.

Then DBMS_MVIEW.EXPLAIN_REWRITE can be executed. Note that only the query is given as input ( MV name is not given in here). MV name can also be given as input here to find the reason by explicitly specifying the MV name (as second argument), but it is not needed.

Example;

declare
query1 varchar2(256) :=
'select dim1.dk1, dim2.dk2
from fact, dim1, dim2, dim3
where dim1.dk1 = fact.dk1
and dim2.dk2 = fact.dk2
and dim2.dk2 = 1';
begin
dbms_mview.explain_rewrite(query1);
end;


After the procedure is executed, message (the reason) appears in rewrite_table.
In the message column of rewrite_table, reason for not using MV can be found.

select message from rewrite_table order by sequence;

Example : QSM-01094: outer-join filter not found in materialized join view

Reference:  Oracle Support -> How To Use DBMS_MVIEW.EXPLAIN_REWRITE and EXPLAIN_MVIEW To Diagnose Query Rewrite and Fast Refresh Problems [ID 149815.1]

Wednesday, April 10, 2013

Oracle Database Versioning and Numbering


Description of admin002.gif follows

Oracle WebCenter as Content Server for EBS R12 Oracle Learning Management / Certification


Currently, using Oracle WebCenter Content  as a content server for EBS R12 is not certified. It will be certified on EBS 12.1 RUP6. 12.1 rup6 is expected to be released on this Spring.
Reference: Oracle Support.

Friday, April 5, 2013

Oracle Export / Import compatability

When using export(exp) import(imp) utilities to transfer data and db structures between different version Oracle databases; compatability information below should be considered;

You should export the data using exp utility from the lower database home and import the data using the imp utility in target database home..

For example; if you should transfer data from 10g to 9i using exp/imp method. Then use 9i exp utility and connect to 10g using Tns. Export the data from 9i home (connecting to 10g database) and then import the exported data using 9i imp utility into 9i database.

Below is related info from Oracle Support;



Summary Export/Import compatibility:

1. Export the data with the Export utility of the lowest database version involved.

2. Import the data with the Import utility of the target database.


Additional information about exp/imp compatability:

Dump files generated by the Export Data Pump are not compatible with dump files generated by the original Export utility.

Exporting with an Oracle7 Export utility from an Oracle9i or higher database is not supported.


Tuesday, April 2, 2013

Reducing patch downtime in Ebs

Oracle Database Queiesce Mode



Same outcome like closing a database and opening in restricted mode.
But queiesce mode does not require a shutdown.
To put database into queiesce mode -> ALTER SYSTEM QUIESCE RESTRICTED;

When the database is quiesce mode; non-dba active sessions will continue to run until they become inactive.
On the other hand; an inactive session cannot become active when database is in quiesce mode. These kind of sessions appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action is processed.

To be able to put the database in quiesce mode , all the active session should be inactive, otherwise "alter system quiesce restricted" command will not return. This command can wait for a long time if active sessions are blocking it.

Active sessions blocking quiesce operation can be identified with the following sql;

select bl.sid, user, osuser, type, program
from v$blocking_quiesce bl, v$session se
where bl.sid = se.sid;


Top put database back to normal -> ALTER SYSTEM UNQUIESCE;


Quiesce State of an Instance can be checked by the following sql;

select active_state from v$instance;

NORMAL: Normal unquiesced state.
QUIESCING: Being quiesced, but some non-DBA sessions are still active.
QUIESCED: Quiesced; no non-DBA sessions are active or allowed.




Monday, April 1, 2013

AIX memory usage/shortage determination.


Vmstat tool in AIX has PO and PI columns. PO and PI columns means swapout and swapin . Values of these columns should be zero, or near zero  most of the time. If nonzero values are displayed , there might be a memory bottleneck on the system. Note that,  Nonzero values does not always indicate a problem ,as pagein and pageout is the main principle of the virtual memory, and Virtual memory comes by design of the operating system.

Here is the detailed information from IBM;

Memory usage determination with the vmstat command
The vmstat command summarizes the total active virtual memory used by all of the processes in the system, as well as the number of real-memory page frames on the free list.

Active virtual memory is defined as the number of virtual-memory working segment pages that have actually been touched. For a more detailed definition, see Late page space allocation. This number can be larger than the number of real page frames in the machine, because some of the active virtual-memory pages may have been written out to paging space.

When determining if a system might be short on memory or if some memory tuning needs to be done,
run the vmstat command over a set interval and examine the pi and po columns on the resulting report.
These columns indicate the number of paging space page-ins per second and the number of paging space page-outs per second. If the values are constantly non-zero, there might be a memory bottleneck.
Having occasional non-zero values is not be a concern because paging is the main principle of virtual memory.
PI COLUMN:
The pi column details the number of pages paged in from paging space. Paging space is the part of virtual memory that resides on disk. It is used as an overflow when memory is over committed. Paging space consists of logical volumes dedicated to the storage of working set pages that have been stolen from real memory. When a stolen page is referenced by the process, a page fault occurs, and the page must be read into memory from paging space.
Due to the variety of configurations of hardware, software and applications, there is no absolute number to look out for. This field is important as a key indicator of paging-space activity. If a page-in occurs, there must have been a previous page-out for that page. It is also likely in a memory-constrained environment that each page-in will force a different page to be stolen and, therefore, paged out.

PO COLUMN:
The po column shows the number (rate) of pages paged out to paging space. Whenever a page of working storage is stolen, it is written to paging space,
if it does not yet reside in paging space or if it was modified. If not referenced again, it will remain on the paging device until the process terminates
or disclaims the space. Subsequent references to addresses contained within the faulted-out pages results in page faults, and the pages are paged in individually
by the system. When a process terminates normally, any paging space allocated to that process is freed. If the system is reading in a significant number of persistent pages, you might see an increase in po without corresponding increases in pi. This does not necessarily indicate thrashing, but may warrant investigation into data-access patterns of the applications.


Recovering Parallel

Parallel media recovey : Roll forward can be parallelized by specifiying following command;

RECOVER PARALLEL {integer} , integer : degree of parallelism(default is number of cpu's)

In rac, Oracle distributes these parallel recovery processes accross the rac nodes automatically.  Usage Example : RECOVER DATABASE PARALLEL 4;

 Also , RECOVERY_PARALLELISM initialization parameter can be used to increase performance of Instance or Crash recovery..

Note that: Instance Recovery is initiated by SMON automatically at instance startup. Media Recovery is initiated by user.