osa – Technology Geek

Step By Step Install Database Vault 11g

Posted on March 9, 2013December 23, 2018 by Osama Mustafa in Database

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 ins_rdbms.mk 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 ins_rdbms.mk dv_off
cd $ORACLE_HOME/bin
relink all

thank you

Osama mustafa

What is ORA-00600

Posted on February 27, 2013December 23, 2018 by Osama Mustafa in Database

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 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 :

Check Alert Log .
Don’t forget to look at the Ora-00600 Tools.
If you find any Notes related to your problem use it and read it carefully.
The last option you could help is contact Oracle Support by open Services Request but provide the full information such as:

alertlog for database.
Traces.
If any change happened lately included with SR.
I post About RDA it’s useful to use it when you open SR.

Thank you
Osama Mustafa

Change SYSMAN Password

Posted on August 16, 2012December 23, 2018 by Osama Mustafa in Database

SYSMAN : User in database used for Enterprise manager .

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

Step-1

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

Step-2

emctl stop dbconsole

Step-3

edit emoms.properties ($ORACLE_HOME/localhost.domainname_sid/sysman/config/)

**emoms.properties:

FROM :

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

TO:

oracle.sysman.eml.mntr.emdRepPwd=ORACLE
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

edit targets.xml files ($ORACLE_HOME/localhost.domainname_sid/sysman/emd/)

**targets.xml

FROM :

[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=”UserName” VALUE=”SYSMAN” ENCRYPTED=”TRUE”/]
[Property NAME=”password” VALUE=”ORACLE” ENCRYPTED=”TRUE”/]

Step-4:

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

Posted on August 16, 2012December 23, 2018 by Osama Mustafa in Database

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’;

OBJECT_ID
———-

SELECT c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
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 :
1-Locks

Osama Mustafa

Cursor in EXECUTE IMMEDIATE

Posted on July 26, 2012December 23, 2018 by Osama Mustafa in Database

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

syntax :

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

example :
V_query := ‘Cursor statement’ ;

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

Link Useful :
1-Blog
2-Cursor Loop Example

Enjoy

Osama mustafa

Moving SPfile From ASM to File System

Posted on July 1, 2012December 23, 2018 by Osama Mustafa in Database

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
pfile=’/u01/new.pfile’;

Shutdown immediate ;
startup ;

5-
Check new Location For Spfile ;

Thank you
Osama Mustafa

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

Posted on June 11, 2012December 23, 2018 by Osama Mustafa in Database

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
or
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

NAME                TYPE        VALUE
------------------ ----------- -------
processes           integer     150

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

  COUNT(*)
----------
       149

SQL> show parameter sessions

NAME                TYPE        VALUE
------------------ ----------- -------
Session              integer     150

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

  COUNT(*)
----------
       149

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=

“(ADDRESS=(PROTOCOL=tcp)(HOST=10.122.28.12)(PORT=1521))” sid=’ORCL1′;

System altered.

SQL> show parameter local

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

Thank you

Osama Mustafa

SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Posted on June 10, 2012December 23, 2018 by Osama Mustafa in Database

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
                                                 TENANCE_PLAN

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 :

BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; /

Thank you
Osama Mustafa

Check who Use the same record you want to delete

Posted on June 8, 2012December 23, 2018 by Osama Mustafa in Scripts

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
,r.name 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