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, May 31, 2011

find log for concurrent program

in Apps you want to find out why the concurrent program failed or to view the request log of the concurrent program.

To view the log goto:

on the App tier:

log in as applmgr:
cd $APPLCSF

and then under log --> log files
under out --> any out files if there are.


example:

>cd $APPLCSF
pspapp01(ICMDEV) /product/appl/inst/apps/PSPDEV_pspapp01/logs/appl/conc

cmdapp01cdc(ICMDEV) /product/appl/inst/apps/ICMDEV_icmdapp01cdc/logs/appl/conc
>ls -lrt
total 16100

drwxr-xr-x 2 applmgr oinstall 3964928 May 31 14:53 log
drwxr-xr-x 2 applmgr oinstall 4251648 May 31 14:53 out

Monday, May 23, 2011

find string in the files and underlying directories

find . -exec grel -l "string" {} \; -print

Monday, May 16, 2011

Port 9710 was reported as currently in use by the system

Oracle BI presentation services won't startup after port change.

scenario:

Host1 has already BI presentation services running with the default ports and
you installed BI presentation services in another home as a different user and when you try to start the services you will get port 9710 is already in use.


You changed instanceconfig.xml file

$BI_DATAHOME/web/config/instanceconfig.xml to a different port and the log still shows the default port.

>vi sawserver.out.log
"sawserver.out.log" 45 lines, 1392 characters
Type: Information
Severity: 30
Time: Mon May 16 14:59:48 2011
File: project/sawserver/sawserver.cpp Line: 386
Properties: ThreadID-1
Location:
saw.sawserver

Oracle BI Presentation Services 10.1.3.4.1 (Build 090414.1900) are starting up.
---------------------------------------
Type: Warning
Severity: 40
Time: Mon May 16 14:59:49 2011
File: project/webcomm/socketrpcserver.cpp Line: 323
Properties: ThreadID-1
Location:
saw.rpc.server.initialize
saw.sawserver

Port 9710 was reported as currently in use by the system. The configured listener address may be in the TIME_WAIT state or may be
in use by some other service.
---------------------------------------
Type: Error
Type: Information
Severity: 30
Time: Mon May 16 14:59:48 2011
File: project/sawserver/sawserver.cpp Line: 386
Properties: ThreadID-1
Location:
saw.sawserver



solution:

stop saw server
add the below line to the top of the instanceconfig.xml file and before the dsn line.

WebConfig
ServerInstance
Listener port="9720"
DSN>AnalyticsWeb "careful this is case sensitive"



re start the saw server.

Friday, May 13, 2011

ld.so.1: schconfig: fatal: libnqutilitygeneric.so: open failed: No such file or directory

I was getting the below error while trying to configure the scheduler


HOST$:(/export/home/obidev2)
>$BI_HOME/setup/sa-init64.sh
HOST$:(/export/home/obidev2)

HOST$:(/product/obidev2/OracleBI/setup)
>./common.sh
HOST$:(/product/obidev2/OracleBI/setup)
>schconfig
ld.so.1: schconfig: fatal: libnqutilitygeneric.so: open failed: No such file or directory
Killed

Gave another try by sourcing the environment:

HOST$:(/product/obidev2/OracleBI/setup)
>sa-init64.sh
HOST$:(/product/obidev2/OracleBI/setup)
>common.sh
HOST$:(/product/obidev2/OracleBI/setup)

HOST$:(/product/obidev2/OracleBI/server/Bin64)
>./schconfig
Copyright (c) 1997-2009 Oracle Corporation, All rights reserved
Oracle BI home directory is not defined
Incomplete Oracle BI Server Suite configuration

Error: Incomplete Oracle BI Server Suite configuration

