Event Monitor (EMON) Slave Process Constantly Consuming CPU

A production database of  showed 4 cores of CPU burning constantly by 2 process E000 and E001. On further investigation on Oracle Support we found out it is apparently an unpublished bug which still persists in 11.2.0.4 and 12.1.0.2. You can temporarily solve the issue by killing the OS process for EMON using below method. … Continue reading Event Monitor (EMON) Slave Process Constantly Consuming CPU

RMAN-08132: WARNING: cannot update recovery area reclaimable file list

ORA-00245: control file backup failed; target is likely on a local file system RMAN-08132: WARNING: cannot update recovery area reclaimable file list Solution : This is due to the snapshot of the control file being on a local filesystem in a RAC database. Change the snapshot control file back to a shared disk RMAN > … Continue reading RMAN-08132: WARNING: cannot update recovery area reclaimable file list

ASH Script for Finding out Top Wait Events in Oracle

prompt ************************************ prompt **** ASH OVERALL WAIT PROFILE prompt ************************************ set lines 999 SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available duration FROM v$active_session_history; select * from ( select NVL(event,'CPU') event,count(*), round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history WHERE user_id0 AND sample_timetrunc(sysdate-1) group by event order by 2 desc ) where rownum<10;

Compress Table Partitions with Oracle HCC

SQL> ALTER TABLE MOVE PARTITION COMPRESS FOR QUERY LOW PARALLEL 8; SQL> ALTER TABLE MOVE PARTITION COMPRESS FOR QUERY HIGH PARALLEL 8; SQL> ALTER TABLE MOVE PARTITION COMPRESS FOR ARCHIVE LOW PARALLEL 8; SQL> ALTER TABLE MOVE PARTITION COMPRESS FOR ARCHIVE HIGH PARALLEL 8;

ASH and AWR Scripts for Checking Wait Events and Top Consuming SQL’s

http://gavinsoorma.com/2012/11/ash-and-awr-performance-tuning-scripts/

Install and Configure Oracle Application Express Apex 5.0, 4.2.6, 4.2.x with Embedded PL/SQL Gateway

1. Download Software as Zip file from OTN http://www.oracle.com/technetwork/developer- tools/apex/downloads/index.html 2.Unzip apex_5.0_en.zip Software to ORACLE_HOME directory, it will create a directory called Apex 3. Login to SQLPLUS as SYSDBA in database where you will create the Apex instance. Create a tablespace called "apex" for the Apex 5 installation $ cd $ORACLE_HOME/apex SELECT dbms_xdb.gethttpport FROM dual; … Continue reading Install and Configure Oracle Application Express Apex 5.0, 4.2.6, 4.2.x with Embedded PL/SQL Gateway

Active – Active (Bi-Directional) Replication using Oracle Golden Gate 12c

Since it was announced in Early 2010 that Oracle GoldenGate will be the RoadMap for Replication for Oracle Database, The adoption of GoldenGate over Streams has been quite Rapid. The complexity of Streams has intimidated many adopters, specially for Multi-Master or Peer-to-Peer Replication. GoldenGate not only makes MultiMaster Replication easy but is so full of … Continue reading Active – Active (Bi-Directional) Replication using Oracle Golden Gate 12c

Generate DDL from a Data Pump Export File

-- Get DDL from impdp -- The below command will not import the dump, but will just create a .sql file with the DDL statements impdp system/**** directory=import_dir dumpfile=IMPORT.DMP sqlfile=getddl.sql

Running SQL Script for Multiple Databases SQL*PLUS | Mutiple Servers SQL*PLUS in Shell Script | BASH Script Looping SQL*PLUS commands

For running a single SQL Command via sqlplus on multiple servers, we have to first do a few pre-requisites 1. Oracle client should be installed on the Unix/Linux Server 2. Create Local TNS entry in the tnsnames.ora file for the databases where you will run the commands 3. All databases should have one common user … Continue reading Running SQL Script for Multiple Databases SQL*PLUS | Mutiple Servers SQL*PLUS in Shell Script | BASH Script Looping SQL*PLUS commands

RMAN — ORA-19909: datafile 1 belongs to an orphan incarnation in a Standby Database.

We had an interesting scenario where we did a full rman refresh of a standby database becuase the archivelogs went missing and there was a gap. After doing the restore we got the below error in the RMAN recovery rman nocatalog target sys/******@mw auxiliary / connected to target database: MW (DBID=000000000) using target database control … Continue reading RMAN — ORA-19909: datafile 1 belongs to an orphan incarnation in a Standby Database.