Skip navigation.

Feed aggregator

REDO_TRANSPORT_USER and Recovery Appliance (ZDLRA)

Fuad Arshad - Tue, 2016-05-17 09:14

“REDO_TRANSPORT_USER” was an Oracle Database Parameter that was introduced in Oracle release 11.1 to help transporting redo from a primary to a standby by using a user designated for log transport , The default configuration assumes the user “SYS” is performing the transport.
This distinction is very important since the user “SYS” is available on every Oracle database and as such most data guard environment when created with default settings are created with “SYS” being the used for Log Transport services.
The Zero Data Loss Recovery Appliance (ZDLRA) adds an interesting twist to this configuration. In order for Real-TIme redo to work on a ZDLRA, the “REDO_TRANSPORT_USER” needs to be set to the Virtual Private Catalog (VPC) user of the ZDLRA. For database that are not participating in the Data Guard configuration , this is not an issue and a user does not be created on the Protected Database i.e the database being backed up to the ZDLRA. The important distinction comes into play if you already have a standby configured to receive redo, that process will break since we have switched the “REDO_TRANSPORT_USER” to a user that doesn’t exist on the protected database. In order to avoid this issue if you already have a Data Guard , you will need to create the VPC user as a user in the primary database with the "create session” and “sysoper" with an optional “sysdg” (12c) .
An example configuration is detailed below.
SQL> select * from v$pwfile_users;

SQL> select * from v$pwfile_users;

USERNAMESYSDBSYSOPSYSASSYSBASYSDGSYSKMCON_IDSYSTRUETRUEFALSEFALSEFALSEFALSE 0SYSDGFALSEFALSEFALSEFALSETRUEFALSE 0SYSBACKUPFALSEFALSEFALSETRUEFALSEFALSE 0SYSKMFALSEFALSEFALSEFALSEFALSETRUE 0

SQL> create user ravpc1 identified by ratest;
User created.

SQL> grant sysoper,create session to ravpc1;
Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAMESYSDBSYSOPSYSASSYSBASYSDGSYSKMCON_IDSYSTRUETRUEFALSEFALSEFALSEFALSE 0SYSDGFALSEFALSEFALSEFALSETRUEFALSE 0SYSBACKUPFALSEFALSEFALSETRUEFALSEFALSE 0SYSKMFALSEFALSEFALSEFALSEFALSETRUE 0RAVPC1FALSETRUEFALSEFALSEFALSEFALSE 0

SQL> spool off


Once you have ensure that the password file has the entries , copy the password file to the standby node(s) and then ensure that the destination state on the primary to the standby is reset by deferring and then reenabling the destination state

SQL> alter system set log_archive_dest_state_X=defer scope=both sid='*'
SQL> alter system set log_archive_dest_state_X=enable scope=both sid='*'

This will ensure that you have redo transport working to the Data Guard standby and the ZDLRA


References

Data Guard Standby Database log shipping failing reporting ORA-01031 and Error 1017 when using Redo Transport User (Doc ID 1542132.1)
MAA White Paper - Deploying a Recovery Appliance in a Data Guard environment
REDO_TRANSPORT_USER Reference
Redo Transport Services
Real-Time Redo for Recovery Appliance

Bitcoin for Enterprise IT

Gerger Consulting - Tue, 2016-05-17 08:54
We have a free webinar on May 18th! Attend our free webinar, presented by Balaji S. Srinivasan, one of the most prominent figures in the Bitcoin community, and learn how you can monetize any database with Bitcoin.

Many thanks to 145+ people who already signed up! Register here.



Categories: Development

12 Game of Thrones Career Lessons You Can Benefit From

Complete IT Professional - Tue, 2016-05-17 05:00
The popular fantasy show has many characters and plot lines. We can learn a lot from them on how we can improve our career. Game of Thrones is probably the best show I’ve ever seen. Every week, my girlfriend and I watch it, glued to the screen for an hour and then we eagerly await […]
Categories: Development

We are Hiring - Red Samurai ADF and JET Team

Andrejus Baranovski - Tue, 2016-05-17 01:19
Do you want to join Red Samurai force and become one of our technical experts? The moment is now - we are looking for additional team player.

You should be strong in ADF, highly preferable to be confident with JavaScript and Oracle JET. It never hurts to be fluent in PL/SQL and understand Oracle Forms. You will work in Red Samurai projects focused on ADF and JET.

What would be your position? You name it - you will get a chance to wear multiple hats - expert ADF or JET developer, performance tuning specialist or the guy who solves unsolvable.

Don't wait - drop me email: abaranovskis at redsamuraiconsulting dot com

Amazon RDS

Pat Shuff - Tue, 2016-05-17 01:07
Today we are going to look at Amazon RDS as a solution for running the Oracle Database in the cloud. According to the Amazon RDS website RDS is an easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you up to focus on your applications and business. Amazon RDS provides you six familiar database engines to choose from, including Amazon Aurora, Oracle, Microsoft SQL Server, PostgreSQL, MySQL and MariaDB.

We are going to focus on the Oracle Standard Edition and Enterprise Edition deployments via RDS. We previously looked at Application Express using Amazon RDS so we won't dive deep into the different shapes available and skim over pricing in this discussion. With Amazon RDS, you can deploy multiple editions of Oracle Database. You can run Amazon RDS for Oracle under two different licensing models – “License Included” and “Bring-Your-Own-License (BYOL)”. In the "License Included" service model, you do not need separately purchased Oracle licenses; the Oracle Database software has been licensed by AWS. "License Included" pricing. Oracle has a policy paper on cloud licensing. It is important to understand database licensing and how it applies to hard partitions, soft partitions, and cloud environments.

Automated backups are turned on by default and snapshots are enabled as they are for EC2 instances. You can scale up and down processors as well as scale up and down IOPs allocated for the Oracle instance. You can use Amazon VPC to connect this instance to your data center as well as Amazon HSM to encrypt your data.

The two biggest issues that you need to consider with any cloud strategy is security and lock in. Backups are done from Amazon zone to Amazon zone. Oracle RMAN is not available as a backup mechanism and neither is the Oracle Advanced Security. Encryption is done at the disk layer and not inside the database. Amazon strips the ability for you to replicate your data back to your data center or use your security keys to encrypt your data. You have to use their backup tools and their encryption technology using their keys to protect your data. Key management and key rotation become an issue for security sensitive applications and data sets.

Amazon RDS pricing is available on the Amazon web page. Pricing starts at $0.035/hour for a quarter virtual processor and 1 GB of RAM and goes up to $3.64/hour for a standard instance and high memory instance. This pricing is for Standard Edition One of the database and includes the database license. For Standard Edition Two and Enterprise Edition, you must bring your own license. Pricing for this model starts at $0.017/hour and grows to $7.56/hour. You can also pay for a reserved account that dedicates an instance to you for as low as $99/year growing upto $33.8K/year. Data ingestion to load the database is free but there is a cost associated with copying data from the RDS instance to your client at a charge ranging from $0.09/GB/month to $0.05/GB/month at higher transfer rates. We recommend that you use the Amazon AWS Pricing Calculator to figure out your monthly charges.

To create an RDS instance, we go to the AWS Console and select the RDS instance. From here we select the "Get Started Now" button in the middle of the screen. We then select the Oracle tab and the Oracle EE Select button. To save money we are going to select the Dev/Test version but both effectively do the same thing. The key difference in the dev or production selections are minor. The production instance preloads the Instance Class with a m3.xlarge, Multi-AZ turned on, and Storage Type set to SSD. There is one item displayed, Provisioned IOPS, in the create screen that is not in the dev option. We can make the dev option look just like the production option by selecting a large enough instance and turning on Multi-AZ in the next screen.

Production instance

Development instance

We are going to select the latest 12.1.0.2 version, an instance with a little memory, general purpose storage to get 3 IOPS/GB since this is a test instance, and define our ORACLE_SID and account to access the instance.

The next screen is what defines this as Platform as a Service and not an EC2 instance with an AMI. We automatically open ports in the operating system and network access by opening port 1521 for the listener, we confirm the OID, select the character name set, turn on or off encryption in the storage, define the backup window and retention period, as well as patching policies. We are going to accept the defaults and not change anything. The one thing that Amazon does that Oracle does not is define the VPC connection when you define the database. Oracle requires a separate step to create a VPN connection. If you select Multi-AZ, I would have expected to see a selection of zones that you can replicate across. For all of the options that I selected, the Availability Zone was greyed out and I could not select the failover zone. I assume that you have to pre-define a zone relationship to make this work but it was never an option for my tests.

Once you click on Create Instance you see a splash screen and can go to the RDS monitor to look at instances that you have launched.

