Automatic Storage Management(ASM) Overview and Background

Automatic Storage Management (ASM) was first released with Oracle Database 10g. For Oracle databases, ASM provided a significant simplification for file system and volume management. In addition to enhancing storage automation, ASM improved file system scalability, performance, and database availability. These benefits hold for both single-instance Oracle databases as well as for Oracle Real Application Clusters (Oracle RAC) database environments. Oracle Database 12c introduces several new capabilities in ASM. Automatic Storage Management (ASM) simplifies the administration of Oracle-related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extension of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files. 

    Automatic Storage Management (ASM) is Oracle’s logical volume manager, it uses OMF (Oracle Managed Files) to name and locate the database files. It can use raw disks, filesystems, or files which can be made to look like disks as long as the device is raw. ASM uses its own database instance to manage the disks, it has its own processes and pfile or spfile, and it uses ASM disk groups to manage disks as one logical unit. The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures, and as such is very small and lightweight.

                                               
                                                Figure 1-1 ASM Architecture

The benefits of ASM are:

• Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
• Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
• Adding disks is straightforward - ASM automatically performs online disk reorganization when you add or remove storage
• Uses redundancy features available in intelligent storage arrays
• The storage system can store all types of database files
• Using disk group makes configuration easier, as files are placed into disk groups
• ASM provides stripping and mirroring (fine and coarse gain - see below)
• ASM and non-ASM Oracle files can coexist
• ASM is free!!!!!!

Key Concepts:

1. ASM Instance: Oracle ASM operates as a separate instance from the Oracle Database instance. The ASM instance is responsible for managing storage resources, interacting with the underlying disks, and coordinating data distribution.
2. ASM Disk: Physical disks are added to ASM and are referred to as ASM disks.ASM disks serve as the building blocks for creating logical storage structures.
3. Disk Group: A Disk Group is a logical entity that consists of one or more ASM disks. Think of a disk group as a storage container where database files are stored.
4. File Management: ASM manages database files, such as data files, control files, and log files. It abstracts the physical file names and manages file distribution across the disks.
5. Mirroring and Striping: ASM automatically handles the mirroring and striping of data for redundancy and improved performance. Mirroring involves creating duplicate copies of files on separate disks. Striping involves distributing parts of a file across multiple disks.
6. Redundancy Options: Disk groups support different redundancy options. "External Redundancy" relies on the storage subsystem for data protection. "Normal or High Redundancy" involves ASM mirroring files to provide fault tolerance.
7. Online Operations: Many administrative operations, such as adding or removing disks, resizing, and rebalancing, can be performed online without impacting database availability.

Please feel free to ask. thank you 🙂

Toufique Khan 

What is the difference between row chaining and row migration?

 Overview

If you notice poor performance in your Oracle database Row Chaining and Migration may be one of several reasons, but we can prevent some of them by properly designing and/or diagnosing the database. Row Migration & Row Chaining are two potential problems that can be prevented. By suitably diagnosing, we can improve database performance.

Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

Figure 1-1 Oracle Block

Row Chaining

If the row is too large to fit into an empty data block in this case the oracle stores the data for the row in a chain of one or more data blocks, which can occur when the row is inserted.For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. 

                                                    Figure 1-2 Row Chaining

Some conditions that will cause row chaining are: 

• Tables whose rowsize exceeds the blocksize. 
• Tables with LONG and LONG RAW columns are prone to having chained rows. 
• Tables with more than 255 columns will have chained rows as Oracle breaks wide tables up into pieces. 

So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks. Chained rows affect us differently. Here, it depends on the data we need. If we had a row with two columns that were spread over two blocks, the query:

1. SELECT column1 FROM table where column1 is in Block 1, would not cause any table fetch continued row. It would not actually have to get column2, it would not follow the chained row all of the way out. 
2. On the other hand, if we ask for: SELECT column2 FROM table and column2 is in Block 2 due to row chaining, then you would in fact see a table fetch continued row.

Row Migration

An update statement increases the amount of data in a row so that the row no longer fits in its data blocks. Now the oracle tries to find another free block with enough space to hold the entire row if such a block is available oracle moves the entire row to the new block. We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the forwarding address. So, the original block just has the rowid of the new block and the entire row is moved. 

Figure 1-2 Row Migration

1. Full Table Scans are not affected by migrated rows : 

The forwarding addresses are ignored. We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there. Hence, in a full scan migratedrows don't cause us to really do any extra work -- they are meaningless.

2. Index Read will cause additional IO's on migrated rows :

        When we Index Read into a table, then a migrated row will cause additional IO's. That is because the index will tell us to go to file X, block Y, and slot Z to find this row. But when we get there we find a message that says well, really go to file A, block B, slot C to find this row. We have to do another IO (logical or physical) to find the row.  

Please feel free to ask. thank you 🙂
Toufique Khan 

What is the difference between restore point & guaranteed restore point?

Sometimes it is more efficient to roll-back changes in a database rather than do a point-in-time recovery. Flashback Database can rewind the entire database and changes that occurred within a given time window. The effects are similar to database point-in-time recovery.

Normal Restore Points

