[Warning] Aborted connection to db: user: host: mariadb error log

MariaDB error log reporting multiple error like this and flooding the logs.

Due to refused connections max_connect_errors and max_error_count is breaching its limit and impacting new connections 

[Warning] Aborted connection xxxx to db: xxxx user: xxxx host: xxxx (xxxx)

Although I am still struggling to fix those , have taken below steps to reduce the impact 

1. Stop logging errors in log , although its not good idea but if its repetitive in nature better to let it go

Top 10 tips for Priority Issue Handling in IT Operation Support

At the time of writing this blog i was 17 years of experience in IT operation support , centered & conquered many fire fighting situations.

Over the years while troubleshooting those priority issues i came across very interesting human mindsets, flaws in infrastructure & processes and it built my confidence at certain extent that I started enjoying it 😊

Every priority issue brings in so much to your instincts that one who comprehend will dives and survives.

Although following tips are baselined around databases technology but they are not limited to it and can be referred by anyone.

Priority Issue means something in business critical serving application is not working as expected causing unavailability and revenue loss.

Assuming you are a technical support engineer or manager who's primary responsibility to resolve the situation for your customer , then let's go..

1. Get All Questions Answered

Lucky if you are the first one to sense that issue has happened but fine even if it's not.

As soon as you enter the battle ground which could be in the form of conference meeting or group chat or set of impatient minds around your workstation , keep in mind that first few minutes of your questions to audience will be answered with huge respect and with good response time.

Database Auto Startup in oracle 19c

Follow the Steps below to enable auto startup of DB instance , Listener and PDB's

1. Create dbora file as below with ORACLE_HOME

[root@oraclesrv ~]# cat /etc/init.d/dbora
#! /bin/sh -x
#
# chkconfig: 2345 80 05
# description: start and stop Oracle Database Enterprise Edition on Oracle Linux 5 and 6
#
# In /etc/oratab, change the autostart field from N to Y for any
# databases that you want autostarted.
#
# Create this file as /etc/init.d/dbora and execute:
#  chmod 750 /etc/init.d/dbora
#  chkconfig --add dbora
#  chkconfig dbora on
# Note: Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.
# ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
ORACLE_HOME=/u01/app/oracle/db1
#
# Note: Change the value of ORACLE to the login name of the oracle owner
ORACLE=oracle
PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
case $1 in
'status')
       echo -n $"Oracle Process: "
      su $ORACLE -c "ps -ef | grep pmon | grep -v grep; ps -ef | grep -i listener | grep -v grep;" &
        ;;
'start')
       echo -n $"Starting Oracle: "
      su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &
        ;;
'stop')
       echo -n $"Shutting down Oracle: "
       su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" &
        ;;
'restart')
       echo -n $"Shutting down Oracle: "
       su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" &
        sleep 5
      echo -n $"Starting Oracle: "
      su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &
        ;;
*)
        echo "usage: $0 {start|stop|restart}"
        exit
        ;;
esac
exit

Top 10 most common tasks performed by any DBA

Administrators of Oracle , MySQL , MS-SQL , DB2 , Postgress or any other database technology have following duties in common to deal, with only change in architecture and command lines to manage them.


  1. Monitoring


Database monitoring has huge significance in database administrations , if not done efficiently it can lead to service outage i.e. application unavailability to users.

Days are gone when DBA used to monitor the database manually through a checklist after every hour or two and making note of DB server load , locking/ blocking sessions , OS mount point utilization , tablespace / container space consumption , Archive log utilization , errors in logs etc . 

Top 30 Oracle 19c feature quick look to make dba life easy

 #1 Dynamic fast_start_failover setting to allow changing standby database without need of stopping and starting the data guard broker. 


 #2 Observe-only mode to discover any failures anticipated during fail over without performing actual transition to available standby database. 


 #3 Propagate restore point to standby which was created in primary database to perform point in time recovery. This allows standby to be restored back to the same state as primary using restore point information kept in control file.


 #4 Standby db flashback when primary flashbacks which helps dba by avoiding overhead task of re-creating standby database.

restart or invoke a shell script from inside in linux

As an Unix or Database Administrator, we often write a shell script to perform maintenance task which can be called manually , in crontab or scheduled in deployment pipeline.


Requirement to invoke a shell script within a single call helps to reuse the input's passed such as  password , file name or any other input variable.


The EASY method to do the same is by recalling shell script within a shell script again ...

#!/bin/sh

sh test.sh $0 $1

The SMART way to do the same is by using the goto method to call a block for re-invoking a part of the code ...

#!/bin/sh

goto()
{
echo "Part of Code to be invoke repeatedly.." 
}

