Oracle 19c


Datapatch and Tablespace Full   Recently updated !

Yesterday on a simple 19.13 patching session, got a nice error during datapatch: ORA-01691: usable to extend lob segment in Tablespace SYSTEM ! Got a bit scared, but simple added more space to the tablespace, run datapatch again and all was ok. Maybe patch/datapatch should check also for free space in the SYSTEM Tablespace ?

Here the error:

DBD::Oracle::st execute failed: ORA-01691: unable to extend lob segment SYS.SYS_LOB0000323098C00008$$ by 128 in tablespace SYSTEM
ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN
           INSERT INTO sys.dba_registry_sqlpatch_ru_info
             (patch_id,
              patch_uid,
              patch_descriptor,
              ru_version,
              ru_build_description,
              ru_build_timestamp,
              patch_directory)
           VALUES
             (:patch_id,
              :patch_uid,
              :patch_descriptor,
              :ru_version,
              :ru_build_description,
              TO_TIMESTAMP(:ru_build_timestamp, 'YYMMDDHH24MISS'),
              :patch_directory);
           COMMIT;
         END;" with ParamValues: :patch_descriptor=OCIXMLTypePtr=SCALAR(0x468b0a0), :patch_directory='PK........▒LOS▒.
▒. ..▒[..
...33192793.xml▒][.۸.~..
▒.v▒▒▒.x.▒▒q*.o▒▒*.'s▒▒▒(.-qL▒l.ji▒▒/@Im        8 .$.I▒<▒0j▒.>\.▒.▒~.▒▒▒K▒vE▒▒▒.~K..▒6▒▒b▒▒▒a▒?=F.▒..▒]▒\▒5▒▒▒▒.?|▒▒y4f▒▒{X▒▒▒..▒"▒▒|?d.▒..I▒.E.▒▒▒▒=▒▒▒▒▒..▒▒▒.▒▒5.Wg▒w.ۺ▒..▒v}Q%}.▒..▒▒.▒▒.-▒▒▒▒▒\%▒▒׿▒vE.▒▒xsU▒▒)%ħa@.r▒▒.▒Kۢ▒.▒}..y▒▒.~j2▒▒â.=▒▒▒.▒▒▒O.▒▒<▒wm▒▒▒d▒.▒vw▒▒▒▒....▒o޼.▒}▒▒▒.I.▒x▒.▒▒.▒▒▒▒.▒▒.▒x▒o▒)▒▒wo.vxۿ{|\▒,.i]5▒6▒▒..▒▒.b.g▒b▒▒7Ew▒▒▒▒./▒.▒▒o.▒/▒.▒.▒▒6▒#▒.▒.▒...▒▒..▒▒▒▒ߵ▒l▒▒▒▒...▒▒.▒.▒▒.a(▒.▒?|▒.▒
X▒▒{+▒▒v▒=.▒{▒▒▒▒9Íui▒▒.H.▒▒▒▒▒▒▒▒S.▒^▒|▒9.▒%▒,▒▒▒SU▒.▒. zZ.▒..c;.▒vحY▒B...▒▒▒▒/▒▒.▒▒▒▒.▒▒▒..H{.7}f.▒..▒j.▒mۿ▒.▒S.▒7▒.:§.▒+p▒%▒..w▒.▒.▒{7▒.▒k▒..▒6▒6▒▒>v▒bwMU▒..▒Y_▒+▒IG▒<.|4▒▒\▒4.▒i▒*▒▒;G▒-V.ε▒▒.p.▒▒▒▒
G̍1▒.▒]▒▒▒.1O▒v▒^.▒▒▒.▒:.Wy._q7▒▒.*▒&▒▒`/▒I߹...pW.
▒▒▒▒C.v▒j▒[▒▒.▒▒~▒▒.▒.bo▒▒.U▒..▒V▒▒
.`n▒▒N7▒.`nyup▒▒.w▒.]▒▒B▒.▒▒5D7▒.w.#w.▒.▒m▒▒ܬ▒.p▒.3p.▒▒.▒.l▒.▒▒Z;▒0.\bm▒J▒...', :patch_id="33192793", :patch_uid="24462514", :ru_build_description="Release_Update", :ru_build_timestamp="211004165050", :ru_version="19.13.0.0.0"] at /u01/oracle/db19s/sqlpatch/sqlpatch.pm line 5876, <LOGFILE> line 110.


New in Oracle 19.12 – Log switch SCN in alertlog

Just patched few DBs and noticed this change:

Before:

2021-08-26T05:30:52.463731+02:00
Thread 1 advanced to log sequence 101101 (LGWR switch)
  Current log# 1 seq# 101101 mem# 0: +U01/ANJODB_SITE1/ONLINELOG/group_1.282.988988971

And with 19.12:

2021-08-27T07:17:16.811288+02:00
Thread 1 advanced to log sequence 101176 (LGWR switch),  current SCN: 132131048333
  Current log# 1 seq# 101176 mem# 0: +U01/ANJODB_SITE1/ONLINELOG/group_1.282.988988971

Nice touch from Oracle. 🙂


GI installation – ASM does not start on 19.12

Today a colleague tells me that installing GI on /u00/app/grid/19.12.0 fails, while it works by changing the ORACLE_HOME location. The error in the logs is with the ASM disk group creation:

INFO:  [Aug 16, 2021 4:06:17 PM] Command /u00/app/grid/19.12.0/bin/asmca -silent -oui_internal -configureASM -diskString '/dev/xvd*' -diskGroupName U01 -diskList /dev/xvdg -redundancy EXTERNAL -au_size 4
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn.handleProcess() entered.
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn: getting configAssistantParmas.
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn: checking secretArguments.
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn: starting read loop.
INFO:  [Aug 16, 2021 4:06:22 PM] SYS_PASSWORD_PROMPT
INFO:  [Aug 16, 2021 4:06:22 PM] Processing: SYS_PASSWORD_PROMPT for argument tag -sysAsmPassword
INFO:  [Aug 16, 2021 4:06:22 PM] Skipping line:*******************
INFO:  [Aug 16, 2021 4:06:22 PM] ASMSNMP_PASSWORD_PROMPT
INFO:  [Aug 16, 2021 4:06:22 PM] Processing: ASMSNMP_PASSWORD_PROMPT for argument tag -asmMonitorPassword
INFO:  [Aug 16, 2021 4:06:22 PM] End of argument passing to stdin
INFO:  [Aug 16, 2021 4:06:22 PM] Skipping line:*******************
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] [FATAL] [DBT-30056] Labeling of disks failed.
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: [FATAL] [DBT-30056] Labeling of disks failed.
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15227: could not perform label set/clear operation
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15227: could not perform label set/clear operation
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15031: disk specification '/dev/xvdg' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15031: disk specification '/dev/xvdg' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] [FATAL] [DBT-30002] Disk group U01 creation failed.
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: [FATAL] [DBT-30002] Disk group U01 creation failed.
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15018: diskgroup cannot be created
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15018: diskgroup cannot be created
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15031: disk specification 'AFD:U011' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15031: disk specification 'AFD:U011' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:07:00 PM] Completed Plugin named: asmca
INFO:  [Aug 16, 2021 4:07:00 PM] ConfigClient.saveSession method called
INFO:  [Aug 16, 2021 4:07:00 PM] Automatic Storage Management Configuration Assistant failed.

While I still did not get the original reason of the problem (might be with the Gold Image creation of the GI), I found that it can be worked around with:

chown -R grid:oinstall $ORACLE_BASE/diag


How to change PDB open mode without disconnecting existing sessions: the ambiguous FORCE

When the database is in READ ONLY mode, the way to change it to READ WRITE is by issuing

SQL> ALTER DATABASE OPEN READ WRITE;
Database altered.

However, when working on a PDB, the similar syntax does not work:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE
*
ERROR at line 1:
ORA-65019: pluggable database pdb1 already open

This is strange, and to my surprise, the correct syntax to change a PDB from READ ONLY to READ WRITE is:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE FORCE;
Pluggable database altered.

I would expect that the “FORCE” would do the same as on the STARTUP command here:

SQL> STARTUP FORCE OPEN READ WRITE;
ORACLE instance started.

Total System Global Area 2147481656 bytes
Fixed Size                  8898616 bytes
Variable Size             872415232 bytes
Database Buffers         1258291200 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

On the normal STARTUP command, the FORCE keyword performs a shutdown abort, before starting back the database. It is correctly described in the documentation:

If the database is open, then FORCE shuts down the database with a SHUTDOWN ABORT statement before re-opening it.

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/starting-up-and-shutting-down.html#GUID-CCA52747-05CA-4ED3-BE6D-E2E684C4D87D

The ALTER PLUGGABLE DATABASE explains the different behaviour of the FORCE keyword in this case:

Specify this keyword [FORCE] to change the open mode of a PDB from READ WRITE to READ ONLY, or from READ ONLY to READ WRITE. The FORCE keyword allows users to remain connected to the PDB while the open mode is changed.

When you specify FORCE to change the open mode of a PDB from READ WRITE to READ ONLY, any READ WRITE transaction that is open when you change the open mode will not be allowed to perform any more DML operations or to COMMIT.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-PLUGGABLE-DATABASE.html#GUID-A29491AD-8F0F-4E52-9D94-57FC3FF8FBC7

So, at the end it is good news. It is possible to change the PDB open mode without killing the users’ sessions. Just that “FORCE” keyword seems a bit too strong for my taste.


Opatchauto does not work without DB created. And Oracle support… well

Few months ago I hit an interesting case. After installing Oracle Grid Infrastructure and Database software on a server, the new Release Update come out and I wanted to use opatchauto to apply it automatically on both homes.

It doesn’t work.

sudo ${ORACLE_HOME}/OPatch/opatchauto apply -phBaseDir /tmp/RU_19c_JAN2021/32126842 -prepare-clone -silent /tmp/RU_19c_JAN2021/clone.properties
...
OPATCHAUTO-72128: Cannot execute out of place patching prepare session.
OPATCHAUTO-72128: Clone home properties /tmp/RU_19c_JAN2021/clone.properties specified has entry for non requested home(s) [/u00/app/oracle/product/19.8.0].
OPATCHAUTO-72128: Please correct properties file and re-run out of place patching prepare operation.
OPatchAuto failed.

The setup is all correct, opatchauto is the latest version (13.9.4.5.0, as of February 2021), the inventory has the two homes:

<HOME NAME="OraGI19Home1" LOC="/u00/app/grid/19.8.0" TYPE="O" IDX="1" CRS="true"/>
<HOME NAME="OraDB19Home1" LOC="/u00/app/oracle/product/19.8.0" TYPE="O" IDX="2"/>

The clone.properties file has the name of these homes:

/u00/app/grid/19.8.0=/u00/app/grid/19.10.0
/u00/app/oracle/product/19.8.0=/u00/app/oracle/product/19.10.0

The only “problem” is that no database is created. In fact, the workaround is just to register a fake DB on the CRS:

srvctl add db XX -oraclehome /u00/app/oracle/product/19.8.0

Then the opatchauto works.

I open a SR with Oracle. Inform about the OPatch bug and ask what is the meaning of the OPATCHAUTO-72128 error, because there is no documentation of Opatchauto.

The normal ping-pong with Oracle support starts, they ask me to run with maximum debug level before they can do something. I say that the complete use case is already described and they can try in-house and can/should open a bug.

No success. The support person says that if the customer doesn’t do more work, Oracle doesn’t care about the bug and problem will remain unfixed. I inform that I will blog about this bug, so that the community is aware.

Final answer from this SR:

“Knowledge content not created because the issue was resolved by the customer with no further information provided. Also no further ODM headings were used for the same reason.”


What is the Dell BSAFE Micro-Edition Suite MES ?

On the April 2021 Oracle Critical Patch Update, the top vulnerability affects “Oracle Database – Enterprise Edition Security (Dell BSAFE Micro Edition Suite)”.

But what exactly is this Dell MES ? The answer comes on MES v4.1.6 to v4.5 update 18c / 19c databases (Doc ID 2746801.1) note on My Oracle Support. It explains:

” The BSAFE MES is the underlying encryption libraries used by the Database and associated technologies and products to encrypt data at rest and in transit.  Upgrading to MES 4.5 resolves a number of vulnerabilities and provides the latest and most secure encryption ciphers.”

Please note that already on the January 2021 Oracle Release Update (19.10 or 18.13 there was an update of this component and “Anonymous RC4 Cipher” is not supported and does not work anymore.

Other ciphers are deprecated and should not be used anymore, as they are not fully secure:

  • Unix Crypt (MD5crypt) Password verified (used with Oracle Internet Directory OID)
  •  MD4, MD5, DES, and RC4-related algorithms used for instance in Network encryption (recommended to use AES)
  • SHA-1 used in DBMS_CRYPTO and Checksum in SQLNET (recommended to use SHA-2)
  • TLS 1.0 and TLS 1.1 used to network connection authentication to the database using certificates (recommended to use TLS 1.2)

In my case I had problems not with the database, but with an old OEM Agent, that could not communicate anymore to OEM (after the January RU on OEM). The solution was to remove all deprecated algoritms and find common accepted ones between to old agent and patched OEM.


Explore multiple optimizer features and fixes with SQLT Xplore

Yesterday I described how I come to SQLT Xplore and it helped me to find out, on Oracle 12.2.0.1, that optimizer_features_enable=8.1.3 decreased the parsing time of a query from 5 seconds to 0.2 seconds. Today I show how to use SQLT Xplore.

What is SQLT Xplore?

SQL Xplore automatises the test of almost 2’000 optimizer parameters and bug fixes control against one query, allowing to discover which parameter was eventually the reason of a performance regression.

(more…)

Long parsing on Oracle 12.2 and the discovery of SQLT Xplore

Today I discovered a fantastic free tool from Oracle: SQLT Xplore!

At a client I had one query that took long time parsing. The tkprof result of the 10046 trace showed it took 5 seconds to parse:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      5.17       5.30          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0        665          0          26
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      5.17       5.30          0        665          0          26

Looking around I’ve noticed that disabling the cost based transformation:

ALTER SESSIONS SET "_optimizer_cost_based_transformation"=off;  

…the query was much faster to parse: only 0,5 seconds.

(more…)

Why PGA_AGGREGATE_LIMIT appears to be set, when it is not?

Today I had to explain why the pga_aggregate_target was showing a value, but one does not have to care about it most of the cases. Here the same explanation.

When doing:

SQL> show parameter pga

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ----------------------
pga_aggregate_limit                  big integer                                  3000M
pga_aggregate_target                 big integer                                  1G

One would think that pga_aggregate_target is set to 3000M. However when one checks on the spfile, there is nothing defined:

(more…)

Strange behaviours on setting PDB parameters 3

[Tests performed on Oracle 19.8]

All started when I wanted to create a query to check which parameters are set on a PDB and the difference from the CDB$ROOT container.

col pdb_name for a10 
col value for a20
col pdb_value for a20
col root_value for a20
select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from
  (select pdb_name,name,value 
   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)
   where a.con_id>2 and isdefault='FALSE') a,
  (select 'CDB$ROOT' pdb_name,name,value 
   from v$system_parameter where con_id=0) b
where a.name=b.name
order by 1,2;

But I know there is also one view called pdb_spfile$ that would show the parameters on the PDB pseudo-spfiles:

col pdb_name for a10 
col name for a20
col value$ for a20
select pdb_name,name,value$ 
from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) 
where con_id>2 order by name;

The V$SYSTEM_PARAMETER is well documented, while the PDB_SPFILE$ is not.

Now, the set or unset parameters do not work the same way, as I expected, it trigger some strange behaviours.

(more…)