Skip navigation
1 2 Previous Next

Database Support Blog

18 Posts authored by: Adriana Zagar-Oracle

 

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.

Oracle Trace File Analyzer
Version 12.2.1.3.0 Released
slap-hr
Trace File Analyzer 12.2.1.3.0 released and now available to download.
New Features in 12.2.1.3.0
slap-hr
bulletSimplified Real-Time Summary Report
bulletCloud Collection Upload to Bug or TFAWeb
bulletAutomatic Collection Upload to SRs
bulletFaster and Easier Service Request Data Collection
bulletFixed issues
Release Resources
slap-hr

 

New Feature Slide Deck (source slides)
Main TFA Customer Slide Deck (source slides)
TFA Overview White Paper

TFA Documentation

 

Simplified Real-Time Summary Report

The summary command has been simplified to provide a quick access, real-time view of all key database components.

It shows any potential problems with important elements such as:

  • Database
  • Clusterware
  • ASM
  • Patch
  • Listener
  • Network
  • OS

To run summary use: tfactl summary

 

Cloud Collection Upload to Bug or TFAWeb

Oracle Cloud environments can now use TFA to upload diagnostics directly to bugs and / or TFAWeb.

Upload diagnostics to TFAWeb and make use of the machine learning powered, Anomaly Timeline and analyzers.

 

Upload diagnostics to both TFAWeb and directly to a bug for developer analysis:

 

 

tfactl upload -bugsftp -bug <bug#> -user <UserId> <file1 file2>

Automatic Collection Upload to SRs

TFA can automatically upload diagnostics to an SR as soon as it's collected them.

 

Store the My Oracle Support credentials securely in the wallet with:  tfactl setupmos

 

Then add the -sr <sr_number> option to any diagcollect command to automatically upload the resulting collection to the Service Request. For example:

 

TFA can also upload any other file to a Service Request. Upload using the wallet, which was setup previously:
tfactl upload -wallet -sr <sr_number> <file1 file2>

 

Or upload without the wallet, which will prompt for the password:
tfactl upload -user <user_id> -sr <sr_number> <file1 file2>

 

Faster and Easier Service Request Data Collection

Oracle Trace File Analyzer includes new One Command SRDCs (Service Request Data Collections).

 

These make it fast and easy to collect exactly the right diagnostic data if you need help from Oracle Support.

 

These are the new SRDCs:

Type of ProblemSRDC Types
ORA Errors
  • ORA-01628
  • ORA-00020
  • ORA-30036
  • ORA-00060
  • ORA-01555
Database upgrade problems
  • dbpreupgrade
Database storage problems
  • asm
Excessive SYSAUX Space is Used by the Automatic Workload Repository (AWR)
  • dbawrspace
Database startup / shutdown problems
  • dbshutdown
  • dbstartup
Data Guard problems
  • dbdataguard
Enterprise Manager target discovery / add problems
  • emcliadd
  • emclusdisc
  • emdbsys
  • emgendisc
  • emprocdisc
Enterprise Manager OMS restart problems
  • emrestartoms

 

To run an SRDC collection use: tfactl diagcollect -srdc <srdc_type>

For more help run: tfactl diagcollect -srdc -h

Fixed issues

Issues fixed in this release can be seen with this Bug query


New Release Trace File Analyzer 12.2.1.1.0  Now Available

Faster & Easier SR Data Collection

Oracle Trace File Analyzer includes the new One Command SRDCs (Service Request Data Collections).

These make it fast and easy to collect exactly the right diagnostic data if you need help from Oracle Support.

The new SRDCs include:

Type of ProblemSRDC TypesCollection Scope
Database patching problems
  • dbpatchinstall
  • dbpatchconflict
Local only
Database install / upgrade problems
  • dbinstall
  • dbupgrade
Local only
Enterprise Manager tablespace usage metric problems
  • emtbsmetrics
