Thursday, July 24, 2014

 PGA , SGA , MEMORY Advisors


PGA Advisor
Sql>select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;

In the above figure, look at 5th row
Value=1358954496 is the current orcldb.__pga_aggregate_target  value  (set in initorcldb.ora)
So, Subtract pga_target_for_estimate - estd_extra_bytes_rw for 5th row , 1358954496-954314752
equals to 404639744 i.e eqals to 386 MB.
So, conclusion is that adding extra 386 MB will improve performance(less I/O would be needed).
SGA Advisor
Sql>select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;
DB_TIME is an overall figure for the amount of time spent taken within the database to execute SQL;
Minimizing DB_TIME is the overall objective of all tuning.
Look at the 3rd row i.e value 1952 MB , this is the value of parameter orcldb.__sga_target being set
in initorcldb.ora.
So,It can be seen that if the SGA is raised from its current value of 1952 MB to 3904 MB then
DB_TIME would reduce.










MEMORY TARGET Advisor
Sql>select memory_size,memory_size_factor,estd_db_time from v$memory_target_advice;

Thursday, November 3, 2011

11.5.10.2 Installation Errors

Some More Errors:

Error 1: RW-50015: Error: - JSP is not responding. The service might not have started on the port yet. Please check the service and use the retry button.



Error 2: DBC File Check

RW-00023: Error: - DBC file was not created:
File = /u01/oracle/apps/trainappl/fnd/11.5.0/secure/TRAIN_dba1/train.dbc

------------------------
Executing script in InstantiateFile:
/u01/oracle/apps/traincomn/admin/install/TRAIN_dba1/adgendbc.sh

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

adgendbc.sh started at Wed Nov 2 22:43:07 IST 2011



Params=


Unique constraint error (00001) is OK if key already exists
ADD call failed with exit code 127

Trying to update information ...

UPDATE call failed with exit code 127

Generating /u01/oracle/apps/trainappl/fnd/11.5.0/secure/dba1_train.dbc
DBC generation failed with exit code 127

Updating Server Security Authentication
Updating Server Security Authentication failed with exit code 127
adgendbc.sh exiting with status 127
ERRORCODE = 127 ERRORCODE_END

error while loading shared libraries: librt.so.1: cannot open shared object file: No such file or directory
error while loading shared libraries: libdl.so.2: cannot open shared object file: No such file or directory
------------------------------



Error 3: HTTP Check

checking URL = http://dba1.appsahyd.com:8004
RW-50015: Error: - Portal is not responding. The service might not have started on the port yet. Please check the service and use the retry button.


Error 4: PHP Check

checking URL = http://dba1.appsahyd.com:8004/OA_HTML/US/ICXINDEX.htm
RW-50015: Error: - Login Page is not responding. The service might not have started on the port yet. Please check the service and use the retry button.

------------------------------------------------------------------------------------------------------------------------------------

Error 5: And also observed the below error after sourcing the environmental file:

----------------
ls: error while loading shared libraries: librt.so.1: cannot open shared object file: No such file or directory
----------------



Sol:

As per the Metalink note 747424.1, i replaced all occurances of LD_ASSUME_KERNEL with XD_ASSUME_KERNEL only in DB-tier, but not in the Apps tier. So this was causing the
above issue in the instalation of oracle 11i on OEL5

DB Tier:

O_H/appsutil/bin/adgetlnxver.sh

Apps Tier:

/ad/11.5.0/bin/adgetlnxver.sh

-- Restart rapidwiz as " ./rapidwiz -restart "

--run the autoconfig in apps tier with completed successfully result (and also generated the DBC files)

--run the adstrtal.sh

--This time everything checked OK in the instalation wizard.


After Resolution:

Wednesday, November 2, 2011

11.5.10.2 Installation Errors on RHEL 4

Error :1

RW-50010: Error: - script has returned an error: 1
RW-50004: Error code received when running external process. Check log file for details.
Running Database Install Driver for Train instance

afmkinit.sh exiting with status 127

.end std out.
cp: error while loading shared libraries: libc.so.6: cannot open shared object file: No such file or directory
touch: error while loading shared libraries: librt.so.1: cannot open shared object file: No such file or directory

.end err out.
****************************************************

Check log files @ O_H/appsutil/log/dbInstancecfg.log and installation log(11020548.log)

Sol:

1. Edit the <9i RDBMS_ORACLE_HOME>/appsutil/bin/adgetlnxver.sh file as below

2. Alter by replacing all occurrences of LD_ASSUME_KERNEL with XD_ASSUME_KERNEL

3. Press the retry button in rapidwiz screen

Below MOS Note Explains cleary about this issue:




---------------------------


Error 2:

There was an error while running the command - su orauser1 -c /u01/oracle/db/traindb/9.2.0/temp/Train_dba1/adrun9i.sh APPS APPS
/u01/oracle/db/traindb/9.2.0/appsutil/install/adlnkoh.sh: line 198: /u01/oracle/db/traindb/9.2.0/bin/sqlplus: No such file or directory
RW-50010: Error: - script has returned an error: 1
RW-50004: Error code received when running external process. Check log file for details.
Running Database Install Driver for Train instance

Observed below error in make.log @ RDBMS_O_H/install/make.log


/usr/bin/gcc: line 22: /usr/bin/gcc32: No such file or directory
/usr/bin/gcc: line 22: exec: /usr/bin/gcc32: cannot execute: No such file or directory
make: [

Sol:

Check below cmd:

$ gcc -v
/usr/bin/gcc: line 22: /usr/bin/gcc32: No such file or directory
/usr/bin/gcc: line 22: exec: /usr/bin/gcc32: cannot execute: No such file or directory

Actual o/p should be :

$ gcc -v
Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.3/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --enable-languages=c,c++ --disable-libgcj --host=i386-redhat-linux
Thread model: posix
gcc version 3.2.3 20030502 (Red Hat Linux 3.2.3-47.3)


Installed compat-gcc-32-c++-3.2.3-47.3 & compat-oracle-rhel4-1.0-3 rpms


Tuesday, May 31, 2011

Session Related Queries

1. Show all connected users
set lines 100 pages 999
col ID format a15
select username
, sid ',' serial# "ID"
, status
, last_call_et "Last Activity"
from v$session
where username is not null
order by status desc
, last_call_et desc


2. Time since last user activity
set lines 100 pages 999
select username, floor(last_call_et / 60) "Minutes", statusfrom v$sessionwhere username is not nullorder by last_call_et

3. Sessions sorted by logon time
set lines 100 pages 999
col ID format a15col osuser format a15
col login_time format a14
select username, osuser, sid ',' serial# "ID", status, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time, last_call_etfrom v$sessionwhere username is not nullorder by login_time

4. Show user info including os pid
col "SID/SERIAL" format a10
col username format a15col
osuser format a15col
program format a40
select s.sid ',' s.serial# "SID/SERIAL", s.username, s.osuser, p.spid "OS PID", s.programfrom v$session s, v$process pWhere s.paddr = p.addrorder by to_number(p.spid)

5. Show a users current sql
Select sql_textfrom v$sqlareawhere (address, hash_value) in(select sql_address, sql_hash_value from v$session where username like '&username')

6. Session status associated with the specified os process id

select s.username, s.sid, s.serial#, p.spid, last_call_et, statusfrom V$SESSION s, V$PROCESS pwhere s.PADDR = p.ADDRand p.spid='&pid'

7. All active sql
set feedback offset
serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username'('sid','serial#') ospid = ' process ' program = ' program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
sql_hash_value from v$session
where status = 'ACTIVE' and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in
(select sql_text from v$sqlarea where address = x.sql_address ) loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' )
then dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time ' ' x.current_time ' SQL#=' x.sql_hash_value); dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
8. Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, messagefrom v$session_longops
where time_remaining = 0
order by time_remaining desc

9. List open cursors per user
set pages 999
select sess.username, sess.sid, sess.serial#, stat.value cursors
from v$sesstat stat, v$statname sn, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value


Copied from http://www.shutdownabort.com/

Wednesday, February 16, 2011

Steps to create Readonly/View Only User in OEM

To create a new account with View Only privileges:

1. Logon to Grid Control as a super admin user (SYSMAN or equivalent)











2. Click on the links: Setup -> Administrators -> Click on Create


























3. Remove the 'PUBLIC ROLE' that is listed by default










4. Under Create Administrator: System Privileges select 'VIEW ANY TARGET'.









5. Under Create Administrator: Targets, choose all the targets this new admin should be able to view

















6. Under Create Administrator: Target Privileges , BATCH ASSIGN Choose 'View'











7. Click Apply














Reference - How To Create Administrator Accounts (Super Admin, Admin, View Only, etc) In Grid Control [ID 377310.1]

Steps to Remove/De-register Agent from OEM:

1. Stop the Agent on the target machine:

cd /bin
emctl stop agent

2. Login to the Repository Ddatabase (via sqlplus) as user SYSMAN and execute the following command:
SQL> exec mgmt_admin.cleanup_agent(':');

For example:

SQL> exec mgmt_admin.cleanup_agent('ebsqa-mt.oracle_mt.com:3872');

PL/SQL procedure successfully completed.



3. De-install the Agent on the target machine.


Reference: Metalink Note - 438158.1 -- How to Cleanly De-Install the EM 10g Agent on Windows and Unix

Thursday, September 30, 2010

Default Application users in 11i EBS

We can find out the list of default users in 11i EBS with sql available 3904641 patch:

Below are list of Default Application users in EBS:

ANONYMOUS
AUTOINSTALL
CONCURRENT_MANAGER
FEEDER SYSTEM
INITIAL SETUP
STANDALONE_BATCH_PROCESS
MOBILEADM
ASGADM
WIZARD
ASGUEST
IEXADMIN
IBE_GUEST
IBE_ADMIN
IBEGUEST
OP_SYSADMIN
OP_CUST_CARE_ADMIN
IRC_EMP_GUEST
IRC_EXT_GUEST