ORACLE: Unpredictable Query Results and Core Dumps.

I ran in to this issue on our 10.0.2.1 instance. On SunOS 10.

I was getting these error messages:
ORA-00600: internal error code, arguments: [KGHLKREM1], [0x3C1120018], [], [],
[], [], [], []
Wed Jul 5 10:43:35 2006
Errors in file /u01/app/oracle/admin/gedb/udump/gedb_ora_12927.trc:
ORA-07445: exception encountered: core dump [kgscDump()+232] [SIGSEGV] [Address
not mapped to object] [0x000000010] [] []
ORA-00600: internal error code, arguments: [KGHLKREM1], [0x3C1120018], [], [],
[], [], [], []

This was causing the same query (using PQ's) when execute over and over to return different results.

The core dumps were constanly filling up the /var directory.

In Oracle the heapdump shows that the problem resides in a chunk called qesblFilter_seg which is used for "bloom filter"
functionality. These issues are being caused by Bug 4927717 and Bug 5076183.
.
The "bloom filter" functionality is a new feature in 10 R2 for PQ related operations.

I have set the following parameter on hulk in the init.ora file as well as “altering the system” so that it is now in effect for the instance.
The following parameter turns off the “bloom filter" functionality.

_bloom_filter_enabled=FALSE

There might be a small performance impact on PQ operations, however, it should be minor, as turning this off causes it to simply revert back to the 10 R1 behavior. There are several other bloom filter related bugs on 10.2.0.1 as well. Turning this off will also prevent those issues from occurring as well.

There is no patch for these bugs at this time.

Note: I had to add double quotes around the param. since it is a hidden param. to get it to work with the alter system statement:

1* alter system set "_bloom_filter_enabled"=FALSE
10:59:05 SQL> /

System altered.
Elapsed: 00:00:00.02

Including Datafiles

Here is a nice little feature that works in Oracle 10g.

drop tablespace including contents and datafiles.

Oh I love that; all is cleaned up in one easy statement.

Exporting Made A Little Easier

Even though exporting a several tables in a series may not necessarily be a daunting task, it sure can be a tedious one. Therefore the creation of the following script.

A buddy of mine and myself combined two different pieces of our own code to come up with this nice little export script.

The on-demand export script allows you to create a flat file called ondemand.lst and list within it the tables you want to export in the following manner:

owner.tablename
owner.tablename
etc.

The script will loop through this lst file until all entries have been exported.

As an added value, the table being exported will be gzip’ed in memory before being written to disk.

Here is the code for the ondemand_exp.sh script:
Note: Remember to change the

#!/bin/ksh
. /home/sfulke/.profile

curdt=`date +"%Y-%m-%d_%H:%M"`
clear

################################################################
# MODULE: ondemand_exp.sh (ksh)
# DESCRIPTION: Allows user to export tables by listing them in file . Output is
# compressed in memory via gzip.
# PURPOSE: To increase user options when exporting Oracle tables.
# Example: ./ondemand_exp.sh
#Disclaimer: Use is at your sole risk. All info is provided "as is", without any warranty, # whether express or implied, of its accuracy, completeness.
# CHANGE HISTORY:
# Date Who Change Description
# ------------- --------------------- ----------------------------
# 08-NOV-2004 Tony Bryant (abryan) Initial Creation
# 08-NOV-2004 Samual C. Fulkerson Export code supplied
################################################################

exp_log='’
exp_fil=''
exp_cod=''

filename=$exp_cod/ondemand.lst
read_file_line=`wc -l < $filename`
i=1
while [ "$i" -le "$read_file_line" ]
do
table_name=`cat $filename | head -$i | tail -1`

# remove perviously created named pipe
rm $exp_cod/.ondemand.exp.pipe
# create a named pipe
mknod $exp_cod/.ondemand.exp.pipe p
# read the pipe - output to zip file in the background
gzip < $exp_cod/.ondemand.exp.pipe > $exp_fil/exp_$table_name.$curdt.dmp.gz &
# feed the pipe
command="exp userid= file=$exp_cod/.ondemand.exp.pipe log=$exp_log/exp_$table_name._$curdt.log grants=Y index
es=Y rows=Y constraints=Y direct=Y compress=Y tables=$table_name"
$command
wait
i=`expr $i + 1`
done

exit

You can add a mailx statement to email you upon creation it you like. For exporting a large table list you might even wan to add an error checking piece for grep’ing through the log files and emailing you the results to simplify the task somewhat.

Hiding Passwords from Unix

When you execute a sqlplus session from a unix command line as such (sqlplus /) you can see the user name and password by doing a simple ps –ef from the command line. This of course is a serious security violation. One way to avoid this is to allow sqlplus to prompt you for the password.

If you are executing a script (sqlplus / @scriptname.sql) you will be able to do a ps –ef and see it there as well. To avoid this you can imbed a connect string (connect /) into the first line of the sql script you are executing(sqlplus @scritpname.sql).

Another way to do this would be using an OS authenticated user account to execute the sql script (sqlplus / @scriptname.sql).

However, since we use SAS and since SAS does not play well with Oracle’s OS Authentication and requires a username/password you could try it this way:

echo password |sqlplus username @scriptname.sql

Thanks

FlashBacks (UnDroping A Table)

It is a beautiful day outside, the sun is shining and a cool breeze is gently blowing… and then the phone rings. “Opps, I dropped a table! I dropped the big, I need it now to complete the production process table!”

This table was 611 gbs in size. The process that creates it takes many hours (36) to complete as there are many steps and joins that make it up.

So as not to keep you in suspense, I used the flashback feature of Oracle 10R2 to recover it from the “recycle bin”. It took less than 1 second to accomplish the recovery.

Note: This is Oracle 10gR2 (10.2.0.1) on SunOS 10.
This base is not running in archive log mode.

Here is the syntax from an example:

Note: Don't use this next line unless you really want to empty your recycle bin!
11:48:07 SQL> purge recyclebin;
Recyclebin purged.

11:48:21 SQL> show recyclebin
11:48:29 SQL>

11:48:49 SQL> create table test1
11:48:59 2 as
11:49:00 3 select * from dba_tables;
Table created.

11:49:07 SQL> drop table test1;
Table dropped.

11:49:23 SQL> show recyclebin
ORIGNAME_PLUS_SHOW_RECYC OBJECTNAME_PLUS_SHOW_RECYC
-------------------------------- ------------------------------
OBJTYPE_PLUS_SHOW_RECYC DROPTIME_PLUS_SHOW_
------------------------- -------------------
TEST1 BIN$FX3qmRLpXRfgRAADug+/iA==$0
TABLE 2006-06-05:11:49:23

11:49:31 SQL> flashback table test1 to before drop;
Flashback complete.

11:51:47 SQL> show recyclebin;

11:52:34 SQL> select count(1) from test1;
COUNT(1)
--------------------
1,970

It is as simple as that.

Now if you do not want to have a table go to the recycle bin when you drop a table… then:

11:54:10 SQL> drop table test1 purge;

Table dropped.

Elapsed: 00:00:00.82
11:54:30 SQL> show recyclebin;

To purge a user recycle bin:
11:54:32 SQL> purge recyclebin;
Whatever user you are logged in as at the time of this statement execution will have their recycle bin objects purged.

If you want to purge all users recycle bins:
11:55:01:17 SQL> purge dba_recyclebin; (for 9i and 10R1)

11:55:12:22 SQL> connect sys/ as sysdba
Connected
11:55:12:83 SQL> purge dba_recyclebin; (for 10gR2)

Thanks

Patching from 10.2.0.1 to 10.2.0.2

Today’s task is to patch from .1 to .2 on our SunOS(10) E25K, 160 gbs of ram, 20 dual core CPU’s and 18 terabytes of useable RAID 5 disks.

I would highly recommend that you do a metalink (http://metalink.oracle.com) search for: <10.2.0.2 Patch Set - Known Issues> before you get to far into this. Consider, how this search id’s Note 359415.1. This note contains important additional information for the SunOS I am dealing with. It lets me know that there is an additional OUI (Oracle Universal Installer) patch that must be installed post 10.2.0.2 and pre any other patchs (i.e. 1offs, .3, etc.). This additional patch is to correct bug 5117016. This bug causes the OUI to install the libserver10.a library into $ORACLE_HOME/rdbms/lib
instead of into $ORACLE_HOME/lib. This in-turn can cause subsequent problems when applying patches on top of 10.2.0.2. Therefore this is a Mandatory Patch and must be applied immediately after completing the installation of 10.2.0.2

I downloaded the patch set from metalink (p4547817_10202_SOLARIS64.zip) aprox. 788 mbs in size and unzip’ed it using Sun’s unzip feature.

I pointed my web browser to the README.html file that came with the patch set.

Validated that the cold backup (oracle was down during the backup) completed successfully.

I made sure that the shared_pool_size and java_pool_size were at least 150M or larger in the init.ora file.

I then shutdown the base: sqlplus “/ as sysdba” from the oracle Unix user and then by issuing a . I also shutdown the lsnr. via command.

Next I made of copy of the ../Disk1/response/patchset.rsp file in the ../Disk1/ directory to install the patch set noninteractively. And of course I edited the variables that required changing in the patchset.rsp file.
 A couple of “got-u’s” to watch for:
 One: ensure that the ORACLE_HOME and ORACLE_SID values are set in your environment.
 In the patchset.rsp template ensure you set the oracle home to your current oracle home value. For example: ORACLE_HOME="/u01/app/oracle/product/10.2.0"
 Ensure that you set the oracle home name value as follows: ORACLE_HOME_NAME="OraDb10g_home1".

Then from the ../Disk1/ directory I executed the following on the command line:
./runInstaller -silent –responseFile ../Disk1/patchset.rsp

Then executed the $ORACLE_HOME/roo.sh.

Then since this is a single-instance database I logged into sqlplus as sysdba and did a STARTUP MOUNT.

Then I entered the following SQL*PLUS commands:
1. SQL> STARTUP UPGRADE
2. SQL> SPOOL patch.log
3. SQL> start $ORACLE_HOME/rdbms/admin/catupgrd.sql
4. SQL> SPOOL OFF
5. SQL> SHUTDOWN IMMEDIATE
6. SQL> STARTUP
7. SQL> start $ORACLE_HOME/rdbms/admin/utlrp.sql

The catupgrd.sql script reexectues your catalog and catproc sql scripts. The utlrp.sql script recompiles all PL/SQL invalid packages now instead of when the packages are accessed for the first time.

Now I can restart the lsnr and turn the user loose again; only now on a 10.2.0.2 instance!

GUI, Not Really (Scripts)

I am not a strong proponent for GUI’s. I am not opposed to them exactly; I just think you should know how to operate at the command-line level; then you can use a GUI if you so choose. With that typed; here are some of the scripts I use regularly instead of GUI’s:

Remember the old TOP tool in OEM? Basically this script produces similar output for the most part given you session information.

"top.sql" 93 lines, 2369 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 5/16/1999 --
-- Purpose: Display Oracle session information. --
-- Example: sqlplus / @top.sql --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------

create table toptab
as
select sid,sum(value) as memory
from v$sesstat
group by sid
/
set pagesize 132 linesize 2048 trimspool on recsep off space 2 wrap off
column username format a10
column sid format 999999
column serial# format 9999999
column osuser format a10
column memory format 999999999999
column command format a12
column status format a8
column machine format a15
column program format a25
spool top.log
select username
,v$session.sid
,serial#
,osuser
,memory
,status
,decode(command ,1, 'Create Table'
,2, 'Insert'
,3, 'Select'
,4, 'Create Cluster'
,5, 'Alter Cluster'
,6, 'Update'
,7, 'Delete'
,8, 'Drop'
,9, 'Create Index'
,10, 'Drop Index'
,11, 'Alter Index'
,12, 'Drop Table'
,13, '---'
,14, '---'
,15, 'Alter Table'
,16, '---'
,17, 'Grant'
,18, 'Revoke'
,19, 'Create Synonym'
,20, 'Drop Synonym'
,21, 'Create View'
,22, 'Drop View'
,23, '---'
,24, '---'
,25, '---'
,26, 'Lock Table'
,27, 'No Operation'
,28, 'Rename'
,29, 'Comment'
,30, 'Audit'
,31, 'Noaudit'
,32, 'Create External Database'
,33, 'Drop External Database'
,34, 'Create Database'
,35, 'Alter Database'
,36, 'Create Rollback Segment'
,37, 'Alter Rollback Segment'
,38, 'Drop Rollback Segment'
,39, 'Create Tablespace'
,40, 'Ater Tablespace'
,41, 'Drop Tablespace'
,42, 'Alter Session'
,43, 'Alter User'
,44, 'Commit'
,45, 'Rollback'
,46, 'Savepoint'
, 'Unknown') as command
,program
,machine
from v$session, toptab
where v$session.sid = toptab.sid
and v$session.sid is not null
order by status, memory desc, username, osuser
/
spool off
set pagesize 0 feedback off
select ' '
from dual
/
select ' '
from dual
/
select ' NOTE: Alter System Kill Session''SID,Serial#'';' from dual
/
drop table toptab;
set pagesize 23 linesize 80 trimspool off space 2 feedback on wrap on
clear columns


This little script allows you to quickly display all of the objects in tablespace and their size:

"segment.sql" 20 lines, 867 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 2/10/2006 --
-- Purpose: Display all segments for a given tablespace. --
-- Parameter: Tablespace Name (Upper, Lower, Mixed Case OK --
-- Example: sqlplus / @segment.sql tablespace_namne --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------

set numf 999,999,999,999,999
column segment_name format a32

select segment_name,sum(bytes)
from dba_segments
where tablespace_name=upper('&tspacename')
group by segment_name
order by sum(bytes) asc
/

This script will allow you to do a string search of the sql area.

"tsql1.sql" 72 lines, 2795 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 1/12/2006 --
-- Purpose: Display information about sql currently in the --
-- sql area. --
-- Parameter: String (i.e. insert) Param. is not case senstive --
-- Example: sqlplus / @tsql1.sql string --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------


set linesize 132 pagesize 132
column username format a8
column osuser format a6
column command format a6
column sid format 9999
column serial# format 99999
select a.username,a.osuser,a.sid,a.SERIAL#,substr(sql_text,1,85)
,decode(command ,1, 'CrTab'
,2, 'Ins'
,3, 'Sel'
,4, 'CrClus'
,5, 'AltClus'
,6, 'Up'
,7, 'Del'
,8, 'Drop'
,9, 'CrInd'
,10, 'DrInd'
,11, 'AltInd'
,12, 'DrTab'
,13, '---'
,14, '---'
,15, 'AltTab'
,16, '---'
,17, 'Grant'
,18, 'Revoke'
,19, 'CrSyn'
,20, 'DrSyn'
,21, 'CrVi'
,22, 'DrVi'
,23, '---'
,24, '---'
,25, '---'
,26, 'LkTab'
,27, 'NoOper'
,28, 'Rena'
,29, 'Com'
,30, 'Aud'
,31, 'Noaud'
,32, 'CrExtDb'
,33, 'DrExtDb'
,34, 'CrDb'
,35, 'AltDb'
,36, 'CrRolbSeg'
,37, 'AltRolbSeg'
,38, 'DrRolbSeg'
,39, 'CrTsp'
,40, 'AltTsp'
,41, 'DrTsp'
,42, 'AltSes'
,43, 'AltUsr'
,44, 'Commit'
,45, 'Rolbk'
,46, 'Savpo'
, 'Unknown') as command
from v$session a,v$sqlarea b
where upper(b.sql_text) like upper('%&searchstring.%')
and b.address = a.sql_address
order by a.username,a.osuser,b.sql_text,a.sid,a.serial#
/


This script will display detail information about all the tablespaces:

"tsspace.sql" 35 lines, 1522 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 10/06/2004 --
-- Purpose: Display usage information about tablespaces. --
-- Example: sqlplus / @tsspace.sql --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------


clear columns
clear breaks
set linesize 132 trimspool on pagesize 66

column total_bytes format 999,999,999,999,999
column bytes_free format 999,999,999,999,999
column bytes_used format 999,999,999,999,999
column largest_free format 999,999,999,999,999
column bytes format 999,999,999,999,999
column tablespace_name format a28

spool tsspace.log

select a.TABLESPACE_NAME,a.BYTES total_bytes,a.BYTES - b.BYTES bytes_used,b.BYTES bytes_free
,b.largest largest_free,round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from (select TABLESPACE_NAME,sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME) a, (select TABLESPACE_NAME,sum(BYTES) BYTES,max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

spool off


This script will figure out and display how large a table can be in MB’s for a given tablespace based on the extent size feed into the script:

"./nexte.sql" 12 lines, 678 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 4/28/2005 --
-- Purpose: Used to figure out the largest table that can be --
-- created in a tablespace based on an extent size --
-- Parameters: A: Extent_Size (in MB's) B: Tablespace_Name --
-- Example: sqlplus / @nexte.sql 1 tablespace_name --
-----------------------------------------------------------------

select ltrim(to_char(sum(floor((bytes/1048576)/&&amp;amp;1))*&&1,'999,999,999,999')) as TotMBs
from dba_free_space
where tablespace_name = upper


Here is a nice little script for displaying information about the number of commits, logons, etc.

"tinfo.sql" 11 lines, 520 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 9/15/2004 --
-- Purpose: To display info. such as no. of transactions, --
-- commits, aborts, logons current, cumulative, etc. --
-- Example: sqlplus / @tinfo.sql --
-----------------------------------------------------------------

select value,name
from v$sysstat
where statistic# <7;>
"notdparm.sql" 26 lines, 856 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 9/15/2004 --
-- Purpose: To display non-default init. parm's. --
-- Example: sqlplus / @notdparm.sql --
-----------------------------------------------------------------

spool notdpar.lst
set pagesize 999

ttitle center 'Not Default Init. Parameters' skip 1 -
center '----------------------------' skip 2;
col num format 9999 head 'Parameters'
col name format a45 head 'Name' wrap
col type format 9999 head 'Type'
col value format a45 wrap head 'Value'
col isdefault format a9 head 'Default?'

select name,value
from v$parameter
where isdefault = 'FALSE'
order by name;

ttitle off
spool off

Here is a great little script for producing the ddl required to create an object. The 3 variables must be passed in, in upper case for this to work.

"./getddl.sql" 15 lines, 677 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 8/17/2005 --
-- Purpose: Used to create the ddl for object. --
-- Parameters: A: Object Type (i.e. Table) B: Object Name --
-- C: Object owner (variables must be in upper case --
-- Example: sqlplus / @getddl.sql table table_name owner_name --
-----------------------------------------------------------------

set heading off echo off pagesize 999 long 90000

spool getddl_&&amp;amp;amp;amp;amp;amp;amp;3.&&2.sql
select dbms_metadata.get_ddl('&&amp;amp;amp;amp;amp;amp;amp;1','&&2','&&3')
from dual;
spool off

This script will provide info. on the temporary tablespaces:


"././tmp1.sql" 12 lines, 631 characters------------------------------------------------------------------- Author: Sam Fulkerson ---- Date : 4/26/2006 ---- Purpose: To provide info. on the temporary tablespace. ---- Example: sqlplus / @tmp1.sql -------------------------------------------------------------------
set numf 999,999,999,999,999 linesize 132select tablespace_name, SUM(bytes_used) as Used, SUM(bytes_free) as Free, sum(bytes_used + bytes_free) as Totalfrom v$temp_space_headergroup by tablespace_nameorder by tablespace_name;


This script will display info. about who is using temp:


"././tmp2.sql" 23 lines, 963 characters------------------------------------------------------------------- Author: Sam Fulkerson ---- Date : 4/26/2006 ---- Purpose: To provide info. on who is using temporary ---- tablespace. ---- Parameters: Username-- Example: sqlplus / @tmp2.sql -------------------------------------------------------------------
column tablespace format a8
select a.username, b."USER", b.tablespace, b.contents, b.extents, b.blocksfrom sys.v_$session a, sys.v_$sort_usage bwhere a.saddr = b.session_addr;
select a.osuser, a.process, a.username, a.serial#, sum(b.blocks)*c.value/1024 sort_sizefrom sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter cwhere a.saddr = b.session_addrand c.name = 'db_block_size'and a.osuser like '&&1'group by a.osuser, a.process, a.username, a.serial#, c.value;

CRON (crontab info.)

From a Unix prompt type the following:

crontab –l (to list out the contents of the crontab.)
crontab –e (to edit the contents of the crontab.)
crontab –v (to verify the crontab.)
crontab –r (to remove the contents of the crontab.) Careful, as an ‘e’ and ‘r’ are next to each other on the keyboard.



Here is a header I put in all my crontab’s:

# Parameter 1: Minute <0-59>
# Parameter 2: Hour <0-23>
# Parameter 3: Day of the Month <1-31>
# Parameter 4: Month <1-12>
# Parameter 5: Day of the Week <0-6 sunday="0">
# Parameter 6: Command
# Place a comma between multiple values
# Place a hyphen to indicate a range
# Place an asterisk to indicate all possible values.
#############################################

Some AIX Stuff

To find out what maintenance level the OS is at type:
oslevel –r

FIND (Unix Command)

The “find” command is a very powerful and useful tool. Here are just a few examples to wet your taste buds with:

The below command will give you a list of all files over a gig in size. Just change the '/u01/' to look at what ever mount point is appropriate.

find /u01/ -size +1048576000c -print

The output can be redirected from standard output to a file by appending the following to the end of the command:

>/directory/path/file_name.txt

If you want to remove all files in a directory and its sub-directories, move to the starting directory and execute the following command:

cd /u01/directory_name/
find . -name '*.txt -mtime +32 -exec rm -f {} ";"

This will remove all *.txt files 32 days or older. A ‘oops’ to watch out for, is that some OS’s such as AIX tend to not always update the timestamp on a file when it is moved into another directory such as one being used for archiving. For example; if you just moved a file into a directory you have identified for archival purposes and the timestamp is say, 64 days old on the file in question and it is not re-stamped during the “mv” command and you run this “find” example, that file will be removed. This would be a bad thing if your backup routine had not executed on that file yet. Just a little something to keep in mind.

Creating a primary key with a parallel degree.

Step 1: (Create a primary key, disabled.)
alter table table_name
add (constraint pk_key_name
primary key (field_name))
disable primary key;

Step 2: (Create an unique index with the same name and columns as the primary key.)
create unique index pk_key_name on table_name(field_name)
tablespace tablespace_name
storage (initial ?M next ?M pctincrease 0)
parallel (degree ?)
nologging;

Step 3: (Now enable the primary key.)
alter table table_name enable primary key;

What do you want to do?

Let’s bring this question into more focus. I am of the opinion that simpler is better. “The more you over tic the plumbing, the easier it is to stop it up.” So from a high level:

If you want Replication, you want Streams.
If you want a Stand-By Database, you want Data Guard.
If you want Fail-Over, you want RAC.

Test Post

This is a test 'post', had this been an actual post, it would have contained useful information.