Monthly Archives: November 2020


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

Strange behaviours on setting PDB parameters 3

[Tests performed on Oracle 19.8]

All started when I wanted to create a query to check which parameters are set on a PDB and the difference from the CDB$ROOT container.

col pdb_name for a10 
col value for a20
col pdb_value for a20
col root_value for a20
select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from
  (select pdb_name,name,value 
   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)
   where a.con_id>2 and isdefault='FALSE') a,
  (select 'CDB$ROOT' pdb_name,name,value 
   from v$system_parameter where con_id=0) b
where a.name=b.name
order by 1,2;

But I know there is also one view called pdb_spfile$ that would show the parameters on the PDB pseudo-spfiles:

col pdb_name for a10 
col name for a20
col value$ for a20
select pdb_name,name,value$ 
from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) 
where con_id>2 order by name;

The V$SYSTEM_PARAMETER is well documented, while the PDB_SPFILE$ is not.

Now, the set or unset parameters do not work the same way, as I expected, it trigger some strange behaviours.

(more…)

HugePages not used when starting DB with srvctl (but works with sqlplus)

Once again I end up with my clients database swapping. Why? After quick investigation, could see that HugePages were not used on the last restart of the database.

oracle@myvm1:./trace/ [oracle19] grep -B1 -A4 PAGESIZE alert*.log
2020-04-14T04:36:34.601494+02:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2020-04-14T04:36:34.601550+02:00
        4K       Configured              10              10        NONE
2020-04-14T04:36:34.601642+02:00
     2048K           247816            8193            8193        NONE
--
2020-10-13T22:59:28.856763+02:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2020-10-13T22:59:28.856818+02:00
        4K       Configured              10         4186122        NONE
2020-10-13T22:59:28.856925+02:00
     2048K           202479            8193              17        NONE

Why was that? I did use a normal start command:

oracle@myvm1:./trace/ [oracle19] srvctl start database -db mydb

Let’s put the context. This is a Oracle Restart server, with separation between oracle and grid users.

(more…)

Get unlock from PDB Lockdown profiles

PDB Lockdown profiles allow, on a multitenant database, to limit what a user can do inside a PDB.

One use case is when we want to avoid high privileged users (like Application DBAs) to perform ALTER SYSTEM or ALTER SESSION commands.

Quickly we would think we can do a PDB Lockdown profile like:

SQL> create lockdown profile lock_test;
SQL> alter lockdown profile lock_test disable statement=('ALTER SESSION');
SQL> alter lockdown profile lock_test disable statement=('ALTER SYSTEM');

The problem of this simple profile is that we can lock ourselves, also as common user, inside the lock profile.

Image that you want to enable this profile on several PDBs:

SQL> alter session set container=pdb01;
Session altered.

SQL> alter system set pdb_lockdown=lock_test;
System altered.

SQL> alter session set container=samplepdb;
ERROR:
ORA-01031: insufficient privileges

Oups, you cannot anymore change the active container!

(more…)