Resolving cursor: pin S wait on X using interesting column final_blocking_session in v$session

Recently I was having issue in our test environment, users were complaining that database is not responding for any queries, it appears to be in hanged state. After checking database for possible causes for performance issues, I found following: SQL> select event,count(0) from v$session where username is not null and […]

UNNAMED datafile issue in standby database: ORA-01111, ORA-01110, ORA-01157

We have following two reasons for getting UNNAMED files created on standby database: 1. STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. Setting this parameter to MANUAL […]

ORA-29786: SIHA attribute GET failed with error [Attribute ‘SPFILE’ sts[200] lsts[0]]

Recently while performing fresh ASM installation, I was trying to create spfile from pfile , I encountered this issue: ORA-29786: SIHA attribute GET failed with error [Attribute ‘SPFILE’ sts[200] lsts[0]] [oraasm@rmb-put-mordor ~]$ sqlplus SQL*Plus: Release Production on Fri Sep 6 07:55:51 2013 Copyright (c) 1982, 2011, Oracle. All rights […]

DBCA failing with ORA-01034

I faced this issue while creating database using DBCA in one of the UAT database server. Issue was with just DBCA , I was able to create database manually without any issues. Each attempt of creating database with DBCA utility was failing with ORA-01034: ORACLE not available. After putting lots of efforts, […]

Create pfile from memory

Many a times situation comes when database initialization parameter file gets accidentally removed or overwritten. From version 11g Oracle has came up with solution of recreating parameter file from memory if database is up & running. Oracle already has functionality of creating pfile from spfile & vice-versa. But now with […]