Add a DIAGNOSTIC user to Enterprise Manager for performance insights and query tuning only

When you want a user, probably a developer, to have access via Enterprise Manager to show query performance, but *ONLY* to specific databases and it’s performance stats, you need a ‘restricted’ EM and database user to do this. Next is tested on EM 13.3 and an 18c container database.

Short list of things that need to be done:

  1. Create a new Enterprise Manager Role with performance monitor privileges for specific targets
  2. Create new Enterprise Manager Administrators and add them to the previously create role
  3. Create a new common user in the target databases for monitoring within the target databases
  4. Create a new Enterprise Manager Named Credential for these targets

So that’s a short list, now the (written) instructions per step: Continue reading

ORA-04036 PGA memory exceeds PGA_AGGREGATE_LIMIT

Although the PGA_AGGREGATE_LIMIT is set and Oracle decided to kill the process/session that was using so much, it sometimes still exceeds and grows and eventually exhausting all the memory.

SQL> show parameter pga_aggregate_limit
NAME                            TYPE        VALUE
------------------------------- ----------- ------------------------------
pga_aggregate_limit             big integer 14G

SQL> select round(pga_used_mem/1024/1024) PGA_USED_MEM
from v$process p join v$session s on p.addr = s.paddr
where s.sid = 259;

PGA_USED_MEM
------------
14928

SQL> /

PGA_USED_MEM
------------
15015

From the alert log:

KILL SESSION for sid=(259, 29568):
   Reason = alter system kill session
   Mode = KILL HARD SAFE -/-/-
   Requestor = USER (orapid = 25, ospid = 4952, inst = 1)
   Owner = Process: USER (orapid = 148, ospid = 5628)
   Result = ORA-31

The culprit here is the ORA-31 error. This is the ‘marked for kill’ message. So the process is not killed, but only marked for kill:

Continue reading

Purge a single sql_id from the shared pool

When you want to flush or purge just a single (or a few) statement from the Shared Pool you can use the following query (all on a single line in SQL Developer e.g.):

exec for c in (select address, hash_value from v$sqlarea where sql_id='3hab0nxcfsxyj') loop sys.dbms_shared_pool.purge(c.address||','||c.hash_value,'C'); end loop;

-- If and only if the first argument is a cursor address and hash-value,
--        the flag parameter should be set to 'C' (or 'c').

Happy flushing!

install_ojvmwcu.sql and “Enter value for 1:”

In the Oracle JVM Web Services Call-Out Utility Developer’s guide, it states that the install_ojvmwcu.sql script must be run, but if you do that, it will ask you for a value. Which value?

In 12.2.0.1 the script has been changed, but the documentation hasn’t. The value it is asking for is a password, the password of the OJVMWCU_INSTALL user. Hang on? According to the docs this user must be created manualy first… not any more.

The “install_ojvmwcu.sql” calls “@configure_ojvmwcu.sql &1”, that’s where the “Enter value for 1:” comes from.
The “configure_ojvmwcu.sql” contains:

install_schema  VARCHAR2(20) := 'OJVMWCU_INSTALL';
...

procedure create_user as
  user_exists integer := 0;
begin
  select count(1) into user_exists from dba_users where username = install_schema;
  if user_exists != 0 then
    execute immediate 'drop user ' || install_schema || ' cascade';
  end if;
  execute immediate 'create user ' || install_schema || ' identified by &1';
...

Aha! The &1 the password of the OJVMWCU_INSTALL user, if it’s there it will be dropped and it will be created.

So, run “install_ojvmwcu.sql  password” for installing and creating the OJVMWCU_INSTALL user automagically. After that, grant users with “@grant_ojvmwcu.sql  user”.

Oh, run an “utlrp” (recompile invalid objects) after running the install_ojvmwcu.sql script.

Happy installing.

ODA upgrade to 12.2 fails with [DCS-10001:Internal error encountered: Fail to run root scripts..]

During an upgrade to 12.2.1.4 and in the step running GRID root scripts, the upgrade fails with:

[DCS-10001:Internal error encountered: Fail to run root scripts..]

After that if you run the upgrade again, you will get the following error:

[DCS-10001:Internal error encountered: Fail to extract the GI clone file.]

When I got there error’s, I found in the logging (/u01/app/12.2.0.1/grid/install/root_<host_name>_<date>_<time>-<more_numbers>.log) that there was no PKI trust set up between the ODA and ‘itself’ (aka passwordless login). The Oracle GRID upgrade works by accessing all the nodes in it’s cluster via ssh, including itself. But This was a single (non HA) machine (X6-2M), so did not expect it. Continue reading

Nextcloud 13 update on FreeBSD breaks (with redirection errors)

In Firefox: “The page isn’t redirecting properly”

FIX BELOW:

                    /!\ NEXTCLOUD 13.0.1 UPDATE /!\

The nextcloud 13.0.1 package changes the location of the bundled apps.
After updating to 13.0.1 you MUST adapt your configuration. You MUST
add an additional entry to the "apps-paths" array in config/config.php

    1 =>
    array (
      'path' => '/usr/local/www/nextcloud/apps-pkg',
      'url' => '/apps-pkg',
      'writable' => false,
    ),

For the default installation, the fix can be applied with:

  cd /usr/local/www/nextcloud
  su -m www -c "php ./occ config:import < /usr/local/share/nextcloud/fix-apps_paths.json"

So running this after an ‘failed’ upgrade, run this:

 su -m www -c "php /usr/local/www/nextcloud/occ config:import < /usr/local/share/nextcloud/fix-apps_paths.json"

Happy updating!

ORA-13785: missing target object for tuning task “SYS_AUTO_SPM_EVOLVE_TASK”

A small walkthrough in ‘clearing’ the ORA-13785 error. This might be in the alert log or when you run DBMS_SPM.REPORT_AUTO_EVOLVE_TASK without and object ID. It probably defaults with object ID ‘1’, which is the last run evolve task(?).

The ‘SYS_AUTO_SPM_EVOLVE_TASK’ ‘root object ID’ (I’m not sure if it is called like this) seemed missing, adding it and resetting and resuming SPM resolved the error. This is all done with trial and error, cross checking a working 12c database and a lot of common sence. BACKUP FIRST! It also includes ‘recreating’ the ‘root object ID’ for ‘SYS_AUTO_SQL_TUNING_TASK’ which I found also missing. Continue reading

Oracle Linux patches for Meltdown and Spectre information

Oracle Linux

Shown are the latest kernel versions as of the 9th of January which have Meltdown and Spectre patches.

Kernel versions can be found when running the `uname -r` command.

After the kernel is installed one can find the kernel/packages changelog and security info with the following commands and see in the page table isolation has been activated:

# yum updateinfo list
# yum updateinfo list cves
# yum updateinfo list kernel-uek
# yum updateinfo list --sec-severity=Important
# yum updateinfo info --sec-severity=Important
CVE-2017-1000407 Important/Sec. kernel-uek-4.1.12-112.14.13.el7uek.x86_64

# dmesg | grep isolation
[ 0.000000] Kernel/User page tables isolation: enabled

# rpm -q --changelog kernel | egrep 'CVE-2017-5715|CVE-2017-5753|CVE-2017-5754'
# rpm -q --changelog kernel-uek | egrep 'CVE-2017-5715|CVE-2017-5753|CVE-2017-5754'

Oracle Linux version 6

Kernel: 2.6.32-696.18.7 (errata: ELSA-2018-0008), 2018-01-04.

Kernel-uek: 4.1.12-112.14.10 (errata: ELSA-2018-4006), 2018-01-09.

Oracle Linux version 7

Kernel: 3.10.0-693.11.6 (errata: ELSA-2018-0007), 2018-01-04.

Kernel-uek: 4.1.12-112.14.10 (errata: ELSA-2018-4006), 2018-01-04.

Oracle VM version 3.4

Xen: 4.4.4-155.0.12.el6 (errata: OVMSA-2018-0006), 2018-01-08.

Continue reading

Oracle native connection encryption (in WebLogic Connection Pools)

Wallets for encrypting database connections? No, not any more…!

When you want to encrypt your client connections to the database, one used to create Oracle Wallets. With an Oracle wallet you run ‘SQL*Net over an SSL connection’. Your tcp connection will be transformed to tcps.

This is not necessary if you easily want to encrypt all your connections to the database. You do not use tcps, you still use tcp, but you encrypt SQL*Net traffic, which is a different approach.

If you use “Native Oracle Net Services encryption and integrity”, you can encrypt all SQL*Net traffic from a client, for all connections to a database and it’s even also configurable per WebLogic Connection Pool. Continue reading