Combining Resource Consumer Groups with Application Modules in #Oracle

This article contains a complete working example for the Resource Manager on the command line for those of you who can’t use the Enterprise Manager fort it. Believe me, I feel your pain ūüėČ

As a good practice, PL/SQL procedures should be using DBMS_APPLICATION_INFO to mark their modules and actions. Not only for monitoring purpose but also to provide a way to tweak the system if things start going ugly in terms of performance. Here’s where (more...)

Rebuilding Indexes

One of the special events that can make it necessary to rebuild an index is the case of the “massive DML”, typically a bulk delete that purges old data from a table. You may even find cases where it’s a good idea to mark a couple of your indexes as unusable before doing a massive delete and then rebuild them after the delete.

Despite the fact that a massive delete is an obvious special (more...)

Fast Now, Fast Later

The following is the text of an article I published in the UKOUG magazine several years ago (2010), but I came across it recently while writing up some notes for a presentation and thought it would be worth repeating here.

Fast Now, Fast Later

The title of this piece came from a presentation by Cary Millsap and captures an important point about trouble-shooting as a very memorable aphorism. Your solution to a problem may look (more...)

TNS-12543: TNS:destination host unreachable

Scenario : Setting up a physical standby from Exadata to a non-Exadata single instance. tnsping from standby to primary works fine but tnsping from primary to standby fails with:

TNS-12543: TNS:destination host unreachable

I am able to ssh standby from primary, can ping as well but tnsping doesn’t work. ¬†From the error description we can figure out that something is blocking the access. In this case it was iptables that was enabled on the standby (more...)

Reading AWR Report ‚Äď Part 2

In the previous part I explained the top part of the report. In this part I’ll continue with the actual information about database activity, what we are looking for and other important things we can find in the report. Wait Events The table with the wait events is one of the most important in the … Continue reading Reading AWR Report – Part 2

JDBC, Linux and Entropy

Some troubles¬†— especially¬†those happening only sporadically¬†— are not so easy to shoot and call for a deeper understanding of the matter. In the following real-world example this means: SQL*Net Tracing and some knowledge about the inner workings of the server’s operating system,¬†particularly random number generation. This case¬†was suited well to demonstrate an approach to trouble-shoot […]

Exadata Upgrade to 12.2.0.1 ‚Äď The Missing Step

I decided this week to be a little brave and upgrade one of the Enkitec Exadata racks to 12.2.0.1.  I installed the 12.2.1.0.0 Exadata image a few weeks ago, and have been waiting for a chance to upgrade clusterware to 12.2.  Thankfully, Oracle provides a very good note for this, but I did hit one large snag that should be documented.

The process for upgrading GI to 12.2 (more...)

How to speed up slow unicode migration of a table with xmltype columns

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update   (more...)

Exadata Cell Patching ‚Äď USB I/O Errors

I was playing around with the Exadata X2-2 in the Enkitec lab this weekend, and hit an interesting issue when patching the storage servers.  We were taking the system up to version 12.1.2.3.3 for testing purposes.  I fired off the patchmgr script, and one of the storage servers failed when beginning the first phase of the patching cycle:

[root@enkdb03 patch_12.1.2.3.3.161208]# ./patchmgr -cells cell_group -patch -ignore_alerts
 (more...)

Reduce Hard Parse time using SQL Profile

Few days ago we had concurrency problem with "cursor: pin S wait on X" wait event. This wait event is mostly associated with parsing in some form.

After quick diagnosis I’ve found problematic query. It was fairly complex query which was executed very often with average 0.20 seconds of execution time. As this query was using bind variables, Oracle reused existing plan and problems with "cursor: pin S wait on X" wait (more...)

ORA-38301: can not perform DDL/DML over objects in Recycle Bin

           While dropping a tablespace, getting error ORA-38301: can not perform DDL/DML over objects in Recycle Bin .

SQL> drop tablespace DATA including contents and datafiles;
drop tablespace DATA including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

 

Solution:

Before dropping the tablespace, disable recyclbin . 

1. Disable recyclebin:

SQL> ALTER SYSTEM SET recyclebin (more...)

RMAN-20005: target database name is ambiguous

                          While running rman commands from the catalog database, you might get the error RMAN-20005: target database name is ambiguous. , if the database was restored from another database.

ERROR:

rman target sys/oracle#51234@RMANBKP catalog rman_b2ccrmpd/rman@catdb

Recovery Manager: Release 12.1.0.2.0 – Production on Fri Oct 14 10:34:05 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to (more...)

Hit by the ‚ÄėTech Support‚Äô Scammers

I got a call earlier today from the Tech Support Scammers. You’ve probably heard of this horribly unethical practice already, but the premise is that they cold-call seemingly randomly and try to convince you that there is a problem with your PC/router, and then attempt to get you to allow them remote access to your PC to ‘fix it’. Some then claim problems are due to expired warranties on the computer and demand payment, others (more...)

Slow full table scan due to row chaining

Few days ago I’ve received complaint that simple count on 2 million rows table is running forever.

This was the statement:

select count(1)
from CLIENT k
where k.expires is null;

I've used fake names for table name and columns.
Database version: 11.2.0.4.0

Indeed, query was running longer than I would expect. Oracle was using FULL SCAN of the table with "db file sequential read" wait events. This was little odd (more...)

Extended Stack Profiling – Ideas, Tools and Comments

Topic: This post provides a short summary and pointers to previous work on Extended Stack Profiling for troubleshooting and performance investigations.

Understanding the workload is an important part of troubleshooting activities. We seek answers to questions like: what is the system doing, where is the time spent, which code paths are most used, what are the wait events, etc. Sometimes the relevant diagnostic data is easy to find, other times we need to dig (more...)

Analyze database activity using v$log_history

The¬†v$log_history view contains important information on how application’s users use the database , this view can help you define periods with the most activity in the database. v$log_history queries You can adapt the query to your needs, you just have to change the way you format the date to be able to drilldown to the … Continue reading Analyze database activity using v$log_history

The post Analyze database activity using v$log_history appeared first on Oracle (more...)

ORA-19599 block corruption when filesystemio_options=SETALL on ext4 file system using Linux

Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.

This was virtual machine running oldish 10.2.0.5.0 Oracle database.

I noticed that backup for my database is failing because of archive log corruption. As this is development database I've simply deleted corrupted archive logs and initiated full backup again. But backup (more...)

PeopleTools 8.54 Feature: Application Engine Trace File Enhancements

In this blog, we have been reviewing the new features of PeopleTools 8.54. Today is the turn of Application Engine, particularly on its troubleshooting. This release of PeopleTools include several enhancements on Application Engine tracing, which are outlined below:


  • The .AET trace file can now include the PeopleCode trace. This removes the need of checking the .AET file for the the non-PeopleCode steps and the .TRC file for the PeopleCode steps. Surely, .TRC files (more...)

Being Right and Proving It

One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.

In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)

Debugging Faulty Assumptions

I wanted to install Oracle JDevelper 12.1.3 – a version that I had been eagerly awaiting. Since my primary machine is a MacBook, I wanted to install it on OS X 10.9.3.

I downloaded the generic installer and found that the install didn’t run. Since OS X had disappeared from the documentation, I assumed that I would have to fiddle around until I found a combination of JDK and OS (more...)