Purging a SQL Cursor in 10g

Hello all,
Having issues to purge SQL Cursor in a 10g database?

DBMS_SHARED_POOL.PURGE is available from 10.2.0.4 on, but according to “DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (Doc ID 751876.1)”, it is event-protected.
This means you need to set event 5614566 to be able to use the purge.

Example:

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         1

sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.


sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         1

sys@PRODB10> alter session set events '5614566 trace name context forever';

Session altered.

sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         0
Advertisements

Install Oracle Client 12c on Windows 10 – INS-20802: Oracle Net Configuration Assistant failed

Hello all!
Some days ago a client reach me because he was facing this error when installing Oracle Client 12.1.0.2.0 on Windows 10:

oracle12Error

Researching on topic found this.

Seems Oracle client 12.1.0.2 requires MSVC 2010 redistributable to proceed Oracle Net Configuration step.
After this requested client to install “Microsoft Visual C++ 2010 Redistributable Package” on server. And issue solved! 🙂

The download of Package can be performed from here: https://www.microsoft.com/en-gb/download/details.aspx?id=5555

Hope it helps you!
See you next week!

ORA-00932 to Export Statistics After 12c Upgrade – Undocumented

Hello All,
I have a client that use to Export and Import Dictionary Statistics using a stats table. However, after upgrading database to 12c, is started to raise error below to export data to a stats table (created in 11g) or even to update stats in this table.

SQL> exec dbms_stats.upgrade_stat_table('GREPORA','MYSTATSTABLE');
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at line 28

SQL> EXEC DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' );
BEGIN DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' ); END;

*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 37085
ORA-06512: at line 1

I reviewed several references (all very interesting, by the way, recommend you to take a look):
– ORA-20002 on Importing Statistics using DBMS_STATS Procedures (Doc ID 740345.1)
– Datapump Export Fails With Ora-00932 (Doc ID 1300803.1)
– Error “ORA-00932 – Inconsistent Datatypes: Expected BINARY Got NUMBER” When Executing the Primavera Gather-Statistics Scripts on an Oracle 12c Database (Doc ID 2111114.1)
https://oracle-base.com/articles/12c/concurrent-statistics-collection-12cr1
– http://www.morganslibrary.org/reference/pkgs/dbms_stats.html
– How To Export and Import Dictionary Statistics (Doc ID 1450820.1)
– Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (Doc ID 798257.1)

But no reference found to this specific issue, not even in MOS.

After some tests, I realized that maybe columns order in stats table can be changed in implementation between 11g and 12c. Bingo! The same columns and datatypes are in place, but in different order.

This is not documented in MOS neither on internet/independent blogs, but seems EXPORT% on DBMS_STATS presume stats table in it’s internal code without specified column orders.
As we know, a good development practice is to specify column names when performing any operation. Bad on you, Oracle!

Continue reading

Long running jobs AQ$_PLSQL_NTFN_%

Hello all!

Some time ago I had an issue with some jobs running for 64 days. Cool han?
All jobs names were AQ$_PLSQL_NTFN_%. All activity of jobs were related to SQLID f7zggdz9p7bhk in wait event “Streams AQ: waiting for messages in the queue”.

# SQLID f7zggdz9p7bhk:
begin SYS.SCHEDULER$_JOB_EVENT_HANDLER(context => :1,reginfo => sys.aq$_reg_info(:2, :3, :4, :5, :6, :7),descr => sys.aq$_descriptor(:8, :9, :10, sys.msg_prop_t(:11, :12, :13, :14, :15, :16, :17, :18, sys.aq$_agent(:19, :20, :21), :22, :23), sys.aq$_ntfn_descriptor(:24), :25, :26), payload => :27, payloadl => :28); end;

Reviewing on MOS, found a match to Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8).

The root cause is procedure SYS.SCHEDULER$_JOB_EVENT_HANDLER keep waiting AQ PL/SQL Notification callbacks associated with scheduler job email notifications for messages in “SYS”.”SCHEDULER$_EVENT_QUEUE” which no longer exist.

From MOS, this can be easily solved with fix for Bug 16623661 – No email notification is sent by scheduler, wich is included in 12.1.0.1 (base) and affect versions 11.2.0.2 and later.
Also from MOS, and which I did, is drop these jobs. The problem is these jobs just restart and have the same problem again after automated recreation.

So, apply the fix or upgrade your database. 😉

