Author: Admin

Find long Running Transaction

28-Jul-2008 Admin No Comments

+++++++++++++longtx_12HR.sh #!/bin/sh . /opt/app/oracle/oracle_env NOTIFY_LIST=dbaoracle@1800flowers.com $ORACLE_HOME/bin/sqlplus -s “/ as sysdba” @/opt/app/oracle/scripts/longtx_12HR.sql if [ `cat /opt/app/oracle/scripts/log/long_transaction_list.lst | grep -i “*****ATLAS NOTIFICATION REQUIRED*****” | wc -l` -n e 0 ]; then mailx -s “LONG RUNNING TRANSACTION ON ATLAS `hostname` on `date` ” $NOTIFY_LIST < /opt/app/oracle/scripts/log/long_transactio n_list.lst fi +++++++++++++longtx_12HR.sql set lines 120 pages 300 set serveroutput on exec...

Linux/Unix, shell

Check_recovery.sh program to run sqlplus and return its values remotely.

21-Jul-2008 Admin No Comments

#/bin/ksh -x NOTIFY_LIST=”pmehta@1800flowers.com” SOURCE_ARCH_DEST=/db5/archlog/800P TARGET_SID=UOC SOURCE_SSH=/usr/local/bin/ssh TARGET_SSH=/usr/local/bin/ssh SOURCE_HOST=nyuocdbprod01s TARGET_HOST=uocdbprod21 TARGET_ORACLE_HOME=/oracle/app/oracle/product/10.1 TARGET_ENVFILE=/oracle/app/oracle/oraprocs/oracle_env_UOC SOURCE_ORACLE_HOME=/oracle/app/oracle/product/10.1 SOURCE_ENVFILE=/export/home/oracle/oraprocs/oracle_env_10g_UOC MY_SQL=”select max(SEQUENCE#) from v$log_history;” MY_SQL1=”select count(1) from v$log_history where first_time >= (sysdate – (45/1440));” TARGET_SEQ=`$TARGET_SSH $TARGET_HOST “. $TARGET_ENVFILE; $TARGET_ORACLE_HOME/bin/sqlplus -S / as sysdba

Linux/Unix, shell

Running select from V$ views from remote server

21-Jul-2008 Admin No Comments

#/bin/ksh -x NOTIFY_LIST=”pmehta@1800flowers.com” CONNECT_USER=”/ as sysdba” TARGET_ORACLE_HOME=/oracle/app/oracle/product/10.1 MY_SQL=”select max(SEQUENCE#) from v$log_history;” RUNNING=`/usr/local/bin/ssh uocdbprod21 “. /oracle/app/oracle/oraprocs/oracle_env_UOC; $TARGET_ORACLE_HOME/bin/sqlplus -S / a s sysdba

Linux/Unix, shell

RAC with RHEL4 and 11g

13-Jul-2008 Admin No Comments

http://startoracle.com/2007/09/30/so-you-want-to-play-with-oracle-11gs-rac-heres-how/

Oracle, RAC

longtx.sql

10-Jul-2008 Admin No Comments

Find long running transaction that are running for more than 5 minutes with transaction id. col machine format a30 col sid format 99999 col Transaction_id format a20 col username format a10 col days format 99.99 col minutes format 999999 set lines 120 pages 200 select a.sid, a.username, a.machine, round((sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 1440)…

V$transaction notes for finding XID composition.

10-Jul-2008 Admin No Comments

Notes on v$transaction The information here contains the view description from the Oracle9i documentation Earlier versions of Oracle may not include all columns. The v$transaction view lists the active transactions in the system. Column Datatype Description ————- ————– ————————————– ADDR RAW(4) Address of transaction state object XIDUSN NUMBER Undo segment number XIDSLOT NUMBER Slot number…

Oracle, SQL scripts

telnet listening

09-Jul-2008 Admin No Comments

if you are trying following $ telnet remote-server port and you are getting error, this means no one is listening on that port on remote server. Start the listening application ( like listener ) & then do telnet. it will work if firewall is not blocking it.

Linux/Unix, shell

Find total file sizes

07-Jul-2008 Admin No Comments

sunflower1/800L>./vrm3 | awk ‘{tot+=$5} END {print “TOTAL ” tot/1024}’ TOTAL 52662552 sunflower1/800L>cat vrm3 ls -rlt /EMCdb21/oradata/800P/800P_misc_index31.dbf ls -rlt /EMCdb21/oradata/800P/800P_emp_data23.dbf ls -rlt /EMCdb21/oradata/800P/800P_emp_index55.dbf ls -rlt /EMCdb21/oradata/800P/ORD_SCRPAD_INDEX_06.dbf ls -rlt /EMCdb21/oradata/800P/800P_emp_index56.dbf ls -rlt /EMCdb21/oradata/800P/800P_misc_data_3_04.dbf ls -rlt /EMCdb21/oradata/800P/800P_SAS_DATA_1.dbf ls -rlt /EMCdb24/oradata/800P/800P_ord_index42.dbf ls -rlt /EMCdb24/oradata/800P/800P_misc_index32.dbf ls -rlt /EMCdb24/oradata/800P/800P_phone_index22.dbf ls -rlt /EMCdb24/oradata/800P/800P_atlas_index57.dbf ls -rlt /EMCdb14/oradata/800P/800P_atlas_data34.dbf ls -rlt /EMCdb14/oradata/800P/800P_emp_index57.dbf ls -rlt /EMCdb14/oradata/800P/800P_ord_data29.dbf…

Read More “Find total file sizes” »

Linux/Unix, shell

Clean up

06-Jul-2008 Admin No Comments

* re-format /dev/sdb /dev/sdc once * drop crs home * drop oraInventory * set the time on both boxes

Oracle, RAC

UTL_FILE test program

03-Jul-2008 Admin No Comments

Declare v_filehandle UTL_FILE.FILE_TYPE; file_dir VARCHAR2(50) :=’/db3/oracle/utlfiles’; begin t_file_name :=’Vatti_test.dat’; v_filehandle := UTL_FILE.FOPEN(file_dir, t_file_name, ‘w’); UTL_FILE.PUT_LINE(v_filehandle, ‘Hello TEST’); UTL_FILE.FCLOSE(v_filehandle); exception when others then dbms_output.put_line(sqlcode || ‘ ‘ || sqlerrm); end; /

Oracle, SQL scripts