Skip navigation
1 2 3 Previous Next

Database Support Blog

71 posts

This weekend, Nov 3, 2019, most time zones will set time back one hour.  However, this year, Brazil will not participate in the clock change, now or in the future.  

For questions/official statements about the timezone change itself, please contact the Brazil Government.  IANA has updated Time Zone Database to reflect this change (tzcode2019b.tar) http://www.iana.org/time-zones .  This change is included in the database DST patch v34 Patch 29997937

 

The Database Support team has also put together several KM Notes to help you understand this change.  The Oracle Kernel itself is not impacted by the OS/System clock changes (or for that matter Oracle DST patches) (See KM Note 1013279.6 How does Daylight Savings Time Affect the Database Kernel).  However, columns and datatypes TSTZ and TSLTZ are affected. 

See KM Note 2603532.1  Brazil No Longer Observes DST From November 2019 Onwards- Impact on Oracle RDBMS

 

Kindest Regards
Marilyn

TFA, ORAchk & EXAchk 19.3

New Release TFA, ORAchk & EXAchk 19.3  Now Available

 

ORAchk, EXAchk & TFA Combined into Autonomous Health Framework (AHF)

 

Oracle used to deliver three separate tools:

 

  •     ORAchk
  •     EXAchk
  •     Trace File Analyzer (TFA)

 

These are now combined into a single installer called Autonomous Health Framework (AHF).

 

This single platform specific installer for TFA, ORAchk and EXAchk can be installed either by root (recommended) or not-root users. The installer includes and builds on all functionality of the previous tools.

 

There is no change to the command-line tools, the same commands you used before will still work with this version.

 

orachk, exachk and tfactl can be found in the AHF_LOC/bin directory.

 

Engineered systems have AHF_LOC/bin/exachk and non-engineered systems have AHF_LOC/bin/orachk

 

TFA, ORAchk and EXAchk remain, a value add-on to the existing support contract. There is no extra fee or license required for use.

 

Automatic masking or sanitizing of sensitive information

 

After copies of diagnostic data are collected, TFA, ORAchk and EXAchk use Adaptive Classification and Redaction (ACR) to sanitize sensitive data in the collections.

 

ACR uses a machine learning based engine to redact a predefined set of entity types in a given set of files. ACR also sanitizes or masks entities that occur in path names.

 

  •     Sanitization replaces a sensitive value with random characters
  •     Masking replaces a sensitive value with a series of asterisks

 

ACR currently sanitizes the following entity types:

 

  •     Host names
  •     IP addresses
  •     MAC addresses
  •     Oracle Database names
  •     Tablespace names
  •     Service names
  •     Ports
  •     Operating system user names

 

ORAchk/EXAchk sanitization will convert sensitive data to a string of random characters.

 

To sanitize ORAchk/EXAchk output include the -sanitize option, e.g.:orachk -profile asm -sanitize

 

You can also sanitize post process by passing in an existing log, html report or zip file, i.e.: orachk -sanitize {file_name}

 

TFA diagnostic collections can be redacted (sanitized or masked). To enable automatic redaction use tfactl set redact=[mask|sanitize|none] (default is none)

 

Alternatively collections can be redacted on-demand e.g.:tfactl diagcollect -srdc ORA-00600 -mask or tfactl diagcollect -srdc ORA-00600 -sanitize

 

If you want to reverse lookup a sanitized value, use orachk/exachk -rmap e.g.: orachk -rmap pu406jKxg,kEvGFDT will print the real values associated with those sanitized values

 

Note: orachk -rmap can also be used to lookup a value sanitized by TFA.

 

Problem repair automation options

 

ORAchk and EXAchk now have the capability to automatically fix problems when found.

 

Certain checks have a repair command associated with them.

 

If you want to see what the repair command actually does you can use orachk -showrepair {check_id}

 

To run the repair commands include one of the following options:

 

orachk -repaircheck all

 

orachk -repaircheck {check_id},[{check_id},{check_id}..]

 

orachk -repaircheck {file}

 

Where {check_id} refers to specific check(s) you want to repair or {file} contains a list of check ids to be repaired

 

Performance improvements in diagnostic collection & DBA tools

 

TFA indexes diagnostic data, which is used for DBA tools and diagnostic collections. The indexing has been changed to improve the performance.

 

This change results in lower CPU usage and faster average execution times for diagnostic collections and the running of DBA tools such as ls, grep, tail, vi etc.

 

If you do not use the DBA tools and are prepared to wait longer for DBA tool execution to complete, you can disable this indexing by running

tfactl set indexInventory=false

 

Additionally when TFA is run on an Exadata machine cell collection now takes place in parallel and uses EXAchk to call diagpack for improved performance.

 

New Service Request Data Collections (SRDCs)

 

This release includes new SRDCs.

 

As with all other SRDCs use tfactl diagcollect -srdc srdc_name.

 

  • ahf: Oracle ORAchk or Oracle EXAchk problems (to be run after running with -debug)
  • dbacl: Problems with Access Control Lists (ACLs)
  • dbaqgen: Problems in an Oracle Advanced Queuing Environment
  • dbaqmon: Queue Monitor (QMON) problems
  • dbaqnotify: Notification problems in an Oracle Advanced Queuing Environment
  • dbaqperf: Performance problems in an Oracle Advanced Queuing Environment
  • dbparameters: Oracle Database single instance shutdown problems
  • emagtpatchdeploy: Enterprise Manager 13c Agent patch deployment problems
  • emagentperf: Enterprise Manager 13c Agent performance problems
  • emagentstartup: Enterprise Manager 13c Agent startup problems
  • emfleetpatching: Enterprise Manager Fleet Maintenance Patching problems
  • empatchplancrt: Enterprise Manager patch plan creation problems
  • exservice: Exadata: Storage software service or offload server service problems
  •     ORA-25319: for ORA-25319 problems
  •     ORA-01000: for ORA-01000 problems
  •     ORA-00018: for ORA-00018 problems

 

 

ORAchk and EXAchk integration with Database AutoUpgrade & Cluster Verification Utility (CVU)

 

Both CVU and the database AutoUpgrade tool (in analyze mode) are run by ORAchk & EXAchk in -preupgrade mode

 

All findings from AutoUpgrade and CVU are cross verified to avoid duplication and contradiction.

 

Results are included in the ORAchk/EXAchk report output.

 

CVU checks are only run when a CVU version of 11.2.0.4 or greater if found on the system.

 

CVU related options:

  • -cvuhomewhere to find the CVU installation
  • -cvuonlyonly run CVU checks

 

ORAchk support for ODA X8 machines

  • ORAchk now provides support for ODA X8 machines.

ORAchk and EXAchk support for generic asymmetrical oracle_homes

  • ORAchk and EXAchk used to require all oracle homes to be present on all database servers. If they were not you would see issues such as skipped checks.
  • ORAchk and EXAchk now support asymmetrical oracle_homes, so it does not require the same oracle_home to exist on each node of the cluster.

EXAchk support for Oracle Exadata using RoCE InfiniBand

  • RDMA over Converged Ethernet (RoCE) is a network protocol that allows remote direct memory access (RDMA) over an Ethernet network.
  • EXAchk release 19.3 adds support for RoCE InfiniBand for Oracle Exadata. There are no checks for InfiniBand switches and fabrics when run on RoCE InfiniBand for Oracle Exadata. If the -profile switch is used, it will throw an error saying it is not a supported profile.

The Announcement made at the beginning of CY2018:

 

If you're running an older version of the Oracle Database and you have applications that are actively using DB Links to connect to other databases, regardless of their version, you may need to take action to ensure your database applications continue to work as expected. We are strongly encouraging users of these older databases to consider upgrading to a supported release, or at a minimum, verify the latest patch recommendations have been applied.

 

The issue applies to the following Oracle Databases versions:

10g release 2 (10.2.0.5 and lower)

11g release 1 (11.1.0.7 and lower)

11g release 2 (11.2.0.3 and lower)

12c release 1 (12.1.0.1)

 

If you are on one of the following Database versions, this issue will not affect you:

11g release 2 (11.2.0.4)

12c release 1 (12.1.0.2)

12c release 2 (12.2.0.1)

18c

19c

 

The detailed instructions with the required patches that you need to apply incase you can't upgrade your databases to recommended versions are outlined in DOC ID 2335265.1

 

These patches will increase the Database’s SCN Rate limit automatically from 16K/s to 96K/s, and enable databases to support much higher transaction rates. This fix is already present in 11.2.0.4, 12.1.0.2, 12.2.0.1 and all versions above 12.2.0.1. This SCN rate increase also knows as SCN rollover was slated to happen automatically on June 23rd 2019 on all the above versions and also the lower versions patched to recommended levels.

 

If the patches are not applied, the unpatched database will have a lower SCN rate or lower current max SCN limit.

 

Sometimes, there can be a scenario where in the patched database can be at higher SCN level while the unpatched database is at much lower SCN level due to these rate limit allowance differences. When there is a requirement to connect these patched and unpatched databases, the SCN sync has to happen and if this sync needs the SCN to increase more than the Current max SCN limit in the unpatched database, the dblink connection will fail.

 

Current Status:

 

For 11.2.0.4 & above versions and for patched lower versions, the automatic SCN Rate Limit Increase from 16K/s to 96K/s has already happened on June 23rd, 2019. No major issues were reported.

You would have noticed a message similar to below in the alert log of the databases that are at the recommended patch levels on June 23rd, 2019.

"Database SCN compatibility changed from 1 to 3 due to auto-rollover"

Even if the database was down during this autorollover period, the rollover would have automatically happened whenever the database started after June 23rd, 2019. You would see the same message immediately after the database startup.

 

Action:

 

If you haven't already upgraded the databases or moved them to the recommended patch levels, please do so as soon as possible to eliminate the potential risk on your database links (refer DOC ID 2335265.1). Until then, please monitor the 16k/s SCN headroom of the patched/upgraded database(that has dblinks pointing to lower/unpatched databases) using the script from the DOC ID 1393363.1. If this headroom falls below 20, please remove the dblink connections to unpatched databases and upgrade/patch the unpatched database immediately for the dblink connections to work.

The 16k/s SCN headroom can be monitored using the script from the DOC ID 1393363.1

 

If you want to verify if your database is already running at 96K/s limit(or compatibility 3), you can use the below procedure. If the "Current SCN Compatibility" shows 3, you are at 96K/s SCN rate limit. Please make a note that this procedure may not work in some of the older database versions.

set serveroutput on;

declare

cur_scn_compat number;

max_scn_compat number;

begin

dbms_scn.GetCurrentSCNParams(cur_scn_compat,max_scn_compat);

dbms_output.put_line('Current SCN Compatibility=' || cur_scn_compat);

dbms_output.put_line('max_scn_compat=' || max_scn_compat);

end;

/

 

Thank you for reading this blog post.  Let us know if you have any questions or if there are other areas you would like to see discussed in this forum. 

Oracle

 

Extended Support Fee Waiver for Oracle Database 12.1 and 11.2 for Oracle E-Business Suite

