Oracle ASM vs Oracle AFD

In 12c, Oracle introduced a new feature ASM Filter Driver (AFD) which is a replacement for Oracle ASMLIB. We still have the option to use Oracle ASMLIB if you choose to.

IMO, AFD is superior to ASMLIB and here is why:

  1. Oracle AFD ships with Oracle Grid, no need for extra download. You don’t have to do an initial configuration either like with ASMLIB (oracleasm configure)
  2. We adding disks on 1 node, there is no need to scandisk on other nodes
  3. When you add a disk to AFD, it protects the disk against any OS mistakes like fdisk, mkfs and even dd commands, now how cool is that



Database Automated Maintenance Tasks Schedule in Middle East

In the Middle East, weekends are Friday & Saturday in most countries. However, your Oracle database doesn’t know that until you tell her.

In 11g, there are 3 main automated maintenance tasks in every database enabled by default:

  1. Automatic Optimizer Statistics Collection – Gathers stale or missing statistics for all schema objects. The task name is ‘auto optimizer stats collection’.
  2. Automatic Segment Advisor – Identifies segments that could be reorganized to save space. The task name is ‘auto space advisor’.
  3. Automatic SQL Tuning Advisor – Identifies high load SQL The task name is ‘sql tuning advisor’.

These tasks can run up to 20 hours on Saturdays & Sundays utilizing a good portion of your hardware.

Oracle uses the daily windows (FRIDAY_WINDOW, SATURDAY_WINDOW, etc…) to schedule these tasks

SELECT window_name, repeat_interval, duration FROM DBA_SCHEDULER_WINDOWS;

windows maintenance default

I recommend limiting the duration for Sunday to 4 hours like the rest of the working days and increase FRIDAY_WINDOW to 20 hours.

Here are the commands to do that:

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.SUNDAY_WINDOW','duration','+00 04:00:00.000000');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.FRIDAY_WINDOW','duration','+00 20:00:00.000000');

Also note that jobs during working days start at 10 PM, it might not be suitable to start jobs around that time especially in Ramadan, Here is an example to change the starting time of the schedule for WEDNESAY_WINDOW to 1 AM

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=01;byminute=0; bysecond=0');

Hope this helps

xPath vs DOM Comparison for XML Parsing

This is a little comparison between XPath and DOM. I hope this will help you to determine which method is better suited for your situation.

Complexity: XPath is much simpler that DOM. XPath can also be used in SQL directly while DOM you must write a stored procedure.

Flexibility: DOM is much flexible than XPath. You can read an entire XML document without knowing any specifics about the document upfront. In XPath, knowledge of the document design is required.

Speed: In our test on on Linux, DOM was 3 times as fast as XPath when parsing 500,000 XML documents.


Change Start of Week for Saudi Arabia

In the Kingdom, week starts on Saturday (not Sunday like the USA):

There are 2 ways to achieve this in


First Method:

alter session set nls_TERRITORY ='SAUDI ARABIA';
select   sysdate, to_char(sysdate, 'Day') "Day Name", 
         to_char(sysdate, 'D') "Day Number", 
         trunc(sysdate ,'D')"Beginning of Week"  
from dual;

next_day alter session

Sysdate is Feb 23, 2016 which is Tuesday and 4th day of the week.  If in America territory, Tuesday would’ve been the 3rd day of the week.


Beginning of the week is Saturday, Feb 20, 2016. If in America territory, the date would’ve been Sunday, Feb 21, 2016


Second Method:

select sysdate, next_day(sysdate - 7, 'SATURDAY') from dual;




Notice there is no “alter session” which makes it a bit easier.


Hope this helps

Storing XML Documents in XMLTYPE or VARCHAR Column Type

You can store XML in your table using XMLTYPE column data type or the good old VARCHAR2 provided that XML document is no more than 4000 characters.

I made a quick comparison between both options to help you access which option is a better choice for your situation.

Test environment: Oracle, 64-bit running on Red Hat Linux 64-bit.

Test case setup: Created 2 tables, each table has a single column only. One table with column type VARCHAR2 (4000) while the other table XMLTYPE column type.

Test Case Result Comment
Space utilization Insert 65,000 rows XML rows in both tables VARCHAR table Size: 520 MB.

XML table Size: 168 MB. Note that are an additional LOB segment and a LOB index segments created for XML type.

Storing XML data in column type XMLTYPE saved 3X space as compared to VARCHAR table. That is expected because XMLTYPE is stored in binary format.
Syntax Checking Insert data with incorrect XML format XMLTYPE table rejected the row, while VARCHAR table accepted data This might be viewed as positive or negative point depending on your situation.
Insert Performance Insert 4,000 rows sequentially.  A single commit at the end. VARCHAR table: 0.3 sec

