Sunday, March 26, 2006

Move Oracle Indexes To Another Tablespace

Some (mostly old-school) Oracle DBAs like to move indexes into a tablespace other than the tablespace(s) for tables. Moving an index does rebuild the index, for the limited cases when an index rebuild is a good thing.
This SQL will generate the SQL to move indexes.

select 'alter index ' || owner || '.' || index_name || ' rebuild
tablespace &newTablespace'
from sys.dba_indexes
where owner = '&ownerName'
/

Useful HP-UX Commands

Useful HP-UX commands:
bdf Show disk space.

tusc Trace system calls.

lsof List open files.

/usr/sbin/kmtune Show and sets kernel parameters.

Record system performance:
/usr/bin/sar System activity reporter.
vmstat Virtual memory statistics. Non-zero 'b' column is an I/O blockage.
netstat -i Network status. Large and increasing values for errors and collisions is a symptom of a network problem.

HP-UX operating system administrators may already be running the perfstat tool. If you see rep_server processes running from root, then perfstat is in use.
Perfstat records useful information. Record this info to a readable file with /opt/perf/bin/perfstat -z.

GlancePlus

glance

perfstat -c shows kernel parms.

/usr/sbin/swapinfo

Oracle RMAN Backup Scripts

#!/bin/ksh
#*************************************************************************************
# Open level 0 backup, without recovery catalog, including controlfile.
# (Level 0 incremental backs up all blocks.)
# DB must be in archivelog mode.
# "delete all input" removes archived log files after they have been backed up.
#*************************************************************************************
/bin/date
# The next setting is hard-coded in the RMAN command.
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

run {
allocate channel c1 type disk;
backup
incremental level 0
format '/u01/oracle/admin/backupset/%d.rman_level0_open_disk_nocatalog_database_%s_%p'
database plus archivelog delete all input;
backup
format '/u01/oracle/admin/backupset/%d.rman_level0_open_disk_nocatalog_controlfile_%s_%p'
current controlfile;
release channel c1;
}
EOF

RC=$?
if [ $RC != 0 ]
then
echo "RMAN return code is $RC. See error log in $ORABACKUPSET." | /usr/bin/mailx -s "`hostname`:$ORACLE_SID RMAN level0 to disk nocatalog failed" user@example.com
fi

/bin/date

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#**************************************************************************
# Open level 0 rman backup to disk.
#**************************************************************************
/bin/date

rman << EOF
connect rcvcat rman/x@bakcat
connect target sys/x

run {
allocate channel c1 type disk;
setlimit channel c1 kbytes 2097150 maxopenfiles 32 readrate 200;
backup
incremental level 0
format '/db_archive/rman/%d_rman_level0_open_database_%s_%p'
database;
sql 'alter system archive log current';
backup skip inaccessible
filesperset 20
format '/db_archive/rman/%d_rman_level0_open_archivelog_%s_%p'
archivelog all
delete input;
backup
format '/db_archive/rman/%d_rman_level0_open_controlfile_%s_%p'
current controlfile;
release channel c1;
}
EOF

if [ $? != 0 ]
then
mailx -s "`hostname`:$ORACLE_SID RMAN to disk failed" usererrormessage@example.com < $LOGFILE
fi

/bin/date
/usr/bin/save -v /db_archive/rman
if [ $? != 0 ]
then
mailx -s "`hostname`:$ORACLE_SID RMAN disk files to tape lib failed" user@example.com < $LOGFILE
fi

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#**************************************************************************
# Open level 0 rman backup to tape.
#**************************************************************************

rman nocatalog << EOF
connect target /
run {
allocate channel c1 type 'SBT_TAPE';
backup
incremental level 0
format '%d_rman_level0_open_database_%s_%p'
database;
sql 'alter system archive log current';
backup skip inaccessible
filesperset 20
format '%d_rman_level0_open_archivelog_%s_%p'
archivelog all
delete input;
backup
format '%d_rman_level0_open_controlfile_%s_%p'
current controlfile;
release channel c1;
}
exit;
EOF

RC=$?
if [ $RC != 0 ]
then
mailx -s "$ORACLE_SID@`hostname` RMAN Backup Failed" user@example.com << EOT
Return code $RC.
Successful would have been 0.
EOT

fi

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
/bin/date
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

list backup;
EOF

RC=$?
/bin/date

exit

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
/bin/date
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

list incarnation;
EOF

RC=$?
/bin/date

exit

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#*************************************************************************************
# Delete expired backups then crosscheck.
#*************************************************************************************
/bin/date
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

delete noprompt expired backup;
crosscheck backup;
EOF

RC=$?
/bin/date

exit

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#*************************************************************************************
# Archived redo and control file and spfile, without recovery catalog.
# DB must be in archivelog mode.
# The log file is not being switched because archive_lag_target handles this task.
# "delete all input" removes archived log files after they have been backed up.
#*************************************************************************************
/bin/date
ORABACKUPSET=/u01/oracle/admin/backupset

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

run {
allocate channel c1 type disk;
backup
format '/u01/oracle/admin/backupset/%d.rman_archivelog_%s_%p'
archivelog all
delete all input;
backup
format '/u01/oracle/admin/backupset/%d.rman_controlfile_%s_%p'
current controlfile;
backup
format '/u01/oracle/admin/backupset/%d.rman_spfile_%s_%p'
spfile;
release channel c1;
}
EOF

RC=$?
/bin/date
if [ $RC != 0 ]
then
echo "Return code is $RC. See error log in $ORABACKUPSET." | /usr/bin/mailx -s "`hostname`:$ORACLE_SID RMAN archivelogcontrolfile to disk nocatalog failed" usererrormessage@example.com
fi

exit

**************************************************************
**************************************************************
**************************************************************

#!/bin/ksh
#**********************************************************************
# Database Point In Time Recovery
# /dbteam/code/rmanDBPITR.oracle.ksh
#
# You must know the time to restore until.
# Refer to Oracle 10g Recovery Manager Reference, page 210.
#
# Example:
# - Ensure a good control file is in place. If not, do control file recovery.
# - Restore redo from tape to /u01/oracle/admin/sid/flash_recovery_area/sid/archivelog/.
# - Restore rman backup files from tape to /u01/oracle/admin/backupset.
# - Ensure files are not gzip'd or compressed. (gzip -d /u01/oracle/admin/backupset/*.gz)
# - Ensure database is shut down.
# - Put the proper time in this script in two places. Both times should be the same.
# - Run this script.
# - May need to: ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/sid/temp01.dbf' REUSE;
#**********************************************************************
/bin/date

$ORACLE_HOME/bin/rman nocatalog << EOF
connect target /

run {
startup nomount;
alter database mount;
restore database until time "timestamp '2005-04-07 13:16:00.00'";
recover database until time "timestamp '2005-04-07 13:16:00.00'";
alter database open resetlogs;
}
EOF

exit

Saturday, March 25, 2006

Oracle Export Through Compressing Pipe

This is an example of using a unix pipe to compress an Oracle export on-the-fly. This is useful when disk space is tight and when there are multiple CPUs.

When the export is running, do a top/prstat and watch how exp and compress both get CPU.

This is from a unix shell. Shell environment and PATHs are configured.

$ mknod mypipe p

$ /usr/bin/compress < mypipe > fullexp.SID.`date +%m%d`.dmp.Z &

$ $ORACLE_HOME/bin/exp userid=/ full=y file=mypipe log=fullexp.SID.`date +%m%d`.log direct=y consistent=n

$ rm mypipe

Monday, March 20, 2006

Sites WIth Great Ad Revenue

Supposedly this dating site makes $10,000 a day in Adsense revenue.

Article on ugly web sites.

Sunday, March 19, 2006

Oracle Statspack Install And Configure

Oracle statspack is the replacement to the bstat/estat scripts. Statspack is an Oracle provided tool that takes snapshots of database instance performance and can provide report on the performance.
While there is good documentation on statspack, and at least one thick book on statspack, thick documentation may lead people to believe that it is too complicated to learn.

