Oracle 19c ASMCA interface

This blog post will walk through some of very cool screen shots of 19c ASMCA interface. I have no exposure with 18c ASMCA, but, the landing page of 19c ASMCA is really cool. Here are the screenshot for you:


ASM Instance Management

Disk group Management

DG attributes

 root setup


What's new in 19c - Part III (Data Guard)

Business continuity (Disaster Recovery) has become a key aspect of every business for a long time now. Oracle data guard is one of the best solutions for business critical applications running on  Oracle databases. From its inception, a lot has been enhanced in standby database functionality to meet the market demand.

This blog post is dedicated and focused on some key enhancements introduced in 19c Data Guard. Below are my hand-picked list of new features, which really got my attention:

Fast-Start-Failover (FSFO) in Observer-only Mode

Configuring FSFO was really a big debate for quite sometime in Oracle community. Some may recommend and some are not in favor of enabling FSFO. Personally, I was not in favor of this feature. Though the decision is lot depends on various factors.

FSFO can be configured in observe-only mode wit 19c (validate without real action), which allow DBAs to test an automatic failover configuration without actually causing any damage to the databases in production environment. When FSFO is configured in observer-only mode, no actual changes are made to the DG Broker settings, also doesn't require any application changes. And, when the conditions for FSFO are met, the DG Broker adds the messages to the observer log indicating that FSFO would have been initiated. This makes it easer to justify using FSFO to reduce the recovery time for real failover.

To enable FSFO in observer mode, use the below syntax:


Automatic Flashback Standby

