ORA-24093: AQ agent SCHED$_% not granted privileges of database user %

These days, when trying to add dbms_scheduler notifications for different user I was getting this strange error:

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'APPOWNER.MYJOB',
  recipients =>  'me@company.com',
  events     =>  'job_started, job_succeeded'
  );
END;
/

ORA-24093: AQ agent SCHED$_XXX not granted privileges of database user APPUSER
ORA-06512: at "SYS.DBMS_ISCHED", line 8296
ORA-06512: at "SYS.DBMS_SCHEDULER", line 4353
ORA-06512: at line 3

The issue is that “secure queue access must be granted to an Oracle Database Advanced Queuing (AQ) agent explicitly for both enqueue and dequeue operations. You grant the agent these privileges using the ENABLE_DB_ACCESS procedure in the DBMS_AQADM package” (http://docs.oracle.com/database/121/STRMS/strms_trprop.htm#STRMS1046)

So, problem solved with this:

BEGIN
  DBMS_AQADM.ENABLE_DB_ACCESS(
    agent_name  => 'SCHED$_XXX',
    db_username => 'APPUSER');
END;
/

See you next week!

Online Data Patch Apply with multiple Databases on same Oracle Home: OPatch failed with error code 26

Hi all,
Tricky question, right? It’s easier than you think…
Actually, we don’t commonly think on those situations in first place, but it’s pretty common, specialy if considering server consolidation situations.

The trick is to use clause util enableOnlinePatch insted of apply after first database applying.
In this example I’m applying on-off patch 14084247 in online mode. Check:

# First Database:

[oracle@PRODSERVER 14084247]$ opatch apply online -connectString ORA11:sys::
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/app/product/11.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /opt/oracle/app/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/14084247_Apr_03_2017_14_17_24/apply2017-04-03_14-17-24PM_1.log


The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
Applying interim patch '14084247' to OH '/opt/oracle/app/product/11.2.0/db_1'
Verifying environment and performing prerequisite checks...
All checks passed.
Backing up files...

Patching component oracle.rdbms, 11.2.0.4.0...
Installing and enabling the online patch 'bug14084247.pch', on database 'ORA11'.


Verifying the update...
Patch 14084247 successfully applied
Log file location: /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/14084247_Apr_03_2017_14_17_24/apply2017-04-03_14-17-24PM_1.log

OPatch succeeded.

All good, right?
Let’s see applying to second database with same command:

Second Database:

[oracle@PRODSERVER 14084247]$ opatch apply online -connectString OTHERORA11:sys::
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/app/product/11.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /opt/oracle/app/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/14084247_Apr_03_2017_14_17_45/apply2017-04-03_14-17-45PM_1.log


The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
Applying interim patch '14084247' to OH '/opt/oracle/app/product/11.2.0/db_1'
Verifying environment and performing prerequisite checks...
Log file location: /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/14084247_Apr_03_2017_14_17_45/apply2017-04-03_14-17-45PM_1.log

Recommended actions: Please use 'opatch util applySql' for sql related patches or 'opatch util enableOnlinePatch' for online patches to add sids to already installed patch(es).

OPatch failed with error code 26

Beeep!
So, simply use clause util enableonlinepatch as per below.

Second Database (right way):

[oracle@PRODSERVER 14084247]$ opatch util enableonlinepatch -connectString OTHERORA11:sys:: -id 14084247
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /opt/oracle/app/product/11.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/app/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2017-04-03_14-20-53PM_1.log

Invoking utility "enableonlinepatch"
Installing and enabling the online patch 'bug14084247.pch', on database 'OTHERORA11'.

OPatch succeeded.
[oracle@PRODSERVER 14084247]$

Ok, that’s it for today.

See you next week!

ORA-00001: unique constraint (RMAN.CKP_U1) violated

Hey,
Don’t create so much expectations on this post.

This is because I don’t exactly fixed the issue, but workarounded…
The thing is: This error is caused in catalog database, so the workaround is simple: do a RMAN-nocatalog, I mean, simply don’t connect in catalog to perform the backup.

After completing the backup, I’d suggest you to force a synchronization with command “RESYNC CATALOG“. In worst case, on next execution the implicit resync will fix everything. 🙂

There is no bigger explanations on this, but you can same workaround in MOS Bug 12588237 – RMAN-3002 ORA-1: unique constraint (ckp_u1) violated after dataguard switchover (Doc ID 12588237.8).

And this is it for today!
See you next week!

Exadata: ORA-07445: exception encountered: core dump [ocl_lock_get_waitobj_owner()+26] [11] [0x000000000] [] [] []

Hello all,

This is because the error is generated by an unpublished bug 17891564, as per described in MOS ORA-7445 [ocl_lock_get_waitobj_owner] on an Exadata storage cell (Doc ID 1906366.1).

It affects Exadata storage cell with image version between 11.2.1.2.0 and 11.2.3.3.0. The CELLSRV process crash with this error as per:

Cellsrv encountered a fatal signal 11
Errors in file /opt/oracle/cell11.2.3.3.0_LINUX.X64_131014.1/log/diag/asm/cell//trace/svtrc_11711_27.trc  (incident=257):
ORA-07445: exception encountered: core dump [ocl_lock_get_waitobj_owner()+26] [11] [0x000000000] [] [] []
Incident details in: /opt/oracle/cell11.2.3.3.0_LINUX.X64_131014.1/log/diag/asm/cell//incident/incdir_257/svtrc_11711_27_i257.trc

The CELLSRV process should auto restart after this error.

Continue reading