This quick guide covers a simple install and configuration of Oracle statspack (database version 9.2) on unix. Most DBAs should be able to these instructions and have a useful statspack implementation in less that thirty minutes.


High level instructions:

Create perfstat user in new tablespace.
Install statspack schema from $ORACLE_HOME/rdbms/admin.
Schedule statspack snaps.
Create statspack reports.


Detailed instructions:

Navigate to the statspack install files.
$ cd $ORACLE_HOME/rdbms/admin

$ ls -l sp*
-rw-r--r-- 1 oracle dba 1771 Mar 9 2002 spauto.sql
-rw-r--r-- 1 oracle dba 100932 Apr 17 2002 spcpkg.sql
-rw-r--r-- 1 oracle dba 861 Apr 17 2002 spcreate.sql
-rw-r--r-- 1 oracle dba 47661 Apr 17 2002 spctab.sql
-rw-r--r-- 1 oracle dba 9062 Apr 17 2002 spcusr.sql
-rw-r--r-- 1 oracle dba 84109 Apr 17 2002 spdoc.txt
-rw-r--r-- 1 oracle dba 758 Mar 9 2002 spdrop.sql
-rw-r--r-- 1 oracle dba 4883 Mar 9 2002 spdtab.sql
-rw-r--r-- 1 oracle dba 1363 Mar 9 2002 spdusr.sql
-rw-r--r-- 1 oracle dba 8423 Apr 1 2002 sppurge.sql
-rw-r--r-- 1 oracle dba 137070 Apr 17 2002 sprepins.sql
-rw-r--r-- 1 oracle dba 1284 Mar 9 2002 spreport.sql
-rw-r--r-- 1 oracle dba 27197 Apr 17 2002 sprepsql.sql
-rw-r--r-- 1 oracle dba 2850 Mar 9 2002 sptrunc.sql
-rw-r--r-- 1 oracle dba 588 Mar 9 2002 spuexp.par
-rw-r--r-- 1 oracle dba 30684 Apr 18 2002 spup816.sql
-rw-r--r-- 1 oracle dba 23329 Apr 18 2002 spup817.sql
-rw-r--r-- 1 oracle dba 19129 Apr 18 2002 spup90.sql

Review the installation help file.
$ more spdoc.txt
< snip >
... the minimum space requirement is approximately 100MB.

After locating a suitable disk location, create the tablespace.
$ sqlplus '/ as sysdba'

SQL> CREATE TABLESPACE PERFSTAT LOGGING
DATAFILE '/u109/oradata/DMPRD/perfstat01.dbf' SIZE 100M REUSE AUTOEXTEND
ON NEXT 10M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
SQL> /
Tablespace created.

Create the perfstat user. "Perfstat" is hard coded in Oracle's create scripts.
SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate
There will be prompts for the perfstat user's password, default tablepace, and temporary tablespace.

Check spool files for errors. Note the password is shown in a spool file.
$ more spc*.lis

Verify instance timed_statistics is set to true. Statspack reports are less useful if this is set to false.
$ sqlplus '/ as sysdba'

SQL> show parameter timed_statistics

NAME TYPE VALUE
------------------------------------ -----------------------------------------
timed_statistics boolean TRUE

Verify job_queue_processes is at least one.
$ sqlplus perfstat


SQL> show parameter job
NAME TYPE VALUE
------------------------------------ -----------------------------------------
job_queue_processes integer 10

Schedule the "snap" job with the PERFSTAT user. By default, this will schedule a level 5 snapshot. Snapping every 15 - 30 minutes is generally appropriate. Longer snap intervals (like an hour) often will not provide enough granular details. Install the job as the perfstat user.

$ sqlplus perfstat
SQL> variable jobNbr number;
begin
dbms_job.submit( :jobNbr, 'statspack.snap;', trunc(sysdate), 'sysdate+1/48');
end;
SQL> 2 3
4 /

PL/SQL procedure successfully completed.

SQL> col priv_user form a11
SQL> col schema_user form a11
SQL> col log_user form a11

SQL> select JOB, NEXT_DATE, NEXT_sec, INTERVAL, WHAT
from user_jobs
where job = :jobNbr
2 3
4 /

JOB NEXT_DATE NEXT_SEC
---------- ------------------- --------
INTERVAL
--------------------------------------------------------------------------------
WHAT
--------------------------------------------------------------------------------
2 2006-03-15 00:00:00 00:00:00
sysdate+1/48
statspack.snap;

Oracle provides an interactive script that allows choosing the instance and time frames for the report.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus '/ as sysdba' @spreport.sql

If you decide statspack information is too valuable and might put you out of a job then you may want to remove it. Simply drop the perfstat user.
$ sqlplus '/ as sysdba'
SQL> drop user perfstat cascade;
SQL> drop tablespace perfstat;

Snapshots are stored in the perfstat tablespace, so it will be necessary to remove old snapshots when the tablespace is nearly full. $ORACLE_HOME/rdbms/admin/sppurge.sql is an interactive removal script that prompts the user for a range of snaphot IDs.

==========================================================================
The following instructions may not be appropriate for you environment. Use at your own risk.
==========================================================================



Install custom PL/SQL package that contains purge logic. 10g statspack.purge reduces the needed DELETE SQL.

$ sqlplus perfstat
SQL> create or replace package statspackCustom
as

procedure purge ( p_daysBack in number );
end statspackcustom;
/
show err

create or replace package body statspackCustom as
procedure purge ( p_daysBack in number )
is
minsnap number;
maxsnap number;
snapshots_purged number;
ldbid number;
linst number;
begin

select case when min(snap_id) is null then 0 else min(snap_id) end
into minsnap
from stats$snapshot
where snap_time < sysdate - p_daysBack;

select case when max(snap_id) is null then 0 else max(snap_id) end
into maxsnap
from stats$snapshot
where snap_time < sysdate - p_daysBack;

/* 10g
snapshots_purged := statspack.purge( i_begin_snap => minsnap
, i_end_snap => maxsnap
, i_snap_range => true
, i_extended_purge => false);
*/

/* 9.2, SQL from $ORACLE_HOME/rdbms/admin/sppurge.sql.*/
select dbid
into ldbid
from v$database;
select instance_number
into linst
from v$instance;

delete from stats$snapshot
where instance_number = linst
and dbid = ldbid
and snap_id between minsnap and maxsnap;

delete
from stats$undostat us
where dbid = ldbid
and instance_number = linst
and begin_time < sysdate - p_daysBack;

delete from stats$database_instance di
where instance_number = linst
and dbid = ldbid
and not exists (select 1
from stats$snapshot s
where s.dbid = di.dbid
and s.instance_number = di.instance_number
and s.startup_time = di.startup_time);

delete from stats$statspack_parameter sp
where instance_number = linst
and dbid = ldbid
and not exists (select 1
from stats$snapshot s
where s.dbid = sp.dbid
and s.instance_number = sp.instance_number);

end purge;
end statspackcustom;
/
show err

Schedule the purge to retain the most recent fifteen days.
$ sqlplus perfstat
SQL> col priv_user form a11
SQL> col schema_user form a11
SQL> col log_user form a11

SQL> variable jobNbr number;
begin
dbms_job.submit( :jobNbr, 'statspackCustom.purge(15);', trunc(sysdate), 'trunc(sysdate)+1');
end;
SQL> 2 3
4 /

PL/SQL procedure successfully completed.


Check jobs. Broken or failed jobs should be corrected.
$ sqlplus /
SQL> col priv_user form a11
SQL> col schema_user form a11
SQL> col log_user form a11
1* select what,broken, failures from dba_jobs where schema_user='PERFSTAT'
SQL> /

WHAT
--------------------------------------------------------------------------------
B FAILURES
- ----------
statspackCustom.purge(15);
N 0

statspack.snap;
N 0





...UNDER CONSTRUCTION...

It can be useful to automatically create reports for every snap period and for every day. This is custom code you can develop.

The script that creates the statspack report is in bighost:/export/home/oracle/local/statspackSpreport.ora.ksh.

Place these line in oracle's crontab to schedule the script.
# Report for most recent snap period - not necessarily an hour. Run after snap job completes. 10,40 assumes 30 minute snap starting on hour.
10,40 * * * * WEBDB=/tmp; export WEBDB; ORACLE_HOME=/apps/oracle/product/9.2.0; export ORACLE_HOME; ORACLE_SID=DMPRD; export ORACLE_SID; /bin/umask 002; /export/home/oracle/local/statspackSpreport.ora.ksh $ORACLE_SID MOSTRECENT >> /tmp/$ORACLE_SID.statspackSpreport.mostrecent.out 2>&1

Korn Shell File Name Completion

At the korn shell (ksh) command line, enter the first few characters of a file or directory name, and the shell will complete the name.

In HP-UX ksh, press <esc> <esc>. Or press \.

In Solaris ksh, press <esc> \.

Thursday, March 16, 2006

Oracle OEM From Unix

If have an X display, you may run OEM tools from unix with "oemapp". This is like the MSWindows OEM: oemapp console
Looks in $ORACLE_HOME/bin for oemapp.

Oracle SQLPlus Without Password

Many Oracle administrators are concerned about hard-coding passwords
in scripts and crontabs. Oracle 9i from the command line allows a simple
logon like this: oracle@bighost $ sqlplus '/ as sysdba'

This provides a simple method to run scripts without a password.
Another method is to create and externally authenticated account, such as
ops$oracle. Then log on with: oracle@bighost $ sqlplus /

When using either method, remember to set up the shell environment
variables before running sqlplus.

Tuesday, March 14, 2006

Oracle SQL Developer

Oracle has released SQL Developer, a no-charge GUI tool for developers.

Quest has sold TOAD for several years, though hasn't done much develop in recent years. When in a software development office, the morning are filled with the croaking toad sound when developers start the application.

Tora is an open source package that has similar functionality.

Zfone VoIP Encryption

Philip Zimmermann has released Zfone, a VoIP encryption package that works with Gizmo and other VoIP services. He has submitted the package as a proposed IETF standard.

Sunday, March 12, 2006

HP-UX Going, Going, Gone...

I am working with some machines hosted in a HP data center (Littleton Massachusetts), so the choice of HP-UX is not a surprise.
My previous HP-UX experience was version 10 several years ago. It appears little has changed during those years, and freeware support is lacking.
I tried to install a web browser... any web browser. Unlike other OSs, this is not a ten minute task.

Mozilla distributions support Windows, Mac, and Linux x86.

Opera distributions support more OSs - Windows, Mac, Linus, FreeBSD, Solaris, OS2, QNX, and many mobile platforms.

Opera has a distribution for OS2 and not HP-UX.

HP-UX's 3rd party software developers have left the building.

GMail Beta

GMail has been in beta for a couple years. This is the longest beta I have seen. Just one more feature and another and another. No software is ever complete.

Tuesday, March 07, 2006

DB2 Free Production License

Following up this DB2 post, DB2 Express is no charge. This is useful for development, learning, and lightweight production.

Sunday, March 05, 2006

Open Router Software

XORP is an open source IP router that can run on PC hardware. Enterprise-class routers from Cisco and other routher manufacturers have been highly priced and are often administered by high-priced people who typically barricade their knowledge from the rest of the IT staff.

This Business Week magazine article considers the threat to Cisco that is posed by XORP.

Open source has been sweeping the IT industry. Operating systems, databases, and application servers all have several viable open source products. Products in the networking layer of the IP stack still have fat margins and over-priced personnel and support. This stronghold of fat margins is now under seige by open source.

Online Store Operational Costs

Strategy and Buiness magazine has an article online about the cost to serve of web stores. The article explains how some products are more or less tailored to online sales, and how stores with online and physical presence can tailor their product stocking to reduce the cost to serve.

