Author Archives: kishan

I create a table and insert some rows into the table t11. When i create id column with unique constraint,the data dictionary is not allowing to insert duplicates to the table. So we can either drop or disable the constraint on the id column I disable the constraint and somehow inserted the rows by mistakeContinue reading “Identify duplicate rows in a table and delete them”

we had a production database for one of our cloudera application.The application users started scheduled and batch jobs on the database.There was a complaint from application team about the slow performance.When we diagnosed the logfile,we found out that there was frequent log switches and archive generation which filled FRA space very fast.So we had toContinue reading “How many log switches per hour is normal?”

We can calculate the IO rate of the disk from the OS to get an estimate of read/write to and from disk. Locate a particular datafile on any of storage partition Use dd to read the datafile and write the output to a output file and use time to determine the time taken for roundContinue reading “Calculate the bandwidth of IO subsystem from OS linux”

When we migrate a database to new server due to out of support by vendor eg: physical server,it is good approach to clone the oracle home from source to destination and recover the database.It depends on individuals to install fresh database software or clone. Check the oracle home of the database locate to the oracleContinue reading “Cloning Oracle home”

Junior DBA’s struggle sometimes and scratch their head without knowing the cause of simple errors. Instead of trying to search for the error on internet,it is better to look at the error and identify the cause. I try to execute runInstaller from a directory path to install the grid software where the software files actuallyContinue reading “./runInstaller: line 106: /: No such file or directory”

I was running a simple sql select query to fetch some details from a table.I generated an execution plan for the query to check for optimizers plan for the query processing I observe some interesting details on the execution plan results I can observe 3 sorts in PGA when i ran the query for theContinue reading “Does hard parsing lead to sort in memory???”

It is very dangerous when we deal with online redolog files in database which records the active transaction changes into it and needed for media ,block and crash recovery based on their states We have four usual states of redologs in a typical oracle database 1)active 2)inactive 3)current 4) unused active – require for instanceContinue reading “Drop online redolog group”

Client use the user process and try to connect to the oracle database where PMON monitors and registers the incoming connections. PMON updates the service to the listener every now and then ,when a new connection connect to the database. PROCESSES is the parameter which allocates the maximum load for a connection. When the loadContinue reading “TNS:listener could not find available handler with matching protocol stack”

Oracle doc says PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes. SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login SESSIONS specifiesContinue reading “How to avoid ORA-00018 maximum number of sessions exceeded”

I create a test user and grant basic connect privilege to the database I configure the tns entry and listener entry on remote server I try to connect to the rman from remote client !! I get ORA – 01017 error!! However i given the correct username and password with right service name I amContinue reading “Why database remote connection fail?? ORA – 01017”