You can create restore points to enable you to Flashback the database to a particular point in time or SCN. You can think of it as a bookmark or alias that can be used with commands that recognize a RESTORE POINT clause as shorthand for specifying an SCN. In essence, before you perform any operations that you may have to reverse you can create a normal restore point. The name of the restore point and SCN are then recorded within the control file. So basically creating a restore point eliminates the need to determine the current SCN before performing operations or finding the proper one after the fact. A label for an SCN or time. For commands that support an SCN or time, you can often specify a restore point. Normal restore points exist in the circular list and can be overwritten in the control file. However, if the restore point pertains to an archival backup, then it will be preserved in the recovery catalog. You can use RESTORE POINTS to specify the target SCN in the following contexts:  

• RECOVER DATABASE and FLASHBACK DATABASE commands within RMAN 
• FLASHBACK TABLE in SQL*Plus
• The Fast Recovery Area must be configured (db_recovery_file_dest +db_recovery_file_dest_size).
• FLASHBACK_DATABASE must be enabled.
• Archivelog mode must be active.
• It is aligned with the parameter: db_flashback_retention_target.
• This means that logs older than the specified parameter will be deleted/overwritten, which can result in insufficient information to perform a flashback.
• Without a previous Flashback_Database operation, the Restore Point is useless (warning).
• It causes more I/O because different block versions are retained at different points in time.
• It can generate a larger number of Flashback logs.

Creating Normal Restore Points

Execute a statement such as the following, which creates a restore point named Restore_Point1:

    SQL> create restore point Restore_Point1;

              Restore point created.

 

How It Works

Restore points are named positions in time. While flashing a database back, you can specify a restore point as a destination instead of specifying an SCN or timestamp. However, flashing back to a restore point is possible only if the flashback logs are available for the time associated with the restore point. Because the restore points created by following this recipe are not guaranteed, they are known as unguaranteed or normal restore points. Normal restore points are the default type.

Guaranteed Restore Points (GRP) 

A Guaranteed Restore Point can be used to perform a Flashback Database operation even if flashback logging is not enabled for your database. It can be used to revert a whole database to a known good state days or weeks ago, as long as there is enough disk space in the flash recovery area to store the needed logs. Even effects of NOLOGGING operations like direct load inserts can be reversed using guaranteed restore points. Limits to both types of Restore Points include shrinking a datafile or dropping a tablespace can prevent flashing back the affected datafiles to the restore point. A restore point for which the database is guaranteed to retain the flashback logs for an Oracle Flashback Database

operation. Unlike a normal restore point, a guaranteed restore point does not age out of the control file and must be explicitly dropped. Guaranteed restore points utilize space in the flash recovery area, which must be defined. 

• The Fast Recovery Area must be configured (db_recovery_file_dest + db_recovery_file_dest_size).
• Archivelog mode must be active.
• It is a fixed, never-expiring pointer in the database.
• It must be manually deleted, or else the Fast Recovery Area may become full.
• FLASHBACK_DATABASE does not need to be enabled.
• It is independent of the Retention_target parameter.
• This functionality is also used by the Autoupgrade utility.
• Only Flashback Database operations use Flashback logs.
• It generates fewer Flashback logs.
• It causes less I/O because only one copy of the block image before the change is stored in the Flashback log.
• The user must have the SYSDBA system privileges.

Creating Guaranteed Restore Points

Add the guarantee keyword to your create restore point command. For example:

    SQL> create restore point Restore_Point2 guarantee flashback database;

              Restore point created.

How It Works

When a guaranteed restore point is defined, the associated flashback logs are never deleted unless the

restore point is dropped. This will reduce the available space in the fast recovery area. A filled-up fast recovery area will cause the database instance to abort, with the failure in the recovery writer (RVWR) process. So, create guaranteed restore points only when you need to go back to them after some preestablished event to be completed in the near future, such as doing a test run of the application and then reverting to the starting data sets. After the test is completed, drop the guaranteed restore points.

Flashing Back to a Specific SCN

Flash the database back to your desired SCN. For instance, to flash back to SCN 1050951 ,

issue the following RMAN command:

RMAN> flashback database to scn 1050951;

Starting flashback at 03-APR-24

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=16 device type=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished flashback at 03-APR-24

This command flashed the database back

Flashing Back to a Specific Time

Use the following command to flash back to a time just two minutes ago. Since a day has

24 hours, with 60 minutes each, 2 minutes happen to be 2/60/24 of a day:

RMAN> flashback database to time ’sysdate-2/60/24’;

Starting flashback at 13-APR-24

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 70 is already on disk as

file +FRA/cdb1/archivelog/2024_04_13/thread_1_seq_70.279.789267613

archived log for thread 1 with sequence 71 is already on disk as

file +FRA/cdb1/archivelog/2024_04_13/thread_1_seq_71.280.789285641

media recovery complete, elapsed time: 00:00:40

Finished flashback at 13-APR-24

If you want to flashback to a specific time, not in reference to a time such as sysdate,

you can use the timestamp instead of a formula:

RMAN> flashback database to time "to_date(’08/03/2012 22:00:00’,’mm/dd/yyyyhh24:mi:ss’)";

This flashes the database back to that specific timestamp.

Please feel free to ask. thank you 🙂

Toufique Khan