Posts

Showing posts from February, 2013

Cancel Request In Conurrent Manager

To Cancel Request In Oracle Apps using SqlPlus  Command run the below Query :

Update Fnd_Concurrent_Requests
   SET Phase_Code = ‘C’,
   Status_Code = ‘E’
 Where Request_ID = ;



Or you can Replace Retest_id with  CONCURRENT_PROGRAM_ID.

Thank you
Osama Mustafa

What is ORA-00600

Image
The ORA-600 error is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition.
ORA 600 "internal error code, arguments: [%s], [%s],[%s], [%s], [%s]"
and you should it's different Oracle errors , Because when you see this error then it's indicating for bugs, the above is general description for the error the first characterset / Number is is used with database version to identify the problem by oracle support. and maybe you will find related document on https://support.oracle.com.

When you face this error you should check the below document searching for some Notes/Patch could help you :
 Note 600.1 ORA-600/ORA-7445 Lookup tool You need to Choose database version ( 4 digit ) and First argument in the error, also for more information how to use this tool :
 Note 1082674.1 : A Video To Demonstrate The Usage Of The ORA-600/ORA-7445 Lookup Tool [Video] Notice when you check the alert …

EBS 11i MOS notes

Cloning

NOTE.362473.1 : Cloning E-Business Suite Using Hot Backup for Minimal
NOTE.216212.1 : Business Continuity for Oracle Applications Release 11i
NOTE.233428.1 : Sharing the Application Tier File System in Oracle
NOTE.230672.1 : Cloning Oracle Applications Release 11i with Rapid Clone
NOTE.216664.1 : FAQ: Cloning Oracle Applications Release 11i
NOTE.135792.1 : Cloning Oracle Applications Release 11i




Patching

NOTE.174436.1 : Oracle Applications Patching FAQ
NOTE.175485.1 : How to Apply an 11i Patch When adpatch is Already Running
NOTE.181665.1 : Release 11i Adpatch Basics


Autoconfig:

NOTE.218089.1 : Autoconfig FAQ
NOTE.165195.1 : Using AutoConfig to Manage System Configurations with
NOTE.270519.1 :Customizing an AutoConfig Environment


General Notes:

165195.1 "Using Autoconfig to Manage System Configurations with Oracle Applications 11i".
218089.1 "Frequently Asked Questions about Using Autoconfig with Oracle Applications Release 11i".
217368.1 "Advanced Confi…

oracle server architecture diagram

Image
Oracle Database 10g : 


Oracle Database 11g :



Thank you
Osama Mustafa

Check Session For Specific Request in EBS

Sqlplus apps/apps


