Oracle 19c


How to reclaim storage space on Oracle Autonomous Database

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?

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

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.


Oracle 19c – TFA and Oracle Restart (SIHA)

It took Oracle two months and some persuasion effort to make the analyst to read and believe what I’ve written on the Service Request, but the answer finally come from development:

TFA is disabled in all 19.* SIHA versions. Will let you know once enabled.
You can download TFA from MOS and install it.

My original request in February was:

I’m installing Oracle Restart in silent mode.

I see that TFA is provided both in GI and RDBMS homes under $ORACLE_HOME/suptools/tfa/release/tfa_home

How can I make it to install THIS VERSION OF TFA (and not downloaded from Oracle Support) automatically part of a silent mode installation?

At the moment, when it runs the root.sh from RDBMS installation, it calls both
. /u00/app/oracle/product/19.6.0/install/utl/rootmacro.sh "$@"
and
/u00/app/oracle/product/19.6.0/suptools/tfa/release/tfa_home/install/roottfa.sh

The rootmacro.sh sets:
INSTALL_TFA=0

On the output of the root.sh script there is:

Oracle Trace File Analyzer (TFA) is available at : /u00/app/oracle/product/19.6.0/bin/tfactl

but this does not exist:
-bash: /u00/app/oracle/product/19.6.0/bin/tfactl: No such file or directory

So, I repeat the question:
How can I make it to install the TFA provided part of GI/RDBMS home (and not downloaded from Oracle Support) AUTOMATICALLY and PART OF SILENT MODE INSTALLATION?


Oracle 19c – install JVM

When you did not install Java / JVM directly during database creation, you can do it in a later phase. This operation unfortunately is not so well documented. Here how I do it:

set echo on
alter pluggable database all open;
alter system set "_system_trig_enabled" = false scope=memory;
 
host $ORACLE_HOME/perl/bin/perl ${ORACLE_HOME}/rdbms/admin/catcon.pl -n 1 -l /tmp/jvm -b initjvm ${ORACLE_HOME}/javavm/install/initjvm.sql;
host $ORACLE_HOME/perl/bin/perl ${ORACLE_HOME}/rdbms/admin/catcon.pl -n 1 -l /tmp/jvm -b initxml ${ORACLE_HOME}/xdk/admin/initxml.sql;
host $ORACLE_HOME/perl/bin/perl ${ORACLE_HOME}/rdbms/admin/catcon.pl -n 1 -l /tmp/jvm -b catjava ${ORACLE_HOME}/rdbms/admin/catjava.sql;
select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;
 
shutdown immediate
startup
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp/jvm -e -b utlrp $ORACLE_HOME/rdbms/admin/utlrp.sql
SELECT comp_name, version, status FROM dba_registry ORDER BY comp_name;
SELECT dbms_java.get_jdk_version JDK_Version FROM dual;


Oracle 19c – Install Spatial

Oracle Spatial is now free to users of all Oracle versions. To install, just run on your CDB:

host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp/jvm -b install_spatial $ORACLE_HOME/md/admin/mdinst.sql
set lines 300
col comp_name for a50
SELECT comp_name, version, status FROM dba_registry ORDER BY comp_name;

Tales of Oracle 19c upgrade – ORA-24964: ALTER SESSION SET CONTAINER error

At my client we have a nice automation tool that can run scripts on several DBs based on their group on Enterprise Manager.

Last week I’ve migrated+upgraded OEM repository from 12.2 to 19c, using Refreshable PDB to copy the PDB from a 12.2 CDB to a 19c CDB + running dbupgrade script on the PDB (p_oem_t below). Everything worked nice.

My colleague yesterday tells me the automation tool is not working, giving “ORA-0942 Table or view does exist error” for a query on the OEM repository. This means to me, it can connect to the new repository, as the old one is shutdown. But where can the problem be?

I look at the code, find the query, run it on SQL Developer and all if fine, no errors.

This until I activate the debug modus of the automation tool and I see that it connects to the CDB as SYSDBA and then changes container to the right PDB. Just above the ORA-0942 error, there was a

ORA-24964: ALTER SESSION SET CONTAINER error

Looking around I could find this Metalink note:

ORA-24964 – Alter Session To 12.2 PDB From 12.1 Client (Doc ID 2494623.1)

The keyword on the “Symptoms” is “Upgraded DB”. The code description states:

Cause: An attempt to switch to a PDB with different settings such as character set, time zone or time zone file version on an Oracle 12c Release 1 (12.1) or earlier client failed. (see Docs)

In fact, our automation tool uses an old 11.2 client. To test it, I’ve created on the same 19c CDB also new fresh PDB (PDB01) and tried to connect remotely:

