How to Change the Timezone in Linux

How to Change the Timezone in Linux

Easy steps to change timezone on "CentOS Stream release 8"

It's very simple to change the timezone of Linux machines. Sometimes as DBA you need to change the timezone on cloud environments when you handling multiple sites on the cloud.

Just follow the below steps to "Change the Timezone in Linux" :-

1) login with root or any sudo user to your machine and check current timezone. 

   [root@TEST-D01 ~]# timedatectl

                                       Local time: Tue 2021-07-13 03:40:06 EEST

                                       Universal time: Tue 2021-07-13 00:40:06 UTC

                                         RTC time: Tue 2021-07-13 00:40:06

                                        Time zone: Europe/Kiev (EEST, +0300)

                                     System clock synchronized: no

                                      NTP service: n/a

                              RTC in local TZ: no

My Current timezone is "Europe/Kiev (EEST, +0300)". 

So Let's change the timezone to Asia. 

2) You can get the list of all timezones in Asia by "timedatectl list-timezones |grep Asia" 

                    [root@TEST-D01 ~]# timedatectl list-timezones |grep Asia

                        Asia/Aden

                        Asia/Almaty

                        Asia/Amman

                        Asia/Anadyr

                        Asia/Aqtau

                        Asia/Aqtobe

                        Asia/Ashgabat

                        Asia/Atyrau

                        Asia/Baghdad

                        Asia/Bahrain

                        Asia/Baku

                        Asia/Bangkok

                        Asia/Barnaul

                        Asia/Beirut

                        Asia/Bishkek

                        Asia/Brunei

                        Asia/Chita

                        Asia/Choibalsan

                        Asia/Colombo

                        Asia/Damascus

                        Asia/Dhaka

                        Asia/Dili

                        Asia/Dubai

                        Asia/Dushanbe

                        Asia/Famagusta

                        Asia/Gaza

                        Asia/Hebron

                        Asia/Ho_Chi_Minh

                        Asia/Hong_Kong

                        Asia/Hovd

                        Asia/Irkutsk

                        Asia/Jakarta

                        Asia/Jayapura

                        Asia/Jerusalem

                        Asia/Kabul

                        Asia/Kamchatka

                        Asia/Karachi

                        Asia/Kathmandu

                        Asia/Khandyga

                        Asia/Kolkata

                        Asia/Krasnoyarsk

                        Asia/Kuala_Lumpur

                        Asia/Kuching

                        Asia/Kuwait

                        Asia/Macau

                        Asia/Magadan

                        Asia/Makassar

                        Asia/Manila

                        Asia/Muscat

                        Asia/Nicosia

                        Asia/Novokuznetsk

                        Asia/Novosibirsk

                        Asia/Omsk

                        Asia/Oral

                        Asia/Phnom_Penh

                        Asia/Pontianak

                        Asia/Pyongyang

                        Asia/Qatar

                        Asia/Qostanay

                        Asia/Qyzylorda

                        Asia/Riyadh

                        Asia/Sakhalin

                        Asia/Samarkand

                        Asia/Seoul

                        Asia/Shanghai

                        Asia/Singapore

                        Asia/Srednekolymsk

                        Asia/Taipei

                        Asia/Tashkent

                        Asia/Tbilisi

                        Asia/Tehran

                        Asia/Thimphu

                        Asia/Tokyo

                        Asia/Tomsk

                        Asia/Ulaanbaatar

                        Asia/Urumqi

                        Asia/Ust-Nera

                        Asia/Vientiane

                        Asia/Vladivostok

                        Asia/Yakutsk

                        Asia/Yangon

                        Asia/Yekaterinburg

                        Asia/Yerevan

Now We have the list of all timezones in Asia. Let's set it e.g. to Asia/Qatar.

3. To change the timezone from Europe/Kiev to Asia/Qatar. just run 

[root@TEST-D01 ~]# timedatectl set-timezone Asia/Qatar

[root@TEST-D01 ~]# timedatectl

               Local time: Tue 2021-07-13 04:02:48 +03

           Universal time: Tue 2021-07-13 01:02:48 UTC

                 RTC time: Tue 2021-07-13 01:02:49

                Time zone: Asia/Qatar (+03, +0300)

System clock synchronized: no

              NTP service: n/a

          RTC in local TZ: no

ORA-00907: missing right parenthesis ! while creating view

ORA-00907: missing right parenthesis 

"ORA-00907: missing right parenthesis" can happen if you really missing it in your SQL, But if that is the case you will not search on google.com :).

One of Our oracle SQL developers reported that while creating a view on one of the oracle database were facing "ORA-00907: missing right parenthesis". We started troubleshooting and found that it's a bug from the oracle in 12.2 databases.  The solution is just to apply the patch or change the view of the code. 

CREATE OR REPLACE FORCE VIEW "TEST_V" ("TEST_COL1", "TEST_COL2") AS

