Monthly Archives: April 2015

How to fix broken database jobs


SQL>select JOB,SCHEMA_USER,BROKEN,WHAT from dba_jobs JOB SCHEMA_USER B WHAT ———- —————————— – —————————————- 1 APPUSER                   Y db_backup.p$_backup_p(1); 1 rows selected. SQL> exec dbms_job.broken(1,FALSE); PL/SQL procedure successfully completed. SQL>connect user/password SQL>select JOB,SCHEMA_USER,BROKEN,WHAT from dba_jobs JOB SCHEMA_USER B WHAT ———- —————————— – —————————————- 1 APPUSER         […]

How to determine the CRS Version


To check the Active Version Run the following command on the local node. bash-3.2$ crsctl query crs activeversion Oracle Clusterware active version on the cluster is [11.2.0.3.0] Note: The active version is the lowest software version running in a cluster. To check the Software Version Run the following command on the local node. bash-3.2$ crsctl […]

Ufter the upgrade database Oracle 11g | Invalid Java Class Objects


Issue: Java Class/Classes became INVALID after the upgrade of the database Solution: First Compile and then resolve the Java Classes by using the below command. ALTER JAVA SOURCE “OWNER”.”OBJECT_NAME” COMPILE; ALTER JAVA CLASS “OWNER”.”OBJECT_NAME” RESOLVE; Example: SQL> select owner , object_name, object_type from dba_objects where status=’INVALID’; OWNER OBJECT_NAME OBJECT_TYPE ———– ———————– ———– TEST TestStringTokenizer JAVA […]

OPatch Prerequisite check “CheckActiveFilesAndExecutables” failed


[oracle@db01 18522509]$ /home/oracle/OPatch/opatch apply Oracle Interim Patch Installer version 11.2.0.3.6 Copyright (c) 2013, Oracle Corporation. All rights reserved. Oracle Home : /DBSoft/Product/11.2.4/db_1 Central Inventory : /DBSoft/oraInventory from : /DBSoft/Product/11.2.4/db_1/oraInst.loc OPatch version : 11.2.0.3.6 OUI version : 11.2.0.4.0 Log file location : /DBSoft/Product/11.2.4/db_1/cfgtoollogs/opatch/opatch2014-10-09_05-06-13AM_1.log Verifying environment and performing prerequisite checks… Prerequisite check “CheckActiveFilesAndExecutables” failed. The details are: […]

ORA-27102: out of memory” While Creating a Database


oracle@db ~]$ oerr ORA 27102 27102, 00000, “out of memory” // *Cause: Out of memory // *Action: Consult the trace file for details [oracle@db ~]$ Solution: This is Because of  On 32 Bit version,It cannot allocate more than 4 GB of Memory  to a process Directly.For this either we need to upgrade to 64 bit […]

SHUTDOWN: waiting for active calls to complete.


When I shutdown databse, I can see waiting for arctice calls to compleed in alert log. Shutting down instance (immediate) Stopping background process SMCO Shutting down instance: further logons disabled Stopping background process QMNC Tue Apr 14 15:52:00 2015 Stopping background process CJQ0 Stopping background process MMNL Stopping background process MMON License high water mark […]

How to check RMAN backup status and timings


SQL> col STATUS format a9 SQL> col hrs format 999.99 SQL> select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time, to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key; SQL> / SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS ———– ————- ——— ————– ————– ——- 29 DB FULL RUNNING 01/07/14 10:28 01/07/14 10:28 .00 SQL> / SESSION_KEY INPUT_TYPE […]

Oracle Remote Diagnostics Agent (RDA) utility


Oracle Remote Diagnostics Agent (RDA) is used primarily by Oracle Support. I’ve found that this information is very useful and have used to debug issues in my environment. It is a useful utility to me for particular situations. You can download it from MetaLink – Doc ID: 314422.1 Connection to Database: Open command prompt.  set ORACLE_SID=DB oracle@mdlinux […]

ORA-00494: enqueue [CF] held for too long (more than 900 seconds)


Problem: Today morning one of our development instance crashed due to following error found in alert log file. Contents of alert log file: ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by ‘inst 1, osid 17923’ Incident details in: /u01/app/oracle/diag/rdbms/amlprod/AMLPROD/incident/incdir_66057/AMLPROD_ora_17926_i66057.trc Wed Apr 08 10:44:23 2015 Killing enqueue blocker (pid=17923) on resource CF-00000000-00000000 […]

Enable the In-Memory column store in database


SQL> select banner from v$version where banner like ‘%Database%’; BANNER ——————————————————————————– Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production SQL> select * from v$sgainfo where name=’In-Memory Area Size’; NAME BYTES RES CON_ID ——————————– ———- — ———- In-Memory Area Size 0 No 0 SQL> show parameter inmemory NAME TYPE VALUE ———————————— ———– —————————— inmemory_clause_default […]

Monitoring Index Usage


SQL> create table anar1 (id number); Table created. SQL> insert into anar1 values (1); 1 row created. SQL> insert into anar1 values (2); 1 row created. SQL> insert into anar1 values (3); 1 row created. SQL> commit; Commit complete. SQL> create index idx_anar1_id on anar1(id); Index created. SQL> select table_name, index_name, monitoring, used from v$object_usage; […]

ORA-15001: diskgroup “DATA” does not exist or is not mounted , ORA-15077: could not locate ASM instance serving a required diskgroup


Problem: ORA-00210: cannot open the specified control file ORA-00202: control file: ‘+DATA/kbazerdr/controlfile/current.256.825098041’ ORA-17503: ksfdopn:2 Failed to open file +DATA/kbazerdr/controlfile/current.256.825098041 ORA-15001: diskgroup “DATA” does not exist or is not mounted ORA-15077: could not locate ASM instance serving a required diskgroup ORA-205 signalled during: ALTER DATABASE MOUNT STANDBY DATABASE… Analyze: 1) check if CRS is running on […]