DBA Troubleshoot Queries

Top Ten most CPU TIME Queries

SELECT sql_text,ROUND (CPU_TIME/1000000) "CPU_TIME in Seconds"
FROM (SELECT  sql_text, CPU_TIME,rank() over ( order by CPU_TIME DESC) rank_desc
FROM v$sql)


Continue reading “DBA Troubleshoot Queries” »»»»»

DNS Setup in Linux

DNS  Setup in OEL 5.9 for RAC 11g R2 on VirtualBox:

We are configuring below DNS server for testing purpose and basically to fulfill the requirement of demo RAC 11gR2 installation on Virtualbox with OEL5.9 OS.

Before configuring we should know little about  DNS:

A DNS server is basically used to resolve the Hostname to IP address and IP address to Hostname ass well. In My case the entire hostname with domain name “rac1.db.com” is the Fully Qualified Domain name (FQDM) where “db.com” is the domain name.Subdomains are used to divide FQDM into zones.

Continue reading “DNS Setup in Linux” »»»»»

delete statement performance impact

Delete Statement Response Time  in Oracle:

Below is a quick observation on my test machine.

———————————–Playing with Delete statement with enabled trigger,Logging/Nologging

Below Analysis Result on below System Configuration:

Two Node RAC 11gR2  created on Virtual Box
CPU: 1(2.00 GHz) each node
RAM: 2gb each node

Continue reading “delete statement performance impact” »»»»»

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

–Got below problem:

Continue reading “SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled” »»»»»

ORA-39001 ORA-39000 ORA-31641 ORA-27040

ORA-39001 ORA-39000 ORA-31641 ORA-27040

Taking table/schema dump gets failed using EXPDP with below error:

Connected to: Oracle Database 11g Enterprise Edition Release – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/home/oracle/backupwk/mafsdb/expdat.dmp"


Continue reading “ORA-39001 ORA-39000 ORA-31641 ORA-27040” »»»»»

CRSCTL 11G R2 Usage

CRSCTL 11G R2 Utility Usage:

CRSCTL Usage:  CRSCTL is an interface between user and Oracle Clusterware, which is used to perform several operations to start/stop, enable/disable the clusterware, health check, debugging Clusterware components etc.

We can use this command to do operations from one node for all other nodes in the cluster, which benefited us not to login each and every node of the cluster to perform CRSCTL operations.


Continue reading “CRSCTL 11G R2 Usage” »»»»»

Oracle Physical Standby Archive Log Gap Resolution

Getting below in Alert log:


SUCCESS: diskgroup VOL10 was dismounted
Media Recovery Waiting for thread 1 sequence 4709
Fetching gap sequence in thread 1, gap sequence 4709-4711
Fri Aug 22 13:00:33 2014
FAL[client]: Failed to request gap sequence
 GAP – thread 1 sequence 4709-4711

 DBID 238289769 branch 788617304
FAL[client]: All defined FAL servers have been attempted.
————————————————————- Continue reading “Oracle Physical Standby Archive Log Gap Resolution” »»»»»