Tag: Osama mustafa blog

Remote Diagnostic Agent

Posted on by Osama Mustafa in Database
RDA : It’s utility collect diagnostics information about an Oracle environment, And could be downloaded from https://support.oracle.com for every Operating system there’s specific version for RDA.

RDA is Command Line diagnostic tool that is executed by an engine written in the Perl programming language,The data captured provides Oracle Support with a comprehensive picture of the customer’s environment which aids in problem diagnosis, You can check the below MOS Note :
Remote Diagnostic Agent (RDA) 4 – FAQ [ID 330363.1]

Oracle Support encourages the use of RDA because it greatly reduces service request resolution time by minimizing the number of requests from Oracle Support for more information.RDA Support Most Of Operating system.and also it’s supported for Most Oracle Products.

But why should i use RDA, as i mention before oracle support encourages to use RDA to collect information and also there’s another reasons to use it :

  •  Oracle Fusion Middleware issues
  • Oracle Collaboration products.
  • Oracle Application issues.
  • Installation/configuration issues
  • Performance issues
  • ORA-600, ORA-7445, ORA-3113.
  • Upgrade and migration.
  • Developer issues
  • Oracle Database issues

The Simplest way to review RDA Output is using Html , Web browsing since after run rda.sh the output will generate on the same RDA Folder. (RDA_Output).

After review MOS note :
RDA 4 – Health Check / Validation Engine Guide [ID 250262.1]

after finishing from  a prerequisite system you need to need check before installation of an oracle product.This special check should be done in addition to the installation document and the check inside the installer,The utility called RDA with a module for Health Check called HCVE.

./rda.sh -T hcve

The sample output could be like the below :

Test Results
~~~~~~~~~~~~
ID NAME RESULT VALUE
===== ====================

20 User in /etc/passwd? PASSED userOK
30 Group in /etc/group? PASSED GroupOK

 Thank you
Osama Mustafa

 

Use Order By With Delete Statement

Posted on by Osama Mustafa in Database
if you are using order by in sub query with delete statement like the below :

 delete from tAccountScoring where riskscoring_id=1 and id in
(select * from tAccountScoring where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 over (order by last_update desc))

You will have an error :

Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 –  “missing right parenthesis”
*Cause:   
*Action:

The workaround to solve this issue is simple like the below :

delete from tAccountScoring
where riskscoring_id=1 and id in
(select ID  from (select * from tAccountScoring
where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 order by last_update))

Thank you
Osama Mustafa

Nmap and Oracle – Security Topics

Posted on by Osama Mustafa in Others
The First Question came to my mind when post this topic, What is the relation between NMAP and Oracle ? for the people who doesn’t know what is the NMAP i will talk about it but it will not be enough to give this amazing tool what its deserve .

Nmap is a powerful tool that is capable of generating a multitude of signatures depending on how it is used. However, if we understand the operation of the tool in general, it is easier to recognize its overall signature in network traffic. Dissecting the signature into sub-patterns one can differentiate between fingerprinting attempts that were successful and those that were not. It is important to understand that we have examined only one of the scan types that nmap can perform, the SYN half-open stealth scan. Several other scans are supported by Nmap: Tcp connect, FIN, Xmas, NULL, udp, ping, and even ftp-bounce. Expect to see these in the near future.

There’s lot of features Nmap can provide it to you : Host discovery, Port Scanning , OS detection , Auditing the security of a device by identifying the network connections, and Version detection

They Are two version of this Tools Command line version ( powerful one ) and GUI Version This tools available on  Windows and Linux and if you decide to go with Command line you need to be experts to do that since its need lot of practice.

You can check how the GUI and Command line look like in the below pictures :

provides all the information that is needed for a well-informed, full-fledged, precisely targeted assault on a network. Such an attack would have a high probability of success, and would likely go unnoticed by organizations that lack intrusion detection capabilities.

