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.


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.

Check if Unified Audit is enabled without database running

I wanted to check if the binaries had Unified Audit enabled without using a database (because it was not yet created).

This is part of libknlopt.a library. Depending on which files are inside, it enables or disables options.

For Unified Auditing your can use:

case $(ar t ${ORACLE_HOME}/rdbms/lib/libknlopt.a | grep 'kza.ang.o') in "kzaiang.o") R="enabled" ;; "kzanang.o") R="disabled" ;; *) R="unknown" ;; esac && echo "Unified audit => $R"

For many other options (but not yet Unified Audit) you can check MOS note: How to Check and Enable/Disable Oracle Binary Options (Doc ID 948061.1)

Make a REST call with JSON content to a HTTPS webservice from PL/SQL 4

All keywords are now in the title. 🙂

I’ve started developing this small PL/SQL procedure based on Lucas Jelema blog entry Invoke a REST service from PL/SQL – make an HTTP POST request using UTL_HTTP in Oracle Database 11g XE .

Unfortunately it took me a while to find out why I was getting the error:

<title>400 Bad Request</title>
<h1>Bad Request</h1>
<p>Your browser sent a request that this server could not understand.<br />

There is a empty space at the beginning of a variable in the example on Lucas’ blog post – before the HTTP/1.1 on the line below:

req := utl_http.begin_request(url, 'POST',' HTTP/1.1');

My need is a bit more complex, so I leave here the complete code.

Certificate and Oracle wallet

First you need to download the certificate. A good tutorial do to this is at Shellhacks.com.

Then you need to create a wallet on the database server and add the certificate:

mkdir ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
orapki wallet create -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet -pwd WalletPassword123 -auto_login
orapki wallet add -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet -trusted_cert -cert "/tmp/my_certificate.crt" -pwd WalletPassword123

Oracle ACLs

On the database (my case was Oracle 11.2) you need to have XDB installed and add the ACLs. I skip the instructions to install XDB. The user is called GREEN.

grant execute on utl_http to GREEN;
grant execute on dbms_lock to GREEN;

    acl          => 'acl_file.xml', 
    description  => 'Allows sending alerts to REST service',
    principal    => 'GREEN',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

    acl         => 'acl_file.xml',
    host        => 'my_rest_service.com', 
    lower_port  => 443,
    upper_port  => NULL);    

Procedure to invoke REST service

The rest service requires a username password. I use the encoded version, so there are no visible passwords in the code. You can encode the pair username:password here.

( p_robot in varchar2, p_business_service in varchar2, p_target_ci in varchar2, p_transaction in varchar2, p_status in number, p_message in varchar2) is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'https://my_rest_service.com/api/event';
  buffer varchar2(32767); 
  content varchar2(8000) := '{ "robot":"'||p_robot||'", "business_service":"'||p_business_service||'", "target_ci":"'||p_target_ci||'", "transaction":"'||p_transaction||'", "status":"'||p_status||'", "message":"'||p_message||'", "target_support_group": "", "organization": "ANJO_EVENT"}';
  utl_http.set_wallet('file:${ORACLE_BASE}/admin/${ORACLE_SID}/wallet', null);
	req  :=UTL_HTTP.begin_request(url, 'POST');
	utl_http.set_header(req, 'Authorization', 'Basic f24iYsBpdGVzdDp3ZWJhcGl0ZXN0'); 
  utl_http.set_header(req, 'Content-Length', length(content));
  utl_http.write_text(req, content);

  res := utl_http.get_response(req);
  -- process the response from the HTTP call
      utl_http.read_text(res, buffer,32767);
    end loop;
    when utl_http.end_of_body 
END publish_event;

Trigger to automatically send event

In my case, the idea is to send an event every time a line is inserted into a table. So I just create a small trigger that calls the procedure above.

