setsid Unix command to the rescue!

Assuming you have a main script, which is running a few other scripts in nohup in the background, and at the end, you want to run "tail -f" for the log file in a loop, until done.
If you press Ctrl-C, the main script will die, together with all the other scripts running in nohup, not exactly the desired result.

For example:

Main.sh:

nohup ./script1  > ${NOHUP_FILE1} 2>&1 &
nohup ./script2  > ${NOHUP_FILE2} 2>&1 &

RUN_IND=0
while [ $RUN_IND -eq 0 ]  ; do
ps -ef |grep Main |grep -v grep  > /dev/null
RUN_IND=$?
tail  -5   ${NOHUP_FILE1}
sleep 5
done

The solution will be to replace nohup by setsid, as below:

Main.sh:

setsid./script1  > ${NOHUP_FILE1} 2>&1 &
setsid./script2  > ${NOHUP_FILE2} 2>&1 &

RUN_IND=0
while [ $RUN_IND -eq 0 ]  ; do
ps -ef |grep Main |grep -v grep  > /dev/null
RUN_IND=$?
tail  -5   ${NOHUP_FILE1}
sleep 5
done

How to avoid printing sqlplus query output on the screen?

Sometimes, we have a shell script and inside we run a SQL query and we just want to spool the result in a file, without printing the output on the screen.
SET TERMOUT OFF will do the job, but only if it is inside a SQL script.

So this will work:

echo "set pages 0 feedback off  heads off echo off TERMOUT off" >> ${sql_v}
echo "spool Tmp/db.lst" >> ${sql_v}
echo "select distinct  db_name  from my_table order by  1;" >> ${sql_v}
echo "spool off " >> ${sql_v}
echo "exit; " >> ${sql_v}

sqlplus -s $MY_DB_CONNECT  @${sql_v}

But this will NOT work:

sqlplus -s $MY_DB_CONNECT << ENDofSql1
set pages 0 feedback off  heads off echo off TERM OFF
select distinct  db_name  from my_table order by  ENV_CODE;
exit
ENDofSql1

I thought of mentioning this, since it can be frustrating at times...

Annoying oracle error during software installation

It may happen that you install first oracle server/client in a specific directory and then you'd like to do a brand new installation, but you are getting the error below, even after you've completely removed the OH:

[INS-32025] The chosen installation conflicts with software already installed in the given Oracle home.

The solution is to tamper with the configuration file:

oraInventory/ContentsXML/inventory.xml

First create a backup of the above file and then remove the lines related to the previous installation.

Arithmetic inside Linux ksh script

Sometimes, you write a shell script and the need arises for a little arithemtic inside. There are a few ways to do this, below is the simplest way, in my opinion; this is a very simple example, to calculate the percentage done by a specific job:

#!/bin/ksh

total=27800
done=`grep 9k4dgqum1pkdh MYDB_ora_7519.trc |wc -l`


s=`echo "${done}*100/${total}" | bc`

echo "Completed : ${s}%"

How to choose the sequence file number to use for restore/duplicate RMAN command?

Connect to the RMAN respositor:

 rman target /

 List the available backups; choose the latest sequence log number and add one when you run the restore/duplicate DB command.
 In the example below, I've used 15628+1


 RMAN>list backup;

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
282     302.50K    DISK        00:00:00     03-DEC-2013 13:53:14
        BP Key: 284   Status: AVAILABLE  Compressed: YES  Tag: ENV_52_DB_BKP
        Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/TESTDB11_UAT52_TBS_DB_BKUP_13:52:58-12-03-2013_1_287.rman

  List of Archived Logs in backup set 282
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    15625   10218583388335 03-DEC-2013 13:35:02 10218583388844 03-DEC-2013 13:45:02
  1    15626   10218583388844 03-DEC-2013 13:45:02 10218583389173 03-DEC-2013 13:53:13
  1    15627   10218583389173 03-DEC-2013 13:53:13 10218583389183 03-DEC-2013 13:53:13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
283     Full    4.47G      DISK        00:14:43     03-DEC-2013 14:08:01
        BP Key: 285   Status: AVAILABLE  Compressed: YES  Tag: ENV_52_DB_BKP
        Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/TESTDB11_UAT52_TBS_DB_BKUP_13:52:58-12-03-2013_1_288.rman
  List of Datafiles in backup set 283
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1       Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/system_TESTDB11_01.dbf
  2       Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/sysaux_TESTDB11_01.dbf
  3       Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/undotbs_TESTDB11_01.dbf
  4       Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/tools_TESTDB11_01.dbf
  9       Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluapp52_TESTDB11_01.dbf
  28      Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluref52_TESTDB11_01.dbf
  47      Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluopr52_TESTDB11_01.dbf
  67      Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluref52f_TESTDB11_01.dbf
  70      Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluapp52f_TESTDB11_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