Once the provisioning is finished we can look at the connection details and use SQL Developer to connect to the instance. It is important to note here that we do not have SSH or command line access to this database instance. We only have access through port 1521 and do not have sys, system, or sysdba access to this database instance.

We can connect with SQL Developer by using the details from the previous screen to get the endpoint, port, and instance identifier.

The first thing to note with the database connection is that the RDS instance does not have a container. You are connecting to the base instance and pluggable databases are not available. If you have purchased the multi-tenant option for the database, RDS is not an option. If we dive into the database configuration we note that auditing is turned off for RDS. The primary reason for this is that you would not have access to the audit logs since you don't have file system access to read the logs. If you look at the control management access packs parameter, diagnostics and tuning is turned on and enables. This means that you have to purchase these additional packages to run in RDS. There is no way to turn this option off and these licenses are not included as part of the RDS usage license. You also do not have access to this data but have to go through the monitor screens to view this data and sql developer. The database compatability type is set to 12.0.0. Given that we do not have sys access we can not run in compatability mode to help migrate 11g databases into a container. Some important parameters are set that we can not change; enable_ddl_logging is false disabling DataGuard, enable_goldengate_replication is false disabling Golden Gate, enable_pluggable_database is false disabling Multi-Tenant. Default_tbs_type is set to bigfile and there are no mechanisms to change this.

It is important to figure out what the default user that we created can and can't do when managing the database. The roles assigned to this user are rather limited. We can compare the roles of the oracle user (the one we created) to the sys user. Note that the oracle roles are a short list.

The RDS Option Documentation talks about connecting to enterprise manager and application express. We were not able to connect to ports 1158 or 5500 as suggested. My gut says that this has to do with the port routing rules that were created by default.

If we are running out of table space we can modify the existing instance and grow the storage. This is done by going to the RDS instance page and selecting modify instance. You type in the new storage size and click apply immediately.

Once the modification finishes we can see the new storage in the details page in the RDS console.

We should note that we do not see the addition to the tablespace because it is added to the filesystem but the tablespaces are all configured to auto extend and consume all available space. Unfortunately, this makes it look like all of the tablespace is full and our used percent will always be relatively high for the files that our tables are stored. We need to monitor disk usage separately with a different part of the RDS console.

In summary, you can run the Oracle database in Amazon RDS. There are limitations and issues that you need to be aware of when doing this. Some of the features are limited and not available to you. Some of the features are required which you might not be using today. If you are running an application server in EC2, running the database in RDS makes sense. The intention of this blog is not to tear down one implementation and elevate another but to elevate discussion on what to look out for when you have decided to run in a specific cloud instance. Up next, how does Amazon RDS differ from Amazon EC2 with a pre-configured AMI.

Fun Project from Australia, Anki Overdrive Plus Oracle Cloud Services

Oracle AppsLab - Mon, 2016-05-16 19:44

Editor’s Note: In February while we were in Australia, I had the pleasure to meet Stuart Coggins (@ozcoggs) and Scott Newman (@lamdadsn). They told me about a sweet Anki Overdrive cars plus Oracle Cloud Services hack Stuart and some colleagues did for Pausefest 2016 in Melbourne.

Last week, Stuart sent over a more detailed video of the specifics of the build and a brief writeup of what was involved. Here they are.

Oracle IoT and Anki Overdrive

By Stuart Coggins

Some time ago, our Middleware team stumbled upon the Anki Overdrive and its innovative use of technology, including APIs to augment a video game with physical racing cars.

We first presented an IoT focused demonstration earlier this year at an Innovation event in Melbourne. It was very well received, and considered very “un-Oracle.”

Over the past few months, the demo scope has broadened. And so has collaboration across Oracle’s Lines of Business. We saw an opportunity to make use of some of our Cloud Services with a “Data in Action” theme.

We’ve taken the track to several events, spanning various subject areas. Always sparking the question “what does this have to do with me?” And in some cases, “Why is Oracle playing with racing cars?”

As if the cars are not draw card enough at our events, the drone has been a winner. Again, an opportunity to showcase how using a range of services can make things happen.

As you’ll see in the video, the flow is fairly straightforward… the game, running on a tablet talks to the cars via Bluetooth. Using Bluetooth sniffers on a Raspberry Pi, we interrogate the communication between the devices. There are many game events as well as car activity events (speed of left/right wheels, change lane, turn left, turn right, off track, etc). We’re using Python scripts to forward the data to Oracle’s Internet of Things Cloud Service.

1604IMG_5225_Anki

This is where things get interesting. The speed and laptime data is being filtered out, and forwarded to Oracle’s Database Cloud Service. The “speedo” dials are rendered using Oracle Apex (Application Express), which does a great job. An “off track” event is singled out and instantiates a process defined in Oracle Process Cloud Service. At this point, we’ll integrate to Oracle Service Cloud to create an event for later auditing and logging. Whilst airborne, the drone captures photos of the incident (the crashed cars), and sends them back to the process. The business process has created an incident folder on Oracle Document Cloud Service to record any details regarding the event, including the photos.

Because data is not much use if you’re not going to do something with it, we then hook up Oracle Business Intelligence Cloud Service to the data stored in Database Cloud Service. Post race analysis is visualised to show the results, and with several sets of race data, gives us insight as to which car is consistently recording the fastest laptimes. i.e. the car that should be used when challenging colleagues to a race!

As we’ve been running this for a few months now, and the use cases and applications of this technology grow, we’re getting more and more data. The adage that data creates data is certainly true here.

Ultimately, we’ll dump this data into Hadoop and perform some discovery, perhaps to understand how the track changes during the day (dust/dirt/use) etc. We’d like to get some temperature data from the Pi to understand if that has any effect on the car performances, and perhaps we’ll have enough data for us to be able to analyse the perfect lap, and replay it using the Anki SDK.

1604DSCF8543_Anki

We’re planning a number of hackathons locally with this kit, and we’ll see what other innovations we can highlight.

A big shout out to the technical guy behind sniffing and “translating” the data. The data is not exposed by the SDK and was by no means trivial to maps but it has allowed us to get something meaningful and put it into action.

Enjoy!Possibly Related Posts:

Null is Odd…or Things I Used to Know about SQL Aggregate Functions

The Anti-Kyte - Mon, 2016-05-16 14:03

Brendan McCullum recently played his final Test for New Zealand.
That’s something of an understatement. In his last game he made a century in a mere 54 balls, a feat unmatched in 139 years of test cricket.
From the outside looking in, it seemed that McCullum had come to realise something he’d always known. Playing cricket is supposed to be fun.
What’s more, you can consider yourself quite fortunate if you get paid for doing something you enjoy, especially when that something is hitting a ball with a stick.

With the help of Mr McCullum, what follows will serve to remind me of something I’ve always known but may forget from time to time.
In my case, it’s the fact that NULL is odd. This is especially true when it comes to basic SQL aggregation functions.

Some test data

We’ve got a simple table that holds the number of runs scored by McCullum in each of his Test innings together with a nullable value to indicate whether or not he was dismissed in that innings.

This is relevant because one of the things we’re going to do is calculate his batting average.

In Cricket, the formula for this is :

Runs Scored / (Innings Batted – Times Not Out)

Anyway, here’s the table :

create table mccullum_inns
(
    score number not null,
    not_out number
)
/

…and the data…

