Sécurité de base d'Oracle Database

La sécurité est la principale préoccupation partout si vous êtes sur le Cloud ou sur la base de données Prim. Les fournisseurs de cloud fournissent de nombreuses fonctionnalités de sécurité au niveau de l'infrastructure et certains ont également la sécurité en tant que service.

Notre objectif principal dans cet article est de maintenir la sécurité de vos bases de données on-prim.

1. Nous devons verrouiller et expirer les utilisateurs SYS et SYSTEM pour toutes les bases de données.

Vous n’avez pas besoin d’utilisateurs SYS et SYSTEM au quotidien. Alors pourquoi les garder ouverts. Si vous avez vraiment besoin d'un compte sysdba, créez-en un. Vous pouvez essayer un utilisateur qui peut faire des choses comme déverrouiller des sessions, attribuer le privilège dba_ *, etc.

           

Remarque: - Vous devez verrouiller et expirer chaque utilisateur de la base de données qui n'est pas en cours d'utilisation.

2. Assurez-vous que le paramètre SQL92_SECURITY est défini sur true

      Assurez-vous que le paramètre SQL92_SECURITY est défini sur true, ce qui est déjà recommandé par oracle. Il était par défaut FALSE dans 10g et à cause de cela la plupart du temps reporté à 11g ou 12c. Oracle a changé ce paramètre en TRUE par défaut dans 12.2 selon mon étude.

       qu'est-ce que SQL92_SECURITY?

      Les normes SQL92 spécifient que les administrateurs de sécurité doivent être en mesure d'exiger que les utilisateurs disposent du privilège SELECT sur une table lors de l'exécution d'une instruction UPDATE ou DELETE qui référence les valeurs de colonne de table dans une clause WHERE ou SET. SQL92_SECURITY spécifie si les utilisateurs doivent avoir reçu le privilège d'objet SELECT pour exécuter de telles instructions UPDATE ou DELETE.Source (docs.oracle.com)

     

       Pour changer: - modifier l'ensemble du système sql92_security = TRUE scope = spfile;

3. Remplacez audit_sys_operations par true

      Remplacez audit_sys_operations par true, qui par défaut vaut FALSE jusqu'à 11g. En 12c, c'est TRUE par défaut. La modification de ce paramètre nécessite le rebond de la base de données, alors planifiez en conséquence si vous souhaitez modifier.

       

       qu'est-ce que audit_sys_operations?

      AUDIT_SYS_OPERATIONS active ou désactive l'audit des opérations émises par l'utilisateur SYS et les utilisateurs se connectant avec les privilèges SYSDBA ou SYSOPER. Les enregistrements d'audit sont écrits dans la piste d'audit du système d'exploitation. Les enregistrements d'audit seront écrits au format XML si le paramètre d'initialisation AUDIT_TRAIL est défini sur XML.Source (docs.oracle.com)

      

         Pour changer: - modifier l'ensemble du système audit_sys_operations = true scope = spfile;

4. Réglez certains paramètres sur FALSE ou NONE

      Assurez-vous que "DBA_USERS.PASSWORD" n'est pas défini sur "EXTERNAL" pour aucun utilisateur

Assurez-vous que «REMOTE_OS_ROLES» est défini sur «FALSE».

Assurez-vous que "REMOTE_OS_AUTHENT" est défini sur "FALSE".

Assurez-vous que «REMOTE_LOGIN_PASSWORDFILE» est défini sur «NONE».

Assurez-vous que «REMOTE_LISTENER» est vide.

Assurez-vous que "GLOBAL_NAMES" est défini sur "TRUE".

Les connexions privilégiées à distance à la base de données doivent être restreintes, ce qui signifie que l'utilisateur expérimenté (par exemple, sys) ne peut pas se connecter à la base de données à distance, au lieu de le faire uniquement sur le serveur de base de données. Je crois que le réglage REMOTE_LOGIN_PASSWORDFILE = NONE est suffisant. Donnez votre avis.

Pour changer: - modifier l'ensemble du système remote_login_passwordfile = none scope = spfile;

5. Modifier les mauvaises tentatives de mot de passe

      Modifiez la valeur de votre profil pour les tentatives de mot de passe erronées à 3minimum, qui est FAILED_LOGIN_ATTEMPTS par défaut, c'est 10. Si quelqu'un ne connaît pas le mot de passe après trois tentatives, il est fort probable qu'il ne le sache pas vraiment. Si quelqu'un essaie de casser le mot de passe, cela peut verrouiller l'utilisateur.

      Pour modifier: - modifier la super limite de profil failed_login_attempts 3;

