Oracle Internals

While implementing one sql tuning advice from OEM came across following error: ORA-13786 missing SQL text of statement object %s for tuning task %s" when Accepting a SQL Profile for a Parallel Execution Plan In order to identify exact error, query the view dba_advisor_rationale SQL> select message from dba_advisor_rationale where object_id=<object number from error>; MESSAGE -------------------------------------------- This attribute enables parallel execution. The message clearly states it's sql plan enables the parallel execution of query which needs extra attribute in the statement to proceed. In order to re-produce the error on the database run the following commands: EXEC dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_EXAMPLE', object_id => 2); ORA-13786: missing SQL text of statement object "1" for tuning task "SYS_AUTO_SQL_TUNING_EXAMPLE" SQL> select message from dba_advisor_rationale where object_id=2; ME...

This blogpost will list down steps to manage and create an gold image with OEM 13(available in previous version of OEM versions as well).  Step1: Login to OEM emcli and perform the sync with repository emcli login -username=<oem username> emcli sync Step2: Next step is to create the gold image and a image version. Before this we have to prepare an agent which needed to be used as a gold copy and rest all agents will use same version of plugins. We assume with in this blog source agent is already prepared. emcli create_gold_agent_image -image_name="<image name>" -version_name="<version name>" -source_agent="<source agent with port"> -gold_image_description="<Description>" Step3: Once gold images and it's version is created and we decided to use it. The version needs to be marked as current and then only we can use it for agent upgrades. emcli promote_gold_agent_image -version_name=<version name> -maturity=Cu...

Lately came across an error while taking an export using datapump on database version 12.2.0.4 on a NAS drive. Error Stack ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31641: unable to create dump file "<NAS mount location>/export_file.dmp" ORA - 27086 unable to lock file - already in use Linux-x86_64 Error: 37: No locks available Additional information: 10 ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory It may initially looks like the file has been already been used by some other process, but looking at the file it was never existed and only created while this export dump and no other process was holding lock on this. Now one may also think of any problem with NAS export option, but same option has been working fine for long time with no changes.  At last came to know about about DNFS, this feature is pretty cool and help in pacing up the speed of exports. Check within your database if you see any...

Securing infrastructure remains critical aspects for everyone and for DBA that also means securing the database. This not just mean to secure your data written on disk (Encryption at rest) but also to secure your data which flows through network between client and network.  All leading Database providers comes up with TLS encryption for these in-flight transactions which secure the communication over the network. In this blog we will cover, how to enable TLS based encryption for Oracle Database.  There has been various blogs and sources of information to achieve this aspect but one may struggle to find a common place which list down all these activities at one place. I will try to consolidate them  which will includes steps for RAC and Standby as well. TLS is an incremental version of SSL version 3 and TLS1.2 is the latest one in the series. Using Oracle Database SSL provides:     1. Encrypt communication between client and server.     2. Authenticate ...

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12...

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s...

Though have been working on oracle for many years now and have been using oracle datapump for quiet some time as well. Recently we found an application had issues post schema import, on identifying the root cause found two things: 1. Objects grant were missing on the schema we imported where schema owner was grantee. 2. Object grants were missing on the schema based on sys objects. To my surprise both are actually normal things. With schema and table level import object permissions where importing schema granted permission to other schemas will be played but when imported schema is grantee(permission given by other schema owner) those permissions are not played. If you still want to see those grant use: impdp full=y dumpfile=expdp.dmp sqlfile=fullimpdp.sql INCLUDE=GRANT This will create a sqlfile with name fullimpdp.sql and will consists of object level permissions which are normally not being imported with impdp. Now solution for this problem can be 1. Backup these per...