Wednesday, February 27, 2013

Cancel Request In Conurrent Manager

To Cancel Request In Oracle Apps using SqlPlus  Command run the below Query :

Update Fnd_Concurrent_Requests
   SET Phase_Code = ‘C’,
   Status_Code = ‘E’
 Where Request_ID = ;



Or you can Replace Retest_id with  CONCURRENT_PROGRAM_ID.

Thank you
Osama Mustafa

Tuesday, February 26, 2013

What is ORA-00600

The ORA-600 error is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition.
ORA 600 "internal error code, arguments: [%s], [%s],[%s], [%s], [%s]"

and you should it's different Oracle errors , Because when you see this error then it's indicating for bugs, the above is general description for the error the first characterset / Number is is used with database version to identify the problem by oracle support. and maybe you will find related document on https://support.oracle.com.

When you face this error you should check the below document searching for some Notes/Patch could help you :
 Note 600.1 ORA-600/ORA-7445 Lookup tool
You need to Choose database version ( 4 digit ) and First argument in the error, also for more information how to use this tool :
 Note 1082674.1 : A Video To Demonstrate The Usage Of The ORA-600/ORA-7445 Lookup Tool [Video]
Notice when you check the alert log and see this error, a trace file is generated and an entry is written to the alert.log with details of the trace file location, trace file provide you with more information about the error that could help you to solve it. you can check the below document how to use trace file :

Note 453125.1 11g Diagnosability Frequently Asked Questions
Note 443529.1 11g Quick Steps to Package and Send Critical Error Diagnostic Information to Support[Video]
Most Common Reason for ORA-00600 File-Corruption, Failure in Hardware, I/O , or memory, to solve this error you need to do some steps before Open Services Request :
  1. Check Alert Log .
  2. Don't forget to look at the Ora-00600 Tools.
  3. If you find any Notes related to your problem  use it and read it carefully.
  4. The last option you could help is contact Oracle Support by open Services Request but provide the full information such as:
      1. alertlog for database.
      2. Traces.
      3. If any change happened lately included with SR.
      4. I post About RDA it's useful to use it when you open SR.

Thank you
Osama Mustafa

Monday, February 25, 2013

EBS 11i MOS notes

Cloning

NOTE.362473.1 : Cloning E-Business Suite Using Hot Backup for Minimal
NOTE.216212.1 : Business Continuity for Oracle Applications Release 11i
NOTE.233428.1 : Sharing the Application Tier File System in Oracle
NOTE.230672.1 : Cloning Oracle Applications Release 11i with Rapid Clone
NOTE.216664.1 : FAQ: Cloning Oracle Applications Release 11i
NOTE.135792.1 : Cloning Oracle Applications Release 11i




Patching

NOTE.174436.1 : Oracle Applications Patching FAQ
NOTE.175485.1 : How to Apply an 11i Patch When adpatch is Already Running
NOTE.181665.1 : Release 11i Adpatch Basics


Autoconfig:

NOTE.218089.1 : Autoconfig FAQ
NOTE.165195.1 : Using AutoConfig to Manage System Configurations with
NOTE.270519.1 :Customizing an AutoConfig Environment


General Notes:

165195.1 "Using Autoconfig to Manage System Configurations with Oracle Applications 11i".
218089.1 "Frequently Asked Questions about Using Autoconfig with Oracle Applications Release 11i".
217368.1 "Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i".
362135.1 "Configuring Oracle Applications Release 11i with 10g Release2 Real Application Clusters and Automatic Storage Management"
123718.1 "A Guide to Understanding and Implementing SSL with Oracle Applications 11i".
125767.1 "Upgrading Developer 6i with Oracle Applications 11i".189708.1 Oracle Reports 6i Setup Guide for Oracle Applications 11i
273888.1 Steps to Upgrade Oracle Apps 11.5.4 to Latest Version or 11.5.9
437794.1 Database Status Check Before, During And After Migrations and Upgrades
362202.1 Oracle Applications Release 11i with Oracle 10g(10.1.0.4)
165195.1 for more information on how to run autoconfig
233436.1 Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i
313418.1 Using Discoverer 10.1.2 with Oracle E-Business Suite 11i.
352843.1 HOW TO RUN A PATCH IMPACT ANALYSIS IN OAM

oracle server architecture diagram

Oracle Database 10g : 


Oracle Database 11g :



Thank you
Osama Mustafa

Check Session For Specific Request in EBS

Sqlplus apps/apps


Select request_id,oracle_process_id,phase_code,status_code from
apps.fnd_concurrent_requests where status_code='R'
where request_id in(Request_ID #1,Request_ID #2,Request_ID #3);


select r.request_id,
  r.oracle_process_id spid,
  r.oracle_session_id,s.inst_id,
  r.os_process_id process,
  s.sid,
  s.serial#,
  s.status,to_char(logon_time,'YYYYMMDD HH24:MI:SS') time,s.program,s.sql_address,
  s.paddr
  from apps.fnd_concurrent_requests r,
     gv$session s
 where r.request_id =31082866
  and r.oracle_session_id = s.audsid(+);

Thank you
Osama Mustafa

Saturday, February 23, 2013

ORA-01031: insufficient privileges When Start Windows Services

this common error in windows, and you can be occur for more than one reason and you need to check them all to make sure you did everything is right, the error prevent the oracle services to start automatically and if you check

ORADIM.LOG....
**************************
C:\Oracle\Ora11g\bin\oradim.exe -startup -sid ptdb -usrpwd * -log oradim.log -nocheck 0
Thu Nov 15 15:16:15 2012
ORA-01031: insufficient privileges


You have to check :

1- startup type for the services ( if it's set automatically )
2- Group to user ( the user should be in ORA_DBA Group )
3 - you can check log on tab in the service properties and set the username and password who responsible about this services and can do it by:

Go to Control Panel -> Administrative Tools -> Services .

Choose oracle services and then
click "properties". Select the "Log On" tab and note the account that is set to run the service.


Thank you
Osama mustafa

Friday, February 22, 2013

Remote Diagnostic Agent

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


 

Thursday, February 21, 2013

Use Order By With Delete Statement

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

Tuesday, February 19, 2013

Nmap and Oracle - Security Topics

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

Monday, February 18, 2013

AWR Vs StatPack

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

Sunday, February 17, 2013

Check Oracle Process On Windows Using SQL

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.

Saturday, February 16, 2013

RunLevel Mode In Linux

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

Thursday, February 14, 2013

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

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

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

Tuesday, February 12, 2013

WARNING: failed to read mirror side In alert log

Database Version : 11.2.0.2
Real Application Cluster --- > ASM

How error look like in Database Alert log :

WARNING: Read Failed. group:1 disk:4 AU:11852 offset:892928 size:8192
WARNING: failed to read mirror side 1 of virtual extent 1722 logical extent 0 of file 285 in group [1.4277692974] from disk DATA01_0004  allocation unit 11852 reason error; if possible,will try another mirror side
Errors in file /home/app/oracle/diag/rdbms/absnl/ABSNL1/trace/ABSNL1_ora_24108.trc:
WARNING: Read Failed. group:1 disk:4 AU:11852 offset:892928 size:8192
WARNING: failed to read mirror side 1 of virtual extent 1722 logical extent 0 of file 285 in group [1.4277692974] from disk DATA01_0004  allocation unit 11852 reason error; if possible,will try another mirror side

 For the First time when I see this error i thought i had problem in my storage (ASM) But after checking And everything was Ok i start search at Http://Support.oracle.com

Yes it's Bug , and the Bug Number is 10422126.

you can Check Some Notes related to this issue which You should download Patch and apply it or upgrade to 11.2.0.3

WARNING: Failed To Read Mirror Side 1 continuously reported in the alert log [ID 1289905.1]
WARNING: failed to read mirror side 1 of virtual extent ..... [ID 1274852.1]

Thank you
Osama Mustafa

Linkedin Profile



Also Check Here

How To Monitor Oracle 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

Monday, February 11, 2013

Corruption In Oracle 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


 

Sunday, February 10, 2013

Read ORACLE_SID and ORACLE_HOME Inside PL/SQL Code

 DECLARE 

   V_ORACLE_HOME  VARCHAR2 (2000); 
   V_ORACLE_SID  VARCHAR2 (256); 

 BEGIN 

   DBMS_SYSTEM.GET_ENV ('ORACLE_HOME', V_ORACLE_HOME); 
   DBMS_SYSTEM.GET_ENV ('ORACLE_SID', V_ORACLE_SID ); 
  
   --The below line will print Oracle_home
   DBMS_OUTPUT.PUT_LINE ('ORACLE_HOME :- ' || V_ORACLE_HOME); 
  
   -- the below line will print Oracle_SID
   DBMS_OUTPUT.PUT_LINE ('ORACLE_HOME :-' || V_ORACLE_SID); 
 END; 

Reference :
1-OTN Forum

Thank you
Osama Mustafa

Saturday, February 9, 2013

Gather System Statistics







This procedure gathers system statistics. Which is Important for Index and Tablespace. it's very Good to use in SQL performance.

There are different Mode for Gather_system_stat like the following :


  • NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces. To fine tune system statistics for the workload use 'START' and 'STOP' or 'INTERVAL' options. If you gather both 'NOWORKLOAD' and workload specific (statistics collected using 'INTERVAL' or 'START' and 'STOP' ), the workload statistics will be used by optimizer. Collected components: cpuspeednw, ioseektim, iotfrspeed.
     
  • INTERVAL: Captures system activity during a specified interval. This works in combination with the interval parameter. You should provide an interval value in minutes, after which system statistics are created or updated in the dictionary or stattab. You can use GATHER_SYSTEM_STATS (gathering_mode=>'STOP') to stop gathering earlier than scheduled. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
     
  • START | STOP: Captures system activity during specified start and stop times and refreshes the dictionary or stattab with statistics for the elapsed period. Interval value is ignored. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
For Example :



exec dbms_stats.gather_system_stats('Start');exec dbms_stats.gather_system_stats('Stop'); 
 After run the system_stat all the information inserted in table called "aux_stats$"  Like the following :

  • CPUSPEEDNW -----> CPU speed
  • IOSEEKTIM ------>    I/O seek time in milliseconds
  • IOTFRSPEED ------> I/O transfer speed in millisecond
The above output similar when you have No Workload , but what if you have One :
  • MBRC ----> Average blocks read per Multi block read.
  • MAXTHR ----> Maximum I/O throughput
  • SLAVETHR
  • SREADTIM  ---->Single block read time in milliseconds
  • MREADTIM -----> Multiblock read time in ms
  • CPUSPEED
Reference :
1- Oracle Dcoumentation
2- OTN threads

Thank you 
Osama Mustafa

Friday, February 8, 2013

Another Linux Command

1-Check Memory :

[oracle@192 ~]$ free -m

             total       used       free     shared    buffers     cached
Mem:          1010        997         13          0         71        685
-/+ buffers/cache:        240        770
Swap:         2000          0       2000
[oracle@192 ~]$ vmstat

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0  28820  72248 704336    0    0   117    49 1026  239  3  1 93  3  0
[oracle@192 ~]$ dmesg | grep RAM

BIOS-provided physical RAM map:
hdc: ATAPI 1X DVD-ROM DVD-R-RAM CD-R/RW drive, 32kB Cache, UDMA(33)
Finally : top command that you could use.

2-How Much this Os was running
 
[oracle@192 ~]$ uptime

 23:03:17 up  1:44,  2 users,  load average: 0.04, 0.11, 0.08



3-Check Some Hardware Information

CPU :

[oracle@192 ~]$ cat /proc/cpuinfo

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 37
model name      : Intel(R) Core(TM) i5 CPU       M 430  @ 2.27GHz
stepping        : 2
cpu MHz         : 2261.079
cache size      : 3072 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss nx rdtscp lm constant_tsc up ida nonstop_tsc pni cx16 popcnt lahf_lm [8]
bogomips        : 4522.15

 Thank you
Osama mustafa

Thursday, February 7, 2013

Drop Undo Tablespace Online

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                 string       AUTO
undo_retention                       integer     900
undo_tablespace                    string       UNDOTBS1


CREATE UNDO TABLESPACE undotbs2
DATAFILE'/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M;
Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

System altered.

SQL> show parameter undo ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                 string          AUTO
undo_retention                       integer        900
undo_tablespace                    string          UNDOTBS2
 Now you need to check if there's any segment used old undo tablespace to ensure you will not loose any Data

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs where tablespace_name like '%UND%';

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU11$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU12$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU13$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU14$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU15$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU16$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU17$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU18$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU19$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU20$                     PUBLIC UNDOTBS2                       ONLINE

Make Sure that you don't have any Segment Using Undo01/Old Undo Tablespace and if you have one wait until the transaction become Invalid Or expired.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
 Mission Done.


Thank you
Osama Mustafa

Tuesday, February 5, 2013

ORA-04068: existing state of packages has been discarded

The Below Error happened Usually In RAC environment, and its Indicate to Bugs and you can avoid this error as Work around :

 SQL> alter system set "_disable_fast_validate"=true scope=both;

Please be Noted there's No Restart need After Modify This Parameter.

Also For Better Solution you can upgrade to 11.2.0.3 Or apply Patch Number 9681133 Which Can Be Downloaded From Https://support.oracle.com


Thank you
Osama Mustafa

Monday, February 4, 2013

OTNYathra 2013

The Oracle ACE directors and Java champions in the region are organizing an evangelist event called ‘OTNYathra 2013’ during the month of February 2013.  This yathra or tour will a series of 6 conferences across 6 major cities in a time period of 2 weeks managed by ACE directors and Java Champions of the region.


India is a primary hub for information technology and a station for most software development centers and support centers for Oracle applications.  Oracle community in India comprises of several 400000 users.  In a large country like India, such user concentration is not in one location or one IT park but spread across its length and breadth.  The OTNYathra (Tour) is to travel across the country covering over 6 major IT focused cities and to bring the Oracle community together, giving them awareness and improve the level of knowledge and increase the networking opportunities in the region.   The tour will also give awareness to younger youth who have just completed their engineering and are considering IT career paths.

 Register Here

Thank you
Osama Mustafa

Sunday, February 3, 2013

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

But Today its About RAC Issue while try to create dbconsole Like the following :

emca -config dbcontrol db -repos create -cluster

=============

STARTED EMCA at Jan 30 , 2012 1:01:00 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle.  All rights reserved.


Enter the following information:
 Database unique name: ORCL
 Service name: ORCL

Listener port number: 1561
 Listener ORACLE_HOME [ /u01/grid/11.2.0 ]: /u01/oracle/product/db/11.2.0
 Password for SYS user:
 Password for DBSNMP user:
Password for SYSMAN user:
Cluster name: CRS
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u01/grid/11.2.0 ]:
ASM port [ 1561 ]: 1521
ASM username [ ASMSNMP ]:
ASM user password:
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password: 

This Error is related to Database which is configured with listener port 1561 while ASM instance is not configured with port 1521.

Export ORACLE_SID=+ASM1
sqlplus / as sysasm
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORCL1)(PORT=1561))))' scope=both sid='+ASM1';
 
