How to deal with RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous

It turned out that the my RMAN session didn't know which DBID to perform the requested command against.

I checked my incarnation list, and observed the following:

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       198     PTADB01  2645386576       PARENT  6275306386559 10.04.11
1       2       PTADB01  2645386576       CURRENT 6278643127311 20.04.11
681     923     PTADB01  2663743751       PARENT  6275306386559 10.04.11
681     682     PTADB01  2663743751       CURRENT 6435835146938 16.11.11

Note how two of my incarnations are marked as being PARENT. Never saw this before.

A quick search on the internet pointed me to setting the DBID explicitly, so I vierfied that the DBID listed in the incarnation list above was indeed correct, by checking the backup logs files, and set it in the RMAN session:

RMAN> set DBID=2663743751;

So when the database name is not unique in the recovery catalog, you need to point out which one to use before RMAN can work.
The restore now worked, and the database could be mounted.

ORA-39726: unsupported add/drop column operation on compressed tables when attempting to drop column on a (previously) compressed table

My customer had a partitioned table that was previously compressed with FOR ALL OPERATIONS options.
One of the developers wanted to drop a columnn on a table, but receives the following error message:

ORA-39726: unsupported add/drop column operation on compressed tables

According to Oracle, this is expected behaviour. Oracle Support note 1068820.1 "Error ORA-39726 Drop Column Operation On Compressed Tables 10.2 Release" explains:

"In release 10g, a drop column attempt on a compressed table must raise an ORA-39726 error if the column has to be physically removed from the data blocks.
Hence "DROP COLUMN" and "DROP COLUMN UNUSED" are both illegal because they may have to touch the datablocks. SET UNUSED is OK because it is just a data
dictionary operation (as is DROP COLUMN on virtual columns)."

The Oracle support note concludes:

"In 11g it is allowed to drop columns from a compressed table IF compatible is set to 11.1 or higher AND table was created with the "compress for all OLTP" option but even in this situation there is no real drop but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations."

My database unfortunately has compatible set to 11.0. So the only option at this point is to use the syntax

ALTER TABLE owner.table_name SET UNUSED COLUMN column_name;

This will render the column as good as dropped, another column can be added to the table with the same name.
The number of unused columns for a table can be tracked with

SELECT *
FROM USER_UNUSED_COL_TABS
WHERE TABLE_NAME='table_name';

why am I getting ORA-14006: invalid partition name when attempting to drop a partition in TOAD?

In TOAD you are trying to drop a partition, as follows:

alter table segment_sizes drop partition SYS_P41;

But you get ORA-14006: invalid partition name as a result.

Solution: remove the ";" at the end of the statement, and try again.
Alternatively, execute the statement through SQL*plus.

Source: Derya Oktay's Oracle Weblog

Errors ORA-31693 and ORA-29913 thrown during import

Yesterday when importing some test data from production to test, I encountered the following error during import:

. . imported "< schema name >"."< table name > ":"< partition name >" 77.08 KB 0 rows
ORA-31693: Table data object "< schema name >"."< table name > ":"< partition name >" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
. . imported "< schema name >"."< table name > ":"< partition name >" 25.69 KB 0 rows

The preceeding export executed successfully, without any errors or warnings. I used multiple threads, and the result was 7 dump files.

I first tried reimporting the table, which in many similar cases actually work. This time, it didn't; the same error was thrown.

The solution was to remove the parallel directive from the import parameter file and reimport only the missing table:

USERID='/ AS SYSDBA'
DIRECTORY=DPUMP
DUMPFILE=EXDP_SCHEMA_EXPORT_201311181340_%U.DMP
LOGFILE=IMPDP_missing_tables.log
PARALLEL=1
TRANSFORM=OID:N
TABLE_EXISTS_ACTION=REPLACE
REMAP_SCHEMA=< schema_name >:
TABLES=< schema_name >.< table_name >

. . imported "< schema name >"."< table name > ":"< partition name >" 77.08 KB 0 rows
. . imported "< schema name >"."< table name > ":"< partition name >" 1.205 GB 6758593 rows
. . imported "< schema name >"."< table name > ":"< partition name >" 25.69 KB 0 rows