How to find the AWR snapshots currently available in the database

SET LINES 100 PAGES 999

SELECT SNAP_ID,SNAP_LEVEL, TO_CHAR(BEGIN_INTERVAL_TIME, 'dd.mm.yy hh24:mi:ss') "Starttime"
FROM DBA_HIST_SNAPSHOT 
ORDER BY 1
/

How to check that an Oracle database parameter is set in a unix shell script

Probably many ways to do the same thing, but I tend to spool database information to disk, then assign the values found to shell script variables, as in the following example, where I need to determined whether the parameters db_file_name_convert and log_file_name_convert is set in the database:

###############################################################
# Check that the db_convert_file_name and log_file_name_convert
# are both set. If not, abort script.
###############################################################
TRG_DB=${ORACLE_SID}
cat << EoF > check_${TRG_DB}_convert_params.sql
set termout off
set lines 200
set trimspool on
set heading off
set pages 0
set feedback off
set echo off
set verify off
col name format a30
col value format a40
spool check_${TRG_DB}_convert_params.lst
select name,value
from v\$parameter
where name in ('db_file_name_convert','log_file_name_convert');
exit
EoF
sqlplus -s / as sysdba @check_${TRG_DB}_convert_params.sql
CONV_DATA_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep db_file | awk '{print $2}'`
CONV_LOG_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep log_file | awk '{print $2}'`

case ${CONV_DATA_FILE_SET} in
'') echo db_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo db_file_name_convert is set. Continuing...;;
esac

case ${CONV_LOG_FILE_SET} in
'') echo log_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo log_file_name_convert is set. Continuing...;;
esac
exit

How to receive user response in a korn shell unix script

The following is valid for korn shell but not necessarily in other shell dialects, such as bash:


print -n "Do you want to proceed? [Y/N] "
read answer
[[ $answer == [yY] ]] || { echo "Exiting..."; exit 3; }

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.