insert into mccullum_inns( score,not_out) values (57, null);
insert into mccullum_inns( score,not_out) values (19, 1);
insert into mccullum_inns( score,not_out) values (13, null);
insert into mccullum_inns( score,not_out) values (55, null);
insert into mccullum_inns( score,not_out) values (3, null);
insert into mccullum_inns( score,not_out) values (5, null);
insert into mccullum_inns( score,not_out) values (96, null);
insert into mccullum_inns( score,not_out) values (54, null);
insert into mccullum_inns( score,not_out) values (20, null);
insert into mccullum_inns( score,not_out) values (21, null);
insert into mccullum_inns( score,not_out) values (4, null);
insert into mccullum_inns( score,not_out) values (143, null);
insert into mccullum_inns( score,not_out) values (17, 1);
insert into mccullum_inns( score,not_out) values (10, null);
insert into mccullum_inns( score,not_out) values (8, null);
insert into mccullum_inns( score,not_out) values (10, null);
insert into mccullum_inns( score,not_out) values (36, null);
insert into mccullum_inns( score,not_out) values (29, null);
insert into mccullum_inns( score,not_out) values (24, null);
insert into mccullum_inns( score,not_out) values (3, null);
insert into mccullum_inns( score,not_out) values (25, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (99, null);
insert into mccullum_inns( score,not_out) values (7, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (111, null);
insert into mccullum_inns( score,not_out) values (24, null);
insert into mccullum_inns( score,not_out) values (19, null);
insert into mccullum_inns( score,not_out) values (74, null);
insert into mccullum_inns( score,not_out) values (23, null);
insert into mccullum_inns( score,not_out) values (31, null);
insert into mccullum_inns( score,not_out) values (33, null);
insert into mccullum_inns( score,not_out) values (5, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (5, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (14, 1);
insert into mccullum_inns( score,not_out) values (43, null);
insert into mccullum_inns( score,not_out) values (17, null);
insert into mccullum_inns( score,not_out) values (9, null);
insert into mccullum_inns( score,not_out) values (26, null);
insert into mccullum_inns( score,not_out) values (13, null);
insert into mccullum_inns( score,not_out) values (21, null);
insert into mccullum_inns( score,not_out) values (7, null);
insert into mccullum_inns( score,not_out) values (40, null);
insert into mccullum_inns( score,not_out) values (51, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (25, null);
insert into mccullum_inns( score,not_out) values (85, null);
insert into mccullum_inns( score,not_out) values (9, null);
insert into mccullum_inns( score,not_out) values (42, null);
insert into mccullum_inns( score,not_out) values (97, null);
insert into mccullum_inns( score,not_out) values (24, null);
insert into mccullum_inns( score,not_out) values (11, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (9, null);
insert into mccullum_inns( score,not_out) values (71, null);
insert into mccullum_inns( score,not_out) values (25, null);
insert into mccullum_inns( score,not_out) values (2, null);
insert into mccullum_inns( score,not_out) values (66, null);
insert into mccullum_inns( score,not_out) values (8, null);
insert into mccullum_inns( score,not_out) values (3, null);
insert into mccullum_inns( score,not_out) values (30, null);
insert into mccullum_inns( score,not_out) values (84, 1);
insert into mccullum_inns( score,not_out) values (25, null);
insert into mccullum_inns( score,not_out) values (31, null);
insert into mccullum_inns( score,not_out) values (19, null);
insert into mccullum_inns( score,not_out) values (3, null);
insert into mccullum_inns( score,not_out) values (84, null);
insert into mccullum_inns( score,not_out) values (115, null);
insert into mccullum_inns( score,not_out) values (24, null);
insert into mccullum_inns( score,not_out) values (6, null);
insert into mccullum_inns( score,not_out) values (1, null);
insert into mccullum_inns( score,not_out) values (29, null);
insert into mccullum_inns( score,not_out) values (18, null);
insert into mccullum_inns( score,not_out) values (13, null);
insert into mccullum_inns( score,not_out) values (78, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (24, null);
insert into mccullum_inns( score,not_out) values (89, null);
insert into mccullum_inns( score,not_out) values (185, null);
insert into mccullum_inns( score,not_out) values (19, 1);
insert into mccullum_inns( score,not_out) values (24, null);
insert into mccullum_inns( score,not_out) values (104, null);
insert into mccullum_inns( score,not_out) values (5, null);
insert into mccullum_inns( score,not_out) values (51, null);
insert into mccullum_inns( score,not_out) values (65, null);
insert into mccullum_inns( score,not_out) values (11, 1);
insert into mccullum_inns( score,not_out) values (4, null);
insert into mccullum_inns( score,not_out) values (225, null);
insert into mccullum_inns( score,not_out) values (40, null);
insert into mccullum_inns( score,not_out) values (25, null);
insert into mccullum_inns( score,not_out) values (56, null);
insert into mccullum_inns( score,not_out) values (35, null);
insert into mccullum_inns( score,not_out) values (2, null);
insert into mccullum_inns( score,not_out) values (64, null);
insert into mccullum_inns( score,not_out) values (14, null);
insert into mccullum_inns( score,not_out) values (11, null);
insert into mccullum_inns( score,not_out) values (34, null);
insert into mccullum_inns( score,not_out) values (1, null);
insert into mccullum_inns( score,not_out) values (16, null);
insert into mccullum_inns( score,not_out) values (12, null);
insert into mccullum_inns( score,not_out) values (83, null);
insert into mccullum_inns( score,not_out) values (48, null);
insert into mccullum_inns( score,not_out) values (58, 1);
insert into mccullum_inns( score,not_out) values (61, null);
insert into mccullum_inns( score,not_out) values (5, null);
insert into mccullum_inns( score,not_out) values (31, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (25, null);
insert into mccullum_inns( score,not_out) values (84, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (19, null);
insert into mccullum_inns( score,not_out) values (22, null);
insert into mccullum_inns( score,not_out) values (42, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (23, null);
insert into mccullum_inns( score,not_out) values (68, null);
insert into mccullum_inns( score,not_out) values (13, null);
insert into mccullum_inns( score,not_out) values (4, null);
insert into mccullum_inns( score,not_out) values (35, null);
insert into mccullum_inns( score,not_out) values (7, null);
insert into mccullum_inns( score,not_out) values (51, null);
insert into mccullum_inns( score,not_out) values (13, null);
insert into mccullum_inns( score,not_out) values (11, null);
insert into mccullum_inns( score,not_out) values (74, null);
insert into mccullum_inns( score,not_out) values (69, null);
insert into mccullum_inns( score,not_out) values (38, null);
insert into mccullum_inns( score,not_out) values (67, 1);
insert into mccullum_inns( score,not_out) values (2, null);
insert into mccullum_inns( score,not_out) values (8, null);
insert into mccullum_inns( score,not_out) values (20, null);
insert into mccullum_inns( score,not_out) values (1, null);
insert into mccullum_inns( score,not_out) values (21, null);
insert into mccullum_inns( score,not_out) values (22, null);
insert into mccullum_inns( score,not_out) values (11, null);
insert into mccullum_inns( score,not_out) values (113, null);
insert into mccullum_inns( score,not_out) values (9, null);
insert into mccullum_inns( score,not_out) values (37, null);
insert into mccullum_inns( score,not_out) values (12, null);
insert into mccullum_inns( score,not_out) values (224, null);
insert into mccullum_inns( score,not_out) values (1, null);
insert into mccullum_inns( score,not_out) values (8, null);
insert into mccullum_inns( score,not_out) values (302, null);
insert into mccullum_inns( score,not_out) values (7, null);
insert into mccullum_inns( score,not_out) values (17, null);
insert into mccullum_inns( score,not_out) values (4, null);
insert into mccullum_inns( score,not_out) values (3, null);
insert into mccullum_inns( score,not_out) values (31, null);
insert into mccullum_inns( score,not_out) values (25, null);
insert into mccullum_inns( score,not_out) values (18, null);
insert into mccullum_inns( score,not_out) values (39, null);
insert into mccullum_inns( score,not_out) values (43, null);
insert into mccullum_inns( score,not_out) values (45, null);
insert into mccullum_inns( score,not_out) values (202, null);
insert into mccullum_inns( score,not_out) values (195, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (22, null);
insert into mccullum_inns( score,not_out) values (42, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (41, null);
insert into mccullum_inns( score,not_out) values (55, null);
insert into mccullum_inns( score,not_out) values (6, null);
insert into mccullum_inns( score,not_out) values (80, null);
insert into mccullum_inns( score,not_out) values (27, null);
insert into mccullum_inns( score,not_out) values (4, null);
insert into mccullum_inns( score,not_out) values (20, null);
insert into mccullum_inns( score,not_out) values (75, null);
insert into mccullum_inns( score,not_out) values (17, 1);
insert into mccullum_inns( score,not_out) values (18, null);
insert into mccullum_inns( score,not_out) values (18, null);
insert into mccullum_inns( score,not_out) values (0, null);
insert into mccullum_inns( score,not_out) values (10, null);
insert into mccullum_inns( score,not_out) values (145, null);
insert into mccullum_inns( score,not_out) values (25, null);
commit;

I’ve loaded this into my Oracle 11gXE Enterprise Edition database.

Don’t count on COUNT()

Let’s just check the number of rows in the table :

select count(*), count(score), count(not_out)
from mccullum_inns
/

  COUNT(*) COUNT(SCORE) COUNT(NOT_OUT) 
---------- ------------ --------------
       176          176             9
       

Hmmm, that’s interesting. Whilst there are 176 rows in the table, a count of the NOT_OUT column only returns 9, which is the number of rows with a non-null value in this column.

The fact is that COUNT(*) behaves a bit differently from COUNT(some_column)…

with stick as
(
    select 1 as ball from dual
    union all select 2 from dual
    union all select null from dual
)    
select count(*), count(ball)
from stick
/

COUNT(*)                             COUNT(BALL)
---------- ---------------------------------------
         3                                       2

Tanel Poder provides the explanation as to why this happens here.
Due to this difference in behaviour, you may well consider that COUNT(*) is a completely different function to COUNT(column), at least where NULLS are concerned.

When all else fails, Read the Manual

From very early on, database developers learn to be wary of columns that may contain null values and code accordingly, making frequent use of the NVL function.
However, aggregate functions can prove to be something of a blind spot. This can lead to some interesting results.
Whilst we know ( and can prove) that NULL + anything equals NULL…

select 3 + 1 + 4 + 1 + null as ball
from dual
/

     BALL
----------
         

…if we use an aggregate function…

with stick as 
(
    select 3 as ball from dual
    union all select 1 from dual
    union all select 4 from dual
    union all select 1 from dual
    union all select null from dual
)
select sum(ball) 
from stick
/

SUM(BALL)
----------
         9

…so, calculating an average may well lead to some confusion…

with stick as 
(
    select 3 as ball from dual
    union all select 1 from dual
    union all select 4 from dual
    union all select 1 from dual
    union all select null from dual
)
select avg(ball)
from stick
/

AVG(BALL)
----------
      2.25

…which is not what we would expect given :

with stick as 
(
    select 3 as ball from dual
    union all select 1 from dual
    union all select 4 from dual
    union all select 1 from dual
    union all select null from dual
)
select sum(ball)/count(*) as Average
from stick
/

   AVERAGE
----------
       1.8 

You can see similar behaviour with the MAX and MIN functions :

with stick as 
(
    select 3 as ball from dual
    union all select 1 from dual
    union all select 4 from dual
    union all select 1 from dual
    union all select null from dual
)
select max(ball), min(ball)
from stick
/

 MAX(BALL)  MIN(BALL)
---------- ----------
         4          1

Looking at the documentation, we can see that :

“All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT and REGR_COUNT never return null, but return either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.”

So, if we want our aggregate functions to behave themselves, or at least, behave as we might expect, we need to account for situations where the column on which they are operating may be null.
Returning to COUNT…

select count(nvl(not_out, 0)) 
from mccullum_inns
/

                 COUNT(NVL(NOT_OUT,0))
---------------------------------------
                                    176
                    

Going back to our original task, i.e. finding McCullum’s final batting average, we could do this :

select count(*) as Inns, 
    sum(score) as Runs,
    sum(nvl(not_out,0)) as "Not Outs",
    round(sum(score)/(count(*) - count(not_out)),2) as Average
from mccullum_inns
/

INNS  RUNS   Not Outs AVERAGE
----- ----- ---------- -------
  176  6453          9   38.64
  

However, now we’ve re-learned how nulls are treated by aggregate functions, we could save ourselves a bit of typing…

select count(*) as Inns, 
    sum(score) as Runs,
    count(not_out) as "Not Outs",
    round(sum(score)/(count(*) - count(not_out)),2) as Average
from mccullum_inns
/

INNS  RUNS   Not Outs AVERAGE
----- ----- ---------- -------
  176  6453          9   38.64

Time to draw stumps.


Filed under: Oracle, SQL Tagged: avg, count(*), max, min, null and aggregate functions, NVL, sum

ADF Hidden Gem - Export Collection Listener

Andrejus Baranovski - Mon, 2016-05-16 12:02
How many times you complained about ADF export collection listener generated output? There are two options for the output - CSV and excelHTML. Both of them are not really Excel friendly (Excel complains, each time when such file is opened) and produced output lacks formatting. Luckily there is a way to specify custom formatter for ADF export collection listener and set your own output type. In this way we can produce better and customized output file for Excel, you can construct Excel document with different formatting and layout.

Demo application with custom formatter for ADF export collection listener is available on GitHub repository - rs-export-xls. This was implemented by Red Samurai Consulting colleague - Fedor Zymarev. You are free to check-out the source code - use it in your projects and add new formatting features.  It would be great, if you could contribute to the community and commit any improvements - simply request merge approval into master on GitHub repository.

Demo runs with the entry page, which contains ADF UI table with Export all/selected rows options:


This is how Excel output looks like. There is a placeholder for worksheet title, header is highlighted and format is recognized by Excel:


You only need to set custom type (RSExcelExport) for ADF export collection listener, instead selecting excelHTML available by default:


Custom type is not available in the list of options, you should type it:


Format type is defined with Java class registered in oracle.adf.view.rich.export.FormatHandler text file, located in ADF META-INF/services folder:


Formatting implementation logic take place in RSExcelFormatHandle class. We are using Apache POI, Java API for Microsoft documents, to prepare native format for Excel:

Gluent New World #03: Real Time Stream Processing in Modern Enterprises with Gwen Shapira

Tanel Poder - Mon, 2016-05-16 11:01

It’s time to announce the 3rd episode of Gluent New World webinar series! This time Gwen Shapira will talk about Kafka as a key data infrastructure component of a modern enterprise. And I will ask questions from an old database guy’s viewpoint :)

Apache Kafka and Real Time Stream Processing

Speaker:

  • Gwen Shapira (Confluent)
  • Gwen is a system architect at Confluent helping customers achieve
    success with their Apache Kafka implementation. She has 15 years of
    experience working with code and customers to build scalable data
    architectures, integrating relational and big data technologies. She
    currently specializes in building real-time reliable data processing
    pipelines using Apache Kafka. Gwen is an Oracle Ace Director, an
    author of “Hadoop Application Architectures”, and a frequent presenter
    at industry conferences. Gwen is also a committer on the Apache Kafka
    and Apache Sqoop projects. When Gwen isn’t coding or building data
    pipelines, you can find her pedaling on her bike exploring the roads
    and trails of California, and beyond.

Time:

Abstract:

  • Modern businesses have data at their core, and this data is
    changing continuously. How can we harness this torrent of continuously
    changing data in real time? The answer is stream processing, and one
    system that has become a core hub for streaming data is Apache Kafka.This presentation will give a brief introduction to Apache Kafka and
    describe it’s usage as a platform for streaming data. It will explain
    how Kafka serves as a foundation for both streaming data pipelines and
    applications that consume and process real-time data streams. It will
    introduce some of the newer components of Kafka that help make this
    possible, including Kafka Connect, framework for capturing continuous
    data streams, and Kafka Streams, a lightweight stream processing
    library. Finally it will describe some of our favorite use-cases of
    stream processing and how they solved interesting data scalability
    challenges.

Register:

See you soon!

NB! If you want to move to the "New World" - offload your data and workloads to Hadoop, without having to re-write your existing applications - check out Gluent. We are making history! ;-)

Related Posts

Characterset Conversion Conundrums

Pythian Group - Mon, 2016-05-16 10:30

Every now and then a database needs to be migrated from one characterset to another. The driver behind this is ususally to either synchronize all databases to a single standard or to support new characters or symbols like €,

Categories: DBA Blogs

Taming the Paper Tiger with Oracle Forms Recognition

WebCenter Team - Mon, 2016-05-16 10:16

Author: Dwayne Parkinson – Solution Architect, TEAM Informatics

tiger512We all like to believe that technology makes everything somehow better, right? Our parents’ watches tell time and maybe the date while mine gives me the weather, tells me when to get up and exercise, tracks calories, integrates with email and sends text messages. Seemingly everything from our refrigerator to our garage door opener to the latest and greatest ERP system is connected to our phones and devices these days. Yet amidst all this technology and integration, lurking somewhere in the bowels of virtually every company and organization is a massive pile of paper.

They say the first step to fixing a problem is to admit that we have one.  So let’s admit what the problem is: paper.  It used to be that paper went back and forth between companies as a record of the various transactions.  If you placed an order, it was on paper.  When you got a shipment, there was more paper.  When you needed to pay, there was a paper invoice.  And up until recently, when you paid, someone somewhere was potentially issued a paper check. With the advent of the Electronic Data Interchange (EDI), electronic transactions thankfully became the standard – or so we’d like to think.  What’s really happened however is that only those transactions between electronically astute organizations have migrated to EDI, while smaller organizations and those facing significant technology challenges have unfortunately remained largely paper-based.

Read the article in it's entirety here.

Replication between Tungsten clusters

Pythian Group - Mon, 2016-05-16 09:52
Replication between Tungsten clusters

The process I will describe in this post will allow you to configure replication between Tungsten clusters. The most common use case I have seen for this is a dedicated ETL cluster.

The setup will look like this:

 

Replication between Tungsten clusters

So we will have a composite datasource (compositeprod) composed of east and west clusters, and two ETL clusters, one on each side.

The described setup allows failover or switchover within a single datacenter (e.g. db1.east -> db2.east) or to the Disaster Recovery Site, (e.g. db1.east -> db1.west) for the core cluster.

At the time of this article’s publication it is not possible to replicate between two composite clusters, so each ETL cluster needs to be standalone. This feature is expected for Tungsten 5.0 release.

We will install a standalone replicator on the ETL hosts to bring data in from the core cluster. This assumes you have already completed the required prerequisites.

Configuration

Using the .ini files installation method, the config file for the core cluster hosts would be:

[defaults]
application-password=****
application-port=9999
application-user=app
home-directory=/opt/continuent
mysql-connectorj-path=/opt/continuent/connector-java/mysql-connector-java-5.1.24/mysql-connector-java-5.1.24-bin.jar
replication-password=****
replication-port=3306
replication-user=tungsten
start-and-report=true

[east]
master=db1.east
members=db1.east,db2.east,db3.east

[west]
master=db1.west
members=db1.west,db2.west,db3.west

[compositeprod]
dataservice-composite-datasources=east,west

The config file for etl.east hosts would be:

[defaults]
application-password=****
application-port=9999
application-user=etl
home-directory=/opt/continuent
mysql-connectorj-path=/opt/continuent/connector-java/mysql-connector-java-5.1.24/mysql-connector-java-5.1.24-bin.jar
replication-password=****
replication-port=3306
replication-user=tungsten
start-and-report=true

[defaults.replicator]
home-directory=/opt/continuent/replicator
executable-prefix=tr
rmi-port=10002
thl-port=2113
property=replicator.log.slave.updates=false

[etl_east]
topology=clustered
master=etl1.east
members=etl1.east,etl2.east,etl3.east

[compositeprod]
topology=cluster-alias
master=db1.east
members=db1.east,db2.east,db3.east

[core_etl_east]
topology=cluster-slave
master-dataservice=compositeprod
slave-dataservice=etl_east

For etl.west hosts:

[defaults]
application-password=****
application-port=9999
application-user=etl
home-directory=/opt/continuent
mysql-connectorj-path=/opt/continuent/connector-java/mysql-connector-java-5.1.24/mysql-connector-java-5.1.24-bin.jar
replication-password=****
replication-port=3306
replication-user=tungsten
start-and-report=true

[defaults.replicator]
home-directory=/opt/continuent/replicator
executable-prefix=tr
rmi-port=10002
thl-port=2113
property=replicator.log.slave.updates=false

[etl_west]
topology=clustered
master=etl1.west
members=etl1.west,etl2.west,etl3.west

[compositeprod]
topology=cluster-alias
master=db1.west
members=db1.west,db2.west,db3.west

[core_etl_west]
topology=cluster-slave
master-dataservice=compositeprod
slave-dataservice=etl_west
Installation

Now we need to install the cluster by running the Tungsten installer on each core and ETL host:

./install/continuent-tungsten-4.0.2-6072082/tools/tpm install

Also on ETL hosts only, we need to install the standalone replicator:

./install/tungsten-replicator-4.0.0-18/tools/tpm install

Each ETL host will end up with 2 different replicator processes:

  1. The replicator that brings data in from the core cluster
  2. The replicator that reads data from the ETL cluster master to replicate to the ETL slaves

With the ‘executable-prefix‘ option, it is easy to reference each one:

  1. tr_trepctl status will show info from the core -> ETL replicator
  2. trepctl status will show info from the ETL cluster replicator
Tips & tricks

One thing to keep in mind is that for aliases to actually work, we need to add the folllowing line to .bashrc:

. "/opt/continuent/replicator/share/env.sh"

as there is a bug where the installer only adds the aliases from the cluster installation, and ignores the ones from the standalone replicator installation.

The [defaults.replicator] tag is helpful to pass options to the standalone replicator (it is ignored by the cluster installation), as the same tungsten.ini file will be read by both the cluster and standalone replicator products.

In this case I am using it to specify different THL and RMI ports, so the standalone replicator does not collide with the cluster replicator.

We also need to tell the core -> ETL replicator not to log its updates to the binlog, otherwise the changes will be picked up by the ETL cluster replicator as well, and end up being applied two times on the ETL slaves, resulting in consistency errors.

Do you have any questions about replication between Tungsten clusters? Let me know in the comments!

 

Categories: DBA Blogs

APEX plugin: make tabular report responsive

Tony Andrews - Mon, 2016-05-16 07:45
I often have to build APEX applications that are responsive to the size of the screen they are running on - from desktops down to mobile phones.  While this can be achieved quite easily using a modern responsive theme, reports are often a problem.  For example, this report looks fine on a desktop: ... but gets truncated on a mobile: Here I'm using the APEX 5.0 Universal Theme, which at least Tony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com7http://tonyandrews.blogspot.com/2016/05/apex-plugin-make-tabular-report.html

How to Monetize any Database with Bitcoin

Gerger Consulting - Mon, 2016-05-16 07:14
We have a webinar on May 18th! Attend our free webinar, presented by Balaji S. Srinivasan, one of the most prominent figures in the Bitcoin community, and learn how you can monetize any database with Bitcoin.

Many thanks to 135+ people who already signed up! Register here.


Categories: Development

IBM Bluemix Liberty Buildpack and Spring Boot Applications for "Auto-Scale" and "Monitoring" Services

Pas Apicella - Mon, 2016-05-16 06:08
Working with a customer last week we determined that Spring Boot applications using Liberty Buildpack failed to allow the "Auto-Scale" service to show up "Throughput" metrics and essentially mean we couldn't scale out or down application instances using Throughput metrics.

https://console.ng.bluemix.net/docs/runtimes/liberty/index.html

It turns out the Agent within the IBM Liberty buildpack isn't quite picking up application WAR files created using Spring Boot and hence "Monitoring and Analytics" and "Auto-Scale" service have limited functionality.

IBM Bluemix Services



To solve this we simply need to select the correct generated WAR file. Spring Boot along with Maven produces two WAR files as shown below when the application packaged as a WAR is packaged using "mvn package"

Eg:

-rw-r--r--   1 pasapicella  staff  12341953 13 May 14:17 demo-sb-war-0.0.1-SNAPSHOT.war.original
-rw-r--r--   1 pasapicella  staff  17229369 13 May 14:17 demo-sb-war-0.0.1-SNAPSHOT.war

The WAR file "demo-sb-war-0.0.1-SNAPSHOT.war.original" is the one that is originally generated using maven and it's this file which we should push to IBM Bluemix using the IBM Liberty buildpack
 
If there’s a Main-Class defined in the manifest it attempts to start it up using the war file, thats the WAR file "demo-sb-war-0.0.1-SNAPSHOT.war". Once you push the version of our war without the Main-Class Manifest entry defined "demo-sb-war-0.0.1-SNAPSHOT.war.original" everything starts up correctly and is now happily recording both throughput and monitoring. If the WAR has a Main-Class app, the buildpack will only install the JDK for the app, and the app embeds Tomcat (by default for Spring Boot), so essentially the app is running on Tomcat; if it's a WAR app without Main-Class manifest entry, the buildpack installs Liberty as well and the app will run on Liberty.

Simply push the correct WAR file and your Spring Boot WAR files using Liberty Buildpack can take advantage of the Liberty Buildpack agent for extra "Monitoring" and "Auto-Scale" service support.

Screen Shots for Monitoring and Analytics service with Spring Boot WAR file




To verify this you can use the Basic Spring Boot Application at the following URL. It simply exposes one REST end point service displaying "helloworld".

https://github.com/papicella/SpringBootWARDemo

http://feeds.feedburner.com/TheBlasFromPas
Categories: Fusion Middleware

Cursor_Sharing problem

Jonathan Lewis - Mon, 2016-05-16 05:49

Here’s a possible bug (though maybe “not a bug”) that came up over the weekend on the OTN database forum. An application generating lots of “literal string” SQL was tested with cursor_sharing set to force. This successfully forced the use of bind variable substitution, but a particular type of simple insert statement started generating very large numbers of child cursors – introducing a lot of mutex waits and library cache contention. Here’s a (substituted) statement that was offered as an example of the problem:


INSERT INTO schema.tableName (column1,columns2,..)
VALUES (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5",:"SYS_B_6",timestamp:"SYS_B_7",SYSTIMESTAMP,:"SYS_B_8")

Note particularly the slightly odd looking detail: timestamp:”SYS_B_7″; this is how bind variable substitution looks if you’ve used the “compact” ANSI mechanism for handling datetime literals. We were told, in fact, that the tables had only number, varchar2, and date columns – so it looks a little suspicious when see timestamp values being inserted but the implied coercion wasn’t the source of the problem. Here’s all it takes to see the problem (tested only on 12.1.0.2):


rem
rem     Script:         ansi_datetime_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2016
rem

create table t1 (
        n1      number(8,0),
        v1      varchar2(10),
        d1      date,
        t1      timestamp
);

insert into t1 values(-1,'x',sysdate, systimestamp);

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1'
        );
end;
/

set serveroutput off
alter session set cursor_sharing = force;

prompt  ============================
prompt  Testing DATE literals
prompt  Expect to see child number 2
prompt  ============================

insert into t1 values(1, 'A', date'2016-01-01', null);
insert into t1 values(1, 'A', date'2016-01-02', null);
insert into t1 values(1, 'A', date'2016-01-03', null);

select * from table(dbms_xplan.display_cursor(null,null,'-note -plan_hash'));

prompt  ============================
prompt  Testing TIMESTAMP literals
prompt  Expect to see child number 2
prompt  ============================

insert into t1 values(1, 'A', null, timestamp'2016-01-01 00:00:00');
insert into t1 values(1, 'A', null, timestamp'2016-01-02 00:00:00');
insert into t1 values(1, 'A', null, timestamp'2016-01-03 00:00:00');

select * from table(dbms_xplan.display_cursor(null,null,'-note -plan_hash'));

prompt  ===============================
prompt  Need privilege to see this view
prompt  Pre-coded for the sql_ids above
prompt  ===============================

break on sql_id skip 1

select
        sql_id, child_number, hash_match_failed
from
        v$sql_shared_cursor
where
        sql_id in ('58udhcm270bhn', 'gssz5cbnt7mgn')
order by
        sql_id, child_number
;

A couple of points – there are several more tests in the script demonstrating things that do NOT cause multiple child cursors to appear. I probably didn’t cover all the options that I could have covered but I hit a number of common cases to check that it wasn’t simply that cursor_sharing being severely broken in 12c. I’ve also allowed a side effect to demonstrate the presence of multiple child cursors rather than explcitly listing the child cursors. If the three statements (of each type) had produced shareable cursors then the child number reported by dbms_xplan.display_cursor() would have been zero in both cases. Here are the actual outputs:


SQL_ID  58udhcm270bhn, child number 2
-------------------------------------
insert into t1 values(:"SYS_B_0", :"SYS_B_1", date:"SYS_B_2", null)

-------------------------------------------------
| Id  | Operation                | Name | Cost  |
-------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |       |
-------------------------------------------------


SQL_ID  gssz5cbnt7mgn, child number 2
-------------------------------------
insert into t1 values(:"SYS_B_0", :"SYS_B_1", null, timestamp:"SYS_B_2")


-------------------------------------------------
| Id  | Operation                | Name | Cost  |
-------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |       |
-------------------------------------------------


And, having pre-coded the script with the SQL_IDs of the two guilty statements, here’s the output identifying the cause of the failure to share from v$sql_shared_cursor:


SQL_ID        CHILD_NUMBER H
------------- ------------ -
58udhcm270bhn            0 N
                         1 Y
                         2 Y

gssz5cbnt7mgn            0 N
                         1 Y
                         2 Y

There is a bug on MoS relating to timestamp columns and failure to share cursors – it doesn’t really look like the same problem but it could be related in some way: Bug 13407937 : HIGH VERSION COUNT FOR INSERT WITH CURSOR_SHARING=FORCE AND TIMESTAMP COLUMN. It’s described as “not a bug” :(


Data Integration Tips: ODI 12c – Who Changed My Table Names?

Rittman Mead Consulting - Mon, 2016-05-16 02:05

It’s Sunday night (well, technically Monday morning now), and we have just enough time for another one of my Data Integration Tips. This one, revolving around Oracle Data Integrator 12c, has been on my mind for some time now, so I figured we better just get it out there. Imagine this; you’ve upgraded from Oracle Data Integrator (ODI) 11g to ODI 12c and executed the first test Mapping. But hey…what happened to my C$ table names? And wait a minute, the I$ tables look a bit different as well! Let’s dive in and uncover the truth, shall we?

The Scenario

In the 11g version of Oracle Data Integrator, we could only load one single target table per mapping (or Interface, as they were called way back then). Now, in ODI 12c, we have the new flow-based mapping paradigm, allowing us to choose our sources, apply different components (joins, filters, pivot, aggregates, etc), and load as many targets as we like. Quite an upgrade, if you ask me! But with this redesign comes some minor, albeit important, change under the covers. The temporary tables used to store data that is loaded from a source, across the network, and into a target, known as C$ or Loading tables, are generated by the ODI Substitution API called from within a Knowledge Module step. The underlying code that creates the temp tables has changed to output a different format for the table names. What exactly does that mean for our C$ tables? And why do we care?

In the beginning, the C$ tables were named for the target table. If there were multiple source tables, the C$ name would be indexed – C$_0, C$_1, etc. For example, if your source to target mapping looked like this: F0010 —> ACCOUNT_MASTERS, then the loading table was named C$_0ACCOUNT_MASTERS. If there was a join between two tables executed on staging, then the second loading table would be named C$_1ACCOUNT_MASTERS.

So…what changed in ODI 12c? Let’s take a look at a few mapping examples.

one-to-one-mapping

In this mapping, the C$ table is now named after the source datastore. Instead of C$_0ACCOUNT_MASTERS, we have C$_0F0010. That can be an interesting challenge for data warehouse teams who rely on specific naming conventions for debugging, monitoring, etc. Ok, so let’s take a look at another example.

dataset-mapping

Ok, so normally I wouldn’t work with a Dataset component, but this is a look at the Mapping after an upgrade from ODI 11g. I could use the Convert to Flow feature, but as you’ll find out by reading on, it wouldn’t help with our temp table naming issues. In this example, the loading table is named C$_0DEFAULT. What’s this “default” business all about? Well, that is derived from the Dataset Component name. I must say, that’s much worse than just switching from the target table name to the source name. Yikes! Ok, one final test…

filter-mapping

Oh boy. In this case, the resulting table is called C$_0FILTER. The name? It’s based on the Filter Component name. I’m sensing a pattern here. Basically the name of any component that is mapped to the target table, and in the physical design mapped to an access point, will be used to generate the C$ loading table name.

Digging a bit deeper into the Knowledge Modules, we find that the create loading object step of the KMs invokes the following method.

<%=odiRef.getTable("L", "COLL_NAME", "W")%>

The COLL_NAME refers to the loading table name, while the other options “L” & “W” refer to the format and source of the schema name that will be prefixed to the resulting table name. As mentioned previously, this method would return the target table name with the C$ prefix. Now, it returns the source table or component name for the specific source dataset that is being extracted and loaded into the target work schema. Here’s another way to show these differences in naming conventions:

c$-table-naming

This image is based on a specific use case in which the Oracle Data Integrator customer was using the C$ tables in debugging. As you can see, the naming really doesn’t lend itself to understanding which target the C$ table was created to load.

Here’s the Tip…

Now that we understand what drives the C$ table name, we can workaround the issue. While the use case above is somewhat unique to folks who have upgraded from Oracle Data Integrator 11g, the use of components rather than tables in the naming of temporary objects can be quite confusing. We can easily change the output of <%=odiRef.getTable(“L”, “COLL_NAME”, “W”)%> by changing the component alias, or name, within the mapping. That’s an easy enough task for just a few mappings, but when you’ve upgraded hundreds, or even thousands, to ODI 12c – you’re in for some serious manual labor. Unless, of course, you dive into some Groovy script and the ODI SDK.

In the code snippet below, we first find the mapping we’re interested in editing. Then, work our way through the different components that may exist on the mapping and need a name change. This code was written specifically to handle Dataset, Filter, and source Datastore components only. Any additional components would need to be added to the list or, better yet, a different approach written in Groovy to find the last component before the final target Datastore. Hmm, next DI Tip?

Mapping mapToEdit = mapfinder.findByName(folder, mapName)

try {
 //fix filter name.
 filterComp = mapToEdit.findComponent("Filter") //find the filter named Filter.

 if(filterComp != null) {
  filterComp.setName(targName)
  out.println(mapName + " filter renamed.")
 } else {
  //fix dataset name.
  datasetComp = mapToEdit.findComponent("Default_DS") //find the dataset named Default.

  if(datasetComp != null) {
   datasetComp.setName(targName)
   out.println(mapName + " dataset renamed.")
  } else {
   //fix source datastore name.
   sources = mapToEdit.getSources()
   for(sourceComp in sources) {
    datastoreComp = sourceComp
   }

   datastoreComp.setName(targName)
   out.println(mapName + " source datastore renamed.")
  }
 }
} catch(MapComponentException e) {
 out.println e.toString()
}
tme.persist(mapToEdit)

The “targName” variable in this snippet is set to the target datastore name concatenated with the target data server name. That’s a specific use case, but the key takeaway is that the component name cannot be set to the target datastore name exactly. There must be a slight difference, since components cannot have the exact same name within a single mapping. Another caveat, if we had multiple target tables, this approach may not work out so well. But, again, coming from ODI 11g that’s a non-issue. This code can be run against a project, project folder, or even individual mappings, making it an easy way to change thousands of objects in seconds. Man I love Groovy and the ODI SDK!

That seems to solve our naming issue by modifying our loading table name into something more meaningful than C$_0FILTER. Groovy has come to the rescue and allowed us to batch change mappings in an instant. It seems we’ve completed this Data Integration Tip successfully.

But Wait, There’s More

I did mention earlier that the I$ table had an issue as well. Oh brother. The I$, or integration table, is the result of the mapping logic stored as a dataset in the I$ table just prior to loading into the final target. There is only a slight change to the ODI Substitution API method used in generating the integration table name, but again, just slight enough to bother processes built around the naming conventions.

In the past, the integration table name was based on the target table alias. But now in the latest version of ODI, the I$ table name is built based on the target datastore resource name. Again, this could potentially be problematic for those customers interested in using a different logical name for a physical target table. Something more readable, perhaps. Or maybe removing redundant characters that exist in all tables. Either way, we have to deal with a slight change in the code.

In researching a way to modify the way the I$ table is created, I came across an interesting issue. The call to odiRef.getTableName(“INT_SHORT_NAME”) is supposed to return the integration table name alone, without any schema prefix attached to it. So in the previous example, when our target table was named ACCOUNT_MASTERS, the resulting table should have been I$_ACCOUNT_MASTERS. The original call to odiRef.getTable(“L”, “INT_NAME”, “W”) actually returns ODISTAGE.I$_JDE_ACCOUNT_MASTERS, based on the resource name of the datastore object and prepending the work schema name. Using the INT_SHORT_NAME, we expected a different result. But instead, the code generated a name like this: %INT_PRFJDE_ACCOUNT_MASTERS. This must be a bug in ODI 12.2.1, but I haven’t found it yet in My Oracle Support.

To work around this whole mess, we just searched for the work schema name and removed it from the table name, while replacing the unnecessary characters as well. All of this was completed using Java within the Knowledge Module steps. In the “Define Java Variable” step, which was custom added to setup Java variables in the KM, the below function was included. It lets you perform a substring while specifying length as a parameter. Found and repurposed from here.

String mySubString(String myString, int start, int length) {
 return myString.substring(start, Math.min(start + length, myString.length()));

Then, in the “Set Java Variable” task, again custom, the code below was added to create the integration table name:

ITABLENAME ="<%=odiRef.getTable("L", "INT_NAME", "W")%>".replace("_JDE_","_");
ITABLENAME = mySubString(BMINAME, BMINAME.indexOf(".") + 1, 26);

The end result was a temporary integration table named I$_ACCOUNT_MASTERS, just as we were planning.

So there you have it, another Data Integration Tip shared with the ODI public. Hopefully this, or one of the other many DITips shared by Rittman Mead, can help you solve one of your challenging problems with Oracle Data Integration solutions. Please let me know of any Data Integration Tips you may need solved by commenting below or emailing me at michael.rainey@rittmanmead.com. And if you need a bit more help with your Data Integration setup, feel free to reach out and the experts at Rittman Mead will be glad to help!

The post Data Integration Tips: ODI 12c – Who Changed My Table Names? appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Database as a Virtual Image

Pat Shuff - Mon, 2016-05-16 01:07
The question that we are going to dive into this week is what does it really mean to be platform as a service vs infrastructure as a service. Why not go to Amazon and spin up an EC2 instance or search for an Oracle provided AMI on Amazon or Virtual Image on Azure? What benefit do I get from PaaS? To answer that we need to look at the key differences. Let's look at the two options when you provision a database in the Oracle DBaaS. When you provision a database you have the option of service levels; Database Cloud Service and Database Cloud Service - Virtual Image. We looked at the provisioning of the cloud service. It provisions a database, creates the network rules, and spins up an instance for us. What happens when we select Virtual Image?

The release and version screens are the same. We selected 12c for the release and High Performance for the version. Note that the questions are much simpler. We are not asked about how much storage. We are not asked for an SID or sys password. We are not asked about backup options. We are not given the option of DataGuard, RAC, or GoldenGate. We are only asked to name the instance, pick a compute shape, and provide an ssh public key.

This seems much simpler and better. Unfortunately, this isn't true. What happens from here is that a Linux 6.6 instance is created and a tarball is dropped into a staging area. The database is not provisioned. The file system is not prepared. The network ports are not configured and enabled. True, the virtual instance creation only takes a few minutes but all we are doing is provisioning a Linux instance and copying a tarball into a directory. Details on the installation process can be found at Database Cloud Installation - Virtual Image Documentation.

If you look at the detailed information about a system that is being created with a virtual image and a system that is being created as a service there are vast differences.

The first key difference is the amount of information displayed. Both instances have the same edition, Enterprise Edition - High Performance. Both will display this difference in the database as well as in the banner if asked what version the database is. The Service Level is different with the virtual image displayed as part of the service level. This effects the billing. The virtual image is a lower cost because less is done for you.

Product (per OCPU) General Purpose High-Memory Per Month Per Hour Per Month Per Hour Standard Edition Service $600 $1.008 $700 $1.176 Enterprise Edition Service $3,000 $5.040 $3,100 $5.208 High Performance Service $4,000 $6.720 $4,100 $6.888 Extreme Performance Service $5,000 $8.401 $5,100 $8.569

Virtual Image Product (per OCPU) General Purpose High-Memory Per Month Per Hour Per Month Per Hour Standard Edition Service $400 $0.672 $500 $0.840 Enterprise Edition Service $1,500 $2.520 $1,600 $2.688 High Performance Service $2,000 $3.360 $2,100 $3.528 Extreme Performance Service $3,000 $5.040 $3,100 $5.208

The only other information that we get from the management screen is that the instance comsumes 30 GB rather than 100 GB that the database service instance consumes. Note that the database service instance also has the container name and a connection string for connecting to the database. Both will eventually show an ip address and we should look into the operating system to see the differences. The menu to the right of the instance is also different. If we look at the virtual machine instance we only see ssh access, access rules, and deletion of the instance as options.

The ssh access allows us to upload the public key or look at the existing public key that is used to access the instance. The access rules takes us to a new screen that shows the security rules that have been defined for this instance, which is only ssh and nothing else.

If we look at a database as a service instance, the menu is different and allows us to look at things like the DBaaS Monitor, APEX, Enterprise Manager monitor, as well as the ssh and access rules.

Note that the database as a service instance has a lot more security rules defined with most of them being disabled. We can open up ports 80, 443, 4848, 1158, 5500, and 1521. We don't have to define these rules, just enable them if we are accessing them from a whitelist, ip address range, or public internet.

Once we connect to both instances we can see that both are running

Linux hostname 3.8.13-68.2.2.2.el6uek.x86_64 #2 SMP Fri Jun 19 16:29:40 PDT 2015  x86_64 x86_64 x86_64 GNU/Linux
We can see that the file system is different with the /u01, /u02, /u03, and /u04 partitions not mounted in the screen shots below.

If we look at the installation instructions we see that we have to create the /u01, /u02, /u03, and /u04 disks by hand. These are not created for us. We also need to create a logical volume as well as creating the storage services. Step one is to scale up the service by adding a disk. We need to grow the existing file system by first attaching a logical volume then laying out/expanding the logical volume that we have. Note that we can exactly mirror our on-premise system at this point. If we put everything into a 1 TB /u01 partition and blend the log files and data files into one disk (not really recommended) we can do this.

To add the /u01 disk we need to scale up the service and add storage. Note that we only can add a raw disk and can not grow the data volume as we can with the database service.

Note that this scale up does require a reboot of the service. We have the option of adding one logical unit or a full 1 TB disk then partitioning it or we can add the different volumes into different disks. The drawback of doing this is that the way that attached storage is charged is $50/TB/month so adding four disks that consume 20 GB each will consume $200/month because we are allocated the full 1 TB even though we just allocate 20 GB on each disk. We do not subdivide the disk when it is attached and are charged on a per TB basis and not a per GB basis. To save money it is recommended to allocate a full TB rather than a smaller amount. To improve performance and reliability it is recommended to allocate multiple disks and stripe data across multiple spindles and logical units. This can be done at the logical volume management part of disk management detailed in the documentation in provisioning the virtual image instance.

We can look at the logical volume configuration with the lvm pvdisplay, lvm vgdisplay, and lvm lvdisplay. This allows us to look at the physical volume mapping to map physical volumes to logical unit numbers, look at logical volumes for mirroring and stripping options, and volume group options which gets mapped to the data, reco, and fra areas.

Once our instance has rebooted we note that we added /dev/xvdc which is 21.5 GB in size. After we format this disk it partitions down to a 20 GB disk as we asked. If we add a second disk we will get /dev/xvdd and can map these two new disks into a logical volume that we can map to /u01/and /u02. A nicer command to use to look at this is the lsblk command which does not require elevated root privileges to run.

Once we go through the mapping of the /u01, /u02, /u03, and /u04 disks (the documentation only goes into single disks with no mirroring to mount /u01 and /u02) we can expand the binary bits located in /scratch/db. There are two files in this directory, db12102_bits.tar.gz and db12102_se2bits.tar.gz. These are the enterprise edition and standard edition versions of the database.

We are not going to go through the full installation but look at some of the key differences between IaaS with a tarball (or EC2 with an AMI) and a DBaaS installation. The primary delta is that the database is fully configured and ready to run in about an hour with DBaaS. With IaaS we need to create and mount a file system, untar and install the database, configure network ports, define security rules, and write scripts to automatically start the database upon restarting the operating system. We loose the menu items in the management page to look at the DBaaS Monitor, Enterprise Manager monitor, and Application Express interface. We also loose the patching options that appear in the DBaaS management screen. We loose the automated backup and database instance and PDB creation as is done with the DBaaS.

In summary, the PaaS/DBaaS provisioning in not only a shortcut but removes manual steps in configuring the service as well as daily operations. We could have just as easily provisioned a compute service, attached storage, downloaded the tarball that we want to use from edelivery.oracle.com. The key reasons that we don't want to do this are first pricing and second patching. If we provision a virtual image of database as a service the operating system is ready to accept the tarball and we don't need to install the odbc drivers and other kernel modules. We also get to lease the database on an hourly or monthly basis rather than purchasing a perpetual license to run on our compute instance.

Up next, selecting a pre-configured AMI on Amazon and running it in AWS compared to a virtual image on the Oracle Public Cloud.

Links for 2016-05-15 [del.icio.us]

Categories: DBA Blogs

Six Months with the iPad Pro

Oracle AppsLab - Sun, 2016-05-15 22:26
My first session with the iPad Pro

My first session with the iPad Pro

At first I was skeptical. I was perfectly happy with my iPad Air and the Pro seemed too big and too expensive. Six months later I wouldn’t dream of going back. The iPad Pro has become my primary computing device.

Does the Pro eliminate the need for a laptop or desktop? Almost, but for me not quite yet. I still need my Mac Air for NodeBox coding and a few other things; since they are both exactly the same size I now carry them together in a messenger bag.

iPad Pro and Mac Air share the same bag

iPad Pro and Mac Air share the same bag

The Pro is lighter than it looks and, with a little practice, balances easily on my lap. It fits perfectly on an airplane tray table.

Flying over Vegas using Apple Maps

Flying over Vegas using Apple Maps

Does the 12.9-inch screen really make that much of a difference? Yes! The effect is surprising; after all, it’s the same size as an ordinary laptop screen. But there is something addictive about holding large, high resolution photos and videos in your hands. I *much* prefer photo editing on the iPad. 3D flyovers in Apple Map are almost like being there.

Coda and Safari sharing the screen

Coda and Safari sharing the screen

The extra screen real estate also makes iOS 9’s split screen feature much more practical. Above is a screenshot of me editing a webpage using Coda. By splitting the screen with Safari, I can update code and instantly see the results as I go.

Bloomberg Professional with picture-in-picture

Bloomberg Professional with picture-in-picture

Enterprise users can see more numbers and charts at once. Bloomberg Professional uses the picture-in-picture feature to let you watch the news while perusing a large portfolio display. WunderStation makes dashboards big enough to get lost in.

WunderStation weather dashboard

WunderStation weather dashboard

For web conferences, a major part of my working life at Oracle, the iPad Pro both exceeds and falls short. The participant experience is superb. When others are presenting screenshots I can lean back in my chair and pinch-zoom to see details I would sometimes miss on my desktop. When videoconferencing I can easily adjust the camera or flip it to point at a whiteboard.

But my options for presenting content from the iPad are still limited. I can present images, but cannot easily pull content from inside other apps. (Zoom lets you share web pages and cloud content on Box, Dropbox or Google Drive, but we are supposed to keep sensitive data inside our firewall.) The one-app-at-a-time iOS model becomes a nuisance in situations like this. Until this limitation is overcome I don’t see desktops and laptops on the endangered species list.

Smart Keyboard and Apple Pencil

Smart Keyboard and Apple Pencil

Accessories

The iPad Pro offers two accessories not available with a normal iPad: a “smart keyboard” that uses the new magnetic connector, and the deceptively simple Apple Pencil.

I tried the keyboard and threw it back. It was perfectly fine but I’m just not a keyboard guy. This may seem odd for someone who spends most of his time writing – I’m typing this blog on the iPad right now – but I have a theory about this that may explain who will adopt tablets in the workplace and how they will be used.

I think there are two types of workers: those who sit bolt upright at their desks and those who slump as close to horizontal as they can get; I am a slumper. And there are two kinds of typists: touch typists who type with their fingers and hunt-and-peckers who type with their eyes; I am a, uh, hunter. This places me squarely in the slumper-hunter quadrant.

Slumper-hunters like me love love love tablets and don’t need no stinking keyboards. The virtual keyboard offers a word tray that guesses my words before I do, lets me slide two fingers across the keyboard to precisely reposition the cursor, and has a dictate button that works surprisingly well.

Touch-slumpers are torn: they love tablets but can’t abide typing on glass; for them the smart keyboard – hard to use while slumping – is an imperfect compromise. Upright-hunters could go either way on the keyboard but may not see the point in using a tablet in the first place. Upright-touchers will insist on the smart keyboard and will not use a tablet without one.

Running Horse by Anna Budovsky

Running Horse by Anna Budovsky

If you are an artist, or even just an inveterate doodler, you must immediately hock your Wacom tablet, toss your other high-end styli, and buy the Apple Pencil (with the full-sized Pro as an accessory). It’s the first stylus that actually works. No more circles with dents and monkey-with-big-stick writing. Your doodles will look natural and your signature will be picture perfect.

The above drawing was done in under sixty seconds by my colleague Anna Budovsky. She had never used the iPad Pro before, had never used the app (Paper), and had never before picked up an Apple Pencil. For someone with talent, the Apple Pencil is a natural.

If you are not an artist you can probably skip the Pencil. It’s a bit of a nuisance to pack around and needs recharging once a week (fast and easy but still a nuisance). I carry one anyway just so I can pretend I’m an artist.

The Future

For now the iPad Pro is just a big iPad (and the new Pro isn’t even big). Most apps don’t treat it any differently yet and some older apps still don’t even fully support it. But I am seeing some early signs this may be starting to change.

The iPad Pro has one other advantage: processing power. Normal iPad apps don’t really need it (except to keep up with the hi-res screen). Some new apps, though, are being written specifically for the Pro and are taking things to a new level.

Fractals generated by Frax HD

Fractals generated by Frax HD

Zooming into infinitely complex fractals is not a business application, but it sure is a test of raw processing power. I’ve been exploring fractals since the eighties and have never seen anything remotely as smooth and deep and effortless as Frax HD. Pinch-zooming forever and changing color schemes with a swirl of your hand is a jaw-dropping experience.

The emerging class of mobile CAD apps, like Shapr3D, are more useful but no less stunning. You would think a CAD app would need not just a desktop machine but also a keyboard on steroids and a 3D mouse. Shapr3D uses the Apple Pencil in ingenious ways to replace all that.

A 3D Doodle using Shapr3D

A 3D Doodle using Shapr3D

Sketch curves and lines with ease and then press down (with a satisfying click) to make inflection points. Wiggle the pencil to change modes (sounds crazy but it works). Use the pencil for drawing and your fingers for stretching – Shapr3D keeps up without faltering. I made the strange but complicated contraption above in my first session with almost no instruction – and had fun doing it.

I hesitate to make any predictions about the transition to tablets in the workplace. But I would recommend keeping an eye on the iPad Pro – it may be a sleeping giant.Possibly Related Posts: