Saturday 23 March 2013

V$SESSION_LONGOPS Time Remaining

V$SESSION_LONGOPS with Time Remaining in day, hour, minute, and second format:

col ETA form a20
col TARGET form a30
col MESSAGE form a70
col USERNAME form a12
SELECT *
FROM (SELECT INST_ID
     ,SID
     ,SERIAL#
     ,USERNAME
     ,OPNAME
     ,DECODE(TO_CHAR(TRUNC(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR)/(24*60*60)))||'d ','0d ',NULL,TO_CHAR(TRUNC(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR)/(24*60*60)))||'d ','0d ')||
      DECODE(TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(24*60*60))/(60*60))) ||'h ','0h ',NULL,TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(24*60*60))/(60*60))) ||'h ')||
      DECODE(TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(60*60))/60)) ||'s ','0m ',NULL,TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(60*60))/60)) ||'m ')||
     TO_CHAR(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR), 60)) ||'s' ETA
     ,MESSAGE
     FROM GV$SESSION_LONGOPS
     WHERE TOTALWORK<>SOFAR
     AND SOFAR<>0
     ORDER BY START_TIME DESC);

    

Monday 18 March 2013

Database Link Syntax

It is tempting to create database links by refencing the aliases from a tnsnames.ora file. e.g.

CREATE DATABASE LINK SCOTTS_DB_LINK
CONNECT TO SCOTT IDENTIFIED BY tiger
USING 'PROD_SERV';


But the "one ring to rule them all" tnsnames.ora approach can come back and bite you if the tnsnames.ora file is ever accidentally lost or - worse - replaced with one that is pointing, say, to Production, rather than Development databases!

Rather than using tnsnames.ora aliases, hard-code instead. e.g.

CREATE DATABASE LINK SCOTTS_DB_LINK
CONNECT TO SCOTT IDENTIFIED BY tiger
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PROD01.my.domain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PROD_SERVICE))))';


Now, no matter what happens to your tnsnames.ora file, you know that your database link is pointing to the correct database.

Friday 1 March 2013

Either the database is not running from Oracle Home or the correct pfile was not found

When using dbua to upgrade a database, you might get a pop-up asking for the location of the init.ora, with a message like this:
"Either the database is not running from Oracle Home or the correct pfile was not found" 

Cause:
An earlier upgrade attempt failed, and a dbua file got left "in limbo"

Solution:
[linux1] cd /opt/oracle/cfgtoollogs/dbua/logs

[linux1] ls
PreUpgradeResults.html  sqls.log  trace.log  Welcome_mydb.txt


[linux1] rm Welcome_mydb.txt

From MOS Note: [ID 1303325.1]