Saturday, December 31, 2016

OEM 13c New Features - System Broadcast

I can’t finish the year without a post about OEM 13c. I finally had some time to install it and from first hand go through its new features.  I find this “System Broadcast” feature so useful, especially when you have tons of users using OEM.

You can try to email users, give them a call but they will always forget about the next maintenance window and call you right away when OEM is not available. This feature helps you to send a notification to all the users or a particular list of users.

System Broadcast messages (up to 200 characters) can only be sent using EMCLI, there’s no graphical option yet.

Here’s an example on how to send a message to a user named OEMADMIN:

$ emcli send_system_broadcast -toOption="SPECIFIC" -to="OEMADMIN" -message="OEM will be down for maintenance Friday December 30th"
Successfully requested to send System Broadcast to users.

This is what you see once you login to OEM console:

Send the message to all the users:

$ emcli send_system_broadcast -toOption="ALL" -message="OEM will be down for maintenance Friday December 30th"
Successfully requested to send System Broadcast to users.

You need to be logged in EMCLI to be able to send these messages, if you are not you’ll get this error:

$ emcli send_system_broadcast -toOption="ALL" -message="OEM will be down for maintenance Friday December 30th"
Status:Unauthorized 401

As I said, you need to be logged in EMCLI:

$emcli login -username=sysman
Enter password:

Login successful

$ emcli send_system_broadcast -toOption="ALL" -message="OEM will be down for maintenance Friday December 30th"
Successfully requested to send System Broadcast to users.

Here’s the verb syntax:

emcli send_system_broadcast
      [-to="comma separated user names"]
      [-messageType="INFO|CONF|WARN|ERROR|FATAL" (default is INFO)]
      -message="message details"

[ ]  indicates that the parameter is optional.

Thank you and happy new year!

Sunday, December 18, 2016

ORA-01555 query duration 0 seconds with Dataguard

How many times we have calls from users complaining about their process that failed due to an ORA-01555 error?

We know that if the queries are not well tuned and they modify a lot of data, the image held in the UNDO Tablespace could not be consistent with the real data. But have you ever seen this error right away after executing a SQL statement against a table?

I just did couple of days ago. Here’s the story:

ORA-01555 error appeared in the alertlog’s database with a query duration of 0 seconds.

ORA-01555 caused by SQL statement below (SQL ID: d3rt4tyudufeu, Query Duration=0 sec, SCN: 0x034f.34f660b4)

Any queries plus an analyze table failed right away with ORA-01555:

ERROR at line 1: ORA-0155: snapshot too old: rollback segment number 10 with name "SYSSMU11_1072300523734$" too small

So weird.
After researching a bit on MOS, found a note regarding a bug.

Some minutes later we also started to receive ORA-600 errors related so scn numbers.

ORA-error stack (00600[ktbdchk1: bad dscn])

The MOS note mentions the ORA-01555 and the ORA-600 errors as part of bug 22241601 with a Dataguard configuration. Is worth to mention that yes, we were doing switchover testing recently in this environment.

ALERT Bug 22241601 ORA-600 [kdsgrp1] / ORA-1555 / ORA-600 [ktbdchk1: bad dscn] / ORA-600 [2663] due to Invalid Commit SCN in INDEX (Doc ID 1608167.1)

The solution is to apply the patch but there’s also a tested workaround that is to rebuild online all the indexes of that table.

Hope this helps.


Monday, November 21, 2016

CRS-2632: There are no more servers to try to place resource ‘resource_name’ on that would satisfy its placement policy

I just got this error from CRS while trying to start a database using srvctl. I find this error kind of missleading as the error doesn’t tell what is really happening with the resource.

I suggest you to manually check the logs of the resource. In this case it had to do with a cluster database that one of the instances was not able to start. I tried to start this instance manually and then I got the real error.

The instance was having a missconfiguration in the parameter file that prevented it from starting.

Hope this note will provide you a guidance while having this CRS-2632 error coming out from CRS.



Wednesday, September 21, 2016

Tim Gorman is our guest speaker for September 30th NEOOUG's quarterly meeting.

Tim currently works as a Technical Consultant at Delphix. Oracle ACE Director, Oak Table member and an advocate of the North East Ohio Oracle Users Group for many years. This time Tim brings two interesting sessions, “Split Brain Syndrome” and “Scaling To Infinity”.

Join us for these sessions, have lunch and learn from one of the most remarkable leader in technology.

Keep in mind that this time this quarterly meeting is to be held at the new Oracle's facility in Beachwood, OH. We also require you to pre-register to this event using below URL:

More information here:


Friday, September 16, 2016

WAIT_FOR_GAP. How to restore missing archivelogs from backup?

In a Dataguard configuration, Oracle’s RFS (Remote File Server) writes redo data to the standby. When for any reason it can’t write this data, MRP (Managed Recovery Process) will wait for the archivelog to be applied and have the status “WAIT_FOR_LOG”. This will lead the standby to be out-of-sync with the primary database.

Sometimes some archivelogs can’t be transferred from primary database to the standby leaving a gap in the archivelog sequence. The MRP process will have the status “WAIT_FOR_GAP”.  

In order to fix the archivelog gap we have to manually transfer the archivelogs missing.

To find the gap you can query v$archive_gap (gv$archive_gap for RAC).


