Tag: osama oracle

Multiple DataFiles and Mutliple Tablespace

Posted on by Osama Mustafa in Database
As Any Database administrator you are managing your database check the size of your tablespace and datafiles , try to get better performance But why we are using Mutliple Tablespace and datafiles :

Advantage multiple tablespaces :

Control disk space allocation for database data
Assign specific space quotas for database users
Control availability of data by taking individual tablespaces online or offline
Perform partial database backup or recovery operations
Allocate data storage across devices to improve performance

The Same For Multiple DataFiles :
put each data file on a separate disk array. This decreases contention between disks.

Thank you
Osama Mustafa

Session memory For Oracle User

Posted on by Osama Mustafa in Database
To check the Memory Usage for Each Seesion :

select username,name,value
from v$session join v$sesstat using (sid)
join v$statname using (statistic#)
where name = 'session pga memory' and username='';

Example :

USERNAME              NAME                                   VALUE
———————————————————– ———-
SCDB                       session pga memory                    699884

SCDB                       session pga memory                     651376

Thank you
Osama mustafa

V$Session_Wait VS V$Session_event

Posted on by Osama Mustafa in Database
Both of these tables are view In database , I will talk about what is the difference between them and Why some of record appear in V$Session_wait but not in V$session_event , Simple Topic But useful and good to know , Also Check the document that i post For Oracle that gave you structure for both of these view :

V$SESSION_WAIT 
displays the current or last wait for each session.

V$SESSION_EVENT
lists information on waits for an event by a session

This is simulation for both table appear what is the common between two view :

Conn SPP/SPP
 
select SID, EVENT from v$session_wait where event='DIAG idle wait';
 
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait
 
select SID,EVENT from v$session_event where event ='DIAG idle wait' ;
 
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait

Also its good to know when you are using 10g or later you can use v$seesion which is  gives you real-time information, what is happening right now.

gives you real-time information, what is happening right now :


there’s different type of enqueue:wait in Oracle like the following :

    enq: TX - allocate ITL entry
 
    enq: TX - contention
 
    enq: TX - index contention
 
    enq: TX - row lock contention 
 

to check them you can query V$EVENT_NAME view provides a 
complete list of all the enq: wait events. 

But in V$session_wait you can check the following :


     P1: Lock TYPE (or name) and MODE



    P2: Resource identifier ID1 for the lock



    P3: Resource identifier ID2 for the lock

 Which is not found in v$session_event .

So We can say :

V$SESSION_WAIT 
displays the events for which sessions have just completed waiting or are currently waiting.
V$SESSION_EVENT 
is similar to V$SYSTEM_EVENT, but displays all waits for each session.

 Reference Document :
1-V$session_wait
2-V$session_event
3-Events

Thank you
Osama Mustafa

 

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_1

Posted on by Osama Mustafa in Database

LOG_ARCHIVE_DEST 
is applicable only if we are running the database in ARCHIVELOG mode. LOG_ARCHIVE_DEST parameter are used to specified the archiving location. The Location specified by log_archive_dest must be local . We choose to archive only two local location  i.e,  primary and a secondary destination ( using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST ) .

LOG_ARCHIVE_DEST_n 
initialization parameter defines up to ten (where n = 1, 2, … 10) destinations in oracle 10gand thirty one (n=1,2….31)  destination in oracle 11g , each of which must specify either the  LOCATION or  the SERVICE  attribute to specify where to archive the redo data. All other attributes are optional.We set the attributes for the LOG_ARCHIVE_DEST_n initialization parameter to control different aspects of how redo transport services transfer redo data from a production or primary database destination to another (standby) database destination.For every LOG_ARCHIVE_DEST_n initialization parameter that we define, we must specify corresponding LOG_ARCHIVE_DEST_STATE_n parameter. The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter specifies whether the corresponding destination is currently enabled or disabled.
 

LOG_ARCHIVE_FORMAT 

Syntax : LOG_ARCHIVE_FORMAT = ‘log%t_%s_%r.arc’

is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter.

The following variables can be used in the format:

%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

Reference Document :
1-Oracle Log_Archive_log Documentation.
2-Oracle Log_arcchive_Dest

unable to open connection to libvirt management daemon

Posted on by Osama Mustafa in Operating system
This error appear on red hat virtualization , the cause of this error because daemon for red hat virtualization is disabled :

chkconfig libvirtd on
service libvirtd start

Also you can enable it or check the status for libvirtd by :

/etc/init.d/libvirtd [status|start|stop]

Thank you
Osama mustafa

Comprare Oracle Data Between two Schema

Posted on by Osama Mustafa in Database
While browsing On internet Today i found some script that will help you to compare data between two schema in Oracle for those who don’t love to use tools you can Download them from :

Script Number One
Script Number Two

There are Serveral Tools Used for Comparing such as :

1-Open Source application for comparing two tables of data
2-TOAD FOR ORACLE
3-Oracle SQL DEVELOPER 
4-Oracle Enterprise Manager (OEM).
5-Compare any Two Database http://www.dbsolo.com/schema_comparison.html .
6-RedGate to Compare Data, schema , Database .


Thank you 
Osama Mustafa

Some Way To Active Oracle Trace

Posted on by Osama Mustafa in Database
Reading to Oracle Documentation the trace is facility provides performance information on individual SQL statements. It generates the following statistics for each statement:

  • Parse, execute, and fetch counts
  • CPU and elapsed times
  • Physical reads and logical reads
  • Number of rows processed
  • Misses on the library cache
  • Username under which each parse occurred
  • Each commit and rollback

To enable Oracle Trace you can do the following :

 Option Number One :

alter session set sql_trace = true;

Disable it :

alter session set sql_trace = false;

Option Number two :

Activate
dbms_session.set_sql_trace (true);
Deactivate

dbms_session.set_sql_trace (false);

Option Number three:

Activate
dbms_support.start_trace (binds=>{true|false}, waits=>{true|false});
Deactivate

dbms_support.stop_trace;

Notice that package is not installed by default , Check
EVENT: 10046 “enable SQL statement tracing (including binds/waits)” [ID 21154.1]

There’s The most Common way to Trace your SQL and enable trace , there’s other way you can search on it via Google or Oracle Documentation , all you have to do is trying one of these options .

some Other Useful link :
1-Oracle Documentation 
2-Oracle traces description.
3-Enable Oracle Traces

Thank you
Osama

Oracle EBS Patch Tips

Posted on by Osama Mustafa in Application
What We Mean By Patch ?

Is Program designed to fix some Bugs Or Particular problem .

types of file in patch

cxxxx.drv         the ‘c’ or copy driver
dxxxx.drv         the ‘d’ or ‘database’ driver
gxxxx.drv         the ‘g’ or ‘generate’ driver  -form rep chart
jxxxx.zip         jar file

Applying Patch On EBS ?

-Go to Patch Directory , Unzip .
-inside patch Directory , you find Patch.DRV
-After Run Apps-.evn run adpatch
-answer on the question .
-On Some where in the question , you will be asked to apply patch name which is .DRV

Note : You Need to Enable Maintenance Mode using adadmin .

You will find Topics in Details on my blog talking about how to use adadmin and Adpatch .

Thank you
Osama Mustafa