Due to the certification timing of Oracle E-Business Suite with Oracle Database versions beyond 12.1, Oracle has waived Extended Support fees for Oracle Database 12.1 and 11.2 for Oracle Database licenses used for Oracle E-Business Suite through December 2020.

 

This Extended Support fee waiver will be specific to the Oracle Database licenses, within your company’s footprint, that you use for running Oracle E-Business Suite software.

 

If you have already paid your Oracle Database Extended Support fees, you may be eligible to receive a credit that we will apply against your Oracle account.

 

The Lifetime Support section of the Oracle Software Technical Support Policies documents the Extended Support fee waiver. To contact Oracle and determine your eligibility for either the Extended Support fee waiver or credit for previously paid Extended Support fees, you can either access your on-premises Oracle Premier Support Renewal information via My Support Renewals using your Oracle Single Sign-On and log a service request or, alternatively, you can contact Oracle Support Renewals using the Oracle Global Contacts page.

 

Oracle has been a trusted resource for our customers for over 40 years. We always strive to provide innovations that help your business succeed and help you keep your systems secure. We look forward to our continuing relationship.

What's New in Trace File Analyzer 19.2

For details of previous version history Download the TFA Version History

 

New features in TFA 19.2:

Temporarily restrict automatic collections for specific events

 

  • dbracinst: Oracle RAC-related data collection for Oracle Database problems
  • gridinfra: Oracle RAC-related data collection for Oracle Clusterware problems
  • gridinfrainst: Data collection for Oracle RAC upgrade and patching problems
  • exsmartscan: Oracle Exadata - Smart Scan not working problems
  • zlgeneric: Data collection for Zero Data Loss Recovery Appliance (ZDLRA) problems

 

Event specific collection for default diagnostic collection

 

The default diagcollect has been enhanced to make it easier to collect the most useful diagnostics.

When you run a default diagnostic collection it will now present you with a list of recent events to optionally collect for.

 

 

Option to restrict the collection of excessively large files

 

Prevent excessively large files from delaying or stalling collections.

Run tfactl set maxfilecollectionsize for the diagnostic collection command to consider only the last 200 KB for the files that are larger than the size specified.

 

For more information on the TFA Collector, see TFA Collector - TFA with Database Support Tools Bundle (Note 1513912.1)

Upgrading an Oracle Database While Performing A Migration (Cross Platform or Within the Same OS)

 

This article will explain process of upgrading the database during the migration process. Performing migration and upgrade separately will take considerably long time, but by following correct process you should be able to perform both activities together.

 

Difference between upgrade and migration

Upgrade refers to the upgrade of the software version, while migration refers to moving the database from one location to another location.(he release version may or may not be changed)

 

Before performing migration or upgrade, verify the compatibility:

  • Compatibility of upgrade ( using DBUA/manual Method)

Before performing upgrade make sure that you are upgrading to supported version, refer to below document (This applicable to manual or DBUA upgrade)

 

Database Server Upgrade/Downgrade Compatibility Matrix (Doc ID 551141.1)

 

  • Compatibility of upgrade ( using Exp/Imp )

For upgrade using exp/imp follow below compatibility matrix:

 

Compatibility Matrix for Export And Import Between Different Oracle Versions [Video] (Doc ID 132904.1)

 

  • Compatibility of Migration

Before migrating the database to another OS make sure to verify the Endian compatibility as not all methods support cross platform migration.

 

 

  

Different Method of upgrade

Basically there are two ways of upgrading the database.

  • Running Catupgrd.sql ( Manual or DBUA)
  • EXP/IMP or EXPDP/IMPDP

 

Pre-Upgrade / Pre-Migration steps

Make sure to verify all pre-upgrade check in source Database, Execute Pre-upgrade script from source home as shown below:

 

This script will perform pre-upgrade check and provide recommendations. Follow all the recommendations/warnings by this script carefully. This will eliminate most of the known issues during upgrade. Also make sure that there is no component in invalid state or there is  no SYS/SYSTEM owned objects in invalid state before performing upgrade. Any invalid components, objects ( Owned by SYS/SYSTEM) can cause issue during upgrade.

Running these steps are often missed during migration, you need to fix existing issues in source database before migration if you will perform upgrade as well.

 

Different Method of Migration

There are multiple methods of upgrading and migrating database together. Below are some methods:

  • OS or SAN copy
  • EXP/IMP or EXPDP/IMPDP
  • RMAN backup and Restore
  • RMAN duplicate with noopen mode.
  • Other methods

 

Below method describe the different process:

 

Migration Of An Oracle Database Across OS Platforms (Generic Platform)(Doc ID 733205.1)

 

ADVISOR WEBCAST:

We invite you to attend a 1 hr webcast on this topic scheduled on 23rd April 2019 that explains these methods in details.

 

Upgrading an Oracle Database While Performing A Migration (Cross Platform or Within the Same OS)

 

Schedule:

Tuesday , April 23, 2019 08:00 AM (US Pacific Time)

Tuesday , April 23, 2019 05:00 PM (Central European Time)

Tuesday , April 23, 2019 08:30 PM (India Standard Time)

            

WebEx Conference Details

Event Number: 598 439 151

Event Passcode: 909090

Register for this Advisor Webcast: https://oracleaw.webex.com/oracleaw/onstage/g.php?d=598439151&t=a

Once the host approves your request, you will receive a confirmation email with instructions for joining the meeting.

What is AskTOM Office Hours?

 

Office Hours offers free, live Q&A sessions with experts in various aspects of Oracle technologies. Our experts are drawn from the ranks of Product Managers, developers, development managers and evangelists. We make ourselves available each month to help you be as successful as you can with Oracle products.

 

Why should you subscribe?

 

You don't have to register for an Office Hours on a particular topic. You can simply click on the Join Session button when you go to your Office Hours page within an hour of a session starting.

If you register, however, you will:

  • Get email reminders of your upcoming sessions (a weekly digest on Monday with all your upcoming Office Hours for that week + a reminder within an hour of the sesion starting).
  • Send a question to the experts in advance of the session. This will give us a chance to review the question and prepare an answer.
  • Rate the session and give us advice on how to improve Office Hours in the future.
  • Hear about new Office Hours sessions we've recently added.

So we hope you'll sign up and take full advantage of Office Hours!

 

How does Office Hours work?

 

The Office Hours page will display a Join Session within an hour of your session starting. Click on the link when the time is right, and you'll be placed in the conference, where we will take your questions via chat. 

Check the Get Ready section on the page in advance of the session, so you can make sure your browser and computer are configured properly to participate in the webcast.

 

And if I don't want reminders?

 

We understand. You probably get an awful lot of emails. You'll get at most one digest email on Monday, and one reminder before your session. But if you want to rely solely on your memory and calendar, just sign in to AskTOM, navigate to the Office Hours session and click on the Withdraw button. You will no longer receive notice of sessions on this topic.

 

What can I ask about?

 

Office Hours sessions will usually have a particular focus (example: exception handling in PL/SQL), but could also be a truly open Q&A session ("Ask me anything about PL/SQL").

The objective of Office Hours is to improve your expertise at utilizing Oracle Database to build your applications. Office Hours is not a substitute for My Oracle Support. We will not discuss tickets logged with Support. Office Hours is not a place to discuss licensing or account issues. You need to take up such issues with your Oracle account representative.

You are welcome to share code with us on an Office Hours session, but please be aware that other developers not working for your company may see that code. By sharing your code, you are also giving us permission to view and display that code to others.

 

Here are some of the upcoming AskTom Sessions that you might enjoy:

 

For a full list of upcoming sessions, go here: https://asktom.oracle.com/pls/apex/f?p=100:500:::NO:RP::

In 12c, Oracle XML Database (XDB) is a mandatory component and cannot be uninstalled.  This is because there are many dependencies on XDB.  The following database components / features rely on XDB to be installed and in a VALID state:

•    Oracle Application Express (APEX)

•    Oracle Expression Filter

•    Oracle interMedia / Multimedia DICOM

•    Oracle Multimedia Image metadata extraction of EXIF, ORDIMAGE, IPTC and XMP metadata

•    Spatial (including Oracle Locator)

•    OLAP

•    Oracle Applications such as iRecruitment

•    Any procedure using UTL_TCP, UTL_HTTP, UTL_SMTP

•    XMLTYPE operations

•    XBRL (Extensible Business Reporting Language) from 11.2.0.2 onwards

 

If the XDB component becomes invalid, this will cause the components above to become invalid as well.  XDB can become invalid for different reasons, but a common culprit is the setting for the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable.  This environment variable is used to resolve the location of the shared library "libxdb.so" (libxdb.sl on HP).  If Oracle is not able to access that library, many different errors / symptoms can occur.

 

First, lets discuss which platform uses which environment variable:

 

 

Environment Variable Platforms
LD_LIBRARY_PATH           Linux, Solaris
LIBPATH AIX
SHLIB_PATH                       HP

 

 

Often times, the LD_LIBRARY_PATH environment variable is set for AIX.  However, that setting will have no affect as AIX uses the LIBPATH environment variable.  Keep this in mind if you are using AIX.

 

It is very important that the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable be set correctly.  That is, the first directory referenced must be $ORACLE_HOME/lib.  You can reference other paths in this setting, but they cannot be the first path.

 

If this environment variable is not set correctly when the instance is last started, you may receive one or more of the following errors when installing XDB, upgrading XDB, configuring APEX, running an Export, selecting from xdb.xdb$resource/sys.dba_network_acls, or using other XDB features:

•    ORA-31159: XML DB is in an invalid state

•    ORA-00600: internal error code, arguments: [unable to load XDB library]

•    ORA-00600: internal error code, arguments: [qmx: no ref]

•    ORA-00600: internal error code, arguments: [qmtGetColumnInfo1]

•    ORA-00600: internal error code, arguments: [qmtb_init_len]

•    ORA-00600: internal error code, arguments: [qmtGetBaseType]

•    ORA-00600: internal error code, arguments: [psdnop-1], [600]

•    ORA-00600: internal error code, arguments: [qmtInit1]

•    ORA-07445: exception encountered: core dump [_memcpy()+224] [SIGSEGV] [Address not mapped to object]

•    ORA-19051 Cannot use fast path insert for this XMLType table

•    ORA-31011: XML parsing failed

•    ORA-00600: internal error code, arguments: [qmtcolcb_nomatch], [ID], [annotation], [], [], [], [], [], [], [], [], []

 

Errors of this sort generally occur when the init routines for the internal XDB functions are run in an invalid environment, causing memory corruption.

 

Many failed upgrades happen because the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable is pointing to the wrong $ORACLE_HOME/lib directory rather than to the correct location for the instance, or it is not even set at all.

 

 

To check if the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable was set correctly during the last database startup, run the following OS commands:

 

Determine the pid of the smon process:

 

% ps -ef | grep smon

 

Get the environment of the smon process:

 

% pargs -e  <pid from above> -- Solaris

% ps eauwww <pid from above> -- Linux and AIX

 

