Database is hung

Application team reported that one of their application was having issues and they were not ablet to connect to Oracle database. When  I checked the databases it was completely hung and it was not allowing any new connections. I was able to connect database as sysdba. But it was not allowing any queries. Even simple select * from dual got hung. Then I collected hanganalyze dump and reviewed trace file. In the trace file I found that gather stats job was blocking all other sessions. Issue resolved after killing  blocker session using spid from OS level. 

Hanganalyze

-- Source DB env 
$ . oraenv
orcl

-- Connect to DB as sysdba   
sqlplus / as sysdba
-- Take hanganalyze dump
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- Wait one minute before getting the second hanganalyze
oradebug hanganalyze 3
-- Get trace file name
oradebug tracefile_name
exit

Useful Links:

How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)

Oracle Database 23c

Oracle database 23c is the long term support release. Oracle database 23c documentations are available here. Oracle database 23c free is available for download here. You can use it to test some of the Oracle database 23c features. In couple of months we will come to know about all the new features of Oracle database 23c. So next couple months will be very exiting for Oracle DBAs. Every DBA will be tying out new features in their lab environments. There are already few blogs posts/articles available online explaining some of the new features of Oracle database 23c.   

As per this Oracle blog post Oracle 23c will have 5 years premium support and 3 years extended support. Yon can directly upgrade to 23c from 19c or 21c. If you are still using 12c then you have to follow two steps approach, first upgrade you database to 19c/21c and later upgrade to 23c.  

Some of the useful links: 

• Oracle database 23c documentation
• Oracle database 23c free

Oracle DBA's Tech Upgrade Journey

Being a DBA is not easy any more. Role of DBA has changed significantly in last few years. Along with strong knowledge of Oracle Database, DBAs are expected to know about leading Opensource RDBMS like PostgreSQL and MySQL. Cloud is another latest technology which DBAs are expected to know. Automation tools like Ansible is also gaining popularity to manage repeatable database activities. 

Starting this blogs to keep track of my Tech Upgrade Journey. 

I am planning focus on following topics in this blog:  

Databases :

• Oracle
• MySQL
• PostgreSQL

Public Cloud:

• OCI
• AWS
• Azure
• GCP

Automation Tools:

• Ansible 
• Terraform