Oracle Database Experts

function ALERT {
export DB1=$ORACLE_SID
export DB=$(echo $ORACLE_SID|tr '[A-Z]' '[a-z]' |sed -e '$s/.$//')
export ASMDB=rdbms
if [ "$DB" = "+asm" ]; then export ASMDB=asm ; fi
if [ "$DB" = "+as" ]; then export ASMDB=asm ; export DB="+asm"; fi
[ -d "$ORACLE_BASE/diag/$ASMDB/$DB/$ORACLE_SID/trace" ] && view $ORACLE_BASE/diag/$ASMDB/$DB/$ORACLE_SID/trace/alert_$ORACLE_SID.log ||view $ORACLE_BASE/diag/$ASMDB/$DB1/$ORACLE_SID/trace/alert_$ORACLE_SID.log
}

Notes:
* Works in both RAC and non-RAC.
* Works for ASM and non-ASM instances.
* The ALERT function will open the alert log associated with your current ORACLE_SID

** Requirements:
1. ORACLE_BASE must already be set
2. Add this script to your .profile or .bash_profile depending on the shell you are using

Posted by Charles Kim, Oracle ACE Director

function trace {
# DB = The sed command strips off the last character of ORACLE_SID off
export DB1=$ORACLE_SID
export DB=$(echo $ORACLE_SID|tr '[A-Z]' '[a-z]' |sed -e '$s/.$//')
export ASMDB=rdbms
if [ "$DB" = "+asm" ]; then export ASMDB=asm ; fi
if [ "$DB" = "+as" ]; then export ASMDB=asm ; fi
[ -d "$ORACLE_BASE/diag/$ASMDB/$DB/$ORACLE_SID/trace" ] && cd $ORACLE_BASE/diag/$ASMDB/$DB/$ORACLE_SID/trace || cd $ORACLE_BASE/diag/$ASMDB/$DB1/$ORACLE_SID/trace
}

Notes:
* Works in both RAC and non-RAC.
* Works for ASM and non-ASM instances.
* The trace function will take you to the directory where all the trace files are located including the alert log

** Requirements:
1. ORACLE_BASE must already be set
2. Add this script to your .profile or .bash_profile depending on the shell you are using

Posted by Charles Kim, Oracle ACE Director

Excerpt from Introduction to Oracle Recommended Patches [ID 756388.1]
What are Recommended Patches?

Oracle has introduced a set of Recommended Patches which make it easier for customers to obtain and deploy fixes for known critical issues encountered in targeted environments and configurations. As part of Oracle’s overall maintenance strategy, these provide proactive patch recommendations to customers seeking to upgrade or to improve the stability of their current environments. Customers are advised to install Recommended Patches that apply to their environment.

Recommended Patches are available for products listed below. For details, please review the My Oracle Support notes.

Product My Oracle Support Note
Oracle Database Note:756671.1
Oracle Enterprise Manager Note:822485.1
Oracle Fusion Middleware Note:859115.1

This note will be updated as Oracle announces Recommended Patches for other Oracle products.

Posted by Charles Kim
Oracle ACE Director

Here’s what I think is an incredible piece of code to generate SQL*Loader control files for any table in the database.

cat generate_control.sql
def TAB='&1'
set head off feed off pages 0 trims on serveroutput on size 1000000 lines 2000 ver off
var v_tab VARCHAR2(100);

spool &TAB..control
declare
v_col VARCHAR2(255);

cursor c1 is
select column_name, data_type
from dba_tab_columns
where table_name=upper('&TAB');

v_counter NUMBER := 0;

BEGIN
:v_tab := '&TAB';
dbms_output.put_line('load data');
dbms_output.put_line('INFILE '||chr(39)||'data/'||:v_tab||'.txt'||chr(39)||' BADFILE '||chr(39)||:v_tab||'.bad'||chr(39)||' DISCARDFILE '||
                                chr(39)||:v_tab||'.dis'||chr(39));
