Showing posts from January, 2013

check the root blocker and kill it

Today On OTN this question has been asked how to check the root killer and kill it, While searching and browsing i found nice article i would love to share written by Moid I would thank him for his sharing and amazing topics

Step #1 :
Create a view called root_blocker, and Grant read only permission to  public

Create or replace view root_blocker
       to_char(a.logon_time, 'MM/DD/YY HH:MI:SS') as Logon_Time,
       b.username||'@'||a.machine as blocker,
       '('||a.sid||','||a.serial#||')' as root_blockers_sid_n_serial#,
       'ALTER SYSTEM KILL SESSION '''|||a.sid||','||a.serial#||''' IMMEDIATE;' as "10g Command to kill session",
       'ALTER SYSTEM KILL SESSION '''||a.sid||','||a.serial#||'@'||a.inst_id||''' IMMEDIATE;' as "11g Command to kill session"

Install/Deinstall Oracle Lable Secuirty

Oracle provide you with amazing tools to secure your data, and make sure no one will access to it. One of this amazing tools is Oracle label security (OLS) allows access control down to individual rows based on attached labels. Similar functionality can be reproduced using Fine Grained Access Control (FGAC) but OLS provides an out-of-the-box solution to row-level security. In this article I'll present a simple example of the configuration of OLS.

To understand OLS you could read oracle documentation about it, OLS has two parts One binary which installed through Oracle Universal Installer (OUI) and to know if you have been install it you can check log in information to sqlplus  banner
Oracle Label Security  ....
All this in the 11g become more easier since you can install all features while you choose enterprise edition and use chopt to enable and disable :
chopt enable lbac
chopt disable lbac  talking about second part which can be installed via PL/SQL packages,all this can…

The Effect of the Weak Password

Default/weak passwords.

There is no need to discuss this point any more one of the most Security breaches happened because weak password, can you guess most popular passwords, I provide list for the most common password all the time is your one of them ?

1. password.
2, 123456.
3. 12345678.
4. abc123.
5. qwerty.
6. monkey.
7. letmein.
8. Dragon.
9. 111111.
10. baseball.

Simple steps keep you away in the safe side, No need for software or third party products to change your password in Database 10g, 11g Oracle provide to new features. Noted that what work on 10g is working on 11g.

Oracle Database 10g

In database 10g Oracle provide an example for password verifications functions you can write you own code and you can use
This function must be created in SYS schema, when you run the script you enable the following:
•    alters the default parameters for Password Management this mean all the users on the system have Password Management
•    sets t…

Oracle ACE

I am writing this topic while i don't know what should i write, So happy To Join The Most Amazing Community In the World, Oracle ACE world , Today I join this Amazing Community and i would take this opportunity and thanks my family and friends , and how can i forgot the people who motivate me and show me how this world and community is amazing Kamran, Hans,Kai,  Gokhan , Eddie, Aman and lot of others guys.

Oracle is my world, this is what i love i will love it to the end .

Thank you Guys again

Osama Mustafa

SQL Injection In Oracle

I post earlier about database threats, but today i will go more deeply and start describe these threats.
SQL injection is a type of security exploit in which the attacker injects Structured Query Language (SQL) code through a web form input box, to gain access to resources, or make changes to data,Attackers can execute arbitrary SQL commands through the web application.

It enables an attacker to execute unauthorized SQL commands,For example, when a user logs onto a web page by using auser name and password for validation, a SQL query is used. what do you need ? just web browser.

to start using SQL Injection you need to look for  search page, Login page or even comment ( feedback), also you should be familiar with HTML for example Which Way page POST or GET commands, If POST is used, you cannot see the parameters in the URL check the below :

Some Programming language not take any method, most of them new Language such as ASP, PHP and JSP.

check simple example how attackers try to hack…

monitor Oracle thru Scripts

In this topic i post some scripts to monitor Oracle Session and process you can download them :

1- List of oracle's processes Download here.
2- Active  Inactive Sessions Download here.
3- Active  Inactive Sessions Download here.

Thank you
Osama mustafa

BarCode Fonts

If you look for bar code fonts to make your reports Look more nicer , I uploaded Fonts and you can download them On the Below Link , Hope its useful

BarCode Fonts Download Here

And For Tadqeet Code That Use for Arabic , Called Tafqeet :

Tarqeet You can Dowload it Here

Thank you
Osama Mustafa

DataGuard MOS Notes

Find the below very Useful MOS Notes that could help you In Data Gaurd

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE [ID 1075908.1]
Script to Collect Data Guard Physical Standby Diagnostic Information [ID 241438.1]
How to run DBUA in silent mode? [ID 422737.1]
Bug 15927527 : ORA-1555 ON ACTIVE DATA GUARD
Rman-06571: Datafile 1 Does Not Have Recoverable Copy [ID 1336872.1]