6. Remplacez sec_case_sensitive_logon par true

     Jusqu'à 11g sec_case_sensitive_logon a été défini sur FALSE et ce paramètre est obsolète dans 12c. Assurez-vous donc que vous utilisez aussi VRAI dans les bases de données 11g ou 10g. Vous devrez peut-être travailler avec l'équipe d'application à ce sujet.

      qu'est-ce que sec_case_sensitive_logon?

      SEC_CASE_SENSITIVE_LOGON active ou désactive le respect de la casse des mots de passe dans la base de données. Source (docs.oracle.com)

        Pour changer: - modifier le jeu système sec_case_sensitive_logon = false;

Voici quelques éléments de sécurité de base que vous pouvez implémenter dans la base de données ON-PRIM. Nous mettrons également à jour pour la sécurité du cloud.

N'hésitez pas à ajouter plus de points dans les commentaires. Nous publierons sur notre blog avec votre adresse Gmail. Continuez à partager :)

Oracle Mean company, not database :)

Standard Edition High Availability in 19c for SE2

 In the last few years, we saw many changes done by oracle and it feels like Oracle just wants you to move on cloud or Enterprise edition. We love the SE version because it was serving us well and was not very expensive(almost one-third cost). So now Oracle decided to get it off and introduces SE2. In Oracle 19c  there is no RAC for SE2 but there is one feature called SEHA(standard edition high availability). So let's dig into it. 

What SEHA(Standard Edition High Availability) in 19c SE2?

Starting with 19c (19.7), you can use  Oracle SE2 DB in HA mode.SEHA gives you a cluster-based failover solution for a single instance using Oracle Clusterware. Oracle SEHA gives you cluster capabilities with storage solutions that are already part of Oracle Grid Infrastructure. That means you can have Oracle Clusterware, Oracle ASM, and Oracle ACFS.

Oracle ASM and Oracle ACFS enable Oracle Grid Infrastructure to restart another instance on failover node much faster. Oracle SE2 HA is a Cluster-based active/passive Oracle Database failover solution and only designed to serve a single node only. But you need to study more about the "oracle 10 days rule" to implement SEHA.

Operating system supported for SEHA:- 

• Linux x86-64 
• Oracle Solaris on SPARC (64-bit)
• Microsoft Windows

Oracle 19c SEHA workflow:- 

• Your instance got crashed
• The grid will identify the crash
• The grid will mount the database to another node 
• The grid will start the instance on another node
• The grid will trigger a recovery
• Your instance will be available

Thanks for reading. 

Oracle Mean company, not database :)

How many sessions can oracle database handle?

Sessions can 10g/11g/12c database handle

You can control sessions by your database's SESSIONS initialization parameter from the parameter file. But you also need to take care of the PROCESSES initialization parameter as sessions are proc *1.1+5. it's impossible to exceed this number of sessions unless you raise the parameter value.

SELECT name, value FROM v$parameter WHERE name = 'sessions';

This will show you the total number of sessions:-

SELECT COUNT(*) FROM v$session;


So answer to the question is Oracle can handle thousands of connections, but you need to take care of database resources.


Always use the below formula to set sessions and parameter:-

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

Below SQL helps to check current utilization and max utilization. 

select resource_name, current_utilization, max_utilization, limit_value  from v$resource_limit
    where resource_name in ('sessions', 'processes');

As you see highlighted, We have changed the number of sessions according to our need. 

Oracle Mean company, not database :)

ORA-00600: internal error code, arguments: [ktugnb:clschk_kcbnew_14], [0], [54], [4], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [ktugnb:clschk_kcbnew_14], [0], [54], [4], [], [], [], [], [], [], [], []

As we know we should approach oracle support for any ORA-00600 but I tried to check online. There is not much in google for this error that's why writing this post.  We faced this in our PROD environment. We have one application that helps in copying the data from one database to another and got this error but not always.

Error Description : frmMain.StartReplication-> frmMain.Replication-> clsSybcDB.Process-> [Microsoft][ODBC driver for Oracle][Oracle]ORA-00600: internal error code, arguments: [ktugnb:clschk_kcbnew_14], [0], [40], [4], [], [], [], [], [], [], [], []

ORA-06512: at "OWNER.program_name", line XXXX

ORA-06512: at "OWNER.program_name", line XXXXX

ORA-06512: at line 1

[ACTION]

I found a very good suggestion from cnblog i.e. it's related ASM.  Apply bug fix 18000762

ORA-00600: internal error code, arguments: [ktugnb:clschk_kcbnew_14], [0], [54], [4], [], [], [], [], [], [], [], []

Can be temporarily fixed by expanding undo tablespace and undo retention. There may be a chance that it will not appear again after expanding UNDO tablespace and retention. 

In Our case it was an update statement:- 

update update_table
                   set TEST = rec.TEST
                                      where REC_SEQ_N = rec.REC_SEQ_N;
                if SQL%NOTFOUND then
                    insert into update_table
                    (  TEST
                     
                    )
                    values
                    (  rec.TEST
                    
                    );

               
ORA-00600: internal error code, arguments: [ktugnb:clschk_kcbnew_14], [0], [54], [4], [], [], [], [], [], [], [], []
It can be seen that it is an update statement that triggered the exception and EXTENT MAP BLOCK OF SYSTEM MANAGED UNDO SEGMENT. Thanks cnblog  for sharing this.

Similarly found on Oracle support:- 

1. Doc ID 25763082.8

2. Doc ID 18000762.8

You may check above on Oracle support. I am putting below if someone don't have oracle support account. 

1. Doc ID 25763082.8

Bug 18000762  ASM Hits ORA-600 [ktugnb:clschk_kcbnew_14]

 This note gives a brief overview of bug 18000762.
 The content was last updated on: 15-APR-2020

Description

The ORA-600 [ktugnb:clschk_kcbnew_14] occurs when running OLTP workload with
hub-and-spoke single capture replication topology for 1 source database and 2
target database on RAC with ASM environment. source

Call stack will likely include:

...kserec2 kfnRecordNPI kfncRecRemoteErr kfncLogical kfioLogical ksfdglbsz...

 

Workaround

NONE

2. Doc ID 18000762.8

Bug 25763082  ORA-600 [ktugnb:clschk_kcbnew_14] / [KCBGTCR_13] error on DML Operations - superseded

This note gives a brief overview of bug 25763082. The content was last updated on: 31-MAY-2019

Click here for details of each of the sections below.source

Description

Class violation errors on SMU extent map block due to space reuse.

Rediscovery Notes

DML Queries fails with error on consistent read.

Workaround

NONE

Note:- My issue solved by increase the UNDO size. 

Good Luck. 

Oracle Mean company, not database :)

Find Who And What SQL Is Using Temp Segments

 How Do You Find Who And What SQL Is Using Temp Segments

SELECT b.tablespace,

       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
        a.inst_id as Instance,
        a.sid||','||a.serial# AS sid_serial,
        NVL(a.username, '(oracle)') AS username,
        a.program,
        a.status,
        a.sql_id,
c.sql_text
 FROM   gv$session a,
        gv$sort_usage b,
        gv$parameter p,
gv$sqlarea c
 WHERE  p.name  = 'db_block_size'
 AND    a.saddr = b.session_addr
 AND    a.inst_id=b.inst_id
 AND    c.address= a.sql_address
 AND    c.hash_value = a.sql_hash_value
 AND    a.inst_id=p.inst_id
 AND    a.sql_id is not null
 ORDER BY b.tablespace, b.blocks
 /

From Oracle:-

SELECT x.username, x.sid, x.serial#, x.osuser, y.tablespace, y.blocks, z.sql_text

FROM v$session x, v$tempseg_usage y, v$sqlarea z

WHERE x.saddr = y.session_addr

AND z.address= x.sql_address

AND z.hash_value = x.sql_hash_value

ORDER BY y.tablespace, y.Blocks;

Oracle Mean company, not database :)

Il file di controllo ORA-00240 si è tenuto in attesa per più di XXX secondi

ORA-00240 accodamento file di controllo trattenuto per più di XXX secondi

Descrizione ： accodamento file di controllo tenuto per più di secondi di stringa

Causa 1: -

 Il processo corrente non ha rilasciato il file di controllo accodato entro il tempo massimo consentito.