284     Full    1.33M      DISK        00:00:01     03-DEC-2013 14:08:04
        BP Key: 286   Status: AVAILABLE  Compressed: YES  Tag: ENV_52_DB_BKP
        Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/TESTDB11_UAT52_TBS_DB_BKUP_13:52:58-12-03-2013_1_289.rman
  Control File Included: Ckp SCN: 10218583390120   Ckp time: 03-DEC-2013 14:08:03

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
285     1.38M      DISK        00:00:00     03-DEC-2013 14:08:05
        BP Key: 287   Status: AVAILABLE  Compressed: YES  Tag: ENV_52_DB_BKP
        Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/TESTDB11_UAT52_TBS_DB_BKUP_13:52:58-12-03-2013_1_290.rman

  List of Archived Logs in backup set 285
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    15628   10218583389183 03-DEC-2013 13:53:13 10218583390129 03-DEC-2013 14:08:05

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
286     Full    1.33M      DISK        00:00:01     03-DEC-2013 14:08:08
        BP Key: 288   Status: AVAILABLE  Compressed: YES  Tag: TAG20131203T140807
        Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/ctl/CTLBKP_TESTDB11_13:52:58-12-03-2013.CTL
  Control File Included: Ckp SCN: 10218583390148   Ckp time: 03-DEC-2013 14:08:07

 The duplicate command looks like:

 RUN {
SET NEWNAME FOR DATAFILE 1  TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/system01.dbf';
SET NEWNAME FOR DATAFILE 2  TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/sysaux.dbf';
SET NEWNAME FOR DATAFILE 3  TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4  TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/tools01.dbf';
SET NEWNAME FOR DATAFILE 9  TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/bmluapp52.dbf';
set until sequence 15629 thread 1;
DUPLICATE TARGET DATABASE TO AUXU
TABLESPACE test52,TOOLS
  LOGFILE
    GROUP 1 ('/testdb11/ora_data02/RMAN_backup/auxiliary_dest/redo/redo01a.log',
             '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/redo/redo01b.log') SIZE 200M reuse,
    GROUP 2 ('/testdb11/ora_data02/RMAN_backup/auxiliary_dest/redo/redo02a.log',
             '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/redo/redo02b.log') SIZE 200M reuse;

}

Oracle analytical functions to the rescue

 Below is a typical example of how using analytical functions speeds up a query, which is having a subquery inside.

Original query:

SELECT
DISTINCT CUSTOMER_ID, SUBSCRIBER_NO, TEST3.TEST3, MODEL_DESC,
                        ALT_MODEL_DESC
                        FROM TEST1, TEST2, TEST3
                        WHERE TEST1.CUSTOMER_ID = 517091995
                        AND TEST1.UNIT_ESN = ESN
                        AND TEST1.ESN_SEQ_NO =
                        (SELECT MAX (ESN_SEQ_NO)
                        FROM TEST1 PD1
                        WHERE PD1.CUSTOMER_ID = TEST1.CUSTOMER_ID
                        AND PD1.SUBSCRIBER_NO = TEST1.SUBSCRIBER_NO
                        AND LENGTH(UNIT_ESN) <> 20 )
                        AND TEST2.TEST3 = TEST3.TEST3
                        AND ( TEST3.EXPIRATION_DATE  IS NULL OR TEST3.EXPIRATION_DATE >= SYSDATE          )

;


The new query, using analytical functions (much faster and accesing the TEST1 table only once):

SELECT
DISTINCT CUSTOMER_ID, SUBSCRIBER_NO, TEST3.TEST3, MODEL_DESC,
                        ALT_MODEL_DESC
                        FROM
(select UNIT_ESN,ESN_SEQ_NO,CUSTOMER_ID, SUBSCRIBER_NO, max(ESN_SEQ_NO) over (partition by CUSTOMER_ID,SUBSCRIBER_NO)  as max_ESN_SEQ_NO
from TEST1 where LENGTH(UNIT_ESN) <> 20 ) phd, TEST2, TEST3
                        WHERE phd.CUSTOMER_ID = 517091995
                        and ESN_SEQ_NO=phd.max_ESN_SEQ_NO
                        AND UNIT_ESN = ESN
                        AND TEST2.TEST3 = TEST3.TEST3
                        AND ( TEST3.EXPIRATION_DATE  IS NULL OR TEST3.EXPIRATION_DATE >= SYSDATE)
;

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

I was getting this error while running the RMAN duplicate database in 11g. As I was duplicating only selected tablespaces over, oracle internally was trying to drop the unnecesary tablespaces and the whole duplicate process was failing with as below:

 sql statement: drop tablespace  "TEST1" including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/26/2013 13:06:34
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 11/26/2013 13:06:34
RMAN-11003: failure during parse/execution of SQL statement: drop tablespace  "TEST1" including contents cascade constraints
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

RMAN> **end-of-file**


I was trying manually to drop the tablespace and I was getting the same error, but no queue tables whatsoever reside in this tablespace:

SQL> drop tablespace  "TEST1" including contents cascade constraints;
drop tablespace  "TEST1" including contents cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables



SQL> select * from dba_queue_tables where table_name in
         (select segment_name from dba_segments where tablespace_name='TEST1';

no rows selected

 At this point, my goal was to find out which statement is failing exactly.

 Solution:

SQL> ALTER system SET EVENTS='24005 TRACE NAME ERRORSTACK FOREVER, LEVEL 1';

System altered.


I then checked the alert log file for ORA-24005 and then inside the trace file I've found the exact statement failing:


ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
----- Current SQL Statement for this session (sql_id=0000000000000) -----
drop table "florin"."DDD#DEF$_AQERROR" cascade constraints purge

 In conclusion, oracle treats a table name with special characters as a queue table.
 I was able to drop this table, using DBMS_AQADM.DROP_QUEUE_TABLE, indeed, and then I've dropped the tablespace without any issue.