SELECT

  -----

FROM TEST_T A WHERE TEST_COL1 IS NOT NULL

GROUP BY

  -----

  REGEXP_REPLACE(LISTAGG(TEST_COL1,';' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY TEST_COL1),'([^;]+)(;\1)*(;|$)', '\1\3') AS TEST,

                                        *

ERROR at line 12:

ORA-00907: missing right parenthesis 

This view we were able to create in 12.1 but not in 12.2  so we in the last asked developers to change the view. That is how we handle "ORA-00907: missing right parenthesis" issue.  If you are facing the same issue and are not able to find any clue, just try to create it on the older version. The actual information regarding the bug is not available yet but we update soon we will find it. 

Oracle Means company, not database :) Keep sharing...

Oracle Fail Safe Compatibility with 19c

 Is oracle fail Safe is not compatible with the windows 2019?

The answer is yes. 

Below is the compatibility Matrix.:- 

Note that version 4.1.0 is already on extended support. 

Also, check "Oracle Fail Safe is deprecated with Oracle Database 19c".

Oracle Mean company, not database :)

ORA-65114: space usage in container is too high

I was creating tablespace in a newly created PDB and  was facing  " ORA-65114: space usage in the container is too high "

ORA-65114: space usage in the container is too high 

So it happens when you created PDB with a storage option less than you need. Below is the example, I used to create PDB:

create pluggable database TEST admin user admin identified by "oracle" default tablespace USERS

 datafile '/u02/oradata/TEST/pdb1_users01.dbf' size 250m autoextend on

 storage (maxsize 1g max_shared_temp_size 1g)

 file_name_convert=('/u02/oradata/TEST/datafile/','/u02/oradata/TEST/TEST');

Want to know drawback of oracle ?  check this

This mean oracle will not go beyond 1G. As I used MAX to 1G and below is the error. 

Below is the error:- 

SQL> CREATE TABLESPACE "TEST" LOGGING DATAFILE '/u02/oradata/TEST.DBF' SIZE 750M AUTOEXTEND ON NEXT 8M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "TEST" LOGGING DATAFILE '/u02/oradata/TEST.DBF' SIZE 750M AUTOEXTEND ON NEXT 8M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

*

ERROR at line 1:

ORA-65114: space usage in container is too high 

Solution:-  

Just increase the storage limit of the pluggable database. Run the following SQL in the current PDB to limit the size of all datafiles of that PDB:

 SQL> ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Pluggable database altered.

OR if you want a limited SIZE, You can still define the size. 

SQL> alter pluggable database storage (MAXSIZE 120G);

Note:- If not defined storage option by default of  MAX_PDB_STORAGE is no limit.

How to check the max size of PLUGGABLE DATABASE STORAGE?

To check value in current PDB, Conenct to PDB and run below :

select PROPERTY_VALUE FROM database_properties WHERE property_name = 'MAX_PDB_STORAGE';

To check value in current CDB for all PDB'S, Conenct to CDB and run below :

select PROPERTY_NAME,PROPERTY_VALUE,DESCRIPTION,CON_ID FROM cdb_properties WHERE property_name = 'MAX_PDB_STORAGE';

Oracle Mean company, not database :)

If it helps. Please comment and let us know :)

OPatch failed with error code 73

I was upgrading 19.3 to 19.7 and applying the below patch and during the patch, I faced the error "OPatch failed with error code 73:- 

30805684: OJVM RELEASE UPDATE 19.7.0.0.0

Note:- All instances were down. 

Please check below opatch process :- 

opatch apply

Oracle Interim Patch Installer version 12.2.0.1.21

Copyright (c) 2020, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc

OPatch version    : 12.2.0.1.21

OUI version       : 12.2.0.7.0

Logfile location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-26-35PM_1.log

Verifying environment and performing prerequisite checks...

Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

Following active executables are not used by opatch process :

/u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle

Following active executables are used by opatch process :

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-26-35PM_1.log

OPatch failed with error code 73

In Logs:- 

Following active executables are not used by opatch process :/u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle

Following active executables are used by opatch process : Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

Following active executables are not used by opatch process : /u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle

Solution:- 

This was happening because there was one session that was still connected to sqlplus binaries. 

ps -ef|grep sqlplus 

Killed the session and successfully completed it. 

opatch apply

Oracle Interim Patch Installer version 12.2.0.1.21

Copyright (c) 2020, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory from 

                  : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc

OPatch version    : 12.2.0.1.21

OUI version       : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-33-39PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   30805684

Do you want to proceed? [y|n] y

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')

Is the local system ready for patching? [y|n] y

User Responded with: Y

Backing up files...

Applying interim patch '30805684' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 30805684 successfully applied.

Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-33-39PM_1.log

 

OPatch succeeded.

Note:- Make sure there are no binaries used during applying the patch. 

Keep sharing.  

Oracle Mean company, not database :)

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