Now Same On Node 2 
 Export ORACLE_SID=+ASM2
sqlplus / as sysasm
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORCL2)(PORT=1561))))' scope=both sid='+ASM2';

Thank you
Osama Mustafa

Friday, February 1, 2013

Oracle Mechanism To Store Password

Today I post This Topic About how Oracle Store Password, I choose this topic since i will write on other topics that related to this one, Understand this topic will make you understand the basics how to secure oracle database.

As i mention before there's are simple steps to secure database before start buying products and use them.
Also Please note that article will not show you how to hack or attacks any algorithms it's only show the Title of this topic.I used in this topic Database version 10g,11g.

When You Enter Username which is stored in Plain Text ( Clear Text ) in DBA_USERS view on other hand the password stored as hash, when you log in to The Password is concatenated to the end of your username, and when you type the password , it will be compared to Stored hash password if they match then welcome to your schema otherwise you don't have access to the schema.

I love to keep asking this question since its really good question regarding to password, why Using Hash instead of encrypt it ? The Answer is Simple you can't reverse the hash while you can do that in encrypted.
still difficult ... if you have encrypted file you can decrypt it it will not be easy but POSSIBLE , Otherwise in hash you need other age to extract the password from it.Adding to your information that hashing store any amount of data

for example : hashing could be 16 bytes and its could be different related to hashing algorithms so you cannot retrieve the original data from the hash, so when you enter the password in oracle this password will be hashed and comparing to Stored Hashing password ( in DBA_USERS ) you will log on, actually lot of systems using this way Unix one of them.

and you check all this information username, password and others in Tables called SYS.USER.

SQL> select user#,Name,Password from SYS.USER$;

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
         0 SYS                            8A8F025737A9097A
         1 PUBLIC
         2 CONNECT
         3 RESOURCE
         4 DBA
         5 SYSTEM                         2D594E86F93B17A1


The algorithm that generates that hash is the same in all versions and platforms and does not include any identifying info from the database, host, instance or other information beyond the username and password.Thus, if your username and password are the same on multiple databases they will all have the same hash value.

I always consider and inform client and customers for grant users privileges that they don't need to what it's called "ESCALATE PRIVILEGES"  Check the below example that describe If you don't know the other user's password you can temporarily change the password then restore it by saving the hash and then using the "IDENTIFIED BY VALUES" clause of the ALTER USER command to restore the hash value when you are done.

SQL> conn test/test_123;
Connected.

SQL>  select user#,Name,Password from SYS.USER$ where name='TEST';

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
        85 TEST                           BB68AA665B7F31FB

SQL> alter user test identified by test_osama_123 ;

User altered.

SQL> conn test/test_osama_123;
Connected.

 SQL> alter user test identified by Values 'BB68AA665B7F31FB';
User altered.

you can controlled this error  by password_reuse_time/password_reuse_time parameter in profile.Change password different than test_123. and if you choose the same password the below error appeared :
ORA-28007: the password cannot be reused

