by Deiby Gómez
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.You can read more about this here and here.
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 184.108.40.206.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 220.127.116.11.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Advanced Analyticsand Real Application Testing options
SQL> select banner from v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 - 64bit ProductionPL/SQL Release 22.214.171.124.0 - ProductionCORE 126.96.36.199.0 ProductionTNS for Linux: Version 188.8.131.52.0 - ProductionNLSRTL Version 184.108.40.206.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 -ltrtotal 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
"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.
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 +DATAORA-27300: OS system dependent operation:open failed with status: 2ORA-27301: OS failure message: No such file or directoryORA-27302: failure occurred at: sskgmsmr_7
SQL> select open_mode from v$database;
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.
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 +DATAORA-27300: OS system dependent operation:open failed with status: 2ORA-27301: OS failure message: No such file or directoryORA-27302: failure occurred at: sskgmsmr_7
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:
SQL> CREATE TABLE DGOMEZ (ID NUMBER PRIMARY KEY, VALUE VARCHAR2(20));
SQL> insert into dgomez values (1,'deiby');
1 row created.
SQL> update dgomez set value='gomez' where id=1;
1 row updated.
SQL> delete dgomez where id=1;
1 row deleted.
SQL> alter system switch logfile;
SQL> alter system checkpoint global;
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
ASMCMD> pwd+data/orcl/datafileASMCMD> lsDGOMEZ.271.855889623SYSAUX.257.855877553SYSTEM.258.855877589UNDOTBS1.260.855877635USERS.259.855877633
SQL> drop tablespace dgomez including contents and datafiles;
did the asm delete the related files?
ASMCMD> pwd+data/orcl/datafileASMCMD> lsSYSAUX.257.855877553SYSTEM.258.855877589UNDOTBS1.260.855877635USERS.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';
SQL> alter tablespace dgomez drop datafile '+DATA/ORCL/DATAFILE/dgomez.272.855887463';
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 sysdbaSQL> startupORA-01078: failure in processing system parametersORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.oraORA-27300: OS system dependent operation:open failed with status: 2ORA-27301: OS failure message: No such file or directoryORA-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 ;
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).
Hope you liked the article, and if you have any doubt leave a comment.