Step By Step Install Database Vault 11g

I talked before about how to install Oracle Database Vault 10g But now i will talk about 11g and how to install it ?

in database 11g database vault installation become more easier, all you have to do is check the box to install that option and even if you forgot that you can install it later by run script

the below is screen shot for the installation for database 11g , the idea is to see where to choose Database Vault features:

If you check the below screen you see that in “Select Option” you can choose Database Vault and while installation is running it will be installed :

the installation will remain in normal procedure , If you want to check it’s installed or not you can go with v$option : 

SQL> select * from v$option where parameter =’Oracle Database Vault’;
PARAMETER                                                        VALUE
——————————- ——————————–
Oracle Database Vault                                            FALSE

to enable database vault make sure database , dbconsole and listener are shutdown:

[oracle@prim u01]$ cd /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/
[oracle@prim lib]$ make -f dv_on lbac_on ioracle

SQL> select * from v$option where parameter =’Oracle Database Vault’;
PARAMETER                                                        VALUE
Oracle Database Vault                                            TRUE

Please notice that i use Redhat in windows you need to rename some files read Oracle Documentation.
To disable Database Vault the same thing but :

cd $ORACLE_HOME/rdbms/lib
make -f dv_off
relink all

thank you 
Osama mustafa 

What is ORA-00600

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

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 log and see this error, a trace file is generated and an entry is written to the alert.log with details of the trace file location, trace file provide you with more information about the error that could help you to solve it. you can check the below document how to use trace file :

Note 453125.1 11g Diagnosability Frequently Asked Questions
Note 443529.1 11g Quick Steps to Package and Send Critical Error Diagnostic Information to Support[Video]

Most Common Reason for ORA-00600 File-Corruption, Failure in Hardware, I/O , or memory, to solve this error you need to do some steps before Open Services Request :

  1. Check Alert Log .
  2. Don’t forget to look at the Ora-00600 Tools.
  3. If you find any Notes related to your problem  use it and read it carefully.
  4. The last option you could help is contact Oracle Support by open Services Request but provide the full information such as:
      1. alertlog for database.
      2. Traces.
      3. If any change happened lately included with SR.
      4. I post About RDA it’s useful to use it when you open SR.

Thank you
Osama Mustafa

Change SYSMAN Password

SYSMAN : User in database used for Enterprise manager .

Be notice any failed with these steps you to recreate EM again .

SQL> conn sysman/sysman1@oem
SQL> password
Changing password for SYSMAN
Old password:
New password:
Retype new password:
Password changed

emctl stop dbconsole
edit ($ORACLE_HOME/localhost.domainname_sid/sysman/config/)


oracle.sysman.eml.mntr.emdRepPwd=”c7021fd3720a0f18″ replace with PASSWORD
oracle.sysman.eml.mntr.emdRepPwdEncrypted=”TRUE” replace with FALSE



edit targets.xml files ($ORACLE_HOME/localhost.domainname_sid/sysman/emd/)
[Property NAME=”UserName” VALUE=”80ec9025e45b2d20″ ENCRYPTED=”TRUE”/]
[Property NAME=”password” VALUE=”94124d177df7c5d9″ ENCRYPTED=”TRUE”/]

TO :

Replace username value with “SYSMAN” and password value with “SYSMAN PASSWORD”

[Property NAME=”password” VALUE=”ORACLE” ENCRYPTED=”TRUE”/]

emctl start dbconsole 
Step-5 (in case sysman is locked)
Select Username , account_status from dba_users where username=upper(‘sysman’);
If its locked then
alter user sysman account unlock ;
Thank you
Osama Mustafa 

Locking In Oracle

Locking in Oracle is one of the most common problem we will face as database administrator.

 is the locking Effect on Database performance ?

Yes . impede a transaction from finishing , since the Lock query Take long time running .

When the Locking Happened ?

I will Give you example :

Let assume that we have two Users Each Of them Update on the same table like the following :

User 1 :

SQL> update test set name=’lock’ where id=1;

1 row updated.

User didn’t commit here .

User 2 :
SQL> update test set name=’lock2′ where id=1;

User 2 will be waiting

Inforamtion about locks :
1-Locks scripts One.
2-Locks Scripts Two

Another way to Lock :

performing a DDL (alter,create….) and get an ORA-00054 error.

