Posts

Showing posts from March, 2013

Future Of Oracle DBA

What is the Future of DBA ? One Question but it's been asked lot , and no one knows what is this future could be !! The technology going fast and everything become easy to use, well is this mean all DBA will sit in the future without any work ? the traditional task role of the DBA is dead which mean  Most tasks assigned to the DBA staff can either be automated or eliminated with new features of the software.

Let's Start with CNN Article that mention DBA as one of the Top Ten Promising jobs with solid job growth outlook over the next several years.The DBA will need to understand the business and craft solutions with off the shelf components to satisfy the needs of the business.which mean it will be different depending on the company for example companies will hire DBA to work with development, Other companies will hire DBA to monitor databases and enure high availability.

You can't ensure that your systems and Application working fine without experts and you can't …

First Look : Dbvisit Standby

Image
Dbvisit Standby it's tool for creating standby database to ensure high availability for Oracle Database, It's similar for Data Guard but The main differnce i notice in Data Guard you need to have EE ( Oracle Database Enterprise Editions ) But not any more with DbVisit You can Create your Standby Even With SE ( Oracle Database Standard Editions ).

In Last Three days i was testing this Product , and all i can say it's amazing and easy to use , steps are clear and even when code/Error return the description is so clear, also with Dbvisit You can Have Two way to create Standby : Command line and GUI both are easy and all you have to do is following the steps in the document.

what i notice too support for this product is amazing, Sine i download trail version i revived an email telling me if i need anything all i have to do is sent an email with error code and dbVisit Support team will answering me, Couple of days later they send me another email asking me for my feedback and …

The Fastest Way to Create SSH between Servers

In this short Topic i will provide the 3 steps to create SSH ( User Equivalence ) Without Password you can find lot of way but it's just simple way and don't need much steps to create SSH between server.

Introduction for SSH, "Ssh is a secure remote login program that is similar to rlogin and rsh. The major difference between ssh and other remote login programs is that ssh encrypts the password and other information so that it can't be "sniffed" by others as you type it. Ssh also sets up X11 connections, so the DISPLAY variable does not have to be set on remote machines. Scp is another program used to securely copy files from one host to another."

Example To Use SSH in Oracle : Real Application Cluster ( RAC ).


The Steps :

#1:

Create New SSH Key

oracle@PrimNode$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):[Enter key]
Enter passphrase (empty for no passphrase): [Press enter key]
Enter s…

COLLABORATE 13 - IOUG

the countdown is start, 10 days until the biggest event COLLABORATE 13 from April 7-11 this event allow you to connect oracle user community.

As we All know "Knowledge is power" and it's time to register to increase your knowledge,different topics and all you question will be answered.

If you have any question about registration you can ask using on the social media Here.

Remember if you cannot attend to the COL13 then it's your time to register online.

Register Here

The main conference Twitter hashtag is#C13DEN.

Other Twitter Tips:
Use hashtags as often as you can, but be aware they count towards the 140-character limitWhen listing a session in your tweet, refer to it by the session ID. Retweet tweets that you find useful or insightful, or that you think your Twitter following will enjoy
Thank you
Osama Mustafa

Crack Oracle Password using DBMS_METADATA

First i would thank my friend Xavier Picamal for remind me in this way, another way to crack oracle password yo could consider it the fastest way to crack oracle password.

SQL> SELECT dbms_metadata.get_ddl('USER','TEST') from dual ;

DBMS_METADATA.GET_DDL('USER','TEST')
--------------------------------------------------------------------------------

   CREATE USER "TEST" IDENTIFIED BY VALUES '1E1A7CDA2ED2730E'
      DEFAULT TA SQL> conn / as sysdba
Connected.
SQL> alter user Test identified by Test_123 ;
User altered. SQL> conn test/test_123
Connected. SQL> alter user Test identified by  VALUES '1E1A7CDA2ED2730E'
  2  ;

User altered.

SQL>
SQL>
SQL> conn Test/test;
Connected.
SQL>  Or you could use Hash Value and use another software to cracks Such as : soonerorlater , Online Crack Website.


Thank you
Osama Mustafa


Crack Oracle Password Example

Image
Brute Force One of hacking way used application programs to decode encrypted data such as passwords or Data Encryption Just as a criminal might break into, or "crack" a safe by trying many possible combinations, a brute force cracking application proceeds through all possible combinations of legal characters in sequence. Brute force is considered to be an infallible, although time-consuming, approach.

To help prevent dictionary brute-force attacks many systems will only allow a user to make a mistake in entering their username or password three or four times. If the user exceeds these attempts, the system will either lock them out of the system or prevent any future attempts for a set amount of time.

Today i will provide example how to crack oracle password using Tools called orabf  please remember it's only example and there's million tools can do the same i will mention it later in this Topics and work on them in another thread.

Orabf is an extremely fast of…

Check total Size for Ram/memory For Oracle

Check total size of Ram/Memory For Each Database :

select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb
    from
  (
  select 'sga' nm, sum(value) val
     from v$sga
   union all
   select 'pga', sum(value)
    from v$sysstat
   where name = 'session pga memory'
  )
   group by rollup(nm)
