Oracle DataGuard 12: Standby database not starting after failover

I created a 12c DataGuard setup on Windows (2012 Server) using 3 machines:

• Primary database server (DB1 on server1)
• Standby database server (DB2 on server2)
• separate Observer machine

I created the setup "manually" (scripted, not using EM) and everything worked fine (using FAST_START FAILOVER). Everything, except that after a failover from server2 (initially the standby) to server1 (initially the primary), the database on server2 would not start automatically.

Failing over from server1 to server2 works fine:

• Shutdown server1 (disconnect virtual power)
• Observer notices failure and starts DB2 as primary
• Startup server1
• Observer sees server1 online, re-instates DB1 and starts DB1 as standby

And after re-instatement, the DataGuard configuration works fine again.

When I failover from server2 to server1, DB2 will not restart and not be re-instated after the server comes online again. After such a failover, I had to mount the database manually and then the Observer took over and re-instated the database.

After much searching, I found that in the Windows registry, one key was missing. In the registry (HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDB12Home1), there was no entry for "ORACLE_SID". I created the entry "ORACLE_SID=DB2" and set "ORA_DB2_AUTOSTART=TRUE". This was set to TRUE initially, but was set to FALSE on restart of the machine (probably because ORACLE_SID was not set).

I think that the "oradim" command for creating the service does not work properly. It does not create the ORACLE_SID key in the registry. This stops the database from autostarting (in mount mode) and DataGuard (the Observer) was not able to re-instate the database.  This is already automatically done on the (initial) primary server, probably by DBCA, so that database works fine after a restart.

So, as a quick solution, you should create the ORACLE_SID key in the Windows registry for the standby server in order to start the database automatically. I hope this helps anyone facing this problem!

Oracle Forms (using jacob) and Java 7 update 40 gives a warning that can't be ignored

This week alone, I heard of two customers that were having problems with Oracle Forms using WebUtil and jacob. Especially jacob proves to be a bit of a problem.

Let's backup up for a bit: if you're not familiar with webutil and jacob, first of all, look at oracle support ID 1093985.1 which tells you how to install and configure a lot of things. If this still doesn't ring any bells, you will probably not use webutil/jacob, so you can skip this entire post...

Anyway. After configuring and ( most importantly) signing your jacob.jar, everything worked fine. That is, until Java 7 update 40. Before that, you always had the option to "accept and always ignore" the message about the certificate, which was self-signed and therefore, not trusted.

Then with Java 7 update 40, A new security risk profile was implemented and an unsigned jar file or a jar file signed by an unknown publisher will get you the warning:
"Running applications by UNKNOWN publishers will be blocked in a future release because it is potentially unsafe and a security risk". See this page for details.

You can tick a check-box "I accept ..." and run anyway, but this message will come up EVERY time you start the Forms application. Obviously, application users don't want to tick the check-box and press that Run button every time they start their application. They want their application to start without any interruptions.

So, the way to do this seems rather straightforward. Just make jacob.jar use a trusted certificate. When signing the jar file, almost everyone used the self-signed certificate. This was rather easy to do and there was no problem at all.

First solution: instead of using a self-signed certificate, just use a real certificate issued by a CA. Drawbacks: this costs (some) money and the certificate will eventually expire. So this is not what we will be focusing on. There is an easier way.

Second solution: use the self-signed certificate as trusted. This is done by essentially:

• sign the jacob.jar with the provided signing batch file (sign_webutil.bat / sign_webutil.sh)
• See note 1076945.1
• reuse the created keystore to extract the certificate
• use keytool to extract this information
• import the certificate in your browser as a trusted certificate
• I placed the resulting crt file on my laptop and double-clicked. Just follow the wizard for importing the certificate

Details of these steps:

I modified the sign_webutil.bat (Yes I know, I used Windows...) to suit my needs (Names, Passwords, Locations, etc). After that, I issued the following commands:

set CLASSPATH=C:\Oracle\Middleware\as_fr\jdk\bin

set PATH=C:\Oracle\Middleware\as_fr\forms\java; C:\Oracle\Middleware\as_fr\forms\webutil

sign_webutil.bat C:\Oracle\Middleware\as_fr\forms\java\jacob.jar

A keystore ".keystore" was created in my home directory, so I extracted the certificate using keytool:

keytool -export -alias %JAR_KEY% -file %KEYSTORE%.crt -keystore %KEYSTORE% -storepass %JAR_KEY_PASSWORD%

The parameters all come from the orignal sign_webutil.bat and I mainly copied that file and modified it a bit for ease of use with the export option.

The resulting file ".keystore.crt" was used to import into the client keystore as a trusted certificate (just by double clicking it and following the wizard).