Causa 2: -

 Il problema è stato esaminato nel Bug non pubblicato 7570453 - [3 RAC NODE] ORA-00240 QUANDO L'AVVIO DOPO L'AGGIORNAMENTO AL 10.2.0.4.0 che è stato chiuso come Not a Bug. Gli sviluppatori hanno confermato che questo è solo un avvertimento per far sapere al DBA che una racla CF viene mantenuta per più di 120 secondi.

Questo non è un errore e si verificherà se un accodamento CF viene trattenuto per più di 900 secondi (15 minuti) e non è questo il caso qui.

Il messaggio viene visualizzato quando ci sono molti file di dati nel database e DBWriter impiega troppo tempo a rilasciare l'accodamento CF a causa dell'apertura di questi file di dati.

Azione ： 1. Immettere nuovamente i comandi non riusciti e contattare i servizi di supporto Oracle con le informazioni sull'incidente.

   2. La raccomandazione sarà quella di ridurre questo tempo riducendo il numero di file di database che hai. Si consiglia di avere file di dati meno ma più grandi invece di file di dati più piccoli ma molti.

 Oracle Mean company, not database :)

Mise en file d'attente du fichier de contrôle ORA-00240 maintenue pendant plus de XXX secondes

Mise en file d'attente du fichier de contrôle ORA-00240 maintenue pendant plus de XXX secondes

Description: mise en file d'attente du fichier de contrôle maintenue pendant plus de secondes de chaîne

Cause 1: -

 Le processus en cours n'a pas libéré la mise en file d'attente du fichier de contrôle dans le délai maximal autorisé.

Cause 2: -

 Le problème a été étudié dans le bogue 7570453 non publié - [3 RAC NODE] ORA-00240 LORS DU DÉMARRAGE APRÈS LA MISE À NIVEAU À 10.2.0.4.0, qui a été fermé comme Pas un bogue. Les développeurs ont confirmé qu'il ne s'agissait que d'un avertissement pour informer le DBA une file d'attente des FC est maintenue pendant plus de 120 secondes.

Ce n'est pas une erreur et une erreur se produira si une mise en file d'attente des FC est maintenue pendant plus de 900 secondes (15 minutes) et ce n'est pas le cas ici.

Le message se produit lorsqu'il existe de nombreux fichiers de données dans la base de données et DBWriter prend trop de temps pour libérer la file d'attente CF en raison de devoir ouvrir ces fichiers de données.

Action: 1. Relancez toutes les commandes qui ont échoué et contactez Oracle Support Services avec les informations sur l'incident.

   2. La recommandation sera de réduire ce temps en réduisant le nombre de fichiers de base de données dont vous disposez.

  comment prendre soin d'Oracle Audit

  Qu'est-ce que la règle Oracle 10 jours ??

Oracle Mean company, not database :)

Oracle Database Basic Security

Security is the main concern everywhere if you are on Cloud or on Prim database. Cloud providers provide many security features on the infrastructure level plus some have Security as a service as well.

Our main focus in this article is to maintain the security on your on-prim databases.

1.    We need to lock and expire SYS and SYSTEM users for all databases.

You don’t need SYS and SYSTEM users on daily bases. So why to keep them open. If you really need sysdba account better create one. You may try one user who can do things like unlocking sessions, assigning dba_* privilege, etc.

           

  Note: - You need to lock and expire every user from the database that is not in use.

2.    Make Sure SQL92_SECURITY parameter is set to true 

      Make sure the SQL92_SECURITY parameter is set to true, which is already recommended by oracle. It was defaulting to FALSE in 10g and because of that most of the time carried forwarded to 11g or 12c. Oracle has changed this parameter to TRUE by default in 12.2 as per my study.

       what is SQL92_SECURITY?

      The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements. Source (docs.oracle.com) 

     

       To change: - alter system set sql92_security = TRUE scope = spfile;

3.    Change audit_sys_operations to true 

      Change audit_sys_operations to true which by default is FALSE till 11g. In 12c it is by default TRUE. Changing this parameter needs bouncing of the database, so plan accordingly if you want to change.

       

       what is audit_sys_operations? 

      AUDIT_SYS_OPERATIONS enables or disables the auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER privileges. The audit records are written to the operating system's audit trail. The audit records will be written in XML format if the AUDIT_TRAIL initialization parameter is set to XML.Source (docs.oracle.com) 

      

         To change: - alter system set audit_sys_operations=true scope=spfile;

