Oracle 12c


Explore multiple optimizer features and fixes with SQLT Xplore

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

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?

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…)

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

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.


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.


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…


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
  Instance(s):
    A1021P

Database Status:
SUCCESS

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

DGMGRL> disable fast_start failover;
Disabled.

DGMGRL> disable configuration
Disabled.

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

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
Succeeded.

DGMGRL> show database A1021P_SITE1

Database - a1021p_site1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    A1021P

Database Status:
DISABLED

DGMGRL> enable configuration;
Enabled.

DGMGRL> enable fast_start failover
Enabled.

DGMGRL> show configuration

Configuration - A1021P

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

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

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


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:

2019-07-02T17:41:33.036963+02:00
 WARNING: pga_aggregate_limit value is too high for the
 amount of physical memory on the system
   PGA_AGGREGATE_LIMIT is 20480 MB
   PGA_AGGREGATE_TARGET is 10240 MB.
   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:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/PGA_AGGREGATE_LIMIT.html#GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3

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';
  MAX_USED
----------
        16


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.