HOST$:(/product/obidev2/OracleBI/server/Bin64)
>env | grep ORA
ORACLEBI_HOME=/product/obidev2/OracleBI
ORACLEBIDATA_HOME=/product/obidev2/OracleBIData
ORACLE_HOME=/product/oracle/app/oracle/product/11.2.0/client_1
HOST$:(/product/obidev2/OracleBI/server/Bin64)
>env | grep BI
ORACLEBI_HOME=/product/obidev2/OracleBI
BI_DATAHOME=/product/obidev2/OracleBIData
BI_HOME=/product/obidev2/OracleBI
ORACLEBIDATA_HOME=/product/obidev2/OracleBIData
PATH=/usr/bin::/usr/local:/usr/local/bin:/export/home/obidev2:/export/home/obidev2/bin:/product/oracle/app/oracle/product/11.2.0/client_1/bin:/product/obidev2/OracleBI/web/bin:/product/obidev2/OracleBI/server/Bin
PWD=/product/obidev2/OracleBI/server/Bin64


>cd /product/obidev2/OracleBI/setup
HOST$:(/product/obidev2/OracleBI/setup)
>set +u
HOST$:(/product/obidev2/OracleBI/setup)
>. ./common.sh <-- Make sure you use dot & dot/common.sh --->
HOST$:(/product/obidev2/OracleBI/setup)
>cd -
/product/obidev2/OracleBI/server/Bin64
HOST$:(/product/obidev2/OracleBI/server/Bin64)
>set +u
HOST$:(/product/obidev2/OracleBI/server/Bin64)
>./schconfig
Copyright (c) 1997-2009 Oracle Corporation, All rights reserved

***** Delivers Configuration Menu *****
1 - Configure Scheduler
2 - Configure Mail
3 - Configure iBots
4 - Configure Workflow
5 - Configure Java Extension
0 - Quit
>>> Enter Choice:

Tuesday, May 10, 2011

R12 change port pool

How to change port pool in Oracle Apps R12?

I had the opportunity to change the port pool in R12 and below is the procedure I followed:

for DB :

1) backup the existing context file
2) change the below ports with the new ports.

***** List of ports allocated based on port pool 3 *****
Database Port : 1524
DB ONS Local Port : 6303
DB ONS Remote Port : 6403
Oracle Connection Manager Port : 1524 (EXT_PORT)

verify what are the ports you used/allocated during the cfgclone.pl from the logs.

Checking the port pool 3
done: Port Pool 3 is free
Report file located at $ORA_HOME/appsutil/temp/portpool.lst
4)run the adautocfg.sh

on Apps Tier:

$COMMON_TOP/clone/bin/adcfgclone.pl appsTier

and pick the appropriate pool:

Friday, May 6, 2011

in Oracle Apps R12 Perl lib version doesn't match executable version

Today When I was cloning the Dev with Prod. I got a strange error regarding perl during adcfgclone.pl



icm01/u0001/oracle/product/11.2.0/icmdev2/dbhome_1/appsutil/clone/bin/adcfgclone.pl dbTechStack

Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.0

adcfgclone Version 120.31.12010000.8


| 0% completed


Process failed and when I checked the log:



script returned:
****************************************************

.end std out.
Perl lib version (v5.8.4) doesn't match executable version (v5.10.0) at /usr/perl5/5.8.4/lib/sun4-solaris-64int/Config.pm line 32.
Compilation failed in require at /icm01/u0001/oracle/product/11.2.0/icmdev2/dbhome_1/appsutil/clone/ouicli.pl line 35.
BEGIN failed--compilation aborted at /icm01/u0001/oracle/product/11.2.0/icmdev2/dbhome_1/appsutil/clone/ouicli.pl line 35.

.end err out.


So on further research found out it is Oracle bug in R12: Nice--one more .
Bug 9724411

solution :

export PERL5LIB=/perl/lib/5.10.0:/perl/site_perl/5.10.0:/appsutil/perl

Wednesday, May 4, 2011

ORA-27300 ORA-27301ORA-27302

Today we started getting wired errors and one of them is below:

ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3


After extensive research it proved as:

STATUS 12 - ENOMEM Not enough core / memory
During an exec or a break, the program asked for more memory than the one available by the system. This error also occurs when there are too many segmentation registers which are required for the arrangement of text data or stack segments.

ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

See Note 465002.1 Database Crash With Error ORA-00490

