go to the oracle home and type the following:
opatch lsinventory -details > lsinv.txt
then copy to /tmp
then ftp from /tmp
spool cr_db.sql
set pages 1000
set head off
set termout off
set feedback off
set newpage none
set serveroutput on
select 'CREATE DATABASE '||name text
from v$database;
-- select 'CONTROLFILE REUSE' from dual; -- optional
select 'LOGFILE' from dual;
declare
print_var varchar2(200);
cursor c1 is select member
from gv$logfile where inst_id = 1
order by group#;
logfile gv$logfile.member%TYPE;
cursor c2 is select bytes
from gv$log where inst_id = 1
order by group#;
bytes number;
lsize varchar2(30);
begin
open c1;
open c2;
for record in (
select group#,
count(*) members
from gv$logfile
where inst_id = 1
group by group#)
loop
dbms_output.put_line(print_var);
fetch c2 into bytes;
if mod(bytes,1024) = 0 then
if mod(bytes,1024*1024) = 0 then
lsize := to_char(bytes/(1024*1024))||'M';
else
lsize := to_char(bytes/1024)||'K';
end if;
else
lsize := to_char(bytes);
end if;
lsize := lsize||',';
if record.members > 1 then
fetch c1 into logfile;
print_var := 'GROUP '||record.group#||' (';
dbms_output.put_line(print_var);
print_var := ''''||logfile||''''||',';
for i in 2..record.members loop
fetch c1 into logfile;
dbms_output.put_line(print_var);
print_var := ''''||logfile||''''||',';
end loop;
print_var := rtrim(print_var,',');
dbms_output.put_line(print_var);
print_var := ') SIZE '||lsize;
else
fetch c1 into logfile;
print_var := 'GROUP '||record.group#||' '''||logfile||''''||' SIZE '||lsize;
end if;
end loop;
close c1;
close c2;
print_var := rtrim(print_var,',');
dbms_output.put_line(print_var);
end;
/
select 'MAXLOGFILES '||RECORDS_TOTAL
from v\$controlfile_record_section
where type = 'REDO LOG';
select 'MAXLOGMEMBERS '||dimlm
from sys.x\$kccdi;
select 'MAXDATAFILES '||RECORDS_TOTAL
from v\$controlfile_record_section
where type = 'DATAFILE';
select 'MAXINSTANCES '||RECORDS_TOTAL
from v\$controlfile_record_section
where type = 'DATABASE';
select 'MAXLOGHISTORY '||RECORDS_TOTAL
from v\$controlfile_record_section
where type = 'LOG HISTORY';
select log_mode
from v\$database;
select 'CHARACTER SET '||value
from v\$nls_parameters
where parameter = 'NLS_CHARACTERSET';
select 'NATIONAL CHARACTER SET '||value
from v\$nls_parameters
where parameter = 'NLS_NCHAR_CHARACTERSET';
select 'DATAFILE'
from dual;
declare
cursor c1 is select * from dba_data_files
where tablespace_name = 'SYSTEM' order by file_id;
datafile dba_data_files%ROWTYPE;
print_datafile dba_data_files.file_name%TYPE;
begin
open c1;
fetch c1 into datafile;
-- there is always 1 datafile
print_datafile := ''''||datafile.file_name||''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,';
loop
fetch c1 into datafile;
if c1%NOTFOUND then
-- strip the comma and print the last datafile
print_datafile := rtrim(print_datafile,',');
dbms_output.put_line(print_datafile);
exit;
else
-- print the previous datafile and prepare the next
dbms_output.put_line(print_datafile);
print_datafile := ''''||datafile.file_name||
''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,';
end if;
end loop;
end;
/
select ';'
from dual;
spool off
select owner,
table_name,
column_name,
num_distinct,
last_analyzed
from all_tab_col_statistics
where owner in ('EXAM1','TEST2','EXAMPLE2');
select *
from all_part_col_statistics;
select *
from all_indexes;
select *
from sys.aux_stats\$;
select owner,
table_name,
last_analyzed
from all_tab_col_statistics
where owner not in ('SYS','PERFSTAT','DBSNMP','SYSTEM','SYSMAN');
select table_name,
clustering_factor,
num_rows
from dba_indexes
where table_owner in ('SCHEMA_OWNER') and
clustering_factor > 0;
select table_name,
clustering_factor,
num_rows
from dba_indexes
where table_owner in ('SCHEMA_OWNER') and
clustering_factor > 0;
select owner from dba_tables where table_name = 'TEST_SETUP';
select count(*) from dnalims.test_setup;
select num_rows from dba_tables where table_name='TEST_SETUP';
set linesize 150
set pagesize 66
set head on
column table_name format a15 heading 'Table'
column index_name format a15 heading 'Index'
column num_rows format 999,999,999 heading 'Rows'
column num_blocks format 999,999 heading 'Data Blocks'
column avg_data_blocks_per_key format 999,999 heading 'Data Blks /Key'
column avg_leaf_blocks_per_key format 999,999 heading 'Leaf Blks /Key'
column clustering_factor format 999,999 heading 'Clst Fact'
column blocks format 999,999 heading 'Blks'
column t.num_rows/i.clustering_factor format 999,999 heading 'Ratio'
SELECT i.table_name,
i.index_name,
t.num_rows,
t.blocks,
i.avg_data_blocks_per_key,
i.avg_leaf_blocks_per_key,
i.clustering_factor,
t.num_rows/i.clustering_factor,
to_char(o.created,'MM/DD/YYYY HH24:MI:SSSSS') Created
from dba_indexes i,
dba_objects o,
dba_tables t
where i.index_name = o.object_name and
i.table_name = t.table_name and
table_owner = 'DNALIMS' and
t.table_name = 'TEST_SETUP' and
t.num_rows > 0 and
i.clustering_factor > 0
order by 1;
/
set linesize 150
set pagesize 66
set head on
column table_name format a15 heading 'Table'
column index_name format a15 heading 'Index'
column num_rows format 999,999,999 heading 'Rows'
column num_blocks format 999,999 heading 'Data Blocks'
column avg_data_blocks_per_key format 999,999 heading 'Data Blks /Key'
column avg_leaf_blocks_per_key format 999,999 heading 'Leaf Blks /Key'
column clustering_factor format 999,999 heading 'Clst Fact'
column blocks format 999,999 heading 'Blks'
column t.num_rows/i.clustering_factor format 999,999 heading 'Ratio'
SELECT i.table_name,
i.index_name,
t.num_rows,
t.blocks,
i.avg_data_blocks_per_key,
i.avg_leaf_blocks_per_key,
i.clustering_factor,
t.num_rows/i.clustering_factor,
to_char(o.created,'MM/DD/YYYY HH24:MI:SSSSS') Created
from dba_indexes i, dba_objects o, dba_tables t
where i.index_name = o.object_name
and i.table_name = t.table_name
and table_owner = 'DNALIMS'
and t.num_rows > 0
and i.clustering_factor > 0
order by 1;
/
Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Spool c:\bei\tmp
Select 'Select '''||owner||'.'||table_name||' : ''||count(*) from '||table_name||';',
to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
where owner not in ('SYS','SYSTEM','PERFSTAT')
order by table_name
/
Spool off
Spool count_&user_._&date_
@tmp.LST
Spool off
Select 'WRM$_WR_CONTROL : '||count(*)||num_rows from WRM$_WR_CONTROL;
column table_name format a15 heading 'Table'
column index_name format a15 heading 'Index'
column num_rows format 999,999,999 heading 'Rows'
column num_blocks format 999,999 heading 'Data Blocks'
column avg_data_blocks_per_key format 999,999 heading 'Data Blks /Key'
column avg_leaf_blocks_per_key format 999,999 heading 'Leaf Blks /Key'
column clustering_factor format 999,999 heading 'Clst Fact'
column blocks format 999,999 heading 'Blks'
column t.num_rows/i.clustering_factor format 999,999 heading 'Ratio'
SELECT i.table_name,
i.index_name,
t.num_rows,
t.blocks,
i.avg_data_blocks_per_key,
i.avg_leaf_blocks_per_key,
i.clustering_factor,
t.num_rows/i.clustering_factor,
to_char(o.created,'MM/DD/YYYY HH24:MI:SSSSS') Created
from dba_indexes i, dba_objects o, dba_tables t
where i.index_name = o.object_name
and i.table_name = t.table_name
and table_owner = 'DNALIMS'
and t.num_rows > 0
and i.clustering_factor > 0
order by 1;
/
Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Spool c:\bei\tmp
Select 'Select '''||owner||'.'||table_name||' : ''||count(*) from '||table_name||';',
to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
where owner not in ('SYS','SYSTEM','PERFSTAT')
order by table_name
/
Spool off
Spool count_&user_._&date_
@tmp.LST
Spool off
Select 'WRM$_WR_CONTROL : '||count(*)||num_rows from WRM$_WR_CONTROL;
sql_id can be found in the following views
v$sql, V$sqlstat, or dba_hist_sqlstat
SET LINESIZE 100
COLUMN trace_file FORMAT A60
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
In 11g
SET LINESIZE 100 COLUMN value FORMAT A60 SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
tkprof
$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table “” not found /]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)
--------------------------------------------------------------------------------------------------------------------
If you do not know the Session ID then you can use a select similar to the following to help you identify the target session:
column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';
----------------------------------------------------------------------------------------------------------------------
Note:21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
Note:160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
Note:371678.1 Capture 10046 Traces Upon User Login (without using a trigger)
Note:1102801.1 How to Get 10046 Trace for Parallel Query
Note:242374.1 Tracing PX session with a 10046 event or sql_trace
Note:258418.1 Getting 10046 Trace for Export and Import
/*
Multiple line comments
Can go between these delimiters
*/
SET TERM OFF -- TERM = ON will display on terminal screen (OFF = show in LOG only)
SET ECHO ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON -- TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON -- TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF -- HEADING = OFF will hide column headings
SET FEEDBACK OFF -- FEEDBACK = ON will count rows returned
SET PAUSE OFF -- PAUSE = ON .. press return at end of each page
SET PAGESIZE 0 -- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
SET LINESIZE 80 -- LINESIZE = width of page (80 is typical)
SET VERIFY OFF -- VERIFY = ON will show before and after substitution variables
-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;
SPOOL OFF
This is located in a common library
#!/usr/bin/ksh
# Purpose: Search the alert log of $ORACLE_SID for error
# messages.
# If any error messages are found, send notification
# (see Notes below).
#
# Prereqs: $ORACLE_SID environment variable must be set or
# passed as a positional parameter. Also, $ORATAB
# environment variable must be set.
#
# Notes: If this script is invoked from a terminal session,
# display the results to the screen (STDOUT); otherwise,
# send a mail message to the specified mail accounts.
#
# Exit Status: 99 = Required input, $ORACLE_SID, not supplied.
# 90 = Invalid Oracle sid.
# 95 = init.ora file not found for input sid.
# 97 = Alert log file does not exist.
#
# Author: Michael Culp
# Date: May 2002
#===============================================================
# R E V I S I O N H I S T O R Y
#===============================================================
# Who:
# Date:
# Comments:
################################################################
function error_notification
{
if [ "$TERMINAL" = "Y" ]; then
echo "$ERROR_MSG"
else
env|grep SEND_MAIL_TO|while read LINE
do
alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
echo "$ERROR_MSG" | mail $(sendvar)
done
fi
}
export SEND_MAIL_TO='mike.culp@gmail.com'
export SEND_MAIL_TO2='mike.culp@gmail.com'
SERVER=$(uname -n)
DATE=$(date +%c%t)
ERROR_MSG_FILE=/home/oracle/alertlog_message
# check to see if command is being executed interactively
/usr/bin/tty -s
STATUS=$?
if [ $STATUS = 0 ]
then
clear
TERMINAL=Y
else
TERMINAL=N
fi
# Test for required inputs.
# Positional parameters override env variables.
if [ ${1:-0} = 0 ]; then
if [ ${ORACLE_SID:-0} = 0 ]; then
ERROR_MSG=`echo "ORACLE_SID must be supplied as input to $0"`
error_notification
exit 99
fi
else
export ORACLE_SID=$1
fi
# Call the validate_sid function to verify ORACLE_SID is valid.
# The function also sets the ORACLE_HOME and ORACLE_BASE variables.
validate_sid
if [ "$VALID_SID" != "Y" ]; then
ERROR_MSG=`echo "ORACLE_SID ($ORACLE_SID) is invalid. Script is $0"`
error_notification
exit 90
fi
INIT_PATH="$ORACLE_HOME"/dbs
INIT_FILE="$INIT_PATH"/init"$ORACLE_SID".ora
if [ ! -s "$INIT_FILE" ]; then
ERROR_MSG=`echo "$INIT_FILE does not exist. Script is $0"`
error_notification
exit 95
fi
LOG_PATH="/"`awk -F= '/background_dump_dest/ { print $2 }' "$INIT_FILE" |tr -s " "|cut -f2-30 -d/`
ALERTLOG="$LOG_PATH"/alert_"$ORACLE_SID".log
# Create a list of valid Oracle error message prefixes by parsing
# the oerr script in $ORACLE_HOME/bin
/home/oracle/bin/awk_message_prefixes > /tmp/ora_prefixes
# Archiver error message prefix is not included in the oerr
# script. Add an entry for this into the "ora_prefixes" file.
echo '^ARC[0-9]: Error' >> /tmp/ora_prefixes
# Search the alert.log for any occurrences of an Oracle error
# message prefix.
grep -bsf /tmp/ora_prefixes $ALERTLOG > $ERROR_MSG_FILE
status=$?
if [ $status -eq 1 ]; then # no matches found
if [ $TERMINAL = 'Y' ]; then
echo "Amazingly enough, there are NO errors in $ALERTLOG"
fi
elif [ $status -gt 1 ]; then
ERROR_MSG=`echo "$ALERTLOG does not exist."`
error_notification
exit 97
fi
# If there are any messages in the message file, send
# appropriate notifications.
if [ -s $ERROR_MSG_FILE ]; then
echo "\nMessages from server $SERVER on $DATE" >> $ERROR_MSG_FILE
if [ $TERMINAL = 'Y' ]; then
cat $ERROR_MSG_FILE
else
env|grep SEND_MAIL_TO|while read LINE
do
alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
mail $(sendvar) < $ERROR_MSG_FILE
done
fi
fi
if [ -f $ERROR_MSG_FILE ]; then
rm $ERROR_MSG_FILE
fi
if [ -f /tmp/ora_prefixes ]; then
rm -f /tmp/ora_prefixes
fi
exit
Clone DB OracleBase article
This article is a test
When doing any type of upgrade/patch I'm often asked about backing up the existing binaries and how to save the customer from minimal downtime. One of the ways this can be achieved is by simply making another oracle home tar copied from the existing home. This now gives several options that allow flexibity in many ways.
# tar -cvf /tmp/u01.tar /u01
First parameter is the location of the tar file, second paramater is the directory where the oracle home is located
# gzip /tmp/u01.tar
You can then gzip it to make it smaller/easier to move it
Once moved to the destination server
# gunzip /tmp/u01.tar.gz
# cd /
# tar -xvf /tmp/u01.tar
Run the root configutation scripts, generated as part of the original installation, on the destination server as the "root" user.
/u01/app/oraInventory/orainstRoot.sh /u01/app/oracle/product/11.2.0/db_1/root.sh
If you have not prepared the ".bash_profile" as part of the destination server setup, copy the file from the source to the destination server.
# scp /home/oracle/.bash_profile oracle@192.168.2.136:/home/oracle/.bash_profile
Edit the ".bash_profile" file on the destination server, giving it the correct value for the ORACLE_HOSTNAME environment variable.
Amend any hostname or IP references in the "listener.ora" and "tnsnames.ora" files in the "$ORACLE_HOME/network/admin" directory.
Edit the "/etc/oratab" making sure all instances are referenced correctly. In my case I need to add the following entry.
DB11G:/u01/app/oracle/product/11.2.0/db_1:Y
Tested a table on Exadata
I felt most folks looking for full-table scans were not getting the full picture and created my own script to pick up the operations I felt went into this category.
#!/bin/ksh
sqlplus -s "/ as sysdba" <<EOF
spool fts_MRC.log
set linesize 200
set pagesize 66
col operation format a13
col object_name format a32
col object_owner format a10
col options format a15
col executions format 999,999,999
select
a.sql_id,a.object_owner,a.object_name, rtrim(a.operation) operation,
a.options, sum(b.executions) executions, c.bytes, (sum(b.executions)*c.bytes)/(1024*1024) fts_meg
from
v\$sql_Plan a, v\$sqlarea b, dba_segments c
where (a.object_owner=c.owner and a.object_name=c.segment_name) and
a.address=b.address and
a.operation IN ('TABLE ACCESS','INDEX','HASH JOIN') and
nvl(a.options,'NULL') in ('FULL','FULL SCAN','FAST FULL SCAN','SKIP SCAN','SAMPLE FAST FULL SCAN','OUTER','NULL') and
a.object_owner not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS') and
b.executions>1
group by a.sql_id,a.object_owner, a.object_name, operation, options, c.bytes
order by 8, a.object_owner,a.object_name,operation, options, executions desc;
spool off
EOF
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';