Thank you
Osama Mustafa

Database HealthCheck

Stable Environment without any problem need to be monitored by DBA, Trying To Check Database With Specific Period or even everyday it's called HealthCheck All that to avoid any Problem That could occur in the future which will prevent users and customer doing their work.

Therefore any DBA should perform Healthcheck for the database By Check the below :

1- Check Alert Log.

Check alert log is very important step, which its Indicate for any Error occurs in Database level, So the Data is always append, don't forget to rotate alert log or purge if you are using 11g you can use ADRCI tools.
you can find alert log location :

in 10g

SQL> show parameter background_dump_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/admin/orcl/bdump in 11g

SQL> show parameter diagnostic_dest

NAME                                 TYPE        VALU…

Check TimeZone Before Upgrades

Lot of question Regarding to TimeZone While Upgrade Oracle Database, You have to Check TimeZone When you have Data Or Schedule Job With Timezone info.

There are three Cases I will Describe them later In this article , but First You need to check Timezone

SQL> select version from v$timezone_file;
After Doing That you will Have Number As Output , This Number Should Compare to 4 ( value ) with Three below Cases :
1- Output =  4

You could Contuine with Upgrade without Do anything

2- Output >  4
Actions for the DSTv4 update in the patchset [ID 553812.1]
Updated DST transitions and new Time Zones in Oracle Time Zone File patches [ID 412160.1]

