The database instance behavior and the ab_.dat file

Some people still think that the ASM is which performs all IO activity of our database, I have seen several DBAs worried about to use ASM because of  the database will have a new "IO Layer", that is absolutely false because the ASM instance perform a few IO tasks, the most IO task are performed by the database instance and that is the goal of this article, to explain how the ASM instance and database instance work together in order to perform the IO operations. Through this article we will talk about the File extent Map, the ab_<ASM SID>.dat file, the importance of the Shared pool when we are using ASM, and so on.  The most important thing here is that you (the reader) understand how the IO operations are performed when you are using ASM.

What is the "ab_<ASM SID>.dat" file? This file is used by the database instance in order to messages ASM instance. When the database instance needs to send a message  to the ASM instance, the database instance reads this file in order to find out the enough information for getting connected to ASM instance. This file is in $ORACLE_HOME/dbs. If this file doesn’t exist the database will not be able to connect to ASM instance and you will receive an error. This file is important because  is involved in the database instance work.

What is "File Extent Map"? The Oracle Documentation gives us the following definition:

An extent map is a list of extent pointers that point to all the data extents of a file. This is the raw storage for the file. Each extent pointer gives the disk and allocation unit of the data extent. For reliability, each extent pointer also includes a check byte to ensure it has not been damaged. This is particularly important when using an in-memory copy of the extent map to direct file I/O to a particular disk location.

You can read more about The File Extent Map here. 

The definition says something about direct IO access to the disks, that is because through this file the database instance access the file in the disks directly, remember that one file is not only in one disk, the file is spread across all the disks in the diskgroup.

Which creates the File Extent Map? The ASM. And let’s read what does the Oracle Documentation says about this:
When a database instance creates or opens an ASM file, it communicates those requests to the ASM instance. In response, the ASM instance provides file extent map information to the database instance.

Now we see that when the database instance "Creates" or "Opens" an ASM file the File Extent Map is created by the ASM instance. The ASM instance sends the File Extent Map to the database instance and the database instance only acknowledge it.

The File Extent Map is stored in the "Shared Pool". Once the database has the File Extent Map in the SGA access directly each file directly in the disks for all operations of IO except "create" or "open" a file.

"In Oracle Database 10g, for datafiles, ASM sends the entire File Extent Map to the database instance once the ASM has opened the file.
In Oracle Database 11g, for datafiles, ASM initially sends the 60 direct extents to the database instance. For the remaining extent, which are located in indirect extents, the RDBMS requests subsequent extent maps on demand." Oracle ASM by Nitin, Murali and Rich.

I would like to say here that there are many people thinking that a database instance runs better in Filesystem than ASM, because they have created a test environment with all at the same way (parameters, versions, etc), one in Filesystem and the other in ASM, then they tested both environments and Filesystem gave a better performance. Remember that since ASM creates the File Extent Mapa and that structure is stored in the Shared Pool you should increase the SGA memory in the environment that is using ASM in order that you can compare both environments correctly. 

Now I will try to explain how the database operations works in an ASM environment. Each example here was executed in the following environment:

