Video : Scalable Sequences in Oracle Database 18c Onward

In today’s video we’ll discuss Scalable Sequences, which were documented for the first time in Oracle 18c.

The video is based on this article.

The star of today’s video is David Peak, who is now working on the Oracle Pandemic Response Systems. This video is a throwback to a hotel we stayed in at SΓ£o Paulo a few years back.

Cheers

Tim…

Vagrant & Docker Builds : APEX 20.2 and other updates

The recent release of APEX 20.2 has triggered a build frenzy.

Vagrant

All my GitHub Vagrant builds that include APEX have been updated to APEX 20.2. The builds themselves are unchanged. This was literally an update to the environment files, so it took longer to test the builds than it did to make the changes.

While I was at it, I did a couple of extra updates. I updated Tomcat to version 9.0.39 on all relevant builds, and updated the optional patch script for the single instance database 19c on OL8 build to use the October 2020 bundle patch. The GI bundle isn’t available yet, so I’ve not altered the OL8 19c RAC build. That will happen soon.

Update: I’ve got the GI bundle patch now, and the OL8 19c RAC build has been updated to use it.

There will of course be more updates to the builds once we get the new versions of AdoptOpenJDK, ORDS and SQLcl, that are probably coming soon.

Packer

I mentioned in my VirtualBox 6.1.16 post I would be updating the oraclebase/oracle-7 and oraclebase/oracle-8 vagrant boxes to include the VirtualBox 6.1.16 guest additions. Those are done now.

Docker

This is pretty much the same as the Vagrant story.

The relevant GitHub Docker builds for Oracle database and ORDS containers have been updated to include APEX 20.2.

I’ve also added Tomcat 9.0.39 to the ORDS builds, and updated the optional patch script for the database 19c on OL8 build to use the October 2020 bundle patch.

Once again, more changes will appear as the new versions of AdoptOpenJDK, ORDS and SQLcl appear.

Conclusion

Automation is awesome! A few minutes and we are bang up to date!

Cheers

Tim…

VirtualBox 6.1.16

VirtualBox 6.1.16 has been released.

The downloads and changelog are in the usual places.

I’ve done the installation on Windows 10, macOS and Oracle Linux 7 hosts with no drama.

I’ll be running new Packer builds for the oraclebase/oracle-7 and oraclebase/oracle-8 vagrant boxes, so they should appear with the new version of the guest additions over the next day or so.

Cheers

Tim…

PS. It seems I didn’t write a blog post about the release of 6.1.14, but I am using it on all my servers. Interesting… I can only assume I was abducted by aliens immediately after upgrading to 6.1.14. It’s the only possible explanation!

Video : Vagrant Oracle Real Application Clusters (RAC) Build

In today’s video we’ll discuss how to build a 2-node RAC setup using Vagrant.

This video is based on the OL8 19c RAC build, but it’s similar to that of the OL7 19c RAC build also. If you don’t have access to the patches from MOS, stick with the OL7 build, as it will work with the 19.3 base release. The GitHub repos are listed here.

If you need some more words to read, you can find descriptions of the builds here, as well as a beginners guide to Vagrant.

The video is a talk through of using the build, not an explanation of each individual step in the build, as that would be a really long video. If you just want a RAC to play with, run the build and you’ll have one. If you want to learn about the steps involved in doing a RAC build, read the scripts that make up the build. Please don’t ask for a GUI step through of a build or I’ll be forced to ask you to read Why no GUI installations anymore? πŸ™‚

The star of today’s video is Neil Chandler, who seems to be doing a bit of plumbing! πŸ™‚

Cheers

Tim…

Video : Temporal Validity in Oracle Database 12c Onward

In today’s video we discuss how Temporal Validity can make querying of effective date ranges simpler in Oracle 12c and beyond.

The video is based on this article.

The syntax looks similar to Flashback Query and Flashback Version Query.

The star of today’s video is a knitted creation by the wife, Debra Lilley. I’m not entirely sure what it is. Maybe a sea squirt? I’m sure I’m going to get into trouble for not knowing, as I’m sure it’s a perfect representation… πŸ™‚

