IOUG Collaborate 2018 - Slide Decks are Available!

Last week I presented 3 sessions at my favorite Oracle conference - the IOUG Collaborate.
The slide decks for my presentations are now available:


Data Guard for Beginners
ASM Conepts, Architecture and Best Practices
Get the Oracle Performance Diagnostics Capabilities You Need without Spending a Fortune


I'd like to thank everybody who attended my sessions! Hope to see you again next year.

ODC Appreciation Day : Online Move Data File Operation

I've decided to dedicate my ODC Appreciation Day blog post to "Online Move Data File Operation" - a feature which has been introduced in Oracle 12c.
As a quick background to this blog post, ODC (Oracle Developer Community) appreciation day is Tim Hall's (Oracle-Base) initiative which started last year (when it was OTN Appreciation Day).

So what is online move data file operation?

Moving data files is a common-use case, for example, when the DBA would like to move data files to a faster/larger disk. Another common use case is when DBA would like to migrate the files from traditional file systems to Oracle's Automatic Storage Management (more information about Oracle ASM is available in my ODC published article : https://community.oracle.com/docs/DOC-995178)

Prior to Oracle 12c, there was no way to accomplish this task with zero downtime. Common solution was to take the entire tablespace or data file offline, move it to the new location, change the location using the "ALTER DATABASE" command (which  essentially changes the location in the Oracle's control file) and then bring the tablespace/file online again; however, during this operation the entire tablespace or datafile won't be accessible. Better approach to mitigate the downtime was to change the tablespace or data file to be in a read-only mode which will at least allow queries to be executed against the data which reside on that tablespace/file - but it's still not favorable because DMLs/DDLs cannot be executed during that operation.

Oracle 12c allows us to do this completely online with zero downtime - while the database is open and users are accessing the data file. I've presented this feature with several syntax examples as part of my IOUG Collaborate 16 presentation - "Best New Features of  Oracle Database 12c" (slide deck is available here: https://www.slideshare.net/PiniDibask/best-new-features-of-oracle-database-12c).

You could also find several examples and even a short video which demonstrates this cool feature in the Oracle documentation: https://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837

Summary

Whether running Oracle databases on premise or in the cloud, this feature is very useful. When Larry Ellison spoke at Oracle OpenWorld 2017 keynotes about Oracle 18c and Oracle's option of to ensure having up to 30 minutes down-time a year with their cloud offering, this feature is another useful way  that can help when it comes to ensuring zero impact on the customer's applications (among the many other Oracle's HA & DR features - such as ASM, RAC Rolling Upgrades, Active Data Guard, Flashback, etc.)

Getting free space ORA errors even though tablespace has enough free space

Introduction

Recently I've seen an interesting question in the OTN discussions forums. 

The question was - Why do I get free space errors (such as ORA-01654) even though tablespace has enough free space. Here is the link to the thread: https://community.oracle.com/message/13869346#13869346

While this may sounds like a weird and complex scenario -  it is actually a pretty common issue and simple to explain.

Background

When Oracle needs to allocate more space for a segment (such as index segment, table segment, etc.) it doesn't allocate a single Oracle Block for the extra space, but rather an extent which is a logical unit of database storage space allocation made up of a number of contiguous data blocks.

When there is no more enough free space in the database blocks, and Oracle needs to allocate, it has to allocate an extent. The size of the extent depends on the tablespace storage configuration which could have either uniform extents sizes where each new extent has the same size, or system-managed extents sizes where Oracle determines the optimal size of additional extents and the extent sizes may vary (Read more about this here: http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#i19599)

Solving The Mystery

The total free space in a tablespace might be large enough for allocating additional extent; however, in some cases where the tablespace is fragmented, the largest contiguous extent might be smaller than the next extent size. 

In order to obtain the next extent size, it is possible to query the NEXT column in the DBA_SEGMENTS dictionary view, as follows:

SQL> CREATE TABLE large_extent_tbl
(
   id   NUMBER (20)
)
STORAGE (NEXT 10M)
TABLESPACE TEST;  
Table created.

SQL> insert into large_extent_tbl values (1);

1 row created.

SQL> SELECT next_extent/1024/1024
  FROM dba_segments
 WHERE segment_name = 'LARGE_EXTENT_TBL';

NEXT_EXTENT/1024/1024
---------------------
                   10

As you can see I created the table with a NEXT EXTENT definition of 10 which is larger than the largest contiguous extent in the tablespace. In order to obtain the largest contiguous extent in the tablespace we can query DBA_FREE_SPACE as follows:

SQL> SELECT ROUND(SUM (bytes) / 1024 / 1024) "total_free_space (MB)",
       ROUND(MAX (bytes) / 1024 / 1024) "largest_contiguous_extent (MB)"
  FROM dba_free_space
 WHERE tablespace_name = 'TEST' ; 
total_free_space (MB) largest_contiguous_extent (MB)
--------------------- ------------------------------
                  128                              6

SQL>

As you can see, the largest contiguous extent is 6MB which is smaller than the NEXT extent for the segment (which is 10MB).

Additional Resources

• AskTom - How to Find tablespace Fragmentation
• MOS DOC ID 162994.1 - Script to Report Extents and Contiguous Free Space

My article was published in the OTN!

Hi everyone,

I'm honored and grateful that my new article "Verifying I/O Activity Balance Across Disks in ASM" was published in the OTN. 

The article describes Oracle Automatic Storage Management's mirroring and striping capabilities, focusing on how to identify unbalanced I/O operations.

Click here to read the full atricle: https://community.oracle.com/docs/DOC-995178

What's the difference between SYSDBA and SYSASM?

Introduction

ASM was first introduced in 2003 with Oracle 10gR1 and since then it's the recommended storage management solution by Oracle and it's being used as a filesystem and volumn manager.

In the "old" 10g days, we used to administer the ASM instance using the as SYSDBA role.

The Problem
The problem with using SYSDBA is that in many organization there is a clear seperation between the DBA to the ASM administrator -the DBA in some organization doesn't suppose to add disks, alter disk groups, etc.

The Solution
The solution for this problem was introduced in Oracle 11gR1 when Oracle introduced a new role, SYSASM that should be used by the ASM administrators to perform administrative tasks, such as CREATE/DROP/ALTER diskgroup, or startup/shutdown the ASM instance.
The SYSDBA should be used by the ASM for read-only operations such as qureying dynamic views (e.g. V$ASM_DISKGROUP, V$ASM_FILE, V$ASM_OPERATION, etc.)

Although SYSASM was introduced in Oracle 11gR1, SYSDBA role still had (in Oracle 11gR1) full administrative permissions, but every time an administrative command was executed (such as starting the ASM instance), a warning was reported in the ASM alert log file:
"WARNING: Deprecated privilege SYSDBA for command <…>"

Starting from Oracle 11gR2, Oracle enforced the seperation between the DBA to the ASM administrator and if you will try to connect as SYSDBA and perform administrative task you will get an error because administrative tasks can only be performed by ASM Administrators who connect AS SYSASM, as you can see in the following screenshot: