DBA Blogs

Oracle Database Security Master Class AZORA Meetup Thursday

Bobby Durrett's DBA Blog - Fri, 2019-03-01 16:33

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

Categories: DBA Blogs

Installing Oracle GoldenGate 18c Microservices on Windows…. I know, why?

DBASolved - Fri, 2019-03-01 12:00

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.

Categories: DBA Blogs

How can I export the tablespace that has been dropped one of the datafile?

Tom Kyte - Fri, 2019-03-01 03:26
Hi,Tom During my work,i have met such question,and hope yr answer. 1. I wrongly create a datafile under the tablespace DRSYS,then I alter database 'datafile ' offline, and drop this datafile under OS command.Now the troubles arise. W...
Categories: DBA Blogs

Oracle instant client 12c EZConnect Using tcps instead of tcp

Tom Kyte - Thu, 2019-02-28 09:06
I download oracle instant client 12.2.0.1.0, I try to use sql loader to load csv data to the database. I can successfully load using following: <code>sqlLdr.exe userid=user/password@//192.9.200.228:1521/oracle ERRORS=4000 control=D:\temp\csma\xx_2...
Categories: DBA Blogs

export and import taking a lot of time

Tom Kyte - Thu, 2019-02-28 09:06
Hi, I need to export and import a table having 2 million data in it. using exp/imp command . I tried using the below commands but it took a lot of time to export and then import the data. Please help. Commands :exp test/test full=y file=REL...
Categories: DBA Blogs

How to generate DDL for APEX app from code

Tom Kyte - Thu, 2019-02-28 09:06
In Oracle SQL Developer, 'Application Express' section, we can export DDL for an APEX app by context menu, 'Quick DDL' > 'Save to Worksheet' menu. I'd like to do the same by executing some SQL or PL/SQL code. I know we have DBMS_METADATA.GET_...
Categories: DBA Blogs

Reading Header Info from CSV

Tom Kyte - Thu, 2019-02-28 09:06
Hi Team My csv looks like below 123456,20,20,1500 --- this is the header abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs I need to read the header info. Can you pls tell me whats the best ...
Categories: DBA Blogs

Emails sending out of 12c SuperCluster Database

Tom Kyte - Wed, 2019-02-27 14:46
Oracle version: <code>SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 26 15:14:07 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Feb 26 2019 14:05:04 -06:00 Connected to: Oracle Database...
Categories: DBA Blogs

How to group by for a data set

Tom Kyte - Wed, 2019-02-27 14:46
Hi Tom, I have a table with many records for example as below. <code>create table test (a varcahr2(20)); insert into test values ('1'); insert into test values ('2'); insert into test values ('3'); insert into test values ('abc-oo cde')...
Categories: DBA Blogs

Using Access Advisor in Oracle 12

Tom Kyte - Wed, 2019-02-27 14:46
Hello, I am trying to use Access Advisor package in a 12.2 DB. When executing DBMS_ADVISOR.EXECUTE_TASK I get error: begin * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01843: not a valid month ORA-06512: at "SY...
Categories: DBA Blogs

Oracle 12.2: Delete Privilege needs an additional Select Privilege

Tom Kyte - Tue, 2019-02-26 20:26
Hi, Why do we need an extra select privilege additional to the delete privilege? From my point of view, this does'nt make sense: <code>--execute as user a: create table b.t (col varchar2 (10)); insert into b.t values ('a'); commit; gr...
Categories: DBA Blogs

Bind Variables

Tom Kyte - Tue, 2019-02-26 20:26
Hi Tom, I tried to use Bind Variables in my Script. But that script is taking more time than expected. Please have a look at the below script. area@DBWH> truncate table t1; Table truncated. Elapsed: 00:00:00.50 area@DBWH> declare ...
Categories: DBA Blogs

can't we use nologging clause with domain index

Tom Kyte - Tue, 2019-02-26 20:26
Hi Tom, I am trying to create domain index on table, I am getting <b><i>ORA-29850: invalid option for creation of domain indexes</i></b>. Can you please explain why can't we create domain index with nologging option. <code> SQL> begin 2 c...
Categories: DBA Blogs

ORA-30926 on MERGE statement

Bobby Durrett's DBA Blog - Tue, 2019-02-26 15:19

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

Categories: DBA Blogs

getting error ( ORA-06553: PLS-306: wrong number or types of arguments in call to) after migrating code from oracle 11.2.0.3.0 to 12 c

Tom Kyte - Mon, 2019-02-25 08:06
we are in the process of upgrading our oracle database from 11.2 to 12.2 ran into an error: ORA-06553: PLS-306: wrong number or types of arguments in call to I have created a simplified script that recreates the issue. the error occurs at com...
Categories: DBA Blogs

AWS Support Knowledge Center

Pakistan's First Oracle Blog - Sun, 2019-02-24 19:29
In addition to AWS documentation and blogs, one of the best resource is AWS Knowledge Center. It contains frequently asked questions from AWS customers so this resource contains real world problems with their solution.


AWS Knowledge Center contains FAQ from almost all of the AWS services. For example, I was researching about if there was a way to restore or recover a terminated EC2 instance in case I didn't have any backups or AMIs, and all the EBS volumes were deleted and the Answer was No.

The great thing was that AWS did mention the best practice in that case about taking backups or making AMIs of instances or better yet enabling the termination protection.

AWS Knowledge Center is quite a rich resource and like everything else with AWS, its continuously evolving.
Categories: DBA Blogs

Fetching all records from a extremely large oracle table

Tom Kyte - Sat, 2019-02-23 19:26
Hi Tom, I am working on a ETL project that involves fetching all records from a Extremely large oracle table (that contains millions and millions of records) and has a very large number of partitions. I need to extract all data from the Table. ...
Categories: DBA Blogs

Measuring usage of schema objects

Tom Kyte - Sat, 2019-02-23 19:26
I have been tasked with identifying which of my group's schema objects (tables and views) are not being used anymore. What system table(s) can I query to find out which users are running SELECT statements against a specific owner's schema objects, p...
Categories: DBA Blogs

New_time function is failed to convert dst changes for date column

Tom Kyte - Sat, 2019-02-23 19:26
Hi , I have 2 table columns with date as datatype. 1 stores in central standard time other in central local time. Everyday a batch runs to populate these values to another oracle db. target db column is in eastern local. when I use new_time functi...
Categories: DBA Blogs

Is there a timeout for a query over database link?

Tom Kyte - Sat, 2019-02-23 19:26
Greetings - I have written PL/sql code that gathers tablespace information among 100's of Databases on a regular basis that would assist in our Capacity Planning Program. This code is configured using dbms scheduler on a central server/db. At...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs