asmcmd “connected to an idle instance” – not

This is more a note to myself in case I’ll encounter a similar environment. But maybe it helps others – at least my search results weren’t suitable to Windows in the first place. Issue C:\> set ORACLE_HOME=C:\path\to\grid\home C:\> set ORACLE_SID=+ASM1 C:\> asmcmd connected to an idle instance. Environment Windows 2012R2 Oracle Grid Infrastructure 12.1.0.1 2 […]

“Collection iterator pickler fetch”: pipelined vs simple table functions

Alex R recently discovered interesting thing: in SQL pipelined functions work much faster than simple non-pipelined table functions, so if you already have simple non-pipelined table function and want to get its results in sql (select * from table(fff)), it’s much better to create another pipelined function which will get and return its results through PIPE ROW().

A bit more details:

Assume we need to return collection “RESULT” from PL/SQL function into SQL query “select (more...)

ORA-01452: Cannot CREATE UNIQUE INDEX; Duplicate Keys Found

Problem:-

While creating the unique index on a table, got below error.

SQL> create unique index TEST_IDX on TEST5(EMPNO);
create unique index TEST_IDX on TEST5(EMPNO)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Cause:- You tried to execute a CREATE UNIQUE INDEX statement on one or more columns that contain duplicate values.

Solution:-

Find the duplicate rows in the table and delete them.

Use below script to delete the duplicate rows

REM This (more...)

ORA-29516: Aurora Assertion Failure: Assertion Failure

 

This post is for steps which solve ORA-29516.

 

If the java component is corrupted, then while executing java related packages, you may face below error.

Cause

You have set additional mount options on the shared memory device, /dev/shm, other than just the option named defaults.

Problem:

select dbms_java.longname(‘TEST’) from dual;
select dbms_java.longname(‘TEST’) from dual
*
ERROR at line 1:
ORA-29516: Aurora assertion failure: Assertion failure at eox.c:359
Uncaught exception Root of (more...)

ORA-19588: Archived Log RECID Is No Longer Valid

As we know each and every problem have solutions. Let’s have a solution to the Error:- ORA-19588.

PROBLEM:

While taking RMAN backup, got below error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t4 channel at 05/01/2017 03:18:01
ORA-19588: archived log RECID 143154 STAMP 942443843 is no longer valid

Cause: The indicated record has been marked as deleted. This indicates that the corresponding file has either been (more...)

RMAN-20035: Invalid High RECID In Rman Catalog

We are going to learn about the steps which we use to solve issues RMAN-20035. 

 

 

we face RMAN-20035: invalid high RECID  after connecting to catalog database.

 

executing command: SET COMMAND ID
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 07/27/2016 10:54:19
RMAN-03014: implicit resync of recovery catalog failed

on default channel at 07/27/2016 10:54:19
RMAN-20035: invalid (more...)

Impdp Resumable Mystery

Resumable operations are operations that can be suspended instead of failing, allowing us to fix the problem and resume the operation. When importing data using impdp, if (for example) a tablespace gets full, instead of failing the entire import (and force us to restart it from the beginning), we can identify that and add some … Continue reading Impdp Resumable Mystery

Oracle issues after upgrade to 12.2

Sometimes it’s really hard even to create reproducible test case to send it to oracle support, especially in case of intermittent errors.
In such cases, I think it would be really great to have access to similar service requests or bugs of other oracle clients.
So while my poll about knowledge sharing is still active, I want to share a couple of bugs we have faced after upgrade to 12.2 (and one bug from (more...)

Garbled display while running FMW installer on Linux

A colleague faced this while running FMW installer on a Linux machine. The display appeared like this

 

 

 

 

 

 

 

This thread gave a clue that it could have something to do with fonts. So I checked what all fonts related stuff was installed.

[root@someserver ~]# rpm -aq |grep -i font
stix-fonts-1.1.0-5.el7.noarch
xorg-x11-font-utils-7.5-20.el7.x86_64
xorg-x11-fonts-cyrillic-7.5-9.el7.noarch
xorg-x11-fonts-ISO8859-1-75dpi-7.5-9.el7.noarch
xorg-x11-fonts-ISO8859-9-100dpi-7.5-9.el7. (more...)

root.sh fails with CRS-2101:The OLR was formatted using version 3

Got this while trying to install 11.2.0.4 RAC on Redhat Linux 7.2. root.sh fails with a message like

ohasd failed to start
Failed to start the Clusterware. Last 20 lines of the alert log follow:
2017-11-09 15:43:37.883:
[client(37246)]CRS-2101:The OLR was formatted using version 3.

This is bug 18370031. Need to apply the patch before running root.sh.

Easy(lazy) way to check which programs have properly configured FetchSize

select 
   s.module
  ,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch
from v$sql s
where 
    s.rows_processed>100
and s.executions    >1
and s.fetches       >1
and s.module is not null
and s.command_type  = 3    -- SELECTs only
and s.program_id    = 0    -- do not account recursive queries from stored procs
and s.parsing_schema_id!=0 -- <> SYS
group by s.module
order by rows_per_fetch desc nulls last
/

Playing with RAC Voting Disks

Lately I ran into a case of losing of a voting disk, so this lead me to create a GI environment on my laptop (using virtual box) and play with voting disks failures. In this post I’ll explain what I did and what happened. Background I’m not going to explain too much about the voting … Continue reading Playing with RAC Voting Disks

Analyzing the Listener.log

A little bit more than a month ago I spoke at OSWOUG event (and wrote this post about it). In the event, Jared Still talked about free tools for Oracle database. He basically said that there is a lot of stuff out there on the internet, and if you can’t find what you need, then … Continue reading Analyzing the Listener.log

Parsing

Here’s a quick quiz.

According to the Oracle 12.1 Database SQL Tuning Guide the first stage of parsing a statement is the Syntax Check, which is followed by the Semantic Check, followed by the Shared Pool Check. So where you do think the statement text will be while the Syntax Check is going on ?

 

 


markhot

How can a single piece of SQL text – checked very carefully – end up with multiple SQL_IDs ? There are probably quite a lot of people who know the answer to this question but won’t think of it until they’re reminded and, thanks to a question that came up on the forum formerly known as OTN a couple of days ago, I was reminded about it recently and rediscovered an article I had drafted (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...)

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

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

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