How to set up an alias that quickly shows you the running instances on a Solaris server

Thanks to Mats Strömberg who showed me this little trick on how to use the -o option with ps, to look directly at the command column of the output from ps.

oracle@testserv1:/home/oracle $ alias ri='ps -ef -o comm|grep -v grep|grep ora_[p]mon|sed '"'"'s/ora_pmon_//'"'"'|sort -n'
oracle@testserv1:/home/oracle $ ri
APITDB01
TESTDB01
TESTDB02
TESTDB03

How to deal with impdp error ORA-29913: error in executing ODCIEXTTABLEOPEN callout

I recently hit this error from one of my customers during import of a schema:

ORA-31693: Table data object "SH"."SALES_REG1_Q1" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout

After some searching on the internet, I found evidence of bug 8393456, and it is indeed confirmed on the Oracle Support website.

The problem seems to be related to cases where you use both PARALLEL (>1) and REMAP_SCHEMA at the same time.
Oracle versions affected are 11.2.0.1, 11.1.0.7 and 10.2.0.4.

I implemented the workaround by setting PARALLEL=1 and I can confirm that it solves the problem.

How to use v$session_longops to check long running processes

I am setting NLS_DATE_FORMAT so that the START_TIME and LAST_UPDATE_TIME will be more accurate.

SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
SET TERMOUT ON
SET LINES 300
COL "CURRENTLY EXECUTING" FORMAT A35
COL OSUSER FORMAT A10
COL MACHINE FORMAT A20
COL USERNAME FORMAT A20
COL PROGRAM FORMAT A40
COL UNITS FORMAT A20
COL OPNAME FORMAT A10
COL START_TIME FORMAT A20
COL LAST_UPDATE_TIME FORMAT A20
COL SQL_ID FORMAT A15
SET VERIFY OFF
SET FEEDBACK OFF

SELECT
    S.SID,
    S.SERIAL#,
    S.OSUSER,
    S.MACHINE,
    S.USERNAME,
    S.PROGRAM,
    S.SQL_ID,
    SUBSTR(Q.SQL_TEXT,1,30) || '...' "CURRENTLY EXECUTING",
    L.OPNAME,
    L.SOFAR,
    L.TOTALWORK,
    L.UNITS,
    L.START_TIME,
    L.LAST_UPDATE_TIME,
    L.TIME_REMAINING "SECONDS LEFT",
    TO_TIMESTAMP(L.LAST_UPDATE_TIME,'DD.MM.YYYY HH24:MI:SS')-TO_TIMESTAMP(L.START_TIME,'DD.MM.YYYY HH24:MI:SS') "RUNNING FOR"
FROM V$SESSION_LONGOPS L JOIN V$SESSION S ON L.SID = S.SID
                         JOIN V$SQL Q ON S.SQL_ID = Q.SQL_ID
AND   S.SID = &SID
AND   S.SERIAL#  = L.SERIAL#
ORDER BY L.LAST_UPDATE_TIME DESC;

EXIT

Result:

SID	SERIAL#	OSUSER	MACHINE	USERNAME	PROGRAM	SQL_ID	CURRENTLY EXECUTING	OPNAME	SOFAR	TOTALWORK	UNITS	START_TIME	LAST_UPDATE_TIME	SECONDS LEFT	RUNNING FOR
408	2624	oracle	testserver1	SH	sqlplus@testserver1 (TNS V1-V3)	3w11rcbvd5a32	UPDATE SALES_Q1_DI_MASTERS SET ...	Table Scan	82432	82636	Blocks	06.03.2014 07:35:26	06.03.2014 09:44:22	19	+00 02:08:56.000000

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