Oracle Internals

 Oracle Physical standby sometimes can go out of sync and it may not be possible to get the archive logs due to the size and amount of time. In these cases earlier we need to either rebuild the standby or take the incremental backup from primary database and recover the standby. With 12c in place oracle introduced one more method commonly known as recover form service as well which makes this process simple and straightforward. Using service method does following 1. It takes the incremental backup from the SCN of the standby datafile and transfer that backup to DR site over the network (earlier this method use to be done manually). 2. Applies the incremental backup to physical standby. Lets discuss the steps now in detail: Step1 : Start the standby database is mount state.      srvctl stop database -d <db unique name>      srvctl start database -d <db unique name> -o mount Step2 : Stop the standby database recovery     alter datab...

If you ever upgrade your database running on a RAC cluster (including Exadata) to version 19c and then rollback your operation back to previous version and you encountered error " ORA-29702: error occurred in Cluster Group Service operation" while starting your database. Additionally you can start the database with another name but not the same database which has been upgraded and now being rollback.  This is due to  Bug 31561819 and it's related to specifically with RAC cluster. Mentioned bug exists from version 12.1.0.2 to 19.8.0 and later included in patch set itself. So if you need to fix this problem you would need to apply this patch. As a workaround none is mentioned in oracle support but there is one: Workaround : Perform the cold restart services of the your cluster. crsctl stop crs (on all nodes sequentially) crsctl start crs (on all nodes sequentially) Once restart of cluster services is done, try starting the database back and it should work as in our case. If...

There would be occasions where you would upgrade your oracle database which is hosting your APEX and post upgrade you get error while opening APEX URL " Error Processing Request Contact your application administrator " This looks very strange as you have just upgraded only database. Database version and APEX version are compatible. For Argument lets say both are on 19c.  Now since UI doesn't give detailed information about the error you would need to get to backend oracle database and query view apex_debug_messages.     select * from apex_debug_messages; Looking at the results of this view you would get the clue of the error in this case it was:      ORA-04063 package body APEX_190100.WWV_FLOW_SESSION_RAS has error This means this package body has problem and looking at the status of this body it's found to be invalid and when you try to compile it get following error      alter package APEX_050100.WWV_FLOW_SESSION_RAS compile body;  ...

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 ...