ORA-00054: resource busy and acquire with NOWAIT specified

 to solve this issue

SQL> select object_id from dba_objectswhere owner=’Username’  and object_name=’Table’;


SELECT c.owner,
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id
and a.object_id=;

Refer also to :

Osama Mustafa


Sometimes you want to use cursor without Open Cursor,you can use ref cursor .

syntax :

   SQL_Text varchar2
(32760) := 'qurey'; --your query goes here
   cur sys_refcursor
open cur for SQL_Text;

example :
V_query := ‘Cursor statement’ ;

rc sys_refcursor;
open rc for ;
fetch rc into variable;
exit when rc%notfound;
<do your process.>
end loop;
close rc;

Link Useful :
2-Cursor Loop Example 


Osama mustafa

Moving SPfile From ASM to File System

its Easy Steps


SQL>Show parameter Spfile ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora


Create pfile=’/u01/new.pfile’ from spfile ;


Create spfile=’/u01/app/oracle/product/10.2.0/db_1/dbs/newspfile.ora’ from


Shutdown immediate ;
startup ;

Check new Location For Spfile ;

Thank you
Osama Mustafa

ORA-12516: TNS:listener could not find available handler

after the increasing the Number processes in the Oracle database, it solved the problem.

But Before this , i am gonna show you some steps to check the process via OS and Database LETS BEGIN :

  • check if the number of connections reaches the database’s  process parameter using following Unix command:
ps -ef | grep oracleSID | grep -v grep | wc -l
ps aux | grep oracleSID | grep -v grep | wc -l
  •  check if the number of connections reaches the database’s  process parameter using following Database Command :
SQL> show parameter processes

------------------ ----------- -------
processes integer 150

SQL> select count(*) from v$process;

SQL> show parameter sessions

------------------ ----------- -------
Session integer 150

SQL> select count(*) from v$session;


Now We need To Increase the Both Parameter By :

SQL> alter system set processes=300 scope=spfile;

System altered.
SQL> alter system set Session=300 scope=spfile;

System altered.

If this Solution Not Work For you , Try this One :

SQL> alter system set local_listener=

System altered.

SQL> show parameter local

NAME                                 TYPE        VALUE
———————————— ———– ——————————
local_listener                       string      (ADDRESS=(PROTOCOL=tcp)(HOST=1

Thank you 

Osama Mustafa


Oracle Database Resource Manager (the Resource Manager) : 
enables you to optimize resource allocation among the many concurrent database sessions.

When database resource allocation decisions are left to the operating system, you may encounter the following problems:

  • Excessive overhead.

Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.

  • Inefficient scheduling

 The operating system deschedules database servers while they hold latches, which is inefficient.

  • Inappropriate allocation of resources

The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.

  • Inability to manage database-specific resources, such as parallel execution servers and active sessions

Check The Resource Manager On your Database :

SQL> show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      SCHEDULER[0x3003]:DEFAULT_MAIN

All this is Introduction about the Error that appear when you upgrade to 11g , you will getting the following messages in the alert.log.

Setting Resource Manager plan SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Feb 05 22:00:03 2009
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Feb 05 22:00:39 2009
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Solution to  Disable the resource manager , Steps to Do this :

1-set the current resource manager plan to null

alter system set resource_manager_plan='' scope=both 

2-change the active windows to use the null resource manager plan 

    execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
    execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

3-run :

SQL> execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

You Can Also Disable SQL tuning By :

client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);

Thank you
Osama Mustafa

Check who Use the same record you want to delete

Just Run the Below Script and after this kill the session :

  select owner||’.’||object_name obj
 ,oracle_username||’ (‘||s.status||’)’ oruser
 ,os_user_name osuser
 ,machine computer
 ,l.process unix
 ,’||s.sid||’,’||s.serial#||’ ss
 , rs
 ,to_char(s.logon_time,’yyyy/mm/dd hh24:mi:ss’) time
 from       v$locked_object l
 ,dba_objects o
 ,v$session s
 ,v$transaction t
 ,v$rollname r
 where l.object_id = o.object_id
and s.sid=l.session_id
and s.taddr=t.addr
and t.xidusn=r.usn
order by osuser, ss, obj ;

To kill the session : 

Alter System kill Session ‘Sid,Serail#’ immediate ;

Thank you
Osama mustafa