Select request_id,oracle_process_id,phase_code,status_code from
apps.fnd_concurrent_requests where status_code='R'
where request_id in(Request_ID #1,Request_ID #2,Request_ID #3);


select r.request_id,
  r.oracle_process_id spid,
  r.oracle_session_id,s.inst_id,
  r.os_process_id process,
  s.sid,
  s.serial#,
  s.status,to_char(logon_time,'YYYYMMDD HH24:MI:SS') time,s.program,s.sql_address,
  s.paddr
  from apps.fnd_concurrent_requests r,
     gv$session s
 where r.request_id =31082866
  and r.oracle_session_id = s.audsid(+);
Thank you
Osama Mustafa

ORA-01031: insufficient privileges When Start Windows Services

this common error in windows, and you can be occur for more than one reason and you need to check them all to make sure you did everything is right, the error prevent the oracle services to start automatically and if you check

ORADIM.LOG....
**************************
C:\Oracle\Ora11g\bin\oradim.exe -startup -sid ptdb -usrpwd * -log oradim.log -nocheck 0
Thu Nov 15 15:16:15 2012
ORA-01031: insufficient privileges


You have to check :

1- startup type for the services ( if it's set automatically )
2- Group to user ( the user should be in ORA_DBA Group )
3 - you can check log on tab in the service properties and set the username and password who responsible about this services and can do it by:

Go to Control Panel -> Administrative Tools -> Services .

Choose oracle services and then
click "properties". Select the "Log On" tab and note the account that is set to run the service.


Thank you
Osama mustafa

Remote Diagnostic Agent

RDA : It's utility collect diagnostics information about an Oracle environment, And could be downloaded from https://support.oracle.com for every Operating system there's specific version for RDA.

RDA is Command Line diagnostic tool that is executed by an engine written in the Perl programming language,The data captured provides Oracle Support with a comprehensive picture of the customer's environment which aids in problem diagnosis, You can check the below MOS Note :
Remote Diagnostic Agent (RDA) 4 - FAQ [ID 330363.1]

Oracle Support encourages the use of RDA because it greatly reduces service request resolution time by minimizing the number of requests from Oracle Support for more information.RDA Support Most Of Operating system.and also it's supported for Most Oracle Products.

But why should i use RDA, as i mention before oracle support encourages to use RDA to collect information and also there's another reasons to use it :

 Oracle Fusion Middleware issue…

Use Order By With Delete Statement

if you are using order by in sub query with delete statement like the below :

 delete from tAccountScoring where riskscoring_id=1 and id in
(select * from tAccountScoring where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 over (order by last_update desc)) You will have an error :


Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:   
*Action: The workaround to solve this issue is simple like the below :

delete from tAccountScoring
where riskscoring_id=1 and id in
(select ID  from (select * from tAccountScoring
where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 order by last_update))
Thank you
Osama Mustafa

Nmap and Oracle - Security Topics

Image
The First Question came to my mind when post this topic, What is the relation between NMAP and Oracle ? for the people who doesn't know what is the NMAP i will talk about it but it will not be enough to give this amazing tool what its deserve .

Nmap is a powerful tool that is capable of generating a multitude of signatures depending on how it is used. However, if we understand the operation of the tool in general, it is easier to recognize its overall signature in network traffic. Dissecting the signature into sub-patterns one can differentiate between fingerprinting attempts that were successful and those that were not. It is important to understand that we have examined only one of the scan types that nmap can perform, the SYN half-open stealth scan. Several other scans are supported by Nmap: Tcp connect, FIN, Xmas, NULL, udp, ping, and even ftp-bounce. Expect to see these in the near future.

There's lot of features Nma…

AWR Vs StatPack

When you face performance issue in database the first thing coming to your mind is Automatic Workload Repository (AWR) or STATPACK reports but what is the difference between them, in this article i will try to mention as much as i can the difference between them.

1-you should be aware that AWR not exists in database 9i so you forced to use statepack, include to that steps to generate AWR much easier than STATPACK.

2-AWR hold all the information and statistics that exists in STATPACK, include to that AWR hold Additional Information.

3- in AWR you will find information called Active Session History ( ASH ) which is not exists in STATPACK.

4- To generate STATPACK you should Run Procedure to enable snapshot, you can use DBMS_JOB or schedule it using crontab.

5- AWR snapshots provide a persistent view of database statistics. A snapshot is a collection of performance statistics that are captured at a specific point in time,AWR snapshots are scheduled every 60 minutes by default.

 6- Stats…

Check Oracle Process On Windows Using SQL

Image
Usually When You want to check oracle process on Linux OS you are using "ps" command but what if you want to check and display all the oracle process on windows :

sqlplus / as sysdba

select a.sid,a.serial#, a.program, p.pid, p.spid, a.osuser, b.name, b.DESCRIPTION, p.PGA_USED_MEM   from v$session a,v$process p, v$bgprocess b where a.paddr=b.paddr
and    a.paddr=p.addr and p.background=1;


The above picture taken by SQL Developer.

RunLevel Mode In Linux

As Database administrator you dealing with Different operating system everyday, most of this operating system is Linux/Unix, During the boot up for Linux the init command open files called "/etc/initab"  this file linux start decide which run level the system should booted to. After start the OS you can check "/etc/initab" using Editor (vim command).

there's different type of run level in linux you should know about them :

0 - halt (Do NOT set initdefault to this).1 - Single user mode.2 - Multiuser, without NFS (The same as 3, if you do not have networking).3 - Full multiuser mode.4 - unused.5 - X11.6 - reboot (Do NOT set initdefault to this). The Above modes available in /etc/initaband you can check them, when you open the files you will see lines


 id:5:initdefault:  Which indicate for default level. and you can change it.

Short Description for the RunLevels :

Runlevel 0:

Cause the system shutdown , and you can't set this as default. no reason to do that.

R…

ORA-27086: unable to lock file - already in use

In Alert log :

Thu Feb 14 09:34:57 EST 2013
Errors in file /u01/app/oracle/admin/rdmetdev/udump/rdmetdev_ora_6321.trc:
ORA-27050: function called with invalid FIB/IOV structure
Additional information: 2
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10
usually this problem occurred because NFS is Hanged,and can check OS logs to find "statd: server localhost not responding, timed out".

1- Check rpc.stat is working and you can restart again , and you have to Restart NFS Services also

Stop :

# service nfslock stop
# service nfs stop
# service portmap stop
# umount /proc/fs/nfsd  Start :
# service portmap start
# service nfs start
# service nfslock start
# mount -t nfsd nfsd /proc/fs/nfsd
Please follow the order, also note that when you restart the NFS service the server could hang at most for 1 minute.

Finally you should Reboot your server.


Thank you
Osama Mustafa

Basic Backup Techniques

Oracle Provide you with More than Way to Backup Let's talk about them shortly :

1- Hot Backup

performed on data even though it is actively accessible to users and may currently be in a state of being updated.

- Connect / as sysdba
- alter database begin backup;
- copy all data files using OS command / not necessary to copy temp datafile.
- alter database end backup;
- alter system switch log;
- alter database backup control files to ‘’;
 2- Cold backup

Here all you have to do is shutdown database Copy Files ( Controlfile , Datafiles , Redolog .... ) using OS Command.

3- Backup Using RMAN

RMAN is most powerful utility for Backup in Oracle gives you lot of options, the simple script is below :

set/export ORACLE_SID=
rman target /
run {
backup database;
backup archivelog all;
} 4- Export/import Commands

The Export utility provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurati…

WARNING: failed to read mirror side In alert log

Database Version : 11.2.0.2
Real Application Cluster --- > ASM

How error look like in Database Alert log :

WARNING: Read Failed. group:1 disk:4 AU:11852 offset:892928 size:8192
WARNING: failed to read mirror side 1 of virtual extent 1722 logical extent 0 of file 285 in group [1.4277692974] from disk DATA01_0004  allocation unit 11852 reason error; if possible,will try another mirror side
Errors in file /home/app/oracle/diag/rdbms/absnl/ABSNL1/trace/ABSNL1_ora_24108.trc:
WARNING: Read Failed. group:1 disk:4 AU:11852 offset:892928 size:8192
WARNING: failed to read mirror side 1 of virtual extent 1722 logical extent 0 of file 285 in group [1.4277692974] from disk DATA01_0004  allocation unit 11852 reason error; if possible,will try another mirror side
 For the First time when I see this error i thought i had problem in my storage (ASM) But after checking And everything was Ok i start search at Http://Support.oracle.com

Yes it's Bug , and the Bug Number is 10422126.

you can Check So…

Linkedin Profile

Image
Also Check Here

How To Monitor Oracle Database

I talked before about Oracle Users, and The effect Of them In Our Database , Today i will Share Some Idea to monitor Oracle Database in Simple Way without any third Party

Any Company should be Concern about Powerful user in their company, i am talking here about database users. Specially In Production System.

as we all know Oracle database comes with two powerful account SYSTEM, SYS Very often individual accounts with DBA roles also are created for DBAs to perform their daily duties without using the SYS or SYSTEM accounts.These admin accounts in the Oracle database usually have the ability to manage user security, maintain database storage, and perform backup and recovery tasks.but when the both left without monitored they may perform fraudulent activities without leaving any trace, Stealing Backup, Data or even take look at some personal Data such as Credit card number , Social Number and Mobile number for example.Granting DBA access to business owners is a blatant violation of …

Corruption In Oracle Database

There are two types of corruption in Oracle database physical and logical what is the difference between them,Database written to blocks if this write failed then Database Corruption happened Sometimes because I/O Problems, Power Problem ... etc which cause no time for header to been updated, usually Oracle Corruption will not effecting on your work until you try to read this block. There are two types of Corruption:

1- Physical Corruption  (Media Corruption)

2- Logical Corruption (Soft Corruption) 

The Details can be Find in Alertlog
ORA-01578:ORACLE data block corrupted (file # string, block # string)


Physical Corruption :

this kind of corruption can be happened when I/O Problems, Memory Failure, Server Controller

Regarding to Oracle documentation the corruption could be happened by:

Bad header:the beginning of the block (cache header) is corrupt with invalid values.Block is Fractured/Incomplete:Information from the block header does not match the block tailBlock checksum is invali…

Read ORACLE_SID and ORACLE_HOME Inside PL/SQL Code

DECLARE 

   V_ORACLE_HOME  VARCHAR2 (2000); 
   V_ORACLE_SID  VARCHAR2 (256); 

 BEGIN 

   DBMS_SYSTEM.GET_ENV ('ORACLE_HOME', V_ORACLE_HOME); 
   DBMS_SYSTEM.GET_ENV ('ORACLE_SID', V_ORACLE_SID ); 

   --The below line will print Oracle_home
   DBMS_OUTPUT.PUT_LINE ('ORACLE_HOME :- ' || V_ORACLE_HOME); 

   -- the below line will print Oracle_SID
   DBMS_OUTPUT.PUT_LINE ('ORACLE_HOME :-' || V_ORACLE_SID); 
 END; 

Reference :
1-OTN Forum

Thank you
Osama Mustafa

Gather System Statistics

This procedure gathers system statistics. Which is Important for Index and Tablespace. it's very Good to use in SQL performance.

There are different Mode for Gather_system_stat like the following :


NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces. To fine tune system statistics for the workload use 'START' and 'STOP' or 'INTERVAL' options. If you gather both 'NOWORKLOAD' and workload specific (statistics collected using 'INTERVAL' or 'START' and 'STOP' ), the workload statistics will be used by optimizer. Collected components: cpuspeednw, ioseektim, iotfrspeed.
INTERVAL: …

Another Linux Command

1-Check Memory :

[oracle@192 ~]$ free -m

             total       used       free     shared    buffers     cached
Mem:          1010        997         13          0         71        685
-/+ buffers/cache:        240        770
Swap:         2000          0       2000[oracle@192 ~]$ vmstat

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0  28820  72248 704336    0    0   117    49 1026  239  3  1 93  3  0[oracle@192 ~]$ dmesg | grep RAM

BIOS-provided physical RAM map:
hdc: ATAPI 1X DVD-ROM DVD-R-RAM CD-R/RW drive, 32kB Cache, UDMA(33) Finally : top command that you could use.

2-How Much this Os was running

[oracle@192 ~]$ uptime

 23:03:17 up  1:44,  2 users,  load average: 0.04, 0.11, 0.08



3-Check Some Hardware Information

CPU :

[oracle@192 ~]$ cat /proc/cpuinfo

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 37
model name      : In…

Drop Undo Tablespace Online

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                 string       AUTO
undo_retention                       integer     900
undo_tablespace                    string       UNDOTBS1


CREATE UNDO TABLESPACE undotbs2
DATAFILE'/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M; Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

System altered.

SQL> show parameter undo ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                 string          AUTO
undo_retention                       integer        900
undo_tablespace                    string          UNDOTBS2  Now you need to check if there's any segment used old undo tablespace to ensure you will not loose any Data

SQL> select segm…

ORA-04068: existing state of packages has been discarded

The Below Error happened Usually In RAC environment, and its Indicate to Bugs and you can avoid this error as Work around :

 SQL> alter system set "_disable_fast_validate"=true scope=both;
Please be Noted there's No Restart need After Modify This Parameter.

Also For Better Solution you can upgrade to 11.2.0.3 Or apply Patch Number 9681133 Which Can Be Downloaded From Https://support.oracle.com


Thank you
Osama Mustafa

OTNYathra 2013

The Oracle ACE directors and Java champions in the region are organizing an evangelist event called ‘OTNYathra 2013’ during the month of February 2013.This yathra or tour will a series of 6 conferences across 6 major cities in a time period of 2 weeks managed by ACE directors and Java Champions of the region.


India is a primary hub for information technology and a station for most software development centers and support centers for Oracle applications.Oracle community in India comprises of several 400000 users.In a large country like India, such user concentration is not in one location or one IT park but spread across its length and breadth.The OTNYathra (Tour) is to travel across the country covering over 6 major IT focused cities and to bring the Oracle community together, giving them awareness and improve the level of knowledge and increase the networking opportunities in the region.The tour will also give awareness to younger youth who have just completed their en…

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

But Today its About RAC Issue while try to create dbconsole Like the following :

emca -config dbcontrol db -repos create -cluster

=============

STARTED EMCA at Jan 30 , 2012 1:01:00 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle.  All rights reserved.


Enter the following information:
 Database unique name: ORCL
 Service name: ORCL

Listener port number: 1561
 Listener ORACLE_HOME [ /u01/grid/11.2.0 ]: /u01/oracle/product/db/11.2.0
 Password for SYS user:
 Password for DBSNMP user:
Password for SYSMAN user:
Cluster name: CRS
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u01/grid/11.2.0 ]:
ASM port [ 1561 ]: 1521
ASM username [ ASMSNMP ]:
ASM user password:
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password: 

This…

Oracle Mechanism To Store Password

Today I post This Topic About how Oracle Store Password, I choose this topic since i will write on other topics that related to this one, Understand this topic will make you understand the basics how to secure oracle database.

As i mention before there's are simple steps to secure database before start buying products and use them.
Also Please note that article will not show you how to hack or attacks any algorithms it's only show the Title of this topic.I used in this topic Database version 10g,11g.

When You Enter Username which is stored in Plain Text ( Clear Text ) in DBA_USERS view on other hand the password stored as hash, when you log in to The Password is concatenated to the end of your username, and when you type the password , it will be compared to Stored hash password if they match then welcome to your schema otherwise you don't have access to the schema.

I love to keep asking this question since its really good question regarding to password, why Using Hash …