4.    Set some parameters to FALSE or NONE     

      Ensure ‘DBA_USERS.PASSWORD’ is not set to ‘EXTERNAL’ for any user

Ensure ‘REMOTE_OS_ROLES’ is set to ‘FALSE’.

Ensure ‘REMOTE_OS_AUTHENT’ is set to ‘FALSE’.

Ensure ‘REMOTE_LOGIN_PASSWORDFILE’ is set to ‘NONE’.

Ensure ‘REMOTE_LISTENER’ is empty.

Ensure ‘GLOBAL_NAMES’ is set to ‘TRUE’.

Remote privileged connections to the database should be restricted means power user (e.g. sys) is not able to connect to DB remotely, instead of only in the DB server. I believe setting REMOTE_LOGIN_PASSWORDFILE = NONE is enough. Comment your opinion. 

To change: - alter system set remote_login_passwordfile=none scope=spfile;

5.    Change wrong password attempts 

      Change the value of your profile for the wrong password attempts to 3minimum which is FAILED_LOGIN_ATTEMPTS by default it's 10. If someone doesn’t know the password in three attempts most likely they really don’t know it. If someone tries to break the password this can lock the user.

      To change: - alter profile super limit failed_login_attempts 3;  

6.   Change  sec_case_sensitive_logon to true 

     Till 11g sec_case_sensitive_logon was set to FALSE and this parameter is deprecated in 12c. So make sure you are using as TRUE in 11g or 10g databases also. You may need to work with the application team on this. 

      what is sec_case_sensitive_logon?

      SEC_CASE_SENSITIVE_LOGON enables or disables password case sensitivity in the database. Source (docs.oracle.com) 

        To change: - alter system set sec_case_sensitive_logon=false;

These are a few basic security you can implement in ON-PRIM database. We will update for cloud security as well.

Please feel free to add more points in the comments. We will post in our blog with your Gmail address. Keep sharing :)

 Oracle Mean company, not database :)

 

Oracle Text Installation 12c

How to install Oracle TEXT

What is Oracle TEXT:-

It provided indexing with work and theme searching. It also has capabilities  to view text in query applications and  in document classification applications.

Installing Oracle Text :-

Oracle TEXT is not a part of the database installation. you can use GUI and CLI methods to configure ORACLE TEXT on DB after the DB installation.

1. You need to connect using SYS user and catctx.sql to start the installation of Oracle Text.
2. Oracle recommends to set set OVERFLOW-CONTROL=*NO-CONTROL on the terminal you are going to run catctx.sql for Oracle TEXT.
3. drdefus.sql need to run using CTXSYS user that is needed for Oracle TEXT if you are using US english text which we all do.


Steps to follow during the installation of Oracle TEXT:-
1. log in to the Db server and open a terminal to set OVERFLOW-CONTROL=*NO-CONTROL to start the installation of Oracle TEXT.
2. connect to SYS user and run the below script to install ORACLE TEXT(Make sure you change the path for the script. It's $ORACLE_HOME\admin\catctx.sql)

@@D:\app\oracle\product\12.1.0\dbhome_1\ctx\admin\catctx.sql CTXSYS SYSAUX TEMPORARY NOLOCK;

where  TEMPORARY is temp tablespace, you need to check the name of the temp tablespace before running the script to install oracle TEXT.

It will take 10-15min to complete the script which includes creating some procedures and inserting data.

3. You need run drdefus.sql if you are going to work with US English texts.
connect CTXSYS/CTXSYS@TNS
and run @@D:\app\oracle\product\12.1.0\dbhome_1\ctx\admin\defaults\drdefus.sql;



You are ready to use ORACLE TEXT.

Oracle Mean company, not database :)

Oracle database 18c XE

Oracle database 18c XE

Oracle Database 18c XE is a free for use but with below limitations on H/W and memory:-

1.       Instance:-  We can only have one instance per server with the instance name XE. Please be aware that we                             cannot change the name of the instance.  This is default and it is not clear whether we are                                   allowed to change it.

2.       Threads:-   We can only have  2 user threads.

3.       CPU:-           We can only have 2 CPUs(max) in 18c XE. If your machine have more CPU’s it will not count more                        than two CPU’s.

4.       Memory:-  We can only have RAM up to 2GB(max). 

