Oracle 19c


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