You can check the description for the error :
ORA-28007:the password cannot be reused
Cause: The password cannot be reused for the specified number of days or for the specified number of password changes
Action: Try the password that you have not used for the specified number of days or the specified number of password changes Refer to the password parameters in the CREATE PROFILE statement.

if you don't modify the profile you can be aware of the below :
  • you should be aware with alter user Privileges.
  • Check who has Access to DBA_USERS.
Notice the above information for Database 10g.

Now I will talk about Database 11g which get enhance for storing Database By adding new column to DBA_USER called PASSWORD_VERSION to understand it if this column contain 10g this the above information can be applied. if no 10g mention in that column you should search for new methods :)

Include to this new features 11g Introduce the ability of Store password In CASE_SENSITIVE and password hash no more appeared in DBA_USERS and to check it you should have access to SYS.USER$

Now Let's Back to PASSWORD_VERSION Column if this column contain 10g then SYS.USER$ Column PASSWORD   will not be reliable  Why ? Because hashing in this case will not support case sensitive so Upper or lower will be the same will be explain with example.
On Other hand if the PASSWORD_VERSION contain only 11g  then you will need to look in the SYS.USER$.SPARE4 column and you will see a much larger hex number.  This is because Oracle has switched to the SHA-1.

Another Interesting Thing that you could see in 11g if you assign the same password to user the hash value in the column will be Totally different but it will be usable in an "IDENTIFIED BY VALUES" amazing algorithm and new way to store oracle password and more secure.

