ERROR: ORA-01994: GRANT failed: password file missing or disabled SOLUTION: 1)Log in as the same user who owns the file $ORACLE_HOME and create the password file as follows. $orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID…
Read MoreERROR: ORA-30012: undo tablespace ‘UNDOTBS’ does not exist or of wrong type. CAUSE: The parameter UNDO_MANAGEMENT is set to AUTO but the undo_tablespace does not exist or is not of…
Read MoreHere this blog is useful to understand what will happen while giving startup or shutdown database commands in background. Full details about oracle database startup and shutdown in background.…
Read MoreIf IMPDP hangs long time, and if checking in v$sessionwait shows it is waiting on “wait for unread message on broadcast channel”, check the alert log. You may see something…
Read MorePlease follow to Add Services to a Single instance While the database is up, run the following as sys: alter system set service_names=’DB_DEFAULT_SERVICE, DB_HTML_DB, DB_DEV_SERVICE’ When you check lsnrctl it…
Read MoreQuery will find out TOP Elapsed time SQL in certain hours with below script SELECT st.sql_text, sub.sql_id, sub.ELAPSED_TIME PER_EXEC_ELAPSED_TIME_MINUTES FROM DBA_HIST_SQLTEXT st, ( SELECT t.sql_id, ROUND ( SUM (t.elapsed_time_delta /…
Read MoreProvided script will find out the UNDO tablespace blocks usage to the corresponding SQL ID which you will provide. select dhs.sql_id, round(sum((vu.activeblks*8)/1024)) ActiveUNDOMB, round(max((vu.unexpiredblks*8)/1024)) UnexpiredUNDOMB ,max(vu.tuned_undoretention)TunedUndo, max(vu.begin_time)newest_time from v$undostat vu…
Read MoreThis Query will provide much information about the multiple hash plans… select SQL_ID , PLAN_HASH_VALUE , sum(EXECUTIONS_DELTA) EXECUTIONS , sum(ROWS_PROCESSED_DELTA) CROWS , trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS , trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS from DBA_HIST_SQLSTAT S,…
Read MoreFind Index Fragmentation and Usage To Find Index Fragmentation ~~~~~~~~~~~~~~~~~~~~~~~~~~~ analyze index <index_name> validate structure; select round((del_lf_rows/lf_rows)*100,2) ratio, height, lf_blks, lf_rows from index_stats; We are comparing the ratio between no…
Read MoreTroubleshoot RMAN-06214 error RMAN> DELETE FORCE NOPROMPT OBSOLETE DEVICE TYPE DISK; If error still occurs, then try the below RMAN> CROSSCHECK COPY OF CONTROLFILE; rman> DELETE FORCE NOPROMPT OBSOLETE DEVICE…
Read More