Oracle 19c


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