Cool Features Web Seminar Jan 2021 – Quick Recap

Thanks to everyone attending our Cool Features – not only for DBAs web seminar yesterday on Jan 14, 2021. As over 500 attendees stayed until the end of the 2 hour long session with not less than 16 different features, we assume that you’ve enjoyed it. And from the questions you did ask, it looks like as some of the features really caught your attention. On Feb 10 it’s time for the next session for EMEA and India. But beforehand, let’s do a Cool Features Web Seminar Jan 2021 – Quick Recap, especially regarding the questions you’ve had.

Photo by Jason Blackeye on Unsplash

Extended VARCHAR2

Regardless which of the current Oracle releases you’ll use, MAX_STRING_SIZE is still set to the default STANDARD. Hence, Extended VARCHAR2 is not on by default for on-prem databases. You will find a different setup in the cloud where for instance in ADW it is enabled already. There MAX_STRING_SIZE=EXTENDED is set.

• Oracle 19c: MAX_STRING_SIZE
• Oracle 21c: MAX_STRING_SIZE

Furthermore, as I explained, you can enable this on a per-PDB basis. The parameter is PDB_MODIFIABLE. And you can enable it in a PDB without enabling it in the CDB$ROOT before.

SQL> alter session set container=PDB1;
Session altered.

SQL> startup
Pluggable Database opened.
SQL> alter system set max_string_size=extended scope=spfile;
System altered.

SQL> shutdown
Pluggable Database closed.
SQL> startup upgrade
Pluggable Database opened.

SQL> start ?/rdbms/admin/utl32k.sql

[..]

PL/SQL procedure successfully completed.
Commit complete.
Package altered.
Package altered.
Session altered.

SQL> shutdown
Pluggable Database closed.
SQL> startup
Pluggable Database opened.

SQL> alter session set container=CDB$ROOT;
Session altered.

SQL> select con_id, name,  value from V$SYSTEM_PARAMETER where name='max_string_size'

    CON_ID NAME 			  VALUE
---------- ------------------------------ --------------------
	 0 max_string_size		  STANDARD
	 3 max_string_size		  EXTENDED

SQL> show parameter max_stri

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
max_string_size 		     string	 STANDARD

The CON_ID=0 output in V$SYSTEM_PARAMETER is a bit strange as there is no CON_ID=0 in a CDB environment.

In addition, somebody asked whether the reduced number of roundtrips for the Extended VARCHAR2 compared to CLOB may be dependent on the SQL Plus setting of LONGCHUNKSIZE. It is not. I verified it today with the maximum setting of LONGCHUNKSIZE on my platform. It has no effect on the test results and the number of roundtrips.

Selecting the CLOB column:

SQL> select C from TEST;

10 rows selected.


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	132  consistent gets
	 80  physical reads
	  0  redo size
      16310  bytes sent via SQL*Net to client
      11890  bytes received via SQL*Net from client
	 52  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 10  rows processed

Versus selecting the Extended VARCHAR2 column:

SQL> select E from TEST; 

10 rows selected.


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	 14  consistent gets
	  0  physical reads
	  0  redo size
      40721  bytes sent via SQL*Net to client
	380  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 10  rows processed

Please see Franck Pachot’s blog post for further details.

The database environment I used for my tests had 8k DB_BLOCK_SIZE.

Install and Patch in one pass

I blogged about this already a while ago.: Install and Patch in one single action with OUI.

But what I missed in the slides was the fact that you need to exchange OPatch as well when you unzip. So the complete sequence of actions is:

1. Unzip the base release, e.g. 19.3.0
2. Remove the OPatch subdirectory
3. Unzip the most recent OPatch – it will recreate the subdirectory you’ve erased
4. Unzip the RU into a separare ./patchRU directory
5. Optional: Unzip one-off patches into separate ./patch1 … ./patch2 subdirectories

Unfortunately I couldn’t find any indication that no other shell than bash may allow the use of -applyRU. So you may please need to double-check with Oracle Support.

Read-Only Oracle Homes

I didn’t have this on the slides explicitly but only in the audio. Correct, ROOH are the default in Oracle Database 21c.

Read-Only Oracle Home Default

Read-only Oracle homes, where all configuration data and log files reside outside of the read-only Oracle home, are the default option for Oracle Database installations and upgrades.

Read-only Oracle homes enable an easy, flexible, and software-image based deployment of Oracle software that can automatically and seamlessly be distributed across multiple servers. Read-only Oracle homes also enable patching and updating of Oracle Database without extended downtime, as patching simply means replacing a given set of binaries in a defined location.

Regarding the question whether Oracle E-Business Suite (EBS) supports Read-Only Oracle Homes, I received super-service by the EBS team telling me to tell the customer to open an SR. Roy and I still try to find out the answer internally. My blunt guess would be that EBS does not support ROOH – but it would be great to get a definite answer. I’ll let the person who’d asked know as soon as possible.

Another very good question was whether you can revert from a read-only home to a standard home again. I had to smile because there seem to be more undocumented options of roohctl than documented ones. And roohctl -disable is another one:

oracle@hol:/u01/app/oracle/product/19/bin
$ roohctl -disable
Disabling Read-Only Oracle home.
Update orabasetab file to disable Read-Only Oracle home.
Orabasetab file has been updated successfully.
Read-Only Oracle home has been disabled successfully.
Check the log file /u01/app/oracle/cfgtoollogs/roohctl/roohctl-210115PM041120.log for more details.

Please be aware of:

• Bug 32602534 – READONLY HOME NOT SUPPORTED ON GRID HOME – ROOHCTL UTILITY 

Daniel’s topics

Daniel published a blog post where he answered the open questions for his topics. Many of you seem to be really into the Online Table Move feature. Which is great – and Daniel covered it all here: https://dohdatabase.com/2021/01/15/follow-up-on-cool-features-webinar-jan-2021/

Recording and Slides

The recording is available now. Please find it linked from the Videos page. Slides are available already, and we refreshed the upload now including Fred’s and Jeff’s slides, too.

The Upcoming Events

On February 10 we will deliver Upgrade Internals and so much more for you. And on March 17 we will do a session about Performance Testing for on prem using Cloud resources. We hope to see and hear you again then.

–Mike