Cheers

Tim…

Update. Debra just told me it’s a Coronavirus… πŸ™‚

Video Conference Equipment Breakdown

Buying equipment can be really confusing because the reviews are very inconsistent, with a mix of 5 star and 1 star ratings. Also a lot depends on your experience level. If you are a beginner, simplicity is probably your number one priority. As you get more experienced, people often want greater levels of control (not me). So I thought I would give out a breakdown of what I use at the moment, just in case it helps anyone who is a bit confused.

I’ve given links to Amazon where possible.

Microphone Stuff

I tried several USB microphones before I got to this setup. I think this was the cheapest I’ve used and it seems to be the most consistent for my voice. I’m sure I could get better quality with an XLR mic and a mixer, but I really don’t need the extra hassle. I just plug this in and start.

I use a wind screen and a pop filter, yet I still get plosive noises all the time. I used to use a separate adjustable pop filter, but I was constantly readjusting it, which drove me mad, so I changed to one attached to the mic.

Backdrop (Green Screen)

Every frame and backdrop I looked at has extremely varied reviews. Most of the frames were described as flimsy in the reviews, so I spent more and got something that was really solid, which is both height adjustable and can adjust from 4 feet (121 cm) to 10 feet (305 cm) in width. The green screen is thick, and is so wide I have it folded double when I’m using the 6 feet width I’m currently using.

  • 3.6Mx2.8M Heavy Duty Backdrop Studio Support System Kit Tripod Adjustable W/Bag : (amazon.co.uk)
  • Neewer 6-Pack Set Heavy Duty Muslin Spring Clamps : (amazon.co.uk)
  • Neewer 9 x 13 feet/2.8 x 4 meters Photography Background Photo Video Studio Fabric Backdrop Background Screen (Green) : (amazon.com) (amazon.co.uk)

It’s early days, but I’m really happy with the result.

I only bought the following brick background because there was a delay with the above green screen, but then the green screen came the next day.

  • Allenjoy White Brick Wall with Gray Wooden Floor Photography Background : (amazon.co.uk)

Lighting

I tried using normal lighting with the green screen and it was OK, but there were green screen artefacts, especially at the creases on the green screen. As a result I decided to get some lights. As soon as these were on and pointing to the green screen, not me, all the artefacts disappeared, even on the heavy creases. These are the lights I got.

WebCam

I checked out the reviews and this webcam seemed to have a good mix of price and features. There are loads to choose from. You could of course use a proper camera if that is your thing, but it’s far too much work for me, so a webcam is fine.

Nothing to complain about here. The quality is fine. It would be even better if I had some reasonable lighting. πŸ™‚

Software

  • Open Broadcaster Software (OBS) Studio : I use this for green screen with my live conference sessions. You can see my basic instructions here. I could use it to put my face into my YouTube videos, but I hate seeing my face in videos.
  • Camtasia : I use this for all my video production for my YouTube channel. I started life on macOS, but later switched to Windows. It works well on both. It is a lot simpler to use than most of the other video editors I tried.

So that’s it. If you’re involved in the video world you will notice it’s all really basic, and most of all really easy to use. Nothing fancy going on here.

Cheers

Tim…

PS. If you only use Zoom or Teams for conferencing, I would suggest using the virtual backgrounds to hide your messy life behind you. You get artefacts on screen, but why would you spend money on all this crap if you can use them for free? πŸ™‚

Open Broadcaster Software (OBS) Studio : First Time Using Green Screen

I’m not sure if you know this, but a lot of people are using video conferencing, and not all video conference tools allow you to use virtual backgrounds. 😱 That’s a bit of a problem if your washing is permanently on display in the background.

I finally took the plunge and ordered a green screen, but how do you go about using it? Fortunately Samuel Nitsche mentioned Open Broadcaster Software (OBS) Studio, so like the obedient sheep that I am, I gave it a go. This post contains some quick notes to get you up and running if you want to try it.

You can download the software from here.

Add Your Camera

  • Open OBS Studio.
  • Click “+” in the “Sources” panel.
  • Pick “Video Capture Device” from the popup menu.
  • Type in the name you want for your device. Using all the powers of my imagination, I decided on “WebCam”.
  • Click the “OK” button.
  • Your face should be on screen now. If you have multiple cameras connected, select your camera in the “Device” dropdown.
  • Click “OK”, and your camera will be in the “Sources” list.

Enable Green Screen (Chroma Key)

  • Right-click on your Source (WebCam) and select the “Filters” option from the popup menu.
  • Click the “+” under the “Effect Filters” panel.
  • Pick the “Chroma Key” option, and on the subsequent dialog click the “OK” button.
  • Select the background colour under the “Key Color Type”. The default is green. It will also allow you to set a specific colour using a colour picker from the background. The default “Green” option worked for me.
  • Adjust the settings until the background is all grey, and you still look clear. The “Similarity” setting seems to make the most difference for me.
  • Once you are happy with the look, click the “Close” button.
  • You should now see yourself on screen with a black background.
  • Position and stretch your image as required. For things like Teams/Zoom meetings, you probably want to make yourself full screen. You can zoom in a little if the edges of your background are showing.

Green Screen Tips:

  • Try to remove big creases from your green screen. Small creases won’t make a big difference.
  • Try to have even lighting on your green screen. Shadows, including any caused by you, will make the green screen less effective. Another tip from Samuel Nitsche.
  • The background images can have a big impact on how good the green screen effect works.
  • Play around until your are happy. It doesn’t have to be perfect!

Add Background Images

  • Click the “+” in the “Sources” panel.
  • Select the “Image” option from the popup menu.
  • Enter a meaningful name for the background image, and click the “OK” button.
  • Click the “Browse” button and pick a background image from your file system.
  • Click the “OK” button.
  • The image will be in the “Sources” list, and it will be higher in the list than your camera.
  • Use the up and down arrows in the source panel to move the image below the camera.
  • You should now be in front of the image of your choice.
  • Add multiple images and use the “eye” icon next to them to switch between them.

Start Using It

  • Click the “Start Virtual Camera” button.
  • In your conference tool (Team, Zoom etc.) select the “OSB Virtual Camera” device as your video input.
  • Everyone will now see your studio quality green screen. πŸ˜‰

As the title says, this is my first time using this, so my suggestions and advice are probably not worth much. πŸ™‚

Cheers

Tim…

PS. I’ve posted a quick breakdown of the equipment I’m currently using here.

Upgrades : You have to do them. When are you going to learn? (TLSv1.2)

Questions:

  • Do you remember when SSLv3 was a thing?
  • Do you remember when everyone disabled SSLv3 on their websites?
  • Do you remember how loads of people running Oracle database version 11.2.0.2 and lower cried because all their database callouts failed?
  • Do you remember how they were all forced to patch to 11.2.0.3 or 11.2.0.4 to get support for TLS?
  • Do you remember thinking, I’ll never let something like that happen again?

I’m so sick of saying this. I know I sound like a broken record, but it’s like I’m living in the movie Groundhog Day.

There is no such thing as standing still in tech. It’s like swimming upstream in a river. It takes work to remain stationary. The minute you stop for a rest you are actually moving backwards. I’m sure your next response is,

“But Tim, if it ain’t broke, don’t fix it!”

The minute you stop patching and upgrading, your application is already broken. Yesterday you had an up-to-date system. Today you don’t. You have stopped, but the world around you continued to move on, and sometimes what they do will have a direct impact on you.

The security folks have been complaining about TLSv1.0 and TLSx1.1 for ages, but we are now in the position where the world and their dog are switching off those protocols, and the “we don’t need no stinking patches or upgrades” brigade are pissing and moaning again.

You knew this was going to happen. You had plenty of warning. It is your fault things are now failing. The bad decisions you made have led you to this point, so stop blaming other people. IT IS YOUR FAULT!

Where do you go from here?

First things first, start planning your patch cycles and upgrade cycles. That isn’t a “one time and done” plan. That is from now until forever. You’ve got to keep your server operating systems and software up to date.

If you can’t cope with that, then move to a cloud service that will patch your shit for you!

I know upgrades aren’t necessarily a quick fix, as they need some planning, so you will need some sticking plasters to get your through the immediate issues. Things to consider are:

  • Your load balancers and/or reverse proxies can hide some of your crap from the outside world. You can support TLSv1.2+ between the client and the reverse proxy, then drop down to a less secure protocol between your reverse proxy and your servers.
  • You can do a similar thing with database callouts to the outside world. Use an internal proxy between you and the external resource. The connection between your proxy and the outside world will speak on TLSv1.2+, but the callout from the database to your proxy will speak using a protocol your database can cope with.

These are not “fixes”. They are crappy sticking-plaster solutions to hide your incompetence. You need to fix your weak infrastructure, but these will buy you some time…

I don’t really care if you think you have a compelling counter argument, because I’m still going to scream “WRONG” at you. If you don’t think patching and upgrades are important, please quit your tech job and go be incompetent somewhere else. Have a nice life and don’t let the door hit you on the ass on your way out!

Cheers

Tim…

PS. You know this is going to happen again soon, when the world decides that anything less than TLSv1.3 is evil.

Video : Multitenant : Disk I/O (IOPS, MBPS) Resource Management for Pluggable Databases (PDBs)

In today’s video we’ll discuss how Resource Manager allows us to manage the disk I/O (IOPS, MBPS) usage in PDBs. This can be useful to stop a small number of PDBs using all disk IOPS and/or bandwidth on the server.

The video is based on the following article.

You might also find these useful.

The star of the today’s video is my dad Graham Wood, who is now living a life of leisure. Unfortunately the travel restrictions mean I won’t be able to visit him this year. πŸ™

Cheers

Tim…

Data Pump : API for PL/SQL (DBMS_DATAPUMP) and REST (ORDS)

I’m on holiday, so it’s a lot easier to tick things off my giant list at the moment. πŸ™‚

Someone asked me about the DBMS_DATAPUMP package, which is a PL/SQL API for Data Pump. I knew I had written about it, but it turned out I only had one little example in my main Data Pump article here. I put it on my list to expand it a little with some other common examples. I finally got round to this here.

It’s by no means an exhaustive list of what the DBMS_DATAPUMP package can do, but if someone new is coming to it, it puts a bit more meat on the bones compared to what I had before.

One of the reasons that moved that up the list was I was thinking about looking at the ORDS Database API endpoints related to Data Pump, which resulted in this.

That was a rather odd experience for a few of reasons.

  • There were some issues with authentication. I expected it to work with both the default administrator and an ORDS enabled schema, but it only worked with the latter. That was confusing the hell out of me for a time. I’m told it will work with both in the future…
  • There are two ways to use Data Pump. There is a REST endpoint “/database/datapump/jobs/” that can perform all the actions, and there are two RPC-style endpoints “/database/datapump/export” and “/database/datapump/import”. I decided that RPC is not for me and chose to ignore it. I’m a lot happier with the REST endpoint.
  • The API has extremely limited functionality at the moment.

I guess I was expecting this to feel like a wrapper over the DBMS_DATAPUMP package, but it is not like that at all. In this version it’s an API that covers a few basic tasks and completely locks you out of everything else. I can’t imagine many DBAs being able to use it, but it might be ideal for some developers if you are OK with giving them DBA credentials to the database. I wrote some of my thoughts/concerns at the bottom of the article here.

Despite my reservations, this is effectively version 1.0 of this functionality. I would suggest people try it out and feedback their requirements to the ORDS folks, so it can be improved.

On a more general note, it’s clear the ORDS Database API needs a rethink in terms of access management. It seems to be a bit of an all or nothing at the moment. I think it’s safe to say that as the APIs expand, there will be a range of people needing to use them, each with very different security constraints. I might want someone to be able to perform table or schema-level exports/imports, but I will want to control what they can do, and by giving them the access to do this, it doesn’t mean I want them to be able to run DBCA commands to create new instances as well. I think you get my point. It would seem like the access to the APIs need to respond to the underlying roles and privileges for the user, not using a separate ORDS parameter mechanism. Just my thought…

Cheers

Tim…