But how can i use Nmap with Oracle, when i do some penetration testing i use this tool as command line to dicover the vulnerability, when oracle database installed on server it‘s allow to use ports in server which maybe cause vulnerability ( the simplest Way to describe ) I cannot post everything about NMAP here since i will need new book to talk about it.
  
the Below some examples that you could use note that I use command line version, For Example something Called Oracle Query runs a given query against the Oracle database server and returns the results   

nmap -p 1521 –script oracle-query –script-args 

another one could be used called Oracle hash dump which dump the database password hashes from Oracle and MS-SQL. The results are returned in format suitable and could be store in file.

For Example : CVE-2012-3137 vulnerability,. The vulnerability exists in Oracle 11g R1/R2 and allows linking the session key to a password hash. When initiating an authentication attempt as a valid user the server will respond with a session key and salt. Once received the script will disconnect the connection thereby not recording the login attempt. The session key and salt can then be used to brute force the users password, there’s patch to fix this security issue.

To avoid such an issue you should Apply Oracle security Patches, CPU patches , And OS Patches by doing maintenance like this you can minimize Vulnerability that could be happened

Thank you 
Osama Mustafa

AWR Vs StatPack

Posted on by Osama Mustafa in Database
When you face performance issue in database the first thing coming to your mind is Automatic Workload Repository (AWR) or STATPACK reports but what is the difference between them, in this article i will try to mention as much as i can the difference between them.

1-you should be aware that AWR not exists in database 9i so you forced to use statepack, include to that steps to generate AWR much easier than STATPACK.

2-AWR hold all the information and statistics that exists in STATPACK, include to that AWR hold Additional Information.

3- in AWR you will find information called Active Session History ( ASH ) which is not exists in STATPACK.

4- To generate STATPACK you should Run Procedure to enable snapshot, you can use DBMS_JOB or schedule it using crontab.

5- AWR snapshots provide a persistent view of database statistics. A snapshot is a collection of performance statistics that are captured at a specific point in time,AWR snapshots are scheduled every 60 minutes by default.

 6- Statspack snapshot purges must be scheduled manually otherwise AWR snapshots are purged automatically using MMON.

Thank you
Osama Mustafa

Check Oracle Process On Windows Using SQL

Posted on by Osama Mustafa in Database
Usually When You want to check oracle process on Linux OS you are using “ps” command but what if you want to check and display all the oracle process on windows :

sqlplus / as sysdba

select a.sid,a.serial#, a.program, p.pid, p.spid, a.osuser, b.name, b.DESCRIPTION, p.PGA_USED_MEM   from v$session a,v$process p, v$bgprocess b where a.paddr=b.paddr
and    a.paddr=p.addr and p.background=1;

The above picture taken by SQL Developer.

RunLevel Mode In Linux

Posted on by Osama Mustafa in Operating system
As Database administrator you dealing with Different operating system everyday, most of this operating system is Linux/Unix, During the boot up for Linux the init command open files called “/etc/initab”  this file linux start decide which run level the system should booted to. After start the OS you can check “/etc/initab” using Editor (vim command).

there’s different type of run level in linux you should know about them :

  • 0 – halt (Do NOT set initdefault to this).
  • 1 – Single user mode.
  • 2 – Multiuser, without NFS (The same as 3, if you do not have networking).
  • 3 – Full multiuser mode.
  • 4 – unused.
  • 5 – X11.
  • 6 – reboot (Do NOT set initdefault to this).

The Above modes available in /etc/initaband you can check them, when you open the files you will see lines

 id:5:initdefault:

 Which indicate for default level. and you can change it.

Short Description for the RunLevels :

Runlevel 0:

Cause the system shutdown , and you can’t set this as default. no reason to do that.

RunLevel 1:

in this Level System start in something called Single User Mode which mean root user only who can log in to the system.and notice there’s no networking in this mode it will be useful for repair and maintenance.

RunLevel 2:

The System Will log in to mutli user mode which mean you can log in to any users but without networking .

RunLevel 3:

Its same as Runlevel 2 but with networking, This level is common for most linux.

RunLevel 4:

Custom Level, or Custom Boot Level ( Undefined one).

RunLevel 5:

Networking, Multi user Mode With X window Which mean when the OS end of boot the GUI screen will appear to users “Welcome Screen” and can log in, this is what you see in the Linux For example Redhat.

RunLevel 6:

Reboot your Operating System, Sure you don’t want to set this to default.

you can use any runlevel by command –> init ‘run-level-number’

Thank you
Osama Mustafa

ORA-27086: unable to lock file – already in use

Posted on by Osama Mustafa in Database
In Alert log :

Thu Feb 14 09:34:57 EST 2013
Errors in file /u01/app/oracle/admin/rdmetdev/udump/rdmetdev_ora_6321.trc:
ORA-27050: function called with invalid FIB/IOV structure
Additional information: 2
ORA-27086: unable to lock file – already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10

usually this problem occurred because NFS is Hanged,and can check OS logs to find “statd: server localhost not responding, timed out”.

1- Check rpc.stat is working and you can restart again , and you have to Restart NFS Services also

Stop :

# service nfslock stop
# service nfs stop
# service portmap stop
# umount /proc/fs/nfsd

 Start :

# service portmap start
# service nfs start
# service nfslock start
# mount -t nfsd nfsd /proc/fs/nfsd

Please follow the order, also note that when you restart the NFS service the server could hang at most for 1 minute.

Finally you should Reboot your server.

Thank you
Osama Mustafa

Basic Backup Techniques

Posted on by Osama Mustafa in Database
Oracle Provide you with More than Way to Backup Let’s talk about them shortly :

1- Hot Backup

performed on data even though it is actively accessible to users and may currently be in a state of being updated.

– Connect / as sysdba
– alter database begin backup;
– copy all data files using OS command / not necessary to copy temp datafile.
– alter database end backup;
– alter system switch log;
– alter database backup control files to ‘’;

 2- Cold backup

Here all you have to do is shutdown database Copy Files ( Controlfile , Datafiles , Redolog …. ) using OS Command.

3- Backup Using RMAN

RMAN is most powerful utility for Backup in Oracle gives you lot of options, the simple script is below :

set/export ORACLE_SID=
rman target /
run {
backup database;
backup archivelog all;
}

4- Export/import Commands

The Export utility provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

Check Oracle documentation and example here.

Thank you
Osama Mustafa

How To Monitor Oracle Database

Posted on by Osama Mustafa in Database
I talked before about Oracle Users, and The effect Of them In Our Database , Today i will Share Some Idea to monitor Oracle Database in Simple Way without any third Party

Any Company should be Concern about Powerful user in their company, i am talking here about database users. Specially In Production System.

as we all know Oracle database comes with two powerful account SYSTEM, SYS Very often individual accounts with DBA roles also are created for DBAs to perform their daily duties without using the SYS or SYSTEM accounts.These admin accounts in the Oracle database usually have the ability to manage user security, maintain database storage, and perform backup and recovery tasks.but when the both left without monitored they may perform fraudulent activities without leaving any trace, Stealing Backup, Data or even take look at some personal Data such as Credit card number , Social Number and Mobile number for example.Granting DBA access to business owners is a blatant violation of segregation of duties. However, the list of powerful users should not be limited to these admin accounts. Users with special database privileges such as UPDATE ALL TABLE and SELECT ALL TABLE should also be considered powerful users and should be targets of database monitoring.

 usually you need to monitor different activities on your system/applications or database.the  best practices of monitoring database for example : logon/logoff activities of SYS/SYSTEM, database schema structure changes and DML (update, insert, delete) on different tables such as tables contain sensentive data ( bank account …. ) .

 Oracle Provide with basic and Easy tools to do all the above such:
1- Basic Oracle Audit Trail 