XMLTYPE table: 1.3 sec

VARCHAR table 3X faster than XMLTYPE.

None of the tables had indexes.

Querying table  based on an value within a XML document Create index on xml table with “extractvalue” option.

No index created on VARCHAR table

XML is always faster that VARCHAR table in retrieving data There is so much flexibility and performance gains in retrieving data with XMLTYPE column type than VARCHAR table

Hope this helps


ORA-00821 on RAC with ASM


Database went down and you can’t bring it up because of this unwelcomed error:

ORA-00821: Specified value of sga_target M is too small


Further, “startup nomount” still gives the same error and you can’t alter the spfile and adjust the value that caused this violation.


Here are the steps I follow to fix this issue, these steps have been tested on Oracle RAC with ASM 64 bit on Redhat Linux AS V. 5.

Summarized steps to recover:

  1. Create pfile from spfile
  2. Edit pfile and adjust value causing this issue
  3. Start the database up with nomount option
  4. Create spfile from pfile
  5. Adjust ASM alias
  6. Bounce the database
  7. Remove old spfile from ASM


Steps should be on a single node only.


1)      Create pfile from spfile

create pfile=’/u02/backups/dbatst/’ from spfile=’+data01/dbatst/spfiledbatst.ora’;


For pfile, you need to specify a location, otherwise it will overwrite your pfile in $ORACLE_HOME/dbs, which will further complicate your recovery.


 For spfile, you need to find where your spfile file is on ASM, remember your database is not even mounted, so Oracle is totally lost as where the location of database’s spfile is.


2)      Edit pfile

 Now change value causing violation.


3)    Start the database up with nomount option

Startup nomount pfile=’/u02/backups/dbatst/’

 This should bring up the database in nomount stage.


4)      Create spfile from pfile

create spfile=’+DATA01′ from pfile=’/u02/backups/dbatst/initdbatst.ora’;

Notice now that in nomount stage, Oracle recognizes where to put the spfile and you don’t need to specify the full ASM path to spfile.

spfile Location: <+dg>/database name/parameterfile

In my situation it will be ‘+DATA01/dbatst/parameterfile’

Also note that Oracle will create a new spfile without overwriting the old one. You should delete the old spfile as indicated later.


5)      Adjust ASM alias

“Alias” in ASM is like link “ln” operating system command.


The location of spfile alias on ASM is in the pfile under $ORACLE_HOME/dbs.

The spfile alias on ASM is pointing to the old spfile file, not the new one.

To fix this issue, you need to delete old alias and recreate a new alias


Here are the commands:


cd to alias location, location should be ‘+dg/dbname/spfiledbname.ora’

ls -l  , just to confirm it is an alias

delete alias:

rmalias spfiledbatst.ora


recreate alias

mkalias +DATA01/DBATST/PARAMETERFILE/spfile.535.831221333 spfiledbatst.ora


6)      Bounce the database

Use srvctl utility to bounce the database.

After database comes up successfully, make sure it is using the spfile.

 From a sqlplus session:

      show parameter spfile


7)      Delete old spfile

Just to keep things tidy, remove the old spfile from <+dg>/database name/parameterfile



Hazem Ameen
Senior Oracle DBA



How to Find an Oracle DBA Job in Saudi Arabia

I receive several emails through this blog or LinkedIn asking how to find an Oracle DBA job in Saudi Arabia if I reside in some other country.

Before you starting your job hunting, keep in mind that most companies can survive with local talent which became a lot through the last several years due to Oracle’s popularity. So you must have a unique set of skills required immediately for a company to relocate you.

The best and fastest way is through good old fashioned networking (talking to people that know people). This way you resume will make it directly to department heads and decision makers. In this case, you will have an edge because you are already recommended by your connection and there is a chance the company will not even advertise the job opening until they talk to you first. Even if the company doesn’t have an immediate job opening, they will keep you in mind for future openings.

Another less effective way is responding to ads in newspapers or recruiting websites. The company will be waiting for applicants’ resumes and yours will be one of those many resumes (hope your resume will stand out).

The least effective way is posting your resume on a recruiting website, LinkedIn or applying through a company’s website and waiting for the company to contact you. This rarely produces an immediate response. Your resume will eventually age on recruiting websites and will be bypassed in searches. In case of company’s website, if their inbox is not already full, your resume will rarely makes its way to the IT department.

Hope this helps