------------- --------------- ------------------------ ------------------------
2             2             823                  811

You can see that we are missing archivelogs from sequence 811 to 823 for thread 2. If these archivelogs are not available in the primary we have to restore them from backup.


Keep in mind that parameter THREAD defaults to 1, so you must specify the thread number when you are trying to restore from a different thread.

After restoring these archivelogs the RFS process should transfer them automatically to the standby. Verify if the gap is fixed.



Thursday, May 26, 2016

OEM Agent Plug-in Testing

Today I want to write about Oracle Enterprise Manager (OEM) 12c plug-ins and how to test them. 

OEM is divided into two logical parts, the EM Platform and the EM Plug-ins. The EM Platform basically consists of the UI console, job system, metrics, EM agent, etc. The EM Plug-ins are modules that you can attach to you EM Platform to extend the monitoring functionality. As a result, we have plug-ins for Oracle Databases, Fusion Middleware, even hardware or 3rd-party vendors like MS SQL Server.

The plug-ins have a set of Perl scripts that the agent uses to compute status and metrics for the targets. You can find these scripts under “<AGENT_HOME>/plugins/<plug-in_name>/scripts”.
Let us see an example:

Fusion Middleware plug-in version
$ cd  <AGENT_HOME>/plugins/oracle.sysman.emas.agent.plugin_12.

In there you can see tons of Perl scripts. These scripts are being used by the agent to compute response and metrics for the targets registered in the agent.

Now let’s say you have a Web Cache target that shows an incorrect status in OEM and you want to know how the agent computes the response status of this target.

 $ ls webcache*.pl

You can see that there’s a response script available. 
Now the question is, how to test it?

Based on the MOS id 1534087.1, you can create an script to load the required variables to your shell. I did create the file and source it:

$ .

Now I’m able to test the

$ perl

Looks like having 1 as the result means that the Web Cache is up per the script’s logic. This result is passed to the agent and finally to the OMS.

Hope this is helpful for you while trying to investigate status related issues with your targets.



Thursday, April 14, 2016

How to upgrade the RMAN recovery catalog to 12c?

Some days back I faced an issue while trying to upgrade the RMAN recovery catalog to support 12c databases.

RMAN> upgrade catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-07539: insufficient privileges to create or upgrade the catalog schema

The problem is that for 12c the catalog owner requires additional privileges. The solution is to run the dbmsrmansys.sql script that comes with the 12c binaries.

You have to copy this file from your 12c home “$ORACLE_HOME/rdbms/admin” directory to where your RMAN recovery catalog database is and execute it.

SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql

The script is going to complain for the lack of 2 other scripts, but the upgrade runs just fine.

$ rman CATALOG rman@catalog
recovery catalog database Password:



Sunday, April 10, 2016

ALTER SYSTEM in a RAC environment

Few weeks ago I had to modify an instance parameter to a different value. In this case, I just wanted to modify it for only one instance and leave the other instance as it was.

I thought that if I didn’t specify the instance name using the SID clause, the default is going to be just that instance. I was really wrong about that and the command ended modifying both instances.

After looking at Oracle’s documentation here’s what I’ve found.

SID Clause

·         Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.
·         Specify SID = 'sid' if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = '*'.

If you do not specify this clause:
·         If the instance was started up with a pfile (client-side initialization parameter file), then Oracle Database assumes the SID of the current instance.
·         If the instance was started up with an spfile (server parameter file), then Oracle Database assumes SID = '*'.

If you specify an instance other than the current instance, then Oracle Database sends a message to that instance to change the parameter value in the memory of that instance.

So now I know that in future I need to specify the SID in a RAC instance using spfile.



Wednesday, March 30, 2016

Why You Should Attend GLOC 2016?

This year’s Great Lakes Oracle Conference (GLOC) is going to be huge! I had the privilege to attend the GLOC 2014 and present in the GLOC 2015. The conference continues to bring most of the best Oracle experts to the Midwest, helping professionals and organizations to keep up to-date on products plus a great networking opportunity.

Keynote presentations are in charge of Maria Colgan and Bryn Llewellyn, both from Oracle. If you are a performance enthusiast, you should know or use Snapper, well, Tanel Poder is in da’ house with “Connecting Hadoop and Oracle” and showing us “Modern Linux Tools for Oracle Performance Diagnosis”. Want more? Tim Gorman is going to present on “Troubleshooting Using ASH” and Kellyn Pot’Vin-Gorman with “OEM 13c – Empowering The DBA With Advanced Features”.

If you like to know why your execution plan changed or your SQL performance is not optimal, we have Carlos Sierra explaining how-to “Find An Execution Plan The CBO hides” and Mauro Pagano will show how-to use “SQLd360, SQL Tuning Diagnosis Made Easy”.

If what you like is Oracle development, Jeff Smith from Oracle is going to present “More Than Just Tips & Tricks: SQL Developer & How-To Turbo Charge Your Oracle Experience”. GLOC 2016 features more than 50 sessions for DBA’s, developers and application administrators plus the pre-conference workshops, including “RAC Attack”!

Don’t miss the opportunity and be part of the biggest conference in the Great Lakes Region!

Uhhh, I almost forgot! The early registration ends April 11, so hurry up!