By enable AUDIT_TRAIL Parameter ( DB,OS ,NONE) Check Oracle Documentation here, For Example if you set this parameter to OS you should Check AUDIT_FILE_DEST, On another hand if you set this parameter to DB the audit trail will be recorded in a system table named SYS.AUD$ and e SYS user will be audited if the AUDIT_SYS_OPERATIONS parameter is set to TRUE.

Oracle audit utilities allow one to audit by session, user, action and object.   also privilege and object can be audited For example, to audit the TEST user’s login activity, one can issue the following statement: AUDIT SESSION BY TEST.In addition to the standard audit features, Oracle’s finegrained audit package DBMS_FGA allows the monitoring of data access based on content. One can specify value-based audit policies using SQL statements while using the finegrained audit package.

2- Create Your Own Trigger

Triggers are database procedures fired off by a specified event. Database triggers can be associated with a table, schema or database. They also can be used as a complementary mechanism to the standard features of Oracle audit facilities

3- Emails 

You can enable this feature by command line or by OEM, This utility allow you to receive any error in alert log.

4-LOGMINER/Archivelog

LogMiner Can detect wrongdoings by the database users. Most production databases have turned on the archive log to write archive logs to multiple destinations. The archive logs are used to restore Oracle databases to a point in time. All DDL and DML activities can be reversed using the archive logs. The fraudulent activities of SYS or SYSTEM are recorded there.You can Check V$SQL_TEX.

Thank you
Osama Mustafa

Corruption In Oracle Database

Posted on by Osama Mustafa in Database
There are two types of corruption in Oracle database physical and logical what is the difference between them,Database written to blocks if this write failed then Database Corruption happened Sometimes because I/O Problems, Power Problem … etc which cause no time for header to been updated, usually Oracle Corruption will not effecting on your work until you try to read this block. There are two types of Corruption:

1- Physical Corruption  (Media Corruption)

2- Logical Corruption (Soft Corruption) 

The Details can be Find in Alertlog
ORA-01578:ORACLE data block corrupted (file # string, block # string)


Physical Corruption :

this kind of corruption can be happened when I/O Problems, Memory Failure, Server Controller

Regarding to Oracle documentation the corruption could be happened by:

  • Bad header:the beginning of the block (cache header) is corrupt with invalid values.
  • Block is Fractured/Incomplete:Information from the block header does not match the block tail
  • Block checksum is invalid
  • Block is misplaced
you can DB_BLOCK_CHECKSUM init parameter are used to identify if the block was changed by something external to Oracle and after the block was last written by Oracle. You can check the Parameter documentation Here.
Logical Corruption:

this kind of corruption you will not find it alertlog only by DBVerify utility, also be noted if db_block_checking Parameter is enabled, it may produce the internal error ORA-600 [kddummy_blkchk]

So , the difference between both kind is clear , Logical Corruption is Header/Footer and database will try to read that block , In the physical Corruption Something prevent us to read that block.

How can i know i have Corruption:

1- RMAN
2- DBVerify

DBVerify :

DBVERIFY is an external command-line utility that performs a physical data structure integrity check on an offline database. It can be used against backup files and online files (or pieces of files). You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored or as a diagnostic aid when you have encountered data corruption problems.

Documentation is here.

RMAN

Rman could Detect both corruption logical/physical , check the below script that could check if the database contains corruption without even take backup:

RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;
OR
RMAN> run {
allocate channel diskl1 type disk;
allocate channel disk2 type disk;
allocate channel disk3 type disk;
allocate channel disk4 type disk;
backup validate check logical database;
}

The above script to check Database Validation.


Which Is Better :

  1. RMAN can be run with PARALLELISM option
  2. DB Verfiy Check Empty Block.
  3. Both Tools Can check for Rang Blocks.

RMAN:
VALIDATE DATAFILE 1 BLOCK …. to ……

DBV:
start=… end=….

Some Other Blogs Could Be useful :
1- Database Corruption
2-  Other Tools To Check Corruption

Thank you
Osama mustafa