Oracle RAC 11g RACaThon (playlist)

Run Some SQL On All Databases On Server

for ORACLE_SID in $( ps -ef|grep [p]mon|awk -F'_' '{ print $3 }' )
do
export ORACLE_SID
ORAENV_ASK=NO
. oraenv
echo "$ORACLE_SID"
sqlplus -s / as sysdba  <<EOF
<your SQL here>
EOF
done

e.g. Recompile invalid objects in all databases
for ORACLE_SID in $( ps -ef|grep [p]mon|awk -F'_' '{ print $3 }' )
do
export ORACLE_SID
ORAENV_ASK=NO
. oraenv
echo "$ORACLE_SID"
sqlplus -s / as sysdba  <<EOF
@?/rdbms/admin/utlrp parallel 4;
EOF
done

Convert Interval to Seconds

  SELECT *  
    FROM DBA_SCHEDULER_JOB_RUN_DETAILS  
   WHERE OWNER = 'SCOTT'  
     AND ACTUAL_START_DATE >= (SYSDATE - 4)  
     AND (  
          EXTRACT(DAY    FROM RUN_DURATION) * 86400 +  
          EXTRACT(HOUR   FROM RUN_DURATION) * 3600 +  
          EXTRACT(MINUTE FROM RUN_DURATION) *  60 +  
          EXTRACT(SECOND FROM RUN_DURATION)  
          ) > 1.5  
ORDER BY ACTUAL_START_DATE;  

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);
    

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.

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]