The Orginial Post from Tom Kyte.


Thank you
Osama Mustafa

Connect to Oracle without Tnsnames.ora

Regarding to oracle documentation :

TNSNAMES.ORA is a SQL*Net configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.

Example how connection define :

orcl1 =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = my-server )(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = orcl1)
 )
)
Sometimes you can Bypass the tnsnames.ora and connect to sqlplus without even Create new connection, all you have to do is  put all of the connectivity information in your connection string this type of connection called "EZCONNECT".

sqlplus username/password@[//]host[:port][/service_name]  and to enable EZCONNECT you should add the below line in sqlnet.ora which is located in the same directory $ORACLE_HOME/network/admin
check the below examples :

NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)
The Below using the default listener port 1521 :

1- sqlplus scott/tiger@myserve…

behind the scenes : Oracle Procedure Security

One of the main Reason using Oracle PL/SQL procedures for controlling data access, One of the main reasons is insecure coding practices. One of the widely used attack techniques on applications is SQL injection. I write before about SQL injection but since it's big topic and need to be covered in more than one parts.

as reminder what is the SQL Injection : One of Hacking way to manipulate the SQL statements using web applications for access/query database.  While run Web Application, the programmer may directly use the user input without hide or even any validation. This opens a new way for the attacker to access and retrieve data . By sending specially crafted user input.

You need to know that Any dynamic SQL query using invalidated user inputs are vulnerable to SQL injection. Some methods that developers use to  prevent SQL injection are parameterized queries or stored procedures

the parameterized queries approach is the most secure way against SQL injection than the tradi…

Switchover_status is Not Allowed

Check On the Primary database and the following status will appear to you : SQL> select name ,open_mode, database_role, switchover_status from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

MAN MOUNTED PHYSICAL STANDBY NOT ALLOWED


To make sure everything is Ok on both Side Primary and Standby :

SQL> select name ,open_mode, database_role, switchover_status from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

MAN READ WRITE PRIMARY TO STANDBY  Another Check :

On Primary Database :

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

56
At Standby :
SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
56
You Solve this problem By Fire the below command :

SQL > alter database commit to switchover to physical standby with session shutdown

The Above Command Should be Run on Primary Database to Generate Special marker on Online …

Database Link Secuirty Issue

"Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services."

But did you ask your self before about the impact of using Oracle Database link ? and how to secure my database link ?


One Of Common Issue that you need to be aware of is Privileges , When you create database link most of users use DBA Role which mean user will able to do anything he want in database,which mean
who gains access to a database link can execute queries with the privileges of the DBLINK account  to avoid this try to create user with less Privileges he needs.

Another issue in 10g When you create database link check the below :

CREATE DATABASE LINK "TEST_LINK" CONNECT TO "Test" IDENTIFIED BY Test ;

Database link created.  After that …

Recreate Lost Pfile With Simple Command