Note : “IDENTIFIED BY VALUES” command  is not supported  which mean maybe will gone in Next Releases.Also SPARE 4 Column is not documented by oracle So Any next Patch or new Version Oracle may change how the password information is exposed.

Let's work On Examples Now:

SQL> show parameters sec_case;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE


SQL> alter user test identified by test ;
User altered.


SQL> select username,password,password_versions from dba_users where username = 'TEST';

USERNAME                       PASSWORD                       PASSWORD_VERSION
------------------------------ ------------------------------ --------
TEST                                                                                 10G 11G

SQL> select name,password,spare4 from sys.user$ where name = 'TEST';   
NAME                           PASSWORD                       SPARE4
------------------------------ ------------------------------
TEST                           7A0F2B316C212D67               S:6129354E59DE31C3554426823BB98CC0CDC3A9F5495076D46305BB73ECD


Now Let's Test Our Work on this User :)

SQL> conn / as sysdba
Connected.

SQL> alter user test identified by TesT ;
User altered.

SQL> conn test/test;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

SQL> conn test/TesT ;
Connected.
Let's Check Case_sensitive if it's enabled :

SQL> conn / as sysdba
Connected.
SQL> alter user test identified by TesT ;

User altered.

SQL> conn test/test;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn test/TesT ;
Connected.





let's use Identified By Values and see changes happened to DBA_USERS and SYS.USER$, Please notice that in the below example when i use Hash Value that stored in password The User able to connect using lower/upper Letters even Case_senentive parameter is enabled but when i used Hash that stored in SPARE4 everything is changed

SQL> conn / as sysdba
Connected.

SQL> alter user test identified by values '7A0F2B316C212D67';
User altered.

SQL> select username,password,password_versions from dba_users where username = 'TEST';

USERNAME                       PASSWORD                       PASSWORD
------------------------------ ------------------------------ --------
TEST                                                          10G

SQL> select name,password,spare4 from sys.user$ where name = 'TEST';   

NAME                           PASSWORD                       SPARE4
------------------------------ ------------------------------
TEST                           7A0F2B316C212D67

SQL> conn test/test ;
Connected.
SQL> conn test/TesT ;
Connected.

SQL> alter user test identified by values 'S:6129354E59DE31C3554426823BB98CC0CDC3A9F5495076D426305BB73ECD';
User altered.

SQL> select username,password,password_versions from dba_users where username = 'TEST';

USERNAME                       PASSWORD                       PASSWORD
------------------------------ ------------------------------ --------
TEST                                                          11G


SQL> select name,password,spare4 from sys.user$ where name = 'TEST';

NAME                           PASSWORD                       SPARE4
------------------------------ ------------------------------
TEST                                                          S:6129354E59DE31C3554426823BB98CC0CDC3A9F5495076D426305BB73ECD

SQL> conn test/test ;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

SQL> conn test/TesT ;
Connected.

Now The Let's Turn off the Case_sensitive parameter and set it to False what will be happened

SQL> show parameter case ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> alter system set sec_case_sensitive_logon=FALSE scope=both ;

System altered.

SQL> select username,password,password_versions from dba_users where username = 'TEST';

USERNAME                       PASSWORD                       PASSWORD
------------------------------ ------------------------------ --------
TEST                                                          11G

SQL> select name,password,spare4 from sys.user$ where name = 'TEST';

NAME                           PASSWORD
------------------------------ ------------------------------
SPARE4
--------------------------------------------------------------------------------
TEST
S:6EC2440ABAAC65F5883FF868BBC50757FB722E260633A417B04A982E1F15


SQL> conn test/test ;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Until This moment i am still unable to connect even i turned off the Case_sensitive parameter.

SQL> conn test/TesT ;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Also Same Error which indicate you should enable Parameter again or change password again :)


I wish it was useful article and full of demonstration if you have question Post it in comment please.

Thank you
Osama Mustafa