Note 580552.1 Ora-04030 Ora-07445 Ora-27300 Ora-27301 Ora-27302 Crashed Database

Note 560309.1> /> Database Cannot Start Due to Lack of Memory

** All related to swap space being depleted. Check the OS system logs. **> />

SHRINK SYSAUX AWR SPACE

SELECT occupant_name, schema_name, space_usage_kbytes/1024/1024 FROM v$sysaux_occupants;

OCCUPANT_NAME SCHEMA SIZE_IN_GB


SM/ADVISOR SYS 0.821045
SM/OPTSTAT SYS 1.971924
SM/AWR SYS 46.2876


Here clearly it shows AWR is occupying more than 46g. How can I reclaim this space back?

SQL> select * from DBA_HIST_WR_CONTROL;

DBID snap_interval retention topnsql
3861913925 +00 00:15:00.000000 +14 00:00:00.000000 DEFAULT

 shows retention as 14 days @ 15 mins time interval . Looks normal here so check further

If you ever want to change the retention then use below:

SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 5760);

Here 4320 is 4*24*60 ( Convert the days to minutes). For 3 days, it 5760 minutes.



SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83193  Huge snapshot difference that means we have long history of snapshots which needs to be purged.

SQL> show parameter statistics

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL  should be typical.
timed_os_statistics integer 0
timed_statistics boolean TRUE


Thus at this time we can call this as the huge history of snapshots are stored in the database:

Use the below to purge the old snapshots and reclaim the space:

SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id,high_snap_id);


To my surprise the drop snapshot is not dropping the snapshots:


SQL> exec dbms_workload_repository.drop_snapshot_range( 4907 , 9000);

PL/SQL procedure successfully completed.

SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83197


SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
3

SQL> exec dbms_workload_repository.drop_snapshot_range( 4907 , 9000);


PL/SQL procedure successfully completed.


SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83193


SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83195

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
2


SQL> exec dbms_workload_repository.drop_snapshot_range( 4907 , 9000);

PL/SQL procedure successfully completed.

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
1


After running the drop snapshot I still see the low snap as the same:


select * from dba_hist_snapshot order by 1<-- indeed show 4907 was from 2008.
4907 3861913925 1 9/16/2008 2:40:41.000 AM 2/1/2009 10:00:49.693 PM 2/1/2009 11:00:02.760 PM +00 00:00:10.200000 1 0
4908 3861913925 1 9/16/2008 2:40:41.000 AM 2/1/2009 11:00:02.760 PM 2/2/2009 12:00:16.084 AM +00 00:00:09.900000 1 0
4909 3861913925 1 9/16/2008 2:40:41.000 AM 2/2/2009 12:00:16.084 AM 2/2/2009 1:00:28.861 AM +00 00:00:10.100000 1 0
4910 3861913925 1 9/16/2008 2:40:41.000 AM 2/2/2009 1:00:28.861 AM 2/2/2009 2:00:42.319 AM +00 00:00:09.700000 1 0

So started doing some research and found the below:



DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE Is Not Removing All Snapshots Data [ID 1218543.1]
Cause
This is not a bug but expected behavior, as oracle does not drop snapshots data associated with the baselines .

To overcome this:

Drop the baseline to clear the data for these snapshots.


SQL> select BASELINE_ID,START_SNAP_ID,END_SNAP_ID from WRM$_BASELINE
2 ;

BASELINE_ID START_SNAP_ID END_SNAP_ID
----------- ------------- -----------
2 4907 5192
3 4932 4956
11 6524 6548
12 6620 6645
1 4908 4932
4 4956 4980
5 4980 5004
6 5004 5028
7 5028 5052
8 5052 5076
9 5076 5139
10 5139 5215
14 5924 6867


SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE ( baseline_name => 'AWR_1234290129964',cascade=>true,dbid=>3861913925);

PL/SQL procedure successfully completed.


WOW,Now the min snapshot has been changed.

SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4908 83215


So I went ahead and deleted all the old baselines to purge the old AWR historic info and reclaimed all the space.

Good luck to you all: