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.

How to backup your controlfile to trace

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/fullpath/backup_controlfile.trc' [REUSE] [RESETLOGS | NORESETLOGS];

Note that if you do not specify either RESETLOGS or NORESETLOGS during the create command, oracle will include both versions in the resulting file, and you must pick the appropriate one for your situation.

REUSE will generate a backup controlfile statement which includes the REUSE keyword.
This in turn, indicates that when the "create controlfile" command is executed, you can reuse the existing physical control files on your server. If you don't, and the file exists from a previous database incarnation, Oracle will throw an error, so I prefer to leave it there.

What is the CJQ0 background process?

When starting the Oracle database you may see something like the following in the alert log:

Tue Mar 04 12:40:50 2014
CJQ0 started with pid=27, OS id=5526

This means your database is starting the optional background process for job queue processing

Oracle Database uses job queue processes to run user jobs.

The initialization parameter JOB_QUEUE_PROCESSES states the maximum number of job queue processes that can concurrently run on an instance.

Source: Oracle Documentation

How to enable block change tracking for faster incremental backups:

From Oracle 10g and onwards, you can enable block change tracking to speed up incremental backups. In short,

"if change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile"

Syntax:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/path/PRODDB_rman_change_track.dbf' REUSE;

Database altered.

More info here

SQL: view memory parameter

set lines 200
set pages 100
col name format a30
col value format a40
select name from v$database
/
select name, value/1024/1024 "MB"
from v$parameter
where name in ('java_pool_size',
                'db_cache_size',
                'shared_pool_size',
                'large_pool_size',
                'streams_pool_size',
                'sga_max_size',
                'sga_target',
                'memory_max_target',
                'memory_target',
                'pga_aggregate_target')
/
exit

An easy way to find DML locks

The view DBA_DML_LOCKS is a convenient view to use if you want to identify locks caused by uncommited DML statements.

Consider the following simple example:

INSERT INTO FAMILY_TREE
(PERSON_ID, FULL_NAME, GENDER, CITIZENSHIP, COUNTRY_OF_RESIDENCE)
VALUES
(5, 'Arne Kåsa', 'M', 'Norwegian', 'Sweden');

-- Do not commit --

Logged in as a privileged user from another session, execute:

SELECT * FROM DBA_DML_LOCKS WHERE OWNER = 'VEGARDK';

SESSION_ID	OWNER	NAME	MODE_HELD	MODE_REQUESTED	LAST_CONVERT	BLOCKING_OTHERS
362	VEGARDK	FAMILY_TREE	Row-X (SX)	None	39	Not Blocking

COMMIT;

SELECT * FROM DBA_DML_LOCKS WHERE OWNER = 'VEGARDK';

No rows returned.

How to work around ORA-14080: partition cannot be split along the specified high bound

Problem:
My table had partitions for Jan 2014 and March 2014. Any attempt to split the overflow partition at this point will throw the error message

ORA-14080: partition cannot be split along the specified high bound

The following query shows my partitions for year 2014:

SELECT  PARTITION_NAME, 
        HIGH_VALUE,
        NUM_ROWS
FROM    DBA_TAB_PARTITIONS
WHERE   TABLE_NAME ='SALES'
AND     TABLE_OWNER = 'SCOTT'
AND     (PARTITION_NAME LIKE '%Y2014' OR PARTITION_NAME LIKE '%MAX%')
ORDER BY PARTITION_NAME;

Output:

PARTITION_NAME	HIGH_VALUE	NUM_ROWS
SALES_M01_Y2014	TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	3936
SALES_M03_Y2014	TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	2308
SALES_MAX	MAXVALUE	0

My solution:
Split the partition with the highest bound (March 2014) into two new ones, one that will take care of the rows for February 2014, and another one which will be empty:

SQL> 
  1  ALTER TABLE SCOTT.SALES
  2  SPLIT PARTITION SALES_M03_Y2014 AT (TO_DATE('2014-03-01', 'YYYY-MM-DD')) INTO (
  3  PARTITION SALES_M02_Y2014 TABLESPACE SCOTT_DATA,
  4* PARTITION SALES_MAX_NEW TABLESPACE SCOTT_DATA);

Table altered.

Rebuild local indexes, if any:

SQL> ALTER TABLE SCOTT.SALES MODIFY PARTITION SALES_M02_Y2014 REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

Analyze the partitio, and check to make sure the that it really does contains zero rows. You can use the initial query in this post for that:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','SALES','SALES_M02_Y2014');

PL/SQL procedure successfully completed.

Let's execute the query above once more:

PARTITION_NAME	HIGH_VALUE	NUM_ROWS
SALES_M01_Y2014	TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	3936
SALES_M02_Y2014	TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	2308
SALES_MAX	MAXVALUE	0
SALES_MAX_NEW	TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	0

Drop the temporary partition:

SQL> ALTER TABLE SCOTT.SALES DROP PARTITION SALES_MAX_NEW;

Table altered.

We now have:

PARTITION_NAME	HIGH_VALUE	NUM_ROWS
SALES_M01_Y2014	TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	3936
SALES_M02_Y2014	TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	2308
SALES_MAX	MAXVALUE	0