Sunday, March 31, 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 hire someone to do all the tasks, Which mean DBA will be exists in the future but for concern will be on other products such Cloud, Big data, Exadata, oracle appliance.So DBA OF all Sort that needed now will be needed in the future but the main change as i see is the career path and daily task for DBA.

if you want more accurate answer check the Database version 9i,10g and 11g and now we are waiting for 12c, with every version  it's automated new more mundane tasks, But with simple way for example enterprise manager is controlling all database backup, Monitor and recovery so this indicate the DBA will have much time to architect database systems, monitor security, and many other things.

DBA is very important Job and will be found now or later check the discussion for tom Kyte about that Click here

Thank you
Osama mustafa

Friday, March 29, 2013

First Look : Dbvisit Standby


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 I am writing this article to ensure my view point is clear, I checked DbVisit Forum and  answers was within 1-3 Working days which indicate they have very active supporting team to improve this product.


The Below Figure describe Dbvisit Standby architecture :




 There's no need to mention Creation steps since everything already included in documents but the below will find some screen shot for the installation , Most of the steps done on Primary Database all you have to do on standby 3 steps as i remember :

On Primary Database :















Standby Screen Shot :







Thank you
Osama Mustafa

Thursday, March 28, 2013

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 same passphrase again: [Pess enter key]
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
33:b3:fe:af:95:95:18:11:31:d5:de:96:2f:f2:35:f9

#2:

This Step will Just copy the password File Generated in Step 1 to Server 2.

oracle@PrimNode$ ssh-copy-id -i ~/.ssh/id_rsa.pub You need to use ip/hostname to another server.
oracle@Server2 password:
Now try logging into the machine, with "ssh 'remote-host'", and check in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.

#3:

Just Check

oracle@PrimNode$ ssh Server2
Last login: Thu Mar 28 01:54:21 2013 from primora10g
[Test it no password]
oracle@Server2$


Thank you
Osama Mustafa

Wednesday, March 27, 2013

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 limit
  • When 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

Monday, March 25, 2013

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


Sunday, March 24, 2013

Crack Oracle Password Example

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 offline brute force/dictionary attack tool that can be used when the particular username and hash are known for an Oracle account.  Obviously the speed of the brute force attack slows down the longer the amount of characters that it is trying to brute force with but for short username/hash combinations it can be over a million tries per second. 

and you can visit the website here.

Check the below example :


set head off
set feed off
set verify off
Spool Hacker.lis
select password||':'||username from dba_users
Spool off ;

Output sample for the file look like :

PASSWORD||':'||USERNAME
-------------------------------------------------------------
2D594E86F93B17A1:SYSTEM
8A8F025737A9097A:SYS
4F538DF5F344F348:MGMT_VIEW
FFF45BB2C0C327EC:DBSNMP
2CA614501F09FCCC:SYSMAN
BCD8F9C79618E694:MARIA
C43474F068EDB100:MARK
FB129C4425FBCF2E:TEST
224D9FB5D377B8E1:JENNIFER
6399F3B38EDF3288:HR
F894844C34402B67:SCOTT
if you have any tag on the column remove them and save the file again since Orabf  don't read these tags













Because i am using version 0.7.5 the command should be like :
orabfscript hacker.lis -c default.txt
Remember Default.txt is the file already exist in the software and output append inside this file.

Small trick !!! i will provide another example using another software such as :
  • Repscan 
  • Checkpwd 
  • John The Ripper
  •  bfora
Thank you
Osama Mustafa

Saturday, March 23, 2013

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

Thursday, March 21, 2013

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@myservername/orcl1

Different Listener port will be like the below :

2-  sqlplus scott/tiger@myservername:1522/orcl1

3-   sqlplus USER/PASSWORD@//myservername:1521/orcl1

and if you want to disable EZCONNECT For Secuirty Reasons add the below line in :

NAMES.DIRECTORY_PATH=( tnsnames)

Thank you
Osama Mustafa

Tuesday, March 19, 2013

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 traditional approach of joining string to build a dynamic SQL string, in the second type usually leads to data format problems, you have to worry about how to encode the parameter and you need each company have it's own way to do that :

Query_sql = "SELECT * FROM emp where emp_id = :emp_Id";

A stored procedure is a database object just like table, Group of SQL statement that form a logical unit and perform a particular task to execute it you need to call it using Procedure name mostly is used as container for the code but the question is if i use them in my Code is this make me secure against SQL Injection ?
Answer also is simple Not always because if code not set properly then SQL Injection could be happened again.

CREATE OR REPLACE PROCEDURE Test (Param_1 IN VARCHAR2) AS
       sql VARCHAR;
       code VARCHAR;
BEGIN
   Sql := 'SELECT emp_id, Emp_Name, Job, Sal WHERE' +
          ' Emp_Name=''' || Param_1 || '''';
   EXECUTE IMMEDIATE sql INTO code;
END;

the Value Param_1 will taken from user input  concatenated with the string,The user input is enclosed in the single quotes and concatenated to a string to form SQL query.so the problem is related to this  Instead of the parameter being a search string to the SQL query, the user input has become the part of the query as it is enclosed inside the single quotes. If the user enters the values as 1' or '1'='1'.
Then This Stored Procedure is Not secure as we think.

So How to Write Secure Procedure ?

One Answer : Test Your Code and You should know what you write, If you query working fine that not mean its secure and no hacker will get in.

Validate inputs that comes from users, also like i mention before Use parametrized stored procedure with embedded parameters, don't forget to Use a low privileged users and give right and correct role/Privileges to application users and finally avoid use of dynamic SQL queriesif you have another way.


so as conclusion is if you are using Stored procedure correctly then you are pretty much safe from SQL Injection and always remember when you attend to do this don't use :

1 - Dynamic SQL inside the Stored procedure.
2 - try to avoid concatenated string.


Thank you
Osama Mustafa



Monday, March 18, 2013

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 Redolog Headers Which Mean transfer the archive to standby , after that the status should be changed.

Please check the below Link from OTN Forum that describe the same problem also and The Solution is Mentioned already there:

1-  Not allowed status in primary db switch_over status  Press here.
2- Not allowed Issue while DR switchover Press here.

Thank you
Osama Mustafa

Sunday, March 17, 2013

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 check the below table :

SQL> select name, userid, passwordx from sys.link$ where name='TEST_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM';

NAME     USERID  PASSWORDX
--------------------------------------------------------------------------------
TEST_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM OSAMA   05CB53401E442441B428B900A97DE31A10

as you see the password is saved as hash, and can be decrypt .


But what if :

SQL> SELECT DBMS_METADATA.GET_DDL('DB_LINK','TEST_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM') from dual ;
Check the output below :

CREATE DATABASE LINK "TEST_LINK"
CONNECT TO "Osama" IDENTIFIED BY VALUES '05CB53401E442441B428B900A97DE31A10'

another security Issue of using Database link.

Imagine what could be happened next.

Thank you
Osama Mustafa

Friday, March 15, 2013

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_management=AUTO

NAME||'='||VALUE
--------------------------------------------------------------------------------
undo_tablespace=UNDOTBS2
remote_login_passwordfile=EXCLUSIVE
db_domain=
dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes=10
background_dump_dest=/u01/app/oracle/admin/orcl/bdump
user_dump_dest=/u01/app/oracle/admin/orcl/udump
core_dump_dest=/u01/app/oracle/admin/orcl/cdump
audit_file_dest=/u01/app/oracle/admin/orcl/adump
audit_trail=TRUE
db_name=orcl

NAME||'='||VALUE
--------------------------------------------------------------------------------
open_cursors=300
os_authent_prefix=
pga_aggregate_target=94371840

23 rows selected.

SQL> spool off ;

Just edit the new file and startup.

Thank you
Osama Mustafa

Wednesday, March 13, 2013

FRM-92101 - Oracle EBS







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

Tuesday, March 12, 2013

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

Monday, March 11, 2013

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 it's very useful to understand what you see as DBA you work with Development team and negotiate them about performance and their requirement and understand them is the best, On the other hand if you don't have any Experience you can't stop here and saying to yourself i can't become Oracle DBA Start learning and it's not bad idea to take courses such as SQL-Fundemtals I & II.

Don't forget another thing start working on your Oracle training and education Reading is not enough it's only give you concept and the idea behind that thing, Learning operating system administration skills, and what i mean by Operating system not just windows you need to learn about Linux, Unix .... Etc and how to manage them and using command line, learn batch & Shell Scripting is very useful, Don't forget to take look at Oracle Documentation everytime you can is the first and the best reference all the time, You need to learn about something read from the source don't start with others articles, and don't understand me wrong with that everyone has his point but stat with source and see other point to create yours.

There's also oracle magazine latest technology and Some tips/trick to do something you can subscribe with it,Maybe you don't understand something so you need someone to explain it to you Oracle OTN Forum is available to you for free, experts and guru will answer you, Books will increase your knowledge and proof your point.

Create your own Vmware and test don't make something passed without testing, Practical way will proof and increase your knowledge, then let's make all the above as point :

  • Attend training courses
  • Read Books
  • Read Magazine.
  • Visit Oracle Documentation site and blogs.
  • Check OTN Forum
  • Talk with DBA
Bookmark this website,
Very Useful & Good Starting Point to become Oracle DBA.


long topic to discuss and need of you lot of working, If you want to be a DBA, find one or two methods of acquiring knowledge that you are comfortable with. Take training courses, read books, whatever. and i will update this topic also to become Comprehensive.

Thank you
Osama Mustafa

Saturday, March 9, 2013

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

Friday, March 8, 2013

Step By Step Install Database Vault On 10g



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 role and can manage database role and configurations, the username must be minimum 2 and maximum 30 character , the password for this user should be complex.

another user called : Database Vault manager which is granted DV_ACCTMGR role, and used to manage database user account , this user is created to facilitate separation duties which mean while you install you can only create one user do all this jobs , the username should be minimum 2/maximum 30 character and the password is complex .

The below is screen-shot for installing Database Vault (Notice Database and Listener should be shutdown) :

















Thank you 
Osama Mustafa




Thursday, March 7, 2013

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

Wednesday, March 6, 2013

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.
  •     Fine Grained Auditing to define specific conditions necessary for an audit record to be generated, resulting in a more meaningful audit trail
  •     Database Resource Manager to set resource limits and quotas on the amount of various system resources available to users
  •     Roles to manage object privileges
  •      Oracle Label Security for more sophisticated row level security
  •     Data Encryption to provide an additional layer of protection

Which Kind Of Security Plan you follow , Do you think the Basic Steps to Secure Database will be enough or should someone enable auditing , install database firewall .... when you answer consider that more security means it will be hard to deal with application and environment.

tell me your case about the security ? what you think ?

Thank you 
Osama mustafa  
 

Monday, March 4, 2013

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



Sunday, March 3, 2013

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 :

  1. -Make Sure you Database is Up and Running.
  2. -Set/export ORACLE_SID.
  3. -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.emcp.exception.DatabaseUnavailableException: Database instance unavailable" on Windows [ID 1332546.1]

Error #2:

Running EMCA fails with "SEVERE: Invalid username/password"

Solution :

  1. -Make Sure You Insert Correct Password.
  2. -In Some Version you can use "Password" 
  3. -Recreate Password File.

You can Get back to this MOS note :
Problem: Running EMCA fails with "SEVERE: Invalid username/password" [ID 744176.1]

EMCA fails with SEVERE: Invalid username/password or database/scan listener not up or database service is not registered with scan listener. [ID 1330272.1]

Error  #3:

WARNING: ORA-01031: insufficient privileges

Solution

You need to check the logs, different error could be appear.

Failed to unlock all EM-related accounts
Alter User <> Identified By
Also Check MOS Notes :
Troubleshooting the 'ORA-01031: insufficient privileges' error when using EMCA to Create or Drop DBconsole [ID 358201.1]

Error #4:

SEVERE: Listener is not up or database service is not registered with it. Start the Listener and register database service and run EM Configuration Assistant again .

Solution :

  1. - set/export ORACLE_SID
  2. -Check your Listener.
  3. -Check if Database Registered.
Check the below MOS note :

Troubleshooting the EMCA Error "ORA-12514" "Listener is not up. Start the Listener and run EM Configuration Assistant again" When Creating or Dropping DBConsole [ID 368591.1]

EMCA Fails With Error "ORA-12541: TNS:no listener" and "Listener is not up. Start the Listener and run EM Configuration Assistant again" [ID 975024.1]

Saturday, March 2, 2013

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;


This is the Most common Usage for this package more information read Oracle Documentation
 
Thank you
Osama mustafa

Friday, March 1, 2013

Using Production Data is this Right ?

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 using Prod Data. after reading this blog ask yourself one question how the production  make job easier ? by let developer/unauthorized people looking to customer Data !!!!  Different point of view Customer want their data to be secure and the employees want to test the code and something easy and real to use.

Check this The Ponemon Institute has come out with some interesting (and scary) data on data security during development and testing.


 This chart shows what Breach of data. It shows a lot of sensitive data such as card holder data, customer data, credit card information and business confidential information.

Personally I prefer to use a subset or dummy data use Red-Gate Data Generator, 
There's lot of security issue can be lead by Using Prod data for testing/development such as  severely compromise its confidentiality, even leading to legal action.

Take this example Hannaford Brothers,In March, the Maine-based Hannaford Brothers grocery store chain
announced that 4.2 million customer card transactions had been compromised by the hackers. More than 1800 credit card numbers were immediately used for fraudulent transactions.

after all this examples is it Ok to use Prod Data On test ?  Do you have a legitimate reason?  do you have
Security,Encryption, Firewalls, Breech Detection , Include to that There's difference Security Rules On prod data and Test Data , Production database For authorized People, Privacy, Auditing,  Roles , Privileges lot of conditions to access it. on the other hand Development data Frequently wide open,Dozens  of employees  have access, Access from many to unlimited places, Home access And you still Want to use Production Data ? Do you programs care or know the difference ?

Some Rules you have to follow :

  • Make your employees aware of the policies and procedures.
  • If it is  possible to not use production data, take that option.use  alternate ways of testing scenarios.
  • Ensure that production data is masked or scrubbed when it is moved out of the production environment.
if it's necessary to use your Production Data Then Do it right by scrambling (Scrambling is the function of replacing a character (or byte) of data with a different character (or byte) of data) Oracle Provide you with solutions to do that called data masking By write your own function that will Scramble data for you.
another option could be use is encryption (Encryption is a series of algorithms used to encrypt data into nonsensical characters (not in the English alphabet)). There's Another way you can hide production data NULL’ing Out,Substitution, Gibberish Generation ....


 Finally Using live data in non-production is either illegal or expensive. For the companies using it illegally, it’s only a matter of time before somebody slips up and the practice is discovered
Using live data in non-production is either illegal or expensive. For the companies using it illegally, it’s only a matter of time before somebody slips up and the practice is discovered. For the companies paying extra to keep their developers compliant, they’ll find themselves resistant to new development and undercut by companies who’ve used their data in a strategic way. In the long run, the tiny benefit is just not worth the risk. - See more at: http://www.businesscomputingworld.co.uk/are-you-using-live-customer-data-outside-of-your-production-database/#sthash.8r06L9KL.dpuf

Using live data in non-production is either illegal or expensive. For the companies using it illegally, it’s only a matter of time before somebody slips up and the practice is discovered. For the companies paying extra to keep their developers compliant, they’ll find themselves resistant to new development and undercut by companies who’ve used their data in a strategic way. In the long run, the tiny benefit is just not worth the risk. - See more at: http://www.businesscomputingworld.co.uk/are-you-using-live-customer-data-outside-of-your-production-database/#sthash.8r06L9KL.dpuf
Read Ponemon Institute Report

Thank you
Osama Mustafa