DB2 Certification

There is a continuous base of support and interest in the DB2 database from IBM. While there is apparently not as much demand as some other database engines, there is a a group of customers who are happy to use what works for them: DB2, CICS, JCL, CLIST, REXX.

DB2 certification tutorials.

DB2 certification books.

DB2 certification newsletter from IBM.

DB2 magazine carries good articles from columnists such as Robert Catterall.


This is a typical DB2 job advertisement.
Title: Contract to hire or Perm DB2 DBA in the DTC
Skills: DB2, Linux/Unix, Windows NT

Date: 2-21-2006
Location: Greenwood Village, CO
Area code: 303

Tax term: FULLTIME CON_W2 CON_HIRE_W2
Pay rate: 0
Length: 6 mo cth

Position ID: 1653-A0747
Dice ID: ktsor

Job description:
Are you a solid, experienced DB2 DBA? Would you like to work for a company that not only prides themselves on their satisfied customers, but also considers their employee base to be their greatest treasure?

We are working with a company in the DTC that is looking for a DB2 DBA to join their team. They need someone with at least 3 years of experience in a DB2 DBA role on a Linux/Unix and Windows NT platform. The selected candidate will be responsible for day-to-day admin of three databases as well as quality control and data audits to ensure accurate and appropriate use of data. The person in this position will also be working with clients to resolve data conflicts and any other troubleshooting needs.

Other helpful things to know would be Query Patroller and Quest Central.

This company is looking for someone that's able to hit the ground running ASAP. If you are interested in this position, please e-mail your resume, contract rate and salary requirements to saprakl@kellyservices.com. Local candidates will be given preference.

Travel required: none
Telecommute: no

Saturday, March 04, 2006

EVDO WiFi Router

This EV-DO WiFi router may be enough to get me to subscribe to Verizon Wireless broadband wireless EV-DO. I wish Verizon's EV-DO coverage was better.

Some more information on the EV-DO WiFi router.

Friday, March 03, 2006

Broadband In The Boonies

Notes on setting up shop far from good internet access.

Navini networks has some wimax deployed in the field. A base station near an internet connection pointed at the remote wimax modem can provide a connection. Craft a reflecting antenna around the back side to direct the beam. Navinia sells the base station with either omni or sectional antenna. The sectional antenna base station may peform better.

The Junxion Box takes a PC Card and shares the connection via Ethernet or wifi. This is useful with Verizon BroadbandAccess wireless data service, and could be used to back up the primary connection.

Lumin makes a portable wifi repeater with solar cell and battery. This could be a nice package to squat on a ridgeline that has line of site between the source and destination. A backup link such as IDSL or Verizon wireless broadband would be useful as a fall back.

FCC and FAA allow towers up to 200 feet without paperwork / flashing red light. If this could provide line of sight, then it might even be possible to sell tower space to a wifi provider or Verizon. Remember to ground the tower and consider wind loads and power supply.
Consider a used tower.

Speakeasy can provide DSL, IDSL for those further away from a central office, and T1. Speakeasy has a reputation for good reliability and low ping times. They provide quotes via email by submitting a service address and service phone number.
IDSL is usually more expensive than ordinary DSL and bandwidth is about 130K. With bandwidth prioritization/shaping, this could be used for interactive command line work or as a backup link.

Bandwidth prioritization can make a small pipe feel more snappy.
Hawking sells an inexpensive appliance named Broadband Booster that can provide traffic prioritization, though it is not configurable. This would be useful if running VOIP in a home office. You can also roll your own with an old PC, linux, and bandwidth shaping software. More expensive enterprise class products are available.

For power outages, install a large UPS with a generator or solar cells. Or get a tiny (10 minute capacity) UPS with a whole house generator. A 10kW Cummins Onan generator (natural gas or propane) with automatic transfer switch is nearly $5000 (including shipping, plus installation).
This wind turbine looks interesting. This would require large UPSs for times when there is less wind.