Get the very last out of Oracle CloudWorld 2022 by watching some of the recordings. You can find some really great sessions available on demand. All available for free on YouTube.

Upgrade to Oracle Database 19c

My session shows how to upgrade to Oracle Database 19c using AutoUpgrade. A short demo to get you started, and then I talk about more advanced scenarios, like upgrading with Data Guard and RAC.

AutoUpgrade 2.0: internals and new features

In this session, I talk about some of the newest features in AutoUpgrade. A lot of customers have asked us to enhance AutoUpgrade with patching capabilities. We heard you – and that is one of the new features I present in this session.

What Else

The playlist has close to 60 videos, so there is much to dig into. Some of my personal favorites:

• Revolutionizing objects, documents, and relational development by Juan Loaiza. He talks about a very cool new feature: JSON Relational Duality. Even if you are not into JSON, it is still worth a view.

• The latest enhancements in SQL plan management by Nigel Bayliss. Because it talks about SQL Plan Management which is one of the most underrated features in the database, often when I talk to customers about database upgrades, they are worried about SQL plan regression. This tool deals with precisely that.

• Tips and tricks for the Oracle Database developer by Jeff Smith. Because you always learn a new, handy trick in Jeff’s session.

And then there are so many more exciting sessions to watch.

Oracle CloudWorld 2023

The next CloudWorld takes place in Las Vegas on September 18-21.

Registration opens on April 11 with attractive early bird prices.

I hope to see you in Las Vegas.

I didn’t know Joel Kallman. I heard about his name, and I knew he had something to do with APEX. But we never had to opportunity to meet each other. After his untimely death, I became aware of his huge impact on the Oracle community. So many people shared so many great stories about him and how he influenced the Oracle community. It became apparent to me how much he meant for the Oracle community.

This post is about the Oracle community. When I started working with Oracle, I quickly found out how great the community was. People were happily sharing knowledge – on blogs, at conferences, in videos. I was blown away by the amount of help and guidance I could get. For free – and even with a huge smile on top of that. I was a rookie and all that help made a huge difference. This is what I mean – when I say I believe I have met Joel’s spirit. But still, after many years working with Oracle, I use and value the community.

Since I joined Oracle, it has been on my to-do list to do a rolling upgrade. I never tried that for real. Last week, I had time to try out rolling upgrades with DBMS_ROLLING. Provision a Data Guard environment, open the documentation, get a cup of coffee, and off we go…

At one point, I was stuck. Like in – really stuck! Friday night – even after trying Connor’s gin/tonic debugging – still stuck. Saturday morning – after a good night’s, sleep still stuck. Errors, errors, errors!

Luckily, I attended a talk at UKOUG Tech Talk in April this year about rolling upgrades. Clive and Zahid did a really good presentation on rolling upgrades with a lot of valuable technical knowledge. Flip through the slides, and then – voila – problem solved. Now I could proceed with the upgrade. Thanks Clive, thanks Zahid.

If it hadn’t been for the community – for people happily sharing knowledge and helping each other – for Joel’s spirit, I would probably still be stuck in that rolling upgrade.

Never stop sharing knowledge!

These days there is an abundance of virtual events. I feel exhausted from it from time to time. So you might ask yourself. Is there really room for virtual events?

The answer is: Yes, there is :-)

We have added more upcoming webinars to our Virtual Classroom Series: Upgrade to Oracle Database 19c. If you enjoyed the previous ones, I am sure that you will love these as well.

Of course, we still keep our promise to you: All tech – no marketing, no buzzwords

If you miss any of the webinars, head over to the Webinar page and find the recording and the slides. All the previous nine webinars are there as well.

I hope to see you there!

Wow! Mike and I gave a webinar yesterday: Cool Features – not only for DBAs. We showed a lot of cool features – and the audience recognised that by asking really cool questions. We had more than 100 questions to answer live, and below you can find the answer to those questions that we had to investigate further.

But first a few practical remarks:

• The recording will be available after a few days.
• The slides are already available, however, it is in PDF format, so all the videos are lost.
• We plan to post all the demos on our YouTube channel over the coming weeks.
• The next webinar in our Virtual Classroom Series is Database Upgrade Internals – and so much more on Wednesday 10 February 2021 at 10:00 CET

Expression Based Parameters

Ahh – one of my new favourites. A viewer asked whether there is any syntax check when you make an expression based parameter. So lets try. First command should work:

SQL> alter session set resumable_timeout='3000/2';

Session Altered.

Now, let’s make a syntax error and see what happens:

SQL> alter session set resumable_timeout='3000//2';

ORA-32005: error while parsing size specification [3000//2]

SQL> alter system set resumable_timeout='3000//2';

ORA-32005: error while parsing size specification [3000//2]

SQL> alter system set resumable_timeout='3000//2' scope=both;

ORA-32005: error while parsing size specification [3000//2]

SQL> alter system set resumable_timeout='3000//2' scope=memory;

ORA-32005: error while parsing size specification [3000//2]

SQL> alter system set resumable_timeout='3000//2' scope=spfile;

ORA-32005: error while parsing size specification [3000//2]

Conclusion: Your expressions are checked when you issue the ALTER SESSION or ALTER SYSTEM command.

But take care when using environment variables in your expressions. If the environment variable is available when you issue the statement, the command will succeed. However, if the environment variable is not present when the database restarts, then you will have a problem. This could be the case if you forget to add the environment variable to your profile or the environment in Grid Infrastructure (srvctl setenv). If this happens, the startup will error out.

And finally, the expressions you put into a pfile are not checked before the database starts using that pfile. If there are invalid expressions in your pfile, the database startup will error out as well.

Online Table Move

Indexes and LOBs

When you move a table online the indexes remain valid during and after the move. Optionally, you can specify the UPDATE INDEXES clause to change the index storage attributes as well (like moving index to a new tablespace):

SQL> alter table t1 move online tablespace data update indexes(i1 tablespace data);

The initial version of blog post claimed that indexes become unusable during online table move unless you specified the UPDATE INDEXES clause. This is not true! Kudos to Olaf Nowatzki for information me. Thanks!

The LOB segments that are created to support the LOB columns remain in the same tablespace, even if you move the table to a new tablespace. But if you want, you can also move the LOB segment as well:

SQL> alter table t1 move online tablespace data lob(c1) store as (tablespace data);

A viewer was concerned whether he could move a table online and also update indexes because one of the indexes was really wide. Apparently, in a previous version this had been an issue to them. I tested this out with an index on 10 columns defined as VARCHAR2(128 BYTE). And this was not a problem. If you index is even wider; test it yourself – or consider whether you really need such an index.

It is supported move an IOT online as well – but not partitioned IOTs.

The Rest

If the table has unused columns when you move it, it will still have unused columns. They are not affected by a move operation. Actually, Connor McDonald made a really good video where he talks about unused columns.

Also, an attendee wanted to know whether the online command generated more UNDO or TEMP. Let’s see what happens with my 125 MB large table (no indexes):

As you can see it does require more UNDO to move the table online. But the numbers might change on a busy system with many indexes and LOBs. TEMP appears to be relative unaffected – it might change if I have unique indexes and must rebuild those. Lesson learned – expect more resource usage – test before trying in production.

And finally – does online table move work on tables with OLS (Label Security) policies? I don’t know – so far I haven’t been able to get confirmation. My immediate answer is NO. Take for instance DBMS_REDEFINITION – it is not supported when the table has OLS policies, so I assume the same applies for online table move.

Online Convert To Partitioned Table

This feature only works on a non-partitioned (or regular table). If your table is already partitioned, and you want to change to a different partitioning method, you must use another method (like DBMS_REDEFINITION).

Online Data File Move

When you move a data file online, the database creates an exact copy of the data file in the new location. It is bit-by-bit identifical.

This also means that any free space in the data file is not reclaimed, nor is the High Water Mark affected.

Standby Database Operations

I demoed how the standby database in 19c can do automatic flashback, when the primary database flashes back. A viewer asked whether you can flash back to the same restore point multiple times without the standby database complaining.

My immediate answer was: yes, you can flash back to the same restore point multiple times. The documentation doesn’t mention such a restriction, so I was eager to try it out.

I made a data guard setup in OCI and I was able to flash back to the same restore point three times – and the standby database followed each time.

Gradual Password Rollover

With gradual password rollover a user can temporarily have two passwords. A viewer asked whether you can identify the sessions that are still connecting with the old password. And you can with unified audit trail provided you are auditing logons. In that case simply look at the AUTHENTICATION_TYPE column of the audit entries for LOGON actions:

SQL> select   authentication_type, event_timestamp 
     from     unified_audit_trail 
     where    action_name='LOGON' and dbusername='APP_USER' 
     order by event_timestamp;

Your result will be something like:

Look at the VERIFIER information. If a user is using the old password in a rollover period, the identifier is suffixed -OLD. Similar, the suffix is -NEW if the new password is used.

This is a very useful addition to gradual password rollover.

Conclusion

Thanks to everyone that participated today. We had so much fun preparing the webinar for you. And really – thanks for all the cool questions. Doing stuff remote is really hard, and you really feel disconnected from the audience. But it helps a lot with good questions.

See you next time.