5.       Storage:-    We can only have  12GB of user data stored (max). 

Some important Key features in 18c XE :-

1.       Pluggable databases :-   Oracle Multitenant Container Database only can have Up to 3 Pluggable Databases. 

2.       Compression :-               Basic Table Compression and Index Compression.

3.       Encryption  :-                  Transparent Data Encryption(TDE) which enables you to encrypt sensitive data that                                                 you store in tables and tablespaces.

4.       partitioning :-                 Enhance performance, availability, and manageability.

We can now run multiple XE installations inside multiple VMs/Containers with one each. That means that we can run 4 instances on 4 VMs, one instance per VM.

Oracle Mean company, not database :)

ORA-04031

ORA-04031 Errors Occurring (Doc ID 2063751.1)

SYMPTOMS ORA-04031 Errors Occurring

ORA-04031 errors occurring in a RAC instance with the trace file showing high memory usage for "ges resource dynamic" and "ges enqueues" memory in the shared pool.  This can cause LMD processes to become unresponsive leading to an instance termination.

The following query can be used to determine if this fix will help:

select substr(RESOURCE_NAME,instr(RESOURCE_NAME,'[',1,3)+1,2) , MASTER_NODE, count(*)   from gv$ges_resource   where substr(RESOURCE_NAME,instr(RESOURCE_NAME,'[',1,3)+1,2) in ('DX', 'BB')   group by substr(RESOURCE_NAME,instr(RESOURCE_NAME,'[',1,3)+1,2), MASTER_NODE    order by 3 desc;

If that shows a large number of entries (much larger than the expected total number of transactions), then this fix will help.

CAUSE for ORA-04031

This is due to unpublished bug:21373473 fixed in 12.2, and occurs due to DX and BB locks being cached but not cleared out.  This fix removes the need to cache DX and BB locks and hence reduces overall shared pool memory usage in RAC instances.

ORA-04031 errors occurring with high memory utilization for "ges enqueues" & "ges resource dynamic" memory allocations.

SOLUTION

Download and apply the one-off patch number 21373473 (Patch:21373473) for your platform and version combination. Please note that if using 12.1.0.2, then you should also apply the related patch number 21260431 (Patch:21260431) which also impacts the shared pool memory allocations identified here.

This issue can also be worked around by setting 

_GES_DIRECT_FREE_RES_TYPE="CTARAHDXBB" in the instances.



Note:- We also found ORA-01041: internal error. hostdef extension doesn't exist in alert logs that depend on ORA-04031. also suggest opening a Service request with oracle if not sure with the above method.

ORA-02051 Another Session Or Branch In Same Transaction Failed

ORA-02051 Another Session Or Branch In Same Transaction Failed (Doc ID 2253226.1)         

SYMPTOMS for ORA-02051 Another Session Or Branch In Same Transaction Failed.
Database performance is slow and caused   the transactions ORA-02051 another session or branch in same transaction failed or finalized

CAUSE for ORA-02051 Another Session Or Branch In Same Transaction Failed.
Session transactions branches caused the issue Excessive Waits On The Event "Global transaction acquire instance locks"

SOLUTION
Please use below sql and identified underscore parameter values for ORA-02051 Another Session Or Branch In Same Transaction Failed :
SQL>
select a.ksppinm "Parameter", b.ksppstvl "Session Value",c.ksppstvl "Instance Value"  FROM x$ksppi a,x$ksppcv b, x$ksppsv c  WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/'  AND (a.ksppinm like '%clusterwide_global%' or a.ksppinm like '%disable_autotune_gtx%') ;

output:-

Parameter                                                          Session Value         Instance Value
----------------------------------------             -------------------- --------------------
_clusterwide_global_transactions             TRUE                              TRUE
_disable_autotune_gtx                                  FALSE                             FALSE


Set following to solver ORA-02051 Another Session Or Branch In Same Transaction Failed :-
alter system set "_clusterwide_global_transactions"=false scope=spfile;
alter system set "_disable_autotune_gtx"=TRUE scope = spfile;  

Note:-  bounce the instances for the changes to take effect

Oracle Mean company, not database :)

ORA-3136 WARNING: inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136) 

The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

You may also witness ORA-12170 without timeout error on the database server sqlnet.log file. This entry would also have the client address which failed to get authenticated. Some applications or JDBC thin driver applications may not have these details.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs, the warning would appear in the alert log and the client connection will be terminated.