After these steps, the jar file was signed with a certificate that is now trusted. Trusted by my laptop that is, so everyone using this application must also import this certificate. Could be a problem, but most customers I see use Oracle Forms mainly internally, so distributing the certificate will not be much of a problem.

On first access of the application, a new message appears. Somewhat like this:

There is another checkbox this time, saying "Do not show this again for apps from the publisher and location above". Effectively, this gives us the same functionality as before: the "accept and always ignore" we always had. After this, no more messages will be displayed...

Using sqlplus input parameters to call specific scripts IF - THEN - ELSE style

Quite a while ago, I described using "NVL" for SQL*plus commandline parameters. Using this technique, you can create scripts that can cope with commandline parameters that are not always supplied on the commandline. So you don't always know whether a parameter is supplied or not and you don't want the script to go asking questions like "Enter the value for 4:" if you didn't supply a value for the fourth parameter.

A modified script made sure that all parameters were properly initialized and could be used in further statements without any prompts from sqlplus:

  COLUMN inputpar01 NEW_VALUE 1 NOPRINT
  COLUMN inputpar02 NEW_VALUE 2 NOPRINT
  COLUMN inputpar03 NEW_VALUE 3 NOPRINT
  COLUMN inputpar04 NEW_VALUE 4 NOPRINT
  select 1 inputpar01
       , 2 inputpar02
       , 3 inputpar03
       , 4 inputpar04
    from dual
   where 1=2;
  --
  PROMPT connecting as &1
  CONNECT &1/&2@&3
  SELECT username from user_users;
  PROMPT value for parameter 4 = &4

Recently, I had a question if the user could be prompted to enter the value for the fourth parameter if this had been omitted from the command line. So, you supply parameters 1, 2 and 3 and leave out parameter 4, which the script actually needs (for whatever reasons).
This can be accomplished using extra scripts ("helper" scripts).

I create the first helper script, that asks for a parameter and I call it "ask_parameter.sql". The contents are very simple:

  accept &1 prompt "What is the value for parameter &2 : "

A second script "dummy_script.sql" is created and has no content. This script will be used when a value is actually provided for the fourth parameter.

After these two scripts are created, we modify the original script to:

  COLUMN inputpar01 NEW_VALUE 1 NOPRINT
  COLUMN inputpar02 NEW_VALUE 2 NOPRINT
  COLUMN inputpar03 NEW_VALUE 3 NOPRINT
  COLUMN inputpar04 NEW_VALUE 4 NOPRINT
  select 1 inputpar01
       , 2 inputpar02
       , 3 inputpar03
       , 4 inputpar04
    from dual
   where 1=2;
  --
  PROMPT connecting as &1
  CONNECT &1/&2@&3
  SELECT username from user_users;
  PROMPT value for parameter 4 = &4
  --
  -- Check and set the fourth parameter 
  -- (NEWLY ADDED FUNCTIONALITY)
  --
  COLUMN ask_parameter NEW_VALUE ask_command NOPRINT;
  --
  SELECT nvl2( '&4'
             , 'dummy_script.sql'
             , 'ask_parameter.sql 4 my_name_for_par4'
             ) ask_parameter
  FROM dual;
  --
  set verify on
  set feedback on
  set termout on
  --
  -- Run the script determined in the previous step
  --
  @@&ask_command.
  --
  PROMPT value for parameter 4 = &4

The new part does the following:

• define a new parameter "ask_command" that will contain the name of the script to call
• based on the value of parameter 4, either select the value "dummy_script.sql" if it already contains a value, or select the value "ask_parameter.sql" (along with two new parameters for that script) if it contains no value
• run the script selected in the previous step. This is why we need the dummy script: if you just leave this NULL, sqlplus will error on the @@ command.
• if this is "ask_parameter.sql", then within that script &1 and &2 represent the parameters for that script and not the parameters for the main script. However, if you set "4" using the accept, it will set the fourth parameter for the main script!

In this case, when you call the main script using "testuser testpassword testdatabase"as parameters, you will be prompted:

  What is the value for parameter my_name_for_par4 :

This sets the value for parameter 4 of the main script, after which you can use it for any purpose (like using it as a parameter for another script).

So by using (generic) helper scripts, you can check, replace or prompt for any parameters. Just make sure that the scripts are in the same directory, or add a (relative) path to the helper scripts, should you decide to place them in a subdirectory (which is recommended for simplicity).

OEM DBconsole locking and huge resource usage

For some time now, we have had an 11g 2-node RAC database up and running (actually, we have more, but this one behaved somewhat different than expected). It has never been really necessary to plug it into Grid Control, so we still use DBConsole to monitor it when we need. It obviously is no production system we are talking about here...

Today, I came across something funny. The servers had been restarted some time ago, and we had decided to shut down the second node, in order to be able to determine the effect on the application the developers were working on. DBConsole hadn't been switched on yet, so I started it. And that is when the trouble began.

Within seconds, the CPU and I/O usage went way up. DBConsole was still (somewhat) responsive, so I investigated the problem using OEM. Two major problems were visible:

• Thousands of "active sessions" reported on the Cluster Home page
• A lot of active SYSMAN sessions, mainly for locking a MGMT_FAILOVER_TABLE and for executing EMD_NOTIFICATION.OMS_FAILOVER

One of the SYSMAN sessions was blocking several others, but getting apparently nowhere with the work it should be doing. Searching the internet I found some possible causes, but none of them seemed to help me here.

Then I decided to just use trial-and-error. Obviously, the MGMT_FAILOVER_TABLE was involved somewhere. Looking at the data in that table revealed that there were 135 rows in there. Not knowing whether this is normal, I looked it up in other RAC databases. These had only 1 row in the table, so I stopped DBConsole, made a backup of the table contents, removed all rows (probably could have left the most recent one in there..) and started DBConsole. It's been quite some time already, and the system is still running smoothly with low CPU and I/O usage, and a normal load from the developers.

I still don't know why there were that many rows in this table, but I am confident that there shouldn't be. Luckily, the system is smart enough to recover if you delete all the rows and after that, normal operations resume. If anyone knows what causes this, feel free to comment.

I'm looking forward to any answer that clarifies what really happened here ;-)


For those who wish to reproduce: the DB and OS used are

• 11gR2 (11.2.0.2.0)
• RedHat Linux 5.5

SRW.RUN_REPORT and the dreaded REP-0178

I was working on a Forms&Reports migration last month (6i to 11gR2) and had little trouble upgrading the entire application, except for one small part: some reports were used as a starting report, calling other reports (based on various criteria), using SRW.RUN_REPORT to do so.

SRW.RUN_REPORT was calling the other reports using both the "userid" en "server" parameters, which were deprecated in 10g. Using the "userid" will net you an Oracle error: REP-01434.
If you set the "REPORTS_SRWRUN_TO_SERVER" environment variable to YES, this error can be avoided, but I chose to eliminate these keywords altogether (as there weren't that many reports that used them). So after this, I expected the reports to run smoothly...

When invoking the starting report via the URL, the report itself ran OK, but the SRW.RUN_REPORT did not. Eventually, in the rwEng-0_diagnostic.log file In found only “REP-1428 An error occurred while running procedure SRW.RUN_REPORT in program unit beforereport”. Not much help there.

Because the SRW.RUN_REPORT effectively calls rwclient.exe (it’s a Windows 2008 implementation), I tried using rwclient from the command box. At first, this didn’t work either (REP-0178 Cannot connect to Reports Server), until I saw that one of the rwclient.bat scripts invoked reports.bat (setting the environment) and the other did not. When setting only two environment variables (ORACLE_HOME and ORACLE_INSTANCE), rwclient.exe also worked.

Encouraged by this, I set these two environment variables as system variables, so they will always be available for any program. Just to be sure, I rebooted the entire server as this was only an acceptation server at the moment. After reboot, the reports using SRW.RUN_REPORT also worked. Mission accomplished ;-).


Some details of the environment:
• OS = Windows 2008 R2
• AS = WebLogic 10.3.5
• FMW = 11.1.2 Forms&Reports Services
• Database (different server) = 11.2.0.3

Oracle Certified Master 11g - finally !

It has been quiet on my blog lately, but that was for a reason. I was very busy studying and preparing for my OCM (Oracle Certified Master) 11g exam. This month, I took the (upgrade) exam and last week I got the highly anticipated "Congratulations" e-mail from Oracle!

So, as of now I am an Oracle Certified Master for 9i, 10g and 11g. Needless to say that I am very happy with this result. At the same time I will try to post more frequently on my blog, as from now on, I should have some time left to do this again.

Dynamic SQL: Using a variable number of binds a variable number of times

Well known fact: most queries you execute often, have better performance when you use bind variables. On two separate occasions in the last month, a developer came to me with a question about Dynamic SQL.

The problem they both had was that when dynamically creating a (complex) SQL statement, they wanted to use bind variables. However, they didn't know how many variables they were actually going to use and they didn't know how many times they were going to use them throughout the statement.

Let's start out with something simple to clarify the problem:

CREATE OR REPLACE PROCEDURE dynamic_sql_example
  ( par_a IN varchar2
  , par_b IN number
  , par_c IN varchar2
  )
IS
  TYPE  t_cur IS REF CURSOR;
  c_cur t_cur;
  type t_rec is record (par_a varchar2(20), par_b number, par_c varchar2(20));
  r_rec t_rec;
  --
  l_qt varchar2(1) := chr(39);
BEGIN
  OPEN c_cur FOR 'SELECT '||l_qt||par_a||l_qt||
                      ', '||par_b||
                      ', '||l_qt||par_c||l_qt||
                  ' FROM dual';
  FETCH c_cur INTO r_rec;
  -- do something here
  CLOSE c_cur;
END;
/

All that this actually does is open a cursor for a simple SQL select statement. The problem with this statement is that it will generate a different execution plan for each distinct set of parameters. In order to prevent this, we quickly switch to bind variables:

CREATE OR REPLACE PROCEDURE dynamic_sql_example
  ( par_a IN varchar2
  , par_b IN number
  , par_c IN varchar2
  )
IS
  TYPE  t_cur IS REF CURSOR;
  c_cur t_cur;
  type t_rec is record (par_a varchar2(20), par_b number, par_c varchar2(20));
  r_rec t_rec;
  --
  l_qt varchar2(1) := chr(39);
BEGIN
  OPEN c_cur FOR 'SELECT :x par_a'||
                      ', :y par_b'||
                      ', :z par_c'||
                  ' FROM dual'
           USING par_a, par_b, par_c;
  FETCH c_cur INTO r_rec;
  CLOSE c_cur;
END;
/

This statement is superior to the previous one, as it will generate one execution plan because it uses bind variables. If you think that's just a small detail, try googling "bind variables" and see why you should use them...

So, now the developer wants to create a dynamic SQL statement using an as of yet unknown number and order of bind variables:

CREATE OR REPLACE PROCEDURE dynamic_sql_example
  ( par_a IN varchar2
  , par_b IN number
  , par_c IN varchar2
  )
IS
  TYPE  t_cur IS REF CURSOR;
  c_cur t_cur;
  type t_rec is record (par_a varchar2(20), par_b number, par_c varchar2(20));
  r_rec t_rec;
  --
  l_qt varchar2(1) := chr(39);
  --
  l_stmt varchar2(1000);
  --
BEGIN
  IF par_b = 1 THEN
    l_stmt := 'SELECT :x par_a'||
                  ' , :y par_b'||
                  ' , :z par_c'||
               ' FROM dual'||
              ' WHERE :y = 1'||
                ' AND :x = ''A''';
  ELSE
    l_stmt := 'SELECT :x par_a'||
                  ' , :y par_b'||
                  ' , :z par_c'||
               ' FROM dual'||
              ' WHERE :x = ''Z''';
  END IF;
  --
  OPEN c_cur FOR l_stmt
           USING par_a, par_b, par_c;
  FETCH c_cur INTO r_rec;
  CLOSE c_cur;
END;
/

Depending on the value of par_b, the statement will need either 5 bind variables (when the value = 1) or 4 bind variables (when the value != 1). Thus, we can not use the OPEN..FOR..USING construction without having to resort to another IF..THEN..ELSE construct. When the statement becomes more complex, this becomes too complex to handle. Executing the code as displayed above, will net you an "ORA-01008: not all variables bound" error.

The alternative is to take the bind variables out of the equation and store them in an unambigious and predetermined order:

CREATE OR REPLACE PROCEDURE dynamic_sql_example
  ( par_a IN varchar2
  , par_b IN number
  , par_c IN varchar2
  )
IS
  TYPE  t_cur IS REF CURSOR;
  c_cur t_cur;
  type t_rec is record (par_a varchar2(20), par_b number, par_c varchar2(20));
  r_rec t_rec;
  --
  l_qt varchar2(1) := chr(39);
  --
  l_with varchar2(1000);
  l_stmt varchar2(1000);
  --
BEGIN
  l_with := 'WITH my_parameters '||
             ' AS (SELECT :x par_a'||
                      ' , :y par_b'||
                      ' , :z par_c'||
                   ' FROM dual) ';
  --
  IF par_b = 1 THEN
    l_stmt := 'SELECT par.par_a par_a'||
                  ' , par.par_b par_b'||
                  ' , par.par_c par_c'||
               ' FROM dual'||
                  ' , my_parameters  par'||
              ' WHERE par.par_b = 1'||
                ' AND par.par_a = ''A''';
  ELSE
    l_stmt := 'SELECT par.par_a par_a'||
                  ' , par.par_b par_b'||
                  ' , par.par_c par_c'||
               ' FROM dual'||
                  ' , my_parameters  par'||
              ' WHERE par.par_a = ''Z''';
  END IF;
  --
  OPEN c_cur FOR l_with || l_stmt
           USING par_a, par_b, par_c;
  FETCH c_cur INTO r_rec;
  CLOSE c_cur;
END;
/

Using a WITH..AS construct, we now have a predetermined set of bind variables in a predetermined order. By referencing them in the rest of the code as column values, we are completely independent of the number and order of the bind variables in that part.

Mission Accomplished :-)