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 […]
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 […]
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 11.2.0.3.0 Production on Fri Sep 6 07:55:51 2013 Copyright (c) 1982, 2011, Oracle. All rights […]
I was recently working on tuning – production environment, which had number of materialized views which were scheduled to complete refresh during off hours. This complete refresh process was very time consuming, also producing a large amount archivelogs & undo. Customer was complaining about sudden change in materialized view behavior, […]
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, […]
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 […]
AWR reports are very essential part of performance tuning in Oracle databases. Many a times we require multiple copies of AWR reports so that we can compare it with each other. Traditional method of generating AWR reports (@?/rdbms/admin/awrrpt.sql) just generate single report at a time. Also generating AWR reports for […]
Many a time situation comes, when undo tablespace becomes very big in size and due to space crunch , we need to recreate undo tablespace with appropriate size & drop old one. This post provides u simple steps to perform this activity. SQL> SHOW PARAMETER UNDO NAME TYPE VALUE ———————————— […]
Many a times we receive false alerts for low free space on the oracle tablespaces, as our traditional tablespace monitoring scripts does not consider maxsize clause while calculating free space in the tablespace. Please find below the tablespace monitoring script – which gives you exact free space in tablespace: WITH […]
Adityanath's Oracle Blog 