Local only
(on EM Agent target)
Enterprise Manager general metrics page or threshold problems
  • emmetricalert
Local only
(on EM Agent target & OMS)
Enterprise Manager debug log collection
  • emdebugon
  • emdebugoff
Local only
(on EM Agent target & OMS)

Quick System & Cluster Summary

The new summary command can be used to quickly understand the viability of systems and clusters.

It shows a fast, easy to read summary of the status including any potential problems with important elements such as:

  • Clusterware
  • ASM
  • Patch
  • Listener
  • OS
  • Network

Summary is currently only supported on Linux.

Log Maintenance Made Easy

Use Oracle Trace File Analyzer to either automatically or manually purge database logs.

Automatic purging is OFF by default, except on a Domain Service Cluster (DSC).

The new managelogs command manages ADR log and trace files, including:

  • Shows disk space usage of individual diagnostic destinations
  • Purges logs and trace files based on diagnostic location and or age

Oracle Trace File Analyzer will automatically check disk usage and record snapshots for later diagnosis.

Simple IPS (Incident Packaging Service) Interface

The Oracle Database stores details of certain problems in ADR (Automatic Diagnostic Repository).

 

IPS packages these details for later diagnosis.

Oracle Trace File Analyzer can invoke IPS to query and collect these packages.

Microsoft Windows Support

This release adds support for Microsoft Windows, on all versions supported by the Database.

Easy to Use Menu Interface

As well as the command line interface and the shell interface. This release adds a menu interface for simple access to diagnostic operations.

This starts with the new menu command.

Built on the Latest, Most Secure Java release 1.8

Oracle Trace File Analyzer now uses the latest Java features; this has made JRE 1.8 a requirement.

JRE 1.8 is already shipped with the latest versions of the 12c Database and Grid Infrastructure. It is also included with the download install.

The JRE is platform specific. As Oracle Trace File Analyzer includes the JRE, the install is now platform specific.

Oracle introduced a new feature, adaptive cursor sharing, in 11g, to improve the plans that are selected for queries containing bind variables. This feature can result in more cursors for the same query containing bind variables. We'll explain why in this article. Before we get into the details, let's review a little history. Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables. However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values. In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value. Let's look at an example to see exactly how this works. Assume I have simple table emp which has 100,000 rows and has one index called emp_i1 on deptno column.

SQL> desc emp

 

Name                   Null?    Type

---------------------- -------- ----------------------------------

ENAME                           VARCHAR2(20)

EMPNO                           NUMBER

PHONE                           VARCHAR2(20)

DEPTNO                          NUMBER

There is a data skew in the deptno column, so when I gathered statistics on the emp table, Oracle automatically created a histogram on the deptno column.

SQL> select table_name, column_name, histogram from user_tab_cols;

 

TABLE_NAME         COLUMN_NAME        HISTOGRAM

------------------ ------------------ ---------------

EMP                DEPTNO             HEIGHT BALANCED

EMP                EMPNO              NONE

EMP                ENAME              NONE

EMP                PHONE              NONE

Now I will execute a simple select on my emp table, which has a single WHERE clause predicate on the deptnocolumn. The predicate contains a bind variable. We will begin by using the value 9 for this bind variable. The value 9 occurs 10 times in the table, i.e. in 0.0001% of the rows.

SQL> exec :deptno := 9

 

SQL> select /*ACS_1*/ count(*), max(empno)

2  from emp

3  where deptno = :deptno;

 

 

COUNT(*) MAX(EMPNO)

---------- ----------

10         99

Given how selective the value 9 is, we should expect to get an index range scan for this query. Lets check the execution plan.

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------

SQL_ID 272gr4hapc9w1, child number 0

------------------------------------------------------------------------

select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

 

Plan hash value: 3184478295

------------------------------------------------------------------------

| Id | Operation                    | Name  | Rows | Bytes |Cost (%CPU)|

------------------------------------------------------------------------