oracle@l-master $ sqlplus sys@\"l-oem19c_t/c_oem_t\" as sysdba

 SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 23 17:06:27 2020
 Enter password:
  
 Connected to:
 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  
 SQL> select pdb_name from dba_pdbs;
 PDB_NAME
 ------------------------------
 PDB$SEED
 P_OEM_T 
 PDB01
  
 SQL> alter session set container=PDB01;
 Session altered.
  
 SQL>  alter session set container=CDB$ROOT;
 Session altered.
  
 SQL> alter session set container=P_OEM_T;
 ERROR:
 ORA-24964: ALTER SESSION SET CONTAINER error 

When I tried the same but using a 19c client, it works without problem:

oracle@l-master-19c $ sqlplus sys@\"l-oem19c_t/c_oem_t\" as sysdba
  
 SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 23 17:08:55 2020
 Version 19.3.0.0.0
  
 Copyright (c) 1982, 2019, Oracle.  All rights reserved.
  Enter password:
  
 Connected to:
 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Version 19.4.0.0.0
  
 SQL> select pdb_name from dba_pdbs;
  
 PDB_NAME
 ---------------------------------
 PDB$SEED
 P_OEM_T
 PDB01
  
 SQL> alter session set container=PDB01;
 Session altered.
  
 SQL>  alter session set container=CDB$ROOT;
 Session altered.
  
 SQL> alter session set container=P_OEM_T;
 Session altered.


Install Oracle Locator in Oracle 19c 1

The documentation lacks a lot concerning this free feature. Only it says that was part of Oracle Multimedia which is now desupported, but Oracle Locator continues to be supported.

https://docs.oracle.com/en/database/oracle/oracle-database/19/spatl/oracle-locator.html#GUID-EC6DEA23-8FD7-4109-A0C1-93C0CE3D6FF2
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/behavior-changes-deprecated-desupport-oracle-database.html#GUID-BABC1C60-EA07-4EBE-8C67-B69B59E4F742

Well, after some try and error, checking scripts and so on, here is the simple installation of Oracle Locator in Oracle 19c, container architecture:

-- Connect sys to the Root container (CDB$ROOT) and run:
SQL> @?/md/admin/catmdloc.sql

-- Then connect to the PDB where you need Oracle Locator and run again:
SQL> alter session set container=MY_PDB 
SQL> @?/md/admin/catmdloc.sql

And that’s it. The script creates the MDSYS user without authentication, it uses the SYSAUX tablespace and creates a directory pointing to $ORACLE_HOME/md/admin and then tables, including external tables with sharing=object.

So, it is easier than before, no need to first create tablespace or user as it was the case in previous releases.

If you don’t run the script first on the root container, you’ll get errors regarding the external tables with sharing=object.


Install Oracle Locator in Oracle 12.2

[Update: this is not anymore valid for Oracle 19c. To install Oracle Locator on Oracle 19c, check this post]

Oracle Locator is the free subset of Oracle Spatial functions. It is installed in MDSYS schemas. Some applications that use geographical data need Oracle Locator in order to compile.

There are two ways to install Oracle Locator, an easy and a complex.

When doing on single-tenant databases and Oracle Locator is only need for one pluggable database, please use the simple way.

Simple way to install Oracle Locator

In this case we install Locator directly on the pluggable database.

SQL> alter session set container=XPTO;
SQL> create user mdsys identified by "XXXXX" default tablespace sysaux;

@?/md/admin/mdprivs.sql
@?/md/admin/catmdloc.sql
SQL> alter user mdsys password expire;
SQL> alter user mdsys account lock;

SQL> col comp_name for a50
SQL> select comp_id,comp_name,status from dba_registry;

COMP_ID                        COMP_NAME                                          STATUS
------------------------------ -------------------------------------------------- -----------
CATALOG                        Oracle Database Catalog Views                      VALID
CATPROC                        Oracle Database Packages and Types                 VALID
XDB                            Oracle XML Database                                VALID
OWM                            Oracle Workspace Manager                           VALID

Complex way to install Oracle Locator

When you have a multitnenant environment and you want that Locator is available on all future PDBs that you create, the following method is to use. Unfortunately there are a few bugs (on 12.2 at least) when I tried this.

We need to install Oracle Spatial first and then remove Oracle Spatial and leaving Locator only objects.

I start with a fairly empty CDB, without JAVAVM. This must first be installed. I’ve already downloaded the scripts from the note described below, so I don’t go much in detail.

set lines 200
select comp_id,version, status from dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG                        12.2.0.1.0                     VALID
CATPROC                        12.2.0.1.0                     VALID
XDB                            12.2.0.1.0                     VALID
OWM                            12.2.0.1.0                     VALID


-- Install JAVAVM, XDB, XML - Repairing/Reinstalling JVM To A Multitenant Database (CDB and all PDBs) (Doc ID 2262919.1):
==========================================================================================================================

INSTALL_DIR=/temp/rdms122_java
cp $INSTALL_DIR/reinitjvm.sql $ORACLE_HOME/javavm/install
sqlplus / as sysdba << EOF
alter profile default limit password_verify_function null;
@$INSTALL_DIR/fulljvminstall.sql
shutdown immediate;
EOF