CREATE OR REPLACE TRIGGER green.trg_table1_monitor
   ON green.table1_monitor
   publish_event (
	   p_robot => :new.host_name,
		 p_business_service => 'IT DB',
		 p_target_ci => '356224bs5b1f6f401d2262eb0b9619ad',
		 p_transaction => 'Table1',
		 p_status => 7,
		 p_message => 'Green-Table1 -Exception: '|| substr(:new.exception,0,100) 

Et voilà!

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;

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                                   VALID
CATPROC                                   VALID
XDB                                       VALID
OWM                                       VALID

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

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

-- 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;


COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG                                   VALID
CATPROC                                   VALID
XML                                       VALID
JAVAVM                                    VALID
CATJAVA                                   VALID
XDB                                       VALID
OWM                                       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                                   VALID
CATPROC                                   VALID
XML                                       VALID
JAVAVM                                    VALID
CATJAVA                                   VALID
XDB                                       VALID
OWM                                       VALID
ORDIM                                     VALID
SDO                                       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                                   VALID
CATPROC                                   VALID
XML                                       VALID
JAVAVM                                    VALID
CATJAVA                                   VALID
XDB                                       VALID
OWM                                       VALID
ORDIM                                     VALID
SDO                                       REMOVED

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

vi /tmp/bug2307454.sql
drop function mdsys.NDM_RESET_ID_GENERATOR;

$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;
FOR cmdline IN (select 'drop public synonym '|| object_name cmd from dba_objects where status!='VALID' and owner='PUBLIC') LOOP
execute immediate(cmdline.cmd); 
alter session set "_ORACLE_SCRIPT" = true;

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

Average size of set of rows from a table

Sometimes users have strange requests that I avoid asking the reason.

Today a user asked me how he could find the average size of a set of rows inside a table. Not only for one column and not for the whole table.

This reminded me from university times, when doing SQL exercises.

With Oracle 11g and listagg function, it is quite easy to fulfill the request:

set serveroutput on;

  col_list varchar(2500);
  result number;
select 'select round('||listagg('nvl(avg(length('||column_name||')),0)','+') within group (order by column_id)||') 
  from MYAPP.mytable
  where creationdate > to_date(''01.01.2018'',''dd.mm.yyyy'') 
    and creationdate < to_date(''31.12.2018'',''dd.mm.yyyy'')' into col_list 
from all_tab_columns where owner='MYAPP' and table_name='MYTABLE' ;
execute immediate (col_list) into result;

DGMGRL error: Object “a1021p_site1” was not found

While testing some scripts, I was getting an error on DGMGRL, while querying some databases:

DGMGRL> show database A1021P_SITE1
Object "a1021p_site1" was not found

But it would work if I put single quotes around:

DGMGRL> show database 'A1021P_SITE1'

Database - A1021P_SITE1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON

Database Status:

The problem is on the way the database was added to the configuration, and it is quite simple to fix it…

  1. after disabling configuration
  2. and connecting with password to the database
  3. then just rename the database to a name without using quotes.
DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
Error: ORA-16602: database must be disabled to perform this operation

DGMGRL> disable fast_start failover;

DGMGRL> disable configuration

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
ORA-16541: database is not enabled
Configuration details cannot be determined by DGMGRL

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
ORA-16541: database is not enabled
Configuration details cannot be determined by DGMGRL

DGMGRL> connect sys/XXXX@a1021p_site1

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;

DGMGRL> show database A1021P_SITE1

Database - a1021p_site1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON

Database Status:

DGMGRL> enable configuration;

DGMGRL> enable fast_start failover

DGMGRL> show configuration

Configuration - A1021P

  Protection Mode: MaxAvailability
    a1021p_site1 - Primary database
    a1021p_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:


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


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.


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:


WARNING: pga_aggregate_limit value is too high for the amount of physical memory on the system

On Oracle 12.2 I have this fancy message after the database mounts. It is a datawarehouse and there are quite some memory.

The full message is:

 WARNING: pga_aggregate_limit value is too high for the
 amount of physical memory on the system
   physical memory size is 302063 MB
   limit based on physical memory and SGA usage is 512 MB
   SGA_TARGET is 276480 MB

There are 300GB of physical memory on the server.

SGA_TARGET is set to 270GB, PGA_AGGREGATE_LIMIT to 20GB. There should be 10GB available for the OS to run even when the PGA usage goes to the maximum it is defined.

So why does Oracle gives me this message?

The answer is in the documentation:


Even though PGA_AGGREGATE_LIMIT is set to a value different from 0, Oracle calculates so that it does not overpass 90% of the physical memory.

90% of 300GB is 270GB, minus 270GB from SGA size, means there is nothing left. Fortunately it is not in action, and I can see that by checking the maximum PGA used since instance start:

SQL> select round(value/1024/1024/1024) max_used from v$pgastat where name='maximum PGA allocated';

CRS-6706: Oracle Clusterware Release patch level (‘2565072065’) does not match Software patch level (‘277169099’)

Today on a dirt and quick patching session I end up with the following error when restarting the Oracle Restart Grid Infratructure.

The complete error was:

oracle@anjovm1: [+ASM] crsctl start has
CRS-6706: Oracle Clusterware Release patch level ('2565072065') does not match Software patch level ('277169099'). Oracle Clusterware cannot be started.
CRS-4000: Command Start failed, or completed with errors.

This was because I’ve installed the OJVM RU to the Grid home. The solution was simply:

oracle@anjovm1: ~oracle/ [+ASM] /u00/app/grid/18.0.0/OPatch/opatch rollback -id 28790647

And then I could restart:

oracle@anjovm1:~oracle/ [+ASM] crsctl start has
CRS-4123: Oracle High Availability Services has been started.

Index columns query

Query to show all indexes, its characteristics and respective columns from a table.
Note: It does not filter on owner.

select c.index_name,
       c.table_name||'('||listagg(column_name,',') within group (order by column_position)||')' as columns,
       decode(status,'VALID',null,status||' ')
       ||decode(visibility,'VISIBLE',null,visibility||' ')
       ||decode(segment_created,'YES',null,'EMPTY ')
       ||decode(uniqueness,'NONUNIQUE',null,uniqueness||' ')
       ||funcidx_status as STATUS
 from dba_ind_columns c, dba_indexes i
where i.table_name=c.table_name and i.index_name=c.index_name
  and c.table_name='&TABLE_NAME'
group by c.table_name,c.index_name,i.status,visibility, segment_created,uniqueness,funcidx_status
order by c.table_name,c.index_name;

Example of output:

INDEX_NAME                     COLUMNS                                 STATUS  
------------------------------ --------------------------------------- ----------
LIB_VERSION_NAME_IDX           LIB_VERSION(VERSION_NAME)                     
LIB_VERSION_PK                 LIB_VERSION(ID)                         UNIQUE