Some related references:
Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8)
AQ$_PLSQL_NTFN Scheduler Jobs Executed in large numbers affecting Database performance (Doc ID 2001165.1)
Bug 21665897: FIX FOR BUG 14712567 CAUSES LARGE NUMBERS OF AQ$_PLSQL_NTFN_% TO BE SPAWNED

Hope it helps!
Cheers!

Logs Exploding: ORA-00904: “UTL_RAW”.”CAST_FROM_NUMBER”: invalid identifier

Hello all!
Last week I suddenly had several database connections being denied due “ORA-09925: Unable to create audit trail file”. This just a few hours a maintenance in this Database.

When investigating, realized there was TO MUCH traces like “$DBNAME_q00%.trc using several GBs.
As you know, those traces are from QMON (Queue Monitor) Background Process, usually related to Oracle Streams Advanced Queueing.
Oh! Also noticed that database was started with srvctl, as per usually recommended.

After some research I found MOS Note: ORA-00904 Reported by QMON When Starting Database With SRVCTL (Doc ID 1950142.1).
This situation seems to be a match to bug Bug 18680601/19995869. Regarding Doc:
– As solution, Oracle recommends apply merge of patches 19995869 and patch 18628216.
– As immediate action, the workaround is shutdown database and start with sqlplus instead of srvctl.

Check below one of my trace files to match to your situation.

Hope it helps.

Cheers!

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11.2.0.4
System name:    Linux
Node name:      racgrepora1
Release:        2.6.32-642.15.1.el6.x86_64
Version:        #1 SMP Mon Feb 20 02:26:38 EST 2017
Machine:        x86_64
Instance name: GREPORADB
Redo thread mounted by this instance: 1
Oracle process number: 77
Unix process pid: 36422, image: oracle@racgrepora1 (Q001)


*** 2017-08-01 05:05:51.352
*** SESSION ID:(646.64779) 2017-08-01 05:05:51.352
*** CLIENT ID:() 2017-08-01 05:05:51.352
*** SERVICE NAME:(SYS$BACKGROUND) 2017-08-01 05:05:51.352
*** MODULE NAME:(Streams) 2017-08-01 05:05:51.352
*** ACTION NAME:(QMON Slave) 2017-08-01 05:05:51.352

KSV 12801 error in slave process

*** 2017-08-01 05:05:51.352
ORA-12801: error signaled in parallel query server PZ99, instance racwt22:JTS012 (2)
ORA-00904: "UTL_RAW"."CAST_FROM_NUMBER": invalid identifier
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-12801: error signaled in parallel query server PZ99, instance racwt22:JTS012 (2)
ORA-00904: "UTL_RAW"."CAST_FROM_NUMBER": invalid identifier

ORA-00600: [qkswcWithQbcRefdByMain4]

Hello all,
This days I found this in a client’s 12c Database when trying to create a Materialized View:

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

A perfect match to MOS ORA-00600 [qkswcWithQbcRefdByMain4] when Create MV “WITH” clause (Doc ID 2232872.1).

The root cause is documented on BUG 22867413 – ORA-600 CALLING DBMS_ADVISOR.TUNE_MVIEW.
The given solution is to apply Patch 22867413.

After applying patch, issue solved. 🙂

Continue reading

CRS Not Starting after Removing OS User: How to Workaround and How to Solve!

Hello all!
Turns that a few days ago a client reached me because his CRSD was simply not starting. Like this:

[root@proddb proddb]$ ./crsctl start res ora.crsd -init
CRS-2672: Attempting to start 'ora.crsd' on 'proddb'
CRS-2676: Start of 'ora.crsd' on 'proddb' succeeded

[root@proddb proddb]$ ps -ef |grep crsd
root 19217 13424 0 11:53 pts/0 00:00:00 grep crsd

After some investigation, I found the following:

2017-01-24 14:00:06.859: [ CRSSEC][1690195712]{1:51052:2} Exception: OwnerEntry construction failed to retrieve user id by name with ACL string: owner:jacknobody:rwx and error: 1
2017-01-24 14:00:06.912: [ CRSSEC][1690195712]{1:51052:2} Exception: ACL entry creation failed for: owner:jacknobody:rwx

Hmmm, seems some CRS resources are owned by “Jack Nobody”… Turns that I this us was removed from OS:

[root@proddb proddb]$ cat /etc/passwd |grep jacknobody
[root@proddb proddb]$ 

What to do now?

Continue reading