Monthly Archives: November 2020

Why PGA_AGGREGATE_LIMIT appears to be set, when it is not?

This entry was posted in Oracle Oracle 12c Oracle 19c on by

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

This entry was posted in Oracle Oracle 19c on by

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

set pages 110
col pdb_name for a10 
col name for a30 
col value for a20
col pdb_value for a20
col root_value for a20
col source for a10
select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE,source from
  (select pdb_name,name,value,a.con_id, decode(ismodified,'MODIFIED','PDB SPFILE','PDB$SEED') SOURCE 
   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)
   where a.con_id>2 and (ismodified='MODIFIED' or isdefault='FALSE')) a,
  (select 'CDB$ROOT' pdb_name,name,value,con_id,null
   from v$system_parameter where con_id=0) b
where a.name=b.name and a.con_id>2
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 
and bitand(nvl(spare2,0),1)=0
order by name;

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

A slight more complete query to check parameters which are not set the same between cdb$root and the PDB is this one:

with pdb_params as (select pdb_name,name,trim(both '''' from value$) value$ 
                                   from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid)
                                   where con_id>1 and bitand(nvl(spare2,0),1)=0 ),
        cdb_params as (select name, value,inst_id,con_id from gv$parameter ),
        spfile_params as (select name,value, sid from v$spparameter where isspecified='TRUE')
  select pdb_name, pdb_params.name, value$ pdb_value, cdb_params.value root_value, spfile_params.value spfile_value
    from pdb_params, cdb_params, spfile_params
    where pdb_params.name(+)=cdb_params.name and cdb_params.name(+)=spfile_params.name
        and (value$!=cdb_params.value or value$!=spfile_params.value)
union all 
  select case when sys_context('USERENV','CON_NAME') !='CDB$ROOT' then 'CALL THIS SCRIPT FROM CDB$ROOT' end pdb_name,
        null,null,null,null from dual
order by 1,2;

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)

This entry was posted in Oracle on by

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

This entry was posted in Oracle Oracle 19c Security on by

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