This timeout restriction was introduced to combat Denial of Service (DoS) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.

Cause:

There can be three main reasons for this error

1.             Server gets a connection request from a malicious client which is not supposed to connect to the database, in which case the error thrown is the correct behavior. You can get the client address for which the error was thrown via sqlnet log file.

2.             The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

3.             The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

Diagnosis: 

The default value of 60 seconds is good enough in most conditions for the database server to authenticate a client connection. If it’s taking longer period, then it’s worth checking all the below points before going for the workaround:

1. Check whether local connection on the database server is successful & quick.

2. If local connections are quick, then check for underlying network delay with the help of your network administrator.

3. Check whether your Database performance has degraded by anyway.

4. Check alert log for any critical errors for e.g., ORA-600 or ORA-7445 and get them resolved first.

Resolution:
 
These critical errors might have triggered the slowness of the database server.

As a workaround to avoid only these warning messages, you can set the parametersSQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername
to the value more than 60. 

In server side sqlnet.ora file add SQLNET.INBOUND_CONNECT_TIMEOUT

SQLNET.INBOUND_CONNECT_TIMEOUT = 120

In listener.ora file INBOUND_CONNECT_TIMEOUT_listenername

INBOUND_CONNECT_TIMEOUT_LISTENER = 110

From Oracle version 10.2.0.3 onwards the default value of INBOUND_CONNECT_TIMEOUT_ is 60 seconds. For previous releases it is zero by default.

Oracle Mean company, not database :)

What is Deprecation of Non-CDB Architecture means

Please check the below link for point (8.1.1 : Deprecation of Non-CDB Architecture).

https://docs.oracle.com/database/121/UPGRD/deprecated.htm#BABDBCJI

"Deprecation" means "at some stage in future, Oracle *might* stop doing enhancements on this features, and at some stage after that, Oracle *might* no longer support it".

In 12.1.0.1.0 non-CDB (old architecture) is not deprecated whereas from 12.1.0.2.0 it got deprecated. Even if you have single database, it is better to have it in multitenant architecture as 1CDB and 1PDB.

As per Oracle document:- https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/deprecated-features-oracle-database-12c-r2.html#GUID-5D181F03-F74D-4888-B7B2-7176CF6FA8F8

Deprecation of Non-CDB Architecture

The non-CDB architecture was deprecated in Oracle Database 12c. It can be desupported and unavailable in a release after Oracle Database 19c .

Oracle recommends use of the CDB architecture.

Oracle Database 19c ?

Release 12.2: New releases will be annual and the version will be the last two digits of the release year. The release originally planned as 12.2.0.2 will now be release 18c, and the release originally planned as 12.2.0.3 will be release 19c. Releases 18c and 19c will be treated as under the umbrella of 12.2 for Lifetime Support purposes. The current plan is for Oracle Database 19c to be the last release for 12.2. This may change in the future to Oracle 20 as the last release for 12.2

COUNT STOPKEY ROWNUM optimization

No COUNT STOPKEY in explain plan:- ROWNUM optimization

Product:- Oracle Server (RDBMS)

Range of versions believed to be affected:-  12.1

confirmed affected:- 1. 11.1.0.7
                                    2. 10.2.0.4

Platforms affected:-         all

So I was doing explain plan for below SQL on database version 11.2.0.1.0:-

select * from test where rownum = 0;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1829668517

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |  3219K|   733M| 35580   (2)| 00:07:07 |
|   1 |  COUNT              |               |       |       |            |          |
|*  2 |   FILTER            |               |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST          |  3219K|   733M| 35580   (2)| 00:07:07 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=0)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     129363  consistent gets
     129358  physical reads
          0  redo size
       6210  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

On Database version 11.2.0.4.0 it was:-

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2416982823

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |   240 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST            |     1 |   240 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=0)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       6210  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

After studying on oracle support I found this as a bug on 11.2.0.1.0 explained under Oracle
Please check your version for the bug because you can easily see the difference and performance matrix between both explain plans.

Beware! This use of rownum< can cause performance problems. Using rownum may change the all_rows optimizer mode for a query to first_rows, causing unexpected sub-optimal execution plans. One solution is to always include an all_rows hint when using rownum to perform a top-n query.

Keep sharing :)