Oracle 19c

What is the Dell BSAFE Micro-Edition Suite MES ?

This entry was posted in Oracle Oracle 19c PSU Security on by

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

This entry was posted in Oracle Oracle 10g Oracle 11g Oracle 12c Oracle 19c Performance and tagged parsing regression sqlt on by

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

This entry was posted in Oracle Oracle 11g Oracle 12c Oracle 19c Performance on by

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?

This entry was posted in Oracle Oracle 12c Oracle 19c on by

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

This entry was posted in Oracle Oracle 19c on by

[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.

set pages 110
col pdb_name for a10 
col name for a30 
col value for a20
col pdb_value for a20
col root_value for a20
col source for a10
select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE,source from
  (select pdb_name,name,value,a.con_id, decode(ismodified,'MODIFIED','PDB SPFILE','PDB$SEED') SOURCE 
   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)
   where a.con_id>2 and (ismodified='MODIFIED' or isdefault='FALSE')) a,
  (select 'CDB$ROOT' pdb_name,name,value,con_id,null
   from v$system_parameter where con_id=0) b
where a.name=b.name and a.con_id>2
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 
and bitand(nvl(spare2,0),1)=0
order by name;

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

A slight more complete query to check parameters which are not set the same between cdb$root and the PDB is this one:

with pdb_params as (select pdb_name,name,trim(both '''' from value$) value$ 
                                   from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid)
                                   where con_id>1 and bitand(nvl(spare2,0),1)=0 ),
        cdb_params as (select name, value,inst_id,con_id from gv$parameter ),
        spfile_params as (select name,value, sid from v$spparameter where isspecified='TRUE')
  select pdb_name, pdb_params.name, value$ pdb_value, cdb_params.value root_value, spfile_params.value spfile_value
    from pdb_params, cdb_params, spfile_params
    where pdb_params.name(+)=cdb_params.name and cdb_params.name(+)=spfile_params.name
        and (value$!=cdb_params.value or value$!=spfile_params.value)
union all 
  select case when sys_context('USERENV','CON_NAME') !='CDB$ROOT' then 'CALL THIS SCRIPT FROM CDB$ROOT' end pdb_name,
        null,null,null,null from dual
order by 1,2;

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

(more…)

Get unlock from PDB Lockdown profiles

This entry was posted in Oracle Oracle 19c Security on by

PDB Lockdown profiles allow, on a multitenant database, to limit what a user can do inside a PDB.

One use case is when we want to avoid high privileged users (like Application DBAs) to perform ALTER SYSTEM or ALTER SESSION commands.

Quickly we would think we can do a PDB Lockdown profile like:

SQL> create lockdown profile lock_test;
SQL> alter lockdown profile lock_test disable statement=('ALTER SESSION');
SQL> alter lockdown profile lock_test disable statement=('ALTER SYSTEM');

The problem of this simple profile is that we can lock ourselves, also as common user, inside the lock profile.

Image that you want to enable this profile on several PDBs:

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

SQL> alter system set pdb_lockdown=lock_test;
System altered.

SQL> alter session set container=samplepdb;
ERROR:
ORA-01031: insufficient privileges

Oups, you cannot anymore change the active container!

(more…)

Which OPatch to use? One OPatch version fits (almost) all.

This entry was posted in OPatch Oracle Oracle 11g Oracle 12c Oracle 19c PSU on by

The download page of Oracle OPatch has quite some room for improvement: put some ‘order by’ on the version and platform would be welcome. Also, make clear that there are very few versions of it.

In fact, for database, there are just two versions of OPatch! One OPatch version that covers all database supported versions from 12.1 to 20c. For paid long-term supported Oracle 11.2 there is another version.

So, in summary, here the OPatch version you need to patch your DBs:

Oracle Database versionOPatch version
11.211.2.x
12.112..2.x
12.212..2.x
18c12..2.x
19c12..2.x
20c12..2.x

The download link is this one:

https://support.oracle.com/epmos/faces/ui/patch/PatchDetail.jspx?patchId=6880880

For Enterprise Manager (middleware) there is another OPatch version, 13.9.x which I don’t have experience with.

The information about which OPatch versions is needed to apply the Database RU, RUR, is now part of the Patch Availability Document. For instance for OCtober 2020, this is what we can see:

Note 1: For Enterprise Manager (middleware) there is another OPatch version, 13.9.x which I don’t have experience with.

Note 2 – for Oracle guys out there: when we see the current size of the Release Updates, maybe it would be worth to include the latest version of OPatch within it. It would not increase so much the size and avoid the need of checking if we have the latest OPatch.

How to reclaim storage space on Oracle Autonomous Database

This entry was posted in Cloud OCI Oracle 19c on by

Man shall pay only for what it uses. This is also a motto of the Cloud and Oracle with second-level billing pushes this model.

Concerning disk space, however, it is not always easy. While terabyte prices are getting cheaper, sometimes you make a big cleanup of your database and then you would like to pay only for what is being used.

On Oracle Autonomous Databases it is the sum of datafiles size that counts.

Image now that you have a huge table and then drop it. The datafile space is not recovered.

In order to recover space you need:

  • Purge the recycle bin:

SQL> purge dba_recyclebin

  • reduce size of DATA tablespace datafile

SQL> alter database datafile <file_id> resize yyyM;

Now, this will be possible only if there are no extents used at the end of the datafile. Otherwise, one can try to alter table <table_name> move online; and then alter tablespace <tbs_name> coalesce; but this is not sure to help.

During my tests I only had one table, which made things easier.

Let’s hope that Oracle either changes the way to calculate the space used or provides a way to (continuous) defragment a datafile and make the size dynamic.

To check the storage used on Autonomous Database and find the datafile file_id, you can run the following query:

-- Get Space used by tablespace and file_id
select TBS "File_ID-Tablespace",
  round(sum(bytes)/1024/1024/1024,2) USED_GB,
  round(sum(bytes)/max(PROPERTY_VALUE)*100) PCT 
from
  (select file_id||'-'||tablespace_name TBS, bytes 
    from DBA_DATA_FILES
    where tablespace_name!='SAMPLESCHEMA'),
  (select PROPERTY_VALUE 
    from DATABASE_PROPERTIES 
    where PROPERTY_NAME = 'MAX_PDB_STORAGE')
group by rollup(TBS);

FILE_ID-TABLESPACE USED_GB PCT 
------------------ ------- --- 
3252-SYSTEM           0.41   2 
3253-SYSAUX           3.16  16 
3254-UNDOTBS1         0.44   2 
3255-DATA              0.1   0 
3256-DBFS_DATA         0.1   0 
                       4.2  21 

-- Get Total space used by DB
select round(USED_BYTES/1024/1024/1024,2) USED_GB,
  round(MAX_BYTES/1024/1024/1024,2) MAX_GB,
  round(USED_BYTES/MAX_BYTES*100,2) PCT_USED 
from
  (select PROPERTY_VALUE MAX_BYTES
    from DATABASE_PROPERTIES 
    where PROPERTY_NAME = 'MAX_PDB_STORAGE'),
  (select sum(BYTES) USED_BYTES
    from DBA_DATA_FILES 
    where TABLESPACE_NAME != 'SAMPLESCHEMA');

USED_GB MAX_GB PCT_USED 
------- ------ -------- 
    4.2     20    21.01

.anjo

Oracle Cloud: what parameters make ATP different from ADW?

This entry was posted in Cloud OCI Oracle 19c on by

Using the Free Tier of Oracle Cloud I created one Autonomous DB of each type – one Autonomous Transaction Processing and one Autonomous Data Warehouse (Autonomous JSON are not yet available). Then did run

select name, display_value
from v$parameter
where isdefault='FALSE'
order by 1;

on each of the DBs, I got the follow differences (empty means not set):

ParameterATPADW
optimizer_ignore_hintsTRUE
optimizer_ignore_parallel_hintsTRUE
parallel_degree_policyAUTO
parallel_min_degreeCPU
pdb_lockdownOLTPDWCS
pga_aggregate_target3000M5100M
resource_manager_planFORCE:OLTP_PLANFORCE:DWCS_PLAN
result_cache_max_result1
result_cache_modeMANUALFORCE
sga_target8000M3400M

Both the databases (PDB) share the same Container (CDB).

I did check also

select *
from database_properties;

but there are no initial state differences.

Something I found interesting. I had a 2-month old ATP when I created the ADW. Immediately I saw that my old ATP was not using ASM, compared to the ADW, also that ADW was a cluster DB while the old ATP was single instance.

I recreated the ATP to check if this remained. But no. My new ATP was co-located on the same database as the ADW, so parameters are mostly the same as we could see above.

For historical reasons, I leave here the parameter changes between ATP created in June and end-August 2020. For paths, only the differences are highlighted:

parameterATP June 2020ATP August 2020
audit_file_dest/u01/…/u02/…
cluster_databaseFALSETRUE
control_files/u01/…,/u05/…+DATA/…,+RECO/….
db_create_file_dest/u01/app/oracle/oradata+DATA
db_nameFBPOD1feio1pod
db_recovery_file_dest/u05/fra+RECO
de_recovery_file_dest_size571558474874921167764M
diagnostic_dest/u01/app/oracle/u02/app/oracle
drcp_connection_limit600
external_keystore_credential/u01/…/wallets/tde_seps+DATA/encrypt_wallet/tde_seps
gcs_server_processes04
instance_number2
log_archive_dest_1LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY
log_archive_dest_state_4ENABLE
log_archive_dest_state_5ENABLE
log_archive_dest_state_6ENABLE
max_pdbs4096
processes2000040000
sga_max_size321G301G
thread2

Attention OPatch 12.2.0.1.19: ‘opatch util cleanup’ command deletes files from /etc, /bin, /lib 2

This entry was posted in OPatch Oracle 12c Oracle 19c PSU Security on by

On January 28th 2020 I reported to Oracle on a Severy 1 SR that the latest OPatch version tries to delete system files from /etc, /bin, /lib :

I wrote in the SR the use case, provided the logs. On my case, the opatch cleanup command “just” deleted the configuration files of my backup client in /etc (opatch deletes files that are writable by the user who calls the command – ‘oracle’/’grid’ in most of the cases).

The location from where ‘opatch cleanup util’ tries to delete files depends from where you call it! So, if you call from / or from $ORACLE_BASE or from $ORACLE_HOME/OPatch you’ll get different behaviours!

It took two months until I got an answer to that SR, saying a bug was created and it will be fixed on the next version of OPatch:

Today there was a release of April 2020 Security patches, the latest Oracle Patch Bundles. To my surprise, for Oracle versions 12.2, 18c and 19c, there is the requirement of using OPatch 12.2.0.1.19 version, the exact same version that has this dangerous bug!

The bug reported end of January was not yet fixed! At least, on OPatch readme there is now a warning:

So, be careful until then and do not use ‘opatch util cleanup’. Anyway, this command is not anymore needed, as a cleanup (without deleting any wrong file) of the $ORACLE_HOME/.patch_storage directory is automatically done at the end of the patching.