Month: March 2017

Database, Errors and Bugs, Migration | Upgrades | Patching, Performance

Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade

 , , , , ,

Hello all!

As DBAs we are always being recommended by Oracle and also recommending to clients to update their databases, but we have to be aware about new features and their effects. This is the case of Adaptive Query Optimization and in this particular case on SQL Plan Directives.

SQL Plan Directives are one of the functionalities that compose the Adaptive Query Optimization in Oracle Database 12c. The basic idea is pretty interesting: The SQL Optimizer keeps reviewing bad (“suboptimal”, as they like to say) plans, tipically incorrect cardinality estimations and generates SQL plan directives, like for missing histograms or extended statistics.

In my case, just after the upgrade to 12c (made on Jan 27th), the CPU usage increased for the same report always ran in the database:

12c_upgrade

Ok, how to check it?

More“Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade”

Database, Enterprise Manager, SQL | PLSQL

EM Event: Number of failed login attempts exceeds threshold value [Part 2]

Hello,
As a second part of last week’s post, here is a way to accomplish that without using audit. Using a simple logon trigger! šŸ™‚
Take a look:

First step: Create a table to store the data.

CREATE TABLE SYS.TRG_LOGON (SCHEMA VARCHAR2(50), SERVER VARCHAR2(200), FAILTIME DATE, ERROR VARCHAR2(200));

More“EM Event: Number of failed login attempts exceeds threshold value [Part 2]”

Database, Errors and Bugs, SQL | PLSQL

User EXPIRED(GRACE) – How to Never Expire!

 , ,

Ok, this is a common issue, once it’s like the default behavior of oracle for DEFAULTĀ profile, used as DEFAULT to new users. But it’s really easy to solve. šŸ™‚

Before expiring you should be received some:

ORA-28002: the password will expire within x days

Ok then, but what if you are facing errors to reuse the same password, don’t know the password and yet, how to never let it happen again?

More“User EXPIRED(GRACE) – How to Never Expire!”

Errors and Bugs, GoldenGate, RAC | Data Guard

GOLDENGATE process show RUNNING status, when not RUNNING.

This might occur in case of crash onĀ host or database node whereĀ goldengate is running.

Goldengate maintains information about the process status in the file system, when the server crashes the file that has PID data is not released, and the process status in GGSCI shows erroneous information.

In GGSCI, process status it’s OK:

GGSCI (greporaLAB.net) 7> info mgr

Manager is running (IP port greporaLAB.net.7809, Process ID 42064).

If you try to stop process, you will not be able to:

More“GOLDENGATE process show RUNNING status, when not RUNNING.”

Database, Errors and Bugs, GoldenGate

OGG-02077 Extract encountered a read error in the asynchronous reader thread and is abending: Error code 1683

Have this crazy in ggserr.log?

Source Context :
SourceModule : [er.redo.ora]
SourceID : [/scratch/aime/adestore/views/aime_stuya22/oggcore/OpenSys/src/app/er/redo/oracle/redoora.c]
SourceFunction : [REDOORA_handle_read_error(errmsg_t &)]
SourceLine : [11630]

ERROR OGG-02077 Extract encountered a read error in the asynchronous reader thread and is abending: Error code 1683, error message: ORA-01683: unable to extend index . partition by in tablespace.

Check db alert_*.log, you should find some alert.

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1470969483_24757 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1470969483_24757 by 128 in tablespace SYSAUX

Resolving issue:

alter tablespace SYSAUX add datafile '+DG' size 1g;

Some options are:
Reduce AWR retention:

SQL> SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
FROM dba_hist_wr_control;
SQL> exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 10800, interval => 10);

Reduce Statistics Level:

SQL> show parameter STATISTICS_LEVEL
NAME                 TYPE        VALUE
---------------------------------------------------------
statistics_level     string     ALL

SQL> alter system set STATISTICS_LEVEL = TYPICAL scope=both sid='*';

Type your message when beep šŸ™‚

Database, Enterprise Manager, Errors and Bugs

EM Repository: ORA-00060: Deadlock detected error in alert log

 , , , , ,

Hello!
Some time ago I found some ORA-00060: Deadlock detected. errors in a client OEM databaseā€¦ Like this:

Thu Dec 22 09:01:55 2016
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1757.trc.
Thu Dec 22 09:02:07 2016
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1759.trc.
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1759.trc.

In summary, after investigating the trace (as per below), found that the issueis caused by the following command:

More“EM Repository: ORA-00060: Deadlock detected error in alert log”