2.14.2019

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.



References:
https://www.oracle.com/a/tech/docs/database19c-wp.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/new-features.html#GUID-06A15128-1172-48E5-8493-CD670B9E57DC
https://medium.com/oracledevs/oracle-database-19c-now-available-on-oracle-exadata-9b57963e2c89

2.04.2019

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
        ORA-06512

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)

Cause:
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)
Bug 14512766 : ORA-600 [OSSNET_ASSIGN_MSGID_1] DURING RMAN CONVERSION

1.23.2019

Automated Cell Maintenance

One of the key actives for a DBA is to well maintain the database servers and Oracle environments. In a complex Oracle environment, managing and maintaining file system space plays a very crucial role. When a FS, where Oracle binaries are stored,  runs out of space, it could lead to some sort of consequences and some situations it can cause service interruption.

One of the routine actives for a DBA in a very busy system is to maintain the FS space by regularly purging or cleaning the old log and trace files. Some DBAs perform these activities through a schedule job. However, Oracle does introduced an auto maintain jobs. For example, in a cluster environment, the logs are maintained in terms of size as well as retention of the historical copies. On Exadata too Oracle has automated the Cell maintenance in place.

In this blog post, we will run through some of useful information about automated cell maintenance activities.

The Management Server (MS) component carries the responsibility of auto space management. For example, when there is a shortage of space in ADR, the MS deletes the files as per below default policy:


  • Files which are older than 7 days in ADR and LOG_HOME directories
  • alert.log will be renamed once it reaches to 10MB, and the historical files are kept for 7 days
  • Upon 80% of FS utilization, the MS triggers the deletion policy for / (root) and /var/log/oracle directories
  • Similar, the deletion policy will be activated when the /opt/filesystem 90% utilized
  • Alerts are cleared based on the criteria and policies

The default retention policy is set to 7 days. If you want to modify the default behavior, you will have to change the metricHistoryDays and dragHistoryDays attributes with ALTER CELL command.

Read the below Oracle document for more insights about auto cell maintenance tasks.

https://docs.oracle.com/en/engineered-systems/exadata-database-machine/sagug/exadata-storage-server-configuring.html#GUID-EACAE5AF-A89D-4A3D-9CC1-A99D6E6FE46E

1.22.2019

Automated Cloud Scale Performance Monitoring capabilities with Exadata Software version 19.1

Starting with v12.2, Oracle Autonomous Health Framework (AHF) multiple components work together 24x7 autonomously to keep the database system healthy and reduces human intervention & reaction time utilizing machine learning technologies .

There is no doubt that Exadata Database Machine delivers extreme performance for all sorts of workload. However, diagnosing critical performance issues still needs some manual work and human intervention to identify root causes. This blog post highlights a new autonomous performance enhancement introduced with Exadata system software v 19.1.

Exadata software Release 19.1 comes with an automated, cloud-scale performance monitoring for a wide-range of sub-systems, such as: CPU, Memory, File System, I/O and network. This feature built with the combination of years of real-world performance triaging experience by Oracle Support, industry best practices and Artificial intelligence (AI) technologies. This feature simplifies root cause analysis without much human intervention. It can automatically detect runtime critical performance issues and also figure out the root cause without human intervention.

Taking a few real-world scenarios, as a DBA, we all have come across on many occasions where a spinning process on a database server eating up all system resources causing complete database death (poor performance). With this enhancement, Exadata System Software automatically identifies the exact process that is causing spinning and generates an alert with root cause analysis. Another typical example will be automatically detecting the misconfiguration of huge pages settings on the server and sending alerts. When how a server and perform badly if the huge pages setting is right on the system.

No additional configuration and special skill set is required for this. Management Server (MS) is responsible to perform these activities. All you need is have Exadata software version 19.1 or higher, and configure your alerts on the servers.

For more details, read the oracle documentation.

https://docs.oracle.com/en/engineered-systems/exadata-database-machine/dbmso/whats-new-oracle-exadata-database-machine-19.1.0.html#GUID-B3DE3C62-278A-48E3-889A-22B9C84B1413

Stay tuned and hunger for more Exadata software 19.1 new features.