|  0 | SELECT STATEMENT             |       | |       |    2 (100)|

|  1 |  SORT AGGREGATE              |       | 1|    16 |           |

|  2 |   TABLE ACCESS BY INDEX ROWID| EMP   | 1|    16 |    2 (0)|

|  3 |    INDEX RANGE SCAN          | EMP_I1|     1| |    1   (0)|

------------------------------------------------------------------------

So we got the index range scan that we expected. Now let's look at the execution statistics for this statement

SQL> select child_number, executions, buffer_gets,

2 is_bind_sensitive, is_bind_aware

3  from v$sql

4  where sql_text like 'select /*ACS_1%';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE

------------ ---------- ----------- ----------------- -------------

0          1          53 Y                 N

You can see we have one child cursor that has been executed once and has a small number of buffer gets. We also see that the cursor has been marked bind sensitive. A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for. This cursor was marked bind sensitive because the histogram on the deptno column was used to compute the selectivity of the predicate "where deptno = :deptno". Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans. Now let's change the value of the bind variable to 10, which is the most popular value for the deptno column. It occurs 99900 times in the table, i.e in 99.9% of the rows.

SQL> exec :deptno := 10

 

SQL> select /*ACS_1*/ count(*), max(empno)

2  from emp

3  where deptno = :deptno;

 

COUNT(*) MAX(EMPNO)

---------- ----------

99900 100000

We expect to get the same plan as before for this execution because Oracle initially assumes it can be shared. Let's check:

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------

SQL_ID 272gr4hapc9w1, child number 0

------------------------------------------------------------------------

select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

 

Plan hash value: 3184478295

------------------------------------------------------------------------

| Id | Operation                    | Name  | Rows | Bytes |Cost (%CPU)|

------------------------------------------------------------------------

|  0 | SELECT STATEMENT             |       | |       |    2 (100)|

|  1 |  SORT AGGREGATE              |       | 1|    16 |           |

|  2 |   TABLE ACCESS BY INDEX ROWID| EMP   | 1|    16 |    2 (0)|

|  3 |    INDEX RANGE SCAN          | EMP_I1|     1| |    1   (0)|

------------------------------------------------------------------------

The plan is still an index range scan as before, but if we look at the execution statistics, we should see two executions and a big jump in the number of buffer gets from what we saw before.

SQL> select child_number, executions, buffer_gets,

2    is_bind_sensitive, is_bind_aware

3  from v$sql

4  where sql_text like 'select /*ACS_1%';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE

------------ ---------- ----------- ----------------- -------------

0          2        1007 Y                 N

You should also note that the cursor is still only marked bind sensitive and not bind aware at this point. So let's re-execute the statement using the same popular value, 10.

SQL> exec :deptno := 10

 

SQL> select /*ACS_1*/ count(*), max(empno)

2  from emp

3  where deptno = :deptno;

 

COUNT(*) MAX(EMPNO)

---------- -----------

99900 100000

Behind the scenes during the first two executions, Oracle was monitoring the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. Hence a new plan is generated based on the current bind value, 10. Let's check what the new plan is.

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

SQL_ID 272gr4hapc9w1, child number 1

--------------------------------------------------------------------

select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

 

Plan hash value: 2083865914

--------------------------------------------------------------------

| Id  | Operation            | Name  | Rows | Bytes | Cost (%CPU)|

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       | |       |   240 (100)|

|   1 |  SORT AGGREGATE      | |     1  | 16  |            |

|*  2 |   TABLE ACCESS FULL  | EMP | 95000  | 1484K |   240 (1)|

--------------------------------------------------------------------

Given how unselective the value 10 is in the table, it's not surprising that the new plan is a full table scan. Now if we display the execution statistics we should see an additional child cursor (#1) has been created. Cursor #1 should show a number of buffers gets lower than cursor #0 and it is marked both bind sensitive and bind aware. A bind aware cursor may use different plans for different bind values, depending on how selective the predicates containing the bind variable are. Looking at the execution statistics:

SQL> select child_number, executions, buffer_gets,

2    is_bind_sensitive, is_bind_aware

3  from v$sql

4  where sql_text like 'select /*ACS_1%';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE

------------ ---------- ----------- ----------------- -------------

0          2        1007 Y                 N

1          1 821 Y                 Y 

we see that there is a new cursor, which represents the plan which uses a table scan. But if we execute the query again with a more selective bind value, we should use the index plan:

SQL> exec :deptno := 9

 

SQL> select /*ACS_1*/ count(*), max(empno)

2  from emp

3  where deptno = :deptno;

 

COUNT(*) MAX(EMPNO)

---------- ----------

10         99

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------

SQL_ID 272gr4hapc9w1, child number 2

------------------------------------------------------------------------

select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

 

Plan hash value: 3184478295

------------------------------------------------------------------------

| Id | Operation                    | Name  | Rows | Bytes |Cost (%CPU)|

------------------------------------------------------------------------

|  0 | SELECT STATEMENT             |       | |       |    2 (100)|

|  1 |  SORT AGGREGATE              |       | 1|    16 |           |

|  2 |   TABLE ACCESS BY INDEX ROWID| EMP   | 1|    16 |    2 (0)|

|  3 |    INDEX RANGE SCAN          | EMP_I1|     1| |    1   (0)|

------------------------------------------------------------------------

The proper plan was chosen, based on the selectivity produced by the current bind value. There is one last interesting thing to note about this. If we look at the execution statistics again, there are three cursors now:

SQL> select child_number, executions, buffer_gets,

2    is_bind_sensitive, is_bind_aware, is_shareable

3  from v$sql

4  where sql_text like 'select /*ACS_1%';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR

------------ ---------- ----------- --------- --------- ----------

0          2         957 Y         N         N

1          1         765 Y         Y         Y

2          2           6 Y         Y Y

The original cursor was discarded when the cursor switched to bind aware mode. This is a one-time overhead. Note that the cursor is marked as not shareable (is_shareable is "N"), which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used. In other words, it is just waiting to be garbage collected. There is one other reason that you may notice additional cursors for such a query in 11g. When a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value's selectivity. If it cannot find such a cursor, it will create a new one (like above, when one (#1) was created for unselective "10" and one (#2) was created for highly-selective "9"). If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache. This will result in one being left behind that is in a not shareable state. This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions.

Q & A

Instead of answering the questions in your comments one by one, I am going to summarize the questions and provide my answers here.

Q: Is this behavior managed by 11g optimizer automatically and we don't need cursor_sharing anymore?

A: We have not changed the behavior of the cursor_sharing parameter yet, for backwards compatibility purposes. So if you set it to similar, adaptive cursor sharing will only kick in for queries where the literals are replace with binds. We hope that in the future, this feature will persuade people to set cursor_sharing to force.


Q: Would it have any impact like holding library cache latches for longer time to search for appropriate child cursor.

A: Any additional overhead in matching a cursor is always a concern, and we strive to minimize the impact. There is of course some increase in the code path to match a bind-aware cursor, since it requires more intelligent checks. This feature should not, however, impact cursors which are not yet marked bind-aware.


Q: What triggers a cursor to be marked "bind sensitive"?

A: Our goal is to consider many types of predicates where the selectivity can change when the bind value changes.
In this first version of the feature, we only handle equality predicates where a histogram exists on the column and range predicates (with or without histogram). We do not currently consider LIKE predicates, but it is on the top of our list for future work.

Q: Also it sounds like the optimizer is using the number of rows returned to decided that it's time for a new plan...

A: I am not going to go into the details of the "special sauce" for how we decide to mark a cursor bind-aware. The number of rows processed is one input.


Q: Are you planning a hint to mark statements as bind-aware ?

A: Yes, we plan to add this in the future. This will allow users to bypass the startup cost of automatically determining that a query is a good candidate for bind-aware cursor sharing.

New Release ORAchk 12.2.0.1.3 & EXAchk 12.2.0.1.3 Now Available

 

Upgrade to Database 12.2 with confidence

This release includes lots of new checks to help when upgrading the database to 12.2.

These include both pre and post upgrade verification to prevent problems related to:

  • OS configuration
  • Grid Infrastructure & Database patch prerequisites
  • Database configuration
  • Cluster configuration

See the ORAchk or EXAchk Health Check Catalog for more details of new checks.

Improve performance of SQL queries

Many new checks focus on known issues in 12c Optimizer as well as SQL Plan Management.These checks target problems such as:

  • Wrong results returned
  • High memory & CPU usage
  • Errors such as ORA-00600 or ORA-07445
  • Issues with cursor usage
  • Other general SQL plan management problems

Tailor Collection Manager email notifications & control sample data

Collection Manager email notification can now be customized based on one or more business units. Existing users which have notification already configured will receive notification for all business units by default.

It is now possible to delete or reload the sample data which Collection Manager ships with.

Prevent Clusterware related outages and corruptions

The Oracle Cluster Registry subnet records can become stale due to configuration changes. This can lead to various startup problems.

Additionally vulnerability to certain OS bugs can lead to corruption in clustered databases.

This release has checks included to verify network configuration and OS patching.

Guard against Exadata Critical Issues

New checks detect Exadata critical issues.

EXAchk also now verifies readiness to apply PSU on OJVM (Oracle Java Virtual Machine). In addition various other Exadata best practice recommendations have been added.

Find more details in the EXAchk Health Check Catalog.

Avoid a SuperCluster critical issue and storage problems

New checks are now available for a critical issue and storage problems affecting SuperCluster.

These detect vulnerability to a Critical Issue affecting SuperCluster. As well as checks to prevent storage related problems and verification of readiness to apply PSU on OJVM (Oracle Java Virtual Machine).

The Oracle GoldenGate Monitor communicates with core Oracle GoldenGate components to provide a complete, real-time view of all your real-time data integration and replication solutions with Oracle GoldenGate. Oracle GoldenGate Monitor is designed to take advantage of the low-impact monitoring agent that is included with Oracle GoldenGate. The agent collects information — such as status, lag, number of inserts, updates, and deletes— from an Oracle GoldenGate replication instance and sends those metrics to the GoldenGate Monitor Server. Using this data, Oracle GoldenGate Monitor graphically displays your end-to-end replication solutions and provides sophisticated server side alerting logic.

 

OGG Monitor provides users with the following:

  • Comprehensive set of metrics
  • Customizable views
  • Customizable alert notifications
  • Drill into process monitoring points

 

Additional Resources are available via the OGG Monitor Documentation Library.

 

An hour long advisor webcast on this topic is available via Oracle Database Advisor Webcast Schedule and Archive recordings (Doc ID 1456176.1), in the 'Archived 2017' tab.

Links to the recording, the ppt and Q&A are available.

Oracle ORAchk & EXAchk 12.2.0.1.2

New Release ORAchk 12.2.0.1.2 & EXAchk 12.2.0.1.2 Now Available

Enhanced Elastic Stack integration

The existing ORAchk & EXAchk integration with the Elastic Stack (previously called ELK stack) has been enhanced.

Many new field tags have been added to the ORAchk & EXAchk JSON output.

The extra tags allow dashboard filtering based on facts such as:

  • Engineered System type
  • Engineered System version
  • Hardware type
  • Node name
  • OS version
  • Rack identifier
  • Rack type
  • Database version
  • And more...

The Kibana dashboard can be used to view health check compliance across the data center.

Now with these new field tags results can also be filtered based on any combination of exposed system attributes.

Collection Manager health score filtering

A new filter has been added to the Collection Manager:

Health Score <= ?

