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:
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.
Now, the set or unset parameters do not work the same way, as I expected, it trigger some strange behaviours.
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.
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!
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:
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.
Man shall pay only for what it uses. This is also a motto of the Cloud and Oracle with second-level billing pushes this model.
Concerning disk space, however, it is not always easy. While terabyte prices are getting cheaper, sometimes you make a big cleanup of your database and then you would like to pay only for what is being used.
On Oracle Autonomous Databases it is the sum of datafiles size that counts.
Image now that you have a huge table and then drop it. The datafile space is not recovered.
In order to recover space you need:
Purge the recycle bin:
SQL> purge dba_recyclebin
reduce size of DATA tablespace datafile
SQL> alter database datafile <file_id> resize yyyM;
Now, this will be possible only if there are no extents used at the end of the datafile. Otherwise, one can try to alter table <table_name> move online; and then alter tablespace <tbs_name> coalesce; but this is not sure to help.
During my tests I only had one table, which made things easier.
Let’s hope that Oracle either changes the way to calculate the space used or provides a way to (continuous) defragment a datafile and make the size dynamic.
To check the storage used on Autonomous Database and find the datafile file_id, you can run the following query:
-- Get Space used by tablespace and file_id
select TBS "File_ID-Tablespace",
round(sum(bytes)/1024/1024/1024,2) USED_GB,
round(sum(bytes)/max(PROPERTY_VALUE)*100) PCT
from
(select file_id||'-'||tablespace_name TBS, bytes
from DBA_DATA_FILES
where tablespace_name!='SAMPLESCHEMA'),
(select PROPERTY_VALUE
from DATABASE_PROPERTIES
where PROPERTY_NAME = 'MAX_PDB_STORAGE')
group by rollup(TBS);
FILE_ID-TABLESPACE USED_GB PCT
------------------ ------- ---
3252-SYSTEM 0.41 2
3253-SYSAUX 3.16 16
3254-UNDOTBS1 0.44 2
3255-DATA 0.1 0
3256-DBFS_DATA 0.1 0
4.2 21
-- Get Total space used by DB
select round(USED_BYTES/1024/1024/1024,2) USED_GB,
round(MAX_BYTES/1024/1024/1024,2) MAX_GB,
round(USED_BYTES/MAX_BYTES*100,2) PCT_USED
from
(select PROPERTY_VALUE MAX_BYTES
from DATABASE_PROPERTIES
where PROPERTY_NAME = 'MAX_PDB_STORAGE'),
(select sum(BYTES) USED_BYTES
from DBA_DATA_FILES
where TABLESPACE_NAME != 'SAMPLESCHEMA');
USED_GB MAX_GB PCT_USED
------- ------ --------
4.2 20 21.01
Using the Free Tier of Oracle Cloud I created one Autonomous DB of each type – one Autonomous Transaction Processing and one Autonomous Data Warehouse (Autonomous JSON are not yet available). Then did run
select name, display_value from v$parameter where isdefault='FALSE' order by 1;
on each of the DBs, I got the follow differences (empty means not set):
Parameter
ATP
ADW
optimizer_ignore_hints
TRUE
optimizer_ignore_parallel_hints
TRUE
parallel_degree_policy
AUTO
parallel_min_degree
CPU
pdb_lockdown
OLTP
DWCS
pga_aggregate_target
3000M
5100M
resource_manager_plan
FORCE:OLTP_PLAN
FORCE:DWCS_PLAN
result_cache_max_result
1
result_cache_mode
MANUAL
FORCE
sga_target
8000M
3400M
Both the databases (PDB) share the same Container (CDB).
I did check also
select * from database_properties;
but there are no initial state differences.
Something I found interesting. I had a 2-month old ATP when I created the ADW. Immediately I saw that my old ATP was not using ASM, compared to the ADW, also that ADW was a cluster DB while the old ATP was single instance.
I recreated the ATP to check if this remained. But no. My new ATP was co-located on the same database as the ADW, so parameters are mostly the same as we could see above.
For historical reasons, I leave here the parameter changes between ATP created in June and end-August 2020. For paths, only the differences are highlighted:
Recover a Oracle 11.1.0.6 on Windows installation. I’ve all files in an external drive and I’m trying to make the DB to run on my laptop.
I’ve copied to my local disk;
Added the entries to the registry that found necessary
Re-created the Inventory
Re-created a init file with correct locations from the spfile
Now I had still had the error:
SQL> startup nomount
ORA-02778: Name given for the log directory is invalid
Nothing on the logs, all directories mentioned on pfile exist and are writable.
I’ve downloaded the Microsoft ProcessMonitor, configured to trace only Oracle company processes and looked what it tried to read.
Finally I found the culprit: %ORACLE_HOME%\RDBMS\log was missing. Just the log subfolder.
This was during the xcopy to the local disk, I excluded all “log” files, as there were some huge ones. Unfortunately it also excluded this folder. Now looks better:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size 2130160 bytes
Variable Size 989859600 bytes
Database Buffers 721420288 bytes
Redo Buffers 6918144 bytes
SQL>
It was nice to see that most of them offer either a powerpoint or icon set that you can use for your presentations. Here is the link for the ones I use:
Queue tables are used to take care of events. There is a mechanism that insert rows and another that takes care of the existing rows, usually deleting them at the end.
When possible one should use Oracle Advanced Queuing mechanism which takes care of managing the queue and a simple request will give you the next in the line.
Some applications develop their own queuing systems and it is a good way to learn how queue works.
Imagine a table with a list of elements and two specific columns:
order of arrival
priority
The first to be served is the one that, having the highest priority, was the first to arrive.