Prior to Oracle 19c, when flashback database or point-in-time operations are performed on a primary database, the underlying standby database needs to be put into same point-in-time as its primary database with a manual procedure (for example, FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# - 2;). This functionality is automated in 19c. This new feature enables the standby database to be flashed back automatically whenever flashback database operation is triggered on the primary database. By automating this process, it drastically reduces the time & efforts and improves RTO.

So, when a primary database has any flashback database or point-in-time recovery operations, the standby automatically follow the primary database, and the MRP on standby database perform the following actions:

  • detect the new incarnation
  • flashback the standby to the exam time as its primary
  • restart the standby recovery and move the standby to the new branch of redo
** Note : Flashback operation success is subject to flashback data availability

Automatic flashback standby operation takes place when the database is opened in MOUNT state. If the standby database is open in READ ONLY mode, the error messages are recorded in the alert log and whenever standby database is restarted, the recovery process (MRP) automatically executes the flashback operation.

DML Operations on Active Data Guard

Performing DML operations on Active Data Guard was something long awaited. I remember, there are some application that needs to long an entry into the database whenever they connected to the database. This was causing many applications no to use with Data Guard, specially for testing.

So, it's here finally with Oracle 19c. Though Oracle doesn't recommend heavy DML operations on active standby database pridicting the performance impact on the primary database. But, it's good for applications that mostly read-applications with occasional DML executions.

To configure DML operations, set ADG_REDIRECT_DML init parameter to TRUE or execute the following SQL statement:


Subsequently, perform the DML operations:

SQL> INSERT INTO table VALUES (.......);

** The settings can be database or session level.

DML operations on active standby database are transparently redirected to the primary database upon setting the above configuration, including DML operations that are part of PL/SQL blocks. However, the active data guard session waits until the corresponding changes (DML) are shipped to and applied to the ADG standby database, while maintaining the read-consistency.

To redirect PL/SQL operation from active standby data guard database to primary database, configure the following:


Subsequently, perform the PL/SQL operations :


Image source (https://blogs.oracle.com/oracle-database/oracle-database-19c-now-available-on-oracle-exadata)

Automatic outage resolution with Data Guard

One of the common scenarios of delayed redo transport and gap resolution on data guard is due to network hangs, disconnects, and disk I/O issue. With 19c, new DATA_GUARD_MAX_IO_TIME and DATA_GUARD_MAX_LONGIO_TIME parameters, DBA can tune the amount of wait time for those detection based on the user network and Disk I/O behavior. Data Guard has an internal mechanism to detect these hung processes and terminate them allowing the normal outage resolution to occur.

Here is the list of new parameters for Data guard:

Stay tuned for more 19c new features.


What's new in 19c - Part II (Automatic Storage Management - ASM)

Not too many features to talk on 19c ASM. Below is my hand-pick features of 19c ASM for this blog post.

Automatic block corruption recovery 

With Oracle 19c, the CONTENT.CHECK disk group attribute on Exadata and cloud environment is set to true by default. During data copy operation, if Oracle ASM relocation process detects block corruption, it perform automatic block corruption recovery by replacing the corrupted blocks with an uncorrupted mirror copy, if one is avialble.

Parity Protected Files Support

The level of data mirroring is controlled through ASM disk group REDUNDANCY attribute. When a two or three way of ASM mirroring is configured to a disk group to store write-once files, like archived logs and backup sets, a great way of space is wasted. To reduce the storage overahead to such file types, ASM now introduced PARITY value with REDUNDANCY file type property. The PARITY value specifies single parity for redundancy. Set the REDUNDANCY settings to PARITY to enable this feature.

The redundancy of a file  can be modified after its creation. When the property is changed from HIGH, NORMAL or UNPROTECTED to PARITY, only the files created after this change will have impact, while the existing files doesn't have any impact.

A few enhancements are done in Oracle ACFS, Oracle ADVM and ACFS replication. Refer 19c ASM new features for more details.

** Leaf nodes are de-supported as part of Oracle Flex Cluster architecture from 19c.


What's new in 19c - Part I (Grid Infrastructure)

Every new Oracle release comes with bundle of new features and enhancements. Though not every new feature is really needed to everyone, there are few new features that worth considering. As part of 19c new features article series, this post is about the new features introduced in Grid Infrastructure. This blog post focuses on some real useful GI features with deprecated and de-supported features in 19.2.

Dry-run to validate Cluster upgrade readiness

Whether it's a new installation or upgrade from previous version to latest version, system readiness is the key factor for success. With 19c, Cluster upgrade can have a dry-run to ensure the system readiness without actually performing the upgrade of the cluster. To determine if the system is ready for the upgrade, run the upgrade in dry-run mode. During the dry-run upgrade, you can click the Help button on the installer page to understand what is being done or asked.

Use the command below from the 19c binaries home to run the cluster upgrade in Dry-run mode:

$ gridSetup.sh -dryRunForUpgrade

Once you run through with all the interactive screens for dry-run, check the gridSetupActions<timestamp>.log file for errors and fix them for real upgrade run.

Multiple ASMBn

It is a common practice to have multiple disk groups in a RAC environment. It is also possible to have some disk groups in MOUNT state and some disk groups in DISMOUNT state on a DB node. However, when a db instance on a node try to communicate (startup) with the DISMOUNT disk group will throw errors.

Multiple ASMB project allows for the database to use DISK GROUPS across multiple ASM instances simultaneously.  This enhancement provides the HA to RAC stack by allowing DB to use multiple disk groups even if a given ASM instance happens to have some DISMOUNT disk groups.

AddNode and Cloning with Installer Wizard

Adding a new node and the functionality of installing a gold image (cloning) is simplified and made easy in 19c. Adding new node and Cloning homes now directly available with Installer Wizard, you no longer need to use add node.sh and clone.pl scripts. These commands will be depreciated in the upcoming releases.

Run ./gridSetup.sh to start the installer.

In the upcoming blog, I will discuss about ASM 19c features.


Oracle 19c and my favorite list

Today (14-Feb-2019) Oracle officially released the 19c docs and Oracle Database 19c for Exadata through edelivery channel. Since the news is out, Oracle community is busy talking about 19c availability and sharing articles about 19c etc.

I spent a little amount of time to scan through some of really useful features of 19c for DBAs, and here is my list:
  • Availability
    • Simplified DB parameter management in Broker Configuration
    • Flashback Standby DB when Primary DB is flashed back
    • Active Data Guard DML Redirection
    • New parameter for tuning automatic outage resolution with DG
  • Data Warehousing
    • SQL Diagnostic and Repair Enhancements
    • Automatic Indexing 
    • Performance Enhancement for in-memory external tables
    • Real-Time Statistics
    • High Frequency Automatic Optimizer Statistics Collection
  • Automated install, config and patch
  • Automated upgrade, migration and utilities
  • Performance
    • SQL Quarantine 
    • Real-time monitoring for Developers
    • Workload capture and Replay in a PDB
  • RAC and Grid
    • Automated Transaction Draining for Oracle Grid Infrastructure Upgrades
    • Zero-downtime Oracle Grid Infrastructure Patching

I will start writing series of articles about my favorite Oracle 19c features. Stay tune.



ORA-600 [ossnet_assign_msgid_1] on Exadata

On a Exadata system with Oracle v12.1, a MERGE statement with parallelism was frequently failing with below ORA errors:

                   ORA-12805: parallel query server died unexpectedly

A quick look in the alert.log, an ORA-600 is noticed.

ORA-00600: internal error code, arguments: [ossnet_assign_msgid_1], [],[ ] 

The best and easy way to diagnose any ORA-600 errors is to utilize the ORA-600 tool available on MOS.

In our case, with large hash join, the following MOS note helped to fix the issue:

On Exadata Systems large hash joins can fail with ORA-600 [OSSNET_ASSIGN_MSGID_1] (Doc ID 2254344.1)

On Exadata Systems large hash joins can fail with ORA-600 [OSSNET_ASSIGN_MSGID_1] and the root cause if often a too small default value  for _smm_auto_min_io_size  and  _smm_auto_max_io_size'

and the workaround to fix the issue is to set the following underscore (_) parameters:

_smm_auto_max_io_size = 2048
_smm_auto_min_io_size = 256

In some cases, the below MOS notes helps to fix ORA-600 [ossnet_assign_msgid_1] error.

ORA-600 [ossnet_assign_msgid_1] (Doc ID 1522389.1)