Today I faced very funny issue related to FRA – Flash Recovery Area. Linux guys informed us to clear old files as one of the mount point on database server had reached to its threshold free space. After investigating we found that one of database ORACLE_HOME was 192GB is size. […]
Some days before when I was cloning one of the production database into UAT box, step to open database with resetlogs option failed due to insufficient space in one of the diskgroup. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00344: unable to re-create […]
Writing custom messages in alert log … IS IT POSSIBLE??? Yes it is… We can edit, update or delete alert log entries with any editor, but oracle has a package that does the same work: dbms_system.ksdwrt. We can write our own customized messages in alert.log sitting at SQL*PLUS session using […]
Recently I faced very interesting issue, when user was complaining that he is getting (SCHEMA:TEST1) ORA-01031: insufficient privileges while creating materialized view under different schema (SCHEMA:TEST2) though TEST1 has DBA role assigned. For TEST1: SQL> select * from dba_sys_privs where grantee=’TEST1′; GRANTEE PRIVILEGE ADM —————————— —————————————- — TEST1 UNLIMITED TABLESPACE […]
Many a time we DBA’s face issues as we cannot drop other schema’s database link with schema qulalifier Since dot is allowed in the database link name. Oracle check for a dblink with the given name under the same schema. For performing this task we need to connect to owner schema […]
I always see the following messages in Alertlog , was curious to know the reason behind it… Wed Jun 25 06:35:22 2014 SUCCESS: diskgroup DATA2 was mounted SUCCESS: diskgroup DATA2 was dismounted SUCCESS: diskgroup DATA2 was mounted SUCCESS: diskgroup DATA2 was dismounted SUCCESS: diskgroup DATA2 was mounted SUCCESS: diskgroup DATA2 […]
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, […]
Adityanath's Oracle Blog 