3 - Output < 4 
You have to download utltzpv4.sqlfrom MOS (
With This Script also you have two case
if script returns no rows, there is nothing that needs to be done. Just proceed with the upgrade.

If script retunrs the detail of columns that contain TZ data which may be affected by the upgrade, then follo…

Stop Standby Database And Work As Standalone

Sometimes you need to disconnect Standby Database, Or even Delete the Configuration And let the primary Is StandAlone Database to do that you have Temporary way or Permanet Way :

 1 - Temporary :

Run the below command in standby Database:

SQL> alter database recover managed standby database cancel;
2- Permant With The Following Steps :

Run the below command in standby Database: 
SQL> alter database recover managed standby database cancel;
 Shutdown Standby Database.
change the parameters LOG_ARCHIVE_DEST_n/LOG_ARCHIVE_CONFIG (if set).
 Remove tnsnames.ora for standby In primary Database.

Thank you
Osama mustafa

Compare Two AWR/ Different Period

Very and Useful Article To learn how to compare between AWR With Different Period

Press the Link Here.


1-Use  DBMS_WORKLOAD_REPOSITORY.CREATE_BASLELINE_TEMPLATE to instruct Oracle to keep the snapshots for (for example) every Sunday.
2- generate comparison reports with the awrddrpt.sql Comparing any Sunday.

Thank you
Osama Mustafa

Moving Undo/Temporary Tablespace

To Move these tablespace Easily you can create them By Following the below Steps :

sqlplus '/as sysdba'SQL>CREATE TEMPORARY TABLESPACE TEMPFILE '/new_location/.dbf'SIZE 64M REUSE AUTOEXTEND ON NEXT 28 MAXSIZE unlimited;   SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE Drop the Old One By Run the Below Command :SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;     For Undo Tablespace Check the below Steps:   SQL>create undo tablespace  datafile '/new_location/.dbf' size 2000m;   Make New Tablespace for Database:SQL> alter system set undo_tablespace= undotbs2 ;   Drop Old Tablespace:SQL> drop tablespace undotbs including contents;Thank you Osama Mustafa

ora-27086 unable to lock file / Alertlog

ORA-27086:unable to lock file - already in use
Cause:the file is locked by another process, indicating that it is currently in use by a database instance.
Action:determine which database instance legitimately owns this file.

If you check the above Picture you will there's two Pmon process for the same instance, So what you do is the follwing

export ORACLE_SID=swf33
sqlplus / as sysdba
Shutdown immediate;

 After shutdown database another process still up use kill -9 OS command to kill it and startup database.

Thank you
Osama Mustafa

Applying Critical Patch Updates (CPU).

A Critical Patch Update is a collection of patches for multiple security vulnerabilities. Critical Patch Update patches are usually cumulative but each advisory describes only the security fixes added since the previous Critical Patch Update advisory. Thus, prior Critical Patch Update advisories should be reviewed for information regarding earlier published security fixes.its released on January, April, July &October.

Steps to Apply CPU:

Shutdown all the instance , and oracle services such as listener. ensure $PATH variable.Download Patchand unzip it.Use the Opatch like this : opatch napply -skip_subset -skip_duplicate.Startup databas.Post-Installation :

cd $ORACLE_HOME/rdbms/adminsqlplus / as sysdbaSQL> @catbundle.sql cpu applyNote : For rac database nothing diffrenece unless you have to shutdown services node by node and patching ( you can patch one node then the other one).

Useful MOS notes :
Introduction To Oracle Database catbundle.sql [ID 605795.1]


privileges Scripts

These script made by René Nyffenegger and shared here for knowledge

List user role and privileges :

select lpad(' ', 2*level) || granted_role "User, his roles and privileges" from ( /* THE USERS */ select null grantee, username granted_role from dba_users where username like upper('%&enter_username%') /* THE ROLES TO ROLES RELATIONS */ union select grantee, granted_role from dba_role_privs/* THE ROLES TO PRIVILEGE RELATIONS */ union select grantee, privilege from dba_sys_privs ) start with grantee is null connect by grantee = prior granted_role;
Check System privileges:

select lpad(' ', 2*level) || c "Privilege, Roles and Users" from ( /* THE PRIVILEGES */ select null p, name c from system_privilege_map where name like upper('%&enter_privliege%') /* THE ROLES TO ROLES …

Zombie process

Zombie process is an inactive computer process,according to wikipedia article, "...On Unix operating systems, a zombie process or defunct process is a process that has completed execution but still has an entry in the process table, allowing the process that started it to read its exit status. In the term's colorful metaphor, the child process has died but has not yet been reaped..."

Find zombie by :

# ps aux | awk '{ print $8 " " $2 }' | grep -w Z  after that use kill -9  PID

Thank you
osama mustafa

Useful Linux Commands For DBA

Show Routing Table :netstat -r
Check ORA errors in the Logs:

grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort -uInzip CPIO Files :cpio -idmv <  Sort Files By Size :
ls -l |sort -k 5
Find Command archive and move to another Folder :
find ./ -name "*.arch" -mtime +1 -exec mv {} /u01/;Find Command archive and Remove it:
find ./ -name "*.ARC" -mtime +1 -exec rm {} \;Find Command with Zip :
find ./ -name "*.ARC" -mtime +1 -exec gzip {} \; Find Command With List :
 find ./ -name "*.ARC" -mtime +1 -ls 

Thank you
Osama Mustafa

Check Default Tablespace For User and Change it

Check the Below Demo how you can check tablespace for specific User and how can  you change it

Create New User Without Determine which Tablespace:

SQL > Create User test Identified by test ;

Check TableSpace for Test User:

 SQL> select default_tablespace from dba_users where username='TEST';


Change TableSpace For Test User :

Solution One : Change Default TableSpace For Database By :

 SQL> alter database default tablespace ;

Solution two : define Tablespace When Creating User

 SQL > create user Test identified by Test default tablespace USER

Thank you
Osama Mustafa

Stage Oracle EBS R12

in this article i will clarfiy how to download Oracle EBS R12 and learn how to stage this product.

Let's start with how to download Oracle EBS R12 :

Step 1 : Go to
Step 2 : Enter Your UserName and Password to get acces to this site, and you also register for free.
Step 3 : in this step you have to select which product you need in our case "E-Business Suite" and platform compatiable with EBS , Windows, Linux , AiX and Solaris.
Step 4 : we need to download all the zip files belonging to the following parts:

Rapid Install Start Here Rapid Install RDBMS Rapid Install Databases Rapid Install Tools Rapid Install APPL_TOPStaging Oracle EBS is Easy & Simple

By Creating Stage Directory , and make sure you have enough space on that direcoty , copy download file to stage folder, and exetation for downloadable files "zip".Unzip the files and in this case you will have Stage area for EBS. Documet Dowload Here.
Thank you
Osama mustafa

ORA-01151: use media recovery to recover block, restore backup if needed

SQL> startup
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size 1288820 bytes
Variable Size 192939404 bytes
Database Buffers 402653184 bytes
Redo Buffers 2904064 bytes
Database mounted.
ORA-01172: recovery of thread 1 stuck at block 4545 of file 1
ORA-01151: use media recovery to recover block, restore backup if needed

Cause :
  There is block corruption in tablespace.

Solution :

SQL> shutdown immediate; SQL> startup mount; SQL> recover database; SQL> alter database open;

Find Resote Point Rman

Some Times you need to Restore your Database To Earlier time , and before doing that you have to check weather database have valid to do that or not

Simple Query :

SQL> select resetlogs_change#,to_char(resetlogs_time,'DD-MON-YYYY HH24:MI:SS') Last_resetlog_time from v$database_incarnation;   RESETLOGS_CHANGE# LAST_RESETLOG_TIME ----------------- -------------------- 1 11-SEP-2008 22:57:51 88033 23-MAY-2011 10:12:42Or Sometimes you could Use v$restore_point , Such asSELECT scn, time, database_incarnation#, guarantee_flashback_database from v$restore_point; Thank you Osama Mustafa

ORA-20222: Workload was not captured in the current database