[oracle@db12102 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 17 23:53:22 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Where is my ab_<ASM SID>.dat file?

[grid@db12102 dbs]$ pwd
/u01/app/grid/product/12.1.0/grid/dbs
[grid@db12102 dbs]$ ls -ltr
total 12
-rw-r--r-- 1 grid oinstall 2992 Feb 3 2012 init.ora
-rw-rw---- 1 grid oinstall 1544 Aug 17 23:29 hc_+ASM.dat
-rw-rw---- 1 grid oinstall 3161 Aug 17 23:29 ab_+ASM.dat

Now I will try to find out in what database operations does the database instance use the ab_<ASM SID>.dat file:

Renaming the ab_+ASM.dat file simulating a problem:

[grid@db12102 dbs]$ mv ab_+ASM.dat ab_+ASM.dat.bak

TABLESPACE CREATION

"ASM creates a Continuing Operation Directory (COD) entry to track the pending file creation. The RDBMS instance subsequently issues the appropriate IO  to initialize the file. When initialization is complete, the database instance messages ASM to commit the creation of the file. When ASM receives the commit message, ASM's LGWR flushes the Active Change Directory (ACD) change record with the file-creation information. DBWR subsequently asynchronously writes the appropriate allocation table, File Directory and Alias Directory entries to disk. If the RDBMS instance explicitly aborts the file creation without committing the creation, the ASM uses the COD to roll back the file creation."  Oracle ASM by Nitin, Murali and Rich.

Renaming the ab_+ASM.dat file simulating a problem:

[grid@db12102 dbs]$ mv ab_+ASM.dat ab_+ASM.dat.bak

SQL> create tablespace dgomez datafile size 100M;
create tablespace dgomez datafile size 100M
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-27300: OS system dependent operation:open failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sskgmsmr_7

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

As you can see for "CREATE TABLESPACE" operations the database instance needs to message ASM instance, why? because the database instance needs to create a datafile. do you remember when we gave the definition of "File Extent Map"? there says for "creating files" and also for "opening file", well this is a file creation so that the ab_+ASM.dat is needed.

DATAFILE CREATION

SQL> alter tablespace system add datafile size 100M;
alter tablespace system add datafile size 100M
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-27300: OS system dependent operation:open failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sskgmsmr_7

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

This operation is similar to the last one, it is regarding "creating a file", the file ab_+ASM is needed by the database instance.
There is other important thing here, after to receive an IO error the database instance is up and running well.

TABLE CREATION and DML operations:

Renaming the ab_+ASM.dat file simulating a problem:

[grid@db12102 dbs]$ mv ab_+ASM.dat ab_+ASM.dat.bak

SQL> CREATE TABLE DGOMEZ (ID NUMBER PRIMARY KEY, VALUE VARCHAR2(20));

Table created.

SQL> insert into dgomez values (1,'deiby');

1 row created.

SQL> commit;

Commit complete.

SQL> update dgomez set value='gomez' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete dgomez where id=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint global;

System altered.

wow! all the operations were finished successfully. That means that the ab_+ASM.dat is not needed by these operations. Why? well, since they were not "opening file" or "creating file" operations that means that the Extent map exists already, so that the database instance uses the File Extent Map if they need to access the files.
As you know the “commit” and “switch log file” operations doesn’t create DBWn so that datafiles access are not performed, however is good to know what happen in all the possible cases, don’t get happy with few knowledge, go for more!

DROP TABLESPACE OPERATION

Renaming the ab_+ASM.dat file simulating a problem:

[grid@db12102 dbs]$ mv ab_+ASM.dat ab_+ASM.dat.bak

ASMCMD> pwd
+data/orcl/datafile
ASMCMD> ls
DGOMEZ.271.855889623
SYSAUX.257.855877553
SYSTEM.258.855877589
UNDOTBS1.260.855877635
USERS.259.855877633

SQL> drop tablespace dgomez including contents and datafiles;

Tablespace dropped.

did the asm delete the related files?

ASMCMD> pwd
+data/orcl/datafile
ASMCMD> ls
SYSAUX.257.855877553
SYSTEM.258.855877589
UNDOTBS1.260.855877635
USERS.259.855877633

Insteresting… The tablespace and its datafiles were deleted successfully. I thought that this operation was going to fails, but no. one more time, we can see that we can trust in Oracle Documentation (at least in this case ), the Oracle documentation says “Create” and “Open” a file, this is a “delete file” operation.

DELETE DATAFILE OPERATION

SQL> select file_name from dba_data_files where tablespace_name='DGOMEZ';

FILE_NAME
--------------------------------------------------------------------------------
+DATA/ORCL/DATAFILE/dgomez.271.855887267
+DATA/ORCL/DATAFILE/dgomez.272.855887463

SQL> alter tablespace dgomez drop datafile '+DATA/ORCL/DATAFILE/dgomez.272.855887463';

Tablespace altered.

This operation is similar to the last one, it is regarding to delete a file and we saw that the database instance doesn't need the ab_+ASM.dat file.

DATABASE START UP OPERATION

[oracle@db12102 ~]$ sqlplus / as sysdba
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-27300: OS system dependent operation:open failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sskgmsmr_7

We receive an error and that means that the ab_+ASM.dat is used by this operation. It make sense because when the database is getting the "open" status the database needs to open every datafile so that the database instance send a message to the ASM instance and the ASM instance is which opens each datafile, then it creates the File Extent Map and so on. Let's see what the ASM does in this operation:

"When an database instance needs to open an ASM file, it sends to the ASM instance a file-open request, with the filename, via one of the o0nn processes. ASM consults the File Directory to get the extent map for the file. ASM sends the extent map to the database instance." Oracle ASM by Nitin, Murali and Rich.

DATABASE SHUT DOWN OPERATION

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

For this operations the ab_+ASM.dat is not needed by the database instance. The database instance knows already where are the datafiles and it closes them.

Since I said the File Extent Map is stored in the shared pool you would try to Flush the Shared Pool trying to find out something weird, but Oracle is Oracle 🙂

SQL> Alter system flush shared_pool ;

System altered.

SQL> select * from gv$sgastat where name like '%pointer%';

INST_ID POOL NAME BYTES CON_ID
---------- ------------ -------------------------- ---------- ----------
1 shared pool ASM extent pointer array 112016 0
1 shared pool kxfpdp pointers 262136 0
1 shared pool ksbsa pointer array 3488 0
1 shared pool Wait event pointers 384 0
1 shared pool DBWR array of pointers to 8 0
1 shared pool obj hash table pointers 224 0

6 rows selected.

No, the File extent maps are still there (ASM extent pointer array).

Conclusion

• We have seen that ASM doesn't perform all the IO operations, the database instance is which perform the most IO operations directly the file in the disks through the File Extent Map.
• The File Extent Map is created by ASM.
• DML operations don't need the ab_<ASM_SID>.dat
• We also saw that the ab_<ASM SID>.dat is very important in the database instance work.
• You should know so far that the File Extent Map is stored in the Shared Pool.
• The corruption of the ab_<ASM SID>.dat is not dangerous enough for to make crash the database instance.
• You also should have clear that the operations "create" and "open" uses the ab_<ASM SID>.dat
• When you are migrating a database from Filesystem to ASM you should increase the database memory.

Hope you liked the article, and if you have any doubt leave a comment.