while true; do

 echo -n "Proceed Y/N:"
 read INPUT

 if [ $INPUT == "Y" ] ; then
    goto;
 else
    break
    exit
 fi

done

smtp-server: 501 5.1.7 Invalid address

Error:

smtp-server: 501 5.1.7 Invalid address
. . . message not sent

Scenario:


mailx receives smtp-server: 501 5.1.7 Invalid address when shell script triggered via crontab job ; no errors received and mail delivered to recipient when same shell script executed on linux prompt.



Cause:


1. Invalid email address or domain name in recipient filled value -c or at the end of line in mailx.

interview questions on concurrent manager oracle apps with solution

Q1. Why concurrent request is in pending standby mode for long time ?

Answer:

Cause

Phase code Pending and Status code Standby means concurrent request has been processed by ICM (Internal Concurrent Manager) , however it is held by CRM(conflict resolution manager) due to incompatibilities with other programs that are currently running. One more possibility of pending standby is CRM itself is not available / not running.

Solution

i. Through SYSADMIN login go to System Administrator responsibility -> concurrent manager -> CRM And check if it is running state if not then activate the same

ii. In above scenario if CRM is running and still concurrent request is in pending standby state then click on verify button and resubmit the request.

iii. if above two steps doesn't help then application developer intervention is required to re-validate the incompatibilities in application developer responsibility -> concurrent -> program -> Incompatibilities 

concurrent manager workflow in oracle apps

Concurrent Manager has executes job defined by program executable as per schedule specified during concurrent request submission.

Concurrent manager has below managers helps to processes the concurrent request based on their defined functions

ICM - Internal concurrent manager
SM - Standard manager
CRM - Conflict resolution manager

Functioning:

1> ICM - Internal Concurrent manager

- TO start/stop/reset other concurrent managers
- Should be running prior to other managers

oracle network error solution

Listing down oracle common network TNS error and solution faced mostly during database administration


You may click on individual ORA- error string to find more details in for of error details , cause and possible solution to fix in your environment.

#1 ORA-12545: Connect failed because target host or object does not exist


#2 ORA-12547: TNS: lost contact

TOP 10 common oracle error and solution

Listing down top 10 oracle error and solution faced mostly during database administration


You may click on individual ORA- error string to find more details in for of error details , cause and possible solution to fix in your environment.


#1  ORA-01031: insufficient privileges


#2  ORA-03114: not connected to ORACLE


#3  ORA-27037: unable to obtain file status

future of DBA post autonomous database 18c

                                 

                                   Life of DBA

Database administration considered as a formidable role in IT industry , it takes huge efforts to get noticed after background work performed by every individual DBA through qualification , certification and innovation.

Day to day production issue handling across the clock 24/7 , keeping database up and running , abandon work-life balance , performing optimal to application queries without a single human error or common mistakes is backbreaking.

• Database health check 
• Backup / Recovery
• Performance Tuning
• Patching / Upgrades
• User queries
• Security
• Root cause analysis
• Client process

               


     Launch of autonomous database cloud 18c

• Self- Driving
• Self- Scale
• Self- Repair

what is oracle 18c autonomous database

Oracle unveils worlds first autonomous database cloud on Oct 2, 2017 by oracle chairman of board and CTO Larry Ellison.

Features:

Ground Breaking Machine learning technique to automate and eliminate human labor , reducing human errors, no more manual tuning leading improved availability with high performance and security that is also at very lower cost which reflect as next generation industry-leading databases.

Oracle autonomous database cloud to attain availability of 99.95% with only 30 min of planned/Unplanned downtime.

ASM DiskGroup Migration

Step Wise Disk-Group Migration from DG01 to DG02

Scenario:

DBTST is an Oracle11g single instance database on ASM which holds complete database in Disk Group named DG01 spread across 4*50 GB of Disks. Objective is to migrate complete databases from DG01 to DG02 Disk Group. Here DG02 is a new Disk Group on 500 GB disk (/dev/mapper/mpathe)

col NAME for a20
col PATH  for a40
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH from v$asm_disk;

GROUP_NUMBER DISK_NUMBER MOUNT_S STATE OS_MB   TOTAL_MB  FREE_MB NAME       PATH
------- -------- ------- -------- ---------- ---------- -------- --------------- ----------------------------------------
           1           2 CACHED  NORMAL                 51200    51200         43 DG01_0002    /dev/mapper/mpathc
           1           1 CACHED  NORMAL                 51200    51200         41 DG01_0001    /dev/mapper/mpathb
           1           3 CACHED  NORMAL                 51200    51200         45 DG01_0003    /dev/mapper/mpathd
           1           0 CACHED  NORMAL                 51200    51200         46 DG01_0000    /dev/mapper/mpatha
           0           0 CLOSED  NORMAL                 512000          0          0                      /dev/mapper/mpathe

