Real issues - Oracle , Postgres, MongoDB, mysql , Pyhon

Oracle :

- GG process was creating  blocking session in DB . Wait event was Enq- TM contention

Reason :  Foreign key column is not indexes and hence causing contention.  As per oracle doc, If ver is > 11.2 , Session take TM lock (table exclusive lock) while modifying foreign key column.

- ORA-04031   :  Shared pool out of memory 

Can occur when Shared pool is configured low or its fragmented.

To avoid this error, 

- Make sure application code is reusing cursor as much as possible. Use bind variables.

- RMAN incremental backup is running slow.

Issue :  Some queries are taking long time and controlfile backup section has more data.

Solution :  We do not have RMAN catalog DB , so here are the options to resolve the issue.

1, Recreate controlfile.

2, Clear RMAN section using DBMS_BACKUP_RESTORE.resetCfileSection() package

3, Tune backup queries which are running long.

I trace session , identify long running queries and  implemented hint using SQL_PATCH to tune the queries. 

- Multiple session complained with message 'resource busy and acquire with NOWAIT specified or timeout expired' 

1, We found blocking session.

2, Different blocker had different wait events  such as  'library cache lock load' and 'enq-SQ contention' and 'Flashback log sync'

3,   I investigated block chain. and found that LGWR was the main holder and was running with wait event 'controlfile sequential read' 

Additionally , I found FS (ORACLE_HOME) was 100% full and archive generation rate was very high in last couple of hours. Due to high load on DB , LGWR was taking long time to read controlfile and same time file system got full so process got hanged.   

- Migrate Oracle 12.1 to Oracle 12.2 on Private Cloud (Oraas) :

Issue :  There were active - active oracle databases setup across 7 regions and customer has a requirement to migrate all databases to Cloud environment(Oraas).

Solution :  Size of the database was between 20 TB to 50TB.

To minimize downtime, transportable tablespace method was used (xtts)  to migrate and upgrade database.