DBA Blogs

Next Thursday Daniel Morgan from TidalScale will be giving a free Oracle Database Security Master Class for our March Arizona Oracle User Group meeting. See the details in our Meetup link:

#meetup_oembed .mu_clearfix:after { visibility: hidden; display: block; font-size: 0; content: " "; clear: both; height: 0; }* html #meetup_oembed .mu_clearfix, *:first-child+html #meetup_oembed .mu_clearfix { zoom: 1; }#meetup_oembed { background:#eee;border:1px solid #ccc;padding:10px;-moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;margin:0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 12px; }#meetup_oembed h3 { font-weight:normal; margin:0 0 10px; padding:0; line-height:26px; font-family:Georgia,Palatino,serif; font-size:24px }#meetup_oembed p { margin: 0 0 10px; padding:0; line-height:16px; }#meetup_oembed img { border:none; margin:0; padding:0; }#meetup_oembed a, #meetup_oembed a:visited, #meetup_oembed a:link { color: #1B76B3; text-decoration: none; cursor: hand; cursor: pointer; }#meetup_oembed a:hover { color: #1B76B3; text-decoration: underline; }#meetup_oembed a.mu_button { font-size:14px; -moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;border:2px solid #A7241D;color:white!important;text-decoration:none;background-color: #CA3E47; background-image: -moz-linear-gradient(top, #ca3e47, #a8252e); background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, #a8252e), color-stop(1, #ca3e47));disvplay:inline-block;padding:5px 10px; }#meetup_oembed a.mu_button:hover { color: #fff!important; text-decoration: none; }#meetup_oembed .photo { width:50px; height:50px; overflow:hidden;background:#ccc;float:left;margin:0 5px 0 0;text-align:center;padding:1px; }#meetup_oembed .photo img { height:50px }#meetup_oembed .number { font-size:18px; }#meetup_oembed .thing { text-transform: uppercase; color: #555; }

Oracle Database Security Master Class

Thursday, Mar 7, 2019, 12:00 PM

Republic Services – 2nd Floor Conference Rooms
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ

13 AZORAS Attending

Oracle Database Security Master Class: Year after year Oracle databases that should be secure are violated. In some cases data is stolen, in some cases data is altered, in some cases the database is not the target but rather is means to an end. In almost all cases there is no excuse. No excuse … but there is an explanation. Oracle professionals d…

Check out this Meetup →

Please RSVP so that we know how many people will be there.

Given the ever-increasing number of security threats to our databases this session on Oracle database security should be valuable to us all.

I hope to see you there!

Bobby

I’m not a huge fan of the Windows platform although I’ve spent the early part of my career as a Systems Admin working on Windows. As I’ve gotten a bit older, Linux/Unix seem to be more my way of thinking; however, there are still many customers out there who run Oracle Databases and Microsoft SQL Server databases on the Windows platform. They still have a need to move data, right?

In this post, you’ll walk through how to install (yes a boring installation post) Oracle GoldenGate 18c Microservices on the Windows platform. This will include building the ServiceManager and first deployment. First there are a few prerequisites that need to be installed on the Microsoft Windows Platform. These items are:

• Microsoft Visual C++ 2010 SP1 (64-bit)
• Microsoft Visual C++ 2012 (64-bit)
• Microsoft Visual C++ 2017 (64-bit)

You wll also need to confirm with the certification matrix on what platforms are supported. You can find the certification matrix here.

Installation:

Just like previous releases starting with Oracle GoldenGate 12c (12.1.x), where the Oracle Universal Installer (OUI) was added; Oracle GoldenGate 18c is installed the same way on Windows as on *nix platforms. For this post, you will look at the OUI and the Oracle GoldenGate Configuration Assistance (OGGCA) on Windows.

1. Unzip the downloaded zip file to a directory of your choice. The zip file can either be pulled from Oracle EDelivery or from the Oracle Technology Network. The command below will unzip the file from a Linux prompt, but you can use the Windows Extractor or Winzip to unzip the file.

unzip V980818-01.zip -d ./ogg18c_win

2. Navigate to the directory where the you unzipped the file. You will find a directory called fbo_gg_Windows_x64_services_shiphome. Inside of this directory you will find another called Disk1. You will need to be inside of the Disk1 directory.

3. From the Disk1 directory, you will run the Setup application. You can double-click this application. This will start the Oracle Universal Installer.

4. On the first step of the Oracle GoldenGate 18c installation, you need to specify what version of the Oracle Database you will run Oracle GoldenGate against. Since there is an Oracle Database 18c installed, select the Oracle GoldenGate for Oracle Database 18c.

5. The Software Location is the same at the Oracle GoldenGate Home (OGG_HOME) location. Specify where the software should be installed.

6. Finally, confirm everything you have selected during the installation wizard and click “Install”. At this time, you can also option to save the install steps into a response file by click the “Save Response File” button.

7. At this point, the installation will begin and should complete fairly quickly. Once the installation is done, the Oracle Universal Installer can be closed.

With the Oracle GoldenGate 18c (18.1.0) software installed, you now want to to build your ServiceManager and first deployment. In order to do this, you will need to go to the $OGG_HOME\bin directory and run the batch file called OGGCA.

To run the Oracle GoldenGate Configuration Assistant (OGGCA (batch file)):

1. Navigate to the Oracle GoldenGate Home and enter the $OGG_HOME\bin directory

2. Double-Click on the batch file for the Oracle GoldenGate Configuration Assistanct (oggca). This will start the configuration assistant.

3. Being that this is a new installation, you will provide all the following information:

• Select the radio button for “Create New Service Manager”
• Provide the ServiceManager Deployment Home location
• Leave “Listening hostname/address” as the default
• Provide a “Listening Port”
• Select the checkbox for “Registering Service Manager as a system service/daemon”