This allows results to be filtered to only show collections where the health score is less than or equal to the value specified.

The default value of the filter is 100, so will show all collections initially.

A number of other existing Collection Manager field labels have been renamed for ease of use.

Oracle Platinum Services supports Oracle Auto Service Request for qualified OEM fault events

EXAchk will now check the Oracle Platinum Services monitoring agent, the monitoring user, monitored databases, and their diagnostic directories.

This helps to ensure quality of Oracle Platinum Services monitoring and that relevant diagnostics can be supplied for faster fault resolution.

Oracle Database Security Assessment Tool included

ORAchk and EXAchk now include the Oracle Database Security Assessment Tool (DBSAT).

DBSAT analyzes database configurations and security policies to uncover security risks and improve the security posture of Oracle Databases.

All DBSAT results are included within the ORAchk and EXAchk report output, under the check "Validate database security configuration using database security assessment tool".

The new Oracle Trace File Analyzer (TFA) with Database Support Tools Bundle release 12.1.2.8.4 is now available to download.

 

New Features in TFA 12.1.2.8.4:

Install and Run TFA without Root Privileges

Where possible you should install TFA as root. However if you are unable to install as root then TFA can now be installed as the ORACLE_HOME owner.

When installed as non root user TFA will not run a daemon (install as root to run the daemon) and as such will not perform automatic collections or collect from remote hosts. On-demand collections will work, but you will be limited to only collecting local files you have access to read.

To install without a daemon, tell TFA where to install using the -extractto option and where to find a JRE using the -javahome option. Use the JRE already available in the RDBMS home unless you have a later version available.

installTFALite -extractto <install_dir> -javahome <jre_home>

SR Data Collection is 10X Easier

When you need help from Oracle Support for certain types of Database problems they'll ask you to run a Service Request Data Collection (SRDC). Previously this would have involved reading many different support documents then collecting output from many different tasks and gathering lots of different diagnostics.

TFA can now run SRDC collections for you with a single command:

tfactl diagcollect -srdc <srdc_type>

For example the following will capture and zip everything needed to diagnose and resolve an ORA-00600:

tfactl diagcollect -srdc ora600

The files and diagnostics collected will depend on the problem the SRDC collection is about. TFA will prompt you for any other important information it needs depending on the SRDC type.

TFA currently has 10 different SRDC types with more on the way:

 

SRDCs for ORA errors:

  • ora600
  • ora7445
  • ora700
  • ora4031
  • ora4030
  • ora27300
  • ora27301
  • ora27302

 

SRDC for database performance problems:

  • dbperf

 

SRDC for all other types of internal database errors:

  • internalerror

 

For more information about SRDCs run tfactl diagcollect -srdc -help

Bash No Longer Required

TFA no longer has a requirement for bash. TFA will use a different mechanism for discovery if bash is unavailable.

If you do not use bash and your configuration does not follow ADR standards (i.e. diag directory is not in ORACLE_BASE) it will not be automatically discovered.

However any directories that are missed by the discovery can be easily added using the directory add command:

tfactl directory add <dir_path>

If bash is available, TFA will use it for discovery no matter where the diag directory is located.

For more details on TFA and to download see Document 1513912.1

Oracle Support has historically recommended that customers use Patch Set Update (PSU) as the default method for database patch delivery. Moving forward, customers and engineers should consult a new matrix that targets patch delivery recommendations on the basis of both database version and platform.

 

CALL TO ACTION:

•    Reference the Oracle Database - Overview of Database Patch Delivery Methods (Doc ID 1962125.1), Section, "Which Patching Method to Use?"

The new release: ORAchk release 12.2.0.1.1 is now available to download.

New Features in ORAchk 12.2.0.1.1:

Easier to Use & Upgrade Oracle Health Checks Collection Manager

The Oracle Health Checks Collection Manager has a new easier to navigate interface, based on the Oracle Application Express (APEX) 5 theme. This new interface is only available if you have APEX 5 installed on your database.