See the following document for more information:

(Doc ID 373303.1) How to Check the Environment Variables for an Oracle Process

 

If you find that the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable is not set or is not set correctly, do the following:

 

1. Shutdown the database

 

2. Set LD_LIBRARY_PATH (LIBPATH for AIX or SHLIB_PATH for HP) as follows:

 

    csh: setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:<other paths>

    ksh: export LD_LIBRARY_PATH=$ORACLE_HOME/lib:<other paths>

 

3. On AIX only, run /usr/sbin/slibclean as root to remove any currently unused modules in the kernel and library memory.

 

4. Restart the database.

 

If you are using RAC and startup the database with the SVRCTL command, see the document below:

(Doc ID 733567.1) Setting Environment Variables Through Srvctl

 

We hope this information is helpful. For more information on the installation of XDB and best practices, see the following document:

(Doc ID 1292089.1) Master Note for Oracle XML Database (XDB) Install / Deinstall

Oracle Trace File Analyzer 18.4.1

New Release Trace File Analyzer 18.4.1  Now Available

 

New SRDCs (Service Request Diagnostic Collections)

The following new SRDCs are now available:

  • dbdatapatch - for Datapatch problems
  • dbstandalonedbca - for Database Configuration Assistant problems
  • dbspatialinstall - for Oracle Spatial Installation problems
  • dbrmanperf - has been updated to include diagnostic data collection for RMAN Backup, RMAN Maintenance, RMAN Restore and Recover, RMAN-08137 or RMAN-08120, and RMAN Performance problems
  • dbtextupgrade - for Oracle Text version 12.1.0.1 and later upgrade problems
  • ORA-04023 - for ORA-04023 problems
  • ORA-04063 - for ORA-04063 problems
  • dbspatialexportimport - for Oracle Spatial Export/Import problems

 

As with all other SRDCs use tfactl diagcollect -srdc srdc_name.

Merging of multiple automatic SRDCs

If TFA detects a significant event, then it waits five minutes to check any other events, which occur as a result, and collects all of these.

If there are multiple SRDC related events, which occur on the same Database within that five minute period, then TFA performs one merged SRDC collection for all of them.

For example: If an ORA-00600 event occurs and 30 seconds later an ORA-04031 event occurs, then TFA performs one SRDC that collects everything necessary to resolve both the errors.  Merging of SRDCs applies to the following:

 

  • ORA-07445
  • ORA-04030
  • ORA-04031

Over future releases the automatic collection of specific SRDCs will be extended to cover other events.

Running SRDC commands in silent mode

SRDCs have prompts for problem details, which are specific to the SRDC type.

The answers to these prompts can now be provided directly from the command line, allowing the full automation of all SRDCs.

You can view the SRDC-specific prompt by running:

tfactl diagcollection -srdc {srdc_type} -help

Step by Step Instructions for Detection, Diagnosis & Resolution of ORA-600 (ORA-00600 Internal error)

 

As a DBA it is not uncommon to find ORA-00600 errors in your alert log.

 

Because there is no other context to the ORA-600 it can be difficult to know if it's something you need to act on.

 

This post guides you through what the error means, how to use Oracle Trace File Analyzer (TFA) to detect it, how to diagnose the cause and then how to resolve.

Here's what's covered below:

 

 

This is the overall process for detection, diagnosis & resolution:

ORA-600 detection diagnosis and resolution process.jpg

 

What is an ORA-600 (ORA-00600 internal error)?

ORA-00600 internal error, also referred to as ORA-600 is a generic error code. The Oracle Database uses ORA-00600 when it encounters an unexpected internal condition.

The error will look like this:

Thu Sep 13 08:09:49 2018

Errors in file /scratch/app/oradb/diag/rdbms/ogg11204/ogg112041/trace/ogg112041_ora_8881.trc  (incident=99577):

ORA-00600: internal error code, arguments: [ktfbtgex-7], [1015817], [1024], [1015816], [], [], [], [], [], [], [], []

Incident details in: /scratch/app/oradb/diag/rdbms/ogg11204/ogg112041/incident/incdir_99577/ogg112041_ora_8881_i99577.trc

The arguments in square brackets will contain different values. These depend on where in the code the problem occurred and what it was doing.

 

The value of the first argument shows where in the code the error occurred. Later arguments contain other runtime values such as variable values.

 

The first argument and database version is often enough to identify a known problem. But as functions get invoked in many different ways, Oracle Support may also need to use the error stack trace for more specific analysis. This shows the code path taken leading up to the error.

 

 

 

1) Detecting ORA-00600 Errors

The first step is to detect when an ORA-00600 error occurs. For this you should make sure the latest Oracle Trace File Analyzer (TFA) is installed and email notifications are enabled.

 

You may already have TFA installed. It’s included with Oracle Grid Infrastructure 11.2.0.4+ and 12.1.0.2+ and with the 12.2 Database. For more details on TFA, take a look at the TFA Overview Guide.

 

Oracle releases new versions of TFA every 3 months. New versions include bug fixes and enhancements to the data collected.  So you should upgrade to the latest if possible.

 

For the purposes of ORA-00600 error detection, the earliest version of TFA you should have is 18.3.0.

 

TFA installation

Perform the TFA installation as the root user.

 

Installation will:

Install on all nodes if clustered

Auto discover relevant Oracle Software & Exadata Storage Servers

Start monitoring all discovered items for significant events

Automatically collect all required diagnostics when significant errors occur and send you email notification

 

To install TFA follow these steps:

 

1) Download latest version from My Oracle Support Document 1513912.1, transfer to required server & unzip

Execute the installer (if the database is clustered the installer takes care of the remote nodes as well):

 

./installTFA<platform>

 

2) Configure the email(s) to use for notification when TFA detects significant errors

To set the notification email to use for a specific ORACLE_HOME, include the operating system owner in the command:

 

tfactl set notificationAddress=os_user:email

 

For example:

 

tfactl set notificationAddress=oracle:some.body@example.com

 

To set the notification email to use for any ORACLE_HOME:

 

tfactl set notificationAddress=email

 

For example:

 

tfactl set notificationAddress=another.body@example.com

 

 

3) Configure the SMTP server for TFA to use when sending email notifications.

Set the SMTP parameters when prompted:

 

tfactl set smtp

 

You can verify the smtp configuration using:

 

tfactl sendmail email_address

 

When TFA detects an ORA-00600 error has occurred (or other events), you will get an email notification like this:

ORA-600 Notification.jpg

 

 

 

What to do if you can’t install TFA as root?

If you are unable to install as root it is still useful to use TFA for diagnostic collection.

 

You will not be able to detect errors or collect from remote hosts unless installed as root.

 

1) Download the latest version from My Oracle Support Document 1513912.1, transfer to each required server & unzip:

2) Execute the installer:

 

./installTFA<platform> -extractto <dir> -javahome <jre_home>
Use the JRE 1.8 or higher, already available in the RDBMS home.

 

 

If you install as a non-root user, TFA will not be able to automatically detect ORA-00600, so to collect diagnostic packages you will need to follow the steps in the next section, where you will perform an on-demand diagnostic collection.

 

 

What to do if the ORA-00600 error occurred before you had TFA installed

If the ORA-00600 error occurred before you had TFA 18.3.0+ installed you can still use TFA to collect all necessary diagnostics.

 

As the oracle user or equivalent run:

 

tfactl diagcollect –srdc ORA-00600

 

 

This will prompt for time of the event and database, then show you all corresponding errors found and let you collect diagnostics for the appropriate one.

 

Then it will collect all diagnostics and package them in a single zip file, ready to move onto the next step.

 

For example:

bash-4.1$ /scratch/app/11.2.0.4/grid/bin/tfactl diagcollect -srdc ORA-00600

Enter the time of the ORA-00600 [YYYY-MM-DD HH24:MI:SS,<RETURN>=ALL] : 2018-09-13

Enter the Database Name [<RETURN>=ALL] :

No events matching the timestamp Sep/12/2018 18:00:00-Sep/13/2018 06:00:00.

The timestamp must be between Sep/13/2018 07:08:40 and Sep/13/2018 10:17:18.

  1. 1. Sep/13/2018 10:17:18 : [ogg11204] ORA-00600: internal error code, arguments: [ktfbtgex-7], [1015817], [1024], [1015816], [], [], [], [], [], [], [], []
  2. 2. Sep/13/2018 09:41:24 : [ogg11204] ORA-00600: internal error code, arguments: [ktfbtgex-7], [1015817], [1024], [1015816], [], [], [], [], [], [], [], []
  3. 3. Sep/13/2018 09:31:29 : [ogg11204] ORA-00600: internal error code, arguments: [ktfbtgex-7], [1015817], [1024], [1015816], [], [], [], [], [], [], [], []
  4. 4. Sep/13/2018 08:38:43 : [ogg11204] ORA-00600: internal error code, arguments: [ktfbtgex-7], [1015817], [1024], [1015816], [], [], [], [], [], [], [], []
  5. 5. Sep/13/2018 08:09:49 : [ogg11204] ORA-00600: internal error code, arguments: [ktfbtgex-7], [1015817], [1024], [1015816], [], [], [], [], [], [], [], []

 

Please choose the event : 1-5 [1] 1

Selected value is : ( 1. Sep/13/2018 10:17:18 : [ogg11204] ORA-00600: internal error code, arguments: [ktfbtgex-7], [1015817], [1024], [1015816], [], [], [], [], [], [], [], [] )

Scripts to be run by this srdc: ipspack rdahcve1210 rdahcve1120 rdahcve1110

Components included in this srdc: OS CRS DATABASE NOCHMOS

Collecting data for local node(s)

Scanning files from Sep/13/2018 04:17:18 to Sep/13/2018 16:17:18

Collection Id : 20180914032048myserver69

Detailed Logging at : /scratch/tfa_repository/srdc_ora600_collection_Fri_Sep_14_03_20_48_PDT_2018_node_local/diagcollect_20180914032048_myserver69.log

2018/09/14 03:20:53 PDT : NOTE : Any file or directory name containing the string .com will be renamed to replace .com with dotcom

2018/09/14 03:20:53 PDT : Collection Name : tfa_srdc_ora600_Fri_Sep_14_03_20_48_PDT_2018.zip

2018/09/14 03:20:54 PDT : Scanning of files for Collection in progress...

2018/09/14 03:20:54 PDT : Collecting additional diagnostic information...

2018/09/14 03:21:04 PDT : Getting list of files satisfying time range [09/13/2018 04:17:18 PDT, 09/13/2018 16:17:18 PDT]

2018/09/14 03:21:21 PDT : Collecting ADR incident files...

2018/09/14 03:22:27 PDT : Completed collection of additional diagnostic information...

2018/09/14 03:22:31 PDT : Completed Local Collection

.--------------------------------------.

|          Collection Summary          |

+------------+-----------+------+------+

| Host       | Status    | Size | Time |

+------------+-----------+------+------+

| myserver69 | Completed | 20MB |  97s |

'------------+-----------+------+------'

Logs are being collected to: /scratch/tfa_repository/srdc_ora600_collection_Fri_Sep_14_03_20_48_PDT_2018_node_local

/scratch/tfa_repository/srdc_ora600_collection_Fri_Sep_14_03_20_48_PDT_2018_node_local/myserver69.tfa_srdc_ora600_Fri_Sep_14_03_20_48_PDT_2018.zip

 

 

2. Compare diagnostics to known problems

ORA-00600 diagnosis can be done automatically using the My Oracle Support ORA-00600 Troubleshooting Tool.

 

The ORA-00600 Troubleshooting Tool performs the same actions as an older tool you may have heard of the “ORA-600 lookup tool”, but it does it automatically and can also be used to log SRs.

 

 

On the first screen choose to “Troubleshoot a new issue” and then click next

ORA-600 Troubleshooting Tool Step 1-highlight.jpg

 

Now choose the zip file TFA created, choose upload and then next

ORA-600 Troubleshooting Tool Step 2-highlight.jpg

 

The ORA-00600 Troubleshooting tool will now analyze the contents of the diagnostics in the TFA collected zip file and compare it to known problems.

Follow the instructions on these to understand more about the problem and how to fix it.

ORA-600 Troubleshooting Tool Step 3.jpg

 

 

3. Create a new SR

If the troubleshooting tool is unable to find a known problem, or you need more help then scroll down and choose Create SR

ORA-600 Troubleshooting Tool Step 4-highlight.jpg

 

On the SR creation screen, complete all fields and choose Create SR

ORA-600 Troubleshooting Tool Step 5.jpg

 

This will create a new SR and automatically attach the TFA diagnostic collection you previously provided.

Oracle Support will now complete further analysis on the diagnostics and work with you to help resolve the problem.

 

 

Summary

In summary

ORA-00600 is a generic error code. The Oracle Database uses ORA-00600 when it encounters an unexpected internal condition.

Use Oracle Trace File Analyzer (TFA) to automatically detect ORA-00600 errors.

Alternatively TFA can be used on-demand after an ORA-00600 to collect and package all required diagnostics.

Use the My Oracle Support ORA-00600 Troubleshooting Tool to compare the TFA collected diagnostics to known problems

If you need further help continue with the Troubleshooting Tool to log an SR.

This will automatically upload the TFA diagnostic collection, providing Oracle Support everything they need to help you resolve the problem.

 

For more information and to download Oracle Trace File Analyzer see Document 1513912.1.

 

A new release of Oracle Database will soon be available. We encourage you to start planning now to stay current.

 

We recommend the following upgrade options:

 

  • If you are currently running 11.2.0.4/12.1.0.2 and you have an Unlimited License Agreement with no immediate need to upgrade, Oracle Database 19c is a good choice. The production release of Oracle Database 19c will be available in CY2019.
  • If you are currently running 11.2.0.4/12.1.x and you need to upgrade now, 18c is a good choice.

 

NOTE: Oracle Database 18c will be supported for two years after Oracle Database 19c platforms have released on premises. Oracle Database 19c is the Long Term Support Release or “Terminal Patch Set” for DB 12.2.

 

The purpose of these recommendations is to help you stay within the guidelines of Lifetime Support and Error Correction Policies (allowing you to avoid costly support exceptions), and to encourage you to take advantage of the new features that are available in the latest product releases.

 

For questions related to certification combinations and Lifetime Support agreements with other Oracle products like Oracle E-Business Suite, Oracle JD Edwards, Oracle PeopleSoft, and Oracle Siebel, please contact your Oracle Account Representative.

 

For additional information, refer to Release Schedule of Current Database Releases (Doc ID 742060.1).

 

Regards,

Oracle Support

New Release Trace File Analyzer 18.3.0  Now Available

Automatic SRDCs

Oracle Trace File Analyzer will now automatically invoke the relevant SRDC when it detects the following events:

  • ORA-00600
  • ORA-04030
  • ORA-04031
  • ORA-07445

  style="margin:0.5em"

  • Add the user Tomcat runs as to the TFA access list tfactl access add -user {tomcat_user}

New SRDCs

  style="margin:0.5em"

  • dbsqlperf - for SQL performance problems
  • dbtde - for Transparent Data Encryption (TDE) problems
  • emagentperf - for Enterprise Manager Agent performance problems
  • emomscrash - for Enterprise Manager crash problems
  • emomsheap - for Enterprise Manager java heap usage or performance problems
  • emomshungcpu - for Enterprise Manager OMS crash, restart or performance problems

As with all other SRDCs use tfactl diagcollect -srdc srdc_name.

ORAchk automatic start from install

Oracle Trace File Analyzer root installations on Linux or Solaris on non-engineered systems will automatically setup and run the ORAchk daemon.

The daemon will be restarted at 1am every morning, in order to discover any environment changes. A full local ORAchk run will be performed at 2am every morning and a partial run of the most impactful checks will be run every 2 hours via the oratier1 profile.

Any collections older than 2 weeks will automatically be purged.

Once auto start is enabled the daemon settings can be changed as per normal and auto start can be removed any time by using tfactl run orachk -autostop

Oracle Database 18c (18.3) has officially been released for download on Linux  x86-64. See KM Doc 742060.1 for more details on the release schedule for other  platforms.

              

This is the first version of the database to follow the new yearly release  model.  Read KM Doc 2285040.1 for more details.  For practical  purposes, you can think of Oracle Database 18c as a Patchset of the Oracle  Database 12c release. However, instead of being named “Oracle Database 12c Release  2 12.2.0.2”,  the new naming structure reflects the year in which the  product released.
                   
Oracle Database 18c contains a lot of small, but extremely useful incremental  improvements, most of which focus on the three key marquee features in Oracle  Database 12c Release 2:

            

  •     Multitenant
  •     Database In-Memory
  •     Oracle Database Sharding

 

You can read more  details on the changes and improvements in Maria Colgan's Oracle Database blog post or in the Oracle  Database 18c documentation.

REST service

Oracle Trace File Analyzer now includes REST support, allowing invocation & query over HTTPS.

To facilitate development and testing this REST support, Oracle REST Data Services (ORDS) is included within the install.

To enable REST, start ORDS: tfactl rest -start.

The standalone ORDS setup feature utilizes file based user authentication and is provided solely for use in test and development environments.

For production use, the included ords.war should be deployed and configured.

REST supports printing details, starting a diagcollect and downloading collections

See the documentation for full API details and examples

Oracle Cluster Health Advisor integration

Oracle Trace File Analyzer now integrates with Oracle cluster Health Advisor and consumes the problem events Oracle Cluster Health Advisor detects.

When Oracle Cluster Health Advisor detects a problem event Oracle Trace File Analyzer will automatically trigger the relevant diagnostic collection and send email notification.

Email notification is configured via the standard Oracle Trace File Analyzer notification process.

New SRDCs

The following new SRDCs are now available:

  • ORA-01031 - for ORA-01031 errors
  • ORA-01578 - for ORA-01578 errors
  • ORA-08102 - for ORA-08102 errors
  • ORA-08103 - for ORA-08103 errors
  • dbblockcorruption - for problems showing alert log messages of "Corrupt block relative dba"
  • dbfs - for ASM/DBFS/DNFS/ACFS problems
  • dbpartition - for create/maintain partitioned/subpartitioned table/index problems
  • dbpartitionperf - for slow create/alter/drop commands against partitioned table/index
  • dbsqlperf - for SQL performance problems
  • dbundocorruption - for UNDO corruption problems
  • esexalogic for Exalogic full Exalogs data collection information
  • listener_services - for listener errors : TNS-12516 / TNS-12518 / TNS-12519 / TNS-12520
  • naming_services - for naming service errors: ORA-12154 / ORA-12514 / ORA-12528
  • dbaudit - Standard Information for Database Auditing

  style="margin:0.5em"

  • dbrman600 - for RMAN-00600 error
  • dbrmanperf - for RMAN Performance problems

As with all other SRDCs use tfactl diagcollect -srdc srdc_name.

Use external SMTP server for notifications

To configure the SMTP server that Oracle Trace File Analyzer will use for notifications run tfactl set smtp

You can check the current SMTP configuration details using tfactl print smtp

Once configured you can then verify configuration by sending a test email with tfactl sendmail {email_address}

Metadata search capability

All metadata stored in the Oracle Trace File Analyzer index is now searchable using tfactl search -showdatatypes|-json [json_details].

Searching for all events for a particular database between certain dates can be done like this:

tfactl search -json '{ "data_type":"event", "content":"oracle", "database":"rac11g", "from":"01/20/2017 00:00:00", "to":"12/20/2018 00:00:00" }' 

Listing all index events can be done with: tfactl search -json '{"data_type":"event"}'

Listing all available datatypes can be done with :tfactl search -showdatatypes

3 X Faster Performance

 

ORAchk & EXAchk have been completely rewritten, with a focus on performance and extensibility.

Internal testing results show this version runs up to 3 X faster than 12.2.0.1.3. Performance is particularly improved on lower resourced environments.

 

Option to Only Run Checks That Previously Failed

 

New option allows ORAchk/EXAchk to only run checks that failed previously:

 

    Generate a health check report

    Fix the issues identified

    Generate another health check report verifying only the issues that failed before

 

Use the failed checks option by passing in the html report, zip or directory

-failedchecks previous_result

 

Windows Support Without Requiring Cygwin

 

As ORAchk has now been rewritten in Python, it no longer has the need for Cygwin on Windows.

ORAchk just needs Python 2.7 software and libraries. The runtime will provide details of exactly what libraries it needs.

 

Run Multiple Instances at the Same Time

 

It is now possible to run multiple instances of ORAchk/EXAchk at the same time on the same machine.

 

As a result of this change the location of the root_orachk.sh and root_exachk.sh have now changed and are by default created in the user's home directory.

 

If you run as non-root, this location change will need to updated in the /etc/sudoers file, for example:

 

Previous /etc/sudoers entry:

oracle ALL=(root) NOPASSWD:/home/oracle/.orachk/root_orachk.sh

 

New /etc/sudoers entry:

oracle ALL=(root) NOPASSWD:/home/oracle/root_orachk.sh

 

The location of this script can be changed by setting the environment variable RAT_ROOT_SH_DIR

export RAT_ROOT_SH_DIR=/some/other/location

 

ORAchk support for Grid Infrastructure with no Database

 

ORAchk now provides support for Grid Infrastructure stand-alone checks, where no database is installed.

 

To run grid infrastructure checks in an environment with no rdbms installed use the option:

-nordbms

 

New Oracle Stack Health Checks

 

As always this release of ORAchk and EXAchk contain many new Oracle Stack Health Checks.

 

For full details of the new checks see:

 

   ORAchk Health Check Catalog

   EXAchk Health Check Catalog

 

The Health Check Catalog is also contained within the download.

Welcome to the My Oracle Support Community! We highly encourage you to personalize your community display name to make your activity more memorable. Please see https://community.oracle.com/docs/DOC-1022508 for instructions.