Migration Steps:

How to enable sql session tracing in oracle database

Enabling session tracing in oracle database


1. Identify sql client session to be traced


For example you are planning to trace the session for module ABC , identify the SPID for session e.g. 13762780

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set line 1000
col OSUSER for a10
col SID for 999
col USERNAME for a20
col MACHINE for a20
set pagesize 100
SELECT distinct
a.sid,
a.serial#,
b.spid, sq.sql_id,
b.pid,
a.username,
a.machine,
a.status,
a.module,
a.osuser,
a.LAST_CALL_ET/60 "Inac_Min",a.logon_time,c.event, sq.sql_text
FROM
v$session a,
v$process b,
v$session_wait c,
v$sqlarea sq
WHERE
a.username IS NOT NULL
AND
a.paddr=b.addr
and a.username='GENEVA_ADMIN' and a.sql_hash_value=sq.hash_value
and (upper(a.module) like '%ABC%')
and c.sid=a.sid;


2. Enable trace

SQL2314W SQLSTATE=01650 runstats issue in db2

Error:

db2 "runstats on table <SCHEMA_NAME>.<TABLE_NAME>"

SQL2314W Some statistics are in an inconsistent state. The newly collected "TABLE" statistics are inconsistent with the existing "INDEX" statistics.SQLSTATE=01650

exec(): 0509-036 Cannot load program sqlplus

Error

1.

$sqlplus -v

exec(): 0509-036 Cannot load program sqlplus because of the following errors:

        0509-130 Symbol resolution failed for /usr/lib/libc.a[aio_64.o] because:

        0509-136   Symbol kaio_rdwr64 (number 1) is not exported from

                   dependent module /unix.

2.

$sudo ./rootpre.sh

+ sudo ./rootpre.sh

Required file ./pw-syscall32 is missing.

Aborting pre-installation procedure. Installations of Oracle may fail.

Background:

Oracle 9.2.0.4 binary copied from one to another server having same configuration for server migration as well as 9.2.0.8 upgrade.

Newly copied Oracle 9i binary has been relinked. 

Post which above errors observed

Oracle Database Timezone Issue

Problem:

Post changes in Linux system timezone (in /etc/sysconfig/clock file ) from ZONE="America/New York" to ZONE="Asia/Kolkata" systimetamp over the DB link or TNS alias started reflecting old/wrong timezone 

sqlplus <username>@<TNS_Alias>/<password>;

SQL> select systimestamp,current_timestamp from dual;

SYSTIMESTAMP                            CURRENT_TIMESTAMP
-----------------------------------     -------------------------------------------
22-05-13 06:11:17.900646 AM -05:30      22-05-13 05:11:17.900654 PM +05:30 


Here , +05:30 was the correct time-stamp for Timezone "Asia/Kolkata"

Analysis:

It was identified that time zone of the database server was not matching with one of the Data guard setup in Linux zone configuration file /etc/sysconfig/clock

Required ZONE="Asia/Kolkata" 
But found ZONE="America/New York"

OCIException OCI error 1034

Error:

2013-04-26 22:43:38.154: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::connectInt (2) Exception OCIException

2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection:connect:excp OCIException OCI error 1034

2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: connect1 errcode 1034

2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: connect2 oracleHome /oravl01/ora11g/112_GRID oracleSid CRAMADG2

2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::connectInt: server not attached

2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::shutdown mode 4

2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] ORA-01031: insufficient privileges

2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: shutdown errcode 1031

2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: oci error try shutdown abort

2013-04-26 22:43:39.182: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::connectInt: server not attached

2013-04-26 22:43:39.210: [ora.cramadg.db][3523208960] {2:35163:2} [start] ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Process ID: 0

Session ID: 0 Serial number: 0

Background:

PRVF-9652 : Cluster Time Synchronization Services check failed

Error:

NTP daemon slewing option check failed on some nodes
Check failed on nodes:
PRVF-5436 : The NTP daemon running on one or more nodes lacks the slewing option "-x"
Clock synchronization check using Network Time Protocol(NTP) failed
PRVF-9652 : Cluster Time Synchronization Services check failed

When :

Error at cluster verification check while installing 11g Grid Infrastructure (11.2.0.3) on Linux

Analysis:

During GI installation cluster verification has failed without showing much details in GUI. So $GRID_HOME/cfgtoollogs/oui/installActions*.log has been mined and found cluster verification check failure errors .  

Installation did not impacted due to this error, after closing GUI which has shown partial successful completion of installation cluster verification has been invoked manually as below to verify the root cause


$cluvfy stage -post crsinst -n inpudicrmrdbprtrpt1,inpudicrmrdbprtrpt2