Oracle DBA Place

Usually I take care for backups, but the first time I tempted to neglect this habit - I just have met Morphy's law :( Fortunately there is a solution: yum install testdisk OR apt-get install testdisk To recover files simply type: photorec and follow the instructions So simple :)

I am used to relay on tools for formatting and indenting SQL or PL/SQL code. Looking for the same for linux scripts (especially bash) I found the following code fmt.script #!/usr/bin/env perl # fmt.script - format scripts in awk, csh, ksh, perl, sh # # we do: # standardize indentation (an indent is one tab by default) # strip trailing whitespace # change ${var} to $var where possible # change ">x" to "> x" for shell scripts # change "[ ... ]" to "test ..." for Bourne shell scripts # # we may do someday, but these are harder: # convert $VAR to $var unless a setenv or default environment variable # possibly prepending stuff from template.sh # "if ... \nthen", for ... in\ndo", "while/until ... \ndo", "fn()\n{" # # to have fmt.script reformat itself (a fair test, yes?) try: # fmt.script fmt.script fmt.script.new # use tabs for indents # fmt.script -s4 fmt.script fmt.script.new # in...

I noticed that many DBAs are not aware of the need to run a prerequisite system check before installation of an oracle product. This special check should be done in addition to the installation document and the check inside the installer. The utility called RDA with a module for Health Check called HCVE. It is well documented for RAC but can be used for simple installation. RDA is maintained frequently and adjusted to the OS version against the oracle software. You can read about it in Metalink Doc ID: 250262.1 "RDA - Health Check / Validation Engine Guide" Sample ./rda.sh -T hcve Processing HCVE tests ... Available Pre-Installation Rule Sets: 1. Oracle Database 10g R1 (10.1.0) PreInstall (AIX) 2. Oracle Database 10g R2 (10.2.0) PreInstall (AIX) 3. Oracle Database 11g R1 (11.1.0) PreInstall (AIX) 4. Oracle Application Server 10g (9.0.4) PreInstall (AIX) 5. Oracle Fusion Middleware 11g R1 (11.1.1) PreInstall (AIX) 6. Oracle Portal PreInstall (Generic) Availab...

The trcsess utility consolidates trace output from selected trace files based on several criteria: trcsess &nbsp[output=output_file_name]         &nbsp[session=session_id]         &nbsp[clientid=client_id]         &nbsp[service=service_name]         &nbsp[action=action_name]         &nbsp[module=module_name]         &nbsp[trace_files] trcsess output=main.trc service=psdwh *trc After the consolidate trace file had been generated you can execute tkprof on it. For example: tkprof main.trc main.prf sys=no sort=exeela TKPROF: Release 10.2.0.3.0 - Production on Thu Feb 28 13:38:23 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. for more information: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm

In my firm we have a seperation between System guys to the DBAs, we don't have root privileges. this is the reason that they are peparing us the ASM disks. After using "/etc/init.d/oracleasm createdisk..." I tailored a script to see all the information that I can get WITHOUT the need to connect to the ASM instance. #!/bin/ksh export ORACLE_HOME=`grep ASM /etc/oratab | cut -d: -f2` export PATH=$PATH:~$user/dba/scripts/bin:$ORACLE_HOME/bin export SID=`grep ASM /etc/oratab | cut -d: -f1` printf "\n%-15s %-14s %-11s %s\n" "ASM disk" "based on" "Minor,Major" "Size (Mb)" printf "%-15s %-14s %-11s %s\n" "===============" "=============" "===========" "=========" for i in `/etc/init.d/oracleasm listdisks` do v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk '{print $2}' | sed 's/\"//g'` v_minor=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}...

When talking ASM between DBAs and System guys I had to let them "see" the new filesystem in the way they are used to. I parsed the output of asmcmd utility to be as close as it can be to the command in Unix/Linux, I called it asmbdf: #!/bin/ksh export user=oracle export ORACLE_HOME=`grep ASM /etc/oratab | cut -d: -f2` export PATH=$PATH:~$user/dba/scripts/bin:$ORACLE_HOME/bin export ORACLE_SID=`grep ASM /etc/oratab | cut -d: -f1` asmcmd lsdg | \ awk '{ if ( FNR == 1 ) { printf "%-20s %10s %10s %10s %10s %-20s\n","Filesystem","Size","Used","Avail","Use%","Mounted on" } if ( FNR > 1 ) { if ( $2 == "EXTERN" ) { REDUNDENCY=1 } if ( $2 == "NORMAL" ) { REDUNDENCY=2 } if ( $2 == "HIGH" ) { REDUNDENCY=3 } printf "%-20s %10d %10d %10d %10.2f%% %-20s\n",$(NF),($8/REDUNDENCY)*1024,(($8-$9)/REDUNDENCY)*1024,($9/REDUNDE...

I discovered that oracle has a utility to analyze EVENT 10046 trace files. It seems to arrange it in HTML and collect some more relevant information from the database. To create the trace file see tracing commands . The TRCANLZR can be found in Metalink Note: 224270.1 Enjoy it :)

orakill The orakill utility is provided only with Oracle databases on Windows platforms. The executable ( orakill.exe ) is available to DBAs to kill Oracle sessions directly from the DOS command line without requiring any connection to the database. In the UNIX world, a DBA can kill a shadow process by issuing the kill –9 command from the UNIX prompt. UNIX is able to provide this capability given that the UNIX operating system is based on processes that fork other processes. All processes can be listed by using the ps UNIX command. The Oracle background processes will be listed separately from all of the Oracle sessions since they have their own process. Unlike the UNIX operating system, Windows systems are thread-based. For each instance, the background processes and sessions are all contained within the oracle.exe executable. These processes are not listed in the "Processes" tab of Windows Task Manager. Each session creates its own thread within oracle.exe and t...