dbms_output.put_line('INTO TABLE '||:v_tab);
dbms_output.put_line('APPEND');
dbms_output.put_line('FIELDS TERMINATED BY '||chr(39)||'|'||chr(39)||' trailing nullcols ');

FOR r1 in c1 LOOP

IF v_counter = 0 THEN
  IF substr(r1.data_type,0,9) = 'TIMESTAMP' THEN
    v_col := '( '||r1.column_name||' timestamp '||chr(34)||'YYYY-MM-DD HH24:MI:SS.FF'||chr(34);
    dbms_output.put_line(v_col);
  ELSE
    v_col := '( '||r1.column_name;
    dbms_output.put_line(v_col);
  END IF;
else
  IF substr(r1.data_type,0,9) = 'TIMESTAMP' THEN
    v_col := ','||r1.column_name||' timestamp '||chr(34)||'YYYY-MM-DD HH24:MI:SS.FF'||chr(34);
    dbms_output.put_line(v_col);
  ELSE
    v_col := ','||r1.column_name;
    dbms_output.put_line(v_col);
  END IF;
END IF;

-- CREATETIMESTAMP timestamp "YYYY-MM-DD HH24:MI:SS.FF",

v_counter := v_counter + 1;
END LOOP;
dbms_output.put_line(')');

END;
/

spool off
set lines 66

Posted by Charles Kim
Oracle ACE Director

Frist set initialization parameter:
*.cluster_database=false

SQL> startup mount restrict exclusive;
ORACLE instance started.

Total System Global Area 4429185024 bytes
Fixed Size 2102032 bytes
Variable Size 1191185648 bytes
Database Buffers 3221225472 bytes
Redo Buffers 14671872 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

The easiest and probably the most effective way to automate Oracle software installation is to use response files. You can either use the response files that is supplied with Oracle or create your own. To create your own response files, simply execute runInstaller with the record option. Near the end of the installation, instead of clicking on the Install option, cancel the installation and you will see the response file at your specified location.

runInstaller -record -destinationFile /tmp/crs.rsp
runInstaller -record -destinationFile /tmp/crs_up.rsp
runInstaller -record -destinationFile /tmp/asm.rsp
runInstaller -record -destinationFile /tmp/asm_up.rsp

Get the latest relevant ASM information from Viskosity Technologies’ Automatic Storage Management (ASM) Pocket Reference Guide.

Provided by Charles Kim, Oracle ACE Director

Get the latest relevant RAC information for Viskosity Technologies’ Real Application Clusters (RAC) Pocket Reference Guide.

Provided by Charles Kim, Oracle ACE Director

Here’s Charles Kim’s Oracle Database 11g New Features presentation at the Dallas Oracle User Group (DOUG) on May 2008.

By Charles Kim, Oracle ACE

Here’s Charles Kim’s Oracle Database 11g New Features presentation at the Suncoast Oracle User Group (SOUG) in Tampa, Florida.

Presentation Date – March 2008 by Charles Kim

Solaris:

cat /var/opt/oracle/oraInst.loc |grep inventory_loc |cut -d= -f2 |xargs -I {} -t cat {}/ContentsXML/inventory.xml

Linux and other Unix OS:

cat /etc/oraInst.loc |grep inventory_loc |cut -d= -f2 |xargs -I {} -t cat {}/ContentsXML/inventory.xml

Notes:
1. The -t option tells it to display out the syntax before execution
2. The -I is for Insert mode. utility is executed for each
line from standard input, taking the entire
line as a single argument, inserting it in
argument s for each occurrence of replstr.

Posted by Charles Kim, Oracle ACE Director

Here’s a recent presentation by Charles Kim, Bill Burke, and Nitin Vengurlekar on August 2009

Three of the authors of the Data Guard Handbook met in Dallas and provided a 2 hours presentation on Data Guard. Each of the authors took components of Data Guard and provided an in-depth overview of the technology.

Posted by Charles Kim, Oracle ACE