After you have provide all the needed details, select Next to move through the wizard.

4. Next step, you will add a new GoldenGate Deployment. On this step, the only thing you need to do is click “Next”. The radio button should already be selected for you.

5. Now you will need to provide the deployment details. This consists of the Deployment Name and the Software Home. Keep in mind that the Software Home is the Oracle GoldenGate Home (OGG_HOME). Should be automatically populated as well.

6. After defining the name of the Deployment, you are given the opportunity to provide the deployment home location. On this screen, you can also choose to customize the locations of various deployment files. For now, just provide a deployment home.

7. Next step is to make sure your environment variables are correct.

8. Provide an administrator account. This account is defined at the “Security Role” within the Microservices Deployment framework. This is the same account that will be used to login to the ServiceManager and associated Deployments. If this account is lost, there is no way to recreate the account to date. This same account will need to be use when creating additional deployments under the same ServiceManager.

9. At this point, you have to choose if you want your deployment to be secure or un-secure. To simplify the understanding of the install, lets go with un-secure; however, we will not be able to change the decision unless we recreate the deployment. Use the checkbox for SSL/TLS security very wisely.

10. Now you will assign port numbers to all the services within the deployment. These port numbers will allow you to access each service individually of each other. There is a port number for the following services:

• Administration Server
• Distribution Server
• Receiver Server
• Performance Metric Server – TCP
• Performance Metric Server – UDP

Additionally, you need to select the type of NoSQL database that will be used for storing performance metrics information. In the example, you selected Berkely database (BDB). You can also select Light Memory Database (LMDB). Lastly, you will provide a location where the database information will be stored.

11. Now, you will provide the default GoldenGate schema. This is the GoldenGate user that will be inside of the Oracle Database/Pluggable Database. Items like Automatic Heartbeat and Checkpoint tables will go into this schema. In this example, lets call it GGATE.

12. Before creating the deployment, you now have the opportunity to review your selections on the Summary page. At the same time, you can select the “Save Response File” and edit the resulting file for silent installs of the deployments as well. If you are happy with everything you are selecting, then you can click “Finish” to begin building the ServiceManager and associated first deployment.

After clicking “Finish”, the Oracle GoldenGate Configuration Assistant will build the ServiceManaager and the first deployment. At this point, you should be able to access the ServiceManager using the hostname and port number you specified during the configuration.

I was on call last week and I got a ticket about a batch job that was failing on a MERGE statement with an ORA-30926 error. Our support team worked around it by deleting some duplicate rows in a table that was feeding into the MERGE. This week I wanted to go back and try to understand what would cause ORA-30926 errors on MERGE statements.

I read through some blog posts and Oracle support documents relating to ORA-30926 and merge. Then I tried building some simple test scripts to see when you get ORA-30926 on a MERGE. At the end of my search I came back to this simple statement from the 18c SQL Language Reference manual’s description of the MERGE statement:

MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.

I was puzzled by the term “deterministic” but “cannot update the same row…multiple times” seems simple enough. I created test scripts to show whether you can update the same row multiple times with a MERGE: zip

Here are the two test tables:

create table target (a number,b number);

create table source (a number,b number);

Here is data and a MERGE statement that causes the error:

SQL> insert into target values (1,1);
SQL> insert into source values (1,2);
SQL> insert into source values (1,3);

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b;
using source
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables 

This seems very simple. We join the source and target tables on column A with value 1. The merge statement tries to update column B on the target table row twice. It tries to set it to 2 and then to 3. I guess this is where the term “deterministic” comes in. If the merge statement updated B to 2 and 3 which would it do first and which second?

The example above with values 2 and 3 for B makes good sense but I saw some odd behavior when I used source rows with 1 and 2 for the B values of the two rows. With B values of 1 and 2 in this case the MERGE does not get an error:

SQL> insert into target values (1,1);
SQL> insert into source values (1,1);
SQL> insert into source values (1,2);

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b;

2 rows merged.

SQL> select * from target;

         A          B                                                           
---------- ----------                                                           
         1          2                                                           

This seems to contradict the idea that you cannot update the same row multiple times with a MERGE. In this case it seems like B is updated twice, once to 1 which is what it already was set to and then to 2. I guess this works because B was already set to 1 so the update of B to the same value does not count as an update. It seems like only one update took place and then B ends up set to 2.

This example does not work with a slightly different MERGE statement on Oracle 12.1 or earlier:

SQL> select banner from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b
  6  when not matched then
  7  insert values (source.a,source.b);
using source
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables 

SQL> select * from source;

         A          B                                                           
---------- ----------                                                           
         1          1                                                           
         1          2                                                           

SQL> 
SQL> select * from target;

         A          B                                                           
---------- ----------                                                           
         1          1                                                           

All that I can say based on these two tests is that sometimes an update of a column to the same value counts as an update and sometimes it does not. The preceding example works on 12.2:

SQL> select banner from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production    

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b
  6  when not matched then
  7  insert values (source.a,source.b);

2 rows merged.

SQL> select * from source;

         A          B                                                           
---------- ----------                                                           
         1          1                                                           
         1          2                                                           

SQL> 
SQL> select * from target;

         A          B                                                           
---------- ----------                                                           
         1          2                                                           

It all seemed so simple when I read the documentation. Maybe instead of saying:

You cannot update the same row of the target table multiple times in the same MERGE statement.

Instead it should say:

You may not be able to update the same row of the target table multiple times in the same MERGE statement.

We should not count on being able to update the same row multiple times with a single MERGE statement, but clearly there are some cases in which we can. If you are like me and you get paged on a job that fails with ORA-30926 on a MERGE, it makes sense to get rid of the multiple updates on a single row because you most likely have hit a case where it is not allowed.

Bobby