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.
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.
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.
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.
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.
Oracle and Microsoft announced in June 2019 a cloud interoperability partnership which enables workloads across Microsoft Azure and Oracle Cloud. By creating a first joint multi-cloud solution, the software giants can each continue to provide the best of their services. At the same time customers do not need to decide which vendor they opt-out when moving their on-premises constructs.
Being myself an Oracle Database Administrator, this article aims to check what is the impact of distributing resources in multiple clouds, with databases remaining on Oracle Cloud Infrastructure.
It is not aim of the article to discuss the costs of resources on any of the clouds.
On OCI, to be able to ping between hosts, it might be necessary to add an Ingress rule that allows pings. The rule should look like below. The most important is the Protocol ICMP and type 8. The source CIDR should not be larger than the VCN CIDR.
Test if port is open:
To check if the remote port is open, I use the follow commands. This works both with IPs or hostnames and also lets you know if port is open, but listener is not running (on this port):
[opc@alfama ~]$ ### PORT UNREACHEABLE, SERVER DOWN/WRONG? ###
[opc@alfama ~]$ export CHECK_IP="10.1.2.4/1521"
[opc@alfama ~]$ timeout 1 bash -c '</dev/tcp/${CHECK_IP} && echo Port ${CHECK_IP} is open || echo Port ${CHECK_IP} is closed' || echo Connection timeout
Connection timeout
[opc@alfama ~]$ ### PORT OPEN, USING HOSTNAME ###
[opc@alfama ~]$ export CHECK_IP="luz.subnetpriv2/1521"
[opc@alfama ~]$ timeout 1 bash -c '</dev/tcp/${CHECK_IP} && echo Port ${CHECK_IP} is open || echo Port ${CHECK_IP} is closed' || echo Connection timeout
Port luz.subnetpriv2/1521 is open
[opc@alfama ~]$ ### PORT OPEN, USING IP ###
[opc@alfama ~]$ timeout 1 bash -c '</dev/tcp/${CHECK_IP} && echo Port ${CHECK_IP} is open || echo Port ${CHECK_IP} is closed' || echo Connection timeout
Port 10.1.5.3/1521 is open
[opc@alfama ~]$ ### PORT CLOSED ###
[opc@alfama ~]$ export CHECK_IP="luz.subnetpriv2/1522"
[opc@alfama ~]$ timeout 1 bash -c '</dev/tcp/${CHECK_IP} && echo Port ${CHECK_IP} is open || echo Port ${CHECK_IP} is closed' || echo Connection timeout
bash: connect: No route to host
bash: /dev/tcp/luz.subnetpriv2/1522: No route to host
Port luz.subnetpriv2/1522 is closed
[opc@alfama ~]$ ### PORT OPEN but LISTENER DOWN ###
[opc@alfama ~]$ timeout 1 bash -c '</dev/tcp/${CHECK_IP} && echo Port ${CHECK_IP} is open || echo Port ${CHECK_IP} is closed' || echo Connection timeout
bash: connect: Connection refused
bash: /dev/tcp/10.1.5.3/1521: Connection refused
Port 10.1.5.3/1521 is closed
Bonus
At Oracle OCI, one can access other VMs of the VCN using the <hostname>.<subnet>
On Azure, the <hostname> is enough to access other host of the VNet.