-- Oracle restart starts back the database automatically

sqlplus / as sysdba << EOF
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME/rdbms/admin utlrp.sql
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l $INSTALL_DIR -n 1 -b post_jvm_installation_info_ $INSTALL_DIR/jvm_info.sql;
set lines 200
select comp_id,version, status from dba_registry;
EOF

...

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG                        12.2.0.1.0                     VALID
CATPROC                        12.2.0.1.0                     VALID
XML                            12.2.0.1.0                     VALID
JAVAVM                         12.2.0.1.0                     VALID
CATJAVA                        12.2.0.1.0                     VALID
XDB                            12.2.0.1.0                     VALID
OWM                            12.2.0.1.0                     VALID

Next step is to install Oracle Multimedia, which is also required by Spatial:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l ${ORACLE_BASE}/admin/${ORACLE_SID}/create/logs -d $ORACLE_HOME/md/admin -b mdinst mdinst.sql

SQL> select comp_id,version, status from dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG                        12.2.0.1.0                     VALID
CATPROC                        12.2.0.1.0                     VALID
XML                            12.2.0.1.0                     VALID
JAVAVM                         12.2.0.1.0                     VALID
CATJAVA                        12.2.0.1.0                     VALID
XDB                            12.2.0.1.0                     VALID
OWM                            12.2.0.1.0                     VALID
ORDIM                          12.2.0.1.0                     VALID
SDO                            12.2.0.1.0                     VALID

And finally we install Spatial and remove it again, leaving Locator

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l ${ORACLE_BASE}/admin/${ORACLE_SID}/create/logs -d $ORACLE_HOME/md/admin -b mddins mddins.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME/rdbms/admin utlrp.sql


SQL> select comp_id,version, status from dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG                        12.2.0.1.0                     VALID
CATPROC                        12.2.0.1.0                     VALID
XML                            12.2.0.1.0                     VALID
JAVAVM                         12.2.0.1.0                     VALID
CATJAVA                        12.2.0.1.0                     VALID
XDB                            12.2.0.1.0                     VALID
OWM                            12.2.0.1.0                     VALID
ORDIM                          12.2.0.1.0                     VALID
SDO                            12.2.0.1.0                     REMOVED

There were some objects invalid, which match the Oracle note: “Invalid MDSYS objects after running mddins.sql to keep Locator option on 12.2.0.1 (Doc ID 2307454.1)”

vi /tmp/bug2307454.sql
drop function mdsys.NDM_RESET_ID_GENERATOR;
@$ORACLE_HOME/md/admin/semremov.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l ${ORACLE_BASE}/admin/${ORACLE_SID}/create/logs -d /tmp -b bug2307454 bug2307454.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME/rdbms/admin utlrp.sql

There were still some invalid public synonyms left. I opened an SR, and Oracle said they can be removed:

SQL> set lines 200
SQL> set pages 40
SQL> col owner for a20
SQL> col object_name for a50
SQL> col object_type for a30
SQL> select owner,object_name,object_type,con_id,sharing,status from cdb_objects where status!='VALID' and owner!='MLOG' order by 1,2;

OWNER                OBJECT_NAME                                        OBJECT_TYPE                        CON_ID SHARING            STATUS
-------------------- -------------------------------------------------- ------------------------------ ---------- ------------------ -------
PUBLIC               ALL_SDO_GEOR_SYSDATA                               SYNONYM                                 3 METADATA LINK      INVALID
...
21 rows selected.

alter session set "_ORACLE_SCRIPT" = true;
BEGIN
FOR cmdline IN (select 'drop public synonym '|| object_name cmd from dba_objects where status!='VALID' and owner='PUBLIC') LOOP
dbms_output.put_line(cmdline.cmd);
execute immediate(cmdline.cmd); 
END LOOP;
END;
/
alter session set "_ORACLE_SCRIPT" = true;

And that’s it. A complex way to install Locator…


pupdel.sql and SQLPLUS_PRODUCT_PROFILE

On Oracle 18c and 19c, the CreateDBCatalog.sql script has one more line calling:

/u00/app/oracle/product/19.3.0/sqlplus/admin/pupdel.sql

What is this pupdel.sql?

It creates a trigger after drop of user on the database, that will delete entries on SYSTEM.SQLPLUS_PRODUCT_PROFILE table belonging to this user.

But, what is SYSTEM.SQLPLUS_PRODUCT_PROFILE ?

This is the new name for PRODUCT_USER_PROFILE table, which allows to restrict usage of sqlplus based on keywords/attributes and users. PRODUCT_USER_PROFILE is now a synonym of SQLPLUS_PRODUCT_PROFILE and there is also the public view PRODUCT_PRIVS which shows the privileges of the current connected user.

From Oracle 19c all this is anyway desupported, as described at the documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/SQL-Plus-security.html#GUID-F94ACCFA-824F-4B58-B0D5-A73AB56DE3AA