ORAchk continues to include the APEX 4 based Collection Manager app, however all new functionality in new releases will only go into the APEX 5 Collection Manager app.

The Collection Manager can now be upgraded from the ORAchk tool. It will be upgraded to the latest version of whichever app your database can support. If you have APEX 5, you will get the new theme interface.

./orachk -cmupgrade

More Flexibility for Running Root Privileged Checks

A large number of the Oracle Stack Health Checks don't require root access, however a subset need to run with root privileges.

The script used to execute these root privilege checks is root_orachk.sh This root script will be created in the temporary directory used by ORAchk, which is $HOME by default and can be changed by setting exporting RAT_TMPDIR.

There may be times when you want to place the root script in a different location from the temporary directory, for either security or convenience reasons.

You can now choose to have the root script created outside of the standard temporary directory, in a custom directory you set via the RAT_ROOT_SH_DIR environment variable:

export RAT_ROOT_SH_DIR=/orahome/oradb/

You can use a specific location for sudo remote access, like this:

export RAT_ROOT_SH_DIR=/mylocation

Then the entry in the /etc/sudoers:

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

Support for AIX 7.2

With this release, ORAchk adds support for the latest version of IBM AIX 7.2 (in addition to existing support for versions 5.2, 5.3, 6.1 and 7.1)

Support for New Oracle Database Appliances

ORAchk already has support for the existing Oracle Database Appliance HA; this release adds support for new Oracle Database Appliances Small and Medium.

New Checks

New Health Checks have been added across the Oracle stack, including checks for Database & RAC for both existing versions and 12.2 upgrade readiness.

To see new checks for specific systems see the ORAchk Health Check catalog.

For more details and to download the latest release of ORAchk see Document 1268927.2

Adriana Zagar-Oracle

EM Express in 12c

Posted by Adriana Zagar-Oracle Jun 24, 2016

Introduction

 

You might be familiar with the DB Control, which was available with 10g and 11g databases. In 12c, this is replaced by a light weight tool called Enterprise Manager Database Express (EM Express). EM Express is the web-based interface for managing the 12c database, including the multitenant databases. It can handle the common core of tasks done by DBAs such as managing users, managing database initialization parameters, memory or storage, checking performance and SQL Tuning Advisor information etc.

The main advantage of EM Express is that it does not have its own repository or background tasks or processes that periodically collect information, thereby reducing the load on the database server. At the same time, it can handle the basic administrative tasks performed by DBAs. The remaining functions done by the DB Control in11g can still be done by using the Enterprise Manager DB Plug-in deployed from Enterprise Manager Cloud Control 12c.

How to configure EM Express

 

How does EM Express handle these tasks if there is no repository or background processes? It is a browser-based UI that does its job by sending requests to the Database Listener, which invokes a servlet which runs SQL and returns HTML to the browser. Hence, the configuration is also simple, requiring only a port to be enabled in the DB listener. There is no separate installation required for EM Express.


1. Ensure the listener is up and running


$ lsnrctl status

 

2. If the listener is not running on the default port 1521, ensure to set the local_listener parameter.

 

ALTER SYSTEM SET LOCAL_LISTENER=listener1;

 

Resolve the listener name alias for the LOCAL_LISTENER setting through a tnsnames.ora file on the database host using a text editor, as follows:

 

listener1=

(DESCRIPTION =

(ADDRESS = (PROTOCOL=tcp)(HOST=db-server)(PORT=1525)))


3. Enable the TCP dispatcher


dispatchers="(PROTOCOL=TCP)(SERVICE=<sid>XDB)"


 

4. Ensure the ORCL12cXDB service is registered in the listener.

 

5. Now, we can configure the port in the database. To access EM Express, the database should be open. If the database is created by DBCA, the port may be configured already. We can verify this using dbms_xdb.getHttpPort() , for HTTP port or dbms_xdb.getHttpsPort(), for HTTPS port.

 

For example:

disp.png

 

If its not configured or if you want to change it, you can do so. The port can be set using dbms_xdb.setHttpPort()for HTTP port or dbms_xdb.setHttpsPort() for HTTPS port.

 

Please note we can use either HTTP port or HTTPS port to connect to the database. Both need not be configured.

disp.png

 

6. To access EM Express, enter a URL in the following format in a Web browser, specifying the EM Express port number:

 

https://database-hostname:portnumber/em/

 

Use the following query while connected to the database to generate the URL :

 

For HTTPS :

 

(a) If DB_DOMAIN is configured

SELECT 'https://'||SYS_CONTEXT('USERENV','SERVER_HOST')||'.'||SYS_CONTEXT('USERENV','DB_DOMAIN')||':'||dbms_xdb_config.gethttpsport()||'/em/' from dual;

 

(b) If DB_DOMAIN is not configured

 

SELECT 'https://'||SYS_CONTEXT('USERENV','SERVER_HOST')||':'||dbms_xdb_config.gethttpsport()||'/em/' from dual;

 

For HTTP :

 

(a) If DB_DOMAIN is configured

 

SELECT 'http://'||SYS_CONTEXT('USERENV','SERVER_HOST')||'.'||SYS_CONTEXT('USERENV','DB_DOMAIN')||':'||dbms_xdb_config.gethttpport()||'/em/' from dual;

 

(b) If DB_DOMAIN is not configured

 

SELECT 'http://'||SYS_CONTEXT('USERENV','SERVER_HOST')||':'||dbms_xdb_config.gethttpport()||'/em/' from dual;

 

disp.png

 

Use this URL in any browser to access the EM Express

 

7. The privileges required to access EM Express:

 

By default SYS and SYSTEM users have the required privileges to access EM Express. For other users, you can grant:

 

EM_EXPRESS_BASIC: enables users to connect to EM Express and to view the pages in read-only mode.

EM_EXPRESS_ALL    : enables users to connect to EM Express and use all the functionality provided by EM Express (read/write access to all EM Express features).


Note: EM Express can be configured in RAC , CDB or PDB databases.

 

Note: You can configure EM Express in the CDB level and even for separate PDBs.

What are the functionalities available with EM Express

 

Dashboard

 

Dashboard gives an overview on what is going on in the database.

It contains various sections such as the Status, Incidents reported in last 24 hours, Jobs currently running in the database, Performance, Resource Usage, SQL Monitor information. Option to further drill down is limited.


  • Status: This section contains and overview of the main characteristics of the database, such as Version, Database Name, Oracle Home, Last Backup, and so on.
  • Performance: In this pane, you find a simplified breakdown of resource consumption.
  • Incidents: This section lists the problems have been detected and stored in the Automatic Diagnostic Repository.
  • Resources: This pane gives you an overview of the database/server resource consumption in terms of CPU, memory, or storage.
  • Running Jobs:  This section lists the Database Scheduler Jobs currently being executed.
  • SQL Monitor: This pane shows the SQL Monitor information. The statement IDs are active links that you can click to access the SQL Monitor tool.

 

The other main tabs include Configuration, Storage, Security and Performance.

disp.png

 

Configuration tab contains sections such as Initialization parameters, Memory, Database Feature Usage and the Current database properties.

disp.png

 

Storage tab contains drop down to access details of the tablespaces, Undo & Redo management and Control file management.


disp.png

Details on Users, roles and profiles are present under the Security tab.

disp.png

 

The Diagnostic Pack and Tuning Pack licenses required for accessing Performance hub and SQL Tuning Advisor respectively, are found under the Performance tab.

disp.png

 

.

Conclusion

 

EM Express is not a replacement of DB Console. It is a light weight yet powerful GUI tool which helps the Database Administrators in the basic day to day activities. For further options, you will have to use the Cloud control portal

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.