If you  lost all my files under $ORACLE_HOME/dbs but database is still up and running, check the below :[oracle@192 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Mar 15 14:49:58 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool newfile.ora
SQL>     select name||'='||value from v$parameter where isdefault = 'FALSE';

NAME||'='||VALUE
--------------------------------------------------------------------------------
processes=150
sga_target=285212672
control_files=/u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/oradat
a/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/control03.ctl

db_block_size=8192
compatible=10.2.0.1.0
db_file_multiblock_read_count=16
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
undo_ma…

FRM-92101 - Oracle EBS

Image
The Above Picture is what you see When you are trying to get access to any forms/Screen in oracle EBS if you check the Logs ( $LOG_HOME ) :


formsweb: Forms session exception stack trace: oracle.forms.engine.RunformException: Forms session failed during startup: no response from runtime process at oracle.forms.servlet.RunformProcess.connect(Unknown Source) at oracle.forms.servlet.RunformProcess.dataToRunform(Unknown Source) at oracle.forms.servlet.RunformSession.dataToRunform(Unknown Source) at oracle.forms.servlet.ListenerServlet.doPost(Unknown Source) at javax.servlet.http.HttpServlet.service(HttpServlet.java:763) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
The Solution is Simple Just Install The below Package from Oracle Site Here , Don't forget Choose the right one regarding to your OS, And Use the below command :

rpm -ivh openmotif21-2.1.30-11 ......rpm 

Thank you
Osama Mustafa

Check Oracle EBS URL

You can the link for Login Page in oracle Apps by fire the below query in sqlplus as apps user :


Query #1 :
SELECT home_url
  FROM icx_parameters;


Query #2:


SELECT profile_option_value  FROM fnd_profile_option_values WHERE profile_option_id= (SELECT profile_option_id  FROM fnd_profile_options WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')  AND level_value = 0;


Thank you
Osama Mustafa

How to Become Oracle DBA

How to become Oracle DBA ?

This question asked on lot of blogs for Guru People , Before start thinking about that question you need to ask yourself some question.

Do you love to work with pressure, Do you Love working for long hours ?  Do you use your free time to devour the latest in technology from internet and reports? Do you love to work during weekend ? Last question is Are you able to deal with Sensitive & critical data with no mistake ? If all you answer is Yes,then welcome to Oracle DBA World where the human becoming theory.

Oracle is not easy world and it's not that hard but you need to know it's Complex, Just as note Don't expect to be rich also make your rule is "love what you do and do what you love".

 Recently i saw Threads On OTN Forum where the poster was asking for information about starting his Career as Oracle DBA, I will advice about that and hope it will be useful, if you have any programming language skills it will make your job easier …

Step By Step Install Database Vault 11g

Image
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                                            FAL…

Step By Step Install Database Vault On 10g

Image
Oracle Database Vault restricts access to specific areas in an Oracle database from any user, including users who have administrative access. For example, you can restrict administrative access to employee salaries, customer medical records, or other sensitive information.
You configure Oracle Database Vault to manage the security of an individual Oracle Database instance. You can install Oracle Database Vault on standalone Oracle Database installations, in multiple Oracle homes, and in Oracle Real Application Clusters (Oracle RAC) environments.
Today i will provide step by step how to install Database Vault on Oracle Database 10g, Notice to install it you should upgrade your database at least to 10.2.0.3 to avoid any errors.
Database Vault is very useful to protect your data from users such as DBA who has access to all tables , But the questions is who is control database vault ?
Usually there are two users to control it , Database vault owner this user is granted the DV_Owner rol…

Avoid Backup Database with corrupted Archivelog

Corrupt block seq: 34229 blocknum=1.
Bad header found during deleting archived log
Data in bad block - seq:0. bno:0. time:0
beg:0 cks:0
calculated check value: 0
No need for Oracle DBA to tell you there's corruption , usually this error indicate that archivelog is corrupted in our case Archivelog number 34229.

To Solve this problem :

1- Check Validate for this archivelog
 RMAN> validate archvielog sequence 34229;  2 - Now to avoid this error , you should delete archivelog on Os level.
3 -
RMAN > crosscheck archivelog all;
DELETE EXPIRED ARCHIVELOG sequence 34229;Then Backup database.

 Thank you
Osama Mustafa

Oracle Security Case Study

Does your security procedure protect your data?

In most of the companies , there is access to Email Systems, Intranet , networks and internet , most of these user are using the application that connected to Database ( assume that it's Oracle Database).

By Creating Security Procedure to protect database and what this database contain you create hard environment to deal with  since the three compentents are availability,  integrity and secuirty which mean if you increase the security then integrity will not be on the same level and so on,The Oracle database has several layers of security and provides auditing functionality at each level. most of then mention in Oracle Security Section website.


Password management : One of the basic steps to Enforce user to follow the rules such as : password expiration, limit password reuse, limit the number of failed logon attempts, force password complexity, lock and expire database accounts    Database Auditing to monitor user activity.  …

Limit The Access To The Database

In this Article, i explain how to limit access to database for only one user per schema which mean one concurrent user per schema.

Resource_limit should set to True
SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TURE
resource_manager_plan                string After change this parameter Bounce database.

Connect to database using sysdba privileges 
sqlplus / as sysdba create profile Only_one_user limit sessions_per_user 1;Create New User/modify old one depend on what you want:

create user test identified by test profile Only_one_user;
grant connect to test; Now Try to connect to this user using more than one terminal, if you did you will receive error
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit Thank you
Osama Mustafa



Most Common Dbcontrol issue

After while on Different oracle Forums, I notice that people Facing lot of issues with dbcontrol/dbconsole, Today i will post the most common Issue about dbcontrol and how to solve it.

Notice that sometime the Solution will be different Regarding to DB version and OS Version I will Post Some MOS notes that could help you. Sometimes you need to apply Patch and on other hand there's Workaround.

Error #1 : 

Exception in thread "main" oracle.sysman.emcp.exception.DatabaseUnavailableException: Database instance unavailable

Solution :

-Make Sure you Database is Up and Running.-Set/export ORACLE_SID.-Check if Database registered with Listener. You can get back to this MOS note :
EMCA fails with "SEVERE: Database instance unavailable" [ID 750697.1]

EMCA fails with Database Instance is unavailable. Fix the ORA error thrown and run EM Configuration Assistant again. [ID 1511262.1]

Creating dbconsole 11.2 fails with "Exception in thread "main" oracle.sysman.em…

DBMS_METADATA

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

TableSpace

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

Get All Tablespace Script :

set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
  ||  tablespace_name || ''') from dual;' from dba_tablespaces
Table

 select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
User

SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;
Role

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual;
 SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') from dual;
SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual;

Using Production Data is this Right ?

Image
Production Data Contain Sensitive information should not be shared with unauthorized people this data contain financial , Account Number , ATM passwords  ... , Most of the company contain Developers team to support applications and modify the code as they need it, But the developers need data to test the code, How to get this data, This the Question ?

I seen lot of Company Use Production Data on development database/Test Database because it's great for testing,really easy and No cost for doing this but  is this right ? My View On this Topic No production data is allowed on the development. There's lot of point to discuss to proof exactly what i mean and if it's necessary to use it then hide it with multiple ways i will talk about it.


There is a lot more chance that the data may be compromised,This data should be removed and sanitized to make it anonymous / De-personalized.I read lot of articles every article explain something different for example This article support usi…